3 * UCC (University [of WA] Computer Club) Electronic Accounting System
5 * SQLite Coke Bank (Accounts Database)
7 * This file is licenced under the 3-clause BSD Licence. See the file
8 * COPYING for full details.
10 #include "../cokebank.h"
13 const char * const csBank_CreateAccountQry = "CREATE TABLE IF NOT EXISTS accounts ("
14 " acct_id INTEGER PRIMARY KEY NOT NULL,"
15 " acct_balance INTEGER NOT NULL,"
16 " acct_name STRING UNIQUE,"
17 " acct_uid INTEGER UNIQUE,"
18 " acct_pin INTEGER CHECK (acct_pin > 0 AND acct_pin < 10000),"
19 " acct_is_disabled BOOLEAN NOT NULL DEFAULT false,"
20 " acct_is_coke BOOLEAN NOT NULL DEFAULT false,"
21 " acct_is_wheel BOOLEAN NOT NULL DEFAULT false,"
22 " acct_is_door BOOLEAN NOT NULL DEFAULT false,"
23 " acct_is_internal BOOLEAN NOT NULL DEFAULT false"
25 const char * const csBank_CreateCardsQry = "CREATE TABLE IF NOT EXISTS cards ("
26 " acct_id INTEGER NOT NULL,"
27 " card_name STRING NOT NULL UNIQUE,"
28 " FOREIGN KEY (acct_id) REFERENCES accounts (acct_id) ON DELETE CASCADE"
29 // Deletion of the account frees the card ^ ^ ^
33 int Bank_Initialise(const char *Argument);
34 int Bank_Transfer(int SourceUser, int DestUser, int Ammount, const char *Reason);
35 int Bank_GetUserFlags(int UserID);
36 int Bank_SetUserFlags(int UserID, int Mask, int Value);
37 sqlite3_stmt *Bank_int_MakeStatemnt(sqlite3 *Database, const char *Query);
40 sqlite3 *gBank_Database;
43 int Bank_Initialise(const char *Argument)
48 rv = sqlite3_open(Argument, &gBank_Database);
51 fprintf(stderr, "CokeBank: Unable to open database '%s'\n", Argument);
52 fprintf(stderr, "Reason: %s\n", sqlite3_errmsg(gBank_Database));
53 sqlite3_close(gBank_Database);
58 rv = sqlite3_exec(gBank_Database, "SELECT acct_id FROM accounts LIMIT 1", NULL, NULL, &errmsg);
63 else if( rv == SQLITE_NOTFOUND )
68 rv = sqlite3_exec(gBank_Database, csBank_CreateAccountQry, NULL, NULL, &errmsg);
69 if( rv != SQLITE_OK ) {
70 fprintf(stderr, "SQLite Error: %s\n", errmsg);
75 rv = sqlite3_exec(gBank_Database, csBank_CreateCardsQry, NULL, NULL, &errmsg);
76 if( rv != SQLITE_OK ) {
77 fprintf(stderr, "SQLite Error: %s\n", errmsg);
85 fprintf(stderr, "SQLite Error: %s\n", errmsg);
96 int Bank_Transfer(int SourceUser, int DestUser, int Ammount, const char *Reason)
102 // Begin SQL Transaction
103 sqlite3_exec(gBank_Database, "BEGIN TRANSACTION", NULL, NULL, NULL);
105 // Take from the source
106 query = mkstr("UPDATE accounts SET acct_balance=acct_balance-%i WHERE acct_id=%i", Ammount, SourceUser);
107 rv = sqlite3_exec(gBank_Database, query, NULL, NULL, &errmsg);
109 if( rv != SQLITE_OK )
111 fprintf(stderr, "SQLite Error: %s\n", errmsg);
112 sqlite3_free(errMsg);
113 sqlite3_query(gBank_Database, "ROLLBACK", NULL, NULL, NULL);
117 // Give to the destination
118 query = mkstr("UPDATE accounts SET acct_balance=acct_balance+%i WHERE acct_id=%i", Ammount, DestUser);
119 rv = sqlite3_exec(gBank_Database, query, NULL, NULL, &errmsg);
121 if( rv != SQLITE_OK )
123 fprintf(stderr, "SQLite Error: %s\n", errmsg);
124 sqlite3_free(errmsg);
125 sqlite3_query(gBank_Database, "ROLLBACK", NULL, NULL, NULL);
129 // Commit transaction
130 sqlite3_query(gBank_Database, "COMMIT", NULL, NULL, NULL);
138 int Bank_GetUserFlags(int UserID)
140 sqlite3_stmt *statement;
146 query = mkstr("SELECT acct_is_disabled,acct_is_coke,acct_is_wheel,acct_is_door,acct_is_internal FROM accounts WHERE acct_id=%i LIMIT 1", UserID);
147 rv = sqlite3_prepare_v2(gBank_Database, query, strlen(query)+1, &statement, NULL);
149 if( rv != SQLITE_OK ) {
150 fprintf(stderr, "SQLite Error: %s\n", sqlite3_errmsg(gBank_Database));
155 rv = sqlite3_step(statement);
156 if( rv != SQLITE_ROW )
158 sqlite3_finalise(statement);
159 if( rv == SQLITE_DONE )
161 return -1; // User not found
163 if( rv != SQLITE_OK ) {
164 fprintf(stderr, "SQLite Error: %s\n", sqlite3_errmsg(gBank_Database));
172 if( sqlite3_column_int(statement, 0) ) ret |= USER_FLAG_DISABLED;
174 if( sqlite3_column_int(statement, 1) ) ret |= USER_FLAG_COKE;
176 if( sqlite3_column_int(statement, 2) ) ret |= USER_FLAG_WHEEL;
178 if( sqlite3_column_int(statement, 3) ) ret |= USER_FLAG_DOOR;
180 if( sqlite3_column_int(statement, 3) ) ret |= USER_FLAG_INTERNAL;
182 // Destroy and return
183 sqlite3_finalise(statement);
191 int Bank_SetUserFlags(int UserID, int Mask, int Value)
197 #define MAP_FLAG(name, flag) (Mask&(flag)?(Value&(flag)?","name"=1":","name"=0"))
199 "UDPATE accounts WHERE acct_id=%i SET acct_id=acct_id%s%s%s%s%s",
200 MAP_FLAG("acct_is_coke", USER_FLAG_COKE),
201 MAP_FLAG("acct_is_wheel", USER_FLAG_WHEEL),
202 MAP_FLAG("acct_is_door", USER_FLAG_DOORGROUP),
203 MAP_FLAG("acct_is_internal", USER_FLAG_INTERNAL),
204 MAP_FLAG("acct_is_disabled", USER_FLAG_DISABLED)
209 rv = sqlite3_query(gBank_Database, query, NULL, NULL, &errmsg);
211 if( rv != SQLITE_OK )
213 fprintf(stderr, "SQLite Error: %s\n", errmsg);
214 sqlite3_free(errmsg);
224 int Bank_GetBalance(int User)
226 sqlite3_stmt *statement;
229 query = mkstr("SELECT acct_balance FROM accounts WHERE acct_id=%i", User);
234 sqlite3_stmt *Bank_int_MakeStatemnt(sqlite3 *Database, const char *Query)
238 rv = sqlite3_prepare_v2(Database, Query, strlen(Query)+1, &ret, NULL);
240 if( rv != SQLITE_OK ) {
241 fprintf(stderr, "SQLite Error: %s\n", sqlite3_errmsg(gBank_Database));
242 fprintf(stderr, "query = \"%s\"\n", Query);