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.
