search

Buzzinoffbond / phpliteadmin

Automatically exported from code.google.com/p/phpliteadmin
0 stars 0 forks source link

Tables without rowid, no rows are found/shown #240

Closed GoogleCodeExporter closed 9 years ago

GoogleCodeExporter commented 9 years ago 
What steps will reproduce the problem?
1. Create a table without rowid (an optimization which is available with sqlite 
2.8.3 and later), eg
[public_html]$ sqlite3 without_rowid.db
SQLite version 3.8.2 2013-12-06 14:53:30
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table with_rowid (c1, c2);
sqlite> create table without_rowid (p1 primary key, c2) without rowid;
sqlite> insert into with_rowid values (1, 2);
sqlite> insert into without_rowid values (3, 4);
sqlite> select * from with_rowid;
1|2
sqlite> select * from without_rowid;
3|4
2. Open the db in browser with phpliteadmin, both tables "with_rowid" and 
"without_rowid" are found
3. Select table "with_rowid" and one row is shown, now select table 
"without_rowid"

What is the expected output? What do you see instead?

Again one row should be displayed, but instead no row is found

What version of the product are you using? On what operating system? Which
Database Extension (PDO/SQLiteDatabase/SQLiteDatabase3 - see Database
structure-tab in phpLiteAdmin)?

Fedora 20, phpLiteAdmin v1.9.4.1, SQLite version 3.8.2 2013-12-06

Please provide any additional information below.

The "WITHOUT ROWID" optimization is documented at 
https://www.sqlite.org/withoutrowid.html

Original issue reported on code.google.com by StephanB...@gmail.com on 30 Dec 2013 at 7:36

GoogleCodeExporter commented 9 years ago 
Thanks for reporting this issue. We should look at it and if we can confirm it, 
which I think we will, we should fix it with 1.9.6.

Original comment by crazy4ch...@gmail.com on 15 Jan 2014 at 9:48

GoogleCodeExporter commented 9 years ago 
I was able to reproduce this using SQLite 3.8.6.
We are using the ROWID column to identify a row, e.g. as parameter when 
deleting or editing a row. We need to use the primary key instead for WITHOUT 
ROWID tables. This means we cannot rely on it being integer and so on.
I will have a closer look at it and see how much effort it is.

Original comment by crazy4ch...@gmail.com on 26 Dec 2014 at 11:47

GoogleCodeExporter commented 9 years ago 
With revision dbb1af5da0cb9b495c1d4bc33f77333c9eac5001 I started to fix this.
Just tried it with a table without rowid. It basically works.
There were still problems with SQLites weak type system which I addressed in 
20279d45c6ab5e344a71ef9c25b04a2e2e6a5b8b.
One problem still remains: If the table is with rowid, SQLite allows primary 
keys to be NULL. Even worse, multiple rows can exist with the primary key being 
NULL. These rows currently cannot be distinguished by phpLiteAdmin. Therefore, 
I guess we will use rowids when possible and only use primary keys if the table 
is without rowid.

Original comment by crazy4ch...@gmail.com on 27 Dec 2014 at 6:33

GoogleCodeExporter commented 9 years ago 
With revision 022cd162f9e003a697bf75de6062d5280d1118f7, phpLiteAdmin again uses 
rowids to address rows if the table has one.  If the table does not have one, 
it uses the primary key.

In this case the primary key must not be NULL (enforced by SQLite), so there is 
no problem with multiple rows having a NULL primary key anymore.

Original comment by crazy4ch...@gmail.com on 27 Dec 2014 at 7:45