def list(cls, typeid=None, name_pattern=None):
''' Lists entities with the given type identifier and whose
names match the given pattern. Both parameters are optional. '''
query = Entity.__list_query_all
query_parameters = { }
if typeid is not None:
if name_pattern is not None:
query = Entity.__list_query_by_type_and_name
query_parameters = { 'type': typeid, 'name': name_pattern }
else:
query = Entity.__list_query_by_type
query_parameters = { 'type': typeid }
elif name_pattern is not None:
query = Entity.__list_query_by_name
query_parameters = { 'name': name_pattern }
query += Entity.__list_query_order_by
selection = None
if query_parameters is not None:
selection = Database.instance().select(query, query_parameters)
else:
selection = Database.instance().select(query)
for row in selection:
# uniqueid, typeid, name, stateid, statevalue, lastcheckin
unique_id, row = row[0], (row[1], row[2], row[3], row[4], row[5])
yield Entity.__create_from_db_row(unique_id, row)
def setUpClass(cls):
search_path = sys.argv[0].split(':')
import_modules(search_path, 'entities')
EntityType.register(199, 'TestEntity', Entity)
# empty history
Database.instance().write('DELETE FROM history')
def save(self):
''' Inserts or updates the entity in the database. '''
db = Database.instance()
with db.writer():
row = Database.instance().select(Entity.__exists_query, self.unique_id).fetchone()
if row is None:
db.write(Entity.__insert_stmt, self.unique_id, self.entity_type.type_id, self.name, self.state.id, self.state_value, self.last_checkin)
else:
db.write(Entity.__update_stmt, self.name, self.state.id, self.state_value, self.last_checkin, self.unique_id)
def testRollback(self):
path = 'db/rollback.db'
db = Database.instance(path)
db.write('CREATE TABLE test1(a PRIMARY KEY, b)')
db.write('INSERT INTO test1 VALUES (1, 2)')
with db.writer():
db.write('INSERT INTO test1 VALUES (3, 4)')
raise RollbackException
try:
with db.writer():
raise Exception('unmanaged')
except:
pass # expected
db.write('INSERT INTO test1 VALUES (5, 6)')
for s in db.select('SELECT * FROM test1'):
print 'Row:',
for i in s:
print i,
print
print 'Dumping database:'
for d in db.dump():
print d
# Cleanup
import os
os.remove(path)
os.rmdir('db')
def query(cls, time_from, time_to, entity_id, limit, offset):
''' Returns history at most "limit" records starting from "offset"
between "time_from" and "time_to" for the entity with the given "entity_id" identifier.
The "limit" and "offset" parameters are required the rest are optional. '''
conditions = []
parameters = dict()
if time_from is not None:
conditions.append('timestamp >= :ts_from')
parameters['ts_from'] = time_from
if time_to is not None:
conditions.append('timestamp <= :ts_to')
parameters['ts_to'] = time_to
if entity_id is not None:
conditions.append('entityid = :eid')
parameters['eid'] = entity_id
query = 'SELECT timestamp, entityid, entityname, action, type FROM ' + EntityHistory.__tablename__
if len(conditions) > 0:
query = query + ' WHERE ' + ' AND '.join(conditions)
query = query + ' ORDER BY timestamp DESC'
if limit is not None:
query = query + ' LIMIT :limit'
parameters['limit'] = limit
if offset is not None:
query = query + ' OFFSET :offset'
parameters['offset'] = offset
db = Database.instance()
for timestamp, entityid, entityname, action, actiontype in db.select(query, parameters):
yield EntityHistory(timestamp, entityid, entityname, action, actiontype)
def printall(self):
print 'DB | uniqueid | typeid | name | state | statevalue | assignedid | lastcheckin'
print 'DB | -------- | ------ | ---- | ----- | ---------- | ---------- | -----------'
for row in Database.instance().select('SELECT * FROM entity'):
print 'DB',
for value in row:
print '|', value,
print
def check_database_table(cls):
''' Checks whether the related database table exists
and creates it if is does not. '''
db = Database.instance()
with db.writer():
try:
db.select(EntityHistory.__table_exists)
except:
db.write(EntityHistory.__table_create)
def create_user(self, username, password):
''' Inserts a new user into the database with the given credentials. '''
db = Database.instance()
with db.writer():
if db.select(Authentication.__user_exists_query, username.lower()).fetchone():
return False
else:
db.write(Authentication.__user_create_stmt, username.lower(), password)
return True
def edit_user(self, uid, username, password):
''' Modifies the credentials of the user with the given identifier. '''
db = Database.instance()
with db.writer():
existing_uid = db.select(Authentication.__user_exists_query, username.lower()).fetchone()[0]
if existing_uid and existing_uid != uid:
return False
else:
db.write(Authentication.__user_edit_stmt, username.lower(), password, uid)
return True
def main_entry():
''' Loads, configures and start system modules,
then waits for the exit condition, finally
it stop all started system modules '''
database = Database.instance()
ModuleLoader.load_and_configure_modules( database )
ModuleLoader.start_modules()
if sysargs.server:
__wait_for_exit_signal()
else:
raw_input('Press ENTER to finish')
ModuleLoader.stop_modules()
def testDefault(self):
db = Database.instance()
db.write('CREATE TABLE test1(a PRIMARY KEY, b)')
db.write('INSERT INTO test1 VALUES (1, 2)')
for s in db.select('SELECT * FROM test1'):
print 'Row:',
for i in s:
print i,
print
print 'Dumping database:'
for d in db.dump():
print d
# Cleanup
import os
os.remove('db/default.db')
os.rmdir('db')
def count(cls, time_from, time_to, entity_id):
''' Returns the number of records between "time_from" and "time_to"
for the entity with the given "entity_id" identifier.
All parameters are optional. '''
conditions = []
parameters = dict()
if time_from is not None:
conditions.append('timestamp >= :ts_from')
parameters['ts_from'] = time_from
if time_to is not None:
conditions.append('timestamp <= :ts_to')
parameters['ts_to'] = time_to
if entity_id is not None:
conditions.append('entityid = :eid')
parameters['eid'] = entity_id
query = 'SELECT COUNT(rowid) FROM ' + EntityHistory.__tablename__
if len(conditions) > 0:
query = query + ' WHERE ' + ' AND '.join(conditions)
return Database.instance().select(query, parameters).fetchone()[0]
def delete(cls, unique_id):
''' Deletes the entity from the database with the given identifier. '''
db = Database.instance()
with db.writer():
db.write(Entity.__delete_stmt, unique_id)
def find(cls, unique_id):
''' Returns the entity registered with "unique_id" identifier. '''
row = Database.instance().select(Entity.__query_find, unique_id).fetchone()
if row:
return Entity.__create_from_db_row(unique_id, row)
def delete_user(self, uid):
''' Deletes the user with the given identifier. '''
db = Database.instance()
with db.writer():
db.write(Authentication.__user_delete_stmt, uid)
def log(cls, entity, action, action_type):
''' Saves an entry to the database. '''
db = Database.instance()
with db.writer():
db.write(EntityHistory.__insert_stmt, time.time(), entity.unique_id, entity.name, action, action_type)
def list_users(self):
''' Lists parameters of all known users in the database. '''
for uid, username, admin in Database.instance().select(Authentication.__list_query):
yield (uid, username, admin)
def authenticate(self, username, password_hash):
''' Executes authentication with the given credentials,
returns the session information if it succeeds. '''
userid, admin = Database.instance().select(Authentication.__auth_query, username.lower(), password_hash).fetchone()
if userid:
return (self.__initialize_session(userid), admin)