diff options
author | Christian Grothoff <christian@grothoff.org> | 2011-08-02 21:33:25 +0000 |
---|---|---|
committer | Christian Grothoff <christian@grothoff.org> | 2011-08-02 21:33:25 +0000 |
commit | bd0fcbc4b2502e0297db8aff345e74cc916afd12 (patch) | |
tree | e7c8698c7cc9fe2a0b1a1bc60e0c2d31aa5c9a8c /src/datastore/plugin_datastore_sqlite.c | |
parent | 1aacd1bf70f89ede0bd351f4f346a68dd3b6ad1d (diff) | |
download | gnunet-bd0fcbc4b2502e0297db8aff345e74cc916afd12.tar.gz gnunet-bd0fcbc4b2502e0297db8aff345e74cc916afd12.zip |
3 broken versions for sqlite
Diffstat (limited to 'src/datastore/plugin_datastore_sqlite.c')
-rw-r--r-- | src/datastore/plugin_datastore_sqlite.c | 75 |
1 files changed, 60 insertions, 15 deletions
diff --git a/src/datastore/plugin_datastore_sqlite.c b/src/datastore/plugin_datastore_sqlite.c index 3d2a1bb06..1e1f4d87b 100644 --- a/src/datastore/plugin_datastore_sqlite.c +++ b/src/datastore/plugin_datastore_sqlite.c | |||
@@ -167,19 +167,32 @@ static void | |||
167 | create_indices (sqlite3 * dbh) | 167 | create_indices (sqlite3 * dbh) |
168 | { | 168 | { |
169 | /* create indices */ | 169 | /* create indices */ |
170 | sqlite3_exec (dbh, | 170 | if ( (SQLITE_OK != |
171 | "CREATE INDEX idx_hash ON gn090 (hash)", NULL, NULL, NULL); | 171 | sqlite3_exec (dbh, |
172 | sqlite3_exec (dbh, | 172 | "CREATE INDEX IF NOT EXISTS idx_hash ON gn090 (hash)", NULL, NULL, NULL)) || |
173 | "CREATE INDEX idx_hash_vhash ON gn090 (hash,vhash)", NULL, | 173 | (SQLITE_OK != |
174 | NULL, NULL); | 174 | sqlite3_exec (dbh, |
175 | sqlite3_exec (dbh, "CREATE INDEX idx_expire_repl ON gn090 (expire ASC,repl DESC)", NULL, NULL, | 175 | "CREATE INDEX IF NOT EXISTS idx_hash_vhash ON gn090 (hash,vhash)", NULL, |
176 | NULL); | 176 | NULL, NULL)) || |
177 | sqlite3_exec (dbh, "CREATE INDEX idx_comb ON gn090 (anonLevel ASC,expire ASC,prio,type,hash)", | 177 | (SQLITE_OK != |
178 | NULL, NULL, NULL); | 178 | sqlite3_exec (dbh, "CREATE INDEX IF NOT EXISTS idx_expire_repl ON gn090 (expire ASC,repl DESC)", NULL, NULL, |
179 | sqlite3_exec (dbh, "CREATE INDEX idx_expire ON gn090 (expire)", | 179 | NULL)) || |
180 | NULL, NULL, NULL); | 180 | (SQLITE_OK != |
181 | sqlite3_exec (dbh, "CREATE INDEX idx_repl_rvalue ON gn090 (repl,rvalue)", | 181 | sqlite3_exec (dbh, "CREATE INDEX IF NOT EXISTS idx_comb ON gn090 (anonLevel ASC,expire ASC,prio,type,hash)", |
182 | NULL, NULL, NULL); | 182 | NULL, NULL, NULL)) || |
183 | (SQLITE_OK != | ||
184 | sqlite3_exec (dbh, "CREATE INDEX IF NOT EXISTS idx_expire ON gn090 (expire)", | ||
185 | NULL, NULL, NULL)) || | ||
186 | (SQLITE_OK != | ||
187 | sqlite3_exec (dbh, "CREATE INDEX IF NOT EXISTS idx_repl_rvalue ON gn090 (repl,rvalue)", | ||
188 | NULL, NULL, NULL)) || | ||
189 | (SQLITE_OK != | ||
190 | sqlite3_exec (dbh, "CREATE INDEX IF NOT EXISTS idx_repl ON gn090 (repl DESC)", | ||
191 | NULL, NULL, NULL)) ) | ||
192 | GNUNET_log_from (GNUNET_ERROR_TYPE_ERROR, | ||
193 | "sqlite", | ||
194 | "Failed to create indices: %s\n", | ||
195 | sqlite3_errmsg (dbh)); | ||
183 | } | 196 | } |
184 | 197 | ||
185 | 198 | ||
@@ -313,10 +326,42 @@ database_setup (const struct GNUNET_CONFIGURATION_Handle *cfg, | |||
313 | (sq_prepare (plugin->dbh, | 326 | (sq_prepare (plugin->dbh, |
314 | "UPDATE gn090 SET repl = MAX (0, repl - 1) WHERE _ROWID_ = ?", | 327 | "UPDATE gn090 SET repl = MAX (0, repl - 1) WHERE _ROWID_ = ?", |
315 | &plugin->updRepl) != SQLITE_OK) || | 328 | &plugin->updRepl) != SQLITE_OK) || |
329 | #if 1 | ||
330 | /* FIXME: this is the O(n) version */ | ||
331 | (sq_prepare (plugin->dbh, | ||
332 | "SELECT type,prio,anonLevel,expire,hash,value,_ROWID_ FROM gn090" | ||
333 | " ORDER BY repl DESC, Random() LIMIT 1", | ||
334 | &plugin->selRepl) != SQLITE_OK) || | ||
335 | #elif 0 | ||
336 | /* FIXME: this gives O(n) queries, presumably because the LEFT JOIN generates | ||
337 | a temporary table with all matching expressions before the ORDER BY and LIMIT | ||
338 | clauses are applied */ | ||
316 | (sq_prepare (plugin->dbh, | 339 | (sq_prepare (plugin->dbh, |
317 | "SELECT type,prio,anonLevel,expire,hash,value,_ROWID_ FROM gn090" | 340 | "SELECT type,prio,anonLevel,expire,hash,value,gn090._ROWID_ " |
318 | " ORDER BY repl DESC, Random() LIMIT 1", | 341 | "FROM (SELECT random() AS v) AS t1," |
342 | " (SELECT MAX(repl) AS m FROM gn090 INDEXED BY idx_repl) AS t2 " | ||
343 | " LEFT JOIN gn090 INDEXED BY idx_repl_rvalue" | ||
344 | " ON repl=t2.m AND" | ||
345 | " (rvalue>=t1.v OR" | ||
346 | " NOT EXISTS (SELECT 1 FROM gn090 INDEXED BY idx_repl_rvalue WHERE repl=t2.m AND rvalue>=t1.v))" | ||
347 | " ORDER BY rvalue ASC" | ||
348 | " LIMIT 1 ", | ||
319 | &plugin->selRepl) != SQLITE_OK) || | 349 | &plugin->selRepl) != SQLITE_OK) || |
350 | #else | ||
351 | /* NOTE: this fails, because sqlite doesn't link the t2.m (or the t1.v) to the temporary result from before | ||
352 | (parse error during preparation) */ | ||
353 | (sq_prepare (plugin->dbh, | ||
354 | "SELECT type,prio,anonLevel,expire,hash,value,_ROWID_ " | ||
355 | "FROM (SELECT random() AS v) AS t1," | ||
356 | " (SELECT MAX(repl) AS m FROM gn090 INDEXED BY idx_repl) AS t2, " | ||
357 | " (SELECT type,prio,anonLevel,expire,hash,value,_ROWID_ FROM gn090 INDEXED BY idx_repl_rvalue" | ||
358 | " WHERE repl=t2.m AND" /* "no such column: t2.m" */ | ||
359 | " (rvalue>=t1.v OR" | ||
360 | " NOT EXISTS (SELECT 1 FROM gn090 INDEXED BY idx_repl_rvalue WHERE repl=t2.m AND rvalue>=t1.v))" | ||
361 | " ORDER BY rvalue ASC" | ||
362 | " LIMIT 1)", | ||
363 | &plugin->selRepl) != SQLITE_OK) || | ||
364 | #endif | ||
320 | (sq_prepare (plugin->dbh, | 365 | (sq_prepare (plugin->dbh, |
321 | "SELECT type,prio,anonLevel,expire,hash,value,_ROWID_ FROM gn090 " | 366 | "SELECT type,prio,anonLevel,expire,hash,value,_ROWID_ FROM gn090 " |
322 | " WHERE NOT EXISTS (SELECT 1 FROM gn090 WHERE expire < ?1 LIMIT 1) OR expire < ?1 " | 367 | " WHERE NOT EXISTS (SELECT 1 FROM gn090 WHERE expire < ?1 LIMIT 1) OR expire < ?1 " |