The is the sample SQL for implementing crud-fest

gistfile1.sql

-- Copyright (c) 2012 - Richard Bucker
-- All Rights Reserved
--
-- this file/script will create the necessary tables
-- in the database as part of the crud_fest project.
--
-- NOTE: the initial testing is being performed against
-- a SQLite3 instance, however, in order to test Grails
-- I'll be executing this code against H2.
--
-- SQLITE3:
--    $ sqlite3 /tmp/crud_fest.db < setup.sql 
-- 
create table test_cards (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    serial_number INTEGER NOT NULL,
    card_number VARCHAR(19) NOT NULL,
    expiration_date VARCHAR(4),
    issue_date TIMESTAMP,
    street varchar(200),
    zipcode VARCHAR(10),
    pin VARCHAR(6),
    atm_pin VARCHAR(6),
    CVV VARCHAR(6),
    CVV2 VARCHAR(6),
    track1 VARCHAR(150),
    track2 VARCHAR(150),
    track3 VARCHAR(150),
    reset_balance BOOLEAN,
    is_decrement BOOLEAN,
    actual_balance NUMERIC(15,2),
    open_to_buy NUMERIC(15,2),
    constraint unique_card unique (card_number, expiration_date),
    constraint unique_serial unique (serial_number)
);

create table message_field_dictionary (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    field_id INTEGER,
    field_name VARCHAR(25),
    field_description TEXT,
    field_format VARCHAR(200),
    data_types VARCHAR(200),
    default_values VARCHAR(200),
    constraint unique_id unique (field_id),
    constraint unique_name unique (field_name)
);

create table message_test_cases (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    test_case_id INTEGER,
    short_name VARCHAR(100),
    description TEXT,
    elapsed_ceiling INTEGER,
    is_active BOOLEAN,
    group_name VARCHAR(100),
    sub_group_name VARCHAR(100),
    constraint unique_testcase unique (test_case_id),
    constraint unique_name unique (short_name)
);

create table message_request (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    test_case_id INTEGER,
    field_id INTEGER,
    field_value VARCHAR(200),
    constraint unique_reqfield unique (test_case_id,field_id),
    FOREIGN KEY (field_id) REFERENCES message_field_dictionary(field_id)
);

create table message_response (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    test_case_id INTEGER,
    field_id INTEGER,
    field_value VARCHAR(200),
    constraint unique_respfield unique (test_case_id,field_id),
    FOREIGN KEY (field_id) REFERENCES message_field_dictionary(field_id)
);

create table message_test_results (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    test_case_id INTEGER,
    started TIMESTAMP,
    finished TIMESTAMP,
    elapsed_time INTEGER,
    results VARCHAR(100),
    request TEXT,
    response TEXT,
    errors TEXT,
    trace TEXT,
    FOREIGN KEY (test_case_id) REFERENCES message_test_cases(test_case_id)
);

-- END