[Gambas-user] Issue 193 in gambas: SQLite Foreign Key code not allowed

[gambas at ...2524...]

Status: New
Owner: ----
Labels: Version Type-Bug Priority-Medium OpSys-Any Dist-Any Arch-Any  
Desktop-Any GUI-Any

New issue 193 by john.aar... at ...626...: SQLite Foreign Key code not allowed
http://code.google.com/p/gambas/issues/detail?id=193

I'm using SQLite 3.6.22. I've installed the gambas3-gb-db-sqlite3 package.  
I'm not able to specify a Foreign Key for a SQLite3 table (country 'linked'  
to city table) in Gambas code (though I can do it when running SQLite3 from  
Terminal).
Code sample:
   DatabaseConnection.Exec("PRAGMA foreign_keys = 1")
   ' Check if the database has a country table
   If Not DatabaseConnection.Tables.Exist("country") Then
     Print "Database country table not found. Creating new country table"
     ' Add a country table to the database
     DatabaseConnection.Begin
     JHTable = DatabaseConnection.Tables.Add("country")
     With JHTable
       .Fields.Add("_id", db.Serial) '             id          field as  
autoinc integer
       .Fields.Add("country", db.String) ' country field as unlimited string
       .PrimaryKey = ["_id"]
       .Update()
     End With
     DatabaseConnection.Commit
   End If
   If Not DatabaseConnection.Tables.Exist("city") Then
     Print "Database city table not found. Creating new city table"
     ' Add a city table to the database
     DatabaseConnection.Begin
     JHTable = DatabaseConnection.Tables.Add("city")
     With JHTable
       .Fields.Add("_id", db.Serial) '_id field as autoinc integer
       .Fields.Add("city", db.String) 'city field as unlimited string
       .Fields.Add("country_id", db.Integer) 'country_id field as integer"
       .PrimaryKey = ["_id"]
       .ForeignKey("country_id", "country", "_id", "CASCADE", "CASCADE")
'     Foreign Key not working in Gambas2
       .Update()
     End With
     DatabaseConnection.Commit
The problem is that the Gambas3 runtime gives a popup stating "Unknown  
symbol 'ForeignKey' in class 'Table'". In Gambas2, it gave a compilation  
error on the ForeignKey line. For both Gambas2 & Gambas3, I changed the  
code to remove the With &End With statements and explicitly coded JHTable  
before each 'Fields.' with the result that a ForeignKey selectio option was  
not given though a PrimaryKey selection option was given.

Version: 2.22 & 3.0
Operating system: Linux / FreeBSD
Distribution: Ubuntu 10.04
Architecture: x86
GUI component: GTK+ (as I haven't specified the GUI)
Desktop used: Gnome2

Project attached - relevant code is in ModuleDatabase.

Database (after running code attached).




Attachments:
	JewishHeritage.tar.gz  22.9 KB
	JewishHeritage.db  3.0 KB