[MDEV-8882] CONNECT Engine - Unable to Merge Mutliple SQLite Created: 2015-10-02  Updated: 2015-10-21  Resolved: 2015-10-21

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - Connect
Affects Version/s: 10.0.19
Fix Version/s: 10.0.22, 10.1.9

Type: Bug Priority: Major
Reporter: Brandon Darbyson Assignee: Olivier Bertrand
Resolution: Fixed Votes: 0
Labels: None
Environment:

Centos 7


 Description   

We're trying to merge two sqlite databases that are similar to those outlined below, and see the following when we try to query the resulting table in MariaDB:

/usr/local/data/SQLiteDB_1.dat < create table users ( id varchar(10), name varchar(24) );
 
-----------------
 
| id  |  name
 
| f3  | Alice
 
| 6g | Bob
 

/usr/local/data/SQLiteDB_2.dat < create table users( id varchar(10), name varchar(24) );
 
------------------
 
| id   |  name
 
| s1  | Alison
 
| 9y  | Robert
 

MariaDB > create table people ( id varchar(10), name varchar(24) ) ENGINE=CONNECT TABLE_TYPE=ODBC multiple=1 tabname='users' CONNECTION='DSN=SQLite3 Datasource;DBQ=/usr/local/data/SQLiteDB_*.dat;';
 
 
 
MariaDB > select * from people:
 
 
 
----------------
 
| id  | name
 
| f3   | Alice
 
| 6g  | Bob
 
| f3   | Alice
 
| 6g  | Bob



 Comments   
Comment by Olivier Bertrand [ 2015-10-21 ]

The problem is that the sqlite ODBC driver does not recognize the DBQ keyword. The merge table should be created as:

create table people ( id varchar(10), name varchar(24) )
 
ENGINE=CONNECT TABLE_TYPE=ODBC multiple=1 tabname='users'
 
CONNECTION='DSN=SQLite3 Datasource;Database=/usr/local/data/SQLiteDB_*.dat;';

This is all right for simple tables but for multiple tables CONNECT failed to retrieve the database name because only looking for the DBQ keyword. This is what will be fixed in next versions of MariaDB.

Meanwhile, you can alternatively merge the tables using the TBL table type:

create table users1 (id varchar(10), name varchar(24)) engine=connect table_type=ODBC tabname='users'
 
CONNECTION='Driver=SQLite3 ODBC Driver;Database=/usr/local/data/SQLiteDB_1.dat';
 
 
 
create table users2 (id varchar(10), name varchar(24)) engine=connect table_type=ODBC tabname='users'
 
CONNECTION='Driver=SQLite3 ODBC Driver;Database=/usr/local/data/SQLiteDB_2.dat';
 
 
 
create table people2 (id varchar(10), name varchar(24)) ENGINE=CONNECT TABLE_TYPE=TBL table_list='users1,users2';

Generated at Thu Feb 08 07:30:29 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.