You can download the updated file and samples (also compiled), from the archive file SynopseSQLite3.zip
There are 3 sample applications up to now, which purpose is to:
- show In-Memory ORM
- show SQLite3-based ORM
- basic Client/Server ORM
Sample 01 - In Memory ORM
purpose of this sample is to show the basic
ORM usage of the framework:
- a TSampleRecord class is defined in Unit1.pas
- a static server (i.e. in-memory database) is initialized
(see
TSQLRestServerStatic.Create below);
it will store the data in a JSON file in the disk and
won't require
the SQLite3 database engine
- the purpose of the form in Unit1.pas is to add a record to
the
database; the Time field is filled with the current
date and time
- the 'Find a previous message' button show how to perform a
basic query
- on application quit, the Database.Destroy will update the
JSON file
- since the framework use UTF-8 encoding, we use some basic
functions for
fast conversion to/from the User Interface; in real
applications,
you should better use our SQLite3i18n unit and the
corresponding
TLanguageFile.StringToUTF8() and
TLanguageFile.UTF8ToString() methods
- note the tiny size of the EXE (since we don't use SQLite3),
less than
80KB with LVCL
Sample 02 - Embedded SQLite3 ORM
purpose of this sample is to show embedded
SQLite3 database usage:
- a TSampleRecord class is defined in Unit1.pas
- a SQLite3 server is initialized (see
TSQLRestServerDB.Create below) and
will work embedded, i.e. not in Client/Server mode
here
- the CreateMissingTables method will create all necessary
tables in the
SQLite3 database
- the purpose of the form in Unit1.pas is to add a record to
the
database; the Time field is filled with the current
date and time
- the 'Find a previous message' button show how to perform a
basic query
- since the framework use UTF-8 encoding, we use some basic
functions for
fast conversion to/from the User Interface; in real
applications,
you should better use our SQLite3i18n unit and the
corresponding
TLanguageFile.StringToUTF8() and
TLanguageFile.UTF8ToString() methods
- note that you didn't need to write any SQL statement, only
define a
class and call some methods; even the query was made
very easy (just an
obvious WHERE clause to write)
- thanks to the true object oriented modeling of the
framework, the same
exact Unit1 is used for both static in-memory database
engine, or
with SQLite3 database storage: only the
TForm1.Database object creation
instance was modified
- look at the tiny size of the EXE (even with SQLite3 engine
embedded), less
than 400KB with LVCL
Sample 03 - NamedPipe Client-Server
purpose of this sample is to show
Client/Server SQLite3 database usage:
- a TSampleRecord class is defined in Unit1.pas
- this sample uses down projects, Project03Client.dpr and
Project03Server.dpr
- a SQLite3 server is initialized in Project03Server
- the CreateMissingTables method will create all necessary
tables in the
SQLite3 database
- one or more client instances can be run in
Project03Client
- the purpose of the Client form in Unit1.pas is to add a
record to the
database; the Time field is filled with the current
date and time
- the 'Find a previous message' button show how to perform a
basic query
- since the framework use UTF-8 encoding, we use some basic
functions for
fast conversion to/from the User Interface; in real
applications,
you should better use our SQLite3i18n unit and the
corresponding
TLanguageFile.StringToUTF8() and
TLanguageFile.UTF8ToString() methods
- note that you didn't need to write any SQL statement, only
define a
class and call some methods; even the query was made
very easy (just an
obvious WHERE clause to write)
- thanks to the true object oriented modeling of the
framework, the same
exact Unit1 is used for both static in-memory database
engine, or
with SQLite3 database storage: only the
TForm1.Database object creation
instance was modified
- look at the tiny size of the EXE (even with SQLite3 engine
embedded), less
than 400KB for the server, and 80KB for the client,
with LVCL
31 reactions
1 From Alin - 24/01/2010, 19:57
thank you ... i`ll look on those ASAP
2 From iPasha - 01/02/2010, 22:09
Amazing job! Thank you very much!
I'm very impressed!
3 From mingodad - 02/02/2010, 16:37
An interesting idea, I'm looking for something like this for a long time,.
Do you have a public svn server to follow the development of it ?
I'm interested on the the freepascal port and with it the wince version too.
4 From mingodad - 03/02/2010, 11:47
I'm trying compiling it with freepascal trunk and getting several errors, mainly because you are using the same names for structures and functions on your wrapper/mirror of RTTI, why not use a slight variation when naming then ?
I did some modifications that allow it to compile a bit more with freepascal, how I can send it to you ?
5 From Arnaud Bouchez - 04/02/2010, 11:43
I'll try soon to compile the framework with FPC.
There is no svn server available up to now. This may be a good idea! Which one do you think could fit our needs?
You can email me the code at abouchez at magic dot fr
Thanks a lot for your interest and support! That's GREAT!
6 From mingodad - 05/02/2010, 12:40
I'm using google http://code.google.com/projecthosti... it's easy and free, also there is sourceforge http://www.sf.net.
7 From A. Bouchez - 05/02/2010, 20:25
Thanks for the advice.
I'll work on the framework on next Saturday and Sunday, trying to validate FPC 2.4 as a compiler.
More to come on this website! Stay tuned!
8 From Martin - 06/02/2010, 22:33
I have 2 questions:
1) Can your framework works with TCP/IP: one PC with server application (and database) and one or more PC with clients application. If yes can you made a demo about this.
2) OK for simple query I can use ORM but if I need call a complex query can I call it with "old" way? How?
Sorry but I don't have found a your forum or mail to post you these question.
9 From A. Bouchez - 07/02/2010, 11:14
1) the framework can work with TCP/IP, thanks to the HTTP protocol: the overhead from a proprietary TCP/IP protocol is quite null, and HTTP/1.1 is well supported and defined.
2) From the TSQLRestClientURI class, you can call a complex query, whatever SQL select statement you want with the List() method and the SQLWhere parameter. If you want a complete and complex statement (with JOIN and UNION e.g.) you can use the ExecuteList() method, with its SQL parameter: it returns a table with all result rows. Note that such complex queries are handled perfectly by the SQLite3 engine, but not by our limited (but faster and smaller in code size) embedded static database engine.
3) this is the right place to post a question, don't be shy!!!!
10 From Martin - 08/02/2010, 00:01
OK. I have create a small demo about HTTP Server/Client (similar your demo 03). However my demo have some problems: when I try to add a new record to the database I get an error (but if I open with a SQLite editor the database I can find the new record), besides if I search the record with "Find a preview message" I get always "No found". I don't understand the problem. Can I send you my demo so you can fix it and add it on your package? How?
I think your SQLite3 Framework is a very good product however without an officail documentation the people (like me) need of many small demo to understand how use all the features.
11 From A. Bouchez - 08/02/2010, 13:55
There was indeed an issue in the HTTP Server/Client. The classes worked without problem in the regression test program, but had some difficulties with connection/unconnection of TCP/IP streams. In the regression test program, the connection was kept alive: so this error wasn't triggered.
This has been fixed in the 1.4 today's release.
A dedicated HTTP Server/Client is provided in this 1.4 release.
I'm writing currently official documentation for the framework.
Thanks for your interest.
12 From Martin - 08/02/2010, 16:28
Now works very well. Thanks. I hope is not a problem if I bugs/problems feedback. This is a great project.
13 From VidHack - 09/02/2010, 01:03
Thank You for the project and the recent update. Wanted to let you know that the new server Project04Server.exe Fails on WinXP. I get an exception. Client ran fine. This is the one in the EXE folder. Thanks for your work though.... Great stuff. I could send you a screenshot if you would like. hmmmm, just thinking out loud, I am connected to the internet, wonder if already having an IP assigned is a problem? Oh well, let me know if you need more data....
14 From VidHack - 09/02/2010, 01:19
Here is the exception, from Delphi 2007, trying to compile....
First chance exception at $7C812AFB. Exception class ECrtSocket with message 'Socket Creation error on 0.0.0.0:8080 (10048)'. Process Project04Server.exe (3160)
Note: I am using DHCP from my router, so cant use 127.0.0.1 or 0.0.0.0. Seems the app should check for existing IP and give a error msgbox, not a hard crash. Thanks for your effort, still an awsum project!
15 From A. Bouchez - 09/02/2010, 10:46
This exception is not about the framework, but your local PC configuration. You may have another server running on the 8080 port (perhaps the same server run more than once!). Just change the port number.
In a real project, you'll have to put a try..except block to protect the server class creation. The programmer has to write its own error handling, not the framework!
In the sample applications, I didn't put all these try..except protections, in order to provide an easy to read code.
You can use 0.0.0.0 as binding adress for a TCP/IP server: it doesn't have anything to do with DHCP, and 127.0.0.1 (=localhost) and 0.0.0.0 (=local binding) are always valid IP addresses.
Thanks for your interrest!
16 From A. Bouchez - 09/02/2010, 11:02
Another possibility is that your software firewall didn't allow you to bind the specified TCP/IP port. You must allow the server to access to this resource. In doubt, unable your XP firewall.
17 From Sofija - 09/02/2010, 17:08
What are the chances to have this project support Firebird, or to implement adapter pattern for database connectivity that would make supporting another database easier?
18 From A.Bouchez - 09/02/2010, 18:55
100% chances if you implement it. That's Open Source magic!
See the SQlite3.pas unit, especially the TSQLRestServerDB class - just some methods to implement according to the targeted database. To be more precise, all Engine*() virtual methods and few others. I guess Firebird could be added.
I think direct connectivity (i.e. object inheritance) is better than visual components in real apps.
I think the FPC or MSEGUI binding to Firebird are a good start for such implementations.
Perhaps the core SQLite3Commons should be modified for some SQL commands, e.g. the CREATE TABLE which are more specific for Firebird than for SQLite3. Since I'm no Firebird user, I can not easily help you about the SQL statement to write, but I can help you changing the framework in order to accept any database particularity.
The purpose of our framework was to be fast and without any dependencies, so using an external client/server database like Firebird is not perhaps worth it. What for do you need Firebird, which can not be handled by SQLite3 with our Client/Server approach of the framework?
19 From VidHack - 09/02/2010, 23:15
Thanks for the firewall tip. I shut off my proxy server running on Port:8080 and your server/client demo worked great. No problem compiling! Thanks for your work and troubleshooting tip. Enjoy.
20 From Mohammed Nasman - 10/02/2010, 07:33
Another vote for Sofija suggestion.
limiting your framework to sqlite make it less useful tool, specially for client/server applications, because most users uses sqlite lite for single user applications.
also I think you need better sample, I have download your framework, but I haven't get used to do with the samples you provided, maybe doing "MasterApp" with your sample will help a lot
21 From A. Bouchez - 10/02/2010, 08:38
Our framework is natively Client/Server, so why use a Client/Server database inside a Client/Server framework?
The framework removes the singe user limitation of SQLite3. That was one of its purpose.
There is a lack of documentation, today... still working on it. But the code itself is a LOT commented. And we began providing samples. A "Master App" is coming, but we are using some proprietary UI components here (from TMS) so it's difficult to give the source code and not allowing you to compile it without paying a third-party company.
It's not a RAD framework (use Delphi VCL for that), but its aim is to build code-based applications. For our applications, it was more powerful than the RAD approach, and solve a lot of RADiism: code mixed within the UI, design-fast-try-to-maintain approach, not standalone applications.
We didn't want to reinvent the wheel (as we use to say in France): there are a lot of good DB framework existing, especially the VCL or some other projects like MSEGUI. Our goal was to have a Client/Server REST light solution for object persistence in some of our softwares.
22 From Mohammed Nasman - 10/02/2010, 09:00
I think when your documentation completed, it will give us better idea about how to use it, specially for people who haven't used such framework before like me .
and when your masterApp is ready it will help alot to understand how the framework working, and for any one who would like to test it, could download trial version of TMS components, to understand how it work, and then apply the concept to his/her preferred components.
23 From A. Bouchez - 10/02/2010, 09:11
Thanks Mohammed for your interest.
You're boosting me!
I'm very surprised with the quality and number of feedback about our framework.
Delphi is not dead, indeed!!!!
24 From Mohammed Nasman - 10/02/2010, 09:33
Thank you for your great work.
I'm more happy to see such a framework with Delphi, Delphi will not dead because there are a lot go great developers boost it with quality work as you
25 From Sofija - 10/02/2010, 13:11
I liked the idea of lightweight and fast framework that could provide ports for non Delphi clients. That is really great because one could write a server that could work with web, mobile or desktop client apps. I wanted to test performance when it is used with large resultset from Firebird database. There is also embedded version of Firebird that could be used same way as SQLite. Firebird can be very optimized and really is a great database engine.
I'm not sure about your plans, but, it may be a good idea to have complete base/abstract classes for database connectivity and operations, after which descendants would define database specific syntax.
I'm also glad to see that Delphi is not dead , its a very powerful tool.
26 From VidHack - 14/02/2010, 17:47
Fast Hack.....
From Project04Server:
procedure TForm1.FormCreate(Sender: TObject);
begin
Model := CreateSampleModel;
//Server := TSQLite3HttpServer.Create('8080',Model,ChangeFileExt(paramstr(0),'.db3'));
Server := TSQLite3HttpServer.Create('8080',Model,':memory:');
Server.DBServer.CreateMissingTables;
end;
WOW... very impressive! An IN-MEMORY, shared database, your framework is great! Thanks.
27 From A.Bouchez - 15/02/2010, 07:53
Yes, since we use SQLite3 library, all features of this database are available to the framework, like IN-MEMORY database.
I've used it in the tests regressions embedded in SQLite3Commons.pas in order to validate both file-based and in-memory database.
Thanks for publishing the trick
28 From E. Hernandez - 10/03/2010, 14:45
Can anyone provide me a sample of how to use the encryption?
Thanks
29 From A. Bouchez - 10/03/2010, 15:26
The encryption is tested in the SQLite3_Test function of the SQLite3.pas unit. You'll find basic encryption usage example in there.
In short: just use CreateSQLEncryptTable(password) to change the global current password, which will encrypt and uncrypt your file on the fly.
You can use ChangeSQLEncryptTablePassWord() to change the encryption password of a given file, or uncrypt it (if you put '' as a password, it means not crypted).
See the comments of both these functions to get details.
Note that encryption is common for the whole engine: you can't mix encrypted and not encrypted files.
30 From Micha - 09/04/2010, 15:37
Hello
It seems that I'm too stupid for this.
I want to get a list of records from a table, but I don't get it.
What I have:
A table with 10 columns
I want a list of records matching a certain value within a certain column.
What do I need? How is the correct code to get the list?
Thanks in advance!
Micha
31 From A.Bouchez - 21/06/2010, 11:17