[Zope] Ape & MySQL: Error adding ZODB MountPoint

[Elena Schulz]

Hi Bobb, hi Shane,

>db0=MySQLdb.connect(host="servername",user="user",passwd="password",db="dat
a
> base")
This works well, but Shane there is the below bug in apelib.sql.dbapi.py.
With the given patch it works for me :-).

Now the one 'set of problems' remain for me. That is customizing the
serializers/deserializers to transfer the data columns in the data and
property-tables from blob's to readable text so it can be used by external
applications.

-- Anyway thanks a lot for your help so far, Elena.

Adding this to zope.conf and changing dbapi.py works with mysql (postgresql
not tested)

<ape-db sql>
  <ape-storage>
    mapper-variation sql
    <ape-dbapi-connection db>
      connection-class apelib.sql.dbapi.MySQLConnection
      module-name MySQLdb
      connect-expression connect(db='ape', user='myUserName',
passwd='myPassWord', host="localhost")
    </ape-dbapi-connection>
  </ape-storage>
  mount-point /sql
</ape-db>

Patch for dbapi.py:

    def create_table(self, table, column_defs):
        """Creates a table.
        """
        table_name = self.prefix + table
        cols = []
        pkeys = []
        for name, typ, unique in column_defs:
            col = self.translate_name(name)
            db_type = self.translate_type(typ)
            ################Elena: changed, cause mysql needs NOT NULL on
primary keys
            null_constr = ''
            if unique:
                null_constr = ' NOT NULL'
                pkeys.append(col)
            cols.append("%s %s%s" % (col, db_type, null_constr))
            ########################################
        if pkeys:
            cols.append('PRIMARY KEY (%s)' % ', '.join(pkeys))
        sql = "CREATE TABLE %s (%s)" % (table_name, ', '.join(cols))
        self.execute(sql)


Error without the patch while creating a table by ape:

Traceback (innermost last):
  File L:\Zope\Zope-2.7.0\lib\python\ZPublisher\Publish.py, line 163, in
publish_module_standard
  File L:\Zope\ZopeData\Products\Localizer\__init__.py, line 58, in
new_publish
  File L:\Zope\Zope-2.7.0\lib\python\ZPublisher\Publish.py, line 127, in
publish
  File L:\Zope\Zope-2.7.0\lib\python\Zope\App\startup.py, line 203, in
zpublisher_exception_hook
    (Object: Database)
  File L:\Zope\Zope-2.7.0\lib\python\ZPublisher\Publish.py, line 91, in
publish
  File L:\Zope\Zope-2.7.0\lib\python\ZPublisher\BaseRequest.py, line 302, in
traverse
  File L:\Zope\Zope-2.7.0\lib\python\App\ApplicationManager.py, line 122, in
__bobo_traverse__
    (Object: Database)
  File L:\Zope\Zope-2.7.0\lib\python\App\ApplicationManager.py, line 113, in
__getitem__
    (Object: Database)
  File L:\Zope\Zope-2.7.0\lib\python\DBTab\DBTab.py, line 96, in getDatabase
  File L:\Zope\Zope-2.7.0\lib\python\DBTab\DBTab.py, line 113, in
_createDatabase
  File L:\Zope\Zope-2.7.0\lib\python\Zope\Startup\datatypes.py, line 172, in
open
  File L:\Zope\Zope-2.7.0\lib\python\Products\Ape\datatypes.py, line 48, in
createDB
  File L:\Zope\Zope-2.7.0\lib\python\Products\Ape\datatypes.py, line 36, in
open
  File
L:\Zope\Zope-2.7.0\lib\python\Products\Ape\lib\apelib\zodb3\storage.py, line
45, in __init__
  File L:\Zope\Zope-2.7.0\lib\python\Products\Ape\lib\apelib\core\io.py,
line 86, in init_databases
  File L:\Zope\Zope-2.7.0\lib\python\Products\Ape\lib\apelib\sql\sqlbase.py,
line 54, in init
  File L:\Zope\Zope-2.7.0\lib\python\Products\Ape\lib\apelib\sql\sqlbase.py,
line 45, in create
  File L:\Zope\Zope-2.7.0\lib\python\Products\Ape\lib\apelib\sql\dbapi.py,
line 235, in create_table
  File L:\Zope\Zope-2.7.0\lib\python\Products\Ape\lib\apelib\sql\dbapi.py,
line 276, in execute
  File I:\Python\ActivePython232\Lib\site-packages\MySQLdb\cursors.py, line
95, in execute
  File I:\Python\ActivePython232\Lib\site-packages\MySQLdb\cursors.py, line
114, in _execute
  File I:\Python\ActivePython232\Lib\site-packages\MySQLdb\connections.py,
line 33, in defaulterrorhandler
IntegrityError: (1171, 'All parts of a PRIMARY KEY must be NOT NULL;  If you
need NULL in a key, use UNIQUE instead')