Create new DB in run-time

[Alexandr1567]

Hello!

Can anybody say how to create database in run-time using ZEOS?

I'm need:
1. Create database
2. Set PRAGMAs
3. Create tables

If not exists DB file, then it will be created. I know about that. But PRAGMAs don't set.

I trying do it using code:

Code: Select all

var
  ss: TStringList;
begin
  ss := TStringList.Create;
  ss.Add('PRAGMA auto_vacuum=full');
  ss.Add('PRAGMA automatic_index=on');
  ss.Add('PRAGMA busy_timeout=4000');
  ss.Add('PRAGMA cache_size=2000;');
  ss.Add('PRAGMA case_sensitive_like=off;');
  ss.Add('PRAGMA checkpoint_fullfsync=off;');
  ss.Add('PRAGMA collation_list="NOCASE", "RTRIM", "BINARY";');
  ss.Add('PRAGMA compile_options="ENABLE_COLUMN_METADATA", "ENABLE_FTS3", "ENABLE_FTS3_PARENTHESIS", "ENABLE_RTREE", "SOUNDEX", "SYSTEM_MALLOC", "THREADSAFE=1";');
  ss.Add('PRAGMA encoding=UTF-8;');
  ss.Add('PRAGMA foreign_keys=on;');
  ss.Add('PRAGMA freelist_count=0;');
  ss.Add('PRAGMA fullfsync=off;');
  ss.Add('PRAGMA ignore_check_constraints=off;');
  ss.Add('PRAGMA journal_mode=delete;');
  ss.Add('PRAGMA journal_size_limit=-1;');
  ss.Add('PRAGMA legacy_file_format=off;');
  ss.Add('PRAGMA locking_mode=normal;');
  ss.Add('PRAGMA max_page_count=1073741823;');
  ss.Add('PRAGMA mmap_size=0;');
  ss.Add('PRAGMA page_count=8;');
  ss.Add('PRAGMA page_size=4096;');
  ss.Add('PRAGMA parser_trace=;');
  ss.Add('PRAGMA read_uncommitted=off;');
  ss.Add('PRAGMA recursive_triggers=off;');
  ss.Add('PRAGMA reverse_unordered_selects=off;');
  ss.Add('PRAGMA schema_version=128;');
  ss.Add('PRAGMA secure_delete=off;');
  ss.Add('PRAGMA synchronous=full;');
  ss.Add('PRAGMA temp_store=default;');
  ss.Add('PRAGMA user_version=0;');
  ss.Add('PRAGMA vdbe_addoptrace=;');
  ss.Add('PRAGMA vdbe_listing=;');
  ss.Add('PRAGMA vdbe_trace=;');
  ss.Add('PRAGMA wal_autocheckpoint=1000;');
  ss.Add('PRAGMA writable_schema=on;');
  ss.Add('CREATE TABLE "dbinfo" (');
  ss.Add('  "groupsCount" INTEGER,');
  ss.Add('  "linksCount" INTEGER,');
  ss.Add('  "treeDepth" INTEGER,');
  ss.Add('  "imagesCount" INTEGER,');
  ss.Add('  "major_ver" SMALLINT,');
  ss.Add('  "minor_ver" SMALLINT,');
  ss.Add('  "TimeCreate" TIME,');
  ss.Add('  "DateCreate" DATE,');
  ss.Add('  "LastAccess" DATETIME,');
  ss.Add('  "UID" TEXT,');
  ss.Add('  "name" TEXT,');
  ss.Add('  "desc" TEXT);');
  ZConnection1.Connect;
  if ZConnection1.ExecuteDirect(ss.Text) then
    ShowMessage('ok')
  else
    ShowMessage('fail');
end;

After press it shows me "ok", but without my RAGMAs and table.

Why that?

[marsupilami]

Hello Alexandr1567,

I seem to remember that some pragmas can only be set while you are not in the context of a transaction. Also I seem to remember that Zeos always starts a transaction after connecting to a sqlite database. Maybe you want to check for these things in the sqlite documentation.
Best regards,

Jan

[Alexandr1567]

Yes!1 Something works!

I disable AutoCommit propery of connection.

Can I switch this property when connection eastablished?
e.g,:
Set DB
Open DB
Switch AutoCommit off
Set Pragmas
Shitch AutoCommin On


Cunnectly I've recoonect DB after AutoCommit is switched.

[marsupilami]

Hello Alexandr1567,

I am not 100% sure because I don't use SQLite usually. I prefer the embedded version of Firebird.
So - for safety reasons I would suggest you create your database and then disconnect and reconnect again. Maybe it is a good idea to switch Autcommit before connecting. So something like this:

Set DB
Switch AutoCommit off
Open DB
Set Pragmas
Close DB
Switch AutoCommin On
Open DB

Best regards,

Jan

[Alexandr1567]

Thanks..

Not sure It's a bug or not:
1. Set to TZConnection.Database path to non-exists DB file.
2. Connect and DirectExec PRAGMAs => DB file creates with defined PRAGMAs.
3. Try to create tables..
4. SQL Error: SQL logic error or missing database.

So bad...

But...
1. Set to TZConnection.Database path to non-exists DB file.
2. Connect and DirectExec PRAGMAs
3. Reconnect TZCnnection
4. Try to create tables..
5. All is ok.