Table of Contents
Valentina SQLite Server
Valentina Server includes a powerful, multi-user access ready SQLite-based database server.
Download Valentina Server
Valentina SQLite Server was introduced with Valentina Server 6.0. Choose the latest build and choose your OS_64 folder, because Valentina Server is a 64-bit application.
Please use our forum, SQLite category for any questions and discussions:
Valentina Server has evolved into a dual server platform including:
- Valentina Database Server (incorporating the complete Valentina 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.
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
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, …
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:
1) Download and install Valentina Server 6.0 or later : http://www.valentina-db.com/download/
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 6.0 or later. Valentina Studio 6.x can co-exist on the same computer as Valentina Studio 5.x.
Valentina Studio is our professional GUI Manager for working with databases. Valentina SQLite Server is supported under Valentina Studio 6.x 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).
how to download, install, order and enable license file: MAC, WIN, Linux
3) Download and install “Valentina for Xojo” ADK v6.0.
Move Existed DB
If you have some SQLite database you should drop it into the folder 'databases_sqlite' of VSERVER. If this folder doesn`t exist - create it.
The next simple step is to connect from Valentina Studio to your SQLite Server.
Start Valentina Studio 6.0. On the first start it will scan default ports for Valentina Server, mySQL and posterSQL on the localhost. For the found servers it will create default bookmarks. You can see on the picture two such bookmarks for the same Valentina Server. The first bookmark is for SQLite databases, the second one is for Valentina databases.
Click SQLite bookmark and you will be connected. 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.
At this moment you can access Valentina SQLite Server using the following APIs (implemented as native plugins):
Xojo provides its own universal Database API, which covers work with different DB 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. An example below demonstrates its usage also.
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
For Valentina SQLite Server we have used, of course, the same protocol, as for Valentina DB Server. This means that you will get smart protocol, which do compression of pictures before sending them, as well as zip compression of big data if they happen. In 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.
We have implemented for SQLite Server both Server-side and Client-side cursors.
A client-side cursor is the one, which you could see in mySQL or postgreSQL. When you SELECT some records, they all are read from disk on server and sent into RAM of your client-computer. Now you can work with this read-only snapshoot. 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
Using Valentina SQLite Server as data-source of Valentina Reports is similar to Valentina data-sources except “protocol” prefix:
vsqlite_server://host = '...' port = '...' dbname = '...' user = '...' password = '...' ssl = 'true/false' [options = '...'];
Also, you may check V4RB and VSDK, vREST SQLite examples for details.
Read More... about possible data sources for a Valentina Report.
You can use REST API of Valentina Server to work with your SQLite databases.
First of all this means, that you can access your SQLite Server from virtually any OS (mac, win, linux, iOS, Android, WinPhone, … ) and any API (PHP, Python, NET, …) right now!
You can read our article about REST API for Valentina Server 6.0 for the details.
Valentina Server 6.0 adds Notifications similar to postgreSQL. This is the feature of VServer, not of the database. So you can use it working with SQLite databases. You can read this article for the details.
You can use SSL features of VServer working with SQLite databases.
You can use ACL features of VServer working with SQLite databases. This means you can specify rules which connections allow or disable.
SNMP of VServer allows you to use a lot of existed tools to monitor state of VServer.
SQLite Server - Prices
Free Standalone Instance
Free option of Valentina Server includes:
- 10 connections to SQLite db
- 5 connections to Valentina DB
- 5 connections to Valentina Reports.
This is enough e.g. for small business office.
Valentina Server (Standalone Version)
You can download Valentina Server for personal use and evaluation purposes for free; this version has always supported five simultaneous connections of Valentina DB, but from Valentina Server 6, the number of supported SQLite connections is greater. Valentina Server provides 10 free connections for SQLite. You can use these connections from any supported API, Valentina Studio and Reports. If you need more than 10 connections and priority technical support, you should check commercial pricing.
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 Embedded Server is a great option for developers that want to deploy their applications bundled with Valentina Server under Valentina Developer Network. 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 the base VDN price of $599 (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
This base version can serve most scenarios of deployment in the small offices with 5-25 users.
- 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, …)