From 88e80c5ac36f6d2e76caf1a99efbf987b042c0fb Mon Sep 17 00:00:00 2001 From: John Hodge Date: Sun, 30 Jan 2011 16:49:14 +0800 Subject: [PATCH] SQLite CokeBank compiles --- src/cokebank.h | 20 ++- src/cokebank_sqlite/Makefile | 30 ++++ src/cokebank_sqlite/main.c | 302 ++++++++++++++++++++++++++++++++--- 3 files changed, 328 insertions(+), 24 deletions(-) create mode 100644 src/cokebank_sqlite/Makefile diff --git a/src/cokebank.h b/src/cokebank.h index 9450ecf..ae244a2 100644 --- a/src/cokebank.h +++ b/src/cokebank.h @@ -14,6 +14,8 @@ #ifndef _COKEBANK_H_ #define _COKEBANK_H_ +#include + #define COKEBANK_SALES_ACCT ">sales" //!< Sales made into #define COKEBANK_DEBT_ACCT ">liability" //!< Credit taken out of @@ -146,14 +148,24 @@ extern int Bank_GetUserAuth(const char *Salt, const char *Username, const char * extern int Bank_GetAcctByCard(const char *CardID); /** - * \brief Add a card to an git account - * \param User User ID + * \brief Add a card to an account + * \param AcctID Account ID * \param CardID MIFARE card ID * \return Boolean failure * \retval 0 Success - * \retval 1 Bad user ID + * \retval 1 Bad account ID * \retval 2 Card in use */ -extern int Bank_AddUserCard(int User, const char *CardID); +extern int Bank_AddAcctCard(int AcctID, const char *CardID); + +// --- +// Server provided helper functions +// --- +/** + * \brief Create a formatted string on the heap + * \param Format Format string + * \return Heap string + */ +extern char *mkstr(const char *Format, ...); #endif diff --git a/src/cokebank_sqlite/Makefile b/src/cokebank_sqlite/Makefile new file mode 100644 index 0000000..14ed6bd --- /dev/null +++ b/src/cokebank_sqlite/Makefile @@ -0,0 +1,30 @@ + +BIN := ../../cokebank_sqlite.so +OBJ := main.o + +CPPFLAGS := +CFLAGS := -Wall -Wextra -Werror -g -fPIC -Wmissing-prototypes -Wstrict-prototypes +LDFLAGS := -shared -Wl,-soname,cokebank.so -lsqlite3 + +ifneq ($(USE_LDAP),) + CFLAGS += -DUSE_LDAP + LDFLAGS += -lldap +endif + +DEPFILES := $(OBJ:%.o=%.d) + +.PHONY: all clean + +all: $(BIN) + +clean: + $(RM) $(BIN) $(OBJ) $(DEPFILES) + +$(BIN): $(OBJ) + $(CC) $(LDFLAGS) -o $(BIN) $(OBJ) + +%.o: %.c + $(CC) -c $< -o $@ $(CFLAGS) $(CPPFLAGS) + @cpp $< -MM -MF $*.d + +-include $(DEPFILES) diff --git a/src/cokebank_sqlite/main.c b/src/cokebank_sqlite/main.c index 858f594..e962b9d 100644 --- a/src/cokebank_sqlite/main.c +++ b/src/cokebank_sqlite/main.c @@ -7,15 +7,19 @@ * This file is licenced under the 3-clause BSD Licence. See the file * COPYING for full details. */ +#include +#include +#include +#include #include "../cokebank.h" #include const char * const csBank_CreateAccountQry = "CREATE TABLE IF NOT EXISTS accounts (" " acct_id INTEGER PRIMARY KEY NOT NULL," -" acct_balance INTEGER NOT NULL," +" acct_balance INTEGER NOT NULL DEFAULT 0," " acct_name STRING UNIQUE," -" acct_uid INTEGER UNIQUE," -" acct_pin INTEGER CHECK (acct_pin > 0 AND acct_pin < 10000)," +" acct_uid INTEGER UNIQUE 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," @@ -29,6 +33,11 @@ const char * const csBank_CreateCardsQry = "CREATE TABLE IF NOT EXISTS cards (" // Deletion of the account frees the card ^ ^ ^ ")"; +// === TYPES === +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); @@ -38,6 +47,7 @@ const char * const csBank_CreateCardsQry = "CREATE TABLE IF NOT EXISTS cards (" char *Bank_GetAcctName(int AcctID); sqlite3_stmt *Bank_int_MakeStatemnt(sqlite3 *Database, const char *Query); sqlite3_stmt *Bank_int_QuerySingle(sqlite3 *Database, const char *Query); + int Bank_int_IsValidName(const char *Name); // === GLOBALS === sqlite3 *gBank_Database; @@ -98,10 +108,12 @@ int Bank_Initialise(const char *Argument) */ int Bank_Transfer(int SourceUser, int DestUser, int Ammount, const char *Reason) { - char *query + char *query; int rv; char *errmsg; + Reason = ""; // Shut GCC up + // Begin SQL Transaction sqlite3_exec(gBank_Database, "BEGIN TRANSACTION", NULL, NULL, NULL); @@ -112,8 +124,8 @@ int Bank_Transfer(int SourceUser, int DestUser, int Ammount, const char *Reason) if( rv != SQLITE_OK ) { fprintf(stderr, "SQLite Error: %s\n", errmsg); - sqlite3_free(errMsg); - sqlite3_query(gBank_Database, "ROLLBACK", NULL, NULL, NULL); + sqlite3_free(errmsg); + sqlite3_exec(gBank_Database, "ROLLBACK", NULL, NULL, NULL); return 1; } @@ -125,12 +137,12 @@ int Bank_Transfer(int SourceUser, int DestUser, int Ammount, const char *Reason) { fprintf(stderr, "SQLite Error: %s\n", errmsg); sqlite3_free(errmsg); - sqlite3_query(gBank_Database, "ROLLBACK", NULL, NULL, NULL); + sqlite3_exec(gBank_Database, "ROLLBACK", NULL, NULL, NULL); return 1; } // Commit transaction - sqlite3_query(gBank_Database, "COMMIT", NULL, NULL, NULL); + sqlite3_exec(gBank_Database, "COMMIT", NULL, NULL, NULL); return 0; } @@ -142,7 +154,6 @@ int Bank_GetUserFlags(int UserID) { sqlite3_stmt *statement; char *query; - int rv; int ret; // Build Query @@ -164,12 +175,12 @@ int Bank_GetUserFlags(int UserID) // - Wheel if( sqlite3_column_int(statement, 2) ) ret |= USER_FLAG_WHEEL; // - Door - if( sqlite3_column_int(statement, 3) ) ret |= USER_FLAG_DOOR; + if( sqlite3_column_int(statement, 3) ) ret |= USER_FLAG_DOORGROUP; // - Internal if( sqlite3_column_int(statement, 3) ) ret |= USER_FLAG_INTERNAL; // Destroy and return - sqlite3_finalise(statement); + sqlite3_finalize(statement); return ret; } @@ -183,9 +194,10 @@ int Bank_SetUserFlags(int UserID, int Mask, int Value) int rv; char *errmsg; - #define MAP_FLAG(name, flag) (Mask&(flag)?(Value&(flag)?","name"=1":","name"=0")) + #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, MAP_FLAG("acct_is_coke", USER_FLAG_COKE), MAP_FLAG("acct_is_wheel", USER_FLAG_WHEEL), MAP_FLAG("acct_is_door", USER_FLAG_DOORGROUP), @@ -195,7 +207,7 @@ int Bank_SetUserFlags(int UserID, int Mask, int Value) #undef MAP_FLAG // Execute Query - rv = sqlite3_query(gBank_Database, query, NULL, NULL, &errmsg); + rv = sqlite3_exec(gBank_Database, query, NULL, NULL, &errmsg); free(query); if( rv != SQLITE_OK ) { @@ -225,14 +237,14 @@ int Bank_GetBalance(int AcctID) ret = sqlite3_column_int(statement, 0); // Clean up and return - sqlite3_finalise(statement); + sqlite3_finalize(statement); return ret; } /* * Get the name of an account */ -char *Bank_GetUserName(int AcctID) +char *Bank_GetAcctName(int AcctID) { sqlite3_stmt *statement; char *query; @@ -244,13 +256,251 @@ char *Bank_GetUserName(int AcctID) if( !statement ) return NULL; // Read return value - ret = strdup( sqlite3_column_text(statement, 0) ); + ret = strdup( (const char*)sqlite3_column_text(statement, 0) ); // Clean up and return - sqlite3_finalise(statement); + sqlite3_finalize(statement); + return ret; +} + +/* + * Get an account ID from a name + */ +int Bank_GetAcctByName(const char *Name) +{ + char *query; + sqlite3_stmt *statement; + int ret; + + if( !Bank_int_IsValidName(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; + + ret = sqlite3_column_int(statement, 0); + + sqlite3_finalize(statement); + return ret; +} + +/* + * Create a new named account + */ +int Bank_CreateAcct(const char *Name) +{ + char *query; + char *errmsg; + int rv; + + if( Name ) + { + if( !Bank_int_IsValidName(Name) ) return -1; + query = mkstr("INSERT INTO accounts (acct_name) VALUES ('%s')", Name); + } + else + { + query = strdup("INSERT INTO accounts (acct_name) VALUES (NULL)"); + } + + rv = sqlite3_exec(gBank_Database, query, NULL, NULL, &errmsg); + if( rv != SQLITE_OK ) + { + fprintf(stderr, "SQLite Error: '%s'\n", errmsg); + fprintf(stderr, "Query = '%s'\n", query); + sqlite3_free(errmsg); + free(query); + return -1; + } + + free(query); + + return sqlite3_last_insert_rowid(gBank_Database); +} + +/* + * Create an iterator for user accounts + */ +tAcctIterator *Bank_Iterator(int FlagMask, int FlagValues, int Flags, int MinMaxBalance, time_t LastSeen) +{ + char *query; + const char *balanceClause; + const char *lastSeenClause; + const char *orderClause; + const char *revSort; + sqlite3_stmt *ret; + + if( Flags & BANK_ITFLAG_MINBALANCE ) + balanceClause = "acct_balance>="; + else if( Flags & BANK_ITFLAG_MAXBALANCE ) + balanceClause = "acct_balance<="; + else { + balanceClause = "1!="; + MinMaxBalance = 0; + } + + if( Flags & BANK_ITFLAG_SEENAFTER ) + lastSeenClause = "acct_last_seen>="; + else if( Flags & BANK_ITFLAG_SEENBEFORE ) + lastSeenClause = "acct_last_seen<="; + else { + lastSeenClause = "datetime(0,'unixepoch')!="; + } + + switch( Flags & BANK_ITFLAG_SORTMASK ) + { + case BANK_ITFLAG_SORT_NONE: + orderClause = ""; + revSort = ""; + break; + case BANK_ITFLAG_SORT_NAME: + orderClause = "ORDER BY acct_name"; + revSort = " DESC"; + break; + case BANK_ITFLAG_SORT_BAL: + orderClause = "ORDER BY acct_balance"; + revSort = " DESC"; + break; + case BANK_ITFLAG_SORT_LASTSEEN: + orderClause = "ORDER BY acct_balance"; + revSort = " DESC"; + break; + default: + fprintf(stderr, "BUG: Unknown sort (%x) in SQLite CokeBank\n", Flags & BANK_ITFLAG_SORTMASK); + return NULL; + } + if( !(Flags & BANK_ITFLAG_REVSORT) ) + revSort = ""; + + #define MAP_FLAG(name, flag) (FlagMask&(flag)?(FlagValues&(flag)?" AND "name"=1":" AND "name"=0"):"") + query = mkstr("SELECT acct_id FROM accounts WHERE 1=1" + "%s%s%s%s%s" // Flags + "%s%i" // Balance + "%sdatetime(%lli,'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_door", USER_FLAG_DOORGROUP), + MAP_FLAG("acct_is_internal", USER_FLAG_INTERNAL), + MAP_FLAG("acct_is_disabled", USER_FLAG_DISABLED), + balanceClause, MinMaxBalance, + lastSeenClause, LastSeen, + orderClause, revSort + ); + #undef MAP_FLAG + + ret = Bank_int_MakeStatemnt(gBank_Database, query); + if( !ret ) return NULL; + + free(query); + + return (void*)ret; +} + +/* + * Get the next account in an iterator + */ +int Bank_IteratorNext(tAcctIterator *It) +{ + int rv; + rv = sqlite3_step( (sqlite3_stmt*)It ); + + if( rv == SQLITE_DONE ) return -1; + if( rv != SQLITE_ROW ) { + fprintf(stderr, "SQLite Error: %s\n", sqlite3_errmsg(gBank_Database)); + return -1; + } + + return sqlite3_column_int( (sqlite3_stmt*)It, 0 ); +} + +/* + * Free an interator + */ +void Bank_DelIterator(tAcctIterator *It) +{ + sqlite3_finalize( (sqlite3_stmt*)It ); +} + +/* + * Check user authentication token + */ +int Bank_GetUserAuth(const char *Salt, const char *Username, const char *Password) +{ + Salt = Password = Username; // Shut up GCC + // DEBUG HACKS! + #if 1 + return Bank_GetAcctByName(Username); + #else + return -1; + #endif +} + +/* + * Get an account number given a card ID + * NOTE: Actually ends up just being an alternate authentication token, + * as no checking is done on the ID's validity, save for SQL sanity. + */ +int Bank_GetAcctByCard(const char *CardID) +{ + char *query; + sqlite3_stmt *statement; + int ret; + + if( !Bank_int_IsValidName(CardID) ) + return -1; + + query = mkstr("SELECT acct_id FROM cards WHERE card_name='%s' LIMIT 1", CardID); + statement = Bank_int_QuerySingle(gBank_Database, query); + free(query); + if( !statement ) return -1; + + ret = sqlite3_column_int(statement, 0); + + sqlite3_finalize(statement); + return ret; } +/* + * Add a card to an account + */ +int Bank_AddAcctCard(int AcctID, const char *CardID) +{ + char *query; + int rv; + char *errmsg; + + if( !Bank_int_IsValidName(CardID) ) + return -1; + + // TODO: Check the AcctID too + + // 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); + if( rv == SQLITE_CONSTRAINT ) + { + sqlite3_free(errmsg); + free(query); + return 2; // Card in use + } + if( rv != SQLITE_OK ) + { + fprintf(stderr, "SQLite Error: '%s'\n", errmsg); + fprintf(stderr, "Query = '%s'\n", query); + sqlite3_free(errmsg); + free(query); + return -1; + } + free(query); + + return 0; +} + /* * Create a SQLite Statement */ @@ -259,7 +509,6 @@ sqlite3_stmt *Bank_int_MakeStatemnt(sqlite3 *Database, const char *Query) int rv; sqlite3_stmt *ret; rv = sqlite3_prepare_v2(Database, Query, strlen(Query)+1, &ret, NULL); - free(query); if( rv != SQLITE_OK ) { fprintf(stderr, "SQLite Error: %s\n", sqlite3_errmsg(Database)); fprintf(stderr, "query = \"%s\"\n", Query); @@ -280,10 +529,10 @@ sqlite3_stmt *Bank_int_QuerySingle(sqlite3 *Database, const char *Query) // Prepare query ret = Bank_int_MakeStatemnt(Database, Query); - if( !statement ) return NULL; + if( !ret ) return NULL; // Get row - rv = sqlite3_step(statement); + rv = sqlite3_step(ret); // - Empty result set if( rv == SQLITE_DONE ) return NULL; // - Other error @@ -295,3 +544,16 @@ sqlite3_stmt *Bank_int_QuerySingle(sqlite3 *Database, const char *Query) return ret; } + +/** + * \brief Checks if the passed account name is valid + */ +int Bank_int_IsValidName(const char *Name) +{ + while(*Name) + { + if( *Name == '\'' ) return 0; + Name ++; + } + return 1; +} -- 2.20.1