Ok i came accross this weird issue the other day... i created a
schema.yml file
~~~~~~~~~~~~~~~~~
propel:
_attributes: { package: lib.model.ams,
defaultIdMethod: native }
ams_company:
id: ~
company: { type: varchar(255),
required: true }
created_at: ~
updated_at: ~
ams_type:
id: ~
type: { type: varchar(255),
required: true }
created_at: ~
updated_at: ~
ams_transaction_type:
id: ~
type: { type: varchar(255),
required: true }
created_at: ~
updated_at: ~
ams_reporting_close:
id: ~
close_date: { type: timestamp,
required: true }
ams_company_id: { type: integer,
foreignTable: ams_company, foreignReference: id, required: true }
sf_guard_user_id: { type: integer,
foreignTable: sf_guard_user, foreignReference: id, required: true }
created_at: ~
updated_at: ~
ams_transaction_group:
id: ~
ams_type_id: { type: integer,
foreignTable: ams_type, foreignReference: id, required: true }
ams_company_id: { type: integer,
foreignTable: ams_company, foreignReference: id, required: true }
deal_id: { type: integer, required:
true }
generated_name: { type: varchar(255),
required: true }
default_transaction_date: { type: date, required:
true }
post_date: { type: timestamp,
required: true }
third_party_name: { type: varchar(255),
required: true }
sf_guard_user_id: { type: integer,
foreignTable: sf_guard_user, foreignReference: id, required: true }
created_at: ~
updated_at: ~
_uniques:
ams_type_id_ams_transaction_group_id: [ams_type_id, id]
ams_asset:
id: ~
domain_id: { type: integer, required:
true }
domain_name: { type: varchar(255),
required: true }
ams_company_id: { type: integer,
foreignTable: ams_company, foreignReference: id, required: true }
post_date: { type: timestamp,
required: true }
sf_guard_user_id: { type: integer,
foreignTable: sf_guard_user, foreignReference: id, required: true }
created_at: ~
updated_at: ~
ams_transaction:
id: ~
ams_company_id: { type: integer,
foreignTable: ams_company, foreignReference: id, required: true }
ams_type_id: { type: integer, required:
true }
ams_transaction_group_id: { type: integer, required:
true }
ams_asset_id: { type: integer,
foreignTable: ams_asset, foreignReference: id, required: true }
domain_name: { type: varchar(255),
required: true }
transaction_date: { type: date, required:
true }
price: { type: double, required:
true }
asset_change: { type: double, required:
true }
asset_total: { type: double, required:
true }
post_date: { type: timestamp,
required: true }
sf_guard_user_id: { type: integer,
foreignTable: sf_guard_user, foreignReference: id, required: true }
reversed_by_ams_transaction_id: { type: integer,
foreignTable: ams_transaction, foreignReference: id, required: false }
is_reversal: { type: boolean }
is_current: { type: boolean }
reporting_close_id: { type: boolean }
created_at: ~
updated_at: ~
_foreignKeys:
ams_transaction_group_fk:
foreignTable: ams_transaction_group
onDelete: cascade
references:
- { local: ams_type_id, foreign: id }
- { local: ams_transaction_group_id, foreign: id }
Then i generated the sql based on that...
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
# This is a fix for InnoDB in MySQL >= 4.1.x
# It "suspends judgement" for fkey relationships until are tables are
set.
SET FOREIGN_KEY_CHECKS = 0;
#-----------------------------------------------------------------------------
#-- ams_company
#-----------------------------------------------------------------------------
DROP TABLE IF EXISTS `ams_company`;
CREATE TABLE `ams_company`
(
`id` INTEGER NOT NULL AUTO_INCREMENT,
`company` VARCHAR(255) NOT NULL,
`created_at` DATETIME,
`updated_at` DATETIME,
PRIMARY KEY (`id`)
)Type=InnoDB;
#-----------------------------------------------------------------------------
#-- ams_type
#-----------------------------------------------------------------------------
DROP TABLE IF EXISTS `ams_type`;
CREATE TABLE `ams_type`
(
`id` INTEGER NOT NULL AUTO_INCREMENT,
`type` VARCHAR(255) NOT NULL,
`created_at` DATETIME,
`updated_at` DATETIME,
PRIMARY KEY (`id`)
)Type=InnoDB;
#-----------------------------------------------------------------------------
#-- ams_transaction_type
#-----------------------------------------------------------------------------
DROP TABLE IF EXISTS `ams_transaction_type`;
CREATE TABLE `ams_transaction_type`
(
`id` INTEGER NOT NULL AUTO_INCREMENT,
`type` VARCHAR(255) NOT NULL,
`created_at` DATETIME,
`updated_at` DATETIME,
PRIMARY KEY (`id`)
)Type=InnoDB;
#-----------------------------------------------------------------------------
#-- ams_reporting_close
#-----------------------------------------------------------------------------
DROP TABLE IF EXISTS `ams_reporting_close`;
CREATE TABLE `ams_reporting_close`
(
`id` INTEGER NOT NULL AUTO_INCREMENT,
`close_date` DATETIME NOT NULL,
`ams_company_id` INTEGER NOT NULL,
`sf_guard_user_id` INTEGER NOT NULL,
`created_at` DATETIME,
`updated_at` DATETIME,
PRIMARY KEY (`id`),
INDEX `ams_reporting_close_FI_1` (`ams_company_id`),
CONSTRAINT `ams_reporting_close_FK_1`
FOREIGN KEY (`ams_company_id`)
REFERENCES `ams_company` (`id`),
INDEX `ams_reporting_close_FI_2` (`sf_guard_user_id`),
CONSTRAINT `ams_reporting_close_FK_2`
FOREIGN KEY (`sf_guard_user_id`)
REFERENCES `sf_guard_user` (`id`)
)Type=InnoDB;
#-----------------------------------------------------------------------------
#-- ams_transaction_group
#-----------------------------------------------------------------------------
DROP TABLE IF EXISTS `ams_transaction_group`;
CREATE TABLE `ams_transaction_group`
(
`id` INTEGER NOT NULL AUTO_INCREMENT,
`ams_type_id` INTEGER NOT NULL,
`ams_company_id` INTEGER NOT NULL,
`deal_id` INTEGER NOT NULL,
`generated_name` VARCHAR(255) NOT NULL,
`default_transaction_date` DATE NOT NULL,
`post_date` DATETIME NOT NULL,
`third_party_name` VARCHAR(255) NOT NULL,
`sf_guard_user_id` INTEGER NOT NULL,
`created_at` DATETIME,
`updated_at` DATETIME,
PRIMARY KEY (`id`),
UNIQUE KEY `ams_type_id_ams_transaction_group_id` (`ams_type_id`,
`id`),
INDEX `I_referenced_ams_transaction_group_fk_1` (`id`,`id`),
CONSTRAINT `ams_transaction_group_FK_1`
FOREIGN KEY (`ams_type_id`)
REFERENCES `ams_type` (`id`),
INDEX `ams_transaction_group_FI_2` (`ams_company_id`),
CONSTRAINT `ams_transaction_group_FK_2`
FOREIGN KEY (`ams_company_id`)
REFERENCES `ams_company` (`id`),
INDEX `ams_transaction_group_FI_3` (`sf_guard_user_id`),
CONSTRAINT `ams_transaction_group_FK_3`
FOREIGN KEY (`sf_guard_user_id`)
REFERENCES `sf_guard_user` (`id`)
)Type=InnoDB;
#-----------------------------------------------------------------------------
#-- ams_asset
#-----------------------------------------------------------------------------
DROP TABLE IF EXISTS `ams_asset`;
CREATE TABLE `ams_asset`
(
`id` INTEGER NOT NULL AUTO_INCREMENT,
`domain_id` INTEGER NOT NULL,
`domain_name` VARCHAR(255) NOT NULL,
`ams_company_id` INTEGER NOT NULL,
`post_date` DATETIME NOT NULL,
`sf_guard_user_id` INTEGER NOT NULL,
`created_at` DATETIME,
`updated_at` DATETIME,
PRIMARY KEY (`id`),
INDEX `ams_asset_FI_1` (`ams_company_id`),
CONSTRAINT `ams_asset_FK_1`
FOREIGN KEY (`ams_company_id`)
REFERENCES `ams_company` (`id`),
INDEX `ams_asset_FI_2` (`sf_guard_user_id`),
CONSTRAINT `ams_asset_FK_2`
FOREIGN KEY (`sf_guard_user_id`)
REFERENCES `sf_guard_user` (`id`)
)Type=InnoDB;
#-----------------------------------------------------------------------------
#-- ams_transaction
#-----------------------------------------------------------------------------
DROP TABLE IF EXISTS `ams_transaction`;
CREATE TABLE `ams_transaction`
(
`id` INTEGER NOT NULL AUTO_INCREMENT,
`ams_company_id` INTEGER NOT NULL,
`ams_type_id` INTEGER NOT NULL,
`ams_transaction_group_id` INTEGER NOT NULL,
`ams_asset_id` INTEGER NOT NULL,
`domain_name` VARCHAR(255) NOT NULL,
`transaction_date` DATE NOT NULL,
`price` DOUBLE NOT NULL,
`asset_change` DOUBLE NOT NULL,
`asset_total` DOUBLE NOT NULL,
`post_date` DATETIME NOT NULL,
`sf_guard_user_id` INTEGER NOT NULL,
`reversed_by_ams_transaction_id` INTEGER,
`is_reversal` TINYINT,
`is_current` TINYINT,
`reporting_close_id` TINYINT,
`created_at` DATETIME,
`updated_at` DATETIME,
PRIMARY KEY (`id`),
INDEX `ams_transaction_FI_1` (`ams_company_id`),
CONSTRAINT `ams_transaction_FK_1`
FOREIGN KEY (`ams_company_id`)
REFERENCES `ams_company` (`id`),
INDEX `ams_transaction_FI_2` (`ams_asset_id`),
CONSTRAINT `ams_transaction_FK_2`
FOREIGN KEY (`ams_asset_id`)
REFERENCES `ams_asset` (`id`),
INDEX `ams_transaction_FI_3` (`sf_guard_user_id`),
CONSTRAINT `ams_transaction_FK_3`
FOREIGN KEY (`sf_guard_user_id`)
REFERENCES `sf_guard_user` (`id`),
INDEX `ams_transaction_FI_4` (`reversed_by_ams_transaction_id`),
CONSTRAINT `ams_transaction_FK_4`
FOREIGN KEY (`reversed_by_ams_transaction_id`)
REFERENCES `ams_transaction` (`id`),
INDEX `FI__transaction_group_fk`
(`ams_type_id`,`ams_transaction_group_id`),
CONSTRAINT `ams_transaction_group_fk`
FOREIGN KEY (`ams_type_id`,`ams_transaction_group_id`)
REFERENCES `ams_transaction_group` (`id`,`id`)
ON DELETE CASCADE
)Type=InnoDB;
# This restores the fkey checks, after having unset them earlier
SET FOREIGN_KEY_CHECKS = 1;
Which game me all kinds of errors!!
I had to modify the sql in a few places to get it to work... below is
the modified sql file...
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
# This is a fix for InnoDB in MySQL >= 4.1.x
# It "suspends judgement" for fkey relationships until are tables are
set.
SET FOREIGN_KEY_CHECKS = 0;
#-----------------------------------------------------------------------------
#-- ams_asset
#-----------------------------------------------------------------------------
DROP TABLE IF EXISTS `ams_asset`;
CREATE TABLE `ams_asset`
(
`id` INTEGER NOT NULL AUTO_INCREMENT,
`domain_id` INTEGER NOT NULL,
`domain_name` VARCHAR(255) NOT NULL,
`ams_company_id` INTEGER NOT NULL,
`post_date` DATETIME NOT NULL,
`sf_guard_user_id` INTEGER NOT NULL,
`created_at` DATETIME,
`updated_at` DATETIME,
PRIMARY KEY (`id`),
INDEX `ams_asset_FI_1` (`ams_company_id`),
CONSTRAINT `ams_asset_FK_1`
FOREIGN KEY (`ams_company_id`)
REFERENCES `ams_company` (`id`),
INDEX `ams_asset_FI_2` (`sf_guard_user_id`),
CONSTRAINT `ams_asset_FK_2`
FOREIGN KEY (`sf_guard_user_id`)
REFERENCES `sf_guard_user` (`id`)
)Type=InnoDB;
#-----------------------------------------------------------------------------
#-- ams_company
#-----------------------------------------------------------------------------
DROP TABLE IF EXISTS `ams_company`;
CREATE TABLE `ams_company`
(
`id` INTEGER NOT NULL AUTO_INCREMENT,
`company` VARCHAR(255) NOT NULL,
`created_at` DATETIME,
`updated_at` DATETIME,
PRIMARY KEY (`id`)
)Type=InnoDB;
#-----------------------------------------------------------------------------
#-- ams_type
#-----------------------------------------------------------------------------
DROP TABLE IF EXISTS `ams_type`;
CREATE TABLE `ams_type`
(
`id` INTEGER NOT NULL AUTO_INCREMENT,
`type` VARCHAR(255) NOT NULL,
`created_at` DATETIME,
`updated_at` DATETIME,
PRIMARY KEY (`id`)
)Type=InnoDB;
#-----------------------------------------------------------------------------
#-- ams_transaction_type
#-----------------------------------------------------------------------------
DROP TABLE IF EXISTS `ams_transaction_type`;
CREATE TABLE `ams_transaction_type`
(
`id` INTEGER NOT NULL AUTO_INCREMENT,
`type` VARCHAR(255) NOT NULL,
`created_at` DATETIME,
`updated_at` DATETIME,
PRIMARY KEY (`id`)
)Type=InnoDB;
#-----------------------------------------------------------------------------
#-- ams_reporting_close
#-----------------------------------------------------------------------------
DROP TABLE IF EXISTS `ams_reporting_close`;
CREATE TABLE `ams_reporting_close`
(
`id` INTEGER NOT NULL AUTO_INCREMENT,
`close_date` DATETIME NOT NULL,
`ams_company_id` INTEGER NOT NULL,
`sf_guard_user_id` INTEGER NOT NULL,
`created_at` DATETIME,
`updated_at` DATETIME,
PRIMARY KEY (`id`),
INDEX `ams_reporting_close_FI_1` (`ams_company_id`),
CONSTRAINT `ams_reporting_close_FK_1`
FOREIGN KEY (`ams_company_id`)
REFERENCES `ams_company` (`id`),
INDEX `ams_reporting_close_FI_2` (`sf_guard_user_id`),
CONSTRAINT `ams_reporting_close_FK_2`
FOREIGN KEY (`sf_guard_user_id`)
REFERENCES `sf_guard_user` (`id`)
)Type=InnoDB;
#-----------------------------------------------------------------------------
#-- ams_transaction_group
#-----------------------------------------------------------------------------
DROP TABLE IF EXISTS `ams_transaction_group`;
CREATE TABLE `ams_transaction_group`
(
`id` INTEGER NOT NULL AUTO_INCREMENT,
`ams_type_id` INTEGER NOT NULL,
`ams_company_id` INTEGER NOT NULL,
`deal_id` INTEGER NOT NULL,
`generated_name` VARCHAR(255) NOT NULL,
`default_transaction_date` DATE NOT NULL,
`post_date` DATETIME NOT NULL,
`third_party_name` VARCHAR(255) NOT NULL,
`sf_guard_user_id` INTEGER NOT NULL,
`created_at` DATETIME,
`updated_at` DATETIME,
PRIMARY KEY (`id`),
UNIQUE KEY `ams_type_id_ams_transaction_group_id` (`ams_type_id`,
`id`),
INDEX `I_referenced_ams_transaction_group_fk_1` (`ams_type_id`,`id`),
CONSTRAINT `ams_transaction_group_FK_1`
FOREIGN KEY (`ams_type_id`)
REFERENCES `ams_type` (`id`),
INDEX `ams_transaction_group_FI_2` (`ams_company_id`),
CONSTRAINT `ams_transaction_group_FK_2`
FOREIGN KEY (`ams_company_id`)
REFERENCES `ams_company` (`id`),
INDEX `ams_transaction_group_FI_3` (`sf_guard_user_id`),
CONSTRAINT `ams_transaction_group_FK_3`
FOREIGN KEY (`sf_guard_user_id`)
REFERENCES `sf_guard_user` (`id`)
)Type=InnoDB;
#-----------------------------------------------------------------------------
#-- ams_transaction
#-----------------------------------------------------------------------------
DROP TABLE IF EXISTS `ams_transaction`;
CREATE TABLE `ams_transaction`
(
`id` INTEGER NOT NULL AUTO_INCREMENT,
`ams_company_id` INTEGER NOT NULL,
`ams_type_id` INTEGER NOT NULL,
`ams_transaction_group_id` INTEGER NOT NULL,
`ams_asset_id` INTEGER NOT NULL,
`domain_name` VARCHAR(255) NOT NULL,
`transaction_date` DATE NOT NULL,
`price` DOUBLE NOT NULL,
`asset_change` DOUBLE NOT NULL,
`asset_total` DOUBLE NOT NULL,
`post_date` DATETIME NOT NULL,
`sf_guard_user_id` INTEGER NOT NULL,
`reversed_by_ams_transaction_id` INTEGER,
`is_reversal` TINYINT,
`is_current` TINYINT,
`reporting_close_id` TINYINT,
`created_at` DATETIME,
`updated_at` DATETIME,
PRIMARY KEY (`id`),
INDEX `ams_transaction_FI_1` (`ams_company_id`),
CONSTRAINT `ams_transaction_FK_1`
FOREIGN KEY (`ams_company_id`)
REFERENCES `ams_company` (`id`),
INDEX `ams_transaction_FI_2` (`ams_asset_id`),
CONSTRAINT `ams_transaction_FK_2`
FOREIGN KEY (`ams_asset_id`)
REFERENCES `ams_asset` (`id`),
INDEX `ams_transaction_FI_3` (`sf_guard_user_id`),
CONSTRAINT `ams_transaction_FK_3`
FOREIGN KEY (`sf_guard_user_id`)
REFERENCES `sf_guard_user` (`id`),
INDEX `ams_transaction_FI_4` (`reversed_by_ams_transaction_id`),
CONSTRAINT `ams_transaction_FK_4`
FOREIGN KEY (`reversed_by_ams_transaction_id`)
REFERENCES `ams_transaction` (`id`),
INDEX `FI__transaction_group_fk`
(`ams_type_id`,`ams_transaction_group_id`),
CONSTRAINT `ams_transaction_group_fk`
FOREIGN KEY (`ams_type_id`,`ams_transaction_group_id`)
REFERENCES `ams_transaction_group` (`ams_type_id`,`id`)
ON DELETE CASCADE
)Type=InnoDB;
# This restores the fkey checks, after having unset them earlier
SET FOREIGN_KEY_CHECKS = 1;
So my question is... whats the deal with Propel sql generator?? is
this a bug or is it an issue with my schema??
--
If you want to report a vulnerability issue on symfony, please send it to
security at symfony-project.com
You received this message because you are subscribed to the Google
Groups "symfony users" group.
To post to this group, send email to symfony-users@googlegroups.com
To unsubscribe from this group, send email to
symfony-users+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/symfony-users?hl=en
