Обсуждение: pgaccess 0.86
Hi,
I'm playing with pgaccess 0.86, it's a very interesting tool, congratulations
to Teo for the excellent job.
I would report a bug on table information:
pgaccess displays the wrong size for types varchar (see your phonebook
example):
table name phonebook
owner marco
field information:
---------------------------------------
field name field type size
---------------------------------------
name varchar -5 <-- (32)
phone_nr varchar -5 <-- (16)
city varcahr -5 <-- (32)
company bool 1
continent char16 16
---------------------------------------
Jose'
Jose' Soares Da Silva wrote: > > pgaccess displays the wrong size for types varchar (see your phonebook > example): > > table name phonebook > owner marco > field information: > --------------------------------------- > field name field type size > --------------------------------------- > name varchar -5 <-- (32) Yes. I know! There were some changes in system tables struct between 6.2.1 and 6.3 and PgAccess is reporting wrong the varchar field size. Is there anyone who could send me the appropriate SQL query that must be used in order to determine the field name, types and sizes in 6.3.2 version? I didn't found any time to hack a bit the new system table structures. -- Constantin Teodorescu FLEX Consulting Braila, ROMANIA
On Mon, 25 May 1998, Jose' Soares Da Silva wrote:
> Hi,
> I'm playing with pgaccess 0.86, it's a very interesting tool, congratulations
> to Teo for the excellent job.
> I would report a bug on table information:
>
> pgaccess displays the wrong size for types varchar (see your phonebook
> example):
>
> table name phonebook
> owner marco
> field information:
> ---------------------------------------
> field name field type size
> ---------------------------------------
> name varchar -5 <-- (32)
> phone_nr varchar -5 <-- (16)
> city varcahr -5 <-- (32)
> company bool 1
> continent char16 16
> ---------------------------------------
The size lenght is stored in field "attlen" for types other than VARCHAR
and BPCHAR. In this case "attlen" = -1 and size lenght+4 is stored in
field "atttypmod".
Here a bug fix:
-------------------------------------------------------------------------------
2423 proc {show_table_information} {tblname} {
2423 global dbc tiw activetab indexlist
2423 set tiw(tablename) $tblname
2423 if {$tiw(tablename)==""} return;
2423 Window show .tiw
2423 .tiw.lb delete 0 end vvvvvvvvvvvvvvvvvvvvvvvvvvv
2423 .tiw.ilb delete 0 end > field atttypmod >
2423 set tiw(isunique) {} > contains lenght >
2423 set tiw(isclustered) {} > for VARCHAR and BPCHAR >
2423 set tiw(indexfields) {} vvvvvvvvv
2423 pg_select $dbc "select attnum,attname,typname,attlen, atttypmod, usename,pg_class.oid from
pg_class,pg_user,pg_attribute,pg_typewhere (pg_class.relname='$tiw(tablename)') and
(pg_class.oid=pg_attribute.attrelid)and (pg_class.relowner=pg_user.usesys
id) and (pg_attribute.atttypid=pg_type.oid) order by attnum" rec {
2423 set fsize $rec(attlen)
2423 set ftype $rec(typname)
2423 if {$fsize=="-1"} { <- if (attlen > 0) then
2423 set fsize $rec(atttypmod) <- LENGHT = attlen
2423 incr fsize -4 <- else
2423 } <- LENGHT = attypmod - 4
2423 if {$ftype=="text"} {
2423 set fsize ""
2423 }
-----------------------------------------------------------------------------
Jose'
On Mon, 25 May 1998, Jose' Soares Da Silva wrote:
> The size lenght is stored in field "attlen" for types other than VARCHAR
> and BPCHAR. In this case "attlen" = -1 and size lenght+4 is stored in
> field "atttypmod".
>
> Here a bug fix:
> -------------------------------------------------------------------------------
> 2423 proc {show_table_information} {tblname} {
> 2424 global dbc tiw activetab indexlist
> 2425 set tiw(tablename) $tblname
> 2426 if {$tiw(tablename)==""} return;
> 2427 Window show .tiw
> 2428 .tiw.lb delete 0 end vvvvvvvvvvvvvvvvvvvvvvvvvvv
> 2429 .tiw.ilb delete 0 end > field atttypmod >
> 2430 set tiw(isunique) {} > contains lenght >
> 2431 set tiw(isclustered) {} > for VARCHAR and BPCHAR >
> 2432 set tiw(indexfields) {} vvvvvvvvv
> 2433 pg_select $dbc "select attnum,attname,typname,attlen, atttypmod, usename,pg_class.oid from
pg_class,pg_user,pg_attribute,pg_typewhere (pg_class.relname='$tiw(tablename)') and
(pg_class.oid=pg_attribute.attrelid)and (pg_class.relowner=pg_user.uses
ys
> id) and (pg_attribute.atttypid=pg_type.oid) order by attnum" rec {
> 2434 set fsize $rec(attlen)
> 2435 set ftype $rec(typname)
> 2436 if {$fsize=="-1"} { <- if (attlen > 0) then
> 2437 set fsize $rec(atttypmod) <- LENGHT = attlen
> 2438 incr fsize -4 <- else
> 2439 } <- LENGHT = attypmod - 4
> 2440 if {$ftype=="text"} {
> 2441 set fsize ""
> 2442 }
> -----------------------------------------------------------------------------
I created a table with all data types to know the behavior of all data types
and I see there are some types with attlen and atttypmod both equal to -1
therefore for these types we don't have the size:
. text
. varchar (defined without size)
. bpchar (defined without size)
. bytea
. path
. polygon
I correct pgaccess.tcl as following:
<DELETED>
2423 pg_select $dbc "select attnum,attname,typname,attlen,atttypmod,usename,pg_class.oid from
pg_class,pg_user,pg_attribute,pg_typewhere (pg_class.relname='$tiw(tablename)') and
(pg_class.oid=pg_attribute.attrelid)and (pg_class.relowner=pg_user.usesysid
) and (pg_attribute.atttypid=pg_type.oid) order by attnum" rec {
2424 set fsize $rec(attlen)
2425 set fsize1 $rec(atttypmod)
2426 set ftype $rec(typname)
2427
2428 if { $fsize=="-1" && $fsize1!="-1" } {
2429 set fsize $rec(atttypmod)
2430 incr fsize -4
2431 }
2432
2433 if { $fsize1=="-1" && $fsize=="-1" } {
2434 set fsize ""
2435 }
2436
2437 if {$rec(attnum)>0} {.tiw.lb insert end [format "%-33s %-14s %-4s" $rec(attname) $ftype $fsize]}
2438 set tiw(owner) $rec(usename)
<DELETED>
-------------------------------------------------------------------------
...and now it pgaccess.tcl works with all PostgreSQL data types.
Jose'
This is PgAccess 0.87, a small bug fix release. (same http://www.flex.ro/pgaccess and ftp://ftp.flex.ro/pub/pgaccess download sites available) With the help of Jose' Soares Da Silva, now table information shows field sizes corectly. Thanks Jose, -- Constantin Teodorescu FLEX Consulting Braila, ROMANIA