stock sql + factories

create.sql

-- Describes everything needed to create the database schema.

create sequence emp_seq;
create sequence dept_seq;
create sequence job_seq;

create table dept (
id              number(15)      not null,
name            varchar2(20)    not null,
city            varchar2(20)    not null,
state           varchar2(2)     not null,
create_user     varchar2(30)    not null,
create_date     date            not null,
update_user     varchar2(30),
update_date     date
);

alter table dept add constraint dept_pk primary key
 (id) using index;

alter table dept add constraint dept_uk unique
 (name) using index;

create table job (
id              number(15)      not null,
name            varchar2(20)    not null,
create_user     varchar2(30)    not null,
create_date     date            not null,
update_user     varchar2(30),
update_date     date
);

alter table job add constraint job_pk primary key
 (id) using index;

alter table job add constraint job_uk unique
 (name) using index;

create table emp (
id              number(15)      not null,
first_name      varchar2(15)    not null,
last_name       varchar2(15)    not null,
job_id          number(15)      not null,
dept_id         number(15)      not null,
manager_id      number(15),
hire_date       date,
salary          number(7),
test_flag       varchar2(1)     default 'N' not null constraint test_flag_ck check(test_flag in ('Y','N')),
create_user     varchar2(30)    not null,
create_date     date            not null,
update_user     varchar2(30),
update_date     date
);

alter table emp add constraint emp_pk primary key
 (id) using index;

alter table emp add constraint emp_dept_fk
  foreign key (dept_id)
  references dept (id);

alter table emp add constraint emp_job_fk
  foreign key (job_id)
  references job (id);

alter table emp add constraint emp_manager_fk
  foreign key (manager_id)
  references emp (id);

drop.sql

-- Describes everything needed to destroy the database schema.

drop table emp;
drop table dept;
drop table job;
drop sequence emp_seq;
drop sequence dept_seq;
drop sequence job_seq;

factories.rb

require 'factory_girl'

CREATE_USER = 'JohnDoe'
CREATE_DATE = Time.now

# Arbitrary times to encapsulate start/end dates as far
# as possible.  INF time is Jan 19, 2038 -- aka Y2K38
TIME_ZERO = Time.at(0)
TIME_INF  = Time.at(2147472000)

Factory.define :department do |o|
  o.name 'Generic'
  o.city 'Generic'
  o.state 'Generic'
  o.create_user CREATE_USER
  o.create_date CREATE_DATE
end

Factory.define :job do |o|
  o.name 'Generic'
  o.create_user CREATE_USER
  o.create_date CREATE_DATE
end

Factory.define :employee do |o|
  o.first_name 'John'
  o.last_name 'Doe'
  o.association :job
  o.association :department
  o.test_flag 'N'
  o.create_user CREATE_USER
  o.create_date CREATE_DATE
end

insert.sql

-- Describes shared fixture (ie not test-specific) data.

insert into dept values (dept_seq.nextval, 'Accounting', 'New York', 'NY', 'INIT', sysdate, null, null);
insert into dept values (dept_seq.nextval, 'Research',   'Dallas', 'TX', 'INIT', sysdate, null, null);
insert into dept values (dept_seq.nextval, 'Sales',      'Chicago', 'IL', 'INIT', sysdate, null, null);
insert into dept values (dept_seq.nextval, 'Operations', 'Boston', 'MA', 'INIT', sysdate, null, null);

insert into job values (job_seq.nextval, 'President', 'INIT', sysdate, null, null);
insert into job values (job_seq.nextval, 'Manager', 'INIT', sysdate, null, null);
insert into job values (job_seq.nextval, 'Analyst', 'INIT', sysdate, null, null);
insert into job values (job_seq.nextval, 'Clerk', 'INIT', sysdate, null, null);
insert into job values (job_seq.nextval, 'Salesman', 'INIT', sysdate, null, null);

insert into emp values (emp_seq.nextval, 'Kim', 'King', (select id from job where name = 'President'),(select id from dept where name = 'Accounting'), null, '17-NOV-1981', 5000 , 'N', 'INIT', sysdate, null, null);
insert into emp values (emp_seq.nextval, 'Jim', 'Jones', (select id from job where name = 'Manager'),(select id from dept where name = 'Research'), (select id from emp where last_name = 'King') , '02-APR-1981', 2975, 'N', 'INIT', sysdate, null, null);
insert into emp values (emp_seq.nextval, 'Fred', 'Ford', (select id from job where name = 'Analyst'),(select id from dept where name = 'Research'), (select id from emp where last_name = 'Jones'), '03-DEC-1981', 3000, 'N', 'INIT', sysdate, null, null);
insert into emp values (emp_seq.nextval, 'Sally', 'Smith', (select id from job where name = 'Clerk'),(select id from dept where name = 'Research'), (select id from emp where last_name = 'Ford'), '17-DEC-1980', 800, 'Y', 'INIT', sysdate, null, null);
insert into emp values (emp_seq.nextval, 'Bill', 'Blake', (select id from job where name = 'Manager'),(select id from dept where name = 'Sales'), (select id from emp where last_name = 'King'), '01-MAY-1981', 2850, 'N', 'INIT', sysdate, null, null);
insert into emp values (emp_seq.nextval, 'Amy', 'Allen', (select id from job where name = 'Salesman'),(select id from dept where name = 'Sales'), (select id from emp where last_name = 'Blake'), '20-FEB-1981', 1600, 'N', 'INIT', sysdate, null, null);
insert into emp values (emp_seq.nextval, 'William', 'Ward', (select id from job where name = 'Salesman'),(select id from dept where name = 'Sales'), (select id from emp where last_name = 'Blake'), '22-FEB-1981', 1250, 'N', 'INIT', sysdate, null, null);
insert into emp values (emp_seq.nextval, 'Mike', 'Martin', (select id from job where name = 'Salesman'),(select id from dept where name = 'Sales'), (select id from emp where last_name = 'Blake'), '28-SEP-1981', 1250, 'Y', 'INIT', sysdate, null, null);
insert into emp values (emp_seq.nextval, 'Carla', 'Clark', (select id from job where name = 'Manager'),(select id from dept where name = 'Accounting'), (select id from emp where last_name = 'King'), '09-JUN-1981', 2450, 'N', 'INIT', sysdate, null, null);
insert into emp values (emp_seq.nextval, 'Sam', 'Scott', (select id from job where name = 'Analyst'),(select id from dept where name = 'Research'), (select id from emp where last_name = 'Jones'), '09-DEC-1982', 3000, 'N', 'INIT', sysdate, null, null);
insert into emp values (emp_seq.nextval, 'Tom', 'Turner', (select id from job where name = 'Salesman'),(select id from dept where name = 'Sales'), (select id from emp where last_name = 'Blake'), '08-SEP-1981', 1500, 'N', 'INIT', sysdate, null, null);
insert into emp values (emp_seq.nextval, 'Abe', 'Adams', (select id from job where name = 'Clerk'),(select id from dept where name = 'Research'), (select id from emp where last_name = 'Scott'), '12-JAN-1983', 1100, 'Y', 'INIT', sysdate, null, null);
insert into emp values (emp_seq.nextval, 'Joan', 'James', (select id from job where name = 'Clerk'),(select id from dept where name = 'Sales'), (select id from emp where last_name = 'Blake'), '03-DEC-1981', 950, 'N', 'INIT', sysdate, null, null);
insert into emp values (emp_seq.nextval, 'Michelle', 'Miller', (select id from job where name = 'Clerk'),(select id from dept where name = 'Accounting'), (select id from job where last_name = 'Clark'), '23-JAN-1982', 1300, 'Y', 'INIT', sysdate, null, null);