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.
14 #include "../cokebank.h"
17 const char * const csBank_CreateAccountQry = "CREATE TABLE IF NOT EXISTS accounts ("
18 " acct_id INTEGER PRIMARY KEY NOT NULL,"
19 " acct_balance INTEGER NOT NULL DEFAULT 0,"
20 " acct_name STRING UNIQUE,"
21 " acct_uid INTEGER UNIQUE DEFAULT NULL,"
22 " acct_pin INTEGER CHECK (acct_pin > 0 AND acct_pin < 10000) DEFAULT NULL,"
23 " acct_is_disabled BOOLEAN NOT NULL DEFAULT false,"
24 " acct_is_coke BOOLEAN NOT NULL DEFAULT false,"
25 " acct_is_wheel BOOLEAN NOT NULL DEFAULT false,"
26 " acct_is_door BOOLEAN NOT NULL DEFAULT false,"
27 " acct_is_internal BOOLEAN NOT NULL DEFAULT false"
29 const char * const csBank_CreateCardsQry = "CREATE TABLE IF NOT EXISTS cards ("
30 " acct_id INTEGER NOT NULL,"
31 " card_name STRING NOT NULL UNIQUE,"
32 " FOREIGN KEY (acct_id) REFERENCES accounts (acct_id) ON DELETE CASCADE"
33 // Deletion of the account frees the card ^ ^ ^
37 struct sAcctIterator // Unused really, just used as a void type
42 int Bank_Initialise(const char *Argument);
43 int Bank_Transfer(int SourceAcct, int DestAcct, int Ammount, const char *Reason);
44 int Bank_GetUserFlags(int AcctID);
45 int Bank_SetUserFlags(int AcctID, int Mask, int Value);
46 int Bank_GetBalance(int AcctID);
47 char *Bank_GetAcctName(int AcctID);
48 sqlite3_stmt *Bank_int_MakeStatemnt(sqlite3 *Database, const char *Query);
49 sqlite3_stmt *Bank_int_QuerySingle(sqlite3 *Database, const char *Query);
50 int Bank_int_IsValidName(const char *Name);
53 sqlite3 *gBank_Database;
56 int Bank_Initialise(const char *Argument)
61 rv = sqlite3_open(Argument, &gBank_Database);
64 fprintf(stderr, "CokeBank: Unable to open database '%s'\n", Argument);
65 fprintf(stderr, "Reason: %s\n", sqlite3_errmsg(gBank_Database));
66 sqlite3_close(gBank_Database);
71 rv = sqlite3_exec(gBank_Database, "SELECT acct_id FROM accounts LIMIT 1", NULL, NULL, &errmsg);
76 else if( rv == SQLITE_NOTFOUND )
81 rv = sqlite3_exec(gBank_Database, csBank_CreateAccountQry, NULL, NULL, &errmsg);
82 if( rv != SQLITE_OK ) {
83 fprintf(stderr, "SQLite Error: %s\n", errmsg);
88 rv = sqlite3_exec(gBank_Database, csBank_CreateCardsQry, NULL, NULL, &errmsg);
89 if( rv != SQLITE_OK ) {
90 fprintf(stderr, "SQLite Error: %s\n", errmsg);
98 fprintf(stderr, "SQLite Error: %s\n", errmsg);
109 int Bank_Transfer(int SourceUser, int DestUser, int Ammount, const char *Reason)
115 Reason = ""; // Shut GCC up
117 // Begin SQL Transaction
118 sqlite3_exec(gBank_Database, "BEGIN TRANSACTION", NULL, NULL, NULL);
120 // Take from the source
121 query = mkstr("UPDATE accounts SET acct_balance=acct_balance-%i WHERE acct_id=%i", Ammount, SourceUser);
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_exec(gBank_Database, "ROLLBACK", NULL, NULL, NULL);
132 // Give to the destination
133 query = mkstr("UPDATE accounts SET acct_balance=acct_balance+%i WHERE acct_id=%i", Ammount, DestUser);
134 rv = sqlite3_exec(gBank_Database, query, NULL, NULL, &errmsg);
136 if( rv != SQLITE_OK )
138 fprintf(stderr, "SQLite Error: %s\n", errmsg);
139 sqlite3_free(errmsg);
140 sqlite3_exec(gBank_Database, "ROLLBACK", NULL, NULL, NULL);
144 // Commit transaction
145 sqlite3_exec(gBank_Database, "COMMIT", NULL, NULL, NULL);
153 int Bank_GetUserFlags(int UserID)
155 sqlite3_stmt *statement;
161 "SELECT acct_is_disabled,acct_is_coke,acct_is_wheel,acct_is_door,acct_is_internal"
162 " FROM accounts WHERE acct_id=%i LIMIT 1",
165 statement = Bank_int_QuerySingle(gBank_Database, query);
167 if( !statement ) return -1;
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_DOORGROUP;
180 if( sqlite3_column_int(statement, 3) ) ret |= USER_FLAG_INTERNAL;
182 // Destroy and return
183 sqlite3_finalize(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",
201 MAP_FLAG("acct_is_coke", USER_FLAG_COKE),
202 MAP_FLAG("acct_is_wheel", USER_FLAG_WHEEL),
203 MAP_FLAG("acct_is_door", USER_FLAG_DOORGROUP),
204 MAP_FLAG("acct_is_internal", USER_FLAG_INTERNAL),
205 MAP_FLAG("acct_is_disabled", USER_FLAG_DISABLED)
210 rv = sqlite3_exec(gBank_Database, query, NULL, NULL, &errmsg);
212 if( rv != SQLITE_OK )
214 fprintf(stderr, "SQLite Error: %s\n", errmsg);
215 sqlite3_free(errmsg);
225 int Bank_GetBalance(int AcctID)
227 sqlite3_stmt *statement;
231 query = mkstr("SELECT acct_balance FROM accounts WHERE acct_id=%i LIMIT 1", AcctID);
232 statement = Bank_int_QuerySingle(gBank_Database, query);
234 if( !statement ) return INT_MIN;
237 ret = sqlite3_column_int(statement, 0);
239 // Clean up and return
240 sqlite3_finalize(statement);
245 * Get the name of an account
247 char *Bank_GetAcctName(int AcctID)
249 sqlite3_stmt *statement;
253 query = mkstr("SELECT acct_name FROM accounts WHERE acct_id=%i LIMIT 1", AcctID);
254 statement = Bank_int_QuerySingle(gBank_Database, query);
256 if( !statement ) return NULL;
259 ret = strdup( (const char*)sqlite3_column_text(statement, 0) );
261 // Clean up and return
262 sqlite3_finalize(statement);
267 * Get an account ID from a name
269 int Bank_GetAcctByName(const char *Name)
272 sqlite3_stmt *statement;
275 if( !Bank_int_IsValidName(Name) ) return -1;
277 query = mkstr("SELECT acct_id FROM accounts WHERE acct_name='%s' LIMIT 1", Name);
278 statement = Bank_int_QuerySingle(gBank_Database, query);
280 if( !statement ) return -1;
282 ret = sqlite3_column_int(statement, 0);
284 sqlite3_finalize(statement);
289 * Create a new named account
291 int Bank_CreateAcct(const char *Name)
299 if( !Bank_int_IsValidName(Name) ) return -1;
300 query = mkstr("INSERT INTO accounts (acct_name) VALUES ('%s')", Name);
304 query = strdup("INSERT INTO accounts (acct_name) VALUES (NULL)");
307 rv = sqlite3_exec(gBank_Database, query, NULL, NULL, &errmsg);
308 if( rv != SQLITE_OK )
310 fprintf(stderr, "SQLite Error: '%s'\n", errmsg);
311 fprintf(stderr, "Query = '%s'\n", query);
312 sqlite3_free(errmsg);
319 return sqlite3_last_insert_rowid(gBank_Database);
323 * Create an iterator for user accounts
325 tAcctIterator *Bank_Iterator(int FlagMask, int FlagValues, int Flags, int MinMaxBalance, time_t LastSeen)
328 const char *balanceClause;
329 const char *lastSeenClause;
330 const char *orderClause;
334 if( Flags & BANK_ITFLAG_MINBALANCE )
335 balanceClause = "acct_balance>=";
336 else if( Flags & BANK_ITFLAG_MAXBALANCE )
337 balanceClause = "acct_balance<=";
339 balanceClause = "1!=";
343 if( Flags & BANK_ITFLAG_SEENAFTER )
344 lastSeenClause = "acct_last_seen>=";
345 else if( Flags & BANK_ITFLAG_SEENBEFORE )
346 lastSeenClause = "acct_last_seen<=";
348 lastSeenClause = "datetime(0,'unixepoch')!=";
351 switch( Flags & BANK_ITFLAG_SORTMASK )
353 case BANK_ITFLAG_SORT_NONE:
357 case BANK_ITFLAG_SORT_NAME:
358 orderClause = "ORDER BY acct_name";
361 case BANK_ITFLAG_SORT_BAL:
362 orderClause = "ORDER BY acct_balance";
365 case BANK_ITFLAG_SORT_LASTSEEN:
366 orderClause = "ORDER BY acct_balance";
370 fprintf(stderr, "BUG: Unknown sort (%x) in SQLite CokeBank\n", Flags & BANK_ITFLAG_SORTMASK);
373 if( !(Flags & BANK_ITFLAG_REVSORT) )
376 #define MAP_FLAG(name, flag) (FlagMask&(flag)?(FlagValues&(flag)?" AND "name"=1":" AND "name"=0"):"")
377 query = mkstr("SELECT acct_id FROM accounts WHERE 1=1"
378 "%s%s%s%s%s" // Flags
380 "%sdatetime(%lli,'unixepoch')" // Last seen
381 "%s%s" // Sort and direction
383 MAP_FLAG("acct_is_coke", USER_FLAG_COKE),
384 MAP_FLAG("acct_is_wheel", USER_FLAG_WHEEL),
385 MAP_FLAG("acct_is_door", USER_FLAG_DOORGROUP),
386 MAP_FLAG("acct_is_internal", USER_FLAG_INTERNAL),
387 MAP_FLAG("acct_is_disabled", USER_FLAG_DISABLED),
388 balanceClause, MinMaxBalance,
389 lastSeenClause, LastSeen,
394 ret = Bank_int_MakeStatemnt(gBank_Database, query);
395 if( !ret ) return NULL;
403 * Get the next account in an iterator
405 int Bank_IteratorNext(tAcctIterator *It)
408 rv = sqlite3_step( (sqlite3_stmt*)It );
410 if( rv == SQLITE_DONE ) return -1;
411 if( rv != SQLITE_ROW ) {
412 fprintf(stderr, "SQLite Error: %s\n", sqlite3_errmsg(gBank_Database));
416 return sqlite3_column_int( (sqlite3_stmt*)It, 0 );
422 void Bank_DelIterator(tAcctIterator *It)
424 sqlite3_finalize( (sqlite3_stmt*)It );
428 * Check user authentication token
430 int Bank_GetUserAuth(const char *Salt, const char *Username, const char *Password)
432 Salt = Password = Username; // Shut up GCC
435 return Bank_GetAcctByName(Username);
442 * Get an account number given a card ID
443 * NOTE: Actually ends up just being an alternate authentication token,
444 * as no checking is done on the ID's validity, save for SQL sanity.
446 int Bank_GetAcctByCard(const char *CardID)
449 sqlite3_stmt *statement;
452 if( !Bank_int_IsValidName(CardID) )
455 query = mkstr("SELECT acct_id FROM cards WHERE card_name='%s' LIMIT 1", CardID);
456 statement = Bank_int_QuerySingle(gBank_Database, query);
458 if( !statement ) return -1;
460 ret = sqlite3_column_int(statement, 0);
462 sqlite3_finalize(statement);
468 * Add a card to an account
470 int Bank_AddAcctCard(int AcctID, const char *CardID)
476 if( !Bank_int_IsValidName(CardID) )
479 // TODO: Check the AcctID too
482 query = mkstr("INSERT INTO cards (acct_id,card_name) VALUES (%i,'%s')",
484 rv = sqlite3_exec(gBank_Database, query, NULL, NULL, &errmsg);
485 if( rv == SQLITE_CONSTRAINT )
487 sqlite3_free(errmsg);
489 return 2; // Card in use
491 if( rv != SQLITE_OK )
493 fprintf(stderr, "SQLite Error: '%s'\n", errmsg);
494 fprintf(stderr, "Query = '%s'\n", query);
495 sqlite3_free(errmsg);
505 * Create a SQLite Statement
507 sqlite3_stmt *Bank_int_MakeStatemnt(sqlite3 *Database, const char *Query)
511 rv = sqlite3_prepare_v2(Database, Query, strlen(Query)+1, &ret, NULL);
512 if( rv != SQLITE_OK ) {
513 fprintf(stderr, "SQLite Error: %s\n", sqlite3_errmsg(Database));
514 fprintf(stderr, "query = \"%s\"\n", Query);
522 * Create a SQLite statement and query it for the first row
523 * Returns NULL if the the set is empty
525 sqlite3_stmt *Bank_int_QuerySingle(sqlite3 *Database, const char *Query)
531 ret = Bank_int_MakeStatemnt(Database, Query);
532 if( !ret ) return NULL;
535 rv = sqlite3_step(ret);
536 // - Empty result set
537 if( rv == SQLITE_DONE ) return NULL;
539 if( rv != SQLITE_ROW ) {
540 fprintf(stderr, "SQLite Error: %s\n", sqlite3_errmsg(gBank_Database));
541 fprintf(stderr, "query = \"%s\"\n", Query);
549 * \brief Checks if the passed account name is valid
551 int Bank_int_IsValidName(const char *Name)
555 if( *Name == '\'' ) return 0;