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
|
EXEC SQL WHENEVER SQLERROR CONTINUE;
static inline int iserror() {
return !(SQLSTATE[0] == '0' && SQLSTATE[1] == '0');
}
static inline void printerror() {
printf("%c%c%c%c%c\n", SQLSTATE[0], SQLSTATE[1], SQLSTATE[2], SQLSTATE[3], SQLSTATE[4]);
}
static inline int testerror(const char *code) {
for(int i = 0; i < 5; i++) {
if(code[i] != SQLSTATE[i])
return 0;
}
return 1;
}
int pg_connect(const char *string) {
EXEC SQL BEGIN DECLARE SECTION;
const char *target = string;
EXEC SQL END DECLARE SECTION;
EXEC SQL CONNECT TO :target;
if(iserror()) {
sqlprint();
printerror();
}
return !iserror();
}
void pg_disconnect() {
EXEC SQL DISCONNECT;
}
int pg_init() {
EXEC SQL WHENEVER SQLERROR GOTO pg_init_error;
EXEC SQL CREATE TABLE schema (version INTEGER);
EXEC SQL INSERT INTO schema (version) VALUES (1);
EXEC SQL CREATE TABLE channel (
id serial,
name varchar,
PRIMARY KEY (id)
);
EXEC SQL CREATE TABLE channel_hours (
channel_id integer,
time_index integer,
lines integer,
PRIMARY KEY (channel_id, time_index),
FOREIGN KEY (channel_id) REFERENCES channel (id)
);
/* "user" is a reserved word */
EXEC SQL CREATE TABLE users (
id serial,
nick varchar,
words integer,
characters integer,
kicks integer,
kicked integer,
monolog_lines integer,
monologs integer,
seen_first integer,
seen_last integer,
PRIMARY KEY (id)
);
EXEC SQL CREATE TABLE user_hours (
user_id integer,
time_index integer,
lines integer,
PRIMARY KEY (user_id, time_index),
FOREIGN KEY (user_id) REFERENCES users (id)
);
EXEC SQL CREATE TABLE word (
id serial PRIMARY KEY,
name varchar,
count integer
);
EXEC SQL COMMIT;
return 1;
pg_init_error:
sqlprint();
return 0;
}
EXEC SQL WHENEVER SQLERROR CONTINUE;
int pg_upgrade() {
EXEC SQL BEGIN DECLARE SECTION;
int version = 0;
EXEC SQL END DECLARE SECTION;
EXEC SQL SELECT version INTO :version FROM schema;
if(testerror("02000")) {
EXEC SQL INSERT INTO schema (version) VALUES (1);
} else if(iserror()) {
sqlprint();
return 0;
}
printf("current schema version: %d\n", version);
EXEC SQL COMMIT;
return !iserror();
}
/* vim: set syn=c: */
|
