How our solutions works:

SQLite is a compact embedded DBMS. Most DBMSs use a client-server architecture: data is stored and processed on the server, and queries are sent to it by the client. The “client” is the part of the program with which the user interacts. A “server” can be a separate process on the same computer or a third-party device, as is the case with websites.

SQLite is designed differently and does not have a server. This means that the software stores all data on one device. The DBMS is built into the application and works as its integral part. If you install a program that uses SQLite on your computer, the database will also be stored on it. The database format is one text file that can be read on any platform. This approach increases productivity and speed.

A virtual table is an object that is registered with an open SQLite database connection. From the perspective of an SQL statement, the virtual table object looks like any other table or view. But behind the scenes, queries and updates on a virtual table invoke callback methods of the virtual table object instead of reading and writing on the database file.

The virtual table mechanism allows an application to publish interfaces that are accessible from SQL statements as if they were tables.

Our specialists have developed the SQLite extension – DbaSQLite, which allows using the virtual table mechanism to work with SQLite tools with external data stored in an external .dba format file.

Let’s imagine, that there is an external .dba file that contains data about the doctor code in the medical information system and data about the doctor himself:

-------------------------------------------------------------------------------------------------
CODEMED                         NAMEMED
-------------------------------------------------------------------------------------------------
CODE1                           NAME1
CODE2                           NAME2
CODE2                           NAME3
------------------------------------------------------------------------------------------------- 

It is necessary to provide access to this data by means of SQLite for joint analysis together with other data located inside the SQLite database. The extension we developed allows us to solve this problem using the SQLite shell.

Selecting all records:

select load_extension('sqldba.dll','sqlite3_sqldba_init');
create virtual table if not exists med1 using sqldba('med.dba');
select * from med1

А result of the request:

CODE1|NAME1
CODE2|NAME2
CODE3|NAME3

More complex example:

select load_extension('sqldba.dll','sqlite3_sqldba_init');
create virtual table if not exists med1 using sqldba('med.dba');
select * from med1

А result of the request:

CODE2|NAME2

The mechanism of virtual tables makes it possible to conduct a convenient joint analysis of data located in storages of different physical structures. Especially often such problems arise when working with medical information. If you are faced with similar tasks, then we are happy to offer you the SQLite extension we already have or develop new extensions for you that take into account your specific needs.

---