Valentina SQLite Server

Valentina Server includes a powerful, multi-user access-ready SQLite-based database server. Valentina SQLite Server allows up to create, upload and manage SQLite databases. It also leverages many servers features not directly supported by the SQLite format without introducing any incompatibilities. It also allows your Valentina SQLite Server databases to work with Valentina Reports Server, also a part of Valentina Server.

Valentina SQLite Server is a part of Valentina Server. Choose the latest build and install it into your OS_64 directory as Valentina Server is a 64-bit application.

http://www.valentina-db.com/download/

There is a dedicated sub-forum for SQLite under the Valentina Server category. Your first stop for questions and discussions is our free support forum:

https://valentina-db.com/discussions/forums/

Valentina Server has evolved into a dual server platform including:

• Valentina Database Server (incorporating the complete ValentinaDB Database system as a Server)
• Valentina Reports Server (incorporating a full Reports Server for use with all popular database formats)

This may be a unique combination since database and reports servers have been traditionally made available as entirely separate products.

Valentina Report Server can use many different data sources:

• Work remotely with Database Servers such as MySQL, PostgreSQL, MS SQL, MongoDB, and of course Valentina DB Server
• Valentina DB files managed by the same installation Valentina Server, with multi-user access
• SQLite files located on the same computer as Valentina Server, but used only in exclusive read mode.

How can we overcome the built-in limitations found in SQLite DB access? SQLite lacks a “native” server and either you should invent your own client-server protocols OR you should use third-party solutions to work with SQLite databases remotely. There are a few drawbacks to both types of solutions. Obviously, one of those is extremely time-consuming and creates more opportunities to introduce bugs and issues. Third-party solutions bring along their own interfaces and then you should write two branches - the first one for remote access and another one (native approach) for local SQLite storage.

Our conclusion was that we can provide multi-user access to SQLite databases within the scope of our own Valentina Server.

[You can skip this section]

To resolve this task, we made the following steps:

1) Put the whole SQLite engine into our VKernel.dll, which takes care of LOCAL databases.

2) Add 3 new C++ interfaces into VShared.dll

• VShared/VSQL/Interfaces/VSQL_I_SqliteCursor.h
• VShared/VSQL/Interfaces/VSQL_I_SqliteDatabase.h
• VShared/VSQL/Interfaces/VSQL_I_SqliteStatement.h

These interfaces wrap the native C API of the SQLite engine. C++ Interfaces help us in few ways:

2.1 Allow having few implementations. We may need two. The first one is for VCLIENT and the second one is for LOCAL access. 
2.2 Because of that we can write C++ code, totally the same to access both local and remote SQLite databases.
2.3 Valentina Server will use local implementation to access the SQLite engine.
2.4 Provide C++ style. 

3) In the VCLIENT.dll we provide the implementation of these 3 interfaces, which talk to the remote Valentina Server.

4) In the VKERNEL.dll we provide the implementation of these 3 interfaces, as an alternative way to use SQLite engine, using our own C++ API.

5) In Valentina Server we add a layer, which listens to the special ports for SQLite Server and uses VKERNEL.dll → local-sqlite-implementation to work with SQLite engine. And of course, it does much more work integrating SQLite DB where it is possible into Valentina Server features, such as SSL, SNMP, Reports, and REST, …

For those who want to do something quickly (but please read the rest of the article to get all the possible info) the most important steps are:

1) Download and install Valentina Server version 6 or later.

The installer does not contain a license file, so obtain it through a request on the online store. Email is sent automatically in a few seconds for FREE Valentina Server.

• Without a license file, the Valentina Server provides only 1 connection.
• Free Valentina Server license provides 5 connections.

2) Download and install Valentina Studio version 6 or later.

Valentina Studio is our professional database management application for working with databases. Valentina SQLite Server is supported in Valentina Studio through its plugin architecture. For this, we added the database plugin 'sqlite_client' into Valentina Studio, and re-wrote an existing plugin for local SQLite files to use our own CPP API (see Technical Issues).

3) Download and install a Valentina ADK for your programming language. The following list contains Valentina ADK which has the support of Sqlite Server.

• Valentina for C - using General API
• Valentina for Java - using General API
• Valentina for LiveCode - using RevDB API
• Valentina for NET - using General API
• Valentina for PHP - using PDO API
• Valentina for Python - using PEP 249 -- Python Database API Specification v2.0
• Valentina for Xojo - using RBDB API

If you have an existing SQLite database you should drop it into the 'databases_sqlite' directory of the Valentina Server. If this folder doesn`t exist - create it.

The next simple step is to establish a connection between Valentina Studio and to SQLite Server.

Start Valentina Studio. Upon launch, Valentina Studio scans default ports for Valentina Server: ValentinaDB, SQLite, MySQL, and PostgreSQL on the localhost. For the found servers it will create default bookmarks.

Two such bookmarks for the same installation of Valentina Server. The first bookmark is for SQLite databases, and the second one is for ValentinaDB databases.

From the Servers column, select the SQLite bookmark and you will be connected to Valentina SQLite Server. After this, you will see your SQLite databases if they are present.

Click on a database to open it and start working with its objects and records. If you do not have an SQLite database yet, you can create a new one.

At this moment you can access Valentina SQLite Server using the following APIs (implemented as native plugins):

• C++
• Xojo (http://www.xojo.com)
• LiveCode (http://www.livecode.com)
• Java
• NET
• Obj-C
• PHP
• Python
• REST (opens access for near any OS and near any language).

Xojo provides its own universal Database API, which uses a common denominator model to provide support for different database servers and local SQLite files. We follow this API to allow you to migrate your existing Xojo App with SQLite local DB to our Valentina SQLite Server in no time.

To connect to Valentina Sqlite Server you should use our class VSQLiteDatabase as shown in the following code. You can find a working example here: 'V4RB/Examples/RBDB_way/VSlite'.

There is one more helper class from us, VSQLitePreparedStatement - to allow SQL queries with binding. Here is a code example to get started:

db = new VSQLiteDatabase
 
if( gClient ) then
    db.Host = "localhost"
    db.UserName = "sa"
    db.Password = "sa"
    db.Port = 15532   
    Connected = db.Connect()
 
    db.databaseName = "vsqlitetest.sqlite"
    Connected = db.CreateDatabaseFileEx()
else
    Dim db_file as FolderItem = app.dataloc.TrueChild("vsqlitetest.sqlite")
    db.DatabaseFile = db_file
 
    Connected = db.CreateDatabaseFileEx()
end if

Read more on the following WIKI page(s) about Database API for SQLite in the V4RB plugin:

• Xojo Database API in the V4RB plugin
• Xojo: Migration to Valentina SQLite Server

Valentina Server 6.0 having first started on a clean computer will create ini-file with default settings. A special section about what you should be aware of now exists for SQLite:

[SQLITE]
; The path, where sqliSQLitete databases are stored.
SQLITESYSTEMCATALOG = databases_sqlite

; Non-secure port to listen to Sqlite connections. 0 - not listen.
PORT_SQLITE = 15532

; Secure port to listen to Sqlite connections. 0 - not listen.
PORT_SQLITE_SSL = 15534

Read More... about INI-file of Valentina Server 6.0

For Valentina SQLite Server we have used, of course, the same protocol, as for Valentina DB Server. This means that you can leverage our intelligent protocol, which enables the compression of pictures before sending them, as well as zip compression of large data if they happen. At the same time, our protocol is very effective for the smallest SQL commands such as access to or changing one field/record of a table.

We have implemented for Valentina SQLite Server both Server-side and Client-side cursors.

Client-side cursors work much like they do in mySQL or postgreSQL. When you SELECT some records, they all are read from disk on server and sent into RAM on your client-computer. You can work with this in read-only mode. You know how many records are present in the cursor, and you can iterate them in any direction, and jump to Nth record.

A server-side cursor reads from the disk only one record and send it back to client-computer. You don't know how many records was found. You cannot jump to Nth record. You can only move to the next or previous record. If you try some prohibited feature, you will get exception about this.

Valentina Server also incorporates Valentina Reports Server.

Using Valentina SQLite Server as data-source for Valentina Reports is similar to Valentina data-sources except “protocol” prefix:

vsqlite_server://host = '...' port = '...' dbname = '...' user = '...' password = '...' ssl = 'true/false' [options = '...'];

You can test these against our Valentina for Xojo, Valentina SDK, Valentina REST API, and SQLite examples.

Read More... about all possible supported datasources for Valentina Reports.

You can use the REST API of Valentina Server to work with your SQLite databases.

You can access your SQLite Server from virtually any OS (mac, win, Linux, iOS, Android, WinPhone, … ) and any API (PHP, Python, NET, …) without much effort.

Read more about the Valentina REST API for Valentina Server for the details.

Valentina Server supports Notifications similar to how they are implemented in PostgreSQL. This is the feature of Valentina Server and not the database, so you can use it working with SQLite databases.

Read article Notifications in Valentina Server for details.

You can use the SSL features of Valentina Server with SQLite databases.

Read the introduction to Using SSL with Valentina Server for more information.

You can use ACL features of Valentina Server working with SQLite databases. This means you can specify rules on which connections to allow or disable.

SNMP support in Valentina Server allows you to use many existing tools to monitor the state of Valentina Server.

TODO.

You can immediately download and evaluate Free Valentina SQLite Server on all supported platforms (mac64, win32, win64, win64_arm, lin64, lin32_arm, lin64_arm).

The number of simultaneous connections supported by any version of the Valentina SQLite Server is higher (better) than the number of connections for the Valentina DB Server and Valentina Report Server. This is to allow very low-cost deployment of unlimited connection support for the Valentina SQLite Server.

Beyond 'FREE Valentina Server', you can get additional connections, direct technical support, and more. You can use these connections from any supported API, Valentina Studio, and Reports. If you need more than 10 connections and priority technical support, check current pricing. Pricing includes 12 full months of updates at no additional cost.

Valentina Developer Network is a great option for developers that want to deploy their applications bundled with Valentina Server without paying per deployment royalties. This allows you to deploy the base version of Valentina Server with your applications - in unlimited quantities, and on all three operating systems: Windows, Linux (x86 and ARM7), and macOS.

For a low base price (including 12 months of free updates and upgrades), you can deploy the base version of Valentina Server, which includes:

• 5 - to Valentina DB Server
• 5 - to Valentina Reports Server
• 10 - to SQLite Server

• Xojo: Migration to Valentina SQLite Server - shows step by step how to change EXISTING Xojo project with local SQLite DB to Valentina SQLite Server adding 10-12 lines of code.

• Xojo Database API in the V4RB plugin – for Xojo
• ADK API for SQLite – for other ADKs (VNET, VJAVA, …)

• Valentina Server
• Valentina Report Server - REST Access