def get(self):
args = self.parse_args([{
'name': 'ds_id',
'required': True,
'location': 'args',
'cast': int
}, {
'name': 'tables',
'required': False,
'location': 'args',
'cast': json.loads
}])
tables = args.get('tables')
db = DBMeta(self.user_id)
ds = db.datasource(id=args['ds_id']).single()
connector = Connector(user_id=self.user_id,
type=ds.type,
db=ds.name,
**ds.params)
engine = Engine(connector)
dbmeta = DBMeta(self.user_id)
vtables = dbmeta.vtable(ds_id=args['ds_id']).all()
all_table = engine.tables() + [_.name for _ in vtables]
result = [{
'name': _,
'schema': [_['name'] for _ in engine.schema(_)]
} for _ in all_table if not tables or _ in tables]
self.response(**{'total': len(result), 'schema': result})
def post(self):
args = self.parse_args([
{
'name': 'name',
'required': True,
'location': 'body'
},
{
'name': 'ds_id',
'required': True,
'location': 'body'
},
{
'name': 'rules',
'required': True,
'location': 'body'
},
{
'name': 'type',
'required': True,
'location': 'body',
'default': 'join'
},
])
self.meta = DBMeta(self.user_id)
if args['type'] == 'join':
table_json = self.join_table()
else:
table_json = self.aggr_table()
if not table_json:
return
if self.meta.vtable(name=args['name']).first():
self.response(409, u'已存在名字为 %s 的合表' % args['name'])
return
ds = self.meta.datasource(id=args['ds_id']).single()
connector = Connector(user_id=self.user_id,
type=ds.type,
db=ds.name,
**ds.params)
engine = Engine(connector)
if args['name'] in engine.tables():
self.response(409, u'原始库中已存在表(%s)' % args['name'])
return
vtb = self.meta.vtable(ds_id=args['ds_id'],
name=args['name'],
query=table_json)
vtb.insert()
self.meta.commit()
self.response(message='success')
del self.meta
def post(self):
args = self.parse_args([
{'name': 'name', 'required': True, 'location': 'body'},
{'name': 'type', 'required': True, 'location': 'body'},
{'name': 'params', 'required': True, 'location': 'body'}
])
args['params'] = params(args['type'], **args['params'])
db = DBMeta(self.user_id)
dss = db.datasource(name=args['name']).all()
if dss:
self.response(409, message='已存在名称为 %s 的数据源' % args['name'])
return
if not args['params'].get('filelist'):
connector = Connector(args['type'], db=args['name'], **args['params'])
databases = Engine(connector).databases()
if args['name'] not in databases:
self.response(412, u'数据库中不存在 %s 库' % args['name'])
return
ds = db.datasource(**args)
ds.insert()
db.commit()
self.response(message='success')
def get(self):
args = self.parse_args([{
'name': 'type',
'required': True,
'location': 'args'
}, {
'name': 'params',
'required': True,
'location': 'args'
}])
engine = Engine(
Connector(user_id=self.user_id,
type=args['type'],
**args['params']))
dbs = engine.databases()
self.response(**{'total': len(dbs), 'databases': dbs})
def get(self):
args = self.parse_args([{
'name': 'pageno',
'required': False,
'location': 'args'
}, {
'name': 'pagesize',
'required': False,
'location': 'args'
}, {
'name': 'name',
'required': False,
'location': 'args'
}])
dbmeta = DBMeta(self.user_id)
kwargs = {}
if args.get('name'):
kwargs['name'] = args['name']
datasources = dbmeta.datasource(**kwargs).all()
result = []
for ds in datasources:
item = {
'id': ds.id,
'name': ds.name,
'ctime': ds.ctime,
'utime': ds.utime,
'type': ds.type.upper()
}
connector = Connector(type=ds.type.upper(),
user_id=self.user_id,
db=ds.name,
**ds.params)
if isinstance(connector, ODOConnector):
engine = Engine(connector)
ds.params['filelist'] = engine.tables()
item.update(ds.params)
result.append(item)
self.response(**{"total": len(result), "datasources": result})
def get(self):
args = self.parse_args([{
'name': 'sql',
'required': True,
'location': 'args'
}, {
'name': 'schema',
'required': False,
'location': 'args'
}, {
'name': 'type',
'required': True,
'location': 'args'
}])
tree = yield self.gen_tree(args['sql'])
Visitor(args['type'], args.get('schema')).visit(tree)
query = Query.load(tree, Engine.create(args['type']))
self.response(sql=str(query))
def join_table(self):
wheres = parse_where(self.args['rules'])
ds = self.meta.datasource(id=self.args['ds_id']).single()
engine = Engine(
Connector(user_id=self.user_id,
type=ds.type,
db=ds.name,
**ds.params))
if len(wheres) != 1:
self.response(422, '需要最终合成一张表')
return
tablenames, clause = wheres.items()[0]
columns = []
vtables = self.meta.vtable(ds_id=self.args['ds_id']).all()
vtables = {tb.name: tb.query for tb in vtables}
for name in tablenames:
columns += [
Column(name, _['name']).label('%s.%s' % (name, _['name']))
for _ in self.schema(engine, vtables, name)
]
return Table(*columns).select_from(*tablenames).where(*clause).json()
def get(self):
args = self.parse_args([{
'name': 'ds_id',
'required': True,
'location': 'args'
}, {
'name': 'tables',
'required': True,
'location': 'args',
'cast': json.loads
}, {
'name': 'rules',
'required': False,
'location': 'args',
'cast': json.loads
}])
result = []
rules = args['rules']
tables = args['tables']
dbmeta = DBMeta(self.user_id)
ds = dbmeta.datasource(id=args['ds_id']).single()
connector = Connector(user_id=self.user_id,
type=ds.type,
db=ds.name,
**ds.params)
engine = Engine(connector)
wheres = parse_where(rules)
vtables = dbmeta.vtable(ds_id=self.args['ds_id']).all()
vtables = [(tb.name, tb.query) for tb in vtables]
for tablenames, clause in wheres.items():
columns = []
for name in tablenames:
columns += [
Column(name, _['name']).label('%s.%s' % (name, _['name']))
for _ in engine.schema(name)
]
table_json = Table(*columns).select_from(*tablenames).where(
*clause).json()
q = Query(table=table_json, limit=10)
q.bind(connector)
ret = q.execute()
result.append({
'data': ret.json_data,
'sql': q.sql,
'columns': ret.columns,
'names': tablenames
})
keys = sum(wheres.keys(), ())
tables = [(_, dict(vtables).get(_, _)) for _ in tables
if _ not in keys]
for name, tb in tables:
q = get_query(tb, 10)
q.bind(connector)
ret = q.execute()
result.append({
'data': ret.json_data,
'columns': ret.columns,
'names': [name],
'schema': ret.schema
})
self.response(total=len(result), tables=result)