-
Notifications
You must be signed in to change notification settings - Fork 0
/
owncloud_infosystem.sql
97 lines (97 loc) · 5.05 KB
/
owncloud_infosystem.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
CREATE view infos as select id, summary as name, '...' || SUBSTR(calendardata,290,240) || '...' as text, 1 as zone, startdate as dateAdded, startdate as date, 0 as votes, 0 as rating, Null as exp_action, lastmodified as lastModified, enddate as expiration from oc_clndr_objects;
CREATE TABLE category_alias (id integer PRIMARY KEY, category_id integer, name string, Unique (name));
CREATE TABLE item_ref(id integer PRIMARY KEY,item1_id integer, item1_type_id integer, item2_id integer, item2_type_id integer, unique (item1_id, item1_type_id, item2_id, item2_type_id));
CREATE TABLE links(id integer PRIMARY KEY,name text,mediatype text,source text,mirror text,dateAdded datetime,zone integer default 3, pid integer, fid integer, votes integer default 0 not null, rating integer default 0 not null, unique (source));
CREATE TABLE zone_bez (id integer PRIMARY KEY, zone integer, bez string, Unique (zone));
CREATE TABLE zones (id integer PRIMARY KEY, zones integer, zone integer, Unique (zones,zone));
CREATE TABLE category_item(id integer PRIMARY KEY,category_id integer not null,item_id integer not null,item_type_id integer not null,UNIQUE (category_id,item_id,item_type_id));
CREATE TABLE history(item_id integer, item_type_id integer, search_term text, categories text, ex_categories text, zones integer, timestamp datetime, command text, parameters text, output text);
CREATE TABLE category(id integer PRIMARY KEY,name text,parent integer, zone integer,UNIQUE (name));
CREATE TABLE files(id integer PRIMARY KEY,name text,mediatype text,disksource text,inode integer,bdisksource text,binode integer,source text,zone integer default 3, votes integer default 0 not null, rating integer default 0 not null, UNIQUE (disksource,inode));
CREATE VIEW category_link as select category_id, item_id from category_item where item_type_id=2;
CREATE VIEW category_info as select category_id, item_id from category_item where item_type_id=1;
CREATE VIEW v_infos as select * from infos;
CREATE VIEW category_file as select category_id, item_id from category_item where item_type_id=3;
CREATE VIEW v_links as select *,dateAdded as date from links;
CREATE VIEW v_files as select *,Null as dateAdded,Null as date from files;
INSERT INTO "zones" VALUES(1,1,1);
INSERT INTO "zones" VALUES(2,1,3);
INSERT INTO "zones" VALUES(3,1,8);
INSERT INTO "zones" VALUES(4,1,9);
INSERT INTO "zones" VALUES(5,1,11);
INSERT INTO "zones" VALUES(6,1,13);
INSERT INTO "zones" VALUES(7,1,15);
INSERT INTO "zones" VALUES(8,2,2);
INSERT INTO "zones" VALUES(9,2,3);
INSERT INTO "zones" VALUES(10,2,10);
INSERT INTO "zones" VALUES(11,2,11);
INSERT INTO "zones" VALUES(12,2,14);
INSERT INTO "zones" VALUES(13,2,15);
INSERT INTO "zones" VALUES(14,3,1);
INSERT INTO "zones" VALUES(15,3,2);
INSERT INTO "zones" VALUES(16,3,3);
INSERT INTO "zones" VALUES(17,3,7);
INSERT INTO "zones" VALUES(18,3,8);
INSERT INTO "zones" VALUES(19,3,9);
INSERT INTO "zones" VALUES(20,3,10);
INSERT INTO "zones" VALUES(21,3,11);
INSERT INTO "zones" VALUES(22,3,13);
INSERT INTO "zones" VALUES(23,3,14);
INSERT INTO "zones" VALUES(24,3,15);
INSERT INTO "zones" VALUES(25,4,1);
INSERT INTO "zones" VALUES(26,4,3);
INSERT INTO "zones" VALUES(27,4,4);
INSERT INTO "zones" VALUES(28,4,5);
INSERT INTO "zones" VALUES(29,4,7);
INSERT INTO "zones" VALUES(30,5,1);
INSERT INTO "zones" VALUES(31,5,3);
INSERT INTO "zones" VALUES(32,5,5);
INSERT INTO "zones" VALUES(33,5,7);
INSERT INTO "zones" VALUES(34,5,8);
INSERT INTO "zones" VALUES(35,5,9);
INSERT INTO "zones" VALUES(36,5,11);
INSERT INTO "zones" VALUES(37,5,13);
INSERT INTO "zones" VALUES(38,5,15);
INSERT INTO "zones" VALUES(39,6,2);
INSERT INTO "zones" VALUES(40,6,6);
INSERT INTO "zones" VALUES(41,6,7);
INSERT INTO "zones" VALUES(42,7,1);
INSERT INTO "zones" VALUES(43,7,2);
INSERT INTO "zones" VALUES(44,7,3);
INSERT INTO "zones" VALUES(45,7,5);
INSERT INTO "zones" VALUES(46,7,6);
INSERT INTO "zones" VALUES(47,7,7);
INSERT INTO "zones" VALUES(48,7,8);
INSERT INTO "zones" VALUES(49,7,9);
INSERT INTO "zones" VALUES(50,7,10);
INSERT INTO "zones" VALUES(51,7,11);
INSERT INTO "zones" VALUES(52,7,12);
INSERT INTO "zones" VALUES(53,7,13);
INSERT INTO "zones" VALUES(54,7,14);
INSERT INTO "zones" VALUES(55,7,15);
INSERT INTO "zones" VALUES(56,8,8);
INSERT INTO "zones" VALUES(57,9,8);
INSERT INTO "zones" VALUES(58,9,9);
INSERT INTO "zones" VALUES(59,9,11);
INSERT INTO "zones" VALUES(60,10,8);
INSERT INTO "zones" VALUES(61,10,10);
INSERT INTO "zones" VALUES(62,10,11);
INSERT INTO "zones" VALUES(63,12,8);
INSERT INTO "zones" VALUES(64,12,12);
INSERT INTO "zones" VALUES(65,13,8);
INSERT INTO "zones" VALUES(66,13,9);
INSERT INTO "zones" VALUES(67,13,11);
INSERT INTO "zones" VALUES(68,13,13);
INSERT INTO "zones" VALUES(69,13,15);
INSERT INTO "zones" VALUES(70,14,8);
INSERT INTO "zones" VALUES(71,14,10);
INSERT INTO "zones" VALUES(72,14,11);
INSERT INTO "zones" VALUES(73,14,14);
INSERT INTO "zones" VALUES(74,14,15);
INSERT INTO "zones" VALUES(75,15,8);
INSERT INTO "zones" VALUES(76,15,9);
INSERT INTO "zones" VALUES(77,15,10);
INSERT INTO "zones" VALUES(78,15,11);
INSERT INTO "zones" VALUES(79,15,13);
INSERT INTO "zones" VALUES(80,15,14);
INSERT INTO "zones" VALUES(81,15,15);