[Gambas-devel] More Database Driver Changes

[Nigel Gerrard]

Benoit,

Could you have a look at the following changes and feedback your
comments.  The changes apply to gambas-0.92.

The major change is an attempt to implement access to fields with the
same name contained in a multiple table query. e.g.

select fred.field1, sid.field1 from fred, field.

In order for this to work within Postgresql, version 7.4.1 or greater
needs to be available.

Please note that I have defined the $ character to separate
tablename$filedname within the class.  It would be better if '.' could
be used as $ is valid within field/tablename.  In each of my attempts to
use '.' a syntax error is thrown (thus I've gone to $). I'm sure  that
you will be able to get around this.

Project.Module is a rouh demonstration of what I have been trying to
achieve.

The other major change (and probably the one I have not tested enough)
is the use of parameters within .EXEC eg. .Exec("select * from &1",
table) would get evaluated as select * from 'table' which would throw an
error in mysql or postgresql.

Have a look at the changelogs to see what else has been implemented. 
Drop me a line if there is anything you need.

Nigel
-------------- next part --------------
A non-text attachment was scrubbed...
Name: db_changes.tar.gz
Type: application/x-compressed-tar
Size: 11881 bytes
Desc: not available
URL: <http://lists.gambas-basic.org/pipermail/devel/attachments/20040419/fb1fd345/attachment.bin>
-------------- next part --------------
' Gambas class file

PRIVATE $hConn AS NEW Connection

PUBLIC PROCEDURE Main()

  DIM rTest AS Result
  DIM sTable1 AS String
  DIM sTable2 AS String

  sTable1 = "Player"
  sTable2 = "Team"

  WITH $hConn
    '.Type = "postgresql"
    '.Type = "mysql"
    .Type = "sqlite"
    .Host = "localhost"
    
    IF $hConn.Type = "sqlite" THEN  
      '.Host = "/home/ngerrard/gambas-0.91a/app/gambas-database-manager/"
    ELSE
      '.Host = "10.10.10.0"
    ENDIF
    .Login = "ngerrard"
    .Password = ""
  END  WITH

  TRY $hConn.Open
  IF ERROR THEN PRINT "NIGEL: DATABASE NOT Running"
  'END IF

  IF NOT $hConn.Database.Exist("test") THEN
     PRINT "Database does not exist"
     $hConn.Database.Create("test")
     $hConn.Close
     QUIT
  ENDIF

  $hConn.Close

  $hConn.Name = "test"
  $hConn.Open

  ' Create and populate the tables
  Create_tables()
  Enter_details()
  'Test 1
  'rTest = $hConn.Exec("select * from Player,Team where Player.Field2 = Team.id")
  'rTest = $hConn.Exec("select Player.Field1, Team.Field1, Field2 from Player,Team where Player.Field2 = Team.id")
  'Test 2
  'rTest = $hConn.Exec("select Player.Field1 a, Team.Field1 b, Field2 from Player,Team where Player.Field2 = Team.id")
  'Test 3
  'rTest = $hConn.Exec("select a.Field1, b.Field1, Field2 from Player a,Team b where a.Field2 = b.id") '- does not work. Player.Field1 is shown
  'Test 4
  'rTest = $hConn.Exec("select a.Field1 as Foo, b.Field1 as Bar, Field2 from Player a,Team b where a.Field2 = b.id") 
  'Test 5
  rTest = $hConn.Exec("select * from &1, &2 where Player.Field2 = Team.id", sTable1, sTable2)

  PRINT "The select statement contains the following fields:"
  FOR EACH rTest.Field
      PRINT rTest.Field.Name
  NEXT

  PRINT "Type : " & $hConn.Type & " Database : " & $hConn.Name
  PRINT "Records selected :"
  WHILE rTest.Available
        'Test 1
        'PRINT rTest!Player$Field1 & ":" & rTest!Team$Field1 & ":" & rTest!Field2
        'Test 2
        'success for mysql
        'fails for postgresql
        'partly for sqlite
        
        'PRINT rTest!a & ":" & rTest!b & ":" & rTest!Field2 'works with mysql, not with postgresql and sort of works with sqlite
        'Test 3
        'fails for sqlite
        'success for mysql
        'fails for postgresql
        'PRINT rTest!a$Field1 & ":" & rTest!b$Field1 & ":" & rTest!Field2 
        'Test 4
        'For postgresql test: Works
        'PRINT rTest!foo & ":" & rTest!bar & ":" & rTest!Field2 
        'Test 5
        PRINT rTest!Player$Field1 & ":" & rTest!Team$Field1 & ":" & rTest!Field2
        rTest.MoveNext
  WEND

  ' Drop Tables
  $hConn.Tables.Delete("Player")
  $hConn.Tables.Delete("Team")

  $hConn.Close

  PRINT "Ending..."
  'CATCH
  '  PRINT "Nigel: Cannot open database"

END

PUBLIC SUB Create_tables()

  DIM hTable AS Table
  
  IF $hConn.Tables.Exist("Player") THEN
     $hConn.Tables.Delete("Player")
  ENDIF

  IF $hConn.Tables.Exist("Team") THEN
     $hConn.Tables.Delete("Team")
  ENDIF

  IF $hConn.Type = "mysql" THEN  
     hTable = $hConn.Tables.Create("Player","InnoDB")
  ELSE
     hTable = $hConn.Tables.Create("Player")
  ENDIF
  hTable.Field.Create("id", gb.Integer)
  hTable.Field.Create("Field1", gb.String)
  hTable.Field.Create("Field2", gb.Integer)
  hTable.PrimaryKey=["id"]
  hTable.Update

  hTable = $hConn.Tables.Create("Team")
  hTable.Field.Create("id", gb.Integer)
  hTable.Field.Create("Field1", gb.String)
  hTable.PrimaryKey=["id"]
  hTable.Update

  PRINT "Tables successfully Created"

'CATCH

'  PRINT "Unable to Create Table"

END

PUBLIC SUB Enter_details()

  DIM rTest AS Result

  rTest= $hConn.Create("Player")

  rTest!id = 1
  rTest!Field1 = "Kevin Keegan"
  rTest!Field2 = 1
  rTest.Update

  rTest!id = 2
  rTest!Field1 = "Joe Royle"
  rTest!Field2 = 2
  rTest.Update

  rTest= $hConn.Create("Team")

  rTest!id = 1
  rTest!Field1 = "Liverpool"
  rTest.Update

  rTest!id = 2
  rTest!Field1 = "Manchester City"
  rTest.Update

END