It sounds like what you are looking for is a http://en.wikipedia.org/wiki/Data_Definition_Language tool.
We are in a similar pain to what you are describing but with Perl. So I'm kind of in the same boat. On Thu, Mar 29, 2012 at 4:00 PM, Joe Van Dyk <[email protected]> wrote: > https://github.com/nkiraly/DBSteward/wiki looks like an interesting tool to > solve this problem. > > You store schema in xml files -- the files contain metadata about the > functions, tables, etc. When you want to do a release, dbsteward can > automatically generate sql that contains the changes. I haven't tried it yet > though. > > Joe > > > On Thursday, March 29, 2012 12:11:26 PM UTC-7, Joe Van Dyk wrote: >> >> I'm starting to move more logic into the database, using triggers, views, >> functions, CTEs, etc. When plv8/json comes out for postgres, I can see >> myself putting lots of logic in there. >> >> I'm having problems with the "standard" way of doing database migrations >> in sequel and activerecord. Both sequel and activerecord let you put >> arbitrary sql code into timestamped files. When each file is ran, a >> schema_versions table is updated with the filename (or timestamp in the >> filename), which keeps record of which migrations have been applied to the >> current database. >> >> If a lot of coding is being done at the database level, that means that >> modifications to existing views, functions, etc usually result in something >> like this: >> >> Migration 1 defines a function and a view that uses that function. >> >> create function calculate(x int) returns int as $$ >> >> return x + 1; >> >> $$ language sql; >> >> >> >> create view foos as ( >> >> select something, calculate(something) from a_table >> >> ); >> >> Migration 2 changes the type of the function. I have to drop all objects >> that depend on foo, and recreate them by copying their entire body -- even >> if there weren't any changes in most of the other code! >> >> -- Have to drop view before I can change the type of the `calculate` >> function. >> drop view foos; >> >> create or replace calculate(x bigint) returns bigint as $$ >> >> return x + 1; >> >> $$ language sql; >> -- I could do `drop function calculate(int) cascade`, but I >> might accidentally drop some objects that wouldn't get recreated below. >> >> >> >> -- Now I have to recreate foo. >> >> create view foos as ( >> >> select something, calculate(something) from a_table >> >> ); >> >> If I'm building a system based on views and functions and triggers, my >> migrations would be filled with duplicated code, and it's difficult to find >> the latest version of the code. You might say "don't do that!", but for my >> purposes (e-commerce, shipping, transactions), I'm finding it's a lot easier >> and faster to have the database ensure the integrity of the data by doing >> the logic inside the database. >> >> You can (of course) dump the current database schema (which includes all >> the code definitions), but I think you lose comments. And you wouldn't >> generally want to edit a giant file that contains the whole schema. >> >> Any ideas on how to solve this problem? >> >> My best idea is to how the sql code contained in their own canonical files >> (app/sql/orders/shipping.sql, app/sql/orders/creation.sql, etc). Everyone >> develops directly on these. Whenever it's time for a release, then you'd >> want to make a new migration file, look at all the changed code since the >> previous release, figure out the dependency chain of the database objects >> that need to be dropped and recreated, and then copy the sql from the >> canonical sql files into a new sequel/activerecord migration file. But it's >> a pain. :/ >> >> Thoughts are very welcome. I hope I explained this well enough, I'm >> cutting back on my caffeine intake and I'm a little groggy atm. >> >> Oh, I asked a similar question on Stack >> Overflow: http://stackoverflow.com/q/9711405/17076 >> The result was a function that let me pass in: >> - sql code to run >> - database views to drop and recreate >> The function would retrieve the view definition, drop the views, run the >> sql code, then recreate the view definition (in reverse order of dropping). >> Perhaps a system of functions like this would help solve the problem of >> having to copy/paste sql code into the migration files. >> >> Joe > > -- > You received this message because you are subscribed to the Google Groups > "sequel-talk" group. > To view this discussion on the web visit > https://groups.google.com/d/msg/sequel-talk/-/palc4T69N1kJ. > > To post to this group, send email to [email protected]. > To unsubscribe from this group, send email to > [email protected]. > For more options, visit this group at > http://groups.google.com/group/sequel-talk?hl=en. -- —Silas -- You received this message because you are subscribed to the Google Groups "sequel-talk" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/sequel-talk?hl=en.
