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.
15 #include "../cokebank.h"
18 const char * const csBank_DatabaseSetup =
19 "CREATE TABLE IF NOT EXISTS accounts ("
20 " acct_id INTEGER PRIMARY KEY NOT NULL,"
21 " acct_balance INTEGER NOT NULL DEFAULT 0,"
22 " acct_last_seen DATETIME NOT NULL DEFAULT (datetime('now')),"
23 " acct_name STRING UNIQUE,"
24 " acct_uid INTEGER UNIQUE DEFAULT NULL,"
25 " acct_pin INTEGER DEFAULT NULL," //" acct_pin INTEGER CHECK (acct_pin > 0 AND acct_pin < 10000) DEFAULT NULL,"
26 " acct_is_disabled BOOLEAN NOT NULL DEFAULT false,"
27 " acct_is_coke BOOLEAN NOT NULL DEFAULT false,"
28 " acct_is_admin BOOLEAN NOT NULL DEFAULT false,"
29 " acct_is_door BOOLEAN NOT NULL DEFAULT false,"
30 " acct_is_internal BOOLEAN NOT NULL DEFAULT false"
32 "CREATE TABLE IF NOT EXISTS cards ("
33 " acct_id INTEGER NOT NULL,"
34 " card_name STRING NOT NULL UNIQUE,"
35 " FOREIGN KEY (acct_id) REFERENCES accounts (acct_id) ON DELETE CASCADE"
36 // Deletion of the account frees the card ^ ^ ^
38 "CREATE TABLE IF NOT EXISTS items ("
39 " item_id INTEGER PRIMARY KEY NOT NULL,"
40 " item_handler STRING NOT NULL,"
41 " item_index INTEGER NOT NULL,"
42 " item_name STRING NOT NULL,"
43 " item_price INTEGER NOT NULL,"
44 " item_is_enabled BOOLEAN NOT NULL DEFAULT true"
46 "INSERT INTO accounts (acct_name,acct_is_admin,acct_uid) VALUES ('root',1,0);"
47 "INSERT INTO accounts (acct_name,acct_is_internal,acct_uid) VALUES ('"COKEBANK_SALES_ACCT"',1,-1);"
48 "INSERT INTO accounts (acct_name,acct_is_internal,acct_uid) VALUES ('"COKEBANK_DEBT_ACCT"',1,-2);"
49 "INSERT INTO accounts (acct_name,acct_is_internal,acct_uid) VALUES ('"COKEBANK_FREE_ACCT"',1,-3);"
53 struct sAcctIterator // Unused really, just used as a void type
58 int Bank_Initialise(const char *Argument);
59 int Bank_Transfer(int SourceAcct, int DestAcct, int Ammount, const char *Reason);
60 int Bank_GetFlags(int AcctID);
61 int Bank_SetFlags(int AcctID, int Mask, int Value);
62 int Bank_GetBalance(int AcctID);
63 char *Bank_GetAcctName(int AcctID);
64 sqlite3_stmt *Bank_int_MakeStatemnt(sqlite3 *Database, const char *Query);
65 sqlite3_stmt *Bank_int_QuerySingle(sqlite3 *Database, const char *Query);
66 int Bank_int_IsValidName(const char *Name);
69 sqlite3 *gBank_Database;
72 int Bank_Initialise(const char *Argument)
77 rv = sqlite3_open(Argument, &gBank_Database);
80 fprintf(stderr, "CokeBank: Unable to open database '%s'\n", Argument);
81 fprintf(stderr, "Reason: %s\n", sqlite3_errmsg(gBank_Database));
82 sqlite3_close(gBank_Database);
87 rv = sqlite3_exec(gBank_Database, "SELECT acct_id FROM accounts LIMIT 1", NULL, NULL, &errmsg);
92 else if( rv == SQLITE_NOTFOUND || rv == SQLITE_ERROR )
96 rv = sqlite3_exec(gBank_Database, csBank_DatabaseSetup, NULL, NULL, &errmsg);
97 if( rv != SQLITE_OK ) {
98 fprintf(stderr, "Bank_Initialise - SQLite Error: %s\n", errmsg);
103 Log_Info("SQLite database rebuilt");
108 fprintf(stderr, "Bank_Initialise - SQLite Error: %s (rv = %i)\n", errmsg, rv);
109 sqlite3_free(errmsg);
119 int Bank_Transfer(int SourceUser, int DestUser, int Ammount, const char *Reason)
125 Reason = ""; // Shut GCC up
127 // Begin SQL Transaction
128 sqlite3_exec(gBank_Database, "BEGIN TRANSACTION", NULL, NULL, NULL);
130 // Take from the source
131 query = mkstr("UPDATE accounts SET acct_balance=acct_balance-%i,acct_last_seen=datetime('now') WHERE acct_id=%i", Ammount, SourceUser);
132 printf("query = \"%s\"\n", query);
133 rv = sqlite3_exec(gBank_Database, query, NULL, NULL, &errmsg);
135 if( rv != SQLITE_OK )
137 fprintf(stderr, "Bank_Transfer - SQLite Error: %s\n", errmsg);
138 sqlite3_free(errmsg);
139 sqlite3_exec(gBank_Database, "ROLLBACK", NULL, NULL, NULL);
143 // Give to the destination
144 query = mkstr("UPDATE accounts SET acct_balance=acct_balance+%i,acct_last_seen=datetime('now') WHERE acct_id=%i", Ammount, DestUser);
145 printf("query = \"%s\"\n", query);
146 rv = sqlite3_exec(gBank_Database, query, NULL, NULL, &errmsg);
148 if( rv != SQLITE_OK )
150 fprintf(stderr, "Bank_Transfer - SQLite Error: %s\n", errmsg);
151 sqlite3_free(errmsg);
152 sqlite3_exec(gBank_Database, "ROLLBACK", NULL, NULL, NULL);
156 // Commit transaction
157 sqlite3_exec(gBank_Database, "COMMIT", NULL, NULL, NULL);
165 int Bank_GetFlags(int UserID)
167 sqlite3_stmt *statement;
173 "SELECT acct_is_disabled,acct_is_coke,acct_is_admin,acct_is_door,acct_is_internal"
174 " FROM accounts WHERE acct_id=%i LIMIT 1",
177 statement = Bank_int_QuerySingle(gBank_Database, query);
179 if( !statement ) return -1;
184 if( sqlite3_column_int(statement, 0) ) ret |= USER_FLAG_DISABLED;
186 if( sqlite3_column_int(statement, 1) ) ret |= USER_FLAG_COKE;
188 if( sqlite3_column_int(statement, 2) ) ret |= USER_FLAG_ADMIN;
190 if( sqlite3_column_int(statement, 3) ) ret |= USER_FLAG_DOORGROUP;
192 if( sqlite3_column_int(statement, 4) ) ret |= USER_FLAG_INTERNAL;
194 // Destroy and return
195 sqlite3_finalize(statement);
203 int Bank_SetFlags(int UserID, int Mask, int Value)
209 #define MAP_FLAG(name, flag) (Mask&(flag)?(Value&(flag)?","name"=1":","name"=0"):"")
211 "UPDATE accounts SET acct_id=acct_id%s%s%s%s%s WHERE acct_id=%i",// LIMIT 1",
212 MAP_FLAG("acct_is_coke", USER_FLAG_COKE),
213 MAP_FLAG("acct_is_admin", USER_FLAG_ADMIN),
214 MAP_FLAG("acct_is_door", USER_FLAG_DOORGROUP),
215 MAP_FLAG("acct_is_internal", USER_FLAG_INTERNAL),
216 MAP_FLAG("acct_is_disabled", USER_FLAG_DISABLED),
222 rv = sqlite3_exec(gBank_Database, query, NULL, NULL, &errmsg);
223 if( rv != SQLITE_OK )
225 fprintf(stderr, "Bank_SetUserFlags - SQLite Error: %s\n", errmsg);
226 fprintf(stderr, "query = '%s'\n", query);
228 sqlite3_free(errmsg);
239 int Bank_GetBalance(int AcctID)
241 sqlite3_stmt *statement;
245 query = mkstr("SELECT acct_balance FROM accounts WHERE acct_id=%i LIMIT 1", AcctID);
246 statement = Bank_int_QuerySingle(gBank_Database, query);
248 if( !statement ) return INT_MIN;
251 ret = sqlite3_column_int(statement, 0);
253 // Clean up and return
254 sqlite3_finalize(statement);
259 * Get the name of an account
261 char *Bank_GetAcctName(int AcctID)
263 sqlite3_stmt *statement;
267 query = mkstr("SELECT acct_name FROM accounts WHERE acct_id=%i LIMIT 1", AcctID);
268 statement = Bank_int_QuerySingle(gBank_Database, query);
270 if( !statement ) return NULL;
273 ret = strdup( (const char*)sqlite3_column_text(statement, 0) );
275 // Clean up and return
276 sqlite3_finalize(statement);
281 * Get an account ID from a name
283 int Bank_GetAcctByName(const char *Name)
286 sqlite3_stmt *statement;
289 if( !Bank_int_IsValidName(Name) ) return -1;
291 query = mkstr("SELECT acct_id FROM accounts WHERE acct_name='%s' LIMIT 1", Name);
292 statement = Bank_int_QuerySingle(gBank_Database, query);
294 if( !statement ) return -1;
296 ret = sqlite3_column_int(statement, 0);
297 sqlite3_finalize(statement);
299 if( ret == 0 ) return -1;
304 * Create a new named account
306 int Bank_CreateAcct(const char *Name)
314 if( !Bank_int_IsValidName(Name) ) return -1;
315 query = mkstr("INSERT INTO accounts (acct_name) VALUES ('%s')", Name);
319 query = strdup("INSERT INTO accounts (acct_name) VALUES (NULL)");
322 rv = sqlite3_exec(gBank_Database, query, NULL, NULL, &errmsg);
323 if( rv != SQLITE_OK )
325 fprintf(stderr, "Bank_CreateAcct - SQLite Error: '%s'\n", errmsg);
326 fprintf(stderr, "Query = '%s'\n", query);
327 sqlite3_free(errmsg);
334 return sqlite3_last_insert_rowid(gBank_Database);
338 * Create an iterator for user accounts
340 tAcctIterator *Bank_Iterator(int FlagMask, int FlagValues, int Flags, int MinMaxBalance, time_t LastSeen)
343 const char *balanceClause;
344 const char *lastSeenClause;
345 const char *orderClause;
350 if( Flags & BANK_ITFLAG_MINBALANCE )
351 balanceClause = " AND acct_balance>=";
352 else if( Flags & BANK_ITFLAG_MAXBALANCE )
353 balanceClause = " AND acct_balance<=";
355 balanceClause = " AND 1!=";
359 // Last seen condition
360 if( Flags & BANK_ITFLAG_SEENAFTER )
361 lastSeenClause = " AND acct_last_seen>=";
362 else if( Flags & BANK_ITFLAG_SEENBEFORE )
363 lastSeenClause = " AND acct_last_seen<=";
365 lastSeenClause = " AND datetime(-1,'unixepoch')!=";
369 switch( Flags & BANK_ITFLAG_SORTMASK )
371 case BANK_ITFLAG_SORT_NONE:
375 case BANK_ITFLAG_SORT_NAME:
376 orderClause = "ORDER BY acct_name";
379 case BANK_ITFLAG_SORT_BAL:
380 orderClause = "ORDER BY acct_balance";
383 case BANK_ITFLAG_SORT_LASTSEEN:
384 orderClause = "ORDER BY acct_balance";
388 fprintf(stderr, "BUG: Unknown sort (%x) in SQLite CokeBank\n", Flags & BANK_ITFLAG_SORTMASK);
391 if( !(Flags & BANK_ITFLAG_REVSORT) )
394 #define MAP_FLAG(name, flag) (FlagMask&(flag)?(FlagValues&(flag)?" AND "name"=1":" AND "name"=0"):"")
395 query = mkstr("SELECT acct_id FROM accounts WHERE 1=1"
396 "%s%s%s%s%s" // Flags
398 "%sdatetime(%"PRIu64",'unixepoch')" // Last seen
399 "%s%s" // Sort and direction
401 MAP_FLAG("acct_is_coke", USER_FLAG_COKE),
402 MAP_FLAG("acct_is_admin", USER_FLAG_ADMIN),
403 MAP_FLAG("acct_is_door", USER_FLAG_DOORGROUP),
404 MAP_FLAG("acct_is_internal", USER_FLAG_INTERNAL),
405 MAP_FLAG("acct_is_disabled", USER_FLAG_DISABLED),
406 balanceClause, MinMaxBalance,
407 lastSeenClause, (uint64_t)LastSeen,
410 //printf("query = \"%s\"\n", query);
413 ret = Bank_int_MakeStatemnt(gBank_Database, query);
414 if( !ret ) return NULL;
422 * Get the next account in an iterator
424 int Bank_IteratorNext(tAcctIterator *It)
427 rv = sqlite3_step( (sqlite3_stmt*)It );
429 if( rv == SQLITE_DONE ) return -1;
430 if( rv != SQLITE_ROW ) {
431 fprintf(stderr, "Bank_IteratorNext - SQLite Error: %s\n", sqlite3_errmsg(gBank_Database));
435 return sqlite3_column_int( (sqlite3_stmt*)It, 0 );
441 void Bank_DelIterator(tAcctIterator *It)
443 sqlite3_finalize( (sqlite3_stmt*)It );
447 * Check user authentication token
449 int Bank_GetUserAuth(const char *Salt, const char *Username, const char *Password)
451 Salt = Password = Username; // Shut up GCC
454 return Bank_GetAcctByName(Username);
461 * Get an account number given a card ID
462 * NOTE: Actually ends up just being an alternate authentication token,
463 * as no checking is done on the ID's validity, save for SQL sanity.
465 int Bank_GetAcctByCard(const char *CardID)
468 sqlite3_stmt *statement;
471 if( !Bank_int_IsValidName(CardID) )
474 query = mkstr("SELECT acct_id FROM cards WHERE card_name='%s' LIMIT 1", CardID);
475 statement = Bank_int_QuerySingle(gBank_Database, query);
477 if( !statement ) return -1;
479 ret = sqlite3_column_int(statement, 0);
481 sqlite3_finalize(statement);
487 * Add a card to an account
489 int Bank_AddAcctCard(int AcctID, const char *CardID)
495 if( !Bank_int_IsValidName(CardID) )
498 // TODO: Check the AcctID too
501 query = mkstr("INSERT INTO cards (acct_id,card_name) VALUES (%i,'%s')",
503 rv = sqlite3_exec(gBank_Database, query, NULL, NULL, &errmsg);
504 if( rv == SQLITE_CONSTRAINT )
506 sqlite3_free(errmsg);
508 return 2; // Card in use
510 if( rv != SQLITE_OK )
512 fprintf(stderr, "Bank_AddAcctCard - SQLite Error: '%s'\n", errmsg);
513 fprintf(stderr, "Query = '%s'\n", query);
514 sqlite3_free(errmsg);
524 * Create a SQLite Statement
526 sqlite3_stmt *Bank_int_MakeStatemnt(sqlite3 *Database, const char *Query)
530 rv = sqlite3_prepare_v2(Database, Query, strlen(Query)+1, &ret, NULL);
531 if( rv != SQLITE_OK ) {
532 fprintf(stderr, "SQLite Error: %s\n", sqlite3_errmsg(Database));
533 fprintf(stderr, "query = \"%s\"\n", Query);
541 * Create a SQLite statement and query it for the first row
542 * Returns NULL if the the set is empty
544 sqlite3_stmt *Bank_int_QuerySingle(sqlite3 *Database, const char *Query)
550 printf("Bank_int_QuerySingle: (Query='%s')\n", Query);
554 ret = Bank_int_MakeStatemnt(Database, Query);
557 printf("Bank_int_QuerySingle: RETURN NULL ret=NULL\n");
563 rv = sqlite3_step(ret);
564 // - Empty result set
565 if( rv == SQLITE_DONE ) {
567 printf("Bank_int_QuerySingle: RETURN NULL (rv == SQLITE_DONE)\n");
572 if( rv != SQLITE_ROW ) {
573 fprintf(stderr, "SQLite Error: %s\n", sqlite3_errmsg(gBank_Database));
574 fprintf(stderr, "query = \"%s\"\n", Query);
579 printf("Bank_int_QuerySingle: RETURN %p\n", ret);
585 * \brief Checks if the passed account name is valid
587 int Bank_int_IsValidName(const char *Name)
591 if( *Name == '\'' ) return 0;