Hi everyone, this is a long one
today I noticed one of my xxxx_settings.table file was written
everytime. The table definition was changed a long ago, from the old
chema:
CREATE TABLE settings(
id INTEGER PRIMARY KEY AUTOINCREMENT,
key CHAR(512),
value CHAR(512)
);
to the new:
CREATE TABLE settings(
id INTEGER PRIMARY KEY AUTOINCREMENT,
key CHAR(512) NOT NULL UNIQUE,
value CHAR(512)
);
the change is, key column was made NOT NULL UNIQUE.
So first question I noticed. In the migrate procedure, I noticed the
following code:
for key in keys:
if not key in sql_fields_old:
query = ['ALTER TABLE %s ADD %s %s;' % \
(self._tablename, key,
sql_fields_aux[key].replace(', ', new_add))]
elif self._db._dbname == 'sqlite':
query = None
...
so, for SQL databases, if a column is removed or dropped, it's not
migrated for sqlite. Is there a reason for this?
Then I tried commenting the "if self._db._dbname == 'sqlite':" line,
and I got the following SQL error:
OperationalError: Cannot add a UNIQUE column
which makes sense, it's not trivial to add a UNIQUE column to an
existing database...
But what makes me worry is, at the end of the _migrate method, I find this:
if query:
...
if key in sql_fields:
sql_fields_old[key] = sql_fields[key]
else:
del sql_fields_old[key]
tfile = open(self._dbt, 'w')
portalocker.lock(tfile, portalocker.LOCK_EX)
print "Here2:", self._dbt, sql_fields, sql_fields_old
cPickle.dump(sql_fields_old, tfile)
portalocker.unlock(tfile)
tfile.close()
First, sql_fields_old is updated with the migrated value ONLY if query
is not None. For sqlite it's None for changed columns. So, later,
cPickle dumps the value of sql_fields_old, so the file
xxxx_settings.table is written with the same old values, as this field
was not migrated.
So, for every request, web2py detects a migration is needed, but it
does nothing but writing the .table file with the old values.
I think the correct behaviour should be throwing an error if migration
can't be done.
Can you suggest a fix for this?
Thanks very much.
--
Álvaro J. Iradier Muro
Departamento de Desarrollo
[email protected]
