> so, for SQL databases, if a column is removed or dropped, it's not > migrated for sqlite. Is there a reason for this?
sqlite does not support drop columns. On Aug 27, 2:03 pm, Álvaro J. Iradier <[email protected]> wrote: > 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]
