Database structure import
lazarus / 16-Databases

----------------------------------------------------------------------
J2M · 2023-06-14 05:40

Hello

I would like to build an app that have access to a Sqlite database.

I am discovering the functionalities of Lazarus to manipulate such
database, and I am wondering if there is a functionality to directly
manipulate or configure the structure of the database in the IDE ? And
to have access easily to the structure of the database with imported
structuration elements of the DB, without managing it outside the IDE.

I am referring to the Windev IDE, where you have a functionality
called "analysis" where you define the structure of the database,
which is then physically connected to whatever db technology you want
: sqlite, access, mysql, ....

This analysis gives you also access in your code to have access to the
content of the database without any SQL functions, and the structure
of the database is also directly declared automatically in the code.

- analysis : https://doc.windev.com/en-US/?2011022

The analysis is synchronized with the project, then some commands give
access to the "file" (elements of the DB)
- find easily : https://doc.windev.com/en-US/?3044036.

Does it exist similar functionality in Lazarus ?

----------------------------------------------------------------------
marcov · 2023-06-14 06:51

I have a small app, and use the below code to create the database if
it doesn't exist:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TSqlCreateFrm.buCreateClick(Sender: TObject);var
newfile:boolean;begin    try    // Since we're making this database
for the first time,    // check whether the file already exists   
newFile := not FileExists(SQLite3Connection1.DatabaseName);     if
newFile then    begin      // Create the database and the tables     
try        SQLite3Connection1.Open;        SQLTransaction1.Active :=
true;         // Here we're setting up a table named "DATA" in the new
database        SQLite3Connection1.ExecuteDirect('CREATE TABLE
"AUTHORS"('+                    ' "id" Integer NOT NULL PRIMARY KEY
AUTOINCREMENT,'+                    ' "AuthorName" Char(128) NOT
NULL,'+                    ' "LastVisited" DateTime NOT NULL,'+      
            ' "LastTorrent" DateTime NOT NULL,'+                    '
"ItemsOld" Integer NOT NULL,'+                    ' "ItemsNew" Integer
NOT NULL,'+                    ' "Missing" Text,'+                   
' "Notes" Text);');         // Creating an index based upon id in the
DATA Table        SQLite3Connection1.ExecuteDirect('CREATE UNIQUE
INDEX "Data_id_idx" ON "AUTHORS"( "id" );');        
SQLTransaction1.Commit;         ShowMessage('Succesfully created
database.');      except        ShowMessage('Unable to Create new
Database');      end;    end;  except    ShowMessage('Unable to check
if database file exists');  end;   SQLite3Connection1.Close; end;

----------------------------------------------------------------------
Zvoni · 2023-06-14 07:28

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i //*snipp*  
      // Here we're setting up a table named "DATA" in the new
database        SQLite3Connection1.ExecuteDirect('CREATE TABLE
"AUTHORS"('+                    ' "id" Integer NOT NULL PRIMARY KEY
AUTOINCREMENT,'+                    ' "AuthorName" Char(128) NOT
NULL,'+                    ' "LastVisited" DateTime NOT NULL,'+      
            ' "LastTorrent" DateTime NOT NULL,'+                    '
"ItemsOld" Integer NOT NULL,'+                    ' "ItemsNew" Integer
NOT NULL,'+                    ' "Missing" Text,'+                   
' "Notes" Text);'); //      *snipp* Errr.... no, you don't create a
Table called "DATA".... LOL

btw: Since it's SQLite,
don't use AUTOINCREMENT in your PK
don't use CHAR(128) --> SQLite ignores this, and assigns TEXT
don't use DATETIME --> use one of the three Storage-classes for
DateTime: TEXT (for ISO-DateTime-String), INTEGER (for UNIXEPOCH),
REAL (for JulianDays)

----------------------------------------------------------------------
paweld · 2023-06-14 09:17

Lazarus doesn't have it, but one user wrote a tool in lazarus that may
solve your problem: https://github.com/flakron-shkodra/LazSqlX