database.pgsql 14.3 KB
Newer Older
1 2 3
-- PostgreSQL include file 31/10/2002
-- Maintainer: James Arthur, j_a_arthurATyahooDOTcom

4 5 6
--
-- Table structure for access
--
7

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

17 18 19 20 21 22 23 24 25 26 27
--
-- Table structure for accesslog
--

CREATE TABLE accesslog (
  nid integer default '0',
  url varchar(255) default NULL,
  hostname varchar(128) default NULL,
  uid integer default '0',
  timestamp integer NOT NULL default '0'
);
28
CREATE INDEX accesslog_timestamp_idx ON accesslog (timestamp);
29

30 31 32
--
-- Table structure for authmap
--
33

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)
41 42
);

43 44 45
--
-- Table structure for blocks
--
46

47 48
CREATE TABLE blocks (
  module varchar(64) NOT NULL default '',
49
  delta varchar(32) NOT NULL default '0',
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 '',
54
  custom smallint NOT NULL default '0'
55 56
);

57 58 59
--
-- Table structure for book
--
60

61 62 63 64 65
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',
66
  log text default '',
67
  PRIMARY KEY (nid)
68
);
69
CREATE INDEX book_nid_idx ON book(nid);
70
CREATE INDEX book_parent ON book(parent);
71

72 73 74
--
-- Table structure for boxes
--
75

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

87 88 89
--
-- Table structure for bundle
--
90

91 92 93 94 95 96
CREATE TABLE bundle (
  bid SERIAL,
  title varchar(255) NOT NULL default '',
  attributes varchar(255) NOT NULL default '',
  PRIMARY KEY  (bid),
  UNIQUE (title)
97 98
);

99 100 101
--
-- Table structure for cache
--
102

103 104
CREATE TABLE cache (
  cid varchar(255) NOT NULL default '',
105
  data text default '',
106
  expire integer NOT NULL default '0',
Dries's avatar
Dries committed
107
  created integer NOT NULL default '0',
108
  PRIMARY KEY  (cid)
109 110
);

111 112 113
--
-- Table structure for comments
--
114

115 116 117 118 119 120
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 '',
121
  comment text NOT NULL default '',
122 123 124
  hostname varchar(128) NOT NULL default '',
  timestamp integer NOT NULL default '0',
  link varchar(16) NOT NULL default '',
125 126
  score integer NOT NULL default '0',
  status smallint  NOT NULL default '0',
127
  thread varchar(255) default '',
128
  users text default '',
129
  PRIMARY KEY  (cid)
130
);
131
CREATE INDEX comments_nid_idx ON comments(nid);
132

133 134 135
--
-- Table structure for directory
--
136

137 138 139 140
CREATE TABLE directory (
  link varchar(255) NOT NULL default '',
  name varchar(128) NOT NULL default '',
  mail varchar(128) NOT NULL default '',
141 142
  slogan text NOT NULL default '',
  mission text NOT NULL default '',
143 144
  timestamp integer NOT NULL default '0',
  PRIMARY KEY  (link)
145
);
146

147 148 149
--
-- Table structure for feed
--
150

151 152 153 154
CREATE TABLE feed (
  fid SERIAL,
  title varchar(255) NOT NULL default '',
  url varchar(255) NOT NULL default '',
155 156
  refresh integer NOT NULL default '0',
  timestamp integer NOT NULL default '0',
157 158
  attributes varchar(255) NOT NULL default '',
  link varchar(255) NOT NULL default '',
159
  description text NOT NULL default '',
160 161 162
  PRIMARY KEY  (fid),
  UNIQUE (title),
  UNIQUE (url)
163 164
);

165 166 167 168 169 170
--
-- Table structure for table 'forum'
--

CREATE TABLE forum (
  nid integer NOT NULL default '0',
Dries's avatar
Dries committed
171
  tid integer NOT NULL default '0',
172 173 174 175
  icon varchar(255) NOT NULL default '',
  shadow integer NOT NULL default '0',
  PRIMARY KEY  (nid)
);
Dries's avatar
Dries committed
176
CREATE INDEX forum_tid_idx ON forum(tid);
177

178 179 180
--
-- Table structure for history
--
181

182 183 184 185 186
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)
187 188
);

189 190 191
--
-- Table structure for item
--
192

193 194 195 196 197 198
CREATE TABLE item (
  iid SERIAL,
  fid integer NOT NULL default '0',
  title varchar(255) NOT NULL default '',
  link varchar(255) NOT NULL default '',
  author varchar(255) NOT NULL default '',
199
  description text NOT NULL default '',
200
  timestamp integer NOT NULL default '0',
201 202
  attributes varchar(255) NOT NULL default '',
  PRIMARY KEY  (iid)
203 204
);

205 206 207
--
-- Table structure for locales
--
208

209 210 211
CREATE TABLE locales (
  lid SERIAL,
  location varchar(128) NOT NULL default '',
212 213 214 215 216 217 218 219 220
  string text NOT NULL default '',
  da text NOT NULL default '',
  fi text NOT NULL default '',
  fr text NOT NULL default '',
  en text NOT NULL default '',
  es text NOT NULL default '',
  nl text NOT NULL default '',
  no text NOT NULL default '',
  sw text NOT NULL default '',
221
  PRIMARY KEY  (lid)
222 223
);

224
--
225
-- Table structure for table 'moderation_filters'
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 251 252 253 254 255
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)
256 257
);

258 259 260
--
-- Table structure for node
--
261

262 263 264 265 266 267 268 269 270 271 272 273
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',
274
  users text NOT NULL default '',
275
  attributes varchar(255) NOT NULL default '',
276 277
  teaser text NOT NULL default '',
  body text NOT NULL default '',
278
  changed integer NOT NULL default '0',
279
  revisions text NOT NULL default '',
280 281
  static integer NOT NULL default '0',
  PRIMARY KEY  (nid)
282
);
283 284
CREATE INDEX node_type_idx ON node(type);
CREATE INDEX node_title_idx ON node(title,type);
285 286
CREATE INDEX node_status_idx ON node(status);
CREATE INDEX node_uid_idx ON node(uid);
287 288
CREATE INDEX node_moderate_idx ON node (moderate);
CREATE INDEX node_promote_status_idx ON node (promote, status);
289

290 291 292 293 294 295 296 297 298 299 300 301 302 303 304
--
-- 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);

305 306 307
--
-- Table structure for page
--
308

309 310 311 312
CREATE TABLE page (
  nid integer NOT NULL default '0',
  link varchar(128) NOT NULL default '',
  format smallint NOT NULL default '0',
313
  description varchar(128) NOT NULL default '',
314
  PRIMARY KEY  (nid)
315
);
316
CREATE INDEX page_nid_idx ON page(nid);
317

318
--
319
-- Table structure for table 'url_alias'
320 321
--

322 323
CREATE TABLE url_alias (
  pid serial,
324 325 326 327
  dst varchar(128) NOT NULL default '',
  src varchar(128) NOT NULL default '',
  PRIMARY KEY  (pid)
);
328 329
CREATE INDEX url_alias_src_idx ON url_alias(src);
CREATE INDEX url_alias_dst_idx ON url_alias(dst);
330 331 332
--
-- Table structure for permission
--
333

334 335
CREATE TABLE permission (
  rid integer NOT NULL default '0',
336
  perm text default '',
337
  tid integer NOT NULL default '0'
338
);
339
CREATE INDEX permission_rid_idx ON permission(rid);
340

341 342 343
--
-- Table structure for poll
--
344

345 346 347
CREATE TABLE poll (
  nid integer NOT NULL default '0',
  runtime integer NOT NULL default '0',
348
  voters text NOT NULL default '',
349 350
  active integer NOT NULL default '0',
  PRIMARY KEY  (nid)
351
);
352

353 354 355
--
-- Table structure for poll_choices
--
356

357 358 359 360 361 362 363
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)
364
);
365
CREATE INDEX poll_choices_nid_idx ON poll_choices(nid);
366

367 368 369
--
-- Table structure for role
--
370

371 372 373 374 375 376
CREATE TABLE role (
  rid SERIAL,
  name varchar(32) NOT NULL default '',
  PRIMARY KEY  (rid),
  UNIQUE (name)
);
377

378 379 380
--
-- Table structure for search_index
--
381

382
CREATE TABLE search_index (
383 384 385 386
  word varchar(50) NOT NULL default '',
  lno integer NOT NULL default '0',
  type varchar(16) default NULL,
  count integer default NULL
387 388 389
);
CREATE INDEX search_index_lno_idx ON search_index(lno);
CREATE INDEX search_index_word_idx ON search_index(word);
390

391 392 393 394 395 396 397 398 399 400 401 402 403
--
-- 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)
);

404 405 406 407 408 409 410 411 412 413 414
--
-- 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)
-- );

415 416 417
--
-- Table structure for site
--
418

419 420 421 422
CREATE TABLE site (
  sid SERIAL,
  name varchar(128) NOT NULL default '',
  link varchar(255) NOT NULL default '',
423
  size text NOT NULL default '',
424 425
  changed integer NOT NULL default '0',
  checked integer NOT NULL default '0',
426 427 428 429 430 431 432
  feed varchar(255) NOT NULL default '',
  refresh integer NOT NULL default '0',
  threshold integer NOT NULL default '0',
  PRIMARY KEY  (sid),
  UNIQUE (name),
  UNIQUE (link)
);
433

434

435 436 437
--
-- Table structure for system
--
438

439 440 441 442 443 444 445 446
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',
  PRIMARY KEY  (filename)
);
447

448

449

450 451 452
--
-- Table structure for term_data
--
453

454 455 456 457
CREATE TABLE term_data (
  tid SERIAL,
  vid integer NOT NULL default '0',
  name varchar(255) NOT NULL default '',
458
  description text default '',
459 460 461 462
  weight smallint NOT NULL default '0',
  PRIMARY KEY  (tid)
);
CREATE INDEX term_data_vid_idx ON term_data(vid);
463

464 465 466
--
-- Table structure for term_hierarchy
--
467

468 469 470 471 472 473
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);
474

475 476 477
--
-- Table structure for term_node
--
478

479 480 481 482 483 484
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);
485

486 487 488
--
-- Table structure for term_relation
--
489

490 491 492 493 494 495
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);
496

497 498 499
--
-- Table structure for term_synonym
--
500

501 502 503 504 505 506
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);
507

508 509 510
--
-- Table structure for users
--
511

512
CREATE TABLE users (
513
  uid integer NOT NULL default '0',
514 515 516 517 518 519 520 521 522 523 524 525 526
  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 '',
  timestamp integer NOT NULL default '0',
  status smallint NOT NULL default '0',
  timezone varchar(8) default NULL,
  language char(2) NOT NULL default '',
  init varchar(64) default '',
527
  data text default '',
528 529 530 531
  rid integer NOT NULL default '0',
  PRIMARY KEY  (uid),
  UNIQUE (name)
);
532
CREATE INDEX users_timestamp_idx ON users(timestamp);
533

534 535
CREATE SEQUENCE users_uid_seq INCREMENT 1 START 1;

536 537 538
--
-- Table structure for variable
--
539

540
CREATE TABLE variable (
541
  name varchar(48) NOT NULL default '',
542
  value text NOT NULL default '',
543 544
  PRIMARY KEY  (name)
);
545

546 547 548
--
-- Table structure for vocabulary
--
549

550 551 552
CREATE TABLE vocabulary (
  vid SERIAL,
  name varchar(255) NOT NULL default '',
553
  description text default '',
554 555 556 557
  relations smallint NOT NULL default '0',
  hierarchy smallint NOT NULL default '0',
  multiple smallint NOT NULL default '0',
  required smallint NOT NULL default '0',
558
  nodes text default '',
559 560 561
  weight smallint NOT NULL default '0',
  PRIMARY KEY  (vid)
);
562

563 564 565
--
-- Table structure for watchdog
--
566

567 568 569 570
CREATE TABLE watchdog (
  wid SERIAL,
  uid integer NOT NULL default '0',
  type varchar(16) NOT NULL default '',
571
  message text NOT NULL default '',
572
  link varchar(255) NOT NULL default '',
573 574 575 576 577
  location varchar(128) NOT NULL default '',
  hostname varchar(128) NOT NULL default '',
  timestamp integer NOT NULL default '0',
  PRIMARY KEY  (wid)
);
578

579 580 581
--
-- Insert some default values
--
582

583
INSERT INTO system VALUES ('modules/admin.module','admin','module','',1);
584 585 586 587 588 589 590
INSERT INTO system VALUES ('modules/block.module','block','module','',1);
INSERT INTO system VALUES ('modules/comment.module','comment','module','',1);
INSERT INTO system VALUES ('modules/help.module','help','module','',1);
INSERT INTO system VALUES ('modules/node.module','node','module','',1);
INSERT INTO system VALUES ('modules/page.module','page','module','',1);
INSERT INTO system VALUES ('modules/story.module','story','module','',1);
INSERT INTO system VALUES ('modules/taxonomy.module','taxonomy','module','',1);
591 592
INSERT INTO system VALUES ('themes/marvin/marvin.theme','marvin','theme','Internet explorer, Netscape, Opera',1);

593
INSERT INTO variable(name,value) VALUES('update_start', 's:10:"2003-10-27";');
594
INSERT INTO variable(name,value) VALUES('theme_default','s:6:"marvin";');
595
INSERT INTO users(uid,name,mail,rid) VALUES(0,'','', '1');
596

597 598
INSERT INTO blocks(module,delta,status) VALUES('user', '0', '1');
INSERT INTO blocks(module,delta,status) VALUES('user', '1', '1');
599

600 601 602 603 604 605 606 607 608 609 610 611 612
---
--- Functions
---

CREATE FUNCTION "greatest"(integer, integer) RETURNS integer AS '
BEGIN
  IF $1 > $2 THEN
    RETURN $1;
  END IF;
  RETURN $2;
END;
' LANGUAGE 'plpgsql';

613 614 615 616 617
CREATE FUNCTION "rand"() RETURNS float AS '
BEGIN
  RETURN random();
END;
' LANGUAGE 'plpgsql';