mORMot Open Source

Hi,
I've been very busy for quite a long time but now I'm back.
I believe the demo app is complete enough to give an overall view on how to start with SQLite Framework.
Would you like to review the current code and possibly include it to the Synopse SQLite FW demos?

ok, now I'm confused I have no idea how is that supposed to work, except that in my case (HasRole) Dest is always integer ID of Dest record ... could you give some code examples?

I'm glad you like it, and thanks for introducing them

Typecasting is not a problem, since you define Dest type anyway, you're also certain about RealDest type.

it is up to you, whether you want us to define more fields in TSQLRecordMany, thus avoiding typecasts when using, or to use typecast each time

perhaps ... to be honest, RealDest came to my mind as opposite to Dest which (for me) was not Dest at all (just it's id) ]:->

I think loading per-request could be a good idea to avoid recursion and other problems.
How about implementing a method "GetDest" or something similar:

pseudocode:

function TSQLRecordMany.GetDest: TSQLRecord;
if fRealDest = nil then
 begin
  fRealDest:= TDestClassType.NewInstance();
  fRealDest.Create(fDestId);
 end;
result:= fRealDest;
end;

of course, TSQLRecordMany will have to take care of freeing the "fRealDest" object on each FillOne and on destroy ...

How about some method executing

SELECT r.*,  p.* FROM UserRoles p JOIN UserRole r ON p.Dest = r.ID WHERE p.Source = %

on UserRoles (TSQLRecordMany)?

That way it'd be possible to fill both the TSQLRecordMany and it's Dest with proper data, using one query only.

Oh, btw - what has happened to
"constructor TSQLRecordMany.Create(aSourceClass: TSQLRecordClass; aSourceID: integer);" ?

I know all the fields are available inside of query condition, but the only selected data is ID of Dest [which is obvious]. I don't however know the ID of pivot record, thus making it impossible to access ValidUntil field (in my example). Compare the code you have posted in post #11 ( http://synopse.info/forum/viewtopic.php?id=164#p866) and the current (r7) HasRole implementation.
http://code.google.com/p/synopse-sqlite … n7&r=7#189
I know it could be achieved by adding "UserRoles.ValidUntil >= date('now')" to select valid roles, but ... I want the ID of UserRoles record anyway, even when it has expired.

I was referring to the method described above.
So far I could either iterate pivot table one-by-one (User.Roles.FillMany, User.Roles.FillOne], thus having access both to the pivot's additional data stored [directly, via User.Roles.ValidUntil] and the actual record [Database.Retrieve(User.Roles.Dest)] - which is a bit inconvenient because I need to store & free later retrieved Dest record.
The second method was to call DestGet / DestGetJoined, but I had no access to the pivot data (ValidUntil).

Thus, if I need to DISPLAY [access via Delphi code] both UserRole.name AND UserRoles.ValidUntil, I had to use first method ...

What is the purpose of DestGetJoined if that methods selects IDs only anyway?

So, in case of FillMany we do the following ->
SELECT FROM PivotTable WHERE source = .. AND MOD(dest, 64) = ...

than, once we have both RecordReferences we do SELECTs to find more data ...

While DestGetJoined selects list of ID's and then does id-based SELECTing, am I right?

if that eliminates usage of MOD(dest, 64) in WHERE clause than I like it I guess that might be a little bottleneck since I don't think indexes will be used when WHERE contains a function call?

By the way, there should be overloaded DestGetJoined

similar to that:

function TSQLRecordMany.DestGetJoined(aClient: TSQLRestClient;
  out DestIDs: TIntegerDynArray): boolean; 
begin
result:= DestGetJoined(aClient, '', fSourceID, DestIDs);
end;

// -- edit
Oh, as I can see, DestGetJoined doesn't accept empty WHERE clause... but WHY? Do I have to add ' 1 = 1 ' if I want to list each associated record? Wouldn't it be smarter to do:

if aSQL <> '' then
 aSQL := ' AND ' + aSQL;

and later insert it as the last statement of the query?

also, it's a bit strange but ...

that works fine:

ACustomer.Tasks.DestGetJoined(Database, '1 = 1', ACustomer.ID, fIds); //debugger says: ACustomer.ID = 3

but if I pass 0 as sourceID, the auto retrieving fails. debugger says that fSourceID = 0, so it fails on the second "if aSourceID = 0" check in DestGetJoined ...

Thank you

I can't wait to know what I'm doing wrong

What bothers me now ... why doesn't TSQLRecord keep it's TSQLRest object once passed in constructor? Have a look at TUser.HasRole implementation ... I need to pass the connection reference all the time. I know I could use global variable, but ... why?

Currently it is available in SVN only, at the

http://code.google.com/p/synopse-sqlite-demo/

but I'll upload the zip archive in 15 minutes

Hi,
may I ask you what is the progress of integrating ZDBC to the Framework?

Hi,
indeed that was a bit unclear - since the demo 02 used the server directly. Unfortunately I'm the kind of guy who looks at the examples first, than refers to the documentations when needs to clarify something

I've changed the code to follow the correct pattern.

Also, the demo shows TSQLTableToGrid as well now

BTW please have a look at line 402 of current revision (r4). In my case, using the Iso8601Now instead of Now, causes "Invalid floating point operation", pointing the method

procedure DateToIso8601PChar(Date: TDateTime; P: PUTF8Char; Expanded: boolean); overload;
// we use YYYYMMDD date format
var Y,M,D: word;
begin
  DecodeDate(Date,Y,M,D);
  DateToIso8601PChar(P,Expanded,Y,M,D); // <-- debugger points that line.
end;

Hi,
indeed, so far I haven't played with UI classes at all, I just thought all of them depend on TMS.
As I can see, the TSQLTableToGrid can't use TSQLRestServerDB directly, I had to export it via named pipe and than create a client - just for TSQLTableGrid to access it. Is that really necessary?
Anyway, it's definitely worth showing but I won't commit it unless you tell me why does it require the URIClient to be created & used

Hi,
I thought you're main point against native TToolBar & TTabSet is a poor look of those old components. SpTBXLib provides skinning support for TB2K (but not only, of course).
Anyway, I'd love to see the code which compiles under old Delphi with no dependencies on commercial component sets

My main development environment for all the "private" (hobby) projects is Delphi 7 (Personal edition!) ..
That makes Synopse SQLite3 most complete and powerful DB framework available Of course I'm fully aware that I'm in minority, and I'll have to switch to more recent Delphi version one day.

Besides that, personally I don't like ribbon idea at all. I hate using Office 2007 just because it has ribbon instead of menus & toolbars. I don't get the idea of Ribbon, and don't know any advantages over menus & toolbars, except for bigger icons in Ribbon

Have you considered optional dependency on SpTBXLib which is a mature, customizable, attractive-looking MPL-licensed set of menu & toolbars components (based on Toolbar2000)?

Thank you.

Would you be so kind to explain me how is it done? So far I thought that any operation done on "shared" object (common for multiple threads) must be protected with "synchronize", no matter what. Apparently, I was wrong - in your code I found CriticalSection to be used in SQLite wrapper, but not SQLite3Commons at all. What is the difference, why isn't for example TSQLRestServerDB "protected" (locked) with CriticalSection?

How do I find what parts / methods of my code needs to be protected with CriticalSection and which not?

So far I haven't stumbled upon any interesting explanation / article / blog note, all google results are:

a) examples of synchronization between thread and the GUI. From simple progressbar to most complex, but still the topic is quite simple and obvious: each time you access / modify the property of GUI component, do that in "Synchronize"
b) articles explaining Critical Sections, Mutexes etc.
c) Examples of very very simple thread-safe classes (thread safe stack or list) - they all do the same - implement lock / unlock methods which do enter/leave critical section and return the actual stack/list pointer on locking.

Could you please provide me with a good reference to read which parts of code should be protected?

thank you!

Actually, I was looking for a way to get an array / list of TSQLRecord descendent ...
The MultiFieldValues returns a TSQLTableJSON which has all the data, but does it "wrap" the data in TSQLRecord descendents?

Hi,
what I understood is that there are following methods of accessing DB:

• HTTP

• FastCGI

• NamedPipes

• Direct access

Are all of them thread-safe?

Which of these methods consumes less memory / CPU? My application is not a typical DB app, it's rather very uncommon to use ORM / SQL in such an application, but since it has rich GUI, it already uses quite a lot of memory ... and I'd like to avoid much more consumption.

I have two threads in my app:
main thread (obvious)
background worker thread

while the background worker thread keeps updating DB when necessary (it might be quite intense sometimes) with DELETE / UPDATE / INSERT queries, the main thread might want to do SELECT from time to time. The most crucial is SELECT time (it has to have highest priority) because I want to avoid UI from freezing (actually the SELECT itself is not a big deal, currently it is very poorly written, instead of SQLite it does three nested iterations and lots of slow string comparisons for building data list, so the only possible scenario for UI freeze would be waiting for Critical Section unlock).

Now, should I rely on client-server architecture (correct me if I'm wrong, but this would require me to use HTTP, FastCGI or Named Pipes?) to create a server in main thread, than use two clients -> one for main thread, and the second created in background worker? Under this scenario I don't have to care about synchronization, do I?

The second option would be using Direct access (create TSQLRestServerDB, than access it from both threads), but in that case I'd need to handle synchronization on my own, right?