Analyze and inspect CREATE TABLE SQL statements and translate across databases. Uses Ragel internally for parsing.
Ruby Other
Fetching latest commit…
Cannot retrieve the latest commit at this time.
Permalink
Failed to load latest commit information.
features
lib
spec
.gitignore
Gemfile
LICENSE
README.md
Rakefile
create_table.gemspec

README.md

CreateTable

Analyze and inspect CREATE TABLE SQL statements and translate across databases.

See the introductory post on Brighter Planet's blog.

BETA

Tests and documentation are being added constantly.

TODO: stop making excuses and write a proper README.

Quickstart

Parsing

>> require 'create_table'
=> true
>> c = CreateTable.new(%{
  CREATE TABLE employees
  (employeeid INTEGER NOT NULL,
  lastname VARCHAR(25) NOT NULL,
  firstname VARCHAR(25) NOT NULL,
  reportsto INTEGER NULL); 
})
=> #<CreateTable>
>> c.columns.map(&:name)
=> ["employeeid", "lastname", "firstname", "reportsto"]
>> c.columns.map(&:data_type)
=> ["INTEGER", "CHARACTER VARYING(25)", "CHARACTER VARYING(25)", "INTEGER"]
>> c.columns.map(&:allow_null)
=> [false, false, false, true]

Generating cross-platform SQL

>> require 'create_table'
=> true
>> c = CreateTable.new(%{
  CREATE TABLE cats (
    id INTEGER AUTO_INCREMENT, /* AUTO_INCREMENT with an underscore is MySQL-style... */
    nickname CHARACTER VARYING(255),
    birthday DATE,
    license_id INTEGER,
    price NUMERIC(5,2),
    PRIMARY KEY ("id")
  )
})
=> #<CreateTable>
>> c.to_mysql
=> ["CREATE TABLE cats ( `id` INTEGER PRIMARY KEY AUTO_INCREMENT, nickname CHARACTER VARYING(255), birthday DATE, license_id INTEGER, price NUMERIC(5,2) )"]
>> c.to_postgresql
=> ["CREATE TABLE cats ( \"id\" SERIAL PRIMARY KEY, nickname CHARACTER VARYING(255), birthday DATE, license_id INTEGER, price NUMERIC(5,2) )"]
>> c.to_sqlite3
=> ["CREATE TABLE cats ( \"id\" INTEGER PRIMARY KEY AUTOINCREMENT, nickname CHARACTER VARYING(255), birthday DATE, license_id INTEGER, price NUMERIC(5,2) )"]

As a web service

Check out the CreateTable demo web service!

$ curl -i -X POST -H "Accept: application/json" --data "CREATE TABLE cats ( id INTEGER AUTO_INCREMENT, nickname CHARACTER VARYING(255), birthday DATE, license_id INTEGER, price NUMERIC(5,2), PRIMARY KEY (\"id\") )" http://create-table.herokuapp.com/statements
HTTP/1.1 201 Created
Cache-Control: max-age=0, private, must-revalidate
Content-Type: application/json; charset=utf-8
Date: Fri, 24 Aug 2012 22:24:52 GMT
Etag: "f13513b9126eb1fb909229e828c6a7cd"
Location: http://create-table.herokuapp.com/statements/9
Server: thin 1.4.1 codename Chromeo
X-Rack-Cache: invalidate, pass
X-Runtime: 0.051092
X-Ua-Compatible: IE=Edge,chrome=1
Content-Length: 1490
Connection: keep-alive

{"statement":{"original":"CREATE TABLE cats ( id INTEGER AUTO_INCREMENT, nickname CHARACTER VARYING(255), birthday DATE, license_id INTEGER, price NUMERIC(5,2), PRIMARY KEY (\"id\") )","mysql":"CREATE TABLE cats ( `id` INTEGER PRIMARY KEY AUTO_INCREMENT, nickname CHARACTER VARYING(255), birthday DATE, license_id INTEGER, price NUMERIC(5,2) )","postgresql":"CREATE TABLE cats ( \"id\" SERIAL PRIMARY KEY, nickname CHARACTER VARYING(255), birthday DATE, license_id INTEGER, price NUMERIC(5,2) )","sqlite3":"CREATE TABLE cats ( \"id\" INTEGER PRIMARY KEY AUTOINCREMENT, nickname CHARACTER VARYING(255), birthday DATE, license_id INTEGER, price NUMERIC(5,2) )","columns":[{"name":"id","data_type":"INTEGER","allow_null":false,"default":null,"primary_key":true,"unique":true,"autoincrement":true,"charset":null,"collate":null},{"name":"nickname","data_type":"CHARACTER VARYING(255)","allow_null":true,"default":null,"primary_key":false,"unique":false,"autoincrement":false,"charset":null,"collate":null},{"name":"birthday","data_type":"DATE","allow_null":true,"default":null,"primary_key":false,"unique":false,"autoincrement":false,"charset":null,"collate":null},{"name":"license_id","data_type":"INTEGER","allow_null":true,"default":null,"primary_key":false,"unique":false,"autoincrement":false,"charset":null,"collate":null},{"name":"price","data_type":"NUMERIC(5,2)","allow_null":true,"default":null,"primary_key":false,"unique":false,"autoincrement":false,"charset":null,"collate":null}]}}

Real-world usage

Brighter Planet logo

We will soon use create_table for the Brighter Planet reference data web service.

Copyright

Copyright 2012 Brighter Planet, Inc.