Database Migration

[Sambuddha Basu]

Hello Rick,

I have faced some problems while working on the database migration. The following are listed out:

On adding a new column with type Boolean, I get some errors.

So Craig suggested that I should create a new table which has a is_private field, copy all the bmarks data to that table. Then, I should delete the original bmarks table and rename the newly created table to bmarks.

What do you think about this? Should I go ahead with this?

Thanks,

Sambuddha

[Sambuddha Basu]

Also, the link to the code which threw the error is http://pastebin.ubuntu.com/7495140/

[Richard Harding]

On Tue, 20 May 2014, Sambuddha Basu wrote:

> Hello Rick,
> I have faced some problems while working on the database migration. The
> following are listed out:
> On adding a new column with type Boolean, I get some errors.

Please paste the errors. You should be able to add a boolean column using
sqlalchemy ok.

Here's a link to the test in the alembic code for this
https://github.com/zzzeek/alembic/blob/master/tests/test_sqlite.py#L13

> So Craig suggested that I should create a new table which has a is_private
> field, copy all the bmarks data to that table. Then, I should delete the
> original bmarks table and rename the newly created table to bmarks.
> What do you think about this? Should I go ahead with this?

No, we should not need to do this. I do think that the column will need a
default value. I'd propose that the default be made true at first. In this
way, all bookmarks added will be private, and we'll have to locate all
bookmark input sources in order to make sure that they public vs defaulting
everything to public.

If you continue to hit issues please paste the full debug information. The
commands you're running, the traceback of those errors, and the code that's
being run. It's difficult to help debug without all the information.

--

Rick Harding
@mitechie
http://blog.mitechie.com
https://bmark.us

[Sambuddha Basu]

The errors can be seen here at http://pastebin.ubuntu.com/7497367/

On Wednesday, 21 May 2014 03:03:08 UTC+4, Sambuddha Basu wrote:

[Richard Harding]

On Wed, 21 May 2014, Sambuddha Basu wrote:

> The errors can be seen here at http://pastebin.ubuntu.com/7497367/

Ok cool, that helps. So it appears the error isn't in adding the column,
but in adding the constraint sqlalchmy uses to make sure you only store a 0
or 1 in that new column. That's the line in the error message:

ALTER TABLE bmarks ADD CHECK (is_private IN (0, 1))

You can see that CHECK is a constraint in the docs
http://www.sqlite.org/lang_createtable.html#ckconst

So we need to figure out why the constraint isn't valid. I'd be curious
what version of sqlite you're using and if that's a more recent addition to
the sqlite api.

The other thing to test is what version of sqlalchemy we're on and see if
that might need to be updated to support things properly.

[Craig Maloney]

On 05/21/2014 09:50 AM, Richard Harding wrote:
> On Wed, 21 May 2014, Sambuddha Basu wrote:
>
>> The errors can be seen here at http://pastebin.ubuntu.com/7497367/
>
> Ok cool, that helps. So it appears the error isn't in adding the column,
> but in adding the constraint sqlalchmy uses to make sure you only store a 0
> or 1 in that new column. That's the line in the error message:
>
> ALTER TABLE bmarks ADD CHECK (is_private IN (0, 1))
>
> You can see that CHECK is a constraint in the docs
> http://www.sqlite.org/lang_createtable.html#ckconst
>
> So we need to figure out why the constraint isn't valid. I'd be curious
> what version of sqlite you're using and if that's a more recent addition to
> the sqlite api.
>

This is why I was recommending the table rename approach. I wasn't able
to get SQLite 3.7.9 to accept that ALTER statement.

--
--
Craig Maloney (cr...@decafbad.net) http://decafbad.net
"Work hard, rock hard, eat hard, sleep hard, grow big,
wear glasses if you need 'em." -- The Webb Wilder Credo

[Richard Harding]

On Wed, 21 May 2014, Craig Maloney_2 wrote:

> On 05/21/2014 09:50 AM, Richard Harding wrote:
> >On Wed, 21 May 2014, Sambuddha Basu wrote:
> >
> >>The errors can be seen here at http://pastebin.ubuntu.com/7497367/
> >
> >Ok cool, that helps. So it appears the error isn't in adding the column,
> >but in adding the constraint sqlalchmy uses to make sure you only store a 0
> >or 1 in that new column. That's the line in the error message:
> >
> >ALTER TABLE bmarks ADD CHECK (is_private IN (0, 1))
> >
> >You can see that CHECK is a constraint in the docs
> >http://www.sqlite.org/lang_createtable.html#ckconst
> >
> >So we need to figure out why the constraint isn't valid. I'd be curious
> >what version of sqlite you're using and if that's a more recent addition to
> >the sqlite api.
> >
>
> This is why I was recommending the table rename approach. I wasn't
> able to get SQLite 3.7.9 to accept that ALTER statement.

Ok, I understand. This feels like something out of sync with the tools
we're using. I'd like to check with the latest alembic/sqlalchemy versions
as well as verify in sqlite documentation when the CHECK constraint was
supported so that we can verify where the issue lies.