database.pgsql 22.6 KB
Newer Older
Dries's avatar
 
Dries committed
1 2 3
--
-- Table structure for access
--
Dries's avatar
 
Dries committed
4

Dries's avatar
 
Dries committed
5 6 7 8 9
CREATE TABLE access (
  aid SERIAL,
  mask varchar(255) NOT NULL default '',
  type varchar(255) NOT NULL default '',
  status smallint NOT NULL default '0',
10
  PRIMARY KEY (aid)
Dries's avatar
 
Dries committed
11 12
);

Dries's avatar
 
Dries committed
13 14 15 16 17
--
-- Table structure for accesslog
--

CREATE TABLE accesslog (
18
  aid SERIAL,
19
  sid varchar(32) NOT NULL default '',
20
  mask varchar(255) NOT NULL default '',
Dries's avatar
 
Dries committed
21 22
  title varchar(255) default NULL,
  path varchar(255) default NULL,
Dries's avatar
 
Dries committed
23 24 25
  url varchar(255) default NULL,
  hostname varchar(128) default NULL,
  uid integer default '0',
Dries's avatar
 
Dries committed
26
  timer integer NOT NULL default '0',
27 28
  timestamp integer NOT NULL default '0',
  PRIMARY KEY (aid)
Dries's avatar
 
Dries committed
29
);
Dries's avatar
 
Dries committed
30
CREATE INDEX accesslog_timestamp_idx ON accesslog (timestamp);
Dries's avatar
 
Dries committed
31
--
32
-- Table structure for table 'aggregator_category'
Dries's avatar
 
Dries committed
33
--
Dries's avatar
 
Dries committed
34

35 36
CREATE TABLE aggregator_category (
  cid serial,
Dries's avatar
 
Dries committed
37
  title varchar(255) NOT NULL default '',
Dries's avatar
 
Dries committed
38
  description text,
39
  block smallint NOT NULL default '0',
40
  PRIMARY KEY (cid),
41 42 43 44 45 46 47 48 49 50
  UNIQUE (title)
);

--
-- Table structure for table 'aggregator_category_feed'
--

CREATE TABLE aggregator_category_feed (
  fid integer NOT NULL default '0',
  cid integer NOT NULL default '0',
51
  PRIMARY KEY (fid,cid)
52 53 54 55 56 57 58 59 60
);

--
-- Table structure for table 'aggregator_category_item'
--

CREATE TABLE aggregator_category_item (
  iid integer NOT NULL default '0',
  cid integer NOT NULL default '0',
61
  PRIMARY KEY (iid,cid)
62 63 64 65 66 67 68 69 70 71 72 73 74
);

--
-- Table structure for table 'aggregator_feed'
--

CREATE TABLE aggregator_feed (
  fid serial,
  title varchar(255) NOT NULL default '',
  url varchar(255) NOT NULL default '',
  refresh integer NOT NULL default '0',
  checked integer NOT NULL default '0',
  link varchar(255) NOT NULL default '',
Dries's avatar
 
Dries committed
75 76
  description text,
  image text,
77 78 79
  etag varchar(255) NOT NULL default '',
  modified integer NOT NULL default '0',
  block smallint NOT NULL default '0',
80
  PRIMARY KEY (fid),
81
  UNIQUE (url),
Dries's avatar
 
Dries committed
82
  UNIQUE (title)
Dries's avatar
 
Dries committed
83 84
);

85 86 87 88 89
--
-- Table structure for table 'aggregator_item'
--

CREATE TABLE aggregator_item (
Dries's avatar
 
Dries committed
90
  iid SERIAL,
91 92 93 94
  fid integer NOT NULL default '0',
  title varchar(255) NOT NULL default '',
  link varchar(255) NOT NULL default '',
  author varchar(255) NOT NULL default '',
Dries's avatar
 
Dries committed
95
  description text,
96
  timestamp integer default NULL,
97
  PRIMARY KEY (iid)
98 99
);

100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119
--
-- Table structure for authmap
--

CREATE TABLE authmap (
  aid SERIAL,
  uid integer NOT NULL default '0',
  authname varchar(128) NOT NULL default '',
  module varchar(128) NOT NULL default '',
  PRIMARY KEY (aid),
  UNIQUE (authname)
);

--
-- Table structure for blocks
--

CREATE TABLE blocks (
  module varchar(64) NOT NULL default '',
  delta varchar(32) NOT NULL default '0',
120
  theme varchar(255) NOT NULL default '',
121 122
  status smallint NOT NULL default '0',
  weight smallint NOT NULL default '0',
123
  region varchar(64) DEFAULT 'left' NOT NULL,
124 125 126
  custom smallint NOT NULL default '0',
  throttle smallint NOT NULL default '0',
  visibility smallint NOT NULL default '0',
127
  pages text NOT NULL default ''
128 129 130 131 132 133 134
);

--
-- Table structure for book
--

CREATE TABLE book (
135
  vid integer NOT NULL default '0',
136 137 138
  nid integer NOT NULL default '0',
  parent integer NOT NULL default '0',
  weight smallint NOT NULL default '0',
139
  PRIMARY KEY (vid)
140 141 142 143 144 145 146 147 148 149 150 151 152 153
);
CREATE INDEX book_nid_idx ON book(nid);
CREATE INDEX book_parent ON book(parent);

--
-- Table structure for boxes
--

CREATE TABLE boxes (
  bid SERIAL,
  title varchar(64) NOT NULL default '',
  body text default '',
  info varchar(128) NOT NULL default '',
  format smallint NOT NULL default '0',
154
  PRIMARY KEY (bid),
Dries's avatar
 
Dries committed
155
  UNIQUE (info)
156
);
157

Dries's avatar
 
Dries committed
158 159 160
--
-- Table structure for cache
--
Dries's avatar
 
Dries committed
161

Dries's avatar
 
Dries committed
162 163
CREATE TABLE cache (
  cid varchar(255) NOT NULL default '',
Dries's avatar
 
Dries committed
164
  data text default '',
Dries's avatar
 
Dries committed
165
  expire integer NOT NULL default '0',
Dries's avatar
 
Dries committed
166
  created integer NOT NULL default '0',
Dries's avatar
Dries committed
167
  headers text default '',
168
  PRIMARY KEY (cid)
Dries's avatar
 
Dries committed
169
);
170
CREATE INDEX cache_expire_idx ON cache(expire);
Dries's avatar
 
Dries committed
171

Dries's avatar
 
Dries committed
172 173 174
--
-- Table structure for comments
--
Dries's avatar
 
Dries committed
175

Dries's avatar
 
Dries committed
176 177 178 179 180 181
CREATE TABLE comments (
  cid SERIAL,
  pid integer NOT NULL default '0',
  nid integer NOT NULL default '0',
  uid integer NOT NULL default '0',
  subject varchar(64) NOT NULL default '',
Dries's avatar
 
Dries committed
182
  comment text NOT NULL default '',
Dries's avatar
 
Dries committed
183 184
  hostname varchar(128) NOT NULL default '',
  timestamp integer NOT NULL default '0',
Dries's avatar
 
Dries committed
185
  score integer NOT NULL default '0',
186
  status smallint NOT NULL default '0',
187
  format smallint NOT NULL default '0',
Dries's avatar
 
Dries committed
188
  thread varchar(255) default '',
Dries's avatar
 
Dries committed
189
  users text default '',
Dries's avatar
 
Dries committed
190 191
  name varchar(60) default NULL,
  mail varchar(64) default NULL,
192
  homepage varchar(255) default NULL,
193
  PRIMARY KEY (cid)
Dries's avatar
 
Dries committed
194
);
Dries's avatar
 
Dries committed
195
CREATE INDEX comments_nid_idx ON comments(nid);
Dries's avatar
 
Dries committed
196

Dries's avatar
 
Dries committed
197
--
198 199 200 201
-- Table structre for table 'contact'
--

CREATE TABLE contact (
202
  cid SERIAL,
Dries's avatar
Dries committed
203
  category varchar(255) NOT NULL default '',
204
  recipients text NOT NULL default '',
Dries's avatar
Dries committed
205
  reply text NOT NULL default '',
206 207
  PRIMARY KEY (cid),
  UNIQUE (category)
208 209 210 211
);

--
-- Table structre for table 'node_comment_statistics'
Dries's avatar
 
Dries committed
212 213 214 215 216
--

CREATE TABLE node_comment_statistics (
  nid integer NOT NULL,
  last_comment_timestamp integer NOT NULL default '0',
217
  last_comment_name varchar(60) default NULL,
Dries's avatar
 
Dries committed
218 219 220 221
  last_comment_uid integer NOT NULL default '0',
  comment_count integer NOT NULL default '0',
  PRIMARY KEY (nid)
);
222
CREATE INDEX node_comment_statistics_timestamp_idx ON node_comment_statistics(last_comment_timestamp);
Dries's avatar
 
Dries committed
223

Dries's avatar
 
Dries committed
224 225 226
--
-- Table structure for directory
--
Dries's avatar
 
Dries committed
227

Dries's avatar
 
Dries committed
228 229 230 231
CREATE TABLE directory (
  link varchar(255) NOT NULL default '',
  name varchar(128) NOT NULL default '',
  mail varchar(128) NOT NULL default '',
Dries's avatar
 
Dries committed
232 233
  slogan text NOT NULL default '',
  mission text NOT NULL default '',
Dries's avatar
 
Dries committed
234
  timestamp integer NOT NULL default '0',
235
  PRIMARY KEY (link)
Dries's avatar
 
Dries committed
236
);
Dries's avatar
 
Dries committed
237

Dries's avatar
 
Dries committed
238 239 240 241 242
--
-- Table structure for table 'files'
--

CREATE TABLE files (
243
  fid SERIAL,
Dries's avatar
 
Dries committed
244
  nid integer NOT NULL default '0',
245
  vid integer NOT NULL default '0',
246
  description varchar(255) NOT NULL default '',
Dries's avatar
 
Dries committed
247 248 249
  filename varchar(255) NOT NULL default '',
  filepath varchar(255) NOT NULL default '',
  filemime varchar(255) NOT NULL default '',
Dries's avatar
 
Dries committed
250
  filesize integer NOT NULL default '0',
251
  list smallint NOT NULL default '0'
Dries's avatar
 
Dries committed
252
);
253 254
CREATE INDEX files_fid_idx ON files(fid);
CREATE INDEX files_vid_idx ON files(vid);
Dries's avatar
 
Dries committed
255

Dries's avatar
 
Dries committed
256 257 258 259 260 261 262 263 264 265 266 267
--
-- Table structure for table 'filter_formats'
--

CREATE TABLE filter_formats (
  format SERIAL,
  name varchar(255) NOT NULL default '',
  roles varchar(255) NOT NULL default '',
  cache smallint NOT NULL default '0',
  PRIMARY KEY (format)
);

Dries's avatar
 
Dries committed
268 269 270 271 272
--
-- Table structure for table 'filters'
--

CREATE TABLE filters (
Dries's avatar
 
Dries committed
273 274 275 276
  format integer NOT NULL DEFAULT '0',
  module varchar(64) NOT NULL DEFAULT '',
  delta smallint NOT NULL DEFAULT 1,
  weight smallint DEFAULT '0' NOT NULL
Dries's avatar
 
Dries committed
277
);
Dries's avatar
 
Dries committed
278
CREATE INDEX filters_module_idx ON filters(module);
279
CREATE INDEX filters_weight_idx ON filters(weight);
Dries's avatar
 
Dries committed
280

Dries's avatar
 
Dries committed
281 282 283 284 285 286 287
--
-- Table structure for table 'flood'
--

CREATE TABLE flood (
  event varchar(64) NOT NULL default '',
  hostname varchar(128) NOT NULL default '',
288
  timestamp integer NOT NULL default '0'
Dries's avatar
 
Dries committed
289 290
);

Dries's avatar
 
Dries committed
291 292 293 294 295 296
--
-- Table structure for table 'forum'
--

CREATE TABLE forum (
  nid integer NOT NULL default '0',
297
  vid integer NOT NULL default '0',
Dries's avatar
 
Dries committed
298
  tid integer NOT NULL default '0',
299
  PRIMARY KEY (nid)
Dries's avatar
 
Dries committed
300
);
Dries's avatar
 
Dries committed
301
CREATE INDEX forum_tid_idx ON forum(tid);
302
CREATE INDEX forum_vid_idx ON forum(vid);
Dries's avatar
 
Dries committed
303

Dries's avatar
 
Dries committed
304 305 306
--
-- Table structure for history
--
Dries's avatar
 
Dries committed
307

Dries's avatar
 
Dries committed
308 309 310 311
CREATE TABLE history (
  uid integer NOT NULL default '0',
  nid integer NOT NULL default '0',
  timestamp integer NOT NULL default '0',
312
  PRIMARY KEY (uid,nid)
Dries's avatar
 
Dries committed
313 314
);

Dries's avatar
 
Dries committed
315
--
Dries's avatar
 
Dries committed
316
-- Table structure for locales_meta
Dries's avatar
 
Dries committed
317
--
Dries's avatar
 
Dries committed
318

Dries's avatar
 
Dries committed
319 320 321 322 323 324 325
CREATE TABLE locales_meta (
  locale varchar(12) NOT NULL default '',
  name varchar(64) NOT NULL default '',
  enabled int4 NOT NULL default '0',
  isdefault int4 NOT NULL default '0',
  plurals int4 NOT NULL default '0',
  formula varchar(128) NOT NULL default '',
326
  PRIMARY KEY (locale)
Dries's avatar
 
Dries committed
327 328 329 330 331 332 333 334
);

--
-- Table structure for locales_source
--


CREATE TABLE locales_source (
335
  lid SERIAL,
336
  location varchar(255) NOT NULL default '',
Dries's avatar
 
Dries committed
337
  source text NOT NULL,
338
  PRIMARY KEY (lid)
Dries's avatar
 
Dries committed
339 340
);

Dries's avatar
 
Dries committed
341 342 343 344 345 346
--
-- Table structure for locales_target
--

CREATE TABLE locales_target (
  lid int4 NOT NULL default '0',
Dries's avatar
 
Dries committed
347
  translation text DEFAULT '' NOT NULL,
Dries's avatar
 
Dries committed
348 349 350
  locale varchar(12) NOT NULL default '',
  plid int4 NOT NULL default '0',
  plural int4 NOT NULL default '0',
351
  UNIQUE (lid)
Dries's avatar
 
Dries committed
352
);
353 354 355 356
CREATE INDEX locales_target_lid_idx ON locales_target(lid);
CREATE INDEX locales_target_lang_idx ON locales_target(locale);
CREATE INDEX locales_target_plid_idx ON locales_target(plid);
CREATE INDEX locales_target_plural_idx ON locales_target(plural);
Dries's avatar
 
Dries committed
357

358 359 360 361
--
-- Table structure for table 'menu'
--

Dries's avatar
 
Dries committed
362

363
CREATE TABLE menu (
Dries's avatar
 
Dries committed
364
  mid serial,
365 366 367
  pid integer NOT NULL default '0',
  path varchar(255) NOT NULL default '',
  title varchar(255) NOT NULL default '',
Dries's avatar
 
Dries committed
368
  description varchar(255) NOT NULL default '',
369
  weight smallint NOT NULL default '0',
Dries's avatar
 
Dries committed
370
  type smallint NOT NULL default '0',
371
  PRIMARY KEY (mid)
372
);
Dries's avatar
 
Dries committed
373
--
Dries's avatar
 
Dries committed
374
-- Table structure for table 'moderation_filters'
Dries's avatar
 
Dries committed
375
--
Dries's avatar
 
Dries committed
376

Dries's avatar
 
Dries committed
377 378 379 380
CREATE TABLE moderation_filters (
  fid SERIAL,
  filter varchar(255) NOT NULL default '',
  minimum smallint NOT NULL default '0',
381
  PRIMARY KEY (fid)
Dries's avatar
 
Dries committed
382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403
);

--
-- Table structure for table 'moderation_roles'
--

CREATE TABLE moderation_roles (
  rid integer NOT NULL default '0',
  mid integer NOT NULL default '0',
  value smallint NOT NULL default '0'
);
CREATE INDEX moderation_roles_rid_idx ON moderation_roles(rid);
CREATE INDEX moderation_roles_mid_idx ON moderation_roles(mid);

--
-- Table structure for table 'moderation_votes'
--

CREATE TABLE moderation_votes (
  mid SERIAL,
  vote varchar(255) default NULL,
  weight smallint NOT NULL default '0',
404
  PRIMARY KEY (mid)
Dries's avatar
 
Dries committed
405 406
);

Dries's avatar
 
Dries committed
407 408 409
--
-- Table structure for node
--
Dries's avatar
 
Dries committed
410

Dries's avatar
 
Dries committed
411 412
CREATE TABLE node (
  nid SERIAL,
413
  vid integer NOT NULL default '0',
414
  type varchar(32) NOT NULL default '',
Dries's avatar
 
Dries committed
415 416 417 418
  title varchar(128) NOT NULL default '',
  uid integer NOT NULL default '0',
  status integer NOT NULL default '1',
  created integer NOT NULL default '0',
419
  changed integer NOT NULL default '0',
Dries's avatar
 
Dries committed
420 421 422
  comment integer NOT NULL default '0',
  promote integer NOT NULL default '0',
  moderate integer NOT NULL default '0',
Dries's avatar
 
Dries committed
423
  sticky integer NOT NULL default '0',
424
  PRIMARY KEY (nid)
Dries's avatar
 
Dries committed
425
);
Dries's avatar
 
Dries committed
426
CREATE INDEX node_type_idx ON node(type);
427
CREATE INDEX node_title_type_idx ON node(title,type);
Dries's avatar
 
Dries committed
428 429
CREATE INDEX node_status_idx ON node(status);
CREATE INDEX node_uid_idx ON node(uid);
Dries's avatar
 
Dries committed
430 431
CREATE INDEX node_moderate_idx ON node (moderate);
CREATE INDEX node_promote_status_idx ON node (promote, status);
432 433
CREATE INDEX node_created ON node(created);
CREATE INDEX node_changed ON node(changed);
434
CREATE INDEX node_vid_idx ON node(vid);
435
CREATE INDEX node_status_type_nid_idx ON node(status,type,nid);
Dries's avatar
 
Dries committed
436

437 438 439 440 441 442 443
--
-- Table structure for table `node_access`
--

CREATE TABLE node_access (
  nid SERIAL,
  gid integer NOT NULL default '0',
444
  realm varchar(255) NOT NULL default '',
445 446 447
  grant_view smallint NOT NULL default '0',
  grant_update smallint NOT NULL default '0',
  grant_delete smallint NOT NULL default '0',
448
  PRIMARY KEY (nid,gid,realm)
Dries's avatar
 
Dries committed
449
);
450

451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468
--
-- Table structure for table 'node_revisions'
--

CREATE TABLE node_revisions (
  nid integer NOT NULL default '0',
  vid integer NOT NULL default '0',
  uid integer NOT NULL default '0',
  title varchar(128) NOT NULL default '',
  body text NOT NULL default '',
  teaser text NOT NULL default '',
  log text NOT NULL default '',
  timestamp integer NOT NULL default '0',
  format int NOT NULL default '0',
  PRIMARY KEY  (nid,vid)
);
CREATE INDEX node_revisions_uid_idx ON node_revisions(uid);
CREATE SEQUENCE node_revisions_vid_seq INCREMENT 1 START 1;
469

Dries's avatar
 
Dries committed
470 471 472 473 474 475 476 477 478
--
-- Table structure for table 'node_counter'
--

CREATE TABLE node_counter (
  nid integer NOT NULL default '0',
  totalcount integer NOT NULL default '0',
  daycount integer NOT NULL default '0',
  timestamp integer NOT NULL default '0',
479
  PRIMARY KEY (nid)
Dries's avatar
 
Dries committed
480 481 482 483 484
);
CREATE INDEX node_counter_totalcount_idx ON node_counter(totalcount);
CREATE INDEX node_counter_daycount_idx ON node_counter(daycount);
CREATE INDEX node_counter_timestamp_idx ON node_counter(timestamp);

Dries's avatar
 
Dries committed
485
--
Dries's avatar
 
Dries committed
486
-- Table structure for table 'url_alias'
Dries's avatar
 
Dries committed
487 488
--

489 490 491 492 493 494 495 496 497 498
CREATE TABLE profile_fields (
  fid serial,
  title varchar(255) default NULL,
  name varchar(128) default NULL,
  explanation TEXT default NULL,
  category varchar(255) default NULL,
  page varchar(255) default NULL,
  type varchar(128) default NULL,
  weight smallint DEFAULT '0' NOT NULL,
  required smallint DEFAULT '0' NOT NULL,
Dries's avatar
 
Dries committed
499
  register smallint DEFAULT '0' NOT NULL,
500
  visibility smallint DEFAULT '0' NOT NULL,
501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518
  options text,
  UNIQUE (name),
  PRIMARY KEY (fid)
);
CREATE INDEX profile_fields_category ON profile_fields (category);

--
-- Table structure for table 'profile_values'
--

CREATE TABLE profile_values (
  fid integer default '0',
  uid integer default '0',
  value text
);
CREATE INDEX profile_values_uid ON profile_values (uid);
CREATE INDEX profile_values_fid ON profile_values (fid);

Dries's avatar
 
Dries committed
519 520
CREATE TABLE url_alias (
  pid serial,
Dries's avatar
 
Dries committed
521
  src varchar(128) NOT NULL default '',
522
  dst varchar(128) NOT NULL default '',
523
  PRIMARY KEY (pid)
Dries's avatar
 
Dries committed
524
);
Dries's avatar
 
Dries committed
525
CREATE INDEX url_alias_dst_idx ON url_alias(dst);
526
CREATE INDEX url_alias_src_idx ON url_alias(src);
Dries's avatar
 
Dries committed
527 528 529
--
-- Table structure for permission
--
Dries's avatar
 
Dries committed
530

Dries's avatar
 
Dries committed
531 532
CREATE TABLE permission (
  rid integer NOT NULL default '0',
Dries's avatar
 
Dries committed
533
  perm text default '',
Dries's avatar
 
Dries committed
534
  tid integer NOT NULL default '0'
Dries's avatar
 
Dries committed
535
);
Dries's avatar
 
Dries committed
536
CREATE INDEX permission_rid_idx ON permission(rid);
Dries's avatar
 
Dries committed
537

Dries's avatar
 
Dries committed
538 539 540
--
-- Table structure for poll
--
Dries's avatar
 
Dries committed
541

Dries's avatar
 
Dries committed
542 543 544
CREATE TABLE poll (
  nid integer NOT NULL default '0',
  runtime integer NOT NULL default '0',
Dries's avatar
 
Dries committed
545
  polled text NOT NULL default '',
Dries's avatar
 
Dries committed
546
  active integer NOT NULL default '0',
547
  PRIMARY KEY (nid)
Dries's avatar
 
Dries committed
548
);
Dries's avatar
 
Dries committed
549

Dries's avatar
 
Dries committed
550 551 552
--
-- Table structure for poll_choices
--
Dries's avatar
 
Dries committed
553

Dries's avatar
 
Dries committed
554 555 556 557 558 559
CREATE TABLE poll_choices (
  chid SERIAL,
  nid integer NOT NULL default '0',
  chtext varchar(128) NOT NULL default '',
  chvotes integer NOT NULL default '0',
  chorder integer NOT NULL default '0',
560
  PRIMARY KEY (chid)
Dries's avatar
 
Dries committed
561
);
Dries's avatar
 
Dries committed
562
CREATE INDEX poll_choices_nid_idx ON poll_choices(nid);
Dries's avatar
 
Dries committed
563

Dries's avatar
 
Dries committed
564 565 566
--
-- Table structure for role
--
Dries's avatar
 
Dries committed
567

Dries's avatar
 
Dries committed
568 569 570
CREATE TABLE role (
  rid SERIAL,
  name varchar(32) NOT NULL default '',
571
  PRIMARY KEY (rid),
Dries's avatar
 
Dries committed
572 573
  UNIQUE (name)
);
Dries's avatar
 
Dries committed
574

575 576 577 578 579 580
--
-- Table structure for table 'search_dataset'
--
CREATE TABLE search_dataset (
  sid integer NOT NULL default '0',
  type varchar(16) default NULL,
581
  data text NOT NULL default ''
582
);
583
CREATE INDEX search_dataset_sid_type_idx ON search_dataset(sid, type);
584

Dries's avatar
 
Dries committed
585 586 587
--
-- Table structure for search_index
--
Dries's avatar
 
Dries committed
588

Dries's avatar
 
Dries committed
589
CREATE TABLE search_index (
Dries's avatar
 
Dries committed
590
  word varchar(50) NOT NULL default '',
591
  sid integer NOT NULL default '0',
Dries's avatar
 
Dries committed
592
  type varchar(16) default NULL,
593 594
  fromsid integer NOT NULL default '0',
  fromtype varchar(16) default NULL,
595
  score float default NULL
Dries's avatar
 
Dries committed
596
);
597
CREATE INDEX search_index_sid_type_idx ON search_index(sid, type);
598
CREATE INDEX search_index_fromsid_fromtype_idx ON search_index(fromsid, fromtype);
Dries's avatar
 
Dries committed
599
CREATE INDEX search_index_word_idx ON search_index(word);
Dries's avatar
 
Dries committed
600

601 602 603 604 605 606
--
-- Table structures for search_total
--

CREATE TABLE search_total (
  word varchar(50) NOT NULL default '',
607 608
  count float default NULL,
  PRIMARY KEY(word)
609 610
);

Dries's avatar
 
Dries committed
611 612 613 614 615
--
-- Table structure for sessions
--

CREATE TABLE sessions (
616
  uid integer not null,
Dries's avatar
 
Dries committed
617 618 619
  sid varchar(32) NOT NULL default '',
  hostname varchar(128) NOT NULL default '',
  timestamp integer NOT NULL default '0',
620
  cache integer NOT NULL default '0',
Dries's avatar
 
Dries committed
621 622 623 624
  session text,
  PRIMARY KEY (sid)
);

Dries's avatar
 
Dries committed
625 626 627 628 629 630 631 632 633 634 635
--
-- Table structure for sequences
-- This is only used under MySQL, co commented out
--
--
-- CREATE TABLE sequences (
--   name varchar(255) NOT NULL,
--   id integer NOT NULL,
--   PRIMARY KEY (name)
-- );

Dries's avatar
 
Dries committed
636 637 638
--
-- Table structure for system
--
Dries's avatar
 
Dries committed
639

Dries's avatar
 
Dries committed
640 641 642 643 644 645
CREATE TABLE system (
  filename varchar(255) NOT NULL default '',
  name varchar(255) NOT NULL default '',
  type varchar(255) NOT NULL default '',
  description varchar(255) NOT NULL default '',
  status integer NOT NULL default '0',
Dries's avatar
 
Dries committed
646
  throttle smallint NOT NULL default '0',
Dries's avatar
 
Dries committed
647
  bootstrap integer NOT NULL default '0',
648
  PRIMARY KEY (filename)
Dries's avatar
 
Dries committed
649
);
Dries's avatar
 
Dries committed
650

Dries's avatar
 
Dries committed
651 652 653
--
-- Table structure for term_data
--
Dries's avatar
 
Dries committed
654

Dries's avatar
 
Dries committed
655 656 657 658
CREATE TABLE term_data (
  tid SERIAL,
  vid integer NOT NULL default '0',
  name varchar(255) NOT NULL default '',
Dries's avatar
 
Dries committed
659
  description text default '',
Dries's avatar
 
Dries committed
660
  weight smallint NOT NULL default '0',
661
  PRIMARY KEY (tid)
Dries's avatar
 
Dries committed
662 663
);
CREATE INDEX term_data_vid_idx ON term_data(vid);
Dries's avatar
 
Dries committed
664

Dries's avatar
 
Dries committed
665 666 667
--
-- Table structure for term_hierarchy
--
Dries's avatar
 
Dries committed
668

Dries's avatar
 
Dries committed
669 670 671 672 673 674
CREATE TABLE term_hierarchy (
  tid integer NOT NULL default '0',
  parent integer NOT NULL default '0'
);
CREATE INDEX term_hierarchy_tid_idx ON term_hierarchy(tid);
CREATE INDEX term_hierarchy_parent_idx ON term_hierarchy(parent);
Dries's avatar
 
Dries committed
675

Dries's avatar
 
Dries committed
676 677 678
--
-- Table structure for term_node
--
Dries's avatar
 
Dries committed
679

Dries's avatar
 
Dries committed
680 681
CREATE TABLE term_node (
  nid integer NOT NULL default '0',
682 683
  tid integer NOT NULL default '0',
  PRIMARY KEY (tid,nid)
Dries's avatar
 
Dries committed
684 685 686
);
CREATE INDEX term_node_nid_idx ON term_node(nid);
CREATE INDEX term_node_tid_idx ON term_node(tid);
Dries's avatar
 
Dries committed
687

Dries's avatar
 
Dries committed
688 689 690
--
-- Table structure for term_relation
--
Dries's avatar
 
Dries committed
691

Dries's avatar
 
Dries committed
692 693 694 695 696 697
CREATE TABLE term_relation (
  tid1 integer NOT NULL default '0',
  tid2 integer NOT NULL default '0'
);
CREATE INDEX term_relation_tid1_idx ON term_relation(tid1);
CREATE INDEX term_relation_tid2_idx ON term_relation(tid2);
Dries's avatar
 
Dries committed
698

Dries's avatar
 
Dries committed
699 700 701
--
-- Table structure for term_synonym
--
Dries's avatar
 
Dries committed
702

Dries's avatar
 
Dries committed
703 704 705 706 707 708
CREATE TABLE term_synonym (
  tid integer NOT NULL default '0',
  name varchar(255) NOT NULL default ''
);
CREATE INDEX term_synonym_tid_idx ON term_synonym(tid);
CREATE INDEX term_synonym_name_idx ON term_synonym(name);
Dries's avatar
 
Dries committed
709

Dries's avatar
 
Dries committed
710 711 712
--
-- Table structure for users
--
Dries's avatar
 
Dries committed
713

Dries's avatar
 
Dries committed
714
CREATE TABLE users (
Dries's avatar
 
Dries committed
715
  uid integer NOT NULL default '0',
Dries's avatar
 
Dries committed
716 717 718 719 720 721 722 723
  name varchar(60) NOT NULL default '',
  pass varchar(32) NOT NULL default '',
  mail varchar(64) default '',
  mode smallint NOT NULL default '0',
  sort smallint default '0',
  threshold smallint default '0',
  theme varchar(255) NOT NULL default '',
  signature varchar(255) NOT NULL default '',
Dries's avatar
 
Dries committed
724
  created integer NOT NULL default '0',
725
  access integer NOT NULL default '0',
726
  login integer NOT NULL default '0',
Dries's avatar
 
Dries committed
727 728
  status smallint NOT NULL default '0',
  timezone varchar(8) default NULL,
Dries's avatar
 
Dries committed
729
  language varchar(12) NOT NULL default '',
Dries's avatar
 
Dries committed
730
  picture varchar(255) NOT NULL DEFAULT '',
Dries's avatar
 
Dries committed
731
  init varchar(64) default '',
Dries's avatar
 
Dries committed
732
  data text default '',
733
  PRIMARY KEY (uid),
Dries's avatar
 
Dries committed
734 735
  UNIQUE (name)
);
736
CREATE INDEX users_access_idx ON users(access);
Dries's avatar
 
Dries committed
737

Dries's avatar
 
Dries committed
738 739
CREATE SEQUENCE users_uid_seq INCREMENT 1 START 1;

Dries's avatar
 
Dries committed
740 741 742 743 744 745 746 747 748 749
--
-- Table structure for users_roles
--

CREATE TABLE users_roles (
  uid integer NOT NULL default '0',
  rid integer NOT NULL default '0',
  PRIMARY KEY (uid, rid)
);

Dries's avatar
 
Dries committed
750 751 752
--
-- Table structure for variable
--
Dries's avatar
 
Dries committed
753

Dries's avatar
 
Dries committed
754
CREATE TABLE variable (
Dries's avatar
 
Dries committed
755
  name varchar(48) NOT NULL default '',
Dries's avatar
 
Dries committed
756
  value text NOT NULL default '',
757
  PRIMARY KEY (name)
Dries's avatar
 
Dries committed
758
);
Dries's avatar
 
Dries committed
759

Dries's avatar
 
Dries committed
760 761 762
--
-- Table structure for vocabulary
--
Dries's avatar
 
Dries committed
763

Dries's avatar
 
Dries committed
764 765 766
CREATE TABLE vocabulary (
  vid SERIAL,
  name varchar(255) NOT NULL default '',
Dries's avatar
 
Dries committed
767
  description text default '',
768
  help varchar(255) NOT NULL default '',
Dries's avatar
 
Dries committed
769 770 771 772
  relations smallint NOT NULL default '0',
  hierarchy smallint NOT NULL default '0',
  multiple smallint NOT NULL default '0',
  required smallint NOT NULL default '0',
Dries's avatar
Dries committed
773
  tags smallint NOT NULL default '0',
774
  module varchar(255) NOT NULL default '',
Dries's avatar
 
Dries committed
775
  weight smallint NOT NULL default '0',
776
  PRIMARY KEY (vid)
Dries's avatar
 
Dries committed
777
);
Dries's avatar
 
Dries committed
778

Dries's avatar
 
Dries committed
779 780 781 782 783 784 785 786 787 788
--
-- Table structure for vocabulary_node_types
--

CREATE TABLE vocabulary_node_types (
  vid integer NOT NULL default '0',
  type varchar(16) NOT NULL default '',
  PRIMARY KEY (vid, type)
);

Dries's avatar
 
Dries committed
789 790 791
--
-- Table structure for watchdog
--
Dries's avatar
 
Dries committed
792

Dries's avatar
 
Dries committed
793 794 795 796
CREATE TABLE watchdog (
  wid SERIAL,
  uid integer NOT NULL default '0',
  type varchar(16) NOT NULL default '',
Dries's avatar
 
Dries committed
797
  message text NOT NULL default '',
798
  severity smallint NOT NULL default '0',
Dries's avatar
 
Dries committed
799
  link varchar(255) NOT NULL default '',
Dries's avatar
 
Dries committed
800
  location varchar(128) NOT NULL default '',
801
  referer varchar(128) NOT NULL default '',
Dries's avatar
 
Dries committed
802 803
  hostname varchar(128) NOT NULL default '',
  timestamp integer NOT NULL default '0',
804
  PRIMARY KEY (wid)
Dries's avatar
 
Dries committed
805
);
Dries's avatar
 
Dries committed
806

Dries's avatar
 
Dries committed
807 808 809
--
-- Insert some default values
--
Dries's avatar
 
Dries committed
810

Dries's avatar
 
Dries committed
811 812
INSERT INTO system VALUES ('modules/block.module','block','module','',1,0,0);
INSERT INTO system VALUES ('modules/comment.module','comment','module','',1,0,0);
Dries's avatar
Dries committed
813
INSERT INTO system VALUES ('modules/filter.module','filter','module','',1,0,0);
Dries's avatar
 
Dries committed
814
INSERT INTO system VALUES ('modules/help.module','help','module','',1,0,0);
815
INSERT INTO system VALUES ('modules/menu.module','menu','module','',1,0,0);
Dries's avatar
 
Dries committed
816 817 818
INSERT INTO system VALUES ('modules/node.module','node','module','',1,0,0);
INSERT INTO system VALUES ('modules/page.module','page','module','',1,0,0);
INSERT INTO system VALUES ('modules/story.module','story','module','',1,0,0);
Dries's avatar
Dries committed
819
INSERT INTO system VALUES ('modules/system.module','system','module','',1,0,0);
Dries's avatar
 
Dries committed
820
INSERT INTO system VALUES ('modules/taxonomy.module','taxonomy','module','',1,0,0);
Dries's avatar
Dries committed
821 822
INSERT INTO system VALUES ('modules/user.module','user','module','',1,0,0);
INSERT INTO system VALUES ('modules/watchdog.module','watchdog','module','',1,0,0);
823 824
INSERT INTO system VALUES ('themes/engines/phptemplate/phptemplate.engine', 'phptemplate', 'theme_engine', '', 1, 0, 0);
INSERT INTO system VALUES ('themes/bluemarine/page.tpl.php', 'bluemarine', 'theme', 'themes/engines/phptemplate/phptemplate.engine', 1, 0, 0);
Dries's avatar
 
Dries committed
825

Dries's avatar
 
Dries committed
826
INSERT INTO variable(name,value) VALUES('update_start', 's:10:"2005-03-21";');
Dries's avatar
 
Dries committed
827
INSERT INTO variable(name,value) VALUES('theme_default','s:10:"bluemarine";');
828 829
INSERT INTO users(uid,name,mail) VALUES(0,'','');
INSERT INTO users_roles(uid,rid) VALUES(0, 1);
Dries's avatar
 
Dries committed
830

Dries's avatar
Dries committed
831
INSERT INTO role (name) VALUES ('anonymous user');
Dries's avatar
 
Dries committed
832
INSERT INTO permission VALUES (1,'access content',0);
Dries's avatar
 
Dries committed
833

Dries's avatar
Dries committed
834
INSERT INTO role (name) VALUES ('authenticated user');
Dries's avatar
 
Dries committed
835
INSERT INTO permission VALUES (2,'access comments, access content, post comments, post comments without approval',0);
Dries's avatar
 
Dries committed
836

837 838
INSERT INTO blocks(module,delta,theme,status) VALUES('user', 0, 'bluemarine', 1);
INSERT INTO blocks(module,delta,theme,status) VALUES('user', 1, 'bluemarine', 1);
Dries's avatar
 
Dries committed
839

840 841
INSERT INTO node_access VALUES (0, 0, 'all', 1, 0, 0);

Dries's avatar
 
Dries committed
842 843 844
INSERT INTO filter_formats (name, roles, cache) VALUES ('Filtered HTML',',1,2,',1);
INSERT INTO filter_formats (name, roles, cache) VALUES ('PHP code','',0);
INSERT INTO filter_formats (name, roles, cache) VALUES ('Full HTML','',1);
845
INSERT INTO filters VALUES (1,'filter',0,0);
846
INSERT INTO filters VALUES (1,'filter',2,1);
847
INSERT INTO filters VALUES (2,'filter',1,0);
848
INSERT INTO filters VALUES (3,'filter',2,0);
849 850
INSERT INTO variable (name,value) VALUES ('filter_html_1','i:1;');

851 852 853
INSERT INTO locales_meta (locale, name, enabled, isdefault) VALUES ('en', 'English', '1', '1');

INSERT INTO url_alias (src, dst) VALUES ('node/feed', 'rss.xml');
854

855 856
INSERT INTO variable (name, value) VALUES ('node_options_forum', 'a:1:{i:0;s:6:"status";}');

857 858 859 860
INSERT INTO menu VALUES (2, 0, '', 'Primary links', '', 0, 115);
INSERT INTO variable VALUES ('menu_primary_menu', 'i:2;');
INSERT INTO variable VALUES ('menu_secondary_menu', 'i:2;');

Dries's avatar
 
Dries committed
861 862 863
---
--- Alter some sequences
---
864
ALTER SEQUENCE menu_mid_seq RESTART 3;
Dries's avatar
 
Dries committed
865

Dries's avatar
 
Dries committed
866 867 868 869
---
--- Functions
---

870
CREATE FUNCTION greatest(integer, integer) RETURNS integer AS '
Dries's avatar
 
Dries committed
871
BEGIN
872 873 874
  IF $2 IS NULL THEN
    RETURN $1;
  END IF;
Dries's avatar
 
Dries committed
875 876 877 878 879 880 881
  IF $1 > $2 THEN
    RETURN $1;
  END IF;
  RETURN $2;
END;
' LANGUAGE 'plpgsql';

882 883 884 885
CREATE FUNCTION greatest(integer, integer, integer) RETURNS integer AS '
  SELECT greatest($1, greatest($2, $3));
' LANGUAGE 'sql';

Dries's avatar
 
Dries committed
886 887 888 889 890
CREATE FUNCTION "rand"() RETURNS float AS '
BEGIN
  RETURN random();
END;
' LANGUAGE 'plpgsql';
891 892 893 894 895 896

CREATE FUNCTION "concat"(text, text) RETURNS text AS '
BEGIN
  RETURN $1 || $2;
END;
' LANGUAGE 'plpgsql';
Dries's avatar
 
Dries committed
897

898 899 900
CREATE FUNCTION "if"(boolean, anyelement, anyelement) RETURNS anyelement AS '
  SELECT CASE WHEN $1 THEN $2 ELSE $3 END;
' LANGUAGE 'sql';
Dries's avatar
 
Dries committed
901