9394559170

Committed 26 Apr 2024 06:36AM UTC coverage: 94.656% (-0.02%) from 94.674%

Build # 9394559170

Build Type

push

github

Committed by

xmatthias

Commit Message

Loader should be passed as kwarg for clarity

Run Details

20280 of 21425 relevant lines covered (94.66%)

0.95 hits per line

Source File
Press 'n' to go to next uncovered line, 'b' for previous

98.72

/freqtrade/persistence/migrations.py

import logging
from typing import List, Optional

from sqlalchemy import inspect, select, text, update

from freqtrade.exceptions import OperationalException
from freqtrade.persistence.trade_model import Order, Trade


logger = logging.getLogger(__name__)


def get_table_names_for_table(inspector, tabletype) -> List[str]:
    return [t for t in inspector.get_table_names() if t.startswith(tabletype)]


def has_column(columns: List, searchname: str) -> bool:
    return len(list(filter(lambda x: x["name"] == searchname, columns))) == 1


def get_column_def(columns: List, column: str, default: str) -> str:
    return default if not has_column(columns, column) else column


def get_backup_name(tabs: List[str], backup_prefix: str):
    table_back_name = backup_prefix
    for i, table_back_name in enumerate(tabs):
        table_back_name = f'{backup_prefix}{i}'
        logger.debug(f'trying {table_back_name}')

    return table_back_name


def get_last_sequence_ids(engine, trade_back_name: str, order_back_name: str):
    order_id: Optional[int] = None
    trade_id: Optional[int] = None

    if engine.name == 'postgresql':
        with engine.begin() as connection:
            trade_id = connection.execute(text("select nextval('trades_id_seq')")).fetchone()[0]
            order_id = connection.execute(text("select nextval('orders_id_seq')")).fetchone()[0]
        with engine.begin() as connection:
            connection.execute(text(
                f"ALTER SEQUENCE orders_id_seq rename to {order_back_name}_id_seq_bak"))
            connection.execute(text(
                f"ALTER SEQUENCE trades_id_seq rename to {trade_back_name}_id_seq_bak"))
    return order_id, trade_id


def set_sequence_ids(engine, order_id, trade_id, pairlock_id=None):

    if engine.name == 'postgresql':
        with engine.begin() as connection:
            if order_id:
                connection.execute(text(f"ALTER SEQUENCE orders_id_seq RESTART WITH {order_id}"))
            if trade_id:
                connection.execute(text(f"ALTER SEQUENCE trades_id_seq RESTART WITH {trade_id}"))
            if pairlock_id:
                connection.execute(
                    text(f"ALTER SEQUENCE pairlocks_id_seq RESTART WITH {pairlock_id}"))


def drop_index_on_table(engine, inspector, table_bak_name):
    with engine.begin() as connection:
        # drop indexes on backup table in new session
        for index in inspector.get_indexes(table_bak_name):
            if engine.name == 'mysql':
                connection.execute(text(f"drop index {index['name']} on {table_bak_name}"))
            else:
                connection.execute(text(f"drop index {index['name']}"))


def migrate_trades_and_orders_table(
        decl_base, inspector, engine,
        trade_back_name: str, cols: List,
        order_back_name: str, cols_order: List):
    base_currency = get_column_def(cols, 'base_currency', 'null')
    stake_currency = get_column_def(cols, 'stake_currency', 'null')
    fee_open = get_column_def(cols, 'fee_open', 'fee')
    fee_open_cost = get_column_def(cols, 'fee_open_cost', 'null')
    fee_open_currency = get_column_def(cols, 'fee_open_currency', 'null')
    fee_close = get_column_def(cols, 'fee_close', 'fee')
    fee_close_cost = get_column_def(cols, 'fee_close_cost', 'null')
    fee_close_currency = get_column_def(cols, 'fee_close_currency', 'null')
    open_rate_requested = get_column_def(cols, 'open_rate_requested', 'null')
    close_rate_requested = get_column_def(cols, 'close_rate_requested', 'null')
    stop_loss = get_column_def(cols, 'stop_loss', '0.0')
    stop_loss_pct = get_column_def(cols, 'stop_loss_pct', 'null')
    initial_stop_loss = get_column_def(cols, 'initial_stop_loss', '0.0')
    initial_stop_loss_pct = get_column_def(cols, 'initial_stop_loss_pct', 'null')
    is_stop_loss_trailing = get_column_def(
        cols, 'is_stop_loss_trailing',
        f'coalesce({stop_loss_pct}, 0.0) <> coalesce({initial_stop_loss_pct}, 0.0)')
    max_rate = get_column_def(cols, 'max_rate', '0.0')
    min_rate = get_column_def(cols, 'min_rate', 'null')
    exit_reason = get_column_def(cols, 'sell_reason', get_column_def(cols, 'exit_reason', 'null'))
    strategy = get_column_def(cols, 'strategy', 'null')
    enter_tag = get_column_def(cols, 'buy_tag', get_column_def(cols, 'enter_tag', 'null'))
    realized_profit = get_column_def(cols, 'realized_profit', '0.0')

    trading_mode = get_column_def(cols, 'trading_mode', 'null')

    # Leverage Properties
    leverage = get_column_def(cols, 'leverage', '1.0')
    liquidation_price = get_column_def(cols, 'liquidation_price',
                                       get_column_def(cols, 'isolated_liq', 'null'))
    # sqlite does not support literals for booleans
    if engine.name == 'postgresql':
        is_short = get_column_def(cols, 'is_short', 'false')
    else:
        is_short = get_column_def(cols, 'is_short', '0')

    # Futures Properties
    interest_rate = get_column_def(cols, 'interest_rate', '0.0')
    funding_fees = get_column_def(cols, 'funding_fees', '0.0')
    funding_fee_running = get_column_def(cols, 'funding_fee_running', 'null')
    max_stake_amount = get_column_def(cols, 'max_stake_amount', 'stake_amount')

    # If ticker-interval existed use that, else null.
    if has_column(cols, 'ticker_interval'):
        timeframe = get_column_def(cols, 'timeframe', 'ticker_interval')
    else:
        timeframe = get_column_def(cols, 'timeframe', 'null')

    open_trade_value = get_column_def(cols, 'open_trade_value',
                                      f'amount * open_rate * (1 + {fee_open})')
    close_profit_abs = get_column_def(
        cols, 'close_profit_abs',
        f"(amount * close_rate * (1 - {fee_close})) - {open_trade_value}")
    exit_order_status = get_column_def(cols, 'exit_order_status',
                                       get_column_def(cols, 'sell_order_status', 'null'))
    amount_requested = get_column_def(cols, 'amount_requested', 'amount')

    amount_precision = get_column_def(cols, 'amount_precision', 'null')
    price_precision = get_column_def(cols, 'price_precision', 'null')
    precision_mode = get_column_def(cols, 'precision_mode', 'null')
    contract_size = get_column_def(cols, 'contract_size', 'null')

    # Schema migration necessary
    with engine.begin() as connection:
        connection.execute(text(f"alter table trades rename to {trade_back_name}"))

    drop_index_on_table(engine, inspector, trade_back_name)

    order_id, trade_id = get_last_sequence_ids(engine, trade_back_name, order_back_name)

    drop_orders_table(engine, order_back_name)

    # let SQLAlchemy create the schema as required
    decl_base.metadata.create_all(engine)

    # Copy data back - following the correct schema
    with engine.begin() as connection:
        connection.execute(text(f"""insert into trades
            (id, exchange, pair, base_currency, stake_currency, is_open,
            fee_open, fee_open_cost, fee_open_currency,
            fee_close, fee_close_cost, fee_close_currency, open_rate,
            open_rate_requested, close_rate, close_rate_requested, close_profit,
            stake_amount, amount, amount_requested, open_date, close_date,
            stop_loss, stop_loss_pct, initial_stop_loss, initial_stop_loss_pct,
            is_stop_loss_trailing,
            max_rate, min_rate, exit_reason, exit_order_status, strategy, enter_tag,
            timeframe, open_trade_value, close_profit_abs,
            trading_mode, leverage, liquidation_price, is_short,
            interest_rate, funding_fees, funding_fee_running, realized_profit,
            amount_precision, price_precision, precision_mode, contract_size,
            max_stake_amount
            )
        select id, lower(exchange), pair, {base_currency} base_currency,
            {stake_currency} stake_currency,
            is_open, {fee_open} fee_open, {fee_open_cost} fee_open_cost,
            {fee_open_currency} fee_open_currency, {fee_close} fee_close,
            {fee_close_cost} fee_close_cost, {fee_close_currency} fee_close_currency,
            open_rate, {open_rate_requested} open_rate_requested, close_rate,
            {close_rate_requested} close_rate_requested, close_profit,
            stake_amount, amount, {amount_requested}, open_date, close_date,
            {stop_loss} stop_loss, {stop_loss_pct} stop_loss_pct,
            {initial_stop_loss} initial_stop_loss,
            {initial_stop_loss_pct} initial_stop_loss_pct,
            {is_stop_loss_trailing} is_stop_loss_trailing,
            {max_rate} max_rate, {min_rate} min_rate,
            case when {exit_reason} = 'sell_signal' then 'exit_signal'
                 when {exit_reason} = 'custom_sell' then 'custom_exit'
                 when {exit_reason} = 'force_sell' then 'force_exit'
                 when {exit_reason} = 'emergency_sell' then 'emergency_exit'
                 else {exit_reason}
            end exit_reason,
            {exit_order_status} exit_order_status,
            {strategy} strategy, {enter_tag} enter_tag, {timeframe} timeframe,
            {open_trade_value} open_trade_value, {close_profit_abs} close_profit_abs,
            {trading_mode} trading_mode, {leverage} leverage, {liquidation_price} liquidation_price,
            {is_short} is_short, {interest_rate} interest_rate,
            {funding_fees} funding_fees, {funding_fee_running} funding_fee_running,
            {realized_profit} realized_profit,
            {amount_precision} amount_precision, {price_precision} price_precision,
            {precision_mode} precision_mode, {contract_size} contract_size,
            {max_stake_amount} max_stake_amount
            from {trade_back_name}
            """))

    migrate_orders_table(engine, order_back_name, cols_order)
    set_sequence_ids(engine, order_id, trade_id)


def drop_orders_table(engine, table_back_name: str):
    # Drop and recreate orders table as backup
    # This drops foreign keys, too.

    with engine.begin() as connection:
        connection.execute(text(f"create table {table_back_name} as select * from orders"))
        connection.execute(text("drop table orders"))


def migrate_orders_table(engine, table_back_name: str, cols_order: List):

    ft_fee_base = get_column_def(cols_order, 'ft_fee_base', 'null')
    average = get_column_def(cols_order, 'average', 'null')
    stop_price = get_column_def(cols_order, 'stop_price', 'null')
    funding_fee = get_column_def(cols_order, 'funding_fee', '0.0')
    ft_amount = get_column_def(cols_order, 'ft_amount', 'coalesce(amount, 0.0)')
    ft_price = get_column_def(cols_order, 'ft_price', 'coalesce(price, 0.0)')
    ft_cancel_reason = get_column_def(cols_order, 'ft_cancel_reason', 'null')
    ft_order_tag = get_column_def(cols_order, 'ft_order_tag', 'null')

    # sqlite does not support literals for booleans
    with engine.begin() as connection:
        connection.execute(text(f"""
            insert into orders (id, ft_trade_id, ft_order_side, ft_pair, ft_is_open, order_id,
            status, symbol, order_type, side, price, amount, filled, average, remaining, cost,
            stop_price, order_date, order_filled_date, order_update_date, ft_fee_base, funding_fee,
            ft_amount, ft_price, ft_cancel_reason, ft_order_tag
            )
            select id, ft_trade_id, ft_order_side, ft_pair, ft_is_open, order_id,
            status, symbol, order_type, side, price, amount, filled, {average} average, remaining,
            cost, {stop_price} stop_price, order_date, order_filled_date,
            order_update_date, {ft_fee_base} ft_fee_base, {funding_fee} funding_fee,
            {ft_amount} ft_amount, {ft_price} ft_price, {ft_cancel_reason} ft_cancel_reason,
            {ft_order_tag} ft_order_tag
            from {table_back_name}
            """))


def migrate_pairlocks_table(
        decl_base, inspector, engine,
        pairlock_back_name: str, cols: List):

    # Schema migration necessary
    with engine.begin() as connection:
        connection.execute(text(f"alter table pairlocks rename to {pairlock_back_name}"))

    drop_index_on_table(engine, inspector, pairlock_back_name)

    side = get_column_def(cols, 'side', "'*'")

    # let SQLAlchemy create the schema as required
    decl_base.metadata.create_all(engine)
    # Copy data back - following the correct schema
    with engine.begin() as connection:
        connection.execute(text(f"""insert into pairlocks
        (id, pair, side, reason, lock_time,
         lock_end_time, active)
        select id, pair, {side} side, reason, lock_time,
         lock_end_time, active
        from {pairlock_back_name}
        """))


def set_sqlite_to_wal(engine):
    if engine.name == 'sqlite' and str(engine.url) != 'sqlite://':
        # Set Mode to
        with engine.begin() as connection:
            connection.execute(text("PRAGMA journal_mode=wal"))


def fix_old_dry_orders(engine):
    with engine.begin() as connection:

        # Update current dry-run Orders where
        # - stoploss order is Open (will be replaced eventually)
        # 2nd query:
        # - current Order is open
        # - current Trade is closed
        # - current Order trade_id not equal to current Trade.id
        # - current Order not stoploss

        stmt = update(Order).where(
            Order.ft_is_open.is_(True),
            Order.ft_order_side == 'stoploss',
            Order.order_id.like('dry%'),

        ).values(ft_is_open=False)
        connection.execute(stmt)

        # Close dry-run orders for closed trades.
        stmt = update(Order).where(
            Order.ft_is_open.is_(True),
            Order.ft_trade_id.not_in(
                select(
                    Trade.id
                ).where(Trade.is_open.is_(True))
                  ),
            Order.ft_order_side != 'stoploss',
            Order.order_id.like('dry%')

        ).values(ft_is_open=False)
        connection.execute(stmt)


def check_migrate(engine, decl_base, previous_tables) -> None:
    """
    Checks if migration is necessary and migrates if necessary
    """
    inspector = inspect(engine)

    cols_trades = inspector.get_columns('trades')
    cols_orders = inspector.get_columns('orders')
    cols_pairlocks = inspector.get_columns('pairlocks')
    tabs = get_table_names_for_table(inspector, 'trades')
    table_back_name = get_backup_name(tabs, 'trades_bak')
    order_tabs = get_table_names_for_table(inspector, 'orders')
    order_table_bak_name = get_backup_name(order_tabs, 'orders_bak')
    pairlock_tabs = get_table_names_for_table(inspector, 'pairlocks')
    pairlock_table_bak_name = get_backup_name(pairlock_tabs, 'pairlocks_bak')

    # Check if migration necessary
    # Migrates both trades and orders table!
    # if ('orders' not in previous_tables
    # or not has_column(cols_orders, 'funding_fee')):
    migrating = False
    # if not has_column(cols_trades, 'funding_fee_running'):
    if not has_column(cols_orders, 'ft_order_tag'):
        migrating = True
        logger.info(f"Running database migration for trades - "
                    f"backup: {table_back_name}, {order_table_bak_name}")
        migrate_trades_and_orders_table(
            decl_base, inspector, engine, table_back_name, cols_trades,
            order_table_bak_name, cols_orders)

    if not has_column(cols_pairlocks, 'side'):
        migrating = True
        logger.info(f"Running database migration for pairlocks - "
                    f"backup: {pairlock_table_bak_name}")

        migrate_pairlocks_table(
            decl_base, inspector, engine, pairlock_table_bak_name, cols_pairlocks
        )
    if 'orders' not in previous_tables and 'trades' in previous_tables:
        raise OperationalException(
            "Your database seems to be very old. "
            "Please update to freqtrade 2022.3 to migrate this database or "
            "start with a fresh database.")

    set_sqlite_to_wal(engine)
    fix_old_dry_orders(engine)

    if migrating:
        logger.info("Database migration finished.")

1	import logging	1✔
2	from typing import List, Optional	1✔
3
4	from sqlalchemy import inspect, select, text, update	1✔
5
6	from freqtrade.exceptions import OperationalException	1✔
7	from freqtrade.persistence.trade_model import Order, Trade	1✔
8
9
10	logger = logging.getLogger(__name__)	1✔
11
12
13	def get_table_names_for_table(inspector, tabletype) -> List[str]:	1✔
14	return [t for t in inspector.get_table_names() if t.startswith(tabletype)]	1✔
15
16
17	def has_column(columns: List, searchname: str) -> bool:	1✔
18	return len(list(filter(lambda x: x["name"] == searchname, columns))) == 1	1✔
19
20
21	def get_column_def(columns: List, column: str, default: str) -> str:	1✔
22	return default if not has_column(columns, column) else column	1✔
23
24
25	def get_backup_name(tabs: List[str], backup_prefix: str):	1✔
26	table_back_name = backup_prefix	1✔
27	for i, table_back_name in enumerate(tabs):	1✔
28	table_back_name = f'{backup_prefix}{i}'	1✔
29	logger.debug(f'trying {table_back_name}')	1✔
30
31	return table_back_name	1✔
32
33
34	def get_last_sequence_ids(engine, trade_back_name: str, order_back_name: str):	1✔
35	order_id: Optional[int] = None	1✔
36	trade_id: Optional[int] = None	1✔
37
38	if engine.name == 'postgresql':	1✔
39	with engine.begin() as connection:	1✔
40	trade_id = connection.execute(text("select nextval('trades_id_seq')")).fetchone()[0]	1✔
41	order_id = connection.execute(text("select nextval('orders_id_seq')")).fetchone()[0]	1✔
42	with engine.begin() as connection:	1✔
43	connection.execute(text(	1✔
44	f"ALTER SEQUENCE orders_id_seq rename to {order_back_name}_id_seq_bak"))
45	connection.execute(text(	1✔
46	f"ALTER SEQUENCE trades_id_seq rename to {trade_back_name}_id_seq_bak"))
47	return order_id, trade_id	1✔
48
49
50	def set_sequence_ids(engine, order_id, trade_id, pairlock_id=None):	1✔
51
52	if engine.name == 'postgresql':	1✔
53	with engine.begin() as connection:	1✔
54	if order_id:	1✔
55	connection.execute(text(f"ALTER SEQUENCE orders_id_seq RESTART WITH {order_id}"))	1✔
56	if trade_id:	1✔
57	connection.execute(text(f"ALTER SEQUENCE trades_id_seq RESTART WITH {trade_id}"))	1✔
58	if pairlock_id:	1✔
59	connection.execute(	1✔
60	text(f"ALTER SEQUENCE pairlocks_id_seq RESTART WITH {pairlock_id}"))
61
62
63	def drop_index_on_table(engine, inspector, table_bak_name):	1✔
64	with engine.begin() as connection:	1✔
65	# drop indexes on backup table in new session
66	for index in inspector.get_indexes(table_bak_name):	1✔
67	if engine.name == 'mysql':	1✔
68	connection.execute(text(f"drop index {index['name']} on {table_bak_name}"))	×
69	else:
70	connection.execute(text(f"drop index {index['name']}"))	1✔
71
72
73	def migrate_trades_and_orders_table(	1✔
74	decl_base, inspector, engine,
75	trade_back_name: str, cols: List,
76	order_back_name: str, cols_order: List):
77	base_currency = get_column_def(cols, 'base_currency', 'null')	1✔
78	stake_currency = get_column_def(cols, 'stake_currency', 'null')	1✔
79	fee_open = get_column_def(cols, 'fee_open', 'fee')	1✔
80	fee_open_cost = get_column_def(cols, 'fee_open_cost', 'null')	1✔
81	fee_open_currency = get_column_def(cols, 'fee_open_currency', 'null')	1✔
82	fee_close = get_column_def(cols, 'fee_close', 'fee')	1✔
83	fee_close_cost = get_column_def(cols, 'fee_close_cost', 'null')	1✔
84	fee_close_currency = get_column_def(cols, 'fee_close_currency', 'null')	1✔
85	open_rate_requested = get_column_def(cols, 'open_rate_requested', 'null')	1✔
86	close_rate_requested = get_column_def(cols, 'close_rate_requested', 'null')	1✔
87	stop_loss = get_column_def(cols, 'stop_loss', '0.0')	1✔
88	stop_loss_pct = get_column_def(cols, 'stop_loss_pct', 'null')	1✔
89	initial_stop_loss = get_column_def(cols, 'initial_stop_loss', '0.0')	1✔
90	initial_stop_loss_pct = get_column_def(cols, 'initial_stop_loss_pct', 'null')	1✔
91	is_stop_loss_trailing = get_column_def(	1✔
92	cols, 'is_stop_loss_trailing',
93	f'coalesce({stop_loss_pct}, 0.0) <> coalesce({initial_stop_loss_pct}, 0.0)')
94	max_rate = get_column_def(cols, 'max_rate', '0.0')	1✔
95	min_rate = get_column_def(cols, 'min_rate', 'null')	1✔
96	exit_reason = get_column_def(cols, 'sell_reason', get_column_def(cols, 'exit_reason', 'null'))	1✔
97	strategy = get_column_def(cols, 'strategy', 'null')	1✔
98	enter_tag = get_column_def(cols, 'buy_tag', get_column_def(cols, 'enter_tag', 'null'))	1✔
99	realized_profit = get_column_def(cols, 'realized_profit', '0.0')	1✔
100
101	trading_mode = get_column_def(cols, 'trading_mode', 'null')	1✔
102
103	# Leverage Properties
104	leverage = get_column_def(cols, 'leverage', '1.0')	1✔
105	liquidation_price = get_column_def(cols, 'liquidation_price',	1✔
106	get_column_def(cols, 'isolated_liq', 'null'))
107	# sqlite does not support literals for booleans
108	if engine.name == 'postgresql':	1✔
109	is_short = get_column_def(cols, 'is_short', 'false')	×
110	else:
111	is_short = get_column_def(cols, 'is_short', '0')	1✔
112
113	# Futures Properties
114	interest_rate = get_column_def(cols, 'interest_rate', '0.0')	1✔
115	funding_fees = get_column_def(cols, 'funding_fees', '0.0')	1✔
116	funding_fee_running = get_column_def(cols, 'funding_fee_running', 'null')	1✔
117	max_stake_amount = get_column_def(cols, 'max_stake_amount', 'stake_amount')	1✔
118
119	# If ticker-interval existed use that, else null.
120	if has_column(cols, 'ticker_interval'):	1✔
121	timeframe = get_column_def(cols, 'timeframe', 'ticker_interval')	1✔
122	else:
123	timeframe = get_column_def(cols, 'timeframe', 'null')	1✔
124
125	open_trade_value = get_column_def(cols, 'open_trade_value',	1✔
126	f'amount * open_rate * (1 + {fee_open})')
127	close_profit_abs = get_column_def(	1✔
128	cols, 'close_profit_abs',
129	f"(amount * close_rate * (1 - {fee_close})) - {open_trade_value}")
130	exit_order_status = get_column_def(cols, 'exit_order_status',	1✔
131	get_column_def(cols, 'sell_order_status', 'null'))
132	amount_requested = get_column_def(cols, 'amount_requested', 'amount')	1✔
133
134	amount_precision = get_column_def(cols, 'amount_precision', 'null')	1✔
135	price_precision = get_column_def(cols, 'price_precision', 'null')	1✔
136	precision_mode = get_column_def(cols, 'precision_mode', 'null')	1✔
137	contract_size = get_column_def(cols, 'contract_size', 'null')	1✔
138
139	# Schema migration necessary
140	with engine.begin() as connection:	1✔
141	connection.execute(text(f"alter table trades rename to {trade_back_name}"))	1✔
142
143	drop_index_on_table(engine, inspector, trade_back_name)	1✔
144
145	order_id, trade_id = get_last_sequence_ids(engine, trade_back_name, order_back_name)	1✔
146
147	drop_orders_table(engine, order_back_name)	1✔
148
149	# let SQLAlchemy create the schema as required
150	decl_base.metadata.create_all(engine)	1✔
151
152	# Copy data back - following the correct schema
153	with engine.begin() as connection:	1✔
154	connection.execute(text(f"""insert into trades	1✔
155	(id, exchange, pair, base_currency, stake_currency, is_open,
156	fee_open, fee_open_cost, fee_open_currency,
157	fee_close, fee_close_cost, fee_close_currency, open_rate,
158	open_rate_requested, close_rate, close_rate_requested, close_profit,
159	stake_amount, amount, amount_requested, open_date, close_date,
160	stop_loss, stop_loss_pct, initial_stop_loss, initial_stop_loss_pct,
161	is_stop_loss_trailing,
162	max_rate, min_rate, exit_reason, exit_order_status, strategy, enter_tag,
163	timeframe, open_trade_value, close_profit_abs,
164	trading_mode, leverage, liquidation_price, is_short,
165	interest_rate, funding_fees, funding_fee_running, realized_profit,
166	amount_precision, price_precision, precision_mode, contract_size,
167	max_stake_amount
168	)
169	select id, lower(exchange), pair, {base_currency} base_currency,
170	{stake_currency} stake_currency,
171	is_open, {fee_open} fee_open, {fee_open_cost} fee_open_cost,
172	{fee_open_currency} fee_open_currency, {fee_close} fee_close,
173	{fee_close_cost} fee_close_cost, {fee_close_currency} fee_close_currency,
174	open_rate, {open_rate_requested} open_rate_requested, close_rate,
175	{close_rate_requested} close_rate_requested, close_profit,
176	stake_amount, amount, {amount_requested}, open_date, close_date,
177	{stop_loss} stop_loss, {stop_loss_pct} stop_loss_pct,
178	{initial_stop_loss} initial_stop_loss,
179	{initial_stop_loss_pct} initial_stop_loss_pct,
180	{is_stop_loss_trailing} is_stop_loss_trailing,
181	{max_rate} max_rate, {min_rate} min_rate,
182	case when {exit_reason} = 'sell_signal' then 'exit_signal'
183	when {exit_reason} = 'custom_sell' then 'custom_exit'
184	when {exit_reason} = 'force_sell' then 'force_exit'
185	when {exit_reason} = 'emergency_sell' then 'emergency_exit'
186	else {exit_reason}
187	end exit_reason,
188	{exit_order_status} exit_order_status,
189	{strategy} strategy, {enter_tag} enter_tag, {timeframe} timeframe,
190	{open_trade_value} open_trade_value, {close_profit_abs} close_profit_abs,
191	{trading_mode} trading_mode, {leverage} leverage, {liquidation_price} liquidation_price,
192	{is_short} is_short, {interest_rate} interest_rate,
193	{funding_fees} funding_fees, {funding_fee_running} funding_fee_running,
194	{realized_profit} realized_profit,
195	{amount_precision} amount_precision, {price_precision} price_precision,
196	{precision_mode} precision_mode, {contract_size} contract_size,
197	{max_stake_amount} max_stake_amount
198	from {trade_back_name}
199	"""))
200
201	migrate_orders_table(engine, order_back_name, cols_order)	1✔
202	set_sequence_ids(engine, order_id, trade_id)	1✔
203
204
205	def drop_orders_table(engine, table_back_name: str):	1✔
206	# Drop and recreate orders table as backup
207	# This drops foreign keys, too.
208
209	with engine.begin() as connection:	1✔
210	connection.execute(text(f"create table {table_back_name} as select * from orders"))	1✔
211	connection.execute(text("drop table orders"))	1✔
212
213
214	def migrate_orders_table(engine, table_back_name: str, cols_order: List):	1✔
215
216	ft_fee_base = get_column_def(cols_order, 'ft_fee_base', 'null')	1✔
217	average = get_column_def(cols_order, 'average', 'null')	1✔
218	stop_price = get_column_def(cols_order, 'stop_price', 'null')	1✔
219	funding_fee = get_column_def(cols_order, 'funding_fee', '0.0')	1✔
220	ft_amount = get_column_def(cols_order, 'ft_amount', 'coalesce(amount, 0.0)')	1✔
221	ft_price = get_column_def(cols_order, 'ft_price', 'coalesce(price, 0.0)')	1✔
222	ft_cancel_reason = get_column_def(cols_order, 'ft_cancel_reason', 'null')	1✔
223	ft_order_tag = get_column_def(cols_order, 'ft_order_tag', 'null')	1✔
224
225	# sqlite does not support literals for booleans
226	with engine.begin() as connection:	1✔
227	connection.execute(text(f"""	1✔
228	insert into orders (id, ft_trade_id, ft_order_side, ft_pair, ft_is_open, order_id,
229	status, symbol, order_type, side, price, amount, filled, average, remaining, cost,
230	stop_price, order_date, order_filled_date, order_update_date, ft_fee_base, funding_fee,
231	ft_amount, ft_price, ft_cancel_reason, ft_order_tag
232	)
233	select id, ft_trade_id, ft_order_side, ft_pair, ft_is_open, order_id,
234	status, symbol, order_type, side, price, amount, filled, {average} average, remaining,
235	cost, {stop_price} stop_price, order_date, order_filled_date,
236	order_update_date, {ft_fee_base} ft_fee_base, {funding_fee} funding_fee,
237	{ft_amount} ft_amount, {ft_price} ft_price, {ft_cancel_reason} ft_cancel_reason,
238	{ft_order_tag} ft_order_tag
239	from {table_back_name}
240	"""))
241
242
243	def migrate_pairlocks_table(	1✔
244	decl_base, inspector, engine,
245	pairlock_back_name: str, cols: List):
246
247	# Schema migration necessary
248	with engine.begin() as connection:	1✔
249	connection.execute(text(f"alter table pairlocks rename to {pairlock_back_name}"))	1✔
250
251	drop_index_on_table(engine, inspector, pairlock_back_name)	1✔
252
253	side = get_column_def(cols, 'side', "'*'")	1✔
254
255	# let SQLAlchemy create the schema as required
256	decl_base.metadata.create_all(engine)	1✔
257	# Copy data back - following the correct schema
258	with engine.begin() as connection:	1✔
259	connection.execute(text(f"""insert into pairlocks	1✔
260	(id, pair, side, reason, lock_time,
261	lock_end_time, active)
262	select id, pair, {side} side, reason, lock_time,
263	lock_end_time, active
264	from {pairlock_back_name}
265	"""))
266
267
268	def set_sqlite_to_wal(engine):	1✔
269	if engine.name == 'sqlite' and str(engine.url) != 'sqlite://':	1✔
270	# Set Mode to
271	with engine.begin() as connection:	1✔
272	connection.execute(text("PRAGMA journal_mode=wal"))	1✔
273
274
275	def fix_old_dry_orders(engine):	1✔
276	with engine.begin() as connection:	1✔
277
278	# Update current dry-run Orders where
279	# - stoploss order is Open (will be replaced eventually)
280	# 2nd query:
281	# - current Order is open
282	# - current Trade is closed
283	# - current Order trade_id not equal to current Trade.id
284	# - current Order not stoploss
285
286	stmt = update(Order).where(	1✔
287	Order.ft_is_open.is_(True),
288	Order.ft_order_side == 'stoploss',
289	Order.order_id.like('dry%'),
290
291	).values(ft_is_open=False)
292	connection.execute(stmt)	1✔
293
294	# Close dry-run orders for closed trades.
295	stmt = update(Order).where(	1✔
296	Order.ft_is_open.is_(True),
297	Order.ft_trade_id.not_in(
298	select(
299	Trade.id
300	).where(Trade.is_open.is_(True))
301	),
302	Order.ft_order_side != 'stoploss',
303	Order.order_id.like('dry%')
304
305	).values(ft_is_open=False)
306	connection.execute(stmt)	1✔
307
308
309	def check_migrate(engine, decl_base, previous_tables) -> None:	1✔
310	"""
311	Checks if migration is necessary and migrates if necessary
312	"""
313	inspector = inspect(engine)	1✔
314
315	cols_trades = inspector.get_columns('trades')	1✔
316	cols_orders = inspector.get_columns('orders')	1✔
317	cols_pairlocks = inspector.get_columns('pairlocks')	1✔
318	tabs = get_table_names_for_table(inspector, 'trades')	1✔
319	table_back_name = get_backup_name(tabs, 'trades_bak')	1✔
320	order_tabs = get_table_names_for_table(inspector, 'orders')	1✔
321	order_table_bak_name = get_backup_name(order_tabs, 'orders_bak')	1✔
322	pairlock_tabs = get_table_names_for_table(inspector, 'pairlocks')	1✔
323	pairlock_table_bak_name = get_backup_name(pairlock_tabs, 'pairlocks_bak')	1✔
324
325	# Check if migration necessary
326	# Migrates both trades and orders table!
327	# if ('orders' not in previous_tables
328	# or not has_column(cols_orders, 'funding_fee')):
329	migrating = False	1✔
330	# if not has_column(cols_trades, 'funding_fee_running'):
331	if not has_column(cols_orders, 'ft_order_tag'):	1✔
332	migrating = True	1✔
333	logger.info(f"Running database migration for trades - "	1✔
334	f"backup: {table_back_name}, {order_table_bak_name}")
335	migrate_trades_and_orders_table(	1✔
336	decl_base, inspector, engine, table_back_name, cols_trades,
337	order_table_bak_name, cols_orders)
338
339	if not has_column(cols_pairlocks, 'side'):	1✔
340	migrating = True	1✔
341	logger.info(f"Running database migration for pairlocks - "	1✔
342	f"backup: {pairlock_table_bak_name}")
343
344	migrate_pairlocks_table(	1✔
345	decl_base, inspector, engine, pairlock_table_bak_name, cols_pairlocks
346	)
347	if 'orders' not in previous_tables and 'trades' in previous_tables:	1✔
348	raise OperationalException(	1✔
349	"Your database seems to be very old. "
350	"Please update to freqtrade 2022.3 to migrate this database or "
351	"start with a fresh database.")
352
353	set_sqlite_to_wal(engine)	1✔
354	fix_old_dry_orders(engine)	1✔
355
356	if migrating:	1✔
357	logger.info("Database migration finished.")	1✔

freqtrade / freqtrade / 9394559170

Source File Press 'n' to go to next uncovered line, 'b' for previous

Source File
Press 'n' to go to next uncovered line, 'b' for previous