Hello, 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. > You should check out Liquibase (http://liquibase.org/). It's been around for a number of years and supports a many common database refactorings including a <createProcedure> ( http://liquibase.org/manual/create_stored_procedure) tag. The documentation for that tag indicates how to reuse your code so it doesn't have to be re-specified each time. Seems to me though you could probably do something similar with Sequel Migrations if configured appropriately. Hope this helps, -Bill > 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<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 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.
