turntables <img src=“https://codeclimate.com/github/psyomn/turntables.png” /> <img src=“https://travis-ci.org/psyomn/turntables.png?branch=master” alt=“Build Status” />

Description

You can use this in order to manage your (simple) sqlite3 databases. I wanted something light and simple in order to help on some development I’m doing on my own. I found myself repeating a pattern most of this time, and decided to bring an end to it by writing this gem.

The concept is inspired by the Rails migrations, and something else I’ve seen in some Bukkit code. Ultimately the functionality of this library can be further abstracted, and made more modular in the future, as well as translated in other languages. But alas our mortal time is limited.

Features

Examples

This is a simple tutorial on how to invoke the database manager.

You need a directory structure like this:

.
|-- main.rb
`-- sql
    |-- mono
    `-- seq
        |-- 1.sql
        |-- 2.sql
        |-- 3.sql
        `-- 4.sql

The sql files can be like this for example. The comments that are prepended with ‘–$’ are inserted in the version history table within the table manager so that if someone needs to diagnose issues on the long run, there are the comments there at least (along with a version, and date field).

1.sql:

--$ First version will be created with this. The comments to be added in the
--$ versions history table can be prepended with the '$' sign in order for you
--$ specify comments (if needed). It's here if you want it. 
-- This is an example file to show how the turntables gem could work maybe
-- with people. and stuff.
-- This SQL file adds the person, and work_descriptions entities to the 
-- database. 
CREATE TABLE persons (
  id      integer primary key autoincrement,
  name    varchar(50),
  surname varchar(50), 
  age     integer
);

CREATE TABLE work_descriptions (
  id          integer primary key autoincrement,
  description text
);

2.sql:

--$ Bump to version 2. This is starting to look good!
-- And this is for the second revision of the database.

CREATE TABLE inventory (
  id integer primary key autoincrement, 
  description text
);

3.sql:

--$ We did a mistake. We forgot to add a field to the persons table. The extra
--$ field to be added is the DOB. 
ALTER TABLE persons ADD COLUMN dob BIGINT;

4.sql:

--$ This table is added because now the system requires accounts as well.
--$ Sat Jul 13 21:45:40 EDT 2013
-- Table to create accounts for people.
CREATE TABLE accounts (
  balance  float,
  owner_id integer,
  FOREIGN KEY(owner_id) references persons(id)
);

main.rb should be like this:

require 'turntables'

include Turntables

puts "This is to test the library and proof of concept of turntables"

# Ideal way of calling this library
turntable = Turntable.new

# sql is a directory containing two subdirs: seq, mono
# seq contains sequential transactions
# mono contains monolithic transactions
turntable.register('sql')
turntable.make!

# Note: There should not be any other calls to the library. We give it the 
# sql repository location, and then it's the responsibility of Turntables to
# decide whether to take action or not.

If you require that the database be made at a specified location, you should use the method ‘make_at!(location)`. To do this, just do what the above code does until `make!`. Then you need to call `make_at!` in the following way:

turntable.make_at!("path/to/database.db")

Right now versions should be marked as the filename. Try to have intergers as versions, to avoid odd behaviour. So in this case versions 1,2,3 would respectively be in file form 2.sql, 3.sql.

The monolithic transactions should be a combinations of all the versions, up to the one it denotes. In other words, version 4 would be the combination of all previous ones including itself 2.sql, 3.sql, 4.sql.

If you require it, you can also query the version histories in your application. To do so, you just need to use the VersionHistory active record, in the turntables gem.

Requirements

I’ve tested this with Ruby Versions = 1.9.3, though you should not really have any issues in theory.
SQLite3 gem

Install

$ gem install turntables

Synopsis

$ turntables

Copyright

License

License is MIT regardless what you may see or find elsewhere; I might have not changed it due to lack of time.