Bug with Sqlite Exception while processing Server Explorer drag

Hi, I am running Visual Studio 2008 SP1 with System.Data.SQLite 1.0.58 installed side by side with

the 1.0.48 installed by lightspeed on Windows Vista SP1 x64.

The GAC appears to have x86 version of 1.0.48, and x86,x64 versions of the latest 1.0.58
as this following listing, that I got through PowerShell + PSCX shows:

2# dir System.Data*

Version        PublicKeyToken    Arch  Culture Name
-------        --------------    ----  ------- ----
2.0.0.0        b77a5c561934e089  Amd64         System.Data
2.0.0.0        b77a5c561934e089  X86           System.Data
3.5.0.0        89845dcd8080cc91  MSIL          System.Data.SqlServerCe
3.5.1.0        89845dcd8080cc91  MSIL          System.Data.SqlServerCe
3.5.0.0        b77a5c561934e089  MSIL          System.Data.Services
3.5.0.0        b77a5c561934e089  MSIL          System.Data.Entity
3.5.0.0        b77a5c561934e089  MSIL          System.Data.DataSetExtensions
2.0.0.0        b77a5c561934e089  MSIL          System.Data.SqlXml
2.0.37.0       db937bc2d44ff139  MSIL          System.Data.SQLite.Linq
1.0.58.0       db937bc2d44ff139  Amd64         System.Data.SQLite
1.0.48.0       db937bc2d44ff139  X86           System.Data.SQLite
1.0.58.0       db937bc2d44ff139  X86           System.Data.SQLite
1.0.58.0       1fdb50b1b62b4c84  MSIL          System.Data.SQLite
3.5.0.0        b77a5c561934e089  MSIL          System.Data.Linq
3.5.1.0        89845dcd8080cc91  MSIL          System.Data.SqlServerCe.Entity
3.5.0.0        b77a5c561934e089  MSIL          System.Data.Services.Design
2.0.0.0        b77a5c561934e089  Amd64         System.Data.OracleClient
2.0.0.0        b77a5c561934e089  X86           System.Data.OracleClient
3.5.0.0        b77a5c561934e089  MSIL          System.Data.Entity.Design
3.5.0.0        b77a5c561934e089  MSIL          System.Data.Services.Client

When trying to drag a table from the server explorer I get the following (somewhat illogical?) exception..

 Exception while processing Server Explorer drag: System.EntryPointNotFoundException: Unable to find an entry point named 'sqlite3_open_interop' in DLL 'System.Data.SQLite.DLL'.
   at System.Data.SQLite.UnsafeNativeMethods.sqlite3_open_interop(Byte[] utf8Filename, IntPtr& db)
   at System.Data.SQLite.SQLite3.Open(String strFilename, Boolean usePool)
   at System.Data.SQLite.SQLiteConnection.Open()
   at Mindscape.LightSpeed.Generator.Extraction.Sqlite3.OpenConnection()
   at Mindscape.LightSpeed.Generator.Extraction.Provider`1.ExtractModel(Boolean pluralise)
   at Mindscape.LightSpeed.Generator.Integration.EntityBuilder.PopulateTypeModel(ProviderType providerType, String connectionString)
   at Mindscape.LightSpeed.Generator.Integration.EntityBuilder.PopulateTypeModel(IDbConnection connection)
   at Mindscape.LightSpeed.Generator.Integration.EntityBuilder.AddTablesToModel(Model model, IDbConnection connection, IEnumerable`1 tableNames, IGenerationLog log)
   at Mindscape.LightSpeed.Generator.Integration.LightSpeedModelDiagram.<>c__DisplayClass4.<OnDragDrop>b__3(DragObject dragObject, IGenerationLog log)
   at Mindscape.LightSpeed.Generator.Integration.ServerExplorer.IfIsServerExplorerDrag(DragEventArgs e, DragAction action)

 Please advise...

Thanks for the detailed report.  This problem seems to be the result of having both 1.0.48 and 1.0.58 in the GAC, though as you say the actual exception that is thrown is a bit strange: it is as if the loader is using different search strategies for managed and unmanaged code, and ends up looking for the sqlite3_open_interop entry point in the wrong version of the DLL (the entry point in 1.0.58 has a different signature).  The reason for the confusion seems to be that VS Server Explorer is loading 1.0.58 and LightSpeed is loading 1.0.48.

I will investigate for a permanent fix, but in the meantime the problem can be addressed by setting up a binding redirect for 1.0.48.0, redirecting it to 1.0.58.0.  You can do this either at the devenv.exe application level or at the machine level.  (I am sure you are familiar with setting up redirects, but just in case: go into Administrative Tools > Microsoft .NET Framework 2.0 Configuration; go into Configured Assemblies and choose Add Configured Assembly; enter System.Data.SQLite and the appropriate public key token; and on the Binding Policy tab enter Requested Version 1.0.48.0 and New Version 1.0.58.0.  Or, if you want the redirect to apply to Visual Studio only, go into Applications, add devenv.exe, go to the Configured Assemblies node underneath that, and proceed as above.)  Let us know if this doesn't work around the problem for you, and as I say I will continue to investigate for a proper fix.

I actually just got around of it by installing a nightly build.....

Your nightlies are compiled with 1.0.58 so the problem kind of solves itself.

Now that I can actually read in stuff... How does one setup "relations" in SQLite between tables in a way that

lightspeed can pick it up?

If you set up a foreign key when creating the tables, that should be enough for the designer to infer the association.  LightSpeed's convention is that the FK column name should end in Id -- we will use that to infer the name of the association.

Here's an (abbreviated) example from our test suite:

CREATE TABLE Grants
(
 /* ... */
);

CREATE TABLE Grantees
(
    Id           NVARCHAR(50) NOT NULL PRIMARY KEY,
    GrantId      NVARCHAR(50) NOT NULL,
   
  FOREIGN KEY (GrantId) REFERENCES Grants(Id)   
);

The LightSpeed runtime engine does *not* require the foreign key (but in that case you'll have to set up the relationship by hand, either in code or in the designer using the toolbox).

 I have something like this:

CREATE  TABLE IF NOT EXISTS `files` (
  `id` INT NOT NULL,
  `filemame` VARCHAR(1024) NOT NULL ,
  `import_date` DATETIME NOT NULL ,
  PRIMARY KEY (`id`) )

CREATE  TABLE IF NOT EXISTS `streams` (
  `id` INT NOT NULL,
  `files_id` INT NOT NULL ,
  ...
  CONSTRAINT `fk_streams_files`
  FOREIGN KEY (`files_id` )
  REFERENCES `files` (`id` ),
  ...
);

Which doesn't seem to be working.

When I drag this table into the editor I will get all of the fields without any of the FKs.

Should I just redo the schema withuot the CONSTRAINT KeyWord?

Or should the Id Suffix be without an leading '_' ?

 Hey,
Just wanted "everyone" to know that once you omit that:

CREATE  TABLE IF NOT EXISTS `streams` (
  `id` INT NOT NULL,
  `files_id` INT NOT NULL ,
  ...
  FOREIGN KEY (`files_id` )
  REFERENCES `files` (`id` ),
  ...
);

Works with Lightspeed, while this doesn't:

CREATE  TABLE IF NOT EXISTS `streams` (
  `id` INT NOT NULL,
  `files_id` INT NOT NULL ,
  ...
  CONSTRAINT `fk_streams_files`
  FOREIGN KEY (`files_id` )
  REFERENCES `files` (`id` ),
  ...
); 

I'm a happy beaver at this point. Now just to get the same thing working with MySQL

This appears to be down to SQLite slightly mishandling the backquotes.  Normally when SQLite reports a table name, it reported the unquoted name (e.g. files).  But for some reason when it reports the name of the table referenced by a foreign key, it reports the quoted name (e.g. `files`).  We don't recognise the name in its quoted form and therefore fail to locate a target entity for the association.

You can fix this by declaring your foreign key as:

CONSTRAINT `fk_streams_files`
  FOREIGN KEY (`files_id` )
  REFERENCES files (`id` )

(note lack of backquotes around files in the REFERENCES clause).

I will log an issue for this and we will see if we can make our name matching a bit smarter.