[Bug 35345] New: Pass custom SQL to rebuild_elasticsearch.pl
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=35345 Bug ID: 35345 Summary: Pass custom SQL to rebuild_elasticsearch.pl Change sponsored?: --- Product: Koha Version: master Hardware: All OS: All Status: NEW Severity: new feature Priority: P5 - low Component: Searching - Elasticsearch Assignee: koha-bugs@lists.koha-community.org Reporter: domm@plix.at 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). -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=35345 --- Comment #1 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- 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 :) -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=35345 --- Comment #2 from Thomas Klausner <domm@plix.at> --- 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? -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=35345 --- Comment #3 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- git grep "\-\-where" touch_all_items.pl update_patrons_category.pl delete_items.pl ... :) -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=35345 --- Comment #4 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- rebuild_zebra.pl as well! -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=35345 --- Comment #5 from Thomas Klausner <domm@plix.at> --- (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) -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=35345 --- Comment #6 from Thomas Klausner <domm@plix.at> --- Created attachment 159044 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=159044&action=edit 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 -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=35345 Thomas Klausner <domm@plix.at> changed: What |Removed |Added ---------------------------------------------------------------------------- Change sponsored?|--- |Sponsored Patch complexity|--- |Trivial patch -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=35345 Thomas Klausner <domm@plix.at> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|NEW |Needs Signoff -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=35345 --- Comment #7 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- (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. -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=35345 David Nind <david@davidnind.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Needs Signoff |Signed Off -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=35345 David Nind <david@davidnind.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #159044|0 |1 is obsolete| | --- Comment #8 from David Nind <david@davidnind.com> --- Created attachment 162015 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=162015&action=edit 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> -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=35345 Emily Lamancusa <emily.lamancusa@montgomerycountymd.gov> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |emily.lamancusa@montgomeryc | |ountymd.gov Status|Signed Off |Failed QA Assignee|koha-bugs@lists.koha-commun |domm@plix.at |ity.org | --- Comment #9 from Emily Lamancusa <emily.lamancusa@montgomerycountymd.gov> --- 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. -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=35345 Thomas Klausner <domm@plix.at> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #162015|0 |1 is obsolete| | --- Comment #10 from Thomas Klausner <domm@plix.at> --- Created attachment 164118 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=164118&action=edit 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 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=35345 --- Comment #11 from Thomas Klausner <domm@plix.at> --- 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..) -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=35345 Thomas Klausner <domm@plix.at> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Failed QA |Signed Off -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=35345 Nick Clemens <nick@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #164118|0 |1 is obsolete| | --- Comment #12 from Nick Clemens <nick@bywatersolutions.com> --- Created attachment 164157 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=164157&action=edit 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> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=35345 Nick Clemens <nick@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |nick@bywatersolutions.com See Also| |https://bugs.koha-community | |.org/bugzilla3/show_bug.cgi | |?id=36467 Status|Signed Off |Passed QA --- Comment #13 from 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 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=35345 Katrin Fischer <katrin.fischer@bsz-bw.de> changed: What |Removed |Added ---------------------------------------------------------------------------- Severity|new feature |enhancement -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=35345 Katrin Fischer <katrin.fischer@bsz-bw.de> changed: What |Removed |Added ---------------------------------------------------------------------------- Summary|Pass custom SQL to |Pass custom SQL to |rebuild_elasticsearch.pl |rebuild_elasticsearch.pl to | |determine which records to | |index --- Comment #14 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- An SQL example for documentation might be a nice addition :) -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=35345 Katrin Fischer <katrin.fischer@bsz-bw.de> changed: What |Removed |Added ---------------------------------------------------------------------------- Version(s)| |24.05.00 released in| | Status|Passed QA |Pushed to main -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=35345 --- Comment #15 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- Pushed for 24.05! Well done everyone, thank you! -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=35345 Fridolin Somers <fridolin.somers@biblibre.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |fridolin.somers@biblibre.co | |m --- Comment #16 from Fridolin Somers <fridolin.somers@biblibre.com> --- A close sibling of Bug 29921 ;) -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=35345 Fridolin Somers <fridolin.somers@biblibre.com> changed: What |Removed |Added ---------------------------------------------------------------------------- See Also| |https://bugs.koha-community | |.org/bugzilla3/show_bug.cgi | |?id=33243 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=35345 Fridolin Somers <fridolin.somers@biblibre.com> changed: What |Removed |Added ---------------------------------------------------------------------------- See Also| |https://bugs.koha-community | |.org/bugzilla3/show_bug.cgi | |?id=29921 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=35345 Fridolin Somers <fridolin.somers@biblibre.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Version(s)|24.05.00 |24.05.00,23.11.06 released in| | Status|Pushed to main |Pushed to stable --- Comment #17 from Fridolin Somers <fridolin.somers@biblibre.com> --- Small useful enhancement, Pushed to 23.11.x for 23.11.06 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=35345 Fridolin Somers <fridolin.somers@biblibre.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Keywords| |release-notes-needed -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=35345 Katrin Fischer <katrin.fischer@bsz-bw.de> changed: What |Removed |Added ---------------------------------------------------------------------------- Keywords|release-notes-needed | Text to go in the| |This adds a `--where` release notes| |parameter to the | |`rebuild_elasticsearch.pl` | |script that allows to | |flexibly select the records | |for reindexing with SQL. | |Examples would be the | |authority type or ranges | |and lists of biblionumbers | |and authids. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=35345 Lucas Gass <lucas@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Pushed to stable |RESOLVED Resolution|--- |FIXED CC| |lucas@bywatersolutions.com --- Comment #18 from Lucas Gass <lucas@bywatersolutions.com> --- Enhancement will not be backported to 23.05.x -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=35345 Lisette Scheer <lisette@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |koha@trust-box.at --- Comment #19 from Lisette Scheer <lisette@bywatersolutions.com> --- *** Bug 33243 has been marked as a duplicate of this bug. *** -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=35345 Mengu Yazicioglu <mengu@devinim.com.tr> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |mengu@devinim.com.tr -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=35345 Thomas Klausner <domm@plix.at> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |julian.maurice@biblibre.com --- Comment #20 from Thomas Klausner <domm@plix.at> --- *** Bug 29921 has been marked as a duplicate of this bug. *** -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=35345 Jonathan Druart <jonathan.druart@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Blocks| |40964 Referenced Bugs: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=40964 [Bug 40964] koha-elasticsearch is missing --where option -- You are receiving this mail because: You are watching all bug changes.
participants (1)
-
bugzilla-daemon＠bugs.koha-community.org