Editing a table shows as if table is empty where it's actually not

[sezeryalcin]

Details for the issue

I have an existing DB. I tested both newly created DB by sqlitebrowser and one created by another app like PHP.

When I click on modify table, table columns are not populated. It's showing it as if it's empty
For example:
CREATE TABLE updates (
);

But when I use "Copy Create Command" or open with another app, I see this:

CREATE TABLE "updates" ( "time" INTEGER, "id" INTEGER, "t" REAL, "h" REAL, "st" REAL, "enabled" INTEGER, "relay_on" TEXT, "cod" TEXT, "ip" TEXT)

Useful extra information

I'm opening this issue because:

• DB4S is crashing
• [ X] DB4S has a bug
• DB4S needs a feature
• DB4S has another problem

I'm using DB4S on:

• Windows: ( version: ___ )
• [ X] Linux: (Debian 9 64 bit )
• Mac OS: ( version: ___ )
• Other: ___

I'm using DB4S version:

• 3.10.1
• 3.10.0
• [ x] 3.9.1
• Other: ___

I have also:

• Tried out the latest nightly version: https://github.com/sqlitebrowser/sqlitebrowser#nightly-builds
• Searched for an existing similar issue: https://github.com/sqlitebrowser/sqlitebrowser/issues?utf8=%E2%9C%93&q=is%3Aissue%20

[chrisjlocke]

Can you upload the database to dbhub.io so we can take a look?
The data can be deleted - its just the schema which is useful.

[sezeryalcin]

yes, I can
even now I created a new db and it says this:
Warning: There is something with this table definition that our parser doesnit fully understand. Modifying and saving this table might result in problems.

[sezeryalcin]

Here is db. really empty with nothing special
https://db4s-beta.dbhub.io/sezeryalcin/schedules.sqlite3

[sezeryalcin]

Now I installed version 3.10 and it has the same issue. Previously I was using Debian 9 default version.
I have 2 languages (English and Turkish) on this laptop. Should that cause something like this?

[sezeryalcin]

When I do Copy Create Statement I get this:

CREATE TABLE data ( device_id INTEGER, id INTEGER, name TEXT, type TEXT, data TEXT, PRIMARY KEY(id) )
But When I do Edit table definition, no fields are there and showing this:
CREATE TABLE data (

);

[sezeryalcin]

Here is a more detailed error msg:
Sqlite parse error: "line 2:25: unexpected char: 'I'" ( "CREATE TABLE data (\n\tdevice_id\tINTEGER,\n\tid\tINTEGER,\n\tname\tTEXT,\n\ttype\tTEXT,\n\tdata\tTEXT,\n\tPRIMARY KEY(id)\n)" )

Also I am able to browse data and I am sure db file is fine. I also compiled older sqliteman app and it's working fine. So there is something wrong with sqlite browser.

[karim]

Thanks for mentioning your language settings. I think this might be the problem. Can you switch both DB4S and your keyboard to English and try again?

I'm just guessing since the last error shows unexpected char and it is I, and in Turkish there are many versions of I, dotted and dotless.

[sezeryalcin]

yes I already tested it with default US english and no changes and same error.
I am testing both newly created db's and existing ones, both have issues. Settings show UTF-8 encoding.
There is both dotted and non-dotted I in Turkish. This was a bit more troubling but with UTF-8 there should be no issues. I think there is some mishandled encoding somewhere.

[chrisjlocke]

Not that it helps, but in Windows, DB4S displays and edits the schema quite happily.

So it might be something to do with the languages....

[justinclift]

Just tested DB4S 3.9.1 on Fedora 25, using the DB4S provided by the distribution. That's showing the structure ok too:

The system language on this Fedora desktop is English though. It does seem like it might be a bug triggered by something to do with language settings.

[MKleusberg]

After setting my locale to Turkish I could reproduce this issue here. Switching it back to another one makes the problem go away.

This is caused by a combination of two problems we have:

1. SQL is case-independent. So for parsing SQL statements, Antlr (our parser generator) needs to convert characters to lower and/or upper case and it does this based on the current locale (which seems stupid for a parser but what are we going to do about it?). This inserts a dotted I where a dotless one should be.
2. Antlr (again) doesn't support all Unicode code points properly. With the dotted I appearing in the statement it stumbles upon that character and stops parsing.

Now, if the parsing isn't successful we can't fill in the Edit Table dialog because we just don't know how the table looks like. For browsing there is a fallback mechanism which explains why that is working for you. The only way to solve this is to fix the parsing problems mentioned above.

So I've just merged a commit which should fix problem 1. This means problem 2 isn't triggered anymore as well.

@sezeryalcin Can you check if it's working for you, too? You'll need to compile DB4S yourself to test but if you have any problems, we can help you here 😄

[justinclift]

@sezeryalcin We have step by step instructions for compiling, if that helps. I think the Ubuntu instructions also work for Debian. Literally just cut-n-paste them. 😄

    https://github.com/sqlitebrowser/sqlitebrowser/blob/master/BUILDING.md#ubuntu-linux

[sezeryalcin]

Hey guys, I will compile and let you know as possible as I need this to work. I think Martin found the main problem. Thank you for your quick responses.
In Turkish we have I and ı and İ and i. So back in early computer days, probably somebody from IBM decided to map upper I and lower İ to English ASCII I and create new characters for other 2 cases. That created a lot of issues and problems over years.

[sezeryalcin]

I confirm that this fix worked. Tested with both English and Turkish from settings. Both worked fine and showed table details properly.

Thank you all for your help.
Any guess on when this could be pushed with Debian 9 updates? I will use my own compiled version meanwhile.

[justinclift]

Any guess on when this could be pushed with Debian 9 updates?

Not sure yet. We'd need to make another main release (likely 3.11.0), and then whoever made the 3.9.1 package for Debian would need to update it for with that.

[MKleusberg]

Yeah, I think what will take most time here is us preparing a new major release. There are still many things that need to be finished. But Arto, the Debian package maintainer, usually pushes our updates relatively quickly into the Debian repository 😄

Thanks for testing by the way! I'll close this issue then. If you should notice any other problems with this (or with any other part of the program) feel free to open a new issue 😄