EahrnYRq

1notify tbl2, 'recreating';
2
3\c postgres
4
5drop database if exists nyxia;
6
7create database nyxia;
8
9\c nyxia
10
11
12create table accounts (
13    account_id serial primary key,
14    email text not null
15);
16
17create table characters (
18--id key
19    character_id serial primary key,
20    account_id int not null references accounts on delete cascade on update cascade,
21    multiple_characters int references characters on update cascade,
22    dead boolean default false,
23    initializing boolean default true,
24--race
25    race text,
26    name text default 'name me',
27--traits
28    curses int[],
29    traits int[] default '{0,0}',
30--stats
31    m_health int default 8,
32    health int default 8,
33    injuries int default 0,
34    wounds int default 0,
35    white_mana int default 0,
36    blue_mana int default 0,
37    black_mana int default 0,
38    red_mana int default 0,
39    green_mana int default 0,
40    companions int[],
41    current_movement int default 4,
42    norm_movement int default 4,
43--attributes
44    startstat int default 2,
45    strength int,
46    dexterity int,
47    constitution int,
48    wisdom int,
49    charisma int,
50    awareness int,
51    nyx int,
52    white_devotion int default 0,
53    blue_devotion int default 0,
54    black_devotion int default 0,
55    red_devotion int default 0,
56    green_devotion int default 0,
57    paragon int default 200,
58    xp_used int default 0
59);
60--making sure attributes get setup correctly
61create function inserted() RETURNS trigger AS $$
62declare id int;
63declare startvalue int;
64begin
65    id = new.character_id;
66    if initializing from characters where character_id = id then
67        startvalue = new.startstat;
68        if (select strength from characters where character_id = id) IS null then
69            new.strength = startvalue;
70            update characters set strength = startvalue  where character_id = id; 
71            RAISE NOTICE 'strength %,' , strength from characters where character_id = id;
72        else
73            RAISE NOTICE 'strength wasn''t null, was: %,' , (select strength from characters where character_id = id);
74        end if;
75        if (select dexterity from characters where character_id = id) IS null then
76            new.Dexterity = startvalue;
77            update characters set Dexterity = startvalue  where character_id = id; 
78            RAISE NOTICE 'Dexterity %,' , Dexterity from characters where character_id = id;
79        else
80            RAISE NOTICE 'Dexterity wasn''t null, was: %,' , (select Dexterity from characters where character_id = id);
81        end if;
82        if (select Constitution from characters where character_id = id) IS null then
83            new.Constitution = startvalue;
84            update characters set Constitution = startvalue  where character_id = id;
85            RAISE NOTICE 'Constitution %,' , Constitution from characters where character_id = id;
86        else
87            RAISE NOTICE 'Constitution wasn''t null, was: %,' , (select Constitution from characters where character_id = id);
88        end if;
89        if (select Wisdom from characters where character_id = id) IS null then
90            new.Wisdom = startvalue;
91            update characters set Wisdom = startvalue  where character_id = id; 
92            RAISE NOTICE 'Wisdom %,' , Wisdom from characters where character_id = id;
93        else
94            RAISE NOTICE 'Wisdom wasn''t null, was: %,' , (select Wisdom from characters where character_id = id);
95        end if;
96        if (select charisma from characters where character_id = id) IS null then
97            new.charisma = startvalue;
98            update characters set charisma = startvalue  where character_id = id;
99            RAISE NOTICE 'charisma %,' , charisma from characters where character_id = id;
100        else
101            RAISE NOTICE 'charisma wasn''t null, was: %,' , (select charisma from characters where character_id = id);
102        end if;
103        if (select awareness from characters where character_id = id) IS null then
104            new.awareness = startvalue;
105            update characters set awareness = startvalue  where character_id = id;
106            RAISE NOTICE 'awareness %,' , awareness from characters where character_id = id;
107        else
108            RAISE NOTICE 'awareness wasn''t null, was: %,' , (select awareness from characters where character_id = id);
109        end if;
110        if (select nyx from characters where character_id = id) IS null then
111            new.nyx = startvalue;
112            update characters set nyx = startvalue  where character_id = id; 
113            RAISE NOTICE 'nyx %,' , nyx from characters where character_id = id;
114        else
115            RAISE NOTICE 'nyx wasn''t null, was: %,' , (select nyx from characters where character_id = id);
116        end if;
117        update characters set initializing = false where character_id = id;
118    end if;
119    return new;
120end; 
121$$ LANGUAGE 'plpgsql';
122
123create function it_updated() RETURNS trigger AS $$
124declare texts text;
125begin
126    if not initializing from characters where character_id = new.character_id then
127    perform PG_NOTIFY('tbl2', row_to_json(NEW)::text);
128    end if;
129    return new;
130end; 
131$$ LANGUAGE 'plpgsql';
132
133create trigger inserts after insert on characters
134    for each row execute procedure inserted();
135end;
136
137create trigger updated after update on accounts
138    for each row execute procedure it_updated();
139end;
140
141create trigger updated after update on characters
142    for each row execute procedure it_updated();
143end;
144
145insert into accounts (email) values ('nicolaieno@msn.com');
146--insert into characters (account_id, race, startstat) values (1,'goblin', 1);