Pages: 1

#1 Re: mORMot 1 » Delay executing query when SQLite3 db resides in non-writable folder » 2019-07-16 16:47:30

w5m

A huge thank you to you Arnaud. As you probably realised, I hadn't stumbled across SQLITE_OPEN_READONLY before. I can confirm that your suggestion has resolved the delay.

#2 Re: mORMot 1 » Delay executing query when SQLite3 db resides in non-writable folder » 2019-07-16 14:32:16

w5m

So I've run ProcessMonitor and can see that attempts are still being made to create -journal and -wal files in the same directory as the database, even though I've attempted to issue each of the following in separate test runs...

PRAGMA journal_mode=MEMORY;
PRAGMA journal_mode=OFF;

Any ideas why these are not being applied?

#3 Re: mORMot 1 » Delay executing query when SQLite3 db resides in non-writable folder » 2019-07-16 11:46:54

w5m

I have since come across this SQLite documentation which talks about the creation of temporary files (to faciliate rollbacks amongst other things). I believe that the delay I described in my previous post is SQLite attempting to write -journal (and perhaps -wal) files to the non-writable folder where the database resides.

I have been attempting to execute...

PRAGMA journal_mode=OFF;

...but I'm not convinced it's taking effect.

I modified the TSQLDBSQLite3Connection.Connect procedure as follows...

procedure TSQLDBSQLite3Connection.Connect;
var Log: ISynLog;
begin
  Log := SynDBLog.Enter;
  Disconnect; // force fTrans=fError=fServer=fContext=nil
  fDB := TSQLDatabase.Create(UTF8ToString(Properties.ServerName),Properties.PassWord);
  fDB.ExecuteNoException('PRAGMA journal_mode=OFF;');
  //fDB.SetWalMode(true); // slower INSERT in WAL mode for huge number of rows
  inherited Connect; // notify any re-connection
end;

I have also tried modifying TSQLDBConnection.NewStatementPrepared to include this line immediately before the function result is assigned in both places...

Stmt.Execute('PRAGMA journal_mode=OFF;', False);

This doesn't seem to make any difference to the delay I'm experiencing, so I'm unclear whether the setting hasn't taken effect, or perhaps it has but isn't the source of the delay.

#4 mORMot 1 » Delay executing query when SQLite3 db resides in non-writable folder » 2019-07-16 09:22:48

w5m
Replies: 4

Hi there,

I am using the code below to successfully execute 2 very simple SELECT queries on a SQLite3 database (away from mORMot). I recently discovered that if the database resides in a non-writable folder, the TSQLDBConnectionProperties.Execute function (and more specifically the NewThreadSafeStatementPrepared function within it) takes ~1500 milliseconds to complete for each query, whereas the same database residing in a folder with Write permissions enabled, does not experience a delay when running the same code (i.e. executing the same SELECT queries against it).

Can you think of any reasons why not having Write permission on a folder would cause this delay in query execution? Could the use of prepared statements be attempting to write something back to the folder? Is there a way to run a query without using a prepared statement?

Any thoughts on this would be gratefully received.

Many thanks,
Clive

function GetSqliteResultset(const ADB: String; const APassword: String = ''; const ASQL: String = ''): String;
var
  conn: TSQLDBSQLite3ConnectionProperties;
  db: TSQLDBSQLite3Connection;

  procedure GetResult;
  var
    rows: ISQLDBRows;
  begin
    rows := conn.Execute(ASQL, []);
    while rows.Step do
      ShowMessage(rows.ColumnString('Description') + ', ' + rows.ColumnString('Index'));
  end;

begin
  // Establish sqlite connection, execute passed query and fetch results
  conn := TSQLDBSQLite3ConnectionProperties.Create(ADB, '', '', APassword);
  try
    try
      db := conn.MainConnection as TSQLDBSQLite3Connection;
      GetResult;
    except
      ShowMessage('Error executing SQL or processing results.');
    end;
  finally
    conn.Free;
  end;
end;

procedure TForm1.But_ConnectClick(Sender: TObject);
var
  sql: String;
begin
  sql := 'SELECT TypeDescription AS ''Description'', BranchIndex AS ''Index'' FROM tblBranchOne';
  GetSqliteResultSet(Ed_DbFilename.Text, Ed_Password.Text, sql);

  sql := 'SELECT TypeDescription AS ''Description'', BranchIndex AS ''Index'' FROM tblBranchTwo';
  GetSqliteResultSet(Ed_DbFilename.Text, Ed_Password.Text, sql);
end;

#5 Re: mORMot 1 » Encrypt database » 2014-11-14 15:13:17

w5m

Excellent - thanks for confirming Arnaud. I've got the encryption sorted now.

I found another of your posts which gave an example of how to structure the rest of the code correctly...
http://synopse.info/forum/viewtopic.php?pid=9397#p9397

Many thanks!

#6 Re: mORMot 1 » Encrypt database » 2014-11-13 20:13:23

w5m

Thanks for the quick response Arnaud.

What I'm trying to do is protect a dataset such that it cannot be browsed. I thought I had to unencrypt the database in order to query it, then re-encrypt it so that it remains unbrowsable by others.

Are you saying that I can throw away my calls to ChangeSQLEncryptTablePassword and instead pass the password as the 4th parameter of TSQLDBSQLite3ConnectionProperties.Create()? If so, does that mean the database needs to have been previously encrypted? If so, how?

You mentioned I'm not using the correct approach by using a TSQLDBSQLite3ConnectionProperties instance locally and the Execute method. What is the correct approach?

Apologies if I'm asking basic questions, but I'm very new to both SQLite and your units.

Any advice would be much appreciated.

#7 Re: mORMot 1 » Encrypt database » 2014-11-13 14:55:18

w5m

I'm using the static SQLite3 wrapper (i.e. away from mORMot) and just wanted to check I'm using encryption correctly in the following code snippet... 

procedure TForm1.connectButtonClick(Sender: TObject);
var
  conn: TSQLDBSQLite3ConnectionProperties;
  dbFilename: String;
  rows: Variant;
begin
  dbFilename := 'C:\Temp\bds';

  ChangeSQLEncryptTablePassword(dbFilename, '123', '');  // unencrypt tables

  conn := TSQLDBSQLite3ConnectionProperties.Create(dbFilename, '', '', '');
  try
    with conn.Execute('SELECT * FROM TableName', [], @rows) do
    begin
      while Step do
        ShowMessage(rows.Id + ': ' + rows.Description);
    end;
  finally
    FreeAndNil(conn);
  end;
 
  ChangeSQLEncryptTablePassword(dbFilename, '', '123');  // re-encrypt tables
end;

...or is there a better way?

By the way, it seems that CreateSQLEncryptTable isn't available as a public method in SynSQLite3Static.pas.

Any advice would be much appreciated.

Pages: 1

Board footer

Powered by FluxBB