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 server 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.

Download Valentina SQLite Server

Valentina SQLite Server is a part of Valentina Server. Valentina SQLite Server was introduced with Valentina Server release 6.0. Choose the latest build and install into your OS_64 directory as Valentina Server is a 64-bit application.

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

Valentina SQLite Server Support

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

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

Background of Valentina SQLite Server

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 datasources:

  • Work remotely with Database Servers such as mySQL, postgreSQL, MS SQL, Oracle, 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.

The Problem of SQLite DB Access & a Solution

How can we overcome the built in limitations found in SQLite DB access? SQLite lacks “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 few drawbacks in both types of solution. 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.

Technical Issues

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 SQLite engine. C++ Interfaces help us in few ways:

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

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

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

5) In Valentina Server we add layer, which listens to the special ports for SQLite Server and use 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, REST, …

Quick Start

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

Installation

1) Download and install Valentina Server version 6 or later : http://www.valentina-db.com/download/

Valentina Server 6 is no longer supported. Do not install Valentina Server 6.x to the computer where you have installed Valentina Server 5.x, because it will overwrite it.

The installer does not contains a license file, so obtain it through request on the online store. Without a license file, Valentina Server provides only 1 connection. You can start working if you want, but keep in mind that Valentina Studio and your application will not be able to connect simultaneously.

2) Download and install Valentina Studio version 6 or later. Valentina Studio 6.x can co-exist on the same computer as Valentina Studio 5.x.

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).

Check out 5 minute video tutorials for each OS which shows for Valentina Server and Valentina Studio
how to download, install, order and enable license file: MAC, WIN, Linux

3) Download and install “Valentina for Xojo” ADK.

Import Existing SQLite Databases

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

Valentina Studio

The next simple step is to establish a connection between Valentina Studio 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 Server 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 any SQLite database yet, you can create a new one.

Client APIs

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

Valentina SQLite Server for Xojo

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:

Valentina Server ini-file

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 sqlite databases are stored.
SQLITESYSTEMCATALOG = databases_sqlite

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

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

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

SQLite Server Features

Intelligent Protocol

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 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 change one field/record of a table.

Client-side and Server-side Cursors

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.

Datasource for Reports

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.

REST API for Valentina SQLite Server

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.

Notifications for SQLite

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.

SSL for SQLite

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

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

ACL for SQLite

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

SNMP for SQLite

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

SQLite Encryption

TODO.

Get Valentina SQLite Server Now

Valentina SQLite Server Evaluation Version

You can immediately download and evaluate Valentina SQLite Server on all supported platforms.

By default, Valentina Server supports a single connection, but you can also request an evaluation license that allows you to initialize it to five simultaneous connections of ValentinaDB and 10 simultaneous connections of SQLite. Go to our store and request your license now.

Valentina Server (Standalone Version)

Beyond Valentina Server (Evaluation Version), 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 DB/Reports SQLite Price
5 10 $0
5 10 $199
10 25 $299
25 UNLIMITED $499
250 UNLIMITED $999
UNLIMITED UNLIMITED $1499

Don't forget pricing includes ValentinaDB Server, Valentina SQLite Database Server and Valentina Reports Server, all together and SQLite connections are priced at a lower rate than others.

For example, at the $499 level, you get:
1) UNLIMITED number of connections for SQLite Server
2) 25 connections for Valentina DB Server
3) 25 connections for Valentina Report Server

Valentina Server (VDN / Embedded)

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 and Mac OS X.

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

Since these are unnamed connections that you programmatically control, a scenarios of deployment can serve 5-25 users or more.

See Also