https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=34064 David Cook <dcook@prosentient.com.au> 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=34064 David Cook <dcook@prosentient.com.au> changed: What |Removed |Added ---------------------------------------------------------------------------- See Also| |https://bugs.koha-community | |.org/bugzilla3/show_bug.cgi | |?id=30486 -- 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=34064 David Cook <dcook@prosentient.com.au> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #152479|0 |1 is obsolete| | --- Comment #2 from David Cook <dcook@prosentient.com.au> --- Created attachment 152480 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=152480&action=edit Bug 34064: Add an audit script to compare database with kohastructure.sql This script can take a connected database handle and compare that database schema against kohastructure.sql to see what changes the database would need in order to match kohastructure.sql NOTE: It uses SQL::Translation::Diff, which is installed with DBIx::Class. WARNING: The diff doesn't seem to compare comments, so that difference won't appear in the output. If we wanted, we could easily enhance the audit_database.pl script to also compare comments. WARNING: The output is a proposed series of SQL commands. While they are useful to review, they won't always duplicate the changes done by updatedatabase.pl, so it's important to carefully analyze the output. The key purpose of this audit script is to just highlight the differences between the two. Test plan: 0. Apply patch 1. vi ./installer/data/mysql/kohastructure.sql 2. Comment out some columns, change NULL status, or whatever you like 3. perl misc/maintenance/audit_database.pl \ --filename /kohadevbox/koha/installer/data/mysql/kohastructure.sql 4. Note that the output includes SQL commands to change the database to match the new kohastructure.sql 5a. Try using koha-foreach and note that the database name appears above the database comparison 5b. koha-foreach "perl misc/maintenance/audit_database.pl \ --filename /kohadevbox/koha/installer/data/mysql/kohastructure.sql" -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=34064 Emmi Takkinen <emmi.takkinen@koha-suomi.fi> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #152480|0 |1 is obsolete| | CC| |emmi.takkinen@koha-suomi.fi --- Comment #3 from Emmi Takkinen <emmi.takkinen@koha-suomi.fi> --- Created attachment 152481 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=152481&action=edit Bug 34064: Add an audit script to compare database with kohastructure.sql This script can take a connected database handle and compare that database schema against kohastructure.sql to see what changes the database would need in order to match kohastructure.sql NOTE: It uses SQL::Translation::Diff, which is installed with DBIx::Class. WARNING: The diff doesn't seem to compare comments, so that difference won't appear in the output. If we wanted, we could easily enhance the audit_database.pl script to also compare comments. WARNING: The output is a proposed series of SQL commands. While they are useful to review, they won't always duplicate the changes done by updatedatabase.pl, so it's important to carefully analyze the output. The key purpose of this audit script is to just highlight the differences between the two. Test plan: 0. Apply patch 1. vi ./installer/data/mysql/kohastructure.sql 2. Comment out some columns, change NULL status, or whatever you like 3. perl misc/maintenance/audit_database.pl \ --filename /kohadevbox/koha/installer/data/mysql/kohastructure.sql 4. Note that the output includes SQL commands to change the database to match the new kohastructure.sql 5a. Try using koha-foreach and note that the database name appears above the database comparison 5b. koha-foreach "perl misc/maintenance/audit_database.pl \ --filename /kohadevbox/koha/installer/data/mysql/kohastructure.sql" Signed-off-by: Emmi Takkinen <emmi.takkinen@koha-suomi.fi> -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=34064 --- Comment #4 from David Cook <dcook@prosentient.com.au> --- Thanks, Emmi, although I'm going to make a little tweak. I've been running this on a backup server, and finding the output too unwieldy without the tweak. It's amazing looking at real world databases though. My first database has about 1400 lines of suggested changes. Lots of the changes are for little things like synchronizing column collations, but it just goes to show how different updatedatabase.pl is from kohastructure.sql... -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=34064 --- Comment #5 from David Cook <dcook@prosentient.com.au> --- Created attachment 152482 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=152482&action=edit Bug 34064: Remove no_batch_alters option This change removes the no_batch_alters option. When working with real world databases, you really want to use batch alters to group together changes. Otherwise, the output becomes too overwhelming/unwieldy. On real world databases, I'm noticing big discrepancies, although I think most of the time this is a difference in the collation at the column level. -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=34064 --- Comment #7 from Jonathan Druart <jonathan.druart+koha@gmail.com> --- Why not using koha-dump? With misc/maintenance/sync_db_comments.pl you could then compare with kohastructure.sql I think. -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=34064 --- Comment #9 from Emmi Takkinen <emmi.takkinen@koha-suomi.fi> --- (In reply to David Cook from comment #4)

Thanks, Emmi, although I'm going to make a little tweak.

I've been running this on a backup server, and finding the output too unwieldy without the tweak.

It's amazing looking at real world databases though. My first database has about 1400 lines of suggested changes.

Lots of the changes are for little things like synchronizing column collations, but it just goes to show how different updatedatabase.pl is from kohastructure.sql...

Oh boy. And here I was thinking that my database has a lot of changes to consider (maybe around 200 lines). Although, mine isn't as cleaned up as our production databases. Would be interesting to see how those still differ from kohastructure.sql. -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=34064 --- Comment #10 from Marcel de Rooy <m.de.rooy@rijksmuseum.nl> --- (In reply to Jonathan Druart from comment #7)

Why not using koha-dump? With misc/maintenance/sync_db_comments.pl you could then compare with kohastructure.sql I think.

Yes, we are already having code to do this job or similar code that we could merge instead of adding more? -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=34064 --- Comment #12 from David Cook <dcook@prosentient.com.au> --- (In reply to Jonathan Druart from comment #7)

Why not using koha-dump? With misc/maintenance/sync_db_comments.pl you could then compare with kohastructure.sql I think.

You mean comparing a schema dump and kohastructure.sql? That could work, but it puts a lot of work on the human brain I think. -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=34064 --- Comment #14 from David Cook <dcook@prosentient.com.au> --- (In reply to Emmi Takkinen from comment #9)

Oh boy. And here I was thinking that my database has a lot of changes to consider (maybe around 200 lines). Although, mine isn't as cleaned up as our production databases. Would be interesting to see how those still differ from kohastructure.sql.

Right? I'm finding the discrepancies very interesting. -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=34064 David Cook <dcook@prosentient.com.au> changed: What |Removed |Added ---------------------------------------------------------------------------- See Also| |https://bugs.koha-community | |.org/bugzilla3/show_bug.cgi | |?id=34065 --- Comment #16 from David Cook <dcook@prosentient.com.au> --- I'm a bit disappointed that people aren't as excited about this script as I am, but that's OK. I wrote it to help me fix my local databases, and it's already showing me lots of differences. I can keep this as a local tool, and use it raise Bugzilla issues. I just thought the community would find it useful, since this is an area that we've really struggled with together for many years. -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=34064 Owen Leonard <oleonard@myacpl.org> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #152481|0 |1 is obsolete| | Attachment #152482|0 |1 is obsolete| | --- Comment #17 from Owen Leonard <oleonard@myacpl.org> --- Created attachment 152575 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=152575&action=edit Bug 34064: Add an audit script to compare database with kohastructure.sql This script can take a connected database handle and compare that database schema against kohastructure.sql to see what changes the database would need in order to match kohastructure.sql NOTE: It uses SQL::Translation::Diff, which is installed with DBIx::Class. WARNING: The diff doesn't seem to compare comments, so that difference won't appear in the output. If we wanted, we could easily enhance the audit_database.pl script to also compare comments. WARNING: The output is a proposed series of SQL commands. While they are useful to review, they won't always duplicate the changes done by updatedatabase.pl, so it's important to carefully analyze the output. The key purpose of this audit script is to just highlight the differences between the two. Test plan: 0. Apply patch 1. vi ./installer/data/mysql/kohastructure.sql 2. Comment out some columns, change NULL status, or whatever you like 3. perl misc/maintenance/audit_database.pl \ --filename /kohadevbox/koha/installer/data/mysql/kohastructure.sql 4. Note that the output includes SQL commands to change the database to match the new kohastructure.sql 5a. Try using koha-foreach and note that the database name appears above the database comparison 5b. koha-foreach "perl misc/maintenance/audit_database.pl \ --filename /kohadevbox/koha/installer/data/mysql/kohastructure.sql" Signed-off-by: Emmi Takkinen <emmi.takkinen@koha-suomi.fi> Signed-off-by: Owen Leonard <oleonard@myacpl.org> -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=34064 --- Comment #19 from Owen Leonard <oleonard@myacpl.org> --- A note about my testing: I did not perform the test using koha-foreach, only on my single test database. The database I use for testing is an older one from our production system and it has been updated again and again. I needed a tool today to figure out whether my problems importing patrons were Koha's fault or my database's, and this tool identified a number of problems which I was able to resolve. Now I can test patron imports again. Note: I changed one spelling error identified by the QA tool during signoff: unecessarily -> unnecessarily -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=34064 Martin Renvoize <martin.renvoize@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #152575|0 |1 is obsolete| | --- Comment #21 from Martin Renvoize <martin.renvoize@ptfs-europe.com> --- Created attachment 152739 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=152739&action=edit Bug 34064: Add an audit script to compare database with kohastructure.sql This script can take a connected database handle and compare that database schema against kohastructure.sql to see what changes the database would need in order to match kohastructure.sql NOTE: It uses SQL::Translation::Diff, which is installed with DBIx::Class. WARNING: The diff doesn't seem to compare comments, so that difference won't appear in the output. If we wanted, we could easily enhance the audit_database.pl script to also compare comments. WARNING: The output is a proposed series of SQL commands. While they are useful to review, they won't always duplicate the changes done by updatedatabase.pl, so it's important to carefully analyze the output. The key purpose of this audit script is to just highlight the differences between the two. Test plan: 0. Apply patch 1. vi ./installer/data/mysql/kohastructure.sql 2. Comment out some columns, change NULL status, or whatever you like 3. perl misc/maintenance/audit_database.pl \ --filename /kohadevbox/koha/installer/data/mysql/kohastructure.sql 4. Note that the output includes SQL commands to change the database to match the new kohastructure.sql 5a. Try using koha-foreach and note that the database name appears above the database comparison 5b. koha-foreach "perl misc/maintenance/audit_database.pl \ --filename /kohadevbox/koha/installer/data/mysql/kohastructure.sql" Signed-off-by: Emmi Takkinen <emmi.takkinen@koha-suomi.fi> Signed-off-by: Owen Leonard <oleonard@myacpl.org> Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com> -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=34064 Martin Renvoize <martin.renvoize@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Signed Off |Passed QA CC| |martin.renvoize@ptfs-europe | |.com QA Contact|testopia@bugs.koha-communit |martin.renvoize@ptfs-europe |y.org |.com --- Comment #23 from Martin Renvoize <martin.renvoize@ptfs-europe.com> --- I think this is actually really rather useful to admins.. it's a simple way to do the compare of dumps that Jonathan suggests, but in a nice report. Script works, doesn't cause any harm.. I'm going to PQA :) -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=34064 --- Comment #25 from David Cook <dcook@prosentient.com.au> --- (In reply to Tomás Cohen Arazi from comment #24)

Mason, are we good to go with the dependencies?

SQL::Translator SQL::Translator::Diff

Marking blocked until then.

They're dependencies of DBIx::Class, but probably a good idea to explicitly add them as well. -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=34064 --- Comment #27 from Tomás Cohen Arazi <tomascohen@gmail.com> --- (In reply to David Cook from comment #26)

(In reply to David Cook from comment #25)

...

They're dependencies of DBIx::Class, but probably a good idea to explicitly add them as well.

Or rather they're dependencies of the Debian package.

I checked. And `pmvers` would tell me the Diff one was not installed. -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=34064 --- Comment #29 from David Cook <dcook@prosentient.com.au> --- (In reply to Tomás Cohen Arazi from comment #27)

I checked. And `pmvers` would tell me the Diff one was not installed.

`pmvers` doesn't work for SQL::Translator::Diff, since that package doesn't have a $VERSION variable. -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=34064 --- Comment #31 from David Cook <dcook@prosentient.com.au> --- Created attachment 153022 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=153022&action=edit Bug 34064: Add SQL::Translator dependency to cpanfile -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=34064 --- Comment #33 from David Cook <dcook@prosentient.com.au> --- The API looks the same so maybe I will just downgrade it... https://metacpan.org/release/VEESH/SQL-Translator-1.63/view/lib/SQL/Translat... https://metacpan.org/release/ILMARI/SQL-Translator-0.11024/view/lib/SQL/Tran... -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=34064 --- Comment #35 from David Cook <dcook@prosentient.com.au> --- So I think we've figured out the dependency thing. Should this still be BLOCKED or should it go back to "Passed QA"? -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=34064 Tomás Cohen Arazi <tomascohen@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Passed QA |Pushed to master Version(s)| |23.11.00 released in| | -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=34064 Fridolin Somers <fridolin.somers@biblibre.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |fridolin.somers@biblibre.co | |m --- Comment #38 from Fridolin Somers <fridolin.somers@biblibre.com> --- Enhancement not pushed to 23.05.x Looks great ! -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=34064 Pedro Amorim <pedro.amorim@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Blocks| |36039 Referenced Bugs: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36039 [Bug 36039] Output of database_audit.pl should be accessible through UI -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=34064 Martin Renvoize <martin.renvoize@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC|martin.renvoize@ptfs-europe | |.com | -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=34064 Magnus Enger <magnus@libriotech.no> changed: What |Removed |Added ---------------------------------------------------------------------------- See Also| |https://bugs.koha-community | |.org/bugzilla3/show_bug.cgi | |?id=41278 -- You are receiving this mail because: You are watching all bug changes.