SubSonic does not recognize SQLite foreign keys

Question

I'm using SubSonic 3.0.0.3, and I can't seem to get ActiveRecord ActiveX files to recognize and generate code for foreign keys and relationships in my SQLite database.

I think that it generates everything else just fine, but, looking at other snippets online, it looks like there should be more code generated than just one class in ActiveRecord.csand Structs.csfor each of my tables. Inside Structs.cs, IsForeignKeyalways falsefor every column, even for those for which I set a foreign key. In addition, each area is Foreign Keysempty in each generated ActiveRecord class.

I am using VS2008 with links to SubSonic 3.0.0.3, System.Data.SQLite 1.0.66.0 and System.Data.SQLite.Linq 2.0.38.0 in my project. I created a database using SQLite Expert Personal 3.1.0.2076. I made some dummy tables to try and check how SubSonic handles one: many, many: many relationships. This is where DDL SQLite Expert spills out my small database:

CREATE TABLE [Person] (
[PersonID] INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT,
[PersonName] TEXT  NOT NULL,
[PersonAge] INT  NOT NULL
);

CREATE TABLE [Group] (
[GroupID] INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT,
[GroupName] TEXT  NOT NULL,
[GroupDescription] TEXT  NOT NULL
);

CREATE TABLE [Dog] (
  [DogID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 
  [PersonID] INT NOT NULL CONSTRAINT [DogPersonFK] REFERENCES [Person]([PersonID]) ON DELETE CASCADE ON UPDATE CASCADE, 
  [DogName] TEXT NOT NULL);

CREATE TABLE [GroupPersons] (
  [GroupID] INTEGER NOT NULL CONSTRAINT [GroupPersonToGroupFK] REFERENCES [Group]([GroupID]) ON DELETE CASCADE ON UPDATE CASCADE, 
  [PersonID] INTEGER NOT NULL CONSTRAINT [GroupPersonToPersonFK] REFERENCES [Person]([PersonID]) ON DELETE CASCADE ON UPDATE CASCADE, 
  CONSTRAINT [sqlite_autoindex_GroupPersons_1] PRIMARY KEY ([GroupID], [PersonID]));

, . SQLite Expert , , , Person PersonID, PersonID Dog GroupPersons. , " ", .tt, . , , Person, Dog Group x.Save(), System.Data.SQLite , SQLite error near "WHERE":syntax error. Save().

, ?

Answer 1

, FKTables "SQLite.ttinclude". key code:

16 (var schema = conn.GetSchema( "COLUMNS" );), :

var schemaForeignKeys = conn.GetSchema("FOREIGNKEYS");

29 (tbl.Name = row [ "TABLE_NAME" ]. ToString();) :

tbl.FKTables = new List<FKTable>();
var foreignKeyTables = schemaForeignKeys.Select("TABLE_NAME='" + tbl.Name + "'");
foreach (var foreignKeyTable in foreignKeyTables) {
    FKTable foreignKey = new FKTable();
    foreignKey.ThisTable = foreignKeyTable["TABLE_NAME"].ToString();
    foreignKey.ThisColumn = foreignKeyTable["FKEY_FROM_COLUMN"].ToString();
    foreignKey.OtherTable = foreignKeyTable["FKEY_TO_TABLE"].ToString();
    foreignKey.OtherColumn = foreignKeyTable["FKEY_TO_COLUMN"].ToString();
    foreignKey.OtherClass = CleanUp(foreignKey.OtherTable);
    foreignKey.OtherQueryable = foreignKey.OtherClass;
    tbl.FKTables.Add(foreignKey);
}

53 (col.IsNullable = [ "IS_NULLABLE" ]. ToString() == "True";), insert:

col.IsForeignKey = tbl.FKTables.Any(x => x.ThisColumn == col.Name);

key code.

, , , ? : (Id, Name) (Id, #PersonId) #PersonId - "SET TO NULL", , SQLite 3.6.23.1 (, Data.SQLite 1.0.66.0). , :

PRAGMA foreign_keys = ON;

Data.SQLite, ( 1.0.67.0, http://sqlite-dotnet2.cvs.sourceforge.net/viewvc/sqlite-dotnet2/SQLite.NET/System.Data.SQLite/SQLiteConnection.cs?r1=1.80&r2=1.81).

, ( ) Data.SQLite . .

. :)

Answer 2

. , :

• x.Save() .

SQLite , , Subsonic , SQLite , . Subsonic SQLite, , .

, SubSonic. , : . x.Save() SQL, , .

( ) , , , :

CREATE TABLE [Dog] (
  [DogID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 
  [PersonID] INTEGER NOT NULL,
  [DogName] TEXT NOT NULL,
  FOREIGN KEY ([PersonID]) REFERENCES [Person]([PersonID]) ON DELETE CASCADE ON UPDATE CASCADE);

CREATE TABLE [GroupPersons] (
  [GroupID] INTEGER NOT NULL,
  [PersonID] INTEGER NOT NULL,
  FOREIGN KEY ([GroupID]) REFERENCES [Group]([GroupID]) ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY ([PersonID]) REFERENCES [Person]([PersonID]) ON DELETE CASCADE ON UPDATE CASCADE,
  PRIMARY KEY ([GroupID], [PersonID]));