database.pgsql 20.1 KB
Newer Older
Dries's avatar
 
Dries committed
1 2 3
-- PostgreSQL include file 31/10/2002
-- Maintainer: James Arthur, j_a_arthurATyahooDOTcom

Dries's avatar
 
Dries committed
4 5 6
--
-- Table structure for access
--
Dries's avatar
 
Dries committed
7

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

Dries's avatar
 
Dries committed
16 17 18 19 20
--
-- Table structure for accesslog
--

CREATE TABLE accesslog (
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 26 27
  url varchar(255) default NULL,
  hostname varchar(128) default NULL,
  uid integer default '0',
  timestamp integer NOT NULL default '0'
);
Dries's avatar
 
Dries committed
28
CREATE INDEX accesslog_timestamp_idx ON accesslog (timestamp);
Dries's avatar
 
Dries committed
29

Dries's avatar
 
Dries committed
30 31 32
--
-- Table structure for authmap
--
Dries's avatar
 
Dries committed
33

Dries's avatar
 
Dries committed
34 35 36 37 38 39 40
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)
Dries's avatar
 
Dries committed
41 42
);

Dries's avatar
 
Dries committed
43 44 45
--
-- Table structure for blocks
--
Dries's avatar
 
Dries committed
46

Dries's avatar
 
Dries committed
47 48
CREATE TABLE blocks (
  module varchar(64) NOT NULL default '',
Dries's avatar
 
Dries committed
49
  delta varchar(32) NOT NULL default '0',
Dries's avatar
 
Dries committed
50 51 52 53
  status smallint NOT NULL default '0',
  weight smallint NOT NULL default '0',
  region smallint NOT NULL default '0',
  path varchar(255) NOT NULL default '',
Dries's avatar
 
Dries committed
54 55
  custom smallint NOT NULL default '0',
  throttle smallint NOT NULL default '0'
Dries's avatar
 
Dries committed
56 57
);

Dries's avatar
 
Dries committed
58 59 60
--
-- Table structure for book
--
Dries's avatar
 
Dries committed
61

Dries's avatar
 
Dries committed
62 63 64 65 66
CREATE TABLE book (
  nid integer NOT NULL default '0',
  parent integer NOT NULL default '0',
  weight smallint NOT NULL default '0',
  format smallint default '0',
Dries's avatar
 
Dries committed
67
  log text default '',
Dries's avatar
 
Dries committed
68
  PRIMARY KEY (nid)
Dries's avatar
 
Dries committed
69
);
Dries's avatar
 
Dries committed
70
CREATE INDEX book_nid_idx ON book(nid);
Dries's avatar
 
Dries committed
71
CREATE INDEX book_parent ON book(parent);
Dries's avatar
 
Dries committed
72

Dries's avatar
 
Dries committed
73 74 75
--
-- Table structure for boxes
--
Dries's avatar
 
Dries committed
76

Dries's avatar
 
Dries committed
77 78 79
CREATE TABLE boxes (
  bid SERIAL,
  title varchar(64) NOT NULL default '',
Dries's avatar
 
Dries committed
80
  body text default '',
Dries's avatar
 
Dries committed
81
  info varchar(128) NOT NULL default '',
Dries's avatar
 
Dries committed
82
  format smallint NOT NULL default '0',
Dries's avatar
 
Dries committed
83 84 85
  PRIMARY KEY  (bid),
  UNIQUE (info),
  UNIQUE (title)
Dries's avatar
 
Dries committed
86 87
);

Dries's avatar
 
Dries committed
88
--
89
-- Table structure for table 'aggregator_category'
Dries's avatar
 
Dries committed
90
--
Dries's avatar
 
Dries committed
91

92 93
CREATE TABLE aggregator_category (
  cid serial,
Dries's avatar
 
Dries committed
94
  title varchar(255) NOT NULL default '',
Dries's avatar
 
Dries committed
95
  description text,
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
  block smallint NOT NULL default '0',
  PRIMARY KEY  (cid),
  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',
  PRIMARY KEY  (fid,cid)
);

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

CREATE TABLE aggregator_category_item (
  iid integer NOT NULL default '0',
  cid integer NOT NULL default '0',
  PRIMARY KEY  (iid,cid)
);

--
-- 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
132 133
  description text,
  image text,
134 135 136 137 138
  etag varchar(255) NOT NULL default '',
  modified integer NOT NULL default '0',
  block smallint NOT NULL default '0',
  PRIMARY KEY  (fid),
  UNIQUE (url),
Dries's avatar
 
Dries committed
139
  UNIQUE (title)
Dries's avatar
 
Dries committed
140 141
);

142 143 144 145 146
--
-- Table structure for table 'aggregator_item'
--

CREATE TABLE aggregator_item (
Dries's avatar
 
Dries committed
147
  iid SERIAL,
148 149 150 151
  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
152
  description text,
153 154 155 156 157
  timestamp integer default NULL,
  PRIMARY KEY  (iid)
);


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 '',
Dries's avatar
 
Dries committed
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
  hostname varchar(128) NOT NULL default '',
Dries's avatar
 
Dries committed
184
  format smallint NOT NULL default '0',
Dries's avatar
 
Dries committed
185
  timestamp integer NOT NULL default '0',
Dries's avatar
 
Dries committed
186 187
  score integer NOT NULL default '0',
  status 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 192
  name varchar(60) default NULL,
  mail varchar(64) default NULL,
  url varchar(255) default NULL,
193
  homepage varchar(255) default NULL,
Dries's avatar
 
Dries committed
194
  PRIMARY KEY  (cid)
Dries's avatar
 
Dries committed
195
);
Dries's avatar
 
Dries committed
196
CREATE INDEX comments_nid_idx ON comments(nid);
Dries's avatar
 
Dries committed
197

Dries's avatar
 
Dries committed
198 199 200 201 202 203 204 205 206 207 208 209 210 211
--
-- Table structre for table 'node_last_comment'
--

CREATE TABLE node_comment_statistics (
  nid integer NOT NULL,
  cid integer NOT NULL default '0',
  last_comment_timestamp integer NOT NULL default '0',
  last_comment_name varchar(60)  default NULL,
  last_comment_uid integer NOT NULL default '0',
  comment_count integer NOT NULL default '0',
  PRIMARY KEY (nid)
);

Dries's avatar
 
Dries committed
212 213 214
--
-- Table structure for directory
--
Dries's avatar
 
Dries committed
215

Dries's avatar
 
Dries committed
216 217 218 219
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
220 221
  slogan text NOT NULL default '',
  mission text NOT NULL default '',
Dries's avatar
 
Dries committed
222 223
  timestamp integer NOT NULL default '0',
  PRIMARY KEY  (link)
Dries's avatar
 
Dries committed
224
);
Dries's avatar
 
Dries committed
225

Dries's avatar
 
Dries committed
226 227 228 229 230
--
-- Table structure for table 'files'
--

CREATE TABLE files (
Dries's avatar
 
Dries committed
231 232
  fid serial,
  nid integer NOT NULL default '0',
Dries's avatar
 
Dries committed
233 234 235
  filename varchar(255) NOT NULL default '',
  filepath varchar(255) NOT NULL default '',
  filemime varchar(255) NOT NULL default '',
Dries's avatar
 
Dries committed
236 237
  filesize integer NOT NULL default '0',
  list smallint NOT NULL default '0',
Dries's avatar
 
Dries committed
238 239 240
  PRIMARY KEY  (fid)
);

Dries's avatar
 
Dries committed
241 242 243 244 245 246 247 248 249 250 251 252
--
-- 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
253 254 255 256 257
--
-- Table structure for table 'filters'
--

CREATE TABLE filters (
Dries's avatar
 
Dries committed
258 259 260 261
  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
262 263
);

Dries's avatar
 
Dries committed
264 265
CREATE INDEX filters_module_idx ON filters(module);

Dries's avatar
 
Dries committed
266 267 268 269 270 271 272 273 274 275
--
-- Table structure for table 'flood'
--

CREATE TABLE flood (
  event varchar(64) NOT NULL default '',
  hostname varchar(128) NOT NULL default '',
  timestamp int(11) NOT NULL default '0'
);

Dries's avatar
 
Dries committed
276 277 278 279 280 281
--
-- Table structure for table 'forum'
--

CREATE TABLE forum (
  nid integer NOT NULL default '0',
Dries's avatar
 
Dries committed
282
  tid integer NOT NULL default '0',
Dries's avatar
 
Dries committed
283 284 285
  shadow integer NOT NULL default '0',
  PRIMARY KEY  (nid)
);
Dries's avatar
 
Dries committed
286
CREATE INDEX forum_tid_idx ON forum(tid);
Dries's avatar
 
Dries committed
287

Dries's avatar
 
Dries committed
288 289 290
--
-- Table structure for history
--
Dries's avatar
 
Dries committed
291

Dries's avatar
 
Dries committed
292 293 294 295 296
CREATE TABLE history (
  uid integer NOT NULL default '0',
  nid integer NOT NULL default '0',
  timestamp integer NOT NULL default '0',
  PRIMARY KEY  (uid,nid)
Dries's avatar
 
Dries committed
297 298
);

Dries's avatar
 
Dries committed
299
--
Dries's avatar
 
Dries committed
300
-- Table structure for locales_meta
Dries's avatar
 
Dries committed
301
--
Dries's avatar
 
Dries committed
302

Dries's avatar
 
Dries committed
303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318
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 '',
  PRIMARY KEY  (locale)
);

--
-- Table structure for locales_source
--


CREATE TABLE locales_source (
Dries's avatar
 
Dries committed
319
lid serial,
320
  location text NOT NULL default '',
Dries's avatar
 
Dries committed
321
  source text NOT NULL,
Dries's avatar
 
Dries committed
322
  PRIMARY KEY  (lid)
Dries's avatar
 
Dries committed
323 324
);

Dries's avatar
 
Dries committed
325 326 327 328 329 330
--
-- Table structure for locales_target
--

CREATE TABLE locales_target (
  lid int4 NOT NULL default '0',
Dries's avatar
 
Dries committed
331
  translation text DEFAULT '' NOT NULL,
Dries's avatar
 
Dries committed
332 333 334
  locale varchar(12) NOT NULL default '',
  plid int4 NOT NULL default '0',
  plural int4 NOT NULL default '0',
335
    UNIQUE  (lid)
Dries's avatar
 
Dries committed
336 337
);

338 339 340 341
--
-- Table structure for table 'menu'
--

Dries's avatar
 
Dries committed
342

343
CREATE TABLE menu (
Dries's avatar
 
Dries committed
344
  mid serial,
345 346 347
  pid integer NOT NULL default '0',
  path varchar(255) NOT NULL default '',
  title varchar(255) NOT NULL default '',
Dries's avatar
 
Dries committed
348
  description varchar(255) NOT NULL default '',
349
  weight smallint NOT NULL default '0',
Dries's avatar
 
Dries committed
350
  type smallint NOT NULL default '0',
351 352
  PRIMARY KEY  (mid)
);
Dries's avatar
 
Dries committed
353
--
Dries's avatar
 
Dries committed
354
-- Table structure for table 'moderation_filters'
Dries's avatar
 
Dries committed
355
--
Dries's avatar
 
Dries committed
356

Dries's avatar
 
Dries committed
357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384
CREATE TABLE moderation_filters (
  fid SERIAL,
  filter varchar(255) NOT NULL default '',
  minimum smallint NOT NULL default '0',
  PRIMARY KEY  (fid)
);

--
-- 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',
  PRIMARY KEY  (mid)
Dries's avatar
 
Dries committed
385 386
);

Dries's avatar
 
Dries committed
387 388 389
--
-- Table structure for node
--
Dries's avatar
 
Dries committed
390

Dries's avatar
 
Dries committed
391 392 393 394 395 396 397 398 399 400 401 402
CREATE TABLE node (
  nid SERIAL,
  type varchar(16) NOT NULL default '',
  title varchar(128) NOT NULL default '',
  score integer NOT NULL default '0',
  votes integer NOT NULL default '0',
  uid integer NOT NULL default '0',
  status integer NOT NULL default '1',
  created integer NOT NULL default '0',
  comment integer NOT NULL default '0',
  promote integer NOT NULL default '0',
  moderate integer NOT NULL default '0',
Dries's avatar
 
Dries committed
403
  users text NOT NULL default '',
Dries's avatar
 
Dries committed
404 405
  teaser text NOT NULL default '',
  body text NOT NULL default '',
Dries's avatar
 
Dries committed
406
  changed integer NOT NULL default '0',
Dries's avatar
 
Dries committed
407
  revisions text NOT NULL default '',
Dries's avatar
 
Dries committed
408
  sticky integer NOT NULL default '0',
Dries's avatar
 
Dries committed
409
  format smallint NOT NULL default '0',
Dries's avatar
 
Dries committed
410
  PRIMARY KEY  (nid)
Dries's avatar
 
Dries committed
411
);
Dries's avatar
 
Dries committed
412 413
CREATE INDEX node_type_idx ON node(type);
CREATE INDEX node_title_idx ON node(title,type);
Dries's avatar
 
Dries committed
414 415
CREATE INDEX node_status_idx ON node(status);
CREATE INDEX node_uid_idx ON node(uid);
Dries's avatar
 
Dries committed
416 417
CREATE INDEX node_moderate_idx ON node (moderate);
CREATE INDEX node_promote_status_idx ON node (promote, status);
418 419
CREATE INDEX node_created ON node(created);
CREATE INDEX node_changed ON node(changed);
Dries's avatar
 
Dries committed
420

421 422 423 424 425 426 427 428 429 430 431 432
--
-- Table structure for table `node_access`
--

CREATE TABLE node_access (
  nid SERIAL,
  gid integer NOT NULL default '0',
  realm text NOT NULL default '',
  grant_view smallint NOT NULL default '0',
  grant_update smallint NOT NULL default '0',
  grant_delete smallint NOT NULL default '0',
  PRIMARY KEY  (nid,gid,realm)
Dries's avatar
 
Dries committed
433
);
434 435


Dries's avatar
 
Dries committed
436 437 438 439 440 441 442 443 444 445 446 447 448 449 450
--
-- 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',
  PRIMARY KEY  (nid)
);
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
451 452 453
--
-- Table structure for page
--
Dries's avatar
 
Dries committed
454

Dries's avatar
 
Dries committed
455 456 457 458
CREATE TABLE page (
  nid integer NOT NULL default '0',
  link varchar(128) NOT NULL default '',
  format smallint NOT NULL default '0',
Dries's avatar
 
Dries committed
459
  description varchar(128) NOT NULL default '',
Dries's avatar
 
Dries committed
460
  PRIMARY KEY  (nid)
Dries's avatar
 
Dries committed
461
);
Dries's avatar
 
Dries committed
462
CREATE INDEX page_nid_idx ON page(nid);
Dries's avatar
 
Dries committed
463

Dries's avatar
 
Dries committed
464
--
Dries's avatar
 
Dries committed
465
-- Table structure for table 'url_alias'
Dries's avatar
 
Dries committed
466 467
--

468 469 470 471 472 473 474 475 476 477
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
478
  register smallint DEFAULT '0' NOT NULL,
479
  visibility smallint DEFAULT '0' NOT NULL,
Dries's avatar
 
Dries committed
480
  overview smallint DEFAULT '0' NOT NULL,
481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498
  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
499 500
CREATE TABLE url_alias (
  pid serial,
Dries's avatar
 
Dries committed
501 502 503 504
  dst varchar(128) NOT NULL default '',
  src varchar(128) NOT NULL default '',
  PRIMARY KEY  (pid)
);
Dries's avatar
 
Dries committed
505
CREATE INDEX url_alias_dst_idx ON url_alias(dst);
Dries's avatar
 
Dries committed
506 507 508
--
-- Table structure for permission
--
Dries's avatar
 
Dries committed
509

Dries's avatar
 
Dries committed
510 511
CREATE TABLE permission (
  rid integer NOT NULL default '0',
Dries's avatar
 
Dries committed
512
  perm text default '',
Dries's avatar
 
Dries committed
513
  tid integer NOT NULL default '0'
Dries's avatar
 
Dries committed
514
);
Dries's avatar
 
Dries committed
515
CREATE INDEX permission_rid_idx ON permission(rid);
Dries's avatar
 
Dries committed
516

Dries's avatar
 
Dries committed
517 518 519
--
-- Table structure for poll
--
Dries's avatar
 
Dries committed
520

Dries's avatar
 
Dries committed
521 522 523
CREATE TABLE poll (
  nid integer NOT NULL default '0',
  runtime integer NOT NULL default '0',
Dries's avatar
 
Dries committed
524
  voters text NOT NULL default '',
Dries's avatar
 
Dries committed
525 526
  active integer NOT NULL default '0',
  PRIMARY KEY  (nid)
Dries's avatar
 
Dries committed
527
);
Dries's avatar
 
Dries committed
528

Dries's avatar
 
Dries committed
529 530 531
--
-- Table structure for poll_choices
--
Dries's avatar
 
Dries committed
532

Dries's avatar
 
Dries committed
533 534 535 536 537 538 539
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',
  PRIMARY KEY  (chid)
Dries's avatar
 
Dries committed
540
);
Dries's avatar
 
Dries committed
541
CREATE INDEX poll_choices_nid_idx ON poll_choices(nid);
Dries's avatar
 
Dries committed
542

Dries's avatar
 
Dries committed
543 544 545
--
-- Table structure for role
--
Dries's avatar
 
Dries committed
546

Dries's avatar
 
Dries committed
547 548 549 550 551 552
CREATE TABLE role (
  rid SERIAL,
  name varchar(32) NOT NULL default '',
  PRIMARY KEY  (rid),
  UNIQUE (name)
);
Dries's avatar
 
Dries committed
553

Dries's avatar
 
Dries committed
554 555 556
--
-- Table structure for search_index
--
Dries's avatar
 
Dries committed
557

Dries's avatar
 
Dries committed
558
CREATE TABLE search_index (
Dries's avatar
 
Dries committed
559 560 561 562
  word varchar(50) NOT NULL default '',
  lno integer NOT NULL default '0',
  type varchar(16) default NULL,
  count integer default NULL
Dries's avatar
 
Dries committed
563 564 565
);
CREATE INDEX search_index_lno_idx ON search_index(lno);
CREATE INDEX search_index_word_idx ON search_index(word);
Dries's avatar
 
Dries committed
566

Dries's avatar
 
Dries committed
567 568 569 570 571 572 573 574 575 576 577 578 579
--
-- Table structure for sessions
--

CREATE TABLE sessions (
  uid integer NOT NULL,
  sid varchar(32) NOT NULL default '',
  hostname varchar(128) NOT NULL default '',
  timestamp integer NOT NULL default '0',
  session text,
  PRIMARY KEY (sid)
);

Dries's avatar
 
Dries committed
580 581 582 583 584 585 586 587 588 589 590
--
-- 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
591 592 593
--
-- Table structure for system
--
Dries's avatar
 
Dries committed
594

Dries's avatar
 
Dries committed
595 596 597 598 599 600
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
601
  throttle smallint NOT NULL default '0',
Dries's avatar
 
Dries committed
602
  bootstrap integer NOT NULL default '0',
Dries's avatar
 
Dries committed
603 604
  PRIMARY KEY  (filename)
);
Dries's avatar
 
Dries committed
605

Dries's avatar
 
Dries committed
606 607 608
--
-- Table structure for term_data
--
Dries's avatar
 
Dries committed
609

Dries's avatar
 
Dries committed
610 611 612 613
CREATE TABLE term_data (
  tid SERIAL,
  vid integer NOT NULL default '0',
  name varchar(255) NOT NULL default '',
Dries's avatar
 
Dries committed
614
  description text default '',
Dries's avatar
 
Dries committed
615 616 617 618
  weight smallint NOT NULL default '0',
  PRIMARY KEY  (tid)
);
CREATE INDEX term_data_vid_idx ON term_data(vid);
Dries's avatar
 
Dries committed
619

Dries's avatar
 
Dries committed
620 621 622
--
-- Table structure for term_hierarchy
--
Dries's avatar
 
Dries committed
623

Dries's avatar
 
Dries committed
624 625 626 627 628 629
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
630

Dries's avatar
 
Dries committed
631 632 633
--
-- Table structure for term_node
--
Dries's avatar
 
Dries committed
634

Dries's avatar
 
Dries committed
635 636 637 638 639 640
CREATE TABLE term_node (
  nid integer NOT NULL default '0',
  tid integer NOT NULL default '0'
);
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
641

Dries's avatar
 
Dries committed
642 643 644
--
-- Table structure for term_relation
--
Dries's avatar
 
Dries committed
645

Dries's avatar
 
Dries committed
646 647 648 649 650 651
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
652

Dries's avatar
 
Dries committed
653 654 655
--
-- Table structure for term_synonym
--
Dries's avatar
 
Dries committed
656

Dries's avatar
 
Dries committed
657 658 659 660 661 662
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
663

Dries's avatar
 
Dries committed
664 665 666
--
-- Table structure for users
--
Dries's avatar
 
Dries committed
667

Dries's avatar
 
Dries committed
668
CREATE TABLE users (
Dries's avatar
 
Dries committed
669
  uid integer NOT NULL default '0',
Dries's avatar
 
Dries committed
670 671 672 673 674 675 676 677
  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
678 679
  created integer NOT NULL default '0',
  changed integer NOT NULL default '0',
Dries's avatar
 
Dries committed
680 681
  status smallint NOT NULL default '0',
  timezone varchar(8) default NULL,
Dries's avatar
 
Dries committed
682
  language varchar(12) NOT NULL default '',
Dries's avatar
 
Dries committed
683
  picture varchar(255) NOT NULL DEFAULT '',
Dries's avatar
 
Dries committed
684
  init varchar(64) default '',
Dries's avatar
 
Dries committed
685
  data text default '',
Dries's avatar
 
Dries committed
686 687 688
  PRIMARY KEY  (uid),
  UNIQUE (name)
);
689
CREATE INDEX users_changed_idx ON users(changed);
Dries's avatar
 
Dries committed
690

Dries's avatar
 
Dries committed
691 692
CREATE SEQUENCE users_uid_seq INCREMENT 1 START 1;

Dries's avatar
 
Dries committed
693 694 695 696 697 698 699 700 701 702
--
-- 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
703 704 705
--
-- Table structure for variable
--
Dries's avatar
 
Dries committed
706

Dries's avatar
 
Dries committed
707
CREATE TABLE variable (
Dries's avatar
 
Dries committed
708
  name varchar(48) NOT NULL default '',
Dries's avatar
 
Dries committed
709
  value text NOT NULL default '',
Dries's avatar
 
Dries committed
710 711
  PRIMARY KEY  (name)
);
Dries's avatar
 
Dries committed
712

Dries's avatar
 
Dries committed
713 714 715
--
-- Table structure for vocabulary
--
Dries's avatar
 
Dries committed
716

Dries's avatar
 
Dries committed
717 718 719
CREATE TABLE vocabulary (
  vid SERIAL,
  name varchar(255) NOT NULL default '',
Dries's avatar
 
Dries committed
720
  description text default '',
721
  help varchar(255) NOT NULL default '',
Dries's avatar
 
Dries committed
722 723 724 725
  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
726
  nodes text default '',
Dries's avatar
 
Dries committed
727 728 729
  weight smallint NOT NULL default '0',
  PRIMARY KEY  (vid)
);
Dries's avatar
 
Dries committed
730

Dries's avatar
 
Dries committed
731 732 733
--
-- Table structure for watchdog
--
Dries's avatar
 
Dries committed
734

Dries's avatar
 
Dries committed
735 736 737 738
CREATE TABLE watchdog (
  wid SERIAL,
  uid integer NOT NULL default '0',
  type varchar(16) NOT NULL default '',
Dries's avatar
 
Dries committed
739
  message text NOT NULL default '',
Dries's avatar
 
Dries committed
740
  link varchar(255) NOT NULL default '',
Dries's avatar
 
Dries committed
741 742 743 744 745
  location varchar(128) NOT NULL default '',
  hostname varchar(128) NOT NULL default '',
  timestamp integer NOT NULL default '0',
  PRIMARY KEY  (wid)
);
Dries's avatar
 
Dries committed
746

Dries's avatar
 
Dries committed
747 748 749
--
-- Insert some default values
--
Dries's avatar
 
Dries committed
750

Dries's avatar
 
Dries committed
751 752 753
INSERT INTO system VALUES ('modules/admin.module','admin','module','',1,0,0);
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
754
INSERT INTO system VALUES ('modules/filter.module','filter','module','',1,0,0);
Dries's avatar
 
Dries committed
755 756 757 758
INSERT INTO system VALUES ('modules/help.module','help','module','',1,0,0);
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
759
INSERT INTO system VALUES ('modules/system.module','system','module','',1,0,0);
Dries's avatar
 
Dries committed
760
INSERT INTO system VALUES ('modules/taxonomy.module','taxonomy','module','',1,0,0);
Dries's avatar
Dries committed
761 762
INSERT INTO system VALUES ('modules/user.module','user','module','',1,0,0);
INSERT INTO system VALUES ('modules/watchdog.module','watchdog','module','',1,0,0);
Dries's avatar
 
Dries committed
763 764
INSERT INTO system VALUES ('themes/bluemarine/xtemplate.xtmpl','bluemarine','theme','themes/engines/xtemplate/xtemplate.engine',1,0,0);
INSERT INTO system VALUES ('themes/engines/xtemplate/xtemplate.engine','xtemplate','theme_engine','',1,0,0);
Dries's avatar
 
Dries committed
765

766
INSERT INTO variable(name,value) VALUES('update_start', 's:10:"2004-10-18";');
Dries's avatar
 
Dries committed
767
INSERT INTO variable(name,value) VALUES('theme_default','s:10:"bluemarine";');
768 769
INSERT INTO users(uid,name,mail) VALUES(0,'','');
INSERT INTO users_roles(uid,rid) VALUES(0, 1);
Dries's avatar
 
Dries committed
770

Dries's avatar
 
Dries committed
771
INSERT INTO role (name) VALUES ('anonymous user');
Dries's avatar
 
Dries committed
772
INSERT INTO permission VALUES (1,'access content',0);
Dries's avatar
 
Dries committed
773

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

Dries's avatar
 
Dries committed
777 778
INSERT INTO blocks(module,delta,status) VALUES('user', '0', '1');
INSERT INTO blocks(module,delta,status) VALUES('user', '1', '1');
Dries's avatar
 
Dries committed
779

780 781
INSERT INTO node_access VALUES (0, 0, 'all', 1, 0, 0);

Dries's avatar
 
Dries committed
782 783 784
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);
785
INSERT INTO filters VALUES (1,'filter',0,0);
786
INSERT INTO filters VALUES (1,'filter',2,1);
787
INSERT INTO filters VALUES (2,'filter',1,0);
788
INSERT INTO filters VALUES (3,'filter',2,0);
789 790
INSERT INTO variable (name,value) VALUES ('filter_html_1','i:1;');

Dries's avatar
 
Dries committed
791
INSERT INTO locales_meta(locale, name, enabled, isdefault) VALUES('en', 'English', '1', '1');
792

Dries's avatar
 
Dries committed
793 794 795 796 797 798
---
--- Alter some sequences
---
ALTER SEQUENCE menu_mid_seq RESTART 2;


Dries's avatar
 
Dries committed
799 800 801 802
---
--- Functions
---

803
CREATE FUNCTION greatest(integer, integer) RETURNS integer AS '
Dries's avatar
 
Dries committed
804
BEGIN
805 806 807
  IF $2 IS NULL THEN
    RETURN $1;
  END IF;
Dries's avatar
 
Dries committed
808 809 810 811 812 813 814
  IF $1 > $2 THEN
    RETURN $1;
  END IF;
  RETURN $2;
END;
' LANGUAGE 'plpgsql';

Dries's avatar
 
Dries committed
815 816 817 818 819
CREATE FUNCTION "rand"() RETURNS float AS '
BEGIN
  RETURN random();
END;
' LANGUAGE 'plpgsql';
820 821 822 823 824 825

CREATE FUNCTION "concat"(text, text) RETURNS text AS '
BEGIN
  RETURN $1 || $2;
END;
' LANGUAGE 'plpgsql';
Dries's avatar
 
Dries committed
826 827 828 829 830 831 832 833 834 835 836 837

CREATE FUNCTION "if"(integer, text, text) RETURNS text AS '
BEGIN
  IF $1 THEN
    RETURN $2;
  END IF;
  IF NOT $1 THEN
    RETURN $3;
  END IF;
END;
' LANGUAGE 'plpgsql';