System.Data.SQLite

I'm still playing w/ writing a simple SQLite 3 schema browser using the GetSchema() support in SQLite.Net version 1.0.26.1.  I've run into what I think is a bug in the logic which determines what indexes are primary keys.

The logic, in SQLiteConnection::Schema_Indexes, is complicated due to the surprising difficulty of determining which indices are primary keys using the SQLite API.  A heuristic is used whereby INTEGER PRIMARY KEYs are assumed to not have a corresponding index in the SQLITE_MASTER table, therefore a 'fake' index is inserted into the resulting table, while other primary keys are assumed to have an automatically generated index 'sqlite_autoindex_[tablename]_[n]'.  In this later case, the 'sqlite_autoindex_[tablename]_' prefix is saved into the primaryKey variable so the subsequent code which enumerates the 'plain' SQLite indices can check for indices with this prefix, and assume them to be primary keys.

This brittle logic seems a necessary workaround of a limitation in the SQLite API.  Unfortunately, due to the way the logic is written, if a table has an INTEGER PRIMARY KEY, the primaryKey variable is left at "", so all subsequent indexes on that table will pass the StartsWith(primaryKey) test, thereby marking those indices as primary keys as well!

I've modified the SQLiteConnection.cs file to correct this problem.  The new file (based on the 1.0.26.1 version) is at:

http://apocryph.org/~anelson/sqliteconnection.cs

The SQL to create the database that I've been using to test this behavior is at:

http://apocryph.org/~anelson/testdb.sql

Without this fix, the indices 'ip_id' and 'idx_ip_se' on 'int_pkey', among others, are reported incorrectly as primary keys by GetSchema().

If there's some other more convenient way for me to provide this patch do let me know.

Adam

Damn!  I have been tweaking that dumb index code for forever and bugs are still getting unearthed!  The two most difficult pieces of all the schema code have been  determining the "unique" flag in the DataReader.GetSchemaTable() function, and determining primary keys.  I'm not surprised there's an occasional logic hole in there.

Thanks for the report.  I'll be updating CVS today and will have some binaries checked in for testing a bit later.

Robert

 

Well, seems that the new mechanism doesnt work for composite primary keys ?!

The primary key index for this table

CREATE TABLE OrderDetails ( 
   OrderId INTEGER, 
   ProductId INTEGER, 
   UnitPrice DECIMAL,
   Qunatity TINYINT,
   DISCOUNT FLOAT,
   CONSTRAINT PK_OrderDetails PRIMARY KEY ( OrderId,  ProductId )
);

queried using the following:

   string pkIndex = String.Empty;

   DataTable idxes = GetSchema("Indexes", 
      new string[]{"Main",null,"OrderDetails"});
   
   foreach(DataRow row in idxes.Rows) {
      if (row["PRIMARY_KEY"] != DBNull.Value && Convert.ToBoolean(row["PRIMARY_KEY"])) {
         pkIndex = row["INDEX_NAME"].ToString();
         break;
      }
   }
   DateTable idxCols = GetSchema("IndexColumns", 
      new string[] {"Main",null,"OrderDetails", idxes });

returns an index named "sqlite_master_PK_OrderDetails" with only 1 row (OrderId).

By design ;) or i'm doing something wrong ?

sigh.  I hate SQLite's index schema thingie.  I'll test this out and let you know.

Robert

 

Ok, here's the way it works apparently ...

For primary keys, if only one column is a primary key, and if that column is type integer, then no index is created and the column is an alias for the rowid.  Otherwise, for all other types of indexes and primary keys, including multiple primary keys, a real index is created.

I think I'm finally done.  Unless someone else finds yet another exception.

Robert

 

Ok I've checked in the final tweaks to sqlite's index schemas.  I also took the opportunity to tune both functions as well as the GetSchemaTable() code in SQLiteDataReader.  I've tested it on several variations of indexes and constraints, and it seems to function properly for all of them.

I've heard rumors that SQLite 3.3.5 is not far away, so I'm going to hold off a little before releasing anything official.  I will however, checkin binaries that you may check out and test with.