X-Git-Url: https://git.ucc.asn.au/?a=blobdiff_plain;f=src%2Fcokebank_sqlite%2Fmain.c;h=34761b2ebf9f0ab36637718d609c7681873159a8;hb=8a516dd60ab15cf514481c74dd087a294915565d;hp=e962b9d94a9652c06c9ca00f89a4031d2336915d;hpb=88e80c5ac36f6d2e76caf1a99efbf987b042c0fb;p=tpg%2Fopendispense2.git diff --git a/src/cokebank_sqlite/main.c b/src/cokebank_sqlite/main.c index e962b9d..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,24 +15,41 @@ #include "../cokebank.h" #include -const char * const csBank_CreateAccountQry = "CREATE TABLE IF NOT EXISTS accounts (" +#define DEBUG 0 + +const char * const csBank_DatabaseSetup = +"CREATE TABLE IF NOT EXISTS accounts (" " acct_id INTEGER PRIMARY KEY NOT NULL," " acct_balance INTEGER NOT NULL DEFAULT 0," +" acct_last_seen DATETIME NOT NULL DEFAULT (datetime('now'))," " acct_name STRING UNIQUE," " acct_uid INTEGER UNIQUE DEFAULT NULL," -" acct_pin INTEGER CHECK (acct_pin > 0 AND acct_pin < 10000) DEFAULT NULL," +" acct_pin INTEGER DEFAULT NULL," //" acct_pin INTEGER CHECK (acct_pin > 0 AND acct_pin < 10000) DEFAULT NULL," " acct_is_disabled BOOLEAN NOT NULL DEFAULT false," " acct_is_coke BOOLEAN NOT NULL DEFAULT false," -" acct_is_wheel BOOLEAN NOT NULL DEFAULT false," +" acct_is_admin BOOLEAN NOT NULL DEFAULT false," " acct_is_door BOOLEAN NOT NULL DEFAULT false," " acct_is_internal BOOLEAN NOT NULL DEFAULT false" -")"; -const char * const csBank_CreateCardsQry = "CREATE TABLE IF NOT EXISTS cards (" +");" +"CREATE TABLE IF NOT EXISTS cards (" " acct_id INTEGER NOT NULL," " card_name STRING NOT NULL UNIQUE," " 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);" +"INSERT INTO accounts (acct_name,acct_is_internal,acct_uid) VALUES ('"COKEBANK_FREE_ACCT"',1,-3);" +; // === TYPES === struct sAcctIterator // Unused really, just used as a void type @@ -41,11 +59,12 @@ struct sAcctIterator // Unused really, just used as a void type // === PROTOYPES === int Bank_Initialise(const char *Argument); int Bank_Transfer(int SourceAcct, int DestAcct, int Ammount, const char *Reason); - int Bank_GetUserFlags(int AcctID); - int Bank_SetUserFlags(int AcctID, int Mask, int Value); + int Bank_GetFlags(int AcctID); + int Bank_SetFlags(int AcctID, int Mask, int Value); 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); @@ -68,34 +87,28 @@ 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 } - else if( rv == SQLITE_NOTFOUND ) + else if( rv == SQLITE_NOTFOUND || rv == SQLITE_ERROR ) { sqlite3_free(errmsg); // Create tables - // - Accounts - rv = sqlite3_exec(gBank_Database, csBank_CreateAccountQry, NULL, NULL, &errmsg); - if( rv != SQLITE_OK ) { - fprintf(stderr, "SQLite Error: %s\n", errmsg); - sqlite3_free(errmsg); - return 1; - } - // - Mifare relation - rv = sqlite3_exec(gBank_Database, csBank_CreateCardsQry, NULL, NULL, &errmsg); + rv = Bank_int_QueryNone(gBank_Database, csBank_DatabaseSetup, &errmsg); if( rv != SQLITE_OK ) { - fprintf(stderr, "SQLite Error: %s\n", errmsg); + fprintf(stderr, "Bank_Initialise - SQLite Error: %s\n", errmsg); sqlite3_free(errmsg); return 1; } + + Log_Info("SQLite database rebuilt (%s)", Argument); } else { // Unknown error - fprintf(stderr, "SQLite Error: %s\n", errmsg); + fprintf(stderr, "Bank_Initialise - SQLite Error: %s (rv = %i)\n", errmsg, rv); sqlite3_free(errmsg); return 1; } @@ -115,34 +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 WHERE acct_id=%i", Ammount, SourceUser); - 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, "SQLite Error: %s\n", errmsg); + 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 WHERE acct_id=%i", Ammount, DestUser); - 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, "SQLite Error: %s\n", errmsg); + 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; } @@ -150,7 +165,7 @@ int Bank_Transfer(int SourceUser, int DestUser, int Ammount, const char *Reason) /* * Get user flags */ -int Bank_GetUserFlags(int UserID) +int Bank_GetFlags(int UserID) { sqlite3_stmt *statement; char *query; @@ -158,7 +173,7 @@ int Bank_GetUserFlags(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 ); @@ -173,11 +188,11 @@ int Bank_GetUserFlags(int UserID) // - Coke if( sqlite3_column_int(statement, 1) ) ret |= USER_FLAG_COKE; // - Wheel - if( sqlite3_column_int(statement, 2) ) ret |= USER_FLAG_WHEEL; + if( sqlite3_column_int(statement, 2) ) ret |= USER_FLAG_ADMIN; // - Door if( sqlite3_column_int(statement, 3) ) ret |= USER_FLAG_DOORGROUP; // - Internal - if( sqlite3_column_int(statement, 3) ) ret |= USER_FLAG_INTERNAL; + if( sqlite3_column_int(statement, 4) ) ret |= USER_FLAG_INTERNAL; // Destroy and return sqlite3_finalize(statement); @@ -188,7 +203,7 @@ int Bank_GetUserFlags(int UserID) /* * Set user flags */ -int Bank_SetUserFlags(int UserID, int Mask, int Value) +int Bank_SetFlags(int UserID, int Mask, int Value) { char *query; int rv; @@ -196,25 +211,31 @@ int Bank_SetUserFlags(int UserID, int Mask, int Value) #define MAP_FLAG(name, flag) (Mask&(flag)?(Value&(flag)?","name"=1":","name"=0"):"") query = mkstr( - "UDPATE accounts WHERE acct_id=%i SET acct_id=acct_id%s%s%s%s%s", - UserID, + "UPDATE accounts SET acct_id=acct_id%s%s%s%s%s WHERE acct_id=%i",// LIMIT 1", MAP_FLAG("acct_is_coke", USER_FLAG_COKE), - MAP_FLAG("acct_is_wheel", USER_FLAG_WHEEL), + MAP_FLAG("acct_is_admin", USER_FLAG_ADMIN), MAP_FLAG("acct_is_door", USER_FLAG_DOORGROUP), MAP_FLAG("acct_is_internal", USER_FLAG_INTERNAL), - MAP_FLAG("acct_is_disabled", USER_FLAG_DISABLED) + MAP_FLAG("acct_is_disabled", USER_FLAG_DISABLED), + UserID ); #undef MAP_FLAG + #if DEBUG + printf("Bank_SetFlags: query=\"%s\"\n", query); + #endif + // Execute Query - rv = sqlite3_exec(gBank_Database, query, NULL, NULL, &errmsg); - free(query); + rv = Bank_int_QueryNone(gBank_Database, query, &errmsg); if( rv != SQLITE_OK ) { - fprintf(stderr, "SQLite Error: %s\n", errmsg); + fprintf(stderr, "Bank_SetUserFlags - SQLite Error: %s\n", errmsg); + fprintf(stderr, "query = '%s'\n", query); + free(query); sqlite3_free(errmsg); return -1; } + free(query); return 0; } @@ -266,22 +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); - sqlite3_finalize(statement); + +// printf("ret = %i\n", ret); + + if( ret == 0 ) { + return -1; + } return ret; } @@ -304,10 +339,10 @@ 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, "SQLite Error: '%s'\n", errmsg); + fprintf(stderr, "Bank_CreateAcct - SQLite Error: '%s'\n", errmsg); fprintf(stderr, "Query = '%s'\n", query); sqlite3_free(errmsg); free(query); @@ -331,23 +366,26 @@ 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 = "acct_balance>="; + balanceClause = " AND acct_balance>="; else if( Flags & BANK_ITFLAG_MAXBALANCE ) - balanceClause = "acct_balance<="; + balanceClause = " AND acct_balance<="; else { - balanceClause = "1!="; + balanceClause = " AND 1!="; MinMaxBalance = 0; } + // Last seen condition if( Flags & BANK_ITFLAG_SEENAFTER ) - lastSeenClause = "acct_last_seen>="; + lastSeenClause = " AND acct_last_seen>="; else if( Flags & BANK_ITFLAG_SEENBEFORE ) - lastSeenClause = "acct_last_seen<="; + lastSeenClause = " AND acct_last_seen<="; else { - lastSeenClause = "datetime(0,'unixepoch')!="; + lastSeenClause = " AND datetime(-1,'unixepoch')!="; } + // Sorting clause switch( Flags & BANK_ITFLAG_SORTMASK ) { case BANK_ITFLAG_SORT_NONE: @@ -377,18 +415,19 @@ 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), - MAP_FLAG("acct_is_wheel", USER_FLAG_WHEEL), + MAP_FLAG("acct_is_admin", USER_FLAG_ADMIN), MAP_FLAG("acct_is_door", USER_FLAG_DOORGROUP), 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); #undef MAP_FLAG ret = Bank_int_MakeStatemnt(gBank_Database, query); @@ -409,7 +448,7 @@ int Bank_IteratorNext(tAcctIterator *It) if( rv == SQLITE_DONE ) return -1; if( rv != SQLITE_ROW ) { - fprintf(stderr, "SQLite Error: %s\n", sqlite3_errmsg(gBank_Database)); + fprintf(stderr, "Bank_IteratorNext - SQLite Error: %s\n", sqlite3_errmsg(gBank_Database)); return -1; } @@ -431,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; @@ -481,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); @@ -490,7 +529,7 @@ int Bank_AddAcctCard(int AcctID, const char *CardID) } if( rv != SQLITE_OK ) { - fprintf(stderr, "SQLite Error: '%s'\n", errmsg); + fprintf(stderr, "Bank_AddAcctCard - SQLite Error: '%s'\n", errmsg); fprintf(stderr, "Query = '%s'\n", query); sqlite3_free(errmsg); free(query); @@ -518,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 @@ -527,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)); @@ -542,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; } @@ -550,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;