ChinaGisRestApi: RestDbInterface.py annotate

annotate RestDbInterface.py @ 252:efd2469d1722

geometry-column of tables will be displayed as string

author	fknauft
date	Tue, 20 Sep 2011 11:19:35 +0200
parents	9e34cd7b2e57
children	901c1f745d13

rev	line source
2 61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	1 '''
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	2 Created on 19.5.2010
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	3
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	4 @author: casties
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	5 '''
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	6
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	7 from OFS.Folder import Folder
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	8 from Products.PageTemplates.PageTemplateFile import PageTemplateFile
60 9fdadb60529f working on authentication and authorization casties parents: 58 diff changeset	9 from AccessControl import getSecurityManager, Unauthorized
2 61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	10 from Products.ZSQLExtend import ZSQLExtend
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	11 import logging
15 5e3edf980813 more work on xml import casties parents: 14 diff changeset	12 import re
18 060797795a4d work on json return values casties parents: 17 diff changeset	13 import json
19 78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	14 import time
70 9ec7e32e8ad3 working on maps casties parents: 61 diff changeset	15 import psycopg2
9ec7e32e8ad3 working on maps casties parents: 61 diff changeset	16 # make psycopg use unicode objects
9ec7e32e8ad3 working on maps casties parents: 61 diff changeset	17 import psycopg2.extensions
9ec7e32e8ad3 working on maps casties parents: 61 diff changeset	18 psycopg2.extensions.register_type(psycopg2.extensions.UNICODE)
9ec7e32e8ad3 working on maps casties parents: 61 diff changeset	19 psycopg2.extensions.register_type(psycopg2.extensions.UNICODEARRAY)
2 61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	20
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	21 from zope.interface import implements
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	22 from zope.publisher.interfaces import IPublishTraverse
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	23 from ZPublisher.BaseRequest import DefaultPublishTraverse
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	24
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	25
70 9ec7e32e8ad3 working on maps casties parents: 61 diff changeset	26 def unicodify(s,alternate='latin-1'):
9ec7e32e8ad3 working on maps casties parents: 61 diff changeset	27 """decode str (utf-8 or latin-1 representation) into unicode object"""
9ec7e32e8ad3 working on maps casties parents: 61 diff changeset	28 if not s:
9ec7e32e8ad3 working on maps casties parents: 61 diff changeset	29 return u""
9ec7e32e8ad3 working on maps casties parents: 61 diff changeset	30 if isinstance(s, str):
9ec7e32e8ad3 working on maps casties parents: 61 diff changeset	31 try:
9ec7e32e8ad3 working on maps casties parents: 61 diff changeset	32 return s.decode('utf-8')
9ec7e32e8ad3 working on maps casties parents: 61 diff changeset	33 except:
9ec7e32e8ad3 working on maps casties parents: 61 diff changeset	34 return s.decode(alternate)
9ec7e32e8ad3 working on maps casties parents: 61 diff changeset	35 else:
9ec7e32e8ad3 working on maps casties parents: 61 diff changeset	36 return s
9ec7e32e8ad3 working on maps casties parents: 61 diff changeset	37
9ec7e32e8ad3 working on maps casties parents: 61 diff changeset	38 def utf8ify(s):
9ec7e32e8ad3 working on maps casties parents: 61 diff changeset	39 """encode unicode object or string into byte string in utf-8 representation.
9ec7e32e8ad3 working on maps casties parents: 61 diff changeset	40 assumes string objects to be utf-8"""
9ec7e32e8ad3 working on maps casties parents: 61 diff changeset	41 if not s:
9ec7e32e8ad3 working on maps casties parents: 61 diff changeset	42 return ""
9ec7e32e8ad3 working on maps casties parents: 61 diff changeset	43 if isinstance(s, str):
9ec7e32e8ad3 working on maps casties parents: 61 diff changeset	44 return s
9ec7e32e8ad3 working on maps casties parents: 61 diff changeset	45 else:
9ec7e32e8ad3 working on maps casties parents: 61 diff changeset	46 return s.encode('utf-8')
2 61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	47
16 cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	48 def getTextFromNode(node):
14 05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	49 """get the cdata content of a XML node"""
16 cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	50 if node is None:
14 05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	51 return ""
16 cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	52
cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	53 if isinstance(node, list):
cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	54 nodelist = node
cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	55 else:
cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	56 nodelist=node.childNodes
cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	57
14 05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	58 rc = ""
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	59 for node in nodelist:
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	60 if node.nodeType == node.TEXT_NODE:
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	61 rc = rc + node.data
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	62 return rc
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	63
16 cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	64 def sqlName(s,lc=True):
cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	65 """returns restricted ASCII-only version of string"""
15 5e3edf980813 more work on xml import casties parents: 14 diff changeset	66 if s is None:
5e3edf980813 more work on xml import casties parents: 14 diff changeset	67 return ""
5e3edf980813 more work on xml import casties parents: 14 diff changeset	68
5e3edf980813 more work on xml import casties parents: 14 diff changeset	69 # all else -> "_"
16 cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	70 s = re.sub(r'[^A-Za-z0-9_]','_',s)
15 5e3edf980813 more work on xml import casties parents: 14 diff changeset	71 if lc:
5e3edf980813 more work on xml import casties parents: 14 diff changeset	72 return s.lower()
5e3edf980813 more work on xml import casties parents: 14 diff changeset	73
5e3edf980813 more work on xml import casties parents: 14 diff changeset	74 return s
2 61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	75
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	76
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	77 class RestDbInterface(Folder):
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	78 """Object for RESTful database queries
7 bd52d9445a41 trying to rework casties parents: 5 diff changeset	79 path schema: /db/{schema}/{table}/
2 61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	80 omitting table gives a list of schemas
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	81 omitting table and schema gives a list of schemas
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	82 """
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	83 implements(IPublishTraverse)
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	84
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	85 meta_type="RESTdb"
4 e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly. casties parents: 2 diff changeset	86 manage_options=Folder.manage_options+(
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly. casties parents: 2 diff changeset	87 {'label':'Config','action':'manage_editRestDbInterfaceForm'},
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly. casties parents: 2 diff changeset	88 )
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly. casties parents: 2 diff changeset	89
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly. casties parents: 2 diff changeset	90 # management templates
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly. casties parents: 2 diff changeset	91 manage_editRestDbInterfaceForm=PageTemplateFile('zpt/editRestDbInterface',globals())
2 61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	92
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	93 # data templates
8 a9a49f5765c9 reworking templates and schema casties parents: 7 diff changeset	94 XML_index = PageTemplateFile('zpt/XML_index', globals())
a9a49f5765c9 reworking templates and schema casties parents: 7 diff changeset	95 XML_schema = PageTemplateFile('zpt/XML_schema', globals())
a9a49f5765c9 reworking templates and schema casties parents: 7 diff changeset	96 XML_schema_table = PageTemplateFile('zpt/XML_schema_table', globals())
a9a49f5765c9 reworking templates and schema casties parents: 7 diff changeset	97 HTML_index = PageTemplateFile('zpt/HTML_index', globals())
a9a49f5765c9 reworking templates and schema casties parents: 7 diff changeset	98 HTML_schema = PageTemplateFile('zpt/HTML_schema', globals())
a9a49f5765c9 reworking templates and schema casties parents: 7 diff changeset	99 HTML_schema_table = PageTemplateFile('zpt/HTML_schema_table', globals())
22 1a4b56716902 NEW - # 12: create table and upload data casties parents: 21 diff changeset	100 JSONHTML_index = PageTemplateFile('zpt/JSONHTML_index', globals())
1a4b56716902 NEW - # 12: create table and upload data casties parents: 21 diff changeset	101 JSONHTML_schema = PageTemplateFile('zpt/JSONHTML_schema', globals())
1a4b56716902 NEW - # 12: create table and upload data casties parents: 21 diff changeset	102 JSONHTML_schema_table = PageTemplateFile('zpt/JSONHTML_schema_table', globals())
24 f3811d9a36da start adding checkuploadtable casties parents: 23 diff changeset	103 # JSON_* templates are scripts
45 ed8db63fab4f more refactoring of templates casties parents: 44 diff changeset	104 def JSON_index(self):
30 51db9e78bf98 working on field types casties parents: 29 diff changeset	105 """JSON index function"""
51db9e78bf98 working on field types casties parents: 29 diff changeset	106 self.REQUEST.RESPONSE.setHeader("Content-Type", "application/json")
45 ed8db63fab4f more refactoring of templates casties parents: 44 diff changeset	107 json.dump(self.getListOfSchemas(), self.REQUEST.RESPONSE)
2 61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	108
45 ed8db63fab4f more refactoring of templates casties parents: 44 diff changeset	109 def JSON_schema(self,schema):
30 51db9e78bf98 working on field types casties parents: 29 diff changeset	110 """JSON index function"""
51db9e78bf98 working on field types casties parents: 29 diff changeset	111 self.REQUEST.RESPONSE.setHeader("Content-Type", "application/json")
45 ed8db63fab4f more refactoring of templates casties parents: 44 diff changeset	112 json.dump(self.getListOfTables(schema), self.REQUEST.RESPONSE)
30 51db9e78bf98 working on field types casties parents: 29 diff changeset	113
44 c6c47034d2a4 more refactoring of templates casties parents: 43 diff changeset	114 def JSON_schema_table(self,schema,table):
30 51db9e78bf98 working on field types casties parents: 29 diff changeset	115 """JSON index function"""
51db9e78bf98 working on field types casties parents: 29 diff changeset	116 self.REQUEST.RESPONSE.setHeader("Content-Type", "application/json")
44 c6c47034d2a4 more refactoring of templates casties parents: 43 diff changeset	117 json.dump(self.getTable(schema, table), self.REQUEST.RESPONSE)
30 51db9e78bf98 working on field types casties parents: 29 diff changeset	118
2 61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	119
4 e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly. casties parents: 2 diff changeset	120 def __init__(self, id, title, connection_id=None):
2 61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	121 """init"""
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	122 self.id = id
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	123 self.title = title
4 e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly. casties parents: 2 diff changeset	124 # database connection id
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly. casties parents: 2 diff changeset	125 self.connection_id = connection_id
2 61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	126 # create template folder
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	127 self.manage_addFolder('template')
44 c6c47034d2a4 more refactoring of templates casties parents: 43 diff changeset	128
2 61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	129
44 c6c47034d2a4 more refactoring of templates casties parents: 43 diff changeset	130 def getRestDbUrl(self):
c6c47034d2a4 more refactoring of templates casties parents: 43 diff changeset	131 """returns url to the RestDb instance"""
c6c47034d2a4 more refactoring of templates casties parents: 43 diff changeset	132 return self.absolute_url()
c6c47034d2a4 more refactoring of templates casties parents: 43 diff changeset	133
22 1a4b56716902 NEW - # 12: create table and upload data casties parents: 21 diff changeset	134 def getJsonString(self,object):
1a4b56716902 NEW - # 12: create table and upload data casties parents: 21 diff changeset	135 """returns a JSON formatted string from object"""
1a4b56716902 NEW - # 12: create table and upload data casties parents: 21 diff changeset	136 return json.dumps(object)
4 e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly. casties parents: 2 diff changeset	137
17 ed997e639cfd more upload table casties parents: 16 diff changeset	138 def getCursor(self,autocommit=True):
4 e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly. casties parents: 2 diff changeset	139 """returns fresh DB cursor"""
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly. casties parents: 2 diff changeset	140 conn = getattr(self,"_v_database_connection",None)
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly. casties parents: 2 diff changeset	141 if conn is None:
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly. casties parents: 2 diff changeset	142 # create a new connection object
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly. casties parents: 2 diff changeset	143 try:
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly. casties parents: 2 diff changeset	144 if self.connection_id is None:
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly. casties parents: 2 diff changeset	145 # try to take the first existing ID
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly. casties parents: 2 diff changeset	146 connids = SQLConnectionIDs(self)
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly. casties parents: 2 diff changeset	147 if len(connids) > 0:
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly. casties parents: 2 diff changeset	148 connection_id = connids[0][0]
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly. casties parents: 2 diff changeset	149 self.connection_id = connection_id
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly. casties parents: 2 diff changeset	150 logging.debug("connection_id: %s"%repr(connection_id))
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly. casties parents: 2 diff changeset	151
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly. casties parents: 2 diff changeset	152 da = getattr(self, self.connection_id)
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly. casties parents: 2 diff changeset	153 da.connect('')
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly. casties parents: 2 diff changeset	154 # we copy the DAs database connection
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly. casties parents: 2 diff changeset	155 conn = da._v_database_connection
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly. casties parents: 2 diff changeset	156 #conn._register() # register with the Zope transaction system
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly. casties parents: 2 diff changeset	157 self._v_database_connection = conn
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly. casties parents: 2 diff changeset	158 except Exception, e:
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly. casties parents: 2 diff changeset	159 raise IOError("No database connection! (%s)"%str(e))
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly. casties parents: 2 diff changeset	160
5 7539a9e69365 small fix casties parents: 4 diff changeset	161 cursor = conn.getcursor()
17 ed997e639cfd more upload table casties parents: 16 diff changeset	162 if autocommit:
ed997e639cfd more upload table casties parents: 16 diff changeset	163 # is there a better version to get to the connection?
ed997e639cfd more upload table casties parents: 16 diff changeset	164 cursor.connection.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
ed997e639cfd more upload table casties parents: 16 diff changeset	165
4 e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly. casties parents: 2 diff changeset	166 return cursor
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly. casties parents: 2 diff changeset	167
55 2f4c427dec44 wrangled kml construction into template and method casties parents: 53 diff changeset	168 def getFieldNameMap(self,fields):
2f4c427dec44 wrangled kml construction into template and method casties parents: 53 diff changeset	169 """returns a dict mapping field names to row indexes"""
2f4c427dec44 wrangled kml construction into template and method casties parents: 53 diff changeset	170 map = {}
2f4c427dec44 wrangled kml construction into template and method casties parents: 53 diff changeset	171 i = 0
2f4c427dec44 wrangled kml construction into template and method casties parents: 53 diff changeset	172 for f in fields:
2f4c427dec44 wrangled kml construction into template and method casties parents: 53 diff changeset	173 map[f[0]] = i
2f4c427dec44 wrangled kml construction into template and method casties parents: 53 diff changeset	174 i += 1
2f4c427dec44 wrangled kml construction into template and method casties parents: 53 diff changeset	175
2f4c427dec44 wrangled kml construction into template and method casties parents: 53 diff changeset	176 return map
2f4c427dec44 wrangled kml construction into template and method casties parents: 53 diff changeset	177
17 ed997e639cfd more upload table casties parents: 16 diff changeset	178 def executeSQL(self, query, args=None, hasResult=True, autocommit=True):
4 e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly. casties parents: 2 diff changeset	179 """execute query with args on database and return all results.
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly. casties parents: 2 diff changeset	180 result format: {"fields":fields, "rows":data}"""
16 cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	181 logging.debug("executeSQL query=%s args=%s"%(query,args))
17 ed997e639cfd more upload table casties parents: 16 diff changeset	182 cur = self.getCursor(autocommit=autocommit)
53 f5bfcfa97e7e more JSON store casties parents: 45 diff changeset	183 if args is not None:
f5bfcfa97e7e more JSON store casties parents: 45 diff changeset	184 # make sure args is a list
f5bfcfa97e7e more JSON store casties parents: 45 diff changeset	185 if isinstance(args,basestring):
f5bfcfa97e7e more JSON store casties parents: 45 diff changeset	186 args = (args,)
f5bfcfa97e7e more JSON store casties parents: 45 diff changeset	187
4 e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly. casties parents: 2 diff changeset	188 cur.execute(query, args)
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly. casties parents: 2 diff changeset	189 # description of returned fields
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly. casties parents: 2 diff changeset	190 fields = cur.description
17 ed997e639cfd more upload table casties parents: 16 diff changeset	191 if hasResult:
ed997e639cfd more upload table casties parents: 16 diff changeset	192 # get all data in an array
ed997e639cfd more upload table casties parents: 16 diff changeset	193 data = cur.fetchall()
ed997e639cfd more upload table casties parents: 16 diff changeset	194 cur.close()
55 2f4c427dec44 wrangled kml construction into template and method casties parents: 53 diff changeset	195 #logging.debug("fields: %s"%repr(fields))
58 5ed0769f5ad3 more cleanup in kml generation casties parents: 55 diff changeset	196 #logging.debug("rows: %s"%repr(data))
17 ed997e639cfd more upload table casties parents: 16 diff changeset	197 return {"fields":fields, "rows":data}
ed997e639cfd more upload table casties parents: 16 diff changeset	198 else:
ed997e639cfd more upload table casties parents: 16 diff changeset	199 cur.close()
ed997e639cfd more upload table casties parents: 16 diff changeset	200 return None
16 cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	201
60 9fdadb60529f working on authentication and authorization casties parents: 58 diff changeset	202 def isAllowed(self,action,schema,table,user=None):
9fdadb60529f working on authentication and authorization casties parents: 58 diff changeset	203 """returns if the requested action on the table is allowed"""
9fdadb60529f working on authentication and authorization casties parents: 58 diff changeset	204 if user is None:
9fdadb60529f working on authentication and authorization casties parents: 58 diff changeset	205 user = self.REQUEST.get('AUTHENTICATED_USER',None)
9fdadb60529f working on authentication and authorization casties parents: 58 diff changeset	206 logging.debug("isAllowed action=%s schema=%s table=%s user=%s"%(action,schema,table,user))
9fdadb60529f working on authentication and authorization casties parents: 58 diff changeset	207 # no default policy!
9fdadb60529f working on authentication and authorization casties parents: 58 diff changeset	208 return True
9fdadb60529f working on authentication and authorization casties parents: 58 diff changeset	209
9fdadb60529f working on authentication and authorization casties parents: 58 diff changeset	210
2 61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	211 def publishTraverse(self,request,name):
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	212 """change the traversal"""
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	213 # get stored path
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	214 path = request.get('restdb_path', [])
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	215 logging.debug("publishtraverse: name=%s restdb_path=%s"%(name,path))
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	216
13 e2c73c077533 NEW - # 12: create table and upload data casties parents: 12 diff changeset	217 if name in ("index_html", "PUT"):
2 61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	218 # end of traversal
13 e2c73c077533 NEW - # 12: create table and upload data casties parents: 12 diff changeset	219 if request.get("method") == "POST" and request.get("action",None) == "PUT":
e2c73c077533 NEW - # 12: create table and upload data casties parents: 12 diff changeset	220 # fake PUT by POST with action=PUT
e2c73c077533 NEW - # 12: create table and upload data casties parents: 12 diff changeset	221 name = "PUT"
e2c73c077533 NEW - # 12: create table and upload data casties parents: 12 diff changeset	222
e2c73c077533 NEW - # 12: create table and upload data casties parents: 12 diff changeset	223 return getattr(self, name)
2 61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	224 #TODO: should we check more?
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	225 else:
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	226 # traverse
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	227 if len(path) == 0:
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	228 # first segment
8 a9a49f5765c9 reworking templates and schema casties parents: 7 diff changeset	229 if name == 'db':
2 61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	230 # virtual path -- continue traversing
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	231 path = [name]
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	232 request['restdb_path'] = path
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	233 else:
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	234 # try real path
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	235 tr = DefaultPublishTraverse(self, request)
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	236 ob = tr.publishTraverse(request, name)
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	237 return ob
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	238 else:
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	239 path.append(name)
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	240
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	241 # continue traversing
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	242 return self
44 c6c47034d2a4 more refactoring of templates casties parents: 43 diff changeset	243
c6c47034d2a4 more refactoring of templates casties parents: 43 diff changeset	244
2 61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	245 def index_html(self,REQUEST,RESPONSE):
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	246 """index method"""
8 a9a49f5765c9 reworking templates and schema casties parents: 7 diff changeset	247 # ReST path was stored in request
2 61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	248 path = REQUEST.get('restdb_path',[])
19 78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	249
8 a9a49f5765c9 reworking templates and schema casties parents: 7 diff changeset	250 # type and format are real parameter
26 2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	251 resultFormat = REQUEST.get('format','HTML').upper()
27 a2e4ca3f1cff NEW - # 12: create table and upload data casties parents: 26 diff changeset	252 queryType = REQUEST.get('type',None)
19 78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	253
26 2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	254 logging.debug("index_html path=%s resultFormat=%s queryType=%s"%(path,resultFormat,queryType))
8 a9a49f5765c9 reworking templates and schema casties parents: 7 diff changeset	255
26 2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	256 if queryType is not None:
2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	257 # non-empty queryType -- look for template
2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	258 pt = getattr(self.template, "%s_%s"%(resultFormat,queryType), None)
8 a9a49f5765c9 reworking templates and schema casties parents: 7 diff changeset	259 if pt is not None:
26 2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	260 return pt(format=resultFormat,type=queryType,path=path)
8 a9a49f5765c9 reworking templates and schema casties parents: 7 diff changeset	261
2 61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	262 if len(path) == 1:
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	263 # list of schemas
61 e81d034b28a5 more permission handling and table metadata casties parents: 60 diff changeset	264 return self.showListOfSchemas(format=resultFormat)
2 61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	265 elif len(path) == 2:
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	266 # list of tables
61 e81d034b28a5 more permission handling and table metadata casties parents: 60 diff changeset	267 return self.showListOfTables(format=resultFormat,schema=path[1])
2 61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	268 elif len(path) == 3:
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	269 # table
25 cef1bfa821cb fixed problem with not passing request casties parents: 24 diff changeset	270 if REQUEST.get("method") == "POST" and REQUEST.get("create_table_file",None) is not None:
24 f3811d9a36da start adding checkuploadtable casties parents: 23 diff changeset	271 # POST to table to check
61 e81d034b28a5 more permission handling and table metadata casties parents: 60 diff changeset	272 return self.checkTable(format=resultFormat,schema=path[1],table=path[2])
24 f3811d9a36da start adding checkuploadtable casties parents: 23 diff changeset	273 # else show table
61 e81d034b28a5 more permission handling and table metadata casties parents: 60 diff changeset	274 return self.showTable(format=resultFormat,schema=path[1],table=path[2])
2 61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	275
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	276 # don't know what to do
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	277 return str(REQUEST)
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	278
12 0fa22c291ff1 NEW - # 12: create table and upload data casties parents: 9 diff changeset	279 def PUT(self, REQUEST, RESPONSE):
0fa22c291ff1 NEW - # 12: create table and upload data casties parents: 9 diff changeset	280 """
0fa22c291ff1 NEW - # 12: create table and upload data casties parents: 9 diff changeset	281 Implement WebDAV/HTTP PUT/FTP put method for this object.
0fa22c291ff1 NEW - # 12: create table and upload data casties parents: 9 diff changeset	282 """
13 e2c73c077533 NEW - # 12: create table and upload data casties parents: 12 diff changeset	283 logging.debug("RestDbInterface PUT")
e2c73c077533 NEW - # 12: create table and upload data casties parents: 12 diff changeset	284 #logging.debug("req=%s"%REQUEST)
12 0fa22c291ff1 NEW - # 12: create table and upload data casties parents: 9 diff changeset	285 #self.dav__init(REQUEST, RESPONSE)
0fa22c291ff1 NEW - # 12: create table and upload data casties parents: 9 diff changeset	286 #self.dav__simpleifhandler(REQUEST, RESPONSE)
13 e2c73c077533 NEW - # 12: create table and upload data casties parents: 12 diff changeset	287 # ReST path was stored in request
e2c73c077533 NEW - # 12: create table and upload data casties parents: 12 diff changeset	288 path = REQUEST.get('restdb_path',[])
e2c73c077533 NEW - # 12: create table and upload data casties parents: 12 diff changeset	289 if len(path) == 3:
e2c73c077533 NEW - # 12: create table and upload data casties parents: 12 diff changeset	290 schema = path[1]
e2c73c077533 NEW - # 12: create table and upload data casties parents: 12 diff changeset	291 tablename = path[2]
e2c73c077533 NEW - # 12: create table and upload data casties parents: 12 diff changeset	292 file = REQUEST.get("create_table_file",None)
e2c73c077533 NEW - # 12: create table and upload data casties parents: 12 diff changeset	293 if file is None:
e2c73c077533 NEW - # 12: create table and upload data casties parents: 12 diff changeset	294 RESPONSE.setStatus(400)
e2c73c077533 NEW - # 12: create table and upload data casties parents: 12 diff changeset	295 return
4 e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly. casties parents: 2 diff changeset	296
30 51db9e78bf98 working on field types casties parents: 29 diff changeset	297 fields = None
51db9e78bf98 working on field types casties parents: 29 diff changeset	298 fieldsStr = REQUEST.get("create_table_fields",None)
51db9e78bf98 working on field types casties parents: 29 diff changeset	299 logging.debug("put with schema=%s table=%s file=%s fields=%s"%(schema,tablename,file,repr(fieldsStr)))
51db9e78bf98 working on field types casties parents: 29 diff changeset	300 if fieldsStr is not None:
51db9e78bf98 working on field types casties parents: 29 diff changeset	301 # unpack fields
51db9e78bf98 working on field types casties parents: 29 diff changeset	302 fields = [{"name":n, "type": t} for (n,t) in [f.split(":") for f in fieldsStr.split(",")]]
31 6d2055f1b4fa more work on types casties parents: 30 diff changeset	303
28 9e4f9cfd1edc start adding field structure to xml upload casties parents: 27 diff changeset	304 ret = self.createTableFromXML(schema, tablename, file, fields)
26 2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	305 # return the result as JSON
2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	306 format = REQUEST.get("format","JSON")
2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	307 if format == "JSON":
20 67ca17753cd5 NEW - # 12: create table and upload data casties parents: 18 diff changeset	308 RESPONSE.setHeader("Content-Type", "application/json")
67ca17753cd5 NEW - # 12: create table and upload data casties parents: 18 diff changeset	309 json.dump(ret, RESPONSE)
26 2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	310
2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	311 elif format == "JSONHTML":
20 67ca17753cd5 NEW - # 12: create table and upload data casties parents: 18 diff changeset	312 RESPONSE.setHeader("Content-Type", "text/html")
67ca17753cd5 NEW - # 12: create table and upload data casties parents: 18 diff changeset	313 RESPONSE.write("<html>\n<body>\n<pre>")
67ca17753cd5 NEW - # 12: create table and upload data casties parents: 18 diff changeset	314 json.dump(ret, RESPONSE)
67ca17753cd5 NEW - # 12: create table and upload data casties parents: 18 diff changeset	315 RESPONSE.write("</pre>\n</body>\n</html>")
13 e2c73c077533 NEW - # 12: create table and upload data casties parents: 12 diff changeset	316
e2c73c077533 NEW - # 12: create table and upload data casties parents: 12 diff changeset	317 else:
e2c73c077533 NEW - # 12: create table and upload data casties parents: 12 diff changeset	318 # 400 Bad Request
e2c73c077533 NEW - # 12: create table and upload data casties parents: 12 diff changeset	319 RESPONSE.setStatus(400)
e2c73c077533 NEW - # 12: create table and upload data casties parents: 12 diff changeset	320 return
252 efd2469d1722 geometry-column of tables will be displayed as string fknauft parents: 230 diff changeset	321 def getAttributeNames(self,schema='public',table=None):
efd2469d1722 geometry-column of tables will be displayed as string fknauft parents: 230 diff changeset	322 return self.executeSQL("SELECT attname FROM pg_attribute, pg_class WHERE pg_class.oid = attrelid AND attnum>0 AND relname = '%s';"%(table))
efd2469d1722 geometry-column of tables will be displayed as string fknauft parents: 230 diff changeset	323
efd2469d1722 geometry-column of tables will be displayed as string fknauft parents: 230 diff changeset	324 def getAttributeTypes(self,schema='public',table=None):
efd2469d1722 geometry-column of tables will be displayed as string fknauft parents: 230 diff changeset	325 return self.executeSQL("SELECT field_name, gis_type FROM public.gis_table_meta_rows WHERE table_name = '%s';"%(table))
efd2469d1722 geometry-column of tables will be displayed as string fknauft parents: 230 diff changeset	326
61 e81d034b28a5 more permission handling and table metadata casties parents: 60 diff changeset	327 def showTable(self,format='XML',schema='public',table=None,REQUEST=None,RESPONSE=None):
2 61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	328 """returns PageTemplate with tables"""
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	329 logging.debug("showtable")
38 f6d9a3caf986 fixed errors from merging casties parents: 37 diff changeset	330 if REQUEST is None:
f6d9a3caf986 fixed errors from merging casties parents: 37 diff changeset	331 REQUEST = self.REQUEST
f6d9a3caf986 fixed errors from merging casties parents: 37 diff changeset	332
23 860ec92f99df added cross-site access header (for GET) casties parents: 22 diff changeset	333 # should be cross-site accessible
25 cef1bfa821cb fixed problem with not passing request casties parents: 24 diff changeset	334 if RESPONSE is None:
cef1bfa821cb fixed problem with not passing request casties parents: 24 diff changeset	335 RESPONSE = self.REQUEST.RESPONSE
38 f6d9a3caf986 fixed errors from merging casties parents: 37 diff changeset	336
23 860ec92f99df added cross-site access header (for GET) casties parents: 22 diff changeset	337 RESPONSE.setHeader('Access-Control-Allow-Origin', '*')
38 f6d9a3caf986 fixed errors from merging casties parents: 37 diff changeset	338
22 1a4b56716902 NEW - # 12: create table and upload data casties parents: 21 diff changeset	339 # everything else has its own template
9 76ac7a721273 more rework of templates casties parents: 8 diff changeset	340 pt = getattr(self.template, '%s_schema_table'%format, None)
2 61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	341 if pt is None:
9 76ac7a721273 more rework of templates casties parents: 8 diff changeset	342 return "ERROR!! template %s_schema_table not found"%format
2 61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	343
44 c6c47034d2a4 more refactoring of templates casties parents: 43 diff changeset	344 #data = self.getTable(schema,table)
c6c47034d2a4 more refactoring of templates casties parents: 43 diff changeset	345 return pt(schema=schema,table=table)
2 61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	346
58 5ed0769f5ad3 more cleanup in kml generation casties parents: 55 diff changeset	347 def getTable(self,schema='public',table=None,sortBy=1,username='guest'):
2 61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	348 """return table data"""
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	349 logging.debug("gettable")
252 efd2469d1722 geometry-column of tables will be displayed as string fknauft parents: 230 diff changeset	350 attrNames=self.getAttributeNames(schema,table)
efd2469d1722 geometry-column of tables will be displayed as string fknauft parents: 230 diff changeset	351 attrTypes=self.getAttributeTypes(schema,table)
efd2469d1722 geometry-column of tables will be displayed as string fknauft parents: 230 diff changeset	352 attrString=""
efd2469d1722 geometry-column of tables will be displayed as string fknauft parents: 230 diff changeset	353 # try:
efd2469d1722 geometry-column of tables will be displayed as string fknauft parents: 230 diff changeset	354 for name in attrNames['rows']:
efd2469d1722 geometry-column of tables will be displayed as string fknauft parents: 230 diff changeset	355 logging.debug("name: ", name[0])
efd2469d1722 geometry-column of tables will be displayed as string fknauft parents: 230 diff changeset	356 not_added=True
efd2469d1722 geometry-column of tables will be displayed as string fknauft parents: 230 diff changeset	357 if name[0] == "the_geom": #FJK: the table column is "the_geom"
efd2469d1722 geometry-column of tables will be displayed as string fknauft parents: 230 diff changeset	358 attrString=attrString+"ST_AsText("+name[0]+"),"
efd2469d1722 geometry-column of tables will be displayed as string fknauft parents: 230 diff changeset	359 not_added=False
efd2469d1722 geometry-column of tables will be displayed as string fknauft parents: 230 diff changeset	360 break
efd2469d1722 geometry-column of tables will be displayed as string fknauft parents: 230 diff changeset	361 for a_iter in attrTypes['rows']:
efd2469d1722 geometry-column of tables will be displayed as string fknauft parents: 230 diff changeset	362 not_added = True
efd2469d1722 geometry-column of tables will be displayed as string fknauft parents: 230 diff changeset	363 logging.debug("attrTypes.field_name: ", a_iter[0])
efd2469d1722 geometry-column of tables will be displayed as string fknauft parents: 230 diff changeset	364 if a_iter[0]==name[0]:
efd2469d1722 geometry-column of tables will be displayed as string fknauft parents: 230 diff changeset	365 logging.debug("attrTypes.gis_type: ", a_iter[1])
efd2469d1722 geometry-column of tables will be displayed as string fknauft parents: 230 diff changeset	366 if a_iter[1] == "the_geom": #FJK: the table column is registered in gis_table_meta_rows as type "the_geom"
efd2469d1722 geometry-column of tables will be displayed as string fknauft parents: 230 diff changeset	367 attrString=attrString+"ST_AsText("+name[0]+"),"
efd2469d1722 geometry-column of tables will be displayed as string fknauft parents: 230 diff changeset	368 not_added=False
efd2469d1722 geometry-column of tables will be displayed as string fknauft parents: 230 diff changeset	369 if not_added:
efd2469d1722 geometry-column of tables will be displayed as string fknauft parents: 230 diff changeset	370 attrString=attrString+name[0]+","
efd2469d1722 geometry-column of tables will be displayed as string fknauft parents: 230 diff changeset	371 attrString=str(attrString).rsplit(",",1)[0] #to remove last ","
efd2469d1722 geometry-column of tables will be displayed as string fknauft parents: 230 diff changeset	372 if sortBy:
efd2469d1722 geometry-column of tables will be displayed as string fknauft parents: 230 diff changeset	373 data = self.executeSQL('select %s from "%s"."%s" order by %s'%(attrString,schema,table,sortBy))
efd2469d1722 geometry-column of tables will be displayed as string fknauft parents: 230 diff changeset	374 else:
efd2469d1722 geometry-column of tables will be displayed as string fknauft parents: 230 diff changeset	375 data = self.executeSQL('select %s from "%s"."%s"'%(attrString,schema,table))
efd2469d1722 geometry-column of tables will be displayed as string fknauft parents: 230 diff changeset	376 # except:
227 11dea0923d2f Upload of xml-tables with empty Cells possible fknauft parents: 87 diff changeset	377 """ table does not exist """
252 efd2469d1722 geometry-column of tables will be displayed as string fknauft parents: 230 diff changeset	378 # fields=self.get
efd2469d1722 geometry-column of tables will be displayed as string fknauft parents: 230 diff changeset	379 # self.createEmptyTable(schema, table, fields)
2 61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	380 return data
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	381
26 2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	382 def hasTable(self,schema='public',table=None,username='guest'):
2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	383 """return if table exists"""
2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	384 logging.debug("hastable")
2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	385 data = self.executeSQL('select 1 from information_schema.tables where table_schema=%s and table_name=%s',(schema,table))
2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	386 ret = bool(data['rows'])
2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	387 return ret
2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	388
61 e81d034b28a5 more permission handling and table metadata casties parents: 60 diff changeset	389 def showListOfTables(self,format='XML',schema='public',REQUEST=None,RESPONSE=None):
2 61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	390 """returns PageTemplate with list of tables"""
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	391 logging.debug("showlistoftables")
23 860ec92f99df added cross-site access header (for GET) casties parents: 22 diff changeset	392 # should be cross-site accessible
25 cef1bfa821cb fixed problem with not passing request casties parents: 24 diff changeset	393 if RESPONSE is None:
cef1bfa821cb fixed problem with not passing request casties parents: 24 diff changeset	394 RESPONSE = self.REQUEST.RESPONSE
23 860ec92f99df added cross-site access header (for GET) casties parents: 22 diff changeset	395 RESPONSE.setHeader('Access-Control-Allow-Origin', '*')
24 f3811d9a36da start adding checkuploadtable casties parents: 23 diff changeset	396
9 76ac7a721273 more rework of templates casties parents: 8 diff changeset	397 pt = getattr(self.template, '%s_schema'%format, None)
2 61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	398 if pt is None:
9 76ac7a721273 more rework of templates casties parents: 8 diff changeset	399 return "ERROR!! template %s_schema not found"%format
2 61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	400
45 ed8db63fab4f more refactoring of templates casties parents: 44 diff changeset	401 #data = self.getListOfTables(schema)
ed8db63fab4f more refactoring of templates casties parents: 44 diff changeset	402 return pt(schema=schema)
2 61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	403
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	404 def getListOfTables(self,schema='public',username='guest'):
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	405 """return list of tables"""
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	406 logging.debug("getlistoftables")
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	407 # get list of fields and types of db table
61 e81d034b28a5 more permission handling and table metadata casties parents: 60 diff changeset	408 #qstr="""SELECT c.relname AS tablename FROM pg_catalog.pg_class c
e81d034b28a5 more permission handling and table metadata casties parents: 60 diff changeset	409 # LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
e81d034b28a5 more permission handling and table metadata casties parents: 60 diff changeset	410 # WHERE c.relkind IN ('r','') AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
e81d034b28a5 more permission handling and table metadata casties parents: 60 diff changeset	411 # AND pg_catalog.pg_table_is_visible(c.oid)
e81d034b28a5 more permission handling and table metadata casties parents: 60 diff changeset	412 # AND c.relname ORDER BY 1"""
e81d034b28a5 more permission handling and table metadata casties parents: 60 diff changeset	413 qstr = """SELECT table_name FROM information_schema.tables WHERE table_type = 'BASE TABLE'
e81d034b28a5 more permission handling and table metadata casties parents: 60 diff changeset	414 AND table_schema = %s ORDER BY 1"""
e81d034b28a5 more permission handling and table metadata casties parents: 60 diff changeset	415 data=self.executeSQL(qstr,(schema,))
2 61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	416 return data
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	417
61 e81d034b28a5 more permission handling and table metadata casties parents: 60 diff changeset	418 def showListOfSchemas(self,format='XML',REQUEST=None,RESPONSE=None):
2 61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	419 """returns PageTemplate with list of schemas"""
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	420 logging.debug("showlistofschemas")
23 860ec92f99df added cross-site access header (for GET) casties parents: 22 diff changeset	421 # should be cross-site accessible
25 cef1bfa821cb fixed problem with not passing request casties parents: 24 diff changeset	422 if RESPONSE is None:
cef1bfa821cb fixed problem with not passing request casties parents: 24 diff changeset	423 RESPONSE = self.REQUEST.RESPONSE
23 860ec92f99df added cross-site access header (for GET) casties parents: 22 diff changeset	424 RESPONSE.setHeader('Access-Control-Allow-Origin', '*')
24 f3811d9a36da start adding checkuploadtable casties parents: 23 diff changeset	425
9 76ac7a721273 more rework of templates casties parents: 8 diff changeset	426 pt = getattr(self.template, '%s_index'%format, None)
2 61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	427 if pt is None:
9 76ac7a721273 more rework of templates casties parents: 8 diff changeset	428 return "ERROR!! template %s_index not found"%format
2 61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	429
45 ed8db63fab4f more refactoring of templates casties parents: 44 diff changeset	430 #data = self.getListOfSchemas()
ed8db63fab4f more refactoring of templates casties parents: 44 diff changeset	431 return pt()
2 61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	432
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	433 def getListOfSchemas(self,username='guest'):
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	434 """return list of schemas"""
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	435 logging.debug("getlistofschemas")
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	436 # TODO: really look up schemas
9 76ac7a721273 more rework of templates casties parents: 8 diff changeset	437 data={'fields': (('schemas',),), 'rows': [('public',),]}
2 61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	438 return data
26 2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	439
61 e81d034b28a5 more permission handling and table metadata casties parents: 60 diff changeset	440 def checkTable(self,format,schema,table,REQUEST=None,RESPONSE=None):
26 2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	441 """check the table.
2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	442 returns valid data fields and table name."""
2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	443 if REQUEST is None:
2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	444 REQUEST = self.REQUEST
2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	445 RESPONSE = REQUEST.RESPONSE
74 6e19a9af00e6 htmlHead() included fknauft parents: 73 diff changeset	446
26 2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	447 file = REQUEST.get("create_table_file",None)
2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	448 res = self.checkTableFromXML(schema, table, file)
2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	449 logging.debug("checkTable result=%s"%repr(res))
2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	450 # return the result as JSON
61 e81d034b28a5 more permission handling and table metadata casties parents: 60 diff changeset	451 if format == "JSON":
26 2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	452 RESPONSE.setHeader("Content-Type", "application/json")
2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	453 json.dump(res, RESPONSE)
2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	454
61 e81d034b28a5 more permission handling and table metadata casties parents: 60 diff changeset	455 elif format == "JSONHTML":
26 2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	456 RESPONSE.setHeader("Content-Type", "text/html")
2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	457 RESPONSE.write("<html>\n<body>\n<pre>")
2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	458 json.dump(res, RESPONSE)
2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	459 RESPONSE.write("</pre>\n</body>\n</html>")
2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	460
2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	461 else:
61 e81d034b28a5 more permission handling and table metadata casties parents: 60 diff changeset	462 return "ERROR: invalid format"
74 6e19a9af00e6 htmlHead() included fknauft parents: 73 diff changeset	463
26 2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	464 def checkTableFromXML(self,schema,table,data,REQUEST=None,RESPONSE=None):
2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	465 """check the table with the given XML data.
2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	466 returns valid data fields and table name."""
2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	467 logging.debug("checkTableFromXML schema=%s table=%s"%(schema,table))
2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	468 # clean table name
2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	469 tablename = sqlName(table)
2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	470 tableExists = self.hasTable(schema, table)
2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	471 if data is None:
2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	472 fieldNames = []
2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	473 else:
2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	474 # get list of field names from upload file
2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	475 fields = self.importExcelXML(schema,tablename,data,fieldsOnly=True)
14 05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	476
26 2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	477 res = {'tablename': tablename, 'table_exists': tableExists}
2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	478 res['fields'] = fields
2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	479 return res
19 78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	480
14 05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	481 def createEmptyTable(self,schema,table,fields):
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	482 """create a table with the given fields
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	483 returns list of created fields"""
15 5e3edf980813 more work on xml import casties parents: 14 diff changeset	484 logging.debug("createEmptyTable")
19 78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	485
14 05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	486 sqlFields = []
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	487 for f in fields:
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	488 if isinstance(f,dict):
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	489 # {name: XX, type: YY}
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	490 name = sqlName(f['name'])
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	491 type = f['type']
70 9ec7e32e8ad3 working on maps casties parents: 61 diff changeset	492 if hasattr(self, 'toSqlTypeMap'):
9ec7e32e8ad3 working on maps casties parents: 61 diff changeset	493 sqltype = self.toSqlTypeMap[type]
9ec7e32e8ad3 working on maps casties parents: 61 diff changeset	494 else:
9ec7e32e8ad3 working on maps casties parents: 61 diff changeset	495 sqltype = 'text'
14 05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	496
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	497 else:
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	498 # name only
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	499 name = sqlName(f)
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	500 type = 'text'
31 6d2055f1b4fa more work on types casties parents: 30 diff changeset	501 sqltype = 'text'
14 05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	502
31 6d2055f1b4fa more work on types casties parents: 30 diff changeset	503 sqlFields.append({'name':name, 'type':type, 'sqltype':sqltype})
14 05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	504
60 9fdadb60529f working on authentication and authorization casties parents: 58 diff changeset	505 if self.hasTable(schema,table):
9fdadb60529f working on authentication and authorization casties parents: 58 diff changeset	506 # TODO: find owner
9fdadb60529f working on authentication and authorization casties parents: 58 diff changeset	507 if not self.isAllowed("update", schema, table):
9fdadb60529f working on authentication and authorization casties parents: 58 diff changeset	508 raise Unauthorized
9fdadb60529f working on authentication and authorization casties parents: 58 diff changeset	509 self.executeSQL('drop table "%s"."%s"'%(schema,table),hasResult=False)
9fdadb60529f working on authentication and authorization casties parents: 58 diff changeset	510 else:
9fdadb60529f working on authentication and authorization casties parents: 58 diff changeset	511 if not self.isAllowed("create", schema, table):
9fdadb60529f working on authentication and authorization casties parents: 58 diff changeset	512 raise Unauthorized
9fdadb60529f working on authentication and authorization casties parents: 58 diff changeset	513
43 562717546168 refactoring... casties parents: 41 diff changeset	514 fieldString = ", ".join(['"%s" %s'%(f['name'],f['sqltype']) for f in sqlFields])
562717546168 refactoring... casties parents: 41 diff changeset	515 sqlString = 'create table "%s"."%s" (%s)'%(schema,table,fieldString)
562717546168 refactoring... casties parents: 41 diff changeset	516 logging.debug("createemptytable: SQL=%s"%sqlString)
562717546168 refactoring... casties parents: 41 diff changeset	517 self.executeSQL(sqlString,hasResult=False)
562717546168 refactoring... casties parents: 41 diff changeset	518 self.setTableMetaTypes(schema,table,sqlFields)
562717546168 refactoring... casties parents: 41 diff changeset	519 return sqlFields
19 78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	520
28 9e4f9cfd1edc start adding field structure to xml upload casties parents: 27 diff changeset	521 def createTableFromXML(self,schema,table,data, fields=None):
13 e2c73c077533 NEW - # 12: create table and upload data casties parents: 12 diff changeset	522 """create or replace a table with the given XML data"""
28 9e4f9cfd1edc start adding field structure to xml upload casties parents: 27 diff changeset	523 logging.debug("createTableFromXML schema=%s table=%s data=%s fields=%s"%(schema,table,data,fields))
17 ed997e639cfd more upload table casties parents: 16 diff changeset	524 tablename = sqlName(table)
28 9e4f9cfd1edc start adding field structure to xml upload casties parents: 27 diff changeset	525 self.importExcelXML(schema, tablename, data, fields)
17 ed997e639cfd more upload table casties parents: 16 diff changeset	526 return {"tablename": tablename}
76 e0a79d926902 Path for kml included fknauft parents: 55 diff changeset	527
28 9e4f9cfd1edc start adding field structure to xml upload casties parents: 27 diff changeset	528 def importExcelXML(self,schema,table,xmldata,fields=None,fieldsOnly=False):
14 05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	529 '''
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	530 Import XML file in Excel format into the table
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	531 @param table: name of the table the xml shall be imported into
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	532 '''
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	533 from xml.dom.pulldom import parseString,parse
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	534
60 9fdadb60529f working on authentication and authorization casties parents: 58 diff changeset	535 if not (fieldsOnly or self.isAllowed("create", schema, table)):
9fdadb60529f working on authentication and authorization casties parents: 58 diff changeset	536 raise Unauthorized
9fdadb60529f working on authentication and authorization casties parents: 58 diff changeset	537
14 05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	538 namespace = "urn:schemas-microsoft-com:office:spreadsheet"
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	539 containerTagName = "Table"
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	540 rowTagName = "Row"
16 cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	541 colTagName = "Cell"
14 05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	542 dataTagName = "Data"
26 2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	543 xmlFields = []
14 05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	544 sqlFields = []
16 cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	545 numFields = 0
14 05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	546 sqlInsert = None
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	547
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	548 logging.debug("import excel xml")
76 e0a79d926902 Path for kml included fknauft parents: 55 diff changeset	549
14 05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	550 ret=""
16 cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	551 if isinstance(xmldata, str):
14 05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	552 logging.debug("importXML reading string data")
16 cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	553 doc=parseString(xmldata)
73 695b6612d4c6 getKmlUrl eingebaut fknauft parents: 21 diff changeset	554 else:
14 05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	555 logging.debug("importXML reading file data")
16 cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	556 doc=parse(xmldata)
14 05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	557
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	558 cnt = 0
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	559 while True:
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	560 node=doc.getEvent()
16 cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	561
14 05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	562 if node is None:
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	563 break
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	564
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	565 else:
16 cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	566 #logging.debug("tag=%s"%node[1].localName)
14 05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	567 if node[1].localName is not None:
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	568 tagName = node[1].localName.lower()
76 e0a79d926902 Path for kml included fknauft parents: 55 diff changeset	569 else:
14 05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	570 # ignore non-tag nodes
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	571 continue
16 cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	572
14 05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	573 if tagName == rowTagName.lower():
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	574 # start of row
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	575 doc.expandNode(node[1])
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	576 cnt += 1
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	577 if cnt == 1:
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	578 # first row -- field names
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	579 names=node[1].getElementsByTagNameNS(namespace, dataTagName)
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	580 for name in names:
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	581 fn = getTextFromNode(name)
26 2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	582 xmlFields.append({'name':sqlName(fn),'type':'text'})
14 05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	583
26 2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	584 if fieldsOnly:
14 05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	585 # return just field names
26 2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	586 return xmlFields
14 05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	587
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	588 # create table
28 9e4f9cfd1edc start adding field structure to xml upload casties parents: 27 diff changeset	589 if fields is None:
31 6d2055f1b4fa more work on types casties parents: 30 diff changeset	590 fields = xmlFields
6d2055f1b4fa more work on types casties parents: 30 diff changeset	591
6d2055f1b4fa more work on types casties parents: 30 diff changeset	592 sqlFields = self.createEmptyTable(schema, table, fields)
16 cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	593 numFields = len(sqlFields)
15 5e3edf980813 more work on xml import casties parents: 14 diff changeset	594 fieldString = ", ".join(['"%s"'%f['name'] for f in sqlFields])
14 05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	595 valString = ", ".join(["%s" for f in sqlFields])
16 cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	596 sqlInsert = 'insert into "%s"."%s" (%s) values (%s)'%(schema,table,fieldString,valString)
26 2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	597 #logging.debug("importexcelsql: sqlInsert=%s"%sqlInsert)
14 05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	598
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	599 else:
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	600 # following rows are data
16 cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	601 colNodes=node[1].getElementsByTagNameNS(namespace, colTagName)
14 05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	602 data = []
16 cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	603 hasData = False
227 11dea0923d2f Upload of xml-tables with empty Cells possible fknauft parents: 87 diff changeset	604 lineIndex=0
16 cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	605 for colNode in colNodes:
227 11dea0923d2f Upload of xml-tables with empty Cells possible fknauft parents: 87 diff changeset	606 lineIndex+=1
16 cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	607 dataNodes=colNode.getElementsByTagNameNS(namespace, dataTagName)
cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	608 if len(dataNodes) > 0:
227 11dea0923d2f Upload of xml-tables with empty Cells possible fknauft parents: 87 diff changeset	609 dataIndex=0
228 52112e9a8d72 Upload of xml-tables with empty Cells possible fknauft parents: 227 diff changeset	610 if colNode.hasAttribute(u'ss:Index'):
230 9e34cd7b2e57 Upload of xml-tables with empty Cells possible fknauft parents: 228 diff changeset	611 dataIndex=int(colNode.getAttribute(u'ss:Index'))
9e34cd7b2e57 Upload of xml-tables with empty Cells possible fknauft parents: 228 diff changeset	612 while dataIndex>lineIndex:
228 52112e9a8d72 Upload of xml-tables with empty Cells possible fknauft parents: 227 diff changeset	613 data.append(None)
230 9e34cd7b2e57 Upload of xml-tables with empty Cells possible fknauft parents: 228 diff changeset	614 lineIndex+=1
9e34cd7b2e57 Upload of xml-tables with empty Cells possible fknauft parents: 228 diff changeset	615 else:
9e34cd7b2e57 Upload of xml-tables with empty Cells possible fknauft parents: 228 diff changeset	616 val = getTextFromNode(dataNodes[0])
9e34cd7b2e57 Upload of xml-tables with empty Cells possible fknauft parents: 228 diff changeset	617 hasData = True
16 cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	618 else:
cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	619 val = None
19 78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	620
14 05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	621 data.append(val)
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	622
16 cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	623 if not hasData:
cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	624 # ignore empty rows
cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	625 continue
cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	626
cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	627 # fix number of data fields
cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	628 if len(data) > numFields:
cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	629 del data[numFields:]
cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	630 elif len(data) < numFields:
cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	631 missFields = numFields - len(data)
cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	632 data.extend(missFields * [None,])
cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	633
cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	634 logging.debug("importexcel sqlinsert=%s data=%s"%(sqlInsert,data))
17 ed997e639cfd more upload table casties parents: 16 diff changeset	635 self.executeSQL(sqlInsert, data, hasResult=False)
14 05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	636
16 cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	637 return cnt
38 f6d9a3caf986 fixed errors from merging casties parents: 37 diff changeset	638
4 e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly. casties parents: 2 diff changeset	639 def manage_editRestDbInterface(self, title=None, connection_id=None,
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly. casties parents: 2 diff changeset	640 REQUEST=None):
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly. casties parents: 2 diff changeset	641 """Change the object"""
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly. casties parents: 2 diff changeset	642 if title is not None:
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly. casties parents: 2 diff changeset	643 self.title = title
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly. casties parents: 2 diff changeset	644
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly. casties parents: 2 diff changeset	645 if connection_id is not None:
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly. casties parents: 2 diff changeset	646 self.connection_id = connection_id
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly. casties parents: 2 diff changeset	647
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly. casties parents: 2 diff changeset	648 #checkPermission=getSecurityManager().checkPermission
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly. casties parents: 2 diff changeset	649 REQUEST.RESPONSE.redirect('manage_main')
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly. casties parents: 2 diff changeset	650
2 61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	651
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	652 manage_addRestDbInterfaceForm=PageTemplateFile('zpt/addRestDbInterface',globals())
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	653
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	654 def manage_addRestDbInterface(self, id, title='', label='', description='',
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	655 createPublic=0,
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	656 createUserF=0,
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	657 REQUEST=None):
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	658 """Add a new object with id id."""
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	659
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	660 ob=RestDbInterface(str(id),title)
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	661 self._setObject(id, ob)
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	662
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	663 #checkPermission=getSecurityManager().checkPermission
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	664 REQUEST.RESPONSE.redirect('manage_main')
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	665
5 7539a9e69365 small fix casties parents: 4 diff changeset	666

Mercurial > hg > ChinaGisRestApi

annotate RestDbInterface.py @ 252:efd2469d1722