4 ms·

> We are now using SQL (SQLite in-memory) for virtually 100% of our business logic execution Any docs, open source etc?

bob1029 5y ago
I don't have any source I am able to share at this time, but I can share the core idea with a simple example. Assume you have a business app where you need to conditionally do a thing. The predicates (conditions) for this thing can be expressed in terms of constraints in a SQL query, the result of which is a scalar value representing true or false. All complex decision trees can ultimately be turned into combinations of these things. An example of this could be a decision point in a business app where you need to determine if the user should be allowed to proceed into a certain feature. Perhaps you call this knowledge IsUserAllowedIntoFeatureA. What we would do is expose a configurable SQL rule which allows for a business analyst to customize the behavior of this fact per-customer: SELECT 1 --1 == true, 0/null/empty == false FROM --My Domain WHERE --My Conditions The above SQL query would be run against a SQLite database that is mapped out of the current instance of that user's state. Once we determine the outcome, we throw away the database. This idea can also be extended to strings, arrays, etc.
- chrisjc 5y ago
  I'd love to understand more. Is there anything like this that already exists that perhaps you modeled this approach from? I'm having trouble envisioning the complete solution, but it really sounds intriguing and you've caught my attention.
- deleted 5y ago
  [deleted]
- rlguarino 5y ago
  I also am very curious to know more about the complete solution. It sounds like you've delegated if statements to configurable SQL queries, does that mean you need to take the context/variables/code and put it into a temporary SQL db whenever you evaluate them. Is the overhead of moving state into and out of SQLite a lot? It reminds me of SQL stream processing in Fluentbit https://docs.fluentbit.io/manual/stream-processing/getting-started/fluent-bit-sql https://docs.fluentbit.io/manual/stream-processing/getting-s...
  - bob1029 5y ago
    > It sounds like you've delegated if statements to configurable SQL queries, does that mean you need to take the context/variables/code and put it into a temporary SQL db whenever you evaluate them. This is precisely what we do. The schema of the target database is known at startup time, so we can build a template copy of the database in memory and use this as a starting point to fill in current copies of domain state. Performance is very good for our requirements. We don't need indexes because the actual amount of data in each database is incredibly small. I have benchmarked 30k SELECT statements per second against this approach, which should be more than enough if you consider that all of this is on a per-user-session basis. We could have hundreds of these in-flight simultaneously without any contention.

I don't have any source I am able to share at this time, but I can share the core idea with a simple example. Assume you have a business app where you need to conditionally do a thing. The predicates (conditions) for this thing can be expressed in terms of constraints in a SQL query, the result of which is a scalar value representing true or false. All complex decision trees can ultimately be turned into combinations of these things. An example of this could be a decision point in a business app where you need to determine if the user should be allowed to proceed into a certain feature. Perhaps you call this knowledge IsUserAllowedIntoFeatureA. What we would do is expose a configurable SQL rule which allows for a business analyst to customize the behavior of this fact per-customer: SELECT 1 --1 == true, 0/null/empty == false FROM --My Domain WHERE --My Conditions The above SQL query would be run against a SQLite database that is mapped out of the current instance of that user's state. Once we determine the outcome, we throw away the database. This idea can also be extended to strings, arrays, etc.

chrisjc 5y ago
I'd love to understand more. Is there anything like this that already exists that perhaps you modeled this approach from? I'm having trouble envisioning the complete solution, but it really sounds intriguing and you've caught my attention.
deleted 5y ago
[deleted]
rlguarino 5y ago
I also am very curious to know more about the complete solution. It sounds like you've delegated if statements to configurable SQL queries, does that mean you need to take the context/variables/code and put it into a temporary SQL db whenever you evaluate them. Is the overhead of moving state into and out of SQLite a lot? It reminds me of SQL stream processing in Fluentbit https://docs.fluentbit.io/manual/stream-processing/getting-started/fluent-bit-sql https://docs.fluentbit.io/manual/stream-processing/getting-s...
- bob1029 5y ago
  > It sounds like you've delegated if statements to configurable SQL queries, does that mean you need to take the context/variables/code and put it into a temporary SQL db whenever you evaluate them. This is precisely what we do. The schema of the target database is known at startup time, so we can build a template copy of the database in memory and use this as a starting point to fill in current copies of domain state. Performance is very good for our requirements. We don't need indexes because the actual amount of data in each database is incredibly small. I have benchmarked 30k SELECT statements per second against this approach, which should be more than enough if you consider that all of this is on a per-user-session basis. We could have hundreds of these in-flight simultaneously without any contention.