Missing 'Admin' when reopening a SQLite3 native mormot atabase

h.hasenack · 2013-03-22 13:51:14

Using the latest compiled SQLITE3 dll's I experience some trouble wehen reopening an existing database.
When de database is created, I can logon.
When the database is reopened (after closing/starting the server ) Then, the Admin record appers to be missing (the LogonName=? /Admin query returns no results.

A workaround is to call TSQLAuthGroup.Initializetable.

Code looks about like this:

procedure TLCSWorkbaseRestServer.FixMissingLogin;
VAR SQLUser:TSQLAuthUser;
begin
  SQLUser := TSQLAuthUser.CreateAndFillPrepare(self,'LogonName=?',['Admin']);
  try
  if not SQLUser.FillOne then
    TSQLAuthGroup.InitializeTable(self,'');
  finally
    SQLUser.Free;
  end;
end;

....

constructor TLCSWorkbaseRestServer.Create(const aAlias:UTF8String);
VAR lConnectionString:string;
    lSQLModel:TSQLModel;
    lServerName,Prot,NativeDbAlias:string;
begin
  if trim(aAlias)='' then
    raise ELCSServer.CreateFmt('%s.Create: an Alias must be proivided',[aAlias]);
  lConnectionString:=ExpandSNGMacros(AliasManager.ConnectionString[aAlias]);
  SPlitString(lConnectionString,':',Prot,NativeDBAlias);
  FPersistentConnectionProperties := LCSRestServer.CreateConnectionPropertiesForConnectionString(lConnectionString);
  if FPersistentConnectionProperties=nil then
    raise ELCSServer.CreateFmt('%s.Create: Unable to create connection properties for alias [%s] : [%s]',[ClassName,aAlias,lConnectionString]);
  FVolatileConnectionProperties := TSQLDBSQLite3ConnectionProperties.Create(cfnSQLLite3RAMFile,'','','');
  if FVolatileConnectionProperties=nil then
    raise ELCSServer.CreateFmt('%s.Create: Unable to create in-memory connection properties',[ClassName]);

  lServerName:=cDefaultLCSServerName;
  // local machine can override default server name
  lServerName:=Registry.LocalMachine.OpenAppRootKey.OpenValue(crvServerName).ReadString(lServerName);
  // current user machine can override default server name
  lServerName:=Registry.CurrentUser.OpenAppRootKey.OpenValue(crvServerName).ReadString(lServerName);
  FRootURI:=Format('%s/%s',[lServerName,Trim(aAlias)]);
  lSQLModel:=TLCSWorkbaseModel.Create(RootURI);
  lSQLModel.Owner:=self;
  RegisterModel(lSQLModel);

  if AnsiSameText(prot,cSQLiteConnectionProtocol) then
    inherited Create(lSQLModel, NativeDbAlias,True)  //*********************** Problem obly happens for this call
  else inherited Create(lSQLModel, cfnSQLLite3RAMFile,True); //******************** virtual tables appear to be not a problem,

  CreateMissingTables;
  FixMissingLogin; // *************************************Workaround call
  ServiceRegister(TLCSWorkbaseImpl,[TypeInfo(ILCSWorkbase)],sicClientDriven);
  ServiceRegister(TLCSSimulationImpl,[TypeInfo(ILCSSimulation)],sicClientDriven);
  LCSRestServer.RegisterDatabase(aAlias,self);

  ExportServerNamedPipe(RootURI);
  ExportServerMessage(RootURI);
  LCSHttpServer.AddServer(self);
end; {- TLCSWorkbaseRestServer.Create }

I suspect a bug, or maybe I just didn't get it right myself.

*UPDATE*

- It appears as if nothiong is stored at all, almost like an open transaction that is not comitted or something like that.
- Problem happens with static & dynamic32 and dynamic64 SQLite libs
- Setting breakpoints at procedure TSQLDataBase.TransactionBegin, TSQLDataBase.Rollback and TSQLDataBase.Commit reveals that in case of the SQLite3 native database, the transaction s started twice, before it is committed twice. That appears a bit odd.

Hans

Last edited by h.hasenack (2013-03-22 14:32:49)

ab · 2013-03-22 15:17:01

Do the AuthGroup and AuthUser tables be available in the DB?

Are the TSQLAuthGroup and TSQLAuthUser records declared within the model as "external"?

You are using a FVolatileConnectionProperties conenction - perhaps it is created in it, so there is no data.

h.hasenack · 2013-03-27 12:37:01

Hi Arnaud

Well, the problem was that I had to omit the VirtualTableExternalRegisterAll when using the native SQLite3 engine. As we only use external databases before, it was unconditionally called.
Now that I omitted it when using sqliote itself to store data, the problem is gone and all data is stored and retrieved as expected

The mormot and sqlite engines appear to work very nicely in both 64bit and 32bit modes.

Regards - Hans

ab · 2013-03-27 14:10:21

Good news.

Yes, even 64 bit mode sounds just fine, from my own testing.

mORMot Open Source

#1 2013-03-22 13:51:14

Missing 'Admin' when reopening a SQLite3 native mormot atabase

#2 2013-03-22 15:17:01

Re: Missing 'Admin' when reopening a SQLite3 native mormot atabase

#3 2013-03-27 12:37:01

Re: Missing 'Admin' when reopening a SQLite3 native mormot atabase

#4 2013-03-27 14:10:21

Re: Missing 'Admin' when reopening a SQLite3 native mormot atabase

Board footer