You are not logged in.
what the terms of a custom class so rosource string in it can be extracted by the method ExtractAllResource? I tried to extract nextgrid, but no success.
SQLite3i18n.ExtractAllResources(
// first, all enumerations to be translated
[TypeInfo(TNumberAction)],
// then some class instances (including the TSQLModel will handle all TSQLRecord)
[Client.Model],
// some custom classes or captions
[TNxCustomColumn,TNxTextColumn,TColumnHeader],[]);many thanks.
ok, i'll try In Memory ORM latter. i think it is faster than Embedded ORM. isn't it?
. i'm more happy than you about it.
thank you for this wonderful framework, and keep it open source. I'm obsessed with this framework, but UI is an important issue for me. although you have to include it, but it takes time for me to learn. slowly and with your support is very good, I'm sure I could master it later.
and of course I believe this framework will be more perfect
thank you....
Yes. now feels much faster. takes an average of only 5 seconds.
{Client Side Version}
if TransactionBegin(TSQLNumber2) then begin
StartSendBacth(TSQLNumber2,ID);
Commit();
end;
if TransactionBegin(TSQLNumber3) then begin
StartSendBacth(TSQLNumber3,ID);
Commit();
end;
if TransactionBegin(TSQLNumber4) then begin
StartSendBacth(TSQLNumber4,ID);
Commit();
end;or you have a better suggestion, to make it faster?
Thank you.
while, a lot faster if I use EngineExecuteAll on the server side.
it only takes less than 5 seconds.
I tried to implement BatchUpdate to update the record on the client side.
but its performance is very slow, takes more than 5 minutes.
while, a lot faster if I use EngineExecuteAll on the server side.
what is wrong with the code that I created the following?
constructor TFileClient.Create(const aServer: AnsiString);
begin
Model:= CreateFileModel(Self);
Filename:= ChangeFileExt(paramstr(0),'.dat');
inherited Create(Model,CreateFileModel(Self),Filename,TSQLRestServerDB,False);
Server.CreateMissingTables(ExeVersion.Version.Version32);
...
end;
procedure SetWinner(aClient: TFileClient; aPID: Integer; aNumber4: RawUTF8);
var
aN2,aN3: RawUTF8;
aSQLMC: TSQLMasterCode;
procedure StartSendBacth(aSQLNClass: TSQLRecordClass; aMCID: Integer);
var aSQLN:TSQLBaseNumber;
aR: TIntegerDynArray;
aN: RawUTF8;
begin
aSQLN:= nil;
if aSQLNClass = TSQLNumber2 then begin
aN:= aN2;
aSQLN:= TSQLNumber2.Create;
end else
if aSQLNClass = TSQLNumber3 then begin
aN:= aN3;
aSQLN:= TSQLNumber3.Create;
end else
if aSQLNClass = TSQLNumber4 then begin
aN:= aNumber4;
aSQLN:= TSQLNumber4.Create;
end;
if aSQLN = nil then
Exit;
with aClient, aSQLN do try
/// Winner
BatchStart(aSQLNClass);
if FillPrepare(aClient,'MasterCodeID = % AND NumberText = %',[aMCID,aN],'ID,IsMatch') then begin
while FillOne do begin
IsMatch:= True;
BatchUpdate(aSQLN);
end;
BatchSend(aR);
end;
/// Loser
BatchStart(aSQLNClass);
if FillPrepare(aClient,'MasterCodeID = % AND NumberText <> %',[aMCID,aN],'ID,IsMatch') then begin
while FillOne do begin
IsMatch:= False;
BatchUpdate(aSQLN);
end;
BatchSend(aR);
end;
finally
aSQLN.Free;
end;
end;
begin
if aNumber4 = '' then
Exit;
aN2:= Copy(aNumber4,3,2);
aN3:= Copy(aNumber4,2,3);
aSQLMC:= TSQLMasterCode.Create;
with aClient, aSQLMC do
if FillPrepare(aClient,'PeriodID = %',[aPID],'ID') then try
if TransactionBegin(TSQLBaseNumber) then
while FillOne do try
{Server Side Version}
//Server.EngineExecuteAll(FormatUTF8('UPDATE Number2 SET IsMatch = 1 WHERE MasterCodeID = % AND NumberText = %',[ID,aN2]));
//Server.EngineExecuteAll(FormatUTF8('UPDATE Number2 SET IsMatch = 0 WHERE MasterCodeID = % AND NumberText <> %',[ID,aN2]));
//Server.EngineExecuteAll(FormatUTF8('UPDATE Number3 SET IsMatch = 1 WHERE MasterCodeID = % AND NumberText = %',[ID,aN3]));
//Server.EngineExecuteAll(FormatUTF8('UPDATE Number3 SET IsMatch = 0 WHERE MasterCodeID = % AND NumberText <> %',[ID,aN3]));
//Server.EngineExecuteAll(FormatUTF8('UPDATE Number4 SET IsMatch = 1 WHERE MasterCodeID = % AND NumberText = %',[ID,aNumber4]));
//Server.EngineExecuteAll(FormatUTF8('UPDATE Number4 SET IsMatch = 0 WHERE MasterCodeID = % AND NumberText <> %',[ID,aNumber4]));
{Client Side Version}
StartSendBacth(TSQLNumber2,ID); //araund 10.890 records
StartSendBacth(TSQLNumber3,ID); //araund 1.804 records
StartSendBacth(TSQLNumber4,ID); //araund 1.624 records
Commit;
except
RollBack;
end;
finally
aSQLMC.Free;
end;
end;thank you
regards
so far, the AV and the odd character has not appeared again.
Thank you.
thank you. i'll try it.
ok, thank you.
i found this on SO to reset AUTO_INCREMENT field in SQLite Database.
DELETE FROM MyTableName
DELETE FROM SQLITE_SEQUENCE WHERE NAME = 'MyTableName'is it posible to do this with framework without engineexecute?
thank you.
Ok. on the other project i test, the TSynFilterUppercase work as expected.
i'l try to recheck what is wrong on my code.
ok. i think now i understand how to use TSynValidate, especially TSynValidateUniqueField.
but not for the TSynFilter, especially in this case TSynFilterUppercase.
how exactly to use TSynFilterUppercase?
thank you.
regard.
What do you want to implement?
with
TSQLFile.AddFilterOrValidate('Name',TSynFilterUpperCase.Create()); i want to update automatically lowercase user input of Name property to uppercase.
with
TSQLFile.AddFilterOrValidate('Name',TSynValidateUniqueField.Create());i want to ensure that no Name value of TSQLFile record duplicated.
Validate the content when adding or updating a record?
Both. adding and updating.
TSynFilterUpperCase and TSynValidateUniqueField should be done when adding record.
when user try to modify the Name value of any record to something that are allready exists, TSynValidateUniqueField should reaise an exception.
In case of wrong validation, do you expect an exception to be raised, for instance?
Yes. like what SQLite3UIEdit does.
ErrMsg := Rec.Validate(Client,ModifiedFields,@FieldIndex);
if ErrMsg<>'' then begin
// invalid field content -> show message, focus component and abort saving
if cardinal(FieldIndex)<cardinal(length(fFieldComponents)) then begin
C := fFieldComponents[FieldIndex];
C.SetFocus;
Application.ProcessMessages;
ShowMessage(ErrMsg,format(sInvalidFieldN,[fFieldCaption[FieldIndex]]),true);
end else
ShowMessage(ErrMsg,format(sInvalidFieldN,['?']),true);
endwhat i did is:
1.registering TSynFileteOrValiedate after TModel creation.
...
result.SetEvents(TypeInfo(TFileEvent));
TSQLFile.AddFilterOrValidate('Name',TSynValidateUniqueField.Create());
TSQLFile.AddFilterOrValidate('Name',TSynFilterUpperCase.Create());
end;2. modifying SetRec function
function TEditForm.SetRec(const aClient: TSQLRestClient; const Value: TSQLFile): boolean;
begin
fClient:= aClient;
...3. performing registed filter and validation
procedure TEditForm.BtnOkClick(Sender: TObject);
begin
if ReadOnly then
ModalResult := mrCancel else begin
Rec.fModified := Iso8601Now;
Rec.fName := trim(S2U(Name.Text));
Rec.Filter();
Rec.Validate(fClient);
....but still not work.
1. I've tried to implement a filter() features at TSQLFile like this.
procedure TEditForm.BtnOkClick(Sender: TObject);
var
P: TSQLRecordProperties;
begin
if ReadOnly then
ModalResult := mrCancel else begin
Rec.fModified := Iso8601Now;
Rec.fName := trim(S2U(Name.Text));
P:= Rec.RecordProps;
Rec.Filter();
//Rec.Validate()
....but it does not work as expected.
2. because TSQLRecord.Validate () requires a parameter TSQLRest, while no declaration TSQLRest in this form, then for a while I did not apply TSQLRecord.Validate here. (Actually I was hoping to get property of TSQLRecordProperties TSQLRest, but it was not, I just found a property TSQLModel).
because no.1 does not work as I expect, and now I do not want to add parameters to
TEditForm.SetRec TSQLRest (const Value: TSQLFile)procedure, so I tried to apply it to the exclusion of such ComputeFieldsBeforeWrite method.
procedure TSQLFile.ComputeFieldsBeforeWrite(aRest: TSQLRest; aOccasion: TSQLEvent);
var F: integer;
begin
inherited;
if (self<>nil) and (aRest<>nil) then
with RecordProps do begin
//for F:= 0 to High(Fields) do
//if Fields[F].ShortName= 'Name' then
//SetStrProp(Self,Fields[F].ShortName,Name+IntToStr(F));
TSQLRecord(Self).Filter();
TSQLRecord(Self).Validate(aRest);
end;
end;but it also does not work.
Can you give me some advice?
also, is it possible to get TSQLRest from TSQLRecordProperties property? or have to passing parameter as SQLite3UIEdit does?
i just modified CreateFileModel function on the MainDemo sample project.
adding two lines of code to implement TSynFilterOrValidate as stated on the documentation.
...
TSQLMemo.AddFilterOrValidate('Name',TSynValidateUniqueField.Create());
TSQLMemo.AddFilterOrValidate('Name',TSynFilterUpperCase.Create());
end;but it is no effect. SQLMemo still accept two or more duplicates name, and also with the uppercase issue.
what is wrong, with piece of code above?
thank you.
regard.
There is a problem with the current implementation of the grid display, when you have one or two rows only within.
I'll fix it ASAP.
while you improve the implementation of the grid view, please check it thoroughly.
because, when I test, the error does not only occur in one or two records, but sometimes it can be more than 2 records.
Also deleting record without a check mark, the data still appear, but if we press the refresh button.
deleting one or more record with check mark is still problematic.
thank you.
thank you for the quick fix. now work as expected.
hi, ab
i try to compile maindemo sample under delphi 2010 and win 7.
compiler stoped at line 2704
while GetQueuedCompletionStatus(FRequestQueue, PtrUInt(Context),
CompletionKey, OverLapped, INFINITE) dowith error message
[DCC Error] SynCrtSock.pas(2704): E2033 Types of actual and formal var parameters must be identical
[DCC Fatal Error] SQLite3HttpClient.pas(135): F2063 Could not compile used unit 'SynCrtSock.pas'can you check it?
thank you.
About code 12002, this is a HTTP Client-Server error (something like Server not responding).
So there is something wrong with your HTTP connection.
so, what must i do to fix it?
There is a problem with the current implementation of the grid display, when you have one or two rows only within.
I'll fix it ASAP.
Thank you, i'll waiting for it.
I just tried to add a new record on maindemo I just downloaded, there is no error message appears.
but the grid displays strange characters like this.
then immediately I tried to add a new record again, and system error appears as follows:
and stop here, after pressing Ok button.
TWinHTTP.InternalSendRequest procedure (const ADATA: TSockData);
var L: integer;
begin
L: = length (Adata);
if not WinHttpSendRequest (fRequest, nil, 0, pointer (ADATA), L, L, 0) or
not WinHttpReceiveResponse (fRequest, nil) then
RaiseLastOSError;
end; //line break herepressing the F8 key, arriving on this line (line 3810) in SynCrtSock
InternalCloseRequest;I press and hold the F8 key, until the AV like this
pressing the F7, F8, F9, Shift + F7, Shift + F8, AV appears again and again.
so I press the Ctrl + F2.
"System Error. Code 12029" sounds like a firewall issue for me.
You do not have the HTTP server working as expected.
is this because of antivirus? i use avg antivirus, i'll try to disable it.
But,
I just downloaded again all the files in the folder maindemo of the source code repository.
I put it in a new folder named maindemo1.
and the system error code 12029 does not appear anymore.
i just run, and then close immediately, not yet create a new data.
Yes, i also tried to enabling
{$define PUREPASCAL} as you suggest.
but
System Error. Code 12029
still occured.
No. PUREPASCAL Conditional Define still still disable in synopse.inc.
You have to BUILD the project, not only COMPILE it, when you change a global conditional.
i understand and i did it, but error as described above still happen;
In case of an AV, close the CPU view, then press F7 or view the call stack. You'll find out why line made the error.
Now, I can not run maindemo.dpr, because the error that occurred at the beginning, just as I described above.
I'll try to run another project.
i have change the @fPipeSecurityAttributes to nil, then run Test again, and all test passed.
then i set back @fPipeSecurityAttributes, then run test again, and all test passed.
i do that, more than one time.
then i open and run the MainDemo Project, then i try to edit record, then AV accured again,
without break on any line, just show the CPU process. pressing F9 key not help.
then i disable the
{.$define EXTRACTALLRESOURCES} in FileMain.pas with compiled sucses.
but if i run the project, immediately EOSError class occured with message:
System Error. Code: 12029
and debuger break on end of procedure TWinHTTP.InternalSendRequest(..) in the SynCrtSock unit.
procedure TWinHTTP.InternalSendRequest(const aData: TSockData);
var L: integer;
begin
L := length(aData);
if not WinHttpSendRequest(fRequest, nil, 0, pointer(aData), L, L, 0) or
not WinHttpReceiveResponse(fRequest,nil) then
RaiseLastOSError;
end;then i enabled it back
{$define EXTRACTALLRESOURCES}and i error occored while compiling with message
Undeclared identifier: 'ExtractAllResources'
i've downloaded the latest version of SQLite3i18n unit.
@TLama
But, i think, this is not the same issue.
You may have some deprecated files around in your search path.
my path hierarchy of the mORMot framework is
..\mORMot
\SQLite3
\Sample
...
\MainDemo
...
\LVCL
\SynProject
and the search path of MainDemo Project is:
..\..\..\Lib\FastMM496\FastMM
..\..\SQLite3
..\..thank again
heare is the result when i run TestSQL3.Dpr with Logs Enabled.
Synopse mORMot Framework Automated tests
------------------------------------------
1. Synopse libraries
1.1. Low level common:
- System copy record: 22 assertions passed 180us
- TDynArray: 519,427 assertions passed 170.73ms
- TDynArrayHashed: 1,200,023 assertions passed 162.34ms
- Fast string compare: 7 assertions passed 109us
- IdemPropName: 10 assertions passed 139us
- Url encoding: 105 assertions passed 1.02ms
- IsMatch: 599 assertions passed 243us
- Soundex: 29 assertions passed 102us
- Numerical conversions: 776,256 assertions passed 250.67ms
- Curr64: 20,039 assertions passed 1.77ms
- CamelCase: 5 assertions passed 89us
- Bits: 4,614 assertions passed 123us
- Ini files: 7,004 assertions passed 34.78ms
- Unicode - Utf8: 61,082 assertions passed 1.03s
- Iso8601 date and time: 24,000 assertions passed 4.84ms
- Url decoding: 1,100 assertions passed 339us
- TSynTable: 873 assertions passed 2.91ms
- TSynCache: 404 assertions passed 793us
- TSynFilter: 1,005 assertions passed 2.55ms
- TSynValidate: 677 assertions passed 737us
- TSynLogFile: 42 assertions passed 695us
Total failed: 0 / 2,617,323 - Low level common PASSED 1.67s
1.2. Low level types:
- RTTI: 34 assertions passed 622us
- Url encoding: 200 assertions passed 706us
- Encode decode JSON: 127,190 assertions passed 89.82ms
Total failed: 0 / 127,424 - Low level types PASSED 92.25ms
1.3. Big table:
- TSynBigTable: 19,250 assertions passed 176.64ms
- TSynBigTableString: 16,228 assertions passed 36.09ms
- TSynBigTableMetaData: 384,060 assertions passed 1.16s
- TSynBigTableRecord: 452,185 assertions passed 2.82s
Total failed: 0 / 871,723 - Big table PASSED 4.20s
1.4. Cryptographic routines:
- Adler32: 1 assertion passed 714us
- MD5: 1 assertion passed 660us
- SHA1: 5 assertions passed 642us
- SHA256: 5 assertions passed 683us
- AES256: 6,372 assertions passed 175.72ms
- Base64: 11,994 assertions passed 82.74ms
Total failed: 0 / 18,378 - Cryptographic routines PASSED 262.66ms
1.5. Compression:
- In memory compression: 12 assertions passed 197.91ms
- Gzip format: 19 assertions passed 385.17ms
- Zip format: 36 assertions passed 679.98ms
- SynLZO: 3,006 assertions passed 86.19ms
- SynLZ: 13,016 assertions passed 291.49ms
Total failed: 0 / 16,089 - Compression PASSED 1.64s
1.6. Synopse PDF:
- TPdfDocument: 4 assertions passed 45.22ms
- TPdfDocumentGDI: 6 assertions passed 9.95ms
Total failed: 0 / 10 - Synopse PDF PASSED 56.09ms
2. mORMot
2.1. Basic classes:
- TSQLRecord: 47 assertions passed 627us
- TSQLRecordSigned: 200 assertions passed 5.96ms
- TSQLModel: 3 assertions passed 687us
Total failed: 0 / 250 - Basic classes PASSED 8.20ms
2.2. File based:
- Database direct access: 10,136 assertions passed 2.02s
- Virtual table direct access: 12 assertions passed 183.67ms
- TSQLTableJSON: 19,030 assertions passed 453.54ms
! Exception ECommunicationException raised with messsage:
The system cannot find the file specifiedct to server "Test"
Synopse framework used: 1.16
SQlite3 engine used: 3.7.11
Generated with: Delphi 7 compiler
Time elapsed for all tests: 155.96s
Tests performed at 02/04/2012 15:12:59
Total assertions failed for all test suits: 0 / 3,651,197
! Some tests FAILED: please correct the code.
Done - Press ENTER to Exithere is the line of SQLite3Commons unit (line 17908)
if Pipe=Invalid_Handle_Value then begin // server must exist
if (aModel<>nil) and (aModel.Owner=self) then
aModel.Free; // avoid memory leak
raise ECommunicationException.CreateFmt(
'%s can''t connect to server "%s"'#13'via "%s":'#13'%s',
[ClassName,ApplicationName,fPipeName,SysErrorMessage(GetLastError)]);
end;this is exception message extracted from Log file
D:\Bali Pedia Projects\mORMot\SQLite3\TestSQL3.exe 0.0.0.0 (2012-04-02 15:10:22)
Host=localhost User=user CPU=2*0-6-5894 OS=6.2=6.0.6002 Wow64=0 Freq=2078154
TSQLLog 1.16 2012-04-02T15:10:23
....
....
0000000013474E0D EXC ECommunicationException ("TSQLRestClientURINamedPipe can't connect to server \"Test\"\rvia \"\\\\.\\pipe\\Sqlite3_Test\":\rThe system cannot find the file specified") at 004D4808 stack trace 00405969 00405A44 00402A9B 749CBB10 7642DCE2 00404EA7 76FD64B4 749CD211 0041DF5F 0041DC9B 0040446E 004D423B 0040621D 004D0B33 004EC9B4 00431D18 005158C5 00515BCD 7642ED6C 76FF377B
00000000134AFB4D EXC ECommunicationException ("TSQLRestClientURINamedPipe can't connect to server \"Test\"\rvia \"\\\\.\\pipe\\Sqlite3_Test\":\rThe system cannot find the file specified") at 004D4808 stack trace 005158C5 00515BCD 7642ED6C 76FF377B 76FF374E but, this only occurs during the first run.
during the next run, all the tests passed.
thanks
Sorry, I forgot about this.
now the error does not appear anymore.
thank you
@Junior/RO:
I've done it. This bug has been there since version 1.15.
I had already been posted in this forum, and I'll have to wait for another user who will report it too, but until now no one did.
I think this only happens on my computer.
I do not know, what information is required from my computer to be able to overcome this problem, while I have not been able to use TSynLog.
I ever use madExcept. if it is indeed useful, I will try to use it, and report back here.
i tried to vacuum the database with this code
Client.EngineExecute('VACUUM');but sometimes ESQLException error occure with message "cannot VACUUM-SQL statements in progress".
how to check there are SQL statements exists/pending to be execute? or to check if TSQLRest have an transaction still active?
thanks.
In the Maindemo example application, some times grid displaying strange character. Especially for the first time running with empty grid, and then create some record.
thanks
thank you ab.
I try to use TSQLCustomToolBar like this:
...
...
TTestAction =
(taTest1,taTest2);
...
...
FToolBar: TSQLCustomToolBar;
...
procedure ActionClick(Sender: TObject);
begin
case TSynToolButton(Sender).Index of
0:ShowMessage('Test -1');
1:ShowMessage('Test -2');
end;
end;
...
...
FToolBar.Init(self,TypeInfo(TTestAction),ActionClick,ImageList,'');
FToolBar.AddToolBar('Test')this code is work as expected, but i want to use TTestAction in the case evaluate, something like:
case TTestAction(Sender) of
taTest1:ShowMessage('Test -1');
taTest2:ShowMessage('Test -2');
end;how to use an associated action of TSynToolButton;
many thanks.
I think, Synopse/mORMot frame is realy great. i imagine, that i can create demo app with embedded db model, and simply change to the client/server model in the future with just change TSQLResClient and TSQLResServer class.
i open this forum everyday, just want to know, if there something new or isue.
this forum is the first most visited website in my google chrome browser (second is facebook
)
but, till know, some time there are exception occured that i can't fix for my self.
i use the maindemo sample to learn the framework.
e.g try to change maindemo sample to embedded db model like this,
//TFileClient = class(TSQLite3HttpClient)
TFileClient = class(TSQLRestClientDB)
public
DB: TSQLDatabase;
Model: TSQLModel;
Filename: TFileName;
//contructor
constructor TFileClient.Create(const aServer: AnsiString);
begin
//inherited Create(aServer,SERVER_HTTP_PORT,CreateFileModel(self));
Model:= CreateFileModel(Self);
Filename:= ChangeFileExt(paramstr(0),'.dat');
//TSQLRestClientDB.create
inherited Create(Model,Model,Filename,TSQLRestServerDB,False);
Server.CreateMissingTables(ExeVersion.Version.Version32);
ForceBlobTransfert := true;
end;and then in the MainForm i disable DEBUGINTERNALSERVER
//{$define DEBUGINTERNALSERVER}but the edit action is not working. cause
Tab.Retrieve(Client,Tab.List.Row,true) is always return false.
and then if terminate the application, FastMM reported too many memory leaks.
is this bug in the framework or i do something wrong in my code?
thanks for your the hard work, and keep the framework open source.
but, i want to refresh the content of the grid immediately after the fMasterCodeID changed.
i changed the name of the procedure above to TFrameMain.InitNumber and execute only one time on then constructor TFrameMain.Create.
and i create new procedure for refreshing/requery content when the fMasterCodeID changed.
procedure TFrameMain.RefreshNumber;
begin
fTable2:= fClient.ListFmt([TSQLNumber2],
FormatUTF8(aSelect,['Number2','Number2','Number2','Number2']),aWC,[fMasterCodeID]);
if fGrid2.Refresh(True) then
fGrid2.Refresh;
end;but still not working..
how to insert a trigger from delphi using your classes
i did tried something like this,
TTriggerEvent = (teInsert, teUpdate, teDelete, teUpdateOf);
TTriggerTime = (ttBefore, ttAfter);
TTriggerKind = (tkForEachRow, tkForEachStatement);
TTriggerEventTime = (
tetBeforeInsert, tetBeforeUpdate, tetBeforeUpdateOf, tetBeforeDelete,
tetAfterInsert, tetAfterUpdate, tetAfterUpdateOf, tetAfterDelete);
TSQLTrigger = record
IsTemporary:Boolean;
TriggerEvent:TTriggerEvent;
TriggerTime:TTriggerTime;
UpdateOf:RawUTF8;
TriggerKind:TTriggerKind;
WhenClause:RawUTF8;
TriggerSQL:RawUTF8;
end;
const
TRGSQL_MASTER_CODE_AIRBUR=
' UPDATE % SET '+CR+
' Number3 = '+CR+
' CASE WHEN LENGTH(NEW.Number4) >= 4 '+CR+
' THEN SUBSTR(NEW.Number4,2,3) '+CR+
' ELSE NEW.Number4 '+CR+
' END, '+CR+
' Number2 = '+CR+
' CASE WHEN LENGTH(NEW.Number4) >= 3 '+CR+
' THEN SUBSTR(NEW.Number4,3,2) '+CR+
' ELSE NEW.Number4 '+CR+
' END, '+CR+
' ManualCount = 0 '+CR+
' WHERE ID = %.ID; ';
EventSQL:array[TTriggerEvent] of string=('INSERT','UPDATE','DELETE','UPDATE OF');
TimeSQL:array[TTriggerTime] of string=('BEFORE','AFTER');
KindSQL:array[TTriggerKind] of string=('FOR EACH ROW','FOR EACH STATEMENT');
function SetTriggerParameter(aSQLTrigger: TSQLTrigger;
aTET: TTriggerEventTime; aUpdateOf: RawUTF8=''): TSQLTrigger;
begin
if aUpdateOf<>'' then begin
Assert(aTET in [tetBeforeUpdateOf,tetAfterUpdateOf],'Error Parameter Update of');
end;
if aTET in [tetBeforeUpdateOf,tetAfterUpdateOf] then begin
Assert(aUpdateOf<>'','Error Parameter Update of');
end;
with aSQLTrigger do begin
case aTET of
tetBeforeInsert..tetBeforeDelete:
TriggerTime:= ttBefore;
tetAfterInsert..tetAfterDelete:
TriggerTime:= ttAfter;
end;
case aTET of
tetBeforeInsert,tetAfterInsert:
TriggerEvent:= teInsert;
tetBeforeUpdate,tetAfterUpdate:
TriggerEvent:= teUpdate;
tetBeforeUpdateOf,tetAfterUpdateOf:
TriggerEvent:= teUpdateOf;
tetBeforeDelete,tetAfterDelete:
TriggerEvent:= teDelete;
end;
UpdateOf:= aUpdateOf;
TriggerKind:= tkForEachRow;
IsTemporary:= False;
WhenClause:= '';
end;
Result:= aSQLTrigger;
end;
function GenerateTriggerSQL(aTableName: RawUTF8; aSQLTrigger: TSQLTrigger): RawUTF8;
var
TriggerName: RawUTF8;
SQL: RawUTF8;
begin
with aSQLTrigger do
begin
TriggerName:=FormatUTF8('TRG_%_%%R',
[aTableName,LeftStr(TimeSQL[TriggerTime],1),LeftStr(EventSQL[TriggerEvent],1)]);
SQL:=FormatUTF8('DROP TRIGGER IF EXISTS [%];%',[TriggerName,CR]);
SQL:=SQL+'CREATE ';
if IsTemporary then
SQL:=SQL+'TEMPORARY ';
SQL := SQL+FormatUTF8('TRIGGER IF NOT EXISTS [%]%',[TriggerName,CR]);
SQL := SQL+FormatUTF8('% % %%',[TimeSQL[TriggerTime],EventSQL[TriggerEvent],UpdateOf,CR]);
SQL := SQL+FormatUTF8('ON %%',[aTableName,CR]);
SQL := SQL+FormatUTF8('%%',[KindSQL[TriggerKind],CR]);
SQL := SQL+FormatUTF8('%%',[WhenClause,CR]);
SQL := SQL+FormatUTF8('%%',['BEGIN',CR]);
SQL := SQL+FormatUTF8('%%',[TriggerSQL,CR]);
SQL := SQL+FormatUTF8('%%',['END',CR]);
end;
Result := SQL;
end;
function TFileServer.ExecuteTrigger(aRecordClass: TSQLRecordClass;
aTET: TTriggerEventTime; aTriggerSQL: RawUTF8; aUpdateOf: RawUTF8=''): RawUTF8;
var
aTrigger: TSQLTrigger;
aTableName: RawUTF8;
aTriggerDef: RawUTF8;
begin
aTableName:= aRecordClass.SQLTableName;
aTrigger:=SetTriggerParameter(aTrigger,aTET,aUpdateOf);
with aTrigger do
TriggerSQL:= aTriggerSQL;
aTriggerDef:= GenerateTriggerSQL(aTableName, aTrigger);
Clipboard.AsText:=aTriggerDef;
with Model do
EngineExecuteAll(aTriggerDef);
end;
constructor TFileServer.Create;
var
aDB:TFileName;
begin
aDB:='sgp.dat';
inherited Create(CreateFileModel(self),aDB);
CreateMissingTables(ExeVersion.Version.Version32);
Server := TSQLite3HttpServer.Create(SERVER_HTTP_PORT,self);
AddAuditTrail(feServerStarted);
OnUpdateEvent := OnDatabaseUpdateEvent;
//DB.user_version:=0;
if DB.user_version<1 then
begin
ExecuteTrigger(TSQLMasterCode,tetAfterInsert,FormatUTF8(TRGSQL_MASTER_CODE_AIRBUR,[TSQLMasterCode.SQLTableName,'NEW']));
ExecuteTrigger(TSQLMasterCode,tetBeforeUpdateOf,FormatUTF8(TRGSQL_MASTER_CODE_AIRBUR,[TSQLMasterCode.SQLTableName,'NEW']),'Number4');
...
....
DB.user_version:=1;
end;
end;this is work as expected. the trigger shown when i open database with third party sqlite manager.
but, i think, my code is still dirty, since i don't know more about RTTI.
hallo..,
what is exactly method to use to refreshing data in the grid using TSQLTableToGrid
i have the code like this :
procedure TFrameMain.RefreshNumber;
begin
fTable2:= MainForm.Client.ListFmt([TSQLNumber2],
FormatUTF8(aSelect,['Number2','Number2','Number2','Number2']),aWC,[fMasterCodeID]);
fGrid2:= TSQLTableToGrid.Create(dg2,fTable2,MainForm.Client);
fGrid2.OnDrawCellBackground:=DoDrawCell;
with fGrid2 do begin
SortForce(1,False);
SetFieldFixedWidth(40);
SetFieldLengthMean('caaa',False);
end;
with dg2 do begin
ColWidths[ColCount-2]:= 20;
ColWidths[ColCount-1]:= 20;
Row:= 1;
end;
fTable3:= MainForm.Client.ListFmt([TSQLNumber3],
FormatUTF8(aSelect,['Number3','Number3','Number3','Number3']),aWC,[fMasterCodeID]);
fGrid3:= TSQLTableToGrid.Create(dg3,fTable3,MainForm.Client);
fGrid3.OnDrawCellBackground:=DoDrawCell;
with fGrid3 do begin
SortForce(1,False);
SetFieldFixedWidth(40);
SetFieldLengthMean('caaa',False);
end;
with dg3 do begin
ColWidths[ColCount-2]:= 20;
ColWidths[ColCount-1]:= 20;
Row:= 1;
end;
fTable4:= MainForm.Client.ListFmt([TSQLNumber4],
FormatUTF8(aSelect,['Number4','Number4','Number4','Number4']),aWC,[fMasterCodeID]);
fGrid4:= TSQLTableToGrid.Create(dg4,fTable4,MainForm.Client);
fGrid4.OnDrawCellBackground:=DoDrawCell;
with fGrid4 do begin
SortForce(1,False);
SetFieldFixedWidth(50);
SetFieldLengthMean('caaa',False);
end;
with dg4 do begin
ColWidths[ColCount-3]:= 40;
ColWidths[ColCount-2]:= 20;
ColWidths[ColCount-1]:= 20;
Row:= 1;
end;
end;Where Clause parameter is fMasterCodeID, as shown above. This procedure is work as expected, except flicker on the grid.
i want more smooth and no flicker.
i tried use something like fGrid4.Refresh, and fGrid4.refresh(true) but not work.
many thanks.
What's wrong with the full database file encryption already included in the unit?
how to use full database file encryption/decryption? so, just my app can use(read/write) to the database.
i tried to search in this forum and the documentation, but i can't found it, or maybe i don't understand.
can u give some simple code?
thank you very much.
hi,
i have problem with default value.
i add fTest field for table TSQLFile on MainDemo project like following:
TSQLFile = class(TSQLRecordSigned)
public
fName: RawUTF8;
..
fTest:Boolean;
published
property Name: RawUTF8 read fName write fName;
...
property Test: Boolean read fTest write fTest default True;
end;but if i insert new record, with ignoring Test field, the Test field is set to 0 (not 1 as expected on default value).
and if add new field (TSQLFile have contained data)
property Test2: Boolean read fTest2 write fTest2 default True;then Test2 value will null (not 1 as expected on default value)
and then error will occured on DrawcheckBox in SQLite3UI unit.
thanks
i don't know how to implement TOnComponentCreated, TOnComponentCreate, TOnComponentValidate in SQLite3UIEdit, and
what is coresponding with SQLite3UIOption.
how i can to override just bound of component coresponding field that setted in SetRecord(..)?
plase give me simple sample code so i can learn it..
and could you fix the SQLitel3UIOption since it's still depend on TAdvGlowButton.?
thanks.
ok, now i understand.
thank you.
i think i more understand now, and i'll learn the documentation.
thanks..
so, did you mean that i just need to change Trigger Definition and then Framework will automatically updated for me?
or i still need to check or call some method?
thanks.
i'm sorry for not clear of my question.
i hope with code sample can make it more clear.
in dataset class perspective, i can do like this:
(...)
Database:TDatabase;
Table:TTable;
TableName:string;
FieldType:TFieldType;
FieldName:string;
i,j:integer;
begin
for i:=0 to Database.TableCount-1 do
begin
Table:=Database.Tables[i];
TableName:=Table.TableName;
for j:=0 to Table.FieldCount-1 do
begin
FieldName:=Table.Fields[i].FieldName;
FieldType:=Table.Fields[i].FieldType;
(....)
end;
end;
end;the point is i want to populate all Table(TSQLRecord) and Published Field Properties that registered in Database;
maybe something like this:
(...)
Database:TSQLDatabase;
Table:TSQLRecord;
TableName:RawUTF8;
FieldType:TSQLFieldType;
FieldName:RawUTF8;
i,j:integer;
begin
for i:=0 to Database.TableCount-1 do
begin
Table:=Database.Tables[i];
TableName:=Table.SQLTableName;
for j:=0 to Table.FieldCount-1 do
begin
(....)
end;
end;
end;how is exactly do that with our framework.?
please give me simple code how to use TSQLRecord.RecordProps with this perspective, so i can learn RTTI step by step.
thank you..
ok. i think i'll try this.
much to be learn about framework..
thanks..
i want to pupulate Database Object especially TSQLRecord and Coresponding Fields and Dependencies,
and then fill it to specific table like follow:
TSQLTableList=class(TSQLRecord)
protected
public
AppVersion:TAppVersion;//default avUltimate
ClassName:RawUTF8;
ClassParent:RawUTF8;
SQLTableName:RawUTF8;
CSVFieldVersion:RawUTF8;
end;
TSQLFieldList=class(TSQLRecord)
protected
public
Owner:TSQLTableList;
FieldName:RawUTF8;
FieldType:RawUTF8;//translated from TSQLFieldType
Bound:TRect;//Set bound to Editor
Defendencies:RawUTF8;//translated from Object e.g TSQLRecord or Enumeration
///.
//.
end;if i have Table definition like this:
TSQLItem= class(TSQLBaseMaster)
private
protected
public
fItemIsSold:Boolean;
fItemIsBought:Boolean;
fItemIsInventoried:Boolean;
fIncomeAccount:TSQLAccount;
fExpenseAccount:TSQLAccount;
fInventoryAccount:TSQLAccount;
fSellingUnit:TSQLItemUnit;
fSellUnitMeasure:Double;
fBuyingUnit:TSQLItemUnit;
fBuyUnitMeasure:Double;
fCurrency:TSQLCurrency;
fQuantityOnHand:Double;
fAverageCost:Currency;
fValueOnHand:Currency;
fItemIsTaxedWhenSold:Boolean;
fSellingTaxCode:TSQLTaxCode;
fBaseSellingPrice:Currency;
fPriceIsTaxInclusive:Boolean;
fTaxInclusiveSellingPrice:Currency;
fTaxExclusiveSellingPrice:Currency;
fItemIsTaxedWhenBought:Boolean;
fBuyingTaxCode:TSQLTaxCode;
fTaxInclusiveBuyingPrice:Currency;
fTaxExclusiveBuyingPrice:Currency;
fItemCategory: TSQLItemCategory;
fItemGroup: TSQLItemGroup;
published
.
end;the TSQLTableList will contained as such:
AppVersion ClassName ClassParent SQLTableName CSVFieldVersion
avUltimate TSQLItem TSQLMasterBase Item ID,Number,Name,ItemIsSold,ItemIsBought,ItemIsInventoried,IncomeAccount,ExpenseAccount,InventoryAccount,SellingUnit,SellUnitMeasure,BuyingUnit,BuyUnitMeasure,Currency,QuantityOnHand,AverageCost,ValueOnHand,ItemIsTaxedWhenSold,SellingTaxCode,BaseSellingPrice,PriceIsTaxInclusive,TaxInclusiveSellingPrice,TaxExclusiveSellingPrice,ItemIsTaxedWhenBought,BuyingTaxCode,TaxInclusiveBuyingPrice,TaxExclusiveBuyingPrice,ItemCategory,ItemGroup
avProfessional TSQLItem TSQLMasterBase Item ID,Number,Name,ItemIsSold,ItemIsBought,ItemIsInventoried,IncomeAccount,ExpenseAccount,InventoryAccount,SellingUnit,SellUnitMeasure,BuyingUnit,BuyUnitMeasure,Currency,QuantityOnHand,AverageCost,ValueOnHand,ItemIsTaxedWhenSold,SellingTaxCode,BaseSellingPrice,PriceIsTaxInclusive,TaxInclusiveSellingPrice,TaxExclusiveSellingPrice,ItemIsTaxedWhenBought,BuyingTaxCode,TaxInclusiveBuyingPrice,TaxExclusiveBuyingPrice,ItemCategory,ItemGroup
avStandard TSQLItem TSQLMasterBase Item ID,Number,Name,SellingUnit,SellUnitMeasure,BuyingUnit,BuyUnitMeasure,QuantityOnHand,AverageCost,ValueOnHand,BaseSellingPrice,ItemCategory,ItemGroup
avSimple TSQLItem TSQLMasterBase Item ID,Number,Name,SellingUnit,SellUnitMeasure,BuyingUnit,BuyUnitMeasure,QuantityOnHand,AverageCost,ValueOnHand,BaseSellingPrice,ItemCategory,ItemGroupavUltimate row is the row that automatically populated,
and three next row is filled manualy..
how to do that...?
thanks...
but i think there is some isues with ovorriding InitializeTable, in this case adding trigger to the database.
if i need to modify the trigger definition, i must drop the table first, and i'll lose data contained in the table.
i know that i can change trigger definition in the third party DB administrator e.g SQLite Expert, but i think is better to do that with pure delphi code in our frame work.
is possible to create other DB objects e.g trigger adhering to TSQLRecord object, View,.. without breaking OOP and our framework?
thank for all of you in this forum for supported.
oh...finaly.., thank you very much...