Mercurial > hg > ChinaGisRestApi

annotate RestDbInterface.py @ 277:9bfa7a6858f1

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