[R-sig-DB] RPostgreSQL - dbExistsTable() is FALSE with schema names?

[Prasenjit Kapat]

On Mon, Apr 6, 2009 at 10:41 PM, Sean Davis <sdavis2 using mail.nih.gov> wrote:
>
>
> On Mon, Apr 6, 2009 at 9:23 PM, Prasenjit Kapat <kapatp using gmail.com> wrote:
>>
>> On Mon, Apr 6, 2009 at 8:01 PM, Sean Davis <sdavis2 using mail.nih.gov> wrote:
>> >
>> >
>> > On Mon, Apr 6, 2009 at 7:42 PM, Dirk Eddelbuettel <edd using debian.org>
>> > wrote:
>> >>
>> >> On 6 April 2009 at 19:18, Prasenjit Kapat wrote:
>> >> | Hi,
>> >> |
>> >> | dbExistsTable (conn, name="myschema.mytable") is FALSE, whereas
>> >> | dbExistsTable (conn, name="mytable") is TRUE ! 'mytable' is part of
>> >> | the 'myschema'.
>> >> |
>> >> | Is this as expected?
>> >>
>> >> Not sure. Check the Pg API doc of what is expected.
>>
>> Hmmm, I am not too familiar with postgres myself. I am trying to cover
>> as much as possible with RPostgreSQL.
>>
>> >> And in general, patches and debugging are welcome.  Sameer, who wrote
>> >> RPostgreSQL as part of last year's Google Summer of Code seems to have
>> >> lost
>> >> touch with the code.
>> >
>> > Looks like dbTableExists() method calls dbListTables() method, and that
>> > uses
>> > the SQL query:
>> >
>> > select tablename from pg_tables
>> > where schemaname !='information_schema'
>> > and schemaname !='pg_catalog'
>> >
>> > This returns the table name, but no schema name.  R code is then used to
>> > check for equality of strings.  This will lead to the incorrect behavior
>> > as
>> > noted.  I can provide a patch, probably, but it won't be immediate, so
>> > if
>> > someone else gets there first, just let us know.
>>
>> It looks like anything involving schema is buggy, probably the code
>> was not tested in such scenarios. For example:
>>
>> dbRemoveTable (con, name="myschema.mytable") is again FALSE and
>> dbRemoveTable (con, name="mytable") gives (which is expected as
>> 'public.mytable' is nonexistent):
>>
>> Error in postgresqlExecStatement(conn, statement, ...) :
>>  RS-DBI driver: (could not Retrieve the result : ERROR:  table
>> "mytable" does not exist
>> )
>> [1] FALSE
>>
>> I'll try to see if any proper solution exists.
>
> I was looking through the DBI docs and I didn't see a mention of schema,
> specifically.  I'm not very familiar with how other DB drivers deal with the
> issue of schema.  Is there a description of how DBI methods are meant to
> deal with schema, or is it up to the implementation to deal with the issue
> via the ... arguments?

Hopefully someone who knows the details can help on this.

Back here, the following is a quick-n-dirty solution (not a patch of course):

dbExistsTable <- function (con, name, ...)
{
  as.logical (
   dim (
    dbGetQuery (con,
     paste ("select schemaname,tablename from pg_tables where schemaname='",
      rev(strsplit(name, ".", fixed=TRUE)[[1]])[2],
      "' and tablename='",
      rev(strsplit(name, ".", fixed=TRUE)[[1]])[1], "'", sep="")
  )) [1])
}

dbRemoveTable <- function (con, name, ..., cascade=FALSE)
{
  if (dbExistsTable (con, name)) {
    dbGetQuery (con, paste ("drop table ", name, ifelse (cascade, "
cascade", ""), ";", sep=""))
  }
}

-- 
Prasenjit