def rest_service( request, service, version, resource ): db = SqlSoup('postgres://%s/%s' % ('localhost', 'mypam')) method = request.method rp = db.execute("""SELECT rest.datasource.url, rest.resource.query, standard.array_accum( rest.parameter.name ) FROM rest.datasource JOIN rest.service ON rest.service.datasource_id=rest.datasource.id JOIN rest.version ON rest.version.service_id=rest.service.id JOIN rest.resource ON rest.resource.version_id=rest.version.id LEFT JOIN rest.parameter ON rest.parameter.resource_id=rest.resource.id WHERE rest.service.name=:service AND rest.version.version=:version AND rest.resource.name=:resource AND rest.resource.method=:method GROUP BY rest.datasource.url, rest.resource.query""", params={ 'service' : service, 'version' : version, 'resource' : resource, 'method' : method }) row = rp.first() if row != None: ( datasource_url, query, parameters ) = row service_db = SqlSoup( datasource_url ) query_params = {} for parameter in parameters: if parameter != None: query_params[ parameter ] = request.REQUEST.get( parameter ) try: query_rp = db.execute( query, params=query_params ) query_row = query_rp.first() except Exception as exc: return HttpResponseBadRequest( str( exc ), content_type="text/plain" ) if query_row != None: print query_row return HttpResponse( query_row[0], content_type="text/plain") return HttpResponseNotFound("service=[%s] version=[%s] resource=[%s] not found" % (service, version, resource), content_type="text/plain")
def migrateVersion3(): log.info('Upgrading DB to version 3.') # for some normal executions db = SqlSoup(engine) try: db.execute('ALTER TABLE Movie ADD dateChanged TIMESTAMP') log.info('Added dateChanged to Movie table') except OperationalError: log.debug('Column dateChanged already added') Session.add(DbVersion(3)) Session.flush()
def migrateVersion3(): log.info('Upgrading DB to version 3.') # for some normal executions db = SqlSoup(engine) try: db.execute('ALTER TABLE Movie ADD dateChanged TIMESTAMP') log.info('Added dateChanged to Movie table') except OperationalError: log.debug('Column dateChanged already added') Session.add(DbVersion(3)) Session.flush()
def raw_sql(sql): engine = create_engine( "mysql://%s:%s@%s/%s" % (CLOUD_MONITOR_MYSQL_USERNAME, CLOUD_MONITOR_MYSQL_PASSWORD, CLOUD_MONITOR_MYSQL_HOST, CLOUD_MONITOR_MYSQL_DB) ) db = SqlSoup(engine) return db.execute(sql).fetchall()
def migrateVersion4(): log.info('Upgrading DB to version 4.') # for some normal executions db = SqlSoup(engine) try: db.execute('ALTER TABLE MovieETA ADD lastCheck INTEGER') log.info('Added lastCheck to MovieETA table') except OperationalError: log.debug('Column lastCheck already added.') try: db.execute('ALTER TABLE MovieQueue ADD lastCheck INTEGER') log.info('Added lastCheck to MovieQueue table') except OperationalError: log.debug('Column lastCheck already added.') Session.add(DbVersion(4)) Session.flush()
def migrateVersion4(): log.info('Upgrading DB to version 4.') # for some normal executions db = SqlSoup(engine) try: db.execute('ALTER TABLE MovieETA ADD lastCheck INTEGER') log.info('Added lastCheck to MovieETA table') except OperationalError: log.debug('Column lastCheck already added.') try: db.execute('ALTER TABLE MovieQueue ADD lastCheck INTEGER') log.info('Added lastCheck to MovieQueue table') except OperationalError: log.debug('Column lastCheck already added.') Session.add(DbVersion(4)) Session.flush()
def main(): db_name = sys.argv[1] db = SqlSoup(db_name) while True: try: cmd = raw_input(prompt) if not cmd: continue if cmd.lower() in ("help", "?"): show_help() continue sql = get_sql(cmd, db.engine.name) or cmd res = db.execute(sql) print_result(res) db.commit() except (SystemExit, KeyboardInterrupt, EOFError): print "\nBye!\n" return 0 except Exception: traceback.print_exc() db.rollback()
def migrateVersion2(): log.info('Upgrading DB to version 2.') # for some normal executions db = SqlSoup(engine) # Remove not used table try: db.execute('DROP TABLE Feed') log.info('Removed old Feed table.') except (OperationalError, NoSuchTableError): log.debug('No Feed table found.') # History add column try: db.execute('DROP TABLE History') log.info('Removed History table.') except (OperationalError, NoSuchTableError): log.debug('No History table found.') # RenameHistory add column try: Session.query(RenameHistory).filter_by(movieQueue = '').all() log.debug('Column "RenameHistory:movieQueue" exists, not necessary.') except (OperationalError, NoSuchTableError): db.execute("CREATE TABLE RenameHistoryBackup(id, movieId, old, new);") db.execute("INSERT INTO RenameHistoryBackup SELECT id, movieId, old, new FROM RenameHistory;") db.execute("DROP TABLE RenameHistory;") db.execute("CREATE TABLE RenameHistory (id, movieQueue, old VARCHAR, new VARCHAR);") db.execute("INSERT INTO RenameHistory SELECT id, movieId, old, new FROM RenameHistoryBackup;") db.execute("DROP TABLE RenameHistoryBackup;") log.info('Added "movieQueue" column to existing RenameHistory Table.') # Mark all history # Quality from string to int movies = Session.query(Movie).all() for movie in movies: # Add moviequeues log.info('Making Queue item for %s' % movie.name) queue = MovieQueue() queue.movieId = movie.id queue.qualityType = movie.quality if movie.quality else 'dvdrip' #just for backup queue.order = 1 queue.active = (movie.status != u'deleted') queue.completed = (movie.status != u'want') queue.markComplete = True Session.add(queue) Session.flush() log.info('Doing some stuff to RenameHistory') history = Session.query(RenameHistory).filter_by(movieQueue = movie.id).first() if history: history.movieQueue = queue.id queue.name = os.path.basename(os.path.dirname(history.old)) Session.flush() Session.add(DbVersion(1)) # Add version 1 for teh nice Session.add(DbVersion(2)) Session.flush()
class API_DB(object): def __init__(self): # Create any tables that don't already exist self.createTables() self.db = SqlSoup(FLAGS.api_db_url) self.db_nets = self.db.networks self.db_ports = self.db.ports self.db_macs = self.db.macs self.db_bonds = self.db.bonds self.db_flowspace = self.db.flowspace self.db_net2slice = self.db.delegated_nets def createTables(self): engine = create_engine(FLAGS.api_db_url) data = MetaData(bind=engine) data.reflect() existing_tables = data.tables.keys() # Dictionary for database description # Format: {Table object /w primary Column: [list of extra Column objects]} db_schema = { Table('networks', data, Column('network_id', String(255), primary_key=True), keep_existing=True) : [], Table('ports', data, Column('id', Integer, primary_key=True, autoincrement=True), keep_existing=True) : [ Column('port_num', Integer), Column('datapath_id', String(255)), Column('network_id', String(255)), Column('bond_id', String(255)) ], Table('macs', data, Column('mac_address', String(255), primary_key=True), keep_existing=True) : [ Column('network_id', String(255)) ], Table('bonds', data, Column('bond_id', String(255), primary_key=True), keep_existing=True) : [ Column('datapath_id', String(255)), Column('network_id', String(255)) ], Table('flowspace', data, Column('id', Integer, primary_key=True), keep_existing=True) : [ Column('datapath_id', String(255)), Column('port_num', Integer), Column('mac_address', String(255)) ], Table('delegated_nets', data, Column('network_id', String(255), primary_key=True), keep_existing=True) : [ Column('slice', String(255)) ], } for tab, colList in db_schema.items(): # Create table if it doesn't exist if tab.name not in existing_tables: tab.create() # Check columns and update if necessary for col in colList: if col.name not in tab.c.keys(): col.create(tab, populate_default=True) def checkConnection(self): try: # Do a simple query self.db.execute("show databases") except sqlexc.OperationalError: # Connection was interrupted for some reason, try restarting session self.db.session.close() self.db.session.rollback() # Try simple query again self.db.execute("show databases") except: # Unknown exception, raise raise ########################################################################### # Functions for retrieving database contents ########################################################################### def getNetworks(self): self.checkConnection() net_list = [] for net in self.db_nets.all(): net_list.append(net.network_id) return net_list def getPorts(self): self.checkConnection() port_list = [] for port in self.db_ports.all(): port_list.append((port.network_id, port.datapath_id, port.port_num, port.bond_id)) return port_list def getMACs(self): self.checkConnection() mac_list = [] for mac in self.db_macs.all(): mac_list.append((mac.network_id, mac.mac_address)) return mac_list def getBonds(self): self.checkConnection() bond_list = [] for bond in self.db_bonds.all(): bond_list.append((bond.bond_id, bond.datapath_id, bond.network_id)) return bond_list def getFlowSpace(self): self.checkConnection() flowspace_list = [] for flowspace in self.db_flowspace.all(): flowspace_list.append((flowspace.id, flowspace.datapath_id, flowspace.port_num, flowspace.mac_address)) return flowspace_list def getDelegatedNets(self): self.checkConnection() net2slice_list = [] for net2slice in self.db_net2slice.all(): net2slice_list.append((net2slice.network_id, net2slice.slice)) return net2slice_list ########################################################################### # Functions for storing API calls into the database ########################################################################### def createNetwork(self, network_id, update=False): self.checkConnection() if not self.db_nets.get(network_id): self.db_nets.insert(network_id=network_id) else: if not update: raise NetworkAlreadyExist(network_id=network_id) self.db.commit() def updateNetwork(self, network_id): self.createNetwork(network_id, True) def deleteNetwork(self, network_id): self.checkConnection() entry = self.db_nets.get(network_id) if entry: self.db.delete(entry) else: raise NetworkNotFound(network_id=network_id) self.db.commit() def addMAC(self, network_id, mac): self.checkConnection() entry = self.db_macs.get(mac) # Check for existing entry if not entry: self.db_macs.insert(network_id=network_id, mac_address=mac) else: if entry.network_id == network_id or network_id == NW_ID_EXTERNAL: # If old network and new network the same, do nothing # Or if trying to change an existing net association to NW_ID_EXTERNAL, do nothing return elif entry.network_id == NW_ID_EXTERNAL: # Allow changing from NW_ID_EXTERNAL to a known network UUID entry.network_id = network_id else: raise MacAddressDuplicated(mac=mac) try: self.db.commit() except sqlexc.IntegrityError: # Most likely due to race condition between setting DB change # and packet arriving at next switch triggering a DB read self.db.session.rollback() def delMAC(self, mac): self.checkConnection() entry = self.db_macs.get(mac) if entry: self.db.delete(entry) else: raise MacAddressNotFound(mac=mac) self.db.commit() def createPort(self, network_id, dpid, port_num, update=False): self.checkConnection() # Check for existing entry dpid = dpid.lstrip('0') params = and_(self.db_ports.datapath_id==dpid, self.db_ports.port_num==port_num) old_entry = self.db_ports.filter(params).first() if not old_entry: # If updating but didn't locate existing entry, raise exception? # For now, just insert the entry and return success self.db_ports.insert(network_id=network_id, datapath_id=dpid, port_num=port_num) else: if update: old_entry.network_id = network_id else: # Entry already exists for (dpid,port) <=> network raise PortAlreadyExist(network_id=network_id, dpid=dpid, port=port_num) self.db.commit() def updatePort(self, network_id, dpid, port_num): self.createPort(network_id, dpid, port_num, True) def deletePort(self, network_id, dpid, port_num): self.checkConnection() dpid = dpid.lstrip('0') params = and_(self.db_ports.datapath_id==dpid, self.db_ports.port_num==port_num) entry = self.db_ports.filter(params).first() if entry: self.db.delete(entry) else: raise PortNotFound(network_id=network_id, dpid=dpid, port=port_num) self.db.commit() def createBond(self, bond_id, dpid, network_id): self.checkConnection() # Check for existing entry dpid = dpid.lstrip('0') if not self.db_bonds.get(bond_id): self.db_bonds.insert(bond_id=bond_id, datapath_id=dpid, network_id=network_id) else: raise BondAlreadyExist(bond_id=bond_id) self.db.commit() def deleteBond(self, bond_id): self.checkConnection() entry = self.db_bonds.get(bond_id) if entry: self.db.delete(entry) else: # Do nothing pass # Delete any ports currently bonded to the bond_id for port in self.db_ports.all(): if port.bond_id == bond_id: port.bond_id = None self.db.commit() def addPort_bond(self, bond_id, port_num): self.checkConnection() bondEntry = self.db_bonds.get(bond_id) if bondEntry: dpid = bondEntry.datapath_id network_id = bondEntry.network_id else: raise BondNotFound(bond_id=bond_id) params = and_(self.db_ports.datapath_id==dpid, self.db_ports.network_id==network_id, self.db_ports.port_num==port_num) entry = self.db_ports.filter(params).first() if entry: # Check for existing entry old_bond_id = entry.bond_id if not old_bond_id: entry.bond_id = bond_id else: raise BondPortAlreadyBonded(port=port_num, bond_id=old_bond_id) else: raise PortNotFound(network_id=network_id, dpid=dpid, port=port_num) self.db.commit() def deletePort_bond(self, bond_id, port_num): self.checkConnection() bondEntry = self.db_bonds.get(bond_id) if bondEntry: dpid = bondEntry.datapath_id else: raise BondNotFound(bond_id=bond_id) params = and_(self.db_ports.datapath_id==dpid, self.db_ports.port_num==port_num, self.db_ports.bond_id==bond_id) entry = self.db_ports.filter(params).first() if entry: entry.bond_id = None else: raise BondPortNotFound(port=port_num, bond_id=bond_id) self.db.commit() def addFlowSpaceID(self, dpid, port_num, mac, id): self.checkConnection() entry = self.db_flowspace.get(id) if not entry: self.db_flowspace.insert(id=id, datapath_id=dpid, port_num=port_num, mac_address=mac) else: raise FlowSpaceIDAlreadyExist(flowspace_id=id) self.db.commit() def delFlowSpaceID(self, id): self.checkConnection() entry = self.db_flowspace.get(id) if entry: self.db.delete(entry) else: # Not found, raise exception? pass self.db.commit() def assignNetToSlice(self, sliceName, network_id): self.checkConnection() entry = self.db_net2slice.get(network_id) if not entry: self.db_net2slice.insert(network_id=network_id, slice=sliceName) else: raise NetworkAlreadyAssigned(network_id=network_id, sliceName=entry.slice) self.db.commit() def removeNetFromSlice(self, network_id): self.checkConnection() entry = self.db_net2slice.get(network_id) if entry: self.db.delete(entry) else: # Not found, raise exception? pass self.db.commit()
def migrateVersion2(): log.info('Upgrading DB to version 2.') # for some normal executions db = SqlSoup(engine) # Remove not used table try: db.execute('DROP TABLE Feed') log.info('Removed old Feed table.') except (OperationalError, NoSuchTableError): log.debug('No Feed table found.') # History add column try: db.execute('DROP TABLE History') log.info('Removed History table.') except (OperationalError, NoSuchTableError): log.debug('No History table found.') # RenameHistory add column try: Session.query(RenameHistory).filter_by(movieQueue='').all() log.debug('Column "RenameHistory:movieQueue" exists, not necessary.') except (OperationalError, NoSuchTableError): db.execute("CREATE TABLE RenameHistoryBackup(id, movieId, old, new);") db.execute( "INSERT INTO RenameHistoryBackup SELECT id, movieId, old, new FROM RenameHistory;" ) db.execute("DROP TABLE RenameHistory;") db.execute( "CREATE TABLE RenameHistory (id, movieQueue, old VARCHAR, new VARCHAR);" ) db.execute( "INSERT INTO RenameHistory SELECT id, movieId, old, new FROM RenameHistoryBackup;" ) db.execute("DROP TABLE RenameHistoryBackup;") log.info('Added "movieQueue" column to existing RenameHistory Table.') # Mark all history # Quality from string to int movies = Session.query(Movie).all() for movie in movies: # Add moviequeues log.info('Making Queue item for %s' % movie.name) queue = MovieQueue() queue.movieId = movie.id queue.qualityType = movie.quality if movie.quality else 'dvdrip' #just for backup queue.order = 1 queue.active = (movie.status != u'deleted') queue.completed = (movie.status != u'want') queue.markComplete = True Session.add(queue) Session.flush() log.info('Doing some stuff to RenameHistory') history = Session.query(RenameHistory).filter_by( movieQueue=movie.id).first() if history: history.movieQueue = queue.id queue.name = os.path.basename(os.path.dirname(history.old)) Session.flush() Session.add(DbVersion(1)) # Add version 1 for teh nice Session.add(DbVersion(2)) Session.flush()
class Upgrade: def __init__(self, engine): self.__db = SqlSoup(engine) def process(self): """Main function that run the update process""" #_upgrade_system_info_structure() old_app_version = self.get_current_app_version() old_db_version = self.get_current_db_version() print("=== Upgrade process") print("\t> Current version (application : %s, database = %s)" % (self.get_current_app_version(), self.get_current_db_version())) print("\t> New version (application : %s, database = %s)" % (self.get_new_app_version(), self.get_new_db_version())) self._sanity_check_before_upgrade() # Database upgrade while upgrade_scripts.db_upgrade(self): pass if old_db_version == self.get_current_db_version(): print("\tThe database was NOT upgraded: nothing to do!") # Application upgrade while upgrade_scripts.app_upgrade(self): pass if old_app_version == self.get_current_app_version(): print("\tThe application was NOT upgraded: nothing to do!") print("=== Upgrade process terminated") def set_version(self, app_version, db_version): """Set the version of the application and the database""" self.update_app_version(app_version) self.update_db_version(db_version) self.commit() def commit(self): self.__db.commit() ##################### # Utility functions # ##################### def _sanity_check_before_upgrade(self): """Check that the upgrade process can be run""" # We use NormalizedVersion to be able to make comparisons new_db_version = self.get_new_db_version() new_app_version = self.get_new_app_version() current_db_version = self.get_current_db_version() current_app_version = self.get_current_app_version() if new_db_version > new_app_version: print("Internal error") print("The new database version number (%s) can't be superior to the application one (%s)" % (new_db_version, new_app_version)) self._abort_upgrade_process() if current_db_version > new_db_version: print("Something is wrong with your installation:") print("Your database version number (%s) is superior to the one you're trying to install (%s)" % (current_db_version, new_db_version)) self._abort_upgrade_process() if current_app_version > new_app_version: print("Something is wrong with your installation:") print("Your application version number (%s) is superior to the one you're trying to install (%s)" % (current_app_version, new_app_version)) self._abort_upgrade_process() if current_db_version > current_app_version: print("Something is wrong with your installation:") print("Your database version number (%s) is superior to the application one (%s)" % (current_db_version, current_app_version)) self._abort_upgrade_process() def get_current_db_version(self): """Return the current version of the database""" db_version = self._sql_execute("SELECT db_version FROM core_system_info").fetchone()[0] if db_version is None or db_version == '': # Should only happen for the first upgrade using this script return NormalizedVersion('0.1.0') else: return NormalizedVersion(self._suggest_normalized_version(db_version)) def get_new_db_version(self): """Return the version of the database we should upgrade to (normalized version)""" return NormalizedVersion(self._suggest_normalized_version(DB_VERSION)) def update_db_version(self, db_version): """Update the version of the database""" if self._sql_execute("SELECT db_version FROM core_system_info").fetchone() is None: sql = "INSERT INTO core_system_info (db_version) VALUES('%s')" % db_version else: sql = "UPDATE core_system_info SET db_version='%s'" % db_version self._sql_execute(sql) def get_current_app_version(self): """Return the current version of the application""" try: app_version = self._sql_execute("SELECT app_version FROM core_system_info").fetchone()[0] # Should only happen if the 'app_version' column doesn't exist (first application upgrade using this script) if app_version is None or app_version == '': app_version = NormalizedVersion('0.1.0') return NormalizedVersion(self._suggest_normalized_version(app_version)) except Exception: return NormalizedVersion('0.1.0') def get_new_app_version(self): """Return the version of the application we should upgrade to (normalized version)""" return NormalizedVersion(self._suggest_normalized_version(__version__)) def update_app_version(self, app_version): """Update the version of the application""" if self._sql_execute("SELECT app_version FROM core_system_info").fetchone() is None: sql = "INSERT INTO core_system_info (app_version) VALUES('%s')" % app_version else: sql = "UPDATE core_system_info SET app_version='%s'" % app_version self._sql_execute(sql) def _suggest_normalized_version(self, version): n_version = suggest_normalized_version(version) if n_version is None: print("Error : invalid version number : %s" % version) print("See : http://wiki.domogik.org/Release_numbering") self._abort_install_process() else: return n_version def _sql_execute(self, sql_code): return self.__db.execute(sql_code) def _abort_upgrade_process(self, message=""): print("Upgrade process aborted : %s" % message) sys.exit(1)