[Midnightbsd-cvs] mports: schema.sql: new schema.

ctriv at midnightbsd.org ctriv at midnightbsd.org
Fri Nov 16 00:33:19 EST 2007


Log Message:
-----------
new schema.

Modified Files:
--------------
    mports/Tools/magus:
        schema.sql (r1.5 -> r1.6)

-------------- next part --------------
Index: schema.sql
===================================================================
RCS file: /home/cvs/mports/Tools/magus/schema.sql,v
retrieving revision 1.5
retrieving revision 1.6
diff -L Tools/magus/schema.sql -L Tools/magus/schema.sql -u -r1.5 -r1.6
--- Tools/magus/schema.sql
+++ Tools/magus/schema.sql
@@ -23,8 +23,9 @@
 CREATE TABLE `categories` (
   `id` int(11) NOT NULL auto_increment,
   `category` varchar(64) default NULL,
-  PRIMARY KEY  (`id`)
-) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+  PRIMARY KEY  (`id`),
+  UNIQUE KEY `category` (`category`)
+) ENGINE=InnoDB;
 
 --
 -- Table structure for table `depends`
@@ -32,11 +33,11 @@
 
 DROP TABLE IF EXISTS `depends`;
 CREATE TABLE `depends` (
-  `port` varchar(128) NOT NULL,
-  `dependency` varchar(128) NOT NULL,
+  `port` int(11) NOT NULL,
+  `dependency` int(11) NOT NULL,
   KEY `port` (`port`),
   KEY `dependency` (`dependency`)
-) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+) ENGINE=InnoDB;
 
 --
 -- Table structure for table `locks`
@@ -45,12 +46,11 @@
 DROP TABLE IF EXISTS `locks`;
 CREATE TABLE `locks` (
   `id` int(11) NOT NULL auto_increment,
-  `port` varchar(128) NOT NULL,
-  `arch` varchar(8) NOT NULL,
+  `port` int(11) NOT NULL,
   `machine` int(11) NOT NULL,
   PRIMARY KEY  (`id`),
-  UNIQUE KEY `port` (`port`,`arch`)
-) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+  UNIQUE KEY `port` (`port`)
+) ENGINE=InnoDB;
 
 --
 -- Table structure for table `logs`
@@ -60,9 +60,9 @@
 CREATE TABLE `logs` (
   `result` int(11) NOT NULL,
   `phase` varchar(32) NOT NULL,
-  `data` longtext,
+  `data` text,
   UNIQUE KEY `result` (`result`,`phase`)
-) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+) ENGINE=InnoDB;
 
 --
 -- Table structure for table `machines`
@@ -71,11 +71,11 @@
 DROP TABLE IF EXISTS `machines`;
 CREATE TABLE `machines` (
   `id` int(11) NOT NULL auto_increment,
-  `arch` varchar(128) NOT NULL,
+  `arch` varchar(12) NOT NULL,
   `name` varchar(128) NOT NULL,
   `maintainer` varchar(128) NOT NULL,
   PRIMARY KEY  (`id`)
-) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+) ENGINE=InnoDB;
 
 --
 -- Table structure for table `port_categories`
@@ -84,8 +84,10 @@
 DROP TABLE IF EXISTS `port_categories`;
 CREATE TABLE `port_categories` (
   `port` varchar(128) NOT NULL,
-  `category` int(11) NOT NULL
-) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+  `category` int(11) NOT NULL,
+  KEY `port` (`port`),
+  KEY `category` (`category`)
+) ENGINE=InnoDB;
 
 --
 -- Table structure for table `ports`
@@ -93,13 +95,33 @@
 
 DROP TABLE IF EXISTS `ports`;
 CREATE TABLE `ports` (
+  `id` int(11) NOT NULL auto_increment,
   `name` varchar(128) NOT NULL,
+  `arch` varchar(8) NOT NULL,
   `version` varchar(32) default NULL,
   `description` text,
   `license` varchar(16) default NULL,
   `pkgname` varchar(128) NOT NULL,
-  PRIMARY KEY  (`name`)
-) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+  PRIMARY KEY  (`id`),
+  UNIQUE KEY `archname` (`name`,`arch`),
+  KEY `name` (`name`)
+) ENGINE=InnoDB;
+
+--
+-- Temporary table structure for view `ready_ports`
+--
+
+DROP TABLE IF EXISTS `ready_ports`;
+/*!50001 DROP VIEW IF EXISTS `ready_ports`*/;
+/*!50001 CREATE TABLE `ready_ports` (
+  `id` int(11),
+  `name` varchar(128),
+  `arch` varchar(8),
+  `version` varchar(32),
+  `description` text,
+  `license` varchar(16),
+  `pkgname` varchar(128)
+) */;
 
 --
 -- Table structure for table `results`
@@ -108,15 +130,28 @@
 DROP TABLE IF EXISTS `results`;
 CREATE TABLE `results` (
   `id` int(11) NOT NULL auto_increment,
-  `port` varchar(128) NOT NULL,
+  `port` int(11) NOT NULL,
   `version` varchar(32) NOT NULL,
   `summary` varchar(32) NOT NULL,
   `machine` int(11) NOT NULL,
-  `arch` varchar(8) NOT NULL,
+  `snap` int(11) NOT NULL,
+  `osversion` varchar(64) NOT NULL,
+  `created` timestamp NOT NULL default CURRENT_TIMESTAMP,
   PRIMARY KEY  (`id`),
   KEY `port` (`port`,`version`),
-  KEY `port_2` (`port`,`version`,`arch`)
-) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+  KEY `summary` (`summary`)
+) ENGINE=InnoDB;
+
+--
+-- Table structure for table `snaps`
+--
+
+DROP TABLE IF EXISTS `snaps`;
+CREATE TABLE `snaps` (
+  `id` int(11) NOT NULL auto_increment,
+  `created` timestamp NOT NULL default CURRENT_TIMESTAMP,
+  PRIMARY KEY  (`id`)
+) ENGINE=InnoDB;
 
 --
 -- Table structure for table `subresults`
@@ -124,31 +159,25 @@
 
 DROP TABLE IF EXISTS `subresults`;
 CREATE TABLE `subresults` (
+  `id` int(11) NOT NULL auto_increment,
   `result` int(11) NOT NULL,
   `phase` varchar(16) NOT NULL,
   `type` varchar(32) NOT NULL,
   `name` varchar(128) NOT NULL,
-  `msg` text
-) ENGINE=MyISAM DEFAULT CHARSET=latin1;
-/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
-
-
+  `msg` text,
+  PRIMARY KEY  (`id`)
+) ENGINE=InnoDB;
 
 --
--- Table structure for table `tasks`
+-- Final view structure for view `ready_ports`
 --
 
-DROP TABLE IF EXISTS `tasks`;
-CREATE TABLE `tasks` (
-  `id` int(11) NOT NULL auto_increment,
-  `type` varchar(64) NOT NULL,
-  `machine` int(11) NOT NULL,
-  `started` tinyint(1) NOT NULL default '0',
-  `completed` tinyint(1) NOT NULL default '0',
-  PRIMARY KEY  (`id`)
-) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
-
-
+/*!50001 DROP TABLE IF EXISTS `ready_ports`*/;
+/*!50001 DROP VIEW IF EXISTS `ready_ports`*/;
+/*!50001 CREATE ALGORITHM=UNDEFINED */
+/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
+/*!50001 VIEW `ready_ports` AS select `ports`.`id` AS `id`,`ports`.`name` AS `name`,`ports`.`arch` AS `arch`,`ports`.`version` AS `version`,`ports`.`description` AS `description`,`ports`.`license` AS `license`,`ports`.`pkgname` AS `pkgname` from `ports` where ((not(`ports`.`id` in (select `locks`.`port` AS `port` from `locks`))) and (not(`ports`.`id` in (select `results`.`port` AS `port` from `results` where (`results`.`version` = `ports`.`version`)))) and ((not(`ports`.`id` in (select `depends`.`port` AS `port` from `depends`))) or (not(`ports`.`id` in (select `depends`.`port` AS `port` from `depends` where ((not(`depends`.`dependency` in (select `results`.`port` AS `port` from (`results` join `ports` on(((`ports`.`id` = `results`.`port`) and (`ports`.`version` = `results`.`version`)))) where ((`results`.`summary` = _latin1'pass') or (`results`.`summary` = _latin1'fail'))))) or `depends`.`dependency` in (select `locks`.`port` AS `port` from `locks`))))))) */;
+/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
 
 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
@@ -158,4 +187,4 @@
 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
 
--- Dump completed on 2007-10-22 16:05:40
+-- Dump completed on 2007-11-16  6:12:14


More information about the Midnightbsd-cvs mailing list