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 <inttypes.h>
  11 #include <stdlib.h>
  12 #include <limits.h>
  13 #include <stdio.h>
  14 #include <string.h>
  15 #include "../cokebank.h"
  16 #include <sqlite3.h>
  17
  18 #define DEBUG   0
  19
  20 const char * const csBank_DatabaseSetup =
  21 "CREATE TABLE IF NOT EXISTS accounts ("
  22 "       acct_id INTEGER PRIMARY KEY NOT NULL,"
  23 "       acct_balance INTEGER NOT NULL DEFAULT 0,"
  24 "       acct_last_seen DATETIME NOT NULL DEFAULT (datetime('now')),"
  25 "       acct_name STRING UNIQUE,"
  26 "       acct_uid INTEGER UNIQUE DEFAULT NULL,"
  27 "       acct_pin INTEGER DEFAULT NULL," //"     acct_pin INTEGER CHECK (acct_pin > 0 AND acct_pin < 10000) DEFAULT NULL,"
  28 "       acct_is_disabled BOOLEAN NOT NULL DEFAULT false,"
  29 "       acct_is_coke BOOLEAN NOT NULL DEFAULT false,"
  30 "       acct_is_admin BOOLEAN NOT NULL DEFAULT false,"
  31 "       acct_is_door BOOLEAN NOT NULL DEFAULT false,"
  32 "       acct_is_internal BOOLEAN NOT NULL DEFAULT false"
  33 ");"
  34 "CREATE TABLE IF NOT EXISTS cards ("
  35 "       acct_id INTEGER NOT NULL,"
  36 "       card_name STRING NOT NULL UNIQUE,"
  37 "       FOREIGN KEY (acct_id) REFERENCES accounts (acct_id) ON DELETE CASCADE"
  38 //                       Deletion of the account frees the card  ^ ^ ^
  39 ");"
  40 "CREATE TABLE IF NOT EXISTS items ("
  41 "       item_id INTEGER PRIMARY KEY NOT NULL,"
  42 "       item_handler STRING NOT NULL,"
  43 "       item_index INTEGER NOT NULL,"
  44 "       item_name STRING NOT NULL,"
  45 "       item_price INTEGER NOT NULL,"
  46 "       item_is_enabled BOOLEAN NOT NULL DEFAULT true"
  47 ");"
  48 "INSERT INTO accounts (acct_name,acct_is_admin,acct_uid) VALUES ('root',1,0);"
  49 "INSERT INTO accounts (acct_name,acct_is_internal,acct_uid) VALUES ('"COKEBANK_SALES_ACCT"',1,-1);"
  50 "INSERT INTO accounts (acct_name,acct_is_internal,acct_uid) VALUES ('"COKEBANK_DEBT_ACCT"',1,-2);"
  51 "INSERT INTO accounts (acct_name,acct_is_internal,acct_uid) VALUES ('"COKEBANK_FREE_ACCT"',1,-3);"
  52 ;
  53
  54 // === TYPES ===
  55 struct sAcctIterator    // Unused really, just used as a void type
  56 {
  57 };
  58
  59 // === PROTOYPES ===
  60  int    Bank_Initialise(const char *Argument);
  61  int    Bank_Transfer(int SourceAcct, int DestAcct, int Ammount, const char *Reason);
  62  int    Bank_GetFlags(int AcctID);
  63  int    Bank_SetFlags(int AcctID, int Mask, int Value);
  64  int    Bank_GetBalance(int AcctID);
  65 char    *Bank_GetAcctName(int AcctID);
  66 sqlite3_stmt    *Bank_int_MakeStatemnt(sqlite3 *Database, const char *Query);
  67  int    Bank_int_QueryNone(sqlite3 *Database, const char *Query, char **ErrorMessage);
  68 sqlite3_stmt    *Bank_int_QuerySingle(sqlite3 *Database, const char *Query);
  69  int    Bank_int_IsValidName(const char *Name);
  70
  71 // === GLOBALS ===
  72 sqlite3 *gBank_Database;
  73
  74 // === CODE ===
  75 int Bank_Initialise(const char *Argument)
  76 {
  77          int    rv;
  78         char    *errmsg;
  79         // Open database
  80         rv = sqlite3_open(Argument, &gBank_Database);
  81         if(rv != 0)
  82         {
  83                 fprintf(stderr, "CokeBank: Unable to open database '%s'\n", Argument);
  84                 fprintf(stderr, "Reason: %s\n", sqlite3_errmsg(gBank_Database));
  85                 sqlite3_close(gBank_Database);
  86                 return 1;
  87         }
  88
  89         // Check structure
  90         rv = Bank_int_QueryNone(gBank_Database, "SELECT acct_id FROM accounts LIMIT 1", &errmsg);
  91         if( rv == SQLITE_OK )
  92         {
  93                 // NOP
  94         }
  95         else if( rv == SQLITE_NOTFOUND || rv == SQLITE_ERROR )
  96         {
  97                 sqlite3_free(errmsg);
  98                 // Create tables
  99                 rv = Bank_int_QueryNone(gBank_Database, csBank_DatabaseSetup, &errmsg);
 100                 if( rv != SQLITE_OK ) {
 101                         fprintf(stderr, "Bank_Initialise - SQLite Error: %s\n", errmsg);
 102                         sqlite3_free(errmsg);
 103                         return 1;
 104                 }
 105
 106                 Log_Info("SQLite database rebuilt (%s)", Argument);
 107         }
 108         else
 109         {
 110                 // Unknown error
 111                 fprintf(stderr, "Bank_Initialise - SQLite Error: %s (rv = %i)\n", errmsg, rv);
 112                 sqlite3_free(errmsg);
 113                 return 1;
 114         }
 115
 116         return 0;
 117 }
 118
 119 /*
 120  * Move Money
 121  */
 122 int Bank_Transfer(int SourceUser, int DestUser, int Ammount, const char *Reason)
 123 {
 124         char    *query;
 125          int    rv;
 126         char    *errmsg;
 127
 128         Reason = "";    // Shut GCC up
 129
 130         // Begin SQL Transaction
 131         Bank_int_QueryNone(gBank_Database, "BEGIN TRANSACTION", NULL);
 132
 133         // Take from the source
 134         query = mkstr("UPDATE accounts SET acct_balance=acct_balance%+i,acct_last_seen=datetime('now') WHERE acct_id=%i", -Ammount, SourceUser);
 135 //      printf("query = \"%s\"\n", query);
 136         rv = Bank_int_QueryNone(gBank_Database, query, &errmsg);
 137         free(query);
 138         if( rv != SQLITE_OK )
 139         {
 140                 fprintf(stderr, "Bank_Transfer - SQLite Error: %s\n", errmsg);
 141                 sqlite3_free(errmsg);
 142                 Bank_int_QueryNone(gBank_Database, "ROLLBACK",  NULL);
 143                 return 1;
 144         }
 145
 146         // Give to the destination
 147         query = mkstr("UPDATE accounts SET acct_balance=acct_balance%+i,acct_last_seen=datetime('now') WHERE acct_id=%i", Ammount, DestUser);
 148 //      printf("query = \"%s\"\n", query);
 149         rv = Bank_int_QueryNone(gBank_Database, query, &errmsg);
 150         free(query);
 151         if( rv != SQLITE_OK )
 152         {
 153                 fprintf(stderr, "Bank_Transfer - SQLite Error: %s\n", errmsg);
 154                 sqlite3_free(errmsg);
 155                 Bank_int_QueryNone(gBank_Database, "ROLLBACK", NULL);
 156                 return 1;
 157         }
 158
 159         // Commit transaction
 160         Bank_int_QueryNone(gBank_Database, "COMMIT", NULL);
 161
 162         return 0;
 163 }
 164
 165 /*
 166  * Get user flags
 167  */
 168 int Bank_GetFlags(int UserID)
 169 {
 170         sqlite3_stmt    *statement;
 171         char    *query;
 172          int    ret;
 173
 174         // Build Query
 175         query = mkstr(
 176                 "SELECT acct_is_disabled,acct_is_coke,acct_is_admin,acct_is_door,acct_is_internal"
 177                 " FROM accounts WHERE acct_id=%i LIMIT 1",
 178                 UserID
 179                 );
 180         statement = Bank_int_QuerySingle(gBank_Database, query);
 181         free(query);
 182         if( !statement )        return -1;
 183
 184         // Get Flags
 185         ret = 0;
 186         // - Disabled
 187         if( sqlite3_column_int(statement, 0) )  ret |= USER_FLAG_DISABLED;
 188         // - Coke
 189         if( sqlite3_column_int(statement, 1) )  ret |= USER_FLAG_COKE;
 190         // - Wheel
 191         if( sqlite3_column_int(statement, 2) )  ret |= USER_FLAG_ADMIN;
 192         // - Door
 193         if( sqlite3_column_int(statement, 3) )  ret |= USER_FLAG_DOORGROUP;
 194         // - Internal
 195         if( sqlite3_column_int(statement, 4) )  ret |= USER_FLAG_INTERNAL;
 196
 197         // Destroy and return
 198         sqlite3_finalize(statement);
 199
 200         return ret;
 201 }
 202
 203 /*
 204  * Set user flags
 205  */
 206 int Bank_SetFlags(int UserID, int Mask, int Value)
 207 {
 208         char    *query;
 209          int    rv;
 210         char    *errmsg;
 211
 212         #define MAP_FLAG(name, flag)    (Mask&(flag)?(Value&(flag)?","name"=1":","name"=0"):"")
 213         query = mkstr(
 214                 "UPDATE accounts SET acct_id=acct_id%s%s%s%s%s WHERE acct_id=%i",// LIMIT 1",
 215                 MAP_FLAG("acct_is_coke", USER_FLAG_COKE),
 216                 MAP_FLAG("acct_is_admin", USER_FLAG_ADMIN),
 217                 MAP_FLAG("acct_is_door", USER_FLAG_DOORGROUP),
 218                 MAP_FLAG("acct_is_internal", USER_FLAG_INTERNAL),
 219                 MAP_FLAG("acct_is_disabled", USER_FLAG_DISABLED),
 220                 UserID
 221                 );
 222         #undef MAP_FLAG
 223
 224         #if DEBUG
 225         printf("Bank_SetFlags: query=\"%s\"\n", query);
 226         #endif
 227
 228         // Execute Query
 229         rv = Bank_int_QueryNone(gBank_Database, query, &errmsg);
 230         if( rv != SQLITE_OK )
 231         {
 232                 fprintf(stderr, "Bank_SetUserFlags - SQLite Error: %s\n", errmsg);
 233                 fprintf(stderr, "query = '%s'\n", query);
 234                 free(query);
 235                 sqlite3_free(errmsg);
 236                 return -1;
 237         }
 238         free(query);
 239
 240         return 0;
 241 }
 242
 243 /*
 244  * Get user balance
 245  */
 246 int Bank_GetBalance(int AcctID)
 247 {
 248         sqlite3_stmt    *statement;
 249         char    *query;
 250          int    ret;
 251
 252         query = mkstr("SELECT acct_balance FROM accounts WHERE acct_id=%i LIMIT 1", AcctID);
 253         statement = Bank_int_QuerySingle(gBank_Database, query);
 254         free(query);
 255         if( !statement )        return INT_MIN;
 256
 257         // Read return value
 258         ret = sqlite3_column_int(statement, 0);
 259
 260         // Clean up and return
 261         sqlite3_finalize(statement);
 262         return ret;
 263 }
 264
 265 /*
 266  * Get the name of an account
 267  */
 268 char *Bank_GetAcctName(int AcctID)
 269 {
 270         sqlite3_stmt    *statement;
 271         char    *query;
 272         char    *ret;
 273
 274         query = mkstr("SELECT acct_name FROM accounts WHERE acct_id=%i LIMIT 1", AcctID);
 275         statement = Bank_int_QuerySingle(gBank_Database, query);
 276         free(query);
 277         if( !statement )        return NULL;
 278
 279         // Read return value
 280         ret = strdup( (const char*)sqlite3_column_text(statement, 0) );
 281
 282         // Clean up and return
 283         sqlite3_finalize(statement);
 284         return ret;
 285 }
 286
 287 /*
 288  * Get an account ID from a name
 289  */
 290 int Bank_GetAcctByName(const char *Name, int bCreate)
 291 {
 292         char    *query;
 293         sqlite3_stmt    *statement;
 294          int    ret;
 295
 296 //      printf("Bank_GetAcctByName: (Name='%s',bCreate=%i)\n", Name, bCreate);
 297
 298         if( !Bank_int_IsValidName(Name) ) {
 299 //              printf("RETURN: -1 (Bad name)");
 300                 return -1;
 301         }
 302
 303         query = mkstr("SELECT acct_id FROM accounts WHERE acct_name='%s' LIMIT 1", Name);
 304         statement = Bank_int_QuerySingle(gBank_Database, query);
 305         free(query);
 306         if( !statement ) {
 307 //              printf("User not found\n");
 308                 if( bCreate )   return Bank_CreateAcct(Name);
 309                 return -1;
 310         }
 311
 312         ret = sqlite3_column_int(statement, 0);
 313         sqlite3_finalize(statement);
 314
 315 //      printf("ret = %i\n", ret);
 316
 317         if( ret == 0 ) {
 318                 return -1;
 319         }
 320         return ret;
 321 }
 322
 323 /*
 324  * Create a new named account
 325  */
 326 int Bank_CreateAcct(const char *Name)
 327 {
 328         char    *query;
 329         char    *errmsg;
 330          int    rv;
 331
 332         if( Name )
 333         {
 334                 if( !Bank_int_IsValidName(Name) )       return -1;
 335                 query = mkstr("INSERT INTO accounts (acct_name) VALUES ('%s')", Name);
 336         }
 337         else
 338         {
 339                 query = strdup("INSERT INTO accounts (acct_name) VALUES (NULL)");
 340         }
 341
 342         rv = Bank_int_QueryNone(gBank_Database, query, &errmsg);
 343         if( rv != SQLITE_OK )
 344         {
 345                 fprintf(stderr, "Bank_CreateAcct - SQLite Error: '%s'\n", errmsg);
 346                 fprintf(stderr, "Query = '%s'\n", query);
 347                 sqlite3_free(errmsg);
 348                 free(query);
 349                 return -1;
 350         }
 351
 352         free(query);
 353
 354         return sqlite3_last_insert_rowid(gBank_Database);
 355 }
 356
 357 /*
 358  * Create an iterator for user accounts
 359  */
 360 tAcctIterator *Bank_Iterator(int FlagMask, int FlagValues, int Flags, int MinMaxBalance, time_t LastSeen)
 361 {
 362         char    *query;
 363         const char      *balanceClause;
 364         const char      *lastSeenClause;
 365         const char      *orderClause;
 366         const char      *revSort;
 367         sqlite3_stmt    *ret;
 368
 369         // Balance condtion
 370         if( Flags & BANK_ITFLAG_MINBALANCE )
 371                 balanceClause = " AND acct_balance>=";
 372         else if( Flags & BANK_ITFLAG_MAXBALANCE )
 373                 balanceClause = " AND acct_balance<=";
 374         else {
 375                 balanceClause = " AND 1!=";
 376                 MinMaxBalance = 0;
 377         }
 378
 379         // Last seen condition
 380         if( Flags & BANK_ITFLAG_SEENAFTER )
 381                 lastSeenClause = " AND acct_last_seen>=";
 382         else if( Flags & BANK_ITFLAG_SEENBEFORE )
 383                 lastSeenClause = " AND acct_last_seen<=";
 384         else {
 385                 lastSeenClause = " AND datetime(-1,'unixepoch')!=";
 386         }
 387
 388         // Sorting clause
 389         switch( Flags & BANK_ITFLAG_SORTMASK )
 390         {
 391         case BANK_ITFLAG_SORT_NONE:
 392                 orderClause = "";
 393                 revSort = "";
 394                 break;
 395         case BANK_ITFLAG_SORT_NAME:
 396                 orderClause = "ORDER BY acct_name";
 397                 revSort = " DESC";
 398                 break;
 399         case BANK_ITFLAG_SORT_BAL:
 400                 orderClause = "ORDER BY acct_balance";
 401                 revSort = " DESC";
 402                 break;
 403         case BANK_ITFLAG_SORT_LASTSEEN:
 404                 orderClause = "ORDER BY acct_balance";
 405                 revSort = " DESC";
 406                 break;
 407         default:
 408                 fprintf(stderr, "BUG: Unknown sort (%x) in SQLite CokeBank\n", Flags & BANK_ITFLAG_SORTMASK);
 409                 return NULL;
 410         }
 411         if( !(Flags & BANK_ITFLAG_REVSORT) )
 412                 revSort = "";
 413
 414         #define MAP_FLAG(name, flag)    (FlagMask&(flag)?(FlagValues&(flag)?" AND "name"=1":" AND "name"=0"):"")
 415         query = mkstr("SELECT acct_id FROM accounts WHERE 1=1"
 416                 "%s%s%s%s%s"    // Flags
 417                 "%s%i"  // Balance
 418                 "%sdatetime(%"PRIu64",'unixepoch')"     // Last seen
 419                 "%s%s"  // Sort and direction
 420                 ,
 421                 MAP_FLAG("acct_is_coke", USER_FLAG_COKE),
 422                 MAP_FLAG("acct_is_admin", USER_FLAG_ADMIN),
 423                 MAP_FLAG("acct_is_door", USER_FLAG_DOORGROUP),
 424                 MAP_FLAG("acct_is_internal", USER_FLAG_INTERNAL),
 425                 MAP_FLAG("acct_is_disabled", USER_FLAG_DISABLED),
 426                 balanceClause, MinMaxBalance,
 427                 lastSeenClause, (uint64_t)LastSeen,
 428                 orderClause, revSort
 429                 );
 430         //printf("query = \"%s\"\n", query);
 431         #undef MAP_FLAG
 432
 433         ret = Bank_int_MakeStatemnt(gBank_Database, query);
 434         if( !ret )      return NULL;
 435
 436         free(query);
 437
 438         return (void*)ret;
 439 }
 440
 441 /*
 442  * Get the next account in an iterator
 443  */
 444 int Bank_IteratorNext(tAcctIterator *It)
 445 {
 446          int    rv;
 447         rv = sqlite3_step( (sqlite3_stmt*)It );
 448
 449         if( rv == SQLITE_DONE ) return -1;
 450         if( rv != SQLITE_ROW ) {
 451                 fprintf(stderr, "Bank_IteratorNext - SQLite Error: %s\n", sqlite3_errmsg(gBank_Database));
 452                 return -1;
 453         }
 454
 455         return sqlite3_column_int( (sqlite3_stmt*)It, 0 );
 456 }
 457
 458 /*
 459  * Free an interator
 460  */
 461 void Bank_DelIterator(tAcctIterator *It)
 462 {
 463         sqlite3_finalize( (sqlite3_stmt*)It );
 464 }
 465
 466 /*
 467  * Check user authentication token
 468  */
 469 int Bank_GetUserAuth(const char *Salt, const char *Username, const char *Password)
 470 {
 471         Salt = Password = Username;     // Shut up GCC
 472         // DEBUG HACKS!
 473         #if 0
 474         return Bank_GetAcctByName(Username);
 475         #else
 476         return -1;
 477         #endif
 478 }
 479
 480 /*
 481  * Get an account number given a card ID
 482  * NOTE: Actually ends up just being an alternate authentication token,
 483  *       as no checking is done on the ID's validity, save for SQL sanity.
 484  */
 485 int Bank_GetAcctByCard(const char *CardID)
 486 {
 487         char    *query;
 488         sqlite3_stmt    *statement;
 489          int    ret;
 490
 491         if( !Bank_int_IsValidName(CardID) )
 492                 return -1;
 493
 494         query = mkstr("SELECT acct_id FROM cards WHERE card_name='%s' LIMIT 1", CardID);
 495         statement = Bank_int_QuerySingle(gBank_Database, query);
 496         free(query);
 497         if( !statement )        return -1;
 498
 499         ret = sqlite3_column_int(statement, 0);
 500
 501         sqlite3_finalize(statement);
 502
 503         return ret;
 504 }
 505
 506 /*
 507  * Add a card to an account
 508  */
 509 int Bank_AddAcctCard(int AcctID, const char *CardID)
 510 {
 511         char    *query;
 512          int    rv;
 513         char    *errmsg;
 514
 515         if( !Bank_int_IsValidName(CardID) )
 516                 return -1;
 517
 518         // TODO: Check the AcctID too
 519
 520         // Insert card
 521         query = mkstr("INSERT INTO cards (acct_id,card_name) VALUES (%i,'%s')",
 522                 AcctID, CardID);
 523         rv = Bank_int_QueryNone(gBank_Database, query, &errmsg);
 524         if( rv == SQLITE_CONSTRAINT )
 525         {
 526                 sqlite3_free(errmsg);
 527                 free(query);
 528                 return 2;       // Card in use
 529         }
 530         if( rv != SQLITE_OK )
 531         {
 532                 fprintf(stderr, "Bank_AddAcctCard - SQLite Error: '%s'\n", errmsg);
 533                 fprintf(stderr, "Query = '%s'\n", query);
 534                 sqlite3_free(errmsg);
 535                 free(query);
 536                 return -1;
 537         }
 538         free(query);
 539
 540         return 0;
 541 }
 542
 543 /*
 544  * Create a SQLite Statement
 545  */
 546 sqlite3_stmt *Bank_int_MakeStatemnt(sqlite3 *Database, const char *Query)
 547 {
 548          int    rv;
 549         sqlite3_stmt    *ret;
 550         rv = sqlite3_prepare_v2(Database, Query, strlen(Query)+1, &ret, NULL);
 551         if( rv != SQLITE_OK ) {
 552                 fprintf(stderr, "SQLite Error: %s\n", sqlite3_errmsg(Database));
 553                 fprintf(stderr, "query = \"%s\"\n", Query);
 554                 return NULL;
 555         }
 556
 557         return ret;
 558 }
 559
 560 int Bank_int_QueryNone(sqlite3 *Database, const char *Query, char **ErrorMessage)
 561 {
 562         #if DEBUG
 563         printf("Bank_int_QueryNone: (Query='%s')\n", Query);
 564         #endif
 565         return sqlite3_exec(Database, Query, NULL, NULL, ErrorMessage);
 566 }
 567
 568 /*
 569  * Create a SQLite statement and query it for the first row
 570  * Returns NULL if the the set is empty
 571  */
 572 sqlite3_stmt *Bank_int_QuerySingle(sqlite3 *Database, const char *Query)
 573 {
 574         sqlite3_stmt    *ret;
 575          int    rv;
 576
 577         #if DEBUG
 578         printf("Bank_int_QuerySingle: (Query='%s')\n", Query);
 579         #endif
 580
 581         // Prepare query
 582         ret = Bank_int_MakeStatemnt(Database, Query);
 583         if( !ret ) {
 584                 #if DEBUG >= 2
 585                 printf("Bank_int_QuerySingle: RETURN NULL ret=NULL\n");
 586                 #endif
 587                 return NULL;
 588         }
 589
 590         // Get row
 591         rv = sqlite3_step(ret);
 592         // - Empty result set
 593         if( rv == SQLITE_DONE ) {
 594                 #if DEBUG >= 2
 595                 printf("Bank_int_QuerySingle: RETURN NULL (rv == SQLITE_DONE)\n");
 596                 #endif
 597                 return NULL;
 598         }
 599         // - Other error
 600         if( rv != SQLITE_ROW ) {
 601                 fprintf(stderr, "SQLite Error: %s\n", sqlite3_errmsg(gBank_Database));
 602                 fprintf(stderr, "query = \"%s\"\n", Query);
 603                 return NULL;
 604         }
 605
 606         #if DEBUG >= 2
 607         printf("Bank_int_QuerySingle: RETURN %p\n", ret);
 608         #endif
 609         return ret;
 610 }
 611
 612 /**
 613  * \brief Checks if the passed account name is valid
 614  */
 615 int Bank_int_IsValidName(const char *Name)
 616 {
 617         if( !Name )     return 0;
 618         while(*Name)
 619         {
 620                 if( *Name == '\'' )     return 0;
 621                 Name ++;
 622         }
 623         return 1;
 624 }