[mirrorbrain-commits] [opensuse-svn] r6522 - trunk/tools/download-redirector-v2/sql

Author: poeml
Date: 2009-02-21 08:03:04 -0700 (Sat, 21 Feb 2009)
New Revision: 6522

Modified:
   trunk/tools/download-redirector-v2/sql/schema-mysql.sql
   trunk/tools/download-redirector-v2/sql/schema-postgresql.sql
Log:
MirrorBrain SQL schema: remove timestamp_file column (never used); remove primary key from file_server table, which wastes massive space and is used only by ORMs

Modified: trunk/tools/download-redirector-v2/sql/schema-mysql.sql
===================================================================
--- trunk/tools/download-redirector-v2/sql/schema-mysql.sql	2009-02-20 21:06:50 UTC (rev 6521)
+++ trunk/tools/download-redirector-v2/sql/schema-mysql.sql	2009-02-21 15:03:04 UTC (rev 6522)
@@ -17,8 +17,9 @@
 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,
+-- we never used the timestamp_file column
+-- the default and update trigger were not used either
+-- `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`),

Modified: trunk/tools/download-redirector-v2/sql/schema-postgresql.sql
===================================================================
--- trunk/tools/download-redirector-v2/sql/schema-postgresql.sql	2009-02-20 21:06:50 UTC (rev 6521)
+++ trunk/tools/download-redirector-v2/sql/schema-postgresql.sql	2009-02-21 15:03:04 UTC (rev 6522)
@@ -55,29 +55,30 @@
 -- --------------------------------------------------------
 
 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,
+	-- we actually never used the timestamp_file column.
+        -- "timestamp_file" timestamp with time zone NULL,
+        -- and the next one should be a unix epoch, which needs only 4 bytes instead of 8:
         "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_fileid_key" ON "file_server" ("serverid", "fileid");
 
-CREATE INDEX "file_server_serverid_key" ON "file_server" ("serverid");
-CREATE INDEX "file_server_fileid_serverid_key" ON "file_server" ("fileid", "serverid");
 
+-- For ORM's that require a primary key named 'id'; this way we don't need to
+-- actually store it and have an index for it.
+-- the index alone needs 800MB for 1.000.000 files
+CREATE VIEW file_server_withpk AS 
+        SELECT '1' 
+	|| LPAD(CAST(fileid AS TEXT), 12, '0') 
+	|| LPAD(CAST(serverid AS TEXT), 12, '0') 
+	AS id, serverid, fileid, timestamp_scanner 
+	FROM file_server;
+
 -- --------------------------------------------------------
 
 CREATE TABLE "marker" (

_______________________________________________
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