Using CONNECT - Virtual and Special Columns
CONNECT supports MariaDB virtual and persistent columns. It is also possible to declare a column as being a CONNECT special column. Let us see on an example how this can be done. The boys table we have seen previously can be recreated as:
create table boys ( linenum int(6) not null default 0 special=rowid, name char(12) not null, city char(12) not null, birth date not null date_format='DD/MM/YYYY', hired date not null date_format='DD/MM/YYYY' flag=36, agehired int(3) as (floor(datediff(hired,birth)/365.25)) virtual, fn char(100) not null default '' special=FILEID) engine=CONNECT table_type=FIX file_name='boys.txt' mapped=Y lrecl=48;
We have defined two CONNECT special columns. You can give them any name; it is the field SPECIAL option that specifies the special column functional name.
Note: the default values specified for the special columns do not mean anything. They are specified just to prevent getting warning messages when inserting new rows.
For the definition of the agehired virtual column, no CONNECT options can be specified as it has no offset or length, not being stored in the file.
The command:
select * from boys where city = 'boston';
will return:
linenum | name | city | birth | hired | agehired | fn |
---|---|---|---|---|---|---|
1 | John | Boston | 1986-01-25 | 2010-06-02 | 24 | d:\mariadb\sql\data\boys.txt |
2 | Henry | Boston | 1987-06-07 | 2008-04-01 | 20 | d:\mariadb\sql\data\boys.txt |
6 | Bill | Boston | 1986-09-11 | 2008-02-10 | 21 | d:\mariadb\sql\data\boys.txt |
Existing special columns are listed in the following table. Other PlugDB special columns exist but they have no interest when called from MariaDB:
Column Name | Type | Description of the column value |
---|---|---|
ROWID | Integer | The row ordinal number in the table. This is equivalent of a virtual column with an auto increment of 1. |
ROWNUM | Integer | The row ordinal number in the file. This is equivalent of ROWID except for multiple tables, TBL tables or XML tables with a multiple column. |
FILEID | String | The full name of the file this row belongs to. Useful in particular for multiple tables represented by several files. |
TABID | String | The name of the table this row belongs to. Useful for TBL tables. |
SERVID | String | The name of the external server used by a MYSQL table. |
Note: CONNECT does not currently support auto incremented columns. However,
a ROWID
special column will do the job of a column auto incremented by 1.