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