Question about size of sql file with 3 tables but no records.
lazarus / 16-Databases

----------------------------------------------------------------------
jamie · 2026-04-03 22:16

I am porting some old C++ code over and I saw that using the Sqlite3
from FreePascal/Laz when creating a new db file with only 3 tables
which are the same tables that have been created with existing files
from the original C++ code, they seem to be at least twice or more in
file size ?

 The C++ of course used the CPP libraries to work with Sqlite but they
are very noticeably smaller, however, when doing some checks the
contents seem to be correct.

 So I took a simple example from the net using only the Sqlite3 unit
and found the size was still 2 or more larger than I think should be.

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 unit
Unit1; {$mode objfpc}{$H+} interface uses  Classes, SysUtils, Forms,
Controls, Graphics, Dialogs, StdCtrls,Sqlite3,FileUtil; type   {
TForm1 }   TForm1 = class(TForm)    Button1: TButton;    Memo1:
TMemo;    procedure Button1Click(Sender: TObject);  private   public  
end; var  Form1: TForm1; implementation {$R *.lfm} { TForm1
} procedure TForm1.Button1Click(Sender: TObject);Var  DB: PSQLite3; 
ErrMsg: PChar;  SQL: String;  rc: Integer;  procedure ExecSQL(constRef
Statement: String);begin  rc := sqlite3_exec(DB, PChar(Statement),
nil, nil, @ErrMsg);  if rc <> SQLITE_OK then  begin   
Memo1.Lines.Add('SQL error: '+ErrMsg^);    sqlite3_free(ErrMsg);   
Exit;  end;end; begin  Memo1.Clear;  rc := sqlite3_open('test.db',
@DB);  try    // Create first table    SQL := 'CREATE TABLE IF NOT
EXISTS users (' +           'id INTEGER PRIMARY KEY AUTOINCREMENT,' + 
         'name TEXT NOT NULL,' +           'email TEXT UNIQUE NOT
NULL);';    ExecSQL(SQL);     // Create second table    SQL := 'CREATE
TABLE IF NOT EXISTS orders (' +           'id INTEGER PRIMARY KEY
AUTOINCREMENT,' +           'user_id INTEGER NOT NULL,' +         
 'product TEXT NOT NULL,' +           'FOREIGN KEY(user_id) REFERENCES
users(id));';    ExecSQL(SQL);     // Create third table    SQL :=
'CREATE TABLE IF NOT EXISTS products (' +           'id INTEGER
PRIMARY KEY AUTOINCREMENT,' +           'name TEXT NOT NULL,' +      
   'price REAL NOT NULL);';    ExecSQL(SQL);  finally   
sqlite3_close(DB);    Memo1.Lines.Add('File Size
:'+FileSize('test.db').Tostring);    Memo1.Lines.Add('Sqlite File
Version :'+Sqlite3.sqlite3_libversion^);    DeleteFile('test.db'); 
end; end; end.  
That generates ~ 24k of a file size, ~ the same as I get when using
the components in lazarus for SQL functions applying the same Tables
on new DBases but they are like I said like 3 times the size of what I
think they should be.

is it possible the existing files are in Ansi and the newly created
files are using UncodeString ?


Any input on this?

Jamie

----------------------------------------------------------------------
Wallaby · 2026-04-03 22:52

Quote from: jamie on April 04, 2026, 12:16:31 amis it possible the
existing files are in Ansi and the newly created files are using
UncodeString ?

Either that, or different page size - compare DB headers.

----------------------------------------------------------------------
jamie · 2026-04-03 23:26

Ok, Thank you for that header information.

QuoteFile Size :24576
Sqlite File Version :3
Magic Header :SQLite format 3
Page Size used :4096

so I didn't check one of the older one's but I am sure its smaller.

The C++ is a 32 bit and I have been doing this via 64 bit, maybe the
32 bit will be smaller.

Jamie

----------------------------------------------------------------------
Zvoni · 2026-04-07 06:51

Also look at this:
https://sqlite.org/fileformat.html
Specifically Point 1.3.7
QuoteThe 4-byte big-endian integer at offset 28 into the header stores
the size of the database file in pages.Look there how many pages are
actually used