Best way to check Database Integrity with latest q2a version?

Question

Over the last years I did some manual hacks / db modifications as some q2a updates failed. Since I have lost overview, I am wondering how I can change my current production DB (big forum, much data to handle!) for integrity.

How do I find out if it has the same structure as the latest q2a version?!

I thought of exporting my recent DB structure and exporting the DB structure of a fresh localhost q2a installation, but maybe there is a better way?

Answer 1

Those are the issues that database migrations try to prevent (such as Rails or Laravel do). However, Q2A doesn't support them.

As I personally like Git's diff very much, when trying to compare things I always try to use it -- it is console-based, clear enough and it doesn't depend on the operating system.

So this is what I would do:

1. Install the Q2A version I'm interested in comparing my current installation to (I'm assuming v1.7.5, but any will do)
2. Export the structure of the current installation to a current.sql file (only structure, no data!). Check this https://stackoverflow.com/questions/6175473
3. Export the stucture of the just installed v1.7.5 in the same way to a default.sql file.
4. In a new directory, setup the git repo with: git init
5. Add and commit the current.sql file
6. Remove the current.sql file
7. Move the default.sql file to the directory
8. Rename the default.sql file to current.sql file
9. git diff

Steps from 4 to 9 just show a non-OS-dependent way of diff-ing files. You could use an IDE, in Windows there is the Winmerge tool, in Linux you could use just the diff command. Considering it is just a single file, you could even use online tools.

Anyway, whatever method you choose, you will be able to see what has changed from your current schema to the default one for a given version.

Comments

@Scott: Check out how Laravel is handling database changes. This guarantees db integrity.

@pupi1985: Yep, your answer confirmed what I thought and I did install the latest q2a, then exported only structure. Same export with my production. Then used Notepad++ Compare plugin (or Atom is also very good) to see the differences.

---

And then you expect just a few differences and everything turns red and green and you just go like this: https://www.youtube.com/watch?v=iMs9feeSknk

Anyway, there is no need for the time being to implement such a robust approach as Laravel does. In fact, I worked this out applying some logic to the qa-config.php file. So in there I just add some IFs to check current Q2A version and, based on that, set the appropriate database name (q2a17, q2a18, etc) so the file automatically figures out the right database to use for the current code commit.

---

@q2apro What exact “integrity” does Laravel guarantee? If you have a migration that adds for example a blog table, the rollback for that migration removes the blog table. So if you added some blog posts the rollback deletes them and you can’t get them back.

---

@all: This will probably help: https://github.com/q2apro/question2answer-db