cubrid.sql 6.11 KB
Newer Older
Carsten Brandt committed
1 2 3 4 5
/**
 * This is the database schema for testing CUBRID support of Yii DAO and Active Record.
 * The database setup in config.php is required to perform then relevant tests:
 */

6 7 8 9 10 11 12 13 14 15 16 17 18 19
DROP TABLE IF EXISTS "composite_fk";
DROP TABLE IF EXISTS "order_item";
DROP TABLE IF EXISTS "order_item_with_null_fk";
DROP TABLE IF EXISTS "item";
DROP TABLE IF EXISTS "order";
DROP TABLE IF EXISTS "order_with_null_fk";
DROP TABLE IF EXISTS "category";
DROP TABLE IF EXISTS "customer";
DROP TABLE IF EXISTS "profile";
DROP TABLE IF EXISTS "null_values";
DROP TABLE IF EXISTS "type";
DROP TABLE IF EXISTS "constraints";

CREATE TABLE "constraints"
Carsten Brandt committed
20
(
21 22
  "id" integer not null,
  "field1" varchar(255)
Carsten Brandt committed
23 24 25
);


26 27 28 29
CREATE TABLE "profile" (
  "id" int(11) NOT NULL AUTO_INCREMENT,
  "description" varchar(128) NOT NULL,
  PRIMARY KEY ("id")
30 31
);

32 33 34 35 36 37 38 39
CREATE TABLE "customer" (
  "id" int(11) NOT NULL AUTO_INCREMENT,
  "email" varchar(128) NOT NULL,
  "name" varchar(128),
  "address" string,
  "status" int (11) DEFAULT 0,
  "profile_id" int(11),
  PRIMARY KEY ("id")
Carsten Brandt committed
40 41
);

42 43 44 45
CREATE TABLE "category" (
  "id" int(11) NOT NULL AUTO_INCREMENT,
  "name" varchar(128) NOT NULL,
  PRIMARY KEY ("id")
Carsten Brandt committed
46 47
);

48 49 50 51 52 53
CREATE TABLE "item" (
  "id" int(11) NOT NULL AUTO_INCREMENT,
  "name" varchar(128) NOT NULL,
  "category_id" int(11) NOT NULL,
  PRIMARY KEY ("id"),
  CONSTRAINT "FK_item_category_id" FOREIGN KEY ("category_id") REFERENCES "category" ("id") ON DELETE CASCADE
Carsten Brandt committed
54 55
);

56 57 58 59 60 61 62
CREATE TABLE "order" (
  "id" int(11) NOT NULL AUTO_INCREMENT,
  "customer_id" int(11) NOT NULL,
  "created_at" int(11) NOT NULL,
  "total" decimal(10,0) NOT NULL,
  PRIMARY KEY ("id"),
  CONSTRAINT "FK_order_customer_id" FOREIGN KEY ("customer_id") REFERENCES "customer" ("id") ON DELETE CASCADE
Carsten Brandt committed
63 64
);

65 66 67 68 69 70
CREATE TABLE "order_with_null_fk" (
  "id" int(11) NOT NULL AUTO_INCREMENT,
  "customer_id" int(11),
  "created_at" int(11) NOT NULL,
  "total" decimal(10,0) NOT NULL,
  PRIMARY KEY ("id")
71 72
);

73 74 75 76 77 78 79 80
CREATE TABLE "order_item" (
  "order_id" int(11) NOT NULL,
  "item_id" int(11) NOT NULL,
  "quantity" int(11) NOT NULL,
  "subtotal" decimal(10,0) NOT NULL,
  PRIMARY KEY ("order_id","item_id"),
  CONSTRAINT "FK_order_item_order_id" FOREIGN KEY ("order_id") REFERENCES "order" ("id") ON DELETE CASCADE,
  CONSTRAINT "FK_order_item_item_id" FOREIGN KEY ("item_id") REFERENCES "item" ("id") ON DELETE CASCADE
Carsten Brandt committed
81 82
);

83 84 85 86 87
CREATE TABLE "order_item_with_null_fk" (
  "order_id" int(11),
  "item_id" int(11),
  "quantity" int(11) NOT NULL,
  "subtotal" decimal(10,0) NOT NULL
88 89
);

90
CREATE TABLE null_values (
91 92 93 94 95
  "id" INT(11) NOT NULL AUTO_INCREMENT,
  "var1" INT NULL,
  "var2" INT NULL,
  "var3" INT DEFAULT NULL,
  "stringcol" VARCHAR (32) DEFAULT NULL,
96 97 98 99
  PRIMARY KEY (id)
);


100 101 102
CREATE TABLE "type" (
  "int_col" int(11) NOT NULL,
  "int_col2" int(11) DEFAULT '1',
Carsten Brandt committed
103
  "smallint_col" smallint DEFAULT '1',
104 105 106 107 108 109 110 111 112 113 114
  "char_col" char(100) NOT NULL,
  "char_col2" varchar(100) DEFAULT 'something',
  "char_col3" string,
  "enum_col" enum('a','B'),
  "float_col" double NOT NULL,
  "float_col2" double DEFAULT '1.23',
  "blob_col" blob,
  "numeric_col" decimal(5,2) DEFAULT '33.22',
  "time" timestamp NOT NULL DEFAULT '2002-01-01 00:00:00',
  "bool_col" tinyint NOT NULL,
  "bool_col2" tinyint DEFAULT '1',
115 116
  "ts_default" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "bit_col" BIT(8) NOT NULL DEFAULT b'10000010'
Carsten Brandt committed
117 118
);

119 120 121 122 123 124
CREATE TABLE "composite_fk" (
  "id" int(11) NOT NULL,
  "order_id" int(11) NOT NULL,
  "item_id" int(11) NOT NULL,
  PRIMARY KEY ("id"),
  CONSTRAINT "FK_composite_fk_order_item" FOREIGN KEY ("order_id","item_id") REFERENCES "order_item" ("order_id","item_id") ON DELETE CASCADE
125 126
);

127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163
INSERT INTO "profile" (description) VALUES ('profile customer 1');
INSERT INTO "profile" (description) VALUES ('profile customer 3');

INSERT INTO "customer" (email, name, address, status, profile_id) VALUES ('user1@example.com', 'user1', 'address1', 1, 1);
INSERT INTO "customer" (email, name, address, status) VALUES ('user2@example.com', 'user2', 'address2', 1);
INSERT INTO "customer" (email, name, address, status, profile_id) VALUES ('user3@example.com', 'user3', 'address3', 2, 2);

INSERT INTO "category" (name) VALUES ('Books');
INSERT INTO "category" (name) VALUES ('Movies');

INSERT INTO "item" (name, category_id) VALUES ('Agile Web Application Development with Yii1.1 and PHP5', 1);
INSERT INTO "item" (name, category_id) VALUES ('Yii 1.1 Application Development Cookbook', 1);
INSERT INTO "item" (name, category_id) VALUES ('Ice Age', 2);
INSERT INTO "item" (name, category_id) VALUES ('Toy Story', 2);
INSERT INTO "item" (name, category_id) VALUES ('Cars', 2);

INSERT INTO "order" (customer_id, created_at, total) VALUES (1, 1325282384, 110.0);
INSERT INTO "order" (customer_id, created_at, total) VALUES (2, 1325334482, 33.0);
INSERT INTO "order" (customer_id, created_at, total) VALUES (2, 1325502201, 40.0);

INSERT INTO "order_with_null_fk" (customer_id, created_at, total) VALUES (1, 1325282384, 110.0);
INSERT INTO "order_with_null_fk" (customer_id, created_at, total) VALUES (2, 1325334482, 33.0);
INSERT INTO "order_with_null_fk" (customer_id, created_at, total) VALUES (2, 1325502201, 40.0);

INSERT INTO "order_item" (order_id, item_id, quantity, subtotal) VALUES (1, 1, 1, 30.0);
INSERT INTO "order_item" (order_id, item_id, quantity, subtotal) VALUES (1, 2, 2, 40.0);
INSERT INTO "order_item" (order_id, item_id, quantity, subtotal) VALUES (2, 4, 1, 10.0);
INSERT INTO "order_item" (order_id, item_id, quantity, subtotal) VALUES (2, 5, 1, 15.0);
INSERT INTO "order_item" (order_id, item_id, quantity, subtotal) VALUES (2, 3, 1, 8.0);
INSERT INTO "order_item" (order_id, item_id, quantity, subtotal) VALUES (3, 2, 1, 40.0);

INSERT INTO "order_item_with_null_fk" (order_id, item_id, quantity, subtotal) VALUES (1, 1, 1, 30.0);
INSERT INTO "order_item_with_null_fk" (order_id, item_id, quantity, subtotal) VALUES (1, 2, 2, 40.0);
INSERT INTO "order_item_with_null_fk" (order_id, item_id, quantity, subtotal) VALUES (2, 4, 1, 10.0);
INSERT INTO "order_item_with_null_fk" (order_id, item_id, quantity, subtotal) VALUES (2, 5, 1, 15.0);
INSERT INTO "order_item_with_null_fk" (order_id, item_id, quantity, subtotal) VALUES (2, 3, 1, 8.0);
INSERT INTO "order_item_with_null_fk" (order_id, item_id, quantity, subtotal) VALUES (3, 2, 1, 40.0);