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"
20 const char * const csBank_DatabaseSetup =
21 "CREATE TABLE IF NOT EXISTS accounts ("
22 " acct_id INTEGER PRIMARY KEY NOT NULL,"
23 " acct_balance INTEGER NOT NULL DEFAULT 0,"
24 " acct_last_seen DATETIME NOT NULL DEFAULT (datetime('now')),"
25 " acct_name STRING UNIQUE,"
26 " acct_uid INTEGER UNIQUE DEFAULT NULL,"
27 " acct_pin INTEGER DEFAULT NULL," //" acct_pin INTEGER CHECK (acct_pin > 0 AND acct_pin < 10000) DEFAULT NULL,"
28 " acct_is_disabled BOOLEAN NOT NULL DEFAULT false,"
29 " acct_is_coke BOOLEAN NOT NULL DEFAULT false,"
30 " acct_is_admin BOOLEAN NOT NULL DEFAULT false,"
31 " acct_is_door BOOLEAN NOT NULL DEFAULT false,"
32 " acct_is_internal BOOLEAN NOT NULL DEFAULT false"
34 "CREATE TABLE IF NOT EXISTS cards ("
35 " acct_id INTEGER NOT NULL,"
36 " card_name STRING NOT NULL UNIQUE,"
37 " FOREIGN KEY (acct_id) REFERENCES accounts (acct_id) ON DELETE CASCADE"
38 // Deletion of the account frees the card ^ ^ ^
40 "CREATE TABLE IF NOT EXISTS items ("
41 " item_id INTEGER PRIMARY KEY NOT NULL,"
42 " item_handler STRING NOT NULL,"
43 " item_index INTEGER NOT NULL,"
44 " item_name STRING NOT NULL,"
45 " item_price INTEGER NOT NULL,"
46 " item_is_enabled BOOLEAN NOT NULL DEFAULT true"
48 "INSERT INTO accounts (acct_name,acct_is_admin,acct_uid) VALUES ('root',1,0);"
49 "INSERT INTO accounts (acct_name,acct_is_internal,acct_uid) VALUES ('"COKEBANK_SALES_ACCT"',1,-1);"
50 "INSERT INTO accounts (acct_name,acct_is_internal,acct_uid) VALUES ('"COKEBANK_DEBT_ACCT"',1,-2);"
51 "INSERT INTO accounts (acct_name,acct_is_internal,acct_uid) VALUES ('"COKEBANK_FREE_ACCT"',1,-3);"
55 struct sAcctIterator // Unused really, just used as a void type
60 int Bank_Initialise(const char *Argument);
61 int Bank_Transfer(int SourceAcct, int DestAcct, int Ammount, const char *Reason);
62 int Bank_GetFlags(int AcctID);
63 int Bank_SetFlags(int AcctID, int Mask, int Value);
64 int Bank_GetBalance(int AcctID);
65 char *Bank_GetAcctName(int AcctID);
66 int Bank_IsPinValid(int AcctID, int Pin);
67 void Bank_SetPin(int AcctID, int Pin);
68 sqlite3_stmt *Bank_int_MakeStatemnt(sqlite3 *Database, const char *Query);
69 int Bank_int_QueryNone(sqlite3 *Database, const char *Query, char **ErrorMessage);
70 sqlite3_stmt *Bank_int_QuerySingle(sqlite3 *Database, const char *Query);
71 int Bank_int_IsValidName(const char *Name);
74 sqlite3 *gBank_Database;
77 int Bank_Initialise(const char *Argument)
82 rv = sqlite3_open(Argument, &gBank_Database);
85 fprintf(stderr, "CokeBank: Unable to open database '%s'\n", Argument);
86 fprintf(stderr, "Reason: %s\n", sqlite3_errmsg(gBank_Database));
87 sqlite3_close(gBank_Database);
92 rv = Bank_int_QueryNone(gBank_Database, "SELECT acct_id FROM accounts LIMIT 1", &errmsg);
97 else if( rv == SQLITE_NOTFOUND || rv == SQLITE_ERROR )
101 rv = Bank_int_QueryNone(gBank_Database, csBank_DatabaseSetup, &errmsg);
102 if( rv != SQLITE_OK ) {
103 fprintf(stderr, "Bank_Initialise - SQLite Error: %s\n", errmsg);
104 sqlite3_free(errmsg);
108 Log_Info("SQLite database rebuilt (%s)", Argument);
113 fprintf(stderr, "Bank_Initialise - SQLite Error: %s (rv = %i)\n", errmsg, rv);
114 sqlite3_free(errmsg);
124 int Bank_Transfer(int SourceUser, int DestUser, int Ammount, const char *Reason __attribute__((unused)))
130 Reason = ""; // Shut GCC up
132 // Begin SQL Transaction
133 Bank_int_QueryNone(gBank_Database, "BEGIN TRANSACTION", NULL);
135 // Take from the source
136 query = mkstr("UPDATE accounts SET acct_balance=acct_balance%+i,acct_last_seen=datetime('now') WHERE acct_id=%i", -Ammount, SourceUser);
137 // printf("query = \"%s\"\n", query);
138 rv = Bank_int_QueryNone(gBank_Database, query, &errmsg);
140 if( rv != SQLITE_OK )
142 fprintf(stderr, "Bank_Transfer - SQLite Error: %s\n", errmsg);
143 sqlite3_free(errmsg);
144 Bank_int_QueryNone(gBank_Database, "ROLLBACK", NULL);
148 // Give to the destination
149 query = mkstr("UPDATE accounts SET acct_balance=acct_balance%+i,acct_last_seen=datetime('now') WHERE acct_id=%i", Ammount, DestUser);
150 // printf("query = \"%s\"\n", query);
151 rv = Bank_int_QueryNone(gBank_Database, query, &errmsg);
153 if( rv != SQLITE_OK )
155 fprintf(stderr, "Bank_Transfer - SQLite Error: %s\n", errmsg);
156 sqlite3_free(errmsg);
157 Bank_int_QueryNone(gBank_Database, "ROLLBACK", NULL);
161 // Commit transaction
162 Bank_int_QueryNone(gBank_Database, "COMMIT", NULL);
170 int Bank_GetFlags(int UserID)
172 sqlite3_stmt *statement;
178 "SELECT acct_is_disabled,acct_is_coke,acct_is_admin,acct_is_door,acct_is_internal"
179 " FROM accounts WHERE acct_id=%i LIMIT 1",
182 statement = Bank_int_QuerySingle(gBank_Database, query);
184 if( !statement ) return -1;
189 if( sqlite3_column_int(statement, 0) ) ret |= USER_FLAG_DISABLED;
191 if( sqlite3_column_int(statement, 1) ) ret |= USER_FLAG_COKE;
193 if( sqlite3_column_int(statement, 2) ) ret |= USER_FLAG_ADMIN;
195 if( sqlite3_column_int(statement, 3) ) ret |= USER_FLAG_DOORGROUP;
197 if( sqlite3_column_int(statement, 4) ) ret |= USER_FLAG_INTERNAL;
199 // Destroy and return
200 sqlite3_finalize(statement);
208 int Bank_SetFlags(int UserID, int Mask, int Value)
214 #define MAP_FLAG(name, flag) (Mask&(flag)?(Value&(flag)?","name"=1":","name"=0"):"")
216 "UPDATE accounts SET acct_id=acct_id%s%s%s%s%s WHERE acct_id=%i",// LIMIT 1",
217 MAP_FLAG("acct_is_coke", USER_FLAG_COKE),
218 MAP_FLAG("acct_is_admin", USER_FLAG_ADMIN),
219 MAP_FLAG("acct_is_door", USER_FLAG_DOORGROUP),
220 MAP_FLAG("acct_is_internal", USER_FLAG_INTERNAL),
221 MAP_FLAG("acct_is_disabled", USER_FLAG_DISABLED),
227 printf("Bank_SetFlags: query=\"%s\"\n", query);
231 rv = Bank_int_QueryNone(gBank_Database, query, &errmsg);
232 if( rv != SQLITE_OK )
234 fprintf(stderr, "Bank_SetUserFlags - SQLite Error: %s\n", errmsg);
235 fprintf(stderr, "query = '%s'\n", query);
237 sqlite3_free(errmsg);
248 int Bank_GetBalance(int AcctID)
250 sqlite3_stmt *statement;
254 query = mkstr("SELECT acct_balance FROM accounts WHERE acct_id=%i LIMIT 1", AcctID);
255 statement = Bank_int_QuerySingle(gBank_Database, query);
257 if( !statement ) return INT_MIN;
260 ret = sqlite3_column_int(statement, 0);
262 // Clean up and return
263 sqlite3_finalize(statement);
268 * Get the name of an account
270 char *Bank_GetAcctName(int AcctID)
272 sqlite3_stmt *statement;
276 query = mkstr("SELECT acct_name FROM accounts WHERE acct_id=%i LIMIT 1", AcctID);
277 statement = Bank_int_QuerySingle(gBank_Database, query);
279 if( !statement ) return NULL;
282 ret = strdup( (const char*)sqlite3_column_text(statement, 0) );
284 // Clean up and return
285 sqlite3_finalize(statement);
290 * Get an account ID from a name
292 int Bank_GetAcctByName(const char *Name, int bCreate)
295 sqlite3_stmt *statement;
298 // printf("Bank_GetAcctByName: (Name='%s',bCreate=%i)\n", Name, bCreate);
300 if( !Bank_int_IsValidName(Name) ) {
301 // printf("RETURN: -1 (Bad name)");
305 query = mkstr("SELECT acct_id FROM accounts WHERE acct_name='%s' LIMIT 1", Name);
306 statement = Bank_int_QuerySingle(gBank_Database, query);
309 // printf("User not found\n");
310 if( bCreate ) return Bank_CreateAcct(Name);
314 ret = sqlite3_column_int(statement, 0);
315 sqlite3_finalize(statement);
317 // printf("ret = %i\n", ret);
326 * Create a new named account
328 int Bank_CreateAcct(const char *Name)
336 if( !Bank_int_IsValidName(Name) ) return -1;
337 query = mkstr("INSERT INTO accounts (acct_name) VALUES ('%s')", Name);
341 query = strdup("INSERT INTO accounts (acct_name) VALUES (NULL)");
344 rv = Bank_int_QueryNone(gBank_Database, query, &errmsg);
345 if( rv != SQLITE_OK )
347 fprintf(stderr, "Bank_CreateAcct - SQLite Error: '%s'\n", errmsg);
348 fprintf(stderr, "Query = '%s'\n", query);
349 sqlite3_free(errmsg);
356 return sqlite3_last_insert_rowid(gBank_Database);
359 int Bank_IsPinValid(int AcctID, int Pin)
361 char *query = mkstr("SELECT acct_id FROM accounts WHERE acct_id=%i AND acct_pin=%i LIMIT 1", AcctID, Pin);
362 sqlite3_stmt *statement = Bank_int_QuerySingle(gBank_Database, query);
366 sqlite3_finalize(statement);
369 return (statement != NULL);
372 void Bank_SetPin(int AcctID, int Pin)
375 char *query = mkstr("UPDATE accounts SET acct_pin=%i WHERE acct_id=%i", Pin, AcctID);
376 int rv = Bank_int_QueryNone(gBank_Database, query, &errmsg);
377 if( rv != SQLITE_OK )
379 fprintf(stderr, "Bank_CreateAcct - SQLite Error: '%s'\n", errmsg);
380 fprintf(stderr, "Query = '%s'\n", query);
381 sqlite3_free(errmsg);
388 * Create an iterator for user accounts
390 tAcctIterator *Bank_Iterator(int FlagMask, int FlagValues, int Flags, int MinMaxBalance, time_t LastSeen)
393 const char *balanceClause;
394 const char *lastSeenClause;
395 const char *orderClause;
400 if( Flags & BANK_ITFLAG_MINBALANCE )
401 balanceClause = " AND acct_balance>=";
402 else if( Flags & BANK_ITFLAG_MAXBALANCE )
403 balanceClause = " AND acct_balance<=";
405 balanceClause = " AND 1!=";
409 // Last seen condition
410 if( Flags & BANK_ITFLAG_SEENAFTER )
411 lastSeenClause = " AND acct_last_seen>=";
412 else if( Flags & BANK_ITFLAG_SEENBEFORE )
413 lastSeenClause = " AND acct_last_seen<=";
415 lastSeenClause = " AND datetime(-1,'unixepoch')!=";
419 switch( Flags & BANK_ITFLAG_SORTMASK )
421 case BANK_ITFLAG_SORT_NONE:
425 case BANK_ITFLAG_SORT_NAME:
426 orderClause = "ORDER BY acct_name";
429 case BANK_ITFLAG_SORT_BAL:
430 orderClause = "ORDER BY acct_balance";
433 case BANK_ITFLAG_SORT_LASTSEEN:
434 orderClause = "ORDER BY acct_balance";
438 fprintf(stderr, "BUG: Unknown sort (%x) in SQLite CokeBank\n", Flags & BANK_ITFLAG_SORTMASK);
441 if( !(Flags & BANK_ITFLAG_REVSORT) )
444 #define MAP_FLAG(name, flag) (FlagMask&(flag)?(FlagValues&(flag)?" AND "name"=1":" AND "name"=0"):"")
445 query = mkstr("SELECT acct_id FROM accounts WHERE 1=1"
446 "%s%s%s%s%s" // Flags
448 "%sdatetime(%"PRIu64",'unixepoch')" // Last seen
449 "%s%s" // Sort and direction
451 MAP_FLAG("acct_is_coke", USER_FLAG_COKE),
452 MAP_FLAG("acct_is_admin", USER_FLAG_ADMIN),
453 MAP_FLAG("acct_is_door", USER_FLAG_DOORGROUP),
454 MAP_FLAG("acct_is_internal", USER_FLAG_INTERNAL),
455 MAP_FLAG("acct_is_disabled", USER_FLAG_DISABLED),
456 balanceClause, MinMaxBalance,
457 lastSeenClause, (uint64_t)LastSeen,
460 //printf("query = \"%s\"\n", query);
463 ret = Bank_int_MakeStatemnt(gBank_Database, query);
464 if( !ret ) return NULL;
472 * Get the next account in an iterator
474 int Bank_IteratorNext(tAcctIterator *It)
477 rv = sqlite3_step( (sqlite3_stmt*)It );
479 if( rv == SQLITE_DONE ) return -1;
480 if( rv != SQLITE_ROW ) {
481 fprintf(stderr, "Bank_IteratorNext - SQLite Error: %s\n", sqlite3_errmsg(gBank_Database));
485 return sqlite3_column_int( (sqlite3_stmt*)It, 0 );
491 void Bank_DelIterator(tAcctIterator *It)
493 sqlite3_finalize( (sqlite3_stmt*)It );
497 * Check user authentication token
499 int Bank_GetUserAuth(const char *Salt, const char *Username, const char *Password)
501 Salt = Password = Username; // Shut up GCC
505 return Bank_GetAcctByName(Username);
512 * Get an account number given a card ID
513 * NOTE: Actually ends up just being an alternate authentication token,
514 * as no checking is done on the ID's validity, save for SQL sanity.
516 int Bank_GetAcctByCard(const char *CardID)
519 sqlite3_stmt *statement;
522 if( !Bank_int_IsValidName(CardID) )
525 query = mkstr("SELECT acct_id FROM cards WHERE card_name='%s' LIMIT 1", CardID);
526 statement = Bank_int_QuerySingle(gBank_Database, query);
528 if( !statement ) return -1;
530 ret = sqlite3_column_int(statement, 0);
532 sqlite3_finalize(statement);
538 * Add a card to an account
540 int Bank_AddAcctCard(int AcctID, const char *CardID)
546 if( !Bank_int_IsValidName(CardID) )
549 // TODO: Check the AcctID too
552 query = mkstr("INSERT INTO cards (acct_id,card_name) VALUES (%i,'%s')",
554 rv = Bank_int_QueryNone(gBank_Database, query, &errmsg);
555 if( rv == SQLITE_CONSTRAINT )
557 sqlite3_free(errmsg);
559 return 2; // Card in use
561 if( rv != SQLITE_OK )
563 fprintf(stderr, "Bank_AddAcctCard - SQLite Error: '%s'\n", errmsg);
564 fprintf(stderr, "Query = '%s'\n", query);
565 sqlite3_free(errmsg);
575 * Create a SQLite Statement
577 sqlite3_stmt *Bank_int_MakeStatemnt(sqlite3 *Database, const char *Query)
581 rv = sqlite3_prepare_v2(Database, Query, strlen(Query)+1, &ret, NULL);
582 if( rv != SQLITE_OK ) {
583 fprintf(stderr, "SQLite Error: %s\n", sqlite3_errmsg(Database));
584 fprintf(stderr, "query = \"%s\"\n", Query);
591 int Bank_int_QueryNone(sqlite3 *Database, const char *Query, char **ErrorMessage)
594 printf("Bank_int_QueryNone: (Query='%s')\n", Query);
596 return sqlite3_exec(Database, Query, NULL, NULL, ErrorMessage);
600 * Create a SQLite statement and query it for the first row
601 * Returns NULL if the the set is empty
603 sqlite3_stmt *Bank_int_QuerySingle(sqlite3 *Database, const char *Query)
609 printf("Bank_int_QuerySingle: (Query='%s')\n", Query);
613 ret = Bank_int_MakeStatemnt(Database, Query);
616 printf("Bank_int_QuerySingle: RETURN NULL ret=NULL\n");
622 rv = sqlite3_step(ret);
623 // - Empty result set
624 if( rv == SQLITE_DONE ) {
626 printf("Bank_int_QuerySingle: RETURN NULL (rv == SQLITE_DONE)\n");
631 if( rv != SQLITE_ROW ) {
632 fprintf(stderr, "SQLite Error: %s\n", sqlite3_errmsg(gBank_Database));
633 fprintf(stderr, "query = \"%s\"\n", Query);
638 printf("Bank_int_QuerySingle: RETURN %p\n", ret);
644 * \brief Checks if the passed account name is valid
646 int Bank_int_IsValidName(const char *Name)
648 if( !Name ) return 0;
651 if( *Name == '\'' ) return 0;