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

[mlennert]

On 11/04/07 13:48, Jarosław Jasiewicz wrote:

Moritz Lennert napisał(a):

On 11/04/07 13:01, Jarosław Jasiewicz wrote:

Hi

Is still possible to use pgSQL views to connect vectors?

Connecting views was possible for or five month ago (in spite of error messages during creating views).

I have some vector joinred to view They still working. But now, when I tried to connect new vector with view I recived simple error message that there is no table I plan to join

What is the exact error message you are receiving.

If that possibilites was removed (after my post, unfortunatly) please let me know

I am not aware of any related changes...

Sorry, I spoke to fast. I think I now found the culprit:

revision 1.40
date: 2006/11/28 08:42:02; author: markus; state: Exp; lines: +1 -1
if table doesn't exist: fatal error

Before it was only a warning, not a fatal error, and so the connection was established and worked.

This undoes what Radim did two years earlier:

revision 1.24
date: 2004/11/25 13:15:47; author: radim; state: Exp; lines: +2 -2
error -> warning if table does not exist

Markus, what was the reason that you changed this ?

Either we have to revert this again, or we have to find a way to check for views as well as tables, which means either implementing db_view_exists() (with all the individual implementations, or just modify the list_tables functions of the individual drivers to include views. In the PostgreSQL driver, the statement used is ( ):

"select * from pg_tables where tablename !~ 'pg_*' order by tablename"

For views this would have to be something like:

SELECT viewname FROM pg_views WHERE schemaname NOT IN ('pg_catalog','information_schema') AND viewname !~ '^pg_';

So the results of these two calls would have to be combined before db__driver_list_tables() returns the results.

I don't know if there are any good reasons not to just include views into the list of tables...

In the meantime, you can just modify v.db.connect at line 231 from

         G_fatal_error(_("Table <%s> does not exist in database <%s>"),dbtable->answer, dbdatabase->answer);

to

         G_warning(_("Table <%s> does not exist in database <%s>"),dbtable->answer, dbdatabase->answer);

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.

Moritz

[Markus_Neteler1]

On Wed, Apr 11, 2007 at 05:25:24PM +0200, Moritz Lennert wrote:

On 11/04/07 13:48, Jaros??aw Jasiewicz wrote:
>Moritz Lennert napisa??(a):
>>On 11/04/07 13:01, Jaros??aw Jasiewicz wrote:
>>>Hi
>>>
>>>Is still possible to use pgSQL views to connect vectors?
>>>
>>>Connecting views was possible for or five month ago (in spite of
>>>error messages during creating views).
>>>
>>>I have some vector joinred to view They still working. But now, when
>>>I tried to connect new vector with view I recived simple error
>>>message that there is no table I plan to join
>>
>>What is the exact error message you are receiving.
>>
>>
>>>
>>>If that possibilites was removed (after my post, unfortunatly) please
>>>let me know
>>
>>I am not aware of any related changes...

Sorry, I spoke to fast. I think I now found the culprit:

revision 1.40
date: 2006/11/28 08:42:02; author: markus; state: Exp; lines: +1 -1
if table doesn't exist: fatal error

Which file is that?
Ah, below I see that you mean v.db.connect/main.c.

Before it was only a warning, not a fatal error, and so the connection
was established and worked.

This undoes what Radim did two years earlier:

revision 1.24
date: 2004/11/25 13:15:47; author: radim; state: Exp; lines: +2 -2
error -> warning if table does not exist

Markus, what was the reason that you changed this ?

I don't really remember but I think that it broke scripts which
use v.db.connect.
At least: if the source code doesn't contain a comment to keep
special tricks it's likely that they get lost.

Either we have to revert this again, or we have to find a way to check
for views as well as tables, which means either implementing
db_view_exists() (with all the individual implementations, or just
modify the list_tables functions of the individual drivers to include
views. In the PostgreSQL driver, the statement used is ( ):

"select * from pg_tables where tablename !~ 'pg_*' order by tablename"

For views this would have to be something like:

SELECT viewname FROM pg_views WHERE schemaname NOT IN
('pg_catalog','information_schema') AND viewname !~ '^pg_';

So the results of these two calls would have to be combined before
db__driver_list_tables() returns the results.

I don't know if there are any good reasons not to just include views
into the list of tables...

I have no suggestion here how to solve the problem. But yes,
views should be visible somehow.

In the meantime, you can just modify v.db.connect at line 231 from

        G_fatal_error(_("Table <%s> does not exist in database
<%s>"),dbtable->answer, dbdatabase->answer);

to

        G_warning(_("Table <%s> does not exist in database
<%s>"),dbtable->answer, dbdatabase->answer);

>
>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?

Markus

[mlennert]

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:

Moritz Lennert napisa??(a):

On 11/04/07 13:01, Jaros??aw Jasiewicz wrote:

Hi

Is still possible to use pgSQL views to connect vectors?

Connecting views was possible for or five month ago (in spite of error messages during creating views).

I have some vector joinred to view They still working. But now, when I tried to connect new vector with view I recived simple error message that there is no table I plan to join

What is the exact error message you are receiving.

If that possibilites was removed (after my post, unfortunatly) please let me know

I am not aware of any related changes...

Sorry, I spoke to fast. I think I now found the culprit:

revision 1.40
date: 2006/11/28 08:42:02; author: markus; state: Exp; lines: +1 -1
if table doesn't exist: fatal error

Which file is that?
Ah, below I see that you mean v.db.connect/main.c.

Yes, sorry.

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.

Moritz

[mlennert]

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.

Moritz

(attachments)

listtab.diff (3.47 KB)

[mlennert]

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

[mlennert]

On 13/04/07 12:50, Jarosław Jasiewicz wrote:

Thanks a lot for your effort. I just updated my grass source base on your diff file (only for pg) now I canged files you send me. All seems to be OK now
As I understand it will be included in cvs soon?

If no one objects (to listing views together with tables), I can commit, yes

BTW
I'm not experienced in grass (I work with that system for year now) but I has worked as an database administaor (on MSSQL Server) for several years and with arcInfo too. Grass, due to open architecture has much more posibilites on database managment than most closed systems.

You might be interested in a little proof of concept reimplementation I did of d.vect.chart (attached - also see http://grass.itc.it/pipermail/grass5/2006-October/026504.html).

This allows you to enter any sql query to chose the data you want to display, thus giving you the freedom to skip view creation and query many different tables at once directly. This obviusly implies a much greater responsibility of the user to make sure the query returns what is needed.

I think it would be interesting to think about whether such approach is something to take further in GRASS.

Moritz

(attachments)

d.vect.chart.sql.tgz (6.34 KB)