Sometimes we need to only re-index a subset of our bibliographic data or authorities. Currently this is only possible by enumerating all id (-bn or -ai), which does not work well when indexing eg 100.000 items of a 2.000.000 DB. Re-indexing everything is also overkill. I propose adding an `--sql` Flag to misc/search_tools/rebuild_elasticsearch.pl which can take arbitrary SQL (that of course has to match the respective tables) and adds it as an additional param to the resultset to index: Example: * Only index auths of type Geograpy: misc/search_tools/rebuild_elasticsearch.pl -a -v --sql "authtypecode = 'GEOGR_NAME'" * Only index books with id 100-150: misc/search_tools/rebuild_elasticsearch.pl -b -v --sql "biblionumber between 100 and 150" In this first version only columns available in the primary tables (biblio, auth_header) are available. Later we could add command line options for joining other tables (eg biblio_metadata).
Hi Thomas, I like this idea. Just a note: we have similar in other jobs and I believe it's usually named --where. So my suggestion would be to use that as a parameter name for some consistency :)
I don't care about --sql or --where, and -w is also still not used, so I'll use that (and modify my patch, which I haven't commited yet) What other tools are using `--where`? Or are you talking about non-Koha tools?
git grep "\-\-where" touch_all_items.pl update_patrons_category.pl delete_items.pl ... :)
rebuild_zebra.pl as well!
(In reply to Katrin Fischer from comment #4) > rebuild_zebra.pl as well! rebuild_zebra.pl already has a --where flag :-) (but it's not using DBIx::Class/Koha::BiblioUtils etc, but directly building a query and executing it via DBI)
Created attachment 159044 [details] [review] Bug 35345: Add --where option to rebuild_elasticsearch.pl Sometimes we need to only re-index a subset of our bibliographic data or authorities. Currently this is only possible by enumerating all id (-bn or -ai), which does not work well when indexing eg 100.000 items of a 2.000.000 DB. Re-indexing everything is also overkill. This patch adds an `--where` flag to misc/search_tools/rebuild_elasticsearch.pl which can take arbitrary SQL (that of course has to match the respective tables) and adds it as an additional param to the resultset to index To test, start koha-testing-docker with ElasticSearch enabled, for example via `ktd --es7 up Before applying the patch, rebuild_elasticsearch will index all data: Biblios: $ misc/search_tools/rebuild_elasticsearch.pl -b -v [12387] Checking state of biblios index [12387] Indexing biblios [12387] Committing final records... [12387] Total 435 records indexed (there might be a waring regarding a broken biblio, which can be ignored) Auth: $ misc/search_tools/rebuild_elasticsearch.pl -a -v [12546] Checking state of authorities index [12546] Indexing authorities [12546] 1000 records processed [12546] Committing final records... [12546] Total 1706 records indexed Now apply the patch Biblio, limit by range of biblioid: $ misc/search_tools/rebuild_elasticsearch.pl -b -v --where "biblionumber between 100 and 150" [12765] Checking state of biblios index [12765] Indexing biblios [12765] Committing final records... [12765] Total 50 records indexed Note that only 50 records where indexed (instead of the whole set of 435 records) Auth, limit by authtypecode: $ misc/search_tools/rebuild_elasticsearch.pl -a -v --where "authtypecode = 'GEOGR_NAME'" [12848] Checking state of authorities index [12848] Indexing authorities [12848] Committing final records... [12848] Total 142 records indexed Again, only 142 have been indexed. Note that this patch also includes perltidied versions of the affected files (because the qa script complained) Sponsored-by: Steiermärkische Landesbibliothek
(In reply to Thomas Klausner from comment #5) > (In reply to Katrin Fischer from comment #4) > > rebuild_zebra.pl as well! > > rebuild_zebra.pl already has a --where flag :-) > > (but it's not using DBIx::Class/Koha::BiblioUtils etc, but directly building > a query and executing it via DBI) Yes, you asked about examples and when checking I realized that the Zebra equivalent already had such a parameter as well. So having it for Elastic too makes even more sense.
Created attachment 162015 [details] [review] Bug 35345: Add --where option to rebuild_elasticsearch.pl Sometimes we need to only re-index a subset of our bibliographic data or authorities. Currently this is only possible by enumerating all id (-bn or -ai), which does not work well when indexing eg 100.000 items of a 2.000.000 DB. Re-indexing everything is also overkill. This patch adds an `--where` flag to misc/search_tools/rebuild_elasticsearch.pl which can take arbitrary SQL (that of course has to match the respective tables) and adds it as an additional param to the resultset to index To test, start koha-testing-docker with ElasticSearch enabled, for example via `ktd --es7 up Before applying the patch, rebuild_elasticsearch will index all data: Biblios: $ misc/search_tools/rebuild_elasticsearch.pl -b -v [12387] Checking state of biblios index [12387] Indexing biblios [12387] Committing final records... [12387] Total 435 records indexed (there might be a waring regarding a broken biblio, which can be ignored) Auth: $ misc/search_tools/rebuild_elasticsearch.pl -a -v [12546] Checking state of authorities index [12546] Indexing authorities [12546] 1000 records processed [12546] Committing final records... [12546] Total 1706 records indexed Now apply the patch Biblio, limit by range of biblioid: $ misc/search_tools/rebuild_elasticsearch.pl -b -v --where "biblionumber between 100 and 150" [12765] Checking state of biblios index [12765] Indexing biblios [12765] Committing final records... [12765] Total 50 records indexed Note that only 50 records where indexed (instead of the whole set of 435 records) Auth, limit by authtypecode: $ misc/search_tools/rebuild_elasticsearch.pl -a -v --where "authtypecode = 'GEOGR_NAME'" [12848] Checking state of authorities index [12848] Indexing authorities [12848] Committing final records... [12848] Total 142 records indexed Again, only 142 have been indexed. Note that this patch also includes perltidied versions of the affected files (because the qa script complained) Sponsored-by: Steiermärkische Landesbibliothek Signed-off-by: David Nind <david@davidnind.com>
This is a great idea! However, the patch contains many Perltidy/formatting changes that are unrelated to the enhancement. Please separate out your code changes into one patch, and the Perltidy/formatting changes into a second patch.
Created attachment 164118 [details] [review] Bug 35345: Add --where option to rebuild_elasticsearch.pl Sometimes we need to only re-index a subset of our bibliographic data or authorities. Currently this is only possible by enumerating all id (-bn or -ai), which does not work well when indexing eg 100.000 items of a 2.000.000 DB. Re-indexing everything is also overkill. This patch adds an `--where` flag to misc/search_tools/rebuild_elasticsearch.pl which can take arbitrary SQL (that of course has to match the respective tables) and adds it as an additional param to the resultset to index To test, start koha-testing-docker with ElasticSearch enabled, for example via `ktd --es7 up Before applying the patch, rebuild_elasticsearch will index all data: Biblios: $ misc/search_tools/rebuild_elasticsearch.pl -b -v [12387] Checking state of biblios index [12387] Indexing biblios [12387] Committing final records... [12387] Total 435 records indexed (there might be a waring regarding a broken biblio, which can be ignored) Auth: $ misc/search_tools/rebuild_elasticsearch.pl -a -v [12546] Checking state of authorities index [12546] Indexing authorities [12546] 1000 records processed [12546] Committing final records... [12546] Total 1706 records indexed Now apply the patch Biblio, limit by range of biblioid: $ misc/search_tools/rebuild_elasticsearch.pl -b -v --where "biblionumber between 100 and 150" [12765] Checking state of biblios index [12765] Indexing biblios [12765] Committing final records... [12765] Total 50 records indexed Note that only 50 records where indexed (instead of the whole set of 435 records) Auth, limit by authtypecode: $ misc/search_tools/rebuild_elasticsearch.pl -a -v --where "authtypecode = 'GEOGR_NAME'" [12848] Checking state of authorities index [12848] Indexing authorities [12848] Committing final records... [12848] Total 142 records indexed Again, only 142 have been indexed. Sponsored-by: Steiermärkische Landesbibliothek Sponsored-by: HKS3 / koha-support.eu
I've now cleaned up the patch to only include the actual changes. Not sure if I should now also change the status (and if yes, to what..)
Created attachment 164157 [details] [review] Bug 35345: Add --where option to rebuild_elasticsearch.pl Sometimes we need to only re-index a subset of our bibliographic data or authorities. Currently this is only possible by enumerating all id (-bn or -ai), which does not work well when indexing eg 100.000 items of a 2.000.000 DB. Re-indexing everything is also overkill. This patch adds an `--where` flag to misc/search_tools/rebuild_elasticsearch.pl which can take arbitrary SQL (that of course has to match the respective tables) and adds it as an additional param to the resultset to index To test, start koha-testing-docker with ElasticSearch enabled, for example via `ktd --es7 up Before applying the patch, rebuild_elasticsearch will index all data: Biblios: $ misc/search_tools/rebuild_elasticsearch.pl -b -v [12387] Checking state of biblios index [12387] Indexing biblios [12387] Committing final records... [12387] Total 435 records indexed (there might be a waring regarding a broken biblio, which can be ignored) Auth: $ misc/search_tools/rebuild_elasticsearch.pl -a -v [12546] Checking state of authorities index [12546] Indexing authorities [12546] 1000 records processed [12546] Committing final records... [12546] Total 1706 records indexed Now apply the patch Biblio, limit by range of biblioid: $ misc/search_tools/rebuild_elasticsearch.pl -b -v --where "biblionumber between 100 and 150" [12765] Checking state of biblios index [12765] Indexing biblios [12765] Committing final records... [12765] Total 50 records indexed Note that only 50 records where indexed (instead of the whole set of 435 records) Auth, limit by authtypecode: $ misc/search_tools/rebuild_elasticsearch.pl -a -v --where "authtypecode = 'GEOGR_NAME'" [12848] Checking state of authorities index [12848] Indexing authorities [12848] Committing final records... [12848] Total 142 records indexed Again, only 142 have been indexed. Sponsored-by: Steiermärkische Landesbibliothek Sponsored-by: HKS3 / koha-support.eu Signed-off-by: David Nind <david@davidnind.com> Signed-off-by: Nick Clemens <nick@bywatersolutions.com>
Restored David's SO line, the changes seemed mostly cosmetic between the patches. Works, is straightforward. I would have asked for test coverage, but there are currently no tests for MetadataIterator or the iterator functions, so letting this one move forward, but filing a bug for those tests. RM call on whether they are a blocker for this
An SQL example for documentation might be a nice addition :)
Pushed for 24.05! Well done everyone, thank you!
A close sibling of Bug 29921 ;)
Small useful enhancement, Pushed to 23.11.x for 23.11.06
Enhancement will not be backported to 23.05.x
*** Bug 33243 has been marked as a duplicate of this bug. ***