Python yoyo.step函数代码示例

"""
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`;"
)

from yoyo import step

step("""
CREATE TABLE movements (
  id     SERIAL,
  token  VARCHAR,
  amount BIGINT
);
    """,
    """DROP TABLE movements;""")

"""
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"),
]

"""
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, 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
        )""")
)

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(
    "CREATE TABLE IF NOT EXISTS softban_log (status text, source text, dated datetime DEFAULT CURRENT_TIMESTAMP)"
)

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'")
)

"""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;",
)

"""
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"),
]

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")
'''

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`",
)

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''',
)

"""
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`",
)

from yoyo import step

step("""
CREATE TABLE prices (
  id     SERIAL,
  bytes  BIGINT,
  amount BIGINT
);
    """,
    """DROP TABLE prices;""")