Use Sqlite in a client/server concurrent mode

Tom Duncan · 2012-02-07 22:31:24

Hi All.
This is what I need to do with my current application.

1. Connect multiple machines to the same sqlite3 database
2. Just using a mapped network drive
3. Have an easy way to update the other machines when an update is done.
4. be nice and quick. 10 tables with up to 10,000 records on a couple of the tables.
5. Easy for me to do. lol

And has anyone done this as a demo so I can see how it is done.

Tom

ab · 2012-02-08 06:45:05

Using SQLite3 over a mapped network drive do not work, due to synchronization issues of remote file locking.
See http://www.sqlite.org/lockingv3.html#how_to_corrupt:

SQLite uses POSIX advisory locks to implement locking on Unix. On Windows it uses the LockFile(), LockFileEx(), and UnlockFile() system calls. SQLite assumes that these system calls all work as advertised. If that is not the case, then database corruption can result. One should note that POSIX advisory locking is known to be buggy or even unimplemented on many NFS implementations (including recent versions of Mac OS X) and that there are reports of locking problems for network filesystems under Windows. Your best defense is to not use SQLite for files on a network filesystem.

Using our mORMot framework will allow you to do exactly what you need.
But you'll need to run a "server" application to serve the sqlite3 database remotely via JSON/HTTP.
You have 700 pages of documentation in http://synopse.info/fossil/wiki?name=Downloads with associated demos.
For instance, see http://synopse.info/fossil/dir?ci=2317e … ent-Server

I'm thinking of adding a remote access provider for our SynDB classes.
It would allow to use Client-Server access to any DB (SQLite3, MSSQL, OleDB, Oracle...) via HTTP/JSON access, without the ORM part of the framework.
But it is not yet published. Stay tuned!

Tom Duncan · 2012-02-08 07:20:51

Thanks so much for that.
Will have a look now.
See if I can get my head around json.
The Android demo uses a MySQL call to a php file which returns the database from the query.
Will stay tuned on what you come up with and also what I can do.

Tom

Tom Duncan · 2012-02-08 20:29:17

Hi,
with a SQlite3 server and Json i/o could I replicate what my Mysql server gives me with my basic4Android program.
The php script on the server is..

<?

$databasehost = "localhost";
$databasename = "****";
$databaseusername ="****";
$databasepassword = "*****";

$con = mysql_connect($databasehost,$databaseusername,$databasepassword) or die(mysql_error());
mysql_select_db($databasename) or die(mysql_error());
$query = file_get_contents("php://input");
$sth = mysql_query($query);

if (mysql_errno()) {
header("HTTP/1.1 500 Internal Server Error");
echo $query.'\n';
echo mysql_error();
}
else
{
$rows = array();
while($r = mysql_fetch_assoc($sth)) {
$rows[] = $r;
}
print json_encode($rows);
}
?>

and on the Android I call like this..

Sub ExecuteRemoteQuery(Query As String, TaskId As Int)
Dim req As HttpRequest
req.InitializePost2("http://192.168.1.5/rsl/reget.php", Query.GetBytes("UTF8"))
hc.Execute(req, TaskId)
End Sub

ExecuteRemoteQuery("SELECT person FROM countries WHERE id='" & tl.First & "'", COUNTRY_POPULATION)

Is this possible with Json using your software

Then on the response from Json..

use the Get(i) function to populate my list for people etc
Also using the same idea to get/send the edit boxes for any changes?

Sorry if I am using Android routines but this because part of the project is to inerface with the Android, the main part is a PC program.

Tom

Last edited by Tom Duncan (2012-02-14 22:42:59)

ab · 2012-02-09 08:24:51

Yes, of course you can do that.

The easiest is to use the SynDB routines, then you can directly fetch all data as JSON.

See TDBExplorerFrame.BtnExecClick method in the SynDBExplorerFrame.pas demo file - http://synopse.info/fossil/artifact/596 … 1083baac7f

var SQL, JSONBuffer: RawUTF8;
    Rows: ISQLDBRows;
....
   Rows := Props.Execute(SQL,[]);
   JSONBuffer := Rows.FetchAllAsJSON(true);
....

In the above code, FetchAllAsJSON() is called with Expanded: boolean = TRUE parameter to retrieve a "standard" JSON content, which can be parsed within Android.

OR you can use direct access to SQLite3 with the SynSQLite3.pas unit, as such:

var DB: TSQLDataBase;
     SQL, JSONBuffer: RawUTF8;
...
   JSONBuffer := DB.ExecuteJSON(SQL,true); // Expand=true here also
...

Note that using TSQLDataBase.ExecuteJSON is able to use an internal Cache, is TSQLDataBase.UseCache property is set to true - but you shall ensure that cache is consistent by flushing it on write.

Tom Duncan · 2012-02-11 12:01:04

I am still finding it hard to get my head around the code.
I might have to wait till someone comes up with demo code that I can see what is going on.
Have checked the DBExplorer area., but I still a way for the client to send the SQL code to the server.

Will keep reading and see if I can see what is happening.

Tom

Tom Duncan · 2012-02-12 00:58:21

I cannot see how I can in server mode.
Attach an existing Sqlite database.
Then have it send out the json response from the query.
then on client, see how to send request via an sql call.
The display of the result i can figure out.
Then work out what other clients are modifying the database, stop conflicts etc.

Tom

ab · 2012-02-12 08:20:42

You can use the default mORMot server - see e.g. sample "04 - HTTP Client-Server" - see http://synopse.info/fossil/dir?ci=cb43d … ent-Server

See the SAD documentation about our RESTful format.
You'll find out the URI to use here to retrieve/set/delete/update records.
See also http://blog.synopse.info/post/2010/07/0 … pplication

You can also execute any SQL statement via TSQLRestClientURI.EngineExecute() or TSQLRestClientURI.EngineExecuteFmt() methods.

But you need to have the rights to do this (for security reasons).
The best is to use authentication with a good user.
But you may not be able to authenticate yourself. You'll have to make the server unsafe. For this, you can set SUPERVISOR_ACCESS_RIGHT.AllowRemoteExecute := true globaly or put a proper rights parameter at TSQLite3HttpServer.AddServer() calling.

If you do not want the ORM part of the framework, you have at hand all bricks to make your own SQLite3 HTTP server returning JSON.

Tom Duncan · 2012-02-12 08:47:42

at the moment all server uses will be on my local server. Will have a look tonight. See if I can get the basics to work.
Feel like a dummy at the moment. Most of my delphi and database stuff has been with vcl's. But this I am sure is the better way for me to go in the long run.
Just have to get my old head around it.
Tom

Tom Duncan · 2012-02-13 02:28:06

Have had a good look at the Http Server.
How can I hook another database to it, not the default one you create.
I assume this uses the Props to get the Database structure.
Once connected do I then do a sql call requesting info from the server, much like the DBexplorer demo.
I will need to fill a grid with clients names. (something line select FirstName, LastName from Patient)
Then use the returned Json to fill the grid.
Then select a field from the grid to display all the details for that patient.
I assume you use edit boxes like your demo,
Then on posting any changes fill out the json string back to the server.
Is this idea OK (ish)
I will keep searching today (your sleep time) and see what I can come up with.

Tom

ab · 2012-02-13 14:11:42

I've added a dedicated sample file to the repository.

The new "13 - StandAlone JSON SQL server" sample aim is to directly serve SQLite3 JSON results from SQL using HTTP server.

The processing is the following:

function TJSONServer.Process(const InURL, InMethod, InHeaders, InContent,
  InContentType: TSockData; out OutContent, OutContentType,
  OutCustomHeader: TSockData): cardinal;
begin
  try
    OutContentType := JSON_CONTENT_TYPE;
    OutContent := fProps.Execute(InContent,[]).FetchAllAsJSON(true);
    result := 200;
  except
    on E: Exception do begin
      OutContentType := TEXT_CONTENT_TYPE;
      OutContent := E.ClassName+': '+StringToAnsi7(E.Message)+#13#10+InContent;
      result := 504;
    end;
  end;
end;

It will expect the incoming SQL statement to be POSTED as HTTP body, which will be executed and returned as JSON.

But it is a very rough mechanism:
- No security is included;
- You can make your process run out of memory if the request returns too much rows;
- All incoming inputs will not be checked;
- No statement cache is used.

Therefore, this method is much less efficient than the one implemented by mORMot.

Using SynDB classes instead of directly SynSQLite3 will allow to use any other DB, not only SQlite3.

See http://synopse.info/fossil/finfo?name=S … Server.dpr

Tom Duncan · 2012-02-13 23:28:21

I feel like a right royal dummy now.
Thanks again for your help but still having problems.
Tested your server by pointing to my demo database.
Then via Android tried to connect. (connection refused)
Then thought I would try a web browser http://localhost:888/root select * from Patient
"the request source not found"
What am I doing wrong.
Sorry to be such a pain.

Tom

Tom Duncan · 2012-02-13 23:29:42

also can I use one of the clients to test this.
Will try the http client as an idea.

ab · 2012-02-14 08:03:37

As I wrote above, the SQL is in the BODY of the HTML message.

So you can't use it from a browser.
But your Android code should work.

Tom Duncan · 2012-02-14 10:18:35

Tested that but got an error. Connection refused, bugger.
Should one of the demo clients be able to be used with the server.

Tom

ab · 2012-02-14 13:21:31

"Connection refused" looks like a FireWall or rooting issue.

Tom Duncan · 2012-02-14 14:02:09

Was ok with my local server and the php file.
I also did a test with 8080 as well.
Can I test it out on any client demos at all. (Pc based)
tom

Tom Duncan · 2012-02-14 23:39:30

Did some more tests this morning on the Android.
http://127.0.0.1:888/root
http://127.0.0.1:8080/root
http://127.0.0.1:3506/root
http://localhost:888/root
Also changed the server port number to match.
Still no go.
Is their any chance please of getting a Delphi client going with the new Json server.
Then at least I can get the PC side of the software going.
This is just so frustrating for me. (and I bet for you)
I really am experienced in programming but not using your very good code.
Sorry to be a pain in the a... still.

Also the huge Timezone difference makes things very hard as well.
I am in Sunny Australia.

Tom

Last edited by Tom Duncan (2012-02-14 23:40:48)

ab · 2012-02-15 07:48:54

I've writen such a Delphi client.

See http://synopse.info/fossil/finfo?name=S … Client.dpr

/// receive SQLite3 results from JSON/SQL HTTP server
program JSONSQLClient;

{$APPTYPE CONSOLE}

uses
  SysUtils,
  SynCrtSock,
  SynCommons;

function Client(const SQL: RawUTF8): RawUTF8;
var Http: THttpClientSocket;
begin
  Http := OpenHttp('localhost','888');
  if Http<>nil then
  try
    Http.Post('root',SQL,TEXT_CONTENT_TYPE);
    result := Http.Content;
  finally
    Http.Free;
  end else
    result := '';
end;

begin
  writeln(Client('select * from People where LastName=''Schubert'''));
  readln;
end.

Very easy to follow, IMHO.

It just ask then display on the console the JSON result for the select * from People where LastName='Schubert' SQL statement.

Tom Duncan · 2012-02-15 09:46:11

Hey it works, thank you for that.
Now all I need to figure out (which I will. lol) is the Json part.
I assume this uses the Get with the json to get all the fields.
Thanks so much for that.

Tom

ab · 2012-02-15 10:32:12

Note that the returned JSON value is an array of objects:

[{object1},{object2}....]

So i you request only one row, you'll get an array with one object inside:

[{"field":"value","field2":102}]

and not just one object:

{"field":"value","field2":102}

It is perhaps what is difficult to parse on the android side.

Tom Duncan · 2012-02-15 11:09:06

will do a check on the Android and see what the results are from my xaamp server.
Also would I be correct in thinking that I can pass the json file and then get the results in an array.
I am just reading the pdf now.
Have made a new version which shows result in a memo, so will be easy to get the results, say into a grid to shaow all patients.
Then get the info for that patient after that.

Tom

ab · 2012-02-15 12:33:04

Tom Duncan wrote:

Also would I be correct in thinking that I can pass the json file and then get the results in an array.

Correct!

Tom Duncan · 2012-02-15 12:53:37

Could you give me a clue. I assume I need to create a database then pass the json data.
Is that correct.

Tom Duncan · 2012-02-15 14:05:17

As a thought. Has anyone developed a data set vcl component from you software?
If that were the case then I could pass the json data jinto the data set then use db aware controls
to input data.
That would save heaps of work.

Oh well, wishful thinking.

Tom

ab · 2012-02-15 15:00:33

There is the TDrawGrid publisher as used in SynDBExplorer and all the UI part of the framework.

1. Put a TDrawGrid on your form;
2. Associate a TSQLTable to it via a TSQLTableToGrid instance;
3. That's all.

See SynDBExplorerFrame.pas unit for sample code.

Tom Duncan · 2012-02-15 20:29:13

and now for the silly newbie question. How do I pass the json data into a table.

Tom

ab · 2012-02-15 20:43:32

See the SynDBExplorer sample code, and TSQLTableJSON details in the documentation!
RTFM!

Tom Duncan · 2012-02-16 03:41:19

thank you for that help.
If I don't figure it out this afternoon then I will try another company.
You wont hear from me again.
Tom

Tom Duncan · 2012-02-16 07:01:27

Just before I throw my computer over a bridge,
I have changed my mind just this once.
Have it sort up and going.
BTW your demo Client/server give the same displayed results.
Only problem is Text fields. VarChar fields are fine.
Here is the Json Dump using memo1.Lines.Add(UTF8ToString(fJSONBuffer));

[{"auto":1,"AutoNbr":"JAF12gjX","Pcode":1,"Pract":1,"Bill_To":22,"BillTo":"JAwmlWAL","BillName":"IERlcHQgb2YgVmV0ZXJhbnMgYWZmZmFpcnMg","Sex":"True","Dob":"1934-05-20","Title":"Mr","Name1":"QWxsYW4=","Name2":"","Sname":"Middleton","DspName":"Middleton, Mr Allan","Address":"MyBPcmlnaW4gQ2xvc2UNCkJyaXNiYW5lDQpRTEQgLCA0MDAxDQo=","Add1":"MyBPcmlnaW4gQ2xvc2U=","Add2":"QnJpc2JhbmU=","Add3":"UUxE","PostCode":"4001","Comment":"c2Rm","Hphone":"07 12345678","Wphone":null,"Fax":null,"Mphone":null,"Email":"tom@ctd.com.au","Mcare":null,"Dva":null,"Fund":null,"Abill_To":"False","Remind":"True","RemWhen":"2010-04-19","Notes":"PFRhYj4yOC8wMy8yMDA5Lg0KR1ROQ0QgLCBDYWxsb3VzIHJlbW92ZWQgZnJvbSBQTUEgIGFuZCBQQ0EgDQpoYWxsIG5haWxzIGNsZWFyZWQgDQo=","Doctor":null,"Ref_Doctor":"","Ref_Prov":"","Ref_Date":null,"History":"","RemSent":null,"SetArchive":null,"ShowBusy":null,"modded":null,"modded_when":null}]

and then using this to get fields... UTF8ToString(Table1.FieldValue(Table1.GetString(0,i) ,1))

I get this for the Address field.

MyBPcmlnaW4gQ2xvc2UNCkJyaXNiYW5lDQpRTEQgLCA0MDAxDQo=

It is this..
3 Origin Close
Brisbane
QLD , 4001

I assume this is in UTF8 type format.
How do I get back to normal Ascii?

Also opening the same Sqlite database in DBexplorer I get the same results for any text fields.
However is Sqlite Database Browser v2 all is fine.

Tom

Last edited by Tom Duncan (2012-02-16 07:34:29)

ab · 2012-02-16 07:52:27

I guess your columns was defined as BLOB.

I open plain SQLite3 files without any problem using SynDBExplorer.

Tom Duncan · 2012-02-16 08:54:49

These fields were created as Text fields, and show up in the database such.
I can put a copy of part of the database on my webpage if you like.
As I said it looks fine using a Sqlite Database manager.

Tom

Btw this same database displays all information correctly on a sqlite based application. (Not client/server)

Last edited by Tom Duncan (2012-02-16 09:32:47)

Tom Duncan · 2013-07-11 05:30:41

Well sorry for the huge delay.
I have been busy on other projects.
Tested your new sample 13 and as local host it works fine.
But I then set the Http := OpenHttp('192.168.1.139','888');
Which is my machine address.
On this machine all is fine, BUT on a remote machine it does not work.
I assume this is a firewall issue, so I gave outgoing rules for 888, still nogo.
Also ran the TestSQL3Register.dpr - still no go.
Ran both as Administrator.

Running out of ideas.
And no for the connection to Android as well.

Any thoughts.

Tom

ab · 2013-07-11 05:55:17

I just checked out running sample 13 client with an IP address over a network.
Works just fine for me.
Tested after compilation with Delphi 7 and Delphi XE4.

Ensure you are using the latest version of the framework from http://synopse.info/fossil/wiki?name=Get+the+source

About the firewall settings, we use high-performance http.sys as server so check perhaps at this level.
You need to add a rule to open the port, not the application itself, since http.sys works in OS kernel mode, not in userland.

Tom Duncan · 2013-07-11 08:32:57

Have the latest version.
Did the same setup and had the same problem
Have had a look at the firewall. and the port has been selected under TCP.
Will have another look later.
Also can I use another Dataset i.e. VolgaDB
It has the normal Dataset and SQL

Tom

Tom Duncan · 2013-07-11 11:28:50

Just made an inbound rule for 888.
Works like a charm thanks

Now will have a look at Datasets.

Tom

ab · 2013-07-11 12:03:31

I did not know the existence of VolgaDB.

It is not integrated by now, but could be easily done I suppose, as we did for other TDataSet providers.
See the SynDBDataset sub-folder in the source code repository.
SynDBBDE.pas is perhaps the easiest to start with.

Tom Duncan · 2013-07-11 12:13:05

Will have a look.
Volga is a simple and quick database that I purchased 10 years ago.
Now open source and still good for every-day work.
Would like to create a server using the databases since they are used by all the clients.
Then have the tablet up and going with Android.

Tom

ab · 2013-07-11 12:26:49

I was not able to find the volgadb open source web page...
Just the grid.

The version on Torry is not Unicode ready, so should not work with Delphi 2009 and up...

volgadb.com sounds just down.

Tom Duncan · 2013-07-11 13:02:51

Cannot find it either.
Will have another look in the morning.

Just one thing. The memo fields that I have saved as a Text field in SQlite don't show up in SynExplorer program.
Is this normal?
I could send a demo version to you if you like.

Tom

Tom Duncan · 2013-07-11 13:27:56

Just one more question.
How do I save data back to the sqlite.
Is this done via Jason again.
Sorry to be so basic about this.
Tom

ab · 2013-07-11 14:15:53

Memo fields should be available, as any text content.

For data update, see ORM layout of the JSON.
E.g. BATCH mode.

Tom Duncan · 2013-07-11 20:15:39

I was using one of my sqlite databases.
The fields are marked as text.
But when viewed in syn explorer the field is blank.
Also did a save of that table as CSV and got blank fields.
Will do some digging on batch.
Are their any demos which show this being done.
I imagined out tell the server that a json file is on the way etc.

Tom

ab · 2013-07-12 08:38:44

The fields are identified as BLOB when retrieved from SQLite3.
How was your database content created?

Tom Duncan · 2013-07-13 00:31:08

The database was created in Delphi using a Sqlite vcl.
Then a batch copy from the Volga database using Variant.
my Sqlite browser picks the Text fields up fine.

As a thought have you any real-world examples using a database and using Json to send and receive data from a Server Application?
i.e. Have a Grid of data then able to add/delete/modify data and then display the new data.

Tom

ab · 2013-07-13 08:56:53

Your SQLite3 content sounds pretty weird.
What is the CREATE TABLE statement used?

See "SQLite3/Samples/16 - Execute SQL via services", and other samples.

Tom Duncan · 2013-07-13 11:32:31

Will send you a snippet of the code in the morning. Sending this on my tablet.
And will have a look at all the samples again.
It is strange not using Tdatabase vcl's etc.
When I get this up and going correctly can I still use the data aware components.
With my current system I use a memory dataset then save the record to the database.

Tom

ab · 2013-07-13 12:25:01

Yes I agree this is a huge change in comparison with standard RAD/VCL design...

But worth it, IMHO...

Please ensure you get the main point of mORMot: it is not just about transmission of DB content using JSON, it is a mean of pure OOP programing, using business objects, not components nor SQL.
JSON is just one detail point of the implementation.
You may be able to use mORMot in a "RAD/component way", but you may loose much of its interest.

Tom Duncan · 2013-07-13 20:06:43

But does that mean that I do not use dbcontrols for text input and dbgrid output.
Do I then use edit boxes and string grid.
Now that would be a change.

Tom

ab · 2013-07-13 20:37:21

Just use LiveBindings instead.

mORMot Open Source

#1 2012-02-07 22:31:24

Use Sqlite in a client/server concurrent mode

#2 2012-02-08 06:45:05

Re: Use Sqlite in a client/server concurrent mode

#3 2012-02-08 07:20:51

Re: Use Sqlite in a client/server concurrent mode

#4 2012-02-08 20:29:17

Re: Use Sqlite in a client/server concurrent mode

#5 2012-02-09 08:24:51

Re: Use Sqlite in a client/server concurrent mode

#6 2012-02-11 12:01:04

Re: Use Sqlite in a client/server concurrent mode

#7 2012-02-12 00:58:21

Re: Use Sqlite in a client/server concurrent mode

#8 2012-02-12 08:20:42

Re: Use Sqlite in a client/server concurrent mode

#9 2012-02-12 08:47:42

Re: Use Sqlite in a client/server concurrent mode

#10 2012-02-13 02:28:06

Re: Use Sqlite in a client/server concurrent mode

#11 2012-02-13 14:11:42

Re: Use Sqlite in a client/server concurrent mode

#12 2012-02-13 23:28:21

Re: Use Sqlite in a client/server concurrent mode

#13 2012-02-13 23:29:42

Re: Use Sqlite in a client/server concurrent mode

#14 2012-02-14 08:03:37

Re: Use Sqlite in a client/server concurrent mode

#15 2012-02-14 10:18:35

Re: Use Sqlite in a client/server concurrent mode

#16 2012-02-14 13:21:31

Re: Use Sqlite in a client/server concurrent mode

#17 2012-02-14 14:02:09

Re: Use Sqlite in a client/server concurrent mode

#18 2012-02-14 23:39:30

Re: Use Sqlite in a client/server concurrent mode

#19 2012-02-15 07:48:54

Re: Use Sqlite in a client/server concurrent mode

#20 2012-02-15 09:46:11

Re: Use Sqlite in a client/server concurrent mode

#21 2012-02-15 10:32:12

Re: Use Sqlite in a client/server concurrent mode

#22 2012-02-15 11:09:06

Re: Use Sqlite in a client/server concurrent mode

#23 2012-02-15 12:33:04

Re: Use Sqlite in a client/server concurrent mode

#24 2012-02-15 12:53:37

Re: Use Sqlite in a client/server concurrent mode

#25 2012-02-15 14:05:17

Re: Use Sqlite in a client/server concurrent mode

#26 2012-02-15 15:00:33

Re: Use Sqlite in a client/server concurrent mode

#27 2012-02-15 20:29:13

Re: Use Sqlite in a client/server concurrent mode

#28 2012-02-15 20:43:32

Re: Use Sqlite in a client/server concurrent mode

#29 2012-02-16 03:41:19

Re: Use Sqlite in a client/server concurrent mode

#30 2012-02-16 07:01:27

Re: Use Sqlite in a client/server concurrent mode

#31 2012-02-16 07:52:27

Re: Use Sqlite in a client/server concurrent mode

#32 2012-02-16 08:54:49

Re: Use Sqlite in a client/server concurrent mode

#33 2013-07-11 05:30:41

Re: Use Sqlite in a client/server concurrent mode

#34 2013-07-11 05:55:17

Re: Use Sqlite in a client/server concurrent mode

#35 2013-07-11 08:32:57

Re: Use Sqlite in a client/server concurrent mode

#36 2013-07-11 11:28:50

Re: Use Sqlite in a client/server concurrent mode

#37 2013-07-11 12:03:31

Re: Use Sqlite in a client/server concurrent mode

#38 2013-07-11 12:13:05

Re: Use Sqlite in a client/server concurrent mode

#39 2013-07-11 12:26:49

Re: Use Sqlite in a client/server concurrent mode

#40 2013-07-11 13:02:51