[GRASS-dev] Re: [GRASS-user] Views in pgSQL

[Moritz Lennert]

On 12/04/07 16:59, Moritz Lennert wrote:
> On 11/04/07 18:15, Moritz Lennert wrote:
>> On 11/04/07 17:37, Markus Neteler wrote:
>>> On Wed, Apr 11, 2007 at 05:25:24PM +0200, Moritz Lennert wrote:
>>>> On 11/04/07 13:48, Jaros??aw Jasiewicz wrote:
> 
>>>>> BTW:
>>>>> Pg Views are very important for me when I connect-reconnect vector 
>>>>> files to different atribute table. I use (used!) views insted of 
>>>>> original table to avoid occasional removing original data and to 
>>>>> speed up transfer limiting data in views only to these needed for
>>>>> (sorry, I think everybody knows it)
>>>> I completely agree with you. Being able to connect maps to views is 
>>>> fundamental in my eyes.
>>>
>>> Yes. But we need a reasonable implementation.
>>> Anyone with suggestions how to implement it?
>>
>> For me the choice is either
>>
>> 1) to consider views as tables (and thus change the way the individual 
>> drivers list tables) or
>>
>> 2) to treat them as separate and change v.db.connect so that it checks 
>> for both.
>>
>> 1) seems much easier to implement, 2) seems cleaner in case we need to 
>> list tables only, not views, in other contexts than v.db.connect.
>>
> 
> As I don't see any reason why 1) should be a problem in the context of 
> GRASS, I've had done a quick implementation of this for the pg driver 
> (diff attached). For me it seems to work.
> 
> Anyone opposed to committing this ?
> 
> For sqlite and mysql, I will have to do some more reading to learn how 
> to get the relevant info (i.e. list of user-relevant views). If someone 
> has some knowledge about this, I would be grateful.

Actually sqlite was very easy:

Index: listtab.c
===================================================================
RCS file: /grassrepository/grass6/db/drivers/sqlite/listtab.c,v
retrieving revision 1.3
diff -u -r1.3 listtab.c
--- listtab.c   9 Feb 2006 03:08:49 -0000       1.3
+++ listtab.c   12 Apr 2007 15:10:36 -0000
@@ -29,7 +29,7 @@
      init_error();

      ret = sqlite3_prepare ( sqlite,
-       "select name from sqlite_master where type = 'table'",
+       "select name from sqlite_master where type = 'table' or type = 
'view'",
          -1, &statement, &rest );

      if ( ret != SQLITE_OK ) {


In mysql, views are only supported since version 5.
The GRASS mysql driver uses the function mysql_list_tables() to get the 
table names. I don't see an equivalent of that for views. Will search a 
bit more.

Moritz