ChinaGisRestApi: RestDbInterface.py annotate

annotate RestDbInterface.py @ 29:0b9f8cca6744

changed fields parameter

author	casties
date	Mon, 30 Aug 2010 11:54:54 +0200
parents	9e4f9cfd1edc
children	51db9e78bf98

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
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	9 from Products.ZSQLExtend import ZSQLExtend
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	10 import logging
15 5e3edf980813 more work on xml import casties parents: 14 diff changeset	11 import re
16 cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	12 import psycopg2
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
2 61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	15
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	16 from zope.interface import implements
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	17 from zope.publisher.interfaces import IPublishTraverse
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	18 from ZPublisher.BaseRequest import DefaultPublishTraverse
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	19 #from zope.publisher.interfaces import NotFound
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	20 #from zope.app import zapi
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	21 #from zope.component import queryMultiAdapter
4 e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly. casties parents: 2 diff changeset	22 import Shared.DC.ZRDB.DA
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly. casties parents: 2 diff changeset	23 from Products.ZSQLMethods.SQL import SQLConnectionIDs
2 61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	24
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	25
16 cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	26 def getTextFromNode(node):
14 05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	27 """get the cdata content of a XML node"""
16 cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	28 if node is None:
14 05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	29 return ""
16 cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	30
cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	31 if isinstance(node, list):
cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	32 nodelist = node
cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	33 else:
cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	34 nodelist=node.childNodes
cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	35
14 05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	36 rc = ""
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	37 for node in nodelist:
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	38 if node.nodeType == node.TEXT_NODE:
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	39 rc = rc + node.data
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	40 return rc
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	41
16 cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	42 def sqlName(s,lc=True):
cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	43 """returns restricted ASCII-only version of string"""
15 5e3edf980813 more work on xml import casties parents: 14 diff changeset	44 if s is None:
5e3edf980813 more work on xml import casties parents: 14 diff changeset	45 return ""
5e3edf980813 more work on xml import casties parents: 14 diff changeset	46
5e3edf980813 more work on xml import casties parents: 14 diff changeset	47 # all else -> "_"
16 cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	48 s = re.sub(r'[^A-Za-z0-9_]','_',s)
15 5e3edf980813 more work on xml import casties parents: 14 diff changeset	49 if lc:
5e3edf980813 more work on xml import casties parents: 14 diff changeset	50 return s.lower()
5e3edf980813 more work on xml import casties parents: 14 diff changeset	51
5e3edf980813 more work on xml import casties parents: 14 diff changeset	52 return s
14 05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	53
2 61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	54 class RestDbInterface(Folder):
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	55 """Object for RESTful database queries
7 bd52d9445a41 trying to rework casties parents: 5 diff changeset	56 path schema: /db/{schema}/{table}/
2 61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	57 omitting table gives a list of schemas
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	58 omitting table and schema gives a list of schemas
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	59 """
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	60 implements(IPublishTraverse)
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	61
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	62 meta_type="RESTdb"
4 e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly. casties parents: 2 diff changeset	63 manage_options=Folder.manage_options+(
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly. casties parents: 2 diff changeset	64 {'label':'Config','action':'manage_editRestDbInterfaceForm'},
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly. casties parents: 2 diff changeset	65 )
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly. casties parents: 2 diff changeset	66
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly. casties parents: 2 diff changeset	67 # management templates
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly. casties parents: 2 diff changeset	68 manage_editRestDbInterfaceForm=PageTemplateFile('zpt/editRestDbInterface',globals())
2 61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	69
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	70 # data templates
8 a9a49f5765c9 reworking templates and schema casties parents: 7 diff changeset	71 XML_index = PageTemplateFile('zpt/XML_index', globals())
a9a49f5765c9 reworking templates and schema casties parents: 7 diff changeset	72 XML_schema = PageTemplateFile('zpt/XML_schema', globals())
a9a49f5765c9 reworking templates and schema casties parents: 7 diff changeset	73 XML_schema_table = PageTemplateFile('zpt/XML_schema_table', globals())
a9a49f5765c9 reworking templates and schema casties parents: 7 diff changeset	74 HTML_index = PageTemplateFile('zpt/HTML_index', globals())
a9a49f5765c9 reworking templates and schema casties parents: 7 diff changeset	75 HTML_schema = PageTemplateFile('zpt/HTML_schema', globals())
a9a49f5765c9 reworking templates and schema casties parents: 7 diff changeset	76 HTML_schema_table = PageTemplateFile('zpt/HTML_schema_table', globals())
22 1a4b56716902 NEW - # 12: create table and upload data casties parents: 21 diff changeset	77 JSONHTML_index = PageTemplateFile('zpt/JSONHTML_index', globals())
1a4b56716902 NEW - # 12: create table and upload data casties parents: 21 diff changeset	78 JSONHTML_schema = PageTemplateFile('zpt/JSONHTML_schema', globals())
1a4b56716902 NEW - # 12: create table and upload data casties parents: 21 diff changeset	79 JSONHTML_schema_table = PageTemplateFile('zpt/JSONHTML_schema_table', globals())
24 f3811d9a36da start adding checkuploadtable casties parents: 23 diff changeset	80 # JSON_* templates are scripts
2 61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	81
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	82
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	83
4 e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly. casties parents: 2 diff changeset	84 def __init__(self, id, title, connection_id=None):
2 61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	85 """init"""
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	86 self.id = id
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	87 self.title = title
4 e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly. casties parents: 2 diff changeset	88 # database connection id
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly. casties parents: 2 diff changeset	89 self.connection_id = connection_id
2 61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	90 # create template folder
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	91 self.manage_addFolder('template')
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	92
4 e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly. casties parents: 2 diff changeset	93
22 1a4b56716902 NEW - # 12: create table and upload data casties parents: 21 diff changeset	94 def getJsonString(self,object):
1a4b56716902 NEW - # 12: create table and upload data casties parents: 21 diff changeset	95 """returns a JSON formatted string from object"""
1a4b56716902 NEW - # 12: create table and upload data casties parents: 21 diff changeset	96 return json.dumps(object)
1a4b56716902 NEW - # 12: create table and upload data casties parents: 21 diff changeset	97
17 ed997e639cfd more upload table casties parents: 16 diff changeset	98 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	99 """returns fresh DB cursor"""
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly. casties parents: 2 diff changeset	100 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	101 if conn is None:
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly. casties parents: 2 diff changeset	102 # create a new connection object
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly. casties parents: 2 diff changeset	103 try:
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly. casties parents: 2 diff changeset	104 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	105 # 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	106 connids = SQLConnectionIDs(self)
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly. casties parents: 2 diff changeset	107 if len(connids) > 0:
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly. casties parents: 2 diff changeset	108 connection_id = connids[0][0]
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly. casties parents: 2 diff changeset	109 self.connection_id = connection_id
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly. casties parents: 2 diff changeset	110 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	111
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly. casties parents: 2 diff changeset	112 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	113 da.connect('')
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly. casties parents: 2 diff changeset	114 # 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	115 conn = da._v_database_connection
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly. casties parents: 2 diff changeset	116 #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	117 self._v_database_connection = conn
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly. casties parents: 2 diff changeset	118 except Exception, e:
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly. casties parents: 2 diff changeset	119 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	120
5 7539a9e69365 small fix casties parents: 4 diff changeset	121 cursor = conn.getcursor()
17 ed997e639cfd more upload table casties parents: 16 diff changeset	122 if autocommit:
ed997e639cfd more upload table casties parents: 16 diff changeset	123 # is there a better version to get to the connection?
ed997e639cfd more upload table casties parents: 16 diff changeset	124 cursor.connection.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
ed997e639cfd more upload table casties parents: 16 diff changeset	125
4 e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly. casties parents: 2 diff changeset	126 return cursor
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly. casties parents: 2 diff changeset	127
17 ed997e639cfd more upload table casties parents: 16 diff changeset	128 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	129 """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	130 result format: {"fields":fields, "rows":data}"""
16 cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	131 logging.debug("executeSQL query=%s args=%s"%(query,args))
17 ed997e639cfd more upload table casties parents: 16 diff changeset	132 cur = self.getCursor(autocommit=autocommit)
4 e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly. casties parents: 2 diff changeset	133 cur.execute(query, args)
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly. casties parents: 2 diff changeset	134 # description of returned fields
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly. casties parents: 2 diff changeset	135 fields = cur.description
17 ed997e639cfd more upload table casties parents: 16 diff changeset	136 if hasResult:
ed997e639cfd more upload table casties parents: 16 diff changeset	137 # get all data in an array
ed997e639cfd more upload table casties parents: 16 diff changeset	138 data = cur.fetchall()
ed997e639cfd more upload table casties parents: 16 diff changeset	139 cur.close()
ed997e639cfd more upload table casties parents: 16 diff changeset	140 return {"fields":fields, "rows":data}
ed997e639cfd more upload table casties parents: 16 diff changeset	141 else:
ed997e639cfd more upload table casties parents: 16 diff changeset	142 cur.close()
ed997e639cfd more upload table casties parents: 16 diff changeset	143 return None
16 cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	144
2 61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	145
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	146 def publishTraverse(self,request,name):
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	147 """change the traversal"""
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	148 # get stored path
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	149 path = request.get('restdb_path', [])
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	150 logging.debug("publishtraverse: name=%s restdb_path=%s"%(name,path))
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	151
13 e2c73c077533 NEW - # 12: create table and upload data casties parents: 12 diff changeset	152 if name in ("index_html", "PUT"):
2 61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	153 # end of traversal
13 e2c73c077533 NEW - # 12: create table and upload data casties parents: 12 diff changeset	154 if request.get("method") == "POST" and request.get("action",None) == "PUT":
e2c73c077533 NEW - # 12: create table and upload data casties parents: 12 diff changeset	155 # fake PUT by POST with action=PUT
e2c73c077533 NEW - # 12: create table and upload data casties parents: 12 diff changeset	156 name = "PUT"
e2c73c077533 NEW - # 12: create table and upload data casties parents: 12 diff changeset	157
e2c73c077533 NEW - # 12: create table and upload data casties parents: 12 diff changeset	158 return getattr(self, name)
2 61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	159 #TODO: should we check more?
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	160 else:
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	161 # traverse
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	162 if len(path) == 0:
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	163 # first segment
8 a9a49f5765c9 reworking templates and schema casties parents: 7 diff changeset	164 if name == 'db':
2 61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	165 # virtual path -- continue traversing
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	166 path = [name]
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	167 request['restdb_path'] = path
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	168 else:
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	169 # try real path
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	170 tr = DefaultPublishTraverse(self, request)
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	171 ob = tr.publishTraverse(request, name)
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	172 return ob
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	173 else:
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	174 path.append(name)
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	175
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	176 # continue traversing
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	177 return self
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	178
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	179 def index_html(self,REQUEST,RESPONSE):
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	180 """index method"""
8 a9a49f5765c9 reworking templates and schema casties parents: 7 diff changeset	181 # ReST path was stored in request
2 61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	182 path = REQUEST.get('restdb_path',[])
19 78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	183
27 a2e4ca3f1cff NEW - # 12: create table and upload data casties parents: 26 diff changeset	184 # type and format are real parameter
26 2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	185 resultFormat = REQUEST.get('format','HTML').upper()
27 a2e4ca3f1cff NEW - # 12: create table and upload data casties parents: 26 diff changeset	186 queryType = REQUEST.get('type',None)
19 78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	187
26 2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	188 logging.debug("index_html path=%s resultFormat=%s queryType=%s"%(path,resultFormat,queryType))
8 a9a49f5765c9 reworking templates and schema casties parents: 7 diff changeset	189
26 2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	190 if queryType is not None:
2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	191 # non-empty queryType -- look for template
2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	192 pt = getattr(self.template, "%s_%s"%(resultFormat,queryType), None)
8 a9a49f5765c9 reworking templates and schema casties parents: 7 diff changeset	193 if pt is not None:
26 2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	194 return pt(format=resultFormat,type=queryType,path=path)
8 a9a49f5765c9 reworking templates and schema casties parents: 7 diff changeset	195
2 61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	196 if len(path) == 1:
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	197 # list of schemas
26 2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	198 return self.showListOfSchemas(resultFormat=resultFormat)
2 61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	199 elif len(path) == 2:
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	200 # list of tables
26 2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	201 return self.showListOfTables(resultFormat=resultFormat,schema=path[1])
2 61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	202 elif len(path) == 3:
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	203 # table
25 cef1bfa821cb fixed problem with not passing request casties parents: 24 diff changeset	204 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	205 # POST to table to check
26 2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	206 return self.checkTable(resultFormat=resultFormat,schema=path[1],table=path[2])
24 f3811d9a36da start adding checkuploadtable casties parents: 23 diff changeset	207 # else show table
26 2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	208 return self.showTable(resultFormat=resultFormat,schema=path[1],table=path[2])
2 61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	209
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	210 # don't know what to do
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	211 return str(REQUEST)
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	212
12 0fa22c291ff1 NEW - # 12: create table and upload data casties parents: 9 diff changeset	213 def PUT(self, REQUEST, RESPONSE):
0fa22c291ff1 NEW - # 12: create table and upload data casties parents: 9 diff changeset	214 """
0fa22c291ff1 NEW - # 12: create table and upload data casties parents: 9 diff changeset	215 Implement WebDAV/HTTP PUT/FTP put method for this object.
0fa22c291ff1 NEW - # 12: create table and upload data casties parents: 9 diff changeset	216 """
13 e2c73c077533 NEW - # 12: create table and upload data casties parents: 12 diff changeset	217 logging.debug("RestDbInterface PUT")
e2c73c077533 NEW - # 12: create table and upload data casties parents: 12 diff changeset	218 #logging.debug("req=%s"%REQUEST)
12 0fa22c291ff1 NEW - # 12: create table and upload data casties parents: 9 diff changeset	219 #self.dav__init(REQUEST, RESPONSE)
0fa22c291ff1 NEW - # 12: create table and upload data casties parents: 9 diff changeset	220 #self.dav__simpleifhandler(REQUEST, RESPONSE)
13 e2c73c077533 NEW - # 12: create table and upload data casties parents: 12 diff changeset	221 # ReST path was stored in request
e2c73c077533 NEW - # 12: create table and upload data casties parents: 12 diff changeset	222 path = REQUEST.get('restdb_path',[])
e2c73c077533 NEW - # 12: create table and upload data casties parents: 12 diff changeset	223 if len(path) == 3:
e2c73c077533 NEW - # 12: create table and upload data casties parents: 12 diff changeset	224 schema = path[1]
e2c73c077533 NEW - # 12: create table and upload data casties parents: 12 diff changeset	225 tablename = path[2]
e2c73c077533 NEW - # 12: create table and upload data casties parents: 12 diff changeset	226 file = REQUEST.get("create_table_file",None)
e2c73c077533 NEW - # 12: create table and upload data casties parents: 12 diff changeset	227 if file is None:
e2c73c077533 NEW - # 12: create table and upload data casties parents: 12 diff changeset	228 RESPONSE.setStatus(400)
e2c73c077533 NEW - # 12: create table and upload data casties parents: 12 diff changeset	229 return
e2c73c077533 NEW - # 12: create table and upload data casties parents: 12 diff changeset	230
29 0b9f8cca6744 changed fields parameter casties parents: 28 diff changeset	231 fields = REQUEST.get("create_table_fields",None)
28 9e4f9cfd1edc start adding field structure to xml upload casties parents: 27 diff changeset	232 logging.debug("put with schema=%s table=%s file=%s fields=%s"%(schema,tablename,file,repr(fields)))
9e4f9cfd1edc start adding field structure to xml upload casties parents: 27 diff changeset	233 ret = self.createTableFromXML(schema, tablename, file, fields)
26 2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	234 # return the result as JSON
2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	235 format = REQUEST.get("format","JSON")
2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	236 if format == "JSON":
20 67ca17753cd5 NEW - # 12: create table and upload data casties parents: 18 diff changeset	237 RESPONSE.setHeader("Content-Type", "application/json")
67ca17753cd5 NEW - # 12: create table and upload data casties parents: 18 diff changeset	238 json.dump(ret, RESPONSE)
26 2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	239
2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	240 elif format == "JSONHTML":
20 67ca17753cd5 NEW - # 12: create table and upload data casties parents: 18 diff changeset	241 RESPONSE.setHeader("Content-Type", "text/html")
67ca17753cd5 NEW - # 12: create table and upload data casties parents: 18 diff changeset	242 RESPONSE.write("<html>\n<body>\n<pre>")
67ca17753cd5 NEW - # 12: create table and upload data casties parents: 18 diff changeset	243 json.dump(ret, RESPONSE)
67ca17753cd5 NEW - # 12: create table and upload data casties parents: 18 diff changeset	244 RESPONSE.write("</pre>\n</body>\n</html>")
13 e2c73c077533 NEW - # 12: create table and upload data casties parents: 12 diff changeset	245
e2c73c077533 NEW - # 12: create table and upload data casties parents: 12 diff changeset	246 else:
e2c73c077533 NEW - # 12: create table and upload data casties parents: 12 diff changeset	247 # 400 Bad Request
e2c73c077533 NEW - # 12: create table and upload data casties parents: 12 diff changeset	248 RESPONSE.setStatus(400)
e2c73c077533 NEW - # 12: create table and upload data casties parents: 12 diff changeset	249 return
12 0fa22c291ff1 NEW - # 12: create table and upload data casties parents: 9 diff changeset	250
26 2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	251 def showTable(self,resultFormat='XML',schema='public',table=None,REQUEST=None,RESPONSE=None):
2 61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	252 """returns PageTemplate with tables"""
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	253 logging.debug("showtable")
23 860ec92f99df added cross-site access header (for GET) casties parents: 22 diff changeset	254 # should be cross-site accessible
25 cef1bfa821cb fixed problem with not passing request casties parents: 24 diff changeset	255 if RESPONSE is None:
cef1bfa821cb fixed problem with not passing request casties parents: 24 diff changeset	256 RESPONSE = self.REQUEST.RESPONSE
23 860ec92f99df added cross-site access header (for GET) casties parents: 22 diff changeset	257 RESPONSE.setHeader('Access-Control-Allow-Origin', '*')
22 1a4b56716902 NEW - # 12: create table and upload data casties parents: 21 diff changeset	258 # GIS gets special treatment
26 2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	259 if resultFormat=="GIS":
23 860ec92f99df added cross-site access header (for GET) casties parents: 22 diff changeset	260 id = REQUEST.get('id',[])
860ec92f99df added cross-site access header (for GET) casties parents: 22 diff changeset	261 doc = REQUEST.get('doc',None)
22 1a4b56716902 NEW - # 12: create table and upload data casties parents: 21 diff changeset	262 return self.showGoogleMap(schema=path[1],table=path[2],id=id,doc=doc)
1a4b56716902 NEW - # 12: create table and upload data casties parents: 21 diff changeset	263
1a4b56716902 NEW - # 12: create table and upload data casties parents: 21 diff changeset	264 # everything else has its own template
26 2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	265 pt = getattr(self.template, '%s_schema_table'%resultFormat, None)
2 61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	266 if pt is None:
26 2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	267 return "ERROR!! template %s_schema_table not found"%resultFormat
2 61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	268
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	269 data = self.getTable(schema,table)
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	270 return pt(data=data,tablename=table)
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	271
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	272 def getTable(self,schema='public',table=None,username='guest'):
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	273 """return table data"""
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	274 logging.debug("gettable")
16 cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	275 data = self.executeSQL('select * from "%s"."%s"'%(schema,table))
2 61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	276 return data
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	277
26 2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	278 def hasTable(self,schema='public',table=None,username='guest'):
2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	279 """return if table exists"""
2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	280 logging.debug("hastable")
2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	281 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	282 ret = bool(data['rows'])
2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	283 return ret
2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	284
2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	285 def showListOfTables(self,resultFormat='XML',schema='public',REQUEST=None,RESPONSE=None):
2 61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	286 """returns PageTemplate with list of tables"""
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	287 logging.debug("showlistoftables")
23 860ec92f99df added cross-site access header (for GET) casties parents: 22 diff changeset	288 # should be cross-site accessible
25 cef1bfa821cb fixed problem with not passing request casties parents: 24 diff changeset	289 if RESPONSE is None:
cef1bfa821cb fixed problem with not passing request casties parents: 24 diff changeset	290 RESPONSE = self.REQUEST.RESPONSE
23 860ec92f99df added cross-site access header (for GET) casties parents: 22 diff changeset	291 RESPONSE.setHeader('Access-Control-Allow-Origin', '*')
24 f3811d9a36da start adding checkuploadtable casties parents: 23 diff changeset	292
26 2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	293 pt = getattr(self.template, '%s_schema'%resultFormat, None)
2 61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	294 if pt is None:
26 2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	295 return "ERROR!! template %s_schema not found"%resultFormat
2 61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	296
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	297 data = self.getListOfTables(schema)
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	298 return pt(data=data,schema=schema)
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	299
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	300 def getListOfTables(self,schema='public',username='guest'):
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	301 """return list of tables"""
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	302 logging.debug("getlistoftables")
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	303 # get list of fields and types of db table
22 1a4b56716902 NEW - # 12: create table and upload data casties parents: 21 diff changeset	304 qstr="""SELECT c.relname AS tablename FROM pg_catalog.pg_class c
2 61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	305 LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	306 WHERE c.relkind IN ('r','') AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	307 AND pg_catalog.pg_table_is_visible(c.oid)"""
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	308 #qstr="select attname, format_type(pg_attribute.atttypid, pg_attribute.atttypmod) from pg_attribute, pg_class where attrelid = pg_class.oid and pg_attribute.attnum > 0"
4 e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly. casties parents: 2 diff changeset	309 data=self.executeSQL(qstr)
2 61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	310 return data
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	311
26 2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	312 def showListOfSchemas(self,resultFormat='XML',REQUEST=None,RESPONSE=None):
2 61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	313 """returns PageTemplate with list of schemas"""
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	314 logging.debug("showlistofschemas")
23 860ec92f99df added cross-site access header (for GET) casties parents: 22 diff changeset	315 # should be cross-site accessible
25 cef1bfa821cb fixed problem with not passing request casties parents: 24 diff changeset	316 if RESPONSE is None:
cef1bfa821cb fixed problem with not passing request casties parents: 24 diff changeset	317 RESPONSE = self.REQUEST.RESPONSE
23 860ec92f99df added cross-site access header (for GET) casties parents: 22 diff changeset	318 RESPONSE.setHeader('Access-Control-Allow-Origin', '*')
24 f3811d9a36da start adding checkuploadtable casties parents: 23 diff changeset	319
26 2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	320 pt = getattr(self.template, '%s_index'%resultFormat, None)
2 61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	321 if pt is None:
26 2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	322 return "ERROR!! template %s_index not found"%resultFormat
2 61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	323
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	324 data = self.getListOfSchemas()
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	325 return pt(data=data)
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	326
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	327 def getListOfSchemas(self,username='guest'):
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	328 """return list of schemas"""
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	329 logging.debug("getlistofschemas")
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	330 # TODO: really look up schemas
9 76ac7a721273 more rework of templates casties parents: 8 diff changeset	331 data={'fields': (('schemas',),), 'rows': [('public',),]}
2 61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	332 return data
14 05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	333
24 f3811d9a36da start adding checkuploadtable casties parents: 23 diff changeset	334 def JSON_index(self,data):
f3811d9a36da start adding checkuploadtable casties parents: 23 diff changeset	335 """JSON index function"""
f3811d9a36da start adding checkuploadtable casties parents: 23 diff changeset	336 self.REQUEST.RESPONSE.setHeader("Content-Type", "application/json")
f3811d9a36da start adding checkuploadtable casties parents: 23 diff changeset	337 json.dump(data, self.REQUEST.RESPONSE)
f3811d9a36da start adding checkuploadtable casties parents: 23 diff changeset	338
f3811d9a36da start adding checkuploadtable casties parents: 23 diff changeset	339 def JSON_schema(self,data,schema):
f3811d9a36da start adding checkuploadtable casties parents: 23 diff changeset	340 """JSON index function"""
f3811d9a36da start adding checkuploadtable casties parents: 23 diff changeset	341 self.REQUEST.RESPONSE.setHeader("Content-Type", "application/json")
f3811d9a36da start adding checkuploadtable casties parents: 23 diff changeset	342 json.dump(data, self.REQUEST.RESPONSE)
f3811d9a36da start adding checkuploadtable casties parents: 23 diff changeset	343
f3811d9a36da start adding checkuploadtable casties parents: 23 diff changeset	344 def JSON_schema_table(self,data,tablename):
f3811d9a36da start adding checkuploadtable casties parents: 23 diff changeset	345 """JSON index function"""
f3811d9a36da start adding checkuploadtable casties parents: 23 diff changeset	346 self.REQUEST.RESPONSE.setHeader("Content-Type", "application/json")
f3811d9a36da start adding checkuploadtable casties parents: 23 diff changeset	347 json.dump(data, self.REQUEST.RESPONSE)
f3811d9a36da start adding checkuploadtable casties parents: 23 diff changeset	348
26 2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	349 def checkTable(self,resultFormat,schema,table,REQUEST=None,RESPONSE=None):
2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	350 """check the table.
2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	351 returns valid data fields and table name."""
2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	352 if REQUEST is None:
2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	353 REQUEST = self.REQUEST
2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	354 RESPONSE = REQUEST.RESPONSE
2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	355
2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	356 file = REQUEST.get("create_table_file",None)
2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	357 res = self.checkTableFromXML(schema, table, file)
2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	358 logging.debug("checkTable result=%s"%repr(res))
2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	359 # return the result as JSON
2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	360 if resultFormat == "JSON":
2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	361 RESPONSE.setHeader("Content-Type", "application/json")
2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	362 json.dump(res, RESPONSE)
2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	363
2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	364 elif resultFormat == "JSONHTML":
2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	365 RESPONSE.setHeader("Content-Type", "text/html")
2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	366 RESPONSE.write("<html>\n<body>\n<pre>")
2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	367 json.dump(res, RESPONSE)
2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	368 RESPONSE.write("</pre>\n</body>\n</html>")
2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	369
2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	370 else:
2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	371 return "ERROR: invalid resultFormat"
2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	372
2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	373 def checkTableFromXML(self,schema,table,data,REQUEST=None,RESPONSE=None):
2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	374 """check the table with the given XML data.
2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	375 returns valid data fields and table name."""
2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	376 logging.debug("checkTableFromXML schema=%s table=%s"%(schema,table))
2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	377 # clean table name
2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	378 tablename = sqlName(table)
2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	379 tableExists = self.hasTable(schema, table)
2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	380 if data is None:
2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	381 fieldNames = []
2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	382 else:
2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	383 # get list of field names from upload file
2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	384 fields = self.importExcelXML(schema,tablename,data,fieldsOnly=True)
2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	385
2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	386 res = {'tablename': tablename, 'table_exists': tableExists}
2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	387 res['fields'] = fields
2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	388 return res
2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	389
14 05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	390 def createEmptyTable(self,schema,table,fields):
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	391 """create a table with the given fields
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	392 returns list of created fields"""
15 5e3edf980813 more work on xml import casties parents: 14 diff changeset	393 logging.debug("createEmptyTable")
14 05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	394 sqlFields = []
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	395 for f in fields:
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	396 if isinstance(f,dict):
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	397 # {name: XX, type: YY}
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	398 name = sqlName(f['name'])
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	399 type = f['type']
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	400
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	401 else:
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	402 # name only
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	403 name = sqlName(f)
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	404 type = 'text'
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	405
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	406 sqlFields.append({'name':name, 'type':type})
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	407
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	408 # drop table if it exists
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	409 try:
16 cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	410 res = self.executeSQL('select * from "%s"."%s" where 1=0'%(schema,table))
cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	411 logging.debug("createemptytable: table %s.%s exists"%(schema,table))
17 ed997e639cfd more upload table casties parents: 16 diff changeset	412 self.executeSQL('drop table "%s"."%s"'%(schema,table),hasResult=False)
14 05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	413 except:
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	414 pass
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	415
15 5e3edf980813 more work on xml import casties parents: 14 diff changeset	416 fieldString = ", ".join(['"%s" %s'%(f['name'],f['type']) for f in sqlFields])
16 cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	417 sqlString = 'create table "%s"."%s" (%s)'%(schema,table,fieldString)
cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	418 logging.debug("createemptytable: SQL=%s"%sqlString)
26 2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	419 self.executeSQL(sqlString,hasResult=False)
14 05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	420 return sqlFields
26 2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	421
28 9e4f9cfd1edc start adding field structure to xml upload casties parents: 27 diff changeset	422 def createTableFromXML(self,schema,table,data, fields=None):
13 e2c73c077533 NEW - # 12: create table and upload data casties parents: 12 diff changeset	423 """create or replace a table with the given XML data"""
28 9e4f9cfd1edc start adding field structure to xml upload casties parents: 27 diff changeset	424 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	425 tablename = sqlName(table)
28 9e4f9cfd1edc start adding field structure to xml upload casties parents: 27 diff changeset	426 self.importExcelXML(schema, tablename, data, fields)
17 ed997e639cfd more upload table casties parents: 16 diff changeset	427 return {"tablename": tablename}
14 05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	428
28 9e4f9cfd1edc start adding field structure to xml upload casties parents: 27 diff changeset	429 def importExcelXML(self,schema,table,xmldata,fields=None,fieldsOnly=False):
14 05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	430 '''
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	431 Import XML file in Excel format into the table
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	432 @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	433 '''
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	434 from xml.dom.pulldom import parseString,parse
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	435
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	436 namespace = "urn:schemas-microsoft-com:office:spreadsheet"
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	437 containerTagName = "Table"
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	438 rowTagName = "Row"
16 cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	439 colTagName = "Cell"
14 05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	440 dataTagName = "Data"
26 2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	441 xmlFields = []
14 05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	442 sqlFields = []
16 cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	443 numFields = 0
14 05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	444 sqlInsert = None
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	445
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	446 logging.debug("import excel xml")
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	447
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	448 ret=""
16 cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	449 if isinstance(xmldata, str):
14 05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	450 logging.debug("importXML reading string data")
16 cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	451 doc=parseString(xmldata)
14 05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	452 else:
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	453 logging.debug("importXML reading file data")
16 cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	454 doc=parse(xmldata)
14 05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	455
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	456 cnt = 0
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	457 while True:
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	458 node=doc.getEvent()
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	459
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	460 if node is None:
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	461 break
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	462
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	463 else:
16 cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	464 #logging.debug("tag=%s"%node[1].localName)
14 05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	465 if node[1].localName is not None:
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	466 tagName = node[1].localName.lower()
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	467 else:
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	468 # ignore non-tag nodes
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	469 continue
16 cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	470
14 05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	471 if tagName == rowTagName.lower():
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	472 # start of row
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	473 doc.expandNode(node[1])
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	474 cnt += 1
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	475 if cnt == 1:
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	476 # first row -- field names
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	477 names=node[1].getElementsByTagNameNS(namespace, dataTagName)
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	478 for name in names:
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	479 fn = getTextFromNode(name)
26 2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	480 xmlFields.append({'name':sqlName(fn),'type':'text'})
14 05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	481
26 2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	482 if fieldsOnly:
14 05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	483 # return just field names
26 2b73f868d34f NEW - # 12: create table and upload data casties parents: 25 diff changeset	484 return xmlFields
14 05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	485
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	486 # create table
28 9e4f9cfd1edc start adding field structure to xml upload casties parents: 27 diff changeset	487 if fields is None:
9e4f9cfd1edc start adding field structure to xml upload casties parents: 27 diff changeset	488 sqlFields = self.createEmptyTable(schema, table, xmlFields)
16 cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	489 numFields = len(sqlFields)
15 5e3edf980813 more work on xml import casties parents: 14 diff changeset	490 fieldString = ", ".join(['"%s"'%f['name'] for f in sqlFields])
14 05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	491 valString = ", ".join(["%s" for f in sqlFields])
16 cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	492 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	493 #logging.debug("importexcelsql: sqlInsert=%s"%sqlInsert)
14 05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	494
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	495 else:
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	496 # following rows are data
16 cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	497 colNodes=node[1].getElementsByTagNameNS(namespace, colTagName)
14 05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	498 data = []
16 cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	499 hasData = False
cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	500 for colNode in colNodes:
cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	501 dataNodes=colNode.getElementsByTagNameNS(namespace, dataTagName)
cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	502 if len(dataNodes) > 0:
cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	503 val = getTextFromNode(dataNodes[0])
cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	504 hasData = True
cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	505 else:
cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	506 val = None
cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	507
14 05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	508 data.append(val)
05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	509
16 cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	510 if not hasData:
cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	511 # ignore empty rows
cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	512 continue
cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	513
cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	514 # fix number of data fields
cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	515 if len(data) > numFields:
cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	516 del data[numFields:]
cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	517 elif len(data) < numFields:
cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	518 missFields = numFields - len(data)
cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	519 data.extend(missFields * [None,])
cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	520
cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	521 logging.debug("importexcel sqlinsert=%s data=%s"%(sqlInsert,data))
17 ed997e639cfd more upload table casties parents: 16 diff changeset	522 self.executeSQL(sqlInsert, data, hasResult=False)
14 05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	523
16 cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	524 return cnt
21 a67b7c1f7ec5 Merge with Falks GIS stuff 78e70dfa7ad6b27d10d490f9ae8820306e4fe5d4 casties parents: 20 19 diff changeset	525
19 78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	526 # Methods for GoogleMaps creation
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	527 def showGoogleMap(self,schema='chgis',table='mpdl',id=[],doc=None):
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	528 logging.debug("showGoogleMap")
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	529 data = self.getDataForGoogleMap(schema,table,id,doc)
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	530 kml=self.getKMLname(data=data,table=table)
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	531 googleMap_page=self.htmlHead()+str(self.getGoogleMapString(kml=kml))
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	532 return googleMap_page
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	533
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	534 def getDataForGoogleMap(self,schema='chgis',table='mpdl',id=[],doc=None):
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	535 logging.debug("getDataForGoogleMap")
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	536 qstr="SELECT * FROM "+schema+"."+table
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	537 try:
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	538 if id!=[]:
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	539 qstr=qstr+" WHERE "
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	540 for id_item in id.split(","):
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	541 if table=='mpdl':
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	542 qstr=qstr+" mpdl_xmlsource_id = '"+id_item+ "' OR"
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	543 else:
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	544 qstr=qstr+" cast(id as text) LIKE '"+id_item+ "' OR"
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	545 qstr=str(qstr).rsplit(" ",1)[0] #to remove last " and "
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	546 data=self.ZSQLSimpleSearch(qstr)
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	547 return data
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	548 except:
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	549 return qstr
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	550
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	551 def getKMLname(self,data=[],table=""):
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	552 logging.debug("getKMLname")
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	553 #session=context.REQUEST.SESSION
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	554 kml4Marker="<kml xmlns=\'http://www.opengis.net/kml/2.2\'><Document><Style id=\'marker_icon\'><IconStyle><scale>15</scale><Icon><href>http://chinagis.mpiwg-berlin.mpg.de/chinagis/images/dot_red.png</href></Icon></IconStyle></Style>\n"
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	555 initializeStringForGoogleMaps=""
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	556 #doLine=container.getVar('doLine')
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	557 # Mapping a set of points from table-based SQL-query:
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	558 if data!=None:
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	559 try:
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	560 SQL="""SELECT \"attribute with gis_id\" FROM public.metadata WHERE tablename LIKE '"""+table+"""'"""
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	561 gisIDattribute=self.ZSQLSimpleSearch(SQL)
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	562 except:
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	563 return "table not registered within metadata"
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	564 for dataset in data:
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	565 try:
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	566 xCoord=getattr(dataset,'longitude')
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	567 yCoord=getattr(dataset,'latitude')
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	568 except:
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	569 try:
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	570 xCoord=getattr(dataset,'x_coord')
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	571 yCoord=getattr(dataset,'y_coord')
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	572 except:
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	573 # try:
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	574 gisID=getattr(dataset,getattr(gisIDattribute[0],'attribute with gis_id'))
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	575 coords=self.getPoint4GISid(gisID)
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	576 if coords!=None:
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	577 xCoord=coords[0]
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	578 yCoord=coords[1]
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	579 # except:
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	580 # return "no coordinates found"
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	581 if float(xCoord)!=0:
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	582 if float(yCoord)!=0:
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	583 kml4Marker=kml4Marker+"<Placemark>"
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	584 kml4Marker=kml4Marker+"<description> <![CDATA[<b>"
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	585 for values in dataset:
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	586 if values != (None, None):
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	587 if str(values).find('name')>-1:
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	588 kml4Marker=kml4Marker+"<name>"+str(values[1])+"</name>\n"
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	589 continue
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	590 elif str(values).find('place')>-1:
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	591 kml4Marker=kml4Marker+"<name>"+str(values[1])+"</name>\n"
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	592 continue
14 05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	593
19 78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	594 kml4Marker=kml4Marker+str(values)+": "
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	595 attribute_string=str(values).replace("'","__Apostroph__")
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	596 attribute_string=str(attribute_string).replace('"','__DoubleApostroph__')
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	597 attribute_string=str(attribute_string).replace(';','__$$__')
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	598 attribute_string=str(attribute_string).replace('&','&')
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	599 if str(attribute_string).find('http')>-1:
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	600 attribute_string='<A HREF=' + str(attribute_string) + ' target=_blank>' + str(attribute_string) + '</A>'
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	601 kml4Marker=kml4Marker+attribute_string+"</a><br>\n"
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	602
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	603 kml4Marker=kml4Marker+"]]></description>\n"
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	604 kml4Marker=kml4Marker+"<styleURL>#marker_icon</styleURL>\n"
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	605 kml4Marker=kml4Marker+"<Point>"
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	606
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	607 kml4Marker=kml4Marker+"<coordinates>"+str(xCoord)+","+str(yCoord)+",0</coordinates>\n"
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	608 kml4Marker=kml4Marker+"</Point>\n"
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	609 kml4Marker=kml4Marker+"</Placemark>\n"
14 05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	610
19 78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	611 kml4Marker=kml4Marker+"</Document>\n</kml>"
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	612 kmlFileName="marker"+str(time.time())+".kml"
14 05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	613
19 78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	614 # kml4Marker=str(kml4Marker).replace('&','$$')
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	615 # kml4Marker=str(kml4Marker).replace(';','__$$__')
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	616 # kml4Marker=str(kml4Marker).replace('#','__SHARP__')
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	617 initializeStringForGoogleMaps="""onload=\"initialize(\'http://chinagis.mpiwg-berlin.mpg.de/chinagis/REST/daten/"""+kmlFileName+"""\')\""""#+str(data)
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	618 initializeStringForGoogleMaps=initializeStringForGoogleMaps.replace("None","0")
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	619 isLoadReady='false'
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	620 while isLoadReady=='false':
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	621 isLoadReady=self.RESTwrite2File(self.daten,kmlFileName,kml4Marker)
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	622
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	623 return initializeStringForGoogleMaps
14 05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	624
19 78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	625 def getGoogleMapString(self,kml):
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	626 logging.debug("getGoogleMapString")
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	627 printed= '<body %s> '%kml +"""\n <div id="map_canvas" style="width: 98%; height: 95%"> </div> \n </body>" \n </html>"""
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	628 return printed
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	629
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	630 def getPoint4GISid(self,gis_id):
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	631 j=0
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	632 while (True):
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	633 j=j+1
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	634 if (j>100): # FJK: just to prevent endless loops
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	635 break
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	636 if (gis_id.isdigit()): # FJK: regular exit from while-loop
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	637 break
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	638 else:
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	639 gis_id=gis_id.strip('abcdefghijklmnopqrstuvwxyz_') # FJK: to strip all letters
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	640 gis_id=gis_id.strip() # FJK: to strip all whitespaces
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	641 resultpoint = [0,0]
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	642 results = None
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	643 try:
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	644 if int(gis_id)>0:
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	645 SQL="SELECT x_coord,y_coord FROM chgis.chgis_coords WHERE gis_id LIKE cast("+ str(gis_id) +" as text);"
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	646 results=self.ZSQLSimpleSearch(SQL)
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	647 #print results
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	648 if results != None:
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	649 for result in results:
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	650 resultpoint=[getattr(result,str('x_coord')),getattr(result,str('y_coord'))]
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	651 if resultpoint !=[0,0]:
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	652 return resultpoint
14 05933707897f NEW - # 12: create table and upload data casties parents: 13 diff changeset	653 else:
19 78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	654 coords=self.getCoordsFromREST_gisID(joinid)
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	655 SQL="INSERT INTO chgis.chgis_coords (gis_id,x_coord,y_coord) VALUES (" +gis_id+ "," +coords[0][1]+ "," +coords[0][0]+ "); ANALYZE chgis.chgis_coords;"
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	656 returnstring=self.ZSQLSimpleSearch(SQL)
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	657 return coords[0]
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	658 except:
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	659 error="gis_id not to interpretable:"+str(gis_id)
16 cbb73d103152 NEW - # 12: create table and upload data casties parents: 15 diff changeset	660
19 78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	661 def getCoordsFromREST_gisID(self,gis_id):
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	662 coordlist=[]
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	663 i=0
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	664 while (i<5 and coordlist==[]):
13 e2c73c077533 NEW - # 12: create table and upload data casties parents: 12 diff changeset	665
19 78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	666 urlresponse=container.urlFunctions.zUrlopenParseString(container.urlFunctions.zUrlopenRead("http://chgis.hmdc.harvard.edu/xml/id/"+gis_id))
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	667 baseDocElement=container.urlFunctions.zUrlopenDocumentElement(urlresponse)
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	668 childnodes=container.urlFunctions.zUrlopenChildNodes(baseDocElement)
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	669 itemnodes=container.urlFunctions.zUrlopenGetElementsByTagName(baseDocElement,'item')
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	670
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	671 for i in range(0,container.urlFunctions.zUrlopenLength(itemnodes)):
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	672 itemnode=container.urlFunctions.zUrlopenGetItem(itemnodes,i)
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	673 itemspatialnodes=container.urlFunctions.zUrlopenGetElementsByTagName(itemnode,'spatial')
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	674 for j in range(0,container.urlFunctions.zUrlopenLength(itemspatialnodes)):
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	675 coord=[]
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	676 itemspatialnode= container.urlFunctions.zUrlopenGetItem(itemspatialnodes,j)
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	677 itemspatiallatnodes=container.urlFunctions.zUrlopenGetElementsByTagName(itemspatialnode,'degrees_latitude')
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	678 for k in range(0,container.urlFunctions.zUrlopenLength(itemspatiallatnodes)):
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	679 itemspatiallatnode= container.urlFunctions.zUrlopenGetItem(itemspatiallatnodes,k)
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	680 coord.append(container.urlFunctions.zUrlopenGetTextData(itemspatiallatnode))
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	681 itemspatiallngnodes=container.urlFunctions.zUrlopenGetElementsByTagName(itemspatialnode,'degrees_longitude')
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	682 for k in range(0,container.urlFunctions.zUrlopenLength(itemspatiallngnodes)):
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	683 itemspatiallngnode= container.urlFunctions.zUrlopenGetItem(itemspatiallngnodes,k)
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	684 coord.append(container.urlFunctions.zUrlopenGetTextData(itemspatiallngnode))
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	685 coordlist.append(coord)
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	686 gis_id= "_"+gis_id
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	687 return coordlist
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	688
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	689 # End for GoogleMaps creation
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	690
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	691 def RESTwrite2File(self,datadir, name,text):
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	692 # try:
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	693 fileid=name
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	694 if fileid in datadir.objectIds():
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	695 datadir.manage_delObjects(fileid)
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	696 newfile=open(name,'w')
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	697 newfile.write(text)
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	698 newfile.close()
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	699 file4Read=open(name,'r')
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	700 fileInZope=datadir.manage_addFile(id=fileid,file=file4Read)
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	701 return "Write successful"
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	702 # except:
78e70dfa7ad6 GoogleMaps related functions fknauft parents: 18 diff changeset	703 # return "Could not write"
13 e2c73c077533 NEW - # 12: create table and upload data casties parents: 12 diff changeset	704
2 61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	705
4 e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly. casties parents: 2 diff changeset	706 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	707 REQUEST=None):
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly. casties parents: 2 diff changeset	708 """Change the object"""
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly. casties parents: 2 diff changeset	709 if title is not None:
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly. casties parents: 2 diff changeset	710 self.title = title
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly. casties parents: 2 diff changeset	711
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly. casties parents: 2 diff changeset	712 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	713 self.connection_id = connection_id
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly. casties parents: 2 diff changeset	714
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly. casties parents: 2 diff changeset	715 #checkPermission=getSecurityManager().checkPermission
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly. casties parents: 2 diff changeset	716 REQUEST.RESPONSE.redirect('manage_main')
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly. casties parents: 2 diff changeset	717
2 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 manage_addRestDbInterfaceForm=PageTemplateFile('zpt/addRestDbInterface',globals())
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	720
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	721 def manage_addRestDbInterface(self, id, title='', label='', description='',
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	722 createPublic=0,
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	723 createUserF=0,
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	724 REQUEST=None):
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	725 """Add a new object with id id."""
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	726
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	727 ob=RestDbInterface(str(id),title)
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	728 self._setObject(id, ob)
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	729
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	730 #checkPermission=getSecurityManager().checkPermission
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	731 REQUEST.RESPONSE.redirect('manage_main')
61a3764cd5fb new version RestDbInterface with working traversal and templates casties parents: diff changeset	732
5 7539a9e69365 small fix casties parents: 4 diff changeset	733

Mercurial > hg > ChinaGisRestApi

annotate RestDbInterface.py @ 29:0b9f8cca6744