[prev in list] [next in list] [prev in thread] [next in thread] 

List:       gambas-user
Subject:    Re: [Gambas-user] Gambas & PostgreSQL cannot create index. relation
From:       Randy <bug.me () comcast ! net>
Date:       2009-01-24 22:56:23
Message-ID: 497B9C97.5030900 () comcast ! net
[Download RAW message or body]



DIM hTable AS Table

  hTable = Global.$hConn.Tables.Add("glcoa")
  WITH hTable
    .Fields.Add("company", db.String, 3)
    .Fields.Add("account", db.String, 12)
    .Fields.Add("description", gb.String, 32)
    .Fields.Add("gltype", gb.String, 1)
    .Fields.Add("glsort", gb.Integer)
    .Fields.Add("status", gb.String, 1)
    .PrimaryKey = ["account"]
    .Update
   END WITH

Here's the debug output for above code. Take notice of the CREATE TABLE SQL. This \
work without any error.

postgresql: 0x8159de8: select substring(version(),12,5)
postgresql: 0x8159de8: show client_encoding
postgresql: 0x8159de8: select relname from pg_class where (relkind = 'r' 
or relkind = 'v') and (relname = 'glcoa') and (relnamespace not in 
(select oid from pg_namespace where nspname = 'information_schema'))
postgresql: 0x8159de8: CREATE TABLE "glcoa" ( "company" VARCHAR(3), 
"account" VARCHAR(12) NOT NULL , "description" VARCHAR(32), "gltype" 
VARCHAR(1), "glsort" INT, "status" VARCHAR(1), PRIMARY KEY (account) )
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 
"glcoa_pkey" for table "glcoa"



Change code to make index:


  DIM hTable AS Table

  hTable = Global.$hConn.Tables.Add("glcoa")
  WITH hTable
    .Fields.Add("company", db.String, 3)
    .Fields.Add("account", db.String, 12)
    .Fields.Add("description", gb.String, 32)
    .Fields.Add("gltype", gb.String, 1)
    .Fields.Add("glsort", gb.Integer)
    .Fields.Add("status", gb.String, 1)
    .PrimaryKey = ["account"]
    .Indexes.Add("glcoa_sort", "glsort", TRUE)
    .Update
   END WITH

Here's the debug output with index. Their is NO CREATE TABLE SQL when you try make \
table with indexes. It's missing! It looks like this is a bug in Gambas when creating \
table with indexes.

postgresql: 0x8159aa8: select substring(version(),12,5)
postgresql: 0x8159aa8: show client_encoding
postgresql: 0x8159aa8: select relname from pg_class where (relkind = 'r' 
or relkind = 'v') and (relname = 'glcoa') and (relnamespace not in 
(select oid from pg_namespace where nspname = 'information_schema'))
postgresql: 0x8159aa8: select pg_class.relname from pg_class, pg_index, 
pg_class pg_class2 where pg_class2.relname = 'glcoa' and 
(pg_class2.relnamespace not in (select oid from pg_namespace where 
nspname = 'information_schema')) and pg_index.indrelid = pg_
class2.oid and pg_index.indexrelid = pg_class.oid and pg_class.relname = 
'glcoa_sort'
postgresql: 0x8159aa8: CREATE UNIQUE INDEX "glcoa_sort" ON glcoa ( glsort )



Randy :-)

BTW; I'm using Gambas 2.10.2

> 
> I just take a look in the source code
> ---------------------
> BEGIN_METHOD(CINDEX_add, GB_STRING name; GB_STRING fields; GB_BOOLEAN unique)
> 
> CTABLE *table = GB.SubCollection.Container(THIS);
> char *name = GB.ToZeroString(ARG(name));
> DB_INDEX info;
> 
> if (DB_CheckName(name, "index"))
> return;
> 
> if (check_index(table, name, FALSE))
> return;
> 
> info.name = name;
> info.fields = GB.ToZeroString(ARG(fields));
> info.unique = VARGOPT(unique, FALSE);
> 
> table->driver->Index.Create(&table->conn->db, table->name, name, &info);
> 
> END_METHOD
> 
> 
> ----------------------------
> if (check_index(table, name, FALSE))
> return;
> 
> 'name' here is your 'glcoa_sort'
> 
> My comment here is that it _looks_ as the required name 'glcoa_sort' must 
> exist/notexist also as field name in the table.
> It should be not exists of course.
> 
> In the above source the last line starting with table looks to me
> as Create index ('thedatabse', table->'glcoa_sort', 'glcoa_sort' , DB_INDEX)
> 
> what results in: "glcoa_sort" ON glcoa (glsort) 
> Anyway here happens something strange to me.
> 
> 
> 
> Did you Try it with 'glcoaaccount' and 'glcoasort' (without the underscore) ???
> and what happens then?
> 
> In your reply:
> postgresql: 0x8159aa8: CREATE UNIQUE INDEX "glcoa_sort" ON glcoa ( glsort )
> 
> it look as it try to use table glcoa, the name after ON !!
> 
> BTW do you have a table named glcoa?
> 
> 
> @benoit
> 
> in CIndex.h
> 
> void *CINDEX_get(CTABLE *table, const char *name);
> int CINDEX_exist(CTABLE *table, const char *name);
> void CINDEX_list(CTABLE *table, char ***list);
> void CINDEX_release(CTABLE *table, void *_object);
> 
> I missing CINDEX_create ????
> Second why the first is void *CIndex and the two last void CIndex ??
> 
> 
> 
> 
> Best regards,
> 
> Ron_1st
> 
> 


=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Scanned with Copfilter Version 0.84beta3a (ProxSMTP 1.6)
AntiSpam:  SpamAssassin 3.2.3
AntiVirus: ClamAV 0.91.2/8899 - Sat Jan 24 08:06:33 2009
by Markus Madlener @ http://www.copfilter.org

------------------------------------------------------------------------------
This SF.net email is sponsored by:
SourcForge Community
SourceForge wants to tell your story.
http://p.sf.net/sfu/sf-spreadtheword
_______________________________________________
Gambas-user mailing list
Gambas-user@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/gambas-user


[prev in list] [next in list] [prev in thread] [next in thread]