first attempt to create the test db for yii (it was mostly created with mysql2firebird) isql -b -e -m -m2 -i firebirdsql.sql

firebird.sql

-- This is the database schema for testing Firebird support of yii Active Record.
-- To test this feature, you need to create a database named '/tmp/yii.fdb' on 'localhost'
-- and create an account 'test/test' which owns this test database.
CREATE DATABASE 'localhost:/tmp/yii1.x.fdb' page_size 8192 user 'SYSDBA' password 'masterkey';
SET TRANSACTION READ COMMITTED RECORD_VERSION ;

CREATE TABLE users
(
id INTEGER  PRIMARY KEY  NOT NULL,
username VARCHAR(128) NOT NULL,
password VARCHAR(128) NOT NULL,
email VARCHAR(128)NOT NULL
);
create SEQUENCE gen_users;
alter SEQUENCE gen_users restart with 0;
set term !! ;
create trigger t_users for users before insert position 0
as begin
if (new.id is null) then
new.id = gen_id(gen_users, 1);
RDB$SET_CONTEXT('USER_SESSION', 'LAST_INSERT_ID', new.id);
end!!
set term ; !!

COMMIT;
INSERT INTO users (username, password, email) VALUES ('user1','pass1','email1');
INSERT INTO users (username, password, email) VALUES ('user2','pass2','email2');
INSERT INTO users (username, password, email) VALUES ('user3','pass3','email3');
COMMIT;
CREATE TABLE profiles
(
id INTEGER  PRIMARY KEY  NOT NULL,
first_name VARCHAR(128)  NOT NULL,
last_name VARCHAR(128)  NOT NULL,
user_id INTEGER  NOT NULL,
CONSTRAINT prof0_FK_profile_user FOREIGN KEY (user_id)
REFERENCES users (id) ON DELETE CASCADE ON UPDATE NO ACTION
);
create SEQUENCE gen_profiles;
alter SEQUENCE gen_profiles restart with 0;
set term !! ;
create trigger t_profiles for profiles before insert position 0
as begin
if (new.id is null) then
new.id = gen_id(gen_profiles, 1);
RDB$SET_CONTEXT('USER_SESSION', 'LAST_INSERT_ID', new.id);
end!!
set term ; !!


INSERT INTO profiles (first_name, last_name, user_id) VALUES ('first 1','last 1',1);
INSERT INTO profiles (first_name, last_name, user_id) VALUES ('first 2','last 2',2);

CREATE TABLE posts
(
id INTEGER  PRIMARY KEY  NOT NULL,
title VARCHAR(128)  NOT NULL,
create_time TIMESTAMP  NOT NULL,
author_id INTEGER  NOT NULL,
content BLOB SUB_TYPE TEXT,
CONSTRAINT post1_FK_post_author FOREIGN KEY (author_id)
REFERENCES users (id) ON DELETE CASCADE ON UPDATE NO ACTION
);
create SEQUENCE gen_posts;
alter SEQUENCE gen_posts restart with 0;
set term !! ;
create trigger t_posts for posts before insert position 0
as begin
if (new.id is null) then
new.id = gen_id(gen_posts, 1);
RDB$SET_CONTEXT('USER_SESSION', 'LAST_INSERT_ID', new.id);
end!!
set term ; !!

set term !! ;
create trigger ts_posts for posts before insert position 0
as begin
if (new.create_time is null) then
new.create_time = current_timestamp;
end!!
set term ; !!

commit;
INSERT INTO posts (title, create_time, author_id, content) VALUES ('post 1','2000-01-01',1,'content 1');
INSERT INTO posts (title, create_time, author_id, content) VALUES ('post 2','2000-01-02',2,'content 2');
INSERT INTO posts (title, create_time, author_id, content) VALUES ('post 3','2000-01-03',2,'content 3');
INSERT INTO posts (title, create_time, author_id, content) VALUES ('post 4','2000-01-04',2,'content 4');
INSERT INTO posts (title, create_time, author_id, content) VALUES ('post 5','2000-01-05',3,'content 5');
commit;

CREATE TABLE comments
(
id INTEGER  PRIMARY KEY  NOT NULL,
content BLOB SUB_TYPE TEXT  NOT NULL,
post_id INTEGER  NOT NULL,
author_id INTEGER  NOT NULL,
CONSTRAINT comm2_FK_post_comment FOREIGN KEY (post_id)
REFERENCES posts (id) ON DELETE CASCADE ON UPDATE NO ACTION,
CONSTRAINT comm3_FK_user_comment FOREIGN KEY (author_id)
REFERENCES users (id) ON DELETE CASCADE ON UPDATE NO ACTION
);
create SEQUENCE gen_comments;
alter SEQUENCE gen_comments restart with 0;
set term !! ;
create trigger t_comments for comments before insert position 0
as begin
if (new.id is null) then
new.id = gen_id(gen_comments, 1);
RDB$SET_CONTEXT('USER_SESSION', 'LAST_INSERT_ID', new.id);
end!!
set term ; !!
commit;

INSERT INTO comments (content, post_id, author_id) VALUES ('comment 1',1, 2);
INSERT INTO comments (content, post_id, author_id) VALUES ('comment 2',1, 2);
INSERT INTO comments (content, post_id, author_id) VALUES ('comment 3',1, 2);
INSERT INTO comments (content, post_id, author_id) VALUES ('comment 4',2, 2);
INSERT INTO comments (content, post_id, author_id) VALUES ('comment 5',2, 2);
INSERT INTO comments (content, post_id, author_id) VALUES ('comment 6',3, 2);
INSERT INTO comments (content, post_id, author_id) VALUES ('comment 7',3, 2);
INSERT INTO comments (content, post_id, author_id) VALUES ('comment 8',3, 2);
INSERT INTO comments (content, post_id, author_id) VALUES ('comment 9',3, 2);
INSERT INTO comments (content, post_id, author_id) VALUES ('comment 10',5, 3);

CREATE TABLE categories
(
id INTEGER  PRIMARY KEY  NOT NULL,
name VARCHAR(128)  NOT NULL,
parent_id INTEGER,
CONSTRAINT cate4_FK_category_category FOREIGN KEY (parent_id)
REFERENCES categories (id) ON DELETE CASCADE ON UPDATE NO ACTION
);
create SEQUENCE gen_categories;
alter SEQUENCE gen_categories restart with 0;
set term !! ;
create trigger t_categories for categories before insert position 0
as begin
if (new.id is null) then
new.id = gen_id(gen_categories, 1);
RDB$SET_CONTEXT('USER_SESSION', 'LAST_INSERT_ID', new.id);
end!!
set term ; !!
commit;

INSERT INTO categories (name, parent_id) VALUES ('cat 1',NULL);
INSERT INTO categories (name, parent_id) VALUES ('cat 2',NULL);
INSERT INTO categories (name, parent_id) VALUES ('cat 3',NULL);
INSERT INTO categories (name, parent_id) VALUES ('cat 4',1);
INSERT INTO categories (name, parent_id) VALUES ('cat 5',1);
INSERT INTO categories (name, parent_id) VALUES ('cat 6',5);
INSERT INTO categories (name, parent_id) VALUES ('cat 7',5);

commit;
CREATE TABLE post_category
(
category_id INTEGER  NOT NULL,
post_id INTEGER  NOT NULL,
PRIMARY KEY (category_id, post_id),
CONSTRAINT FK_post_category_post FOREIGN KEY (post_id)
REFERENCES posts (id) ON DELETE CASCADE ON UPDATE NO ACTION,
CONSTRAINT FK_post_category_category FOREIGN KEY (category_id)
REFERENCES categories (id) ON DELETE CASCADE ON UPDATE NO ACTION
);
commit;
INSERT INTO post_category (category_id, post_id) VALUES (1,1);
INSERT INTO post_category (category_id, post_id) VALUES (2,1);
INSERT INTO post_category (category_id, post_id) VALUES (3,1);
INSERT INTO post_category (category_id, post_id) VALUES (4,2);
INSERT INTO post_category (category_id, post_id) VALUES (1,2);
INSERT INTO post_category (category_id, post_id) VALUES (1,3);

CREATE TABLE orders
(
key1 INTEGER  NOT NULL,
key2 INTEGER  NOT NULL,
name VARCHAR(128),
PRIMARY KEY (key1, key2)
);
commit;
INSERT INTO orders (key1,key2,name) VALUES (1,2,'order 12');
INSERT INTO orders (key1,key2,name) VALUES (1,3,'order 13');
INSERT INTO orders (key1,key2,name) VALUES (2,1,'order 21');
INSERT INTO orders (key1,key2,name) VALUES (2,2,'order 22');
commit;
CREATE TABLE items
(
id INTEGER  PRIMARY KEY  NOT NULL,
name VARCHAR(128),
col1 INTEGER  NOT NULL,
col2 INTEGER  NOT NULL,
CONSTRAINT item5_FK_order_item FOREIGN KEY (col1,col2)
REFERENCES orders (key1,key2) ON DELETE CASCADE ON UPDATE NO ACTION
);
commit;
create SEQUENCE gen_items;
alter SEQUENCE gen_items restart with 0;
set term !! ;
create trigger t_items for items before insert position 0
as begin
if (new.id is null) then
new.id = gen_id(gen_items, 1);
RDB$SET_CONTEXT('USER_SESSION', 'LAST_INSERT_ID', new.id);
end!!
set term ; !!
commit;

INSERT INTO items (name,col1,col2) VALUES ('item 1',1,2);
INSERT INTO items (name,col1,col2) VALUES ('item 2',1,2);
INSERT INTO items (name,col1,col2) VALUES ('item 3',1,3);
INSERT INTO items (name,col1,col2) VALUES ('item 4',2,2);
INSERT INTO items (name,col1,col2) VALUES ('item 5',2,2);
commit;

CREATE TABLE types
(
int_col INT  NOT NULL,
int_col2 INTEGER,
char_col CHAR(100)  NOT NULL,
char_col2 VARCHAR(100),
char_col3 BLOB SUB_TYPE TEXT,
float_col float(4,3)  NOT NULL,
float_col2 float,
blob_col BLOB,
numeric_col NUMERIC(5,2),
time TIMESTAMP,
bool_col integer  NOT NULL,
bool_col2 integer,
bit_col1 BIT,
bit_col2 BIT(32)
);
set term !! ;
create trigger ts_types for types before insert position 0
as begin
if (new.time is null) then
new.time = current_timestamp;
end!!
set term ; !!

set term !! ;
CREATE PROCEDURE LAST_INSERT_ID RETURNS (ID BIGINT) AS
BEGIN
id = RDB$GET_CONTEXT('USER_SESSION', 'LAST_INSERT_ID');
suspend;
END!!
set term ; !!
commit;