Anope IRC Services  Version 2.0
m_sqlite.cpp
Go to the documentation of this file.
1 /* RequiredLibraries: sqlite3 */
2 /* RequiredWindowsLibraries: sqlite3 */
3 
4 #include "module.h"
5 #include "modules/sql.h"
6 #include <sqlite3.h>
7 
8 using namespace SQL;
9 
10 /* SQLite3 API, based from InspiRCd */
11 
14 class SQLiteResult : public Result
15 {
16  public:
17  SQLiteResult(unsigned int i, const Query &q, const Anope::string &fq) : Result(i, q, fq)
18  {
19  }
20 
21  SQLiteResult(const Query &q, const Anope::string &fq, const Anope::string &err) : Result(0, q, fq, err)
22  {
23  }
24 
25  void AddRow(const std::map<Anope::string, Anope::string> &data)
26  {
27  this->entries.push_back(data);
28  }
29 };
30 
33 class SQLiteService : public Provider
34 {
35  std::map<Anope::string, std::set<Anope::string> > active_schema;
36 
38 
39  sqlite3 *sql;
40 
41  Anope::string Escape(const Anope::string &query);
42 
43  public:
44  SQLiteService(Module *o, const Anope::string &n, const Anope::string &d);
45 
46  ~SQLiteService();
47 
48  void Run(Interface *i, const Query &query) anope_override;
49 
50  Result RunQuery(const Query &query);
51 
52  std::vector<Query> CreateTable(const Anope::string &table, const Data &data) anope_override;
53 
54  Query BuildInsert(const Anope::string &table, unsigned int id, Data &data);
55 
56  Query GetTables(const Anope::string &prefix);
57 
58  Anope::string BuildQuery(const Query &q);
59 
60  Anope::string FromUnixtime(time_t);
61 };
62 
63 class ModuleSQLite : public Module
64 {
65  /* SQL connections */
66  std::map<Anope::string, SQLiteService *> SQLiteServices;
67  public:
68  ModuleSQLite(const Anope::string &modname, const Anope::string &creator) : Module(modname, creator, EXTRA | VENDOR)
69  {
70  }
71 
73  {
74  for (std::map<Anope::string, SQLiteService *>::iterator it = this->SQLiteServices.begin(); it != this->SQLiteServices.end(); ++it)
75  delete it->second;
76  SQLiteServices.clear();
77  }
78 
80  {
81  Configuration::Block *config = conf->GetModule(this);
82 
83  for (std::map<Anope::string, SQLiteService *>::iterator it = this->SQLiteServices.begin(); it != this->SQLiteServices.end();)
84  {
85  const Anope::string &cname = it->first;
86  SQLiteService *s = it->second;
87  int i, num;
88  ++it;
89 
90  for (i = 0, num = config->CountBlock("sqlite"); i < num; ++i)
91  if (config->GetBlock("sqlite", i)->Get<const Anope::string>("name", "sqlite/main") == cname)
92  break;
93 
94  if (i == num)
95  {
96  Log(LOG_NORMAL, "sqlite") << "SQLite: Removing server connection " << cname;
97 
98  delete s;
99  this->SQLiteServices.erase(cname);
100  }
101  }
102 
103  for (int i = 0; i < config->CountBlock("sqlite"); ++i)
104  {
105  Configuration::Block *block = config->GetBlock("sqlite", i);
106  Anope::string connname = block->Get<const Anope::string>("name", "sqlite/main");
107 
108  if (this->SQLiteServices.find(connname) == this->SQLiteServices.end())
109  {
110  Anope::string database = Anope::DataDir + "/" + block->Get<const Anope::string>("database", "anope");
111 
112  try
113  {
114  SQLiteService *ss = new SQLiteService(this, connname, database);
115  this->SQLiteServices[connname] = ss;
116 
117  Log(LOG_NORMAL, "sqlite") << "SQLite: Successfully added database " << database;
118  }
119  catch (const SQL::Exception &ex)
120  {
121  Log(LOG_NORMAL, "sqlite") << "SQLite: " << ex.GetReason();
122  }
123  }
124  }
125  }
126 };
127 
129 : Provider(o, n), database(d), sql(NULL)
130 {
131  int db = sqlite3_open_v2(database.c_str(), &this->sql, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, 0);
132  if (db != SQLITE_OK)
133  throw SQL::Exception("Unable to open SQLite database " + database + ": " + sqlite3_errmsg(this->sql));
134 }
135 
137 {
138  sqlite3_interrupt(this->sql);
139  sqlite3_close(this->sql);
140 }
141 
142 void SQLiteService::Run(Interface *i, const Query &query)
143 {
144  Result res = this->RunQuery(query);
145  if (!res.GetError().empty())
146  i->OnError(res);
147  else
148  i->OnResult(res);
149 }
150 
152 {
153  Anope::string real_query = this->BuildQuery(query);
154  sqlite3_stmt *stmt;
155  int err = sqlite3_prepare_v2(this->sql, real_query.c_str(), real_query.length(), &stmt, NULL);
156  if (err != SQLITE_OK)
157  return SQLiteResult(query, real_query, sqlite3_errmsg(this->sql));
158 
159  std::vector<Anope::string> columns;
160  int cols = sqlite3_column_count(stmt);
161  columns.resize(cols);
162  for (int i = 0; i < cols; ++i)
163  columns[i] = sqlite3_column_name(stmt, i);
164 
165  SQLiteResult result(0, query, real_query);
166 
167  while ((err = sqlite3_step(stmt)) == SQLITE_ROW)
168  {
169  std::map<Anope::string, Anope::string> items;
170  for (int i = 0; i < cols; ++i)
171  {
172  const char *data = reinterpret_cast<const char *>(sqlite3_column_text(stmt, i));
173  if (data && *data)
174  items[columns[i]] = data;
175  }
176  result.AddRow(items);
177  }
178 
179  result.id = sqlite3_last_insert_rowid(this->sql);
180 
181  sqlite3_finalize(stmt);
182 
183  if (err != SQLITE_DONE)
184  return SQLiteResult(query, real_query, sqlite3_errmsg(this->sql));
185 
186  return result;
187 }
188 
189 std::vector<Query> SQLiteService::CreateTable(const Anope::string &table, const Data &data)
190 {
191  std::vector<Query> queries;
192  std::set<Anope::string> &known_cols = this->active_schema[table];
193 
194  if (known_cols.empty())
195  {
196  Log(LOG_DEBUG) << "m_sqlite: Fetching columns for " << table;
197 
198  Result columns = this->RunQuery("PRAGMA table_info(" + table + ")");
199  for (int i = 0; i < columns.Rows(); ++i)
200  {
201  const Anope::string &column = columns.Get(i, "name");
202 
203  Log(LOG_DEBUG) << "m_sqlite: Column #" << i << " for " << table << ": " << column;
204  known_cols.insert(column);
205  }
206  }
207 
208  if (known_cols.empty())
209  {
210  Anope::string query_text = "CREATE TABLE `" + table + "` (`id` INTEGER PRIMARY KEY, `timestamp` timestamp DEFAULT CURRENT_TIMESTAMP";
211 
212  for (Data::Map::const_iterator it = data.data.begin(), it_end = data.data.end(); it != it_end; ++it)
213  {
214  known_cols.insert(it->first);
215 
216  query_text += ", `" + it->first + "` ";
217  if (data.GetType(it->first) == Serialize::Data::DT_INT)
218  query_text += "int(11)";
219  else
220  query_text += "text";
221  }
222 
223  query_text += ")";
224 
225  queries.push_back(query_text);
226 
227  query_text = "CREATE UNIQUE INDEX `" + table + "_id_idx` ON `" + table + "` (`id`)";
228  queries.push_back(query_text);
229 
230  query_text = "CREATE INDEX `" + table + "_timestamp_idx` ON `" + table + "` (`timestamp`)";
231  queries.push_back(query_text);
232 
233  query_text = "CREATE TRIGGER `" + table + "_trigger` AFTER UPDATE ON `" + table + "` FOR EACH ROW BEGIN UPDATE `" + table + "` SET `timestamp` = CURRENT_TIMESTAMP WHERE `id` = `old.id`; end;";
234  queries.push_back(query_text);
235  }
236  else
237  for (Data::Map::const_iterator it = data.data.begin(), it_end = data.data.end(); it != it_end; ++it)
238  {
239  if (known_cols.count(it->first) > 0)
240  continue;
241 
242  known_cols.insert(it->first);
243 
244  Anope::string query_text = "ALTER TABLE `" + table + "` ADD `" + it->first + "` ";
245  if (data.GetType(it->first) == Serialize::Data::DT_INT)
246  query_text += "int(11)";
247  else
248  query_text += "text";
249 
250  queries.push_back(query_text);
251  }
252 
253  return queries;
254 }
255 
256 Query SQLiteService::BuildInsert(const Anope::string &table, unsigned int id, Data &data)
257 {
258  /* Empty columns not present in the data set */
259  const std::set<Anope::string> &known_cols = this->active_schema[table];
260  for (std::set<Anope::string>::iterator it = known_cols.begin(), it_end = known_cols.end(); it != it_end; ++it)
261  if (*it != "id" && *it != "timestamp" && data.data.count(*it) == 0)
262  data[*it] << "";
263 
264  Anope::string query_text = "REPLACE INTO `" + table + "` (";
265  if (id > 0)
266  query_text += "`id`,";
267  for (Data::Map::const_iterator it = data.data.begin(), it_end = data.data.end(); it != it_end; ++it)
268  query_text += "`" + it->first + "`,";
269  query_text.erase(query_text.length() - 1);
270  query_text += ") VALUES (";
271  if (id > 0)
272  query_text += stringify(id) + ",";
273  for (Data::Map::const_iterator it = data.data.begin(), it_end = data.data.end(); it != it_end; ++it)
274  query_text += "@" + it->first + "@,";
275  query_text.erase(query_text.length() - 1);
276  query_text += ")";
277 
278  Query query(query_text);
279  for (Data::Map::const_iterator it = data.data.begin(), it_end = data.data.end(); it != it_end; ++it)
280  {
281  Anope::string buf;
282  *it->second >> buf;
283  query.SetValue(it->first, buf);
284  }
285 
286  return query;
287 }
288 
290 {
291  return Query("SELECT name FROM sqlite_master WHERE type='table' AND name LIKE '" + prefix + "%';");
292 }
293 
295 {
296  char *e = sqlite3_mprintf("%q", query.c_str());
297  Anope::string buffer = e;
298  sqlite3_free(e);
299  return buffer;
300 }
301 
303 {
304  Anope::string real_query = q.query;
305 
306  for (std::map<Anope::string, QueryData>::const_iterator it = q.parameters.begin(), it_end = q.parameters.end(); it != it_end; ++it)
307  real_query = real_query.replace_all_cs("@" + it->first + "@", (it->second.escape ? ("'" + this->Escape(it->second.data) + "'") : it->second.data));
308 
309  return real_query;
310 }
311 
313 {
314  return "datetime('" + stringify(t) + "', 'unixepoch')";
315 }
316 
318 
Anope::string FromUnixtime(time_t)
Definition: m_sqlite.cpp:312
static Anope::map< ExtensibleItem< CSMiscData > * > items
Definition: cs_set_misc.cpp:18
int CountBlock(const Anope::string &name)
Definition: config.cpp:35
Map data
Definition: sql.h:15
virtual void OnError(const Result &r)=0
Result RunQuery(const Query &query)
Definition: m_sqlite.cpp:151
Anope::string Escape(const Anope::string &src)
Definition: httpd.h:206
Anope::string query
Definition: sql.h:98
void push_back(char c)
Definition: anope.h:142
std::map< Anope::string, QueryData > parameters
Definition: sql.h:99
Anope::string BuildQuery(const Query &q)
Definition: m_sqlite.cpp:302
Anope::string database
Definition: m_sqlite.cpp:37
const Anope::string & GetError() const
Definition: sql.h:153
SQLiteResult(const Query &q, const Anope::string &fq, const Anope::string &err)
Definition: m_sqlite.cpp:21
void Run(Interface *i, const Query &query) anope_override
Definition: m_sqlite.cpp:142
void AddRow(const std::map< Anope::string, Anope::string > &data)
Definition: m_sqlite.cpp:25
void SetValue(const Anope::string &key, const T &value, bool escape=true)
Definition: sql.h:121
Query GetTables(const Anope::string &prefix)
Definition: m_sqlite.cpp:289
iterator erase(const iterator &i)
Definition: anope.h:155
size_type length() const
Definition: anope.h:131
Block * GetBlock(const Anope::string &name, int num=0)
Definition: config.cpp:43
Definition: sql.h:96
virtual void OnResult(const Result &r)=0
unsigned int id
Definition: sql.h:143
string replace_all_cs(const string &_orig, const string &_repl) const
Definition: anope.h:229
database_map database
Definition: cs_seen.cpp:24
SQLiteService(Module *o, const Anope::string &n, const Anope::string &d)
Definition: m_sqlite.cpp:128
#define anope_override
Definition: services.h:56
Definition: sql.h:11
bool empty() const
Definition: anope.h:126
ModuleSQLite(const Anope::string &modname, const Anope::string &creator)
Definition: m_sqlite.cpp:68
Type GetType(const Anope::string &key) const anope_override
Definition: sql.h:68
std::map< Anope::string, SQLiteService * > SQLiteServices
Definition: m_sqlite.cpp:66
#define MODULE_INIT(x)
Definition: modules.h:45
Definition: sql.h:8
SQLiteResult(unsigned int i, const Query &q, const Anope::string &fq)
Definition: m_sqlite.cpp:17
Anope::string stringify(const T &x)
Definition: anope.h:710
CoreExport Anope::string DataDir
Definition: init.cpp:33
std::vector< Query > CreateTable(const Anope::string &table, const Data &data) anope_override
Definition: m_sqlite.cpp:189
std::map< Anope::string, std::set< Anope::string > > active_schema
Definition: m_sqlite.cpp:35
sqlite3 * sql
Definition: m_sqlite.cpp:39
virtual const Anope::string & GetReason() const
Definition: anope.h:672
Query BuildInsert(const Anope::string &table, unsigned int id, Data &data)
Definition: m_sqlite.cpp:256
const char * c_str() const
Definition: anope.h:117
Definition: logger.h:53
T Get(const Anope::string &tag)
Definition: config.h:44
void OnReload(Configuration::Conf *conf) anope_override
Definition: m_sqlite.cpp:79
int Rows() const
Definition: sql.h:155
Anope::string Escape(const Anope::string &query)
Definition: m_sqlite.cpp:294
Definition: modules.h:163
const Anope::string Get(size_t index, const Anope::string &col) const
Definition: sql.h:169