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
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
|
DROP TABLE IF EXISTS dc_blog;
CREATE TABLE "dc_blog"
(
blog_id VARCHAR(32) NOT NULL ,
blog_uid VARCHAR(32) NOT NULL ,
blog_creadt TIMESTAMP NOT NULL DEFAULT '1970-01-01 00:00:00' ,
blog_upddt TIMESTAMP NOT NULL DEFAULT '1970-01-01 00:00:00' ,
blog_url VARCHAR(255) NOT NULL ,
blog_name VARCHAR(255) NOT NULL ,
blog_desc TEXT NULL ,
blog_status INTEGER NOT NULL DEFAULT 1,
CONSTRAINT pk_blog PRIMARY KEY (blog_id)
);
DROP TABLE IF EXISTS dc_category;
CREATE TABLE "dc_category"
(
cat_id INTEGER NOT NULL ,
blog_id VARCHAR(32) NOT NULL ,
cat_title VARCHAR(255) NOT NULL ,
cat_url VARCHAR(255) NOT NULL ,
cat_desc TEXT NULL ,
cat_position INTEGER NULL DEFAULT 0 ,
cat_lft INTEGER NULL ,
cat_rgt INTEGER NULL ,
CONSTRAINT pk_category PRIMARY KEY (cat_id)
CONSTRAINT uk_cat_url UNIQUE (cat_url,blog_id)
);
DROP TABLE IF EXISTS dc_session;
CREATE TABLE "dc_session"
(
ses_id VARCHAR(40) NOT NULL ,
ses_time INTEGER NOT NULL DEFAULT 0 ,
ses_start INTEGER NOT NULL DEFAULT 0 ,
ses_value TEXT NOT NULL ,
CONSTRAINT pk_session PRIMARY KEY (ses_id)
);
DROP TABLE IF EXISTS dc_setting;
CREATE TABLE "dc_setting"
(
setting_id VARCHAR(255) NOT NULL ,
blog_id VARCHAR(32) NULL ,
setting_ns VARCHAR(32) NOT NULL DEFAULT 'system' ,
setting_value TEXT NULL DEFAULT NULL,
setting_type VARCHAR(8) NOT NULL DEFAULT 'string' ,
setting_label TEXT NULL ,
CONSTRAINT uk_setting UNIQUE (setting_ns,setting_id,blog_id)
);
DROP TABLE IF EXISTS dc_spamrule;
CREATE TABLE dc_spamrule (
rule_id integer NOT NULL ,
blog_id varchar(32) NULL ,
rule_type varchar(16) NOT NULL DEFAULT 'word' ,
rule_content varchar(128) NOT NULL ,
CONSTRAINT dc_pk_spamrule PRIMARY KEY (rule_id)
);
DROP TABLE IF EXISTS dc_user;
CREATE TABLE "dc_user"
(
user_id VARCHAR(32) NOT NULL ,
user_super INTEGER NULL ,
user_status INTEGER NOT NULL DEFAULT 1 ,
user_pwd VARCHAR(40) NOT NULL ,
user_change_pwd INTEGER NOT NULL DEFAULT 0 ,
user_recover_key VARCHAR(32) NULL DEFAULT NULL,
user_name VARCHAR(255) NULL DEFAULT NULL,
user_firstname VARCHAR(255) NULL DEFAULT NULL,
user_displayname VARCHAR(255) NULL DEFAULT NULL,
user_email VARCHAR(255) NULL DEFAULT NULL,
user_url VARCHAR(255) NULL DEFAULT NULL,
user_desc TEXT NULL ,
user_default_blog VARCHAR(32) NULL DEFAULT NULL,
user_options TEXT NULL ,
user_lang VARCHAR(5) NULL DEFAULT NULL,
user_tz VARCHAR(128) NOT NULL DEFAULT 'UTC' ,
user_post_status INTEGER NOT NULL DEFAULT -2 ,
user_creadt TIMESTAMP NOT NULL DEFAULT '1970-01-01 00:00:00' ,
user_upddt TIMESTAMP NOT NULL DEFAULT '1970-01-01 00:00:00' ,
CONSTRAINT pk_user PRIMARY KEY (user_id)
);
DROP TABLE IF EXISTS dc_permissions;
CREATE TABLE "dc_permissions"
(
user_id VARCHAR(32) NOT NULL ,
blog_id VARCHAR(32) NOT NULL ,
permissions TEXT NULL ,
CONSTRAINT pk_permissions PRIMARY KEY (user_id,blog_id)
);
DROP TABLE IF EXISTS dc_post;
CREATE TABLE "dc_post"
(
post_id INTEGER NOT NULL ,
blog_id VARCHAR(32) NOT NULL ,
user_id VARCHAR(32) NOT NULL ,
cat_id INTEGER NULL ,
post_dt TIMESTAMP NOT NULL DEFAULT '1970-01-01 00:00:00' ,
post_tz VARCHAR(128) NOT NULL DEFAULT 'UTC' ,
post_creadt TIMESTAMP NOT NULL DEFAULT '1970-01-01 00:00:00' ,
post_upddt TIMESTAMP NOT NULL DEFAULT '1970-01-01 00:00:00' ,
post_password VARCHAR(32) NULL DEFAULT NULL,
post_type VARCHAR(32) NOT NULL DEFAULT 'post' ,
post_format VARCHAR(32) NOT NULL DEFAULT 'xhtml' ,
post_url VARCHAR(255) NOT NULL ,
post_lang VARCHAR(5) NULL DEFAULT NULL,
post_title VARCHAR(255) NULL DEFAULT NULL,
post_excerpt TEXT NULL DEFAULT NULL,
post_excerpt_xhtml TEXT NULL DEFAULT NULL,
post_content TEXT NULL DEFAULT NULL,
post_content_xhtml TEXT NOT NULL ,
post_notes TEXT NULL DEFAULT NULL,
post_meta TEXT NULL DEFAULT NULL,
post_words TEXT NULL DEFAULT NULL,
post_status INTEGER NOT NULL DEFAULT 0 ,
post_selected INTEGER NOT NULL DEFAULT 0 ,
post_position INTEGER NOT NULL DEFAULT 0 ,
post_open_comment INTEGER NOT NULL DEFAULT 0 ,
post_open_tb INTEGER NOT NULL DEFAULT 0 ,
nb_comment INTEGER NOT NULL DEFAULT 0 ,
nb_trackback INTEGER NOT NULL DEFAULT 0 ,
CONSTRAINT pk_post PRIMARY KEY (post_id) ,
CONSTRAINT uk_post_url UNIQUE (post_url,post_type,blog_id)
);
DROP TABLE IF EXISTS dc_media;
CREATE TABLE "dc_media"
(
media_id INTEGER NOT NULL ,
user_id VARCHAR(32) NOT NULL ,
media_path VARCHAR(255) NOT NULL ,
media_title VARCHAR(255) NOT NULL ,
media_file VARCHAR(255) NOT NULL ,
media_dir VARCHAR(255) NOT NULL DEFAULT '.' ,
media_meta TEXT NULL DEFAULT NULL,
media_dt TIMESTAMP NOT NULL DEFAULT '1970-01-01 00:00:00' ,
media_creadt TIMESTAMP NOT NULL DEFAULT '1970-01-01 00:00:00' ,
media_upddt TIMESTAMP NOT NULL DEFAULT '1970-01-01 00:00:00' ,
media_private INTEGER NOT NULL DEFAULT 0 ,
CONSTRAINT pk_media PRIMARY KEY (media_id)
);
DROP TABLE IF EXISTS dc_post_media;
CREATE TABLE "dc_post_media"
(
media_id INTEGER NOT NULL ,
post_id INTEGER NOT NULL ,
link_type VARCHAR(32) NOT NULL DEFAULT 'attachment' ,
CONSTRAINT pk_post_media PRIMARY KEY (media_id,post_id,link_type)
);
DROP TABLE IF EXISTS dc_pref;
CREATE TABLE dc_pref (
pref_id varchar(255) NOT NULL ,
user_id varchar(32) NULL ,
pref_ws varchar(32) NOT NULL DEFAULT 'system' ,
pref_value text NULL DEFAULT NULL,
pref_type varchar(8) NOT NULL DEFAULT 'string' ,
pref_label text NULL ,
CONSTRAINT dc_uk_pref UNIQUE (pref_ws,pref_id,user_id)
);
DROP TABLE IF EXISTS dc_log;
CREATE TABLE "dc_log"
(
log_id INTEGER NOT NULL ,
user_id VARCHAR(32) NULL ,
blog_id VARCHAR(32) NULL ,
log_table VARCHAR(255) NOT NULL ,
log_dt TIMESTAMP NOT NULL DEFAULT '1970-01-01 00:00:00' ,
log_ip VARCHAR(39) NOT NULL ,
log_msg TEXT NULL ,
CONSTRAINT pk_log PRIMARY KEY (log_id)
);
DROP TABLE IF EXISTS dc_version;
CREATE TABLE "dc_version"
(
module VARCHAR(64) NOT NULL ,
version VARCHAR(32) NOT NULL ,
CONSTRAINT pk_version PRIMARY KEY (module)
);
DROP TABLE IF EXISTS dc_ping;
CREATE TABLE "dc_ping"
(
post_id INTEGER NOT NULL ,
ping_url VARCHAR(255) NOT NULL ,
ping_dt TIMESTAMP NOT NULL DEFAULT '1970-01-01 00:00:00' ,
CONSTRAINT pk_ping PRIMARY KEY (post_id,ping_url)
);
DROP TABLE IF EXISTS dc_comment;
CREATE TABLE "dc_comment"
(
comment_id INTEGER NOT NULL ,
post_id INTEGER NOT NULL ,
comment_dt TIMESTAMP NOT NULL DEFAULT '1970-01-01 00:00:00' ,
comment_tz VARCHAR(128) NOT NULL DEFAULT 'UTC' ,
comment_upddt TIMESTAMP NOT NULL DEFAULT '1970-01-01 00:00:00' ,
comment_author VARCHAR(255) NULL DEFAULT NULL,
comment_email VARCHAR(255) NULL DEFAULT NULL,
comment_site VARCHAR(255) NULL DEFAULT NULL,
comment_content TEXT NULL ,
comment_words TEXT NULL DEFAULT NULL,
comment_ip VARCHAR(39) NULL DEFAULT NULL,
comment_status INTEGER NULL DEFAULT 0 ,
comment_spam_status VARCHAR(128) NULL DEFAULT 0 ,
comment_spam_filter VARCHAR(32) NULL DEFAULT NULL,
comment_trackback INTEGER NOT NULL DEFAULT 0 ,
CONSTRAINT pk_comment PRIMARY KEY (comment_id)
);
DROP TABLE IF EXISTS dc_meta;
CREATE TABLE "dc_meta"
(
meta_id VARCHAR(255) NOT NULL ,
meta_type VARCHAR(64) NOT NULL ,
post_id INTEGER NOT NULL ,
CONSTRAINT pk_meta PRIMARY KEY (meta_id,meta_type,post_id)
);
CREATE INDEX idx_blog_blog_upddt ON dc_blog (blog_upddt) ;
CREATE INDEX idx_category_blog_id ON dc_category (blog_id) ;
CREATE INDEX idx_category_cat_lft_blog_id ON dc_category (blog_id,cat_lft) ;
CREATE INDEX idx_category_cat_rgt_blog_id ON dc_category (blog_id,cat_rgt) ;
CREATE INDEX idx_setting_blog_id ON dc_setting (blog_id) ;
CREATE INDEX idx_user_user_default_blog ON dc_user (user_default_blog) ;
CREATE INDEX idx_user_user_super ON dc_user (user_super) ;
CREATE INDEX idx_permissions_blog_id ON dc_permissions (blog_id) ;
CREATE INDEX idx_post_cat_id ON dc_post (cat_id) ;
CREATE INDEX idx_post_user_id ON dc_post (user_id) ;
CREATE INDEX idx_post_blog_id ON dc_post (blog_id) ;
CREATE INDEX idx_post_post_dt ON dc_post (post_dt) ;
CREATE INDEX idx_post_post_dt_post_id ON dc_post (post_dt,post_id) ;
CREATE INDEX idx_blog_post_post_dt_post_id ON dc_post (blog_id,post_dt,post_id) ;
CREATE INDEX idx_blog_post_post_status ON dc_post (blog_id,post_status) ;
CREATE INDEX idx_media_user_id ON dc_media (user_id) ;
CREATE INDEX idx_post_media_post_id ON dc_post_media (post_id) ;
CREATE INDEX idx_log_user_id ON dc_log (user_id) ;
CREATE INDEX idx_comment_post_id ON dc_comment (post_id) ;
CREATE INDEX idx_comment_post_id_dt_status ON dc_comment (post_id,comment_dt,comment_status) ;
CREATE INDEX idx_meta_post_id ON dc_meta (post_id) ;
CREATE INDEX idx_meta_meta_type ON dc_meta (meta_type) ;
|
