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)
