1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
|
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>SQL support for GRASS</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<!-- This file is lib/db/sqlp/description.html -->
<body bgcolor="white">
<H2>DESCRIPTION</H2>
SQL support for GRASS.
<P>
Supported is a limited set of SQL functions and operators.
<P>
<H2>FUNCTIONS</H2>
<PRE>
ALTER TABLE table ADD [COLUMN] columndef
CREATE TABLE table ( columndefs )
DROP TABLE table
SELECT columns FROM table
SELECT columns FROM table WHERE condition
DELETE FROM table
DELETE FROM table WHERE condition
INSERT INTO table values
INSERT INTO table ( columns ) values
UPDATE table SET assignments
UPDATE table SET assignments WHERE condition
</PRE>
<H2>OPERATORS</H2>
<PRE>
"=" : equal
"<" : smaller than
"<=" : smaller/equal than
">" : larger than
">=" : larger/equal than
"<>" : not equal
"~" : Substring matching
</PRE>
<H2>NOTES</H2>
<ul>
<li> SQL does not support '.' (dots) in table names.
<li> Supported table name characters are only:<br>
[A-Za-z][A-Za-z0-9_]*
<li> A table name must start with a character, not a number.
<li> Brackets are needed, e.g. for<br>
<pre>
select * from some_table where (3*(some_field - another_field) > 5)
</pre>
The parser does not know about precedence yet, so the sub-clause has to
be in brackets to avoid ambiguity.
<li> Text-string matching requires the text part to be 'single quoted' and
multiple queries should be contained in "double quotes". e.g.<br>
<pre>
d.vect map where="individual='juvenile' and area='beach'"
</pre>
<li> An error message such as "dbmi: Protocol error" either indicates an invalid column name
or an unsupported column type (then the GRASS SQL parser needs to be extended).
<li> DBF column names are limited to 10 characters (DBF API definition)
</ul>
<h2>EXAMPLES</h2>
Display all vector points except for LAMAR valley and <i>extensive trapping</i>:
<pre>
d.vect trapping_sites_points fcol=black icon=basic/diamond col=white size=13 \
where="valley <> 'LAMAR' OR (valley = 'LAMAR' AND description = 'extensive trapping')"
</pre>
<p>
Select all attributes from table where str1 column values are not 'No Name':
<pre>
echo "SELECT * FROM archsites WHERE str1 <> 'No Name'" | db.select
</pre>
<h2>SEE ALSO</h2>
<em>
<a HREF="db.execute.html">db.execute</a>,
<a HREF="db.select.html">db.select</a>,
<a HREF="db.tables.html">db.tables</a>,
<a HREF="d.vect.html">d.vect</a><BR>
<a HREF="http://grass.itc.it/grass57/tutorial/links.html">SQL reference links</a>
</em>
<p><i>Last changed: $Date: 2005/05/10 09:58:07 $</i>
<HR>
<BR><a href=index.html>Help Index</a>
</body></html>
|
