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

What did you mean about migrations? Q2A does have DB migrations.

---

Not quite. It just executes a list of queries which only allows queries to be appended (not edited, which makes sense, of course).

Migrations provide two main features to developers: one is the ability to track changes to the database in a repository and the other is to be able to jump to any part of the code and be able to recreate the schema of the database in order to use the code of the app at that given point in time. Q2A fulfills the former but not the latter.

I know there is no dictionary definition of "database migration" but all the frameworks I know that support migrations support them that way.

---

I see what you’re saying but I’m not sure the latter is particularly useful (unless I misunderstood). If you try to use an old database schema with the latest code then you’ll almost certainly get errors. The only way to use an old schema successfully is to install an older version of Q2A which is perfectly possible.

---

The idea is not to try to use an old database schema with the latest code but rather and old database schema with the code "at that given point in time". So there is no need to install the older version of Q2A.

In fact, as the database schema is basically in the code, checking out the code will mean using the database schema compatible with that code (provided the migrations are rolled back and reapplied).

---

OK I misunderstood. I guess you’re talking about rollbacks, when you already have an established database (eg you tried Q2A 1.8 beta but then want to go back to 1.7 for now).

In which case, no Q2A doesn’t support that. They can be useful during development but I don’t see a huge need for it in Q2A, as they are destructive and will delete data (for example reversing a ‘messages’ migration would delete all wall posts/PMs received).

---

@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