src/cokebank_sqlite/main.c

   1 /*
   2  * OpenDispense 2
   3  * UCC (University [of WA] Computer Club) Electronic Accounting System
   4  *
   5  * SQLite Coke Bank (Accounts Database)
   6  *
   7  * This file is licenced under the 3-clause BSD Licence. See the file
   8  * COPYING for full details.
   9  */
  10 #include "../cokebank.h"
  11 #include <sqlite3.h>
  12
  13 const char * const csBank_CreateAccountQry = "CREATE TABLE IF NOT EXISTS accounts ("
  14 "       acct_id INTEGER PRIMARY KEY NOT NULL,"
  15 "       acct_balance INTEGER NOT NULL,"
  16 "       acct_name STRING UNIQUE,"
  17 "       acct_uid INTEGER UNIQUE,"
  18 "       acct_pin INTEGER CHECK (acct_pin > 0 AND acct_pin < 10000),"
  19 "       acct_is_disabled BOOLEAN NOT NULL DEFAULT false,"
  20 "       acct_is_coke BOOLEAN NOT NULL DEFAULT false,"
  21 "       acct_is_wheel BOOLEAN NOT NULL DEFAULT false,"
  22 "       acct_is_door BOOLEAN NOT NULL DEFAULT false,"
  23 "       acct_is_internal BOOLEAN NOT NULL DEFAULT false"
  24 ")";
  25 const char * const csBank_CreateCardsQry = "CREATE TABLE IF NOT EXISTS cards ("
  26 "       acct_id INTEGER NOT NULL,"
  27 "       card_name STRING NOT NULL UNIQUE,"
  28 "       FOREIGN KEY (acct_id) REFERENCES accounts (acct_id) ON DELETE CASCADE"
  29 //                       Deletion of the account frees the card  ^ ^ ^
  30 ")";
  31
  32 // === PROTOYPES ===
  33  int    Bank_Initialise(const char *Argument);
  34  int    Bank_Transfer(int SourceUser, int DestUser, int Ammount, const char *Reason);
  35  int    Bank_GetUserFlags(int UserID);
  36  int    Bank_SetUserFlags(int UserID, int Mask, int Value);
  37 sqlite3_stmt    *Bank_int_MakeStatemnt(sqlite3 *Database, const char *Query);
  38
  39 // === GLOBALS ===
  40 sqlite3 *gBank_Database;
  41
  42 // === CODE ===
  43 int Bank_Initialise(const char *Argument)
  44 {
  45          int    rv;
  46         char    *errmsg;
  47         // Open database
  48         rv = sqlite3_open(Argument, &gBank_Database);
  49         if(rv != 0)
  50         {
  51                 fprintf(stderr, "CokeBank: Unable to open database '%s'\n", Argument);
  52                 fprintf(stderr, "Reason: %s\n", sqlite3_errmsg(gBank_Database));
  53                 sqlite3_close(gBank_Database);
  54                 return 1;
  55         }
  56
  57         // Check structure
  58         rv = sqlite3_exec(gBank_Database, "SELECT acct_id FROM accounts LIMIT 1", NULL, NULL, &errmsg);
  59         if( rv == SQLITE_OK )
  60         {
  61                 // NOP
  62         }
  63         else if( rv == SQLITE_NOTFOUND )
  64         {
  65                 sqlite3_free(errmsg);
  66                 // Create tables
  67                 // - Accounts
  68                 rv = sqlite3_exec(gBank_Database, csBank_CreateAccountQry, NULL, NULL, &errmsg);
  69                 if( rv != SQLITE_OK ) {
  70                         fprintf(stderr, "SQLite Error: %s\n", errmsg);
  71                         sqlite3_free(errmsg);
  72                         return 1;
  73                 }
  74                 // - Mifare relation
  75                 rv = sqlite3_exec(gBank_Database, csBank_CreateCardsQry, NULL, NULL, &errmsg);
  76                 if( rv != SQLITE_OK ) {
  77                         fprintf(stderr, "SQLite Error: %s\n", errmsg);
  78                         sqlite3_free(errmsg);
  79                         return 1;
  80                 }
  81         }
  82         else
  83         {
  84                 // Unknown error
  85                 fprintf(stderr, "SQLite Error: %s\n", errmsg);
  86                 sqlite3_free(errmsg);
  87                 return 1;
  88         }
  89
  90         return 0;
  91 }
  92
  93 /*
  94  * Move Money
  95  */
  96 int Bank_Transfer(int SourceUser, int DestUser, int Ammount, const char *Reason)
  97 {
  98         char    *query
  99          int    rv;
 100         char    *errmsg;
 101
 102         // Begin SQL Transaction
 103         sqlite3_exec(gBank_Database, "BEGIN TRANSACTION", NULL, NULL, NULL);
 104
 105         // Take from the source
 106         query = mkstr("UPDATE accounts SET acct_balance=acct_balance-%i WHERE acct_id=%i", Ammount, SourceUser);
 107         rv = sqlite3_exec(gBank_Database, query, NULL, NULL, &errmsg);
 108         free(query);
 109         if( rv != SQLITE_OK )
 110         {
 111                 fprintf(stderr, "SQLite Error: %s\n", errmsg);
 112                 sqlite3_free(errMsg);
 113                 sqlite3_query(gBank_Database, "ROLLBACK", NULL, NULL, NULL);
 114                 return 1;
 115         }
 116
 117         // Give to the destination
 118         query = mkstr("UPDATE accounts SET acct_balance=acct_balance+%i WHERE acct_id=%i", Ammount, DestUser);
 119         rv = sqlite3_exec(gBank_Database, query, NULL, NULL, &errmsg);
 120         free(query);
 121         if( rv != SQLITE_OK )
 122         {
 123                 fprintf(stderr, "SQLite Error: %s\n", errmsg);
 124                 sqlite3_free(errmsg);
 125                 sqlite3_query(gBank_Database, "ROLLBACK", NULL, NULL, NULL);
 126                 return 1;
 127         }
 128
 129         // Commit transaction
 130         sqlite3_query(gBank_Database, "COMMIT", NULL, NULL, NULL);
 131
 132         return 0;
 133 }
 134
 135 /*
 136  * Get user flags
 137  */
 138 int Bank_GetUserFlags(int UserID)
 139 {
 140         sqlite3_stmt    *statement;
 141         char    *query;
 142          int    rv;
 143          int    ret;
 144
 145         // Build Query
 146         query = mkstr("SELECT acct_is_disabled,acct_is_coke,acct_is_wheel,acct_is_door,acct_is_internal FROM accounts WHERE acct_id=%i LIMIT 1", UserID);
 147         rv = sqlite3_prepare_v2(gBank_Database, query, strlen(query)+1, &statement, NULL);
 148         free(query);
 149         if( rv != SQLITE_OK ) {
 150                 fprintf(stderr, "SQLite Error: %s\n", sqlite3_errmsg(gBank_Database));
 151                 return -1;
 152         }
 153
 154         // Execute Query
 155         rv = sqlite3_step(statement);
 156         if( rv != SQLITE_ROW )
 157         {
 158                 sqlite3_finalise(statement);
 159                 if( rv == SQLITE_DONE )
 160                 {
 161                         return -1;      // User not found
 162                 }
 163                 if( rv != SQLITE_OK ) {
 164                         fprintf(stderr, "SQLite Error: %s\n", sqlite3_errmsg(gBank_Database));
 165                         return -1;
 166                 }
 167         }
 168
 169         // Get Flags
 170         ret = 0;
 171         // - Disabled
 172         if( sqlite3_column_int(statement, 0) )  ret |= USER_FLAG_DISABLED;
 173         // - Coke
 174         if( sqlite3_column_int(statement, 1) )  ret |= USER_FLAG_COKE;
 175         // - Wheel
 176         if( sqlite3_column_int(statement, 2) )  ret |= USER_FLAG_WHEEL;
 177         // - Door
 178         if( sqlite3_column_int(statement, 3) )  ret |= USER_FLAG_DOOR;
 179         // - Internal
 180         if( sqlite3_column_int(statement, 3) )  ret |= USER_FLAG_INTERNAL;
 181
 182         // Destroy and return
 183         sqlite3_finalise(statement);
 184
 185         return ret;
 186 }
 187
 188 /*
 189  * Set user flags
 190  */
 191 int Bank_SetUserFlags(int UserID, int Mask, int Value)
 192 {
 193         char    *query;
 194          int    rv;
 195         char    *errmsg;
 196
 197         #define MAP_FLAG(name, flag)    (Mask&(flag)?(Value&(flag)?","name"=1":","name"=0"))
 198         query = mkstr(
 199                 "UDPATE accounts WHERE acct_id=%i SET acct_id=acct_id%s%s%s%s%s",
 200                 MAP_FLAG("acct_is_coke", USER_FLAG_COKE),
 201                 MAP_FLAG("acct_is_wheel", USER_FLAG_WHEEL),
 202                 MAP_FLAG("acct_is_door", USER_FLAG_DOORGROUP),
 203                 MAP_FLAG("acct_is_internal", USER_FLAG_INTERNAL),
 204                 MAP_FLAG("acct_is_disabled", USER_FLAG_DISABLED)
 205                 );
 206         #undef MAP_FLAG
 207
 208         // Execute Query
 209         rv = sqlite3_query(gBank_Database, query, NULL, NULL, &errmsg);
 210         free(query);
 211         if( rv != SQLITE_OK )
 212         {
 213                 fprintf(stderr, "SQLite Error: %s\n", errmsg);
 214                 sqlite3_free(errmsg);
 215                 return -1;
 216         }
 217
 218         return 0;
 219 }
 220
 221 /*
 222  * Get user balance
 223  */
 224 int Bank_GetBalance(int User)
 225 {
 226         sqlite3_stmt    *statement;
 227         char    *query;
 228
 229         query = mkstr("SELECT acct_balance FROM accounts WHERE acct_id=%i", User);
 230
 231 }
 232
 233
 234 sqlite3_stmt *Bank_int_MakeStatemnt(sqlite3 *Database, const char *Query)
 235 {
 236          int    rv;
 237         sqlite3_stmt    *ret;
 238         rv = sqlite3_prepare_v2(Database, Query, strlen(Query)+1, &ret, NULL);
 239         free(query);
 240         if( rv != SQLITE_OK ) {
 241                 fprintf(stderr, "SQLite Error: %s\n", sqlite3_errmsg(gBank_Database));
 242                 fprintf(stderr, "query = \"%s\"\n", Query);
 243                 return NULL;
 244         }
 245
 246         return ret;
 247 }