perlquestion
blogical
<p>Not necessarily perl, but I'm currently headed towards a perlish solution...</p>
<p>I have two databases, one for development and another for production. They do not share the same data. When I make structural changes in development, I need to apply them to production when I update.</p>
<p>As I am unaware of any native MySQL function combination that addresses this specific need I've begun to think about a perlsih solution.</p>
<p>My current solution is to dump the table structure creation sql, apply some alterations to translate CREATE statements into ALTER / CHANGE statements, and apply that as a patch. This has a few limitations- it doesn't drop leftover columns or keys, and it doesn't create missing columns. While this seems to suit my purposes, I thought I would ask around and see if anyone has seen a similar / better solution.</p>
<p><b>Edit</b><br />Here's what I'm using at the moment. YMMV.<br />
<c>
#Step 1. Dump/export table creation sql
# Note table and field names enclosed in backquotes
#Step 2. Run this filter on it to create alter patch
#Step 3. Manually add commands to drop keys, drop and create table / field to top of patch.
# Note: created tables and fields needn't be accurate, they just need to exist
#Step 4. Run this patch on the database
while (<>) {
s/^\s*CREATE TABLE (`\w+`) \(/ALTER TABLE $1\n/;
s/^\s*(`\w+`)/ CHANGE $1 $1/;
s/^\s*PRIMARY KEY/ DROP PRIMARY KEY, ADD PRIMARY KEY/;
s/^\s*UNIQUE KEY (`\w+`)/ DROP INDEX $1, ADD UNIQUE $1/;
s/^\s*KEY (`\w+`)/ DROP INDEX $1, ADD INDEX $1/;
s/^\)//;
s/ENGINE=(\w+)/ ENGINE=$1,\n/;
s/DEFAULT CHARSET=(\w+)/ DEFAULT CHARSET=$1/;
s/^\s+(.+?),?\s*$/ $1,\n/;
print;
}
</c>
</p>
<p>b.logical</p>
<div class="pmsig"><div class="pmsig-526652">
<p>"One is enough. If you are acquainted with the principle, what do you care for the myriad instances and applications?"<br>- Henry David Thoreau, Walden</p>
</div></div>