In a C# application, using nHibernate & fluent nHibernate, we're looking to capture certain INSERT, UPDATE, DELETE queries performed on a Sqlite database in order to replicate them. We'd like to replicate only certain queries that affect certain entity types.
After several searches and tests, we haven't found a way to do this.
We have developed a solution using an Interceptor as well as Listeners, but neither solution seems to work.
An Interceptor can be used to retrieve the generated SQL, but we lack context for its application.
Listeners (IPostInsertEventListener, IPostUpdateEventListener, IPostDeleteEventListener) have many more parameters, but we can't find a way to retrieve the raw SQL query.
Using Interceptor by overriding OnPrepareStatement(SqlString sql) is the simplest method, but it lacks the context of the entity on which the operation has been performed.
public override SqlString OnPrepareStatement(SqlString sql)
{
// Only raw SQL
Console.WriteLine(sql);
return base.OnPrepareStatement(sql);
}
And by using Listeners, we get a lot more information, we can understand the context and the entity that is being modified, but we can't find the SQL generated.
public class QueryListener(Configuration cfg) : IPreUpdateEventListener, IPostUpdateEventListener, IPreInsertEventListener, IPostInsertEventListener
{
public void Register()
{
cfg.EventListeners.PreUpdateEventListeners = [.. cfg.EventListeners.PreUpdateEventListeners, this];
cfg.EventListeners.PostUpdateEventListeners = [.. cfg.EventListeners.PostUpdateEventListeners, this];
cfg.EventListeners.PreInsertEventListeners = [.. cfg.EventListeners.PreInsertEventListeners, this];
cfg.EventListeners.PostInsertEventListeners = [.. cfg.EventListeners.PostInsertEventListeners, this];
}
public void OnPostUpdate(PostUpdateEvent @event)
{
// Capture SQL here ?
}
public bool OnPreUpdate(PreUpdateEvent @event)
{
// Capture SQL here ?
return false;
}
public bool OnPreInsert(PreInsertEvent @event)
{
// Capture SQL here ?
return false;
}
public void OnPostInsert(PostInsertEvent @event)
{
// Capture SQL here ?
}
#region Async methods
public Task OnPostUpdateAsync(PostUpdateEvent @event, CancellationToken cancellationToken)
{
OnPostUpdate(@event);
return Task.CompletedTask;
}
public Task<bool> OnPreUpdateAsync(PreUpdateEvent @event, CancellationToken cancellationToken)
{
return Task.FromResult(OnPreUpdate(@event));
}
public Task<bool> OnPreInsertAsync(PreInsertEvent @event, CancellationToken cancellationToken)
{
return Task.FromResult(OnPreInsert(@event));
}
public Task OnPostInsertAsync(PostInsertEvent @event, CancellationToken cancellationToken)
{
OnPostInsert(@event);
return Task.CompletedTask;
}
#endregion
}
Ideally, we'd like to reproduce the delta mechanism proposed by SyncFramework ().
Thanks in advance for your help!
In a C# application, using nHibernate & fluent nHibernate, we're looking to capture certain INSERT, UPDATE, DELETE queries performed on a Sqlite database in order to replicate them. We'd like to replicate only certain queries that affect certain entity types.
After several searches and tests, we haven't found a way to do this.
We have developed a solution using an Interceptor as well as Listeners, but neither solution seems to work.
An Interceptor can be used to retrieve the generated SQL, but we lack context for its application.
Listeners (IPostInsertEventListener, IPostUpdateEventListener, IPostDeleteEventListener) have many more parameters, but we can't find a way to retrieve the raw SQL query.
Using Interceptor by overriding OnPrepareStatement(SqlString sql) is the simplest method, but it lacks the context of the entity on which the operation has been performed.
public override SqlString OnPrepareStatement(SqlString sql)
{
// Only raw SQL
Console.WriteLine(sql);
return base.OnPrepareStatement(sql);
}
And by using Listeners, we get a lot more information, we can understand the context and the entity that is being modified, but we can't find the SQL generated.
public class QueryListener(Configuration cfg) : IPreUpdateEventListener, IPostUpdateEventListener, IPreInsertEventListener, IPostInsertEventListener
{
public void Register()
{
cfg.EventListeners.PreUpdateEventListeners = [.. cfg.EventListeners.PreUpdateEventListeners, this];
cfg.EventListeners.PostUpdateEventListeners = [.. cfg.EventListeners.PostUpdateEventListeners, this];
cfg.EventListeners.PreInsertEventListeners = [.. cfg.EventListeners.PreInsertEventListeners, this];
cfg.EventListeners.PostInsertEventListeners = [.. cfg.EventListeners.PostInsertEventListeners, this];
}
public void OnPostUpdate(PostUpdateEvent @event)
{
// Capture SQL here ?
}
public bool OnPreUpdate(PreUpdateEvent @event)
{
// Capture SQL here ?
return false;
}
public bool OnPreInsert(PreInsertEvent @event)
{
// Capture SQL here ?
return false;
}
public void OnPostInsert(PostInsertEvent @event)
{
// Capture SQL here ?
}
#region Async methods
public Task OnPostUpdateAsync(PostUpdateEvent @event, CancellationToken cancellationToken)
{
OnPostUpdate(@event);
return Task.CompletedTask;
}
public Task<bool> OnPreUpdateAsync(PreUpdateEvent @event, CancellationToken cancellationToken)
{
return Task.FromResult(OnPreUpdate(@event));
}
public Task<bool> OnPreInsertAsync(PreInsertEvent @event, CancellationToken cancellationToken)
{
return Task.FromResult(OnPreInsert(@event));
}
public Task OnPostInsertAsync(PostInsertEvent @event, CancellationToken cancellationToken)
{
OnPostInsert(@event);
return Task.CompletedTask;
}
#endregion
}
Ideally, we'd like to reproduce the delta mechanism proposed by SyncFramework (https://github/egarim/SyncFramework/tree/main).
Thanks in advance for your help!
Share Improve this question edited Jan 30 at 13:48 Panagiotis Kanavos 132k16 gold badges203 silver badges265 bronze badges asked Jan 30 at 13:39 pp-mluthipp-mluthi 115 bronze badges1 Answer
Reset to default 0keeping some form of context within your interceptor? If the order of the method calls is compatible and you set the context, it will be available when the OnPrepareStatement method is called.
Something like:
public class SqlLoggingInterceptor : EmptyInterceptor {
private object _currentEntity;
public override bool OnSave(object entity,
object id,
object[] state,
string[] propertyNames,
IType[] types)
{
_currentEntity = entity;
return true;
}
public override SqlString OnPrepareStatement(SqlString sql)
{
// Use _currentEntity here
return base.OnPrepareStatement(sql);
}
}
If I've read the documentation correctly, interceptors are scoped per-session, so there's some potential issues with keeping track of the correct context, but it could be worth exploring.
The stumbling block may be that the OnPrepareStatement method is called before you can set the context.