Pages: 1

#1 2014-11-18 00:08:29

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,662
Website

HTTP remote access for SynDB SQL execution

The SynDBRemote.pas unit allows you to create database applications that perform SQL operations on a remote HTTP server, instead of a database server. You can create connections just like any other SynDB.pas database, but the transmission would take place over HTTP. As a result, no database client is to be deployed on the end user application: it will just use HTTP requests, even over Internet. You can use all the features of SynDB.pas classes, with the ease of one optimized HTTP connection.

See the corresponding documentation at
http://synopse.info/files/html/Synopse% … l#TITL_131

This is the forum thread for the following blog article:
http://blog.synopse.info/post/2014/11/1 … -execution

Offline

#2 2014-11-18 00:41:07

miab3
Member
From: Poland
Registered: 2014-10-01
Posts: 188

Re: HTTP remote access for SynDB SQL execution

@Arnaud

Could you write demo to test the performance of this solution?
Please.

Michal

Offline

#3 2014-11-18 09:07:00

itSDS
Member
From: Germany
Registered: 2014-04-24
Posts: 516

Re: HTTP remote access for SynDB SQL execution

Great Work Arnaud with this you made any supported SQL Database a single unique Network Interface smile Esspecially SQLite

Rad Studio 12.1 Santorini

Offline

#4 2014-11-18 10:26:31

itSDS
Member
From: Germany
Registered: 2014-04-24
Posts: 516

Re: HTTP remote access for SynDB SQL execution

Hi Arnoud,

is it possible to enhance SynDBExplorer to Connect to such a Remote Host ?

ATM i have to look at some tables on server without shutting it down.
So Remote DB access could solve the Problem.

Last edited by itSDS (2014-11-18 10:37:32)

Rad Studio 12.1 Santorini

Offline

#5 2014-11-18 11:22:00

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,662
Website

Re: HTTP remote access for SynDB SQL execution

@miab3
I have added SynRemoteDB connection to the "External DB performance" sample.

The results are pretty good.

Insertion speed
    	  		Direct	Batch	Trans	Batch Trans
SQLite3 (file full)	444	20564	86650	171191
SQLite3 (file off)	1951	81764	87246	183089
SQLite3 (file off exc)	23564	180173	90130	198294
SQLite3 (mem)		70266	218990	91590	232201
TObjectList (static)	279282	414696	268125	416077
TObjectList (virtual)	255375	397298	273987	414215
SQLite3 (ext full)	482	11893	86159	122943
SQLite3 (ext off)	2212	44185	95750	156631
SQLite3 (ext off exc)	28521	143151	94302	172634
SQLite3 (ext mem)	83008	199092	99722	205693
HTTP SQLite3 (off exc)	2165	36464	2079	38478

chart?chtt=Insertion+speed+%28rows%2Fsecond%29&chxl=1:|WinHTTP+SQLite3+%28off+exc%29|SQLite3+%28ext+mem%29|SQLite3+%28ext+off+exc%29|SQLite3+%28ext+off%29|SQLite3+%28ext+full%29|TObjectList+%28virtual%29|TObjectList+%28static%29|SQLite3+%28mem%29|SQLite3+%28file+off+exc%29|SQLite3+%28file+off%29|SQLite3+%28file+full%29&chxt=x,y&chbh=a&chs=600x500&cht=bhg&chco=3D7930,3D8930,309F30,40C355&chxr=0,0,416077&chds=0,416077,0,416077,0,416077,0,416077,0,416077,0,416077,0,416077,0,416077,0,416077,0,416077,0,416077&chd=t:444,1951,23564,70266,279282,255375,482,2212,28521,83008,2165|20564,81764,180173,218990,414696,397298,11893,44185,143151,199092,36464|86650,87246,90130,91590,268125,273987,86159,95750,94302,99722,2079|171191,183089,198294,232201,416077,414215,122943,156631,172634,205693,38478&chdl=Direct|Batch|Trans|Batch+Trans

Read speed
		 	By one	All Virtual	All Direct
SQLite3 (file full)	110854	482905	489572
SQLite3 (file off)	112228	443892	435843
SQLite3 (file off exc)	113076	472366	468779
SQLite3 (mem)		111799	485719	476598
TObjectList (static)	265646	802053	799105
TObjectList (virtual)	269774	381242	794659
SQLite3 (ext full)	118139	238469	482067
SQLite3 (ext off)	116441	229800	458295
SQLite3 (ext off exc)	115839	238891	483278
SQLite3 (ext mem)	119291	239142	442125
HTTP SQLite3 (off exc)	2198	209231	340460

chart?chtt=Read+speed+%28rows%2Fsecond%29&chxl=1:|WinHTTP+SQLite3+%28off+exc%29|SQLite3+%28ext+mem%29|SQLite3+%28ext+off+exc%29|SQLite3+%28ext+off%29|SQLite3+%28ext+full%29|TObjectList+%28virtual%29|TObjectList+%28static%29|SQLite3+%28mem%29|SQLite3+%28file+off+exc%29|SQLite3+%28file+off%29|SQLite3+%28file+full%29&chxt=x,y&chbh=a&chs=600x500&cht=bhg&chco=3D7930,3D8930,309F30,40C355&chxr=0,0,802053&chds=0,802053,0,802053,0,802053,0,802053,0,802053,0,802053,0,802053,0,802053,0,802053,0,802053,0,802053&chd=t:110854,112228,113076,111799,265646,269774,118139,116441,115839,119291,2198|482905,443892,472366,485719,802053,381242,238469,229800,238891,239142,209231|489572,435843,468779,476598,799105,794659,482067,458295,483278,442125,340460&chdl=By+one|All+Virtual|All+Direct

Of course, there was a single client for each request, so "By one" insertion and retrieval would scale when multiple clients are connected as once, due to how the http.sys server works.

Offline

#6 2014-11-18 12:33:45

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,662
Website

Re: HTTP remote access for SynDB SQL execution

itSDS wrote:

is it possible to enhance SynDBExplorer to Connect to such a Remote Host ?

Yes, this was what I had in mind.

Our SynDBExplorer tool is now able to server any remote SynDB connection, or connect to it via HTTP. It could be very handy, even for debugging purposes.

To serve an existing database, just connect to it as usual. Then click on the "HTTP Server" button below the table lists (on left side). You can tune the server properties (HTTP port, database name used for URI, user credentials), then click on the start button.

To connect to this remote connection, run another instance of SynDBExplorer. Create a new connection, using "Remote HTTP" as connection type, and set the other options with the values set on the server side, e.g. with the default values "localhost:8092" (replacing localhost with the server IP for an access over the network) for the server name, "syndbremote" for the database name, and "synopse" for both user name and password. You would be able to access the main server instance remotely, just as if the database was accessed via a regular client. If the server side database is SQLite3, you just change this local engine into a true client-server database - you may be amazed by the resulting performance.

See http://synopse.info/fossil/info/bed4c8bf9e
and the updated documentation.

Offline

#7 2014-11-18 12:49:12

miab3
Member
From: Poland
Registered: 2014-10-01
Posts: 188

Re: HTTP remote access for SynDB SQL execution

@ab,

It works. smile
And if you could add a remote HTTP-SQLite3 server, to which connects the SynDBExplorer? smile

ADD: Oh, it probably already added. (1.18.536) smile

Michal

Last edited by miab3 (2014-11-18 13:13:55)

Offline

#8 2014-11-18 15:16:47

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,662
Website

Re: HTTP remote access for SynDB SQL execution

Commented and answered on Google +:

Sam Shaw wrote:

What're the benefits from it compared with datasnap in short ? Thanks!

A. Bouchez wrote:

It has a totally diverse design and implementation, so it is like comparing oranges and apples.
But IMHO the benefits are that it is Open Source, lighter, using less bandwidth, faster in real use over Internet, scales better, is object-oriented and not RAD - for me it is a benefit smile - even if results could be exposed as TDataSet, allows array binding of parameters, allows late-binding of column values for result sets, works from Delphi 6 up to XE7, is Unicode even before Delphi 2009, has published regression tests, does not require DB.pas nor BDE/FireDAC/DBExpress (so works with Delphi Starter), and is compatible with FPC - so targets both Windows or Linux.
And that it is free, and that you do not have to pay for updates or fixes.
Main limitation in comparison to TDataSnap is that you can't directly push modifications of your TDataSet to the server, and that the Delphi client part is not cross-platform yet.
Once again, IMHO the full RESTful ORM/SOA mORMot is a better approach for new projects, but it has its benefits, even in conjuction with mORMot (for a legacy app using SQL on the client side, or to gives access to the remote server). For cross-platform clients, our ORM/SOA is a much better approach: do not put SQL in your mobile application, but use services, so that you would not need to re-validate and re-publish the app to the store for a small fix!
Of course, do not take my word for it, since I did it.

See https://plus.google.com/109645099475126 … TFB61BXtay

For the benchmark, if we use plain client sockets instead of WinHTTP API, we got:

Insertion
Socket SQLite3 (off exc)	7002	75251	8553	80550

Read
Socket SQLite3 (off exc)	8524	210260	387687

So it confirms that on localhost, plain socket API is faster.
But in real use, e.g. over the Internet,  WinHTTP API has been reported as more stable, so may be preferred on production.

Offline

#9 2014-11-18 15:49:14

miab3
Member
From: Poland
Registered: 2014-10-01
Posts: 188

Re: HTTP remote access for SynDB SQL execution

@ab,

Why are these your connections in SynDBExplorer to Oracle are so slow, and for some of the tables do not work at all?
Could you integrate ZEOS into SynDBExplorer?

Michal

Offline

#10 2014-11-18 16:08:35

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,662
Website

Re: HTTP remote access for SynDB SQL execution

Do you mean our direct Oracle layer?
I do not know why it is slow. I never experienced this.

Are you sure this is not when you create the first connection?
The first time it accessed a DB, it gets all table metadata information, and cache it on a local file.
This could be somewhat slow, on huge DB.
But the 2nd time, if you do not check "Update connection settings", connection should be immediate.

Honestly, we always used our direct SynDBOracle access for Oracle.
I do not see the point of using Zeos for Oracle, in the context of our ORM.

Zeos/ZDBC has been optionally integrated to SynDBExplorer.
See http://synopse.info/fossil/info/e44765fadf
The USEZEOS conditional shall be set.
You need to use the raw ZDBC URI as Server Name.

Offline

#11 2014-11-18 19:25:04

miab3
Member
From: Poland
Registered: 2014-10-01
Posts: 188

Re: HTTP remote access for SynDB SQL execution

@ab,

Thanks,
SynDBExplorer now working with ZEOS too(Firebird, Oracle).

Such a little thing:
In the case of "Remote HTTP" list of metadata(Table Name) is not cleaned and refreshed.
In general, the list of tables is probably unnecessarily cached in SynDBExplorer.config.

select * from ARTICLE
is slow in ZEOS-Oracle too. smile

Michal

Last edited by miab3 (2014-11-18 19:33:00)

Offline

#12 2014-11-18 22:42:32

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,662
Website

Re: HTTP remote access for SynDB SQL execution

It caches the whole foreign keys and indexes information.
Perhaps this is over-optimization...

Offline

#13 2014-11-18 23:20:25

miab3
Member
From: Poland
Registered: 2014-10-01
Posts: 188

Re: HTTP remote access for SynDB SQL execution

@ab,

But the "Remote HTTP" can be join in the various databases on the SynDBExplorer-HTTP-server.
Besides, the database can also be changed between restarts SynDBExplorer.

Could you add (button) cache(Table List) refresh?

Michal

Last edited by miab3 (2014-11-19 09:08:59)

Offline

#14 2014-11-19 17:46:48

swierzbicki
Member
Registered: 2014-11-19
Posts: 28

Re: HTTP remote access for SynDB SQL execution

Hello,

I've followed your blog's instruction but I can't get my client connecting on local server !
I've registered my URL thanks to sample 04 - HTTP Client - Server

I'm getting this exception :

Exception 'first chance' à $7553812F. Classe d'exception ESQLDBRemote avec un message 'TSQLDBWinHTTPConnectionProperties.ProcessMessage: Error 503 from http://localhost:8080/'. Processus Client.exe (2948)

Am I missing something ?

Last edited by swierzbicki (2014-11-19 17:56:45)

Offline

#15 2014-11-19 20:55:45

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,662
Website

Re: HTTP remote access for SynDB SQL execution

Are you sure you defined the database name as expected on both sides?

Offline

#16 2014-11-19 21:06:46

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,662
Website

Re: HTTP remote access for SynDB SQL execution

miab3 wrote:

Could you add (button) cache(Table List) refresh?

I removed the whole cache feature for a remote client connection.
See http://synopse.info/fossil/info/ab6c8515b7

Offline

#17 2014-11-20 09:59:12

miab3
Member
From: Poland
Registered: 2014-10-01
Posts: 188

Re: HTTP remote access for SynDB SQL execution

@ab,

Thanks.
Now better, although it was useful to refresh the (TableName) list without restarting the application.

Michal

Offline

#18 2014-11-20 10:10:13

swierzbicki
Member
Registered: 2014-11-19
Posts: 28

Re: HTTP remote access for SynDB SQL execution

@ab : that was my problem ! Thank you

Offline

#19 2014-11-21 00:00:19

eraldo
Member
From: Brasil
Registered: 2010-07-22
Posts: 69
Website

Re: HTTP remote access for SynDB SQL execution

ab,

How can I run multiple commands (Drop, Create, Insert) within the same transaction?
Just as in the example:
         TSQLDBSQLite3Connection (MainConnection) .StartTransaction;
         TSQLDBSQLite3Connection (MainConnection) .DB.ExecuteAll (script.Text);

Offline

#20 2014-11-21 10:45:09

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,662
Website

Re: HTTP remote access for SynDB SQL execution

Transactions are not properly implemented yet.
Stay tuned to the channel !
smile

Offline

#21 2014-11-21 10:57:43

eraldo
Member
From: Brasil
Registered: 2010-07-22
Posts: 69
Website

Re: HTTP remote access for SynDB SQL execution

Thank you,

We will await with great interest!

Offline

#22 2014-11-21 18:52:19

miab3
Member
From: Poland
Registered: 2014-10-01
Posts: 188

Re: HTTP remote access for SynDB SQL execution

@ab,

I check.
SynDBExplorer using ZEOS works with:

oracle:
firebird-2.5:
mysql:
postgresql:
sqlite:
FreeTDS_MsSQL>=2005:
ado: (jet,sqlncli)

And work as HTTP server(Remote HTTP) those databases. big_smile

mORMot 1.18.552;
ZEOS 7.2.0-beta r3501 http://zeoslib.sourceforge.net/viewtopi … 354#p35354
Delphi XE2-32.

Michal

Last edited by miab3 (2014-11-21 19:24:22)

Offline

#23 2014-11-21 19:19:00

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,662
Website

Re: HTTP remote access for SynDB SQL execution

@miab3
Good news!
big_smile

Perhaps we may put some more ZDBC connection URI samples, in the documentation, to help others using ZDBC.
What do you think?
See http://synopse.info/files/html/Synopse% … #TITLE_181

Offline

#24 2014-11-21 21:15:26

miab3
Member
From: Poland
Registered: 2014-10-01
Posts: 188

Re: HTTP remote access for SynDB SQL execution

@ab,

firebird-2.5:
postgresql:
mysql://SERVER
database
username
password

FreeTDS_MsSQL>=2005://MSSERVER
database
username
password

oracle:SERVER

username
password

sqlite:Full Path Filename

ado:Connection String

Useful will be yet  Edit for LibraryLocation smile

Michal

Offline

#25 2014-11-22 09:36:16

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,662
Website

Re: HTTP remote access for SynDB SQL execution

Thanks Michal.
But I'm not sure we would be able to include info like that directly.

BTW here are some updated benchmark information.
In fact, this is the test I run during the BeDelphi event.

Insertion speed
 	Direct	Batch	Trans	Batch Trans
SQLite3 (file full)	328	28294	86515	180622
SQLite3 (file off)	2045	55146	90103	204649
SQLite3 (file off exc)	24962	196974	90823	207168
SQLite3 (mem)		77068	236753	92489	235327
TObjectList (static)	272583	428082	279610	417048
TObjectList (virtual)	267608	417048	261738	417850
SQLite3 (ext full)	505	12656	92395	162406
SQLite3 (ext off)	2241	47821	94927	179108
SQLite3 (ext off exc)	33816	159184	99275	183418
SQLite3 (ext mem)	85290	210837	100310	207168
WinHTTP SQLite3		1928	31558	2281	35596
Socket SQLite3		7411	77516	9068	83820
ZEOS SQlite3		430	4296	35166	80484
FireDAC SQlite3		16447	28851	35092	93287
ZEOS Firebird		10168	57718	21549	60522
FireDAC Firebird	18885	52176	19820	52487
MSSQL2012 local		4150	38290	12280	51154
ODBC MSSQL2012		4036	4691	3077	7253
FireDAC MSSQL2012	3688	5841	9561	40229
ZEOS PostgreSQL		2863	30644	6537	44608
ODBC PostgreSQL		2621	29277	3378	30214
FireDAC PostgreSQL	1452	31307	5350	31162

chart?chtt=Insertion+speed+%28rows%2Fsecond%29&chxl=1:|FireDAC+PostgreSQL|ODBC+PostgreSQL|ZEOS+PostgreSQL|FireDAC+MSSQL2012|ODBC+MSSQL2012|MSSQL2012+local|FireDAC+Firebird|ZEOS+Firebird|FireDAC+SQlite3|ZEOS+SQlite3|Socket+SQLite3+%28off+exc%29|WinHTTP+SQLite3+%28off+exc%29|SQLite3+%28ext+mem%29|SQLite3+%28ext+off+exc%29|SQLite3+%28ext+off%29|SQLite3+%28ext+full%29|TObjectList+%28virtual%29|TObjectList+%28static%29|SQLite3+%28mem%29|SQLite3+%28file+off+exc%29|SQLite3+%28file+off%29|SQLite3+%28file+full%29&chxt=x,y&chbh=a&chs=600x500&cht=bhg&chco=3D7930,3D8930,309F30,40C355&chxr=0,0,428082&chds=0,428082,0,428082,0,428082,0,428082,0,428082,0,428082,0,428082,0,428082,0,428082,0,428082,0,428082,0,428082,0,428082,0,428082,0,428082,0,428082,0,428082,0,428082,0,428082,0,428082,0,428082,0,428082&chd=t:328,2045,24962,77068,272583,267608,505,2241,33816,85290,1928,7411,430,16447,10168,18885,4150,4036,3688,2863,2621,1452|28294,55146,196974,236753,428082,417048,12656,47821,159184,210837,31558,77516,4296,28851,57718,52176,38290,4691,5841,30644,29277,31307|86515,90103,90823,92489,279610,261738,92395,94927,99275,100310,2281,9068,35166,35092,21549,19820,12280,3077,9561,6537,3378,5350|180622,204649,207168,235327,417048,417850,162406,179108,183418,207168,35596,83820,80484,93287,60522,52487,51154,7253,40229,44608,30214,31162&chdl=Direct|Batch|Trans|Batch+Trans

Read speed
 	By one	All Virtual	All Direct
SQLite3 (file full)	112410	491980	493048
SQLite3 (file off)	110304	486902	491352
SQLite3 (file off exc)	111051	472902	488758
SQLite3 (mem)		113558	486334	456996
TObjectList (static)	265378	790763	783944
TObjectList (virtual)	268961	382204	762078
SQLite3 (ext full)	117486	236428	472545
SQLite3 (ext off)	116357	242742	480122
SQLite3 (ext off exc)	115061	240882	480815
SQLite3 (ext mem)	120767	245194	471698
WinHTTP SQLite3		2141	203790	338272
Socket SQLite3		9200	208567	381010
ZEOS SQlite3		35253	178935	275953
FireDAC SQlite3		8020	76968	98666
ZEOS Firebird		20770	82043	110901
FireDAC Firebird	2263	56851	68761
MSSQL2012 local		8643	187821	363795
ODBC MSSQL2012		11316	120691	202856
FireDAC MSSQL2012	3786	92448	137703
ZEOS PostgreSQL		5764	133024	208498
ODBC PostgreSQL		6552	73012	99427
FireDAC PostgreSQL	1676	57987	72543

chart?chtt=Read+speed+%28rows%2Fsecond%29&chxl=1:|FireDAC+PostgreSQL|ODBC+PostgreSQL|ZEOS+PostgreSQL|FireDAC+MSSQL2012|ODBC+MSSQL2012|MSSQL2012+local|FireDAC+Firebird|ZEOS+Firebird|FireDAC+SQlite3|ZEOS+SQlite3|Socket+SQLite3+%28off+exc%29|WinHTTP+SQLite3+%28off+exc%29|SQLite3+%28ext+mem%29|SQLite3+%28ext+off+exc%29|SQLite3+%28ext+off%29|SQLite3+%28ext+full%29|TObjectList+%28virtual%29|TObjectList+%28static%29|SQLite3+%28mem%29|SQLite3+%28file+off+exc%29|SQLite3+%28file+off%29|SQLite3+%28file+full%29&chxt=x,y&chbh=a&chs=600x500&cht=bhg&chco=3D7930,3D8930,309F30,40C355&chxr=0,0,790763&chds=0,790763,0,790763,0,790763,0,790763,0,790763,0,790763,0,790763,0,790763,0,790763,0,790763,0,790763,0,790763,0,790763,0,790763,0,790763,0,790763,0,790763,0,790763,0,790763,0,790763,0,790763,0,790763&chd=t:112410,110304,111051,113558,265378,268961,117486,116357,115061,120767,2141,9200,35253,8020,20770,2263,8643,11316,3786,5764,6552,1676|491980,486902,472902,486334,790763,382204,236428,242742,240882,245194,203790,208567,178935,76968,82043,56851,187821,120691,92448,133024,73012,57987|493048,491352,488758,456996,783944,762078,472545,480122,480815,471698,338272,381010,275953,98666,110901,68761,363795,202856,137703,208498,99427,72543&chdl=By+one|All+Virtual|All+Direct

Offline

#26 2014-11-22 18:05:00

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,662
Website

Re: HTTP remote access for SynDB SQL execution

ab wrote:

Transactions are not properly implemented yet.
Stay tuned to the channel !
smile

SynDBRemote now supports concurrent transactions (via a simple blocking mode).

But there is no "ExecuteAll" feature.
Perhaps we may add some kind of "batch" mode at SynDB level.
For remote connections, it would help a lot about the performance, e.g. with multi-insertion.

Offline

#27 2014-11-23 14:50:50

miab3
Member
From: Poland
Registered: 2014-10-01
Posts: 188

Re: HTTP remote access for SynDB SQL execution

@ab,

ZEOS MySQL reading By one, was corrected a few days ago by Michael in svn ZEOS 7.2:
http://svn.code.sf.net/p/zeoslib/code-0 … sting-7.2/
Now result is approx. 10 thousand per sec.
Similar to PostgreSQL or Oracle connected locally by ZEOS.

Michal

Last edited by miab3 (2014-11-24 10:41:01)

Offline

#28 2014-11-24 12:41:12

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,662
Website

Re: HTTP remote access for SynDB SQL execution

Great news!
Michael is doing very good work.
smile

Offline

#29 2014-11-26 08:03:53

edwinsn
Member
Registered: 2010-07-02
Posts: 1,218

Re: HTTP remote access for SynDB SQL execution

Hi Arnaud, so even without transaction support, with this remote SQL execution feature, you just made SQLite a real multi-client database server, at least for Delphi programmers smile

Delphi XE4 Pro on Windows 7 64bit.
Lazarus trunk built with fpcupdelux on Windows with cross-compile for Linux 64bit.

Offline

#30 2014-11-26 08:16:41

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,662
Website

Re: HTTP remote access for SynDB SQL execution

And transactions have been implemented now...
smile

Offline

Pages: 1

Board footer

Powered by FluxBB