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

Author: poeml
Date: 2009-02-02 17:59:36 -0700 (Mon, 02 Feb 2009)
New Revision: 6327

Added:
   trunk/tools/download-redirector-v2/sql/mirrorbrain-postgresql.sql
Log:
MirrorBrain: Add a database scheme for PostgreSQL


Added: trunk/tools/download-redirector-v2/sql/mirrorbrain-postgresql.sql
===================================================================
--- trunk/tools/download-redirector-v2/sql/mirrorbrain-postgresql.sql	                        (rev 0)
+++ trunk/tools/download-redirector-v2/sql/mirrorbrain-postgresql.sql	2009-02-03 00:59:36 UTC (rev 6327)
@@ -0,0 +1,122 @@
+
+-- 
+-- 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,
+        "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,
+        "netblock" text,
+        "lat" numeric(6, 3) NULL,
+        "lng" numeric(6, 3) NULL,
+        "country_only" boolean NOT NULL,
+        "region_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