def upgrade(): op.add_column('payment', sa.Column('created_at', sa.DateTime())) op.add_column('payment', sa.Column('updated_at', sa.DateTime())) op.add_column('payment', sa.Column('bank_id', sa.Integer(), nullable=True)) op.add_column('payment', sa.Column('exported', sa.Boolean(), default=False)) op.add_column( 'payment', sa.Column( 'tva_id', sa.Integer(), sa.ForeignKey('tva.id'), nullable=True, )) op.add_column('payment', sa.Column('remittance_amount', sa.Integer(), nullable=True)) op.add_column('tva', sa.Column('compte_a_payer', sa.String(125), default='')) from autonomie.models.base import DBSESSION from autonomie.models.task import Payment session = DBSESSION() for payment in Payment.query(): payment.remittance_amount = payment.amount payment.exported = True payment.created_at = payment.updated_at = payment.date session.merge(payment)
def query(self): """ Return the main query for our list view """ logger.debug("Queryiing") query = DBSESSION().query(distinct(User.id), User) return query.outerjoin(User.companies)
def query(self): """ Return the main query for our list view """ log.debug("Queryiing") query = DBSESSION().query(distinct(User.id), User) return query.outerjoin(User.companies)
def connection(request, settings): """ sets up a SQLAlchemy engine and returns a connection to the database. :param settings: the settings of the test (given by the testing fixture) :returns: a sqlalchemy connection object """ # the following setup is based on `kotti.resources.initialize_sql`, # except that it explicitly binds the session to a specific connection # enabling us to use savepoints independent from the orm, thus allowing # to `rollback` after using `transaction.commit`... initialize_test_database(settings) from autonomie.models.base import DBSESSION, DBBASE engine = engine_from_config(settings, prefix='sqlalchemy.') _connection = engine.connect() DBSESSION.registry.clear() DBSESSION.configure(bind=_connection) DBBASE.metadata.bind = engine def drop_db(): """ drop the test database """ if __current_test_ini_file().endswith('travis.ini'): return options = get_test_options_from_settings(settings) cmd = "echo \"echo 'drop database {db};' | {mysql_cmd}\" | at now" launch_cmd(options, cmd) request.addfinalizer(drop_db) return _connection
def upgrade(): op.add_column('payment', sa.Column('created_at', sa.DateTime())) op.add_column('payment', sa.Column('updated_at', sa.DateTime())) op.add_column('payment', sa.Column('bank_id', sa.Integer(), nullable=True)) op.add_column('payment', sa.Column('exported', sa.Boolean(), default=False)) op.add_column('payment', sa.Column( 'tva_id', sa.Integer(), sa.ForeignKey('tva.id'), nullable=True, )) op.add_column( 'payment', sa.Column('remittance_amount', sa.Integer(), nullable=True) ) op.add_column( 'tva', sa.Column('compte_a_payer', sa.String(125), default='') ) from autonomie.models.base import DBSESSION from autonomie.models.task import Payment session = DBSESSION() for payment in Payment.query(): payment.remittance_amount = payment.amount payment.exported = True payment.created_at = payment.updated_at = payment.date session.merge(payment)
def upgrade(): import logging logger = logging.getLogger('alembic.here') op.add_column( "user_datas", sa.Column( 'statut_social_status_today_id', sa.Integer, sa.ForeignKey('social_status_option.id'), )) op.add_column( "user_datas", sa.Column( "parcours_employee_quality_id", sa.Integer, sa.ForeignKey('employee_quality_option.id'), )) op.add_column( "user_datas", sa.Column("situation_antenne_id", sa.Integer, sa.ForeignKey('antenne_option.id'))) op.add_column("task", sa.Column( "internal_number", sa.String(40), )) op.add_column("task", sa.Column("company_index", sa.Integer)) op.execute("alter table task CHANGE sequence_number project_index int(11)") op.add_column( "task", sa.Column( "company_id", sa.Integer, sa.ForeignKey('company.id'), )) from autonomie.models.base import ( DBSESSION, ) session = DBSESSION() add_company_id(session, logger) add_company_index(session, logger) add_internal_number(session, logger) logger.warn("Adding Contract Histories") from autonomie.models.user import UserDatas, ContractHistory for id_, last_avenant in UserDatas.query('id', 'parcours_last_avenant'): if last_avenant: session.add( ContractHistory(userdatas_id=id_, date=last_avenant, number=-1)) op.add_column("date_convention_cape_datas", sa.Column('end_date', sa.Date(), nullable=True)) op.execute("alter table customer MODIFY code VARCHAR(4);") op.execute("alter table project MODIFY code VARCHAR(4);") create_custom_treasury_modules(session, logger) from zope.sqlalchemy import mark_changed mark_changed(session)
def query(self): query = DBSESSION().query(Task) query = query.with_polymorphic([Invoice, CancelInvoice]) query = query.outerjoin(Invoice.payments) query = query.outerjoin(Task.customer) query = query.options(contains_eager(Invoice.payments).load_only(Payment.id, Payment.date, Payment.mode)) query = query.options(contains_eager(Task.customer).load_only(Customer.name, Customer.code, Customer.id)) return query
def get_cancelinvoices(cls, instance, valid=False): from autonomie.models.task import CancelInvoice query = DBSESSION().query(CancelInvoice) query = query.filter(CancelInvoice.company_id == instance.id) if valid: query = query.filter( CancelInvoice.CAEStatus.in_(CancelInvoice.valid_states)) return query
def query(self): company = self.request.context # We can't have projects without having customers if not company.customers: redirect_to_customerslist(self.request, company) main_query = DBSESSION().query(distinct(Project.id), Project) main_query = main_query.outerjoin(Project.customers) return main_query.filter(Project.company_id == company.id)
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 get_estimations(cls, instance, valid=False): from autonomie.models.task import Estimation query = DBSESSION().query(Estimation) query = query.filter(Estimation.company_id == instance.id) if valid: query = query.filter( Estimation.CAEStatus.in_(Estimation.valid_states)) return query
def get_customers(cls, instance, year): from autonomie.models.task import Invoice from autonomie.models.customer import Customer query = DBSESSION().query(Customer) query = query.filter(Customer.company_id == instance.id) query = query.filter( Customer.invoices.any( and_(Invoice.CAEStatus.in_(Invoice.valid_states), Invoice.financial_year == year))) return query
def get_recording_job(self): """ Initialize a job for importation recording """ # We initialize a job record in the database job = CsvImportJob() job.set_owner(self.request.user.login) DBSESSION().add(job) DBSESSION().flush() return job
def initialize_sql(engine): """ Initialize the database engine """ DBSESSION.configure(bind=engine) DBBASE.metadata.bind = engine if not engine.table_names(): fetch_head() DBBASE.metadata.create_all(engine) return DBSESSION
def query(cls, keys=None, active=True): """ Return a query """ if keys: query = DBSESSION().query(*keys) else: query = super(Company, cls).query() if active: query = query.filter(cls.active == "Y") return query.order_by(cls.name)
def _find_one(cls, name_or_id): """ Used as a creator for the initialization proxy """ with DBSESSION.no_autoflush: res = DBSESSION.query(cls).get(name_or_id) if res is None: # We try with the id res = DBSESSION.query(cls).filter(cls.name==name_or_id).one() return res
def expenseyears(): """ return distinct expense years available in the database """ query = DBSESSION().query(distinct(ExpenseSheet.year)) query = query.order_by(ExpenseSheet.year) years = [year[0] for year in query] current = datetime.date.today().year if current not in years: years.append(current) return years
def query(self): query = DBSESSION().query(Task) query = query.with_polymorphic([Invoice, CancelInvoice]) from sqlalchemy.orm import joinedload query = query.options( joinedload(Invoice.payments).load_only(Payment.id, Payment.date, Payment.mode)) query = query.options( joinedload(Task.customer).load_only(Customer.name, Customer.code, Customer.id)) return query
def get_tasks(cls, instance, type_str=None): from autonomie.models.task import Task query = DBSESSION().query(Task) query = query.filter_by(project_id=instance.id) if type_str is not None: query = query.filter(Task.type_ == type_str) else: query = query.filter(Task.type_.in_(("invoice", "cancelinvoice", "estimation"))) return query
def taskyears(): """ return the distinct financial years available in the database """ query = DBSESSION().query(distinct(Invoice.financial_year)) query = query.order_by(Invoice.financial_year) years = [year[0] for year in query] current = datetime.date.today().year if current not in years: years.append(current) return years
def get_tasks(cls, instance, type_str=None): from autonomie.models.task import Task query = DBSESSION().query(Task) query = query.filter_by(customer_id=instance.id) if type_str is not None: query = query.filter(Task.type_ == type_str) else: query = query.filter( Task.type_.in_(('invoice', 'cancelinvoice', 'estimation'))) return query
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 set(cls, key, appstruct): """ Set a file for the given key, if the key isn't field yet, add a new instance """ instance = cls.get(key) if instance is None: instance = cls(key=key) for attr_name, attr_value in appstruct.items(): setattr(instance, attr_name, attr_value) if instance.id is not None: DBSESSION().merge(instance) else: DBSESSION().add(instance)
def upgrade(): from autonomie.models.task import ( TaskLine, TaskLineGroup, Task, Estimation, CancelInvoice, Invoice, ) from autonomie.models.base import ( DBSESSION, ) session = DBSESSION() index = 0 query = Task.query() query = query.with_polymorphic([Invoice, CancelInvoice, Estimation]) query = query.filter( Task.type_.in_(['invoice', 'estimation', 'cancelinvoice']) ) for task in query: group = TaskLineGroup(task_id=task.id, order=0) for line in task.lines: tline = TaskLine( group=group, order=line.rowIndex, description=line.description, cost=line.cost, tva=line.tva, quantity=line.quantity, ) if hasattr(line, 'product_id'): tline.product_id = line.product_id session.add(tline) if index % 100 == 0: session.flush() op.alter_column( table_name='estimation_payment', column_name='rowIndex', new_column_name='order', type_=sa.Integer, )
def get_associated_tasks_by_type(self, type_str): """ Return the tasks of type type_str associated to the current object """ from autonomie.models.task import Task return DBSESSION().query(Task).filter_by(project_id=self.id, type_=type_str).all()
def set(cls, key, value): instance = cls.get(key) if instance is None: instance = cls(name=key) instance.value = value DBSESSION().merge(instance)
def get_next_index(cls, project, factory): query = DBSESSION.query(func.max(factory.project_index)) query = query.filter(factory.project_id == project.id) max_num = query.first()[0] if max_num is None: max_num = 0 return max_num + 1
def get_next_official_number(year=None): """ Return the next available official number :param int year: The year we'd like to query a number for """ next_ = 1 if year is None: year = datetime.date.today().year query = DBSESSION().query(func.max(Task.official_number)) query = query.filter(extract('year', Task.date) == year) last = query.first()[0] if last: next_ = last + 1 return next_
def get_next_index(cls, company, factory): query = DBSESSION.query(func.max(factory.company_index)) query = query.filter(factory.company_id == company.id) max_num = query.first()[0] if max_num is None: max_num = 0 return max_num + 1
def initialize_sql(engine): """ Initialize the database engine """ DBSESSION.configure(bind=engine) DBBASE.metadata.bind = engine if not engine.table_names(): fetch_head() print("Setting the metadatas") DBBASE.metadata.create_all(engine) from transaction import commit commit() print("Populating the config") populate_config(DBSESSION()) return DBSESSION
def get_official_number(cls): """ Return the greatest official_number actually used in the ManualInvoice table """ current_year = datetime.date.today().year return DBSESSION().query(func.max(CancelInvoice.official_number)).filter( func.year(CancelInvoice.taskDate) == current_year)
def record_preference(request, name, association_dict): """ Record a field association in the request config """ config_obj = get_preferences_obj() associations = load_preferences(config_obj) associations[name] = association_dict if config_obj.value is None: # It's a new one config_obj.value = json.dumps(associations) DBSESSION().add(config_obj) else: # We edit it config_obj.value = json.dumps(associations) DBSESSION().merge(config_obj) return associations
def _get_next_official_number(year=None): """ Return the next available official number :param int year: The year we'd like to query a number for """ next_ = 1 if year is None: year = datetime.date.today().year query = DBSESSION().query(func.max(Task.official_number)) query = query.filter(extract("year", Task.date) == year) last = query.first()[0] if last: next_ = last + 1 return next_
def upgrade(): from autonomie.models import user from autonomie.models.base import DBSESSION db = DBSESSION() for u in db.query(user.User)\ .filter(user.User.userdatas==None)\ .filter(user.User.primary_group==3): situation = "sortie" if u.email: userdata = user.UserDatas( situation_situation=situation, coordonnees_firstname=u.firstname, coordonnees_lastname=u.lastname, coordonnees_email1=u.email, coordonnees_civilite=u'?', ) userdata.user_id = u.id for company in u.companies: companydata = user.CompanyDatas( title=company.goal, name=company.name, ) userdata.activity_companydatas.append(companydata) db.add(userdata) db.flush()
def record_failure(job_model, job_id, task_id, e): """ Record a job's failure """ transaction.begin() # We fetch the job again since we're in a new transaction from autonomie.models.base import DBSESSION job = DBSESSION().query(job_model).filter( job_model.id==job_id ).first() job.jobid = task_id job.status = "failed" # We append an error if hasattr(job, 'error_messages'): if job.error_messages is None: job.error_messages = [] job.error_messages.append(u"%s" % e) transaction.commit()
def filter_notfilled(self, query, appstruct): """ Filter the workshops for which timeslots have not been filled """ notfilled = appstruct.get('notfilled') if notfilled: logger.debug(u"Filtering the workshop that where not filled") attendance_query = DBSESSION().query(distinct(Attendance.event_id)) attendance_query = attendance_query.filter( Attendance.status != 'registered') timeslot_ids = [item[0] for item in attendance_query] query = query.filter( not_( models.Workshop.timeslots.any( models.Timeslot.id.in_(timeslot_ids)))) return query
def add_custom_datas_headers(writer, query): """ Add custom headers that are not added through automation """ # Compte analytique query = DBSESSION().query( func.count(COMPANY_EMPLOYEE.c.company_id).label('nb')) query = query.group_by(COMPANY_EMPLOYEE.c.account_id) code_compta_count = query.order_by(desc("nb")).first() if code_compta_count: code_compta_count = code_compta_count[0] for index in range(0, code_compta_count): new_header = { 'label': "Compte_analytique {0}".format(index + 1), 'name': "code_compta_{0}".format(index + 1), } writer.add_extra_header(new_header) return writer
def add_custom_datas_headers(writer, query): """ Add custom headers that are not added through automation """ # Compte analytique query = DBSESSION().query( func.count(COMPANY_EMPLOYEE.c.company_id).label('nb') ) query = query.group_by(COMPANY_EMPLOYEE.c.account_id) code_compta_count = query.order_by(desc("nb")).first() if code_compta_count: code_compta_count = code_compta_count[0] for index in range(0, code_compta_count): new_header = { 'label': "Compte_analytique {0}".format(index + 1), 'name': "code_compta_{0}".format(index + 1), } writer.add_extra_header(new_header) return writer
def dbsession(config, content, connection, request): """ returns a db session object and sets up a db transaction savepoint, which will be rolled back after the test. :returns: a SQLA session """ from transaction import abort trans = connection.begin() # begin a non-orm transaction request.addfinalizer(trans.rollback) request.addfinalizer(abort) from autonomie.models.base import DBSESSION return DBSESSION()
def get_official_number(cls): """ Return the next official_number available in the Invoice's table Take the max of official Number when taskDate startswith the current year taskdate is a string (YYYYMMDD) """ current_year = datetime.date.today().year return DBSESSION().query(func.max(Invoice.official_number)).filter( Invoice.taskDate.between(current_year * 10000, (current_year + 1) * 10000 ))
def query(self): """ Returns the main query used to find objects E.G: query = DBSESSION().query(distinct(UserDatas.id), UserDatas) query = query.filter(UserDatas.name.startswith('test')) query = query.outerjoin(UserDatas.conseiller) query = query.filter(User.lastname=='A manager') """ query = DBSESSION().query(distinct(self.model.id), self.model) # Pour chaque critère sur lesquels on va ajouter des filtres, on a # besoin d'être sûr que la classe concernée est bien requêtée, il faut # donc ajouter des outerjoins pour chaque classe liée. # NOTE: on ne gère pas les alias (les joins sur deux tables identiques # pour deux relations différentes) already_added = [] for criterion in self.criteria: # On génère le filtre filter_ = criterion.gen_filter() # si il n'y a pas de filtres ... if filter_ is not None: # On récupère l'objet lié join_class = criterion.get_join_class() # Si il y a un outerjoin à faire (le critère se fait sur un # attribut de l'objet lié) if join_class is not None: # on ne va pas outerjoiné deux fois la classe pour le même # critère if criterion.key not in already_added: query = query.outerjoin(join_class) already_added.append(criterion.key) query = query.filter(filter_) return 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 filter_notfilled(self, query, appstruct): """ Filter the workshops for which timeslots have not been filled """ notfilled = appstruct.get('notfilled') if notfilled: logger.debug(u"Filtering the workshop that where not filled") attendance_query = DBSESSION().query(distinct(Attendance.event_id)) attendance_query = attendance_query.filter( Attendance.status != 'registered' ) timeslot_ids = [item[0] for item in attendance_query] query = query.filter( not_( models.Workshop.timeslots.any( models.Timeslot.id.in_(timeslot_ids) ) ) ) return query
def upgrade(): from autonomie.models.task import ( TaskLine, TaskLineGroup, Task, Estimation, CancelInvoice, Invoice, ) from autonomie.models.base import ( DBSESSION, ) session = DBSESSION() index = 0 query = Task.query() query = query.with_polymorphic([Invoice, CancelInvoice, Estimation]) query = query.filter( Task.type_.in_(['invoice', 'estimation', 'cancelinvoice']) ) for task in query: try: task_lines = task.default_line_group.lines except: continue for index, line in enumerate(task.lines): try: task_line = task_lines[index] task_line.unity = line.unity session.merge(task_line) except: pass index += 1 if index % 100 == 0: session.flush()
def get_job(celery_request, job_model, job_id): """ Return the current executed job (in autonomie's sens) :param obj job_model: The Job model :param obj celery_request: The current celery request object :param int job_id: The id of the job :returns: The current job :raises sqlalchemy.orm.exc.NoResultFound: If the job could not be found """ from autonomie.models.base import DBSESSION # We sleep a bit to wait for the current request to be finished : since we # use a transaction manager, the delay call launched in a view is done # before the job element is commited to the bdd (at the end of the request) # if we query for the job too early, the session will not be able to # retrieve the newly created job time.sleep(10) job = DBSESSION().query(job_model).filter( job_model.id==job_id ).one() job.jobid = celery_request.id return job
def query(self): """ Return the main query used to find objects e.g: query = DBSESSION().query(distinct(UserDatas.id), UserDatas) query = query.filter(UserDatas.name.startswith('test')) query = query.outerjoin(UserDatas.conseiller) query = query.filter(User.lastname=='A manager') query = query.filter( UserDatas.id.in_( [list of ids retrieved from independant queries] ) ) """ self.already_joined = [] if self.root: main_query = DBSESSION().query(distinct(self.model.id), self.model) else: main_query = DBSESSION().query(distinct(self.model.id)) # Pour chaque critère sur lesquels on va ajouter des filtres, on a # besoin d'être sûr que la classe concernée est bien requêtée, il faut # donc ajouter des outerjoins pour chaque classe liée. # NOTE: on ne gère pas les alias (les joins sur deux tables identiques # pour deux relations différentes) for criterion in self.query_helpers: # On génère le filtre filter_ = criterion.gen_filter() having = criterion.gen_having_clause() # si il y a un filtre ... if filter_ is not None: main_query = self.join(main_query, criterion) main_query = main_query.filter(filter_) elif having is not None: main_query = self.join(main_query, criterion) main_query = main_query.group_by(self.model.id) main_query = main_query.having(having) if self.query_factories: ids = list(self._get_ids_from_factories()) main_query = main_query.filter(self.model.id.in_(ids)) return main_query
def upgrade(): op.execute(u"Alter table tva modify name VARCHAR(15)") op.execute(u"Alter table tva modify active tinyint(1)") op.add_column('tva', sa.Column('mention', sa.Text(), default='')) from autonomie.models.tva import Tva from autonomie.models.base import DBSESSION session = DBSESSION() for tva in session.query(Tva): if tva.value <= 0: tva.mention = u"TVA non applicable selon l'article 259b du CGI." session.merge(tva) else: tva.mention = u"TVA {0} %".format(tva.value / 100.0) session.merge(tva)
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 upgrade(): disable_listeners() op.add_column('task', sa.Column('date', sa.Date())) from autonomie.models.task import Task from autonomie.models.base import DBSESSION session = DBSESSION() for task in Task.query().filter(Task.type_!='manualinvoice'): task.date = task.taskDate session.merge(task) session.flush() op.execute("alter table groups modify label VARCHAR(255);") op.execute("alter table payment modify remittance_amount VARCHAR(255);") from autonomie.models.user import User, Group for group_id, group_name, group_label in GROUPS: group = session.query(Group).filter(Group.name==group_name).first() if group is None: group = Group(name=group_name, label=group_label) session.add(group) session.flush() users = session.query(User).filter(User.primary_group==group_id) for user in users: user._groups.append(group) session.merge(user) label = u"Peut saisir/modifier/supprimer les paiements de ses factures" group_name = "payment_admin" group = Group.query().filter(Group.name==group_name).first() if group is not None: group.label = label session.merge(group)
def upgrade(): import logging logger = logging.getLogger('alembic.here') op.add_column( "user_datas", sa.Column( 'statut_social_status_today_id', sa.Integer, sa.ForeignKey('social_status_option.id'), ) ) op.add_column( "user_datas", sa.Column( "parcours_employee_quality_id", sa.Integer, sa.ForeignKey('employee_quality_option.id'), ) ) op.add_column( "user_datas", sa.Column( "situation_antenne_id", sa.Integer, sa.ForeignKey('antenne_option.id') ) ) op.add_column( "task", sa.Column( "internal_number", sa.String(40), ) ) op.add_column( "task", sa.Column("company_index", sa.Integer) ) op.execute("alter table task CHANGE sequence_number project_index int(11)") op.add_column( "task", sa.Column( "company_id", sa.Integer, sa.ForeignKey('company.id'), ) ) from autonomie.models.base import ( DBSESSION, ) session = DBSESSION() add_company_id(session, logger) add_company_index(session, logger) add_internal_number(session, logger) logger.warn("Adding Contract Histories") from autonomie.models.user import UserDatas, ContractHistory for id_, last_avenant in UserDatas.query('id', 'parcours_last_avenant'): if last_avenant: session.add( ContractHistory( userdatas_id=id_, date=last_avenant, number=-1 ) ) op.add_column( "date_convention_cape_datas", sa.Column('end_date', sa.Date(), nullable=True) ) op.execute("alter table customer MODIFY code VARCHAR(4);") op.execute("alter table project MODIFY code VARCHAR(4);") create_custom_treasury_modules(session, logger) from zope.sqlalchemy import mark_changed mark_changed(session)
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')