[fpc-pascal] Troubles with SQLDBRESTBridge

[Sven Barth]

Hello together!

I've played around a bit with the SQLDBRESTBridge and encountered some
troubles which I wanted to check up here on the list whether those are
indeed bugs or not before reporting them to Mantis.

For the tests I've used FPC 3.0.4 and Lazarus 2.0 with the
SQLDBRESTBridge and the Lazarus examples being from trunk on Friday 14th
June.

As server I used the restmodule demo in
$lazarus/components/sqldbrest/demo/restmodule and changed it to use
SQLite3 instead of PostgreSQL.
For the client I used the jsonclient demo in
$lazarus/components/sqldbrest/demo/jsonclient.

I created the database using the expenses-sqlite.sql script in
$fpc/packages/fcl-web/examples/restbridge/expenses-sqlite.sql (and no I
didn't execute expenses-data.sql at first which will be important
further down ;) ).

I started the server and tried to access localhost:7331/REST/metadata (I
changed the port from 8080), but this resulted in a 404 error which was
displayed as an empty page (maybe something isn't correctly working
here?). Using localhost:7331/metadata resulted in a "Not found" exception.

So I started to research what went wrong and found various points:
- the metadata was coupled with the rdoConnectionInURL flag in
TSQLDBRestDispatcher.DoRegisterRoutes instead of rdoExposeMetadata (see
patch exposematadata.patch)
- then it turned out that the routes are only registered once the module
was loaded and thus they would never be found so I adjusted
TSQLDBRestModule.HandleRequest to call the router again (this of course
required to change the BasePath of the TSQLDBDispatcher in the example
to 'REST' instead of '') (see patch reroute.patch) - this point I'm
definitely not sure whether it is the right solution (but in the end it
works)
- then the problem was that the module's route ('REST/*/') took
precedence over the routes added by the TSQLDBRestDispatcher, so I
implemented a sorting of the routes so that more specific ones are
encountered first (see patch sort-routes.patch) - don't know whether
this is the right approach either, but this *is* a problem
- then the fact that TSQLDBRestModule is by default set to wkOneShot
lead to the routes being registered again or more precisely the old
routes being left over and pointing to a freed TSQLDBRestDispatcher
instance; thus I added a call to UnRegisterRoutes to
TSQLDBRestDispatcher.Destroy (see unregister-routes.patch)

With these changes the server worked, but then I encountered two
problems in the client:
- if the database table is empty an empty JSON object ('{}') is returned
resulting in the client complaining about the missing metadata (after
this I executed the expenses-data.sql script)
- when changing from one resource to another I got an exception that the
operation is not possible on an active dataset; I solved this for me by
closing the dataset before executing LoadFromStream (see patch
jsonclient.patch)

And an additional note regarding the expenses-sqlite.sql script. The
intention appears to be that the ID columns are auto increment. This
will however *only* work if the type of the columns is "integer primary
key". "bigint primary key" won't work here. See also:
https://www.sqlite.org/autoinc.html
Thus as with patch expenses-sqlite.sql I updat the SQL script for that
database.

If these are indeed bugs I'll either commit the patches as is (e.g. the
one for the SQLite script is rather surely a bug) or open a bug report
for the more complex ones.

I haven't tested more yet, so I don't know whether there are any further
problems. :)

Regards,
Sven

-------------- next part --------------
A non-text attachment was scrubbed...
Name: exposemetadata.patch
Type: text/x-patch
Size: 932 bytes
Desc: not available
URL: <http://lists.freepascal.org/pipermail/fpc-pascal/attachments/20190621/46fada39/attachment-0006.bin>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: reroute.patch
Type: text/x-patch
Size: 1272 bytes
Desc: not available
URL: <http://lists.freepascal.org/pipermail/fpc-pascal/attachments/20190621/46fada39/attachment-0007.bin>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: sort-routes.patch
Type: text/x-patch
Size: 4044 bytes
Desc: not available
URL: <http://lists.freepascal.org/pipermail/fpc-pascal/attachments/20190621/46fada39/attachment-0008.bin>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: unregister-routes.patch
Type: text/x-patch
Size: 449 bytes
Desc: not available
URL: <http://lists.freepascal.org/pipermail/fpc-pascal/attachments/20190621/46fada39/attachment-0009.bin>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: jsonclient.patch
Type: text/x-patch
Size: 344 bytes
Desc: not available
URL: <http://lists.freepascal.org/pipermail/fpc-pascal/attachments/20190621/46fada39/attachment-0010.bin>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: expenses-sqlite.patch
Type: text/x-patch
Size: 1418 bytes
Desc: not available
URL: <http://lists.freepascal.org/pipermail/fpc-pascal/attachments/20190621/46fada39/attachment-0011.bin>