Introducing dwsDatabase

dbDWScript now has database support classes built-in, these are based on a thin, interface-based layer, and can be used Delphi-side as well to get automatic memory management for simple DB access.

It currently supports mORMot SynDB and Universal InterBase, meaning it gets high performance native connectivity to SQLite, FireBird, Oracle, ODBC and OleDB (MySQL, MS SQLServer, MS Jet, AS400…).

Usage

Usage is very simple, for instance to connect to a local SQLite database and print two fields for a table:

var db := DataBase.Create('SQLite', ['d:\db\base.sql3']);
var query := db.Query('select fld1, fld2 from mytable where fld3=?', ['filter']);

while query.Step do
   PrintLn(query.AsString(0)+', '+query.AsString(1));
query.Close; // optional, only required if you need it closed ASAP

and to perform several queries in a transaction

db.BeginTransaction;
db.Exec('delete from mytable');
db.Exec('insert into mytable (fld1, fld3) values (?, ?)', ['hello', 'world']);
db.Commit;

The DataSet class supports classic EOF/Next iteration as well as the simpler Step iteration as in the previous example.

There are also JSON generation helpers, you can get a whole data set or a single record as JSON. This makes building ajax requests or even database “middle-ware” services simple.

Supporting other DB connectivity layers is quite simple, as you’ll see if you look in the source. SynDB & UIB were picked initially because together they offer high coverage of the usual suspects, and bring what are among, and maybe “the”, best in class performance and stability for Delphi DB connectivity these days.

Exemple: minimal web service

Those classes will be (are) used in leveraging the new “DWScript-returns-to-its-web-roots” Web Server (based on Synopse server). For instance in the DWS WebServer demo, you can make a minimal database “middle-ware” service with Windows domain authentication with just the following code:

case WebRequest.Authentication of
   WebAuthentication.None : 
      WebResponse.RequestAuthentication(WebAuthentication.Negotiate);
   WebAuthentication.NTLM .. WebAuthentication.Kerberos : 
      Print(DataBase.Create('UIB', ['dbServer:d:\db.fdb', 'login', 'password'])
                    .Query(WebRequest.QueryString)
                    .StringifyAll);
else
   WebResponse.StatusCode := 401;
end;

The above code will expect http requests with the sql as query string (ie. after the ‘?’ in the url). If the connection isn’t authenticated, it’ll ask for authentication. Note that Windows domain Single Sign On is supported by Chrome, Internet Explorer and FireFox (if you enabled it).

The query is run against a FireBird database hosted on another server, and the result returned as JSON. If the query fails for a reason or another, the client will get automatically get a 500 error code with the exception or error message. Oh, and the reply is compressed automatically if it’s larger than a couple hundred bytes.

And if you want to only serve on a secure connection, just add a check for WebRequest.Security, and you can then safely use extra or alternative authorization tokens or credentials.

6 thoughts on “Introducing dwsDatabase

  1. “The above code will expect http requests with the sql as query string (ie. after the ‘?’ in the url).”

    I take this is just a stupid example, otherwise ouch, hello SQL Injection 🙂

  2. Stefan Glienke :

    I take this is just a stupid example, otherwise ouch, hello SQL Injection :)

    Wanted a short snippet! Though technically, with the above code, you don’t need to fear SQL injection exploits since you’re allowing any SQL to begin with 😉

    That said, coupled with https and some private token mechanism, this will be just as secure (if not more) for a native client as using any other database remoting protocol that allows running arbitrary queries, as you get wire encryption (https), domain authentication and credentials (private token).

  3. Not bad! Are there any plans to support POST-style and Form-encoded parameters anytime soon? Query strings are nice, but they’re of somewhat limited usefulness, especially when you want to send more than a few KB of data to the server.

  4. @Mason Wheeler
    Post (and other http methods) are supported, and you can work with posted JSON content data easily (which is enough with HTML5 clients, jQuery/Ajax, etc.). What’s missing is the content parser for form-url-encoded and multipart mime, as in classic HTML form posts or file uploads. I’ve got a parser for those but it’s proprietary, so what’s needed is a good open-source one coming with a compatible license. I suppose the one in Indy could be used, but I’ve no idea of efficient it is or if it could be isolated easily from the rest of Indy.

  5. @Eric
    I’ll look at that, then. Indy uses a compatible license, and any efficiency concerns at this stage would be minimal compared to the enormous level of overhead removed by switching from Indy’s HTTP server to the HTTP.SYS architecture. So the real issue is how easy it is to isolate.

    Get the functionality first, make sure it works, *then* worry about making it fast. 🙂

  6. @Mason Wheeler
    I agree that “premature optimization is the root of all evil” , but my experiment about speed is that sometimes you have to change the general algorithm to make it really faster…
    For a piece of code like low-level multi-part encoding, I suspect that the Indy implementation is not the best candidate to start with.
    AFAIR Synapse (from Ararat) has one (I used it years ago), and there are others around.

    By the way, if you try to follow SOLID principles, switching from one server to another is easy.
    For instance, in mORMot we have two HTTP servers, totally diverse (one WinSock-based, the other on http.sys – DWS server is a fork in the 2nd). You can switch from one implementation to the other, just by changing the constructor.
    I hope Indy’s mime decoder is similarly decoupled. AFAIR Ararat’s Synapse mostly is.

Comments are closed.