Don't like ads? PRO users don't see any ads ;-)
Public Pastes
Guest

Untitled

By: a guest on May 14th, 2012  |  syntax: None  |  size: 3.99 KB  |  hits: 13  |  expires: Never
download  |  raw  |  embed  |  report abuse  |  print
Text below is selected. Please press Ctrl+C to copy to your clipboard. (⌘+C on Mac)
  1. ## SCHEMA
  2.  
  3. Character:
  4.   tableName: characters
  5.   columns:
  6.     id:
  7.       type: integer(2)
  8.       primary: true
  9.       unsigned: true
  10.       autoincrement: true
  11.     name:
  12.       type: string(255)
  13.       notnull: true
  14.       unique: true
  15.     rank:
  16.       type: integer(2)
  17.       unsigned: true
  18.     level:
  19.       type: integer(2)
  20.       unsigned: true
  21.     race:
  22.       type: enum
  23.       values: [Human, Dwarf, NightElf, Gnome, Draenei, Worgen, Orc, Undead, Tauren, Troll, BloodElf, Goblin]
  24.     class:
  25.       type: enum
  26.       values: [Deathknight, Druid, Hunter, Mage, Paladin, Priest, Rogue, Shaman, Warlock, Warrior]
  27.     gender:
  28.       type: integer(1)
  29.       unsigned: true
  30.   relations:
  31.     Bosses:
  32.       class: Boss
  33.       refClass: BossCharacter
  34.       foreignAlias: Characters
  35.  
  36. Raid:
  37.   tableName: raids
  38.   columns:
  39.     id:
  40.       type: integer(2)
  41.       primary: true
  42.       unsigned: true
  43.       autoincrement: true
  44.     name:
  45.       type: string(255)
  46.       notnull: true
  47.     date:
  48.       type: date
  49.       notnull: true
  50.   indexes:
  51.     raiduniquedate:
  52.       fields: [name, date]
  53.       type: unique
  54.  
  55. Boss:
  56.   tableName: bosses
  57.   columns:
  58.     id:
  59.       type: integer(2)
  60.       primary: true
  61.       unsigned: true
  62.       autoincrement: true
  63.     name:
  64.       type: string(255)
  65.       notnull: true
  66.     raid_id:
  67.       type: integer(2)
  68.       primary: true
  69.       unsigned: true
  70.   relations:
  71.     Raid:
  72.       class: Raid
  73.       local: raid_id
  74.       foreignAlias: Bosses
  75.     Characters:
  76.       foreignAlias: Bosses
  77.       class: Character
  78.       refClass: BossCharacter
  79.   indexes:
  80.     bossuniqueraid:
  81.       fields: [name, raid_id]
  82.       type: unique
  83.  
  84. BossCharacter:
  85.   tableName: bosses_characters
  86.   columns:
  87.     boss_id:
  88.       type: integer(2)
  89.       primary: true
  90.       unsigned: true
  91.     character_id:
  92.       type: integer(2)
  93.       primary: true
  94.       unsigned: true
  95.   relations:
  96.     Boss:
  97.       foreignAlias: BossCharacters
  98.     Character:
  99.       foreignAlias: BossCharacters
  100.   indexes:
  101.     bossunique:
  102.       fields: [boss_id, character_id]
  103.       type: unique
  104.  
  105.  
  106. ## SQL BUILD BY DOCTRINE (wrong)
  107. CREATE TABLE bosses (id SMALLINT UNSIGNED AUTO_INCREMENT, name VARCHAR(255) NOT NULL, raid_id SMALLINT UNSIGNED, UNIQUE INDEX bossuniqueraid_idx (name, raid_id), PRIMARY KEY(id, raid_id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ENGINE = innodb;
  108. CREATE TABLE bosses_characters (boss_id SMALLINT UNSIGNED, character_id SMALLINT UNSIGNED, UNIQUE INDEX bossunique_idx (boss_id, character_id), PRIMARY KEY(boss_id, character_id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ENGINE = innodb;
  109. CREATE TABLE characters (id SMALLINT UNSIGNED AUTO_INCREMENT, name VARCHAR(255) NOT NULL UNIQUE, rank SMALLINT UNSIGNED, level SMALLINT UNSIGNED, race ENUM('Human', 'Dwarf', 'NightElf', 'Gnome', 'Draenei', 'Worgen', 'Orc', 'Undead', 'Tauren', 'Troll', 'BloodElf', 'Goblin'), class ENUM('Deathknight', 'Druid', 'Hunter', 'Mage', 'Paladin', 'Priest', 'Rogue', 'Shaman', 'Warlock', 'Warrior'), gender TINYINT UNSIGNED, PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ENGINE = innodb;
  110. CREATE TABLE raids (id SMALLINT UNSIGNED AUTO_INCREMENT, name VARCHAR(255) NOT NULL, date DATE NOT NULL, UNIQUE INDEX raiduniquedate_idx (name, date), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ENGINE = innodb;
  111. ALTER TABLE bosses ADD CONSTRAINT bosses_raid_id_raids_id FOREIGN KEY (raid_id) REFERENCES raids(id);
  112. # these two lines are completely wrong!
  113. ALTER TABLE bosses ADD CONSTRAINT bosses_raid_id_bosses_characters_boss_id FOREIGN KEY (raid_id) REFERENCES bosses_characters(boss_id);
  114. ALTER TABLE bosses ADD CONSTRAINT bosses_id_bosses_characters_boss_id FOREIGN KEY (id) REFERENCES bosses_characters(boss_id);
  115.  
  116. ALTER TABLE bosses_characters ADD CONSTRAINT bosses_characters_character_id_characters_id FOREIGN KEY (character_id) REFERENCES characters(id);
  117. ALTER TABLE bosses_characters ADD CONSTRAINT bosses_characters_boss_id_bosses_id FOREIGN KEY (boss_id) REFERENCES bosses(id);
create a new version of this paste RAW Paste Data