[mirrorbrain-commits] [opensuse-svn] r6436 - in trunk/tools/download-redirector-v2: . sql

From: Novell Forge SVN <noreply_at_novell.com>
Date: Wed, 11 Feb 2009 13:09:41 -0700 (MST)
Author: poeml
Date: 2009-02-11 13:09:35 -0700 (Wed, 11 Feb 2009)
New Revision: 6436

Added:
   trunk/tools/download-redirector-v2/sql/initialdata-mysql.sql
   trunk/tools/download-redirector-v2/sql/initialdata-postgresql.sql
   trunk/tools/download-redirector-v2/sql/schema-mysql.sql
   trunk/tools/download-redirector-v2/sql/schema-postgresql.sql
Removed:
   trunk/tools/download-redirector-v2/sql/mirrorbrain-mysql.sql
   trunk/tools/download-redirector-v2/sql/mirrorbrain-postgresql.sql
Modified:
   trunk/tools/download-redirector-v2/INSTALL
Log:
MirrorBrain SQL schema:
- add country and region data for importing into newly created databases


Modified: trunk/tools/download-redirector-v2/INSTALL
===================================================================
--- trunk/tools/download-redirector-v2/INSTALL	2009-02-11 19:18:09 UTC (rev 6435)
+++ trunk/tools/download-redirector-v2/INSTALL	2009-02-11 20:09:35 UTC (rev 6436)
@@ -61,31 +61,36 @@
   description below:
 
 
-   a) Using MySQL
+    a) Using MySQL
 
-      - install the MySQL database adapter for the DBD library
-        (openSUSE/SLE package: libapr-util1-dbd-mysql)
-        Note, if the web server is set up seperately from the database server,
-        only the web server needs this package.
+       - install the MySQL database adapter for the DBD library
+         (openSUSE/SLE package: libapr-util1-dbd-mysql)
+         Note, if the web server is set up seperately from the database server,
+         only the web server needs this package.
 
       
-      - install and start MySQL
+       - install and start MySQL
       
-      - log into your database at root:
-          mysql -u root -p
-        there, create the database and grant write permissions for a local user:
-          create database mb_samba;
-          grant all privileges on mb_samba.* to wwwrun_at_localhost identified by '12345';
+       - log into your database at root:
+           mysql -u root -p
+         there, create the database and grant write permissions for a local user:
+           create database mb_samba;
+           grant all privileges on mb_samba.* to wwwrun_at_localhost identified by '12345';
       
-        run the sql file which creates the tables and indices:
-          mysql -u wwwrun -p -D mb_samba < /usr/share/doc/packages/apache2-mod_zrkadlo/sql/mirrorbrain-mysql.sql
-      
-      - the mysql InnoDB engine needs to be configured to use enough memory for the
-        database. This is probably the most important thing to know and the most
-        frequent deployment error, to not tune this. Use the innodb_buffer_pool_size
-        value to match it to the size of your database. The default value is tiny.
-        Although, when your database is tiny, too, it won't matter much. But for
-        larger databases, the value needs to be increased, possibly considerably.
+         run the sql file which creates the tables and indices:
+           mysql -u wwwrun -p -D mb_samba < /usr/share/doc/packages/apache2-mod_zrkadlo/sql/schema-mysql.sql
+
+       - the mysql InnoDB engine needs to be configured to use enough memory for the
+         database. This is probably the most important thing to know and the most
+         frequent deployment error, to not tune this. Use the innodb_buffer_pool_size
+         value to match it to the size of your database. The default value is tiny.
+         Although, when your database is tiny, too, it won't matter much. But for
+         larger databases, the value needs to be increased, possibly considerably.
+
+
+       - import table structure, and initial data:
+           psql -U mb -f sql/schema-mysql.sql mb_samba
+           psql -U mb -f sql/initialdata-mysql.sql mb_samba
   
 
     b) Using PostgreSQL
@@ -144,22 +149,11 @@
 
 
 
-       - psql -U mb -f sql/mirrorbrain-postgresql.sql mb_samba
+       - import table structure, and initial data:
+           psql -U mb -f sql/schema-postgresql.sql mb_samba
+           psql -U mb -f sql/initialdata-postgresql.sql mb_samba
+       
 
-           Password for user mb: 
-           BEGIN
-           psql:sql/mirrorbrain-postgresql.sql:14: NOTICE:  CREATE TABLE will create implicit sequence "file_id_seq" for serial column "file.id"
-	   
-	   [...]
-
-           psql:sql/mirrorbrain-postgresql.sql:92: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "country_pkey" for table "country"
-           CREATE TABLE
-           CREATE FUNCTION
-           COMMIT
-
-
-
-
 - create a group and user to run the mirrorbrain tools under:
     groupadd mirrorbrain
     useradd mirrorbrain -g mirrorbrain
@@ -226,7 +220,7 @@
 
 - example migration commands from MySQL to PostgreSQL: 
     createdb -O mb mb_samba
-    psql -U mb -f sql/mirrorbrain-postgresql.sql mb_samba
+    psql -U mb -f sql/schema-postgresql.sql mb_samba
 
     mb -b samba export --format postgresql | psql -U mb mb_samba
 

Added: trunk/tools/download-redirector-v2/sql/initialdata-mysql.sql
===================================================================
--- trunk/tools/download-redirector-v2/sql/initialdata-mysql.sql	                        (rev 0)
+++ trunk/tools/download-redirector-v2/sql/initialdata-mysql.sql	2009-02-11 20:09:35 UTC (rev 6436)
@@ -0,0 +1,4 @@
+INSERT INTO region VALUES (1,'af','Africa'),(2,'as','Asia'),(3,'eu','Europe'),(4,'na','North America'),(5,'sa','South America'),(6,'oc','Oceania');
+
+INSERT INTO country VALUES (1,'af','Afghanistan'),(2,'ax','A■land Islands'),(3,'al','Albania'),(4,'dz','Algeria'),(5,'as','American Samoa'),(6,'ad','Andorra'),(7,'ao','Angola'),(8,'ai','Anguilla'),(9,'aq','Antarctica'),(10,'ag','Antigua and Barbuda'),(11,'ar','Argentina'),(12,'am','Armenia'),(13,'aw','Aruba'),(14,'au','Australia'),(15,'at','Austria'),(16,'az','Azerbaijan'),(17,'bs','Bahamas'),(18,'bh','Bahrain'),(19,'bd','Bangladesh'),(20,'bb','Barbados'),(21,'by','Belarus'),(22,'be','Belgium'),(23,'bz','Belize'),(24,'bj','Benin'),(25,'bm','Bermuda'),(26,'bt','Bhutan'),(27,'bo','Bolivia'),(28,'ba','Bosnia and Herzegovina'),(29,'bw','Botswana'),(30,'bv','Bouvet Island'),(31,'br','Brazil'),(32,'io','British Indian Ocean Territory'),(33,'bn','Brunei Darussalam'),(34,'bg','Bulgaria'),(35,'bf','Burkina Faso'),(36,'bi','Burundi'),(37,'kh','Cambodia'),(38,'cm','Cameroon'),(39,'ca','Canada'),(40,'cv','Cape Verde'),(41,'ky','Cayman Islands'),(42,'cf','Central African Republic'),(43
 ,'td','Chad'),(44,'cl','Chile'),(45,'cn','China'),(46,'cx','Christmas Island'),(47,'cc','Cocos (Keeling) Islands'),(48,'co','Colombia'),(49,'km','Comoros'),(50,'cg','Congo'),(51,'cd','Congo, Democratic Republic of the'),(52,'ck','Cook Islands'),(53,'cr','Costa Rica'),(54,'ci','CA■te d\'Ivoire'),(55,'hr','Croatia'),(56,'cu','Cuba'),(57,'cy','Cyprus'),(58,'cz','Czech Republic'),(59,'dk','Denmark'),(60,'dj','Djibouti'),(61,'dm','Dominica'),(62,'do','Dominican Republic'),(63,'ec','Ecuador'),(64,'eg','Egypt'),(65,'sv','El Salvador'),(66,'gq','Equatorial Guinea'),(67,'er','Eritrea'),(68,'ee','Estonia'),(69,'et','Ethiopia'),(70,'fk','Falkland Islands (Malvinas)'),(71,'fo','Faroe Islands'),(72,'fj','Fiji'),(73,'fi','Finland'),(74,'fr','France'),(75,'gf','French Guiana'),(76,'pf','French Polynesia'),(77,'tf','French Southern Territories'),(78,'ga','Gabon'),(79,'gm','Gambia'),(80,'ge','Georgia'),(81,'de','Germany'),(82,'gh','Ghana'),(83,'gi','Gibraltar'),(84,'gr','Greece'),(85,'gl',
 'Greenland'),(86,'gd','Grenada'),(87,'gp','Guadeloupe'),(88,'gu','Guam'),(89,'gt','Guatemala'),(90,'gg','Guernsey'),(91,'gn','Guinea'),(92,'gw','Guinea-Bissau'),(93,'gy','Guyana'),(94,'ht','Haiti'),(95,'hm','Heard Island and McDonald Islands'),(96,'va','Holy See (Vatican City State)'),(97,'hn','Honduras'),(98,'hk','Hong Kong'),(99,'hu','Hungary'),(100,'is','Iceland'),(101,'in','India'),(102,'id','Indonesia'),(103,'ir','Iran, Islamic Republic of'),(104,'iq','Iraq'),(105,'ie','Ireland'),(106,'im','Isle of Man'),(107,'il','Israel'),(108,'it','Italy'),(109,'jm','Jamaica'),(110,'jp','Japan'),(111,'je','Jersey'),(112,'jo','Jordan'),(113,'kz','Kazakhstan'),(114,'ke','Kenya'),(115,'ki','Kiribati'),(116,'kp','Korea, Democratic People\'s Republic of'),(117,'kr','Korea, Republic of'),(118,'kw','Kuwait'),(119,'kg','Kyrgyzstan'),(120,'la','Lao People\'s Democratic Republic'),(121,'lv','Latvia'),(122,'lb','Lebanon'),(123,'ls','Lesotho'),(124,'lr','Liberia'),(125,'ly','Libyan Arab Jamahiri
 ya'),(126,'li','Liechtenstein'),(127,'lt','Lithuania'),(128,'lu','Luxembourg'),(129,'mo','Macao'),(130,'mk','Macedonia, the former Yugoslav Republic of'),(131,'mg','Madagascar'),(132,'mw','Malawi'),(133,'my','Malaysia'),(134,'mv','Maldives'),(135,'ml','Mali'),(136,'mt','Malta'),(137,'mh','Marshall Islands'),(138,'mq','Martinique'),(139,'mr','Mauritania'),(140,'mu','Mauritius'),(141,'yt','Mayotte'),(142,'mx','Mexico'),(143,'fm','Micronesia, Federated States of'),(144,'md','Moldova'),(145,'mc','Monaco'),(146,'mn','Mongolia'),(147,'me','Montenegro'),(148,'ms','Montserrat'),(149,'ma','Morocco'),(150,'mz','Mozambique'),(151,'mm','Myanmar'),(152,'na','Namibia'),(153,'nr','Nauru'),(154,'np','Nepal'),(155,'nl','Netherlands'),(156,'an','Netherlands Antilles'),(157,'nc','New Caledonia'),(158,'nz','New Zealand'),(159,'ni','Nicaragua'),(160,'ne','Niger'),(161,'ng','Nigeria'),(162,'nu','Niue'),(163,'nf','Norfolk Island'),(164,'mp','Northern Mariana Islands'),(165,'no','Norway'),(166,'om'
 ,'Oman'),(167,'pk','Pakistan'),(168,'pw','Palau'),(169,'ps','Palestinian Territory, Occupied'),(170,'pa','Panama'),(171,'pg','Papua New Guinea'),(172,'py','Paraguay'),(173,'pe','Peru'),(174,'ph','Philippines'),(175,'pn','Pitcairn'),(176,'pl','Poland'),(177,'pt','Portugal'),(178,'pr','Puerto Rico'),(179,'qa','Qatar'),(180,'re','RACunion'),(181,'ro','Romania'),(182,'ru','Russian Federation'),(183,'rw','Rwanda'),(184,'id','Saint BarthAClemy'),(185,'sh','Saint Helena'),(186,'kn','Saint Kitts and Nevis'),(187,'lc','Saint Lucia'),(188,'id','Saint Martin (French part)'),(189,'pm','Saint Pierre and Miquelon'),(190,'vc','Saint Vincent and the Grenadines'),(191,'ws','Samoa'),(192,'sm','San Marino'),(193,'st','Sao Tome and Principe'),(194,'sa','Saudi Arabia'),(195,'sn','Senegal'),(196,'rs','Serbia'),(197,'sc','Seychelles'),(198,'sl','Sierra Leone'),(199,'sg','Singapore'),(200,'sk','Slovakia'),(201,'si','Slovenia'),(202,'sb','Solomon Islands'),(203,'so','Somalia'),(204,'za','South Afric
 a'),(205,'gs','South Georgia and the South Sandwich Islands'),(206,'es','Spain'),(207,'lk','Sri Lanka'),(208,'sd','Sudan'),(209,'sr','Suriname'),(210,'sj','Svalbard and Jan Mayen'),(211,'sz','Swaziland'),(212,'se','Sweden'),(213,'ch','Switzerland'),(214,'sy','Syrian Arab Republic'),(215,'tw','Taiwan'),(216,'tj','Tajikistan'),(217,'tz','Tanzania, United Republic of'),(218,'th','Thailand'),(219,'tl','Timor-Leste'),(220,'tg','Togo'),(221,'tk','Tokelau'),(222,'to','Tonga'),(223,'tt','Trinidad and Tobago'),(224,'tn','Tunisia'),(225,'tr','Turkey'),(226,'tm','Turkmenistan'),(227,'tc','Turks and Caicos Islands'),(228,'tv','Tuvalu'),(229,'ug','Uganda'),(230,'ua','Ukraine'),(231,'ae','United Arab Emirates'),(232,'gb','United Kingdom'),(233,'us','United States'),(234,'um','United States Minor Outlying Islands'),(235,'uy','Uruguay'),(236,'uz','Uzbekistan'),(237,'vu','Vanuatu'),(238,'ve','Venezuela'),(239,'vn','Viet Nam'),(240,'vg','Virgin Islands, British'),(241,'vi','Virgin Islands, U.
 S.'),(242,'wf','Wallis and Futuna'),(243,'eh','Western Sahara'),(244,'ye','Yemen'),(245,'zm','Zambia'),(246,'zw','Zimbabwe');
+

Added: trunk/tools/download-redirector-v2/sql/initialdata-postgresql.sql
===================================================================
--- trunk/tools/download-redirector-v2/sql/initialdata-postgresql.sql	                        (rev 0)
+++ trunk/tools/download-redirector-v2/sql/initialdata-postgresql.sql	2009-02-11 20:09:35 UTC (rev 6436)
@@ -0,0 +1,4 @@
+INSERT INTO region VALUES (1,'af','Africa'),(2,'as','Asia'),(3,'eu','Europe'),(4,'na','North America'),(5,'sa','South America'),(6,'oc','Oceania');
+
+INSERT INTO country VALUES (1,'af','Afghanistan'),(2,'ax','A■land Islands'),(3,'al','Albania'),(4,'dz','Algeria'),(5,'as','American Samoa'),(6,'ad','Andorra'),(7,'ao','Angola'),(8,'ai','Anguilla'),(9,'aq','Antarctica'),(10,'ag','Antigua and Barbuda'),(11,'ar','Argentina'),(12,'am','Armenia'),(13,'aw','Aruba'),(14,'au','Australia'),(15,'at','Austria'),(16,'az','Azerbaijan'),(17,'bs','Bahamas'),(18,'bh','Bahrain'),(19,'bd','Bangladesh'),(20,'bb','Barbados'),(21,'by','Belarus'),(22,'be','Belgium'),(23,'bz','Belize'),(24,'bj','Benin'),(25,'bm','Bermuda'),(26,'bt','Bhutan'),(27,'bo','Bolivia'),(28,'ba','Bosnia and Herzegovina'),(29,'bw','Botswana'),(30,'bv','Bouvet Island'),(31,'br','Brazil'),(32,'io','British Indian Ocean Territory'),(33,'bn','Brunei Darussalam'),(34,'bg','Bulgaria'),(35,'bf','Burkina Faso'),(36,'bi','Burundi'),(37,'kh','Cambodia'),(38,'cm','Cameroon'),(39,'ca','Canada'),(40,'cv','Cape Verde'),(41,'ky','Cayman Islands'),(42,'cf','Central African Republic'),(43
 ,'td','Chad'),(44,'cl','Chile'),(45,'cn','China'),(46,'cx','Christmas Island'),(47,'cc','Cocos (Keeling) Islands'),(48,'co','Colombia'),(49,'km','Comoros'),(50,'cg','Congo'),(51,'cd','Congo, Democratic Republic of the'),(52,'ck','Cook Islands'),(53,'cr','Costa Rica'),(54,'ci','CA■te d''Ivoire'),(55,'hr','Croatia'),(56,'cu','Cuba'),(57,'cy','Cyprus'),(58,'cz','Czech Republic'),(59,'dk','Denmark'),(60,'dj','Djibouti'),(61,'dm','Dominica'),(62,'do','Dominican Republic'),(63,'ec','Ecuador'),(64,'eg','Egypt'),(65,'sv','El Salvador'),(66,'gq','Equatorial Guinea'),(67,'er','Eritrea'),(68,'ee','Estonia'),(69,'et','Ethiopia'),(70,'fk','Falkland Islands (Malvinas)'),(71,'fo','Faroe Islands'),(72,'fj','Fiji'),(73,'fi','Finland'),(74,'fr','France'),(75,'gf','French Guiana'),(76,'pf','French Polynesia'),(77,'tf','French Southern Territories'),(78,'ga','Gabon'),(79,'gm','Gambia'),(80,'ge','Georgia'),(81,'de','Germany'),(82,'gh','Ghana'),(83,'gi','Gibraltar'),(84,'gr','Greece'),(85,'gl',
 'Greenland'),(86,'gd','Grenada'),(87,'gp','Guadeloupe'),(88,'gu','Guam'),(89,'gt','Guatemala'),(90,'gg','Guernsey'),(91,'gn','Guinea'),(92,'gw','Guinea-Bissau'),(93,'gy','Guyana'),(94,'ht','Haiti'),(95,'hm','Heard Island and McDonald Islands'),(96,'va','Holy See (Vatican City State)'),(97,'hn','Honduras'),(98,'hk','Hong Kong'),(99,'hu','Hungary'),(100,'is','Iceland'),(101,'in','India'),(102,'id','Indonesia'),(103,'ir','Iran, Islamic Republic of'),(104,'iq','Iraq'),(105,'ie','Ireland'),(106,'im','Isle of Man'),(107,'il','Israel'),(108,'it','Italy'),(109,'jm','Jamaica'),(110,'jp','Japan'),(111,'je','Jersey'),(112,'jo','Jordan'),(113,'kz','Kazakhstan'),(114,'ke','Kenya'),(115,'ki','Kiribati'),(116,'kp','Korea, Democratic People''s Republic of'),(117,'kr','Korea, Republic of'),(118,'kw','Kuwait'),(119,'kg','Kyrgyzstan'),(120,'la','Lao People''s Democratic Republic'),(121,'lv','Latvia'),(122,'lb','Lebanon'),(123,'ls','Lesotho'),(124,'lr','Liberia'),(125,'ly','Libyan Arab Jamahiri
 ya'),(126,'li','Liechtenstein'),(127,'lt','Lithuania'),(128,'lu','Luxembourg'),(129,'mo','Macao'),(130,'mk','Macedonia, the former Yugoslav Republic of'),(131,'mg','Madagascar'),(132,'mw','Malawi'),(133,'my','Malaysia'),(134,'mv','Maldives'),(135,'ml','Mali'),(136,'mt','Malta'),(137,'mh','Marshall Islands'),(138,'mq','Martinique'),(139,'mr','Mauritania'),(140,'mu','Mauritius'),(141,'yt','Mayotte'),(142,'mx','Mexico'),(143,'fm','Micronesia, Federated States of'),(144,'md','Moldova'),(145,'mc','Monaco'),(146,'mn','Mongolia'),(147,'me','Montenegro'),(148,'ms','Montserrat'),(149,'ma','Morocco'),(150,'mz','Mozambique'),(151,'mm','Myanmar'),(152,'na','Namibia'),(153,'nr','Nauru'),(154,'np','Nepal'),(155,'nl','Netherlands'),(156,'an','Netherlands Antilles'),(157,'nc','New Caledonia'),(158,'nz','New Zealand'),(159,'ni','Nicaragua'),(160,'ne','Niger'),(161,'ng','Nigeria'),(162,'nu','Niue'),(163,'nf','Norfolk Island'),(164,'mp','Northern Mariana Islands'),(165,'no','Norway'),(166,'om'
 ,'Oman'),(167,'pk','Pakistan'),(168,'pw','Palau'),(169,'ps','Palestinian Territory, Occupied'),(170,'pa','Panama'),(171,'pg','Papua New Guinea'),(172,'py','Paraguay'),(173,'pe','Peru'),(174,'ph','Philippines'),(175,'pn','Pitcairn'),(176,'pl','Poland'),(177,'pt','Portugal'),(178,'pr','Puerto Rico'),(179,'qa','Qatar'),(180,'re','RACunion'),(181,'ro','Romania'),(182,'ru','Russian Federation'),(183,'rw','Rwanda'),(184,'id','Saint BarthAClemy'),(185,'sh','Saint Helena'),(186,'kn','Saint Kitts and Nevis'),(187,'lc','Saint Lucia'),(188,'id','Saint Martin (French part)'),(189,'pm','Saint Pierre and Miquelon'),(190,'vc','Saint Vincent and the Grenadines'),(191,'ws','Samoa'),(192,'sm','San Marino'),(193,'st','Sao Tome and Principe'),(194,'sa','Saudi Arabia'),(195,'sn','Senegal'),(196,'rs','Serbia'),(197,'sc','Seychelles'),(198,'sl','Sierra Leone'),(199,'sg','Singapore'),(200,'sk','Slovakia'),(201,'si','Slovenia'),(202,'sb','Solomon Islands'),(203,'so','Somalia'),(204,'za','South Afric
 a'),(205,'gs','South Georgia and the South Sandwich Islands'),(206,'es','Spain'),(207,'lk','Sri Lanka'),(208,'sd','Sudan'),(209,'sr','Suriname'),(210,'sj','Svalbard and Jan Mayen'),(211,'sz','Swaziland'),(212,'se','Sweden'),(213,'ch','Switzerland'),(214,'sy','Syrian Arab Republic'),(215,'tw','Taiwan'),(216,'tj','Tajikistan'),(217,'tz','Tanzania, United Republic of'),(218,'th','Thailand'),(219,'tl','Timor-Leste'),(220,'tg','Togo'),(221,'tk','Tokelau'),(222,'to','Tonga'),(223,'tt','Trinidad and Tobago'),(224,'tn','Tunisia'),(225,'tr','Turkey'),(226,'tm','Turkmenistan'),(227,'tc','Turks and Caicos Islands'),(228,'tv','Tuvalu'),(229,'ug','Uganda'),(230,'ua','Ukraine'),(231,'ae','United Arab Emirates'),(232,'gb','United Kingdom'),(233,'us','United States'),(234,'um','United States Minor Outlying Islands'),(235,'uy','Uruguay'),(236,'uz','Uzbekistan'),(237,'vu','Vanuatu'),(238,'ve','Venezuela'),(239,'vn','Viet Nam'),(240,'vg','Virgin Islands, British'),(241,'vi','Virgin Islands, U.
 S.'),(242,'wf','Wallis and Futuna'),(243,'eh','Western Sahara'),(244,'ye','Yemen'),(245,'zm','Zambia'),(246,'zw','Zimbabwe');
+

Deleted: trunk/tools/download-redirector-v2/sql/mirrorbrain-mysql.sql

Deleted: trunk/tools/download-redirector-v2/sql/mirrorbrain-postgresql.sql

Copied: trunk/tools/download-redirector-v2/sql/schema-mysql.sql (from rev 6429, trunk/tools/download-redirector-v2/sql/mirrorbrain-mysql.sql)
===================================================================
--- trunk/tools/download-redirector-v2/sql/schema-mysql.sql	                        (rev 0)
+++ trunk/tools/download-redirector-v2/sql/schema-mysql.sql	2009-02-11 20:09:35 UTC (rev 6436)
@@ -0,0 +1,89 @@
+
+-- 
+-- MirrorBrain Database scheme for MySQL
+-- 
+
+-- --------------------------------------------------------
+
+CREATE TABLE `file` (
+  `id` int(11) unsigned NOT NULL auto_increment,
+  `path` varchar(512) NOT NULL,
+  PRIMARY KEY  (`id`),
+  KEY `file_path_idx` (`path`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 ;
+
+-- --------------------------------------------------------
+
+CREATE TABLE `file_server` (
+  `fileid` int(11) unsigned NOT NULL default '0',
+  `serverid` int(11) unsigned NOT NULL default '0',
+-- timestamp_file: the default and update trigger are not used
+  `timestamp_file` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
+  `timestamp_scanner` timestamp NOT NULL default '0000-00-00 00:00:00',
+  KEY `file_server_fileid_idx` (`fileid`),
+  KEY `file_server_serverid_idx` (`serverid`),
+  KEY `file_server_fileid_serverid_idx` (`fileid`,`serverid`),
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 ;
+
+-- --------------------------------------------------------
+
+CREATE TABLE `server` (
+  `id` int(11) unsigned NOT NULL auto_increment,
+  `identifier` varchar(64) NOT NULL,
+  `baseurl` varchar(128) NOT NULL,
+  `baseurl_ftp` varchar(128) NOT NULL,
+-- inconsistently, baseurl_rsync can be null - we should get rid of this.
+  `baseurl_rsync` varchar(128) default NULL,
+  `enabled` tinyint(1) NOT NULL default '0',
+  `status_baseurl` tinyint(1) NOT NULL default '0',
+-- region should be altered to char(2) - as we use it
+  `region` varchar(10) default NULL,
+  `country` char(2) default NULL,
+  `asn` int(11) default NULL,
+  `prefix` char(18) default NULL,
+  `score` int(11) default NULL,
+  `scan_fpm` int(11) default NULL,
+  `last_scan` timestamp NULL default NULL,
+  `comment` text,
+  `admin` text,
+  `admin_email` text,
+  `operator_name` text NOT NULL,
+  `operator_url` text NOT NULL,
+  `public_notes` varchar(512) NOT NULL default '',
+  `lat` float default NULL,
+  `lng` float default NULL,
+  `country_only` tinyint(1) default '0',
+  `region_only` tinyint(1) default '0',
+  `as_only` tinyint(1) default '0',
+  `prefix_only` tinyint(1) default '0',
+  `other_countries` text NOT NULL,
+  `file_maxsize` int(11) NOT NULL default '0',
+  PRIMARY KEY  (`id`),
+  UNIQUE KEY `identifier` (`identifier`),
+  KEY `server_enabled_status_baseurl_score_idx` (`enabled`,`status_baseurl`,`score`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
+
+CREATE TABLE `marker` (
+  `id` int(11) unsigned NOT NULL auto_increment,
+  `subtree_name` varchar(128) NOT NULL,
+  `markers` varchar(512) NOT NULL,
+  PRIMARY KEY  (`id`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
+
+CREATE TABLE `country` (
+  `id` tinyint(1) NOT NULL auto_increment,
+  `code` char(2) NOT NULL,
+  `name` char(64) NOT NULL,
+  PRIMARY KEY  (`id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+
+CREATE TABLE `region` (
+  `id` int(2) NOT NULL auto_increment,
+  `code` char(2) NOT NULL,
+  `name` char(64) NOT NULL,
+  PRIMARY KEY  (`id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 | 
+

Copied: trunk/tools/download-redirector-v2/sql/schema-postgresql.sql (from rev 6429, trunk/tools/download-redirector-v2/sql/mirrorbrain-postgresql.sql)
===================================================================
--- trunk/tools/download-redirector-v2/sql/schema-postgresql.sql	                        (rev 0)
+++ trunk/tools/download-redirector-v2/sql/schema-postgresql.sql	2009-02-11 20:09:35 UTC (rev 6436)
@@ -0,0 +1,125 @@
+
+-- 
+-- MirrorBrain Database scheme for PostgreSQL
+-- 
+
+-- --------------------------------------------------------
+BEGIN;
+-- --------------------------------------------------------
+
+
+CREATE TABLE "file" (
+        "id" serial NOT NULL PRIMARY KEY,
+        "path" varchar(512) NOT NULL
+);
+
+CREATE INDEX "file_path_key" ON "file" ("path");
+
+-- --------------------------------------------------------
+
+CREATE TABLE "server" (
+        "id" serial NOT NULL PRIMARY KEY,
+        "identifier" varchar(64) NOT NULL UNIQUE,
+        "baseurl"       varchar(128) NOT NULL,
+        "baseurl_ftp"   varchar(128) NOT NULL,
+        "baseurl_rsync" varchar(128) NOT NULL,
+        "enabled"        boolean NOT NULL,
+        "status_baseurl" boolean NOT NULL,
+        "region"  varchar(2) NOT NULL,
+        "country" varchar(2) NOT NULL,
+        "asn" integer NOT NULL,
+        "prefix" varchar(18) NOT NULL,
+        "score" smallint NOT NULL,
+        "scan_fpm" integer NOT NULL,
+        "last_scan" timestamp with time zone NULL,
+        "comment" text NOT NULL,
+        "operator_name" varchar(128) NOT NULL,
+        "operator_url" varchar(128) NOT NULL,
+        "public_notes" varchar(512) NOT NULL,
+        "admin"       varchar(128) NOT NULL,
+        "admin_email" varchar(128) NOT NULL,
+        "lat" numeric(6, 3) NULL,
+        "lng" numeric(6, 3) NULL,
+        "country_only" boolean NOT NULL,
+        "region_only" boolean NOT NULL,
+        "as_only" boolean NOT NULL,
+        "prefix_only" boolean NOT NULL,
+        "other_countries" varchar(512) NOT NULL,
+        "file_maxsize" integer NOT NULL default 0
+);
+
+CREATE INDEX "server_enabled_status_baseurl_score_key" ON "server" (
+        "enabled", "status_baseurl", "score"
+);
+
+-- --------------------------------------------------------
+
+CREATE TABLE "file_server" (
+        -- can we just omit the id column?
+        -- no, sqlobject needs a primary key.
+        "id" serial NOT NULL PRIMARY KEY,
+        "serverid" integer NOT NULL REFERENCES "server" ("id") DEFERRABLE INITIALLY DEFERRED,
+        "fileid" integer NOT NULL REFERENCES "file" ("id") DEFERRABLE INITIALLY DEFERRED,
+        "timestamp_file" timestamp with time zone NULL,
+        "timestamp_scanner" timestamp with time zone NULL,
+        UNIQUE ("fileid", "serverid")
+);
+
+-- indexes that are created with "id" column:
+-- NOTICE:  CREATE TABLE will create implicit sequence "file_server_id_seq" for serial column "file_server.id"
+-- NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "file_server_pkey" for table "file_server"
+-- NOTICE:  CREATE TABLE / UNIQUE will create implicit index "file_server_fileid_key" for table "file_server"
+-- CREATE TABLE
+
+-- that are created when not using the "id" column:
+-- NOTICE:  CREATE TABLE / UNIQUE will create implicit index "file_server_fileid_key" for table "file_server"
+-- CREATE TABLE
+
+CREATE INDEX "file_server_serverid_key" ON "file_server" ("serverid");
+CREATE INDEX "file_server_fileid_serverid_key" ON "file_server" ("fileid", "serverid");
+
+-- --------------------------------------------------------
+
+CREATE TABLE "marker" (
+        "id" serial NOT NULL PRIMARY KEY,
+        "subtree_name" varchar(128) NOT NULL,
+        "markers" varchar(512) NOT NULL
+);
+
+-- --------------------------------------------------------
+
+CREATE TABLE "country" (
+        "id" serial NOT NULL PRIMARY KEY,
+        "code" varchar(2) NOT NULL,
+        "name" varchar(64) NOT NULL
+);
+
+CREATE TABLE "region" (
+    "id" serial NOT NULL PRIMARY KEY,
+    "code" varchar(2) NOT NULL,
+    "name" varchar(64) NOT NULL
+);
+
+
+
+-- --------------------------------------------------------
+
+--
+-- from_unixtime
+--
+-- Takes a seconds-since-the-epoch integer and returns a timestamp
+--
+-- => select from_unixtime('1233609530');
+--     from_unixtime    
+-- ---------------------
+--  2009-02-02 22:18:50
+-- (1 row)
+
+CREATE OR REPLACE FUNCTION from_unixtime(integer) RETURNS timestamp AS '
+        SELECT $1::abstime::timestamp without time zone AS result
+' LANGUAGE 'SQL';
+
+-- --------------------------------------------------------
+COMMIT;
+-- --------------------------------------------------------
+

_______________________________________________
Opensuse-svn mailing list
Opensuse-svn_at_forge.novell.com
http://forge.novell.com/mailman/listinfo/opensuse-svn


_______________________________________________
mirrorbrain-commits mailing list

Note: To remove yourself from this list, send a mail with the content
 	unsubscribe
to the address mirrorbrain-commits-request_at_mirrorbrain.org
Received on 2009-02-11Z20:10:15

This archive was generated by hypermail 2.2.0 : 2009-07-10Z19:18:11 GMT