def migrate_datas(): from autonomie_base.models.base import DBSESSION session = DBSESSION() from alembic.context import get_bind conn = get_bind() for row in list(conn.execute('SELECT id, leaders FROM workshop')): if not row.leaders: continue try: leaders_list = json.loads(row.leaders) except ValueError: # This should not happen, but some dumps we use have a bare string # in leaders field. leaders_list = [row.leaders] req = sa.text(""" UPDATE workshop SET description=CONCAT( 'Formateurs: ', IFNULL(:leaders, ''), ' ', IFNULL(description, '') ) WHERE id=:id_ """) conn.execute( req, leaders=', '.join(leaders_list), id_=row.id, ) mark_changed(session) session.flush()
def migrate_datas(): from autonomie_base.models.base import DBSESSION from autonomie.models.config import Config session = DBSESSION() from alembic.context import get_bind conn = get_bind() initial_values = [ ('bookentry_facturation_label_template', '{invoice.customer.label} {company.name}'), ('bookentry_contribution_label_template', "{invoice.customer.label} {company.name}"), ('bookentry_rg_interne_label_template', "RG COOP {invoice.customer.label} {company.name}"), ('bookentry_rg_client_label_template', "RG {invoice.customer.label} {company.name}"), ('bookentry_expense_label_template', "{beneficiaire}/frais {expense_date:%-m %Y}"), ('bookentry_payment_label_template', "{company.name} / Rgt {invoice.customer.label}"), ('bookentry_expense_payment_main_label_template', "{beneficiaire_LASTNAME} / REMB FRAIS {expense_date:%B/%Y}"), ('bookentry_expense_payment_waiver_label_template', "Abandon de créance {beneficiaire_LASTNAME} {expense_date:%B/%Y}"), ] for key, val in initial_values: Config.set(key, val)
def upgrade(): from alembic.context import get_bind conn = get_bind() result = conn.execute(""" select invoice.IDTask, invoice.tva, invoice.discountHT, invoice.expenses, invoice.paymentMode, task.statusDate from coop_invoice as invoice join coop_task as task on task.IDTask=invoice.IDTask where task.CAEStatus='paid'; """).fetchall() for i in result: id_, tva, discountHT, expenses, paymentMode, statusDate = i lines = conn.execute(""" select cost, quantity from coop_invoice_line where IDTask='%s'""" % id_).fetchall() totalht = get_ht(lines, discountHT) tva_amount = int(float(totalht) * (max(int(tva), 0) / 10000.0)) ttc = tva_amount + totalht total = ttc + expenses date = datetime.datetime.fromtimestamp(float(statusDate)) # Adding one payment for each invoice that has been marked as "paid" conn.execute(""" insert into payment (mode, amount, date, task_id) VALUE ('%s', '%s', '%s', '%s')"""%(paymentMode, total, date, id_)) # Using direct sql allows to enforce CAEStatus modifications # Ref #573 # Ref #551 op.execute(""" update coop_task as t join coop_invoice as inv on t.IDTask=inv.IDTask set CAEStatus='resulted' WHERE t.CAEStatus='paid'; """) op.execute(""" update coop_task set CAEStatus='resulted' where CAEStatus='gencinv'; """) op.execute(""" update coop_task set CAEStatus='valid' where CAEStatus='sent' OR CAEStatus='recinv'; """) if table_exists("coop_cancel_invoice"): op.execute(""" update coop_task as t join coop_cancel_invoice as est on t.IDTask=est.IDTask SET t.CAEStatus='valid' WHERE t.CAEStatus='paid'; """)
def migrate_datas(): from autonomie_base.models.base import DBSESSION from alembic.context import get_bind from autonomie.models.user.userdatas import SocialStatusDatas session = DBSESSION() connection = get_bind() userdatas_helper = sa.Table( 'user_datas', sa.MetaData(), sa.Column('id', sa.Integer, primary_key=True), sa.Column('statut_social_status_id', sa.Integer()), sa.Column('statut_social_status_today_id', sa.Integer()), ) for userdata in connection.execute(userdatas_helper.select()): if userdata.statut_social_status_id: social_status_entry = SocialStatusDatas( step='entry', userdatas_id=userdata.id, social_status_id=userdata.statut_social_status_id ) session.add(social_status_entry) if userdata.statut_social_status_today_id: social_status_today = SocialStatusDatas( step='today', userdatas_id=userdata.id, social_status_id=userdata.statut_social_status_today_id ) session.add(social_status_today) session.flush()
def upgrade(): from autonomie.models.base import DBSESSION session = DBSESSION() from alembic.context import get_bind request = "select id, type_, name, creationDate, updateDate from task" conn = get_bind() result = conn.execute(request) index = 0 max_id = -1 for id_, type_, name, creationDate, updateDate in result: creationDate = format_date(creationDate) updateDate = format_date(updateDate) index += 1 node = Node( id=id_, created_at=creationDate, updated_at=updateDate, name=name, type_=type_ ) session.add(node) if index % 50 == 0: session.flush() if id_ > max_id: max_id = id_ request = "select id, name, creationDate, updateDate from project ORDER BY id DESC" result = conn.execute(request).fetchall() # We disable foreign key constraints check op.execute("SET FOREIGN_KEY_CHECKS=0;") index = 0 for id_, name, creationDate, updateDate in result: new_id = id_ + max_id creationDate = format_date(creationDate) updateDate = format_date(updateDate) index += 1 node = Node( id=new_id, created_at=creationDate, updated_at=updateDate, name=name, type_='project' ) session.add(node) # We update the foreignkeys for table in ('estimation', 'invoice', 'cancelinvoice', 'phase', 'project_customer'): op.execute("update {0} set project_id={1} where project_id={2}".format(table, new_id, id_)) # We update the project id op.execute("update project set id={0} where id={1};".format(new_id, id_)) if index % 50 == 0: session.flush() op.execute("SET FOREIGN_KEY_CHECKS=1;")
def upgrade(): # Only run this for somehow supported data types at the date we started naming constraints # Among others, these will probably fail on MySQL if context.get_bind().engine.name not in ('sqlite', 'postgresql'): return metadata = context.get_context().opts['target_metadata'] # Drop every constraint on every table with op.batch_alter_table('alias', naming_convention=metadata.naming_convention) as batch_op: batch_op.drop_constraint('alias_pkey', type_="primary") batch_op.drop_constraint('alias_domain_name_fkey', type_="foreignkey") with op.batch_alter_table('alternative', naming_convention=metadata.naming_convention) as batch_op: batch_op.drop_constraint('alternative_pkey', type_="primary") batch_op.drop_constraint('alternative_domain_name_fkey', type_="foreignkey") with op.batch_alter_table('manager', naming_convention=metadata.naming_convention) as batch_op: batch_op.drop_constraint('manager_domain_name_fkey', type_="foreignkey") batch_op.drop_constraint('manager_user_email_fkey', type_="foreignkey") with op.batch_alter_table('token', naming_convention=metadata.naming_convention) as batch_op: batch_op.drop_constraint('token_pkey', type_="primary") batch_op.drop_constraint('token_user_email_fkey', type_="foreignkey") with op.batch_alter_table('fetch', naming_convention=metadata.naming_convention) as batch_op: batch_op.drop_constraint('fetch_pkey', type_="primary") batch_op.drop_constraint('fetch_user_email_fkey', type_="foreignkey") with op.batch_alter_table('relay', naming_convention=metadata.naming_convention) as batch_op: batch_op.drop_constraint('relay_pkey', type_="primary") with op.batch_alter_table('config', naming_convention=metadata.naming_convention) as batch_op: batch_op.drop_constraint('config_pkey', type_="primary") with op.batch_alter_table('user', naming_convention=metadata.naming_convention) as batch_op: batch_op.drop_constraint('user_pkey', type_="primary") batch_op.drop_constraint('user_domain_name_fkey', type_="foreignkey") with op.batch_alter_table('domain', naming_convention=metadata.naming_convention) as batch_op: batch_op.drop_constraint('domain_pkey', type_="primary") # Recreate constraints with proper names with op.batch_alter_table('domain', naming_convention=metadata.naming_convention) as batch_op: batch_op.create_primary_key('domain_pkey', ['name']) with op.batch_alter_table('alias', naming_convention=metadata.naming_convention) as batch_op: batch_op.create_primary_key('alias_pkey', ['email']) batch_op.create_foreign_key('alias_domain_name_fkey', 'domain', ['domain_name'], ['name']) with op.batch_alter_table('user', naming_convention=metadata.naming_convention) as batch_op: batch_op.create_primary_key('user_pkey', ['email']) batch_op.create_foreign_key('user_domain_name_fkey', 'domain', ['domain_name'], ['name']) with op.batch_alter_table('alternative', naming_convention=metadata.naming_convention) as batch_op: batch_op.create_primary_key('alternative_pkey', ['name']) batch_op.create_foreign_key('alternative_domain_name_fkey', 'domain', ['domain_name'], ['name']) with op.batch_alter_table('manager', naming_convention=metadata.naming_convention) as batch_op: batch_op.create_foreign_key('manager_domain_name_fkey', 'domain', ['domain_name'], ['name']) batch_op.create_foreign_key('manager_user_email_fkey', 'user', ['user_email'], ['email']) with op.batch_alter_table('token', naming_convention=metadata.naming_convention) as batch_op: batch_op.create_primary_key('token_pkey', ['id']) batch_op.create_foreign_key('token_user_email_fkey', 'user', ['user_email'], ['email']) with op.batch_alter_table('fetch', naming_convention=metadata.naming_convention) as batch_op: batch_op.create_primary_key('fetch_pkey', ['id']) batch_op.create_foreign_key('fetch_user_email_fkey', 'user', ['user_email'], ['email']) with op.batch_alter_table('relay', naming_convention=metadata.naming_convention) as batch_op: batch_op.create_primary_key('relay_pkey', ['name']) with op.batch_alter_table('config', naming_convention=metadata.naming_convention) as batch_op: batch_op.create_primary_key('config_pkey', ['name'])
def migrate_datas(): from autonomie_base.models.base import DBSESSION session = DBSESSION() from alembic.context import get_bind connection = get_bind() op.execute("UPDATE sale_product_group SET type_='base'") from zope.sqlalchemy import mark_changed mark_changed(session)
def migrate_datas(): from autonomie_base.models.base import DBSESSION session = DBSESSION() from alembic.context import get_bind conn = get_bind() from autonomie.models.config import Config Config.set('accounting_label_maxlength', 35) session.flush()
def migrate_datas(): from autonomie_base.models.base import DBSESSION session = DBSESSION() from alembic.context import get_bind conn = get_bind() op.execute('update customer set civilite="M. et Mme" where civilite="mr&mme"') from zope.sqlalchemy import mark_changed mark_changed(session)
def upgrade(): from autonomie.models.base import DBSESSION session = DBSESSION() from alembic.context import get_bind request = "select id, type_, name, creationDate, updateDate from task" conn = get_bind() result = conn.execute(request) index = 0 max_id = -1 for id_, type_, name, creationDate, updateDate in result: creationDate = format_date(creationDate) updateDate = format_date(updateDate) index += 1 node = Node(id=id_, created_at=creationDate, updated_at=updateDate, name=name, type_=type_) session.add(node) if index % 50 == 0: session.flush() if id_ > max_id: max_id = id_ request = "select id, name, creationDate, updateDate from project ORDER BY id DESC" result = conn.execute(request).fetchall() # We disable foreign key constraints check op.execute("SET FOREIGN_KEY_CHECKS=0;") index = 0 for id_, name, creationDate, updateDate in result: new_id = id_ + max_id creationDate = format_date(creationDate) updateDate = format_date(updateDate) index += 1 node = Node(id=new_id, created_at=creationDate, updated_at=updateDate, name=name, type_='project') session.add(node) # We update the foreignkeys for table in ('estimation', 'invoice', 'cancelinvoice', 'phase', 'project_customer'): op.execute( "update {0} set project_id={1} where project_id={2}".format( table, new_id, id_)) # We update the project id op.execute("update project set id={0} where id={1};".format( new_id, id_)) if index % 50 == 0: session.flush() op.execute("SET FOREIGN_KEY_CHECKS=1;")
def upgrade(): from autonomie.models.company import Company from autonomie.models.files import File from autonomie_base.models.base import DBSESSION from alembic.context import get_bind from autonomie.models.config import ConfigFiles for i in ('header_id', 'logo_id',): col = sa.Column(i, sa.Integer, sa.ForeignKey('file.id')) op.add_column('company', col) query = "select id, header, logo from company;" conn = get_bind() result = conn.execute(query) session = DBSESSION() for id_, header, logo in result: company = Company.get(id_) basepath = u"%scompany/%s" % (BASEFILEPATH, id_,) if header: header_path = u"%s/header/%s" % (basepath, header) try: file_datas = load_file_struct(header_path, header) except: print("Error while loading a header") print(id_) file_datas = None if file_datas: company.header = file_datas session.add(company.header_file) session.flush() if logo: logo_path = u"%s/logo/%s" % (basepath, logo) try: file_datas = load_file_struct(logo_path, logo) except: print("Error while loading a logo") print(id_) file_datas = None if file_datas: company.logo = file_datas company = session.merge(company) session.flush() filepath = u"%s/main/logo.png" % BASEFILEPATH if os.path.isfile(filepath): ConfigFiles.set('logo.png', load_file_struct(filepath, 'logo.png')) filepath = u"%s/main/accompagnement_header.png" % BASEFILEPATH if os.path.isfile(filepath): ConfigFiles.set( 'accompagnement_header.png', load_file_struct(filepath, 'accompagnement_header.png') )
def upgrade(): from autonomie.models.company import Company from autonomie.models.files import File from autonomie.models import DBSESSION from alembic.context import get_bind from autonomie.models.config import ConfigFiles for i in ('header_id', 'logo_id',): col = sa.Column(i, sa.Integer, sa.ForeignKey('file.id')) op.add_column('company', col) query = "select id, header, logo from company;" conn = get_bind() result = conn.execute(query) session = DBSESSION() for id_, header, logo in result: company = Company.get(id_) basepath = u"%scompany/%s" % (BASEFILEPATH, id_,) if header: header_path = u"%s/header/%s" % (basepath, header) try: file_datas = load_file_struct(header_path, header) except: print("Error while loading a header") print(id_) file_datas = None if file_datas: company.header = file_datas session.add(company.header_file) session.flush() if logo: logo_path = u"%s/logo/%s" % (basepath, logo) try: file_datas = load_file_struct(logo_path, logo) except: print("Error while loading a logo") print(id_) file_datas = None if file_datas: company.logo = file_datas company = session.merge(company) session.flush() filepath = u"%s/main/logo.png" % BASEFILEPATH if os.path.isfile(filepath): ConfigFiles.set('logo.png', load_file_struct(filepath, 'logo.png')) filepath = u"%s/main/accompagnement_header.png" % BASEFILEPATH if os.path.isfile(filepath): ConfigFiles.set( 'accompagnement_header.png', load_file_struct(filepath, 'accompagnement_header.png') )
def lazy_load(): dialect = context.get_bind().dialect.name module = globals() # Lookup the type based on the dialect specific type, or fallback to the generic type type_ = module.get(f'_{dialect}_{name}', None) or module.get(f'_sa_{name}') val = module[name] = type_() return val
def _mssql_use_date_time2(): conn = context.get_bind() result = conn.execute( """SELECT CASE WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '8%' THEN '2000' WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '9%' THEN '2005' ELSE '2005Plus' END AS MajorVersion""").fetchone( ) mssql_version = result[0] return mssql_version not in ("2000", "2005")
def migrate_datas(): from autonomie_base.models.base import DBSESSION session = DBSESSION() from alembic.context import get_bind connection = get_bind() from autonomie.models.user.login import Login op.execute("update groups set editable=0;") op.execute("update groups set `primary`=0;") op.execute( "update groups set `primary`=1 where name IN ('admin', 'contractor', 'manager')" ) op.execute('update accounts set civilite="Monsieur"') for user in connection.execute(user_helper.select()): login = Login( user_id=user.id, login=user.login, ) login.pwd_hash = user.password, login.active = user.active == 'Y' session.add(login) session.flush() op.execute( 'UPDATE user_groups set login_id="%s" where user_id=%s' % ( login.id, user.id ) ) op.drop_column("accounts", "login") op.drop_column("accounts", "password") op.drop_column("accounts", "active") from autonomie.models.user.user import User for userdatas in connection.execute(userdatas_helper.select()): if userdatas.user_id is None: user = User( lastname=userdatas.coordonnees_lastname, firstname=userdatas.coordonnees_firstname, email=userdatas.coordonnees_email1, civilite=userdatas.coordonnees_civilite or 'Monsieur', ) session.add(user) session.flush() connection.execute( userdatas_helper.update().where( userdatas_helper.c.id == userdatas.id ).values(user_id=user.id) ) else: user = User.get(userdatas.user_id) user.civilite = userdatas.coordonnees_civilite or 'Monsieur' session.merge(user) session.flush() op.execute('update accounts set civilite="Monsieur" where civilite is NULL')
def add_company_id(session, logger): logger.warn("Adding company_id to Task") from alembic.context import get_bind conn = get_bind() query = "select t.id, p.company_id from task t, project p where t.project_id = p.id" for id, company_id in conn.execute(query): req = "update task set company_id={0} where id={1}".format(company_id, id) session.execute(req)
def migrate_datas(): from autonomie.models.project.types import BusinessType from autonomie.models.config import Config from autonomie.models.task.mentions import TaskMention from autonomie.models.project.mentions import BusinessTypeTaskMention from autonomie_base.models.base import DBSESSION from alembic.context import get_bind session = DBSESSION() conn = get_bind() # Collect business type ids business_type_ids = [b[0] for b in session.query(BusinessType.id)] # for each fixed config key we now use mentions for index, (doctype, key, label, title) in enumerate((( 'estimation', 'coop_estimationfooter', u"Informations sur l'acceptation des devis", u"Acceptation du devis", ), ( "invoice", "coop_invoicepayment", u"Informations de paiement pour les factures", u"Mode de paiement", ), ( "invoice", "coop_invoicelate", u"Informations sur les retards de paiement", u"Retard de paiement", ))): # We retrieve the configurated value value = Config.get_value(key, "") mention = TaskMention( order=index, label=label, title=title, full_text=value.replace('%IBAN%', "{IBAN}").replace( '%RIB%', "{RIB}").replace('%ENTREPRENEUR%', '{name}')) session.add(mention) session.flush() for btype_id in business_type_ids: rel = BusinessTypeTaskMention( task_mention_id=mention.id, business_type_id=btype_id, doctype=doctype, mandatory=True, ) session.add(rel) session.flush() op.execute( u"INSERT INTO mandatory_task_mention_rel (task_id, mention_id) \ SELECT task.id, {mention_id} from task join node on task.id=node.id where \ node.type_='{type_}'".format(mention_id=mention.id, type_=doctype))
def upgrade(): connection = context.get_bind() metadata = MetaData(bind=connection) resource_providers = Table('resource_providers', metadata, autoload=True) query = select([sqlfunc.count()]).select_from(resource_providers).where( resource_providers.c.root_provider_id == sa.null()) if connection.scalar(query): raise Exception('There is at least one resource provider table ' 'record which is missing its root provider id. ' 'Run the "placement-manage db ' 'online_data_migrations" command.')
def migrate_datas(): """We tolerate duplicate invoice number for invoices issued so far. That means that coops have to migrate to real unique invoice number template before then. """ from autonomie_base.models.base import DBSESSION session = DBSESSION() from alembic.context import get_bind conn = get_bind() conn.execute("UPDATE task set legacy_number = 1") mark_changed(session)
def __getattr__(name): if name in ["TIMESTAMP", "StringID"]: dialect = context.get_bind().dialect.name module = globals() # Lookup the type based on the dialect specific type, or fallback to the generic type type_ = module.get(f'_{dialect}_{name}', None) or module.get(f'_sa_{name}') val = module[name] = type_() return val raise AttributeError(f"module {__name__} has no attribute {name}")
def migrate_datas(): from autonomie_base.models.base import DBSESSION session = DBSESSION() from alembic.context import get_bind conn = get_bind() # Fill SequenceNumber for existing invoices op.execute(""" INSERT INTO task_sequence_number (`task_id`, `index`, `sequence`) SELECT task.id, official_number, 'invoice_year' from task LEFT JOIN invoice on task.id = invoice.id LEFT JOIN cancelinvoice on cancelinvoice.id = task.id WHERE task.official_number IS NOT NULL ;""")
def upgrade(): from kotti.resources import DBSession from alembic.context import get_bind conn = get_bind() if conn.engine.dialect.name == 'mysql': update = "update nodes set path = concat(path, '/') where path not \ like '%/'" else: update = "update nodes set path = path || '/' where path not like '%/'" DBSession.execute(update)
def upgrade(): op.add_column('bank_account', sa.Column('code_journal', sa.String(120), nullable=False)) req = "select config_value from config where config_name='receipts_code_journal';" from alembic.context import get_bind from autonomie_base.models.base import DBSESSION conn = get_bind() res = conn.execute(req).scalar() if res is not None: req = "update bank_account set code_journal='%s'" % res conn.execute(req) session = DBSESSION() from zope.sqlalchemy import mark_changed mark_changed(session)
def migrate_datas(): from autonomie_base.models.base import DBSESSION session = DBSESSION() from alembic.context import get_bind conn = get_bind() from autonomie.models.task import Task op.execute(""" UPDATE task LEFT JOIN invoice on task.id = invoice.id LEFT JOIN cancelinvoice on cancelinvoice.id = task.id SET official_number = CONCAT(IFNULL(prefix, ''), official_number) WHERE (cancelinvoice.id IS NOT NULL) OR (invoice.id IS NOT NULL) ;""")
def upgrade(): from alembic.context import get_bind conn = get_bind() if conn.engine.dialect.name == 'mysql': op.add_column('nodes', sa.Column('path', sa.Unicode(1000))) else: op.add_column('nodes', sa.Column('path', sa.Unicode(1000), index=True)) from kotti import DBSession from kotti.resources import Node for node in DBSession.query(Node).with_polymorphic([Node]): reversed_lineage = reversed(tuple(lineage(node))) node.path = '/'.join(node.__name__ for node in reversed_lineage) or '/'
def upgrade(): op.add_column( 'bank_account', sa.Column('code_journal', sa.String(120), nullable=False) ) req = "select config_value from config where config_name='receipts_code_journal';" from alembic.context import get_bind from autonomie_base.models.base import DBSESSION conn = get_bind() res = conn.execute(req).scalar() if res is not None: req = "update bank_account set code_journal='%s'" % res conn.execute(req) session = DBSESSION() from zope.sqlalchemy import mark_changed mark_changed(session)
def upgrade(): from alembic.context import get_bind conn = get_bind() if conn.engine.dialect.name == 'mysql': op.add_column('nodes', sa.Column('path', sa.Unicode(1000))) else: op.add_column('nodes', sa.Column('path', sa.Unicode(1000), index=True)) from kotti import DBSession from kotti.resources import Node for node in DBSession.query(Node).with_polymorphic([Node]): reversed_lineage = reversed(tuple(lineage(node))) node.path = u'/'.join( node.__name__ for node in reversed_lineage) or u'/'
def upgrade(): from kotti.resources import DBSession from alembic.context import get_bind conn = get_bind() if conn.engine.dialect.name == 'mysql': update = "UPDATE nodes " \ "SET path = concat(path, '/') " \ "WHERE path NOT LIKE '%/'" else: update = "UPDATE nodes " \ "SET path = path || '/' " \ "WHERE path NOT LIKE '%/'" DBSession.execute(update)
def migrate_datas(): from autonomie_base.models.base import DBSESSION session = DBSESSION() from alembic.context import get_bind conn = get_bind() op.alter_column( "custom_invoice_book_entry_module", "percentage", type_=sa.Float(), nullable=False, ) op.alter_column( "task", "internal_number", type_=sa.String(255), nullable=False, )
def upgrade(): connection = context.get_bind() metadata = sa.MetaData(bind=connection) consumers = sa.Table('consumers', metadata, autoload=True) allocations = sa.Table('allocations', metadata, autoload=True) alloc_to_consumer = sa.outerjoin( allocations, consumers, allocations.c.consumer_id == consumers.c.uuid) cols = [ sqlfunc.count(), ] sel = sa.select(cols) sel = sel.select_from(alloc_to_consumer) sel = sel.where(consumers.c.id.is_(None)) if connection.scalar(sel): raise Exception('There is at least one allocation record which is ' 'missing a consumer record. Run the "placement-manage ' 'db online_data_migrations" command.')
def migrate_datas(): from autonomie_base.models.base import DBSESSION from autonomie.models.config import Config session = DBSESSION() from alembic.context import get_bind conn = get_bind() initial_values = [ ( 'bookentry_facturation_label_template', '{invoice.customer.label} {company.name}' ), ( 'bookentry_contribution_label_template', "{invoice.customer.label} {company.name}" ), ( 'bookentry_rg_interne_label_template', "RG COOP {invoice.customer.label} {company.name}" ), ( 'bookentry_rg_client_label_template', "RG {invoice.customer.label} {company.name}" ), ( 'bookentry_expense_label_template', "{beneficiaire}/frais {expense_date:%-m %Y}" ), ( 'bookentry_payment_label_template', "{company.name} / Rgt {invoice.customer.label}" ), ( 'bookentry_expense_payment_main_label_template', "{beneficiaire_LASTNAME} / REMB FRAIS {expense_date:%B/%Y}" ), ( 'bookentry_expense_payment_waiver_label_template', "Abandon de créance {beneficiaire_LASTNAME} {expense_date:%B/%Y}" ), ] for key, val in initial_values: Config.set(key, val)
def upgrade(): from alembic.context import get_bind op.add_column( "user_datas", sa.Column( "situation_situation_id", sa.Integer, sa.ForeignKey("cae_situation_option.id"), ) ) op.add_column( "configurable_option", sa.Column( "order", sa.Integer, default=0 ) ) from autonomie.models.user import ( CaeSituationOption, ) from autonomie_base.models.base import DBSESSION temp_dict = {} for key, value in SITUATION_OPTIONS: if key == "integre": option = CaeSituationOption(label=value, is_integration=True) else: option = CaeSituationOption(label=value) DBSESSION().add(option) DBSESSION().flush() temp_dict[key] = option.id conn = get_bind() query = "select id, situation_situation from user_datas" result = conn.execute(query) for id, situation in result: option_id = temp_dict.get(situation) if option_id is None: continue query = "update user_datas set situation_situation_id='{0}' \ where id='{1}'".format(option_id, id) op.execute(query)
def upgrade(): op.add_column('node', sa.Column('_acl', sa.Text())) from autonomie.models.base import DBSESSION from autonomie.models.node import Node session = DBSESSION() from alembic.context import get_bind conn = get_bind() req = "select max(id) from node" result = conn.execute(req).fetchall() max_id = result[0][0] print("The new max_id is : %s" % max_id) request = "select id, coordonnees_lastname from user_datas" result = conn.execute(request) op.execute("SET FOREIGN_KEY_CHECKS=0;") for index, (id, lastname) in enumerate(result): max_id += 1 new_id = max_id node = Node( id=new_id, name=lastname, type_='userdata', ) session.add(node) # Update des relations for table in "userdatas_socialdocs", "external_activity_datas", \ "company_datas", "date_diagnostic_datas", \ "date_convention_cape_datas", "date_dpae_datas": op.execute( "update {0} set userdatas_id={1} where userdatas_id={2}". format(table, new_id, id)) # Update de la table node op.execute("update user_datas set id={0} where id={1};".format( new_id, id)) if index % 50 == 0: session.flush() op.execute("SET FOREIGN_KEY_CHECKS=1;")
def upgrade(): from autonomie.models import DBSESSION session = DBSESSION() from autonomie.models.activity import ActivityAction from alembic.context import get_bind for name in "subaction_id", "action_id": col = sa.Column(name, sa.Integer, sa.ForeignKey("activity_action.id")) op.add_column("activity", col) label_request = "select id, action_label, subaction_label from activity" conn = get_bind() result = conn.execute(label_request) already_added = {} for id, action_label, subaction_label in result: if (action_label, subaction_label) not in already_added.keys(): found = False for key, value in already_added.items(): if action_label == key[0]: action_id = value[0] found = True if not found: action = ActivityAction(label=action_label) session.add(action) session.flush() action_id = action.id subaction = ActivityAction(label=subaction_label, parent_id=action_id) session.add(subaction) session.flush() subaction_id = subaction.id already_added[(action_label, subaction_label)] = (action_id, subaction_id) else: action_id, subaction_id = already_added[(action_label, subaction_label)] op.execute("update activity set action_id={0}, subaction_id={1} \ where id={2}".format(action_id, subaction_id, id))
def upgrade(): op.add_column('node', sa.Column('_acl', sa.Text())) from autonomie.models.base import DBSESSION from autonomie.models.node import Node session = DBSESSION() from alembic.context import get_bind conn = get_bind() req = "select max(id) from node" result = conn.execute(req).fetchall() max_id = result[0][0] print("The new max_id is : %s" % max_id) request = "select id, coordonnees_lastname from user_datas" result = conn.execute(request) op.execute("SET FOREIGN_KEY_CHECKS=0;") for index, (id, lastname) in enumerate(result): max_id += 1 new_id = max_id node = Node( id=new_id, name=lastname, type_='userdata', ) session.add(node) # Update des relations for table in "userdatas_socialdocs", "external_activity_datas", \ "company_datas", "date_diagnostic_datas", \ "date_convention_cape_datas", "date_dpae_datas": op.execute("update {0} set userdatas_id={1} where userdatas_id={2}".format(table, new_id, id)) # Update de la table node op.execute("update user_datas set id={0} where id={1};".format(new_id, id)) if index % 50 == 0: session.flush() op.execute("SET FOREIGN_KEY_CHECKS=1;")
def upgrade(): from autonomie_base.models.base import DBSESSION session = DBSESSION() from autonomie.models.activity import ActivityAction from alembic.context import get_bind for name in "subaction_id", "action_id": col = sa.Column(name, sa.Integer, sa.ForeignKey("activity_action.id")) op.add_column("activity", col) label_request = "select id, action_label, subaction_label from activity" conn = get_bind() result = conn.execute(label_request) already_added = {} for id, action_label, subaction_label in result: if (action_label, subaction_label) not in already_added.keys(): found = False for key, value in already_added.items(): if action_label == key[0]: action_id = value[0] found = True if not found: action = ActivityAction(label=action_label) session.add(action) session.flush() action_id = action.id subaction = ActivityAction(label=subaction_label, parent_id=action_id) session.add(subaction) session.flush() subaction_id = subaction.id already_added[(action_label, subaction_label)] = (action_id, subaction_id) else: action_id, subaction_id = already_added[(action_label, subaction_label)] op.execute("update activity set action_id={0}, subaction_id={1} \ where id={2}".format(action_id, subaction_id, id))
def migrate_datas(): from autonomie_base.models.base import DBSESSION from autonomie.models.config import Config session = DBSESSION() from alembic.context import get_bind conn = get_bind() deprecated_conf_keys = [ 'compte_cgscop', 'compte_cg_debiteur', 'compte_cg_organic', 'compte_cg_debiteur_organic', 'compte_cg_assurance', 'taux_assurance', 'taux_cgscop', 'taux_contribution_organic', 'sage_assurance', 'sage_cgscop', 'sage_organic', ] q = Config.query().filter(Config.name.in_(deprecated_conf_keys)) q.delete(synchronize_session=False) session.flush()
def upgrade(): from alembic.context import get_bind conn = get_bind() result = conn.execute(""" select invoice.IDTask, invoice.tva, invoice.discountHT, invoice.expenses, invoice.paymentMode, task.statusDate from coop_invoice as invoice join coop_task as task on task.IDTask=invoice.IDTask where task.CAEStatus='paid'; """).fetchall() for i in result: id_, tva, discountHT, expenses, paymentMode, statusDate = i lines = conn.execute(""" select cost, quantity from coop_invoice_line where IDTask='%s'""" % id_).fetchall() totalht = get_ht(lines, discountHT) tva_amount = int(float(totalht) * (max(int(tva), 0) / 10000.0)) ttc = tva_amount + totalht total = ttc + expenses date = datetime.datetime.fromtimestamp(float(statusDate)) # Adding one payment for each invoice that has been marked as "paid" conn.execute(""" insert into payment (mode, amount, date, task_id) VALUE ('%s', '%s', '%s', '%s')""" % (paymentMode, total, date, id_)) # Using direct sql allows to enforce CAEStatus modifications # Ref #573 # Ref #551 op.execute(""" update coop_task as t join coop_invoice as inv on t.IDTask=inv.IDTask set CAEStatus='resulted' WHERE t.CAEStatus='paid'; """) op.execute(""" update coop_task set CAEStatus='resulted' where CAEStatus='gencinv'; """) op.execute(""" update coop_task set CAEStatus='valid' where CAEStatus='sent' OR CAEStatus='recinv'; """) if table_exists("coop_cancel_invoice"): op.execute(""" update coop_task as t join coop_cancel_invoice as est on t.IDTask=est.IDTask SET t.CAEStatus='valid' WHERE t.CAEStatus='paid'; """)
def upgrade(active_plugins=None, options=None): if not migration.should_run(active_plugins, migration_for_plugins): return if context.is_offline_mode(): tables = ['servicedefinitions', 'servicetypes'] else: connection = context.get_bind() tables = connection.engine.table_names() op.create_table( 'providerresourceassociations', sa.Column('provider_name', sa.String(length=255), nullable=False), sa.Column('resource_id', sa.String(length=36), nullable=False, unique=True), ) # Tables are not created if grizzly didn't use neutron-db-manage before # starting neutron service. for table in ('servicedefinitions', 'servicetypes'): if table in tables: op.drop_table(table)
Revises: 27e81ea4d86 Create Date: 2014-02-27 02:00:25.694782 """ from alembic import context from alembic.op import (create_index, create_table, drop_index, drop_table, execute) from sqlalchemy.schema import Column, PrimaryKeyConstraint from sqlalchemy.types import DateTime, Integer, String # revision identifiers, used by Alembic. revision = '2d8b17e13d1' down_revision = '27e81ea4d86' driver_name = context.get_bind().dialect.name def upgrade(): create_table( 'teams', Column('id', Integer, nullable=False), Column('name', String), Column('created_at', DateTime(timezone=True), nullable=False), PrimaryKeyConstraint('id') ) create_index('ix_teams_created_at', 'teams', ['created_at']) create_index('ix_teams_name', 'teams', ['name']) def downgrade():
def upgrade(): from autonomie.models import DBSESSION from autonomie.models.workshop import WorkshopAction from alembic.context import get_bind session = DBSESSION() conn = get_bind() col = sa.Column("activity_id", sa.Integer(), sa.ForeignKey("company_activity.id")) op.add_column("company_datas", col) col = sa.Column("archived", sa.Boolean(), default=False, server_default="0") op.add_column("customer", col) # Migration de accompagnement_header.png en activity_header.png op.execute( 'update config_files set config_files.key="activity_header_img.png" where \ config_files.key="accompagnement_header.png";' ) # Le bas de page des pdfs est celui par defaut pour les ateliers et rdv from autonomie.models.config import Config val = Config.get("coop_pdffootertext").value if val: for key in ("activity", "workshop"): config_key = "%s_footer" % key config = Config.set(config_key, val) # Migration de la taille des libelles pour les actions des rendez-vous op.execute("alter table activity_action modify label VARCHAR(255)") # Migration des intitules des ateliers # 1- Ajout des nouvelles foreignkey for name in "info1_id", "info2_id", "info3_id": col = sa.Column(name, sa.Integer, sa.ForeignKey("workshop_action.id")) op.add_column("workshop", col) # 2- création des options en fonction des valeurs en durs request = "select id, info1, info2, info3 from workshop" result = conn.execute(request) already_added = {} for id, info1, info2, info3 in result: info1 = info1.lower() info2 = info2.lower() info3 = info3.lower() info1_id = info2_id = info3_id = None if (info1, info2, info3) not in already_added.keys(): for key, value in already_added.items(): if key[0] == info1 and info1: info1_id = value[0] if key[1] == info2 and info2: info2_id = value[1] if info1_id is None and info1: w = WorkshopAction(label=info1) session.add(w) session.flush() info1_id = w.id if info2_id is None and info2: w = WorkshopAction(label=info2, parent_id=info1_id) session.add(w) session.flush() info2_id = w.id if info3: w = WorkshopAction(label=info3, parent_id=info2_id) session.add(w) session.flush() info3_id = w.id already_added[(info1, info2, info3)] = (info1_id, info2_id, info3_id) else: info1_id, info2_id, info3_id = already_added[(info1, info2, info3)] request = "update workshop " if info1_id: request += "set info1_id={0}".format(info1_id) if info2_id: request += ", info2_id={0}".format(info2_id) if info3_id: request += ", info3_id={0}".format(info3_id) request += " where id={0}".format(id) op.execute(request)
def migrate_datas(): from autonomie_base.models.base import DBSESSION session = DBSESSION() from alembic.context import get_bind conn = get_bind()
def upgrade(): now = time.time() logger = logging.getLogger("autonomie") logger.addHandler(logging.StreamHandler(sys.stdout)) logger.setLevel(logging.INFO) from depot.fields.upload import UploadedFile from sqlalchemy import bindparam from autonomie.models.base import DBSESSION, METADATA session = DBSESSION() def process(thing, store): id, data, filename, mimetype = thing logger.debug("Handling file with id %s" % id) uploaded_file = UploadedFile({"depot_name": "local", "files": []}) uploaded_file._thaw() uploaded_file.process_content(data, filename=filename, content_type=mimetype) store.append({"nodeid": thing.id, "depot_datas": uploaded_file.encode()}) logger.info("Saved data for node id {}".format(id)) window_size = 10 window_idx = 0 logger.info("# Starting migration of blob datas #") from alembic.context import get_bind conn = get_bind() # Processing the file table logger.debug(" + Processing files") files = sa.Table("file", METADATA) processed_files = [] count = session.query(files.c.id).count() logger.debug(u" + Moving the files on disk") while True: start = window_size * window_idx if start >= count: break logger.debug("Slicing from %s" % (start,)) req = ( "select distinct(file.id), data, node.name, mimetype from file join node on file.id=node.id LIMIT %s, %s" % (start, window_size) ) things = conn.execute(req) if things is None: break for thing in things: process(thing, processed_files) window_idx += 1 logger.debug(u"-> Done") logger.debug(u"Migrating the 'data' column") op.drop_column("file", "data") op.add_column("file", sa.Column("data", sa.Unicode(4096))) files.c.data.type = sa.Unicode(4096) update = files.update().where(files.c.id == bindparam("nodeid")).values({files.c.data: bindparam("depot_datas")}) def chunks(l, n): for i in xrange(0, len(l), n): yield l[i : i + n] for cdata in chunks(processed_files, 10): session.execute(update, cdata) logger.debug(" + Processing config files") logger.debug(u" + Moving the files on disk") config_files = sa.Table("config_files", METADATA) processed_config_files = [] req = "select id, data, name, mimetype from config_files" for thing in conn.execute(req): process(thing, processed_config_files) op.drop_column("config_files", "data") op.add_column("config_files", sa.Column("data", sa.Unicode(4096))) config_files.c.data.type = sa.Unicode(4096) update = ( config_files.update() .where(config_files.c.id == bindparam("nodeid")) .values({config_files.c.data: bindparam("depot_datas")}) ) session.execute(update, processed_config_files) logger.debug(u"-> Done") from zope.sqlalchemy import mark_changed mark_changed(session) logger.info("Blob migration completed in {} seconds".format(int(time.time() - now)))
def migrate_datas(): from autonomie.models.project.types import BusinessType from autonomie.models.config import Config from autonomie.models.task.mentions import TaskMention from autonomie.models.project.mentions import BusinessTypeTaskMention from autonomie_base.models.base import DBSESSION from alembic.context import get_bind session = DBSESSION() conn = get_bind() # Collect business type ids business_type_ids = [b[0] for b in session.query(BusinessType.id)] # for each fixed config key we now use mentions for index, (doctype, key, label, title) in enumerate(( ( 'estimation', 'coop_estimationfooter', u"Informations sur l'acceptation des devis", u"Acceptation du devis", ), ( "invoice", "coop_invoicepayment", u"Informations de paiement pour les factures", u"Mode de paiement", ), ( "invoice", "coop_invoicelate", u"Informations sur les retards de paiement", u"Retard de paiement", ) )): # We retrieve the configurated value value = Config.get_value(key, "") mention = TaskMention( order=index, label=label, title=title, full_text=value.replace( '%IBAN%', "{IBAN}").replace( '%RIB%', "{RIB}").replace( '%ENTREPRENEUR%', '{name}') ) session.add(mention) session.flush() for btype_id in business_type_ids: rel = BusinessTypeTaskMention( task_mention_id=mention.id, business_type_id=btype_id, doctype=doctype, mandatory=True, ) session.add(rel) session.flush() op.execute( u"INSERT INTO mandatory_task_mention_rel (task_id, mention_id) \ SELECT task.id, {mention_id} from task join node on task.id=node.id where \ node.type_='{type_}'".format(mention_id=mention.id, type_=doctype) )
def upgrade(): from autonomie.models.activity import Attendance, Activity from autonomie.models import DBSESSION from alembic.context import get_bind session = DBSESSION() # Migrating attendance relationship query = "select event.id, event.status, rel.account_id, rel.activity_id from activity_participant rel inner join activity on rel.activity_id=activity.id LEFT JOIN event on event.id=activity.id" conn = get_bind() result = conn.execute(query) handled = [] for event_id, status, user_id, activity_id in result: if status == 'planned': user_status = 'registered' elif status == 'excused': user_status = 'excused' status = 'cancelled' elif status == 'closed': user_status = 'attended' elif status == 'absent': user_status = 'absent' status = 'cancelled' # create attendance for each participant if (user_id, activity_id) not in handled: a = Attendance() a.status = user_status a.account_id = user_id a.event_id = activity_id session.add(a) session.flush() # Update the event's status regarding the new norm query = "update event set status='{0}' where id='{1}';".format( status, event_id,) op.execute(query) handled.append((user_id, activity_id,)) # Migrating activity to add duration and use datetimes op.add_column('activity', sa.Column('duration', sa.Integer, default=0)) op.alter_column( 'event', 'date', new_column_name='datetime', type_=sa.DateTime() ) query = "select id, conseiller_id from activity;" result = conn.execute(query) values = [] for activity_id, conseiller_id in result: values.append("(%s, %s)" % (activity_id, conseiller_id)) if values != []: query = "insert into activity_conseiller (`activity_id`, `account_id`) \ VALUES {0}".format(','.join(values)) op.execute(query) op.execute("alter table activity drop foreign key `activity_ibfk_2`;") op.drop_column('activity', 'conseiller_id') op.drop_table('activity_participant')
def upgrade(): # Ajout et modification de la structure de données existantes op.execute("alter table project modify archived BOOLEAN;") for name in ('ht', 'tva', 'ttc'): col = sa.Column(name, sa.Integer, default=0) op.add_column('task', col) for col in ( sa.Column("project_id", sa.Integer, sa.ForeignKey('project.id')), sa.Column("customer_id", sa.Integer, sa.ForeignKey('customer.id')), sa.Column("_number", sa.String(10)), sa.Column("sequence_number", sa.Integer), sa.Column("display_units", sa.Integer, default=0), sa.Column('expenses', sa.Integer, default=0), sa.Column('expenses_ht', sa.Integer, default=0), sa.Column('address', sa.Text, default=""), sa.Column('payment_conditions', sa.Text, default=""), sa.Column("official_number", sa.Integer, default=None), ): op.add_column("task", col) col = sa.Column("sortie_type_id", sa.Integer, sa.ForeignKey('type_sortie_option.id')) op.add_column("user_datas", col) op.execute("alter table user_datas modify parcours_num_hours float DEFAULT NULL") col = sa.Column("cgv", sa.Text, default="") op.add_column("company", col) # Migration des donnees vers la nouvelle structure from alembic.context import get_bind conn = get_bind() from autonomie.models.base import DBSESSION session = DBSESSION() # Expenses will be nodes make_expense_nodes(conn, session) from autonomie.models.task import ( Invoice, CancelInvoice, Estimation, ) # Migration des customer_id et project_id au niveau de la table Task index = 0 for type_ in "invoice", "cancelinvoice", "estimation": conditions = "paymentConditions" if type_ == "cancelinvoice": conditions = "reimbursementConditions" request = "select id, customer_id, project_id, number, \ sequenceNumber, displayedUnits, expenses, expenses_ht, address, %s \ from %s;" % (conditions, type_) result = conn.execute(request) for index, (id, c_id, p_id, number, seq_number, display, expenses, expenses_ht, address, conditions) in enumerate(result): request = sa.text(u"update task set \ project_id=:p_id, \ customer_id=:c_id, \ _number=:number, \ sequence_number=:seq_number, \ display_units=:display, \ expenses=:expenses, \ expenses_ht=:expenses_ht, \ address=:address, \ payment_conditions=:conditions \ where id=:id;" ) conn.execute( request, p_id=p_id, c_id=c_id, number=number, seq_number=seq_number, display=display, expenses=expenses, expenses_ht=expenses_ht, address=address, conditions=conditions, id=id, ) if index % 50 == 0: session.flush() for type_ in ('invoice', 'cancelinvoice'): request = "select id, officialNumber from %s" % (type_,) result = conn.execute(request) for index, (id, official_number) in enumerate(result): request = sa.text(u"update task set \ official_number=:official_number \ where id=:id;" ) conn.execute( request, official_number=official_number, id=id, ) if index % 50 == 0: session.flush() for factory in (Invoice, CancelInvoice, Estimation,): for document in factory.query().options(undefer_group('edit')): document.ttc = document.total() document.ht = document.total_ht() document.tva = document.tva_amount() session.merge(document) index += 1 if index % 50 == 0: session.flush() # Drop old constraints for table in ('estimation', 'invoice', 'cancelinvoice'): for num in [2,3,4]: key = "%s_ibfk_%s" % (table, num,) cmd = "ALTER TABLE %s DROP FOREIGN KEY %s;" % (table, key) try: print(cmd) conn.execute(cmd) except: print("Error while droping a foreignkey : %s %s" % (table, key)) for column in ('customer_id', 'project_id', 'number', \ 'sequenceNumber', 'displayedUnits', 'expenses', \ 'expenses_ht', 'address'): op.drop_column(table, column) op.drop_column('cancelinvoice', 'reimbursementConditions') op.drop_column('estimation', 'paymentConditions') op.drop_column('invoice', 'paymentConditions') for table in ('invoice', 'cancelinvoice'): op.drop_column(table, 'officialNumber')
def upgrade(): ### commands auto generated by Alembic - please adjust! ## bind = context.get_bind() if bind.dialect.name == 'postgresql': create_unique_constraint('users_email_key', 'users', ['email'])
def downgrade(): ### commands auto generated by Alembic - please adjust! ### bind = context.get_bind() if bind.dialect.name == 'postgresql': drop_constraint('users_email_key', 'users')