readme

来自「这个是内存数据库的客户端」· 代码 · 共 275 行

This README describes the implementation of the ODBC driver code
(sql/src/odbc/driver/*) for the MonetDB SQL frontend (sql/src/common/*)
Both are available from  https://sourceforge.net/projects/monetdb/


Author : Martin van Dinther
Date   : 30 august 2002
Version: 1.0


Introduction:
=======================================================================
To enable the usage of the SQL frontend of MonetDB in a generic way,
 access has to be provided via industry standard open database API(s).
Two standard SQL APIs are currently widely accepted: ODBC and JDBC.
ODBC is chosen to be implemented first because:
- it can be implemented as a C library (same programming language as MonetDB)
- its driver manager is available on all platforms where MonetDB is ported
  (on UNIX/Linux we use unixODBC as the driver manager)
- there are JDBC-ODBC bridge drivers available (i.e. Sun) which provide
  a JDBC driver implementation on top of an ODBC driver. This is not
  optimal for performance and stability but we don't have to build it
  ourselves to get this functionality now.


Tip:
=======================================================================
To do ODBC driver development/maintenance you need the Microsoft ODBC
 specification to learn what the ODBC API functions and behavior has to be.
This specification is available as MS Windows help file (ODBC.HLP and ODBC.CNT).
It is part of the MS ODBC SDK which has become part of the MS MDAC SDK.
For convenience it is also available in /ufs/dinther/ODBC/

For compilation you need the ODBC API include header files:
	sqltypes.h	/* ODBC type defines */
	sql.h		/* ODBC core API functions */
	sqlext.h	/* ODBC extension (level 1 and 2) API functions */
	sqlucode.h	/* Unicode versions of the ODBC API functions */
We use the ones distributed with unixODBC.
At CWI these are located in /usr/include/


Files organization (in sql/src/odbc/driver/*):
=======================================================================
All ODBC driver implementation files are located in sql/src/odbc/driver/

Every ODBC API function which needs to be implemented by the driver
 has its own file with the same name as the function and suffix ".c".
The ODBC API functions all start with SQL, so its easy to locate them.
There are 73 SQL*.c files and thus 73 ODBC API functions implemented.

The ODBC API function prototypes and defines are defined in sqltypes.h,
 sql.h, sqlext.h and sqlucode.h so there are NO SQL*.h files.
Instead of including these sql*.h files from every SQL*.c file
there is a general include file called ODBCGlobal.h which includes
the sql*.h files and other standard external include files.
Every SQL*.c must include this ODBCGlobal.h file as first include.

Besides the SQL*.c files there are the ODBC*.[ch] files.
The files ODBCEnv.[ch] ODBCDbc.[ch], ODBCStmt.[ch], ODBCHostVar.[ch]
 and ODBCError.[ch] implement the structures needed to represent the
 handles (pointers to an object) and the internal state of the ODBC objects.
In the implementation section below it is explained why these are necessary.

For convenience there is also an ODBCUtil.[ch] which contains
 general ODBC specific utility functions.

Next there are some files which were already there:
- comm.[ch]		/* client connect(host, port) function. Used in SQLConnect.c */
- catalog_client.c	/* catalog retrieval functions and types. Used in SQLConnect.c */
- statement_dump.c	/* dumps a parsed SQL command. Used in SQLExecute.c */
- prof.c		/* not used */

Of course there is a Makefile. Because we use autoconf and automake
the source file is Makefile.ag.

Lastly there is this README documentation file:


Implementation:
=======================================================================
ODBC exists is several versions (1.0, 2.0, 2.1, 2.5, 3.0, 3.5, 3.51 and 3.52).
The latest version was released by Microsoft around 1997.
The unixODBC driver manager and include files support up to version 3.51.
Hence we chose to implement version 3.51.
The version we want to comply with is defined in ODBCGlobal.h. It must
be defined before the ODBC header files (sql.h etc.) are included!

In ODBC the API functions can be grouped in several ways:
a) ODBC 1 or 2 API compliance (Core, extension level 1, extension level 2)
b) ODBC 3 API compliance (ISO 92, X/Open, ODBC, Deprecated)
c) handle type (environment, connection, statement, descriptor)
I chose to use the handle type organization and created new files
ODBC*[ch] to define the structs and functions for the first three handle
types. Descriptor handles are currently not supported.

In ODBC multiple environments (each application uses its own environment),
 multiple database connections (an application can open multiple database
 connections, even to different ODBC drivers), multiple statements (an
 application can have multiple active statements open even within one
 connection), multiple result sets connected to one executed SQL command,
 multiple input parameters per statement, multiple output columns per result
 set.
The management and validity of these handles is all implemented in
the ODBCEnv.[ch], ODBCDbc.{ch], ODBCStmt.[ch] and ODBCHostVar.[ch] files.

Error handling in ODBC is done in a special way which is explained here.
Every ODBC API function returns a code (integer) which can be:
  - SQL_SUCCESS			/* no msg available */
  - SQL_SUCCESS_WITH_INFO	/* a warning msg is available */
  - SQL_ERROR			/* an error msg is available */
  - SQL_INVALID_HANDLE		/* no msg could be set because no valid handle */
and some special values (SQL_STILL_EXECUTING, SQL_NEED_DATA) we ignore here.
Only when the return code is SQL_ERROR or SQL_SUCCESS_WITH_INFO an
error message is available. The error message can be retrieved via
 SQLGetDiagRec() (or the deprecated function SQLError()).
The application is not required the retrieve the error message.
When the application calls the next ODBC API function on the same handle
the previous error is discarded.
So errors are recorded per handle and kept until the next function on
the handle is called or when the handle is freed (SQLFreeHandle()).
Also one ODBC API function can post several errors (and/or warnings)
on the handle, so we must be able to store multiple error messages.
This complex internal storage and behavior is implemented in the files
ODBCError.[ch]. Simple functions are created to add an error on a
specific handle (e.g. addDbcError()), to clear all errors on a handle
(e.g. clearStmtErrors()) and retrieve an error (e.g. getStmtError()).

Implementation of the ODBC API functions:
------------------------------------------------------------------------
Although 73 SQL*() functions are implemented, many do not implement the
 requested behavior (e.g. SQLBindParameter()). They just set an error
and return SQL_ERROR.

The next ODBC API functions are functionally implemented (some with TODO's):
------------------------------------------------------------------------
- SQLAllocEnv() - SQLAllocConnect() - SQLAllocStmt() - SQLAllocHanlde()
- SQLFreeEnv()  - SQLFreeConnect()  - SQLFreeStmt()  - SQLFreeHanlde()
- SQLConnect() - SQLDriverConnect() - SQLDisconnect()
- SQLPrepare()
- SQLExecute()
- SQLExecDirect()
- SQLBindCol()
- SQLNumResultCols()
- SQLFetch() - SQLGetData()
- SQLRowCount()
- SQLCloseCursor()
- SQLCancel()
- SQLEndTran() - SQLTransact()
- SQLGetDiagRec() - SQLGetDiagField() - SQLError()
- SQLColAttribute() - SQLColAttributes()
- SQLDescribeCol()
- SQLGetInfo()
- SQLGetCursorName() - SQLSetCursorName()
- SQLMoreResults()
- all catalog retrieval functions:
  - SQLTables()
  - SQLColumns()
  - SQLSpecialColumns()
  - SQLPrimarykeys()
  - SQLForeignkeys()
  - SQLStatistics()
  - SQLTablePrivileges()
  - SQLColumnPrivileges()
  - SQLProcedures()
  - SQLProcedureColumns()
  - SQLGetTypeInfo()

The next ODBC API functions always return SQL_ERROR or SQL_INVALID_HANDLE:
------------------------------------------------------------------------
- SQLBrowseConnect()
- SQLBindParameter() - SQLSetParam()
- SQLNumParams() - SQLParamData() - SQLParamOptions() - SQLPutData()
- SQLBulkOperations()
- SQLDescribeParam()
- SQLCopyDesc()
- SQLExtendedFetch()
- SQLFetchScroll()
- SQLSetPos() - SQLSetScrollOptions()
- SQLGetConnectAttr() - SQLGetConnectOption()
- SQLSetConnectAttr() - SQLSetConnectOption()
- SQLGetEnvAttr()
- SQLSetEnvAttr()
- SQLGetStmtAttr() - SQLGetStmtOption()
- SQLSetStmtAttr() - SQLSetStmtOption()
- SQLGetDescRec() - SQLGetDescField()
- SQLSetDescRec() - SQLSetDescField()
- SQLNativeSql()


Implementation of the deprecated ODBC API functions:
------------------------------------------------------------------------
Some ODBC API functions are deprecated in ODBC 3.0 (and higher).
They are currently implemented as wrappers around their ODBC 3.0 equivalent.
If you do not implement them you need an ODBC 3.0 (or higher) driver manager
to do the mapping for you else old ODBC applications (pre ODBC 3.0)
will not work with our ODBC driver.

Not implementation ODBC API functions:
------------------------------------------------------------------------
The ODBC functions SQLDrivers(), SQLDataSources() and SQLFunctions()
do not have to be implemented by the driver, those are always implemented
in the driver manager (also with old ODBC 1.0 driver managers).

The Unicode variants of ODBC API functions which have characters strings
as argument are currently not supported. These functions are recognizable
on their W function name suffix (e.g. SQLConnectW()), see sqlucode.h.



TODO list:
=======================================================================
With a grep on TODO *.[ch] you will get all places and files in which
 some implementation has to be completed.
Sometimes the TODO's are not critical or required (e.g. TODO in SQLEndTran()).

TODO 1) Some ODBC API functions which are not implemented, limit the use
 of the ODBC driver functionality considerably.
Most important are:
- SQLBindParameter()	/* needed for executing parametrized SQL commands */
- more SQLGetInfo() types	/* needed by apps and JDBC-ODBC bridge */
- SQLGetConnectAttr(), SQLSetConnectAttr()	/* especially AUTOCOMMIT */
- SQLGetStmtAttr(), SQLSetStmtAttr()	/* especially ROW_ARRAY_SIZE */
- SQLExentedFetch()			/* fetch multiple rows at once */
- SQLAllocHandle(SQL_HANDLE_DESC)	/* support descriptor handles */
- SQLFreeHandle(SQL_HANDLE_DESC)	/* support descriptor handles */
- all the SQL*W() functions	/* the Unicode (UCS-2) variant API functions */

When these SQL*W() functions are not implemented in a driver the driver
 manager (it must be ODBC 3.0 or higher) will convert those ODBC application
 SQL*W() calls to the ASCII variant (e.g. SQLConnect(), by converting the
 supplied UCS-2 strings to ASCII.

TODO 2) Because MonetDB supports UTF-8 strings we should do the conversion
and thus implement those functions ourselves.
This can be done simply via a wrapper mechanism and two converter functions:
  wchar * UCS2toUTF8(char * str)  and
  char *  UTF8toUCS2(wchar * str).


TODO 3) on MS Windows you need a dll instead of a lib as the resulting ODBC
 driver. This driver dll needs to be installed (placed in some dir and the
 registry needs to be updated). Only than the ODBC driver will be visible for
 the MS ODBC administrator (Control Panel, Data Sources (ODBC), Drivers tab)
 or simply type: odbcad32.exe  from a DOS command line.
Also on MS Windows you need a setup dll which is called by the ODBC
 administrator when you want to add, modify or delete an ODBC data source
 for this ODBC driver. It should present a configuration dialog for
 entering data source configuration settings (such as host, port, default
 user, connection timeout duration, etc.)


TODO 4) Besides the TODOs for increasing the functionality and ODBC compliant
 behavior there are also TODOs considering the performance.
The performance depends a lot on:
- what API the Monet SQL frontend provides (is data fetchable row wise, can multiple
 rows be returned at once, is data represented in the most optimal form (so
 as int or dbl instead of str) so extra conversions are eliminated)
- how the communication with the SQL frontend is realized (is all SQL
 processing done on the client or done on the server (or both), how many
 network calls are needed for fetching 1 row of data, how many client memory)
- how the SQL frontend converts the SQL table into bats and makes use of extra
 bats (indices) for faster joins/retrieval when executing an SQL statement.
Up till now no performance analysis or tuning is done.
On the ODBC level not much performance tuning can be done (ODBC is a thin
wrapper around the Monet SQL frontend API). so it must be done in the SQL frontend,
the communication APIs, SQL to MIL translator and possibly MonetDB.


TODO 5) For ease of maintenance the functions which communicate with the SQL
 frontend (comm.[ch])
 should be placed in the SQL frontend lib or SQL client communications lib.
This is a small cleanup to improve the layered architecture.