def setupDB(): config = Config() dbConn = Database(config) # read SQL skeleton with open(os.path.join(os.getcwd(), 'setup/db_create.sql'), 'r') as f: sql = f.read() # fill in placeholders sql = sql.replace('&user', config.getProperty('Database', 'user')) # run SQL dbConn.execute(sql, None, None) # add admin user sql = ''' INSERT INTO aide_admin.user (name, email, hash, issuperuser) VALUES (%s, %s, %s, %s) ON CONFLICT (name) DO NOTHING; ''' adminPass = config.getProperty('Project', 'adminPassword') uHandler = UserHandling.backend.middleware.UserMiddleware(config) adminPass = uHandler._create_hash(adminPass.encode('utf8')) values = (config.getProperty('Project', 'adminName'), config.getProperty('Project', 'adminEmail'), adminPass, True,) dbConn.execute(sql, values, None)
def migrate_aide(): from modules import Database, UserHandling from util.configDef import Config config = Config() dbConn = Database(config) if dbConn.connectionPool is None: raise Exception('Error connecting to database.') warnings = [] errors = [] # bring all projects up-to-date (if registered within AIDE) projects = dbConn.execute('SELECT shortname FROM aide_admin.project;', None, 'all') if projects is not None and len(projects): # get all schemata and check if project still exists schemata = dbConn.execute( 'SELECT schema_name FROM information_schema.schemata', None, 'all') if schemata is not None and len(schemata): schemata = set([s['schema_name'].lower() for s in schemata]) for p in projects: try: pName = p['shortname'] # check if project still exists if not pName.lower() in schemata: warnings.append( f'WARNING: project "{pName}" is registered but does not exist in database.' ) #TODO: option to auto-remove? continue # make modifications one at a time for mod in MODIFICATIONS_sql: dbConn.execute(mod.format(schema=pName), None, None) except Exception as e: errors.append(str(e)) else: warnings.append( 'WARNING: no project schemata found within database.') else: warnings.append('WARNING: no project registered within AIDE.') return warnings, errors
from tqdm import tqdm from util.configDef import Config from modules import Database config = Config() # setup DB connection dbConn = Database(config) if dbConn.connectionPool is None: raise Exception('Error connecting to database.') dbSchema = config.getProperty('Database', 'schema') # check if correct type of annotations annotationType = dbConn.execute( 'SELECT annotationtype FROM aide_admin.project WHERE shortname = %s;', (args.project, ), 1) if not len(annotationType): raise Exception( f'Project with name "{args.project}" could not be found in database.' ) annotationType = annotationType[0]['annotationtype'] exportAnnotations = args.export_annotations if exportAnnotations and not (annotationType == 'boundingBoxes'): print( 'Warning: project annotations are not bounding boxes; skipping annotation export...' ) exportAnnotations = False # query label definition labeldef = {} # label UUID : (name, index,)
'tiff', 'bmp', 'ico', 'jfif', 'pjpeg', 'pjp' ) if args.file_format.lower().strip().strip('.') not in valid_file_formats: raise Exception('Error: provided file format ("{}") is not valid.'.format(args.file_format)) os.makedirs(args.target_folder, exist_ok=True) # query and export label definition labelQuery = dbConn.execute('SELECT * FROM {schema}.labelclass;'.format(schema=dbSchema), None, 'all') with open(os.path.join(args.target_folder, 'classDefinitions.txt'), 'w') as f: f.write('labelclass,index\n') for labelIdx, l in enumerate(labelQuery): f.write('{},{}\n'.format(l['name'],labelIdx)) # start querying and exporting if exportAnnotations: sql = ''' SELECT * FROM {schema}.annotation AS anno JOIN (SELECT id AS imID, filename FROM {schema}.image) AS img ON anno.image = img.imID '''.format(schema=dbSchema) queryArgs = []
imgFiles = glob.glob(os.path.join(imgBaseDir, '**'), recursive=True) for i in tqdm(imgFiles): if os.path.isdir(i): continue _, ext = os.path.splitext(i) if ext.lower() not in valid_extensions: continue baseName = i.replace(imgBaseDir, '') imgs.add(baseName) # ignore images that are already in database print('Filter images already in database...') imgs_existing = dbConn.execute( ''' SELECT filename FROM {}.image; '''.format(dbSchema), None, 'all') imgs_existing = set([i['filename'] for i in imgs_existing]) imgs = list(imgs.difference(imgs_existing)) imgs = [(i, ) for i in imgs] # push image to database print('Adding to database...') dbConn.insert( ''' INSERT INTO {}.image (filename) VALUES %s; '''.format(dbSchema), imgs) print('Done.')
'ALTER TABLE {schema}.labelclass ADD COLUMN IF NOT EXISTS keystroke SMALLINT UNIQUE;', 'ALTER TABLE {schema}.image ADD COLUMN IF NOT EXISTS last_requested TIMESTAMPTZ;' ] if __name__ == '__main__': parser = argparse.ArgumentParser(description='Update AIde database structure.') parser.add_argument('--settings_filepath', type=str, default='config/settings.ini', const=1, nargs='?', help='Manual specification of the directory of the settings.ini file; only considered if environment variable unset (default: "config/settings.ini").') args = parser.parse_args() if not 'AIDE_CONFIG_PATH' in os.environ: os.environ['AIDE_CONFIG_PATH'] = str(args.settings_filepath) from util.configDef import Config from modules import Database config = Config() dbConn = Database(config) if dbConn.connectionPool is None: raise Exception('Error connecting to database.') dbSchema = config.getProperty('Database', 'schema') # make modifications one at a time for mod in MODIFICATIONS_sql: dbConn.execute(mod.format(schema=dbSchema), None, None) print('Project {} is now up-to-date for the latest changes in AIde.'.format(config.getProperty('Project', 'projectName')))
# parse class names and indices if args.label_folder is not None: classdef = {} classList = [] with open(os.path.join(args.label_folder, 'classes.txt'),'r') as f: lines = f.readlines() for idx, line in enumerate(lines): className = line.strip() classList.append(className) # push to database dbConn.execute(''' INSERT INTO {}.LABELCLASS (name) VALUES ( %s ) ON CONFLICT (name) DO NOTHING; '''.format(dbSchema), (className,)) # get newly assigned index returnVal = dbConn.execute(''' SELECT id FROM {}.LABELCLASS WHERE name LIKE %s'''.format(dbSchema), (className+'%',), 1) classdef[idx] = returnVal[0]['id'] # prepare insertion SQL string if args.annotation_type == 'annotation': sql = '''
dbConn = Database(config) if dbConn.connectionPool is None: raise Exception('Error connecting to database.') # v1 config file v1Config = Config(args.settings_filepath) # db schema of v1 project dbSchema = v1Config.getProperty('Database', 'schema') projectName = v1Config.getProperty('Project', 'projectName') # verify we're running a database on v2 standards isV2 = dbConn.execute( ''' SELECT 1 FROM information_schema.tables WHERE table_schema = 'aide_admin' AND table_name = 'project'; ''', None, 'all') if isV2 is None or not len(isV2): # upgrade to v2 dbName = config.getProperty('Database', 'name') print( f'WARNING: Target database "{dbName}" has not (yet) been upgraded to the AIDE v2 schema; we will attempt to do this now...' ) setupDB() # verify that project is unique uniqueQuery = dbConn.execute( ''' SELECT shortname, name
imgBaseDir += '/' # parse class names and indices if args.label_folder is not None: classdef = {} with open(os.path.join(args.label_folder, 'classes.txt'), 'r') as f: lines = f.readlines() for idx, line in enumerate(lines): className = line.strip() # push to database dbConn.execute( ''' INSERT INTO {}.LABELCLASS (name) VALUES ( %s ) ON CONFLICT (name) DO NOTHING; '''.format(dbSchema), (className, )) # get newly assigned index returnVal = dbConn.execute( ''' SELECT id FROM {}.LABELCLASS WHERE name LIKE %s'''.format( dbSchema), (className + '%', ), 1) classdef[idx] = returnVal[0]['id'] # prepare insertion SQL string if args.annotation_type == 'annotation': sql = ''' INSERT INTO {}.ANNOTATION (username, image, timeCreated, timeRequired, label, x, y, width, height)
dbSchema = config.getProperty('Database', 'schema') # check if running on file server imgBaseDir = config.getProperty('FileServer', 'staticfiles_dir') if not os.path.isdir(imgBaseDir): raise Exception( '"{}" is not a valid directory on this machine. Are you running the script from the file server?' .format(imgBaseDir)) if not imgBaseDir.endswith('/'): imgBaseDir += '/' # get all image paths from the database print('Checking database for image entries...') imgs_db = dbConn.execute( ''' SELECT filename FROM {}.image; '''.format(dbSchema), None, 'all') imgs_existing = set([i['filename'] for i in imgs_db]) # locate all images and their base names on the file system print('Locating image paths...') imgs_files = set() imgFiles = glob.glob(os.path.join(imgBaseDir, '**'), recursive=True) for i in tqdm(imgFiles): if os.path.isdir(i): continue baseName = i.replace(imgBaseDir, '') imgs_files.add(baseName) # filter orphaned images
def migrate_aide(forceMigrate=False): from modules import Database, UserHandling from util.configDef import Config config = Config() dbConn = Database(config) if not dbConn.canConnect(): raise Exception('Error connecting to database.') warnings = [] errors = [] # skip if not forced and if database has same version doMigrate = True # check if DB has version already implemented dbVersion = None hasVersion = dbConn.execute(''' SELECT EXISTS ( SELECT FROM information_schema.tables WHERE table_schema = 'aide_admin' AND table_name = 'version' ) AS hasVersion; ''', None, 1) if hasVersion[0]['hasversion']: # check DB version dbVersion = dbConn.execute('SELECT version FROM aide_admin.version;', None, 1) if dbVersion is not None and len(dbVersion): dbVersion = dbVersion[0]['version'] needsUpdate = version.compare_versions(version.AIDE_VERSION, dbVersion) if needsUpdate is not None: if needsUpdate < 0: # running an older version of AIDE with a newer DB version warnings.append(f'WARNING: local AIDE version ({version.AIDE_VERSION}) is older than the one in the database ({dbVersion}); please update your installation.') elif needsUpdate == 0: doMigrate = False else: doMigrate = True if not doMigrate and not forceMigrate: return warnings, errors # bring all projects up-to-date (if registered within AIDE) projects = dbConn.execute('SELECT shortname FROM aide_admin.project;', None, 'all') if projects is not None and len(projects): # get all schemata and check if project still exists schemata = dbConn.execute('SELECT schema_name FROM information_schema.schemata', None, 'all') if schemata is not None and len(schemata): schemata = set([s['schema_name'].lower() for s in schemata]) for p in projects: try: pName = p['shortname'] # check if project still exists if not pName.lower() in schemata: warnings.append(f'WARNING: project "{pName}" is registered but does not exist in database.') #TODO: option to auto-remove? continue # special modification for CNN-to-labelclass map: drop only dep. on version (remove ancient tests) if version.compare_versions(version.AIDE_VERSION, dbVersion) in (-1, None): dbConn.execute(sql.SQL('DROP TABLE IF EXISTS {};').format( sql.Identifier(pName, 'cnn_labelclass') ), None) # make modifications one at a time for mod in MODIFICATIONS_sql: dbConn.execute(mod.format(schema=pName), None, None) # pre-official 2.0: mark existing CNN states as "labelclass_autoupdate" (as this was the default behavior) if version.compare_versions(dbVersion, '2.0.210514') == -1: dbConn.execute(sql.SQL(''' UPDATE {} SET labelclass_autoupdate = TRUE; ''').format(sql.Identifier(pName, 'cnnstate')), None) except Exception as e: errors.append(str(e)) else: warnings.append('WARNING: no project schemata found within database.') else: warnings.append('WARNING: no project registered within AIDE.') # update DB version accordingly dbConn.execute(''' DELETE FROM aide_admin.version; INSERT INTO aide_admin.version (version) VALUES (%s); ''', (version.AIDE_VERSION, )) return warnings, errors
instance = instances[key] if moduleClass.__class__.__name__ == instance.__class__.__name__: raise Exception( 'Module {} already launched on this server.'.format( moduleClass.__class__.__name__)) # load configuration config = Config() # check if config file points to unmigrated v1 project dbConnector = Database(config) hasAdminTable = dbConnector.execute( ''' SELECT EXISTS ( SELECT FROM information_schema.tables WHERE table_schema = 'aide_admin' AND table_name = 'project' ); ''', None, 1) if not hasAdminTable[0]['exists']: # not (yet) migrated, raise Exception with instructions to ensure compatibility print(f''' The current installation of AIDE: database host: {config.getProperty('Database', 'host')} database name: {config.getProperty('Database', 'name')} schema: {config.getProperty('Database', 'schema', str, '(not specified)')} points to an installation of the legacy AIDE v1. If you wish to continue using AIDE v2, you have to upgrade the project accordingly. For instructions to do so, see here: https://github.com/microsoft/aerial_wildlife_detection/blob/multiProject/doc/upgrade_from_v1.md
help='Target filename for the model.') args = parser.parse_args() # setup print('Setup...') if not 'AIDE_CONFIG_PATH' in os.environ: os.environ['AIDE_CONFIG_PATH'] = str(args.settings_filepath) from util.configDef import Config from modules import Database config = Config() # setup DB connection dbConn = Database(config) if dbConn.connectionPool is None: raise Exception('Error connecting to database.') dbSchema = config.getProperty('Database', 'schema') # get state dict print('Retrieving model state...') stateDict_raw = dbConn.execute( 'SELECT statedict FROM {schema}.cnnstate WHERE partial IS FALSE ORDER BY timecreated DESC LIMIT 1;' .format(schema=config.getProperty('Database', 'schema')), None, 1) # convert from bytes and save to disk print('Saving model state...') stateDict_parsed = io.BytesIO(stateDict_raw[0]['statedict']) stateDict_parsed = torch.load(stateDict_parsed, map_location=lambda storage, loc: storage) torch.save(stateDict_parsed, open(args.target_file, 'wb'))
if not imgBaseDir.endswith('/'): imgBaseDir += '/' # parse class names and indices if args.label_folder is not None: with open(os.path.join(args.label_folder, 'classes.txt'),'r') as f: lines = f.readlines() for idx, line in enumerate(lines): className = line.strip() # push to database dbConn.execute(''' INSERT INTO {}.LABELCLASS (name, idx) VALUES ( %s, %s ) ON CONFLICT (name) DO NOTHING; '''.format(dbSchema), (className,idx,)) # prepare insertion SQL string if args.annotation_type == 'annotation': sql = ''' INSERT INTO {}.ANNOTATION (username, image, timeCreated, timeRequired, segmentationmask, width, height) VALUES( '{}', (SELECT id FROM {}.IMAGE WHERE filename LIKE %s), (TIMESTAMP %s), -1, %s, %s,
parser.add_argument('--settings_filepath', type=str, default='config/settings.ini', const=1, nargs='?', help='Manual specification of the directory of the settings.ini file; only considered if environment variable unset (default: "config/settings.ini").') args = parser.parse_args() if not 'AIDE_CONFIG_PATH' in os.environ: os.environ['AIDE_CONFIG_PATH'] = args.settings_filepath config = Config() dbConn = Database(config) # read SQL skeleton with open(os.path.join(os.getcwd(), 'setup/db_create.sql'), 'r') as f: sql = f.read() # fill in placeholders sql = sql.replace('&user', config.getProperty('Database', 'user')) # run SQL dbConn.execute(sql, None, None) # add admin user sql = ''' INSERT INTO aide_admin.user (name, email, hash, issuperuser) VALUES (%s, %s, %s, %s) ''' adminPass = config.getProperty('Project', 'adminPassword') uHandler = UserHandling.backend.middleware.UserMiddleware(config) adminPass = uHandler._create_hash(adminPass.encode('utf8')) values = (config.getProperty('Project', 'adminName'), config.getProperty('Project', 'adminEmail'), adminPass, True,) dbConn.execute(sql, values, None)
imgBaseDir += os.sep # parse class names and indices if args.label_folder is not None: classdef = {} with open(os.path.join(args.label_folder, 'classes.txt'), 'r') as f: lines = f.readlines() for idx, line in enumerate(lines): className = line.strip() # push to database dbConn.execute( sql.SQL(''' INSERT INTO {id_lc} (name) VALUES ( %s ) ON CONFLICT (name) DO NOTHING; ''').format(id_lc=sql.Identifier(args.project, 'labelclass')), (className, )) # get newly assigned index returnVal = dbConn.execute( sql.SQL(''' SELECT id FROM {id_lc} WHERE name LIKE %s''').format( id_lc=sql.Identifier(args.project, 'labelclass')), (className + '%', ), 1) classdef[idx] = returnVal[0]['id'] # prepare insertion SQL string if args.annotation_type == 'annotation':
def assemble_server(verbose_start=True, check_v1_config=True, migrate_database=True, force_migrate=False, passive_mode=False): # force verbosity if any of the pre-flight checks is enabled verbose_start = any((verbose_start, check_v1_config, migrate_database)) instance_args = os.environ['AIDE_MODULES'].split(',') if verbose_start: configPath = os.environ['AIDE_CONFIG_PATH'] aideModules = ', '.join(instance_args) print(f'''\033[96m ################################# version {AIDE_VERSION} ### #### ######## ######## ## ## ## ## ## ## {platform.platform()} ## ## ## ## ## ## ## ## ## ## ## ###### [config] ######### ## ## ## ## .> {configPath} ## ## ## ## ## ## ## ## #### ######## ######## [modules] .> {aideModules} #################################\033[0m ''') statusOffset = LogDecorator.get_ljust_offset() # load configuration config = Config(None, verbose_start) bottle.BaseRequest.MEMFILE_MAX = 1024**3 #TODO: make hyperparameter in config? # connect to database dbConnector = Database(config, verbose_start) if check_v1_config: # check if config file points to unmigrated v1 project print('Checking database...'.ljust(statusOffset), end='') hasAdminTable = dbConnector.execute( ''' SELECT EXISTS ( SELECT FROM information_schema.tables WHERE table_schema = 'aide_admin' AND table_name = 'project' ); ''', None, 1) if not hasAdminTable[0]['exists']: # not (yet) migrated, raise Exception with instructions to ensure compatibility LogDecorator.print_status('fail') print(f''' The current installation of AIDE: database host: {config.getProperty('Database', 'host')} database name: {config.getProperty('Database', 'name')} schema: {config.getProperty('Database', 'schema', str, '(not specified)')} points to an installation of the legacy AIDE v1. If you wish to continue using AIDE v2, you have to upgrade the project accordingly. For instructions to do so, see here: https://github.com/microsoft/aerial_wildlife_detection/blob/multiProject/doc/upgrade_from_v1.md ''') sys.exit(1) else: LogDecorator.print_status('ok') # check if projects have been migrated print('Checking projects...'.ljust(statusOffset), end='') dbSchema = config.getProperty('Database', 'schema', str, None) if dbSchema is not None: isMigrated = dbConnector.execute( ''' SELECT COUNT(*) AS cnt FROM aide_admin.project WHERE shortname = %s; ''', (dbSchema, ), 1) if isMigrated is not None and len( isMigrated) and isMigrated[0]['cnt'] == 0: LogDecorator.print_status('warn') print(f''' WARNING: the selected configuration .ini file ("{os.environ['AIDE_CONFIG_PATH']}") points to a project that has not yet been migrated to AIDE v2. Details: database host: {config.getProperty('Database', 'host')} database name: {config.getProperty('Database', 'name')} schema: {dbSchema} If you wish to continue using AIDE v2 for this project, you have to upgrade it to v2 accordingly. For instructions to do so, see here: https://github.com/microsoft/aerial_wildlife_detection/blob/multiProject/doc/upgrade_from_v1.md ''') else: LogDecorator.print_status('ok') else: LogDecorator.print_status('ok') if migrate_database: # bring AIDE up-to-date print('Updating database...'.ljust(statusOffset), end='') warnings, errors = migrate_aide(force_migrate) if len(warnings) or len(errors): if len(errors): LogDecorator.print_status('fail') else: LogDecorator.print_status('warn') print( f'Warnings and/or errors occurred while updating AIDE to the latest version ({AIDE_VERSION}):' ) print('\nWarnings:') for w in warnings: print(f'\t"{w}"') print('\nErrors:') for e in errors: print(f'\t"{e}"') if len(errors): sys.exit(2) else: LogDecorator.print_status('ok') # prepare bottle app = Bottle() # parse requested instances instances = {} # "singletons" dbConnector = REGISTERED_MODULES['Database'](config, verbose_start) userHandler = REGISTERED_MODULES['UserHandler'](config, app, dbConnector) taskCoordinator = REGISTERED_MODULES['TaskCoordinator'](config, app, dbConnector, verbose_start) taskCoordinator.addLoginCheckFun(userHandler.checkAuthenticated) for i in instance_args: moduleName = i.strip() if moduleName == 'UserHandler': continue moduleClass = REGISTERED_MODULES[moduleName] # verify _verify_unique(instances, moduleClass) # create instance if moduleName == 'AIController': instance = moduleClass(config, app, dbConnector, taskCoordinator, verbose_start, passive_mode) else: instance = moduleClass(config, app, dbConnector, verbose_start) instances[moduleName] = instance # add authentication functionality if hasattr(instance, 'addLoginCheckFun'): instance.addLoginCheckFun(userHandler.checkAuthenticated) # launch project meta modules if moduleName == 'LabelUI': aideAdmin = REGISTERED_MODULES['AIDEAdmin'](config, app, dbConnector, verbose_start) aideAdmin.addLoginCheckFun(userHandler.checkAuthenticated) reception = REGISTERED_MODULES['Reception'](config, app, dbConnector) reception.addLoginCheckFun(userHandler.checkAuthenticated) configurator = REGISTERED_MODULES['ProjectConfigurator']( config, app, dbConnector) configurator.addLoginCheckFun(userHandler.checkAuthenticated) statistics = REGISTERED_MODULES['ProjectStatistics'](config, app, dbConnector) statistics.addLoginCheckFun(userHandler.checkAuthenticated) elif moduleName == 'FileServer': from modules.DataAdministration.backend import celery_interface as daa_int elif moduleName == 'AIController': from modules.AIController.backend import celery_interface as aic_int # launch model marketplace with AIController modelMarketplace = REGISTERED_MODULES['ModelMarketplace']( config, app, dbConnector, taskCoordinator) modelMarketplace.addLoginCheckFun(userHandler.checkAuthenticated) elif moduleName == 'AIWorker': from modules.AIWorker.backend import celery_interface as aiw_int # launch globally required modules dataAdmin = REGISTERED_MODULES['DataAdministrator'](config, app, dbConnector, taskCoordinator) dataAdmin.addLoginCheckFun(userHandler.checkAuthenticated) staticFiles = REGISTERED_MODULES['StaticFileServer'](config, app, dbConnector) staticFiles.addLoginCheckFun(userHandler.checkAuthenticated) if verbose_start: print('\n') return app
imgBaseDir += os.sep # parse class names and indices if args.label_folder is not None: with open(os.path.join(args.label_folder, 'classes.txt'), 'r') as f: lines = f.readlines() for idx, line in enumerate(lines): className = line.strip() # push to database dbConn.execute( sql.SQL(''' INSERT INTO {id_lc} (name, idx) VALUES ( %s, %s ) ON CONFLICT (name) DO NOTHING; ''').format(id_lc=sql.Identifier(args.project, 'labelclass')), ( className, idx, )) # prepare insertion SQL string if args.annotation_type == 'annotation': # get username usernames = dbConn.execute( ''' SELECT username FROM aide_admin.authentication WHERE project = %s AND isAdmin = TRUE
imgFiles = list(imgFiles) for i in tqdm(imgFiles): if os.path.isdir(i): continue _, ext = os.path.splitext(i) if ext.lower() not in VALID_IMAGE_EXTENSIONS: continue baseName = i.replace(imgBaseDir, '') imgs.add(baseName) # ignore images that are already in database print('Filter images already in database...') imgs_existing = dbConn.execute( sql.SQL(''' SELECT filename FROM {}; ''').format(sql.Identifier(project, 'image')), None, 'all') if imgs_existing is not None: imgs_existing = set([i['filename'] for i in imgs_existing]) else: imgs_existing = set() imgs = list(imgs.difference(imgs_existing)) imgs = [(i, ) for i in imgs] # push image to database print('Adding to database...') dbConn.insert( sql.SQL(''' INSERT INTO {} (filename) VALUES %s;