Creating and Dropping CONNECT Tables

You are viewing an old version of this article. View the current version here.
MariaDB starting with 10.0

The CONNECT handler was introduced in MariaDB 10.0.

Create Table statements for “CONNECT” tables are standard MariaDB create statements specifying engine=CONNECT. There are a few additional table and column options specific to CONNECT.

Table options

(More options will likely be added to this list in future versions of the handler.)

Table OptionTypeDescription
AVG_ROW_LENGTHIntegerCan be specified to help CONNECT estimate the size of a variable record length table.
BLOCK_SIZEIntegerThe number of rows each block of a FIX, BIN, DBF, or VEC table contains. For an ODBC table this is the RowSet size option. For a JDBC table this is the fetch size.
CATFUNCStringThe catalog function used by a catalog table.
COLISTStringThe column list of an OCCUR table.
COMPRESSBooleanTrue if the data file is compressed. Defaults to NO.
CONNECTIONStringSpecifies the connection of an ODBC, JDBC or MYSQL table.
DATA_CHARSETStringThe character set used in the external file or data source.
DBNAMEStringThe target database for ODBC, MYSQL, catalog, and PROXY based tables. The database concept is sometimes known as a schema.
ENGINEStringMust be specfied as CONNECT.
ENDINGIntegerEnd of line length. Defaults to 1 for Unix/Linux and 2 for Windows.
FILE_NAMEStringThe file (path) name for all table types based on files. Can be absolute or relative to the current data directory. If not specified, this is an Inward table and a default value is used.
HEADERIntegerApplies to CSV, VEC, and HTML files. Its meaning depends on the table type.
HUGEBooleanTo specify that a table file can be larger than 2GB. For a MYSQL table, prevents the result set from being stored in memory.
LRECLIntegerThe file record size (often calculated by default).
MAPPEDBooleanSpecifies whether file mapping is used to handle the table file.
MODULEStringThe (path) name of the DLL or shared lib implementing the access of a non-standard (OEM) table type.
MULTIPLEIntegerUsed to specify multiple file tables.
OPTION_LISTStringUsed to specify all other options not yet directly defined.
QCHARStringSpecifies the character used for quoting some fields of a CSV table or the identifiers of an ODBC table.
QUOTEDIntegerThe level of quoting used in CSV table files.
READONLYBooleanTrue if the data file must not be modified or erased.
SEP_CHARStringSpecifies the field separator character of CSV tables.
SEPINDEXBooleanWhen true, indexes are saved in separate files.
SPLITBooleanTrue for a VEC table when each of the columns are in separate files.
SRCDEFStringThe source definition of a table retrieved via ODBC or the MySQL API or used by a PIVOT table.
SUBTYPEStringThe subtype of an OEM table type.
TABLE_LISTStringThe comma separated list of TBL table sub-tables.
TABLE_TYPEStringThe external table type: DOS, FIX, BIN, CSV, FMT, XML, JSON, INI, DBF, VEC, ODBC, JDBC, MYSQL, TBL, PROXY, XCOL, OCCUR, PIVOT, VIR, DIR, WMI, MAC, and OEM. Defaults to DOS, MYSQL, or PROXY depending on what options are used.
TABNAMEStringThe target table or node for ODBC, MYSQL, PROXY, or catalog tables; or the top node name for XML tables.
XFILE_NAMEStringThe file (path) base name for table index files. Can be absolute or relative to the data directory. Defaults to the file name.

All integers in the above table are unsigned big integers.

Because CONNECT handles many table types; many table type specific options are not in the above list and must be entered using the OPTION_LIST option. The syntax to use is:

... option_list='opname1=opvalue1,opname2=opvalue2...'

Be aware that no blanks should be inserted before or after the '=' and ',' characters. The option name is all that is between the start of the string or the last ',' character and the next '=' character, and the option value is all that is between this '=' character and the next ',' or end of string. For instance:

option_list='name=TABLE,coltype=HTML,attribute=border=1;cellpadding=5,headattr=bgcolor=yellow';

This defines four options, 'name', 'coltype', 'attribute', and 'headattr'; with values 'TABLE', 'HTML', 'border=1;cellpadding=5', and 'bgcolor=yellow', respectively. The only restriction is that values cannot contain commas, but they can contain equal signs.

Column options

Column OptionTypeDescription
DATE_FORMATStringThe format indicating how a date is stored in the file.
DISTRIBEnum“scattered”, “clustered”, “sorted” (ascending).
FIELD_FORMATStringThe column format for some table types.
FIELD_LENGTHIntegerSet the internal field length for DATE columns.
FLAGIntegerAn integer value whose meaning depends on the table type.
MAX_DISTIntegerMaximum number of distinct values in this column.
SPECIALStringThe name of the SPECIAL column that set this column value.
  • The MAX_DIST and DISTRIB column options are used for block indexing.
  • All integers in the above table are unsigned big integers.

Index options

Index OptionTypeDescription
DYNAMBooleanSet the index as “dynamic”.
MAPPEDBooleanUse index file mapping.

Note 1: Creating a CONNECT table based on file does not erase or create the file if the file name is specified in the CREATE TABLE statement (“outward” table). If the file does not exist, it will be populated by subsequent INSERT commands or by the “AS select statement” of the CREATE TABLE command. Unlike the CSV engine, CONNECT easily permits the creation of tables based on already existing files, for instance files made by other applications. However, if the file name is not specified, a file with a name defaulting to tablename.tabletype will be created in the data directory (“inward” table).

Note 2: Dropping a CONNECT table is done with a standard DROP statement. For outward tables, this drops only the CONNECT table definition but does not erase the corresponding data file and index files. Use DELETE or TRUNCATE to do so. This is contrary to data and index files of inward tables are erased on DROP like for other MariaDB engines.

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.