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 SourceAcct, int DestAcct, int Ammount, const char *Reason);
35 int Bank_GetUserFlags(int AcctID);
36 int Bank_SetUserFlags(int AcctID, int Mask, int Value);
37 int Bank_GetBalance(int AcctID);
38 char *Bank_GetAcctName(int AcctID);
39 sqlite3_stmt *Bank_int_MakeStatemnt(sqlite3 *Database, const char *Query);
40 sqlite3_stmt *Bank_int_QuerySingle(sqlite3 *Database, const char *Query);
43 sqlite3 *gBank_Database;
46 int Bank_Initialise(const char *Argument)
51 rv = sqlite3_open(Argument, &gBank_Database);
54 fprintf(stderr, "CokeBank: Unable to open database '%s'\n", Argument);
55 fprintf(stderr, "Reason: %s\n", sqlite3_errmsg(gBank_Database));
56 sqlite3_close(gBank_Database);
61 rv = sqlite3_exec(gBank_Database, "SELECT acct_id FROM accounts LIMIT 1", NULL, NULL, &errmsg);
66 else if( rv == SQLITE_NOTFOUND )
71 rv = sqlite3_exec(gBank_Database, csBank_CreateAccountQry, NULL, NULL, &errmsg);
72 if( rv != SQLITE_OK ) {
73 fprintf(stderr, "SQLite Error: %s\n", errmsg);
78 rv = sqlite3_exec(gBank_Database, csBank_CreateCardsQry, NULL, NULL, &errmsg);
79 if( rv != SQLITE_OK ) {
80 fprintf(stderr, "SQLite Error: %s\n", errmsg);
88 fprintf(stderr, "SQLite Error: %s\n", errmsg);
99 int Bank_Transfer(int SourceUser, int DestUser, int Ammount, const char *Reason)
105 // Begin SQL Transaction
106 sqlite3_exec(gBank_Database, "BEGIN TRANSACTION", NULL, NULL, NULL);
108 // Take from the source
109 query = mkstr("UPDATE accounts SET acct_balance=acct_balance-%i WHERE acct_id=%i", Ammount, SourceUser);
110 rv = sqlite3_exec(gBank_Database, query, NULL, NULL, &errmsg);
112 if( rv != SQLITE_OK )
114 fprintf(stderr, "SQLite Error: %s\n", errmsg);
115 sqlite3_free(errMsg);
116 sqlite3_query(gBank_Database, "ROLLBACK", NULL, NULL, NULL);
120 // Give to the destination
121 query = mkstr("UPDATE accounts SET acct_balance=acct_balance+%i WHERE acct_id=%i", Ammount, DestUser);
122 rv = sqlite3_exec(gBank_Database, query, NULL, NULL, &errmsg);
124 if( rv != SQLITE_OK )
126 fprintf(stderr, "SQLite Error: %s\n", errmsg);
127 sqlite3_free(errmsg);
128 sqlite3_query(gBank_Database, "ROLLBACK", NULL, NULL, NULL);
132 // Commit transaction
133 sqlite3_query(gBank_Database, "COMMIT", NULL, NULL, NULL);
141 int Bank_GetUserFlags(int UserID)
143 sqlite3_stmt *statement;
150 "SELECT acct_is_disabled,acct_is_coke,acct_is_wheel,acct_is_door,acct_is_internal"
151 " FROM accounts WHERE acct_id=%i LIMIT 1",
154 statement = Bank_int_QuerySingle(gBank_Database, query);
156 if( !statement ) return -1;
161 if( sqlite3_column_int(statement, 0) ) ret |= USER_FLAG_DISABLED;
163 if( sqlite3_column_int(statement, 1) ) ret |= USER_FLAG_COKE;
165 if( sqlite3_column_int(statement, 2) ) ret |= USER_FLAG_WHEEL;
167 if( sqlite3_column_int(statement, 3) ) ret |= USER_FLAG_DOOR;
169 if( sqlite3_column_int(statement, 3) ) ret |= USER_FLAG_INTERNAL;
171 // Destroy and return
172 sqlite3_finalise(statement);
180 int Bank_SetUserFlags(int UserID, int Mask, int Value)
186 #define MAP_FLAG(name, flag) (Mask&(flag)?(Value&(flag)?","name"=1":","name"=0"))
188 "UDPATE accounts WHERE acct_id=%i SET acct_id=acct_id%s%s%s%s%s",
189 MAP_FLAG("acct_is_coke", USER_FLAG_COKE),
190 MAP_FLAG("acct_is_wheel", USER_FLAG_WHEEL),
191 MAP_FLAG("acct_is_door", USER_FLAG_DOORGROUP),
192 MAP_FLAG("acct_is_internal", USER_FLAG_INTERNAL),
193 MAP_FLAG("acct_is_disabled", USER_FLAG_DISABLED)
198 rv = sqlite3_query(gBank_Database, query, NULL, NULL, &errmsg);
200 if( rv != SQLITE_OK )
202 fprintf(stderr, "SQLite Error: %s\n", errmsg);
203 sqlite3_free(errmsg);
213 int Bank_GetBalance(int AcctID)
215 sqlite3_stmt *statement;
219 query = mkstr("SELECT acct_balance FROM accounts WHERE acct_id=%i LIMIT 1", AcctID);
220 statement = Bank_int_QuerySingle(gBank_Database, query);
222 if( !statement ) return INT_MIN;
225 ret = sqlite3_column_int(statement, 0);
227 // Clean up and return
228 sqlite3_finalise(statement);
233 * Get the name of an account
235 char *Bank_GetUserName(int AcctID)
237 sqlite3_stmt *statement;
241 query = mkstr("SELECT acct_name FROM accounts WHERE acct_id=%i LIMIT 1", AcctID);
242 statement = Bank_int_QuerySingle(gBank_Database, query);
244 if( !statement ) return NULL;
247 ret = strdup( sqlite3_column_text(statement, 0) );
249 // Clean up and return
250 sqlite3_finalise(statement);
255 * Create a SQLite Statement
257 sqlite3_stmt *Bank_int_MakeStatemnt(sqlite3 *Database, const char *Query)
261 rv = sqlite3_prepare_v2(Database, Query, strlen(Query)+1, &ret, NULL);
263 if( rv != SQLITE_OK ) {
264 fprintf(stderr, "SQLite Error: %s\n", sqlite3_errmsg(Database));
265 fprintf(stderr, "query = \"%s\"\n", Query);
273 * Create a SQLite statement and query it for the first row
274 * Returns NULL if the the set is empty
276 sqlite3_stmt *Bank_int_QuerySingle(sqlite3 *Database, const char *Query)
282 ret = Bank_int_MakeStatemnt(Database, Query);
283 if( !statement ) return NULL;
286 rv = sqlite3_step(statement);
287 // - Empty result set
288 if( rv == SQLITE_DONE ) return NULL;
290 if( rv != SQLITE_ROW ) {
291 fprintf(stderr, "SQLite Error: %s\n", sqlite3_errmsg(gBank_Database));
292 fprintf(stderr, "query = \"%s\"\n", Query);