C++ SqliteDatabase::query方法代码示例

string Database_MorphGnt::get_item (const char * item, int rowid)
{
  // The $rowid refers to the main table.
  // Update it so it refers to the sub table.
  SqliteDatabase sql = SqliteDatabase (filename ());
  sql.add ("SELECT");
  sql.add (item);
  sql.add ("FROM morphgnt WHERE rowid =");
  sql.add (rowid);
  sql.add (";");
  vector <string> result = sql.query () [item];
  rowid = 0;
  if (!result.empty ()) rowid = convert_to_int (result [0]);
  // Retrieve the requested value from the sub table.
  sql.clear ();
  sql.add ("SELECT");
  sql.add (item);
  sql.add ("FROM");
  sql.add (item);
  sql.add ("WHERE rowid =");
  sql.add (rowid);
  sql.add (";");
  result = sql.query () [item];
  if (!result.empty ()) return result [0];
  // Not found.
  return "";
}

int Database_MorphGnt::get_id (const char * table_row, string item)
{
  SqliteDatabase sql = SqliteDatabase (filename ());
  // Two iterations to be sure a rowid can be returned.
  for (unsigned int i = 0; i < 2; i++) {
    // Check on the rowid and return it if it's there.
    sql.clear ();
    sql.add ("SELECT rowid FROM");
    sql.add (table_row);
    sql.add ("WHERE");
    sql.add (table_row);
    sql.add ("=");
    sql.add (item);
    sql.add (";");
    vector <string> result = sql.query () ["rowid"];
    if (!result.empty ()) return convert_to_int (result [0]);
    // The rowid was not found: Insert the word into the table.
    // The rowid will now be found during the second iteration.
    sql.clear ();
    sql.add ("INSERT INTO");
    sql.add (table_row);
    sql.add ("VALUES (");
    sql.add (item);
    sql.add (");");
    sql.execute ();
  }
  return 0;
}

// Returns the username that matches the cookie sent by the browser.
// Once a day, $daily will be set true.
string Database_Login::getUsername (string cookie, bool & daily)
{
  SqliteDatabase sql (database ());
  sql.add ("SELECT rowid, timestamp, username FROM logins WHERE cookie =");
  sql.add (cookie);
  sql.add (";");
  map <string, vector <string> > result = sql.query ();
  if (result.empty()) return "";
  string username = result ["username"][0];
  int stamp = convert_to_int (result ["timestamp"] [0]);
  if (stamp != timestamp ()) {
    // Touch the timestamp. This occurs once a day.
    int rowid = convert_to_int (result ["rowid"] [0]);
    sql.clear ();
    sql.add ("UPDATE logins SET timestamp =");
    sql.add (timestamp ());
    sql.add ("WHERE rowid =");
    sql.add (rowid);
    sql.execute ();
    daily = true;
  } else {
    daily = false;
  }
  return username;
}

// Returns the username that matches the remote IP $address and the browser's user $agent,
// and the other fingerprints from the user.
string Database_Login::getUsername (string address, string agent, string fingerprint)
{
  address = md5 (address);
  agent = md5 (agent);
  fingerprint = md5 (fingerprint);
  SqliteDatabase sql (database ());
  sql.add ("SELECT rowid, timestamp, username FROM logins WHERE address =");
  sql.add (address);
  sql.add ("AND agent =");
  sql.add (agent);
  sql.add ("AND fingerprint =");
  sql.add (fingerprint);
  sql.add (";");
  map <string, vector <string> > result = sql.query ();
  if (result.empty()) return "";
  string username = result ["username"][0];
  int stamp = convert_to_int (result ["timestamp"] [0]);
  if (stamp != timestamp ()) {
    // Touch the timestamp. This occurs once a day.
    int rowid = convert_to_int (result ["rowid"] [0]);
    sql.clear ();
    sql.add ("UPDATE logins SET timestamp =");
    sql.add (timestamp ());
    sql.add ("WHERE rowid =");
    sql.add (rowid);
    sql.execute ();
  }
  return username;
}

int Database_Privileges::getBibleBookCount ()
{
  SqliteDatabase sql (database ());
  sql.add ("SELECT count(*) FROM bibles;");
  vector <string> result = sql.query () ["count(*)"];
  if (result.empty ()) return 0;
  return convert_to_int (result [0]);
}

// Returns true if the $id exists
bool Database_Confirm::IDExists (unsigned int id)
{
  SqliteDatabase sql (filename ());
  sql.add ("SELECT id FROM confirm WHERE id =");
  sql.add (id);
  sql.add (";");
  vector <string> ids = sql.query () ["id"];
  return !ids.empty ();
}

string Database_HebrewLexicon::getbdb (string id)
{
  SqliteDatabase sql = SqliteDatabase (filename ());
  sql.add ("SELECT definition FROM bdb WHERE id =");
  sql.add (id);
  sql.add (";");
  vector <string> result = sql.query () ["definition"];
  if (!result.empty ()) return result [0];
  return "";
}

void Database_Privileges::getBible (string username, string bible, bool & read, bool & write)
{
  SqliteDatabase sql (database ());
  sql.add ("SELECT write FROM bibles WHERE username =");
  sql.add (username);
  sql.add ("AND bible =");
  sql.add (bible);
  sql.add (";");
  vector <string> result = sql.query () ["write"];
  read = (!result.empty());
  sql.clear ();
  sql.add ("SELECT write FROM bibles WHERE username =");
  sql.add (username);
  sql.add ("AND bible =");
  sql.add (bible);
  sql.add ("AND write;");
  result = sql.query () ["write"];
  write = (!result.empty());
}

string Database_HebrewLexicon::getstrong (string strong)
{
  SqliteDatabase sql = SqliteDatabase (filename ());
  sql.add ("SELECT definition FROM strong WHERE strong =");
  sql.add (strong);
  sql.add (";");
  vector <string> result = sql.query () ["definition"];
  if (!result.empty ()) return result [0];
  return "";
}

string Database_HebrewLexicon::getpos (string code)
{
  SqliteDatabase sql = SqliteDatabase (filename ());
  sql.add ("SELECT name FROM pos WHERE code =");
  sql.add (code);
  sql.add (";");
  vector <string> result = sql.query () ["name"];
  if (!result.empty ()) return result [0];
  return "";
}

string Database_HebrewLexicon::getmap (string id)
{
  SqliteDatabase sql = SqliteDatabase (filename ());
  sql.add ("SELECT bdb FROM map WHERE id =");
  sql.add (id);
  sql.add (";");
  vector <string> result = sql.query () ["bdb"];
  if (!result.empty ()) return result [0];
  return "";
}

// Returns whether the device, that matches the cookie it sent, is touch-enabled.
bool Database_Login::getTouchEnabled (string cookie)
{
  SqliteDatabase sql (database ());
  sql.add ("SELECT touch FROM logins WHERE cookie =");
  sql.add (cookie);
  sql.add (";");
  vector <string> result = sql.query () ["touch"];
  if (!result.empty()) return convert_to_bool (result [0]);
  return false;
}

string Database_HebrewLexicon::getaug (string aug)
{
  SqliteDatabase sql = SqliteDatabase (filename ());
  sql.add ("SELECT target FROM aug WHERE aug =");
  sql.add (aug);
  sql.add (";");
  vector <string> result = sql.query () ["target"];
  if (!result.empty ()) return result [0];
  return "";
}

// Returns the email's body for $id.
string Database_Confirm::getBody (unsigned int id)
{
  SqliteDatabase sql (filename ());
  sql.add ("SELECT body FROM confirm WHERE id =");
  sql.add (id);
  sql.add (";");
  vector <string> result = sql.query () ["body"];
  if (!result.empty ()) return result [0];
  return "";
}

string Database_Privileges::save (string username)
{
  SqliteDatabase sql (database ());
  
  vector <string> lines;

  lines.push_back (bibles_start ());
  sql.add ("SELECT bible, book, write FROM bibles WHERE username =");
  sql.add (username);
  sql.add (";");
  map <string, vector <string> > result = sql.query ();
  vector <string> bible = result ["bible"];
  vector <string> book =  result ["book"];
  vector <string> write = result ["write"];
  for (size_t i = 0; i < bible.size (); i++) {
    lines.push_back (bible [i]);
    lines.push_back (book [i]);
    // It could have just stored 0 or 1 for the boolean values.
    // But if that were done, then there would be no change in the length of the file
    // when changing only boolean values.
    // And then the client would not re-download that file.
    // To use "on" and "off", that solves the issue.
    bool b = convert_to_bool (write[i]);
    if (b) lines.push_back (on ());
    else lines.push_back (off ());
  }
  lines.push_back (bibles_end ());
  
  lines.push_back (features_start ());
  sql.clear ();
  sql.add ("SELECT feature FROM features WHERE username =");
  sql.add (username);
  sql.add (";");
  result = sql.query ();
  vector <string> feature = result ["feature"];
  for (size_t i = 0; i < feature.size (); i++) {
    lines.push_back (feature [i]);
  }
  lines.push_back (features_end ());
  
  return filter_string_implode (lines, "\n");
}