Hi,
concerning 5.1 I were glad to receive some recommendations
to following problem. GRASS 5.1 is using SQL internally
which is rather restricted with table names
Supported table name characters are:
[A-Za-z][A-Za-z0-9_]*
means:
- must start with character
- dots etc. are forbidden
While the map names due to G_legal_filename() are quite
flexible, the following must fail (note the dot in the out name):
v.extract wdbtemp4 output=wdbtemp4.areas type=area list=1-10000
Copying tables ...
DBMI-DBF driver error: SQL parser error in statement:
create table wdbtemp4.areas ( cat integer, label varchar(40))
Error in db_execute_immediate()
WARNING: Cannot create new table: 'create table wdbtemp4.areas ( cat
integer, label varchar(40))'
WARNING: Cannot copy table
Question is if we want to restrict the map names completely for
GRASS to the SQL rules or find a better workaround...
If course it is convenient to have geometry and table with the same
name...
How do other GIS with SQL support manage this problem?
Suggestions are welcome.
Markus
Markus Neteler wrote:
concerning 5.1 I were glad to receive some recommendations
to following problem. GRASS 5.1 is using SQL internally
which is rather restricted with table names
Supported table name characters are:
[A-Za-z][A-Za-z0-9_]*
means:
- must start with character
- dots etc. are forbidden
While the map names due to G_legal_filename() are quite
flexible, the following must fail (note the dot in the out name):
v.extract wdbtemp4 output=wdbtemp4.areas type=area list=1-10000
Question is if we want to restrict the map names completely for
GRASS to the SQL rules or find a better workaround...
It depends upon how SQL is being used. If the tables are only
temporary, then the table names don't need to correpond to the map
names; e.g. they could be dynamically generated.
If they are persistent, it would be better if they were at least
similar to the map name. However, I would suggest trying to find an
alternative to restricting map names; the use of dots (and possibly
other characters) in map names is well established.
Possible alternatives would include:
a) storing the table name(s) in a file associated with the map (i.e.
as a map component), or
b) an SQL table which contains the mapping between table names and map
names.
--
Glynn Clements <glynn.clements@virgin.net>
Markus Neteler wrote:
> concerning 5.1 I were glad to receive some recommendations
> to following problem. GRASS 5.1 is using SQL internally
> which is rather restricted with table names
>
> Supported table name characters are:
> [A-Za-z][A-Za-z0-9_]*
>
> means:
> - must start with character
> - dots etc. are forbidden
>
> While the map names due to G_legal_filename() are quite
> flexible, the following must fail (note the dot in the out name):
>
> v.extract wdbtemp4 output=wdbtemp4.areas type=area list=1-10000
> Question is if we want to restrict the map names completely for
> GRASS to the SQL rules or find a better workaround...
I have gotton around this problem in programming URL's by
translating the forbidden characters to a sequence of
allowed characters, and translating the sequence back where necessary.
Thus the user would be allowed to user wdbtemp4.area but if that had to
be used as a table name it might be wdbtemp4#-#areas.
What would be necessary is a pair of routines that encode/decode
the table names taking care of any forbidden characters.
--
John Preston <jpreston@uwimona.edu.jm>
On Monday 17 February 2003 09:12 pm, Glynn Clements wrote:
> Question is if we want to restrict the map names completely for
> GRASS to the SQL rules or find a better workaround...
It depends upon how SQL is being used. If the tables are only
temporary, then the table names don't need to correpond to the map
names; e.g. they could be dynamically generated.
If they are persistent, it would be better if they were at least
similar to the map name. However, I would suggest trying to find an
alternative to restricting map names; the use of dots (and possibly
other characters) in map names is well established.
Tables are persistent. We can look at table as part of the vector
which is not stored (usually) in vector directory but in RDBMS (usually).
I also like dots in map names, but I don't see any reasonable solution.
(BTW: also '-' is not allowed)
Possible alternatives would include:
a) storing the table name(s) in a file associated with the map (i.e.
as a map component), or
Table names ARE stored in file (<mapset>/vector/<name>/dbln)
b) an SQL table which contains the mapping between table names and map
names.
Currently the table name generated by module is the same as vector name.
Additional option for table name for each module is not practical.
We could generate the name from vector name and replace problematic
characters, but then everything becomes very confusing for users when
they want to access data in table through some other client
(psql, isql, OpenOffice, ...), which I thing, is quite usual.
Radim
On Monday 17 February 2003 11:25 pm, John A. Preston wrote:
> > Question is if we want to restrict the map names completely for
> > GRASS to the SQL rules or find a better workaround...
I have gotton around this problem in programming URL's by
translating the forbidden characters to a sequence of
allowed characters, and translating the sequence back where necessary.
Thus the user would be allowed to user wdbtemp4.area but if that had to
be used as a table name it might be wdbtemp4#-#areas.
What would be necessary is a pair of routines that encode/decode
the table names taking care of any forbidden characters.
Yes, that could be possible solution, but then you get different name
for table and vector. Problem is that to access data outside GRASS
(OOffice, pgaccess,...), user must have encode/decode routines in his head,
which doesn't seem to be user friendly enough.
Radim
On Monday 17 February 2003 11:25 pm, John A. Preston wrote:
> > > Question is if we want to restrict the map names completely for
> > > GRASS to the SQL rules or find a better workaround...
>
> I have gotton around this problem in programming URL's by
> translating the forbidden characters to a sequence of
> allowed characters, and translating the sequence back where necessary.
> Thus the user would be allowed to user wdbtemp4.area but if that had to
> be used as a table name it might be wdbtemp4#-#areas.
>
> What would be necessary is a pair of routines that encode/decode
> the table names taking care of any forbidden characters.
Yes, that could be possible solution, but then you get different name
for table and vector. Problem is that to access data outside GRASS
(OOffice, pgaccess,...), user must have encode/decode routines in his head,
which doesn't seem to be user friendly enough.
Yes, that's true. But you could (hopefully) provide some simple macros or SQL functions
that do the encoding/decoding for external access. Or maybe a simple
GRASS SQL table import/export function.
I think it is necessary to have all the possibilities available for table names.
--
John Preston <jpreston@uwimona.edu.jm>
John A. Preston wrote:
> Yes, that could be possible solution, but then you get different name
> for table and vector. Problem is that to access data outside GRASS
> (OOffice, pgaccess,...), user must have encode/decode routines in his head,
> which doesn't seem to be user friendly enough.
Yes, that's true. But you could (hopefully) provide some simple macros
or SQL functions that do the encoding/decoding for external access. Or
maybe a simple GRASS SQL table import/export function.
I think it is necessary to have all the possibilities available for
table names.
Agreed. As things stand, "foo.bar" is a valid map name; if e.g.
"v.extract ... output=foo.bar" fails, that is a bug in v.extract.
Simply changing the definition of a valid map name to match SQL syntax
is overkill (and seriously incompatible with previous versions). And
having different definitions of a valid map name depending upon
whether the module uses SQL just isn't acceptable.
A corollary of the above is that there has to exist some mechanism for
mapping map names to table names, and that mechanism can't be the
identity function.
BTW, any mechanism which results in collisions (e.g. converting all
non-alphanumeric characters to an underscore) isn't really acceptable
either[1].
[1] Having said that, I doubt that GRASS handles case correctly. If
map names are meant to be case-sensitive, using the map name as a
filename won't work on Windows. If they are meant to be
case-insensitive, this fact is bound to have been overlooked
somewhere.
--
Glynn Clements <glynn.clements@virgin.net>