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