BES - Modules - SQL Hander: Difference between revisions
Line 5: | Line 5: | ||
=== Mappings between the ODBC data types and DAP2 data types === | === Mappings between the ODBC data types and DAP2 data types === | ||
The SQL Handler maps the datatypes defined by SQL into types defined by DAP. In most cases the mapping is obvious. Here we document each of the supported SQL types and their corresponding DAP type. Note that any types not listed here causes a runtime fatal error. That is, if you include in the ''[select]'' part of the dataset file the name of a column with an unsupported data type, the handler will return an error saying ''SQL Handler: The datatype read from the Data Source is not supported. The problem type code is: <type code>''. | |||
{| | |||
|+ The Mapping between SQL and DAP datatypes | |||
! SQL Type | |||
! DAP Type | |||
! Comments | |||
|- | |||
| SQL_C_CHAR || Str || | |||
|- | |||
| SQL_C_SLONG, SQL_C_LONG || Int32 || | |||
|- | |||
| SQL_C_SHORT|| Int16 || | |||
|- | |||
| SQL_C_FLOAT || Float32 || | |||
|- | |||
| SQL_C_DOUBLE || Float64 || | |||
|- | |||
| SQL_C_NUMERIC || Int32 || | |||
Ttrue); | |||
//#define SQL_C_NUMERIC SQL_NUMERIC | |||
case SQL_C_NUMERIC: | |||
return new SQLSimpleType<SQL_TYPE,ODBC_TYPE,Int32>(getConnector(),&cast_func,true); | |||
//#define SQL_C_DEFAULT 99 | |||
case SQL_C_DEFAULT: | |||
return new SQLSimpleType<SQL_TYPE,ODBC_TYPE,Str,string>(getConnector(),&cast_string,false); | |||
#if 0 | |||
//#define SQL_SIGNED_OFFSET (-20) | |||
//#define SQL_UNSIGNED_OFFSET (-22) | |||
//#define SQL_C_DATE SQL_DATE | |||
#endif | |||
case SQL_C_DATE: | |||
return new SQLSimpleType<SQL_TYPE,ODBC_TYPE,Str>(getConnector(),&cast_func,true); | |||
//#define SQL_C_TIME SQL_TIME | |||
case SQL_C_TIME: | |||
return new SQLSimpleType<SQL_TYPE,ODBC_TYPE,Str>(getConnector(),&cast_func,true); | |||
//#define SQL_C_TIMESTAMP SQL_TIMESTAMP | |||
case SQL_C_TIMESTAMP: | |||
return new SQLSimpleType<SQL_TYPE,ODBC_TYPE,Str>(getConnector(),&cast_func,true); | |||
//#define SQL_C_TYPE_DATE SQL_TYPE_DATE | |||
case SQL_C_TYPE_DATE: | |||
return new SQLSimpleType<SQL_TYPE,ODBC_TYPE,Str>(getConnector(),&cast_func,true); | |||
//#define SQL_C_TYPE_TIME SQL_TYPE_TIME | |||
case SQL_C_TYPE_TIME: | |||
return new SQLSimpleType<SQL_TYPE,ODBC_TYPE,Str>(getConnector(),&cast_func,true); | |||
//#define SQL_C_TYPE_TIMESTAMP SQL_TYPE_TIMESTAMP | |||
case SQL_C_TYPE_TIMESTAMP: | |||
return new SQLSimpleType<SQL_TYPE,ODBC_TYPE,Str>(getConnector(),&cast_func,true); | |||
#if 0 | |||
//#define SQL_C_INTERVAL_YEAR SQL_INTERVAL_YEAR | |||
//#define SQL_C_INTERVAL_MONTH SQL_INTERVAL_MONTH | |||
//#define SQL_C_INTERVAL_DAY SQL_INTERVAL_DAY | |||
//#define SQL_C_INTERVAL_HOUR SQL_INTERVAL_HOUR | |||
//#define SQL_C_INTERVAL_MINUTE SQL_INTERVAL_MINUTE | |||
//#define SQL_C_INTERVAL_SECOND SQL_INTERVAL_SECOND | |||
//#define SQL_C_INTERVAL_YEAR_TO_MONTH SQL_INTERVAL_YEAR_TO_MONTH | |||
//#define SQL_C_INTERVAL_DAY_TO_HOUR SQL_INTERVAL_DAY_TO_HOUR | |||
//#define SQL_C_INTERVAL_DAY_TO_MINUTE SQL_INTERVAL_DAY_TO_MINUTE | |||
//#define SQL_C_INTERVAL_DAY_TO_SECOND SQL_INTERVAL_DAY_TO_SECOND | |||
//#define SQL_C_INTERVAL_HOUR_TO_MINUTE SQL_INTERVAL_HOUR_TO_MINUTE | |||
//#define SQL_C_INTERVAL_HOUR_TO_SECOND SQL_INTERVAL_HOUR_TO_SECOND | |||
//#define SQL_C_INTERVAL_MINUTE_TO_SECOND SQL_INTERVAL_MINUTE_TO_SECOND | |||
//#define SQL_C_BINARY SQL_BINARY | |||
#endif | |||
case SQL_C_BINARY: | |||
return new SQLSimpleType<SQL_TYPE,ODBC_TYPE,Int16>(getConnector(),&cast_func,true); | |||
//#define SQL_C_BIT SQL_BIT | |||
case SQL_C_BIT: | |||
return new SQLSimpleType<SQL_TYPE,ODBC_TYPE,Int16>(getConnector(),&cast_func,true); | |||
//#define SQL_C_SBIGINT (SQL_BIGINT+SQL_SIGNED_OFFSET) | |||
case SQL_C_SBIGINT: | |||
return new SQLSimpleType<SQL_TYPE,ODBC_TYPE,Int32>(getConnector(),&cast_func,true); | |||
//#define SQL_C_UBIGINT (SQL_BIGINT+SQL_UNSIGNED_OFFSET) | |||
case SQL_C_UBIGINT: | |||
return new SQLSimpleType<SQL_TYPE,ODBC_TYPE,Int32>(getConnector(),&cast_func,true); | |||
//#define SQL_C_TINYINT SQL_TINYINT | |||
case SQL_C_TINYINT: | |||
return new SQLSimpleType<SQL_TYPE,ODBC_TYPE,Int16>(getConnector(),&cast_func,true); | |||
//#define SQL_C_SSHORT (SQL_C_SHORT+SQL_SIGNED_OFFSET) | |||
case SQL_C_SSHORT: | |||
return new SQLSimpleType<SQL_TYPE,ODBC_TYPE,Int16>(getConnector(),&cast_func,true); | |||
//#define SQL_C_STINYINT (SQL_TINYINT+SQL_SIGNED_OFFSET) | |||
case SQL_C_STINYINT: | |||
return new SQLSimpleType<SQL_TYPE,ODBC_TYPE,Int16>(getConnector(),&cast_func,true); | |||
//#define SQL_C_ULONG (SQL_C_LONG+SQL_UNSIGNED_OFFSET) | |||
case SQL_C_ULONG: | |||
return new SQLSimpleType<SQL_TYPE,ODBC_TYPE,Int32>(getConnector(),&cast_func,true); | |||
//#define SQL_C_USHORT (SQL_C_SHORT+SQL_UNSIGNED_OFFSET) | |||
case SQL_C_USHORT: | |||
return new SQLSimpleType<SQL_TYPE,ODBC_TYPE,Int32>(getConnector(),&cast_func,true); | |||
//#define SQL_C_UTINYINT (SQL_TINYINT+SQL_UNSIGNED_OFFSET) | |||
case SQL_C_UTINYINT: | |||
return new SQLSimpleType<SQL_TYPE,ODBC_TYPE,Int32>(getConnector(),&cast_func,true); | |||
default: | |||
=== Known problems === | === Known problems === |
Revision as of 18:14, 13 September 2012
Kinds of files the handler will serve
This handler will serve data stored in a relational database if that database is configured to be accessed using ODBC. The handler has been tested using both the unixODBC and iODBC driver managers on Linux and OS/X, respectively. While our testing has been limited to the MySQL and Postgres database servers, the handler contains is not specific to either of those severs; it should work with any database that can be accessed using ODBC.
The handler can be configured to combine information from several tables and provide access to it as a single dataset, including performing the full range of SQL operations. At the same time, the SQL database server is never exposed to the web using this handler, so the database contents are safe.
Mappings between the ODBC data types and DAP2 data types
The SQL Handler maps the datatypes defined by SQL into types defined by DAP. In most cases the mapping is obvious. Here we document each of the supported SQL types and their corresponding DAP type. Note that any types not listed here causes a runtime fatal error. That is, if you include in the [select] part of the dataset file the name of a column with an unsupported data type, the handler will return an error saying SQL Handler: The datatype read from the Data Source is not supported. The problem type code is: <type code>.
SQL Type | DAP Type | Comments |
---|---|---|
SQL_C_CHAR | Str | |
SQL_C_SLONG, SQL_C_LONG | Int32 | |
SQL_C_SHORT | Int16 | |
SQL_C_FLOAT | Float32 | |
SQL_C_DOUBLE | Float64 | |
SQL_C_NUMERIC | Int32 |
//#define SQL_SIGNED_OFFSET (-20) //#define SQL_UNSIGNED_OFFSET (-22) //#define SQL_C_DATE SQL_DATE
case SQL_C_DATE: return new SQLSimpleType<SQL_TYPE,ODBC_TYPE,Str>(getConnector(),&cast_func,true); //#define SQL_C_TIME SQL_TIME case SQL_C_TIME: return new SQLSimpleType<SQL_TYPE,ODBC_TYPE,Str>(getConnector(),&cast_func,true); //#define SQL_C_TIMESTAMP SQL_TIMESTAMP case SQL_C_TIMESTAMP: return new SQLSimpleType<SQL_TYPE,ODBC_TYPE,Str>(getConnector(),&cast_func,true); //#define SQL_C_TYPE_DATE SQL_TYPE_DATE case SQL_C_TYPE_DATE: return new SQLSimpleType<SQL_TYPE,ODBC_TYPE,Str>(getConnector(),&cast_func,true); //#define SQL_C_TYPE_TIME SQL_TYPE_TIME case SQL_C_TYPE_TIME: return new SQLSimpleType<SQL_TYPE,ODBC_TYPE,Str>(getConnector(),&cast_func,true); //#define SQL_C_TYPE_TIMESTAMP SQL_TYPE_TIMESTAMP case SQL_C_TYPE_TIMESTAMP: return new SQLSimpleType<SQL_TYPE,ODBC_TYPE,Str>(getConnector(),&cast_func,true);
//#define SQL_C_INTERVAL_YEAR SQL_INTERVAL_YEAR //#define SQL_C_INTERVAL_MONTH SQL_INTERVAL_MONTH //#define SQL_C_INTERVAL_DAY SQL_INTERVAL_DAY //#define SQL_C_INTERVAL_HOUR SQL_INTERVAL_HOUR //#define SQL_C_INTERVAL_MINUTE SQL_INTERVAL_MINUTE //#define SQL_C_INTERVAL_SECOND SQL_INTERVAL_SECOND //#define SQL_C_INTERVAL_YEAR_TO_MONTH SQL_INTERVAL_YEAR_TO_MONTH //#define SQL_C_INTERVAL_DAY_TO_HOUR SQL_INTERVAL_DAY_TO_HOUR //#define SQL_C_INTERVAL_DAY_TO_MINUTE SQL_INTERVAL_DAY_TO_MINUTE //#define SQL_C_INTERVAL_DAY_TO_SECOND SQL_INTERVAL_DAY_TO_SECOND //#define SQL_C_INTERVAL_HOUR_TO_MINUTE SQL_INTERVAL_HOUR_TO_MINUTE //#define SQL_C_INTERVAL_HOUR_TO_SECOND SQL_INTERVAL_HOUR_TO_SECOND //#define SQL_C_INTERVAL_MINUTE_TO_SECOND SQL_INTERVAL_MINUTE_TO_SECOND //#define SQL_C_BINARY SQL_BINARY
case SQL_C_BINARY: return new SQLSimpleType<SQL_TYPE,ODBC_TYPE,Int16>(getConnector(),&cast_func,true); //#define SQL_C_BIT SQL_BIT case SQL_C_BIT: return new SQLSimpleType<SQL_TYPE,ODBC_TYPE,Int16>(getConnector(),&cast_func,true); //#define SQL_C_SBIGINT (SQL_BIGINT+SQL_SIGNED_OFFSET) case SQL_C_SBIGINT: return new SQLSimpleType<SQL_TYPE,ODBC_TYPE,Int32>(getConnector(),&cast_func,true); //#define SQL_C_UBIGINT (SQL_BIGINT+SQL_UNSIGNED_OFFSET) case SQL_C_UBIGINT: return new SQLSimpleType<SQL_TYPE,ODBC_TYPE,Int32>(getConnector(),&cast_func,true); //#define SQL_C_TINYINT SQL_TINYINT case SQL_C_TINYINT: return new SQLSimpleType<SQL_TYPE,ODBC_TYPE,Int16>(getConnector(),&cast_func,true); //#define SQL_C_SSHORT (SQL_C_SHORT+SQL_SIGNED_OFFSET) case SQL_C_SSHORT: return new SQLSimpleType<SQL_TYPE,ODBC_TYPE,Int16>(getConnector(),&cast_func,true); //#define SQL_C_STINYINT (SQL_TINYINT+SQL_SIGNED_OFFSET) case SQL_C_STINYINT: return new SQLSimpleType<SQL_TYPE,ODBC_TYPE,Int16>(getConnector(),&cast_func,true); //#define SQL_C_ULONG (SQL_C_LONG+SQL_UNSIGNED_OFFSET) case SQL_C_ULONG: return new SQLSimpleType<SQL_TYPE,ODBC_TYPE,Int32>(getConnector(),&cast_func,true); //#define SQL_C_USHORT (SQL_C_SHORT+SQL_UNSIGNED_OFFSET) case SQL_C_USHORT: return new SQLSimpleType<SQL_TYPE,ODBC_TYPE,Int32>(getConnector(),&cast_func,true); //#define SQL_C_UTINYINT (SQL_TINYINT+SQL_UNSIGNED_OFFSET) case SQL_C_UTINYINT: return new SQLSimpleType<SQL_TYPE,ODBC_TYPE,Int32>(getConnector(),&cast_func,true); default: Known problemsThere are no known problems. Configuration parametersConfiguring the ODBC DriverTo configure the handler both the handler itself must be told which tables, or parts of tables, should be accessed and the ODBC driver must be configured. In general, ODBC drivers are pretty easy to configure and, while each driver has its idiosyncrasies, most of the setup is the same for any driver/database combination. Both unixODBC and iODBC use two configuration fills: /etc/odbcinst.ini and /etc/odbc.ini. The driver should have documentation on these, however, there is one parameter you will need to know to make use of the sql handler. In the odbc.ini file, the parameter database is used to reference the actual database that is matched to particular Data Source Name (DSN). You will need to know the DSN since programs that use ODBC to access a database use the DSN and not the name of the database. In addition, there is a user and password parameter set defined for a particular DSN; the sql handler will likely need that too (NB: This might no actually be needed 9/9/12). Configuring the handlerSQL.CheckPointCheckpoints in the SQL handler are phases of the database access process where error conditions can be tested for and reported. If these are activated using the SQL.CheckPoint parameter and an error is found, then a message will be printed in the bes.log and an exception will be thrown. There are five checkpoints supported by the handler:
The default for the handler is to test for and report all errors: SQL.CheckPoint=1,2,3,4,5 Configuring DatasetsOne aspect of the SQL handler that sets it appart from other handlers is that the datasets it serves are not files or collections of files. Instead they are values read from one or more tables in a database. The handler uses one file for each dataset it serves; we call them dataset files. Within a dataset file there are several sections that define which Data Set Name (DSN) to use (recall that the DSN is set in the odbc.ini file which maps the DSN to a particular database, user and password), which tables, how to combine them and which columns to select and if any other constraints should be applied when retrieving the values from the database server. As a data provider, you should plan on having a dataset file for each dataset you want people to access, even if those all come from the same table. A dataset file has five sections:
Each of the sections is denoted by starting a line in the dataset file with its name in square brackets such as: [section] or [select] Information in the section part of the dataset fileThere are six parameters that may be set in the select part of the dataset file:
Note that a dataset file may have several [section] parts, each which lists a different DSN. This provides a failover capability so that if the same information (or similar enough to be accessible using the same SQL statement) exists both locally and remotely, both sources can be given. For example, suppose that your institution maintains a database with many thousands of observations and you want to serve a subset of those. You have a copy of those data on your own computer too, but you would rather have people access the data from the institution's high performance hardware. You can list both DSNs, knowing that the first listed will get preference. The select partThis part lists the columns to include as you would write them in an SQL SELECT statement. Each column name has to be unique. You can use aliases (defined in the preamble of the dataset file) to define different names for two columns from different database tables that are the same. For example, you could define aliases like these: table1.theColumn as col1 table2.theColumn as col2 and then use col1,col2 in the select part of the dataset file The from and where partsEach of these parts are simply substituted and passed to the database just as you would expect. Note that you do not include the actual words FROM or WHERE, just the contents of those parts of the SQL statement. The other partEntries in this parts should be of the form key = value, one per line. They are taken as a group and passed to the ODBC driver. Use this section to provide any parameters that are specific to a particular driver. Using variablesThe dataset files also support 'variables' that can be used to define a name once and then use it repeatedly by simply using the variable name instead. Then if you decide to read from a different table, only the variable definition needs to be changed. Variables are defined as the beginning o the dataset file, before the section part. The syntax for variable is simple: define $variable$ = value, one per line (the $ characters are literal, as is the word define). To reference a variable, use $variable$ wherever you would otherwise use a literal. Some example dataset files[section] # Required. api=odbc # This is the name of the configured DSN server=MySQL_DSN [select] # The attribute list to query # NOTE: The order used here will be kept in the results id, wind_chill, description [from] # The table to use can be a complex FROM clause wind_08_2010 [where] # this is optional constraint which will be applied to ALL # the requests and can be used to limit the shared data. id<100 |