Tema: Existe programa para transformar codigo sql en tablas graficas?
Ver Mensaje Individual
  #3 (permalink)  
Antiguo 13/09/2009, 14:45
Avatar de neodani
neodani
 
Fecha de Ingreso: marzo-2007
Mensajes: 1.811
Antigüedad: 17 años, 2 meses
Puntos: 20
Respuesta: Existe programa para transformar codigo sql en tablas graficas?
Cita:
Iniciado por gnzsoloyo Ver Mensaje
Con los mismos programas que dices se hace eso.
Es una herramienta que suele aparecer con el nombre de "Ingeniería inversa". Es algo que hasta aparece en el Visual Studio, o en el MS Visio.
Puede también aparecer con el nombre de "import schema" (Import DDL definition from ODBC en caso Enterprise Architect, de Sparx).
Gracias, encontré la opción!

Pero me salió que no utiliza ninguna clave foranea lo cual me extrañó y bastante.
Es posible que un sistema de tickets como el conocido OSTICKET no utilice claves foraneas en su tablas?¿

O esto se considera clave foranea? KEY `group_active` (`group_enabled`)


Código sql: 
Ver original
  1. DROP TABLE IF EXISTS `%TABLE_PREFIX%api_key`;
  2. CREATE TABLE `%TABLE_PREFIX%api_key` (
  3.   `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  4.   `isactive` tinyint(1) NOT NULL DEFAULT '1',
  5.   `ipaddr` VARCHAR(16) NOT NULL,
  6.   `apikey` VARCHAR(255) NOT NULL,
  7.   `updated` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  8.   `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  9.   PRIMARY KEY  (`id`),
  10.   UNIQUE KEY `ipaddr` (`ipaddr`)
  11. ) ENGINE=MyISAM;
  12.  
  13. INSERT INTO `%TABLE_PREFIX%api_key` (`id`, `isactive`, `ipaddr`, `apikey`, `updated`, `created`) VALUES (1, 1, '192.168.1.5', 'siri!', NOW(), NOW());
  14.  
  15.  
  16. DROP TABLE IF EXISTS `%TABLE_PREFIX%department`;
  17. CREATE TABLE `%TABLE_PREFIX%department` (
  18.   `dept_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  19.   `tpl_id` INT(10) UNSIGNED NOT NULL DEFAULT '0',
  20.   `email_id` INT(10) UNSIGNED NOT NULL DEFAULT '0',
  21.   `autoresp_email_id` INT(10) UNSIGNED NOT NULL DEFAULT '0',
  22.   `manager_id` INT(10) UNSIGNED NOT NULL DEFAULT '0',
  23.   `dept_name` VARCHAR(32) NOT NULL DEFAULT '',
  24.   `dept_signature` VARCHAR(255) NOT NULL DEFAULT '',
  25.   `ispublic` tinyint(1) UNSIGNED NOT NULL DEFAULT '1',
  26.   `ticket_auto_response` tinyint(1) NOT NULL DEFAULT '1',
  27.   `message_auto_response` tinyint(1) NOT NULL DEFAULT '0',
  28.   `can_append_signature` tinyint(1) NOT NULL DEFAULT '1',
  29.   `updated` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  30.   `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  31.   PRIMARY KEY  (`dept_id`),
  32.   UNIQUE KEY `dept_name` (`dept_name`),
  33.   KEY `manager_id` (`manager_id`),
  34.   KEY `autoresp_email_id` (`autoresp_email_id`),
  35.   KEY `tpl_id` (`tpl_id`)
  36. ) ENGINE=MyISAM;
  37.  
  38. INSERT INTO `%TABLE_PREFIX%department` (`dept_id`, `tpl_id`, `email_id`, `autoresp_email_id`, `manager_id`, `dept_name`, `dept_signature`, `ispublic`, `ticket_auto_response`, `message_auto_response`, `can_append_signature`, `updated`, `created`) VALUES
  39. (1, 0, 1, 0, 0, 'Support', 'Support Dept', 1, 1, 1, 1, NOW(), NOW()),
  40. (2, 0, 1, 0, 0, 'Billing', 'Billing Dept', 1, 1, 1, 1, NOW(), NOW());
  41.  
  42.  
  43. DROP TABLE IF EXISTS `%TABLE_PREFIX%email_banlist`;
  44. CREATE TABLE `%TABLE_PREFIX%email_banlist` (
  45.   `id` INT(11) NOT NULL AUTO_INCREMENT,
  46.   `email` VARCHAR(255) NOT NULL DEFAULT '',
  47.   `submitter` VARCHAR(126) NOT NULL DEFAULT '',
  48.   `added` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  49.   PRIMARY KEY  (`id`),
  50.   UNIQUE KEY `email` (`email`)
  51. ) ENGINE=MyISAM;
  52.  
  53. INSERT INTO `%TABLE_PREFIX%email_banlist` (`id`, `email`, `submitter`, `added`) VALUES
  54. (1, '[email protected]', 'System', NOW());
  55.  
  56. DROP TABLE IF EXISTS `%TABLE_PREFIX%email_template`;
  57. CREATE TABLE `%TABLE_PREFIX%email_template` (
  58.   `tpl_id` INT(11) NOT NULL AUTO_INCREMENT,
  59.   `cfg_id` INT(10) UNSIGNED NOT NULL DEFAULT '0',
  60.   `name` VARCHAR(32) NOT NULL DEFAULT '',
  61.   `notes` text,
  62.   `ticket_autoresp_subj` VARCHAR(255) NOT NULL DEFAULT '',
  63.   `ticket_autoresp_body` text NOT NULL,
  64.   `ticket_alert_subj` VARCHAR(255) NOT NULL DEFAULT '',
  65.   `ticket_alert_body` text NOT NULL,
  66.   `message_autoresp_subj` VARCHAR(255) NOT NULL DEFAULT '',
  67.   `message_autoresp_body` text NOT NULL,
  68.   `message_alert_subj` VARCHAR(255) NOT NULL DEFAULT '',
  69.   `message_alert_body` text NOT NULL,
  70.   `note_alert_subj` VARCHAR(255) NOT NULL,
  71.   `note_alert_body` text NOT NULL,
  72.   `assigned_alert_subj` VARCHAR(255) NOT NULL DEFAULT '',
  73.   `assigned_alert_body` text NOT NULL,
  74.   `ticket_overdue_subj` VARCHAR(255) NOT NULL DEFAULT '',
  75.   `ticket_overdue_body` text NOT NULL,
  76.   `ticket_overlimit_subj` VARCHAR(255) NOT NULL DEFAULT '',
  77.   `ticket_overlimit_body` text NOT NULL,
  78.   `ticket_reply_subj` VARCHAR(255) NOT NULL DEFAULT '',
  79.   `ticket_reply_body` text NOT NULL,
  80.   `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  81.   `updated` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  82.   PRIMARY KEY  (`tpl_id`),
  83.   KEY `cfg_id` (`cfg_id`),
  84.   FULLTEXT KEY `message_subj` (`ticket_reply_subj`)
  85. ) ENGINE=MyISAM;
  86.  
  87.  
  88. DROP TABLE IF EXISTS `%TABLE_PREFIX%groups`;
  89. CREATE TABLE `%TABLE_PREFIX%groups` (
  90.   `group_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  91.   `group_enabled` tinyint(1) UNSIGNED NOT NULL DEFAULT '1',
  92.   `group_name` VARCHAR(50) NOT NULL DEFAULT '',
  93.   `dept_access` VARCHAR(255) NOT NULL DEFAULT '',
  94.   `can_edit_tickets` tinyint(3) UNSIGNED NOT NULL DEFAULT '1',
  95.   `can_delete_tickets` tinyint(1) UNSIGNED NOT NULL DEFAULT '0',
  96.   `can_close_tickets` tinyint(1) UNSIGNED NOT NULL DEFAULT '0',
  97.   `can_transfer_tickets` tinyint(1) NOT NULL DEFAULT '1',
  98.   `can_ban_emails` tinyint(1) UNSIGNED NOT NULL DEFAULT '0',
  99.   `can_manage_kb` tinyint(1) UNSIGNED NOT NULL DEFAULT '0',
  100.   `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  101.   `updated` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  102.   PRIMARY KEY  (`group_id`),
  103.   KEY `group_active` (`group_enabled`)
  104. ) ENGINE=MyISAM;
  105.  
  106. INSERT INTO `%TABLE_PREFIX%groups` (`group_id`, `group_enabled`, `group_name`, `dept_access`, `can_edit_tickets`, `can_delete_tickets`, `can_close_tickets`, `can_transfer_tickets`, `can_ban_emails`, `can_manage_kb`, `created`, `updated`) VALUES
  107. (1, 1, 'Admins', '1', 1, 1, 1, 1, 1, 1, NOW(), NOW()),
  108. (2, 1, 'Managers', '1', 1, 0, 1, 1, 1, 1, NOW(), NOW()),
  109. (3, 1, 'Staff', '1', 0, 0, 0, 0, 0, 0, NOW(), NOW());
  110.  
  111. DROP TABLE IF EXISTS `%TABLE_PREFIX%help_topic`;
  112. CREATE TABLE `%TABLE_PREFIX%help_topic` (
  113.   `topic_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  114.   `isactive` tinyint(1) UNSIGNED NOT NULL DEFAULT '1',
  115.   `noautoresp` tinyint(3) UNSIGNED NOT NULL DEFAULT '0',
  116.   `priority_id` tinyint(3) UNSIGNED NOT NULL DEFAULT '0',
  117.   `dept_id` tinyint(3) UNSIGNED NOT NULL DEFAULT '0',
  118.   `topic` VARCHAR(32) NOT NULL DEFAULT '',
  119.   `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  120.   `updated` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  121.   PRIMARY KEY  (`topic_id`),
  122.   UNIQUE KEY `topic` (`topic`),
  123.   KEY `priority_id` (`priority_id`),
  124.   KEY `dept_id` (`dept_id`)
  125. ) ENGINE=MyISAM;
  126.  
  127. INSERT INTO `%TABLE_PREFIX%help_topic` (`topic_id`, `isactive`, `noautoresp`, `priority_id`, `dept_id`, `topic`, `created`, `updated`) VALUES
  128. (1, 1, 0, 2, 1, 'Support', NOW(), NOW()),
  129. (2, 1, 0, 3, 1, 'Billing', NOW(), NOW());
  130.  
  131. DROP TABLE IF EXISTS `%TABLE_PREFIX%kb_premade`;
  132. CREATE TABLE `%TABLE_PREFIX%kb_premade` (
  133.   `premade_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  134.   `dept_id` INT(10) UNSIGNED NOT NULL DEFAULT '0',
  135.   `isenabled` tinyint(1) UNSIGNED NOT NULL DEFAULT '1',
  136.   `title` VARCHAR(125) NOT NULL DEFAULT '',
  137.   `answer` text NOT NULL,
  138.   `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  139.   `updated` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  140.   PRIMARY KEY  (`premade_id`),
  141.   UNIQUE KEY `title_2` (`title`),
  142.   KEY `dept_id` (`dept_id`),
  143.   KEY `active` (`isenabled`),
  144.   FULLTEXT KEY `title` (`title`,`answer`)
  145. ) ENGINE=MyISAM;
  146.  
  147.  
  148. DROP TABLE IF EXISTS `%TABLE_PREFIX%syslog`;
  149. CREATE TABLE `%TABLE_PREFIX%syslog` (
  150.   `log_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  151.   `log_type` enum('Debug','Warning','Error') NOT NULL,
  152.   `title` VARCHAR(255) NOT NULL,
  153.   `log` text NOT NULL,
  154.   `logger` VARCHAR(64) NOT NULL,
  155.   `ip_address` VARCHAR(16) NOT NULL,
  156.   `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  157.   `updated` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  158.   PRIMARY KEY  (`log_id`),
  159.   KEY `log_type` (`log_type`)
  160. ) ENGINE=MyISAM;
  161.  
  162. DROP TABLE IF EXISTS `%TABLE_PREFIX%ticket`;
  163. CREATE TABLE `%TABLE_PREFIX%ticket` (
  164.   `ticket_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  165.   `ticketID` INT(11) UNSIGNED NOT NULL DEFAULT '0',
  166.   `dept_id` INT(10) UNSIGNED NOT NULL DEFAULT '1',
  167.   `priority_id` INT(10) UNSIGNED NOT NULL DEFAULT '2',
  168.   `staff_id` INT(10) UNSIGNED NOT NULL DEFAULT '0',
  169.   `email` VARCHAR(120) NOT NULL DEFAULT '',
  170.   `name` VARCHAR(32) NOT NULL DEFAULT '',
  171.   `subject` VARCHAR(64) NOT NULL DEFAULT '[no subject]',
  172.   `topic` VARCHAR(64) DEFAULT NULL,
  173.   `phone` VARCHAR(16) DEFAULT NULL,
  174.   `phone_ext` VARCHAR(8) DEFAULT NULL,
  175.   `ip_address` VARCHAR(16) NOT NULL DEFAULT '',
  176.   `status` enum('open','closed') NOT NULL DEFAULT 'open',
  177.   `source` enum('Web','Email','Phone','Other') NOT NULL DEFAULT 'Other',
  178.   `isoverdue` tinyint(1) UNSIGNED NOT NULL DEFAULT '0',
  179.   `isanswered` tinyint(1) UNSIGNED NOT NULL DEFAULT '0',
  180.   `duedate` datetime DEFAULT NULL,
  181.   `reopened` datetime DEFAULT NULL,
  182.   `closed` datetime DEFAULT NULL,
  183.   `lastmessage` datetime DEFAULT NULL,
  184.   `lastresponse` datetime DEFAULT NULL,
  185.   `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  186.   `updated` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  187.   PRIMARY KEY  (`ticket_id`),
  188.   UNIQUE KEY `email_extid` (`ticketID`,`email`),
  189.   KEY `dept_id` (`dept_id`),
  190.   KEY `staff_id` (`staff_id`),
  191.   KEY `status` (`status`),
  192.   KEY `priority_id` (`priority_id`),
  193.   KEY `created` (`created`),
  194.   KEY `closed` (`closed`),
  195.   KEY `duedate` (`duedate`)
  196. ) ENGINE=MyISAM;