Obvious Data
Rails gem to treat DB functions, triggers, etc more like code, and make them more discoverable
Premise
Database functions, triggers, and views can be very powerful when used in the right circumstances, but they lack the discoverability and versioning of source code. Short of logging into your databases, it is difficult to tell what versions of functions and triggers are active, nevermind getting a good picture of what are all the custom functions and triggers in place in your application.
Obvious Data improves this by creating directories for you to place these constructs, treating them more like code and instantly making them greppable, and adding helper methods where you can easily use these files from your migrations.
Usage
Creating directories
$ bin/rails generate obvious_data_directories
This will generate empty db/functions
, db/views
, and db/triggers
directories for you to use.
Adding functions
Say you want to add a function called one()
. You should create a file called one.sql
in db/functions/
with the SQL that's needed to add that function. Thus:
db/functions/one.sql:
CREATE OR REPLACE FUNCTION one() RETURNS integer AS $$
SELECT 1 AS result;
$$ LANGUAGE SQL;
Then, from a migration you can add this function like:
db/migrate/20140724185749_create_one_function.rb
class CreateDummyTable < ActiveRecord::Migration
def up
execute_function_file 'one'
end
def down
drop_function 'one()'
end
end
Run your migration and your function should be operational!
Changing functions
Say it is suddenly decreed that the numeric representation of "one" is now 99. To keep the active version of your function discoverable, you are expected to change the same function file you created in the above section in place, and updating it through a new migration. So:
db/functions/one.sql:
CREATE OR REPLACE FUNCTION one() RETURNS integer AS $$
SELECT 99 AS result;
$$ LANGUAGE SQL;
db/migrate/20140724185750_one_function_to_99.rb
class CreateDummyTable < ActiveRecord::Migration
def up
execute_function_file 'one'
end
def down
# Unfortunately there's currently no easy way to revert this situation
end
end
Dropping functions
What do you do when you no longer want to use a class from your source code? You delete it! Which is the same thing we'll do here. In the migration, make sure to use the full function signature instead of just the function name.
db/functions/one.sql: deleted
db/migrate/20140724185751_drop_one_function.rb:
class CreateDummyTable < ActiveRecord::Migration
def up
drop_function 'one()'
end
def down
# Unfortunately there's currently no easy way to revert this situation
end
end
Adding/changing/dropping views and triggers
Practically the same as adding and changing functions. The available migration helpers are:
execute_view_file(view_name)
drop_view(view_name)
execute_trigger_file(trigger_name)
drop_trigger(trigger_name, table_trigger_is_on)
Supported Databases etc
- Currently the only database supported is PostgreSQL, but no reason that other DBs can't also be supported
- Rails version 3.2.x through 4.2.x
- Ruby versions 1.9.3, 2.1.x, and rubinius 2.2+
Contributing
Make sure you run the tests before and after making changes. Here's how:
- Ensure PostgreSQL is installed
- Run
bundle install
- Run
bundle exec rake app:db:create
- Run
bundle exec rake app:db:setup app:db:migrate app:db:test:prepare
- Run
bundle exec rspec