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) _at_@ -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 _at_@ -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 _at_@ -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) _at_@ -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) _at_@ -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) _at_@ -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) _at_@ -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.orgReceived on Wed Feb 11 2009 - 20:10:15 GMT
This archive was generated by hypermail 2.3.0 : Mon Feb 20 2012 - 23:47:04 GMT