UnsignedByte/Resource/SqliteMgr.cpp

   1 /***************************************************************************
   2  *   Copyright (C) 2008 by Sverre Rabbelier                                *
   3  *   sverre@rabbelier.nl                                                   *
   4  *                                                                         *
   5  *   This program is free software; you can redistribute it and/or modify  *
   6  *   it under the terms of the GNU General Public License as published by  *
   7  *   the Free Software Foundation; either version 3 of the License, or     *
   8  *   (at your option) any later version.                                   *
   9  *                                                                         *
  10  *   This program is distributed in the hope that it will be useful,       *
  11  *   but WITHOUT ANY WARRANTY; without even the implied warranty of        *
  12  *   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the         *
  13  *   GNU General Public License for more details.                          *
  14  *                                                                         *
  15  *   You should have received a copy of the GNU General Public License     *
  16  *   along with this program; if not, write to the                         *
  17  *   Free Software Foundation, Inc.,                                       *
  18  *   59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.             *
  19  ***************************************************************************/
  20
  21 #include "FieldDef.h"
  22 #include "TableDef.h"
  23 #include "SavableManager.h"
  24 #include "SelectionMask.h"
  25 #include "TableImpl.h"
  26 #include "FieldImpl.h"
  27 #include "KeyDef.h"
  28 #include "Key.h"
  29 #include "ForeignKey.h"
  30 #include "Join.h"
  31
  32 #include "SqliteMgr.h"
  33 #include "DatabaseMgr.h"
  34 #include "Actor.h"
  35 #include "Statements.h"
  36 #include "StatementStrings.h"
  37 #include "Exceptions.h"
  38 #include "Assert.h"
  39
  40 SqliteMgr::SqliteMgr()
  41 {
  42         m_db = DatabaseMgr::Get()->DB();
  43         m_odb = m_db->grabdb();
  44
  45         Assert(m_db);
  46         Assert(m_odb);
  47 }
  48
  49 SqliteMgr::~SqliteMgr()
  50 {
  51         m_db->freedb(m_odb);
  52 }
  53
  54 void SqliteMgr::doInsert(SavableManager* bindable)
  55 {
  56         Assert(bindable);
  57
  58         TableImpl* table = bindable->getTable().get();
  59         Assert(table);
  60
  61         sqlite3_stmt* insert = getInsertStmt(table);
  62         Assert(insert);
  63
  64         sqlite3_reset(insert);
  65
  66         if(!table->hasSingularPrimaryKey())
  67                 bindable->bindKeys(m_odb->db, insert);
  68
  69         doStatement(insert);
  70
  71         if(table->hasSingularPrimaryKey())
  72                 bindable->parseInsert(m_odb->db);
  73
  74         commit(table);
  75 }
  76
  77 void SqliteMgr::doErase(SavableManager* bindable)
  78 {
  79         Assert(bindable);
  80
  81         TableImpl* table = bindable->getTable().get();
  82         Assert(table);
  83
  84         sqlite3_stmt* erase = getEraseStmt(table);
  85         Assert(erase);
  86
  87         sqlite3_reset(erase);
  88
  89         bindable->bindKeys(m_odb->db, erase);
  90         doStatement(erase);
  91
  92         commit(table);
  93 }
  94
  95 void SqliteMgr::doUpdate(SavableManager* bindable)
  96 {
  97         Assert(bindable);
  98
  99         TableImpl* table = bindable->getTable().get();
 100         Assert(table);
 101
 102         sqlite3_stmt* update = getUpdateStmt(table);
 103         Assert(update);
 104
 105         sqlite3_reset(update);
 106
 107         bindable->bindUpdate(m_odb->db, update);
 108         doStatement(update);
 109
 110         commit(table);
 111 }
 112
 113 void SqliteMgr::doSelect(SavableManager* bindable)
 114 {
 115         Assert(bindable);
 116
 117         TableImpl* table = bindable->getTable().get();
 118         Assert(table);
 119
 120         sqlite3_stmt* select = getSelectStmt(table);
 121         Assert(select);
 122
 123         sqlite3_reset(select);
 124
 125         bindable->bindKeys(m_odb->db, select);
 126         bool row = doStatement(select);
 127         if(row)
 128                 bindable->parseSelect(select);
 129         else
 130                 throw RowNotFoundException("SqliteMgr::doSelect(), no row.");
 131 }
 132
 133 void SqliteMgr::doLookup(SavableManager* bindable, FieldPtr field)
 134 {
 135         Assert(bindable);
 136         Assert(field);
 137
 138         TableImpl* table = bindable->getTable().get();
 139         Assert(table);
 140
 141         sqlite3_stmt* lookup = getLookupStmt(table, field);
 142         Assert(lookup);
 143
 144         sqlite3_reset(lookup);
 145
 146         bindable->bindLookup(m_odb->db, lookup);
 147         bool row = doStatement(lookup);
 148         if(row)
 149         {
 150                 bindable->parseLookup(lookup);
 151                 doSelect(bindable);
 152         }
 153         else
 154                 throw RowNotFoundException("SqliteMgr::doLookup(), no row.");
 155 }
 156
 157 void SqliteMgr::doForEach(TableImpl* table, Actor& act)
 158 {
 159         Assert(table);
 160
 161         sqlite3_stmt* forEach = getForEachStmt(table);
 162         Assert(forEach);
 163
 164         sqlite3_reset(forEach);
 165
 166         bool good = true;
 167         for(int i = 0; good; i++)
 168         {
 169                 good = doStatement(forEach);
 170                 if(good)
 171                         act.parseRow(forEach, table);
 172         }
 173 }
 174
 175 void SqliteMgr::doSelectMulti(SelectionMask* mask)
 176 {
 177         Assert(mask);
 178
 179         sqlite3_stmt* selectMulti = getSelectMultiStmt(mask);
 180         Assert(selectMulti);
 181
 182         sqlite3_reset(selectMulti);
 183
 184         mask->bindSelectMulti(m_odb->db, selectMulti);
 185
 186         bool good = true;
 187         for(int i = 0; good; i++)
 188         {
 189                 good = doStatement(selectMulti);
 190                 if(good)
 191                         mask->parseRow(selectMulti);
 192         }
 193
 194         sqlite3_finalize(selectMulti);
 195 }
 196
 197 void SqliteMgr::commit(TableImpl* table)
 198 {
 199         Assert(table);
 200
 201         //StatementsPtr statements = getStatements(table);
 202         //statements->commit();
 203         table->modify();
 204         m_statements.clear();
 205 }
 206
 207
 208 bool SqliteMgr::doStatement(sqlite3_stmt* stmt)
 209 {
 210         Assert(stmt);
 211
 212         int rc = sqlite3_step(stmt);
 213
 214         switch(rc) {
 215         case SQLITE_DONE:
 216                 return false;
 217         case SQLITE_ROW:
 218                 return true;
 219         }
 220
 221         throw SqliteError(m_odb->db);
 222 }
 223
 224 StatementsPtr SqliteMgr::getStatements(TableImpl* table)
 225 {
 226         Assert(table);
 227
 228         StatementsPtr statements = m_statements[table];
 229         if(statements)
 230                 return statements;
 231
 232         statements = StatementsPtr(new Statements());
 233
 234         m_statements[table] = statements;
 235         return statements;
 236 }
 237
 238 StatementStringsPtr SqliteMgr::getStatementStrings(TableImpl* table)
 239 {
 240         Assert(table);
 241
 242         StatementStringsPtr statements = m_statementstrings[table];
 243         if(statements)
 244                 return statements;
 245
 246         statements = StatementStringsPtr(new StatementStrings());
 247
 248         m_statementstrings[table] = statements;
 249         return statements;
 250 }
 251
 252 sqlite3_stmt* SqliteMgr::getInsertStmt(TableImpl* table)
 253 {
 254         Assert(table);
 255
 256         StatementsPtr statements = getStatements(table);
 257         sqlite3_stmt* statement = statements->getInsert();
 258         if(statement)
 259                 return statement;
 260
 261         std::string sql;
 262
 263         StatementStringsPtr statementstrings = getStatementStrings(table);
 264         cstring statementstring = statementstrings->getInsert();
 265
 266         if(statementstring.size() != 0)
 267         {
 268                 sql = statementstring;
 269         }
 270         else
 271         {
 272                 sql.append("INSERT INTO ");
 273                 sql.append(table->tableName());
 274                 sql.append(" (");
 275                 for(TableMap::const_iterator it = table->keybegin(); it != table->keyend(); it++)
 276                 {
 277                            if(it != table->keybegin())
 278                                            sql.append(", ");
 279
 280                            sql.append(it->first);
 281                 }
 282                 sql.append(") VALUES(");
 283                 for(TableMap::const_iterator it = table->keybegin(); it != table->keyend(); it++)
 284                 {
 285                         if(it != table->keybegin())
 286                                 sql.append(", ");
 287
 288                         if(table->hasSingularPrimaryKey())
 289                                 sql.append("NULL");
 290                         else
 291                                 sql.append("?");
 292                 }
 293                 sql.append(");");
 294
 295                 statementstrings->setInsert(sql);
 296         }
 297
 298         int errorcode = sqlite3_prepare_v2(m_odb->db, sql.c_str(), (int)sql.size(), &statement, &m_leftover);
 299
 300         if(errorcode != SQLITE_OK)
 301                 throw SqliteError(m_odb->db);
 302
 303         Assert(m_leftover == NULL || strlen(m_leftover) == 0);
 304
 305         statements->setInsert(statement);
 306         return statement;
 307 }
 308
 309 sqlite3_stmt* SqliteMgr::getEraseStmt(TableImpl* table)
 310 {
 311         StatementsPtr statements = getStatements(table);
 312         sqlite3_stmt* statement = statements->getErase();
 313         if(statement)
 314                 return statement;
 315
 316         std::string sql;
 317
 318         StatementStringsPtr statementstrings = getStatementStrings(table);
 319         cstring statementstring = statementstrings->getErase();
 320
 321         if(statementstring.size() != 0)
 322         {
 323                 sql = statementstring;
 324         }
 325         else
 326         {
 327                 sql.append("DELETE ");
 328                 sql.append(table->tableName());
 329                 sql.append(" WHERE ");
 330                 for(TableMap::const_iterator it = table->keybegin(); it != table->keyend(); it++)
 331                 {
 332                         if(it != table->keybegin())
 333                                 sql.append(", ");
 334
 335                         sql.append(it->first);
 336                         sql.append("=?");
 337                 }
 338                 sql.append(";");
 339
 340                 statementstrings->setErase(sql);
 341         }
 342
 343         int errorcode = sqlite3_prepare_v2(m_odb->db, sql.c_str(), (int)sql.size(), &statement, &m_leftover);
 344
 345         if(errorcode != SQLITE_OK)
 346                 throw SqliteError(m_odb->db);
 347
 348         Assert(m_leftover == NULL || strlen(m_leftover) == 0);
 349
 350         statements->setErase(statement);
 351         return statement;
 352 }
 353
 354 sqlite3_stmt* SqliteMgr::getUpdateStmt(TableImpl* table)
 355 {
 356         StatementsPtr statements = getStatements(table);
 357         sqlite3_stmt* statement = statements->getUpdate();
 358         if(statement)
 359                 return statement;
 360
 361         std::string sql;
 362
 363         StatementStringsPtr statementstrings = getStatementStrings(table);
 364         cstring statementstring = statementstrings->getUpdate();
 365
 366         if(statementstring.size() != 0)
 367         {
 368                 sql = statementstring;
 369         }
 370         else
 371         {
 372                 sql.append("UPDATE ");
 373                 sql.append(table->tableName());
 374                 sql.append(" SET ");
 375                 for(FieldDefVector::const_iterator it = table->defbegin(); it != table->defend(); it++)
 376                 {
 377                         if(it != table->defbegin())
 378                                 sql.append(", ");
 379
 380                         sql.append((*it)->getName());
 381                         sql.append("=?");
 382                 }
 383                 sql.append(" WHERE ");
 384                 for(TableMap::const_iterator it = table->keybegin(); it != table->keyend(); it++)
 385                 {
 386                         if(it != table->keybegin())
 387                                 sql.append(", ");
 388
 389                         sql.append(it->first);
 390                         sql.append("=?");
 391                 }
 392                 sql.append(";");
 393
 394                 statementstrings->setUpdate(sql);
 395         }
 396
 397         int errorcode = sqlite3_prepare_v2(m_odb->db, sql.c_str(), (int)sql.size(), &statement, &m_leftover);
 398
 399         if(errorcode != SQLITE_OK)
 400                 throw SqliteError(m_odb->db);
 401
 402         Assert(m_leftover == NULL || strlen(m_leftover) == 0);
 403
 404         statements->setUpdate(statement);
 405         return statement;
 406 }
 407
 408 sqlite3_stmt* SqliteMgr::getSelectStmt(TableImpl* table)
 409 {
 410         StatementsPtr statements = getStatements(table);
 411         sqlite3_stmt* statement = statements->getSelect();
 412         if(statement)
 413                 return statement;
 414
 415         std::string sql;
 416
 417         StatementStringsPtr statementstrings = getStatementStrings(table);
 418         cstring statementstring = statementstrings->getSelect();
 419
 420         if(statementstring.size() != 0)
 421         {
 422                 sql = statementstring;
 423         }
 424         else
 425         {
 426                 sql.append("SELECT ");
 427                 for(FieldDefVector::const_iterator it = table->defbegin(); it != table->defend(); it++)
 428                 {
 429                         if(it != table->defbegin())
 430                                 sql.append(", ");
 431
 432                         sql.append((*it)->getName());
 433                 }
 434
 435                 /**
 436                  * Prevent queries in the form "SELECT  FROM ....", this is for tables that consist of only primary keys.
 437                  */
 438                 if(!table->defsize())
 439                         sql.append("*");
 440
 441                 sql.append(" FROM ");
 442                 sql.append(table->tableName());
 443                 sql.append(" WHERE ");
 444
 445                 for(TableMap::const_iterator it = table->keybegin(); it != table->keyend(); it++)
 446                 {
 447                         if(it != table->keybegin())
 448                                 sql.append(" AND ");
 449
 450                         sql.append(it->first);
 451                         sql.append("=?");
 452                 }
 453                 sql.append(";");
 454
 455                 statementstrings->setSelect(sql);
 456         }
 457
 458         int errorcode = sqlite3_prepare_v2(m_odb->db, sql.c_str(), (int)sql.size(), &statement, &m_leftover);
 459
 460         if(errorcode != SQLITE_OK)
 461                 throw SqliteError(m_odb->db);
 462
 463         Assert(m_leftover == NULL || strlen(m_leftover) == 0);
 464
 465         statements->setSelect(statement);
 466         return statement;
 467 }
 468
 469 sqlite3_stmt* SqliteMgr::getLookupStmt(TableImpl* table, FieldPtr field)
 470 {
 471         StatementsPtr statements = getStatements(table);
 472         sqlite3_stmt* statement = statements->getLookup(field);
 473         if(statement)
 474                 return statement;
 475
 476         std::string sql;
 477
 478         StatementStringsPtr statementstrings = getStatementStrings(table);
 479         cstring statementstring = statementstrings->getLookup(field);
 480
 481         if(statementstring.size() != 0)
 482         {
 483                 sql = statementstring;
 484         }
 485         else
 486         {
 487                 sql.append("SELECT ");
 488                 for(TableMap::const_iterator it = table->keybegin(); it != table->keyend(); it++)
 489                 {
 490                         if(it != table->keybegin())
 491                                 sql.append(", ");
 492
 493                         sql.append(it->first);
 494                 }
 495                 sql.append(" FROM ");
 496                 sql.append(table->tableName());
 497                 sql.append(" WHERE ");
 498                 sql.append(field->getName());
 499                 sql.append("=?");
 500                 sql.append(";");
 501
 502                 statementstrings->setLookup(field, sql);
 503         }
 504
 505         int errorcode = sqlite3_prepare_v2(m_odb->db, sql.c_str(), (int)sql.size(), &statement, &m_leftover);
 506
 507         if(errorcode != SQLITE_OK)
 508                 throw SqliteError(m_odb->db);
 509
 510         Assert(m_leftover == NULL || strlen(m_leftover) == 0);
 511
 512         statements->setLookup(field, statement);
 513         return statement;
 514 }
 515
 516 sqlite3_stmt* SqliteMgr::getForEachStmt(TableImpl* table)
 517 {
 518         StatementsPtr statements = getStatements(table);
 519         sqlite3_stmt* statement = statements->getForEach();
 520         if(statement)
 521                 return statement;
 522
 523         std::string sql;
 524
 525         StatementStringsPtr statementstrings = getStatementStrings(table);
 526         cstring statementstring = statementstrings->getForEach();
 527
 528         if(statementstring.size() != 0)
 529         {
 530                 sql = statementstring;
 531         }
 532         else
 533         {
 534                 sql.append("SELECT ");
 535                 bool comspace = false;
 536                 for(TableMap::const_iterator it = table->keybegin(); it != table->keyend(); it++)
 537                 {
 538                         if(comspace)
 539                                 sql.append(", ");
 540
 541                         sql.append(it->first);
 542                         comspace = true;
 543                 }
 544                 for(FieldDefVector::const_iterator it = table->defbegin(); it != table->defend(); it++)
 545                 {
 546                         if(comspace)
 547                                 sql.append(", ");
 548
 549                         sql.append((*it)->getName());
 550                         comspace = true;
 551                 }
 552
 553                 sql.append(" FROM ");
 554                 sql.append(table->tableName());
 555                 sql.append(";");
 556
 557                 statementstrings->setForEach(sql);
 558         }
 559
 560         int errorcode = sqlite3_prepare_v2(m_odb->db, sql.c_str(), (int)sql.size(), &statement, &m_leftover);
 561
 562         if(errorcode != SQLITE_OK)
 563                 throw SqliteError(m_odb->db);
 564
 565         Assert(m_leftover == NULL || strlen(m_leftover) == 0);
 566
 567         statements->setForEach(statement);
 568         return statement;
 569 }
 570
 571 sqlite3_stmt* SqliteMgr::getSelectMultiStmt(SelectionMask* mask)
 572 {
 573         Assert(mask);
 574
 575         std::string sql;
 576         sqlite3_stmt* statement;
 577
 578         TableImplPtr table = mask->getTable();
 579         Assert(table);
 580
 581         sql.append("SELECT ");
 582         bool comspace = false;
 583         for(TableMap::const_iterator it = table->keybegin(); it != table->keyend(); it++)
 584         {
 585                 if(comspace)
 586                         sql.append(", ");
 587
 588                 sql.append(it->first);
 589                 comspace = true;
 590         }
 591         for(FieldDefVector::const_iterator it = table->defbegin(); it != table->defend(); it++)
 592         {
 593                 if(comspace)
 594                         sql.append(", ");
 595
 596                 sql.append((*it)->getName());
 597                 comspace = true;
 598         }
 599
 600         /**
 601          * Prevent queries in the form "SELECT  FROM ....", this is for tables that consist of only primary keys.
 602          */
 603         if(!table->defsize())
 604                 sql.append("*");
 605
 606         sql.append(" FROM ");
 607         sql.append(table->tableName());
 608
 609         /**
 610          * Allow for queries that retreive data from foreigh tables "SELECT a, b, c, FROM Table INNER JOIN Other ON tableid = fkTable WHERE d = 5;"
 611          */
 612         for(Joins::const_iterator it = mask->joinsbegin(); it != mask->joinsend(); it++)
 613         {
 614                 JoinPtr join = *it;
 615                 sql.append(" INNER JOIN ");
 616                 sql.append(join->getJoinTable()->tableName());
 617                 sql.append(" ON ");
 618                 sql.append(join->getNativeKey()->getName());
 619                 sql.append(" = ");
 620                 sql.append(join->getForeignKey()->getName());
 621         }
 622
 623         /**
 624          * Prevent queries in the form "SELECT a, b, c FROM table WHERE ;", this is for the unmasked selection.
 625          */
 626         if(mask->size())
 627                 sql.append(" WHERE ");
 628
 629         for(Strings::const_iterator it = mask->begin(); it != mask->end(); it++)
 630         {
 631                 if(it != mask->begin())
 632                         sql.append(" AND ");
 633
 634                 sql.append(*it);
 635                 sql.append("=?");
 636         }
 637         sql.append(";");
 638
 639         printf("SQL: '%s'.\n", sql.c_str());
 640
 641         int errorcode = sqlite3_prepare_v2(m_odb->db, sql.c_str(), (int)sql.size(), &statement, &m_leftover);
 642
 643         if(errorcode != SQLITE_OK)
 644                 throw SqliteError(m_odb->db);
 645
 646         Assert(m_leftover == NULL || strlen(m_leftover) == 0);
 647
 648         return statement;
 649 }
 650
 651
 652 std::string SqliteMgr::tableQuery(TableDefPtr table) const
 653 {
 654         Assert(table);
 655
 656         std::string result;
 657         result.append("SELECT type FROM sqlite_master WHERE tbl_name='");
 658         result.append(table->tableName());
 659         result.append("' and sql='");
 660
 661         result.append(creationQuery(table, true));
 662
 663         result.append("';");
 664
 665         return result;
 666 }
 667
 668 /*
 669         "CREATE TABLE IF NOT EXISTS %s("
 670         "%s INTEGER PRIMARY KEY AUTOINCREMENT"
 671         ",versiontext TEXT"
 672         ",grantgroup INTEGER RESTRAINT grantgroup DEFAULT 1"
 673         ");",
 674 */
 675 std::string SqliteMgr::creationQuery(TableDefPtr table, bool verify) const
 676 {
 677         Assert(table);
 678
 679         std::string result;
 680
 681         if(verify)
 682                 result.append("CREATE TABLE ");
 683         else
 684                 result.append("CREATE TABLE IF NOT EXISTS ");
 685
 686         result.append(table->tableName());
 687         result.append("(");
 688
 689         bool comma = false;
 690
 691         for(TableMap::const_iterator it = table->keybegin(); it != table->keyend(); it++)
 692         {
 693                 if(table->hasSingularPrimaryKey() && it == table->keybegin())
 694                 {
 695                         result.append(it->first);
 696                         result.append(" INTEGER PRIMARY KEY AUTOINCREMENT, ");
 697                         continue;
 698                 }
 699
 700                 if(comma)
 701                         result.append(", ");
 702
 703                 result.append(it->first);
 704                 result.append(" INTEGER");
 705                 comma = true;
 706         }
 707
 708         for(FieldDefVector::const_iterator it = table->defbegin(); it != table->defend(); it++)
 709         {
 710                 if(comma)
 711                         result.append(", ");
 712
 713                 result.append(creationString(*it));
 714                 comma = true;
 715         }
 716
 717         if(!table->hasSingularPrimaryKey())
 718         {
 719                 result.append(", PRIMARY KEY(");
 720                 for(TableMap::const_iterator it = table->keybegin(); it != table->keyend(); it++)
 721                 {
 722                         if(it != table->keybegin())
 723                                 result.append(", ");
 724
 725                         result.append(it->first);
 726                 }
 727                 result.append(")");
 728         }
 729
 730         if(verify)
 731                 result.append(")");
 732         else
 733                 result.append(");");
 734
 735         return result;
 736 }
 737
 738 std::string SqliteMgr::creationString(FieldDefPtr field) const
 739 {
 740         Assert(field);
 741
 742         std::string result = field->getName();
 743
 744         if(field->isText())
 745                 result.append(" TEXT");
 746         else
 747                 result.append(" INTEGER");
 748
 749         if(field->getDefaultValue().size() != 0)
 750         {
 751                 result.append(" RESTRAINT ");
 752                 result.append(field->getName());
 753                 result.append(" DEFAULT ");
 754
 755                 if(field->isText())
 756                         result.append("'");
 757                 result.append(field->getDefaultValue());
 758                 if(field->isText())
 759                         result.append("'");
 760         }
 761
 762         return result;
 763 }