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_DatabaseSetup =
18 "CREATE TABLE IF NOT EXISTS accounts ("
19 " acct_id INTEGER PRIMARY KEY NOT NULL,"
20 " acct_balance INTEGER NOT NULL DEFAULT 0,"
21 " acct_last_seen DATETIME NOT NULL DEFAULT (datetime('now')),"
22 " acct_name STRING UNIQUE,"
23 " acct_uid INTEGER UNIQUE DEFAULT NULL,"
24 " acct_pin INTEGER DEFAULT NULL," //" acct_pin INTEGER CHECK (acct_pin > 0 AND acct_pin < 10000) DEFAULT NULL,"
25 " acct_is_disabled BOOLEAN NOT NULL DEFAULT false,"
26 " acct_is_coke BOOLEAN NOT NULL DEFAULT false,"
27 " acct_is_admin BOOLEAN NOT NULL DEFAULT false,"
28 " acct_is_door BOOLEAN NOT NULL DEFAULT false,"
29 " acct_is_internal BOOLEAN NOT NULL DEFAULT false"
31 "CREATE TABLE IF NOT EXISTS cards ("
32 " acct_id INTEGER NOT NULL,"
33 " card_name STRING NOT NULL UNIQUE,"
34 " FOREIGN KEY (acct_id) REFERENCES accounts (acct_id) ON DELETE CASCADE"
35 // Deletion of the account frees the card ^ ^ ^
37 "INSERT INTO accounts (acct_name,acct_is_admin,acct_uid) VALUES ('root',1,0);"
38 "INSERT INTO accounts (acct_name,acct_is_internal,acct_uid) VALUES ('"COKEBANK_SALES_ACCT"',1,-1);"
39 "INSERT INTO accounts (acct_name,acct_is_internal,acct_uid) VALUES ('"COKEBANK_DEBT_ACCT"',1,-2);"
40 "INSERT INTO accounts (acct_name,acct_is_internal,acct_uid) VALUES ('"COKEBANK_FREE_ACCT"',1,-3);"
44 struct sAcctIterator // Unused really, just used as a void type
49 int Bank_Initialise(const char *Argument);
50 int Bank_Transfer(int SourceAcct, int DestAcct, int Ammount, const char *Reason);
51 int Bank_GetFlags(int AcctID);
52 int Bank_SetFlags(int AcctID, int Mask, int Value);
53 int Bank_GetBalance(int AcctID);
54 char *Bank_GetAcctName(int AcctID);
55 sqlite3_stmt *Bank_int_MakeStatemnt(sqlite3 *Database, const char *Query);
56 sqlite3_stmt *Bank_int_QuerySingle(sqlite3 *Database, const char *Query);
57 int Bank_int_IsValidName(const char *Name);
60 sqlite3 *gBank_Database;
63 int Bank_Initialise(const char *Argument)
68 rv = sqlite3_open(Argument, &gBank_Database);
71 fprintf(stderr, "CokeBank: Unable to open database '%s'\n", Argument);
72 fprintf(stderr, "Reason: %s\n", sqlite3_errmsg(gBank_Database));
73 sqlite3_close(gBank_Database);
78 rv = sqlite3_exec(gBank_Database, "SELECT acct_id FROM accounts LIMIT 1", NULL, NULL, &errmsg);
83 else if( rv == SQLITE_NOTFOUND || rv == SQLITE_ERROR )
87 rv = sqlite3_exec(gBank_Database, csBank_DatabaseSetup, NULL, NULL, &errmsg);
88 if( rv != SQLITE_OK ) {
89 fprintf(stderr, "Bank_Initialise - SQLite Error: %s\n", errmsg);
94 Log_Info("SQLite database rebuilt");
99 fprintf(stderr, "Bank_Initialise - SQLite Error: %s (rv = %i)\n", errmsg, rv);
100 sqlite3_free(errmsg);
110 int Bank_Transfer(int SourceUser, int DestUser, int Ammount, const char *Reason)
116 Reason = ""; // Shut GCC up
118 // Begin SQL Transaction
119 sqlite3_exec(gBank_Database, "BEGIN TRANSACTION", NULL, NULL, NULL);
121 // Take from the source
122 query = mkstr("UPDATE accounts SET acct_balance=acct_balance-%i,acct_last_seen=datetime('now') WHERE acct_id=%i", Ammount, SourceUser);
123 printf("query = \"%s\"\n", query);
124 rv = sqlite3_exec(gBank_Database, query, NULL, NULL, &errmsg);
126 if( rv != SQLITE_OK )
128 fprintf(stderr, "Bank_Transfer - SQLite Error: %s\n", errmsg);
129 sqlite3_free(errmsg);
130 sqlite3_exec(gBank_Database, "ROLLBACK", NULL, NULL, NULL);
134 // Give to the destination
135 query = mkstr("UPDATE accounts SET acct_balance=acct_balance+%i,acct_last_seen=datetime('now') WHERE acct_id=%i", Ammount, DestUser);
136 printf("query = \"%s\"\n", query);
137 rv = sqlite3_exec(gBank_Database, query, NULL, NULL, &errmsg);
139 if( rv != SQLITE_OK )
141 fprintf(stderr, "Bank_Transfer - SQLite Error: %s\n", errmsg);
142 sqlite3_free(errmsg);
143 sqlite3_exec(gBank_Database, "ROLLBACK", NULL, NULL, NULL);
147 // Commit transaction
148 sqlite3_exec(gBank_Database, "COMMIT", NULL, NULL, NULL);
156 int Bank_GetFlags(int UserID)
158 sqlite3_stmt *statement;
164 "SELECT acct_is_disabled,acct_is_coke,acct_is_admin,acct_is_door,acct_is_internal"
165 " FROM accounts WHERE acct_id=%i LIMIT 1",
168 statement = Bank_int_QuerySingle(gBank_Database, query);
170 if( !statement ) return -1;
175 if( sqlite3_column_int(statement, 0) ) ret |= USER_FLAG_DISABLED;
177 if( sqlite3_column_int(statement, 1) ) ret |= USER_FLAG_COKE;
179 if( sqlite3_column_int(statement, 2) ) ret |= USER_FLAG_ADMIN;
181 if( sqlite3_column_int(statement, 3) ) ret |= USER_FLAG_DOORGROUP;
183 if( sqlite3_column_int(statement, 4) ) ret |= USER_FLAG_INTERNAL;
185 // Destroy and return
186 sqlite3_finalize(statement);
194 int Bank_SetFlags(int UserID, int Mask, int Value)
200 #define MAP_FLAG(name, flag) (Mask&(flag)?(Value&(flag)?","name"=1":","name"=0"):"")
202 "UPDATE accounts SET acct_id=acct_id%s%s%s%s%s WHERE acct_id=%i",// LIMIT 1",
203 MAP_FLAG("acct_is_coke", USER_FLAG_COKE),
204 MAP_FLAG("acct_is_admin", USER_FLAG_ADMIN),
205 MAP_FLAG("acct_is_door", USER_FLAG_DOORGROUP),
206 MAP_FLAG("acct_is_internal", USER_FLAG_INTERNAL),
207 MAP_FLAG("acct_is_disabled", USER_FLAG_DISABLED),
213 rv = sqlite3_exec(gBank_Database, query, NULL, NULL, &errmsg);
214 if( rv != SQLITE_OK )
216 fprintf(stderr, "Bank_SetUserFlags - SQLite Error: %s\n", errmsg);
217 fprintf(stderr, "query = '%s'\n", query);
219 sqlite3_free(errmsg);
230 int Bank_GetBalance(int AcctID)
232 sqlite3_stmt *statement;
236 query = mkstr("SELECT acct_balance FROM accounts WHERE acct_id=%i LIMIT 1", AcctID);
237 statement = Bank_int_QuerySingle(gBank_Database, query);
239 if( !statement ) return INT_MIN;
242 ret = sqlite3_column_int(statement, 0);
244 // Clean up and return
245 sqlite3_finalize(statement);
250 * Get the name of an account
252 char *Bank_GetAcctName(int AcctID)
254 sqlite3_stmt *statement;
258 query = mkstr("SELECT acct_name FROM accounts WHERE acct_id=%i LIMIT 1", AcctID);
259 statement = Bank_int_QuerySingle(gBank_Database, query);
261 if( !statement ) return NULL;
264 ret = strdup( (const char*)sqlite3_column_text(statement, 0) );
266 // Clean up and return
267 sqlite3_finalize(statement);
272 * Get an account ID from a name
274 int Bank_GetAcctByName(const char *Name)
277 sqlite3_stmt *statement;
280 if( !Bank_int_IsValidName(Name) ) return -1;
282 query = mkstr("SELECT acct_id FROM accounts WHERE acct_name='%s' LIMIT 1", Name);
283 statement = Bank_int_QuerySingle(gBank_Database, query);
285 if( !statement ) return -1;
287 ret = sqlite3_column_int(statement, 0);
288 if( ret == 0 ) return -1;
290 sqlite3_finalize(statement);
295 * Create a new named account
297 int Bank_CreateAcct(const char *Name)
305 if( !Bank_int_IsValidName(Name) ) return -1;
306 query = mkstr("INSERT INTO accounts (acct_name) VALUES ('%s')", Name);
310 query = strdup("INSERT INTO accounts (acct_name) VALUES (NULL)");
313 rv = sqlite3_exec(gBank_Database, query, NULL, NULL, &errmsg);
314 if( rv != SQLITE_OK )
316 fprintf(stderr, "Bank_CreateAcct - SQLite Error: '%s'\n", errmsg);
317 fprintf(stderr, "Query = '%s'\n", query);
318 sqlite3_free(errmsg);
325 return sqlite3_last_insert_rowid(gBank_Database);
329 * Create an iterator for user accounts
331 tAcctIterator *Bank_Iterator(int FlagMask, int FlagValues, int Flags, int MinMaxBalance, time_t LastSeen)
334 const char *balanceClause;
335 const char *lastSeenClause;
336 const char *orderClause;
341 if( Flags & BANK_ITFLAG_MINBALANCE )
342 balanceClause = " AND acct_balance>=";
343 else if( Flags & BANK_ITFLAG_MAXBALANCE )
344 balanceClause = " AND acct_balance<=";
346 balanceClause = " AND 1!=";
350 // Last seen condition
351 if( Flags & BANK_ITFLAG_SEENAFTER )
352 lastSeenClause = " AND acct_last_seen>=";
353 else if( Flags & BANK_ITFLAG_SEENBEFORE )
354 lastSeenClause = " AND acct_last_seen<=";
356 lastSeenClause = " AND datetime(-1,'unixepoch')!=";
360 switch( Flags & BANK_ITFLAG_SORTMASK )
362 case BANK_ITFLAG_SORT_NONE:
366 case BANK_ITFLAG_SORT_NAME:
367 orderClause = "ORDER BY acct_name";
370 case BANK_ITFLAG_SORT_BAL:
371 orderClause = "ORDER BY acct_balance";
374 case BANK_ITFLAG_SORT_LASTSEEN:
375 orderClause = "ORDER BY acct_balance";
379 fprintf(stderr, "BUG: Unknown sort (%x) in SQLite CokeBank\n", Flags & BANK_ITFLAG_SORTMASK);
382 if( !(Flags & BANK_ITFLAG_REVSORT) )
385 #define MAP_FLAG(name, flag) (FlagMask&(flag)?(FlagValues&(flag)?" AND "name"=1":" AND "name"=0"):"")
386 query = mkstr("SELECT acct_id FROM accounts WHERE 1=1"
387 "%s%s%s%s%s" // Flags
389 "%sdatetime(%lli,'unixepoch')" // Last seen
390 "%s%s" // Sort and direction
392 MAP_FLAG("acct_is_coke", USER_FLAG_COKE),
393 MAP_FLAG("acct_is_admin", USER_FLAG_ADMIN),
394 MAP_FLAG("acct_is_door", USER_FLAG_DOORGROUP),
395 MAP_FLAG("acct_is_internal", USER_FLAG_INTERNAL),
396 MAP_FLAG("acct_is_disabled", USER_FLAG_DISABLED),
397 balanceClause, MinMaxBalance,
398 lastSeenClause, LastSeen,
401 //printf("query = \"%s\"\n", query);
404 ret = Bank_int_MakeStatemnt(gBank_Database, query);
405 if( !ret ) return NULL;
413 * Get the next account in an iterator
415 int Bank_IteratorNext(tAcctIterator *It)
418 rv = sqlite3_step( (sqlite3_stmt*)It );
420 if( rv == SQLITE_DONE ) return -1;
421 if( rv != SQLITE_ROW ) {
422 fprintf(stderr, "Bank_IteratorNext - SQLite Error: %s\n", sqlite3_errmsg(gBank_Database));
426 return sqlite3_column_int( (sqlite3_stmt*)It, 0 );
432 void Bank_DelIterator(tAcctIterator *It)
434 sqlite3_finalize( (sqlite3_stmt*)It );
438 * Check user authentication token
440 int Bank_GetUserAuth(const char *Salt, const char *Username, const char *Password)
442 Salt = Password = Username; // Shut up GCC
445 return Bank_GetAcctByName(Username);
452 * Get an account number given a card ID
453 * NOTE: Actually ends up just being an alternate authentication token,
454 * as no checking is done on the ID's validity, save for SQL sanity.
456 int Bank_GetAcctByCard(const char *CardID)
459 sqlite3_stmt *statement;
462 if( !Bank_int_IsValidName(CardID) )
465 query = mkstr("SELECT acct_id FROM cards WHERE card_name='%s' LIMIT 1", CardID);
466 statement = Bank_int_QuerySingle(gBank_Database, query);
468 if( !statement ) return -1;
470 ret = sqlite3_column_int(statement, 0);
472 sqlite3_finalize(statement);
478 * Add a card to an account
480 int Bank_AddAcctCard(int AcctID, const char *CardID)
486 if( !Bank_int_IsValidName(CardID) )
489 // TODO: Check the AcctID too
492 query = mkstr("INSERT INTO cards (acct_id,card_name) VALUES (%i,'%s')",
494 rv = sqlite3_exec(gBank_Database, query, NULL, NULL, &errmsg);
495 if( rv == SQLITE_CONSTRAINT )
497 sqlite3_free(errmsg);
499 return 2; // Card in use
501 if( rv != SQLITE_OK )
503 fprintf(stderr, "Bank_AddAcctCard - SQLite Error: '%s'\n", errmsg);
504 fprintf(stderr, "Query = '%s'\n", query);
505 sqlite3_free(errmsg);
515 * Create a SQLite Statement
517 sqlite3_stmt *Bank_int_MakeStatemnt(sqlite3 *Database, const char *Query)
521 rv = sqlite3_prepare_v2(Database, Query, strlen(Query)+1, &ret, NULL);
522 if( rv != SQLITE_OK ) {
523 fprintf(stderr, "SQLite Error: %s\n", sqlite3_errmsg(Database));
524 fprintf(stderr, "query = \"%s\"\n", Query);
532 * Create a SQLite statement and query it for the first row
533 * Returns NULL if the the set is empty
535 sqlite3_stmt *Bank_int_QuerySingle(sqlite3 *Database, const char *Query)
541 ret = Bank_int_MakeStatemnt(Database, Query);
542 if( !ret ) return NULL;
545 rv = sqlite3_step(ret);
546 // - Empty result set
547 if( rv == SQLITE_DONE ) return NULL;
549 if( rv != SQLITE_ROW ) {
550 fprintf(stderr, "SQLite Error: %s\n", sqlite3_errmsg(gBank_Database));
551 fprintf(stderr, "query = \"%s\"\n", Query);
559 * \brief Checks if the passed account name is valid
561 int Bank_int_IsValidName(const char *Name)
565 if( *Name == '\'' ) return 0;