[ANN] Valentina Server 6 Incorporating SQLite Engine

Add-Ons
1

Hi Xojo Developers,

You can read details about this here: http://www.valentina-db.com/blog/?p=1643

Valentina Server 6.0 is going to become 3 in 1:

  • Valentina DB Server
  • Valentina SQLite DB Server
  • Valentina Report Server

Valentina Report Server can generate reports for:

  • SQLite (local or under VServer now, this and/or another VServer SQLite)
  • Valentina DB (local or under this VServer and/or another VServer)
  • mySQL/mariaDB
  • postgreSQL
  • MS SQL
  • Oracle

You can read details about this here: http://www.valentina-db.com/blog/?p=1643

Additionally to that:

  • Free Valentina Server 6.0 will contain
    5 - connections for Valentina DB and/or Reports
    10 - connections to SQLite DB

  • Embedded Valentina Server (aka VDN, OEM) royalty free for deployment (any number of copies):
    5 - connections for Valentina DB and/or Reports
    10 - connections to SQLite DB.

2

Wow very nice! This will make it difficult to choose between cubeSQL and Valentina.

Question: will you be incorporating the SQLite encryption extensions for seamless drop-in encryption like Xojo/cubeSQL offers?

3

Hi Phillip,

about encryption for SQLite. I have look around and found these options:

  1. http://www.hwaci.com/sw/sqlite/prosupport.html

    • SQLite Encryption Extension (SEE). A drop-in replacement for public-domain SQLite that has the added ability to read/write AES-encrypted databases. $2000 one time fee

  2. https://www.zetetic.net/sqlcipher/commercial-edition-ordering/

    • 500$ for OSX + 500$ for Windows + 500 for Linux… yet per developer

All this is fun … such prices for encryption-feature to FREE db engine :slight_smile:
Compare to Valentina DB, which costs 300$ and encryption is included as tons of other features.

We going to provide easy conversion from SQLite db to Valentina DB, so may be such conversion can be a good option for developers who really need encryption. Also Valentina will give many other advantages.

Anyway, we will decide later about this.

Right now we have:

  • working VServer with SQLite engine, C++ API, protocol, …
  • plugin for Xojo.
  • Valentina Reports can use this SQLite Server.

And we have started integrate access to Valentina SQLite Server into Valentina Studio - GUI Manager.

Soon we going to start beta testing of 6.0 product line.
It have at least one more good surprise. It will be announced soon also.

4

Yes you will want to acquire the $2000 SEE option. You compile that into Valentina and then you are 100% compatible with Xojo encrypted databases as well as cubeSQL. So customers can move between them seamlessly.

Overall though great news.

5

But you pay it once and sell thousands of licenses using it.

6

what do i need to do in the code if i want to use the valentine server for my sqlite db?? currently all i need to do for cubesql in the code is to connect to the db server when client at sharing the data and if he is traveling, just move the data to their laptop and run the standalone one (which does not connect to server).

7

Hi Ricahrd,

same as with cubeSQL. Just connect() …
Because API of access is standard RBDB of Xojo.

Drop a database file into special folder, and server start to see/use it.

8

only if you do not use prepared statements.

9

if i do, then i need to have different command for the cubesql prepared statement and sqlite prepared statement.

10

it’s very interesting. I read the blog and the forum too.
But we need more information. What are the advantages? With WAL mode, multiple applications / threads can already access a Sqlite database without additional layer (each layer adding fragility).

[quote=167784:@Ruslan Zasukhin]same as with cubeSQL. Just connect() …
Because API of access is standard RBDB of Xojo.[/quote]
I suppose that this interface has a time cost.

How the server handles the connections and concurrency?

yes.

11

Hi Oliver,

  • To discuss tech details you are welcome to our Valentina forum: http://valentina-db.com/en/discussions
    We are open to listen any advices and critical requirements and Xojo-specific tricks.

  • It seems, I hear that exists RBDB API of Xojo, which required paid license, and can be limited to features,
    and could exists third-party SQLite plugins which are classes of Xojo, so are not locked to RBDB.
    Well, this is good point, and we can also provide such classes. For now was made only RBDB access.
    As you may know we provide both RBDB and classes for our Valentina DB also.
    For SQLite we have 3 classes, which cover all: Database, Statement, Cursor.

  • I suppose that this interface has a time cost.

Oliver, you mean that RBDB access can be slower of classes? Why?
Limited - I agree. But why slower?

12

  • Excuse me, how this resolve access to REMOTE server? WAL not helps right?
    I think this is main fundamental difference to point.

  • Server means additionally for example: users, SSL, …

  • Our Valentina Server have for years established infrastructure. It is based on third-party powerful framework for development of cross-platform network servers. We have just added
  • 3 new c++ interfaces around SQLite
  • into vkernel.dll we have added 3 classes - implementations - access to local sqlite engine
  • into vclient.dll we have added 3 proxy classes that talk to remote VServer by our standard protocol.
  • of course some special job in vserver: e.g. folder for sqlite dbs, ports for sqlite connections.
  • threading did not me, but our vserver developer, as I know he have spent a week reading docs of sqlite, and the main search some hard-core examples how to handle it. If you know something special to check here - please inform us on our forum.
13

Let me repeat, that the first push in this direction, we have got thinking about
Valentina Report Server → SQLite DB

Now somebody can have configuration
1 Valentina Report Server on computer A
N Valentina SQLite Servers around on computers B,C,D (i.e. more hardware, CPU, HDD/SSD)

Report Server can use all that N servers to access different SQLite dbs, in the same was as it can now access mySQL, postgreSQL, MS SQL, Oracle, other Valentina DB Servers.

This scenario is possible only in such solution. Right?
They name it - scalability.

14

Valentina SQLite Server - also opens multi-user access to SQLite dbs via our Valentina Studio - GUI Manager and Report Designer.

This means that some team can seat on N computers (mac/win/lin) around SQLite Server and develop in parallel:

a) Valentina Reports stored in single Valentina Project under that VServer as Report Server

  • i.e. developer_1 designs report_1;
  • developer_2 designs report_28;

b) SQLite db - add tables, records…

Ahh, developers can be even in other country and still be able work with remote Valentina Server to access reports of team and SQLite db.

15

What are the advantages?

Also we see potential ability “to gift” to SQLite engine our own Valentina SQL functions. This is few hundreds functions.
It seems this can be possible via SQLite extension, on start of vserver we register our functions to be also SQLite engine functions.

This will not happens in 6.0,
I just expose possible advantages, which also cannot be copied by others.

Our VServer team thinks that we can “gift” in such way some other VServer/Valentina features.

16

Ruslan, Thank you for all that information!

As I understand, for Sqlite, tools like CubeSql or Valentina Server bring the connection via a port. And perhaps a queuing mechanism.

For example, in the case of a Xojo web app, Xojo web apps on servers A and B can communicate with the same Sqlite database on server C. On the server C, Valentina Server awaits new connections / requests and sends these requests to the local Sqlite database.

What will happen if Valentina Server receives two simultaneous write requests? Or if Valentina Server receives a write request while Sqlite is already locked with current writing?
Is Valentina puts a pending write request until Sqlite is available? Or it sends all queries to Sqlite, and Sqlite manage this?

As Philip said, Xojo includes encryption for Sqlite. This is important. It would be nice if Valentina Server could handle a encrypted SQLite database.

CubeSql is convenient, but it must rewrite all the queries that contain prepared statements. So almost all write requests in a web app. This is also the case of Valentina Server? What must we rewrite in our existing queries?

Compared to CubeSql, I see that Valentina Server provides other advantages that the connection via a port: reports and PDF server, easy PDF creation, graphic design (schema editing & diagramming) and other features.

Another very interesting point, apparently you will provide tools to migrate on a Valentina database. This is useful if we have reached the limit of Sqlite.

But the most important in the beginning, at least for me, is simple to manage / migrate a Sqlite database in Valentina Server (Many requests to rewrite?), reliability (especially when there are multiple write requests to manage in Sqlite) and speed.

17

Hi Oliver,

you touched 2 big questions. so I will split them in answers.

  • We have queues, we have threads and we have few mutexes that regulate things.

  • If one thread tries to write, while another one writes, sqlite returns error. But exists callback to manage this. We use this to do N attempts during X time to execute the same operations. Only if all attempts fail, server will return error from SQLite engine.

  • with time vserver team will add granularity step by step, but that is internal job, not visible to users of vserver.

18

I think I am not sure what “re-write” you mean.
a) cubeSQL internally re-write something?
b) developer need re-write his code adopting app with LOCAL sqlite engine to REMOTE?

It seems you talking about b), yes?

  • Then I would ask you to explain me better, may be with example BEFORE/AFTER of such re-write. Why it is required.
    May be it is better todo on our Valentina Forum…

  • I can assume that RBDB API works not good with prepared statement? Then, as I understand, more interesting becomes to implement own 3 classes to work with Valentina SQLite Server. Because

a) such classes can unify access to LOCAL and REMOTE sqlite dbs. In very similar way as we have for Valentina DB.
b) such classes will eliminate need in paid Database license of Xojo.

But this will work only for new projects. I think you worry about existed projects/code…

19

ok, thank you Ruslan. So Valentina Server does not replace the access mechanisms of Sqlite. If I understand correctly, if Sqlite is set in WAL mode, we will always have a writing thread at the same time that an “unlimited” number of read threads. Valentina Server adding the server side: access via a port, additional queuing mechanism.

20

ok, I will continue on the Valentina forum by giving code examples. Thank you.