def downgrade():
from inbox.ignition import main_engine
engine = main_engine(pool_size=1, max_overflow=0)
if not engine.has_table('easfoldersyncstatus'):
return
op.drop_column('easfoldersyncstatus', 'initial_sync_start')
op.drop_column('easfoldersyncstatus', 'initial_sync_end')
def upgrade():
from inbox.models.session import session_scope
from inbox.util.html import strip_tags
from inbox.ignition import main_engine
engine = main_engine(pool_size=1, max_overflow=0)
Base = declarative_base()
Base.metadata.reflect(engine)
SNIPPET_LENGTH = 191
class Message(Base):
__table__ = Base.metadata.tables['message']
def calculate_html_snippet(msg, text):
text = text.replace('<br>', ' ').replace('<br/>', ' '). \
replace('<br />', ' ')
text = strip_tags(text)
calculate_plaintext_snippet(msg, text)
def calculate_plaintext_snippet(msg, text):
msg.snippet = ' '.join(text.split())[:SNIPPET_LENGTH]
with session_scope(ignore_soft_deletes=False, versioned=False)\
as db_session:
for message in page_query(db_session.query(Message)):
if not message.decode_error:
calculate_html_snippet(message, message.sanitized_body)
db_session.commit()
def upgrade():
from inbox.ignition import main_engine
engine = main_engine()
if not engine.has_table("easaccount"):
return
# We allow nullable=True because we don't have usernames for existing accounts.
# Furthermore, we won't always get a username.
from inbox.models.constants import MAX_INDEXABLE_LENGTH
op.add_column("easaccount", sa.Column("username", sa.String(255), nullable=True))
op.add_column("easaccount", sa.Column("eas_auth", sa.String(MAX_INDEXABLE_LENGTH), nullable=True))
Base = sa.ext.declarative.declarative_base()
Base.metadata.reflect(engine)
from inbox.models.session import session_scope
class EASAccount(Base):
__table__ = Base.metadata.tables["easaccount"]
with session_scope(ignore_soft_deletes=False, versioned=False) as db_session:
accts = db_session.query(EASAccount).all()
for a in accts:
a.eas_auth = a.email_address
db_session.add(a)
db_session.commit()
op.alter_column("easaccount", "eas_auth", nullable=False, existing_type=sa.String(MAX_INDEXABLE_LENGTH))
def upgrade():
if 'easfoldersyncstatus' in Base.metadata.tables:
from inbox.ignition import main_engine
engine = main_engine(pool_size=1, max_overflow=0)
from inbox.models.session import session_scope
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm.exc import NoResultFound
Base = declarative_base()
Base.metadata.reflect(engine)
from inbox.models.backends.eas import EASFolderSyncStatus
from inbox.models import Folder
from inbox.util.eas.constants import SKIP_FOLDERS
with session_scope(versioned=False, ignore_soft_deletes=False) as \
db_session:
statuses = db_session.query(EASFolderSyncStatus).filter(
EASFolderSyncStatus.eas_folder_type.in_(SKIP_FOLDERS)).all()
for s in statuses:
db_session.delete(s)
db_session.delete(s.folder)
try:
for status in db_session.query(EASFolderSyncStatus)\
.join(Folder).filter(
Folder.name == 'RecipientInfo').all():
db_session.delete(status)
db_session.delete(status.folder)
except NoResultFound:
pass
db_session.commit()
def downgrade_imapthread():
from inbox.models.session import session_scope
from inbox.ignition import main_engine
engine = main_engine(pool_size=1, max_overflow=0)
Base = declarative_base()
Base.metadata.reflect(engine)
class ImapThread_(Base):
__table__ = Base.metadata.tables['imapthread']
# Get data from table-to-be-dropped
with session_scope() as db_session:
results = db_session.query(ImapThread_.id, ImapThread_.g_thrid).all()
to_insert = [dict(id=r[0], g_thrid=r[1]) for r in results]
# Drop columns, add new columns + insert data
op.drop_column('thread', 'type')
op.add_column('thread', sa.Column('g_thrid', sa.BigInteger(),
nullable=True, index=True))
table_ = table('thread',
column('g_thrid', sa.BigInteger),
column('id', sa.Integer))
for r in to_insert:
op.execute(
table_.update().
where(table_.c.id == r['id']).
values({'g_thrid': r['g_thrid']})
)
# Drop table
op.drop_table('imapthread')
def upgrade():
from inbox.config import config
import nacl.secret
import nacl.utils
from inbox.ignition import main_engine
from inbox.models.session import session_scope
engine = main_engine(pool_size=1, max_overflow=0)
Base = sa.ext.declarative.declarative_base()
Base.metadata.reflect(engine)
class Secret(Base):
__table__ = Base.metadata.tables['secret']
class GenericAccount(Base):
__table__ = Base.metadata.tables['genericaccount']
with session_scope(ignore_soft_deletes=False, versioned=False) as \
db_session:
secrets = db_session.query(Secret).filter(
Secret.secret.isnot(None)).all()
# Join on the genericaccount and optionally easaccount tables to
# determine which secrets should have type 'password'.
generic_query = db_session.query(Secret.id).join(
GenericAccount).filter(Secret.id == GenericAccount.password_id)
password_secrets = [id_ for id_, in generic_query]
if engine.has_table('easaccount'):
class EASAccount(Base):
__table__ = Base.metadata.tables['easaccount']
eas_query = db_session.query(Secret.id).join(
EASAccount).filter(Secret.id == EASAccount.password_id)
password_secrets.extend([id_ for id_, in eas_query])
for s in secrets:
plain = s.secret.encode('utf-8') if isinstance(s.secret, unicode) \
else s.secret
if config.get_required('ENCRYPT_SECRETS'):
s._secret = nacl.secret.SecretBox(
key=config.get_required('SECRET_ENCRYPTION_KEY'),
encoder=nacl.encoding.HexEncoder
).encrypt(
plaintext=plain,
nonce=nacl.utils.random(nacl.secret.SecretBox.NONCE_SIZE))
# 1 is EncryptionScheme.SECRETBOX_WITH_STATIC_KEY
s.encryption_scheme = 1
else:
s._secret = plain
if s.id in password_secrets:
s.type = 'password'
else:
s.type = 'token'
db_session.add(s)
db_session.commit()
def downgrade():
from inbox.ignition import main_engine
engine = main_engine(pool_size=1, max_overflow=0)
if not engine.has_table('easaccount'):
return
op.drop_constraint('easaccount_ibfk_2', 'easaccount', type_='foreignkey')
op.drop_column('easaccount', 'password_id')
def upgrade():
from inbox.sqlalchemy_ext.util import JSON, MutableDict
from inbox.ignition import main_engine
engine = main_engine(pool_size=1, max_overflow=0)
from inbox.models.session import session_scope
from sqlalchemy.ext.declarative import declarative_base
op.add_column('account',
sa.Column('_sync_status', MutableDict.as_mutable(JSON()),
default={}, nullable=True))
Base = declarative_base()
Base.metadata.reflect(engine)
class Account(Base):
__table__ = Base.metadata.tables['account']
with session_scope(versioned=False, ignore_soft_deletes=False) \
as db_session:
for acct in db_session.query(Account):
d = dict(sync_start_time=str(acct.sync_start_time),
sync_end_time=str(acct.sync_end_time))
acct._sync_status = json_util.dumps(d)
db_session.commit()
op.drop_column('account', 'sync_start_time')
op.drop_column('account', 'sync_end_time')
def upgrade():
from inbox.ignition import main_engine
engine = main_engine()
Base = sa.ext.declarative.declarative_base()
Base.metadata.reflect(engine)
# We allow nullable=True because we don't have usernames for existing accounts.
# Furthermore, we won't always get a username.
if 'easaccount' in Base.metadata.tables:
from inbox.models.constants import MAX_INDEXABLE_LENGTH
from inbox.models.session import session_scope
from inbox.models.backends.eas import EASAccount
op.add_column('easaccount',
sa.Column('username', sa.String(255), nullable=True))
op.add_column('easaccount',
sa.Column('eas_auth', sa.String(MAX_INDEXABLE_LENGTH),
nullable=True))
with session_scope(ignore_soft_deletes=False, versioned=False) as \
db_session:
accts = db_session.query(EASAccount).all()
for a in accts:
a.eas_auth = a.email_address
db_session.add(a)
db_session.commit()
op.alter_column('easaccount', 'eas_auth', nullable=False,
existing_type=sa.String(MAX_INDEXABLE_LENGTH))
def downgrade():
from inbox.ignition import main_engine
engine = main_engine()
if engine.has_table('easaccount'):
op.drop_column('easaccount', 'username')
op.drop_column('easaccount', 'eas_auth')
def upgrade():
from sqlalchemy.ext.declarative import declarative_base
from inbox.models.session import session_scope
from inbox.ignition import main_engine
engine = main_engine(pool_size=1, max_overflow=0)
if not engine.has_table('easuid'):
return
Base = declarative_base()
Base.metadata.reflect(engine)
class EASUid(Base):
__table__ = Base.metadata.tables['easuid']
class EASFolderSyncStatus(Base):
__table__ = Base.metadata.tables['easfoldersyncstatus']
with session_scope(versioned=False) as db_session:
max_easuid = db_session.query(sa.func.max(EASUid.id)).scalar()
if max_easuid is None:
return
while True:
results = db_session.query(EASUid, EASFolderSyncStatus). \
join(EASFolderSyncStatus, sa.and_(
EASUid.fld_uid == EASFolderSyncStatus.eas_folder_id,
EASUid.device_id == EASFolderSyncStatus.device_id,
EASUid.easaccount_id == EASFolderSyncStatus.account_id,
EASUid.easfoldersyncstatus_id.is_(None))). \
limit(1000).all()
if not results:
return
for easuid, easfoldersyncstatus in results:
easuid.easfoldersyncstatus_id = easfoldersyncstatus.id
db_session.commit()
def downgrade():
from inbox.models.session import session_scope
from sqlalchemy.ext.declarative import declarative_base
from inbox.ignition import main_engine
engine = main_engine(pool_size=1, max_overflow=0)
Base = declarative_base()
Base.metadata.reflect(engine)
class Account(Base):
__table__ = Base.metadata.tables['account']
class ImapAccount(Base):
__table__ = Base.metadata.tables['imapaccount']
class GmailAccount(Base):
__table__ = Base.metadata.tables['gmailaccount']
class Secret(Base):
__table__ = Base.metadata.tables['secret']
op.add_column('gmailaccount', sa.Column('refresh_token',
sa.String(length=512), nullable=True))
with session_scope(versioned=False) \
as db_session:
for acct in db_session.query(GmailAccount):
secret = db_session.query(Secret) \
.filter_by(id=acct.refresh_token_id).one()
acct.refresh_token = secret.secret
db_session.add(acct)
db_session.commit()
op.drop_column('gmailaccount', 'refresh_token_id')
op.drop_table('secret')
def upgrade():
from inbox.ignition import main_engine
engine = main_engine()
Base = sa.ext.declarative.declarative_base()
Base.metadata.reflect(engine)
if 'easaccount' in Base.metadata.tables:
op.drop_column('easaccount', 'eas_state')
def __init__(self):
from inbox.ignition import main_engine
engine = main_engine()
# Set up test database
self.engine = engine
# Populate with test data
self.setup()
def downgrade():
from inbox.ignition import main_engine
engine = main_engine(pool_size=1, max_overflow=0)
if not engine.has_table('thread'):
return
op.drop_column('thread', 'receivedrecentdate')
op.drop_index('ix_thread_namespace_id_receivedrecentdate',
table_name='thread')
def downgrade():
from inbox.ignition import main_engine
engine = main_engine()
if engine.has_table("easaccount"):
op.drop_column("easaccount", "username")
op.drop_column("easaccount", "eas_auth")
def upgrade():
from inbox.ignition import main_engine
engine = main_engine(pool_size=1, max_overflow=0)
if not engine.has_table('easfoldersyncstatus'):
return
op.add_column('easfoldersyncstatus', sa.Column('sync_should_run',
sa.Boolean(), server_default=sa.sql.expression.true(),
nullable=False))
def add_eas_tables():
from inbox.ignition import main_engine
engine = main_engine(pool_size=1, max_overflow=0)
Base = declarative_base()
Base.metadata.reflect(engine)
for table_name in ['easuid', 'easfoldersync']:
if table_name in Base.metadata.tables:
table_names.add(table_name)
def downgrade():
from inbox.ignition import main_engine
engine = main_engine()
Base = sa.ext.declarative.declarative_base()
Base.metadata.reflect(engine)
if 'easthread' in Base.metadata.tables:
op.drop_index('ix_easthread_eas_thrid', table_name='easthread')
def upgrade():
import datetime
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
from inbox.models.session import session_scope
from inbox.ignition import main_engine
engine = main_engine()
now = datetime.datetime.now()
Base = declarative_base()
Base.metadata.reflect(engine)
class GmailAccount(Base):
__table__ = Base.metadata.tables['gmailaccount']
class Secret(Base):
__table__ = Base.metadata.tables['secret']
class GmailAuthCredentials(Base):
__table__ = Base.metadata.tables['gmailauthcredentials']
secret = relationship(Secret)
with session_scope(versioned=False) as db_session:
for acc, sec in db_session.query(GmailAccount, Secret) \
.filter(GmailAccount.refresh_token_id == Secret.id) \
.all():
# Create a new GmailAuthCredentials entry if
# we don't have one already
if db_session.query(GmailAuthCredentials, Secret) \
.filter(GmailAuthCredentials.gmailaccount_id == acc.id) \
.filter(Secret._secret == sec._secret) \
.count() == 0:
# Create a new secret
new_sec = Secret()
new_sec.created_at = now
new_sec.updated_at = now
new_sec._secret = sec._secret
new_sec.type = sec.type # 'token'
new_sec.encryption_scheme = sec.encryption_scheme
# Create a new GmailAuthCredentials entry
auth_creds = GmailAuthCredentials()
auth_creds.gmailaccount_id = acc.id
auth_creds.scopes = acc.scope
auth_creds.g_id_token = acc.g_id_token
auth_creds.client_id = acc.client_id
auth_creds.client_secret = acc.client_secret
auth_creds.created_at = now
auth_creds.updated_at = now
auth_creds.secret = new_sec
db_session.add(auth_creds)
db_session.add(new_sec)
db_session.commit()
def session_scope(versioned=True, ignore_soft_deletes=True, namespace_id=None):
""" Provide a transactional scope around a series of operations.
Takes care of rolling back failed transactions and closing the session
when it goes out of scope.
Note that sqlalchemy automatically starts a new database transaction when
the session is created, and restarts a new transaction after every commit()
on the session. Your database backend's transaction semantics are important
here when reasoning about concurrency.
Parameters
----------
versioned : bool
Do you want to enable the transaction log?
ignore_soft_deletes : bool
Whether or not to ignore soft-deleted objects in query results.
namespace_id : int
Namespace to limit query results with.
Yields
------
InboxSession
The created session.
"""
global cached_engine
if cached_engine is None:
cached_engine = main_engine()
log.info("Don't yet have engine... creating default from ignition",
engine=id(cached_engine))
session = InboxSession(cached_engine,
versioned=versioned,
ignore_soft_deletes=ignore_soft_deletes,
namespace_id=namespace_id)
try:
if config.get('LOG_DB_SESSIONS'):
start_time = time.time()
calling_frame = sys._getframe().f_back.f_back
call_loc = '{}:{}'.format(calling_frame.f_globals.get('__name__'),
calling_frame.f_lineno)
logger = log.bind(engine_id=id(cached_engine),
session_id=id(session), call_loc=call_loc)
logger.info('creating db_session',
sessions_used=cached_engine.pool.checkedout())
yield session
session.commit()
except:
session.rollback()
raise
finally:
if config.get('LOG_DB_SESSIONS'):
lifetime = time.time() - start_time
logger.info('closing db_session', lifetime=lifetime,
sessions_used=cached_engine.pool.checkedout())
session.close()
def downgrade():
from inbox.ignition import main_engine
engine = main_engine(pool_size=1, max_overflow=0)
if not engine.has_table('easaccount'):
return
op.drop_column('actionlog', 'type')
op.drop_table('easactionlog')
def upgrade():
from inbox.ignition import main_engine
engine = main_engine()
Base = sa.ext.declarative.declarative_base()
Base.metadata.reflect(engine)
if 'easthread' in Base.metadata.tables:
op.create_index('ix_easthread_eas_thrid', 'easthread', ['eas_thrid'],
unique=False, mysql_length=256)
def real_db():
"""A fixture to get access to the real mysql db. We need this
to log in to providers like gmail to check that events changes
are synced back."""
engine = main_engine()
session = InboxSession(engine)
yield session
session.rollback()
session.close()
def upgrade():
from inbox.ignition import main_engine
engine = main_engine(pool_size=1, max_overflow=0)
if not engine.has_table('easdevice'):
return
op.add_column('easdevice',
sa.Column('retired', sa.Boolean(),
server_default=sa.sql.expression.false(),
nullable=False))
def downgrade():
from inbox.models.session import session_scope
from sqlalchemy.ext.declarative import declarative_base
from inbox.ignition import main_engine
engine = main_engine(pool_size=1, max_overflow=0)
op.create_table('aolaccount',
sa.Column('id', sa.Integer(), nullable=False),
sa.ForeignKeyConstraint(['id'], [u'imapaccount.id'],
ondelete='CASCADE'),
sa.Column('password', sa.String(256)),
sa.PrimaryKeyConstraint('id')
)
op.create_table('yahooaccount',
sa.Column('id', sa.Integer(), nullable=False),
sa.ForeignKeyConstraint(['id'], [u'imapaccount.id'],
ondelete='CASCADE'),
sa.Column('password', sa.String(256)),
sa.PrimaryKeyConstraint('id')
)
Base = declarative_base()
Base.metadata.reflect(engine)
class Account(Base):
__table__ = Base.metadata.tables['account']
class ImapAccount(Base):
__table__ = Base.metadata.tables['imapaccount']
class YahooAccount(Base):
__table__ = Base.metadata.tables['yahooaccount']
class AOLAccount(Base):
__table__ = Base.metadata.tables['aolaccount']
class GenericAccount(Base):
__table__ = Base.metadata.tables['genericaccount']
with session_scope(versioned=False) \
as db_session:
for acct in db_session.query(GenericAccount):
secret = db_session.query(Secret) \
.filter_by(id=acct.password_id).one()
if acct.provider == 'yahoo':
new_acct = YahooAccount(namespace=acct.namespace,
password=secret.secret)
db_session.add(new_acct)
elif acct.provider == 'aol':
new_acct = AOLAccount(namespace=acct.namespace,
password=secret.secret)
db_session.add(new_acct)
db_session.commit()
engine.execute('drop table genericaccount')
def upgrade():
from sqlalchemy.ext.declarative import declarative_base
from inbox.models.session import session_scope
from inbox.ignition import main_engine
engine = main_engine(pool_size=1, max_overflow=0)
op.add_column('message',
sa.Column('is_created', sa.Boolean,
server_default=sa.sql.expression.false(),
nullable=False))
op.add_column('message',
sa.Column('is_sent', sa.Boolean,
server_default=sa.sql.expression.false(),
nullable=False))
op.add_column('message',
sa.Column('state',
sa.Enum('draft', 'sending', 'sending failed',
'sent')))
op.add_column('message',
sa.Column('is_reply', sa.Boolean()))
op.add_column('message',
sa.Column('resolved_message_id', sa.Integer(),
nullable=True))
op.create_foreign_key('message_ibfk_2',
'message', 'message',
['resolved_message_id'], ['id'])
op.add_column('message',
sa.Column('parent_draft_id', sa.Integer(), nullable=True))
op.create_foreign_key('message_ibfk_3',
'message', 'message',
['parent_draft_id'], ['id'])
Base = declarative_base()
Base.metadata.reflect(engine)
class Message(Base):
__table__ = Base.metadata.tables['message']
class SpoolMessage(Base):
__table__ = Base.metadata.tables['spoolmessage']
with session_scope(versioned=False, ignore_soft_deletes=False) \
as db_session:
for sm in db_session.query(SpoolMessage).yield_per(250):
m = db_session.query(Message).get(sm.id)
m.is_sent = sm.is_sent
m.state = sm.state
m.is_reply = sm.is_reply
m.resolved_message_id = sm.resolved_message_id
m.parent_draft_id = sm.parent_draft_id
db_session.commit()
op.drop_table('spoolmessage')
def upgrade():
from inbox.ignition import main_engine
engine = main_engine(pool_size=1, max_overflow=0)
inspector = sa.inspect(engine)
if 'default_calendar_ibfk_1' in [k['name'] for k in
inspector.get_foreign_keys('account')]:
op.drop_constraint('default_calendar_ibfk_1', 'account',
type_='foreignkey')
op.create_foreign_key('account_ibfk_10', 'account', 'calendar',
['default_calendar_id'], ['id'])
def downgrade():
from inbox.ignition import main_engine
engine = main_engine(pool_size=1, max_overflow=0)
Base = declarative_base()
Base.metadata.reflect(engine)
op.drop_column('foldersync', '_sync_status')
if 'easfoldersync' in Base.metadata.tables:
op.drop_column('easfoldersync', '_sync_status')
def session_scope(versioned=True, ignore_soft_deletes=False):
""" Provide a transactional scope around a series of operations.
Takes care of rolling back failed transactions and closing the session
when it goes out of scope.
Note that sqlalchemy automatically starts a new database transaction when
the session is created, and restarts a new transaction after every commit()
on the session. Your database backend's transaction semantics are important
here when reasoning about concurrency.
Parameters
----------
versioned : bool
Do you want to enable the transaction log?
Yields
------
Session
The created session.
"""
global cached_engine
if cached_engine is None:
cached_engine = main_engine()
log.info("Don't yet have engine... creating default from ignition",
engine=id(cached_engine))
session = new_session(cached_engine, versioned)
try:
if config.get('LOG_DB_SESSIONS'):
start_time = time.time()
calling_frame = sys._getframe().f_back.f_back
call_loc = '{}:{}'.format(calling_frame.f_globals.get('__name__'),
calling_frame.f_lineno)
logger = log.bind(engine_id=id(cached_engine),
session_id=id(session), call_loc=call_loc)
logger.info('creating db_session',
sessions_used=cached_engine.pool.checkedout())
yield session
session.commit()
except BaseException as exc:
try:
session.rollback()
raise
except OperationalError:
log.warn('Encountered OperationalError on rollback',
original_exception=type(exc))
raise exc
finally:
if config.get('LOG_DB_SESSIONS'):
lifetime = time.time() - start_time
logger.info('closing db_session', lifetime=lifetime,
sessions_used=cached_engine.pool.checkedout())
session.close()
def upgrade():
from inbox.ignition import main_engine
engine = main_engine(pool_size=1, max_overflow=0)
if not engine.has_table("easdevice"):
return
op.add_column(
"easdevice",
sa.Column(
"retired",
sa.Boolean(),
server_default=sa.sql.expression.false(),
nullable=False,
),
)
def upgrade():
from inbox.ignition import main_engine
engine = main_engine(pool_size=1, max_overflow=0)
if not engine.has_table("easfoldersyncstatus"):
return
op.add_column(
"easfoldersyncstatus",
sa.Column(
"sync_should_run",
sa.Boolean(),
server_default=sa.sql.expression.true(),
nullable=False,
),
)
def upgrade():
from inbox.ignition import main_engine
engine = main_engine(pool_size=1, max_overflow=0)
from inbox.sqlalchemy_ext.util import JSON, MutableDict
Base = declarative_base()
Base.metadata.reflect(engine)
op.add_column('foldersync',
sa.Column('_sync_status', MutableDict.as_mutable(JSON()),
nullable=True))
if 'easfoldersync' in Base.metadata.tables:
op.add_column('easfoldersync',
sa.Column('_sync_status', MutableDict.as_mutable(JSON()),
nullable=True))
def upgrade():
conn = op.get_bind()
conn.execute(text("set @@foreign_key_checks = 0;"))
op.drop_constraint(u'namespace_id', 'category', type_='unique')
op.create_unique_constraint(u'namespace_id', 'category',
['namespace_id', 'name', 'display_name'])
from inbox.ignition import main_engine
engine = main_engine(pool_size=1, max_overflow=0)
if not engine.has_table('easfoldersyncstatus'):
return
op.drop_constraint(u'account_id_2', 'easfoldersyncstatus', type_='unique')
op.create_unique_constraint(u'account_id_2', 'easfoldersyncstatus',
['account_id', 'device_id', 'eas_folder_id'])
conn.execute(text("set @@foreign_key_checks = 1;"))
def upgrade():
from inbox.ignition import main_engine
engine = main_engine()
Base = sa.ext.declarative.declarative_base()
Base.metadata.reflect(engine)
if "easthread" in Base.metadata.tables:
op.create_index(
"ix_easthread_eas_thrid",
"easthread",
["eas_thrid"],
unique=False,
mysql_length=256,
)
def upgrade():
from inbox.models.session import session_scope
from inbox.ignition import main_engine
engine = main_engine(pool_size=1, max_overflow=0)
Base = declarative_base()
Base.metadata.reflect(engine)
class Folder(Base):
__table__ = Base.metadata.tables['folder']
with session_scope(versioned=False) as db_session:
for folder in db_session.query(Folder).filter(Folder.name == 'Inbox'):
folder.public_id = 'inbox'
folder.exposed_name = 'inbox'
db_session.commit()
def upgrade():
from inbox.ignition import main_engine
engine = main_engine(pool_size=1, max_overflow=0)
if not engine.has_table("easuid"):
return
conn = op.get_bind()
conn.execute("""ALTER TABLE easuid
ADD COLUMN server_id VARCHAR(64) DEFAULT NULL,
ADD COLUMN easfoldersyncstatus_id INT(11) DEFAULT NULL,
ADD INDEX easfoldersyncstatus_id (easfoldersyncstatus_id),
ADD CONSTRAINT easuid_ibfk_4 FOREIGN KEY (easfoldersyncstatus_id)
REFERENCES easfoldersyncstatus (id) ON DELETE CASCADE,
ADD INDEX ix_easuid_server_id (server_id)
""")
def upgrade():
from inbox.ignition import main_engine
engine = main_engine(pool_size=1, max_overflow=0)
Base = declarative_base()
Base.metadata.reflect(engine)
if 'easuid' in Base.metadata.tables:
op.create_index('ix_easuid_msg_uid',
'easuid', ['msg_uid'],
unique=False)
op.create_index('ix_imapuid_msg_uid', 'imapuid', ['msg_uid'], unique=False)
op.create_index('ix_transaction_table_name',
'transaction', ['table_name'],
unique=False)
def upgrade():
op.add_column('transaction',
sa.Column('public_id', mysql.BINARY(16), nullable=True))
op.add_column(
'transaction',
sa.Column('object_public_id', sa.String(length=191), nullable=True))
op.create_index('ix_transaction_public_id',
'transaction', ['public_id'],
unique=False)
from inbox.sqlalchemy_ext.util import generate_public_id, b36_to_bin
# TODO(emfree) reflect
from inbox.models.session import session_scope
from inbox.ignition import main_engine
engine = main_engine(pool_size=1, max_overflow=0)
Base = declarative_base()
Base.metadata.reflect(engine)
class Transaction(Base):
__table__ = Base.metadata.tables['transaction']
with session_scope(versioned=False) as db_session:
count = 0
num_transactions, = db_session.query(sa.func.max(Transaction.id)).one()
print 'Adding public ids to {} transactions'.format(num_transactions)
for pointer in range(0, num_transactions + 1, 500):
for entry in db_session.query(Transaction).filter(
Transaction.id >= pointer, Transaction.id < pointer + 500):
entry.public_id = b36_to_bin(generate_public_id())
count += 1
if not count % 500:
sys.stdout.write('.')
sys.stdout.flush()
db_session.commit()
garbage_collect()
op.alter_column('transaction',
'public_id',
existing_type=mysql.BINARY(16),
nullable=False)
op.add_column(
'transaction',
sa.Column('public_snapshot', sa.Text(length=4194304), nullable=True))
op.add_column(
'transaction',
sa.Column('private_snapshot', sa.Text(length=4194304), nullable=True))
op.drop_column('transaction', u'additional_data')
def run_migrations_offline():
"""Run migrations in 'offline' mode.
This configures the context with just a URL
and not an Engine, though an Engine is acceptable
here as well. By skipping the Engine creation
we don't even need a DBAPI to be available.
Calls to context.execute() here emit the given string to the
script output.
"""
context.configure(engine=main_engine(pool_size=1, max_overflow=0))
with context.begin_transaction():
context.run_migrations()
def downgrade():
conn = op.get_bind()
conn.execute(text("set @@lock_wait_timeout = 20;"))
from inbox.ignition import main_engine
engine = main_engine(pool_size=1, max_overflow=0)
Base = declarative_base()
Base.metadata.reflect(engine)
if "easfoldersyncstatus" in Base.metadata.tables:
conn.execute(
text(
"ALTER TABLE easfoldersyncstatus MODIFY eas_folder_id VARCHAR(64),"
" MODIFY eas_parent_id VARCHAR(64)"
))
def upgrade():
from inbox.models.session import session_scope
from inbox.ignition import main_engine
engine = main_engine(pool_size=1, max_overflow=0)
Base = declarative_base()
Base.metadata.reflect(engine)
class Contact_Old(Base):
__table__ = Base.metadata.tables['contact']
# Delete the "remote" contacts. This is just a server cache for comparing
# any changes, now handled by the previous "local" contacts
with session_scope() as db_session:
db_session.query(Contact_Old).filter_by(source='remote').delete()
op.drop_column('contact', 'source')
def downgrade_imapaccount():
from inbox.models.session import session_scope
from inbox.ignition import main_engine
engine = main_engine(pool_size=1, max_overflow=0)
Base = declarative_base()
Base.metadata.reflect(engine)
class ImapAccount_(Base):
__table__ = Base.metadata.tables['imapaccount']
# Get data from table-to-be-dropped
with session_scope() as db_session:
results = db_session.query(ImapAccount_.id,
ImapAccount_.imap_host).all()
to_insert = [dict(id=r[0], imap_host=r[1]) for r in results]
# Drop columns, add new columns + insert data
op.drop_column('account', 'type')
op.add_column('account', sa.Column('imap_host', sa.String(512)))
table_ = table('account', column('imap_host', sa.String(512)),
column('id', sa.Integer))
for r in to_insert:
op.execute(table_.update().where(table_.c.id == r['id']).values(
{'imap_host': r['imap_host']}))
# Table switch-over
op.drop_constraint('imapuid_ibfk_1', 'imapuid', type_='foreignkey')
op.drop_constraint('uidvalidity_ibfk_1', 'uidvalidity', type_='foreignkey')
op.drop_constraint('foldersync_ibfk_1', 'foldersync', type_='foreignkey')
op.drop_table('imapaccount')
op.rename_table('account', 'imapaccount')
op.create_foreign_key('imapuid_ibfk_1',
'imapuid',
'imapaccount', ['imapaccount_id'], ['id'],
ondelete='CASCADE')
op.create_foreign_key('uidvalidity_ibfk_1',
'uidvalidity',
'imapaccount', ['imapaccount_id'], ['id'],
ondelete='CASCADE')
op.create_foreign_key('foldersync_ibfk_1',
'foldersync',
'imapaccount', ['account_id'], ['id'],
ondelete='CASCADE')
def upgrade():
from inbox.ignition import main_engine
engine = main_engine(pool_size=1, max_overflow=0)
# Do nothing if the affected table isn't present.
if not engine.has_table("easaccount"):
return
# Do not define foreign key constraint here; that's done for all account
# tables in the next migration.
op.add_column(
"easaccount",
sa.Column("password_id", sa.Integer(), sa.ForeignKey("secret.id")))
Base = sa.ext.declarative.declarative_base()
Base.metadata.reflect(engine)
from inbox.models.session import session_scope
class EASAccount(Base):
__table__ = Base.metadata.tables["easaccount"]
secret = sa.orm.relationship(
"Secret", primaryjoin="EASAccount.password_id == Secret.id")
class Secret(Base):
__table__ = Base.metadata.tables["secret"]
with session_scope(versioned=False) as db_session:
accounts = db_session.query(EASAccount).all()
print "# EAS accounts: ", len(accounts)
for account in accounts:
secret = Secret()
# Need to set non-nullable attributes.
secret.created_at = datetime.utcnow()
secret.updated_at = datetime.utcnow()
secret.type = 0
secret.acl_id = 0
secret.secret = account.password
account.secret = secret
db_session.commit()
op.alter_column("easaccount",
"password_id",
existing_type=sa.Integer(),
nullable=False)
def upgrade():
from inbox.ignition import main_engine
from inbox.models.session import session_scope
engine = main_engine(pool_size=1, max_overflow=0)
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
Base.metadata.reflect(engine)
class Message(Base):
__table__ = Base.metadata.tables["message"]
class Thread(Base):
__table__ = Base.metadata.tables["thread"]
with session_scope(versioned=False) as db_session:
count = 0
for msg in (db_session.query(Message).options(
sa.orm.load_only("subject")).yield_per(500)):
truncate_subject(msg)
count += 1
if count > 500:
db_session.commit()
count = 0
db_session.commit()
for thread in (db_session.query(Thread).options(
sa.orm.load_only("subject")).yield_per(500)):
truncate_subject(thread)
count += 1
if count > 500:
db_session.commit()
count = 0
db_session.commit()
op.alter_column("message",
"subject",
type_=sa.String(255),
existing_nullable=True)
op.alter_column("thread",
"subject",
type_=sa.String(255),
existing_nullable=True)
def upgrade():
from inbox.ignition import main_engine
engine = main_engine(pool_size=1, max_overflow=0)
from inbox.models.session import session_scope
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
Base.metadata.reflect(engine)
class Message(Base):
__table__ = Base.metadata.tables['message']
with session_scope(versioned=False) \
as db_session:
null_field_count = db_session.query(func.count(Message.id)). \
filter(or_(Message.from_addr.is_(None),
Message.to_addr.is_(None),
Message.cc_addr.is_(None),
Message.bcc_addr.is_(None))).scalar()
print 'messages to migrate:', null_field_count
if int(null_field_count):
for message in db_session.query(Message):
for attr in ('to_addr', 'from_addr', 'cc_addr', 'bcc_addr'):
if getattr(message, attr) is None:
setattr(message, attr, [])
print '.',
db_session.commit()
print 'making addrs non-nullable'
op.alter_column('message',
'bcc_addr',
existing_type=mysql.TEXT(),
nullable=False)
op.alter_column('message',
'cc_addr',
existing_type=mysql.TEXT(),
nullable=False)
op.alter_column('message',
'from_addr',
existing_type=mysql.TEXT(),
nullable=False)
op.alter_column('message',
'to_addr',
existing_type=mysql.TEXT(),
nullable=False)
def migrate_messages(account_id):
from inbox.ignition import main_engine
from inbox.models import Message, Namespace
from inbox.models.session import session_scope
engine = main_engine(pool_size=1, max_overflow=0)
with session_scope(versioned=False) as db_session:
namespace = db_session.query(Namespace).filter_by(
account_id=account_id).one()
offset = 0
while True:
if engine.has_table("easuid"):
additional_options = [subqueryload(Message.easuids)]
else:
additional_options = []
messages = (db_session.query(Message).filter(
Message.namespace_id == namespace.id).options(
load_only(
Message.id,
Message.is_read,
Message.is_starred,
Message.is_draft,
),
joinedload(Message.namespace).load_only("id"),
subqueryload(Message.imapuids),
subqueryload(Message.messagecategories),
*additional_options).with_hint(
Message,
"USE INDEX (ix_message_namespace_id)").order_by(
asc(Message.id)).limit(1000).offset(offset).all())
if not messages:
return
for message in messages:
try:
message.update_metadata(message.is_draft)
except IndexError:
# Can happen for messages without a folder.
pass
log.info("Updated message",
namespace_id=namespace.id,
message_id=message.id)
db_session.commit()
offset += 1000
def upgrade():
op.add_column(
'message',
sa.Column('is_read',
sa.Boolean(),
server_default=sa.sql.expression.false(),
nullable=False))
op.alter_column('usertagitem',
'created_at',
existing_type=mysql.DATETIME(),
nullable=False)
op.alter_column('usertagitem',
'updated_at',
existing_type=mysql.DATETIME(),
nullable=False)
from inbox.models.session import session_scope
from inbox.ignition import main_engine
engine = main_engine(pool_size=1, max_overflow=0)
Base = declarative_base()
Base.metadata.reflect(engine)
class Message(Base):
__table__ = Base.metadata.tables['message']
class ImapUid(Base):
__table__ = Base.metadata.tables['imapuid']
message = relationship('Message',
backref=backref(
'imapuids',
primaryjoin='and_('
'Message.id == ImapUid.message_id, '
'ImapUid.deleted_at == None)'),
primaryjoin='and_('
'ImapUid.message_id == Message.id,'
'Message.deleted_at == None)')
with session_scope(versioned=False,
ignore_soft_deletes=False) as db_session:
for uid in db_session.query(ImapUid).yield_per(500):
if uid.is_seen:
uid.message.is_read = True
db_session.commit()
def upgrade():
op.alter_column('message',
'state',
type_=sa.Enum('draft', 'sending', 'sending failed', 'sent',
'actions_pending', 'actions_committed'),
existing_type=sa.Enum('draft', 'sending', 'sending failed',
'sent'))
from inbox.ignition import main_engine
engine = main_engine(pool_size=1, max_overflow=0)
if not engine.has_table('easdevice'):
return
op.add_column(
'easdevice',
sa.Column('active',
sa.Boolean(),
server_default=sa.sql.expression.false(),
nullable=False))
def upgrade():
from inbox.ignition import main_engine
engine = main_engine(pool_size=1, max_overflow=0)
Base = declarative_base()
Base.metadata.reflect(engine)
# The model previously didn't reflect the migration, therefore
# only drop the uid constraint if it exists (created with creat_db
# vs a migration).
inspector = sa.inspect(engine)
if "uid" in [c["name"] for c in inspector.get_unique_constraints("event")]:
op.drop_constraint("uid", "event", type_="unique")
op.create_unique_constraint(
"uuid", "event", ["uid", "source", "account_id", "provider_name"]
)
op.alter_column("event", "uid", type_=sa.String(767, collation="ascii_general_ci"))
def upgrade():
from inbox.ignition import main_engine
engine = main_engine(pool_size=1, max_overflow=0)
Base = declarative_base()
Base.metadata.reflect(engine)
# The model previously didn't reflect the migration, therefore
# only drop the uid constraint if it exists (created with creat_db
# vs a migration).
inspector = sa.inspect(engine)
if 'uid' in [c['name'] for c in inspector.get_unique_constraints('event')]:
op.drop_constraint('uid', 'event', type_='unique')
op.create_unique_constraint(
'uuid', 'event', ['uid', 'source', 'account_id', 'provider_name'])
op.alter_column('event',
'uid',
type_=sa.String(767, collation='ascii_general_ci'))
def check_db():
""" Checks the database revision against the known alembic migrations. """
from inbox.ignition import main_engine
inbox_db_engine = main_engine(pool_size=1, max_overflow=0)
# top-level, with setup.sh
alembic_ini_path = os.environ.get("ALEMBIC_INI_PATH",
_absolute_path('../../alembic.ini'))
alembic_cfg = alembic_config(alembic_ini_path)
alembic_basedir = os.path.dirname(alembic_ini_path)
alembic_script_dir = os.path.join(
alembic_basedir, alembic_cfg.get_main_option("script_location"))
assert os.path.isdir(alembic_script_dir), \
'Must have migrations directory at {}'.format(alembic_script_dir)
try:
inbox_db_engine.dialect.has_table(inbox_db_engine, 'alembic_version')
except sqlalchemy.exc.OperationalError:
sys.exit("Databases don't exist! Run bin/create-db")
if inbox_db_engine.dialect.has_table(inbox_db_engine, 'alembic_version'):
res = inbox_db_engine.execute(
'SELECT version_num from alembic_version')
current_revision = [r for r in res][0][0]
assert current_revision, \
'Need current revision in alembic_version table...'
script = ScriptDirectory(alembic_script_dir)
head_revision = script.get_current_head()
log.info('Head database revision: {0}'.format(head_revision))
log.info('Current database revision: {0}'.format(current_revision))
# clean up a ton (8) of idle database connections
del script
gc.collect()
if current_revision != head_revision:
raise Exception(
'Outdated database! Migrate using `alembic upgrade head`')
else:
log.info('[OK] Database scheme matches latest')
else:
raise Exception('Un-stamped database! Run `bin/create-db`. bailing.')
def upgrade():
conn = op.get_bind()
conn.execute(text("set @@foreign_key_checks = 0;"))
op.drop_constraint(u"namespace_id", "category", type_="unique")
op.create_unique_constraint(u"namespace_id", "category",
["namespace_id", "name", "display_name"])
from inbox.ignition import main_engine
engine = main_engine(pool_size=1, max_overflow=0)
if not engine.has_table("easfoldersyncstatus"):
return
op.drop_constraint(u"account_id_2", "easfoldersyncstatus", type_="unique")
op.create_unique_constraint(
u"account_id_2",
"easfoldersyncstatus",
["account_id", "device_id", "eas_folder_id"],
)
conn.execute(text("set @@foreign_key_checks = 1;"))
def downgrade():
from inbox.ignition import main_engine
engine = main_engine(pool_size=1, max_overflow=0)
Base = declarative_base()
Base.metadata.reflect(engine)
if 'easfoldersync' in Base.metadata.tables:
op.alter_column('easfoldersync', 'state',
type_=sa.Enum('initial', 'initial uidinvalid',
'poll', 'poll uidinvalid', 'finish'),
existing_nullable=False)
if 'easuid' in Base.metadata.tables:
op.alter_column('easuid', 'message_id', existing_type=sa.Integer(),
nullable=True)
op.alter_column('easuid', 'fld_uid', existing_type=sa.Integer(),
nullable=True)
op.alter_column('easuid', 'msg_uid', existing_type=sa.Integer(),
nullable=True)
def upgrade():
from inbox.ignition import main_engine
engine = main_engine(pool_size=1, max_overflow=0)
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
Base.metadata.reflect(engine)
if "easfoldersyncstatus" in Base.metadata.tables:
op.drop_constraint("easuid_ibfk_3", "easuid", type_="foreignkey")
op.create_foreign_key(
"easuid_ibfk_3",
"easuid",
"folder",
["folder_id"],
["id"],
ondelete="CASCADE",
)
def upgrade():
from inbox.ignition import main_engine
engine = main_engine()
if not engine.has_table("easaccount"):
return
# We allow nullable=True because we don't have usernames for existing accounts.
# Furthermore, we won't always get a username.
from inbox.models.constants import MAX_INDEXABLE_LENGTH
op.add_column("easaccount",
sa.Column("username", sa.String(255), nullable=True))
op.add_column(
"easaccount",
sa.Column("eas_auth", sa.String(MAX_INDEXABLE_LENGTH), nullable=True),
)
Base = sa.ext.declarative.declarative_base()
Base.metadata.reflect(engine)
from inbox.models.session import session_scope
class EASAccount(Base):
__table__ = Base.metadata.tables["easaccount"]
with session_scope(versioned=False) as db_session:
accts = db_session.query(EASAccount).all()
for a in accts:
a.eas_auth = a.email_address
db_session.add(a)
db_session.commit()
op.alter_column(
"easaccount",
"eas_auth",
nullable=False,
existing_type=sa.String(MAX_INDEXABLE_LENGTH),
)
def upgrade():
from sqlalchemy.ext.declarative import declarative_base
from inbox.ignition import main_engine
from inbox.models.session import session_scope
engine = main_engine(pool_size=1, max_overflow=0)
if not engine.has_table("easuid"):
return
Base = declarative_base()
Base.metadata.reflect(engine)
class EASUid(Base):
__table__ = Base.metadata.tables["easuid"]
class EASFolderSyncStatus(Base):
__table__ = Base.metadata.tables["easfoldersyncstatus"]
with session_scope(versioned=False) as db_session:
max_easuid = db_session.query(sa.func.max(EASUid.id)).scalar()
if max_easuid is None:
return
while True:
results = (
db_session.query(EASUid, EASFolderSyncStatus)
.join(
EASFolderSyncStatus,
sa.and_(
EASUid.fld_uid == EASFolderSyncStatus.eas_folder_id,
EASUid.device_id == EASFolderSyncStatus.device_id,
EASUid.easaccount_id == EASFolderSyncStatus.account_id,
EASUid.easfoldersyncstatus_id.is_(None),
),
)
.limit(1000)
.all()
)
if not results:
return
for easuid, easfoldersyncstatus in results:
easuid.easfoldersyncstatus_id = easfoldersyncstatus.id
db_session.commit()
def upgrade():
from inbox.ignition import main_engine
engine = main_engine(pool_size=1, max_overflow=0)
Base = sa.ext.declarative.declarative_base()
Base.metadata.reflect(engine)
op.drop_constraint("folder_fk1", "folder", type_="foreignkey")
op.drop_constraint("account_id", "folder", type_="unique")
op.create_foreign_key("folder_fk1", "folder", "account", ["account_id"], ["id"])
op.create_unique_constraint(
"account_id", "folder", ["account_id", "name", "canonical_name"]
)
if "easfoldersyncstatus" in Base.metadata.tables:
op.create_unique_constraint(
"account_id_2", "easfoldersyncstatus", ["account_id", "eas_folder_id"]
)
def run_migrations_online():
"""Run migrations in 'online' mode.
In this scenario we need to create an Engine
and associate a connection with the context.
"""
engine = main_engine(pool_size=1, max_overflow=0)
connection = engine.connect()
context.configure(
connection=connection,
target_metadata=target_metadata
)
try:
with context.begin_transaction():
context.run_migrations()
finally:
connection.close()
def upgrade():
from inbox.ignition import main_engine
op.add_column("actionlog", sa.Column("type", sa.String(16)))
# Update action_log entries
from inbox.models import Account, ActionLog, Namespace
from inbox.models.session import session_scope
with session_scope() as db_session:
q = (db_session.query(ActionLog).join(Namespace).join(Account).filter(
ActionLog.status == "pending",
Account.discriminator != "easaccount").options(
contains_eager(ActionLog.namespace, Namespace.account)))
print("Updating {} action_log entries".format(q.count()))
for a in q.all():
a.type = "actionlog"
db_session.commit()
engine = main_engine(pool_size=1, max_overflow=0)
if not engine.has_table("easaccount"):
return
op.create_table(
"easactionlog",
sa.Column("id", sa.Integer()),
sa.Column(
"secondary_status",
sa.Enum("pending", "successful", "failed"),
server_default="pending",
),
sa.Column("secondary_retries",
sa.Integer(),
nullable=False,
server_default="0"),
sa.PrimaryKeyConstraint("id"),
sa.ForeignKeyConstraint(["id"], ["actionlog.id"], ondelete="CASCADE"),
)
