web2py-service/modules/kitcube.py

import re
from gluon import DAL, Field
# from gluon.dal import Row, Rows

dal_field_type = dict(
    id='id',
    bit='boolean',
    tinyint='boolean',
    int='integer',
    bigint='bigint',
    double='double',
    float='double',
    mediumblob='blob',
    blob='blob',
    varchar='string',
    text='text'
)

def search_objects(objs, **kargs):
    ''' search list of objects, matching supplied fields'''
    return filter(lambda s: all([ s[k] == v for k,v in kargs.iteritems() ]), objs)
def search_objects2(axes, sensor_name, module_mark):
    for ax in axes:
        if (ax['sensor_name'] == sensor_name) and (ax['module_mark'] == module_mark):
            return ax['unit']
    return None

        
class DBConnector(object):
    def __init__(self, key, host, database):
        self._modules = None
        self._views = None
        self._module_pattern = None
        self.axis_list = 'Axislist'
        self.sensor_list = 'Sensorlist'
        self.key = key
        self.host = host
        self.database = database
        self.uri = "%s/%s" % (self.host, self.database)
        self.dal = DAL( self.uri, migrate=False, pool_size=10, lazy_tables=True)
    def extract(self):
        self._modules = self.extract_modules()
        self._sensors = self.extract_sensors(self._modules)
    def connect(self):
        self.extract()
        for mod in self._modules:
            s = search_objects(self._sensors, module_name=mod['name'])
            f = [ Field(k['name'], type=k['type'], length=k['length'], rname=k['rname']) for k in s ]
            self.dal.define_table(mod['name'], *f)
    def reconnect(self):
        self.dal._adapter.reconnect()
    def query(self, module, fields=None, len=1):
        db = self.dal
        if module not in self._modules:
            rows = []
        if fields is None:
            rows = db(db[module].id>0).select(db[module].ALL, orderby='id DESC', limitby=(0,len))
        else:
            fields = [ db[module][f] for f in fields ]
            rows = db(db[module].id>0).select(db[module].usec, *fields, orderby='id DESC', limitby=(0,len))
        return {module: rows.as_list()[::-1]}
    def query_view(self, view, len=1):
        res = {}
        for mod, cols in view:
            res.update(self.query(mod, cols, len=len))
        return res
    @property
    def module_pattern(self):
        return self._module_pattern
    @module_pattern.setter
    def module_pattern(self, value):
        self._module_pattern = re.compile(value)
    @property
    def modules(self):
        return self._modules
    @modules.setter
    def modules(self, value):
        self._modules = value
    @property
    def sensors(self):
        return self._sensors
    @property
    def views(self):
        return self._views
    @views.setter
    def views(self, value):
        self._views = value
    
    
    
class KITCube(DBConnector):
    def __init__(self, key, host, database, ignore_fields=[], field_type_fix={}):
        self.ignore_fields = ignore_fields
        self.field_type_fix = field_type_fix
        super(KITCube, self).__init__(key, host, database)

    def extract_modules(self):
        ''' extract modules 
            extrace from database when self._modules is not defined,
            otherwise filter self._modules, 
            
            Returns: 
                [{'name': <name>, 'mark': <module number>}, ... ]
        '''
        tables = [ tname for tname, in self.dal.executesql('show tables') ]
        if self._modules is None:
            matches = [ (tname, self.module_pattern.match(tname)) for tname in tables ]
            modules = [ dict(name=tname, mark=int(m.group('mod_mark'))) for tname, m in matches if m ]
        else:
            modules = [ t for t in self._modules if t in tables ]
        return modules

    def extract_sensors(self, modules):
        ''' extract sensors from database

            Args: 
                modules: list of moudles to be extracted
            
            Returns:
                [ {
                    'name': <sensor name>,       # dots in name is coverted to underscore
                    'rname': <sensor rname>,     # original name from db, if it contains dots
                    'type': <sensor type>,
                    'length': <length>,
                    'unit': <unit>,
                    'module_mark': 'module_mark'
                   },
                   ...
                   ...
                ]
        '''
        fld_type_pattern = re.compile('(?P<fld_type>\w+)\((?P<fld_length>\d*)\)')
        sensor_units = self.extract_units()
        sensors = []

        for mod in modules:
            cols = self.dal.executesql('show columns from ' + mod['name'])
            for fld_name, fld_type, _, fld_key, _, _ in cols:
                if '.' in fld_name:
                    rname = fld_name
                    fld_name = fld_name.replace('.', '_')
                else:
                    rname = None
                fld_name_long = mod['name'] + '.' + fld_name

                if fld_key == 'PRI':
                    fld_type = 'id'
                if fld_name_long in self.field_type_fix.keys():
                    fld_type = self.field_type_fix[fld_name_long]

                m = fld_type_pattern.match(fld_type)
                if m:
                    fld_type = m.group('fld_type')
                    fld_length = m.group('fld_length')
                else:
                    fld_length = None

                if fld_name_long in self.ignore_fields:
                    continue                
                if fld_type in ['mediumblob', 'blob']:
                    continue

                s = search_objects(sensor_units, module=mod['mark'], name=rname or fld_name)
                try:
                    unit = s[0]['unit']
                except:
                    unit = None

                if rname is not None:
                    rname = '`' + rname + '`'

                sensors.append(dict(name=fld_name, rname=rname,
                                    type=fld_type, length=fld_length, 
                                    unit=unit, module_name=mod['name']))
        return sensors

    def extract_units(self):
        self.dal.define_table(self.axis_list,   
                            Field('id'), Field('unit', 'string'))
        self.dal.define_table(self.sensor_list, 
                            Field('id'), Field('name', 'string'),
                            Field('module', 'integer'),
                            Field('axis', 'reference %s' % self.axis_list))
        
        sensortable = self.dal[self.sensor_list]
        axistable = self.dal[self.axis_list]
        
        records = self.dal(sensortable.axis==axistable.id).select()
        sensor_units = [ dict(name=r[self.sensor_list].name, 
                              module=r[self.sensor_list].module, 
                              unit=r[self.axis_list].unit) for r in records ]
        return sensor_units