-
Jul 17th, 2017, 07:27 AM
#1
Thread Starter
PowerPoster
[RESOLVED] How to write an AppServer for remote SQLite-DB (cloud database)?
I would like to write a cloud account system (cloud user login system). The database will be placed on the cloud server (hosting server). Although it is a good choice to use SQLServer Express or MySQL as a cloud database, I prefer SQLite-DB, which has better performance and better portability..
But SQLite-DB is a local file database, don't have the ability to write concurrently, I need to write an AppServer for it. I searched on the Internet for a long time, didn't find a similar example.
At present I have two options (hope someone could make more and better choices):
(1) WinSock
(2) ASP + Http
I have never developed a similar system, if someone could give some advice or a demo, I would be grateful.
Last edited by dreammanor; Jul 17th, 2017 at 07:39 AM.
-
Jul 17th, 2017, 03:47 PM
#2
Re: How to write an AppServer for remote SQLite-DB (cloud database)?
Caution, SQLite isn 't for multiusers.
Read documentation related in SQLite web site.
Last edited by gibra; Jul 18th, 2017 at 02:11 AM.
-
Jul 17th, 2017, 05:44 PM
#3
Hyperactive Member
Re: How to write an AppServer for remote SQLite-DB (cloud database)?
I believe Olaf doesn't mind if I post the link
Last edited by Shaggy Hiker; Jul 18th, 2017 at 09:33 AM.
Reason: Link contained compiled binaries.
Carlos
-
Jul 17th, 2017, 05:45 PM
#4
Hyperactive Member
Re: How to write an AppServer for remote SQLite-DB (cloud database)?
-
Jul 17th, 2017, 10:00 PM
#5
Re: How to write an AppServer for remote SQLite-DB (cloud database)?
@dreammanor - if you will humour me for a minute: before you decide on one database vs. another, or one language/framework/library/anything else vs. another (and before we dive into all of the arguments that those kinds of choices entail) - can you please describe what you expect from your cloud user login system? What is it trying to do? What kind of inputs might it take, and what kind of outputs do you expect?
-
Jul 18th, 2017, 03:59 AM
#6
Re: How to write an AppServer for remote SQLite-DB (cloud database)?
Originally Posted by gibra
Caution, SQLite isn 't for multiusers.
Read documentation related in SQLite web site.
As general as it stands there, your statement is wrong (the warnings you read, are for using SQLite on a Network-Share, which is not a "Server-App").
Here's extracts from https://sqlite.org/whentouse.html
Websites
SQLite works great as the database engine for most low to medium traffic websites (which is to say, most websites)...
The SQLite website uses SQLite itself, of course, and as of this writing (2015) it handles about 400K to 500K HTTP requests per day, about 15-20% of which are dynamic pages touching the database. Dynamic content uses about 200 SQL statements per webpage. This setup runs on a single VM that shares a physical server with 23 others and yet still keeps the load average below 0.1 most of the time.
Server-side database
Systems designers report success using SQLite as a data store on server applications running in the datacenter, ...
With this pattern, the overall system is still client/server: clients send requests to the server and get back replies over the network...
SQLite (like JET) is a "serverless" Dll-based DB-Engine - and delivered as only that (as a Library, to run InProcess in a given App).
Nevertheless these Dll-based engines have to support Locking and Concurrency-mechanisms,
when the same DB is accessed from either multiple threads or multiple Processes on a machine,
to not corrupt the DB-File (which in most cases should reside on a local Disk, on the same machine).
Since these Concurrency-measures are built into these Dll-based-engines, one can easily write
ones own DBServer-App(Process) around it, which then has to support appropriate communication-channels
(usually sockets), to allow for multiple concurrently working clients without problems.
The SQLite-Dll-engine does support multiple, parallel Readers and single Writers currently - but it
does not come with an officially supported "DB-Server-Executable" - so you'll have to use (or write) your own AppServer-Layer.
The SQLite-WebSite (as well as the SQLite-CodeRepository, which is based on Fossil: https://www.fossil-scm.org),
both use SQLite in the BackEnd, delivering Data and processing millions of Site-Requests per week.
@Carlos
Thanks - and here's a somwhat more current article from the CodeBank, which explains RC5-RPC-usage in more detail:
http://www.vbforums.com/showthread.p...ement-per-RC5)
@dreammanor
I'm posting the IIS/ASP-article soon (have beautified and commented a bit more, and also split the resulting
"Client/Server"-RPC-Demo into 5 Steps (into 5 separately usable Project-Folders) starting with a very simple scenario -
and then working my way up to the App you see in the ScreenShots below:
SQLite under Stress, working behind the IIS as the AppServer-Layer (serving a Recordset with 830 Records on 2 Columns in a Loop):
Same thing for ADO/JET under Stress (using the same NWind-DataBase and the same Query):
As you can see in the Form-Caption, SQLite is able to deliver already about 3 times the Requests-per-second as the JET-Engine -
when a single Client-Process is used.
As for "how does it scale behind the IIS, when more than a single Client-Process does this kind of Stressing"?
I'm not going to post more ScreenShot-Clutter for that, only the Results:
- JET is not scaling at all (on 3 parallel stressing Client-Processes, the throughput is about 25 Requests per second, about 75 Req/sec in total).
- SQLite shows, that the SQLite-devs were not lying about "concurrent-parallel reads",
. because it achieves about 150Req/sec on each Client-App, giving a total of about 450 Req/sec (served by the IIS)
Although the scaling-factor is not exactly "the the ideal, theoretical 3" in the case of SQLite, it is significantly higher
(more than twice as much) as the throughput with a single StressClient.
So now you can make a few calculations yourself with these Values (which were measured on a mid-level Intel-CPU,
which had only two real physical CPU-Cores (the other two are due to Intel-HyperThreading and known to not deliver
the same results as a "true QuadCore-CPU").
450 Req/sec with SQLite (about 6 times as much as the JET-Engine behind IIS) means:
- a mid-level powered ServerLayer can deliver about 40Mio SQL-based midsized ResultSets per day to your concurrent Clients.
HTH (and as said, the article will come soon)
Olaf
Last edited by Schmidt; Jul 18th, 2017 at 04:17 AM.
-
Jul 18th, 2017, 04:50 AM
#7
Re: How to write an AppServer for remote SQLite-DB (cloud database)?
Hi Olaf,
I have never used SQLite, the Times look impressiv.
could you please state the ADO/Jet connection which you used to process this Query
regards
Chris
-
Jul 18th, 2017, 02:04 PM
#8
Thread Starter
PowerPoster
Re: How to write an AppServer for remote SQLite-DB (cloud database)?
Originally Posted by gibra
Caution, SQLite isn 't for multiusers.
Read documentation related in SQLite web site.
gibra, thanks for your reply.
Originally Posted by Carlos Rocha
I believe Olaf doesn't mind if I post the link
Carlos Rocha, the link you posted is very useful to me. I have downloaded the file before the link was deleted by the moderator. Thank you very much.
Originally Posted by ChrisE
Hi Olaf,
I have never used SQLite, the Times look impressiv.
could you please state the ADO/Jet connection which you used to process this Query
regards
Chris
There are many examples of SQLite-DB operated by vbRichClient on the CodeBank.
Originally Posted by jpbro
@dreammanor - if you will humour me for a minute: before you decide on one database vs. another, or one language/framework/library/anything else vs. another (and before we dive into all of the arguments that those kinds of choices entail) - can you please describe what you expect from your cloud user login system? What is it trying to do? What kind of inputs might it take, and what kind of outputs do you expect?
jpbro, I am now preparing for the development of IM which I have been trying to do for many years, cloud user login system is an important module of IM. When the IM is complete, I will use VB6 to develop a series of lightweight, small desktop software, such as inventory system (for small shops), personal financial system, data analysis system and so on. All of these software are free, and use the cloud user login system as a unified login module.
In addition to recording some basic information of the users, the cloud user login system also need to deal with "email-verification" or "mobile phone verification" when a new user is registering.
Last edited by dreammanor; Jul 18th, 2017 at 08:22 PM.
-
Jul 18th, 2017, 02:08 PM
#9
Thread Starter
PowerPoster
Re: How to write an AppServer for remote SQLite-DB (cloud database)?
Originally Posted by Schmidt
Olaf, I have tested all of your RPC-Demos today, except for NewAuthScheme. All RPC-Demos are running successfully. The RPC of vbRichClient is really amazing. Thank you very much indeed.
Originally Posted by Schmidt
@dreammanor
I'm posting the IIS/ASP-article soon (have beautified and commented a bit more, and also split the resulting
"Client/Server"-RPC-Demo into 5 Steps (into 5 separately usable Project-Folders) starting with a very simple scenario -
and then working my way up to the App you see in the ScreenShots below:
SQLite under Stress, working behind the IIS as the AppServer-Layer (serving a Recordset with 830 Records on 2 Columns in a Loop):
Same thing for ADO/JET under Stress (using the same NWind-DataBase and the same Query):
As you can see in the Form-Caption, SQLite is able to deliver already about 3 times the Requests-per-second as the JET-Engine -
when a single Client-Process is used.
As for "how does it scale behind the IIS, when more than a single Client-Process does this kind of Stressing"?
I'm not going to post more ScreenShot-Clutter for that, only the Results:
- JET is not scaling at all (on 3 parallel stressing Client-Processes, the throughput is about 25 Requests per second, about 75 Req/sec in total).
- SQLite shows, that the SQLite-devs were not lying about "concurrent-parallel reads",
. because it achieves about 150Req/sec on each Client-App, giving a total of about 450 Req/sec (served by the IIS)
Although the scaling-factor is not exactly "the the ideal, theoretical 3" in the case of SQLite, it is significantly higher
(more than twice as much) as the throughput with a single StressClient.
So now you can make a few calculations yourself with these Values (which were measured on a mid-level Intel-CPU,
which had only two real physical CPU-Cores (the other two are due to Intel-HyperThreading and known to not deliver
the same results as a "true QuadCore-CPU").
450 Req/sec with SQLite (about 6 times as much as the JET-Engine behind IIS) means:
- a mid-level powered ServerLayer can deliver about 40Mio SQL-based midsized ResultSets per day to your concurrent Clients.
HTH (and as said, the article will come soon)
Olaf
Your information greatly improved my confidence in the development of IM app. I think I chose SQLite-DB is extremely correct. Thank you for writing such a great software - vbRichClient.
Last edited by dreammanor; Jul 18th, 2017 at 08:27 PM.
-
Jul 18th, 2017, 02:12 PM
#10
Thread Starter
PowerPoster
Re: How to write an AppServer for remote SQLite-DB (cloud database)?
Re: COM Library for sending mails via SMTP?
Originally Posted by Schmidt
At work we're using the "Dart-Dlls" (former PowerTCP-COMponents) for all kind of Mailing-stuff -
they still offer bundles with COM/ActiveX-support:
http://www.dart.com/mail-activex-email-library-api.aspx
I agree with jpbro, that "writing your own, based on plain winsock-APIs" would be a huge task these days (with all the SSL/TLS-stuff now involved).
But since you mentioned the RC5-(socket-based) Communication-Classes (which BTW don't support standard-Mail-protocols out of the box):
Do you plan to use Mail-Protocols only as "just another transport-vehicle"?
(to ensure "transfers to a Web-Host" in conjunction with your planned "Instant-Messenger-App").
If yes, there are far better ways to approach that.
Olaf
"far better ways " means RPC of vbRichClient?
Last edited by dreammanor; Jul 18th, 2017 at 02:21 PM.
-
Jul 19th, 2017, 04:07 AM
#11
Re: How to write an AppServer for remote SQLite-DB (cloud database)?
Originally Posted by dreammanor
There are many examples of SQLite-DB operated by vbRichClient on the CodeBank.
I know that there are samples, my question was another
but still thanks for your time responding
regards
Chris
-
Jul 19th, 2017, 06:46 AM
#12
Re: How to write an AppServer for remote SQLite-DB (cloud database)?
Originally Posted by ChrisE
I know that there are samples, my question was another ...
I'm not really sure, what your question was, but in case you meant the Connection-String, it was a plain and simple Std-String:
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("/data/NWind.mdb")
If there's any Cnn-Options which influence concurrent behaviour, I'd like to know about them of course.
The ADO-Rs' were selected as openStatic with a ClientCursor at the Serverside -
then the ActiveConnection was severed from the Rs, followed by serializing the Rs to a ByteArray
(using ADOs Binary, not XML-mode) - the ByteArray then compressed - and sent back per Response.BinaryWrite.
Olaf
-
Jul 19th, 2017, 07:14 AM
#13
Re: How to write an AppServer for remote SQLite-DB (cloud database)?
Originally Posted by Schmidt
The ADO-Rs' were selected as openStatic with a ClientCursor at the Serverside -
then the ActiveConnection was severed from the Rs, followed by serializing the Rs to a ByteArray
(using ADOs Binary, not XML-mode) - the ByteArray then compressed - and sent back per Response.BinaryWrite.
Olaf
thats what I wanted to know, have you by any chance also tested this with UDT
sent in a single String.
sorry if I am annoying with my questions
regards
Chris
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|