1.. _rfc-21:
2
3================================================================================
4RFC 21: OGR SQL type cast and field name alias
5================================================================================
6
7Author: Tamas Szekeres
8
9Contact: szekerest@gmail.com
10
11Status: Adopted
12
13Summary
14-------
15
16This proposal provides support for changing the column name and the
17column type in the OGR SQL select list.
18
19The main motivation of this change is to provide better control when
20transferring the OGR_STYLE special field from each data source to the
21other. For example we can use ogr2ogr for this purpose using the
22following command line:
23
24::
25
26   ogr2ogr -f "ESRI Shapefile" -sql "select *, OGR_STYLE from rivers" rivers.shp rivers.tab
27
28The shape driver will truncate the OGR_STYLE field to 80 characters by
29default in length that may not be enough to hold the actual value. So as
30to fix this issue we might want to specify the desired length in the
31select list, like:
32
33::
34
35   ogr2ogr -f "ESRI Shapefile" -sql "select *, CAST(OGR_STYLE AS character(255)) from rivers" rivers.shp rivers.tab
36
37In some cases it would also be useful to change the name of the field in
38the target data source:
39
40::
41
42   ogr2ogr -f "ESRI Shapefile" -sql "select *, CAST(OGR_STYLE AS character(255)) AS 'STYLE' from rivers" rivers.shp rivers.tab
43
44Main concepts
45-------------
46
47To support these new features we will extend the current OGR SQL syntax.
48The proposed addition will keep the syntax conform to the SQL92
49specification:
50
51::
52
53   SELECT <field-list> FROM <table_def>
54        [LEFT JOIN <table_def>
55         ON [<table_ref>.]<key_field> = [<table_ref>.].<key_field>]*
56        [WHERE <where-expr>]
57        [ORDER BY <sort specification list>]
58
59   <field-list> ::= <column-spec> [ { , <column-spec> }... ]
60
61   <column-spec> ::= <field-spec> [ <as clause> ]
62                    | CAST ( <field-spec> AS <data type> ) [ <as clause> ]
63
64   <field-spec> ::= [DISTINCT] <field_ref>
65                    | <field_func> ( [DISTINCT] <field-ref> )
66                    | Count(*)
67
68   <as clause> ::= [ AS ] <column_name>
69
70   <data type> ::= character [ ( field_length ) ]
71                   | float [ ( field_length ) ]
72                   | numeric [ ( field_length [, field_precision ] ) ]
73                   | integer [ ( field_length ) ]
74                   | date [ ( field_length ) ]
75                   | time [ ( field_length ) ]
76                   | timestamp [ ( field_length ) ]
77
78   <field-func> ::= AVG | MAX | MIN | SUM | COUNT
79
80   <field_ref>  ::= [<table_ref>.]field_name
81
82   <sort specification list> ::=
83                 <sort specification> [ { <comma> <sort specification> }... ]
84
85   <sort specification> ::= <sort key> [ <ordering specification> ]
86
87   <sort key> ::=  <field_ref>
88
89   <ordering specification> ::= ASC | DESC
90
91   <table_def> ::= ['<datasource name>'.]table_name [table_alias]
92
93   <table_ref> ::= table_name | table_alias
94
95This RFC doesn't address implementing conversion to the 'integer list',
96'double list' and 'string list' OGR data types, which doesn't conform to
97the SQL92 specification and the necessary conversion routines are
98missing in the OGR code.
99
100Implementation
101--------------
102
103To implement the addition the following changes should be made in the
104OGR codebase:
105
1061. In ogr_swq.h I'll have to add to 4 fields to swq_col_def to hold the
107   field_alias the target_type, the field_length, and field_precision
108
1092. In ogr_swq.h SWQ_DATE, SWQ_TIME, SWQ_TIMESTAMP will be added to
110   swq_field_type enum.
111
1123. In swq.c I'll have to change swq_select_preparse to take care of the
113   field alias and the CAST specification.
114
1154. A new function (swq_parse_typename) will be added to parse the
116   supported typenames.
117
1185. In ogr_gensql.cpp the .ctor of OGRGenSQLResultsLayer will be changed
119   to set up the field name and the field length to the target data
120   source
121
1226. In ogr_gensql.cpp TranslateFeature will be modified to take care of
123   the type change if specified.
124
125Backward Compatibility
126----------------------
127
128The backward compatibility for the current SQL syntax will be retained.
129This addition doesn't require changes in the OGR C and SWIG API.
130
131Documentation
132-------------
133
134The OGR SQL document will be updated to reflect the new features. I'll
135take care of updating the OGR Style Documentation with the support of
136transferring the styles between the data sources.
137
138Implementation Staffing
139-----------------------
140
141Tamas Szekeres will implement the RFC in the development version.
142
143Frank Warmerdam will implement the regression test scripts according to
144this new functionality.
145
146References
147----------
148
149-  Tracking bug for this feature (containing all of the proposed code
150   changes): #2171
151
152Voting History
153--------------
154
155Frank Warmerdam +1
156
157Daniel Morissette +1
158
159Howard Butler +1
160
161Even Rouault +1
162
163Tamas Szekeres +1
164
165Andrey Kiselev +0
166