ROE and domains-table

Hi,

I've dabbled around with PostgreSQL, DBI and ROE today. That was fun.
Almost no distracting docs to keep me from wading through the source .-)

I'm now looking at a local dump of the database I'm targeting, which
has a few tables used by exim for handling emails.

s8 gst # psql vmail
psql (8.4.4)
Type "help" for help.

vmail=# \d
              List of relations
  Schema |      Name      |   Type   | Owner
--------+----------------+----------+-------
  public | aliases        | table    | root
  public | aliases_id_seq | sequence | root
  public | domains        | table    | root
  public | domains_id_seq | sequence | root
  public | incoming       | view     | root
  public | users          | table    | root
  public | users_id_seq   | sequence | root
  public | vacations      | view     | root
(8 rows)

vmail=# \d domains
                                  Table "public.domains"
  Column |          Type          |                      Modifiers
--------+------------------------+------------------------------------------------------
  id     | integer                | not null default nextval('domains_id_seq'::regclass)
  name   | character varying(200) |
Indexes:
     "domains_pkey" PRIMARY KEY, btree (id)

vmail=# \q


The test file I'm using to look at this through gst's (release version
3.2.3 on amd64 gentoo) eyes is:

Eval [

         | db users aliases domains incoming vacations |

         PackageLoader fileInPackages: #( 'ROE' 'DBD-PostgreSQL' 'DBI' ).

         db := DBI.Connection connect: 'DBI:PostgreSQL:dbname=vmail'
user: nil password: nil.

         "tables"
         users := db >> 'users'.
         aliases := db >> 'aliases'.
         domains := db >> 'domains'.
         "views"
         incoming := db >> 'incoming'.
         vacations := db >> 'vacations'.

         { users. aliases. incoming. vacations. domains } do: [ :each |
each inspect ].

         db close.

]

This file gives the following (slightly edited) output:

Loading package ROE
Loading package DBI
Loading package DBD-PostgreSQL
An instance of DBI.PostgreSQL.PGTable
   name: 'users'
   columnsArray: (DBI.PostgreSQL.PGTableColumnInfo(id integer(0) not
null) ... DBI.PostgreSQL.PGTableColumnInfo(quota character varying(20)) )
   contents: [ 5 RASimpleTuples ]
An instance of DBI.PostgreSQL.PGTable
   name: 'aliases'
   columnsArray: (DBI.PostgreSQL.PGTableColumnInfo(id integer(0) not
null) ... DBI.PostgreSQL.PGTableColumnInfo(vacationmsg text(0)) )
   contents: [ 5 RASimpleTuples ]
An instance of DBI.PostgreSQL.PGTable
   name: 'incoming'
   columnsArray: (DBI.PostgreSQL.PGTableColumnInfo(alias character
varying(200)) ... DBI.PostgreSQL.PGTableColumnInfo(quota character
varying(20)) )
   contents: [ 5 RASimpleTuples ]
An instance of DBI.PostgreSQL.PGTable
   name: 'vacations'
   columnsArray: (DBI.PostgreSQL.PGTableColumnInfo(alias character
varying(200)) ... DBI.PostgreSQL.PGTableColumnInfo(vacationmsg text(0)) )
   contents: [ 5 RASimpleTuples ]

Everything's fine up to this point, both tables and views are reported
as PGTable and contain the expected colums and rows, but the domains table
is giving gst trouble:

An instance of DBI.PostgreSQL.PGTable
   name: 'domains'
   attributes: nil
   connection: a PGConnection
   columns: nil
   columnsArray: (DBI.PostgreSQL.PGTableColumnInfo(domain_catalog
character varying(0)) DBI.PostgreSQL.PGTableColumnInfo(id integer(0) not
null) DBI.PostgreSQL.PGTableColumnInfo(name character varying(200))
DBI.PostgreSQL.PGTableColumnInfo(domain_schema character varying(0))
DBI.PostgreSQL.PGTableColumnInfo(domain_name character varying(0))
DBI.PostgreSQL.PGTableColumnInfo(data_type character varying(0))
DBI.PostgreSQL.PGTableColumnInfo(character_maximum_length integer(0))
DBI.PostgreSQL.PGTableColumnInfo(character_octet_length integer(0))
DBI.PostgreSQL.PGTableColumnInfo(character_set_catalog character
varying(0)) DBI.PostgreSQL.PGTableColumnInfo(character_set_schema
character varying(0))
DBI.PostgreSQL.PGTableColumnInfo(character_set_name character
varying(0)) DBI.PostgreSQL.PGTableColumnInfo(collation_catalog character
varying(0)) DBI.PostgreSQL.PGTableColumnInfo(collation_schema character
varying(0)) DBI.PostgreSQL.PGTableColumnInfo(collation_name character
varying(0)) DBI.PostgreSQL.PGTableColumnInfo(numeric_precision
integer(0)) DBI.PostgreSQL.PGTableColumnInfo(numeric_precision_radix
integer(0)) DBI.PostgreSQL.PGTableColumnInfo(numeric_scale integer(0))
DBI.PostgreSQL.PGTableColumnInfo(datetime_precision integer(0))
DBI.PostgreSQL.PGTableColumnInfo(interval_type character varying(0))
DBI.PostgreSQL.PGTableColumnInfo(interval_precision character
varying(0)) DBI.PostgreSQL.PGTableColumnInfo(domain_default character
varying(0)) DBI.PostgreSQL.PGTableColumnInfo(udt_catalog character
varying(0)) DBI.PostgreSQL.PGTableColumnInfo(udt_schema character
varying(0)) DBI.PostgreSQL.PGTableColumnInfo(udt_name character
varying(0)) DBI.PostgreSQL.PGTableColumnInfo(scope_catalog character
varying(0)) DBI.PostgreSQL.PGTableColumnInfo(scope_schema character
varying(0)) DBI.PostgreSQL.PGTableColumnInfo(scope_name character
varying(0)) DBI.PostgreSQL.PGTableColumnInfo(maximum_cardinality
integer(0)) DBI.PostgreSQL.PGTableColumnInfo(dtd_identifier character
varying(0)) )
   contents: [
Object: PGResultSet new "<0x7f63857479d0>" error: ERROR:  column
"domain_catalog" does not exist
LINE 1: SELECT "domain_catalog" AS c1, "id" AS c2, "name" AS c3, "do...
                ^

Error(Exception)>>signal (ExcHandling.st:254)
Error(Exception)>>signal: (ExcHandling.st:264)
DBI.PostgreSQL.PGResultSet(Object)>>error: (SysExcept.st:1415)
[] in DBI.PostgreSQL.PGResultSet>>checkStatusForSelect
(DBD-PostgreSQL.star#VFS.ZipFile/ResultSet.st:110)
False>>ifFalse: (False.st:72)
DBI.PostgreSQL.PGResultSet>>checkStatusForSelect
(DBD-PostgreSQL.star#VFS.ZipFile/ResultSet.st:101)
DBI.PostgreSQL.PGConnection>>select:
(DBD-PostgreSQL.star#VFS.ZipFile/Connection.st:133)
DBI.PostgreSQL.PGTable(DBI.Table)>>basicQuery:
(DBI.star#VFS.ZipFile/Table.st:49)
[] in DBI.PostgreSQL.PGTable(ROE.RASQLRelation)>>query:
(ROE.star#VFS.ZipFile/SQL.st:142)
[] in DBI.PostgreSQL.PGTable(ROE.RASQLRelation)>>logging:do:
(ROE.star#VFS.ZipFile/SQL.st:134)
Time class>>millisecondsToRun: (Time.st:272)
DBI.PostgreSQL.PGTable(ROE.RASQLRelation)>>logging:do:
(ROE.star#VFS.ZipFile/SQL.st:134)
DBI.PostgreSQL.PGTable(ROE.RASQLRelation)>>query:
(ROE.star#VFS.ZipFile/SQL.st:142)
DBI.PostgreSQL.PGTable(ROE.RASQLRelation)>>for:do:
(ROE.star#VFS.ZipFile/SQL.st:105)
DBI.PostgreSQL.PGTable(ROE.RARelation)>>do:
(ROE.star#VFS.ZipFile/Core.st:222)
DBI.PostgreSQL.PGTable(Collection)>>examineOn: (Collection.st:608)
DBI.PostgreSQL.PGTable(Object)>>inspect (Object.st:711)
optimized [] in UndefinedObject>>executeStatements (dbtest.st:17)
Array(SequenceableCollection)>>do: (SeqCollect.st:827)
UndefinedObject>>executeStatements (dbtest.st:19)

Looks like it's somehow getting one of postgresql's system tables
instead of my public.domains.

Am I missing something here?

Thanks,
s.

_______________________________________________
help-smalltalk mailing list
[hidden email]
http://lists.gnu.org/mailman/listinfo/help-smalltalk

On 01/24/2011 10:03 PM, Stefan Schmiedl wrote:
> Looks like it's somehow getting one of postgresql's system tables
> instead of my public.domains.

I suggest you start from the query in dbd-postgresql/Table.st:

select column_name, data_type, character_maximum_length,
              numeric_precision, numeric_precision_radix, numeric_scale,
              is_nullable, ordinal_position
              from information_schema.columns
              where table_name = 'domains' and table_catalog = 'vmail'
              order by ordinal_position

fix it :) and see what needs to be changed.

Paolo

_______________________________________________
help-smalltalk mailing list
[hidden email]
http://lists.gnu.org/mailman/listinfo/help-smalltalk

On Mon, 24 Jan 2011 22:32:16 +0100
Paolo Bonzini <[hidden email]> wrote:

I'm very curious to see where ROE (I guess) got the column
names from. I did not specify any and my domains table only
should have two.

So I think that I need to "start" a bit sooner in the process.

Rest assured that I will make noise while doing this.

s.

_______________________________________________
help-smalltalk mailing list
[hidden email]
http://lists.gnu.org/mailman/listinfo/help-smalltalk

Paolo Bonzini (24.01. 22:32):

heh ... now that I've actually looked at this query, I realise
that my previous email made absolutely no sense :-D

Apologies,

s.

_______________________________________________
help-smalltalk mailing list
[hidden email]
http://lists.gnu.org/mailman/listinfo/help-smalltalk

Paolo Bonzini (24.01. 22:32):

If I didn't mess up, this diff should be against the current (as of 5 min ago)
master branch of the git repo.

diff --git a/packages/dbd-postgresql/Table.st b/packages/dbd-postgresql/Table.st
index f892fae..14f3f83 100644
--- a/packages/dbd-postgresql/Table.st
+++ b/packages/dbd-postgresql/Table.st
@@ -44,15 +44,15 @@ Table subclass: PGTable [
        | query resultSet |
        columnsArray isNil ifTrue: [
            query := 'select column_name, data_type, character_maximum_length,
                numeric_precision, numeric_precision_radix, numeric_scale,
                is_nullable, ordinal_position
                from information_schema.columns
-               where table_name = %1 and table_catalog = %2
+               where table_schema = current_schema and table_name = %1 and table_catalog = %2
                order by ordinal_position' % {
                    self name printString. self connection database printString }.
            resultSet := self connection select: query.
            columnsArray := resultSet rows
                collect: [ :row | PGTableColumnInfo from: row ] ].
        ^columnsArray
     ]
 ]


Running this modified query shows only the two columns that I expect.

Fascinating stuff you can find in postgres. A bit non-obvious, but
fascinating :-)

Does anybody use different schemata in whatever kind of database application
they are building? Making this stuff work with multiple schemata would be
a nice job for somebody with some spare time.

Thanks,
s.

_______________________________________________
help-smalltalk mailing list
[hidden email]
http://lists.gnu.org/mailman/listinfo/help-smalltalk

On 01/25/2011 08:03 PM, Stefan Schmiedl wrote:
Thanks, applied!

Paolo

_______________________________________________
help-smalltalk mailing list
[hidden email]
http://lists.gnu.org/mailman/listinfo/help-smalltalk