Lost database schema recovery help

[pureless]

I recently had a server crash happen to my hosting website where all data was lost. I had backups of all my websites, however there was one database i did not have backed up. The data is not as important to me as the schema is. I still obviously have all the php files associated with it with all the select/insert queries. I was wondering if there was an easy way to rebuild the schema (table and col names is all i need) based off the queries used. Ive tried going through the scripts by hand but it's a LOT of script and i can't seem to tell if i have all col's accounted for or how to tell what they were.

[pureless]

To add to it, i have searched through the script looking for all variable arrays to get the col names but again its just a pain to figure out which table the col name is associated with etc, or telling if i have them all. Im just hoping someone has had this problem before or has an idea of how to go about rebuilding it.

[mac_gyver]

one way to do this is to run the script on a development system and see what database related errors there are, this of course assumes your database logic is testing for errors and reporting/logging them. references in the php code to the columns would produce php undefined variable/undefined array index errors.

 

if your code isn't testing for query errors, you could write your own database query function that does do this and simply do a global search/replace for what ever query function you are currently using and rename it to call the function you wrote.

 

your script shouldn't have a lot of different points where queries are being performed at. if you do, it might be time to simplify the code.

 

BTW - if you are using the mysql_ functions and are going through your code anyway, it might be a good time to update to the mysqli_ or PDO database libraries as the mysql_ functions are depreciated as of php5.5.

[vinny42]

Rebuilding from PHP code is a pretty difficult job. Most MySQL users use wonderful queries like SELECT * everywhere, so MySQL won't tell you which columns are missing, PHP just complains about missing indexes in arrays, somewhere.

 

So no, there is no easy way, it's manual labour looking at queries, looking at which fields in the resultarrays the script uses, hours of fun!

 

This kind of disaster is exactly why I insist on having (apart from backups and backups of backups)) a "structure.sql" file in the sourcecode somewhere, preferably with some test data.

 

PS:mac_gyver is right about refactoring, but do *NOT* do *ANY* of that until you have completely rebuilt the database an verified that it's correct. Making changes now will cause you to loose valuable information about the schema.

Edited September 1, 2013 by vinny42