def __init__(self, table_name, database_url, record_schema, rebuild=True, verbose=False):
        
        '''
            the initialization method for the sqlClient class
            
        :param table_name: string with name for table of records
        :param database_url: string with unique resource identifier to database
        :param record_schema: dictionary with jsonmodel valid schema for records
        :param rebuild: [optional] boolean to rebuild table with schema changes 
        :param verbose: [optional] boolean to enable database logging to stdout
        
        NOTE:   init will automatically update the table schema if the record schema
                differs from the existing table. in order to change the name of a field
                without losing the data associated with the old name, add the old key 
                name to the field's metadata in the schema declaration:
                components['.new_field']['field_metadata']['replace_key'] = '.old_field'
        
        NOTE:   to create a new database, use a tool SQL Workbench
                https://data36.com/install-sql-workbench-postgresql/
                SET AUTOCOMMIT = ON
                create database mydb
        '''
        
        title = '%s.__init__' % self.__class__.__name__
        
    # construct fields
        from jsonmodel.validators import jsonModel
        self.fields = jsonModel(self._class_fields)
    
    # validate inputs
        input_fields = {
            'table_name': table_name,
            'database_url': database_url,
            'record_schema': record_schema
        }
        for key, value in input_fields.items():
            object_title = '%s(%s=%s)' % (title, key, str(value))
            self.fields.validate(value, '.%s' % key, object_title)
    
    # validate database url
        import re
        url_split = re.compile('://+')
        url_error = '%s(database_url="..." must have the format: dialect://user:password@host:port or dialect:///../path/to/local.db' % title
        if not url_split.findall(database_url):
            raise ValueError(url_error)
        sql_dialect, db_path = url_split.split(database_url)
        if sql_dialect == 'sqlite':
            from os import path
            try:
                db_root, db_file = path.split(db_path)
            except:
                raise ValueError(url_error)
            if db_root:
                if not path.exists(db_root):
                    from os import makedirs
                    makedirs(db_root)
        else:
            db_cred, db_file = db_path.split('@')
        
    # construct verbose method
        self.printer_on = True
        def _printer(msg, flush=False):
            if verbose and self.printer_on:
                if flush:
                    print(msg, end='', flush=True)
                else:
                    print(msg)
        self.printer = _printer
    
    # construct record model
        self.record_schema = record_schema
        self.model = jsonModel(record_schema)
    
    # define item key pattern
        import re
        self.item_key = re.compile('\[0\]')

    # TODO add method to create database if not there
    # https://stackoverflow.com/questions/6506578/how-to-create-a-new-database-using-sqlalchemy
        
    # construct database session
        self.engine = create_engine(database_url, echo=verbose)
        self.session = self.engine.connect()
        self.table_name = table_name
        self.database_name = db_file
        self.database_url = database_url
        self.verbose = verbose
        self.database_dialect = sql_dialect
    
    # verify schema criteria 
        for key, value in self.model.keyMap.items():
            if not self.database_dialect in ('sqlite', 'postgres'):
        # verify max length for string fields for certain sql dialects
                if value['value_datatype'] == 'string':
                    if not 'max_length' in value.keys():
                        raise ValueError('%s database requires a "max_length" be declared for string field %s in record_schema.' % (self.database_dialect, key))
        # verify no null datatype declarations
            if value['value_datatype'] == 'null':
                raise ValueError('%s(record_schema={...}) field %s cannot have the null datatype.' % (title, key))
    
    # # ORM construct
    #     from sqlalchemy.orm import sessionmaker
    #     from sqlalchemy.ext.declarative import declarative_base
    #     self.engine = create_engine(database_url, echo=verbose)
    #     dbSession = sessionmaker(bind=self.engine)
    #     self.session = dbSession()
    #     self.base = declarative_base()
    #     class RecordObject(self.base):
    #         __tablename__ = table_name
    #         id = Column(String, primary_key=True)
    #     self.base.metadata.create_all(self.engine)
    
    # construct lab id class
        from labpack.records.id import labID
        self.labID = labID
    
    # construct table metadata
        from sqlalchemy import Table, MetaData
        metadata = MetaData()
    
    # determine if there is a prior table
        migration_complete = True
        prior_name = self.table_name
        table_pattern = '%s_old_\w{24}' % self.table_name
        table_regex = re.compile(table_pattern)
        self.tables = self.engine.table_names()
        for table in self.tables:
            if table_regex.findall(table):
                prior_name = table
                migration_complete = False
                break
        prior_columns = self._extract_columns(prior_name)
        
    # construct new table object
        add_sequence = False
        current_columns = self._parse_columns()
        if not 'id' in current_columns.keys():
            current_columns['id'] = [ 'id', 'string', '', None ]
            if not self.database_dialect in ('sqlite', 'postgres'):
                current_columns['id'] = [ 'id', 'string', '', 24 ]
            self.record_schema['schema']['id'] = ''
            self.model = jsonModel(self.record_schema)
        elif isinstance(current_columns['id'], int):
            if not current_columns['id']:
                add_sequence = True
        table_args = [ self.table_name, metadata ]
    # TODO add sequence for integer ids
    # http://docs.sqlalchemy.org/en/latest/core/defaults.html#sqlalchemy.schema.Sequence
        column_args = self._construct_columns(current_columns)
        table_args.extend(column_args)
        self.table = Table(*table_args)
    
    # process table updates
        if prior_columns:
        
        # determine columns to add, remove, rename and change properties
            add_columns, remove_columns, rename_columns, retype_columns, resize_columns = self._compare_columns(current_columns, prior_columns)
        
        # define update functions
        # https://stackoverflow.com/questions/7300948/add-column-to-sqlalchemy-table
            def _add_column(column_key):
                column = getattr(self.table.c, column_key)
                column_name = column.key
                if column_name.find('.') > -1:
                    column_name = '"%s"' % column_name
                column_type = column.type.compile(self.engine.dialect)
                self.engine.execute('ALTER TABLE %s ADD COLUMN %s %s' % (self.table_name, column_name, column_type))
        
            def _remove_column(column):
                column_name = column.compile(dialect=self.engine.dialect)
                self.engine.execute('ALTER TABLE %s DROP COLUMN %s' % (self.table_name, column_name))
        
        # update table schema
            if remove_columns or rename_columns or retype_columns or resize_columns:
                if not rebuild:
                    raise ValueError('%s table in %s database must be rebuilt in order to update to desired record_schema. try: rebuild=True' % (self.table_name, self.database_name))
                else:
                    new_name = self.table_name
                    old_name = '%s_old_%s' % (self.table_name, labID().id24.replace('-','_').lower())
                    if not migration_complete:
                        old_name = prior_name
                    self._rebuild_table(new_name, old_name, current_columns, prior_columns)
            
            elif not migration_complete:
                print('Update of %s table previously interrupted...' % self.table_name)
                self._rebuild_table(self.table_name, prior_name, current_columns, prior_columns)
                
            elif add_columns:
                column_names = []
                for column_key in add_columns.keys():
                    _add_column(column_key)
                    column_names.append(column_key)
                from labpack.parsing.grammar import join_words
                plural = ''
                if len(column_names) > 1:
                    plural = 's'
                print('%s column%s added to table %s' % (join_words(column_names), plural, self.table_name))
              
    # or create new table
        else:                
            self.table.create(self.engine)                    
            print('%s table created in %s database.' % (self.table_name, self.database_name))
    
    # add tables property with list of tables in database
        self.tables = self.engine.table_names()
def __init__ ( self , table_name , database_url , record_schema , rebuild = True , verbose = False ) : title = '%s.__init__' % self . __class__ . __name__ from jsonmodel . validators import jsonModel self . fields = jsonModel ( self . _class_fields ) input_fields = { 'table_name' : table_name , 'database_url' : database_url , 'record_schema' : record_schema } for key , value in input_fields . items ( ) : object_title = '%s(%s=%s)' % ( title , key , str ( value ) ) self . fields . validate ( value , '.%s' % key , object_title ) import re url_split = re . compile ( '://+' ) url_error = '%s(database_url="..." must have the format: dialect://user:password@host:port or dialect:///../path/to/local.db' % title if not url_split . findall ( database_url ) : raise ValueError ( url_error ) sql_dialect , db_path = url_split . split ( database_url ) if sql_dialect == 'sqlite' : from os import path try : db_root , db_file = path . split ( db_path ) except : raise ValueError ( url_error ) if db_root : if not path . exists ( db_root ) : from os import makedirs makedirs ( db_root ) else : db_cred , db_file = db_path . split ( '@' ) self . printer_on = True def _printer ( msg , flush = False ) : if verbose and self . printer_on : if flush : print ( msg , end = '' , flush = True ) else : print ( msg ) self . printer = _printer self . record_schema = record_schema self . model = jsonModel ( record_schema ) import re self . item_key = re . compile ( '\[0\]' ) self . engine = create_engine ( database_url , echo = verbose ) self . session = self . engine . connect ( ) self . table_name = table_name self . database_name = db_file self . database_url = database_url self . verbose = verbose self . database_dialect = sql_dialect for key , value in self . model . keyMap . items ( ) : if not self . database_dialect in ( 'sqlite' , 'postgres' ) : if value [ 'value_datatype' ] == 'string' : if not 'max_length' in value . keys ( ) : raise ValueError ( '%s database requires a "max_length" be declared for string field %s in record_schema.' % ( self . database_dialect , key ) ) if value [ 'value_datatype' ] == 'null' : raise ValueError ( '%s(record_schema={...}) field %s cannot have the null datatype.' % ( title , key ) ) from labpack . records . id import labID self . labID = labID from sqlalchemy import Table , MetaData metadata = MetaData ( ) migration_complete = True prior_name = self . table_name table_pattern = '%s_old_\w{24}' % self . table_name table_regex = re . compile ( table_pattern ) self . tables = self . engine . table_names ( ) for table in self . tables : if table_regex . findall ( table ) : prior_name = table migration_complete = False break prior_columns = self . _extract_columns ( prior_name ) add_sequence = False current_columns = self . _parse_columns ( ) if not 'id' in current_columns . keys ( ) : current_columns [ 'id' ] = [ 'id' , 'string' , '' , None ] if not self . database_dialect in ( 'sqlite' , 'postgres' ) : current_columns [ 'id' ] = [ 'id' , 'string' , '' , 24 ] self . record_schema [ 'schema' ] [ 'id' ] = '' self . model = jsonModel ( self . record_schema ) elif isinstance ( current_columns [ 'id' ] , int ) : if not current_columns [ 'id' ] : add_sequence = True table_args = [ self . table_name , metadata ] column_args = self . _construct_columns ( current_columns ) table_args . extend ( column_args ) self . table = Table ( * table_args ) if prior_columns : add_columns , remove_columns , rename_columns , retype_columns , resize_columns = self . _compare_columns ( current_columns , prior_columns ) def _add_column ( column_key ) : column = getattr ( self . table . c , column_key ) column_name = column . key if column_name . find ( '.' ) > - 1 : column_name = '"%s"' % column_name column_type = column . type . compile ( self . engine . dialect ) self . engine . execute ( 'ALTER TABLE %s ADD COLUMN %s %s' % ( self . table_name , column_name , column_type ) ) def _remove_column ( column ) : column_name = column . compile ( dialect = self . engine . dialect ) self . engine . execute ( 'ALTER TABLE %s DROP COLUMN %s' % ( self . table_name , column_name ) ) if remove_columns or rename_columns or retype_columns or resize_columns : if not rebuild : raise ValueError ( '%s table in %s database must be rebuilt in order to update to desired record_schema. try: rebuild=True' % ( self . table_name , self . database_name ) ) else : new_name = self . table_name old_name = '%s_old_%s' % ( self . table_name , labID ( ) . id24 . replace ( '-' , '_' ) . lower ( ) ) if not migration_complete : old_name = prior_name self . _rebuild_table ( new_name , old_name , current_columns , prior_columns ) elif not migration_complete : print ( 'Update of %s table previously interrupted...' % self . table_name ) self . _rebuild_table ( self . table_name , prior_name , current_columns , prior_columns ) elif add_columns : column_names = [ ] for column_key in add_columns . keys ( ) : _add_column ( column_key ) column_names . append ( column_key ) from labpack . parsing . grammar import join_words plural = '' if len ( column_names ) > 1 : plural = 's' print ( '%s column%s added to table %s' % ( join_words ( column_names ) , plural , self . table_name ) ) else : self . table . create ( self . engine ) print ( '%s table created in %s database.' % ( self . table_name , self . database_name ) ) self . tables = self . engine . table_names ( )
the initialization method for the sqlClient class
            
        :param table_name: string with name for table of records
        :param database_url: string with unique resource identifier to database
        :param record_schema: dictionary with jsonmodel valid schema for records
        :param rebuild: [optional] boolean to rebuild table with schema changes 
        :param verbose: [optional] boolean to enable database logging to stdout
        
        NOTE:   init will automatically update the table schema if the record schema
                differs from the existing table. in order to change the name of a field
                without losing the data associated with the old name, add the old key 
                name to the field's metadata in the schema declaration:
                components['.new_field']['field_metadata']['replace_key'] = '.old_field'
        
        NOTE:   to create a new database, use a tool SQL Workbench
                https://data36.com/install-sql-workbench-postgresql/
                SET AUTOCOMMIT = ON
                create database mydb