source: ZMS/trunk/zmssqldb.py @ 1823

Revision 1823, 68.6 KB checked in by fhoffmann, 3 months ago (diff)

SQL-Model-Mapper patched for SQLite handling of quotation marks
Line 
1################################################################################
2# zmssqldb.py
3#
4# This program is free software; you can redistribute it and/or
5# modify it under the terms of the GNU General Public License
6# as published by the Free Software Foundation; either version 2
7# of the License, or (at your option) any later version.
8#
9# This program is distributed in the hope that it will be useful,
10# but WITHOUT ANY WARRANTY; without even the implied warranty of
11# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
12# GNU General Public License for more details.
13#
14# You should have received a copy of the GNU General Public License
15# along with this program; if not, write to the Free Software
16# Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.
17################################################################################
18
19# Imports.
20from AccessControl import ClassSecurityInfo
21from App.special_dtml import HTMLFile
22from types import StringTypes
23import Globals
24import copy
25import os
26import sys
27import urllib
28import tempfile
29import time
30import zExceptions
31# Product Imports.
32from zmsobject import ZMSObject
33import _fileutil
34import _globals
35import _objattrs
36import _xmllib
37
38
39################################################################################
40################################################################################
41###
42###   Constructor
43###
44################################################################################
45################################################################################
46manage_addZMSSqlDbForm = HTMLFile('manage_addzmssqldbform', globals())
47def manage_addZMSSqlDb(self, lang, _sort_id, REQUEST, RESPONSE):
48  """ manage_addZMSSqlDb """
49 
50  ##### Create ####
51  id_prefix = _globals.id_prefix(REQUEST.get('id_prefix','e'))
52  new_id = self.getNewId(id_prefix)
53  obj = ZMSSqlDb(new_id,_sort_id+1)
54  self._setObject(obj.id, obj)
55 
56  obj = getattr(self,obj.id)
57  ##### Object State ####
58  obj.setObjStateNew(REQUEST)
59  ##### Init Properties ####
60  obj.manage_changeProperties(lang,REQUEST,RESPONSE)
61  ##### VersionManager ####
62  obj.onChangeObj(REQUEST)
63 
64  ##### Normalize Sort-IDs ####
65  self.normalizeSortIds(id_prefix)
66 
67  # Return with message.
68  if REQUEST.RESPONSE:
69    message = self.getZMILangStr('MSG_INSERTED')%obj.display_type(REQUEST)
70    REQUEST.RESPONSE.redirect('%s/%s/manage_main?lang=%s&manage_tabs_message=%s'%(self.absolute_url(),obj.id,lang,urllib.quote(message)))
71
72
73################################################################################
74################################################################################
75###   
76###   Class
77###   
78################################################################################
79################################################################################
80
81class ZMSSqlDb(ZMSObject):
82
83    # Create a SecurityInfo for this class. We will use this
84    # in the rest of our class definition to make security
85    # assertions.
86    security = ClassSecurityInfo()
87
88
89    # Properties.
90    # -----------
91    meta_type = meta_id = "ZMSSqlDb"
92
93    # Management Options.
94    # -------------------
95    manage_options = (
96    {'label': 'TAB_EDIT',        'action': 'manage_main'},
97    {'label': 'TAB_IMPORTEXPORT',    'action': 'manage_importexport'},
98    {'label': 'TAB_PROPERTIES',    'action': 'manage_properties'},
99    {'label': 'TAB_CONFIGURATION',    'action': 'manage_configuration'},
100    {'label': 'SQL',    'action': 'manage_sql'},
101    )
102
103    # Management Permissions.
104    # -----------------------
105    __authorPermissions__ = (
106        'manage','manage_main','manage_main_iframe','manage_workspace',
107        'manage_moveObjUp','manage_moveObjDown','manage_moveObjToPos',
108        'manage_cutObjects','manage_copyObjects','manage_pasteObjs',
109        'manage_userForm', 'manage_user',
110        'manage_importexport', 'manage_import', 'manage_export',
111        'manage_exportexcel',
112        )
113    __administratorPermissions__ = (
114        'manage_properties','manage_changeProperties','manage_changeTempBlobjProperty',
115        'manage_configuration', 'manage_changeConfiguration',
116        'manage_sql',
117        )
118    __ac_permissions__=(
119        ('ZMS Author', __authorPermissions__),
120        ('ZMS Administrator', __administratorPermissions__),
121        )
122
123    # Management Interface.
124    # ---------------------
125    actions = HTMLFile('dtml/ZMSSqlDb/actions', globals())
126    input_form = HTMLFile('dtml/ZMSSqlDb/input_form', globals())
127    input_details = HTMLFile('dtml/ZMSSqlDb/input_details', globals())
128    browse_db = HTMLFile('dtml/ZMSSqlDb/browse_db', globals())
129    intersection_sql = HTMLFile('dtml/ZMSSqlDb/intersection_sql', globals())
130    manage_main = HTMLFile('dtml/ZMSSqlDb/manage_main', globals())
131    manage_importexport = HTMLFile('dtml/ZMSSqlDb/manage_importexport', globals())
132    manage_properties = HTMLFile('dtml/ZMSSqlDb/manage_properties', globals())
133    manage_sql = HTMLFile('dtml/ZMSSqlDb/manage_sql', globals())
134    manage_configuration = HTMLFile('dtml/ZMSSqlDb/manage_configuration', globals())
135    manage_exportexcel = HTMLFile('dtml/ZMSSqlDb/manage_exportexcel', globals())
136
137
138    # Valid Types.
139    # ------------
140    valid_types = {
141      'blob':{},
142      'date':1,
143      'datetime':1,
144      'details':{},
145      'fk':{},
146      'html':1,
147      'multiselect':{},
148      'multimultiselect':{},
149      'pk':1,
150      'checkbox':1,
151      'password':1,
152      'richtext':1,
153      'text':1,
154      'time':1,
155      'url':1,
156    }
157
158
159    ############################################################################
160    ###
161    ###   CONSTRUCTOR
162    ###
163    ############################################################################
164
165    # --------------------------------------------------------------------------
166    #  ZMSSqlDb.filteredChildNodes:
167    # --------------------------------------------------------------------------
168    def filteredChildNodes(self, REQUEST={}, meta_types=None):
169      return []
170
171
172    # --------------------------------------------------------------------------
173    #  ZMSSqlDb.getModelContainer:
174    # --------------------------------------------------------------------------
175    def getModelContainer( self):
176      id = 'sqlmodel.xml'
177      if id not in self.objectIds(['DTML Method']):
178        model_xml =  getattr(self,'model_xml','<list>\n</list>')
179        self.manage_addDTMLMethod( id, 'SQL-Model (XML)', model_xml)
180      return getattr( self, id)
181
182
183    # --------------------------------------------------------------------------
184    #  ZMSSqlDb.getModel:
185    # --------------------------------------------------------------------------
186    def getModel(self):
187      container = self.getModelContainer()
188      container_xml = container.raw
189      model_xml =  getattr(self,'model_xml',None)
190      if model_xml is None:
191        model_xml = '<list>\n</list>'
192        self.model_xml = model_xml
193        self.model = []
194      if container_xml != model_xml:
195        self.model_xml = container_xml
196        self.model = self.parseXmlString(self.model_xml)
197      return self.model
198
199
200    # --------------------------------------------------------------------------
201    #  ZMSSqlDb.setModel:
202    # --------------------------------------------------------------------------
203    def setModel(self, newModel):
204      container = self.getModelContainer()
205      container.manage_edit( title=container.title, data=newModel)
206
207
208    # --------------------------------------------------------------------------
209    #  ZMSSqlDb.record_encode__:
210    # --------------------------------------------------------------------------
211    def record_encode__(self, cols, record, encoding='utf-8'):
212      charset = getattr(self,'charset','utf-8')
213      row = {}
214      for col in cols:
215        k = col['id']
216        v = record[k]
217        if self.getConfProperty('ZMSSqlDb.record_encode__.k.lower'):
218          k = k.lower()
219        if v is not None and (type(v) is str or type(v) is unicode):
220          try:
221            v = unicode(v,charset).encode(encoding)
222          except:
223            row[k+'_exception'] = _globals.writeError( self, '[record_encode__]: can\'t %s'%k)
224        row[k] = v
225      return row
226
227
228    # --------------------------------------------------------------------------
229    #  ZMSSqlDb.getDA:
230    #
231    #  Return Database Adapter (DA).
232    # --------------------------------------------------------------------------
233    def getDA(self):
234      da = None
235      conn_id = getattr( self, "connection_id", None)
236      if conn_id is not None:
237        da = getattr(self,conn_id)
238        if da.meta_type == 'Z MySQL Database Connection':
239          # Try to re-connect if not connected.
240          try:
241            dbc = da._v_database_connection
242          except AttributeError:
243            da.connect(da.connection_string)
244            dbc = da._v_database_connection
245          # Try to set character-set to utf-8.
246          try:
247            dbc.query('SET NAMES utf8')
248            dbc.query('SET CHARACTER SET utf8')
249          except:
250            pass
251      return da
252
253
254    # --------------------------------------------------------------------------
255    #  ZMSSqlDb.sql_quote__:
256    # --------------------------------------------------------------------------
257    def sql_quote__(self, tablename, columnname, v):
258      da = self.getDA()
259      gadfly = da.meta_type == 'Z Gadfly Database Connection'
260      entities = self.getEntities()
261      entity = filter(lambda x: x['id'].upper() == tablename.upper(), entities)[0]
262      col = (filter(lambda x: x['id'].upper() == columnname.upper(), entity['columns'])+[{'type':'string'}])[0]
263      if col.get('nullable') and v in ['',None]:
264        if gadfly:
265          return "''"
266        else:
267          return "NULL"
268      elif col['type'] in ['int']:
269        try:
270          return str(int(str(v)))
271        except:
272          if gadfly:
273            return "''"
274          else:
275            return "NULL"
276      elif col['type'] in ['float']:
277        try:
278          return str(float(str(v)))
279        except:
280          if da.meta_type == 'Z Gadfly Database Connection':
281            return "''"
282          else:
283            return "NULL"
284      elif col['type'] in ['date','datetime','time']:
285        try:
286          d = self.parseLangFmtDate(v)
287          if d is None:
288            raise zExceptions.InternalError
289          return "'%s'"%self.getLangFmtDate(d,'eng','%s_FMT'%col['type'].upper())
290        except:
291          if da.meta_type == 'Z Gadfly Database Connection':
292            return "''"
293          else:
294            return "NULL"
295      else:
296        v = unicode(str(v),'utf-8').encode(getattr(self,'charset','utf-8'))
297        if v.find("\'") >= 0:
298          v=''.join(v.split("\'"))
299        return "'%s'"%v
300
301
302    """
303    Makes all changes made since the previous commit/rollback permanent and
304    releases any database locks currently held by the Connection object.
305    """
306    def commit(self):
307      da = self.getDA()
308      dbc = da._v_database_connection
309      conn = dbc.getconn(False)
310      conn.commit()
311
312
313    """
314    Undoes all changes made in the current transaction and releases any database
315    locks currently held by this Connection object.
316    """
317    def rollback(self):
318      da = self.getDA()
319      dbc = da._v_database_connection
320      conn = dbc.getconn(False)
321      conn.rollback()
322
323
324    """
325    Execute sql-statement.
326    Supports parameter-markers of mxODBCZopeDA.
327    @param sql: The sql-statement
328    @type sql: C{str}
329    @param params: The values for the parameters.
330    @type params: C{tuple}
331    @param max_rows: The maximum number of rows (default: None, unlimited)
332    @type max_rows: C{str}
333    """
334    def execute(self, sql, params=(), max_rows=None):
335      da = self.getDA()
336      dbc = da._v_database_connection
337      return dbc.execute(sql,params,max_rows)
338
339
340    """
341    Assemble query-result.
342    @return: Dictionary: columns C{list}, records C{list}.
343    @rtype: C{dict}
344    """
345    def assemble_query_result(self, res, encoding=None):
346      from cStringIO import StringIO
347      from Shared.DC.ZRDB.Results import Results
348      from Shared.DC.ZRDB import RDB
349      if type(res) is str:
350        f=StringIO()
351        f.write(res)
352        f.seek(0)
353        result = RDB.File(f)
354      else:
355        result = Results(res)
356      columns = []
357      for result_column in result._searchable_result_columns():
358        colName = result_column['name']
359        colLabel = ''
360        for s in colName.split('_'):
361          colLabel += s.capitalize()
362        try:
363          colType = {'i':'int','n':'float','t':'string','s':'string','d':'datetime','l':'string'}[result_column['type']]
364        except:
365          colType = result_column.get('type',None)
366          _globals.writeError(self,'[query]: Column ' + colName + ' has unknown type ' + str(colType) + '!')
367        column = {}
368        column['id'] = colName
369        column['key'] = colName
370        column['label'] = colLabel
371        column['name'] = colLabel
372        column['type'] = colType
373        column['sort'] = 1
374        columns.append(column)
375      if encoding:
376        result = map(lambda x: self.record_encode__(columns,x,encoding), result)
377      return {'columns':columns,'records':result}
378
379
380    """
381    Execute select-statement.
382    @param sql: The select-statement
383    @type sql: C{str}
384    @param max_rows: The maximum number of rows (default: None, unlimited)
385    @type max_rows: C{str}
386    @return: Dictionary: columns C{list}, records C{list}.
387    @rtype: C{dict}
388    """
389    def query(self, sql, max_rows=None, encoding=None):
390      if max_rows is None:
391        max_rows = getattr(self,'max_rows',999)
392      _globals.writeBlock( self, '[query]: sql=%s, max_rows=%i'%(sql,max_rows))
393      da = self.getDA()
394      dbc = da._v_database_connection
395      if da.meta_type == 'Z SQLite Database Connection': sql = str(sql)
396      return self.assemble_query_result(dbc.query(sql,max_rows),encoding)
397
398
399    """
400    Execute modify-statement.
401    @param sql: The modify-statement
402    @type sql: C{str}
403    @return: Number of affected rows.
404    @rtype: C{int}
405    """
406    def executeQuery(self, sql):
407      from cStringIO import StringIO
408      from Shared.DC.ZRDB.Results import Results
409      from Shared.DC.ZRDB import RDB
410      _globals.writeBlock( self, '[executeQuery]: sql=%s'%sql)
411      da = self.getDA()
412      dbc = da._v_database_connection
413      res = dbc.query(sql)
414      if type(res) is str:
415        f=StringIO()
416        f.write(res)
417        f.seek(0)
418        result=RDB.File(f)
419      else:
420        result=Results(res)
421      return len(result)
422
423
424    # --------------------------------------------------------------------------
425    #  ZMSSqlDb.getEntityColumn:
426    # --------------------------------------------------------------------------
427    def getEntityColumn(self, tableName, columnName):
428      columns = self.getEntity( tableName)['columns']
429      return filter(lambda x: x['id'].upper() == columnName.upper(), columns)[0]
430
431
432    # --------------------------------------------------------------------------
433    #  ZMSSqlDb.getEntity:
434    # --------------------------------------------------------------------------
435    def getEntity(self, tableName):
436      entities = self.getEntities()
437      return filter(lambda x: x['id'].upper() == tableName.upper(), entities)[0]
438
439
440    # --------------------------------------------------------------------------
441    #  ZMSSqlDb.getEntities:
442    # --------------------------------------------------------------------------
443    def getEntities(self):
444
445      #-- [ReqBuff]: Fetch buffered value from Http-Request.
446      REQUEST = self.REQUEST
447      reqBuffId = 'getEntities'
448      try:
449        return self.fetchReqBuff( reqBuffId, REQUEST, True)
450      except:
451        pass
452      print "getEntities"
453     
454      entities = []
455      da = self.getDA()
456      if da is None: return entities
457     
458      tableBrwsrs = da.tpValues()
459     
460      # +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-
461      # +- ENTITES
462      # +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-
463     
464      #-- for custom entities please refer to $ZMS_HOME/conf/db/getEntities.Oracle.dtml
465      method = getattr(self,'getEntities%s'%self.connection_id,None)
466      if method is not None:
467        entities = method( self, REQUEST)
468     
469      #-- retrieve entities from table-browsers
470      if len( entities) == 0:
471        for tableBrwsr in tableBrwsrs:
472          tableName = str(getattr(tableBrwsr,'Name',getattr(tableBrwsr,'name',None))())
473          tableType = str(getattr(tableBrwsr,'Type',getattr(tableBrwsr,'type',None))())
474          if tableType.upper() == 'TABLE':
475            # +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-
476            # +- COLUMNS
477            # +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-
478            cols = []
479            try:
480              columnBrwsrs = []
481              if da.meta_type == 'Z SQLite Database Connection':
482                for columnBrwsr in tableBrwsr.tpValues():
483                  desc = getattr(columnBrwsr,'Description',getattr(columnBrwsr,'description',None))().upper()
484                  desc = desc[desc.find("(")+1:desc.rfind(")")]
485                  for cc in desc.split(","):
486                    c = ''
487                    for l in cc.split("\n"):
488                      if l.find('--') >= 0:
489                        l = l[:l.find('--')]
490                      l = l.strip()
491                      if len(l) > 0:
492                        c += l + ' '
493                    cl = filter(lambda x: len(x.strip()) > 0, c.split(' '))
494                    if len(cl) >= 2:
495                      cid = cl[0]
496                      if cid.startswith('"') and cid.endswith('"'):
497                        cid = cid[1:-1]
498                      ucid = cid.upper()
499                      uctype = cl[1].upper()
500                      if not ucid in ['CHECK','FOREIGN','PRIMARY'] and not uctype.startswith('KEY') and not uctype.startswith('(') and not ucid.startswith('\''):
501                        col = {}
502                        col["id"] = cid
503                        col["description"] = ' '.join(cl[1:])
504                        columnBrwsrs.append(col)
505              else:
506                for columnBrwsr in tableBrwsr.tpValues():
507                  col = {}
508                  col["id"] = columnBrwsr.tpId()
509                  col["description"] = getattr(columnBrwsr,'Description',getattr(columnBrwsr,'description',None))().upper()
510                  columnBrwsrs.append(col)
511              for columnBrwsr in columnBrwsrs:
512                colId = columnBrwsr["id"]
513                colDescr = columnBrwsr["description"]
514                colType = 'string'
515                colSize = None
516                if colDescr.find('INT') >= 0:
517                  colType = 'int'
518                elif colDescr.find('DATE') >= 0 or \
519                     colDescr.find('TIME') >= 0:
520                  colType = 'datetime'
521                elif colDescr.find('CHAR') >= 0 or \
522                     colDescr.find('STRING') >= 0:
523                  colSize = 128
524                  i = colDescr.find('(')
525                  if i >= 0:
526                    j = colDescr.find(')')
527                    if j >= 0:
528                      colSize = int(colDescr[i+1:j])
529                  if colSize > 128:
530                    colType = 'text'
531                  else:
532                    colType = 'string'
533                colId = unicode(colId).encode('utf-8')
534                col = {}
535                col['key'] = colId
536                col['description'] = colDescr.strip()
537                col['id'] = col['key']
538                col['index'] = int(col.get('index',len(cols)))
539                col['label'] = ' '.join( map( lambda x: x.capitalize(), colId.split('_'))).strip()
540                col['name'] = col['label']
541                col['mandatory'] = colDescr.find('NOT NULL') > 0 or da.meta_type == 'Z Gadfly Database Connection'
542                col['type'] = colType
543                col['sort'] = 1
544                col['nullable'] = not col['mandatory']
545                # Add Column.
546                cols.append(col)
547            except:
548              _globals.writeError(self,'[getEntities]')
549            # +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-
550            # +- TABLE
551            # +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-
552            if len(cols) > 0:
553              entity = {}
554              entity['id'] = tableName
555              entity['type'] = 'table'
556              entity['label'] = ' '.join( map( lambda x: x.capitalize(), tableName.split('_'))).strip()
557              entity['sort_id'] = entity['label'].upper()
558              entity['columns'] = self.sort_list(cols,'index')
559              # Add Table.
560              entities.append(entity)
561     
562      #-- Custom properties
563      model = self.getModel()
564      s = []
565      for entity in entities:
566        tableName = entity['id']
567        tableInterface = entity.get('interface','')
568        cols = []
569        colNames = []
570        for col in entity['columns']:
571          colName = col['id'].upper()
572          # Set custom column-properties
573          for modelTable in filter(lambda x: x['id'].upper() == tableName.upper(), model):
574            for modelTableCol in filter(lambda x: x['id'].upper() == colName, modelTable.get('columns',[])):
575              for modelTableColProp in filter(lambda x: x not in ['id'], modelTableCol.keys()):
576                col[modelTableColProp] = modelTableCol[modelTableColProp]
577          cols.append(col)
578          colNames.append(colName)
579        # Add custom columns
580        for modelTable in filter(lambda x: x['id'].upper() == tableName.upper(), model):
581          tableInterface = modelTable.get('interface',tableInterface)
582          for modelTableCol in filter(lambda x: x['id'].upper() not in colNames, modelTable.get('columns',[])):
583            col = modelTableCol
584            col['id'] = col.get('id','?')
585            col['index'] = int(col.get('index',len(cols)))
586            col['type'] = col.get('type','?')
587            col['key'] = col.get('key',col.get('id'))
588            col['label'] = col.get('label',col.get('id'))
589            col['stereotypes'] = self.intersection_list( self.valid_types.keys(), col.keys())
590            col['not_found'] = col.get('description') is None and len(col.get('stereotypes',[]))==0
591            cols.insert(col['index'], col)
592            colNames.append(col['id'].upper())
593        entity['interface'] = tableInterface
594        entity['columns'] = self.sort_list(cols,'index')
595        # Set custom table-properties
596        for modelTable in filter(lambda x: x['id'].upper() ==tableName.upper(), model):
597          for modelTableProp in filter(lambda x: x not in ['columns'], modelTable.keys()):
598            entity[modelTableProp] = modelTable[modelTableProp]
599            entity['sort_id'] = entity['label'].upper()
600        # Add
601        s.append((entity['label'],entity))
602     
603      #-- Custom entities.
604      for entity in model:
605        tableName = entity['id']
606        if entity.has_key('not_found'):
607          del entity['not_found']
608        if tableName.upper() not in map( lambda x: x['id'].upper(), entities):
609          cols = entity.get('columns',[])
610          entity['id'] = tableName
611          entity['type'] = entity.get('type','table')
612          entity['label'] = entity.get('label',' '.join( map( lambda x: x.capitalize(), tableName.split('_'))).strip())
613          entity['sort_id'] = entity['label'].upper()
614          entity['columns'] = self.sort_list(cols,'index')
615          # Add Table.
616          entity['not_found'] = 1
617          s.append((entity['label'],entity))
618     
619      #-- Sort entities
620      s.sort()
621      entities = map(lambda x: x[1], s)
622     
623      #-- [ReqBuff]: Returns value and stores it in buffer of Http-Request.
624      return self.storeReqBuff( reqBuffId, entities, REQUEST)
625
626
627    ############################################################################
628    ###
629    ###   RecordSet
630    ###
631    ############################################################################
632
633    """
634    @rtype: C{string}
635    """
636    def recordSet_Select(self, tablename, select=None, where=None):
637      da = self.getDA()
638      gadfly = da.meta_type == 'Z Gadfly Database Connection'
639      tabledef = self.getEntity(tablename)
640      tablecols = tabledef['columns']
641      selectClause = []
642      fromClause = [ tablename]
643      whereClause = []
644      if where:
645        whereClause.append( where)
646      if select:
647        selectClause.append( select)
648      else:
649        fk_tablename_counter = {}
650        table_AS = self.getConfProperty('ZMSSqlDb.table.AS','AS')
651        for tablecol in tablecols:
652          if tablecol.get('fk') and tablecol['fk'].get('tablename'):
653            fk_tablename = tablecol['fk']['tablename']
654            fk_tablename_counter[fk_tablename] = fk_tablename_counter.get(fk_tablename,0)+1
655            fk_tablename_alias = '%s%i'%(fk_tablename,fk_tablename_counter[fk_tablename])
656            fk_fieldname = tablecol['fk']['fieldname']
657            if fk_fieldname.upper().find(fk_tablename.upper()+'.') < 0:
658              fk_fieldname = fk_tablename+'.'+fk_fieldname
659            fk_fieldname = self.re_sub( fk_tablename+'\.', fk_tablename_alias+'.', fk_fieldname, ignorecase=True)
660            fk_displayfield = tablecol['fk']['displayfield']
661            if fk_displayfield.upper().find(fk_tablename.upper()+'.') < 0:
662              fk_displayfield = fk_tablename+'.'+fk_displayfield
663            fk_displayfield = self.re_sub( fk_tablename+'\.', fk_tablename_alias+'.', fk_displayfield, ignorecase=True)
664            selectClause.append( '%s AS %s'%(fk_displayfield,tablecol['id']))
665            if gadfly:
666              fromClause.append( ', %s %s %s'%(fk_tablename,table_AS,fk_tablename_alias))
667              whereClause.append( '%s.%s=%s'%(tablename,tablecol['id'],fk_fieldname))
668            else:
669              fromClause.append( 'LEFT JOIN %s %s %s ON %s.%s=%s'%(fk_tablename,table_AS,fk_tablename_alias,tablename,tablecol['id'],fk_fieldname))
670          elif tablecol.get('type','?') != '?':
671            selectClause.append( '%s.%s'%(tablename,tablecol['id']))
672      sqlStatement = []
673      sqlStatement.append( 'SELECT '+' , '.join(selectClause)+' ')
674      sqlStatement.append( 'FROM '+' '.join(fromClause)+' ')
675      if whereClause:
676        sqlStatement.append( 'WHERE '+' AND '.join(whereClause)+' ')
677      return ''.join(sqlStatement)
678
679
680    """
681    Initializes record-set.
682    @param REQUEST: the triggering request
683    @type REQUEST: ZPublisher.HTTPRequest
684    @rtype: C{None}
685    """
686    def recordSet_Init(self, REQUEST, all=True):
687      SESSION = REQUEST.SESSION
688      tabledefs = filter( lambda x: not x.get('not_found'), self.getEntities())
689      tablename = SESSION.get('qentity_%s'%self.id)
690      #-- Sanity check.
691      SESSION.set('qentity_%s'%self.id,'')
692      REQUEST.set('primary_key','')
693      REQUEST.set('grid_cols',[])
694      sqlStatement = REQUEST.get('sqlStatement',[])
695      if type(sqlStatement) is not list:
696        sqlStatement = []
697      if len(tabledefs) > 0:
698        if tablename not in map( lambda x: x['id'], tabledefs):
699          tablename = tabledefs[0]['id']
700        tablename = REQUEST.form.get('qentity',tablename)
701        tabledef = filter(lambda x: x['id'].upper() == tablename.upper(), tabledefs)[0]
702        select = None
703        if all:
704          select = '*'
705        sqlStatement.append( self.recordSet_Select( tablename, select))
706        tablecols = tabledef['columns']
707        # Primary Key.
708        primary_key = map(lambda x: x['id'], filter(lambda x: x.get('pk',0)==1, tablecols))
709        primary_key.append(None)
710        #-- Set environment.
711        SESSION.set('qentity_%s'%self.id,tablename)
712        REQUEST.set('tabledef',tabledef)
713        REQUEST.set('grid_cols',tablecols)
714        REQUEST.set('primary_key',primary_key[0])
715      REQUEST.set('sqlStatement',sqlStatement)
716
717
718    """
719    Filter record-set by appending where clause to sql-statement.
720    @param REQUEST: the triggering request
721    @type REQUEST: ZPublisher.HTTPRequest
722    @rtype: C{None}
723    """
724    def recordSet_Filter(self, REQUEST):
725      SESSION = REQUEST.SESSION
726      tablename = SESSION['qentity_%s'%self.id]
727      tabledefs = filter( lambda x: not x.get('not_found'), self.getEntities())
728      #-- Sanity check.
729      SESSION.set('qfilters_%s'%self.id,REQUEST.form.get('qfilters',SESSION.get('qfilters_%s'%self.id,1)))
730      if len(tabledefs) > 0:
731        tabledef = filter(lambda x: x['id'].upper() == tablename.upper(), tabledefs)[0]
732        tablecols = tabledef['columns']
733        tablefilter = self.dt_html(tabledef.get('filter',''),REQUEST)
734        #-- WHERE
735        q = 'WHERE '
736        if ''.join(REQUEST.get('sqlStatement',[])).upper().find(q) > 0:
737          q = 'AND '
738        whereClause = []
739        for i in range(SESSION['qfilters_%s'%self.id]):
740          filterattr='filterattr%i'%i
741          filterop='filterop%i'%i
742          filtervalue='filtervalue%i'%i
743          sessionattr='%s_%s'%(filterattr,self.id)
744          sessionop='%s_%s'%(filterop,self.id)
745          sessionvalue='%s_%s'%(filtervalue,self.id)
746          if REQUEST.get('action','')=='':
747            if REQUEST.get('btn','')==self.getZMILangStr('BTN_RESET'):
748              SESSION.set(sessionattr,'')
749              SESSION.set(sessionop,'')
750              SESSION.set(sessionvalue,'')
751            elif REQUEST.get('btn','')==self.getZMILangStr('BTN_REFRESH'):
752              SESSION.set(sessionattr,REQUEST.form.get(filterattr,''))
753              SESSION.set(sessionop,REQUEST.form.get(filterop,''))
754              SESSION.set(sessionvalue,REQUEST.form.get(filtervalue,''))
755          fk_tablename_counter = {}
756          for tablecol in tablecols:
757            if tablecol.get('fk') and tablecol['fk'].get('tablename'):
758              fk_tablename = tablecol['fk']['tablename']
759              fk_tablename_counter[fk_tablename] = fk_tablename_counter.get(fk_tablename,0)+1
760              fk_tablename_alias = '%s%i'%(fk_tablename,fk_tablename_counter[fk_tablename])
761              fk_displayfield = tablecol['fk']['displayfield']
762              if fk_displayfield.find(fk_tablename+'.') < 0:
763                fk_displayfield = fk_tablename+'.'+fk_displayfield
764              coltable = fk_tablename
765              colname = self.re_sub( fk_tablename+'\.', fk_tablename_alias+'.', fk_displayfield, ignorecase=True)
766              qualifiedname = colname
767            else:
768              coltable = tablename
769              colname = tablecol['id']
770              qualifiedname = '%s.%s'%(coltable,colname)
771            v = SESSION.get(sessionvalue,'')
772            op = SESSION.get(sessionop,'=')
773            if SESSION.get(sessionattr,'') == tablecol['id']:
774              sqlStatement = REQUEST.get('sqlStatement',[])
775              if op in [ 'NULL', 'NOT NULL']:
776                sqlStatement.append(q + qualifiedname + ' IS ' + op + ' ')
777              elif v != '':
778                sqlStatement.append(q + qualifiedname + ' ' + op + ' ' + self.sql_quote__(coltable, colname, v) + ' ')
779              REQUEST.set('sqlStatement',sqlStatement)
780              q = 'AND '
781        if len(tablefilter) > 0:
782          sqlStatement = REQUEST.get('sqlStatement',[])
783          sqlStatement.append(q + '(' + tablefilter + ') ')
784          REQUEST.set('sqlStatement',sqlStatement)
785
786
787    """
788    Sort record-set by appending order-by clause to sql-statement.
789    @param REQUEST: the triggering request
790    @type REQUEST: ZPublisher.HTTPRequest
791    @rtype: C{None}
792    """
793    def recordSet_Sort(self, REQUEST):
794      SESSION = REQUEST.SESSION
795      tablename = SESSION['qentity_%s'%self.id]
796      tabledefs = filter( lambda x: not x.get('not_found'), self.getEntities())
797      #-- Sanity check.
798      qorder = REQUEST.get('qorder','')
799      qorderdir = REQUEST.get('qorderdir','asc')
800      sqlStatement = REQUEST.get('sqlStatement',[])
801      if len(tabledefs) > 0:
802        tabledef = filter(lambda x: x['id'].upper() == tablename.upper(), tabledefs)[0]
803        tablecols = tabledef['columns']
804        #-- ORDER BY
805        if qorder == '' or not qorder in map(lambda x: x['id'], tablecols):
806          for col in tablecols:
807            if col.get('hide',0) != 1:
808              qorder = '%s.%s'%(tablename,col['id'])
809              if col.get('type','') in ['date','datetime','time']:
810                qorderdir = 'desc'
811              break
812        sqlStatement.append('ORDER BY ' + qorder + ' ' + qorderdir + ' ')
813      REQUEST.set('sqlStatement',sqlStatement)
814      REQUEST.set('qorder',qorder)
815      REQUEST.set('qorderdir',qorderdir)
816
817
818    ############################################################################
819    ###
820    ###   Actions
821    ###
822    ############################################################################
823
824
825    """
826    Get reference for foreign-key relation.
827    @param tablename: Name of the SQL-Table.
828    @type tablename: C{string}
829    @return: ID of the row that was inserted.
830    @rtype: int
831    """
832    def getFk(self, tablename, id, name, value, createIfNotExists=1):
833      tabledefs = self.getEntities()
834      tabledef = filter(lambda x: x['id'].upper() == tablename.upper(), tabledefs)[0]
835      tablecols = tabledef['columns']
836      primary_key = (map(lambda x: x['id'], filter(lambda x: x.get('pk',0)==1, tablecols))+[tablecols[0]['id']])[0]
837     
838      # Find existing row-id.
839      sqlStatement = []
840      sqlStatement.append( 'SELECT %s AS existing_id FROM %s'%(primary_key,tablename))
841      sqlStatement.append( 'WHERE %s=%s'%(primary_key,self.sql_quote__(tablename, primary_key, value)))
842      sqlStatement.append( 'OR %s=%s'%(name,self.sql_quote__(tablename, name, value)))
843      sqlStatement = ' '.join(sqlStatement)
844      try:
845        rs = self.query(sqlStatement)['records']
846        if len(rs) == 1:
847          rowid = rs[0]['existing_id']
848          return rowid
849      except:
850        raise zExceptions.InternalError(_globals.writeError( self, '[getFk]: can\'t find existing row - sqlStatement=' + sqlStatement))
851     
852      rowid = None
853      if createIfNotExists:
854        # Get columns to insert
855        c = []
856        tablecol = tablecols[0]
857        if tablecol.get('auto'):
858          new_id = 0
859          try:
860            rs = self.query('SELECT MAX(%s) AS max_id FROM %s'%(primary_key,tablename))['records']
861            if len(rs) == 1:
862              new_id = int(rs[0]['max_id'])+1
863          except:
864            _globals.writeError( self, '[getFk]: can\'t get max_id')
865          c.append({'id':id,'value':str(new_id)})
866        c.append({'id':name,'value':self.sql_quote__(tablename,name,value)})
867       
868        # Assemble sql-statement
869        sqlStatement = []
870        sqlStatement.append( 'INSERT INTO %s ('%tablename)
871        sqlStatement.append( ', '.join(map(lambda x: x['id'], c)))
872        sqlStatement.append( ') VALUES (')
873        sqlStatement.append( ', '.join(map(lambda x: x['value'], c)))
874        sqlStatement.append( ')')
875        sqlStatement = ' '.join(sqlStatement)
876        try:
877          self.executeQuery( sqlStatement)
878        except:
879          raise zExceptions.InternalError(_globals.writeError( self, '[createFk]: can\'t insert row - sqlStatement=' + sqlStatement))
880       
881        # Return with row-id.
882        rowid = (filter(lambda x: x['id']==primary_key, c)+[{'value':None}])[0]['value']
883        if rowid is None:
884          sqlStatement = []
885          sqlStatement.append( 'SELECT %s AS value FROM %s WHERE '%(primary_key,tablename))
886          sqlStatement.append( ' AND '.join(map( lambda x: x['id']+'='+x['value'], filter( lambda x: x['value'].upper()!='NULL', c))))
887          sqlStatement = ' '.join(sqlStatement)
888          try:
889            for r in self.query( sqlStatement)['records']:
890              rowid = r['value']
891          except:
892            raise zExceptions.InternalError(_globals.writeError( self, '[createFk]: can\'t get primary-key - sqlStatement=' + sqlStatement))
893     
894      return rowid
895
896
897    """
898    Insert row into record-set.
899    @param tablename: Name of the SQL-Table.
900    @type tablename: C{string}
901    @param values: Columns (id/value) to be inserted.
902    @type values: C{dict}
903    @return: ID of the row that was inserted.
904    @rtype: C{any}
905    """
906    def recordSet_Insert(self, tablename, values={}):
907      REQUEST = self.REQUEST
908      auth_user = REQUEST.get('AUTHENTICATED_USER')
909      lang = REQUEST['lang']
910      da = self.getDA()
911      if tablename is None:
912        raise zExceptions.InternalError("[recordSet_Insert]: tablename must not be None!")
913      tabledefs = self.getEntities()
914      tabledef = filter(lambda x: x['id'].upper() == tablename.upper(), tabledefs)[0]
915      tablecols = tabledef['columns']
916      primary_key = (map(lambda x: x['id'], filter(lambda x: x.get('pk',0)==1, tablecols))+[tablecols[0]['id']])[0]
917     
918      # Get columns to insert
919      blobs = {}
920      c = []
921      for tablecol in tablecols:
922        id = tablecol['id']
923        if tablecol.get('auto'):
924          if tablecol.get('auto') in ['insert','update']:
925            if tablecol.get('type') in ['date','datetime']:
926              c.append({'id':id,'value':self.getLangFmtDate(time.time(),lang,'%s_FMT'%tablecol['type'].upper())})
927            elif tablecol.get('type') in ['int']:
928              new_id = 0
929              try:
930                rs = self.query('SELECT MAX(%s) AS max_id FROM %s'%(id,tablename))['records']
931                if len(rs) == 1:
932                  new_id = int(rs[0]['max_id'])+1
933              except:
934                _globals.writeError( self, '[recordSet_Insert]: can\'t get max_id')
935              c.append({'id':id,'value':new_id})
936        elif tablecol.get('blob'):
937          value = None
938          blob = tablecol.get('blob')
939          if values.get('blob_%s'%id,None) is not None and values.get('blob_%s'%id).filename:
940            # Process blobs later...
941            blobs['blob_%s'%id] = values['blob_%s'%id]
942        elif (not tablecol.get('details')) and \
943             (not tablecol.get('multiselect') or tablecol.get('multiselect').get('custom') or tablecol.get('multiselect').get('mysqlset')) and \
944             (not tablecol.get('multimultiselect')):
945          value = values.get(id,values.get(id.lower(),values.get(id.upper(),'')))
946          if type(value) is list:
947            value = ','.join(value)
948          c.append({'id':id,'value':value})
949      # Assemble sql-statement
950      c = filter(lambda x: self.sql_quote__(tablename,x['id'],x['value'])!='NULL', c)
951      sqlStatement = []
952      sqlStatement.append( 'INSERT INTO %s ('%tablename)
953      sqlStatement.append( ', '.join(map(lambda x: x['id'], c)))
954      sqlStatement.append( ') VALUES (')
955      sqlStatement.append( ', '.join(map(lambda x: self.sql_quote__(tablename,x['id'],x['value']), c)))
956      sqlStatement.append( ')')
957      sqlStatement = ' '.join(sqlStatement)
958      try:
959        if da.meta_type == 'Z MySQL Database Connection':
960          self.executeQuery('SET @auth_user=\'%s\''%auth_user)
961      except:
962        raise zExceptions.InternalError(_globals.writeError( self, '[recordSet_Insert]: can\'t set auth_user variable'))
963      try:
964        self.executeQuery( sqlStatement)
965      except:
966        raise zExceptions.InternalError(_globals.writeError( self, '[recordSet_Insert]: can\'t insert row - sqlStatement=' + sqlStatement))
967      # Return with row-id.
968      rowid = (filter(lambda x: x['id']==primary_key, c)+[{'value':None}])[0]['value']
969      if rowid is None:
970        sqlStatement = []
971        sqlStatement.append( 'SELECT %s AS value FROM %s WHERE '%(primary_key,tablename))
972        sqlStatement.append( ' AND '.join(map( lambda x: x['id']+'='+self.sql_quote__(tablename,x['id'],x['value']), filter( lambda x: self.sql_quote__(tablename,x['id'],x['value']).upper()!='NULL', c))))
973        sqlStatement = ' '.join(sqlStatement)
974        try:
975          for r in self.query( sqlStatement)['records']:
976            rowid = r['value']
977        except:
978          raise zExceptions.InternalError(_globals.writeError( self, '[recordSet_Insert]: can\'t get primary-key - sqlStatement=' + sqlStatement))
979      # Process blobs now.
980      if blobs:
981        self.recordSet_Update(tablename, rowid, blobs, old_values={})
982      return rowid
983
984
985    """
986    Update row in table.
987    @param tablename: Name of the SQL-Table.
988    @type tablename: C{string}
989    @param rowid: ID of the row to be updated.
990    @type rowid: C{any}
991    @param values: Columns (id/value) to be updated.
992    @type values: C{dict}
993    @return: ID of the row that was updated.
994    @rtype: C{any}
995    """
996    def recordSet_Update(self, tablename, rowid, values={},old_values={}):
997      REQUEST = self.REQUEST
998      auth_user = REQUEST.get('AUTHENTICATED_USER')
999      lang = REQUEST['lang']
1000      da = self.getDA()
1001      if tablename is None:
1002        raise "[recordSet_Update]: tablename must not be None!"
1003      tabledefs = self.getEntities()
1004      tabledef = filter(lambda x: x['id'].upper() == tablename.upper(), tabledefs)[0]
1005      tablecols = tabledef['columns']
1006      primary_key = (map(lambda x: x['id'], filter(lambda x: x.get('pk',0)==1, tablecols))+[tablecols[0]['id']])[0]
1007      # Get old.
1008      sqlStatement = []
1009      sqlStatement.append( 'SELECT * FROM %s '%tablename)
1010      sqlStatement.append( 'WHERE %s=%s '%(primary_key,self.sql_quote__(tablename,primary_key,rowid)))
1011      sqlStatement = ' '.join(sqlStatement)
1012      try:
1013        old = self.query( sqlStatement)['records'][0]
1014      except:
1015        raise zExceptions.InternalError(_globals.writeError( self, '[recordSet_Update]: can\'t get old - sqlStatement=' + sqlStatement))
1016      # Get columns to update
1017      c = []
1018      for tablecol in tablecols:
1019        id = tablecol['id']
1020        consumed = id in REQUEST.get('qexcludeids',[])
1021        if not consumed and tablecol.get('auto'):
1022          if tablecol.get('auto') in ['update']:
1023            if tablecol.get('type') in ['date','datetime']:
1024              c.append({'id':id,'value':self.getLangFmtDate(time.time(),lang,'%s_FMT'%tablecol['type'].upper())})
1025          consumed = True
1026        if not consumed and tablecol.get('blob'):
1027          blob = tablecol.get('blob')
1028          remote = blob.get('remote',None)
1029          if values.get('delete_blob_%s'%id,None):
1030            if remote is None:
1031              value = self._delete_blob(tablename=tablename,id=id,rowid=rowid)
1032            else:
1033              value = self.http_import(self.url_append_params(remote+'/delete_blob',{'auth_user':blob.get('auth_user',auth_user.getId()),'tablename':tablename,'id':id,'rowid':rowid}),method='POST')
1034            c.append({'id':id,'value':value})
1035          elif values.get('blob_%s'%id,None) is not None and values.get('blob_%s'%id).filename:
1036            data = values.get('blob_%s'%id,None)
1037            file = self.FileFromData( data, data.filename)
1038            xml = file.toXml()
1039            if remote is None:
1040              value = self._set_blob(tablename=tablename,id=id,rowid=rowid,xml=xml)
1041            else:
1042              value = self.http_import(self.url_append_params(remote+'/set_blob',{'auth_user':blob.get('auth_user',auth_user.getId()),'tablename':tablename,'id':id,'rowid':rowid,'xml':xml}),method='POST')
1043            c.append({'id':id,'value':value})
1044          consumed = True
1045        if not consumed and tablecol.get('fk') and tablecol.get('fk').get('editable'):
1046          if values.has_key(id):
1047            fk_tablename = tablecol.get('fk').get('tablename')
1048            fk_fieldname = tablecol.get('fk').get('fieldname')
1049            fk_displayfield = tablecol.get('fk').get('displayfield')
1050            value = values.get(id)
1051            if value == '' and tablecol.get('nullable'):
1052              value = None
1053            else:
1054              value = self.getFk( fk_tablename, fk_fieldname, fk_displayfield, value)
1055            if value != old_values.get(id,old[id]):
1056              c.append({'id':id,'value':value})
1057          consumed = True
1058        if not consumed and \
1059           (not tablecol.get('details')) and \
1060           (not tablecol.get('multiselect') or tablecol.get('multiselect').get('custom') or tablecol.get('multiselect').get('mysqlset')) and \
1061           (not tablecol.get('multimultiselect')):
1062          if values.has_key(id) and values.get(id) != old_values.get(id,old[id]):
1063            value = values.get(id)
1064            if value == '' and tablecol.get('nullable'):
1065              value = None
1066            elif type(value) is list:
1067              value = ','.join(value)
1068            if value != old_values.get(id,old[id]):
1069              c.append({'id':id,'value':value})
1070      # Assemble sql-statement
1071      if len(c) > 0:
1072        sqlStatement = []
1073        sqlStatement.append( 'UPDATE %s SET '%tablename)
1074        sqlStatement.append( ', '.join(map(lambda x: x['id']+'='+self.sql_quote__(tablename,x['id'],x['value']), c)))
1075        sqlStatement.append( 'WHERE %s=%s '%(primary_key,self.sql_quote__(tablename,primary_key,rowid)))
1076        sqlStatement = ' '.join(sqlStatement)
1077        try:
1078          if da.meta_type == 'Z MySQL Database Connection':
1079            self.executeQuery('SET @auth_user=\'%s\''%auth_user)
1080        except:
1081          raise zExceptions.InternalError(_globals.writeError( self, '[recordSet_Update]: can\'t set auth_user variable'))
1082        try:
1083          self.executeQuery( sqlStatement)
1084        except:
1085          raise zExceptions.InternalError(_globals.writeError( self, '[recordSet_Update]: can\'t update row - sqlStatement=' + sqlStatement))
1086      # Return with row-id.
1087      return rowid
1088
1089
1090    """
1091    Delete row from table.
1092    @param tablename: Name of the SQL-Table.
1093    @type tablename: C{string}
1094    @param rowid: ID of the row to be deleted.
1095    @type rowid: C{any}
1096    @rtype: C{None}
1097    """
1098    def recordSet_Delete(self, tablename, rowid):
1099      REQUEST = self.REQUEST
1100      auth_user = REQUEST.get('AUTHENTICATED_USER')     
1101      lang = REQUEST['lang']
1102      da = self.getDA()
1103      if tablename is None:
1104        raise zExceptions.InternalError("[recordSet_Delete]: tablename must not be None!")
1105      tabledefs = self.getEntities()
1106      tabledef = filter(lambda x: x['id'].upper() == tablename.upper(), tabledefs)[0]
1107      tablecols = tabledef['columns']
1108      primary_key = (map(lambda x: x['id'], filter(lambda x: x.get('pk',0)==1, tablecols))+[tablecols[0]['id']])[0]
1109      # Assemble sql-statement
1110      sqlStatement = []
1111      sqlStatement.append( 'DELETE FROM %s '%tablename)
1112      sqlStatement.append( 'WHERE %s=%s '%(primary_key,self.sql_quote__(tablename,primary_key,rowid)))
1113      sqlStatement = ' '.join(sqlStatement)
1114      try:
1115        if da.meta_type == 'Z MySQL Database Connection':
1116          self.executeQuery('SET @auth_user=\'%s\''%auth_user)
1117      except:
1118        raise zExceptions.InternalError(_globals.writeError( self, '[recordSet_Update]: can\'t set auth_user variable'))     
1119      try:
1120        self.executeQuery( sqlStatement)
1121      except:
1122        raise zExceptions.InternalError(_globals.writeError( self, '[recordSet_Delete]: can\'t delete row - sqlStatement=' + sqlStatement))
1123
1124
1125    ############################################################################
1126    ###
1127    ###   Blob (remote)
1128    ###
1129    ############################################################################
1130
1131    # --------------------------------------------------------------------------
1132    #  ZMSSqlDb.delete_blob:
1133    # --------------------------------------------------------------------------
1134    def _delete_blob( self, tablename, id, rowid):
1135      tabledefs = self.getEntities()
1136      tabledef = filter(lambda x: x['id'].upper() == tablename.upper(), tabledefs)[0]
1137      tablecols = tabledef['columns']
1138      primary_key = (map(lambda x: x['id'], filter(lambda x: x.get('pk',0)==1, tablecols))+[tablecols[0]['id']])[0]
1139      column = self.getEntityColumn( tablename, id)
1140      blob = column['blob']
1141      path = blob['path']
1142      # Assemble sql-statement
1143      sqlStatement = []
1144      sqlStatement.append( 'SELECT '+id+' AS v FROM %s '%tablename)
1145      sqlStatement.append( 'WHERE %s=%s '%(primary_key,self.sql_quote__(tablename,primary_key,rowid)))
1146      sqlStatement = ' '.join(sqlStatement)
1147      try:
1148        for r in self.query( sqlStatement)['records']:
1149          filename = r['v']
1150          try:
1151            self.localfs_remove(path+filename)
1152          except: pass
1153          value = ''
1154          if column.get('nullable'):
1155            value = None
1156          else:
1157            value = self.sql_quote__(tablename,id,value)
1158          return value
1159      except:
1160        raise zExceptions.InternalError(_globals.writeError( self, '[get_blob]: can\'t delete blob - sqlStatement=' + sqlStatement))
1161
1162    def delete_blob( self, auth_user, tablename, id, rowid, REQUEST=None, RESPONSE=None):
1163      """ ZMSSqlDb.delete_blob """
1164      user = self.findUser( auth_user)
1165      if user is None:
1166        raise zExceptions.Unauthorized
1167      return self._delete_blob( tablename=tablename, id=id, rowid=rowid)
1168
1169
1170    # --------------------------------------------------------------------------
1171    #  ZMSSqlDb.set_blob:
1172    # --------------------------------------------------------------------------
1173    def _set_blob( self, tablename, id, rowid=None, xml=None):
1174      tabledefs = self.getEntities()
1175      tabledef = filter(lambda x: x['id'].upper() == tablename.upper(), tabledefs)[0]
1176      tablecols = tabledef['columns']
1177      primary_key = (map(lambda x: x['id'], filter(lambda x: x.get('pk',0)==1, tablecols))+[tablecols[0]['id']])[0]
1178      column = self.getEntityColumn( tablename, id)
1179      blob = column['blob']
1180      path = blob['path']
1181      file = self.parseXmlString( xml)
1182      # Normalize filename (crop path in local-fs)
1183      filename = file.filename
1184      i = max( filename.rfind('/'), filename.rfind('\\'))
1185      if i > 0:
1186        filename = filename[i+1:]
1187      fileext = ''
1188      i = filename.rfind( '.')
1189      if i > 0:
1190        fileext = filename[ i:]
1191        filename = filename[ :i]
1192      filename = filename + '_' + str( rowid) + fileext
1193      # Update
1194      oldfilename = 'None'
1195      if rowid is not None:
1196        # Assemble sql-statement
1197        sqlStatement = []
1198        sqlStatement.append( 'SELECT '+id+' AS v FROM %s '%tablename)
1199        sqlStatement.append( 'WHERE %s=%s '%(primary_key,self.sql_quote__(tablename,primary_key,rowid)))
1200        sqlStatement = ' '.join(sqlStatement)
1201        try:
1202          for r in self.query( sqlStatement)['records']:
1203            oldfilename = r['v']
1204        except:
1205          raise zExceptions.InternalError(_globals.writeError( self, '[set_blob]: can\'t set blob - sqlStatement=' + sqlStatement))
1206      # Remove old file from server-fs
1207      try:
1208        self.localfs_remove(path+oldfilename)
1209      except: pass
1210      # Write new file to server-fs
1211      self.localfs_write(path+filename,file.getData())
1212      return filename
1213
1214    security.declareProtected('View', 'set_blob')
1215    def set_blob( self, auth_user, tablename, id, rowid=None, xml=None, REQUEST=None, RESPONSE=None):
1216      """ ZMSSqlDb.set_blob """
1217      user = self.findUser( auth_user)
1218      if user is None:
1219        raise zExceptions.Unauthorized
1220      return self._set_blob( tablename=tablename, id=id, rowid=rowid, xml=xml)
1221
1222
1223    # --------------------------------------------------------------------------
1224    #  ZMSSqlDb.get_blob:
1225    # --------------------------------------------------------------------------
1226    security.declareProtected('View', 'get_blob')
1227    def get_blob( self, tablename, id, rowid, REQUEST, RESPONSE):
1228      """ ZMSSqlDb.get_blob """
1229      data = ''
1230      tabledefs = self.getEntities()
1231      tabledef = filter(lambda x: x['id'].upper() == tablename.upper(), tabledefs)[0]
1232      tablecols = tabledef['columns']
1233      primary_key = (map(lambda x: x['id'], filter(lambda x: x.get('pk',0)==1, tablecols))+[tablecols[0]['id']])[0]
1234      column = self.getEntityColumn( tablename, id)
1235      blob = column['blob']
1236      path = blob['path']
1237      # Assemble sql-statement
1238      sqlStatement = []
1239      sqlStatement.append( 'SELECT '+id+' AS v FROM %s '%tablename)
1240      sqlStatement.append( 'WHERE %s=%s '%(primary_key,self.sql_quote__(tablename,primary_key,rowid)))
1241      sqlStatement = ' '.join(sqlStatement)
1242      try:
1243        for r in self.query( sqlStatement)['records']:
1244          filename = r['v']
1245          data = self.localfs_read( path+filename, REQUEST=REQUEST)
1246      except:
1247        raise zExceptions.InternalError(_globals.writeError( self, '[get_blob]: can\'t get_blob - sqlStatement=' + sqlStatement))
1248      return data
1249
1250
1251    ############################################################################
1252    ###
1253    ###   Properties
1254    ###
1255    ############################################################################
1256
1257    ############################################################################
1258    #  ZMSSqlDb.manage_changeProperties:
1259    #
1260    #  Change Sql-Database properties.
1261    ############################################################################
1262    def manage_changeProperties(self, lang, REQUEST=None, RESPONSE=None):
1263      """ ZMSSqlDb.manage_changeProperties """
1264      message = ''
1265      el_data = REQUEST.get('el_data','')
1266      target = 'manage_properties'
1267     
1268      if REQUEST.get('btn','') in [ self.getZMILangStr('BTN_EXECUTE')]:
1269        c = 0
1270        for sql in el_data.split(';'):
1271          try:
1272            sql = sql.replace( '\n', '')
1273            sql = sql.replace( '\r', '')
1274            sql = sql.strip()
1275            if len(sql) > 0:
1276              c = c + 1
1277              self.executeQuery( sql)
1278          except:
1279            message += _globals.writeError( self, '')
1280            break
1281        message += '[%i]'%c
1282        target = 'manage_sql'
1283     
1284      elif REQUEST.get('btn','') not in [ self.getZMILangStr('BTN_CANCEL'), self.getZMILangStr('BTN_BACK')]:
1285        self.connection_id = REQUEST['connection_id']
1286        self.max_rows = REQUEST['max_rows']
1287        self.charset = REQUEST['charset']
1288        self.setModel(REQUEST['model'])
1289        message = self.getZMILangStr('MSG_CHANGED')
1290     
1291      # Return with message.
1292      message = urllib.quote(message)
1293      el_data = urllib.quote(el_data)
1294      return RESPONSE.redirect('%s?lang=%s&manage_tabs_message=%s&el_data=%s'%(target,lang,message,el_data))
1295
1296
1297    ############################################################################
1298    ###
1299    ###   Configuration
1300    ###
1301    ############################################################################
1302
1303    # --------------------------------------------------------------------------
1304    #  ZMSSqlDb.ajaxGetAutocompleteColumns:
1305    # --------------------------------------------------------------------------
1306    security.declareProtected('View', 'ajaxGetAutocompleteColumns')
1307    def ajaxGetAutocompleteColumns(self, tableName, fmt=None, REQUEST=None):
1308      """ ZMSSqlDb.ajaxGetAutocompleteColumns """
1309      RESPONSE = REQUEST.RESPONSE
1310      content_type = 'text/plain; charset=utf-8'
1311      filename = 'ajaxGetAutocompleteColumns.txt'
1312      RESPONSE.setHeader('Content-Type',content_type)
1313      RESPONSE.setHeader('Content-Disposition','inline;filename="%s"'%filename)
1314      RESPONSE.setHeader('Cache-Control', 'no-cache')
1315      RESPONSE.setHeader('Pragma', 'no-cache')
1316      l = map( lambda x: x['id'], filter( lambda x: x['type'] != '?', self.getEntity( tableName)['columns']))
1317      q = REQUEST.get( 'q', '').upper()
1318      if q:
1319        l = filter( lambda x: x.upper().find( q) >= 0, l)
1320      limit = int(REQUEST.get('limit',self.getConfProperty('ZMS.input.autocomplete.limit',15)))
1321      if len(l) > limit:
1322        l = l[:limit]
1323      if fmt == 'json':
1324        return self.str_json(l)
1325      return '\n'.join(l)
1326
1327    ############################################################################
1328    #  ZMSSqlDb.manage_changeConfiguration:
1329    #
1330    #  Change Sql-Database configuration.
1331    ############################################################################
1332    def manage_changeConfiguration(self, lang, btn='', key='all', REQUEST=None, RESPONSE=None):
1333      """ ZMSSqlDb.manage_changeConfiguration """
1334      message = ''
1335      t0 = time.time()
1336      id = REQUEST.get('id','')
1337      target = 'manage_configuration'
1338     
1339      # Change.
1340      # -------
1341      if btn == self.getZMILangStr('BTN_SAVE'):
1342        model = self.getModel()
1343        entities = filter( lambda x: x['id'].upper() == id.upper(), model)
1344        if entities:
1345          entity = entities[0]
1346        else:
1347          entity = {}
1348          entity['id'] = id
1349          entity['type'] = 'table'
1350          entity['columns'] = []
1351          model.append( entity)
1352        entity['label'] = REQUEST.get('label').strip()
1353        entity['type'] = REQUEST.get('type').strip()
1354        entity['interface'] = REQUEST.get('interface').strip()
1355        entity['filter'] = REQUEST.get('filter').strip()
1356        entity['access'] = {
1357         'insert': REQUEST.get( 'access_insert', []),
1358         'update': REQUEST.get( 'access_update', []),
1359         'delete': REQUEST.get( 'access_delete', []),
1360        }
1361        cols = []
1362        for attr_id in REQUEST.get('attr_ids',[]):
1363          col = {}
1364          col['id'] = REQUEST.get( 'attr_id_%s'%attr_id, attr_id).strip()
1365          col['label'] = REQUEST.get( 'attr_label_%s'%attr_id, '').strip()
1366          col['index'] = int(REQUEST.get( 'attr_index_%s'%attr_id))
1367          col['hide'] = int(not REQUEST.get('attr_display_%s'%attr_id,0)==1)
1368          if REQUEST.has_key( 'attr_auto_%s'%attr_id):
1369            col['auto'] = REQUEST.get( 'attr_auto_%s'%attr_id)
1370          if REQUEST.has_key( 'attr_type_%s'%attr_id):
1371            t = REQUEST.get( 'attr_type_%s'%attr_id)
1372            if t in self.valid_types.keys():
1373              d = copy.deepcopy( self.valid_types[ t])
1374              c = []
1375              if type( d) is dict:
1376                xs = 'attr_%s_'%t
1377                xe = '_%s'%attr_id
1378                for k in filter( lambda x: x.startswith(xs) and x.endswith(xe), REQUEST.form.keys()):
1379                  xk = k[len(xs):-len(xe)].split('_')
1380                  xv = REQUEST[k]
1381                  if len( xk) == 1:
1382                    xk = xk[ 0]
1383                    if xk == 'options':
1384                      xv2 = []
1385                      for xi in xv.split('\n'):
1386                        xi = xi.replace('\r','')
1387                        if xi.find('->') > 0:
1388                          xi0 = xi[:xi.find('->')]
1389                          xi1 = xi[xi.find('->')+len('->'):]
1390                          if len(xi0) > 0 and len( xi1) > 0:
1391                            xv2.append( [xi0, xi1])
1392                        else:
1393                          if len(xi) > 0:
1394                            xv2.append( [xi, xi])
1395                      if len( xv2) > 0:
1396                        d[ xk] = xv2
1397                    else:
1398                      if type( xv) is str:
1399                        xv = xv.strip()
1400                        if len( xv) > 0:
1401                          d[ xk] = xv
1402                      elif type( xv) is int:
1403                        if xv != 0:
1404                          d[ xk] = xv
1405                  else:
1406                    if not d.has_key( xk[0]):
1407                      d[ xk[0]] = {}
1408                      c.append( xk[0])
1409                    if not d[ xk[0]].has_key( xk[-1]):
1410                      d[ xk[0]][ xk[-1]] = {}
1411                    if type( xv) is str:
1412                      xv = xv.strip()
1413                      if len( xv) > 0:
1414                        d[ xk[0]][ xk[-1]][ xk[1]] = xv
1415                    elif type( xv) is int:
1416                      if xv != 0:
1417                        d[ xk[0]][ xk[-1]][ xk[1]] = xv
1418              for i in c:
1419                l = d[i].values()
1420                l = map( lambda x: (x['index'],x), l)
1421                l.sort()
1422                l = map( lambda x: x[1], l)
1423                for x in l:
1424                  if not x.get('display'):
1425                    x['hide'] = 1
1426                  try: del x['display']
1427                  except: pass
1428                  try: del x['index']
1429                  except: pass
1430                l = filter( lambda x: len(x.keys()) > 0, l)
1431                d[i] = l
1432              col[ t] = d
1433          cols.append( ( col['index'], col))
1434        cols.sort()
1435        cols = map( lambda x: x[1], cols)
1436        entity['columns'] = cols
1437        f = self.toXmlString( model)
1438        self.setModel(f)
1439        message = self.getZMILangStr('MSG_CHANGED')
1440     
1441      # Delete.
1442      # -------
1443      elif btn == 'delete':
1444        attr_id = REQUEST['attr_id'].strip()
1445        model = self.getModel()
1446        entities = filter( lambda x: x['id'].upper() == id.upper(), model)
1447        if entities:
1448          entity = entities[0]
1449          entity['columns'] = filter( lambda x: x['id'].upper() != attr_id.upper(), entity['columns'])
1450        f = self.toXmlString( model)
1451        self.setModel(f)
1452        message = self.getZMILangStr('MSG_CHANGED')
1453     
1454      # Import.
1455      # -------
1456      elif btn == self.getZMILangStr('BTN_IMPORT'):
1457        f = REQUEST['file']
1458        filename = f.filename
1459        self.setModel(f)
1460        message = self.getZMILangStr('MSG_IMPORTED')%('<i>%s</i>'%filename)
1461     
1462      # Insert.
1463      # -------
1464      elif btn == self.getZMILangStr('BTN_INSERT'):
1465        attr_id = REQUEST['attr_id'].strip()
1466        model = self.getModel()
1467        newValue = {}
1468        newValue['id'] = attr_id
1469        newValue['label'] = REQUEST.get('attr_label').strip()
1470        newValue['hide'] = int(not REQUEST.get('attr_display',0)==1)
1471        if REQUEST.get('attr_type'):
1472          newValue[REQUEST.get('attr_type')] = {}
1473        entities = filter( lambda x: x['id'].upper() == id.upper(), model)
1474        if entities:
1475          entity = entities[0]
1476        else:
1477          entity = {}
1478          entity['id'] = id
1479          entity['type'] = 'table'
1480          entity['columns'] = []
1481          model.append( entity)
1482        entity['columns'].append(newValue)
1483        f = self.toXmlString( model)
1484        self.setModel(f)
1485        message += self.getZMILangStr('MSG_INSERTED')%attr_id
1486     
1487      # Move to.
1488      # --------
1489      elif key == 'attr' and btn == 'move_to':
1490        pos = REQUEST['pos']
1491        attr_id = REQUEST['attr_id']
1492        model = self.getModel()
1493        entities = filter( lambda x: x['id'].upper() == id.upper(), model)
1494        if entities:
1495          entity = entities[0]
1496        else:
1497          entity = {}
1498          entity['id'] = id
1499          entity['type'] = 'table'
1500          entity['columns'] = map( lambda x: {'id':x['id']}, self.getEntity( id)['columns'])
1501          model.append( entity)
1502        cols = entity['columns']
1503        col = filter( lambda x: x['id'].upper() == attr_id.upper(), cols)[0]
1504        i = cols.index( col)
1505        cols.remove( col)
1506        cols.insert( pos, col)
1507        idx = 0
1508        for col in cols:
1509          col['index'] = idx
1510          idx = idx + 1
1511        f = self.toXmlString( model)
1512        self.setModel(f)
1513        message = self.getZMILangStr('MSG_MOVEDOBJTOPOS')%(("<i>%s</i>"%attr_id),(pos+1))
1514     
1515      # Return with message.
1516      target = self.url_append_params( target, { 'lang':lang, 'id':id, 'attr_id':REQUEST.get('attr_id','')})
1517      if len( message) > 0:
1518        message += ' (in '+str(int((time.time()-t0)*100.0)/100.0)+' secs.)'
1519        target = self.url_append_params( target, { 'manage_tabs_message':message})
1520      return RESPONSE.redirect( target)
1521
1522
1523    ############################################################################
1524    ###
1525    ###   Im/Export
1526    ###
1527    ############################################################################
1528
1529    # --------------------------------------------------------------------------
1530    #  ZMSSqlDb.importFile
1531    # --------------------------------------------------------------------------
1532    def importFile(self, file, REQUEST):
1533      message = ''
1534     
1535      # Get filename.
1536      try:
1537        filename = file.name
1538      except:
1539        filename = file.filename
1540     
1541      # Create temporary folder.
1542      folder = tempfile.mktemp()
1543      os.mkdir(folder)
1544     
1545      # Save to temporary file.
1546      filename = _fileutil.getOSPath('%s/%s'%(folder,_fileutil.extractFilename(filename)))
1547      _fileutil.exportObj(file,filename)
1548     
1549      # Find XML-file.
1550      if _fileutil.extractFileExt(filename) == 'zip':
1551        _fileutil.extractZipArchive(filename)
1552        filename = None
1553        for deep in [0,1]:
1554          for ext in ['xml', 'htm', 'html' ]:
1555            if filename is None:
1556              filename = _fileutil.findExtension(ext, folder, deep)
1557              break
1558        if filename is None:
1559          raise zExceptions.InternalError("XML-File not found!")
1560     
1561      # Import Filter.
1562      if REQUEST.get('filter','') in self.getFilterIds():
1563        filename = _filtermanager.importFilter(self, filename, REQUEST.get('filter',''), REQUEST)
1564     
1565      # Import XML-file.
1566      f = open(filename, 'r')
1567      xml = f.read()
1568      f.close()
1569     
1570      # Parse XML-file.
1571      v = self.parseXmlString(xml)
1572      for tablename in v.keys():
1573        for row in v.get(tablename):
1574          qs = 'INSERT INTO %s '%tablename
1575          qa = '( '
1576          qv = '( '
1577          c = 0
1578          for col in row.keys():
1579            val = row.get(col)
1580            if type(val) is str:
1581              val = "'" + val + "'"
1582            if c > 0:
1583              qa += ', '
1584              qv += ', '
1585            qa += str(col) + ' '
1586            qv += str(val) + ' '
1587            c += 1
1588          qs = qs + qa + ') VALUES ' + qv + ')'
1589          self.executeQuery(qs)
1590     
1591      # Remove temporary files.
1592      _fileutil.remove(folder, deep=1)
1593     
1594      # Return with message.
1595      message += self.getZMILangStr('MSG_IMPORTED')%('<i>%s</i>'%_fileutil.extractFilename(filename))
1596      return message
1597
1598
1599    ############################################################################
1600    #  ZMSSqlDb.manage_import:
1601    #
1602    #  Import data to Sql-Database.
1603    ############################################################################
1604    def manage_import(self, lang, REQUEST=None, RESPONSE=None):
1605      """ ZMSSqlDb.manage_import """
1606      message = ''
1607     
1608      if REQUEST.get('btn','') not in  [ self.getZMILangStr('BTN_CANCEL'), self.getZMILangStr('BTN_BACK')]:
1609        message = self.importFile(REQUEST.get('file'),REQUEST)
1610     
1611      # Return with message.
1612      message = urllib.quote(message)
1613      return RESPONSE.redirect('manage_importexport?lang=%s&manage_tabs_message=%s'%(lang,message))
1614
1615
1616    ############################################################################
1617    #  ZMSSqlDb.manage_export:
1618    #
1619    #  Export data from Sql-Database.
1620    ############################################################################
1621    def manage_export(self, lang, REQUEST=None, RESPONSE=None):
1622      """ ZMSSqlDb.manage_export """
1623      export = []
1624      export.append(self.getXmlHeader())
1625      export.append('<dictionary>\n')
1626      for id in REQUEST.get('ids',[]):
1627        export.append('<item key="%s">\n'%id)
1628        export.append('<list>\n')
1629        qs = 'SELECT * FROM %s'%id
1630        rs = self.query(qs)
1631        for i in rs['records']:
1632          export.append('<item>\n')
1633          export.append('<dictionary>\n')
1634          for c in rs['columns']:
1635            col = c['id']
1636            val = i[col]
1637            t = 'string'
1638            if type(val) is int:
1639              t = 'int'
1640            elif type(val) is float:
1641              t = 'float'
1642            export.append('<item key="%s" type="%s">\n'%(col,t))
1643            export.append(str(_xmllib.toCdata(self,val)))
1644            export.append('</item>\n')
1645          export.append('</dictionary>\n')
1646          export.append('</item>\n')
1647        export.append('</list>\n')
1648        export.append('</item>\n')
1649      export.append('</dictionary>\n')
1650      # Zip Xml-Export.
1651      filepath = tempfile.mktemp()
1652      _fileutil.mkDir(filepath)
1653      filename = filepath + os.sep + self.connection_id + '.xml'
1654      f = open(filename,'w')
1655      f.write(''.join(export))
1656      f.close()
1657      export = _fileutil.buildZipArchive(filename)
1658      _fileutil.remove(filepath,deep=1)
1659      # Return Zipped Xml-Export.
1660      content_type = 'application/zip'
1661      filename = self.connection_id + '.zip'
1662      RESPONSE.setHeader('Content-Type',content_type)
1663      RESPONSE.setHeader('Content-Disposition','inline;filename="%s"'%filename)
1664      return ''.join(export)
1665
1666
1667# call this to initialize framework classes, which
1668# does the right thing with the security assertions.
1669Globals.InitializeClass(ZMSSqlDb)
1670
1671################################################################################
Note: See TracBrowser for help on using the repository browser.