This forum is in READ-ONLY mode.
You can look around, but if you want to ask a new question, please use the new forum.
Home » support » General discussion » database design problem
database design problem [message #97283] Tue, 13 April 2010 20:45 Go to next message
websku
Messages: 11
Registered: March 2010
Junior Member
Hello,

What's the solution if doctrine does not generate correct foreign keys for my database? How can I skip build-sql and build-model from the schema?

Thank you in advance!

[Updated on: Tue, 13 April 2010 20:46]

Re: database design problem [message #97290 is a reply to message #97283 ] Tue, 13 April 2010 22:44 Go to previous messageGo to next message
hsostock  is currently offline hsostock
Messages: 96
Registered: November 2009
Member
Hi websku,

If doctrine isn't correctly generating the foreign keys, it's possible there might be a problem with how you specified your schema. If you want to post your schema.yml here, I can take a quick look to see if that's what's causing the issue.

Thanks,
Re: database design problem [message #97292 is a reply to message #97283 ] Tue, 13 April 2010 23:08 Go to previous messageGo to next message
websku
Messages: 11
Registered: March 2010
Junior Member
this is the schema and I've also attached a print screen with database diagram.

Client:
  columns:
    id:
      type: integer(4)
      primary: true
      unsigned: true
      notnull: true
      autoincrement: true
    email:
      type: string(45)
      unique: true
      notnull: true
    password:
      type: string(255)

ClientSetting:
  tableName: client_setting
  columns:
    client_id:
      type: integer(4)
      primary: true
      unsigned: true
      notnull: true
    newsletter:
      type: integer(1)
      unsigned: true
  relations:
    Client:
      class: Client
      local: client_id
      foreign: id
      foreignAlias: ClientSetting
      onDelete: cascade
      onUpdate: cascade

Event:
  columns:
    id:
      type: integer(4)
      primary: true
      unsigned: true
      notnull: true
      autoincrement: true
    client_id:
      type: integer(4)
      primary: true
      unsigned: true
      notnull: true
    event_type_id:
      type: integer(1)
      unsigned: true
      notnull: true
    name:
      type: string(255)
    start_date:
      type: timestamp
    details:
      type: clob(65535)
    host:
      type: string(255)
    url:
      type: string(255)
    confirm:
      type: enum
      values: ['auto','manual','email']
    private:
      type: integer(1)
      unsigned: true
  relations:
    Client:
      class: Client
      local: client_id
      foreign: id
      foreignAlias: Events
      onDelete: cascade
      onUpdate: cascade
    EventType:
      class: EventType
      local: event_type_id
      foreign: id
      foreignAlias: Event
      onDelete: cascade
      onUpdate: cascade

Participant:
  columns:
    id:
      type: integer(8)
      primary: true
      unsigned: true
      notnull: true
      autoincrement: true
    confirmed:
      type: integer(1)
      unsigned: true
    first_name:
      type: string(45)
    last_name:
      type: string(45)
    email:
      type: string(45)
    registration_time:
      type: timestamp

EventType:
  tableName: event_type
  columns:
    id:
      type: integer(1)
      primary: true
      unsigned: true
      notnull: true
      autoincrement: true
    type:
      type: string(255)

EventDesign:
  tableName: event_design
  columns:
    event_id:
      type: integer(4)
      unique: true
      unsigned: true
      notnull: true
    background:
      type: string(255)
    header:
      type: clob(65535)
    footer:
      type: clob(65535)
    logo:
      type: string(255)
  relations:
    Event:
      class: Event
      local: event_id
      foreign: id
      foreignAlias: EventDesign
      onDelete: cascade
      onUpdate: cascade

Form:
  columns:
    id:
      type: integer(4)
      primary: true
      unsigned: true
      notnull: true
      autoincrement: true
    event_id:
      type: integer(4)
      primary: true
      unsigned: true
      notnull: true
    title:
      type: string(255)
    type:
      type: enum
      values: ['before','after']
    description:
      type: string(255)
    submit_label:
      type: string(45)
  relations:
    Event:
      class: Event
      local: event_id
      foreign: id
      foreignAlias: Forms
      onDelete: cascade
      onUpdate: cascade

FormRespond:
  tableName: form_respond
  columns:
    id:
      type: integer(8)
      primary: true
      unsigned: true
      notnull: true
      autoincrement: true
    form_question_id:
      type: integer(8)
      primary: true
      unsigned: true
      notnull: true
    participant_id:
      type: integer(8)
      primary: true
      unsigned: true
      notnull: true
    answer:
      type: clob(65535)
  relations:
    FormQuestion:
      class: FormQuestion
      local: form_question_id
      foreign: id
      foreignAlias: FormResponds
      onDelete: cascade
      onUpdate: cascade
    Participant:
      class: Participant
      local: participant_id
      foreign: id
      foreignAlias: FormResponds
      onDelete: cascade
      onUpdate: cascade

FormQuestion:
  tableName: form_question
  columns:
    id:
      type: integer(8)
      primary: true
      unsigned: true
      notnull: true
      autoincrement: true
    form_id:
      type: integer(4)
      primary: true
      unsigned: true
      notnull: true
    title:
      type: string(255)
    type:
      type: string(45)
    order_no:
      type: integer(1)
    instruction:
      type: string(255)
    error_message:
      type: string(255)
    required:
      type: integer(1)
      unsigned: true
    options:
      type: clob(65535)
    multiple_allowed:
      type: integer(1)
      unsigned: true
    default_value:
      type: clob(65535)
    specify_allowed:
      type: integer(1)
      unsigned: true
    specify_label:
      type: string(100)
  relations:
    Form:
      class: Form
      local: form_id
      foreign: id
      foreignAlias: FormQuestions
      onDelete: cascade
      onUpdate: cascade

ClientInfo:
  tableName: client_info
  columns:
    client_id:
      type: integer(4)
      primary: true
      unsigned: true
      notnull: true
    company:
      type: string(45)
    first_name:
      type: string(45)
    last_name:
      type: string(45)
    phone_1:
      type: string(25)
    phone_2:
      type: string(25)
    logo:
      type: string(45)
  relations:
    Client:
      class: Client
      local: client_id
      foreign: id
      foreignAlias: ClientInfo
      onDelete: cascade
      onUpdate: cascade

Newsletter:
  columns:
    id:
      type: integer(4)
      primary: true
      unsigned: true
      notnull: true
      autoincrement: true
    email:
      type: string(45)
      unique: true

Re: database design problem [message #97296 is a reply to message #97292 ] Wed, 14 April 2010 01:06 Go to previous messageGo to next message
hsostock  is currently offline hsostock
Messages: 96
Registered: November 2009
Member
Hey websku,

When you run the doctrine:build-sql task, it does not generate the sql based on your schema, instead it is based on your current model (i.e. what is currently in lib/model/doctrine). So, if you then change your schema and run doctrine:generate-sql, the changes will not show up in the generated sql file until you rebuild your models.

Hope this helps,

Hale
Re: database design problem [message #97308 is a reply to message #97296 ] Wed, 14 April 2010 06:56 Go to previous messageGo to next message
websku
Messages: 11
Registered: March 2010
Junior Member
I know that, I've deleted my models from lib/model before generating the sql.

After deleting, I've run again doctrine:build-model. What it's very frustrating it's that in the model it creates a one:one relation for client_setting but the alter to add foreign key is not included in the sql.

This is just an example of what is goes wrong.

Thank you!

Re: database design problem [message #97312 is a reply to message #97308 ] Wed, 14 April 2010 08:01 Go to previous messageGo to next message
hsostock  is currently offline hsostock
Messages: 96
Registered: November 2009
Member
Alright, I will take a look and see if I can reproduce your issue and find a solution for you. What version of Symfony/mysql/php are you running?

Thanks,

Hale
Re: database design problem [message #97323 is a reply to message #97312 ] Wed, 14 April 2010 10:56 Go to previous message
websku
Messages: 11
Registered: March 2010
Junior Member
Symfony: 1.4.4
Mysql: 5.0.51b
Php: 5.2.6

I even tried to simplify database schema to only Client and Client_setting table and to run build-model and build-sql but in the sql is not created. And this is really strange!
Previous Topic:Symfony data import and export issue? use excel or other
Next Topic:problem withYAML
Goto Forum:
  

powered by FUDforum - copyright ©2001-2004 FUD Forum Bulletin Board Software