def add_new_columns(self): """ Adds columns present in df but not in the SQL table for given instance of PandasSpecialEngine. Notes ----- Sadly, it seems that we cannot create JSON columns. """ # create deepcopies of the column because we are going to unbound # them for the table model (otherwise alembic would think we add # a column that already exists in the database) cols_to_add = [deepcopy(col) for col in self.table.columns if col.name not in self.get_db_columns_names()] # check columns are not index levels if any((c.name in self.df.index.names for c in cols_to_add)): raise ValueError(('Cannot add any column that is part of the df index!\n' "You'll have to update your table primary key or change your " "df index")) with self.engine.connect() as con: ctx = MigrationContext.configure(con) op = Operations(ctx) for col in cols_to_add: col.table = None # Important! unbound column from table op.add_column(self.table.name, col, schema=self.schema) log(f"Added column {col} (type: {col.type}) in table {self.table.name} " f'(schema="{self.schema}")')
def upgrade(engine): session_maker = sessionmaker(bind=engine) session = session_maker() r = (session.query(model.ByteHubVersion).order_by( model.ByteHubVersion.timestamp.desc()).first()) if not r: current_version = "0" else: current_version = r.version if parse(current_version) >= parse(version.__version__): # Up-to-date: nothing to do session.close() return if parse(current_version) < parse(version.__version__): print(f"Upgrading ByteHub database schema...") with engine.connect() as connection: context = MigrationContext.configure(connection) op = Operations(context) try: op.add_column("namespace", Column("backend", String(128))) except OperationalError: pass # Add version number obj = model.ByteHubVersion() obj.version = version.__version__ session.add(obj) session.commit() session.close()
def add_columns(engine, raw_diffs, table_names): with engine.begin() as conn: ctx = get_migration_context(conn, table_names) op = Operations(ctx) columns = _get_columns_to_add(raw_diffs, table_names) for col in columns: table_name = col.table.name # the column has a reference to a table definition that already # has the column defined, so remove that and add the column col.table = None op.add_column(table_name, col)
def add_columns(engine, raw_diffs, table_names): with engine.begin() as conn: ctx = get_migration_context(conn, table_names) op = Operations(ctx) columns = _get_columns_to_add(raw_diffs, table_names) for col in columns: table_name = col.table.name # the column has a reference to a table definition that already # has the column defined, so remove that and add the column col.table = None op.add_column(table_name, col)
def add_columns(engine, diffs): with engine.begin() as conn: ctx = get_migration_context(conn) op = Operations(ctx) col_diffs = _filter_diffs(diffs, [DiffTypes.ADD_NULLABLE_COLUMN]) for diff in col_diffs: col = diff.column table_name = col.table.name # the column has a reference to a table definition that already # has the column defined, so remove that and add the column col.table = None op.add_column(table_name, col) return col_diffs
def add_columns(engine, raw_diffs, table_names): changes = defaultdict(list) with engine.begin() as conn: ctx = get_migration_context(conn, table_names) op = Operations(ctx) columns = _get_columns_to_add(raw_diffs, table_names) for col in columns: table_name = col.table.name # the column has a reference to a table definition that already # has the column defined, so remove that and add the column col.table = None changes[table_name].append({ 'type': DiffTypes.ADD_COLUMN, 'item_name': col.name }) op.add_column(table_name, col) return dict(changes)
def add_columns(engine, raw_diffs, table_names): changes = defaultdict(list) with engine.begin() as conn: ctx = get_migration_context(conn, table_names) op = Operations(ctx) columns = _get_columns_to_add(raw_diffs, table_names) for col in columns: table_name = col.table.name # the column has a reference to a table definition that already # has the column defined, so remove that and add the column col.table = None changes[table_name].append({ 'type': DiffTypes.ADD_COLUMN, 'value': col.name }) op.add_column(table_name, col) return dict(changes)
def add_measurement_columns(session): """Inspects all SampleMeasurements finding distinct values for measurement_types, then creates new columns in the ExperimentMeasurement table using an Alembic migration context.""" # TODO: Consider index on SampleMeasurement.measurement_types. ExperimentMeasurement = get_ExperimentMeasurement() measurement_types = session.query( SampleMeasurement.measurement_type).distinct().all() measurement_types = sorted([_ for (_, ) in measurement_types]) cols = set([f"measurement_{mt}" for mt in measurement_types]) new_cols = cols - set(ExperimentMeasurement.__table__.columns.keys()) if new_cols: ctx = MigrationContext.configure(session.connection()) op = Operations(ctx) for new_col in new_cols: print(f"creating new_col: {new_col}") op.add_column("experiment_measurements", Column(new_col, DECIMAL(16, 6)))
def add_measurement_columns(start: int, end: int, session): assert start <= end ExperimentMeasurement = get_ExperimentMeasurement() # Get all the distinct measurement types in the batch that this function # call is responsible for extracting measurement_types = sorted([ _ for (_, ) in session.query(SampleMeasurement.measurement_type).filter( SampleMeasurement.sample_id >= start, SampleMeasurement.sample_id <= end).distinct().all( ) # Don't transmit any more than you have to. ]) cols = set([f"measurement_{mt}" for mt in measurement_types]) new_cols = cols - set(ExperimentMeasurement.__table__.columns.keys()) if new_cols: ctx = MigrationContext.configure(session.connection()) op = Operations(ctx) for new_col in new_cols: print(f"creating new_col: {new_col}") op.add_column("experiment_measurements", Column(new_col, DECIMAL(16, 6)))
def create_column(engine, table_oid, column_data): column_type = column_data[TYPE] column_nullable = column_data.get(NULLABLE, True) supported_types = alteration.get_supported_alter_column_types( engine, friendly_names=False, ) sa_type = supported_types.get(column_type) if sa_type is None: logger.warning("Requested type not supported. falling back to VARCHAR") sa_type = supported_types["VARCHAR"] table = tables.reflect_table_from_oid(table_oid, engine) column = MathesarColumn( column_data[NAME], sa_type, nullable=column_nullable, ) with engine.begin() as conn: ctx = MigrationContext.configure(conn) op = Operations(ctx) op.add_column(table.name, column, schema=table.schema) return tables.reflect_table_from_oid(table_oid, engine).columns[column_data[NAME]]
def _schema_init_and_update(self): """ This method tries to create the database table and update the schema. :return: None """ try: # Try to create the database metadata.create_all(self.engine) except OperationalError as exx: # pragma: no cover log.info("{0!r}".format(exx)) # Schema update conn = self.engine.connect() ctx = MigrationContext.configure(conn) op = Operations(ctx) try: # Try to add resolver column op.add_column( TABLE_NAME, Column('resolver', String(length=column_length.get("resolver")))) except Exception as exx: # pragma: no cover log.info("{0!r}".format(exx))
def create_missing_database_entities(Model, engine): m = Model.metadata current_info = get_current_database_info(engine) print(current_info) conn = engine.connect() ctx = MigrationContext.configure(conn) op = Operations(ctx) print "metadata", m for table_name in m.tables: table = m.tables[table_name] if current_info.has_key(table_name): for col in table.columns: print "col", col if not col.name in current_info[table_name]: print " IN TABLE: %s CREATING COLUMN: %s"%(table_name, col.name) op.add_column(table_name, mimic_column(col)) print " ... done" else: args = [table_name] + map(mimic_column, list(table.columns)) print "CREATING TABLE: " + repr(args) op.create_table(*args)
import os from dotenv import load_dotenv from sqlalchemy import create_engine, orm, MetaData, Table, Column, String from alembic.migration import MigrationContext from alembic.operations import Operations load_dotenv() PG_URL = os.environ.get('PG_URL') PG_TABLE = os.environ.get('PG_TABLE') engine = create_engine(PG_URL) context = MigrationContext.configure(engine.connect()) operation = Operations(context) operation.add_column(PG_TABLE, Column('contact_phone_normalized', String(100))) metadata = MetaData() metadata.reflect(bind=engine) keys = metadata.tables[PG_TABLE].columns.keys() print(f'All columns of the table "{PG_TABLE}": {keys}')
from alembic.migration import MigrationContext from alembic.operations import Operations from sqlalchemy import Column, Float, UnicodeText from sqlalchemy import create_engine if __name__ == '__main__': engine = create_engine('postgresql://postgres@localhost:5432/tetres') conn = engine.connect() ctx = MigrationContext.configure(conn) op = Operations(ctx) print("Adding column vht...") for i in range(2010, 2021): try: vht = Column("vht", Float, nullable=True) op.add_column("tt_{}".format(i), vht) print("Successfully added vht column!") except Exception as e: print("Failed adding vht column! Error: {}".format(e)) try: dvh = Column("dvh", Float, nullable=True) op.add_column("tt_{}".format(i), dvh) print("Successfully added dvh column!") except Exception as e: print("Failed adding dvh column! Error: {}".format(e)) try: lvmt = Column("lvmt", Float, nullable=True) op.add_column("tt_{}".format(i), lvmt) print("Successfully added lvmt column!") except Exception as e: print("Failed adding lvmt column! Error: {}".format(e)) try:
def update_member(self, id, data): member=self.get_member(id) if member is None: return None # NOTE: BaseContext's init method sets its base to the base # struct contained in the request, so we need to reset it here # to the base struct that is actually in the database - DCarv # NOTE: Remove base struct from cache! By John Doe model.BASES.bases.pop(member.name) # NOTE: Set old base struct as active! By John Doe self.set_base(member.struct) # NOTE: Check for base content changes! By John Doe old_base=json2base(member.struct) new_base=json2base(data['struct']) # NOTE: List all fields that should be deleted! By John Doe del_cols=[] for old_col_name, old_col in old_base.content.__allstructs__.items(): if old_col_name not in new_base.content.__allsnames__: del_cols.append(old_col) # NOTE: If any field will be deleted, delete it from all documents in # the base! By John Doe if len(del_cols) > 0: # NOTE: Create a fake request for DocumentCustomView and # DocumentContext! By John Doe url="/%s/doc&$$={\"limit\":null}" % new_base.metadata.name for col in del_cols: params={ 'path': "[{\"path\":\"%s\",\"fn\":null,\"mode\":" +\ "\"delete\",\"args\":[]}]" % ("/".join(col.path)) } request=DummyRequest(path=url, params=params) request.method='PUT' request.matchdict={"base": new_base.metadata.name} doc_view=DocumentCustomView( DocumentContextFactory(request), request ) doc_view.update_collection() # NOTE: Check for relation field changes (to ALTER table if needed)! # By John Doe old_doc_table=get_doc_table(old_base.metadata.name, config.METADATA, **old_base.relational_fields) new_doc_table=get_doc_table(new_base.metadata.name, config.METADATA, **new_base.relational_fields) # NOTE: List relational fields that should be deleted! By John Doe del_cols=[] for old_col in old_doc_table.columns: if old_col.name not in new_doc_table.columns: del_cols.append(old_col) # NOTE: List relational fields that should be added! By John Doe new_cols=[] for new_col in new_doc_table.columns: if new_col.name not in old_doc_table.columns: # NOTE: Get liblightbase.lbbase.fields object! By John Doe field=new_base.relational_fields[new_col.name] custom_col=get_custom_column(field) new_cols.append(custom_col) # NOTE: Create alembic connection and operation object! By John Doe db_conn=config.ENGINE.connect() alembic_ctx=MigrationContext.configure(db_conn) alembic_op=Operations(alembic_ctx) # NOTE: Drop columns! By John Doe for col in del_cols: alembic_op.drop_column(new_doc_table.name, col.name) # TODO: New_col cannot be required! By John Doe # NOTE: Add columns! By John Doe for col in new_cols: alembic_op.add_column(new_doc_table.name, col) # TODO: Alter columns? By John Doe db_conn.close() # NOTE: Check for base name change! By John Doe if member.name != data['name']: old_name='lb_doc_%s' %(member.name) new_name='lb_doc_%s' %(data['name']) self.session.execute('ALTER TABLE %s RENAME TO %s' %( old_name, new_name ) ) old_name='lb_file_%s' %(member.name) new_name='lb_file_%s' %(data['name']) self.session.execute('ALTER TABLE %s RENAME TO %s' %( old_name, new_name ) ) old_name='lb_doc_%s_id_doc_seq' %(member.name) new_name='lb_doc_%s_id_doc_seq' %(data['name']) self.session.execute('ALTER SEQUENCE %s RENAME TO %s' %( old_name, new_name ) ) # NOTE: This will add any new fields to the base struct! By John Doe for name in data: setattr(member, name, data[name]) # NOTE: Now commits and closes session in the view instead of here # flush() pushes operations to DB's buffer - DCarv self.session.flush() # NOTE: Por alguma razão o objeto "session" estando com # "autocommit=True" não "commita" no "close" e por isso executamos um # "commit" aqui! "autocommit=True" não comita mais de uma operação em # sequência? By Questor # session_factory: sessionmaker( # class_='Session', # autoflush=True, # bind=Engine(postgresql://lbu:***@127.0.0.1/lb), # autocommit=True, # expire_on_commit=True # ) # registry: <sqlalchemy.\ # util.\ # _collections.\ # ThreadLocalRegistry object at 0x4143f90> # ! By Questor self.session.commit() model.HISTORY.create_member(**{ 'id_base': member.id_base, 'author': 'Author', 'date': datetime.datetime.now().strftime('%d/%m/%Y %H:%M:%S'), 'name': member.name, 'structure': utils.json2object(member.struct), 'status': 'UPDATED' }) self.lbirestart() # NOTE: Remove base struct from cache! By John Doe model.BASES.bases.pop(member.name) return member
def submitRunlistData(): """ Create/edit experimental run list layout. Insert/edit experimental parameters and parameter types. This request must send its parameters as a single JSON encoded string and the `content-type`_ header must be supplied as `application/json`_ .. _content-type: https://developer.mozilla.org/en-US/docs/Web/HTTP/Headers/Content-Type .. _application/json: https://developer.mozilla.org/en-US/docs/Web/HTTP/Basics_of_HTTP/MIME_types .. :quickref: Create/Edit run list; Create or edit the experimental run list layout :param: isNewRunlist : (Boolean) if runlist is new :param: clock : (Integer) creation timestamp :param: runlistType : (Integer) Run list type (unused) :param: runlistRemarks : Object containing global runlist information. Contains the following keys: | ``cols`` : List of experimental parameter objects. Experimental parameter objects contain the following keys: | ``name`` : (String) name of the parameter | ``type`` : (Integer) type of parameter | ``0`` : Regular parameter | ``3`` : Run Title identifier (optinal declaration) | ``4`` : Option parameter | ``parType`` : (Integer - optional) Type of regular parameter (only used if ``type=1``) | ``0`` : Number | ``1`` : Text | ``parUnits`` : (Integer - optional) Physical parameter units (only used if ``type=1``) | ``parOptions`` : (String - optional) JSON encoded string of parameter options (only used if ``type=1``) | ``action`` : (String) Action to be preformed on parameter | ``add`` : add parameter | ``edit`` : edit parameter | ``delete`` : delete parameter """ newRunList = False processRequest = request.get_json() if 'isNewRunlist' in processRequest: newRunList = processRequest["isNewRunlist"] else: newRunList = True try: Session = sessionmaker(bind=current_user.engineObj) session = Session() conn = current_user.engineObj.connect() ctx = MigrationContext.configure(conn) op = Operations(ctx) result = session.query( daqbrokerDatabase.runs).filter_by(clock=session.query( func.max(daqbrokerDatabase.runs.clock)).first()[0]).first() if not result: oldRemarks = {} else: oldRemarks = json.loads(result.runlistRemarks) #dbQuery=text("INSERT INTO runs VALUES(:clock,:clock,0,:linkRemarks,:runlistType,:runlistRemarks)") #startTableAlter="ALTER TABLE runlist " if newRunList: for i, col in enumerate(processRequest["runlistRemarks"]["cols"]): if ((not (int(col["type"]) == 1)) and (not (int(col["type"]) == 2)) and (not (int(col["type"]) == 3))): if int(col["type"]) == 4: newType = daqbrokerDatabase.Text if int(col["type"]) == 0 and int(col["parType"]) == 1: newType = daqbrokerDatabase.Text if int(col["type"]) == 0 and int(col["parType"]) == 0: newType = daqbrokerDatabase.Float newCol = daqbrokerDatabase.Column(col["name"], newType) op.add_column("runlist", newCol) processRequest["runlistRemarks"]["cols"][i][ "action"] = "addOld" else: for i, col in enumerate(processRequest["runlistRemarks"]["cols"]): extra = '' #print(col) if not int(col["type"]) == 3: if i >= len(oldRemarks["cols"]): column = col else: column = oldRemarks["cols"][i] if int(col["type"]) == 4: newType = daqbrokerDatabase.Text if int(col["type"]) == 0 and int(col["parType"]) == 1: newType = daqbrokerDatabase.Text if int(col["type"]) == 0 and int(col["parType"]) == 0: extra = "\"" + column["name"] + "\"::double precision" newType = daqbrokerDatabase.Float if col['action'] == 'add': newCol = daqbrokerDatabase.Column(col["name"], newType) op.add_column("runlist", newCol) processRequest["runlistRemarks"]["cols"][i][ "action"] = "addOld" elif col['action'] == 'edit': if col["name"] != column["name"]: op.alter_column("runlist", oldRemarks["cols"][i]["name"], new_column_name=col["name"], type_=newType, postgresql_using=extra) else: op.alter_column("runlist", oldRemarks["cols"][i]["name"], type_=newType, postgresql_using=extra) elif col['action'] == 'delete': op.drop_column("runlist", col["name"]) #print("done") daqbrokerDatabase.daqbroker_database.metadata.remove( daqbrokerDatabase.daqbroker_database.metadata.tables["runlist"] ) daqbrokerDatabase.daqbroker_database.metadata.reflect( current_user.engineObj, extend_existing=True) processRequest["runlistRemarks"]["cols"] = [ x for x in processRequest["runlistRemarks"]["cols"] if x['action'] != 'delete' ] newRuns = daqbrokerDatabase.runs(clock=processRequest["clock"], linkRemarks='', runlistRemarks=json.dumps( processRequest["runlistRemarks"])) session.add(newRuns) session.commit() conn.close() except Exception as e: session.rollback() traceback.print_exc() raise InvalidUsage(str(e), status_code=500) return jsonify('done')
def insertInstrument(): """ Insert a new instrument or edit an existing instrument on a DAQBroker database. Guest users are not allowed to create instruments. Created instruments are .. :quickref: Create/Edit instrument; Creates or edits a DAQBroker instrument instrument :param: Name : (String) unique instrument name :param: instid : (Integer) unique instrument identifier. Used to edit an existing instrument :param: description : (String) description of the instrument and its :param: email : (String) contact information for the instrument operator :param: Files : (Optional) JSON encoded list of instrument data source objects. Each Contains the following keys: | ``name`` : (String) name of the data source | ``metaid`` : (Integer) unique data source identifier. Only used to edit existing data sources | ``type`` : (Integer) type of instrument data source | ``node`` : (String) unique network node identifier | ``remarks`` : (String) JSON encoded object of extra data source information | ``channels`` : (Optional) JSON encoded list of data channel objects. Each contains the following keys: | ``Name`` : (String) data channel name | ``channelid`` : (Integer) unique channel identifier. -1 if the channel is new. Positive integer if the channel already exists | ``description`` : (String) data channel description | ``units`` : (String) data channel physical units | ``channeltype`` : (Integer) type of data channel | ``0`` : Number | ``1`` : Text | ``2`` : Custom | ``active`` : (Boolean) channel is shown on interface | ``fileorder`` : (Integer) Used to order channels in a data source | ``alias`` : (String) Original data channel name. Kept constant when name changes | ``remarks`` : (String) JSON encoded object with extra information | ``oldname`` : (String) Old channel name. Used to detect changes in the channel name | ``channeltypeOld`` : (Integer) Old channel type. Used to detect changes in the channel type """ processRequest = request.get_json() Session = sessionmaker(bind=current_user.engineObj) session = Session() conn = current_user.engineObj.connect() ctx = MigrationContext.configure(conn) op = Operations(ctx) try: if 'instid' in processRequest: newInst = False instid = processRequest['instid'] instrument = session.query(daqbrokerDatabase.instruments).filter_by(instid=instid).first() else: newInst = True maxInst = session.query(func.max(daqbrokerDatabase.instruments.instid)).one_or_none() # print(maxInst==None) if maxInst[0]: maxInstid = maxInst[0] else: maxInstid = 0 instid = maxInstid + 1 instrument = daqbrokerDatabase.instruments( Name=processRequest['Name'], instid=instid, active=False, description=processRequest['description'], username=current_user.username, email=processRequest['email'], insttype=0, log=None) # Now I have an object called "instrument" that I can use to add sources # and metadatas and to those metadatas I should be able to add channels. for file in processRequest['files']: if 'metaid' in file: metadata = session.query(daqbrokerDatabase.instmeta).filter_by(metaid=file["metaid"]).first() metadata.clock = time.time() * 1000 metadata.name= file['name'] metadata.type=file['type'] metadata.node=file['node'] metadata.remarks=json.dumps(file['remarks']) else: maxMeta = session.query(func.max(daqbrokerDatabase.instmeta.metaid)).first() if maxMeta[0]: maxMetaid = maxMeta[0] else: maxMetaid = 0 metaid = maxMetaid + 1 metadata = daqbrokerDatabase.instmeta( clock=time.time() * 1000, name=file['name'], metaid=metaid, type=file["type"], node=file["node"], remarks=json.dumps( file['remarks']), sentRequest=False, lastAction=0, lasterrortime=0, lasterror='', lockSync=False) instrument.sources.append(metadata) channelid = None if 'channels' in file: channelsInsert = [] for channel in file['channels']: if int(channel['channelid']) < 0: # New channel - have to insert maxChannel = session.query(func.max(daqbrokerDatabase.channels.channelid)).first() if not channelid: if maxChannel[0]: maxChannelid = maxChannel[0] else: maxChannelid = 0 channelid = maxChannelid + 1 else: channelid = channelid + 1 if 'remarks' in channel: if len(channel["remarks"].keys())>0: theRemarks = json.dumps(channel["remarks"]) else: theRemarks = json.dumps({}) else: theRemarks = json.dumps({}) theChannel = daqbrokerDatabase.channels( Name=channel["Name"], channelid=channelid, channeltype=int( channel["channeltype"]), valuetype=0, units=channel['units'], description=channel['description'], active=int( channel['active']) == 1, remarks=theRemarks, lastclock=0, lastValue=None, firstClock=0, fileorder=channel['fileorder'], alias=channel['alias']) metadata.channels.append(theChannel) channelsInsert.append({'name': channel["Name"], 'type': int(channel["channeltype"])}) if not newInst: extra = '' if int(channel['channeltype']) == 1: newType = daqbrokerDatabase.Float extra = "\"" + channel["Name"] + "\"::double precision" column = daqbrokerDatabase.Column(channel["Name"], newType) op.add_column(processRequest['Name'] + "_data", column) elif int(channel['channeltype']) == 2: newType = daqbrokerDatabase.Text column = daqbrokerDatabase.Column(channel["Name"], newType) op.add_column(processRequest['Name'] + "_data", column) elif int(channel['channeltype']) == 3: extra = "\"" + channel["Name"] + "\"::double precision" theType = daqbrokerDatabase.Float column = daqbrokerDatabase.Column(channel["Name"], newType) op.add_column(processRequest['Name'] + "_custom", column) elif not newInst: theChannel = session.query( daqbrokerDatabase.channels).filter_by( channelid=channel['channelid']).first() theChannel.Name = channel["Name"] theChannel.channeltype = int(channel["channeltype"]) theChannel.units = channel['units'] theChannel.description = channel['description'] theChannel.active = int(channel['active']) == 1 theChannel.fileorder = channel['fileorder'] theChannel.alias = channel['alias'] if (not channel['channeltypeOld'] == channel['channeltype']) or ( not channel['oldName'] == str(channel['Name'])): if not channel['oldName'] == str(channel['Name']): newName = str(channel['Name']) oldName = channel['oldName'] else: oldName = str(channel['Name']) newName = None if not channel['channeltypeOld'] == channel['channeltype']: if channel['channeltype'] == 1 or channel['channeltype'] == 3: newType = daqbrokerDatabase.Float extra = "\"" + oldName + "\"::double precision" else: newType = daqbrokerDatabase.Text extra = None else: newType = None if not channel['channeltypeOld'] == channel['channeltype'] and channel['channeltype'] == 3: if not newName: theName = oldName else: theName = newName if not newType: theType = daqbrokerDatabase.Float else: theType = newType column = daqbrokerDatabase.Column(theName, theType) op.drop_column(processRequest['Name'] + "_data", oldName) op.add_column(processRequest['Name'] + "_custom", column) elif not channel['channeltypeOld'] == channel['channeltype'] and channel['channeltypeOld'] != 3: if not newName: theName = oldName else: theName = newName if not newType: if channel['channeltypeOld'] == 1: theType = daqbrokerDatabase.Float else: theType = daqbrokerDatabase.Text else: theType = newType column = daqbrokerDatabase.Column(theName, theType) op.drop_column(processRequest['Name'] + "_custom", oldName) op.add_column(processRequest['Name'] + "_data", column) else: if channel['channeltype'] == 1 or channel['channeltype'] == 2: if extra: op.alter_column( processRequest['Name'] + "_data", oldName, new_column_name=newName, type_=newType, postgresql_using=extra) else: op.alter_column( processRequest['Name'] + "_data", oldName, new_column_name=newName, type_=newType) else: if extra=='': op.alter_column( processRequest['Name'] + "_custom", oldName, new_column_name=newName, type_=newType) else: op.alter_column( processRequest['Name'] + "_data", oldName, new_column_name=newName, type_=newType, postgresql_using=extra) elif newInst: raise InvalidUsage("Cannot issue edit channels on new instrument", status_code=401) if newInst: daqbrokerDatabase.createInstrumentTable(processRequest['Name'], channelsInsert, True) session.add(instrument) daqbrokerDatabase.daqbroker_database.metadata.create_all(current_user.engineObj) session.commit() conn.close() current_user.updateDB() return jsonify('done') except Exception as e: traceback.print_exc() session.rollback() # for statement in deleteStatements: # connection.execute(statement) raise InvalidUsage(str(e), status_code=500)
def update_member(self, id, data): member = self.get_member(id) if member is None: return None # NOTE: BaseContext's init method sets its base to the base # struct contained in the request, so we need to reset it here # to the base struct that is actually in the database - DCarv # NOTE: Remove base struct from cache! By John Doe model.BASES.bases.pop(member.name) # NOTE: Set old base struct as active! By John Doe self.set_base(member.struct) # NOTE: Check for base content changes! By John Doe old_base = json2base(member.struct) new_base = json2base(data['struct']) # NOTE: List all fields that should be deleted! By John Doe del_cols = [] for old_col_name, old_col in old_base.content.__allstructs__.items(): if old_col_name not in new_base.content.__allsnames__: del_cols.append(old_col) # NOTE: If any field will be deleted, delete it from all documents in # the base! By John Doe if len(del_cols) > 0: # NOTE: Create a fake request for DocumentCustomView and # DocumentContext! By John Doe url = "/%s/doc&$$={\"limit\":null}" % new_base.metadata.name for col in del_cols: params={ 'path': "[{\"path\":\"%s\",\"fn\":null,\"mode\":" +\ "\"delete\",\"args\":[]}]" % ("/".join(col.path)) } request = DummyRequest(path=url, params=params) request.method = 'PUT' request.matchdict = {"base": new_base.metadata.name} doc_view = DocumentCustomView(DocumentContextFactory(request), request) doc_view.update_collection() # NOTE: Check for relation field changes (to ALTER table if needed)! # By John Doe old_doc_table = get_doc_table(old_base.metadata.name, config.METADATA, **old_base.relational_fields) new_doc_table = get_doc_table(new_base.metadata.name, config.METADATA, **new_base.relational_fields) # NOTE: List relational fields that should be deleted! By John Doe del_cols = [] for old_col in old_doc_table.columns: if old_col.name not in new_doc_table.columns: del_cols.append(old_col) # NOTE: List relational fields that should be added! By John Doe new_cols = [] for new_col in new_doc_table.columns: if new_col.name not in old_doc_table.columns: # NOTE: Get liblightbase.lbbase.fields object! By John Doe field = new_base.relational_fields[new_col.name] custom_col = get_custom_column(field) new_cols.append(custom_col) # NOTE: Create alembic connection and operation object! By John Doe db_conn = config.ENGINE.connect() alembic_ctx = MigrationContext.configure(db_conn) alembic_op = Operations(alembic_ctx) # NOTE: Drop columns! By John Doe for col in del_cols: alembic_op.drop_column(new_doc_table.name, col.name) # TODO: New_col cannot be required! By John Doe # NOTE: Add columns! By John Doe for col in new_cols: alembic_op.add_column(new_doc_table.name, col) # TODO: Alter columns? By John Doe db_conn.close() # NOTE: Check for base name change! By John Doe if member.name != data['name']: old_name = 'lb_doc_%s' % (member.name) new_name = 'lb_doc_%s' % (data['name']) self.session.execute('ALTER TABLE %s RENAME TO %s' % (old_name, new_name)) old_name = 'lb_file_%s' % (member.name) new_name = 'lb_file_%s' % (data['name']) self.session.execute('ALTER TABLE %s RENAME TO %s' % (old_name, new_name)) old_name = 'lb_doc_%s_id_doc_seq' % (member.name) new_name = 'lb_doc_%s_id_doc_seq' % (data['name']) self.session.execute('ALTER SEQUENCE %s RENAME TO %s' % (old_name, new_name)) # NOTE: This will add any new fields to the base struct! By John Doe for name in data: setattr(member, name, data[name]) # NOTE: Now commits and closes session in the view instead of here # flush() pushes operations to DB's buffer - DCarv self.session.flush() # NOTE: Por alguma razão o objeto "session" estando com # "autocommit=True" não "commita" no "close" e por isso executamos um # "commit" aqui! "autocommit=True" não comita mais de uma operação em # sequência? By Questor # session_factory: sessionmaker( # class_='Session', # autoflush=True, # bind=Engine(postgresql://lbu:***@127.0.0.1/lb), # autocommit=True, # expire_on_commit=True # ) # registry: <sqlalchemy.\ # util.\ # _collections.\ # ThreadLocalRegistry object at 0x4143f90> # ! By Questor self.session.commit() model.HISTORY.create_member( **{ 'id_base': member.id_base, 'author': 'Author', 'date': datetime.datetime.now().strftime('%d/%m/%Y %H:%M:%S'), 'name': member.name, 'structure': utils.json2object(member.struct), 'status': 'UPDATED' }) self.lbirestart() # NOTE: Remove base struct from cache! By John Doe model.BASES.bases.pop(member.name) return member
from alembic.migration import MigrationContext from alembic.operations import Operations from sqlalchemy import Column, Float, UnicodeText, DateTime from sqlalchemy import create_engine if __name__ == '__main__': engine = create_engine('postgresql://postgres@localhost:5432/tetres') conn = engine.connect() ctx = MigrationContext.configure(conn) op = Operations(ctx) print("Adding column processed_start_date...") try: processed_start_date = Column("processed_start_date", DateTime, nullable=True) op.add_column("action_log", processed_start_date) print("Successfully added processed_start_date column!") except Exception as e: print("Failed adding processed_start_date column! Error: {}".format(e)) print("Adding column processed_end_date...") try: processed_end_date = Column("processed_end_date", DateTime, nullable=True) op.add_column("action_log", processed_end_date) print("Successfully added processed_end_date column!") except Exception as e: print("Failed adding processed_end_date column! Error: {}".format(e))
def upgrade(pyramid_env): admin_context = get_admin_context() op = Operations(admin_context) with admin_context.begin_transaction(): op.add_column('WS.WS.SYS_DAV_RES', sa.Column('RES_SIZE', sa.Integer))
def upgrade(pyramid_env): admin_context = get_admin_context() op = Operations(admin_context) with admin_context.begin_transaction(): op.add_column('WS.WS.SYS_DAV_RES', sa.Column('RES_SIZE', sa.Integer))
from alembic.migration import MigrationContext from alembic.operations import Operations from sqlalchemy import Column, VARCHAR from sqlalchemy import create_engine if __name__ == '__main__': engine = create_engine('postgresql://postgres@localhost:5432/tetres') conn = engine.connect() ctx = MigrationContext.configure(conn) op = Operations(ctx) try: status = Column("status", VARCHAR(255), nullable=True) op.add_column("route_wise_moe_parameters", status) print("Successfully added status column!") except Exception as e: print("Failed adding status column! Error: {}".format(e)) try: reason = Column("reason", VARCHAR(255), nullable=True) op.add_column("route_wise_moe_parameters", reason) print("Successfully added reason column!") except Exception as e: print("Failed adding reason column! Error: {}".format(e))