defmodule Odinsea.Repo.Migrations.CreateCharacterRelatedTables do use Ecto.Migration def up do # ============================================================================ # QUEST SYSTEM # ============================================================================ create table(:queststatus) do add :characterid, references(:characters, on_delete: :delete_all), null: false, default: 0 add :quest, :integer, null: false, default: 0 add :status, :integer, null: false, default: 0 add :time, :integer, null: false, default: 0 add :forfeited, :integer, null: false, default: 0 add :customdata, :string, size: 255 end create index(:queststatus, [:characterid]) create index(:queststatus, [:queststatusid]) create table(:queststatusmobs) do add :queststatusid, references(:queststatus, on_delete: :delete_all), null: false, default: 0 add :mob, :integer, null: false, default: 0 add :count, :integer, null: false, default: 0 end create index(:queststatusmobs, [:queststatusid]) create table(:questinfo) do add :characterid, references(:characters, on_delete: :delete_all), null: false, default: 0 add :quest, :integer, null: false, default: 0 add :customdata, :string, size: 555 end create index(:questinfo, [:characterid]) # ============================================================================ # PETS & FAMILIARS # ============================================================================ create table(:pets) do add :name, :string, size: 13 add :level, :integer, null: false add :closeness, :integer, null: false add :fullness, :integer, null: false add :seconds, :integer, null: false, default: 0 add :flags, :integer, null: false, default: 0 end create index(:pets, [:petid]) create table(:familiars) do add :characterid, :integer, null: false, default: 0 add :familiar, :integer, null: false, default: 0 add :name, :string, size: 40, null: false, default: "" add :fatigue, :integer, null: false, default: 0 add :expiry, :bigint, null: false, default: 0 add :vitality, :integer, null: false, default: 0 end create table(:imps) do add :characterid, :integer, null: false, default: 0 add :itemid, :integer, null: false, default: 0 add :level, :integer, null: false, default: 1 add :state, :integer, null: false, default: 1 add :closeness, :integer, null: false, default: 0 add :fullness, :integer, null: false, default: 0 end create index(:imps, [:impid]) # ============================================================================ # MOUNT & TRANSPORT # ============================================================================ create table(:mountdata) do add :characterid, :integer, unique: true add :level, :integer, null: false, default: 0 add :exp, :integer, null: false, default: 0 add :fatigue, :integer, null: false, default: 0 end create unique_index(:mountdata, [:characterid]) create index(:mountdata, [:id]) create table(:trocklocations) do add :characterid, :integer add :mapid, :integer end create index(:trocklocations, [:characterid]) create table(:regrocklocations) do add :characterid, :integer add :mapid, :integer end create index(:regrocklocations, [:characterid]) create table(:hyperrocklocations) do add :characterid, :integer add :mapid, :integer end # ============================================================================ # MONSTER BOOK # ============================================================================ create table(:monsterbook) do add :charid, :integer, null: false, default: 0 add :cardid, :integer, null: false, default: 0 add :level, :integer, default: 1 end create index(:monsterbook, [:id]) create index(:monsterbook, [:charid]) # ============================================================================ # SAVED LOCATIONS # ============================================================================ create table(:savedlocations) do add :characterid, references(:characters, on_delete: :delete_all), null: false add :locationtype, :integer, null: false, default: 0 add :map, :integer, null: false end create index(:savedlocations, [:characterid], name: :savedlocations_ibfk_1) # ============================================================================ # ACHIEVEMENTS & REPORTS # ============================================================================ create table(:achievements) do add :achievementid, :integer, null: false, default: 0 add :charid, :integer, null: false, default: 0 add :accountid, :integer, null: false, default: 0 end create unique_index(:achievements, [:achievementid, :charid], name: :achievements_pkey) create index(:achievements, [:achievementid]) create index(:achievements, [:accountid]) create index(:achievements, [:charid]) create table(:reports) do add :characterid, :integer, null: false, default: 0 add :type, :integer, null: false, default: 0 add :count, :integer, null: false, default: 0 end create index(:reports, [:characterid]) # ============================================================================ # NOTES & MESSAGING # ============================================================================ create table(:notes) do add :to, :string, size: 13, null: false, default: "" add :from, :string, size: 13, null: false, default: "" add :message, :text, null: false add :timestamp, :bigint, null: false add :gift, :boolean, null: false, default: false end create index(:notes, [:to]) # ============================================================================ # RINGS & MARRIAGE # ============================================================================ create table(:rings) do add :partnerringid, :integer, null: false, default: 0 add :partnerchrid, :integer, null: false, default: 0 add :itemid, :integer, null: false, default: 0 add :partnername, :string, size: 255, null: false end create index(:rings, [:ringid]) create index(:rings, [:partnerchrid]) create index(:rings, [:partnerringid]) # ============================================================================ # SIDEKICKS # ============================================================================ create table(:sidekicks) do add :firstid, :integer, null: false, default: 0 add :secondid, :integer, null: false, default: 0 end # ============================================================================ # BATTLE & TOURNAMENT # ============================================================================ create table(:battlelog) do add :accid, references(:accounts, on_delete: :delete_all), null: false, default: 0 add :accid_to, :integer, null: false, default: 0 timestamps(type: :naive_datetime, inserted_at: :when, updated_at: false) end create index(:battlelog, [:accid]) create table(:tournamentlog) do add :winnerid, :integer, null: false, default: 0 add :numcontestants, :integer, null: false, default: 0 timestamps(type: :naive_datetime, inserted_at: :when, updated_at: false) end create table(:speedruns) do add :type, :string, size: 13, null: false add :leader, :string, size: 13, null: false add :timestring, :string, size: 1024, null: false add :time, :bigint, null: false, default: 0 add :members, :string, size: 1024, null: false, default: "" end # ============================================================================ # PLAYER NPCS # ============================================================================ create table(:playernpcs) do add :name, :string, size: 13, null: false add :hair, :integer, null: false add :face, :integer, null: false add :skin, :integer, null: false add :x, :integer, null: false, default: 0 add :y, :integer, null: false, default: 0 add :map, :integer, null: false add :charid, references(:characters, on_delete: :delete_all), null: false add :scriptid, :integer, null: false add :foothold, :integer, null: false add :dir, :integer, null: false, default: 0 add :gender, :integer, null: false, default: 0 add :pets, :string, size: 25, default: "0,0,0" end create index(:playernpcs, [:scriptid]) create index(:playernpcs, [:charid], name: :playernpcs_ibfk_1) create table(:playernpcs_equip) do add :npcid, references(:playernpcs, column: :scriptid, on_delete: :delete_all), null: false add :equipid, :integer, null: false add :equippos, :integer, null: false add :charid, references(:characters, on_delete: :delete_all), null: false end create index(:playernpcs_equip, [:charid], name: :playernpcs_equip_ibfk_1) create index(:playernpcs_equip, [:npcid], name: :playernpcs_equip_ibfk_2) # ============================================================================ # POKEMON SYSTEM # ============================================================================ create table(:pokemon) do add :monsterid, :integer, null: false, default: 0 add :characterid, :integer, null: false, default: 0 add :level, :integer, null: false, default: 1 add :exp, :integer, null: false, default: 0 add :name, :string, size: 255, null: false, default: "" add :nature, :integer, null: false, default: 0 add :active, :boolean, null: false, default: false add :accountid, :integer, null: false, default: 0 add :itemid, :integer, null: false, default: 0 add :gender, :integer, null: false, default: -1 add :hpiv, :integer, null: false, default: -1 add :atkiv, :integer, null: false, default: -1 add :defiv, :integer, null: false, default: -1 add :spatkiv, :integer, null: false, default: -1 add :spdefiv, :integer, null: false, default: -1 add :speediv, :integer, null: false, default: -1 add :evaiv, :integer, null: false, default: -1 add :acciv, :integer, null: false, default: -1 add :ability, :integer, null: false, default: -1 end create index(:pokemon, [:id]) create index(:pokemon, [:characterid]) # ============================================================================ # ANDROID SYSTEM # ============================================================================ create table(:androids) do add :name, :string, size: 13, null: false, default: "Android" add :hair, :integer, null: false, default: 0 add :face, :integer, null: false, default: 0 end create index(:androids, [:uniqueid]) # ============================================================================ # WISHLIST # ============================================================================ create table(:wishlist) do add :characterid, :integer, null: false, primary_key: true add :sn, :integer, null: false, primary_key: true end create index(:wishlist, [:characterid]) end def down do drop table(:wishlist) drop table(:androids) drop table(:pokemon) drop table(:playernpcs_equip) drop table(:playernpcs) drop table(:speedruns) drop table(:tournamentlog) drop table(:battlelog) drop table(:sidekicks) drop table(:rings) drop table(:notes) drop table(:reports) drop table(:achievements) drop table(:savedlocations) drop table(:monsterbook) drop table(:hyperrocklocations) drop table(:regrocklocations) drop table(:trocklocations) drop table(:mountdata) drop table(:imps) drop table(:familiars) drop table(:pets) drop table(:questinfo) drop table(:queststatusmobs) drop table(:queststatus) end end