db_version

rencro · #1 09-03-2013, 08:20 PM

With the db svn not being updated any longer, I decided to make use of the db_version table for my own needs. The daily dumps have dropped this table, guess it woulndt make much sense to have it in there, but since I dont make any manual changes to the db, instead I source in every change as if its an "update" I use this:

Code:

DROP TABLE IF EXISTS `db_version`;
CREATE TABLE `db_version` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `Revision` int(3) NOT NULL,
  `file_name` varchar(64) NOT NULL,
  `date_entered` DATE,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED COMMENT='Last applied sql update to DB';

Now, whenever I souce in a custom sql, or update source and source in a "required" sql, I add this to the bottom:

Code:

-- update inport status
INSERT INTO db_version (`Revision`,`file_name`,`date_entered`) VALUES 
	('2506','load_bots',CURDATE());

In the example above, thats when i source in the load_bots.sql, I will update the name dependent on the name of the actual sql..

Here is what my latest test db looks like:

Code:

1	2506	peqdb_rev2506	2013-08-30
2	2506	load_player	2013-08-30
3	2506	load_views	2013-08-30
4	2506	load_qs	2013-08-30
5	2506	load_bots	2013-08-30
6	0	mercs_peq	2013-08-30
7	2380	2380_optional_merc_merchant	2013-08-30
8	2380	2380_optional_merc_rules	2013-08-30
9	0	2013_02_25_Impr_HT_LT	2013-08-30
10	0	2013_03_23_Escape_FadingMemories	2013-08-30
11	0	2013_04_04_Natures_Bounty	2013-08-30
12	0	2013_04_08_Salvage	2013-08-30
13	0	2013_05_05_Account_Flags	2013-08-30
14	0	2013_05_05_Item_Tick	2013-08-30
15	0	2013_07_11_NPC_Special_Abilities	2013-08-30
16	0	mercfix2	2013-08-30
17	0	reneq_master.sql	2013-08-30
18	0	reneq_zones	2013-08-31
19	0	reneq_tasks	2013-08-31
20	0	schema(peqphp)	2013-08-31
21	0	class_creation	2013-09-01