diff options
author | Christian Grothoff <christian@grothoff.org> | 2018-04-30 16:22:47 +0200 |
---|---|---|
committer | Christian Grothoff <christian@grothoff.org> | 2018-04-30 16:22:47 +0200 |
commit | 4b5c6cceb0284774a161d426b606f40644abfb4c (patch) | |
tree | f2f63b8df7e6bb329ef25eca97e27d4deac55b69 /src/namestore/plugin_namestore_sqlite.c | |
parent | c66809401efc16f2606db7e2c7b7ca8def8be14e (diff) | |
download | gnunet-4b5c6cceb0284774a161d426b606f40644abfb4c.tar.gz gnunet-4b5c6cceb0284774a161d426b606f40644abfb4c.zip |
eliminate use of OFFSET in namestore DB queries
Diffstat (limited to 'src/namestore/plugin_namestore_sqlite.c')
-rw-r--r-- | src/namestore/plugin_namestore_sqlite.c | 141 |
1 files changed, 79 insertions, 62 deletions
diff --git a/src/namestore/plugin_namestore_sqlite.c b/src/namestore/plugin_namestore_sqlite.c index f905787b5..b54b4dba2 100644 --- a/src/namestore/plugin_namestore_sqlite.c +++ b/src/namestore/plugin_namestore_sqlite.c | |||
@@ -147,15 +147,13 @@ create_indices (sqlite3 * dbh) | |||
147 | /* create indices */ | 147 | /* create indices */ |
148 | if ( (SQLITE_OK != | 148 | if ( (SQLITE_OK != |
149 | sqlite3_exec (dbh, | 149 | sqlite3_exec (dbh, |
150 | "CREATE INDEX IF NOT EXISTS ir_pkey_reverse ON ns097records (zone_private_key,pkey)", | 150 | "CREATE INDEX IF NOT EXISTS ir_pkey_reverse " |
151 | "ON ns098records (zone_private_key,pkey)", | ||
151 | NULL, NULL, NULL)) || | 152 | NULL, NULL, NULL)) || |
152 | (SQLITE_OK != | 153 | (SQLITE_OK != |
153 | sqlite3_exec (dbh, | 154 | sqlite3_exec (dbh, |
154 | "CREATE INDEX IF NOT EXISTS ir_pkey_iter ON ns097records (zone_private_key,rvalue)", | 155 | "CREATE INDEX IF NOT EXISTS ir_pkey_iter " |
155 | NULL, NULL, NULL)) || | 156 | "ON ns098records (zone_private_key,uid)", |
156 | (SQLITE_OK != | ||
157 | sqlite3_exec (dbh, | ||
158 | "CREATE INDEX IF NOT EXISTS it_iter ON ns097records (rvalue)", | ||
159 | NULL, NULL, NULL)) ) | 157 | NULL, NULL, NULL)) ) |
160 | LOG (GNUNET_ERROR_TYPE_ERROR, | 158 | LOG (GNUNET_ERROR_TYPE_ERROR, |
161 | "Failed to create indices: %s\n", | 159 | "Failed to create indices: %s\n", |
@@ -260,22 +258,24 @@ database_setup (struct Plugin *plugin) | |||
260 | /* Create table */ | 258 | /* Create table */ |
261 | CHECK (SQLITE_OK == | 259 | CHECK (SQLITE_OK == |
262 | sq_prepare (plugin->dbh, | 260 | sq_prepare (plugin->dbh, |
263 | "SELECT 1 FROM sqlite_master WHERE tbl_name = 'ns097records'", | 261 | "SELECT 1 FROM sqlite_master WHERE tbl_name = 'ns098records'", |
264 | &stmt)); | 262 | &stmt)); |
265 | if ((sqlite3_step (stmt) == SQLITE_DONE) && | 263 | if ( (sqlite3_step (stmt) == SQLITE_DONE) && |
266 | (sqlite3_exec | 264 | (SQLITE_OK != |
267 | (plugin->dbh, | 265 | sqlite3_exec (plugin->dbh, |
268 | "CREATE TABLE ns097records (" | 266 | "CREATE TABLE ns098records (" |
269 | " zone_private_key BLOB NOT NULL," | 267 | " uid INTEGER PRIMARY KEY," |
270 | " pkey BLOB," | 268 | " zone_private_key BLOB NOT NULL," |
271 | " rvalue INT8 NOT NULL," | 269 | " pkey BLOB," |
272 | " record_count INT NOT NULL," | 270 | " rvalue INT8 NOT NULL," |
273 | " record_data BLOB NOT NULL," | 271 | " record_count INT NOT NULL," |
274 | " label TEXT NOT NULL" | 272 | " record_data BLOB NOT NULL," |
275 | ")", | 273 | " label TEXT NOT NULL" |
276 | NULL, NULL, NULL) != SQLITE_OK)) | 274 | ")", |
275 | NULL, NULL, NULL)) ) | ||
277 | { | 276 | { |
278 | LOG_SQLITE (plugin, GNUNET_ERROR_TYPE_ERROR, | 277 | LOG_SQLITE (plugin, |
278 | GNUNET_ERROR_TYPE_ERROR, | ||
279 | "sqlite3_exec"); | 279 | "sqlite3_exec"); |
280 | sqlite3_finalize (stmt); | 280 | sqlite3_finalize (stmt); |
281 | return GNUNET_SYSERR; | 281 | return GNUNET_SYSERR; |
@@ -286,33 +286,40 @@ database_setup (struct Plugin *plugin) | |||
286 | 286 | ||
287 | if ( (SQLITE_OK != | 287 | if ( (SQLITE_OK != |
288 | sq_prepare (plugin->dbh, | 288 | sq_prepare (plugin->dbh, |
289 | "INSERT INTO ns097records (zone_private_key, pkey, rvalue, record_count, record_data, label)" | 289 | "INSERT INTO ns098records (zone_private_key, pkey, rvalue, record_count, record_data, label)" |
290 | " VALUES (?, ?, ?, ?, ?, ?)", | 290 | " VALUES (?, ?, ?, ?, ?, ?)", |
291 | &plugin->store_records)) || | 291 | &plugin->store_records)) || |
292 | (SQLITE_OK != | 292 | (SQLITE_OK != |
293 | sq_prepare (plugin->dbh, | 293 | sq_prepare (plugin->dbh, |
294 | "DELETE FROM ns097records WHERE zone_private_key=? AND label=?", | 294 | "DELETE FROM ns098records WHERE zone_private_key=? AND label=?", |
295 | &plugin->delete_records)) || | 295 | &plugin->delete_records)) || |
296 | (SQLITE_OK != | 296 | (SQLITE_OK != |
297 | sq_prepare (plugin->dbh, | 297 | sq_prepare (plugin->dbh, |
298 | "SELECT record_count,record_data,label" | 298 | "SELECT uid,record_count,record_data,label" |
299 | " FROM ns097records WHERE zone_private_key=? AND pkey=?", | 299 | " FROM ns098records" |
300 | " WHERE zone_private_key=? AND pkey=?", | ||
300 | &plugin->zone_to_name)) || | 301 | &plugin->zone_to_name)) || |
301 | (SQLITE_OK != | 302 | (SQLITE_OK != |
302 | sq_prepare (plugin->dbh, | 303 | sq_prepare (plugin->dbh, |
303 | "SELECT record_count,record_data,label" | 304 | "SELECT uid,record_count,record_data,label" |
304 | " FROM ns097records WHERE zone_private_key=?" | 305 | " FROM ns098records" |
305 | " ORDER BY rvalue LIMIT ? OFFSET ?", | 306 | " WHERE zone_private_key=? AND _rowid_ >= ?" |
307 | " ORDER BY _rowid_ ASC" | ||
308 | " LIMIT ?", | ||
306 | &plugin->iterate_zone)) || | 309 | &plugin->iterate_zone)) || |
307 | (SQLITE_OK != | 310 | (SQLITE_OK != |
308 | sq_prepare (plugin->dbh, | 311 | sq_prepare (plugin->dbh, |
309 | "SELECT record_count,record_data,label,zone_private_key" | 312 | "SELECT uid,record_count,record_data,label,zone_private_key" |
310 | " FROM ns097records ORDER BY rvalue LIMIT ? OFFSET ?", | 313 | " FROM ns098records" |
314 | " WHERE _rowid_ >= ?" | ||
315 | " ORDER BY _rowid_ ASC" | ||
316 | " LIMIT ?", | ||
311 | &plugin->iterate_all_zones)) || | 317 | &plugin->iterate_all_zones)) || |
312 | (SQLITE_OK != | 318 | (SQLITE_OK != |
313 | sq_prepare (plugin->dbh, | 319 | sq_prepare (plugin->dbh, |
314 | "SELECT record_count,record_data,label,zone_private_key" | 320 | "SELECT uid,record_count,record_data,label,zone_private_key" |
315 | " FROM ns097records WHERE zone_private_key=? AND label=?", | 321 | " FROM ns098records" |
322 | " WHERE zone_private_key=? AND label=?", | ||
316 | &plugin->lookup_label)) | 323 | &plugin->lookup_label)) |
317 | ) | 324 | ) |
318 | { | 325 | { |
@@ -405,10 +412,11 @@ namestore_sqlite_store_records (void *cls, | |||
405 | struct GNUNET_CRYPTO_EcdsaPublicKey pkey; | 412 | struct GNUNET_CRYPTO_EcdsaPublicKey pkey; |
406 | uint64_t rvalue; | 413 | uint64_t rvalue; |
407 | size_t data_size; | 414 | size_t data_size; |
408 | unsigned int i; | ||
409 | 415 | ||
410 | memset (&pkey, 0, sizeof (pkey)); | 416 | memset (&pkey, |
411 | for (i=0;i<rd_count;i++) | 417 | 0, |
418 | sizeof (pkey)); | ||
419 | for (unsigned int i=0;i<rd_count;i++) | ||
412 | if (GNUNET_GNSRECORD_TYPE_PKEY == rd[i].record_type) | 420 | if (GNUNET_GNSRECORD_TYPE_PKEY == rd[i].record_type) |
413 | { | 421 | { |
414 | GNUNET_break (sizeof (struct GNUNET_CRYPTO_EcdsaPublicKey) == | 422 | GNUNET_break (sizeof (struct GNUNET_CRYPTO_EcdsaPublicKey) == |
@@ -539,28 +547,50 @@ get_records_and_call_iterator (struct Plugin *plugin, | |||
539 | GNUNET_NAMESTORE_RecordIterator iter, | 547 | GNUNET_NAMESTORE_RecordIterator iter, |
540 | void *iter_cls) | 548 | void *iter_cls) |
541 | { | 549 | { |
542 | uint32_t record_count; | ||
543 | size_t data_size; | ||
544 | void *data; | ||
545 | char *label; | ||
546 | struct GNUNET_CRYPTO_EcdsaPrivateKey zk; | ||
547 | int ret; | 550 | int ret; |
548 | int sret; | 551 | int sret; |
549 | 552 | ||
550 | ret = GNUNET_OK; | 553 | ret = GNUNET_OK; |
551 | for (uint64_t i = 0;i<limit ; i++) | 554 | for (uint64_t i = 0;i<limit ; i++) |
552 | { | 555 | { |
553 | if (SQLITE_ROW == (sret = sqlite3_step (stmt))) | 556 | sret = sqlite3_step (stmt); |
557 | |||
558 | if (SQLITE_DONE == sret) | ||
559 | { | ||
560 | GNUNET_log (GNUNET_ERROR_TYPE_DEBUG, | ||
561 | "Iteration done (no results)\n"); | ||
562 | ret = GNUNET_NO; | ||
563 | break; | ||
564 | } | ||
565 | if (SQLITE_ROW != sret) | ||
554 | { | 566 | { |
567 | LOG_SQLITE (plugin, | ||
568 | GNUNET_ERROR_TYPE_ERROR, | ||
569 | "sqlite_step"); | ||
570 | ret = GNUNET_SYSERR; | ||
571 | break; | ||
572 | } | ||
573 | |||
574 | { | ||
575 | uint64_t seq; | ||
576 | uint32_t record_count; | ||
577 | size_t data_size; | ||
578 | void *data; | ||
579 | char *label; | ||
580 | struct GNUNET_CRYPTO_EcdsaPrivateKey zk; | ||
555 | struct GNUNET_SQ_ResultSpec rs[] = { | 581 | struct GNUNET_SQ_ResultSpec rs[] = { |
582 | GNUNET_SQ_result_spec_uint64 (&seq), | ||
556 | GNUNET_SQ_result_spec_uint32 (&record_count), | 583 | GNUNET_SQ_result_spec_uint32 (&record_count), |
557 | GNUNET_SQ_result_spec_variable_size (&data, &data_size), | 584 | GNUNET_SQ_result_spec_variable_size (&data, |
585 | &data_size), | ||
558 | GNUNET_SQ_result_spec_string (&label), | 586 | GNUNET_SQ_result_spec_string (&label), |
559 | GNUNET_SQ_result_spec_end | 587 | GNUNET_SQ_result_spec_end |
560 | }; | 588 | }; |
561 | struct GNUNET_SQ_ResultSpec rsx[] = { | 589 | struct GNUNET_SQ_ResultSpec rsx[] = { |
590 | GNUNET_SQ_result_spec_uint64 (&seq), | ||
562 | GNUNET_SQ_result_spec_uint32 (&record_count), | 591 | GNUNET_SQ_result_spec_uint32 (&record_count), |
563 | GNUNET_SQ_result_spec_variable_size (&data, &data_size), | 592 | GNUNET_SQ_result_spec_variable_size (&data, |
593 | &data_size), | ||
564 | GNUNET_SQ_result_spec_string (&label), | 594 | GNUNET_SQ_result_spec_string (&label), |
565 | GNUNET_SQ_result_spec_auto_from_type (&zk), | 595 | GNUNET_SQ_result_spec_auto_from_type (&zk), |
566 | GNUNET_SQ_result_spec_end | 596 | GNUNET_SQ_result_spec_end |
@@ -604,6 +634,7 @@ get_records_and_call_iterator (struct Plugin *plugin, | |||
604 | { | 634 | { |
605 | if (NULL != iter) | 635 | if (NULL != iter) |
606 | iter (iter_cls, | 636 | iter (iter_cls, |
637 | seq + 1, | ||
607 | zone_key, | 638 | zone_key, |
608 | label, | 639 | label, |
609 | record_count, | 640 | record_count, |
@@ -612,21 +643,6 @@ get_records_and_call_iterator (struct Plugin *plugin, | |||
612 | } | 643 | } |
613 | GNUNET_SQ_cleanup_result (rs); | 644 | GNUNET_SQ_cleanup_result (rs); |
614 | } | 645 | } |
615 | else | ||
616 | { | ||
617 | if (SQLITE_DONE != sret) | ||
618 | { | ||
619 | LOG_SQLITE (plugin, | ||
620 | GNUNET_ERROR_TYPE_ERROR, | ||
621 | "sqlite_step"); | ||
622 | ret = GNUNET_SYSERR; | ||
623 | } | ||
624 | else | ||
625 | { | ||
626 | ret = GNUNET_NO; | ||
627 | } | ||
628 | break; | ||
629 | } | ||
630 | } | 646 | } |
631 | GNUNET_SQ_reset (plugin->dbh, | 647 | GNUNET_SQ_reset (plugin->dbh, |
632 | stmt); | 648 | stmt); |
@@ -685,7 +701,7 @@ namestore_sqlite_lookup_records (void *cls, | |||
685 | * | 701 | * |
686 | * @param cls closure (internal context for the plugin) | 702 | * @param cls closure (internal context for the plugin) |
687 | * @param zone hash of public key of the zone, NULL to iterate over all zones | 703 | * @param zone hash of public key of the zone, NULL to iterate over all zones |
688 | * @param offset offset in the list of all matching records | 704 | * @param serial serial number to exclude in the list of all matching records |
689 | * @param limit maximum number of results to return | 705 | * @param limit maximum number of results to return |
690 | * @param iter function to call with the result | 706 | * @param iter function to call with the result |
691 | * @param iter_cls closure for @a iter | 707 | * @param iter_cls closure for @a iter |
@@ -694,7 +710,7 @@ namestore_sqlite_lookup_records (void *cls, | |||
694 | static int | 710 | static int |
695 | namestore_sqlite_iterate_records (void *cls, | 711 | namestore_sqlite_iterate_records (void *cls, |
696 | const struct GNUNET_CRYPTO_EcdsaPrivateKey *zone, | 712 | const struct GNUNET_CRYPTO_EcdsaPrivateKey *zone, |
697 | uint64_t offset, | 713 | uint64_t serial, |
698 | uint64_t limit, | 714 | uint64_t limit, |
699 | GNUNET_NAMESTORE_RecordIterator iter, | 715 | GNUNET_NAMESTORE_RecordIterator iter, |
700 | void *iter_cls) | 716 | void *iter_cls) |
@@ -706,8 +722,8 @@ namestore_sqlite_iterate_records (void *cls, | |||
706 | if (NULL == zone) | 722 | if (NULL == zone) |
707 | { | 723 | { |
708 | struct GNUNET_SQ_QueryParam params[] = { | 724 | struct GNUNET_SQ_QueryParam params[] = { |
725 | GNUNET_SQ_query_param_uint64 (&serial), | ||
709 | GNUNET_SQ_query_param_uint64 (&limit), | 726 | GNUNET_SQ_query_param_uint64 (&limit), |
710 | GNUNET_SQ_query_param_uint64 (&offset), | ||
711 | GNUNET_SQ_query_param_end | 727 | GNUNET_SQ_query_param_end |
712 | }; | 728 | }; |
713 | 729 | ||
@@ -719,8 +735,8 @@ namestore_sqlite_iterate_records (void *cls, | |||
719 | { | 735 | { |
720 | struct GNUNET_SQ_QueryParam params[] = { | 736 | struct GNUNET_SQ_QueryParam params[] = { |
721 | GNUNET_SQ_query_param_auto_from_type (zone), | 737 | GNUNET_SQ_query_param_auto_from_type (zone), |
738 | GNUNET_SQ_query_param_uint64 (&serial), | ||
722 | GNUNET_SQ_query_param_uint64 (&limit), | 739 | GNUNET_SQ_query_param_uint64 (&limit), |
723 | GNUNET_SQ_query_param_uint64 (&offset), | ||
724 | GNUNET_SQ_query_param_end | 740 | GNUNET_SQ_query_param_end |
725 | }; | 741 | }; |
726 | 742 | ||
@@ -761,7 +777,8 @@ static int | |||
761 | namestore_sqlite_zone_to_name (void *cls, | 777 | namestore_sqlite_zone_to_name (void *cls, |
762 | const struct GNUNET_CRYPTO_EcdsaPrivateKey *zone, | 778 | const struct GNUNET_CRYPTO_EcdsaPrivateKey *zone, |
763 | const struct GNUNET_CRYPTO_EcdsaPublicKey *value_zone, | 779 | const struct GNUNET_CRYPTO_EcdsaPublicKey *value_zone, |
764 | GNUNET_NAMESTORE_RecordIterator iter, void *iter_cls) | 780 | GNUNET_NAMESTORE_RecordIterator iter, |
781 | void *iter_cls) | ||
765 | { | 782 | { |
766 | struct Plugin *plugin = cls; | 783 | struct Plugin *plugin = cls; |
767 | struct GNUNET_SQ_QueryParam params[] = { | 784 | struct GNUNET_SQ_QueryParam params[] = { |