summaryrefslogtreecommitdiff
path: root/docs
diff options
context:
space:
mode:
authorAleksander Machniak <machniak@kolabsys.com>2014-09-15 10:28:09 (GMT)
committerAleksander Machniak <machniak@kolabsys.com>2014-09-15 10:28:09 (GMT)
commit092232c129fc7c9743057d169f6178ebb3d99871 (patch)
tree4ed082ddfeaa13b3d971a7aa973faafd96d0fbb1 /docs
parent33a41c4a338df09dd9480fca2dbdf2b47d0f6b0f (diff)
downloadkolab-syncroton-092232c129fc7c9743057d169f6178ebb3d99871.tar.gz
Initial DDL for Oracle
Diffstat (limited to 'docs')
-rw-r--r--docs/SQL/oracle.initial.sql104
1 files changed, 104 insertions, 0 deletions
diff --git a/docs/SQL/oracle.initial.sql b/docs/SQL/oracle.initial.sql
new file mode 100644
index 0000000..cb78340
--- /dev/null
+++ b/docs/SQL/oracle.initial.sql
@@ -0,0 +1,104 @@
+CREATE TABLE "syncroton_policy" (
+ "id" varchar(40) NOT NULL PRIMARY KEY,
+ "name" varchar(255) NOT NULL,
+ "description" varchar(255) DEFAULT NULL,
+ "policy_key" varchar(64) NOT NULL,
+ "json_policy" clob DEFAULT NULL
+);
+
+CREATE TABLE "syncroton_device" (
+ "id" varchar(40) NOT NULL PRIMARY KEY,
+ "deviceid" varchar(64) NOT NULL,
+ "devicetype" varchar(64) NOT NULL,
+ "owner_id" varchar(40) NOT NULL,
+ "acsversion" varchar(40) NOT NULL,
+ "policykey" varchar(64) DEFAULT NULL,
+ "policy_id" varchar(40) DEFAULT NULL,
+ "useragent" varchar(255) DEFAULT NULL,
+ "imei" varchar(255) DEFAULT NULL,
+ "model" varchar(255) DEFAULT NULL,
+ "friendlyname" varchar(255) DEFAULT NULL,
+ "os" varchar(255) DEFAULT NULL,
+ "oslanguage" varchar(255) DEFAULT NULL,
+ "phonenumber" varchar(255) DEFAULT NULL,
+ "pinglifetime" integer DEFAULT NULL,
+ "remotewipe" integer DEFAULT 0,
+ "pingfolder" clob DEFAULT NULL,
+ "lastsynccollection" clob DEFAULT NULL,
+ "lastping" timestamp DEFAULT NULL,
+ "contactsfilter_id" varchar(40) DEFAULT NULL,
+ "calendarfilter_id" varchar(40) DEFAULT NULL,
+ "tasksfilter_id" varchar(40) DEFAULT NULL,
+ "emailfilter_id" varchar(40) DEFAULT NULL
+);
+
+CREATE UNIQUE INDEX "syncroton_device_owner_id_idx" ON "syncroton_device" ("owner_id", "deviceid");
+
+
+CREATE TABLE "syncroton_folder" (
+ "id" varchar(40) NOT NULL PRIMARY KEY,
+ "device_id" varchar(40) NOT NULL
+ REFERENCES "syncroton_device" ("id") ON DELETE CASCADE,
+ "class" varchar(64) NOT NULL,
+ "folderid" varchar(254) NOT NULL,
+ "parentid" varchar(254) DEFAULT NULL,
+ "displayname" varchar(254) NOT NULL,
+ "type" integer NOT NULL,
+ "creation_time" timestamp NOT NULL,
+ "lastfiltertype" integer DEFAULT NULL,
+ "supportedfields" clob DEFAULT NULL
+);
+
+CREATE UNIQUE INDEX "syncroton_folder_device_id_idx" ON "syncroton_folder" ("device_id", "class", "folderid");
+
+
+CREATE TABLE "syncroton_synckey" (
+ "id" varchar(40) NOT NULL PRIMARY KEY,
+ "device_id" varchar(40) NOT NULL
+ REFERENCES "syncroton_device" ("id") ON DELETE CASCADE,
+ "type" varchar(64) DEFAULT NULL,
+ "counter" integer DEFAULT 0 NOT NULL,
+ "lastsync" timestamp DEFAULT NULL,
+ "pendingdata" clob
+);
+
+CREATE UNIQUE INDEX "syncroton_synckey_device_idx" ON "syncroton_synckey" ("device_id", "type", "counter");
+
+CREATE TABLE "syncroton_content" (
+ "id" varchar(40) NOT NULL PRIMARY KEY,
+ "device_id" varchar(40) NOT NULL
+ REFERENCES "syncroton_device" ("id") ON DELETE CASCADE,
+ "folder_id" varchar(40) NOT NULL,
+ "contentid" varchar(128) NOT NULL,
+ "creation_time" timestamp DEFAULT NULL,
+ "creation_synckey" integer NOT NULL,
+ "is_deleted" smallint DEFAULT 0
+);
+
+CREATE UNIQUE INDEX "syncroton_content_device_idx" ON "syncroton_content" ("device_id", "folder_id", "contentid");
+
+CREATE TABLE "syncroton_data" (
+ "id" varchar(40) NOT NULL PRIMARY KEY,
+ "class" varchar(40) NOT NULL,
+ "folder_id" varchar(40) NOT NULL,
+ "data" clob
+);
+
+CREATE TABLE "syncroton_data_folder" (
+ "id" varchar(40) NOT NULL PRIMARY KEY,
+ "type" integer NOT NULL,
+ "name" varchar(255) NOT NULL,
+ "owner_id" varchar(40) NOT NULL,
+ "parent_id" varchar(40) DEFAULT NULL
+);
+
+CREATE TABLE "syncroton_modseq" (
+ "device_id" varchar(40) NOT NULL
+ REFERENCES "syncroton_device" ("id") ON DELETE CASCADE,
+ "folder_id" varchar(40) NOT NULL,
+ "synctime" timestamp NOT NULL,
+ "data" clob,
+ PRIMARY KEY ("device_id", "folder_id", "synctime")
+);
+
+INSERT INTO "system" ("name", "value") VALUES ('syncroton-version', '2013100800');