def downgrade():
from inbox.server.config import load_config
load_config()
# These all inherit HasPublicID
from inbox.server.models.tables.base import (
Account, Block, Contact, Message, Namespace,
SharedFolder, Thread, User, UserSession, HasPublicID)
classes = [
Account, Block, Contact, Message, Namespace,
SharedFolder, Thread, User, UserSession]
for c in classes:
assert issubclass(c, HasPublicID)
print '[{0}] Dropping public_id column... '.format(c.__tablename__),
op.drop_column(c.__tablename__, 'public_id')
print 'Dropping index... ',
op.drop_index(
'ix_{0}_public_id'.format(c.__tablename__),
table_name=c.__tablename__)
print 'Done.'
def upgrade():
from inbox.server.config import load_config
load_config()
from inbox.sqlalchemy.util import generate_public_id
from inbox.server.models import session_scope
# These all inherit HasPublicID
from inbox.server.models.tables.base import (
Account, Block, Contact, Message, Namespace,
SharedFolder, Thread, User, UserSession, HasPublicID)
classes = [
Account, Block, Contact, Message, Namespace,
SharedFolder, Thread, User, UserSession]
for c in classes:
assert issubclass(c, HasPublicID)
print '[{0}] adding public_id column... '.format(c.__tablename__),
sys.stdout.flush()
op.add_column(c.__tablename__, sa.Column(
'public_id', mysql.BINARY(16), nullable=False))
print 'adding index... ',
op.create_index(
'ix_{0}_public_id'.format(c.__tablename__),
c.__tablename__,
['public_id'],
unique=False)
print 'Done!'
sys.stdout.flush()
print 'Finished adding columns. \nNow generating public_ids'
with session_scope() as db_session:
count = 0
for c in classes:
garbage_collect()
print '[{0}] Loading rows. '.format(c.__name__),
sys.stdout.flush()
print 'Generating public_ids',
sys.stdout.flush()
for r in db_session.query(c).yield_per(chunk_size):
count += 1
r.public_id = generate_public_id()
if not count % chunk_size:
sys.stdout.write('.')
sys.stdout.flush()
db_session.commit()
garbage_collect()
sys.stdout.write(' Saving. '.format(c.__name__)),
# sys.stdout.flush()
sys.stdout.flush()
db_session.commit()
sys.stdout.write('Done!\n')
sys.stdout.flush()
print '\nUpdgraded OK!\n'
def upgrade():
from inbox.server.config import load_config
load_config()
from inbox.server.models import session_scope
from inbox.server.models.tables.base import Message
with session_scope() as db_session:
results = db_session.query(Message).all()
for message in results:
message.from_addr = [message.from_addr]
message.sender_addr = [message.sender_addr]
db_session.commit()
def upgrade():
from inbox.server.config import load_config
load_config()
from inbox.server.models import session_scope
from inbox.server.models.tables.base import Message
with session_scope() as db_session:
results = db_session.query(Message).all()
for message in results:
message.from_addr = [message.from_addr]
message.sender_addr = [message.sender_addr]
db_session.commit()
from sqlalchemy import create_engine, pool
from logging.config import fileConfig
from inbox.server.config import load_config
# this is the Alembic Config object, which provides
# access to the values within the .ini file in use.
config = context.config
# Interpret the config file for Python logging.
# This line sets up loggers basically.
fileConfig(config.config_file_name)
# Load Inbox server configuration.
load_config()
# add your model's MetaData object here
# for 'autogenerate' support
# from myapp import mymodel
from inbox.server.models import Base, db_uri
target_metadata = Base.metadata
# other values from the config, defined by the needs of env.py,
# can be acquired:
# my_important_option = config.get_main_option("my_important_option")
# ... etc.
def run_migrations_offline():
"""Run migrations in 'offline' mode.
"""
# revision identifiers, used by Alembic.
revision = '1c3f1812f2d9'
down_revision = '482338e7a7d6'
from alembic import op
import sqlalchemy as sa
from sqlalchemy.sql import table, column
from sqlalchemy.ext.declarative import declarative_base
from inbox.server.config import load_config
load_config()
from inbox.server.models import session_scope
from inbox.server.models.ignition import engine
Base = declarative_base()
Base.metadata.reflect(engine)
def upgrade():
genericize_imapaccount()
genericize_thread()
genericize_namespace_contact_foldersync()
def downgrade():
downgrade_imapaccount()
def upgrade():
easupdate = False
print 'Creating new tables and columns...'
op.create_table('folder',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('account_id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(
length=191, collation='utf8mb4_general_ci'),
nullable=True),
sa.ForeignKeyConstraint(['account_id'], ['account.id'],
ondelete='CASCADE'),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('account_id', 'name')
)
op.create_table('internaltag',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('public_id', mysql.BINARY(16), nullable=False),
sa.Column('namespace_id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(length=191), nullable=False),
sa.Column('thread_id', sa.Integer(), nullable=False),
sa.ForeignKeyConstraint(['namespace_id'], ['namespace.id'],
ondelete='CASCADE'),
sa.ForeignKeyConstraint(['thread_id'], ['thread.id'],
ondelete='CASCADE'),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('namespace_id', 'name')
)
op.add_column('folderitem',
sa.Column('folder_id', sa.Integer(), nullable=True))
op.create_foreign_key("fk_folder_id", "folderitem",
"folder", ["folder_id"], ["id"],
ondelete='CASCADE')
op.add_column('account', sa.Column('inbox_folder_id',
sa.Integer, nullable=True))
op.add_column('account', sa.Column('sent_folder_id',
sa.Integer, nullable=True))
op.add_column('account', sa.Column('drafts_folder_id',
sa.Integer, nullable=True))
op.add_column('account', sa.Column('spam_folder_id',
sa.Integer, nullable=True))
op.add_column('account', sa.Column('trash_folder_id',
sa.Integer, nullable=True))
op.add_column('account', sa.Column('archive_folder_id',
sa.Integer, nullable=True))
op.add_column('account', sa.Column('all_folder_id',
sa.Integer, nullable=True))
op.add_column('account', sa.Column('starred_folder_id',
sa.Integer, nullable=True))
op.create_foreign_key('account_ibfk_2', 'account', 'folder',
['inbox_folder_id'], ['id'])
op.create_foreign_key('account_ibfk_3', 'account', 'folder',
['sent_folder_id'], ['id'])
op.create_foreign_key('account_ibfk_4', 'account', 'folder',
['drafts_folder_id'], ['id'])
op.create_foreign_key('account_ibfk_5', 'account', 'folder',
['spam_folder_id'], ['id'])
op.create_foreign_key('account_ibfk_6', 'account', 'folder',
['trash_folder_id'], ['id'])
op.create_foreign_key('account_ibfk_7', 'account', 'folder',
['archive_folder_id'], ['id'])
op.create_foreign_key('account_ibfk_8', 'account', 'folder',
['all_folder_id'], ['id'])
op.create_foreign_key('account_ibfk_9', 'account', 'folder',
['starred_folder_id'], ['id'])
op.add_column('imapuid', sa.Column('folder_id', sa.Integer, nullable=True))
op.create_foreign_key('imapuid_ibfk_3', 'imapuid', 'folder',
['folder_id'], ['id'])
from inbox.server.config import load_config
load_config()
from inbox.server.models import session_scope
from inbox.server.models.ignition import engine
Base = declarative_base()
Base.metadata.reflect(engine)
if 'easuid' in Base.metadata.tables:
easupdate = True
print 'Adding new EASUid columns...'
op.add_column('easuid',
sa.Column('fld_uid', sa.Integer(), nullable=True))
op.add_column('easuid',
sa.Column('folder_id', sa.Integer(), nullable=True))
op.create_foreign_key('easuid_ibfk_3', 'easuid', 'folder',
['folder_id'], ['id'])
op.create_unique_constraint(
'uq_easuid_folder_id_msg_uid_easaccount_id',
'easuid',
['folder_id', 'msg_uid', 'easaccount_id'])
op.create_index('easuid_easaccount_id_folder_id', 'easuid',
['easaccount_id', 'folder_id'])
# Include our changes to the EASUid table:
Base = declarative_base()
Base.metadata.reflect(engine)
class Folder(Base):
__table__ = Base.metadata.tables['folder']
account = relationship('Account', foreign_keys='Folder.account_id',
backref='folders')
class FolderItem(Base):
__table__ = Base.metadata.tables['folderitem']
folder = relationship('Folder', backref='threads', lazy='joined')
class Thread(Base):
__table__ = Base.metadata.tables['thread']
folderitems = relationship('FolderItem', backref="thread",
single_parent=True,
cascade='all, delete, delete-orphan')
namespace = relationship('Namespace', backref='threads')
class Namespace(Base):
__table__ = Base.metadata.tables['namespace']
account = relationship('Account',
backref=backref('namespace', uselist=False))
class Account(Base):
__table__ = Base.metadata.tables['account']
inbox_folder = relationship('Folder',
foreign_keys='Account.inbox_folder_id')
sent_folder = relationship('Folder',
foreign_keys='Account.sent_folder_id')
drafts_folder = relationship('Folder',
foreign_keys='Account.drafts_folder_id')
spam_folder = relationship('Folder',
foreign_keys='Account.spam_folder_id')
trash_folder = relationship('Folder',
foreign_keys='Account.trash_folder_id')
starred_folder = relationship('Folder',
foreign_keys='Account.starred_folder_id')
archive_folder = relationship('Folder',
foreign_keys='Account.archive_folder_id')
all_folder = relationship('Folder',
foreign_keys='Account.all_folder_id')
class ImapUid(Base):
__table__ = Base.metadata.tables['imapuid']
folder = relationship('Folder', backref='imapuids', lazy='joined')
if easupdate:
class EASUid(Base):
__table__ = Base.metadata.tables['easuid']
folder = relationship('Folder', foreign_keys='EASUid.folder_id',
backref='easuids', lazy='joined')
print 'Creating Folder rows and migrating FolderItems...'
# not many folders per account, so shouldn't grow that big
with session_scope(versioned=False, ignore_soft_deletes=False) as db_session:
folders = dict([((i.account_id, i.name), i)
for i in db_session.query(Folder).all()])
count = 0
for folderitem in db_session.query(FolderItem).join(Thread).join(
Namespace).yield_per(CHUNK_SIZE):
account_id = folderitem.thread.namespace.account_id
if folderitem.thread.namespace.account.provider == 'Gmail':
if folderitem.folder_name in folder_name_subst_map:
new_folder_name = folder_name_subst_map[
folderitem.folder_name]
else:
new_folder_name = folderitem.folder_name
elif folderitem.thread.namespace.account.provider == 'EAS':
new_folder_name = folderitem.folder_name.title()
if (account_id, new_folder_name) in folders:
f = folders[(account_id, new_folder_name)]
else:
f = Folder(account_id=account_id,
name=new_folder_name)
folders[(account_id, new_folder_name)] = f
folderitem.folder = f
count += 1
if count > CHUNK_SIZE:
db_session.commit()
count = 0
db_session.commit()
print 'Migrating ImapUids to reference Folder rows...'
for imapuid in db_session.query(ImapUid).yield_per(CHUNK_SIZE):
account_id = imapuid.imapaccount_id
if imapuid.folder_name in folder_name_subst_map:
new_folder_name = folder_name_subst_map[imapuid.folder_name]
else:
new_folder_name = imapuid.folder_name
if (account_id, new_folder_name) in folders:
f = folders[(account_id, new_folder_name)]
else:
f = Folder(account_id=account_id,
name=new_folder_name)
folders[(account_id, new_folder_name)] = f
imapuid.folder = f
count += 1
if count > CHUNK_SIZE:
db_session.commit()
count = 0
db_session.commit()
if easupdate:
print 'Migrating EASUids to reference Folder rows...'
for easuid in db_session.query(EASUid).yield_per(CHUNK_SIZE):
account_id = easuid.easaccount_id
new_folder_name = easuid.folder_name
if (account_id, new_folder_name) in folders:
f = folders[(account_id, new_folder_name)]
else:
f = Folder(account_id=account_id,
name=new_folder_name)
folders[(account_id, new_folder_name)] = f
easuid.folder = f
count += 1
if count > CHUNK_SIZE:
db_session.commit()
count = 0
db_session.commit()
print 'Migrating *_folder_name fields to reference Folder rows...'
for account in db_session.query(Account).filter_by(provider='Gmail'):
if account.inbox_folder_name:
# hard replace INBOX with canonicalized caps
k = (account.id, 'Inbox')
if k in folders:
account.inbox_folder = folders[k]
else:
account.inbox_folder = Folder(
account_id=account.id,
name=folder_name_subst_map[account.inbox_folder_name])
if account.sent_folder_name:
k = (account.id, account.sent_folder_name)
if k in folders:
account.sent_folder = folders[k]
else:
account.sent_folder = Folder(
account_id=account.id,
name=account.sent_folder_name)
if account.drafts_folder_name:
k = (account.id, account.drafts_folder_name)
if k in folders:
account.drafts_folder = folders[k]
else:
account.drafts_folder = Folder(
account_id=account.id,
name=account.drafts_folder_name)
# all/archive mismatch is intentional; semantics have changed
if account.archive_folder_name:
k = (account.id, account.archive_folder_name)
if k in folders:
account.all_folder = folders[k]
else:
account.all_folder = Folder(
account_id=account.id,
name=account.archive_folder_name)
db_session.commit()
if easupdate:
print "Migrating EAS accounts' *_folder_name fields to reference "\
"Folder rows..."
for account in db_session.query(Account).filter_by(provider='EAS'):
if account.inbox_folder_name:
k = (account.id, account.inbox_folder_name)
if k in folders:
account.inbox_folder = folders[k]
else:
account.inbox_folder = Folder(
account_id=account.id,
name=account.inbox_folder_name)
if account.sent_folder_name:
k = (account.id, account.sent_folder_name)
if k in folders:
account.sent_folder = folders[k]
else:
account.sent_folder = Folder(
account_id=account.id,
name=account.sent_folder_name)
if account.drafts_folder_name:
k = (account.id, account.drafts_folder_name)
if k in folders:
account.drafts_folder = folders[k]
else:
account.drafts_folder = Folder(
account_id=account.id,
name=account.drafts_folder_name)
if account.archive_folder_name:
k = (account.id, account.archive_folder_name)
if k in folders:
account.archive_folder = folders[k]
else:
account.archive_folder = Folder(
account_id=account.id,
name=account.archive_folder_name)
db_session.commit()
print 'Final schema tweaks and new constraint enforcement'
op.alter_column('folderitem', 'folder_id', existing_type=sa.Integer(),
nullable=False)
op.drop_constraint('folder_name', 'folderitem', type_='unique')
op.drop_constraint('folder_name', 'imapuid', type_='unique')
op.create_unique_constraint('uq_imapuid_folder_id_msg_uid_imapaccount_id',
'imapuid',
['folder_id', 'msg_uid', 'imapaccount_id'])
op.drop_column('folderitem', 'folder_name')
op.drop_column('imapuid', 'folder_name')
op.drop_column('account', 'inbox_folder_name')
op.drop_column('account', 'drafts_folder_name')
op.drop_column('account', 'sent_folder_name')
op.drop_column('account', 'archive_folder_name')
if easupdate:
print 'Dropping old EASUid columns...'
op.drop_constraint('folder_name', 'easuid', type_='unique')
op.drop_index('easuid_easaccount_id_folder_name', 'easuid')
op.drop_column('easuid', 'folder_name')
def config():
from inbox.server.config import load_config, config
load_config(filename=TEST_CONFIG)
return config
def upgrade():
from inbox.server.config import load_config
load_config()
from inbox.server.models import session_scope, Session
from inbox.server.models.ignition import engine
from inbox.server.models.tables.base import (Part, Namespace, Message,
Thread)
from inbox.sqlalchemy.util import JSON
print 'Creating table for parts...'
op.create_table(
'part', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('message_id', sa.Integer(), nullable=True),
sa.Column('walk_index', sa.Integer(), nullable=True),
sa.Column('content_disposition',
sa.Enum('inline', 'attachment'),
nullable=True),
sa.Column('content_id', sa.String(length=255), nullable=True),
sa.Column('misc_keyval', JSON(), nullable=True),
sa.Column('is_inboxapp_attachment',
sa.Boolean(),
server_default=sa.sql.expression.false(),
nullable=True),
sa.ForeignKeyConstraint(['id'], ['block.id'], ondelete='CASCADE'),
sa.ForeignKeyConstraint(['message_id'], ['message.id'],
ondelete='CASCADE'),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('message_id', 'walk_index'))
print 'Reflecting old block table schema'
Base = declarative_base()
Base.metadata.reflect(engine)
class Block_(Base): # old schema, reflected from database table
__table__ = Base.metadata.tables['block']
print 'Adding namespace_id column to blocks ',
op.add_column(u'block',
sa.Column('namespace_id', sa.Integer(), nullable=False))
print 'Migrating from blocks to parts'
new_parts = []
with session_scope() as db_session:
for block in db_session.query(Block_).yield_per(chunk_size):
# Move relevant fields
p = Part()
p.size = block.size
p.data_sha256 = block.data_sha256
p.message_id = block.message_id
p.walk_index = block.walk_index
p.content_disposition = block.content_disposition
p.content_id = block.content_id
p.misc_keyval = block.misc_keyval
p.is_inboxapp_attachment
old_namespace = db_session.query(Namespace) \
.join(Message.thread, Thread.namespace) \
.filter(Message.id == block.message_id).one()
p.namespace_id = old_namespace.id
# Commit after column modifications
new_parts.append(p)
print 'Deleting old blocks (now parts)... ',
db_session.query(Block_).delete()
db_session.commit()
print 'Done!'
print 'Removing `message_id` constraint from block'
op.drop_constraint('block_ibfk_1', 'block', type_='foreignkey')
print 'Creating foreign key for block -> namespace on block'
op.create_foreign_key('block_ibfk_1',
'block',
'namespace', ['namespace_id'], ['id'],
ondelete='CASCADE')
print 'Dropping old block columns which are now in part'
op.drop_column(u'block', u'walk_index')
op.drop_column(u'block', u'content_disposition')
op.drop_column(u'block', u'misc_keyval')
op.drop_column(u'block', u'content_id')
op.drop_column(u'block', u'is_inboxapp_attachment')
op.drop_constraint(u'message_id', 'block', type_='unique')
op.drop_column(u'block', u'message_id')
# Note: here we use the regular database session, since the transaction
# log requires the `namespace` property on objects. We've set the
# `namespace_id` foreign key, but need to commit the object before the
# SQLalchemy reference is valid
no_tx_session = Session(autoflush=True, autocommit=False)
no_tx_session.add_all(new_parts)
no_tx_session.commit()
print 'Done migration blocks to parts!'
from logging.config import fileConfig
from inbox.server.config import load_config
# this is the Alembic Config object, which provides
# access to the values within the .ini file in use.
config = context.config
# Interpret the config file for Python logging.
# This line sets up loggers basically.
fileConfig(config.config_file_name)
# If alembic was invoked with --tag=test, load the test Inbox config. Otherwise
# load the default config.
if context.get_tag_argument() == 'test':
load_config('tests/config.cfg')
else:
load_config()
from inbox.server.models.tables.base import register_backends
table_mod_for = register_backends()
# add your model's MetaData object here
# for 'autogenerate' support
# from myapp import mymodel
from inbox.server.models import Base
target_metadata = Base.metadata
from inbox.server.models.ignition import db_uri
# other values from the config, defined by the needs of env.py,
def upgrade():
from inbox.server.config import load_config
load_config()
from inbox.server.models import session_scope
from inbox.server.models.ignition import engine
from inbox.server.models.tables.imap import ImapAccount
import inbox.server.auth.gmail as gmail
# Assert we have the dump file
if not os.path.isfile(SQL_DUMP_FILENAME):
print "Can't find old user SQL dump at {0}...\nMigration no users."\
.format(SQL_DUMP_FILENAME)
return
# Imports to `imapaccount_old` table
with open(SQL_DUMP_FILENAME, 'r') as f:
print 'Importing old account data...',
op.execute(f.read())
print 'OK!'
Base = declarative_base()
Base.metadata.reflect(engine)
class ImapAccount_Old(Base):
__table__ = Base.metadata.tables['imapaccount_old']
with session_scope() as db_session:
migrated_accounts = []
for acct in db_session.query(ImapAccount_Old):
print 'Importing {0}'.format(acct.email_address)
existing_account = db_session.query(ImapAccount)\
.filter_by(email_address=acct.email_address)
if existing_account.count() > 0:
print 'Already have account for {0}'.format(acct.email_address)
continue
# Create a mock OAuth response using data from the old table
mock_response = dict(email=acct.email_address,
issued_to=acct.o_token_issued_to,
user_id=acct.o_user_id,
access_token=acct.o_access_token,
id_token=acct.o_id_token,
expires_in=acct.o_expires_in,
access_type=acct.o_access_type,
token_type=acct.o_token_type,
audience=acct.o_audience,
scope=acct.o_scope,
refresh_token=acct.o_refresh_token,
verified_email=acct.o_verified_email)
new_account = gmail.create_account(db_session, acct.email_address,
mock_response)
# Note that this doesn't verify **anything** about the account.
# We're just doing the migration now
db_session.add(new_account)
db_session.commit()
migrated_accounts.append(new_account)
print '\nDone! Imported {0} accounts.'.format(len(migrated_accounts))
print '\nNow verifying refresh tokens...\n'
verified_accounts = []
for acct in migrated_accounts:
try:
print 'Verifying {0}... '.format(acct.email_address),
gmail.verify_account(db_session, acct)
verified_accounts.append(acct)
print 'OK!'
except Exception, e:
print 'FAILED!', e
print 'Done! Verified {0} of {1}'.format(len(verified_accounts),
len(migrated_accounts))
def upgrade():
from inbox.server.config import load_config
load_config()
from inbox.server.models import session_scope
from inbox.server.models.ignition import engine
from inbox.server.models.tables.imap import ImapAccount
import inbox.server.auth.gmail as gmail
# Assert we have the dump file
if not os.path.isfile(SQL_DUMP_FILENAME):
print "Can't find old user SQL dump at {0}...\nMigration no users."\
.format(SQL_DUMP_FILENAME)
return
# Imports to `imapaccount_old` table
with open(SQL_DUMP_FILENAME, 'r') as f:
print 'Importing old account data...',
op.execute(f.read())
print 'OK!'
Base = declarative_base()
Base.metadata.reflect(engine)
class ImapAccount_Old(Base):
__table__ = Base.metadata.tables['imapaccount_old']
with session_scope() as db_session:
migrated_accounts = []
for acct in db_session.query(ImapAccount_Old):
print 'Importing {0}'. format(acct.email_address)
existing_account = db_session.query(ImapAccount)\
.filter_by(email_address=acct.email_address)
if existing_account.count() > 0:
print 'Already have account for {0}'. format(acct.email_address)
continue
# Create a mock OAuth response using data from the old table
mock_response = dict(
email=acct.email_address,
issued_to=acct.o_token_issued_to,
user_id=acct.o_user_id,
access_token=acct.o_access_token,
id_token=acct.o_id_token,
expires_in=acct.o_expires_in,
access_type=acct.o_access_type,
token_type=acct.o_token_type,
audience=acct.o_audience,
scope=acct.o_scope,
refresh_token=acct.o_refresh_token,
verified_email=acct.o_verified_email
)
new_account = gmail.create_account(db_session, acct.email_address, mock_response)
# Note that this doesn't verify **anything** about the account.
# We're just doing the migration now
db_session.add(new_account)
db_session.commit()
migrated_accounts.append(new_account)
print '\nDone! Imported {0} accounts.'.format(len(migrated_accounts))
print '\nNow verifying refresh tokens...\n'
verified_accounts = []
for acct in migrated_accounts:
try:
print 'Verifying {0}... '.format(acct.email_address),
gmail.verify_account(db_session, acct)
verified_accounts.append(acct)
print 'OK!'
except Exception, e:
print 'FAILED!', e
print 'Done! Verified {0} of {1}'.format(len(verified_accounts), len(migrated_accounts))
def upgrade():
easupdate = False
print 'Creating new tables and columns...'
op.create_table(
'folder', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('account_id', sa.Integer(), nullable=False),
sa.Column('name',
sa.String(length=191, collation='utf8mb4_general_ci'),
nullable=True),
sa.ForeignKeyConstraint(['account_id'], ['account.id'],
ondelete='CASCADE'),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('account_id', 'name'))
op.create_table(
'internaltag', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('public_id', mysql.BINARY(16), nullable=False),
sa.Column('namespace_id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(length=191), nullable=False),
sa.Column('thread_id', sa.Integer(), nullable=False),
sa.ForeignKeyConstraint(['namespace_id'], ['namespace.id'],
ondelete='CASCADE'),
sa.ForeignKeyConstraint(['thread_id'], ['thread.id'],
ondelete='CASCADE'),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('namespace_id', 'name'))
op.add_column('folderitem',
sa.Column('folder_id', sa.Integer(), nullable=True))
op.create_foreign_key("fk_folder_id",
"folderitem",
"folder", ["folder_id"], ["id"],
ondelete='CASCADE')
op.add_column('account',
sa.Column('inbox_folder_id', sa.Integer, nullable=True))
op.add_column('account',
sa.Column('sent_folder_id', sa.Integer, nullable=True))
op.add_column('account',
sa.Column('drafts_folder_id', sa.Integer, nullable=True))
op.add_column('account',
sa.Column('spam_folder_id', sa.Integer, nullable=True))
op.add_column('account',
sa.Column('trash_folder_id', sa.Integer, nullable=True))
op.add_column('account',
sa.Column('archive_folder_id', sa.Integer, nullable=True))
op.add_column('account',
sa.Column('all_folder_id', sa.Integer, nullable=True))
op.add_column('account',
sa.Column('starred_folder_id', sa.Integer, nullable=True))
op.create_foreign_key('account_ibfk_2', 'account', 'folder',
['inbox_folder_id'], ['id'])
op.create_foreign_key('account_ibfk_3', 'account', 'folder',
['sent_folder_id'], ['id'])
op.create_foreign_key('account_ibfk_4', 'account', 'folder',
['drafts_folder_id'], ['id'])
op.create_foreign_key('account_ibfk_5', 'account', 'folder',
['spam_folder_id'], ['id'])
op.create_foreign_key('account_ibfk_6', 'account', 'folder',
['trash_folder_id'], ['id'])
op.create_foreign_key('account_ibfk_7', 'account', 'folder',
['archive_folder_id'], ['id'])
op.create_foreign_key('account_ibfk_8', 'account', 'folder',
['all_folder_id'], ['id'])
op.create_foreign_key('account_ibfk_9', 'account', 'folder',
['starred_folder_id'], ['id'])
op.add_column('imapuid', sa.Column('folder_id', sa.Integer, nullable=True))
op.create_foreign_key('imapuid_ibfk_3', 'imapuid', 'folder', ['folder_id'],
['id'])
from inbox.server.config import load_config
load_config()
from inbox.server.models import session_scope
from inbox.server.models.ignition import engine
Base = declarative_base()
Base.metadata.reflect(engine)
if 'easuid' in Base.metadata.tables:
easupdate = True
print 'Adding new EASUid columns...'
op.add_column('easuid',
sa.Column('fld_uid', sa.Integer(), nullable=True))
op.add_column('easuid',
sa.Column('folder_id', sa.Integer(), nullable=True))
op.create_foreign_key('easuid_ibfk_3', 'easuid', 'folder',
['folder_id'], ['id'])
op.create_unique_constraint(
'uq_easuid_folder_id_msg_uid_easaccount_id', 'easuid',
['folder_id', 'msg_uid', 'easaccount_id'])
op.create_index('easuid_easaccount_id_folder_id', 'easuid',
['easaccount_id', 'folder_id'])
# Include our changes to the EASUid table:
Base = declarative_base()
Base.metadata.reflect(engine)
class Folder(Base):
__table__ = Base.metadata.tables['folder']
account = relationship('Account',
foreign_keys='Folder.account_id',
backref='folders')
class FolderItem(Base):
__table__ = Base.metadata.tables['folderitem']
folder = relationship('Folder', backref='threads', lazy='joined')
class Thread(Base):
__table__ = Base.metadata.tables['thread']
folderitems = relationship('FolderItem',
backref="thread",
single_parent=True,
cascade='all, delete, delete-orphan')
namespace = relationship('Namespace', backref='threads')
class Namespace(Base):
__table__ = Base.metadata.tables['namespace']
account = relationship('Account',
backref=backref('namespace', uselist=False))
class Account(Base):
__table__ = Base.metadata.tables['account']
inbox_folder = relationship('Folder',
foreign_keys='Account.inbox_folder_id')
sent_folder = relationship('Folder',
foreign_keys='Account.sent_folder_id')
drafts_folder = relationship('Folder',
foreign_keys='Account.drafts_folder_id')
spam_folder = relationship('Folder',
foreign_keys='Account.spam_folder_id')
trash_folder = relationship('Folder',
foreign_keys='Account.trash_folder_id')
starred_folder = relationship('Folder',
foreign_keys='Account.starred_folder_id')
archive_folder = relationship('Folder',
foreign_keys='Account.archive_folder_id')
all_folder = relationship('Folder',
foreign_keys='Account.all_folder_id')
class ImapUid(Base):
__table__ = Base.metadata.tables['imapuid']
folder = relationship('Folder', backref='imapuids', lazy='joined')
if easupdate:
class EASUid(Base):
__table__ = Base.metadata.tables['easuid']
folder = relationship('Folder',
foreign_keys='EASUid.folder_id',
backref='easuids',
lazy='joined')
print 'Creating Folder rows and migrating FolderItems...'
# not many folders per account, so shouldn't grow that big
with session_scope(versioned=False,
ignore_soft_deletes=False) as db_session:
folders = dict([((i.account_id, i.name), i)
for i in db_session.query(Folder).all()])
count = 0
for folderitem in db_session.query(FolderItem).join(Thread).join(
Namespace).yield_per(CHUNK_SIZE):
account_id = folderitem.thread.namespace.account_id
if folderitem.thread.namespace.account.provider == 'Gmail':
if folderitem.folder_name in folder_name_subst_map:
new_folder_name = folder_name_subst_map[
folderitem.folder_name]
else:
new_folder_name = folderitem.folder_name
elif folderitem.thread.namespace.account.provider == 'EAS':
new_folder_name = folderitem.folder_name.title()
if (account_id, new_folder_name) in folders:
f = folders[(account_id, new_folder_name)]
else:
f = Folder(account_id=account_id, name=new_folder_name)
folders[(account_id, new_folder_name)] = f
folderitem.folder = f
count += 1
if count > CHUNK_SIZE:
db_session.commit()
count = 0
db_session.commit()
print 'Migrating ImapUids to reference Folder rows...'
for imapuid in db_session.query(ImapUid).yield_per(CHUNK_SIZE):
account_id = imapuid.imapaccount_id
if imapuid.folder_name in folder_name_subst_map:
new_folder_name = folder_name_subst_map[imapuid.folder_name]
else:
new_folder_name = imapuid.folder_name
if (account_id, new_folder_name) in folders:
f = folders[(account_id, new_folder_name)]
else:
f = Folder(account_id=account_id, name=new_folder_name)
folders[(account_id, new_folder_name)] = f
imapuid.folder = f
count += 1
if count > CHUNK_SIZE:
db_session.commit()
count = 0
db_session.commit()
if easupdate:
print 'Migrating EASUids to reference Folder rows...'
for easuid in db_session.query(EASUid).yield_per(CHUNK_SIZE):
account_id = easuid.easaccount_id
new_folder_name = easuid.folder_name
if (account_id, new_folder_name) in folders:
f = folders[(account_id, new_folder_name)]
else:
f = Folder(account_id=account_id, name=new_folder_name)
folders[(account_id, new_folder_name)] = f
easuid.folder = f
count += 1
if count > CHUNK_SIZE:
db_session.commit()
count = 0
db_session.commit()
print 'Migrating *_folder_name fields to reference Folder rows...'
for account in db_session.query(Account).filter_by(provider='Gmail'):
if account.inbox_folder_name:
# hard replace INBOX with canonicalized caps
k = (account.id, 'Inbox')
if k in folders:
account.inbox_folder = folders[k]
else:
account.inbox_folder = Folder(
account_id=account.id,
name=folder_name_subst_map[account.inbox_folder_name])
if account.sent_folder_name:
k = (account.id, account.sent_folder_name)
if k in folders:
account.sent_folder = folders[k]
else:
account.sent_folder = Folder(account_id=account.id,
name=account.sent_folder_name)
if account.drafts_folder_name:
k = (account.id, account.drafts_folder_name)
if k in folders:
account.drafts_folder = folders[k]
else:
account.drafts_folder = Folder(
account_id=account.id, name=account.drafts_folder_name)
# all/archive mismatch is intentional; semantics have changed
if account.archive_folder_name:
k = (account.id, account.archive_folder_name)
if k in folders:
account.all_folder = folders[k]
else:
account.all_folder = Folder(
account_id=account.id,
name=account.archive_folder_name)
db_session.commit()
if easupdate:
print "Migrating EAS accounts' *_folder_name fields to reference "\
"Folder rows..."
for account in db_session.query(Account).filter_by(provider='EAS'):
if account.inbox_folder_name:
k = (account.id, account.inbox_folder_name)
if k in folders:
account.inbox_folder = folders[k]
else:
account.inbox_folder = Folder(
account_id=account.id,
name=account.inbox_folder_name)
if account.sent_folder_name:
k = (account.id, account.sent_folder_name)
if k in folders:
account.sent_folder = folders[k]
else:
account.sent_folder = Folder(
account_id=account.id,
name=account.sent_folder_name)
if account.drafts_folder_name:
k = (account.id, account.drafts_folder_name)
if k in folders:
account.drafts_folder = folders[k]
else:
account.drafts_folder = Folder(
account_id=account.id,
name=account.drafts_folder_name)
if account.archive_folder_name:
k = (account.id, account.archive_folder_name)
if k in folders:
account.archive_folder = folders[k]
else:
account.archive_folder = Folder(
account_id=account.id,
name=account.archive_folder_name)
db_session.commit()
print 'Final schema tweaks and new constraint enforcement'
op.alter_column('folderitem',
'folder_id',
existing_type=sa.Integer(),
nullable=False)
op.drop_constraint('folder_name', 'folderitem', type_='unique')
op.drop_constraint('folder_name', 'imapuid', type_='unique')
op.create_unique_constraint('uq_imapuid_folder_id_msg_uid_imapaccount_id',
'imapuid',
['folder_id', 'msg_uid', 'imapaccount_id'])
op.drop_column('folderitem', 'folder_name')
op.drop_column('imapuid', 'folder_name')
op.drop_column('account', 'inbox_folder_name')
op.drop_column('account', 'drafts_folder_name')
op.drop_column('account', 'sent_folder_name')
op.drop_column('account', 'archive_folder_name')
if easupdate:
print 'Dropping old EASUid columns...'
op.drop_constraint('folder_name', 'easuid', type_='unique')
op.drop_index('easuid_easaccount_id_folder_name', 'easuid')
op.drop_column('easuid', 'folder_name')
def config():
from inbox.server.config import load_config
from inbox.server.config import config as confdict
load_config(filename=TEST_CONFIG)
from logging.config import fileConfig
from inbox.server.config import load_config
# this is the Alembic Config object, which provides
# access to the values within the .ini file in use.
config = context.config
# Interpret the config file for Python logging.
# This line sets up loggers basically.
fileConfig(config.config_file_name)
# If alembic was invoked with --tag=test, load the test Inbox config. Otherwise
# load the default config.
if context.get_tag_argument() == 'test':
load_config('tests/config.cfg')
else:
load_config()
# add your model's MetaData object here
# for 'autogenerate' support
# from myapp import mymodel
from inbox.server.models import Base, db_uri
target_metadata = Base.metadata
# other values from the config, defined by the needs of env.py,
# can be acquired:
# my_important_option = config.get_main_option("my_important_option")
# ... etc.
def run_migrations_offline():
def upgrade():
from inbox.server.config import load_config
load_config()
from inbox.server.models import session_scope, Session
from inbox.server.models.ignition import engine
from inbox.server.models.tables.base import (Part, Namespace,
Message, Thread)
from inbox.sqlalchemy.util import JSON
print 'Creating table for parts...'
op.create_table('part',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('message_id', sa.Integer(), nullable=True),
sa.Column('walk_index', sa.Integer(), nullable=True),
sa.Column('content_disposition', sa.Enum(
'inline', 'attachment'), nullable=True),
sa.Column(
'content_id', sa.String(length=255), nullable=True),
sa.Column('misc_keyval', JSON(), nullable=True),
sa.Column('is_inboxapp_attachment', sa.Boolean(),
server_default=sa.sql.expression.false(),
nullable=True),
sa.ForeignKeyConstraint(
['id'], ['block.id'], ondelete='CASCADE'),
sa.ForeignKeyConstraint(
['message_id'], ['message.id'], ondelete='CASCADE'),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('message_id', 'walk_index')
)
print 'Reflecting old block table schema'
Base = declarative_base()
Base.metadata.reflect(engine)
class Block_(Base): # old schema, reflected from database table
__table__ = Base.metadata.tables['block']
print 'Adding namespace_id column to blocks ',
op.add_column(
u'block', sa.Column('namespace_id', sa.Integer(), nullable=False))
print 'Migrating from blocks to parts'
new_parts = []
with session_scope() as db_session:
for block in db_session.query(Block_).yield_per(chunk_size):
# Move relevant fields
p = Part()
p.size = block.size
p.data_sha256 = block.data_sha256
p.message_id = block.message_id
p.walk_index = block.walk_index
p.content_disposition = block.content_disposition
p.content_id = block.content_id
p.misc_keyval = block.misc_keyval
p.is_inboxapp_attachment
old_namespace = db_session.query(Namespace) \
.join(Message.thread, Thread.namespace) \
.filter(Message.id == block.message_id).one()
p.namespace_id = old_namespace.id
# Commit after column modifications
new_parts.append(p)
print 'Deleting old blocks (now parts)... ',
db_session.query(Block_).delete()
db_session.commit()
print 'Done!'
print 'Removing `message_id` constraint from block'
op.drop_constraint('block_ibfk_1', 'block', type_='foreignkey')
print 'Creating foreign key for block -> namespace on block'
op.create_foreign_key('block_ibfk_1', 'block', 'namespace',
['namespace_id'], ['id'], ondelete='CASCADE')
print 'Dropping old block columns which are now in part'
op.drop_column(u'block', u'walk_index')
op.drop_column(u'block', u'content_disposition')
op.drop_column(u'block', u'misc_keyval')
op.drop_column(u'block', u'content_id')
op.drop_column(u'block', u'is_inboxapp_attachment')
op.drop_constraint(u'message_id', 'block', type_='unique')
op.drop_column(u'block', u'message_id')
# Note: here we use the regular database session, since the transaction
# log requires the `namespace` property on objects. We've set the
# `namespace_id` foreign key, but need to commit the object before the
# SQLalchemy reference is valid
no_tx_session = Session(autoflush=True, autocommit=False)
no_tx_session.add_all(new_parts)
no_tx_session.commit()
print 'Done migration blocks to parts!'
