Synopse Open Source

35 reactions

• 1 From Mohammed Nasman - 14/03/2010, 18:28

  A lot of new great stuff.

  Thank you for your hard work.

  Downloading....

• 2 From Martin - 17/03/2010, 18:43

  Good news. I waiting for the full demo before use it.

• 3 From Saan - 19/03/2010, 16:29

  Fantastic,

  Waiting the documentation

• 4 From Saan - 19/03/2010, 16:33

  Fantastic

• 5 From Rapid - 23/03/2010, 13:53

  How about to add encryption for base file?

• 6 From Arnaud Bouchez - 23/03/2010, 14:11

  Encryption is handled by the framework, at the file level.
  It is very fast and easy to use.
  See the CreateSQLEncryptTable() and ChangeSQLEncryptTablePassWord() procedures in the SQLite3.pas unit. And also the way it is unitary tested (in the same unit).
  The only limitation is that this encryption is global for ALL SQLite3 databases access at the same time. You can't mix encrypted and plain files at the same time. But you can convert an encrypted file into another password or plain format, and vice-versa, with ChangeSQLEncryptTablePassWord().

• 7 From Edwin - 25/03/2010, 15:34

  Very promising!
  Would you include FTS3 by default? Come on! It's only additional tens KB!

  Since the detailed document is not ready, I've no idea about how to:
  1. query a list of objects in a specified order (i.e. ORDER BY),
  2. how to handle master-detail (i.e. One-to-many relationship),
  3. how to perform FTS search.
  4. when ORM is not enough, how to directly use SQL commands? Is there any limits?

  Waiting for the document, and it's really very promising!

• 8 From A. Bouchez - 25/03/2010, 19:19

  You can specify any SQL statement in any query. By default, you query your record by ID, but you can get a list of records, resulting from any SQL query statement. The only limitation is the SQLite engine itself.
  One-To-Many relationship is handled simply by defining a TSQLRecord descendant published property. The corresponding SQL will be the ID of the corresponding class. Note that for the framework, one class corresponds to one unique table in the database.
  Including FTS3 is just a matter of a compile definition. For me, it rocks to enable it in the Project/Options dialog box.

  Working with FTS3 is very easy. Just ask for record IDs, as you do with normal FTS3 query in SQLite3, and then get the IDs record content by the ORM oriented methods.

• 9 From Pascal - 27/03/2010, 11:16

  Impressed and waiting on extra samples/docu.

  At the moment it's difficult for me to understand how I can do deletes or inserts using your framework.

  'Delete from X where Y'
  'Insert into X values Y'

• 10 From A. Bouchez - 27/03/2010, 19:20

  In the test program of the framework, you have samples of deleting and inserting. Use Delete() and Add() methods of TSQLRestClient or TSQLRestServer.
  You can either directly call the SQLite3 library with corresponding SQL statements, if don't like the ORM approach of Delete() and Add().

• 11 From Saan - 28/03/2010, 15:18

  How can I retrieve multiple records from 2 tables (one to many relationship)

  Regards

• 12 From A. Bouchez - 28/03/2010, 20:24

  One to many relationship are made easy with the List() method of TSQLRestClient. For direct access to the database (i.e. without any Client/Server overhead), you can use the List() method of TSQLRestClientDB. Both methods require a standard SQL statement.
  Both methods return a TSQLTableJSON instance, by which you can get all the resulting rows of data.
  A strict ORM approach is also available, from any TSQLRecord published property.

• 13 From Esmond - 30/03/2010, 01:37

  The ORM approach is very slick but it seems to be restrictive in how the database/table is created in that it's purely based on a delphi record. Is there any way of extending this?

  FTS3 - an example would be great. I tried - Server.EngineExecuteAll('CREATE VIRTUAL TABLE FTSClients USING fts3();'); after creating the db and got and error. I've only just started looking at SQL and realised that the 'LIKE' operator does what I need, so still happy.

  Thanks

• 14 From A.Bouchez - 30/03/2010, 08:08

  With our framework you can mix ORM approach and "normal" SQL approach, via the TSQLTable[JSON].
  I didn't test much the FTS3 feature, since I don't use it in my programs. I still rely on a much more powerful pure Delphi full text indexing solution (the one I developed for http://bouchez.info/ictus.html or http://bouchez.info/roc.html e.g.).

• 15 From Esmond - 30/03/2010, 12:26

  Likewise I've written a free text index in Delphi. It's lightening fast by using a very flat file structure, but can't be updated on the fly. I'm looking to convert it to using UTF8 so your code gives some useful insight.

• 16 From JBR - 31/03/2010, 16:49

  I think we are all waiting for a more complete demo ! The one existing are very very sparse...

• 17 From Martin - 02/04/2010, 18:42

  Any news about your demo or documentation?

• 18 From A. Bouchez - 02/04/2010, 20:02

  I had to rewrite some part of the framework to get rid of some User Interface dependencies (like the payware TMS components).
  The http://blog.synopse.info/post/2010/... GDI+ unit, with anti-aliased drawing, are part of it.
  For the moment, I was not able to sent mode than very low level demo, as already post with the framework. This is an ORM framework, based on source code, not RAD. You have classes, not visual components. For the moment, the whole framework is documented, and all classes and methods are detailed. That's a start!

• 19 From Micha - 10/04/2010, 11: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.
  For example: All songs of the genre 'Asian Music'. ("Select * From Mp3Table Where Genre='Asian Music'")

  What do I need? How is the correct code to get the list?

  Thanks in advance!

  Micha

• 20 From Micha - 30/04/2010, 16:04

  Hello @all

  Is there no-one who can help me with my issue?
  In the meanwhile I tried so many things, but I still don't get it.

  I tried to find any information using google etc., but I don't find an appropriate example.

  I would be very glad if someone could help me with it.

  Micha

• 21 From A.Bouchez - 03/05/2010, 18:49

  For such low level SQLite3 work, take a look at TSQLRequest.Execute methods, and see how to loop through all rows.

  var R: TSQLRequest
  value: Int64;
  res: integer;
  begin
  try
  R.Prepare(aDB,'Select * From Mp3Table Where Genre=''Asian Music'';'); // note the '' double quotes inside the ' string '
  repeat
  res := R.Step;
  if res=SQLITE_ROW then begin // we got some data
  value := sqlite3_column_int64(R.Request,0);
  // use other low level functions to get your data
  end;
  until res=SQLITE_DONE;
  finally
  R.Close; // always release statement
  end;
  end;

  With the framework, you should better create a TSQLRecord descendant class with the appropriate fields corresponding to your database, then use a Client/Server approach as shown in the supplied samples.

• 22 From Micha - 16/05/2010, 14:01

  Hello Arnaud

  Thanks for your answer and sorry for my delayed reaction. I was offline in the last two weeks.
  I'm really sorry, but I still don't get it work.
  The function .Prepare expects a TSQLHandle. How do I declare/get this handle? How to I 'associate' the handle with my database?
  Of course, I use Model like in the samples.

  I tried this:
  procedure TForm1.Button2Click(Sender: TObject);
  var
  R: TSQLRequest;
  Mp3Record: TSQLMp3Record;
  value: Int64;
  res: integer;

  begin
  Mp3Model := CreateMp3Model;
  Mp3Record := TSQLMp3Record.Create;
  Database := TSQLRestServerDB.Create(Mp3Model, MediaPath);
  try
  R.Prepare(Database, 'Select * From Mp3Table Where Genre = ''Asian Music'';'); // note the '' double quotes inside the ' string '
  repeat
  res := R.Step;
  if res = SQLITE_ROW then
  begin // we got some data
  value := sqlite3_column_int64(R.Request, 0);
  // use other low level functions to get your data
  end;
  until res = SQLITE_DONE;
  finally
  R.Close; // always release statement
  end;
  Mp3Model.Free;
  Mp3Record.Free;
  Database.Free;
  end;

  Here I get the error:
  E2010 Inkompatible Typen: 'TSQLHandle' und 'TSQLRest'

  Where do I 'fill' the Mp3Record with the data fetched from the database?

  I hope someone can answer my questions and help me to solve the problem.

  I'm sorry that I'm not able to get it on my own. Till now I used PHP and MySQL or VB.NET and MSSQL, but I didn't use SQL or SQLite with Delphi yet.

  Micha

• 23 From A. Bouchez - 16/05/2010, 14:50

  You are mixing some high-level (like Model, TSQLRecord, etc...) and low-level commands here (like TSQLRequest or sqlite3_column_int64).

  I suggest you use only high-level stuff, by using TSQLRestClientDB and TSQLTableJSON
  Therefore your code will work stand-alone, and could evolved in a Client/Server approach without any modification. And you'll increase the speed because of some caching mechanism implemented in the high-level classes of the framework.

  var Table: TSQLTableJSON;
  Model: TSQLModel;
  Client: TSQLRestClient;
  Rec: TSQLMp3Record;
  Row: integer;

  Model := TSQLModel.Create([TSQLMp3Record],'root');
  Rec := TSQLMp3Record.Create;
  try
  Client := TSQLRestClientDB.Create(Model,'DemoFile.db');
  try
  Table := Client.List([TSQLMp3Record],'*',Genre = ''Asian Music'');
  if Table<>nil then // =nil on any error
  try
  Rec.FillPrepare(Table);
  for Row := 1 to Table.RowCount do begin
  // get individual field value
  writeln('ID=',Table.GetAsInteger(Row,0));
  // fill whole record at once
  Rec.FillRow(Row);
  end;
  // another possibility to loop through records:
  Rec.FillRewind; // or Rec.FillPrepare(Table); if not already done before
  while Rec.FillOne do
  writeln('ID=',Rec.ID);
  finally
  Table.Free;
  end;
  finally
  Client.Free;
  end;
  finally
  Rec.Free;
  Model.Free;
  end;

  See the samples

• 24 From Micha - 16/05/2010, 20:45

  Hello Arnaud

  Thanks for your quick answer. Sorry if I don't understand everything.
  What I have is just a small (1,7 MB) database with the following tables:
  Mp3Album (just holds every album found in my library)
  Mp3Artitst (holds every artist found in my library)
  Mp3Genre (holds every genre...)
  Mp3Year (holds every year...)
  Mp3Record
  'Mp3Record' is the 'main' table with the following columns:
  ID, Path, Filename, Title, Artist, Album, Genre, Track, Year, Bitrate, Samplerate, Duration

  From this table I would like to fetch data according a certain field (column).
  I crated the database with your framework using a Model reflecting my needs.
  Extraction of my 'Model' unit:
  type
  TSQLMp3Record = class(TSQLRecord)
  private
  fPath: RawUTF8;
  fFilename: RawUTF8;
  fTitle: RawUTF8;
  fArtist: RawUTF8;
  fAlbum: RawUTF8;
  fGenre: RawUTF8;
  fTrack: RawUTF8;
  fYear: RawUTF8;
  fBitrate: RawUTF8;
  fSampleRate: RawUTF8;
  fDuration: RawUTF8;

  published
  property Path: RawUTF8 read fPath write fPath;
  property Filename: RawUTF8 read fFilename write fFilename;
  property Title: RawUTF8 read fTitle write fTitle;
  property Artist: RawUTF8 read fArtist write fArtist;
  property Album: RawUTF8 read fAlbum write fAlbum;
  property Genre: RawUTF8 read fGenre write fGenre;
  property Track: RawUTF8 read fTrack write fTrack;
  property Year: RawUTF8 read fYear write fYear;
  property Bitrate: RawUTF8 read fBitrate write fBitrate;
  property SampleRate: RawUTF8 read fSampleRate write fSampleRate;
  property Duration: RawUTF8 read fDuration write fDuration;
  end;
  ...

  Extraction of my main unit:
  ...
  Mp3Model := CreateMp3Model;
  Mp3Model.AddTable(TSQLMp3Genre);
  Mp3Model.AddTable(TSQLMp3Artist);
  Mp3Model.AddTable(TSQLMp3Album);
  Mp3Model.AddTable(TSQLMp3Year);
  Database := TSQLRestServerDB.Create(Mp3Model, MediaPath);
  TSQLRestServerDB(Database).CreateMissingTables;

  Then I 'scanned' my Mp3 files and added the collected data to the appropriate tables:
  if Database.Add(Mp3Record, true) = 0 then
  ...
  Mp3Record.Free;
  ...

  The tables 'Mp3Album', 'Mp3Artist', 'Mp3Genre' and 'Mp3Year' are just needed to 'create a selection menu' from which I can make a choice.
  With this choice I would like to 'filter' or better said select all records from Mp3Record which match this criteria. That's all.

  Thanks
  Micha

• 25 From A. Bouchez - 16/05/2010, 22:38

  Why are you using TSQLRestServerDB here?
  Are you in Client/Server?
  Normaly, you don't have to use the TSQLRestServerDB to access directly the database.
  You use a TSQLRestClient* instance.
  If you want only a local access to the DB, use TSQLRestClientDB.

  Then to select all records from a specified genre:

  Table := Client.List([TSQLMp3Record],'*',Genre = ''Asian Music'');
  if Table<>nil then // =nil on any error
  try
  Rec.FillPrepare(Table);
  while Rec.FillOne do begin
  // here Rec contains one matching MP3Record
  writeln('ID=',Rec.ID);
  end;
  finally
  Table.Free;
  end;

  Couldn't it be easier?

• 26 From Micha - 17/05/2010, 09:23

  Hi Arnaud

  Sorry when I'm confusing you, but I'm confused too.
  As I said, this all is very new to me. I don't know how to use TSQLRestServerDB and TSQLRestClientDB.
  I just 'studied' the samples and thought I'm on the right way.
  No, I don't use (there's no need) for a client/server construct.
  I just want to read and write a small local database.

  I tried the following;
  procedure TForm1.Button2Click(Sender: TObject);
  var
  Client: TSQLRestClient;
  Resp: TSQLTable;
  Mp3Model: TSQLModel; // 'Model' for database

  begin
  Client := TSQLRestClientDB.Create(Mp3Model, 'C:\Daten\Programmierung\Delphi\SynopseTest\Songs.db3');
  Resp := Client.List([TSQLMp3Record],'*', 'Genre = "Asian Music"');
  if Resp <> nil then // =nil on any error
  try
  Mp3Record.FillPrepare(Resp);
  while Mp3Record.FillOne do begin
  // here Mp3Record contains one matching MP3Record
  ListBox1.Items.Add(IntToStr(Mp3Record.ID));
  // writeln('ID=', Mp3Record.ID);
  end;
  finally
  Resp.Free;
  end;
  end;

  I get an access violation error when the line
  Client := TSQLRestClientDB.Create(Mp3Model, 'C:\Daten\Programmierung\Delphi\SynopseTest\Songs.db3');
  is executed.

  Sorry for my question, but can you give me a complete small script for what I would like to do?
  The database is in the same directory of the program.
  With the search result I want to fill a listbox on a form.

  Thanks again for your help!
  Micha

• 27 From A.Bouchez - 17/05/2010, 10:34

  You didn't initialize the Mp3Model instance, with something like:

  MP3Model := TSQLModel.Create([TSQLMp3Record,TSQLMp3Genre,TSQLMp3Artist,TSQLMp3Album,TSQLMp3Year],'root');

  without forgetting a try..finaly MP3Model.Free end;

• 28 From Micha - 17/05/2010, 23:24

  YES, YES, YES, I got it!
  Thanks so much for your help!
  Here is my small program for a test:
  unit Main;

  interface

  uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, Mp3Data, SQLite3Commons, SQLite3;

  type
  TMainform = class(TForm)
  Ed_DBPath: TEdit;
  Btn_Open: TButton;
  Btn_List: TButton;
  Odlg_DB: TOpenDialog;
  Lb_Result: TListBox;
  procedure Btn_ListClick(Sender: TObject);
  procedure Btn_OpenClick(Sender: TObject);
  private
  { Private-Deklarationen }
  public
  { Public-Deklarationen }
  end;

  var
  Mainform: TMainform;

  MediaPath: String;
  
  implementation

  {$R *.dfm}

  procedure TMainform.Btn_OpenClick(Sender: TObject);
  begin
  if Odlg_DB.Execute then
  begin
  if Odlg_DB.FileName <> '' then
  begin
  MediaPath := Odlg_DB.FileName;
  Ed_DBPath.Text := MediaPath;
  end;
  end;
  end;

  procedure TMainform.Btn_ListClick(Sender: TObject);
  var
  Client: TSQLRestClient;
  Table: TSQLTable;
  Mp3Model: TSQLModel; // 'Model' for database
  Mp3Record: TSQLMp3Record; // Record for database

  begin
  // MP3Model := TSQLModel.Create([TSQLMp3Record, TSQLMp3Genre, TSQLMp3Artist, TSQLMp3Album, TSQLMp3Year], 'root');
  Mp3Model := TSQLModel.Create([TSQLMp3Record], 'root');
  Mp3Record := TSQLMp3Record.Create;
  Client := TSQLRestClientDB.Create(Mp3Model, MediaPath);
  Table := Client.List([TSQLMp3Record],'*', 'Genre = "Asian Music"');
  if Table <> nil then // =nil on any error
  try
  Mp3Record.FillPrepare(Table);
  while Mp3Record.FillOne do
  begin
  // here Mp3Record contains one matching MP3Record
  Lb_Result.Items.Add(Mp3Record.Title);
  end;
  finally
  Table.Free;
  end;

  Mp3Model.Free;
  Mp3Record.Free;
  end;

  end.

  Now I can play with it and include in the actual project.

  Again, thanks so much for your help and your patience!

  Micha

• 29 From A.Bouchez - 18/05/2010, 09:10

  I think you don't free the Client instance here.
  Use the memory leak searching of FastMM4 to find easily such bugs.

• 30 From Micha - 19/05/2010, 14:33

  Hello Arnaud

  You're right. I recognized this too as I implemented the routine into my actual project.
  I added a 'Client.Free' there at the end of the desired procedure.

  Micha

• 31 From A. Bouchez - 19/05/2010, 18:43

  OK!
  It could be a good idea to store the MP3Model and Client into you mainform, and initialize them once once at startup (in the FormCreate) event, and release them during closing (in the FormDestroy).
  Performance and code maintenance will increase.

• 32 From Novalis - 23/05/2010, 11:46

  Hello, may be I found a bug.
  I was wondering why negative numbers were not updated when calling .Update. (it always returned wrong SQL command), so I found this:
  if not (P^ in ['0'..'9'])
  in SQLite3Commons.pas line 7923 and changed to
  if not (P^ in ['-','0'..'9'])

  or should anything else be changed?

• 33 From A. Bouchez - 24/05/2010, 10:16

  Nice catch.

  Fixed in version 1.7.

  I also added a specific unitary test for negative values.

  Thanks for your feedback.

• 34 From A.Bouchez - 21/06/2010, 11:17

  Please discuss about the Synopse SQlite3 Framework in our dedicated forum.

Add ping

Trackback URL : https://blog.synopse.info?trackback/66