Repository navigation
variable type discrepancy #33
Description
table is create with episodenb ::
integer not null
but show as
TEXT
in
sqlitebrowser (version 3.1.0, Qt 4.8.5, QSLite 3.7.17).
This is what I use :
create table if not exists episode (
episodeid integer primary key references filmitem (filmid),
serieid integer not null references filmitem (filmid),
seasonnb integer not null,
episodenb integer no null,
title varchar[max],
foreign key (serieid) references serie (serieid)
);"
This is what is dumped by sqlite3 command line:
sqlite> .dump episode
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE episode (episodeid integer primary key references filmitem (filmid),serieid integer not null references filmitem (filmid),seasonnb integer not null,episodenb integer no null,title varchar[max],foreign key (serieid) references serie (serieid));
COMMIT;
But sqlitebrowser tells me that:
CREATE TABLE "episode" (
episodeid integer,
serieid integer NOT NULL,
seasonnb integer NOT NULL,
episodenb TEXT,
title varchar[max],
PRIMARY KEY(episodeid)
)
Activity
MKleusberg commentedon Jun 4, 2014
Well, there are two sides to look at this issue:
On the one hand your SQL probably doesn't do what you want it to do and would quite likely raise an error when given to other database engines. The problem here is the 'no null' (note the missing 't' to make it 'not null') constraint. Apparently SQLite parses this column definition in a way that 'episodenb' is the column name, 'integer no' the data type and 'null' the constraint. Now, as SQLite doesn't care about data types anyway the awkward data type is accepted just fine and the null constraint might be redundant (null values are allowed by default) but valid. By the way, you can check how SQLite actually interprets your table definition by executing
PRAGMA table_info(`episode`);That said, your statement clearly exposes a bug in our SQL parser: We didn't allow for a NULL constraint; we just checked for NOT NULL instead. Thus a table definition like
would result in TEXT columns as well. This is fixed by commit 6e5c98b which also fixes your table definition. Thank you for pointing this out!! That said, you still might want to fix your table definition 😉 Maybe you can also double check that the fix really does solve the problem as you expected it.
rafirafi commentedon Jun 4, 2014
Indeed this was my mistake (no vs not), I'm glad that you were actually able to turn this in something helpful for the project.
Thanks for the detailed answer 😉
Another little detail, when I click "modify table" on a table in the "Database structure" tab, and resize the "Edit Table definition" window, after the resize it shrinks back but the bottom QTextEdit (I think) is reduced a little too much and shows a scroll area which should not be necessary.
justinclift commentedon Jun 5, 2014
@rafirafi Any chance you can screenshot the error bit? It's kind of hard to visualise just from a text description. 😉
rafirafi commentedon Jun 5, 2014
This is the scroll bar in the "Edit table definition" windows.
But it seems it's not always happening, it depends on how you resize, so it's maybe more a Qt problem.
justinclift commentedon Jun 5, 2014
Ahhh, like the divider between the field definitions and the SQL is too far down, and should be higher up to display the whole SQL statement?
@MKleusberg @rp- Is that workable to do automatically? With GTK (heh) it used to be possible to query the inside dimensions of a widget, then ensure the container for it is large enough. Not being Qt guy though I don't know the details of how Qt does this stuff (yet).
rp- commentedon Jun 13, 2014
I added a splitter between the table and create table statement and set better expanding rules.
651e3bf
justinclift commentedon Jun 13, 2014
@rafirafi Would you have time to try this new code out? 😄