Skip to content

Instantly share code, notes, and snippets.

@rbucker
Created January 23, 2012 21:25
Show Gist options
  • Save rbucker/1665608 to your computer and use it in GitHub Desktop.
Save rbucker/1665608 to your computer and use it in GitHub Desktop.
The is the sample SQL for implementing crud-fest
-- 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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment