Results 1 to 13 of 13

Thread: [RESOLVED] How to write an AppServer for remote SQLite-DB (cloud database)?

  1. #1

    Thread Starter
    PowerPoster
    Join Date
    Sep 2012
    Posts
    2,083

    Resolved [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.

  2. #2
    gibra
    Guest

    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.

  3. #3
    Hyperactive Member
    Join Date
    Jul 2013
    Posts
    400

    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

  4. #4
    Hyperactive Member
    Join Date
    Jul 2013
    Posts
    400

    Re: How to write an AppServer for remote SQLite-DB (cloud database)?

    double post, sorry.
    Carlos

  5. #5
    PowerPoster
    Join Date
    Aug 2010
    Location
    Canada
    Posts
    2,412

    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?

  6. #6
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: How to write an AppServer for remote SQLite-DB (cloud database)?

    Quote Originally Posted by gibra View Post
    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.

  7. #7
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,046

    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

  8. #8

    Thread Starter
    PowerPoster
    Join Date
    Sep 2012
    Posts
    2,083

    Re: How to write an AppServer for remote SQLite-DB (cloud database)?

    Quote Originally Posted by gibra View Post
    Caution, SQLite isn 't for multiusers.
    Read documentation related in SQLite web site.
    gibra, thanks for your reply.

    Quote Originally Posted by Carlos Rocha View Post
    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.

    Quote Originally Posted by ChrisE View Post
    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.

    Quote Originally Posted by jpbro View Post
    @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.

  9. #9

    Thread Starter
    PowerPoster
    Join Date
    Sep 2012
    Posts
    2,083

    Re: How to write an AppServer for remote SQLite-DB (cloud database)?

    Quote Originally Posted by Schmidt View Post
    @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)
    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.

    Quote Originally Posted by Schmidt View Post
    @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.

  10. #10

    Thread Starter
    PowerPoster
    Join Date
    Sep 2012
    Posts
    2,083

    Re: How to write an AppServer for remote SQLite-DB (cloud database)?

    Re: COM Library for sending mails via SMTP?
    Quote Originally Posted by Schmidt View Post
    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.

  11. #11
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,046

    Re: How to write an AppServer for remote SQLite-DB (cloud database)?

    Quote Originally Posted by dreammanor View Post
    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

  12. #12
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: How to write an AppServer for remote SQLite-DB (cloud database)?

    Quote Originally Posted by ChrisE View Post
    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

  13. #13
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,046

    Re: How to write an AppServer for remote SQLite-DB (cloud database)?

    Quote Originally Posted by Schmidt View Post


    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
  •  



Click Here to Expand Forum to Full Width