def listTables(conf,inputs,outputs):
import authenticate.service as auth
if not(auth.is_ftable(inputs["schema"]["value"])):
conf["lenv"]["message"]=zoo._("Unable to identify your parameter as table or field name")
return zoo.SERVICE_FAILED
db=pgConnection(conf,inputs["dataStore"]["value"])
db.parseConf()
if db.connect():
req="select schemaname||'.'||tablename as tablename, tablename as display from pg_tables WHERE schemaname NOT LIKE 'information_schema' AND schemaname NOT LIKE 'pg_%' AND tablename NOT LIKE 'tmp%' AND tablename NOT LIKE 'spatial_ref_sys' AND tablename NOT LIKE 'geometry_columns' "
if inputs.has_key("schema"):
req+="AND schemaname='"+inputs["schema"]["value"]+"'"
req+=" ORDER BY schemaname||'.'||tablename"
res=db.execute(req)
if res:
outputs["Result"]["value"]=json.dumps(res)
return zoo.SERVICE_SUCCEEDED
else:
print >> sys.stderr,"Unable to connect"
return zoo.SERVICE_FAILED
def listTables(conf,inputs,outputs):
import authenticate.service as auth
if not(auth.is_ftable(inputs["schema"]["value"])):
conf["lenv"]["message"]=zoo._("Unable to identify your parameter as table or field name")
return zoo.SERVICE_FAILED
db=pgConnection(conf,inputs["dataStore"]["value"])
db.parseConf()
if db.connect():
req="select schemaname||'.'||tablename as tablename, tablename as display from pg_tables WHERE schemaname NOT LIKE 'information_schema' AND schemaname NOT LIKE 'pg_%' AND tablename NOT LIKE 'spatial_ref_sys' AND tablename NOT LIKE 'geometry_columns' "
if inputs.has_key("schema"):
req+="AND schemaname='"+inputs["schema"]["value"]+"'"
req+=" ORDER BY schemaname||'.'||tablename"
res=db.execute(req)
outputs["Result"]["value"]=json.dumps(res)
return zoo.SERVICE_SUCCEEDED
#return zoo.SERVICE_SUCCEEDED
else:
print >> sys.stderr,"Unable to connect"
return zoo.SERVICE_FAILED
def getTableDescription(conf,inputs,outputs):
import authenticate.service as auth
if not(auth.is_ftable(inputs["table"]["value"])):
conf["lenv"]["message"]=zoo._("Unable to identify your parameter as table or field name")
return zoo.SERVICE_FAILED
db=pgConnection(conf,inputs["dataStore"]["value"])
db.parseConf()
if db.connect():
tmp=inputs["table"]["value"].split('.')
req=getDesc(db.cur,inputs["table"]["value"])
print >> sys.stderr,req
res=db.execute(req)
if res!=False and len(res)>0:
outputs["Result"]["value"]=json.dumps(res)
return zoo.SERVICE_SUCCEEDED
else:
print >> sys.stderr,"unable to run request "+req
return zoo.SERVICE_FAILED
else:
print >> sys.stderr,"Unable to connect"
return zoo.SERVICE_FAILED
def getTableContent(conf, inputs, outputs):
import authenticate.service as auth
if not (auth.is_ftable(inputs["table"]["value"])):
conf["lenv"]["message"] = zoo._("Unable to identify your parameter as table or field name")
return zoo.SERVICE_FAILED
db = pgConnection(conf, inputs["dataStore"]["value"])
db.parseConf()
getTableDescription(conf, inputs, outputs)
tmp = eval(outputs["Result"]["value"].replace("null", "None"))
pkey = 0
geom = []
fields = ""
for i in range(0, len(tmp)):
if tmp[i][3] == "PRI":
pkey = tmp[i][0]
if tmp[i][2] == "geometry":
geom += [i]
if tmp[i][3] == "FOR" and not (inputs.has_key("force")):
input1 = inputs
otbl = inputs["table"]["value"]
inputs["table"]["value"] = tmp[i][4]
getTableDescription(conf, inputs, outputs)
tmp2 = eval(outputs["Result"]["value"].replace("null", "None"))
pkey1 = 0
for j in range(0, len(tmp2)):
if tmp2[j][3] == "PRI":
pkey1 = j
break
hasV = False
for j in range(0, len(tmp2)):
if not (hasV) and (tmp2[j][2].count("char") > 0 or tmp2[j][2].count("text") > 0):
if fields != "":
fields += ","
hasV = True
fields += (
"(SELECT "
+ tmp2[j][1]
+ " FROM "
+ tmp[i][4]
+ " as a WHERE a."
+ tmp2[pkey][1]
+ "="
+ otbl
+ "."
+ tmp[i][1]
+ ")"
)
if not (hasV):
if fields != "":
fields += ","
fields += (
"(SELECT "
+ tmp2[0][1]
+ " FROM "
+ tmp[i][4]
+ " as a WHERE a."
+ tmp2[pkey][1]
+ "="
+ otbl
+ "."
+ tmp[i][1]
+ ")"
)
inputs["table"]["value"] = otbl
else:
if fields != "":
fields += ","
fields += tmp[i][1]
if db.connect():
req = "select count(*) from " + inputs["table"]["value"]
res = db.execute(req)
if res != False:
total = res[0][0]
req = "select "
if inputs.has_key("cols") and inputs["cols"]["value"] != "NULL":
req += inputs["cols"]["value"]
else:
req += fields
req += " from " + inputs["table"]["value"]
if inputs.has_key("clause") and inputs["clause"]["value"] != "NULL":
req += " WHERE " + inputs["clause"]["value"]
if inputs.has_key("sortname") and inputs["sortname"]["value"] != "NULL":
req += " ORDER BY " + inputs["sortname"]["value"] + " " + inputs["sortorder"]["value"]
if inputs.has_key("limit") and inputs["limit"]["value"] != "NULL":
if inputs.has_key("page") and inputs["page"]["value"] != "":
req += " OFFSET " + str((int(inputs["page"]["value"]) - 1) * int(inputs["limit"]["value"]))
page = inputs["page"]["value"]
req += " LIMIT " + inputs["limit"]["value"]
else:
page = 1
req += " LIMIT 10"
print >>sys.stderr, req
res = db.execute(req)
if res != False:
rows = []
for i in range(0, len(res)):
res0 = []
for k in range(0, len(res[i])):
res0 += [str(res[i][k])]
if len(geom) > 0:
for j in range(0, len(geom)):
res0[geom[j]] = "GEOMETRY"
rows += [{"id": res[i][pkey], "cell": res0}]
outputs["Result"]["value"] = json.dumps({"page": page, "total": total, "rows": rows}, ensure_ascii=False)
return zoo.SERVICE_SUCCEEDED
else:
print >>sys.stderr, "unable to run request"
return zoo.SERVICE_FAILED
else:
print >>sys.stderr, "Unable to connect"
return zoo.SERVICE_FAILED
