X-Git-Url: https://git.ucc.asn.au/?a=blobdiff_plain;f=src%2Fcokebank_sqlite%2Fmain.c;h=34761b2ebf9f0ab36637718d609c7681873159a8;hb=8a516dd60ab15cf514481c74dd087a294915565d;hp=26282dc890088e773510fb988c3a3eabe08c98f2;hpb=f8ff5876b5787375fe629450d0c5d088965720d0;p=tpg%2Fopendispense2.git diff --git a/src/cokebank_sqlite/main.c b/src/cokebank_sqlite/main.c index 26282dc..34761b2 100644 --- a/src/cokebank_sqlite/main.c +++ b/src/cokebank_sqlite/main.c @@ -7,6 +7,7 @@ * This file is licenced under the 3-clause BSD Licence. See the file * COPYING for full details. */ +#include #include #include #include @@ -14,6 +15,8 @@ #include "../cokebank.h" #include +#define DEBUG 0 + const char * const csBank_DatabaseSetup = "CREATE TABLE IF NOT EXISTS accounts (" " acct_id INTEGER PRIMARY KEY NOT NULL," @@ -34,6 +37,14 @@ const char * const csBank_DatabaseSetup = " FOREIGN KEY (acct_id) REFERENCES accounts (acct_id) ON DELETE CASCADE" // Deletion of the account frees the card ^ ^ ^ ");" +"CREATE TABLE IF NOT EXISTS items (" +" item_id INTEGER PRIMARY KEY NOT NULL," +" item_handler STRING NOT NULL," +" item_index INTEGER NOT NULL," +" item_name STRING NOT NULL," +" item_price INTEGER NOT NULL," +" item_is_enabled BOOLEAN NOT NULL DEFAULT true" +");" "INSERT INTO accounts (acct_name,acct_is_admin,acct_uid) VALUES ('root',1,0);" "INSERT INTO accounts (acct_name,acct_is_internal,acct_uid) VALUES ('"COKEBANK_SALES_ACCT"',1,-1);" "INSERT INTO accounts (acct_name,acct_is_internal,acct_uid) VALUES ('"COKEBANK_DEBT_ACCT"',1,-2);" @@ -53,6 +64,7 @@ struct sAcctIterator // Unused really, just used as a void type int Bank_GetBalance(int AcctID); char *Bank_GetAcctName(int AcctID); sqlite3_stmt *Bank_int_MakeStatemnt(sqlite3 *Database, const char *Query); + int Bank_int_QueryNone(sqlite3 *Database, const char *Query, char **ErrorMessage); sqlite3_stmt *Bank_int_QuerySingle(sqlite3 *Database, const char *Query); int Bank_int_IsValidName(const char *Name); @@ -75,7 +87,7 @@ int Bank_Initialise(const char *Argument) } // Check structure - rv = sqlite3_exec(gBank_Database, "SELECT acct_id FROM accounts LIMIT 1", NULL, NULL, &errmsg); + rv = Bank_int_QueryNone(gBank_Database, "SELECT acct_id FROM accounts LIMIT 1", &errmsg); if( rv == SQLITE_OK ) { // NOP @@ -84,12 +96,14 @@ int Bank_Initialise(const char *Argument) { sqlite3_free(errmsg); // Create tables - rv = sqlite3_exec(gBank_Database, csBank_DatabaseSetup, NULL, NULL, &errmsg); + rv = Bank_int_QueryNone(gBank_Database, csBank_DatabaseSetup, &errmsg); if( rv != SQLITE_OK ) { fprintf(stderr, "Bank_Initialise - SQLite Error: %s\n", errmsg); sqlite3_free(errmsg); return 1; } + + Log_Info("SQLite database rebuilt (%s)", Argument); } else { @@ -114,36 +128,36 @@ int Bank_Transfer(int SourceUser, int DestUser, int Ammount, const char *Reason) Reason = ""; // Shut GCC up // Begin SQL Transaction - sqlite3_exec(gBank_Database, "BEGIN TRANSACTION", NULL, NULL, NULL); + Bank_int_QueryNone(gBank_Database, "BEGIN TRANSACTION", NULL); // Take from the source - query = mkstr("UPDATE accounts SET acct_balance=acct_balance-%i,acct_last_seen=datetime('now') WHERE acct_id=%i", Ammount, SourceUser); - printf("query = \"%s\"\n", query); - rv = sqlite3_exec(gBank_Database, query, NULL, NULL, &errmsg); + query = mkstr("UPDATE accounts SET acct_balance=acct_balance%+i,acct_last_seen=datetime('now') WHERE acct_id=%i", -Ammount, SourceUser); +// printf("query = \"%s\"\n", query); + rv = Bank_int_QueryNone(gBank_Database, query, &errmsg); free(query); if( rv != SQLITE_OK ) { fprintf(stderr, "Bank_Transfer - SQLite Error: %s\n", errmsg); sqlite3_free(errmsg); - sqlite3_exec(gBank_Database, "ROLLBACK", NULL, NULL, NULL); + Bank_int_QueryNone(gBank_Database, "ROLLBACK", NULL); return 1; } // Give to the destination - query = mkstr("UPDATE accounts SET acct_balance=acct_balance+%i,acct_last_seen=datetime('now') WHERE acct_id=%i", Ammount, DestUser); - printf("query = \"%s\"\n", query); - rv = sqlite3_exec(gBank_Database, query, NULL, NULL, &errmsg); + query = mkstr("UPDATE accounts SET acct_balance=acct_balance%+i,acct_last_seen=datetime('now') WHERE acct_id=%i", Ammount, DestUser); +// printf("query = \"%s\"\n", query); + rv = Bank_int_QueryNone(gBank_Database, query, &errmsg); free(query); if( rv != SQLITE_OK ) { fprintf(stderr, "Bank_Transfer - SQLite Error: %s\n", errmsg); sqlite3_free(errmsg); - sqlite3_exec(gBank_Database, "ROLLBACK", NULL, NULL, NULL); + Bank_int_QueryNone(gBank_Database, "ROLLBACK", NULL); return 1; } // Commit transaction - sqlite3_exec(gBank_Database, "COMMIT", NULL, NULL, NULL); + Bank_int_QueryNone(gBank_Database, "COMMIT", NULL); return 0; } @@ -159,7 +173,7 @@ int Bank_GetFlags(int UserID) // Build Query query = mkstr( - "SELECT acct_is_disabled,acct_is_coke,acct_is_wheel,acct_is_door,acct_is_internal" + "SELECT acct_is_disabled,acct_is_coke,acct_is_admin,acct_is_door,acct_is_internal" " FROM accounts WHERE acct_id=%i LIMIT 1", UserID ); @@ -207,8 +221,12 @@ int Bank_SetFlags(int UserID, int Mask, int Value) ); #undef MAP_FLAG + #if DEBUG + printf("Bank_SetFlags: query=\"%s\"\n", query); + #endif + // Execute Query - rv = sqlite3_exec(gBank_Database, query, NULL, NULL, &errmsg); + rv = Bank_int_QueryNone(gBank_Database, query, &errmsg); if( rv != SQLITE_OK ) { fprintf(stderr, "Bank_SetUserFlags - SQLite Error: %s\n", errmsg); @@ -269,23 +287,36 @@ char *Bank_GetAcctName(int AcctID) /* * Get an account ID from a name */ -int Bank_GetAcctByName(const char *Name) +int Bank_GetAcctByName(const char *Name, int bCreate) { char *query; sqlite3_stmt *statement; int ret; - if( !Bank_int_IsValidName(Name) ) return -1; +// printf("Bank_GetAcctByName: (Name='%s',bCreate=%i)\n", Name, bCreate); + + if( !Bank_int_IsValidName(Name) ) { +// printf("RETURN: -1 (Bad name)"); + return -1; + } query = mkstr("SELECT acct_id FROM accounts WHERE acct_name='%s' LIMIT 1", Name); statement = Bank_int_QuerySingle(gBank_Database, query); free(query); - if( !statement ) return -1; + if( !statement ) { +// printf("User not found\n"); + if( bCreate ) return Bank_CreateAcct(Name); + return -1; + } ret = sqlite3_column_int(statement, 0); - if( ret == 0 ) return -1; - sqlite3_finalize(statement); + +// printf("ret = %i\n", ret); + + if( ret == 0 ) { + return -1; + } return ret; } @@ -308,7 +339,7 @@ int Bank_CreateAcct(const char *Name) query = strdup("INSERT INTO accounts (acct_name) VALUES (NULL)"); } - rv = sqlite3_exec(gBank_Database, query, NULL, NULL, &errmsg); + rv = Bank_int_QueryNone(gBank_Database, query, &errmsg); if( rv != SQLITE_OK ) { fprintf(stderr, "Bank_CreateAcct - SQLite Error: '%s'\n", errmsg); @@ -335,6 +366,7 @@ tAcctIterator *Bank_Iterator(int FlagMask, int FlagValues, int Flags, int MinMax const char *revSort; sqlite3_stmt *ret; + // Balance condtion if( Flags & BANK_ITFLAG_MINBALANCE ) balanceClause = " AND acct_balance>="; else if( Flags & BANK_ITFLAG_MAXBALANCE ) @@ -344,6 +376,7 @@ tAcctIterator *Bank_Iterator(int FlagMask, int FlagValues, int Flags, int MinMax MinMaxBalance = 0; } + // Last seen condition if( Flags & BANK_ITFLAG_SEENAFTER ) lastSeenClause = " AND acct_last_seen>="; else if( Flags & BANK_ITFLAG_SEENBEFORE ) @@ -352,6 +385,7 @@ tAcctIterator *Bank_Iterator(int FlagMask, int FlagValues, int Flags, int MinMax lastSeenClause = " AND datetime(-1,'unixepoch')!="; } + // Sorting clause switch( Flags & BANK_ITFLAG_SORTMASK ) { case BANK_ITFLAG_SORT_NONE: @@ -381,7 +415,7 @@ tAcctIterator *Bank_Iterator(int FlagMask, int FlagValues, int Flags, int MinMax query = mkstr("SELECT acct_id FROM accounts WHERE 1=1" "%s%s%s%s%s" // Flags "%s%i" // Balance - "%sdatetime(%lli,'unixepoch')" // Last seen + "%sdatetime(%"PRIu64",'unixepoch')" // Last seen "%s%s" // Sort and direction , MAP_FLAG("acct_is_coke", USER_FLAG_COKE), @@ -390,7 +424,7 @@ tAcctIterator *Bank_Iterator(int FlagMask, int FlagValues, int Flags, int MinMax MAP_FLAG("acct_is_internal", USER_FLAG_INTERNAL), MAP_FLAG("acct_is_disabled", USER_FLAG_DISABLED), balanceClause, MinMaxBalance, - lastSeenClause, LastSeen, + lastSeenClause, (uint64_t)LastSeen, orderClause, revSort ); //printf("query = \"%s\"\n", query); @@ -436,7 +470,7 @@ int Bank_GetUserAuth(const char *Salt, const char *Username, const char *Passwor { Salt = Password = Username; // Shut up GCC // DEBUG HACKS! - #if 1 + #if 0 return Bank_GetAcctByName(Username); #else return -1; @@ -486,7 +520,7 @@ int Bank_AddAcctCard(int AcctID, const char *CardID) // Insert card query = mkstr("INSERT INTO cards (acct_id,card_name) VALUES (%i,'%s')", AcctID, CardID); - rv = sqlite3_exec(gBank_Database, query, NULL, NULL, &errmsg); + rv = Bank_int_QueryNone(gBank_Database, query, &errmsg); if( rv == SQLITE_CONSTRAINT ) { sqlite3_free(errmsg); @@ -523,6 +557,14 @@ sqlite3_stmt *Bank_int_MakeStatemnt(sqlite3 *Database, const char *Query) return ret; } +int Bank_int_QueryNone(sqlite3 *Database, const char *Query, char **ErrorMessage) +{ + #if DEBUG + printf("Bank_int_QueryNone: (Query='%s')\n", Query); + #endif + return sqlite3_exec(Database, Query, NULL, NULL, ErrorMessage); +} + /* * Create a SQLite statement and query it for the first row * Returns NULL if the the set is empty @@ -532,14 +574,28 @@ sqlite3_stmt *Bank_int_QuerySingle(sqlite3 *Database, const char *Query) sqlite3_stmt *ret; int rv; + #if DEBUG + printf("Bank_int_QuerySingle: (Query='%s')\n", Query); + #endif + // Prepare query ret = Bank_int_MakeStatemnt(Database, Query); - if( !ret ) return NULL; + if( !ret ) { + #if DEBUG >= 2 + printf("Bank_int_QuerySingle: RETURN NULL ret=NULL\n"); + #endif + return NULL; + } // Get row rv = sqlite3_step(ret); // - Empty result set - if( rv == SQLITE_DONE ) return NULL; + if( rv == SQLITE_DONE ) { + #if DEBUG >= 2 + printf("Bank_int_QuerySingle: RETURN NULL (rv == SQLITE_DONE)\n"); + #endif + return NULL; + } // - Other error if( rv != SQLITE_ROW ) { fprintf(stderr, "SQLite Error: %s\n", sqlite3_errmsg(gBank_Database)); @@ -547,6 +603,9 @@ sqlite3_stmt *Bank_int_QuerySingle(sqlite3 *Database, const char *Query) return NULL; } + #if DEBUG >= 2 + printf("Bank_int_QuerySingle: RETURN %p\n", ret); + #endif return ret; } @@ -555,6 +614,7 @@ sqlite3_stmt *Bank_int_QuerySingle(sqlite3 *Database, const char *Query) */ int Bank_int_IsValidName(const char *Name) { + if( !Name ) return 0; while(*Name) { if( *Name == '\'' ) return 0;