Yii book Chapter 6

Archives Agile Web Application Development with Yii1.1 and
1

Noticed that the pdf version of the code examples incorrectly converts the ` symbol into ’ - something that mysql doesn’t like.

So if you’re trying to create the tables / relationships / data & getting errors, consider this:




CREATE TABLE IF NOT EXISTS `tbl_issue`

(

`id` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,

`name` varchar(256) NOT NULL,

`description` varchar(2000),

`project_id` INTEGER,

`type_id` INTEGER,

`status_id` INTEGER,

`owner_id` INTEGER,

`requester_id` INTEGER,

`create_time` DATETIME,

`create_user_id` INTEGER,

`update_time` DATETIME,

`update_user_id` INTEGER

) ENGINE = InnoDB

;

CREATE TABLE IF NOT EXISTS `tbl_user`

(

`id` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,

`email` Varchar(256) NOT NULL,

`username` Varchar(256),

`password` Varchar(256),

`last_login_time` Datetime,

`create_time` DATETIME,

`create_user_id` INTEGER,

`update_time` DATETIME,

`update_user_id` INTEGER

) ENGINE = InnoDB

;

CREATE TABLE IF NOT EXISTS `tbl_project_user_assignment`

(

`project_id` Int(11) NOT NULL,

`user_id` Int(11) NOT NULL,

`create_time` DATETIME,

`create_user_id` INTEGER,

`update_time` DATETIME,

`update_user_id` INTEGER,

PRIMARY KEY (`project_id`,`user_id`)

) ENGINE = InnoDB

;

-- The Relationships

ALTER TABLE `tbl_issue` ADD CONSTRAINT `FK_issue_project` FOREIGN KEY (`project_id`) REFERENCES `tbl_project` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT;

ALTER TABLE `tbl_issue` ADD CONSTRAINT `FK_issue_owner` FOREIGN KEY (`owner_id`) REFERENCES `tbl_user` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT;

ALTER TABLE `tbl_issue` ADD CONSTRAINT `FK_issue_requester` FOREIGN KEY (`requester_id`) REFERENCES `tbl_user` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT;

ALTER TABLE `tbl_project_user_assignment` ADD CONSTRAINT `FK_project_user` FOREIGN KEY (`project_id`) REFERENCES `tbl_project` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT;

ALTER TABLE `tbl_project_user_assignment` ADD CONSTRAINT `FK_user_project` FOREIGN KEY (`user_id`) REFERENCES `tbl_user` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT;

-- Insert some seed data so we can just begin using the database

INSERT INTO `tbl_user`

(`email`, `username`, `password`)

VALUES

('test1@notanaddress.com','Test_User_One', MD5('test1')),

('test2@notanaddress.com','Test_User_Two', MD5('test2'))

;

Also note - if you’re having problems with the CONSTRAINT statements (MySQL said: #1005 - Can’t create table ‘trackstar_dev.#sql-378_123’ (errno: 150), take a close look at tbl_project - make sure it is an INNODB type table.

2

Thanks a lot!

Took me a while before I realized I needed this, too.




INSERT INTO `tbl_project_user_assignment`

(`project_id`, `user_id`)

VALUES

(1, 1),

(1, 2)

;
3

Oooooooooooooooooooooooooh! Thanks!

The explicit reason why it have to be InnoDB guys, is because MySQL default is ISAM and MyISAM both of these do not support foreign keys.

In MySQL you can change your database engine type, you can even change it down to the table level, so each table change have a different type. There are like like 5 types so far, ISAM, MyISAM, HEAP, and two custom InnoDB and Berkely.

4

You can copy all the SQL statements from the PDF and paste it in your favorite editor, find and replace ’ with `, except for the values in the insert statement:

INSERT INTO tbl_user

(email, username, password)

VALUES

('test1@notanaddress.com’,'Test_User_One’, MD5('test1’)),

('test2@notanaddress.com’,'Test_User_Two’, MD5('test2’))

;

5

I can’t make only ‘ALTER’ query to set relationships between tables.

All tables use INNODB, but i get always the following error:

#1005 - Can’t create table ‘trackstar_dev.#sql-8a9_10c’ (errno: 121)

I resolve using:




CREATE TABLE tbl_project

(

id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,

name VARCHAR(128),

description TEXT,

create_time DATETIME,

create_user_id INTEGER,

update_time DATETIME,

update_user_id INTEGER

) ENGINE = InnoDB

;







CREATE TABLE IF NOT EXISTS tbl_issue

(

id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,

name varchar(256) NOT NULL,

description varchar(2000),

project_id INTEGER,

type_id INTEGER,

status_id INTEGER,

owner_id INTEGER,

requester_id INTEGER,

create_time DATETIME,

create_user_id INTEGER,

update_time DATETIME,

update_user_id INTEGER

) ENGINE = InnoDB

;

CREATE TABLE IF NOT EXISTS tbl_user

(

id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,

email Varchar(256) NOT NULL,

username Varchar(256),

password Varchar(256),

last_login_time Datetime,

create_time DATETIME,

create_user_id INTEGER,

update_time DATETIME,

update_user_id INTEGER

) ENGINE = InnoDB

;

CREATE TABLE IF NOT EXISTS tbl_project_user_assignment

(

project_id Int(11) NOT NULL,

user_id Int(11) NOT NULL,

create_time DATETIME,

create_user_id INTEGER,

update_time DATETIME,

update_user_id INTEGER,

PRIMARY KEY (project_id,user_id)

) ENGINE = InnoDB

;

-- The Relationships

ALTER TABLE `tbl_issue` ADD CONSTRAINT `FK_issue_project` FOREIGN KEY (`project_id`) REFERENCES `tbl_project` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT;

ALTER TABLE `tbl_issue` ADD CONSTRAINT `FK_issue_owner` FOREIGN KEY (`owner_id`) REFERENCES `tbl_user` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT;

ALTER TABLE `tbl_issue` ADD CONSTRAINT `FK_issue_requester` FOREIGN KEY (`requester_id`) REFERENCES `tbl_user` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT;

ALTER TABLE `tbl_project_user_assignment` ADD CONSTRAINT `FK_project_user` FOREIGN KEY (`project_id`) REFERENCES `tbl_project` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT;

ALTER TABLE `tbl_project_user_assignment` ADD CONSTRAINT `FK_user_project` FOREIGN KEY (`user_id`) REFERENCES `tbl_user` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT;

-- Insert some seed data so we can just begin using the database

INSERT INTO `tbl_user`

(`email`, `username`, `password`)

VALUES

('test1@notanaddress.com','Test_User_One', MD5('test1')),

('test2@notanaddress.com','Test_User_Two', MD5('test2'))

;