"""
Create snapshot, article, and image tables
"""
from yoyo import step
__depends__ = {'20160524_01_OYDN4-create-base-repository'}
steps = [
step("""CREATE TABLE articles (
id BIGSERIAL PRIMARY KEY,
title VARCHAR(1024) NOT NULL,
href VARCHAR(2083) NOT NULL UNIQUE,
image_url VARCHAR(2083) NOT NULL DEFAULT '',
location e_location_type NOT NULL,
created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now()
);"""),
]
"""
Store the raw statistics collected from SMW.
This migration implements part of the manually created Apiary DB
that WikiApiary launched with.
"""
from yoyo import step
step(
"CREATE TABLE `smwinfo` ( \
`website_id` int(11) NOT NULL, \
`capture_date` datetime NOT NULL, \
`response_timer` float DEFAULT NULL, \
`propcount` bigint(20) NOT NULL, \
`proppagecount` int(11) NOT NULL, \
`usedpropcount` int(11) NOT NULL, \
`declaredpropcount` int(11) NOT NULL, \
`querycount` int(11) DEFAULT NULL, \
`querysize` int(11) DEFAULT NULL, \
`conceptcount` int(11) DEFAULT NULL, \
`subobjectcount` int(11) DEFAULT NULL, \
PRIMARY KEY (`website_id`,`capture_date`) \
) ENGINE=InnoDB DEFAULT CHARSET=utf8",
"DROP TABLE `smwinfo`",
ignore_errors='rollback', # This table is dropped in migration 20.
)
from yoyo import step
step("""
CREATE TABLE movements (
id SERIAL,
token VARCHAR,
amount BIGINT
);
""",
"""DROP TABLE movements;""")
"""Create user_media migration"""
# pylint: disable=C0103
#
# file: migrations/0003.create_user_media.py
#
from yoyo import step
__depends__ = ['0001.create-users', '0002.create-media']
step(
"""
CREATE TABLE user_media
(
id serial,
user_id integer REFERENCES users (id) ON DELETE CASCADE,
media_id integer REFERENCES media (id) ON DELETE CASCADE,
source text NOT NULL,
created_at timestamp with time zone NOT NULL,
updated_at timestamp with time zone,
CONSTRAINT pk_user_media PRIMARY KEY (id),
CONSTRAINT uq_user_media UNIQUE (user_id, media_id)
);
""",
"DROP TABLE IF EXISTS user_media CASCADE;"
)
from yoyo import step
step(
"""
ALTER TABLE User ADD COLUMN registerDate timestamp not null default current_timestamp
""",
"ALTER TABLE User DROP COLUMN registerDate",
)
from yoyo import step
step("""
UPDATE nodes
SET is_input=1
WHERE rowid
IN (SELECT input FROM nodes_to_nodes)
""")
step("""
UPDATE nodes
SET is_response=1
WHERE rowid
IN (SELECT response FROM nodes_to_nodes)
""")
''' #this doesn't work in sqlite
step("""UPDATE nodes n
JOIN nodes_to_nodes n2n
ON n.rowid=n2n.response
SET n.is_response=1""",
"UPDATE nodes SET is_response=0")
step("""UPDATE nodes n
JOIN nodes_to_nodes n2n
ON n.rowid=n2n.input
SET n.is_input=1""",
"UPDATE nodes SET is_input=0")
'''
from yoyo import step
def create_tables(conn):
c = conn.cursor()
c.executescript("""
CREATE TABLE log (
timestamp INTEGER,
source TEXT,
text TEXT
);
""")
step(create_tables)
"""
Add login count to users
"""
from yoyo import step
__depends__ = {'20200113_03_HYCzS-add-timestamps-to-user'}
steps = [
step("""
ALTER TABLE "user"
ADD COLUMN login_count INTEGER NOT NULL DEFAULT 0;
""")
]
# This program is free software: you can redistribute it and/or modify
# it under the terms of the GNU Lesser Public License as published by
# the Free Software Foundation, either version 3 of the License, or
# (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU Lesser Public License for more details.
#
# You should have received a copy of the GNU Lesser Public License
# along with this program. If not, see [http://www.gnu.org/licenses/].
from yoyo import step
steps = [
step(
"INSERT INTO STORAGE (key,value,description) values ('description_button_label','\U00002753 Описание \U00002753','Текст на кнопках Описания')"
),
]
__depends__ = {'20200719_01_SJdgK-add-team-membership'}
add_score = """
ALTER TABLE teams
ADD COLUMN score INT(11) NOT NULL DEFAULT 0
"""
drop_score = """
ALTER TABLE teams
DROP COLUMN score
"""
add_game_state = """
CREATE TABLE game_states(
id BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT,
created_ts DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
updated_ts DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
game_id BIGINT NOT NULL,
full TEXT,
partial TEXT,
CONSTRAINT fk_game_state FOREIGN KEY (game_id) REFERENCES games (id) ON DELETE CASCADE
)
"""
drop_game_state = "DROP TABLE game_states"
steps = [
step(add_score, drop_score),
step(add_game_state, drop_game_state),
]
# This program is free software: you can redistribute it and/or modify
# it under the terms of the GNU Lesser Public License as published by
# the Free Software Foundation, either version 3 of the License, or
# (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU Lesser Public License for more details.
#
# You should have received a copy of the GNU Lesser Public License
# along with this program. If not, see [http://www.gnu.org/licenses/].
from yoyo import step
steps = [
step(
"CREATE INDEX dla_dispatch_list_id_index ON DISPATCH_LIST_ASSIGNS (dispatch_list_id);",
"DROP INDEX dla_dispatch_list_id_index")
]
from yoyo import group, step
from yombo.lib.localdb.migrations import create_index
print(
"Creating new database file. This will take a bit of time on Raspberry Pi like devices."
)
steps = [
group([
# System categories
step("""CREATE TABLE `categories` (
`id` TEXT NOT NULL,
`parent_id` TEXT NOT NULL,
`category_type` TEXT NOT NULL,
`machine_label` TEXT NOT NULL,
`label` TEXT NOT NULL,
`description` TEXT,
`status` INTEGER NOT NULL,
`created_at` INTEGER NOT NULL,
`updated_at` INTEGER NOT NULL,
PRIMARY KEY(id) );"""),
step(create_index("categories", "id", unique=True)),
# Defines the commands table. Lists all possible commands a local or remote gateway can perform.
step("""CREATE TABLE `commands` (
`id` TEXT NOT NULL,
`voice_cmd` TEXT,
`machine_label` TEXT NOT NULL,
`label` TEXT NOT NULL,
`description` TEXT,
`public` INTEGER NOT NULL,
"""
modyfication comments and add table type and insert data in project
"""
from yoyo import step
__depends__ = {
'20180611_01_5VN1q-replace-column-type-for-project-and-add-comments'
}
steps = [
step(
"""
CREATE TABLE `type_for_project` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`type` text NULL,
`pryority` int(11) NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
""", "DROP TABLE type_for_project"),
step(
"""
CREATE TABLE `m2m_project_comment` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`project_id` int(11) NULL,
`comment_id` int(11) NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
""", "DROP TABLE m2m_project_comment"),
step(
"""
"""
Create database
"""
from yoyo import step
steps = [
step("""
CREATE TABLE `accounts` (
`id` int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
`first_name` varchar(255) NOT NULL,
`last_name` varchar(255) NOT NULL,
`cpf` varchar(11) UNIQUE NOT NULL,
`creation` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`update` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) COLLATE 'utf8mb4_general_ci'
""", "DROP TABLE accounts")
]
from yoyo import step
step(
'''CREATE TABLE IF NOT EXISTS loc (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name VARCHAR(50) UNIQUE NOT NULL,
map UNIQUE NOT NULL,
phone INTEGER UNIQUE NOT NULL,
fax INTEGER UNIQUE NOT NULL,
address TEXT UNIQUE NOT NULL,
city TEXT UNIQUE NOT NULL,
zipcode INTEGER UNIQUE NOT NUll,
state TEXT UNIQUE NOT NULL,
email TEXT UNIQUE NOT NULL)''',
'''DROP TABLE IF EXISTS loc''',
)
"""
Adds password columns to user table
"""
from yoyo import step
__depends__ = {'20190615_02_PzAXJ-create-regisration-code-table'}
steps = [
step("ALTER TABLE users ADD COLUMN password VARCHAR")
]
that WikiApiary launched with.
"""
from yoyo import step
step(
"CREATE TABLE `statistics_daily` ( \
`website_id` int(11) NOT NULL, \
`website_date` date NOT NULL, \
`users_min` bigint(20) NOT NULL, \
`users_max` bigint(20) NOT NULL, \
`activeusers_max` bigint(20) NOT NULL, \
`admins_max` bigint(20) NOT NULL, \
`articles_min` bigint(20) NOT NULL, \
`articles_max` bigint(20) NOT NULL, \
`edits_min` bigint(20) NOT NULL, \
`edits_max` bigint(20) NOT NULL, \
`jobs_max` bigint(20) NOT NULL, \
`pages_min` bigint(20) NOT NULL, \
`pages_max` bigint(20) NOT NULL, \
`pages_last` bigint(20) NOT NULL, \
`views_min` bigint(20) NOT NULL, \
`views_max` bigint(20) NOT NULL, \
`smw_propcount_min` bigint(20) NOT NULL, \
`smw_propcount_max` bigint(20) NOT NULL, \
`smw_proppagecount_last` int(11) NOT NULL, \
`smw_usedpropcount_last` int(11) NOT NULL, \
`smw_declaredpropcount_last` int(11) NOT NULL, \
PRIMARY KEY (`website_id`,`website_date`) \
) ENGINE=InnoDB DEFAULT CHARSET=utf8",
"DROP TABLE `statistics_daily`",
)
step('''
CREATE TABLE IF NOT EXISTS "user" (
id SERIAL NOT NULL,
email VARCHAR(255) NOT NULL,
login_token VARCHAR(22),
PRIMARY KEY (id),
UNIQUE (email),
UNIQUE (login_token)
);
CREATE TABLE IF NOT EXISTS endpoint (
created_at TIMESTAMP WITHOUT TIME ZONE NOT NULL,
updated_at TIMESTAMP WITHOUT TIME ZONE NOT NULL,
id SERIAL NOT NULL,
token VARCHAR(22) NOT NULL,
user_id INTEGER NOT NULL,
name VARCHAR(255) NOT NULL,
disabled BOOLEAN NOT NULL,
PRIMARY KEY (id),
UNIQUE (token),
FOREIGN KEY(user_id) REFERENCES "user" (id)
);
CREATE INDEX IF NOT EXISTS ix_endpoint_user_id ON endpoint (user_id);
CREATE TABLE IF NOT EXISTS message (
created_at TIMESTAMP WITHOUT TIME ZONE NOT NULL,
updated_at TIMESTAMP WITHOUT TIME ZONE NOT NULL,
id SERIAL NOT NULL,
endpoint_id INTEGER NOT NULL,
subject TEXT NOT NULL,
body TEXT NOT NULL,
sent BOOLEAN NOT NULL,
reference_id VARCHAR(255),
reqid VARCHAR(32),
PRIMARY KEY (id),
FOREIGN KEY(endpoint_id) REFERENCES endpoint (id)
);
CREATE INDEX IF NOT EXISTS ix_message_endpoint_id ON message (endpoint_id);
-- SQLAlchemy had generated this which makes no sense
-- This was supposed to be a compound index but SQLAlchemy did something weird here; fix it
DROP INDEX IF EXISTS "(no name)";
-- Here's my fix
CREATE UNIQUE INDEX IF NOT EXISTS idx_endpoint_and_reqid ON message (endpoint_id, reqid);
''')
"""Create media migration"""
# pylint: disable=C0103
#
# file: migrations/0002.create-media.py
#
from yoyo import step
step(
"""
CREATE TABLE media
(
id serial,
media_id text NOT NULL,
media_created_time timestamp NOT NULL,
data json NOT NULL,
created_at timestamp with time zone NOT NULL,
updated_at timestamp with time zone,
CONSTRAINT pk_media PRIMARY KEY (id),
CONSTRAINT uq_media_id UNIQUE (media_id)
)
""",
"DROP TABLE IF EXISTS media CASCADE;",
)
"""
Creates session table
"""
from yoyo import step
__depends__ = {'20190620_01_IwAbL-creates-table-locations'}
steps = [
step("""-- This script only contains the table creation statements and does not fully represent the table in the database. It's still missing: indices, triggers. Do not use it as a backup.
-- Sequence and defined type
CREATE SEQUENCE IF NOT EXISTS session_id_seq;
-- Table Definition
CREATE TABLE "public"."session" (
"id" int4 NOT NULL DEFAULT nextval('session_id_seq'::regclass),
"user_id" int4 NOT NULL,
"hashed_session" text NOT NULL,
"created" date NOT NULL DEFAULT now(),
CONSTRAINT "session_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE CASCADE,
PRIMARY KEY ("id")
);""")
]
# This program is free software: you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation, either version 3 of the License, or
# (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program. If not, see <http://www.gnu.org/licenses/>.
from yoyo import step
step(
'CREATE TABLE imports_lastfm ('
' id INTEGER UNIQUE PRIMARY KEY, '
' datetime DATETIME NOT NULL, '
' trackname VARCHAR, '
' artistname VARCHAR, '
' albumname VARCHAR, '
' trackmbid VARCHAR, '
' artistmbid VARCHAR, '
' albummbid VARCHAR '
')',
'DROP TABLE imports_lastfm'
)
from yoyo import step
"""
Create dataset table
"""
SQL_INIT_DB = '''
CREATE TABLE IF NOT EXISTS dataset (
id SERIAL,
name TEXT NOT NULL,
created_on timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
file_ids INT[] NOT NULL,
PRIMARY KEY(id)
);
CREATE UNIQUE INDEX IF NOT EXISTS uniq_dataset_name ON dataset(name);
'''
steps = [
step(SQL_INIT_DB),
]
"""
Logging table to keep log entries for related to a specific
website.
This migration implements part of the manually created Apiary DB
that WikiApiary launched with.
"""
from yoyo import step
step(
"CREATE TABLE `apiary_website_logs` ( \
`log_id` int(11) NOT NULL AUTO_INCREMENT, \
`website_id` int(11) NOT NULL, \
`log_date` datetime NOT NULL, \
`website_name` varchar(255) NOT NULL, \
`log_type` varchar(30) NOT NULL, \
`log_severity` varchar(30) NOT NULL, \
`log_message` varchar(255) NOT NULL, \
`log_bot` varchar(30) DEFAULT NULL, \
`log_url` varchar(255) DEFAULT NULL, \
PRIMARY KEY (`log_id`), \
KEY `idx_log_date` (`log_date`) USING BTREE, \
KEY `idx_website_id_log_date` (`website_id`,`log_date`) USING BTREE \
) ENGINE=InnoDB DEFAULT CHARSET=utf8",
"DROP TABLE `apiary_website_logs`",
)
"""
support filter groups
"""
from yoyo import step
__depends__ = {'20191004_01_SbFhm-added-min-amount-to-twitch-chat-alerts'}
steps = [
step('''
ALTER TABLE `twitch_filters`
ADD COLUMN `id` INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,
ADD COLUMN `unique` INT(1) NULL AFTER `type`,
DROP PRIMARY KEY,
ADD PRIMARY KEY (`id`),
ADD UNIQUE INDEX `twitch_filters_unique` (`channel_id` ASC, `type` ASC, `unique` ASC);
'''),
step('''
ALTER TABLE `twitch_filters`
ADD COLUMN `name` VARCHAR(100) NULL AFTER `unique`;
'''),
step('''
CREATE TABLE `twitch_filter_banned_words` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`channel_id` VARCHAR(36) NOT NULL,
`filter_id` INT UNSIGNED NOT NULL,
`banned_words` VARCHAR(1000) NULL,
PRIMARY KEY (`id`),
INDEX `ix_twitch_filter_banned_words_channel_id_filter_id` (`channel_id` ASC, `filter_id` ASC));
''')
]
from yoyo import step
step(
"CREATE TABLE IF NOT EXISTS softban_log (status text, source text, dated datetime DEFAULT CURRENT_TIMESTAMP)"
)
"""
Assemblies table
"""
from yoyo import step
steps = [
step(
"CREATE TABLE IF NOT EXISTS assemblies (id INTEGER PRIMARY KEY, sample_name VARCHAR(64), directory VARCHAR(128), url VARCHAR(128))",
"DROP TABLE IF EXISTS assemblies",
)
]
from yoyo import step, transaction
transaction(
step("""CREATE TABLE summits_images (
image varchar(64) PRIMARY KEY,
preview varchar(64) NOT NULL,
summit_id integer REFERENCES summits(id),
main boolean DEFAULT false,
comment text DEFAULT NULL
)""")
)
"""
Create local authentication backend
"""
from yoyo import step
__depends__ = {'20180218_01_FjwIl-add-initial-tables'}
steps = [
step(
"""
INSERT INTO auth_backends (uuid, name) VALUES (uuid_generate_v4(), 'Local')
""", """DELETE FROM auth_backends WHERE name='Local' """)
]
"""
Add not-null col in Ad
"""
from yoyo import step
__depends__ = {'20151208_02_dPGPe-rename-currencys-col-in-ad'}
steps = [
step("ALTER TABLE ad CHANGE COLUMN site site VARCHAR(30) NOT NULL"),
step("DELETE FROM ad WHERE site_id is NULL"),
step("ALTER TABLE ad CHANGE COLUMN site_id site_id VARCHAR(100) NOT NULL"),
step("ALTER TABLE ad CHANGE COLUMN country country VARCHAR(2) NOT NULL"),
]
"""
Create Listings
"""
from yoyo import step
__depends__ = {'20170402_01_owjPI-create-users'}
steps = [
step(
"""CREATE TABLE listings (
id integer NOT NULL PRIMARY KEY,
name varchar(255) NOT NULL,
user_id integer NOT NULL,
updated_at timestamp without time zone default (now() at time zone 'utc'),
created_at timestamp without time zone default (now() at time zone 'utc'),
CONSTRAINT fk_listing_user_id FOREIGN KEY (user_id)
REFERENCES users (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION
);""", "DROP TABLE listings")
]
"""
After applying all migrations create views.
"""
from yoyo import step
step(
"CREATE VIEW `apiary_website_logs_summary` AS \
SELECT `apiary_website_logs`.`website_id` AS `website_id`, \
count(0) AS `log_count`, \
max(`apiary_website_logs`.`log_date`) AS `log_date_last`, \
min(`apiary_website_logs`.`log_date`) AS `lot_date_first` \
FROM `apiary_website_logs` \
GROUP BY `apiary_website_logs`.`website_id`;",
"DROP VIEW IF EXISTS `apiary_website_logs_summary`;"
)
"""
add existing users to channels
"""
from yoyo import step
__depends__ = {"20191222_01_xEgbR-add-channels"}
steps = [
step(
"INSERT INTO channels (id, name, type) VALUES(1, 'General', 'DEFAULT')"
),
step("""
insert into channels_users (channel_id, user_id, joined)
select 1, users.id, (SELECT extract(epoch from now() at time zone 'utc'))
from users """),
]
from yoyo import step
step(
"CREATE TABLE IF NOT EXISTS eggs_hatched_log (pokemon text, cp real, iv real, pokemon_id real, dated datetime DEFAULT CURRENT_TIMESTAMP)"
)
"""
create table estornos_pagamentos
"""
from yoyo import step
__depends__ = {'20200413_02_Yv3MX-create-table-notas-fiscais-liquid'}
steps = [
step(
"create table estornos_pagamentos ("
'id SERIAL PRIMARY KEY,'
'codigo_municipio varchar(10),'
'exercicio_orcamento varchar(10),'
'codigo_orgao varchar(10),'
'codigo_unidade varchar(10),'
'data_emissao_empenho timestamp,'
'numero_empenho varchar(100),'
'numero_sub_empenho_nota_pagamento varchar(100),'
'numero_pagamento varchar(100),'
'data_estorno_pagamento timestamp,'
'data_referencia_estorno_pagamento varchar(100),'
'nome_assessor varchar(255),'
'descricao_justificativa text'
")", "DROP TABLE estornos_pagamentos")
]
"""
Add description to Ad
"""
from yoyo import step
__depends__ = {'20151217_01_mGj93-add-not-null-col-in-ad'}
steps = [
step("ALTER TABLE ad ADD description TEXT"),
]
"""
Create Competitors
"""
from yoyo import step
__depends__ = {'20170429_01_Vax7m-create-hostearnings'}
steps = [
step(
"""CREATE TABLE competitors(
date date NOT NULL,
room_id integer NOT NULL,
price real NOT NULL,
currency varchar(16) NOT NULL,
data jsonb NOT NULL,
UNIQUE (date, room_id)
)""", "DROP TABLE competitors")
]
from yoyo import step, transaction
# Removing dummy images downloaded from vk.com as user pictures
# due to bug in vk_get_user and replacing them with NULL to user
# our dummy images instead;
transaction(
step("UPDATE users SET image=NULL, preview=NULL " +
"WHERE image='29a62e8bc3609aef88ac2bc722bf7c71f4f86a32.png'"),
step("DELETE FROM images WHERE " +
"name='29a62e8bc3609aef88ac2bc722bf7c71f4f86a32.png'"),
step("DELETE FROM images WHERE " +
"name='1fc78e0df8d470a82ed55882ac619e7aafa68051.png'")
)
"""
resize-pic-column
"""
from yoyo import step
__depends__ = {'20191115_03_F23uJ-cpr-cert-triggers'}
steps = [step("ALTER TABLE Route MODIFY picture VARCHAR(64);")]
from yoyo import step
step("""
CREATE TABLE prices (
id SERIAL,
bytes BIGINT,
amount BIGINT
);
""",
"""DROP TABLE prices;""")
from yoyo import step
step("CREATE TABLE Users (id INT, username VARCHAR, PRIMARY KEY(id))")
"""
Added ipHash and createdAt to Rent model
"""
from yoyo import step
__depends__ = {'20160408_02_wg1lz-added-movein-to-rent-model'}
steps = [
step("ALTER TABLE rent ADD created_at TIMESTAMP NOT NULL DEFAULT now()"),
step("ALTER TABLE rent ADD ip_hash VARCHAR(512) NOT NULL DEFAULT ''"),
]
"""
chance column name bar to comp_name
"""
from yoyo import step
__depends__ = {'20190510_02_U9djH-add-timestamp-collumn-to-matches'}
steps = [step("ALTER TABLE competitors RENAME bar TO comp_name")]
from yoyo import step
step(
"CREATE TABLE IF NOT EXISTS evolve_log (pokemon text, iv real, cp real, dated datetime DEFAULT CURRENT_TIMESTAMP)"
)
"""
Add constrain to user
"""
from yoyo import step
__depends__ = {'20210325_01_UVqF5-init-models'}
steps = [
step("ALTER TABLE users ADD CONSTRAINT user_name_uniq UNIQUE (user_name)")
]
"""
Use date as the first element of unique indexes instead of home team code,
since arrange by date is much more common.
"""
from yoyo import step
__depends__ = {'20160804_01_TcYzR-initial-database-configuration'}
steps = [
step("""
CREATE UNIQUE INDEX `game_date_home_team_code_game_no_uindex`
ON `game` (`date`, `home_team_code`, `game_no`);
""", """
DROP INDEX `game_date_home_team_code_game_no_uindex` ON `game`;
"""),
step("""
CREATE UNIQUE INDEX `player_year_retrosheet_team_code_uindex`
ON `player` (`year`, `retrosheet_id`, `team_code`);
""", """
DROP INDEX `player_year_retrosheet_team_code_uindex` ON `player`;
"""),
step("""
CREATE UNIQUE INDEX `record_game_date_game_home_team_code_game_game_no_seq_no_uindex`
ON `record` (`game_date`, `game_home_team_code`, `game_no`, `seq_no`);
""", """
DROP INDEX `record_game_date_game_home_team_code_game_game_no_seq_no_uindex`
ON `record`;
"""),
"""
Create table IngredientType
"""
from yoyo import step
__depends__ = {'20210815_11_bzY3P-create-table-command'}
def apply(conn):
cursor = conn.cursor()
cursor.execute("""
CREATE TABLE IngredientType (
id INTEGER NOT NULL AUTO_INCREMENT,
name VARCHAR(30) NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY (name)
);
""")
def rollback(conn):
cursor = conn.cursor()
cursor.execute("""
DROP TABLE IF EXISTS IngredientType;
""")
steps = [step(apply, rollback)]
"""
Added moveIn to Rent model
"""
from yoyo import step
__depends__ = {'20160408_01_OgXf5-added-rent-model'}
steps = [
step("ALTER TABLE rent ADD move_in DATE"),
]
"""
Add disable_autoplay column to user
"""
from yoyo import step
__depends__ = {'20170507_01_mRphn-add-video-key-columns-to-sourcefile'}
steps = [
step(
"ALTER TABLE user ADD COLUMN `disable_autoplay` tinyint(1) DEFAULT 0 AFTER `show_stats`",
"ALTER TABLE user DROP COLUMN `disable_autoplay`",
)
]
);
CREATE TABLE IF NOT EXISTS images (
id serial PRIMARY KEY,
type varchar(256),
payload bytea
);
CREATE TABLE IF NOT EXISTS users (
id serial PRIMARY KEY,
oauth_id varchar(256) NOT NULL,
src smallint NOT NULL,
name varchar(256) NOT NULL,
email varchar(256),
img_id int REFERENCES images DEFAULT NULL,
pub boolean NOT NULL DEFAULT true,
admin boolean NOT NULL DEFAULT false,
UNIQUE (oauth_id, src)
);
CREATE TABLE IF NOT EXISTS climbs (
user_id integer NOT NULL REFERENCES users,
summit_id integer NOT NULL REFERENCES summits,
ts date,
comment text,
PRIMARY KEY (user_id, summit_id)
);
"""
step(sql)
"""
Game library
"""
from yoyo import step
__depends__ = {'20190707_01_JVd8l-new-game-rating'}
steps = [
step("""
-- This script only contains the table creation statements and does not fully represent the table in the database. It's still missing: indices, triggers. Do not use it as a backup.
-- Sequence and defined type
CREATE SEQUENCE IF NOT EXISTS game_library_id_seq;
-- Table Definition
CREATE TABLE "public"."game_library" (
"id" int4 NOT NULL DEFAULT nextval('game_library_id_seq'::regclass),
"user_id" int4 NOT NULL,
"game_id" int4 NOT NULL,
CONSTRAINT "game_library_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id"),
CONSTRAINT "game_library_game_id_fkey" FOREIGN KEY ("game_id") REFERENCES "public"."game"("id"),
PRIMARY KEY ("id")
);""")
]
"""
This table is used to store property values for websites that may
contain multiple results. For example, the database version number
used in a wiki farm may be different if there are multiple database
backends. Using multiprops we can store all of the various versions
used as well as the time periods involved.
This migration implements part of the manually created Apiary DB
that WikiApiary launched with.
"""
from yoyo import step
step(
"CREATE TABLE `apiary_multiprops` ( \
`website_id` int(11) NOT NULL, \
`t_name` varchar(255) NOT NULL, \
`t_value` varchar(255) NOT NULL, \
`first_date` datetime NOT NULL, \
`last_date` datetime NOT NULL, \
`occurrences` int(11) NOT NULL, \
PRIMARY KEY (`website_id`,`t_name`,`t_value`) \
) ENGINE=InnoDB DEFAULT CHARSET=utf8",
"DROP TABLE `apiary_multiprops`",
)
"""
Add traffic source
"""
from yoyo import step
__depends__ = {'20171127_01_isVxy-add-tracking-for-pp'}
steps = [
step("ALTER TABLE `user` ADD COLUMN `trafficSource` VARCHAR (50)",
"ALTER TABLE `user` DROP COLUMN `trafficSource`")
]
"""
Create backers table
"""
from yoyo import step
__depends__ = {'flint_20160629_01_0FFYh'}
step(
"CREATE TABLE backers (id INTEGER PRIMARY KEY, name VARCHAR(20) NOT NULL UNIQUE)",
"DROP TABLE backers",
)
"""
Create local authentication backend
"""
from yoyo import step
__depends__ = {'20180218_01_FjwIl-add-initial-tables'}
steps = [
step(
"""
INSERT INTO auth_backends (uuid, name) VALUES ((lower(hex(randomblob(4))) || '-' || lower(hex(randomblob(2))) || '-4' || substr(lower(hex(randomblob(2))),2) || '-' || substr('89ab',abs(random()) % 4 + 1, 1) || substr(lower(hex(randomblob(2))),2) || '-' || lower(hex(randomblob(6)))), 'Local')
""", """DELETE FROM auth_backends WHERE name='Local' """)
]
from yoyo import step
step(
"CREATE TABLE IF NOT EXISTS vanish_log (pokemon text, cp real, iv real, encounter_id text, pokemon_id real, dated datetime DEFAULT CURRENT_TIMESTAMP)"
)
"""
drop-user-salt
"""
from yoyo import step
__depends__ = {'20190913_01_rb2OO-create-users'}
steps = [step("ALTER TABLE User DROP password_salt;")]
"""
Added currency to Rent model
"""
from yoyo import step
__depends__ = {'20160411_01_ylLTx-added-iphash-and-createdat-to-rent-model'}
steps = [
step("ALTER TABLE rent ADD currency VARCHAR(3) NOT NULL DEFAULT 'EUR'"),
]
from yoyo import step, transaction
transaction(
step("ALTER TABLE summits ADD COLUMN coordinates point"),
step("UPDATE summits SET coordinates=point(lat, lng)"),
step("ALTER TABLE summits DROP COLUMN lat"),
step("ALTER TABLE summits DROP COLUMN lng"),
step("ALTER TABLE summits ALTER COLUMN coordinates SET NOT NULL")
)
#
# file: migrations/0001.create.py
#
from yoyo import step
step(
"""CREATE TABLE tweets (
id serial PRIMARY KEY,
tweet varchar (300) NOT NULL,
created_at timestamptz NOT NULL,
label_s boolean,
pred_s boolean,
pred_p real
)
""",
"DROP TABLE tweets",
)
"""
make end in timeentry nullable
"""
from yoyo import step
__depends__ = {'20191014_01_GoMDS-trigger-password-change-logout'}
steps = [
step("""ALTER TABLE `cs542`.`timeentry`
CHANGE COLUMN `end` `end` TIMESTAMP NULL,
CHANGE COLUMN `total_time` `total_time` decimal(5,2) AS (TIMESTAMPDIFF(second, start, end) / 3600) stored;"""
)
]
