-
Notifications
You must be signed in to change notification settings - Fork 59
/
houdini.sql
11677 lines (11401 loc) · 890 KB
/
houdini.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
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
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
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
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
DROP TABLE IF EXISTS item;
CREATE TABLE item (
id INT NOT NULL,
name VARCHAR(50),
type SMALLINT NOT NULL DEFAULT 1,
cost INT NOT NULL DEFAULT 0,
member BOOLEAN NOT NULL DEFAULT FALSE,
bait BOOLEAN NOT NULL DEFAULT FALSE,
patched BOOLEAN NOT NULL DEFAULT FALSE,
legacy_inventory BOOLEAN NOT NULL DEFAULT FALSE,
vanilla_inventory BOOLEAN NOT NULL DEFAULT FALSE,
epf BOOLEAN NOT NULL DEFAULT FALSE,
tour BOOLEAN NOT NULL DEFAULT FALSE,
release_date DATE NOT NULL,
treasure BOOLEAN NOT NULL DEFAULT FALSE,
innocent BOOLEAN NOT NULL DEFAULT FALSE,
PRIMARY KEY (id)
);
ALTER TABLE item ALTER COLUMN release_date SET DEFAULT now();
COMMENT ON TABLE item IS 'Server item crumbs';
COMMENT ON COLUMN item.id IS 'Unique item ID';
COMMENT ON COLUMN item.name IS 'Item name';
COMMENT ON COLUMN item.type IS 'Item clothing type';
COMMENT ON COLUMN item.cost IS 'Cost of item';
COMMENT ON COLUMN item.member IS 'Is member-only?';
COMMENT ON COLUMN item.bait IS 'Is bait item?';
COMMENT ON COLUMN item.patched IS 'Is item patched?';
COMMENT ON COLUMN item.legacy_inventory IS 'Add to default legacy inventory?';
COMMENT ON COLUMN item.vanilla_inventory IS 'Add to default vanilla inventory?';
COMMENT ON COLUMN item.epf IS 'Is EPF item?';
COMMENT ON COLUMN item.tour IS 'Gives tour status?';
COMMENT ON COLUMN item.release_date IS 'Release date of item';
COMMENT ON COLUMN item.treasure IS 'Is a treasure item?';
COMMENT ON COLUMN item.innocent IS 'Is a innocent item?';
DROP TABLE IF EXISTS postcard;
CREATE TABLE postcard (
id INT NOT NULL,
name VARCHAR (50) NOT NULL,
cost INT NOT NULL DEFAULT 10,
enabled BOOLEAN NOT NULL DEFAULT FALSE,
PRIMARY KEY (id)
);
COMMENT ON TABLE postcard IS 'Server postcard crumbs';
COMMENT ON COLUMN postcard.id IS 'Unique postcard ID';
COMMENT ON COLUMN postcard.name IS 'Postcard name';
COMMENT ON COLUMN postcard.cost IS 'Cost of postcard';
COMMENT ON COLUMN postcard.enabled IS 'Can send postcard?';
DROP TABLE IF EXISTS igloo;
CREATE TABLE igloo (
id INT NOT NULL,
name VARCHAR(50) NOT NULL,
cost SMALLINT NOT NULL DEFAULT 0,
patched BOOLEAN NOT NULL DEFAULT FALSE,
legacy_inventory BOOLEAN NOT NULL DEFAULT FALSE,
vanilla_inventory BOOLEAN NOT NULL DEFAULT FALSE,
PRIMARY KEY(id)
);
COMMENT ON TABLE igloo IS 'Server igloo crumbs';
COMMENT ON COLUMN igloo.id IS 'Unique igloo ID';
COMMENT ON COLUMN igloo.name IS 'Igloo name';
COMMENT ON COLUMN igloo.cost IS 'Cost of igloo';
COMMENT ON COLUMN igloo.patched IS 'Is igloo patched?';
COMMENT ON COLUMN igloo.legacy_inventory IS 'Add to default legacy inventory?';
COMMENT ON COLUMN igloo.vanilla_inventory IS 'Add to default vanilla inventory?';
DROP TABLE IF EXISTS location;
CREATE TABLE location (
id INT NOT NULL,
name VARCHAR(50) NOT NULL,
cost INT NOT NULL DEFAULT 0,
patched BOOLEAN NOT NULL DEFAULT FALSE,
legacy_inventory BOOLEAN NOT NULL DEFAULT FALSE,
vanilla_inventory BOOLEAN NOT NULL DEFAULT FALSE,
PRIMARY KEY (id)
);
COMMENT ON TABLE location IS 'Server location crumbs';
COMMENT ON COLUMN location.id IS 'Unique location ID';
COMMENT ON COLUMN location.name IS 'Location name';
COMMENT ON COLUMN location.cost IS 'Cost of location';
COMMENT ON COLUMN location.patched IS 'Is location patched?';
COMMENT ON COLUMN location.legacy_inventory IS 'Add to default legacy inventory?';
COMMENT ON COLUMN location.vanilla_inventory IS 'Add to default vanilla inventory?';
DROP TABLE IF EXISTS furniture;
CREATE TABLE furniture (
id INT NOT NULL,
name VARCHAR(50) NOT NULL,
type SMALLINT NOT NULL DEFAULT 1,
sort SMALLINT NOT NULL DEFAULT 1,
cost INT NOT NULL DEFAULT 0,
member BOOLEAN NOT NULL DEFAULT FALSE,
patched BOOLEAN NOT NULL DEFAULT FALSE,
legacy_inventory BOOLEAN NOT NULL DEFAULT FALSE,
vanilla_inventory BOOLEAN NOT NULL DEFAULT FALSE,
bait BOOLEAN NOT NULL DEFAULT FALSE,
max_quantity SMALLINT NOT NULL DEFAULT 100,
innocent BOOLEAN NOT NULL DEFAULT FALSE,
PRIMARY KEY(id)
);
COMMENT ON TABLE furniture IS 'Server furniture crumbs';
COMMENT ON COLUMN furniture.id IS 'Unique furniture ID';
COMMENT ON COLUMN furniture.type IS 'Furniture type ID';
COMMENT ON COLUMN furniture.sort IS 'Furniture sort ID';
COMMENT ON COLUMN furniture.cost IS 'Cost of furniture';
COMMENT ON COLUMN furniture.member IS 'Is member-only?';
COMMENT ON COLUMN furniture.patched IS 'Is furniture patched?';
COMMENT ON COLUMN furniture.legacy_inventory IS 'Add to default legacy inventory?';
COMMENT ON COLUMN furniture.vanilla_inventory IS 'Add to default vanilla inventory?';
COMMENT ON COLUMN furniture.bait IS 'Is furniture bait?';
COMMENT ON COLUMN furniture.max_quantity IS 'Max inventory quantity';
COMMENT ON COLUMN furniture.innocent IS 'Is furniture innocent?';
DROP TABLE IF EXISTS flooring;
CREATE TABLE flooring (
id INT NOT NULL,
name VARCHAR(50),
cost INT NOT NULL DEFAULT 0,
patched BOOLEAN NOT NULL DEFAULT FALSE,
legacy_inventory BOOLEAN NOT NULL DEFAULT FALSE,
vanilla_inventory BOOLEAN NOT NULL DEFAULT FALSE,
PRIMARY KEY (id)
);
COMMENT ON TABLE flooring IS 'Server flooring crumbs';
COMMENT ON COLUMN flooring.id IS 'Unique flooring ID';
COMMENT ON COLUMN flooring.name IS 'Flooring name';
COMMENT ON COLUMN flooring.cost IS 'Cost of flooring';
COMMENT ON COLUMN flooring.patched IS 'Is flooring patched?';
COMMENT ON COLUMN flooring.legacy_inventory IS 'Add to default legacy inventory?';
COMMENT ON COLUMN flooring.vanilla_inventory IS 'Add to default vanilla inventory?';
DROP TABLE IF EXISTS card;
CREATE TABLE card (
id INT NOT NULL,
name VARCHAR(50) NOT NULL,
set_id SMALLINT NOT NULL DEFAULT 1,
power_id SMALLINT NOT NULL DEFAULT 0,
element CHAR(1) NOT NULL DEFAULT 's',
color CHAR(1) NOT NULL DEFAULT 'b',
value SMALLINT NOT NULL DEFAULT 2,
description VARCHAR(255) NOT NULL DEFAULT '',
PRIMARY KEY (id)
);
COMMENT ON TABLE card IS 'Server jitsu card crumbs';
COMMENT ON COLUMN card.id IS 'Unique card ID';
COMMENT ON COLUMN card.name IS 'Card name';
COMMENT ON COLUMN card.set_id IS 'Card set ID';
COMMENT ON COLUMN card.power_id IS 'Card power ID';
COMMENT ON COLUMN card.element IS 'Card element';
COMMENT ON COLUMN card.color IS 'Card color';
COMMENT ON COLUMN card.value IS 'Value of card';
COMMENT ON COLUMN card.description IS 'Play description';
DROP TABLE IF EXISTS card_starter_deck;
CREATE TABLE card_starter_deck (
item_id INT NOT NULL,
card_id INT NOT NULL,
quantity SMALLINT NOT NULL DEFAULT 1,
PRIMARY KEY (item_id, card_id),
CONSTRAINT card_starter_deck_ibfk_1 FOREIGN KEY (item_id) REFERENCES item (id) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT card_starter_deck_ibfk_2 FOREIGN KEY (card_id) REFERENCES card(id) ON DELETE CASCADE ON UPDATE CASCADE
);
COMMENT ON TABLE card_starter_deck IS 'Jitsu card starter decks';
COMMENT ON COLUMN card_starter_deck.item_id IS 'Starter deck item ID';
COMMENT ON COLUMN card_starter_deck.card_id IS 'Starter deck card ID';
COMMENT ON COLUMN card_starter_deck.quantity IS 'Card quantity';
DROP TABLE IF EXISTS stamp_group;
CREATE TABLE stamp_group (
id INT NOT NULL,
name VARCHAR(50) NOT NULL,
parent_id INT DEFAULT NULL,
PRIMARY KEY (id),
CONSTRAINT stamp_group_ibfk_1 FOREIGN KEY (parent_id) REFERENCES stamp_group (id) ON DELETE CASCADE ON UPDATE CASCADE
);
COMMENT ON TABLE stamp_group IS 'Stamp group collections';
COMMENT ON COLUMN stamp_group.id IS 'Unique stamp group ID';
COMMENT ON COLUMN stamp_group.name IS 'Name of stamp group';
COMMENT ON COLUMN stamp_group.parent_id IS 'Parent stamp group ID';
DROP TABLE IF EXISTS stamp;
CREATE TABLE stamp (
id INT NOT NULL,
name VARCHAR(50) NOT NULL,
group_id SMALLINT NOT NULL,
member BOOLEAN NOT NULL DEFAULT FALSE,
rank SMALLINT NOT NULL DEFAULT 1,
description VARCHAR(255) NOT NULL DEFAULT '',
PRIMARY KEY(id),
CONSTRAINT stamp_ibfk_1 FOREIGN KEY (group_id) REFERENCES stamp_group (id) ON DELETE CASCADE ON UPDATE CASCADE
);
COMMENT ON TABLE stamp IS 'Server stamp crumbs';
COMMENT ON COLUMN stamp.id IS 'Unique stamp ID';
COMMENT ON COLUMN stamp.name IS 'Stamp name';
COMMENT ON COLUMN stamp.group_id IS 'Stamp group ID';
COMMENT ON COLUMN stamp.member IS 'Is member-only?';
COMMENT ON COLUMN stamp.rank IS 'Stamp difficulty ranking';
COMMENT ON COLUMN stamp.description IS 'Stamp description';
DROP TABLE IF EXISTS room;
CREATE TABLE room (
id INT NOT NULL,
internal_id SERIAL NOT NULL,
name VARCHAR(50) NOT NULL,
member BOOLEAN NOT NULL DEFAULT FALSE,
max_users SMALLINT NOT NULL DEFAULT 80,
required_item INT DEFAULT NULL,
game BOOLEAN NOT NULL DEFAULT FALSE,
blackhole BOOLEAN NOT NULL DEFAULT FALSE,
spawn BOOLEAN NOT NULL DEFAULT FALSE,
stamp_group INT DEFAULT NULL,
PRIMARY KEY(id),
CONSTRAINT room_ibfk_1 FOREIGN KEY (required_item) REFERENCES item (id) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT room_ibfk_2 FOREIGN KEY (stamp_group) REFERENCES stamp_group (id) ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE UNIQUE INDEX room_internal_id ON room (internal_id);
COMMENT ON TABLE room IS 'Server room crumbs';
COMMENT ON COLUMN room.id IS 'Unique room ID';
COMMENT ON COLUMN room.internal_id IS 'Internal room key';
COMMENT ON COLUMN room.name IS 'Room name';
COMMENT ON COLUMN room.member IS 'Is member-only?';
COMMENT ON COLUMN room.max_users IS 'Maximum room users';
COMMENT ON COLUMN room.required_item IS 'Required inventory item';
COMMENT ON COLUMN room.game IS 'Is game room?';
COMMENT ON COLUMN room.blackhole IS 'Is blackhole game room?';
COMMENT ON COLUMN room.spawn IS 'Is spawn room?';
DROP TABLE IF EXISTS character;
CREATE TABLE character (
id INT NOT NULL,
name VARCHAR (30) NOT NULL,
gift_id INT DEFAULT NULL,
stamp_id INT DEFAULT NULL,
PRIMARY KEY (id),
CONSTRAINT character_ibfk_1 FOREIGN KEY (gift_id) REFERENCES item (id) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT character_ibfk_2 FOREIGN KEY (stamp_id) REFERENCES stamp (id) ON DELETE CASCADE ON UPDATE CASCADE
);
COMMENT ON TABLE character IS 'Server character crumbs';
COMMENT ON COLUMN character.id IS 'Unique character ID';
COMMENT ON COLUMN character.name IS 'Character name';
COMMENT ON COLUMN character.gift_id IS 'Character gift item ID';
COMMENT ON COLUMN character.stamp_id IS 'Character stamp ID';
DROP TABLE IF EXISTS epf_com_message;
CREATE TABLE epf_com_message (
message TEXT NOT NULL,
character_id INT NOT NULL,
date TIMESTAMP NOT NULL,
CONSTRAINT epf_com_message_ibfk_1 FOREIGN KEY (character_id) REFERENCES character(id) ON DELETE CASCADE ON UPDATE CASCADE
);
COMMENT ON TABLE epf_com_message IS 'EPF phone com messages';
COMMENT ON COLUMN epf_com_message.message IS 'Message content';
COMMENT ON COLUMN epf_com_message.character_id IS 'Character ID of message';
COMMENT ON COLUMN epf_com_message.date IS 'Date of message creation';
ALTER TABLE epf_com_message ALTER COLUMN date SET DEFAULT now();
DROP TABLE IF EXISTS puffle_item;
CREATE TABLE puffle_item (
id INT NOT NULL,
parent_id INT NOT NULL,
name VARCHAR(50) NOT NULL DEFAULT '',
type VARCHAR(10) NOT NULL DEFAULT 'care',
play_external VARCHAR (10) NOT NULL DEFAULT 'none',
cost INT NOT NULL DEFAULT 0,
quantity SMALLINT NOT NULL DEFAULT 1,
member BOOLEAN NOT NULL DEFAULT FALSE,
food_effect SMALLINT NOT NULL DEFAULT 0,
rest_effect SMALLINT NOT NULL DEFAULT 0,
play_effect SMALLINT NOT NULL DEFAULT 0,
clean_effect SMALLINT NOT NULL DEFAULT 0,
PRIMARY KEY (id),
CONSTRAINT puffle_item_ibfk_1 FOREIGN KEY (parent_id) REFERENCES puffle_item (id) ON DELETE CASCADE ON UPDATE CASCADE
);
COMMENT ON TABLE puffle_item IS 'Server puffle care item crumbs';
COMMENT ON COLUMN puffle_item.id IS 'Unique care item ID';
COMMENT ON COLUMN puffle_item.parent_id IS 'Parent care item ID';
COMMENT ON COLUMN puffle_item.name IS 'Care item name';
COMMENT ON COLUMN puffle_item.type IS 'Type of care item';
COMMENT ON COLUMN puffle_item.play_external IS 'External play mode';
COMMENT ON COLUMN puffle_item.cost IS 'Cost of care item';
COMMENT ON COLUMN puffle_item.quantity IS 'Base quantity of purchase';
COMMENT ON COLUMN puffle_item.member IS 'Is member-only?';
COMMENT ON COLUMN puffle_item.food_effect IS 'Effect on puffle food level';
COMMENT ON COLUMN puffle_item.rest_effect IS 'Effect on puffle rest level';
COMMENT ON COLUMN puffle_item.play_effect IS 'Effect on puffle play level';
COMMENT ON COLUMN puffle_item.clean_effect IS 'Effect on puffle clean level';
DROP TABLE IF EXISTS puffle;
CREATE TABLE puffle (
id INT NOT NULL,
parent_id SMALLINT DEFAULT NULL,
name VARCHAR(50) NOT NULL DEFAULT '',
cost INT NOT NULL DEFAULT 0,
member BOOLEAN NOT NULL DEFAULT FALSE,
favourite_food SMALLINT NOT NULL,
favourite_toy SMALLINT DEFAULT NULL,
runaway_postcard SMALLINT DEFAULT NULL,
PRIMARY KEY (id),
CONSTRAINT puffle_ibfk_1 FOREIGN KEY (parent_id) REFERENCES puffle (id) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT puffle_ibfk_2 FOREIGN KEY (favourite_food) REFERENCES puffle_item (id) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT puffle_ibfk_3 FOREIGN KEY (favourite_toy) REFERENCES puffle_item (id) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT puffle_ibfk_4 FOREIGN KEY (runaway_postcard) REFERENCES postcard (id) ON DELETE CASCADE ON UPDATE CASCADE
);
COMMENT ON TABLE puffle IS 'Server puffle crumbs';
COMMENT ON COLUMN puffle.id IS 'Unique puffle ID';
COMMENT ON COLUMN puffle.parent_id IS 'Base color puffle ID';
COMMENT ON COLUMN puffle.name IS 'Puffle name';
COMMENT ON COLUMN puffle.cost IS 'Puffle cost';
COMMENT ON COLUMN puffle.member IS 'Is member-only?';
COMMENT ON COLUMN puffle.favourite_food IS 'Favourite puffle-care item';
COMMENT ON COLUMN puffle.runaway_postcard IS 'Runaway postcard ID';
DROP TABLE IF EXISTS puffle_treasure_item;
CREATE TABLE puffle_treasure_item (
puffle_id INT NOT NULL,
item_id INT NOT NULL,
PRIMARY KEY (puffle_id, item_id),
CONSTRAINT puffle_treasure_item_ibfk_1 FOREIGN KEY (puffle_id) REFERENCES puffle (id) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT puffle_treasure_item_ibfk_2 FOREIGN KEY (item_id) REFERENCES item (id) ON DELETE CASCADE ON UPDATE CASCADE
);
COMMENT ON TABLE puffle_treasure_item IS 'Puffle digging treasure clothing';
COMMENT ON COLUMN puffle_treasure_item.puffle_id IS 'Puffle type ID';
COMMENT ON COLUMN puffle_treasure_item.item_id IS 'Clothing item ID';
DROP TABLE IF EXISTS puffle_treasure_furniture;
CREATE TABLE puffle_treasure_furniture (
puffle_id INT NOT NULL,
furniture_id INT NOT NULL,
PRIMARY KEY (puffle_id, furniture_id),
CONSTRAINT puffle_treasure_furniture_ibfk_1 FOREIGN KEY (puffle_id) REFERENCES puffle (id) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT puffle_treasure_furniture_ibfk_2 FOREIGN KEY (furniture_id) REFERENCES furniture (id) ON DELETE CASCADE ON UPDATE CASCADE
);
COMMENT ON TABLE puffle_treasure_furniture IS 'Puffle digging treasure furniture';
COMMENT ON COLUMN puffle_treasure_furniture.puffle_id IS 'Puffle type ID';
COMMENT ON COLUMN puffle_treasure_furniture.furniture_id IS 'Furniture item ID';
DROP TABLE IF EXISTS puffle_treasure_puffle_item;
CREATE TABLE puffle_treasure_puffle_item (
puffle_id INT NOT NULL,
puffle_item_id INT NOT NULL,
PRIMARY KEY (puffle_id, puffle_item_id),
CONSTRAINT puffle_treasure_puffle_item_ibfk_1 FOREIGN KEY (puffle_id) REFERENCES puffle (id) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT puffle_treasure_puffle_item_ibfk_2 FOREIGN KEY (puffle_item_id) REFERENCES puffle_item (id) ON DELETE CASCADE ON UPDATE CASCADE
);
COMMENT ON TABLE puffle_treasure_puffle_item IS 'Puffle digging treasure puffle care items';
COMMENT ON COLUMN puffle_treasure_puffle_item.puffle_id IS 'Puffle type ID';
COMMENT ON COLUMN puffle_treasure_puffle_item.puffle_item_id IS 'Puffle care item ID';
DROP TABLE IF EXISTS quest;
CREATE TABLE quest (
id SERIAL,
name VARCHAR(30) NOT NULL,
PRIMARY KEY(id)
);
COMMENT ON TABLE quest IS 'Player map quests';
COMMENT ON COLUMN quest.id IS 'Unique quest ID';
COMMENT ON COLUMN quest.name IS 'Short name of quest';
DROP TABLE IF EXISTS quest_award_item;
CREATE TABLE quest_award_item (
quest_id INT NOT NULL,
item_id INT NOT NULL,
PRIMARY KEY (quest_id, item_id),
CONSTRAINT quest_award_item_ibfk_1 FOREIGN KEY (quest_id) REFERENCES quest (id) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT quest_award_item_ibfk_2 FOREIGN KEY (item_id) REFERENCES item (id) ON DELETE CASCADE ON UPDATE CASCADE
);
COMMENT ON COLUMN quest_award_item.quest_id IS 'Quest ID';
COMMENT ON COLUMN quest_award_item.item_id IS 'Clothing item ID';
DROP TABLE IF EXISTS quest_award_furniture;
CREATE TABLE quest_award_furniture (
quest_id INT NOT NULL,
furniture_id INT NOT NULL,
quantity SMALLINT NOT NULL DEFAULT 1,
PRIMARY KEY (quest_id, furniture_id),
CONSTRAINT quest_award_furniture_ibfk_1 FOREIGN KEY (quest_id) REFERENCES quest (id) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT quest_award_furniture_ibfk_2 FOREIGN KEY (furniture_id) REFERENCES furniture (id) ON DELETE CASCADE ON UPDATE CASCADE
);
COMMENT ON COLUMN quest_award_furniture.quest_id IS 'Quest ID';
COMMENT ON COLUMN quest_award_furniture.furniture_id IS 'Furniture item ID';
DROP TABLE IF EXISTS quest_award_puffle_item;
CREATE TABLE quest_award_puffle_item (
quest_id INT NOT NULL,
puffle_item_id INT NOT NULL,
quantity SMALLINT NOT NULL DEFAULT 1,
PRIMARY KEY (quest_id, puffle_item_id),
CONSTRAINT quest_award_puffle_item_ibfk_1 FOREIGN KEY (quest_id) REFERENCES quest (id) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT quest_award_puffle_item_ibfk_2 FOREIGN KEY (puffle_item_id) REFERENCES puffle_item (id) ON DELETE CASCADE ON UPDATE CASCADE
);
COMMENT ON COLUMN quest_award_puffle_item.quest_id IS 'Quest ID';
COMMENT ON COLUMN quest_award_puffle_item.puffle_item_id IS 'Puffle care item ID';
DROP TABLE IF EXISTS quest_task;
CREATE TABLE quest_task (
id SERIAL NOT NULL,
quest_id INT NOT NULL,
description VARCHAR(50) NOT NULL,
room_id INT DEFAULT NULL,
data VARCHAR(50) DEFAULT NULL,
PRIMARY KEY (id),
CONSTRAINT quest_task_ibfk_1 FOREIGN KEY (quest_id) REFERENCES quest (id) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT quest_task_ibfk_2 FOREIGN KEY (room_id) REFERENCES room (id) ON DELETE CASCADE ON UPDATE CASCADE
);
COMMENT ON TABLE quest_task IS 'Player map quest tasks';
COMMENT ON COLUMN quest_task.id IS 'Unique task ID';
COMMENT ON COLUMN quest_task.quest_id IS 'Task quest ID';
COMMENT ON COLUMN quest_task.description IS 'Description of task';
COMMENT ON COLUMN quest_task.room_id IS 'Room ID for completion';
DROP TABLE IF EXISTS dance_song;
CREATE TABLE dance_song (
id INT NOT NULL,
name VARCHAR (30) NOT NULL,
song_length_millis INT NOT NULL,
song_length INT NOT NULL,
millis_per_bar INT NOT NULL,
PRIMARY KEY (id)
);
COMMENT ON TABLE dance_song IS 'Dance contest multiplayer tracks';
COMMENT ON COLUMN dance_song.id IS 'Unique song ID';
COMMENT ON COLUMN dance_song.name IS 'Name of song';
COMMENT ON COLUMN dance_song.song_length_millis IS 'Length of song in milliseconds';
COMMENT ON COLUMN dance_song.song_length IS 'Length of song in beats';
COMMENT ON COLUMN dance_song.millis_per_bar IS 'Milliseconds per song note';
CREATE TABLE room_waddle (
id INT NOT NULL,
room_id INT NOT NULL,
seats SMALLINT NOT NULL DEFAULT 2,
game VARCHAR(20) NOT NULL,
PRIMARY KEY (id, room_id),
CONSTRAINT room_waddle_ibfk_1 FOREIGN KEY (room_id) REFERENCES room (id) ON DELETE CASCADE ON UPDATE CASCADE
);
COMMENT ON TABLE room_waddle IS 'Server waddle games';
COMMENT ON COLUMN room_waddle.id IS 'Waddle ID';
COMMENT ON COLUMN room_waddle.room_id IS 'Room ID of waddle';
COMMENT ON COLUMN room_waddle.seats IS 'Number of seats';
COMMENT ON COLUMN room_waddle.game IS 'Game of waddle';
CREATE TABLE room_table (
id INT NOT NULL,
room_id INT NOT NULL,
game VARCHAR(20) NOT NULL,
PRIMARY KEY (id, room_id),
CONSTRAINT room_table_ibfk_1 FOREIGN KEY (room_id) REFERENCES room (id) ON DELETE CASCADE ON UPDATE CASCADE
);
COMMENT ON TABLE room_table IS 'Server table games';
COMMENT ON COLUMN room_table.id IS 'Table ID';
COMMENT ON COLUMN room_table.room_id IS 'Room ID of table';
COMMENT ON COLUMN room_table.game IS 'Game of table';
CREATE TABLE chat_filter_rule (
word TEXT,
filter BOOLEAN NOT NULL DEFAULT FALSE,
warn BOOLEAN NOT NULL DEFAULT FALSE,
ban BOOLEAN NOT NULL DEFAULT FALSE,
PRIMARY KEY(word)
);
CREATE UNIQUE INDEX chat_filter_rule_word ON chat_filter_rule(word);
COMMENT ON COLUMN chat_filter_rule.word IS 'Word to filter';
COMMENT ON COLUMN chat_filter_rule.filter IS 'Hide word from players';
COMMENT ON COLUMN chat_filter_rule.warn IS 'Warn player for word';
COMMENT ON COLUMN chat_filter_rule.ban IS 'Ban player for word';
DROP TABLE IF EXISTS penguin;
CREATE TABLE penguin (
id SERIAL,
username VARCHAR(12) NOT NULL,
nickname VARCHAR(30) NOT NULL,
password CHAR(60) NOT NULL,
email VARCHAR(255) NOT NULL,
registration_date TIMESTAMP NOT NULL,
active BOOLEAN NOT NULL DEFAULT FALSE,
safe_chat BOOLEAN NOT NULL DEFAULT FALSE,
last_paycheck TIMESTAMP NOT NULL,
minutes_played INT NOT NULL DEFAULT 0,
moderator BOOLEAN NOT NULL DEFAULT FALSE,
stealth_moderator BOOLEAN NOT NULL DEFAULT FALSE,
character INT DEFAULT NULL,
igloo INT DEFAULT NULL,
coins INT NOT NULL DEFAULT 500,
color INT DEFAULT NULL,
head INT DEFAULT NULL,
face INT DEFAULT NULL,
neck INT DEFAULT NULL,
body INT DEFAULT NULL,
hand INT DEFAULT NULL,
feet INT DEFAULT NULL,
photo INT DEFAULT NULL,
flag INT DEFAULT NULL,
permaban BOOLEAN NOT NULL DEFAULT FALSE,
book_modified SMALLINT NOT NULL DEFAULT 0,
book_color SMALLINT NOT NULL DEFAULT 1,
book_highlight SMALLINT NOT NULL DEFAULT 1,
book_pattern SMALLINT NOT NULL DEFAULT 0,
book_icon SMALLINT NOT NULL DEFAULT 1,
agent_status BOOLEAN NOT NULL DEFAULT FALSE,
field_op_status SMALLINT NOT NULL DEFAULT 0,
career_medals INT NOT NULL DEFAULT 0,
agent_medals INT NOT NULL DEFAULT 0,
last_field_op TIMESTAMP NOT NULL,
com_message_read_date TIMESTAMP NOT NULL,
ninja_rank SMALLINT NOT NULL DEFAULT 0,
ninja_progress SMALLINT NOT NULL DEFAULT 0,
fire_ninja_rank SMALLINT NOT NULL DEFAULT 0,
fire_ninja_progress SMALLINT NOT NULL DEFAULT 0,
water_ninja_rank SMALLINT NOT NULL DEFAULT 0,
water_ninja_progress SMALLINT NOT NULL DEFAULT 0,
snow_ninja_rank SMALLINT NOT NULL DEFAULT 0,
snow_ninja_progress SMALLINT NOT NULL DEFAULT 0,
ninja_matches_won INT NOT NULL DEFAULT 0,
fire_matches_won INT NOT NULL DEFAULT 0,
water_matches_won INT NOT NULL DEFAULT 0,
snow_progress_fire_wins INT NOT NULL DEFAULT 0,
snow_progress_water_wins INT NOT NULL DEFAULT 0,
snow_progress_snow_wins INT NOT NULL DEFAULT 0,
rainbow_adoptability BOOLEAN NOT NULL DEFAULT FALSE,
has_dug BOOLEAN NOT NULL DEFAULT FALSE,
puffle_handler BOOLEAN NOT NULL DEFAULT FALSE,
nuggets SMALLINT NOT NULL DEFAULT 0,
walking INT DEFAULT NULL,
opened_playercard BOOLEAN NOT NULL DEFAULT FALSE,
special_wave BOOLEAN NOT NULL DEFAULT FALSE,
special_dance BOOLEAN NOT NULL DEFAULT FALSE,
special_snowball BOOLEAN NOT NULL DEFAULT FALSE,
map_category SMALLINT NOT NULL DEFAULT 0,
status_field INT NOT NULL DEFAULT 0,
timer_active BOOLEAN NOT NULL DEFAULT FALSE,
timer_start TIME NOT NULL DEFAULT '00:00:00',
timer_end TIME NOT NULL DEFAULT '23:59:59',
timer_total INTERVAL NOT NULL DEFAULT '01:00:00',
grounded BOOLEAN NOT NULL DEFAULT FALSE,
approval_en BOOLEAN NOT NULL DEFAULT FALSE,
approval_pt BOOLEAN NOT NULL DEFAULT FALSE,
approval_fr BOOLEAN NOT NULL DEFAULT FALSE,
approval_es BOOLEAN NOT NULL DEFAULT FALSE,
approval_de BOOLEAN NOT NULL DEFAULT FALSE,
approval_ru BOOLEAN NOT NULL DEFAULT FALSE,
rejection_en BOOLEAN NOT NULL DEFAULT FALSE,
rejection_pt BOOLEAN NOT NULL DEFAULT FALSE,
rejection_fr BOOLEAN NOT NULL DEFAULT FALSE,
rejection_es BOOLEAN NOT NULL DEFAULT FALSE,
rejection_de BOOLEAN NOT NULL DEFAULT FALSE,
rejection_ru BOOLEAN NOT NULL DEFAULT FALSE,
PRIMARY KEY (id),
CONSTRAINT penguin_ibfk_1 FOREIGN KEY (color) REFERENCES item (id) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT penguin_ibfk_2 FOREIGN KEY (head) REFERENCES item (id) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT penguin_ibfk_3 FOREIGN KEY (face) REFERENCES item (id) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT penguin_ibfk_4 FOREIGN KEY (neck) REFERENCES item (id) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT penguin_ibfk_5 FOREIGN KEY (body) REFERENCES item (id) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT penguin_ibfk_6 FOREIGN KEY (hand) REFERENCES item (id) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT penguin_ibfk_7 FOREIGN KEY (feet) REFERENCES item (id) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT penguin_ibfk_8 FOREIGN KEY (photo) REFERENCES item (id) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT penguin_ibfk_9 FOREIGN KEY (flag) REFERENCES item (id) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT penguin_ibfk_10 FOREIGN KEY (character) REFERENCES character (id) ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE INDEX penguin_email ON Penguin(email);
CREATE UNIQUE INDEX penguin_username ON Penguin(username);
ALTER TABLE penguin ALTER COLUMN registration_date SET DEFAULT now();
ALTER TABLE penguin ALTER COLUMN last_paycheck SET DEFAULT now();
ALTER TABLE penguin ALTER COLUMN last_field_op SET DEFAULT now();
ALTER TABLE penguin ALTER COLUMN com_message_read_date SET DEFAULT now();
ALTER SEQUENCE penguin_id_seq RESTART WITH 101;
COMMENT ON TABLE penguin IS 'Penguins';
COMMENT ON COLUMN penguin.id IS 'Unique penguin ID';
COMMENT ON COLUMN penguin.username IS 'Penguin login name';
COMMENT ON COLUMN penguin.nickname IS 'Penguin display name';
COMMENT ON COLUMN penguin.password IS 'Password hash';
COMMENT ON COLUMN penguin.email IS 'User Email address';
COMMENT ON COLUMN penguin.registration_date IS 'Date of registration';
COMMENT ON COLUMN penguin.active IS 'email activated';
COMMENT ON COLUMN penguin.last_paycheck IS 'EPF previous paycheck';
COMMENT ON COLUMN penguin.minutes_played IS 'Total minutes connected';
COMMENT ON COLUMN penguin.moderator IS 'Is user moderator?';
COMMENT ON COLUMN penguin.character IS 'Character ID';
COMMENT ON COLUMN penguin.igloo IS 'Penguin active igloo ID';
COMMENT ON COLUMN penguin.coins IS 'Penguin coins';
COMMENT ON COLUMN penguin.color IS 'Penguin color ID';
COMMENT ON COLUMN penguin.head IS 'Penguin head item ID';
COMMENT ON COLUMN penguin.face IS 'Penguin face item ID';
COMMENT ON COLUMN penguin.neck IS 'Penguin neck item ID';
COMMENT ON COLUMN penguin.body IS 'Penguin body item ID';
COMMENT ON COLUMN penguin.hand IS 'Penguin hand item ID';
COMMENT ON COLUMN penguin.feet IS 'Penguin feet item ID';
COMMENT ON COLUMN penguin.photo IS 'Penguin background ID';
COMMENT ON COLUMN penguin.flag IS 'Penguin pin ID';
COMMENT ON COLUMN penguin.permaban IS 'Is penguin banned forever?';
COMMENT ON COLUMN penguin.book_modified IS 'Is book cover modified?';
COMMENT ON COLUMN penguin.book_color IS 'Stampbook cover color';
COMMENT ON COLUMN penguin.book_highlight IS 'Stampbook highlight color';
COMMENT ON COLUMN penguin.book_pattern IS 'Stampbook cover pattern';
COMMENT ON COLUMN penguin.book_icon IS 'Stampbook cover icon';
COMMENT ON COLUMN penguin.agent_status IS 'Is penguin EPF agent?';
COMMENT ON COLUMN penguin.field_op_status IS 'Is field op complete?';
COMMENT ON COLUMN penguin.career_medals IS 'Total career medals';
COMMENT ON COLUMN penguin.agent_medals IS 'Current medals';
COMMENT ON COLUMN penguin.last_field_op IS 'Date of last field op';
COMMENT ON COLUMN penguin.com_message_read_date IS 'Recent agent message read';
COMMENT ON COLUMN penguin.ninja_rank IS 'Ninja rank';
COMMENT ON COLUMN penguin.ninja_progress IS 'Ninja progress';
COMMENT ON COLUMN penguin.fire_ninja_rank IS 'Fire ninja rank';
COMMENT ON COLUMN penguin.fire_ninja_progress IS 'Fire ninja progress';
COMMENT ON COLUMN penguin.water_ninja_rank IS 'Water ninja rank';
COMMENT ON COLUMN penguin.water_ninja_progress IS 'Water ninja progress';
COMMENT ON COLUMN penguin.ninja_matches_won IS 'CardJitsu matches won';
COMMENT ON COLUMN penguin.fire_matches_won IS 'JitsuFire matches won';
COMMENT ON COLUMN penguin.water_matches_won IS 'JitsuWater matces won';
COMMENT ON COLUMN penguin.rainbow_adoptability IS 'Rainbow puffle adoptability status';
COMMENT ON COLUMN penguin.has_dug IS 'Puffle digging boolean';
COMMENT ON COLUMN penguin.puffle_handler IS 'Has met puffle handler?';
COMMENT ON COLUMN penguin.nuggets IS 'Golden puffle nuggets';
COMMENT ON COLUMN penguin.walking IS 'Walking puffle ID';
COMMENT ON COLUMN penguin.opened_playercard IS 'Has player opened playercard?';
COMMENT ON COLUMN penguin.map_category IS 'Currently selected map category';
COMMENT ON COLUMN penguin.status_field IS 'New player status field';
COMMENT ON COLUMN penguin.timer_active IS 'Is egg-timer active?';
COMMENT ON COLUMN penguin.timer_start IS 'Egg-timer start time';
COMMENT ON COLUMN penguin.timer_end IS 'Egg-timer end time';
COMMENT ON COLUMN penguin.timer_total IS 'Egg-timer total play time';
COMMENT ON COLUMN penguin.grounded IS 'Is player grounded?';
COMMENT ON COLUMN penguin.approval_en IS 'English username approval';
COMMENT ON COLUMN penguin.approval_pt IS 'Portuguese username approval';
COMMENT ON COLUMN penguin.approval_fr IS 'French username approval';
COMMENT ON COLUMN penguin.approval_es IS 'Spanish username approval';
COMMENT ON COLUMN penguin.approval_de IS 'Dutch username approval';
COMMENT ON COLUMN penguin.approval_ru IS 'Russian username approval';
DROP TABLE IF EXISTS activation_key;
CREATE TABLE activation_key (
penguin_id INT NOT NULL,
activation_key CHAR(255) NOT NULL,
PRIMARY KEY (penguin_id, activation_key),
CONSTRAINT activation_key_ibfk_1 FOREIGN KEY (penguin_id) REFERENCES penguin(id) ON DELETE CASCADE ON UPDATE CASCADE
);
COMMENT ON TABLE activation_key IS 'Penguin activation keys';
COMMENT ON COLUMN activation_key.penguin_id IS 'Penguin ID';
COMMENT ON COLUMN activation_key.activation_key IS 'Penguin activation key';
DROP TABLE IF EXISTS permission;
CREATE TABLE permission (
name VARCHAR(50) NOT NULL,
enabled BOOLEAN NOT NULL DEFAULT TRUE,
PRIMARY KEY (name)
);
CREATE UNIQUE INDEX permission_name ON permission(name);
COMMENT ON TABLE permission IS 'Registered server permissions';
COMMENT ON COLUMN permission.name IS 'Unique permission identifier';
DROP TABLE IF EXISTS penguin_permission;
CREATE TABLE penguin_permission (
penguin_id INT NOT NULL,
permission_name VARCHAR(50) NOT NULL,
PRIMARY KEY (penguin_id, permission_name),
CONSTRAINT penguin_permission_ibfk_1 FOREIGN KEY (penguin_id) REFERENCES penguin(id) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT penguin_permission_ibfk_2 FOREIGN KEY (permission_name) REFERENCES permission(name) ON DELETE CASCADE ON UPDATE CASCADE
);
COMMENT ON TABLE penguin_permission IS 'Penguin permissions';
COMMENT ON COLUMN penguin_permission.penguin_id IS 'Penguin ID';
COMMENT ON COLUMN penguin_permission.permission_name IS 'Penguin permission name';
DROP TABLE IF EXISTS penguin_attribute;
CREATE TABLE penguin_attribute (
name TEXT NOT NULL,
penguin_id INT NOT NULL,
value TEXT NOT NULL,
PRIMARY KEY (name, penguin_id),
CONSTRAINT penguin_attribute_ibfk_1 FOREIGN KEY (penguin_id) REFERENCES penguin(id) ON DELETE CASCADE ON UPDATE CASCADE
);
COMMENT ON TABLE penguin_attribute IS 'Custom penguin attributes';
COMMENT ON COLUMN penguin_attribute.name IS 'Attribute unique identifier';
COMMENT ON COLUMN penguin_attribute.penguin_id IS 'Penguin ID';
COMMENT ON COLUMN penguin_attribute.value IS 'Value of attribute';
DROP TABLE IF EXISTS plugin_attribute;
CREATE TABLE plugin_attribute (
name TEXT NOT NULL,
plugin_name TEXT NOT NULL,
value TEXT NOT NULL,
PRIMARY KEY (name, plugin_name)
);
COMMENT ON TABLE plugin_attribute IS 'Custom plugin attributes';
COMMENT ON COLUMN plugin_attribute.name IS 'Attribute unique identifier';
COMMENT ON COLUMN plugin_attribute.plugin_name IS 'Name of plugin attribute belongs to';
COMMENT ON COLUMN plugin_attribute.value IS 'Value of attribute';
DROP TABLE IF EXISTS report;
CREATE TABLE report (
id SERIAL NOT NULL,
penguin_id INT NOT NULL,
reporter_id INT NOT NULL,
report_type SMALLINT NOT NULL DEFAULT 0,
date TIMESTAMP NOT NULL,
server_id INT NOT NULL,
room_id INT NOT NULL,
PRIMARY KEY(id),
CONSTRAINT report_ibfk_1 FOREIGN KEY (penguin_id) REFERENCES penguin (id) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT report_ibfk_2 FOREIGN KEY (reporter_id) REFERENCES penguin (id) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT report_ibfk_3 FOREIGN KEY (room_id) REFERENCES room (id) ON DELETE CASCADE ON UPDATE CASCADE
);
ALTER TABLE report ALTER COLUMN date SET DEFAULT now();
COMMENT ON TABLE report IS 'Player reports';
COMMENT ON COLUMN report.id IS 'Unique report ID';
COMMENT ON COLUMN report.penguin_id IS 'Reported penguin ID';
COMMENT ON COLUMN report.reporter_id IS 'Reporting penguin ID';
COMMENT ON COLUMN report.report_type IS 'Report type ID';
COMMENT ON COLUMN report.date IS 'Date of report';
DROP TABLE IF EXISTS ban;
CREATE TABLE ban (
penguin_id INT NOT NULL,
issued TIMESTAMP NOT NULL,
expires TIMESTAMP NOT NULL,
moderator_id INT DEFAULT NULL,
reason SMALLINT NOT NULL,
comment TEXT DEFAULT NULL,
message TEXT DEFAULT NULL,
PRIMARY KEY (penguin_id, issued, expires),
CONSTRAINT ban_ibfk_1 FOREIGN KEY (penguin_id) REFERENCES penguin (id) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT ban_ibfk_2 FOREIGN KEY (moderator_id) REFERENCES penguin (id) ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE INDEX ban_moderator_id ON ban (moderator_id);
ALTER TABLE ban ALTER COLUMN issued SET DEFAULT now();
ALTER TABLE ban ALTER COLUMN expires SET DEFAULT now();
COMMENT ON TABLE ban IS 'Penguin ban records';
COMMENT ON COLUMN ban.penguin_id IS 'Banned penguin ID';
COMMENT ON COLUMN ban.issued IS 'Issue date';
COMMENT ON COLUMN ban.expires IS 'Expiry date';
COMMENT ON COLUMN ban.moderator_id IS 'Moderator penguin ID';
COMMENT ON COLUMN ban.reason IS 'Ban reason';
COMMENT ON COLUMN ban.comment IS 'Ban comment';
COMMENT ON COLUMN ban.message IS 'Banned for message';
DROP TABLE IF EXISTS warning;
CREATE TABLE warning (
penguin_id INT NOT NULL,
issued TIMESTAMP NOT NULL,
expires TIMESTAMP NOT NULL,
PRIMARY KEY (penguin_id, issued, expires),
CONSTRAINT warning_ibfk_1 FOREIGN KEY (penguin_id) REFERENCES penguin (id) ON DELETE CASCADE ON UPDATE CASCADE
);
COMMENT ON TABLE warning IS 'Penguin moderator warnings';
COMMENT ON COLUMN warning.penguin_id IS 'Warning penguin ID';
COMMENT ON COLUMN warning.issued IS 'Warning issue date';
COMMENT ON COLUMN warning.expires IS 'Warning expiry date';
DROP TABLE IF EXISTS buddy_list;
CREATE TABLE buddy_list (
penguin_id INT NOT NULL,
buddy_id INT NOT NULL,
best_buddy BOOLEAN NOT NULL DEFAULT FALSE,
PRIMARY KEY (penguin_id,buddy_id),
CONSTRAINT buddy_list_ibfk_1 FOREIGN KEY (penguin_id) REFERENCES penguin (id) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT buddy_list_ibfk_2 FOREIGN KEY (buddy_id) REFERENCES penguin (id) ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE INDEX buddy_id ON buddy_list (buddy_id);
COMMENT ON TABLE buddy_list IS 'Penguin buddy relationships';
DROP TABLE IF EXISTS buddy_request;
CREATE TABLE buddy_request (
penguin_id INT NOT NULL,
requester_id INT NOT NULL,
PRIMARY KEY (penguin_id, requester_id),
CONSTRAINT buddy_request_ibfk_1 FOREIGN KEY (penguin_id) REFERENCES penguin (id) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT buddy_request_ibfk_2 FOREIGN KEY (requester_id) REFERENCES penguin (id) ON DELETE CASCADE ON UPDATE CASCADE
);
COMMENT ON TABLE buddy_request IS 'Penguin buddy requests';
DROP TABLE IF EXISTS character_buddy;
CREATE TABLE character_buddy (
penguin_id INT NOT NULL,
character_id SMALLINT NOT NULL,
best_buddy BOOLEAN NOT NULL DEFAULT FALSE,
PRIMARY KEY (penguin_id, character_id),
CONSTRAINT character_buddy_ibfk_1 FOREIGN KEY (penguin_id) REFERENCES penguin (id) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT character_buddy_ibfk_2 FOREIGN KEY (character_id) REFERENCES character (id) ON DELETE CASCADE ON UPDATE CASCADE
);
COMMENT ON TABLE character_buddy IS 'Penguin character buddies';
DROP TABLE IF EXISTS cover_stamp;
CREATE TABLE cover_stamp (
penguin_id INT NOT NULL,
stamp_id INT NOT NULL,
x SMALLINT NOT NULL DEFAULT 0,
y SMALLINT NOT NULL DEFAULT 0,
rotation SMALLINT NOT NULL DEFAULT 0,
depth SMALLINT NOT NULL DEFAULT 0,
PRIMARY KEY (penguin_id, stamp_id),
CONSTRAINT cover_stamp_ibfk_1 FOREIGN KEY (penguin_id) REFERENCES penguin (id) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT cover_stamp_ibfk_2 FOREIGN KEY (stamp_id) REFERENCES stamp (id) ON DELETE CASCADE ON UPDATE CASCADE
);
COMMENT ON TABLE cover_stamp IS 'Stamps placed on book cover';
COMMENT ON COLUMN cover_stamp.penguin_id IS 'Unique penguin ID';
COMMENT ON COLUMN cover_stamp.stamp_id IS 'Cover stamp ID';
COMMENT ON COLUMN cover_stamp.x IS 'Cover X position';
COMMENT ON COLUMN cover_stamp.y IS 'Cover Y position';
COMMENT ON COLUMN cover_stamp.rotation IS 'Stamp cover rotation';
COMMENT ON COLUMN cover_stamp.depth IS 'Stamp cover depth';
DROP TABLE IF EXISTS cover_item;
CREATE TABLE cover_item (
penguin_id INT NOT NULL,
item_id INT NOT NULL,
x SMALLINT NOT NULL DEFAULT 0,
y SMALLINT NOT NULL DEFAULT 0,
rotation SMALLINT NOT NULL DEFAULT 0,
depth SMALLINT NOT NULL DEFAULT 0,
PRIMARY KEY (penguin_id, item_id),
CONSTRAINT cover_item_ibfk_1 FOREIGN KEY (penguin_id) REFERENCES penguin (id) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT cover_item_ibfk_2 FOREIGN KEY (item_id) REFERENCES item (id) ON DELETE CASCADE ON UPDATE CASCADE
);
COMMENT ON TABLE cover_item IS 'Items placed on book cover';
COMMENT ON COLUMN cover_item.penguin_id IS 'Unique penguin ID';
COMMENT ON COLUMN cover_item.item_id IS 'Cover item ID';
COMMENT ON COLUMN cover_item.x IS 'Cover X position';
COMMENT ON COLUMN cover_item.y IS 'Cover Y position';
COMMENT ON COLUMN cover_item.rotation IS 'Stamp cover rotation';
COMMENT ON COLUMN cover_item.depth IS 'Stamp cover depth';
DROP TABLE IF EXISTS penguin_card;
CREATE TABLE penguin_card (
penguin_id INT NOT NULL,
card_id INT NOT NULL,
quantity SMALLINT NOT NULL DEFAULT 1,
member_quantity SMALLINT NOT NULL DEFAULT 0,
PRIMARY KEY (penguin_id, card_id),
CONSTRAINT penguin_card_ibfk_1 FOREIGN KEY (penguin_id) REFERENCES penguin (id) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT penguin_card_ibfk_2 FOREIGN KEY (card_id) REFERENCES card (id) ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE INDEX penguin_card_penguin_id ON penguin_card(penguin_id);
COMMENT ON TABLE penguin_card IS 'Penguin Card Jitsu decks';
COMMENT ON COLUMN penguin_card.penguin_id IS 'Owner penguin ID';
COMMENT ON COLUMN penguin_card.card_id IS 'Card type ID';
COMMENT ON COLUMN penguin_card.quantity IS 'Quantity owned';
COMMENT ON COLUMN penguin_card.member_quantity IS 'Quantity owned as member';
DROP TABLE IF EXISTS penguin_furniture;
CREATE TABLE penguin_furniture (
penguin_id INT NOT NULL,
furniture_id INT NOT NULL,
quantity SMALLINT NOT NULL DEFAULT 1,
PRIMARY KEY (penguin_id, furniture_id),
CONSTRAINT penguin_furniture_ibfk_1 FOREIGN KEY (penguin_id) REFERENCES penguin (id) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT penguin_furniture_ibfk_2 FOREIGN KEY (furniture_id) REFERENCES furniture (id) ON DELETE CASCADE ON UPDATE CASCADE
);
COMMENT ON TABLE penguin_furniture IS 'Penguin owned furniture';
COMMENT ON COLUMN penguin_furniture.penguin_id IS 'Owner penguin ID';
COMMENT ON COLUMN penguin_furniture.furniture_id IS 'Furniture item ID';
COMMENT ON COLUMN penguin_furniture.quantity IS 'Quantity owned';
DROP TABLE IF EXISTS penguin_flooring;
CREATE TABLE penguin_flooring (
penguin_id INT NOT NULL,
flooring_id SMALLINT NOT NULL,
PRIMARY KEY(penguin_id, flooring_id),
CONSTRAINT penguin_flooring_ibfk_1 FOREIGN KEY (penguin_id) REFERENCES penguin (id) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT penugin_flooring_ibfk_2 FOREIGN KEY (flooring_id) REFERENCES flooring (id) ON DELETE CASCADE ON UPDATE CASCADE
);
COMMENT ON TABLE penguin_flooring IS 'Penguin owned furniture';
COMMENT ON COLUMN penguin_flooring.penguin_id IS 'Owner penguin ID';
COMMENT ON COLUMN penguin_flooring.flooring_id IS 'Flooring item ID';
DROP TABLE IF EXISTS penguin_igloo_room;
CREATE TABLE penguin_igloo_room (
id SERIAL,
penguin_id INT NOT NULL,
type INT NOT NULL,
flooring INT NOT NULL,
music INT NOT NULL DEFAULT 0,
location INT NOT NULL,
locked BOOLEAN NOT NULL DEFAULT TRUE,
competition BOOLEAN NOT NULL DEFAULT FALSE,
PRIMARY KEY (id),
CONSTRAINT igloo_room_ibfk_1 FOREIGN KEY (penguin_id) REFERENCES penguin (id) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT igloo_room_ibfk_2 FOREIGN KEY (type) REFERENCES igloo (id) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT igloo_room_ibfk_3 FOREIGN KEY (flooring) REFERENCES flooring (id) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT igloo_room_ibfk_4 FOREIGN KEY (location) REFERENCES location (id) ON DELETE CASCADE ON UPDATE CASCADE
);
ALTER TABLE penguin ADD CONSTRAINT penguin_ibfk_11 FOREIGN KEY (igloo) REFERENCES penguin_igloo_room (id) ON DELETE CASCADE ON UPDATE CASCADE;
COMMENT ON TABLE penguin_igloo_room IS 'Penguin igloo settings';
COMMENT ON COLUMN penguin_igloo_room.id IS 'Unique igloo ID';
COMMENT ON COLUMN penguin_igloo_room.penguin_id IS 'Owner penguin ID';
COMMENT ON COLUMN penguin_igloo_room.type IS 'Igloo type ID';
COMMENT ON COLUMN penguin_igloo_room.flooring IS 'Igloo flooring ID';
COMMENT ON COLUMN penguin_igloo_room.music IS 'Igloo music ID';
COMMENT ON COLUMN penguin_igloo_room.locked IS 'Is igloo locked?';
COMMENT ON COLUMN penguin_igloo_room.competition IS 'Is entered in competition?';
DROP TABLE IF EXISTS igloo_like;
CREATE TABLE igloo_like (
igloo_id INT NOT NULL,
player_id INT NOT NULL,
count SMALLINT NOT NULL DEFAULT 1,
date TIMESTAMP NOT NULL,
PRIMARY KEY (igloo_id, player_id),
CONSTRAINT igloo_like_ibfk_1 FOREIGN KEY (igloo_id) REFERENCES penguin_igloo_room (id) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT igloo_like_ibfk_2 FOREIGN KEY (player_id) REFERENCES penguin (id) ON DELETE CASCADE ON UPDATE CASCADE
);
ALTER TABLE igloo_like ALTER COLUMN date SET DEFAULT now();
COMMENT ON TABLE igloo_like IS 'Player igloo likes';