web2py-service/controllers/sql.py

def index():
    response.generic_patterns = ['*.html']
    db = meta_dbs()
    return locals()

def reset():
    if True:
    # if False:
        info.sql_db.truncate()
        info.sql_module.truncate()
        info.sql_sensor.truncate()
    return locals()


def error():
    return dict(error=session.error)    

def meta_dbs():
    return info(info.sql_db).select()

def meta_modules(db):
    return info(info.sql_db.key==db)\
               (info.sql_module.db_id==info.sql_db.id)\
               .select(info.sql_module.ALL)

def meta_sensors(db, module):
    return info(info.sql_db.key==db)\
               (info.sql_module.name==module)\
               (info.sql_sensor.db_id==info.sql_db.id)\
               (info.sql_sensor.module_id==info.sql_module.id)\
               .select(info.sql_sensor.ALL)

@request.restful()
def api():
    if request.extension is 'html':
        request.extension = 'xml'
    if request.env.http_origin:
        response.headers['Access-Control-Allow-Origin'] = '*'
    response.generic_patterns = ['*.html', '*.xml', '*.json']


    def GET(*args, **kargs):        
        try:
            db_key = args[0]
        except IndexError:
            session.error = 'no database is specified'
            request.extension = 'html'
            redirect('error')

        databases = meta_dbs()

        if db_key not in [d.key for d in databases]:
            session.error = 'database "%s" does not exits' % db_key
            request.extension = 'html'
            redirect('error')
        else:
            modules = [ m.name for m in meta_modules(db_key) ] 

        logger.info(locals().get('modules'))

        try:
            module = args[1]
        except IndexError:
            error = 'Module is not specified'
            # mods = {}
            # for m in modules:
            #     s = meta_sensors(db_key, m.name)
            #     mods[m.name] = [ _s.name for _s in s ]
            mods = { m: [ s.name for s in meta_sensors(db_key, m) ] for m in modules }
            # mods = { m.name : meta_sensors(db_key, m.name) for m in modules } 
            return dict(error=error, modules=mods)

        try:
            module = modules[int(module)]
        except ValueError:
            module = module
        except KeyError:
            session.error = 'Module index out of range: module index = %s' % module
            session.modules = modules
            request.extension = 'html'
            redirect(URL('error'))

        len = int(kargs.get('len', 1))

        db = globals()[db_key]
        db_connector = kitcube[db_key]
        views = db_connector.views

        if module in modules:
            res = db_connector.query(module, len=len)
        elif module in views.keys():
            v = views[module]
            res = db_connector.query_view(v, len=len)
        else:
            session.error = 'Module "%s" does not exits' % module
            session.modules = modules
            request.extension = 'html'
            redirect(URL('error'))

        unit = {}
        if kargs.get('unit', None):
            if module in modules:
                unit[module] = info(info.sql_sensor.module_name==module).select(info.sql_sensor.name, info.sql_sensor.unit)
            else:
                for mod, s in views[module]:
                    rows = info(info.sql_sensor.module_name==mod).select(info.sql_sensor.name, info.sql_sensor.unit)
                    rows.exclude(lambda row: row.name not in s )    
                    unit[mod] = rows

        for m, s in unit.iteritems():
            unit[m] = { _s['name']: _s['unit'] for _s in s }
        
        return dict(data=res, unit=unit)

    return locals()