DEV Community: Roddy

Easy Spring-Boot with Drools

Roddy — Fri, 08 Oct 2021 14:55:04 +0000

There's a million of these articles and they're all overly complex. You can expose your Drools KieContainer as a bean with 2 lines if you let Drools manage the rules on your classpath:

@Bean
public KieContainer kieContainer() {
    KieServices kieServices = KieServices.Factory.get();
    return kieServices.getKieClasspathContainer();
}

Boom. Done. No need to futz with file I/O, the KieFileSystem, or any of that other nonsense. Drools will do it all for you if you let it -- why make your life more complicated?

For completeness, you'd use it in your components/services like this:

@Service
public class FooService {
    // Inject the bean using constructor injection
    private final KieContainer kieContainer;

    public FooService(KieContainer kieContainer) {
        this.kieContainer = kieContainer;
    }

    public void fireRules() {
        KieBase rules = this.kieContainer.getKieBase("validaton");
        KieSession session = rules.newKieSession();
        try {
            session.insert( ... ); // add facts into working memory
            session.fireAllRules();
        } finally {
            session.dispose();
        }
    }
}

This all works if you have your rules on your classpath, with a kmodule.xml describing your kieBases in META-INF.

If you want to see a working example, my library demo application is a Spring-Boot app I developed for my series of articles about how not to interact with databases.

Cover image attribution: coyot @ PixaBay

Behavior Driven Testing and Drools

Roddy — Thu, 07 Oct 2021 05:52:51 +0000

Behavior Driven Design (BDD) and Drools go together like chocolate and cayenne pepper. Your first reaction may be "blech!" but then you actually try it and it kind of grows on you like a fungus. It may not be your cup of cocoa, but it works and when you have a complex problem space, it may actually be an improvement in terms of programmer efficiency.

Behavior Driven Design is one of those hand-wavy terms that is hard to explain in depth. Basically it's a form of software development where your requirements are described by behavior -- the behavior of the user, the system, the widget, whatever. Here's an example:

Given the user is on the login screen
When the user enters an incorrect password
Then the system should send an email

This requirement (called a "spec" or "specification") describes a behavior (actor fails to login), the expected result (email is sent), and some preconditions (location is the login screen.) Specs are written in plain English (or other spoken language) rather than any sort of programming language or psuedo-code.

BDD is closely related to Test Driven Design (TDD) in that the behavior spec that describes the requirements also describes the test. Before any coding can begin, the specs are prepared describing the behavioral expectations for the system. Then code is written to implement this, and finally we use the same specs to verify the expected behavior. It's rather straight-forward, yeah? It's just basically TDD except that instead of the unit test being the first thing that gets written, it's the human-readable description of the behavioral expectation of the system.

The behavior spec defines both our test case and our requirements. What if we could just run the spec like a unit test?

Enter cucumber. (The technology, not the vegetable.)

Cucumbers, gherkins, and other pickles

Cucumber is a BDD testing framework, based around the idea that your spec can both define the requirements and be the test to verify them. It does this by leveraging a language called gherkin, which really is just a list of restricted keywords ("given", "when", "then", etc.) That example spec I showed previously for a failed login behavior? That's gherkin.

It looks suspiciously like English, and that's the point. You don't need to know anything about code or programming to write a spec. (Gherkin can be internationalized as well. I've written behavioral specifications in Klingon just to prove it was possible. My manager laughed and made me rewrite it, of course.)

I'm not going to go too much in depth into Cucumber and how it works. All you really need to know is that your spec is written in something a regular non-programmer person can read, and it describes some behavioral requirement for your application. Cucumber translates that "plain English" into actions that integrate into the testing framework of your choice, thus allowing you to use your requirements as the inputs for your unit or integration test.

Drooling over cucumbers

Hopefully you already know that Drools is a business rules management system. You write rules in either "drl" syntax, in spreadsheets, or in glorified flowcharts, and then let your application throw data at it.

I'll be the first to jump on the "I love Drools!" bandwagon, but even I will admit that it is quite complicated. It's not something that you can drop entry level programmers into and expect them to just get; the framework is extremely powerful, but its abstractions are not always self-evident. If you print the documentation for the latest version of Drools (7.59 at time of writing), you'll get 616 pages about the framework.

This leaves us in a bit of a bind. Drools is supposed to model business logic in the form of rules, but to properly implement them you really need a programmer with a solid understanding of the framework. Your business analyst who understands the business may be able to write some simple or trivial rules, but they're also very likely to do it inefficiently. And for more complex rules, forget it. Your engineer, on the other hand, could write the rule -- but they don't have the necessary business knowledge to do so correctly.

This is where Behavior Driven Design shows up to save the day. Your business analyst writes out the expected behavior in plain English (or another common spoken language.) Your engineer then implements that plain English requirement in Drools. And then, finally, we drop that plain English behavioral specification into Cucumber, and verify that the system does what the business analyst asked it to do.

An anecdote

Yes, I too was skeptical when this was first proposed to me. I was working in healthcare IT for a US-based startup. My application would calculate how much a patient should be charged for whatever-it-is that they were in the hospital for. This was an extremely complex calculation, which involved things like health insurance policies (sometimes multiple ones with varying coverage rules), the patient's recent medical history (especially how many visits/how much they'd paid so far against the plan), the contracted cost of the procedure, physician fees for specialists, various discounts, offsets from charitable donations based on credit score, and so on. Honestly, I understood very little of the business; I've only been to the hospital a handful of times, mostly as a visitor. But I did have a robust set of Drools rules that would magically spit out a reasonable-looking number after I tossed the patient's life history, current horoscope, and yesterday's winning lottery picks into working memory.

And then the Affordable Care Act was passed (commonly called "Obamacare") and all of a sudden we had to update my rules. Now we had a problem. I had some very smart business analysts who understood exactly how we needed to calculate now. I had what was effectively a black box of rules that I could modify because I understood the Drools framework. And then we had a gaping chasm between us which basically represents the fact that we didn't really speak a common language. My analysts could talk about healthcare 'til the cows came home, but to me it sounded like the wah wah wah from the Peanuts cartoons.

So the analysts and I sat down and came up with a plan of action. Our first problem, we realized, is that while we had rules, they were effectively a black box. We had no way of knowing whether the numbers they were producing were accurate. Before we could determine how the rules had to change, we first had to understand how the rules worked today.

Fair enough. The solution here is clearly to write tests to verify behavior based on known inputs.

And this is where the eureka moment occurred. I, clearly, did not have the necessary business knowledge to prepare these test cases. The business analysts, on the other hand did -- but did not have the technical knowledge to implement the tests. So why not have them write gherkin test cases to validate the behavior of the system.

It was genius. After all, if a test failed in the future due to some changes, I could just take that plain English representation of a requirement back to the business analysts and say, "Hey, I implemented the XYZ use case for the ticket, but this other use case failed. Can you review?" And they could review it, because the other use case was written in English and not some programmer mumbo jumbo. Huzzah!

Cucumber and you

Healthcare is hard, and I don't work in that field anymore, so I can't exactly show you the sorts of rules I was dealing with there. Luckily coming up with toy examples for Drools questions is a particular hobby of mine, so we can still muddle through with an example.

So let's say we're writing a game about space piracy. We've got space pirates, regular civilians, and the military all zooming around in their spaceships, hauling cargo of various kinds (some of which is illegal.) Oh and there's also aliens.

Anyway, so we've got this game, and we've decided to implement our business logic in Drools. In this case our business logic will be stuff like validation (eg. is this procedurally-generated ship valid?) and also for calculations (eg. my ship is of type X, I'm at coordinates Y, and I want to go to coordinates Z ... can I?) For the sake of this question, we're going to consider the validation rules -- but if you're curious about the rest of it, I may have gotten a wee bit carried away implementing the example application available on GitHub.

Right. So, validation. Today we get a new requirement: we need to restrict how our ships are named. Luckily our Business Analyst or Product Owner has jumped aboard the BDD bandwagon and gives us our requirements in gherkin:

Scenario: An Alien spaceship cannot have a name starting with U.S.S
  When I have an Alien spaceship named "U.S.S. Enterprise"
  Then my spaceship is not valid
  And the error message should contain: "only human ships may use prefix U.S.S."

Cool biz. It's a pretty simple requirement, so you can probably already imagine what the rule is going to look like, so we'll skip past that temporarily and instead talk about ... well how do I run this as a unit test?

The answer: Cucumber. I think I mentioned that. Specifically, two cucumber libraries: cucumber-java and cucumber-junit (because I am firmly in the JUnit camp, but for reference I have used TestNG for Cucumber and Drools as well and the principles are the same.)

For this example, we'll use the latest version of these two libraries: 6.10.4 at time of writing.

<dependency>
  <groupId>io.cucumber</groupId>
  <artifactId>cucumber-java</artifactId>
  <version>6.10.4</version>
  <scope>test</scope>
</dependency>
<dependency>
  <groupId>io.cucumber</groupId>
  <artifactId>cucumber-junit</artifactId>
  <version>6.10.4</version>
  <scope>test</scope>
</dependency>

It's important to note that Cucumber hasn't caught up with the modern age yet and is only compatible with JUnit 4, so if you're using JUnit 5 you'll need to include a dependency on junit-vintage-engine and remember not to use any JUnit 5 features at all in your tests. (Even your assertions need to be the ones in the old junit.* package.)

The glue

The next thing we do is write what is called the "glue". This is a class (or classes) which "glue" the gherkin to the application, hence the name. It basically provides a translation layer between the gherkin and actual functionality. In our case, for each of the "steps" (the lines prefixed "When", "Then", "Given", "And"), we need to provide a bit of parsing logic to translate that into something that feeds data into the application, does an action, or asserts something.

Cucumber makes some useful annotations available, so the basic outline of our methods will look something like this:


public class SpaceshipValidationGlue {
    @When("^I have an Alien spaceship named \"(.*)\"$")
    public void setSpaceshipSpeciesAndName(String name) {
        // set the name of the spaceshp we're passing into the rules
    }

    @Then("^my spaceship is not valid$")
    public void assertInvalid() throws Exception {
        // fire the rules (if needed) and assert the validity of the result
    }

    @Then("^the error message should contain: \"(.*)\"$")
    public void assertErrorMessageContains(String messages) throws Exception {
        // fire the rules (if needed) and verify the error message
    }
}

Basically, you have a method, annotated with one of the Cucumber @When, @Then, @Given, etc. annotations. These annotations contain a regular expression, which you can use to extract variables. Then in the method you can do whatever-it-is that is being described by the English representation. Note that while Cucumber provides all of the reserved keywords as annotations -- both in English and a variety of other languages -- it doesn't really matter which one you use. There's no difference in using @When versus @Then. However I like to use @When for all of the steps which do some sort of initialization, and @Then for any which do assertions; that way I can just look at the annotation and known generally what that broad category that method belongs to. But you can do as you like.

Now if you're trying to follow along in my GitHub repository, you'll notice that the step definitions in there are a lot more complex -- because I have a lot more rules and a lot more features. It was a surprisingly fun toy example to put together, and I got totally carried away. I make no apologies, but it does mean that at least for this specific step definition you'll need to follow along here instead.

Right. Back to our spaceship name validation. Let's go about implementing the step definitions for this use case.

Usually I like to start from the top, but in this case we need to start from the bottom -- namely, what are we passing into the rules? In this case, we're passing in a Spaceship object, so we'll need to add that to our class definition. Here's where we run into our first gotcha.

For each test suite (not individual test) the cucumber framework will new up an instance of this class. So since we can't pass our spaceship instance between individual methods, we'll need to save it as a class-level variable, and remember to "clear" it between individual Scenarios. (Gherkin Scenarios are analogous to test cases.) To do this, Cucumber gives us a @Before annotation, called a "hook", so we can use that annotation to decorate a "setup" method.

public class SpaceshipValidationGlue {

    private Spaceship spaceshipUnderTest;

    @Before
    public void initialize() {
        this.spaceshipUnderTest = new Spaceship();
    }

    // ...
}

Be careful with your imports! @Before should be imported from io.cucumber.java.Before -- there's an identically named JUnit 4 class which you must not use.

Anyway, now that we've got our spaceship defined, we can implement the @When step definition:

@When("^I have an Alien spaceship named \"(.*)\"$")
public void setSpaceshipSpeciesAndName(String name) {
    this.spaceshipUnderTest.setName(name);
}

That takes care of our test setup. We've got two "Then" steps, which are the methods which validate the consequences of our action (our action being "we fired the validation rules.") So the first thing that has to happen is, clearly, that we need to fire the rules.

@Then("^my spaceship is not valid$")
public void assertInvalid() throws Exception {
    fireRules();

    // TODO validate the results
}


@Then("^the error message should contain: \"(.*)\"$")
public void assertErrorMessageContains(String messages) throws Exception {
    fireRules();

    // TODO verify the error message
}

private void fireRules() {
    KieServices kieServices = KieServices.Factory.get();
    KieContainer kContainer = kieServices.getKieClasspathContainer();
    KieBase kBase = kContainer.getKieBase("validation");
    KieSession session = kBase.newKieSession();
    try {
        session.insert(spaceshipUnderTest);
        session.fireAllRules();
    } finally {
        session.dispose();
    }
}

But wait! Why are we firing the rules twice?

Good question. We shouldn't be. We should instead track the fact that we fired the rules already, and not fire them again and just reuse the same results for subsequent assertions. I like to use this with an AtomicBoolean tracking variable that gets reset in the @Before.

public class SpaceshipValidationGlue {
    private final AtomicBoolean isRulesFired = new AtomicBoolean(false);

    @Before
    public void initialize() {
        this.isRulesFired.set(false);
        // ...
    }

    // ...

    private void fireRules() {
        if(isRulesFired.compareAndSet(false, true)) {
            KieServices kieServices = KieServices.Factory.get();
            KieContainer kContainer = kieServices.getKieClasspathContainer();
            KieBase kBase = kContainer.getKieBase("validation");
            KieSession session = kBase.newKieSession();
            try {
                session.insert(spaceshipUnderTest);
                session.fireAllRules();
            } finally {
                session.dispose();
            }
        }
    }
}

We're getting close. Isn't this exciting?

Next thing we need to do is get the results out of the rules, so we can assert against something in our steps. I'm going to take the easy way out and use a global object called ValidationResult, but there's a number of ways we could do this instead. (Eg. we could pass the result object into working memory, we could have a "valid" flag on the spaceship itself, etc.)

Since we're going to have to share the results across potentially multiple methods doing assertions, we'll need to store the results somewhere that's accessible to all methods in this class -- namely as a class variable. So we'll just drop it alongside the Spaceship variable, and also update the @Before to reset the value between scenarios.

Thus, our completed step definitions look like this:

public class SpaceshipValidationGlue {

    private final AtomicBoolean isRulesFired = new AtomicBoolean(false);

    private Spaceship spaceshipUnderTest;
    private ValidationResult result;

    @Before
    public void initialize() {
        this.isRulesFired.set(false);
        this.spaceshipUnderTest = new Spaceship();
        this.result = null;
    }


    @When("^I have an Alien spaceship named \"(.*)\"$")
    public void setSpaceshipSpeciesAndName(String name) {
        this.spaceshipUnderTest.setName(name);
    }

    @Then("^my spaceship is not valid$")
    public void assertInvalid() throws Exception {
        fireRules();

        assertFalse(result.isValid());
    }

    @Then("^the error message should contain: \"(.*)\"$")
    public void assertErrorMessageContains(String messages) throws Exception {
        fireRules();

        String errorMessage = result.getError();
        assertNotNull(errorMessage);
        assertTrue(errorMessage.contains(messages));
    }

    private void fireRules() {
        if(isRulesFired.compareAndSet(false, true)) {
            result = new ValidationResult();

            KieServices kieServices = KieServices.Factory.get();
            KieContainer kContainer = kieServices.getKieClasspathContainer();
            KieBase kBase = kContainer.getKieBase("validation");
            KieSession session = kBase.newKieSession();
            try {
                session.insert(spaceshipUnderTest);
                session.setGlobal("validation", result);
                session.fireAllRules();
            } finally {
                session.dispose();
            }
        }
        assertNotNull(result);
    }
}

As an aside, one of the cool things about these step definitions is that they're reusable. I can now include I have a spaceship named "foo" in any of my scenarios, and this step definition will provide the implementation for it. IDEs like IntelliJ's IDEA provide built-in support for Gherkin and will automatically map from the gherkin feature to the defined step so you can navigate between them. (Note that this might be a paid feature in IntelliJ; I'm unfamiliar with other IDEs that may or may not support this functionality as well.)

The test runner

Step definitions by themselves aren't tests though. They're just the translation layer, the glue. We still need the actual test runner.

I usually go through these examples stepwise, but for the runner it's easier if I just show you the whole thing. This is the test runner for this example feature:

import io.cucumber.junit.Cucumber;
import io.cucumber.junit.CucumberOptions;
import org.junit.runner.RunWith;

@RunWith(Cucumber.class)
@CucumberOptions(plugin = { "html:target/cucumber_html/spaceshipValidation/",
                            "json:target/json-spaceshipValidation.json",
                            "junit:target/junit-spaceshipValidation.xml"},
                 glue = {"app.roddy.space.validation.spaceship"},
                 features = "src/test/resources/features/validation/spaceshipValidation.feature",
                 tags = "@validation")
public class SpaceshipValidationRulesTest {

}

When you're writing Cucumber tests in Java, you'll end up with a lot of these test runners, and they'll end up being primarily copy-paste jobs. There's a lot of boilerplate, but not a good way of extracing the commonalities into something less verbose.

To start with, the @RunWith annotation is the JUnit4 extension which identifies Cucumber as the test runner. Then @CucumberOptions configures this particular test suite as follows:

Parameter	Description
plugin	These plugins configure how the test outputs its results. HTML generates a webpage with test results. JSON spits out json. JUnit is JUnit xml, commonly used as an input for external reporters (eg Jenkins plugins)
glue	An array of packages where our glue file(s) live. This is the package for the step definition file we just wrote for our example feature.
features	The full path to the feature file. This isn't relative to the class path (src/test/resources), but from the project root.
tags	Not really discussed here, but you can put annotations on your scenarios to categorize them so that you can target them specifically. A common workflow is to tag stuff `@wip` or `@skip` and exclude them from this parameter (eg. `~@wip`)

You'll notice that the file itself is empty. If you have some sort of common setup/tear down steps, this is where you'd put that logic using the JUnit @Before and @BeforeAll annotations (or "after" equivalents). An important thing to remember is that JUnit does properly identify each individual Scenario as a separate JUnit test; IDEs like IntelliJ IDEA are smart enough to understand this, but the JUnit framework itself just sees it as a single black-box thing it's running. So anything that you put in this file will run before all of the scenarios or after all of the scenarios.

At my previous company, when I wrote Drools for a healthcare application, I developed a code-coverage reporting framework for Drools. It required attaching some listeners to the Drools session and collocating the metrics for the suite, and this is where I implemented that sort of logic. Otherwise you could put some common setup/teardown in here as well. You might've noticed that we load the rules into memory every time we call fireRules() in the step definitions; we could improve performance by moving that "load" logic here so the rules are only loaded once.

At this point, we're actually done with our unit tests. You can run SpaceshipValidationRulesTest as a JUnit test, and it will actually track down that gherkin Scenario and run it using your glue. The test will fail, of course, since we've not written the rule yet -- but hey that's test driven development for you. Huzzah!

The rule

The rule is pretty anti-climactic at this point. I intentionally chose a very simple rule with a very simple spec as an example, and as a result it's actually not very interesting in terms of implementation.

global ValidationResult validation;

rule "Only human spaceships can use the prefix U.S.S."
when
  Spaceship( name != null,
             name str[startsWith] "U.S.S.",
             belongsTo != Species.HUMAN)
then
  validation.setIsValid(false);
  validation.appendMessage("only human ships may use prefix U.S.S.");
end

Just like it says on the tin. If the spaceship is not a Human ship, and the name starts with "U.S.S.", then we reject this as not being a valid spaceship.

Conclusion

And there you have it. We have a complex system, a requirement written in plain English that can be run as a test, and a rule implemented against it. Clearly most real world applications won't involve spaceship, aliens, and piracy, but hopefully you can imagine how this might work with a legitimate business use case that requires in-depth knowledge.

Healthcare, banking, accounting, HR -- there's a lot of very complicated businesses which could benefit from business rules, but where it would be a waste of time (and effort) to try and educate your programmers to be subject matter experts. Better to let your SMEs and product owners describe the expected behavior in a way they understand, and let the programmers implement the system using their own expertise, and let the Cucumber keep both sides honest (and accurate.)

It's also worth noting that Cucumber isn't just for Java, and it's not just for unit tests. At the unit test level, it makes sense to implement tests with Java because a Drools application will be written in Java, by definition. But you can also design an integration or end-to-end testing framework that runs independently, and use other languages for that. Cucumber was originally written in Ruby; the healthcare application I've mentioned a few times had its e2e tests written in Cucumber and Ruby. There's also Javascript libraries, C#, a much maligned Python implementation, and so on. I've only shown you here the tip of the proverbial iceberg, and focused on how you can validate your Drools business logic, but the reality is that this is a very powerful tool and there are many more use cases which may be of use to you.

More information

Cover image attribution: congerdesign @ pixabay.

Drools & Databases, Part 3: The Solution

Roddy — Sun, 26 Sep 2021 23:24:21 +0000

In the third and last part of this extended rant about how not to design database and rules interactions, I will describe how you should actually go about doing this.

If you're new to the party, the first two parts of this discussion can be found here:

If you don't want to spend half your day reading, I'll summarize the salient points here.

One of the most common questions on StackOverflow in the Drools tag is something along the lines of "how do I call my database from my rules?"

In part 1, I explained why you shouldn't try to interact with the database directly from your rules (DRL or otherwise.) Safely interacting with a database using a JDBC driver (or library) is incredibly involved and takes significant effort to do safely and properly, especially in a production environment. It's just about impossible to properly manage transactions, connections (with or without pooling), prepared statements, result sets, and so on within the confines of a DRL file. I'm not even sure it's possible to attempt in any sane fashion in a decision table....

In part 2, I talked about why even interacting with DAOs should be avoided. A well-design data abstraction layer would mean that we don't need to worry about things like connection pooling and transaction management, so in theory we could just interact with the DAOs and we'd be fine, right? Technically, yes, you could -- but the danger here becomes that you need to have extensive knowledge of how the Drools lifecycle works, along with how your data models are coupled (especially if you use hibernate's lazy loading.) This creates a significant issue for future maintenance, since it's not possible to test for the problems that you might introduce due to lack of knowledge in these areas -- such as data inconsistency arising from race conditions.

Now, in part 3, I finally get around to explaining what you should do instead. ("Finally!" you say.)

The cop-out

The answer you've all been waiting for is .... it depends.

No really. It depends on what your rules are actually doing and your problem space. But luckily there's a general rule of thumb which is this:

Interact with the database before you call the rules to get your data and pass it into working memory. Interact with the database after you call the rules to persist your results.

There's still problems here and I'll call them out, but we'll be wading into treacherous waters. Just put a bunch of DBAs into a room, ask them if we should be storing calculated or derived data in the database, and duck for cover.

The details

Let's start with an example, because I like talking about actual code instead of waving my hands around and talking theory.

Scenario: the library

Here's the situation we're going to be coding against. Our client is a library (with books in it; link is to Wikipedia for those unfamiliar with the concepts.) Our application tracks users who borrow and return books. A user is allowed to borrow up to 3 books at a time. Each book is allowed to be borrowed for a set amount of time (1 week, rounded to the nearest day). If the user has any books that are overdue, they are not allowed to borrow any new books until they have returned the overdue book(s).

The library has a simple database that models books and people. The person table has an id (primary key), and some information about the person borrowing the book. The book table has an id (primary key), some information about the book itself, and a borrowed_by field which is a foreign key reference to the person table's id field. So when a book is "borrowed", the borrowed_by field is set to the borrower's id. The borrowed_on field indicates when this event occurred.

Pretty simple. For the graphically oriented, here's the relationship diagram:

(Diagram made in DbDiagram, a pretty sweet and free online tool for this stuff.)

If you prefer looking at a complete, working application, the code from this post is available on GitHub here.

The application

Our application is going to be handling BookEvents. These events come from an external system (the barcode readers used by the librarians to scan books that are being borrowed or returned.) The event arrives at our application looking like this simple model:



enum BookEventType { BORROW, RETURN; }

class BookEvent {
    BookEventType eventType; // required
    Integer bookId; // required
    Integer userId; // optional
}

Notice that userId is optional. When a book is returned, we don't actually care who is returning it -- maybe it was tossed into a chute on the outside of the building after hours and gets scanned when the librarians arrive the following day. Maybe someone found the book on the bus and is returning it out of good will. It doesn't really matter, from anyone's perspective, who actually does the return. But for a 'borrow' event we do need to know it.

When we receive a RETURN BookEvent, we need to process it and update the appropriate tables as needed; this method should generally always return an "OK"/success -- situations like "this is not a library book" or "the returned book is damaged" are out of scope for this exercise.

When we receive a BORROW BookEvent, we need to return an indicator that says either:

No, the user cannot borrow this book.
Yes, the user has now successfully borrowed this book.

The "Yes"/success workflow should actually update the database and underlying system.

Calling the rules

At this point, we have enough information to start coding. First, we need to write a method that will take the received BookEvent and call the rules.



/**
 *  Process the book event and return an indication of success or failure.
 *
 * @param  event the event being processed
 * @return       true if the event is successful, false otherwise
 */
 boolean processEvent(BookEvent event) {
   // TODO
 }

Now, this is the part where many people will go to StackOverflow and start asking about how to interact with the database fromthe rules. Their first instinct is, of course, to just pass the BookEvent to the rules and let it sort itself out.

That's the wrong way to go about it, however. What we should do instead is first get the data we need out of the database, and then call the rules.

Slight detour: the data

What I like to do at this point is take a minute to think about my rules and mentally design my input objects. I want to make sure that the data I pass into my rules is sufficient, and is organized in a way that that is easily accessible. The easiest way to do this, I find, it to describe my rules in plain English, and describe the data I need to have in order for it to work.

In our current scenario we have the following rules:

If the person has 3+ books already checked out, a BORROW is not allowed
If the person has any overdue book checked out, a BORROW is not allowed
If the person is RETURNing a book, it is allowed
If the person has < 3 books checked out, and none are overdue, the BORROW is allowed

And an error case, just to make things interesting:

If the book is already checked out by this user, a BORROW is not allowed (maybe this was an accidental duplicate request)

Also an error case because of how our BookEvent is modelled:

If the BORROW request has no associated person, it is not allowed

Looking at these rules we need the following information:

The event type (BORROW or RETURN)
The id of the book being borrowed
The person, if known
- All books currently checked out by this person
- For eached checked out book, the datetime it was checked out

Now that we know the data we need, we can model the data we're going to pass into the rules:

We'll pass in the BookEventType as-is
We need the Book we're trying to check out -- id at minimum
We'll pass in a Person instance, so we can identify when they're missing from the request for the error case
We'll need to pass in a CheckedOutBook model that indicates when the book was checked out

We can model this as follows:



class CheckedOutBook {
  int id;
  OffsetDateTime checkedOutOn;
}

class Person {
  int id;
  List<CheckedOutBook> checkedOutBooks;
}

Now we're good to go. Our next step is, of course, getting our data out of the database and creating our inputs

Preparing our rule inputs

For this example, we'll assume that we've already got a proper abstraction layer set up (Spring, Hibernate, whatever.) So we'll just get the data out of the database using some offscreen data services that will conveniently return the data data we need.



Person getUserFromEvent(BookEvent event) {
  User userFromDb = dataService.getUserById( event.getUserId() );

  Person person = new Person();
  person.setId( userFromDb.getId() );

  // Convert all Book (db model) into CheckedOutBook (rules model)
  // and save to Person
  person.setCheckedOutBooks(
    userFromDb.getBooks()
              .stream()
              .map( b -> {
                  CheckedOutBook book = new CheckedOutBook();
                  book.setId(b.getId());
                  book.setCheckedOutOn(b.getBorrowedOn());
                  return book;
              }).collect(Collectors.toList());
  return person;
}

And now all that's left is to actually call the rules:



boolean processEvent(BookEvent event) {
    // First, get the necessary data from the database
    Person person = getUserFromEvent(event);

    // Call the rules
    KieServices kieServices = KieServices.Factory.get();
    KieContainer kContainer = kieServices.getKieClasspathContainer();
    KieBase kBase = kContainer.getKieBase(kbaseName);

    KieSession session = kBase.newKieSession();
    session.insert(person); 
    session.insert(event); 
    session.fireAllRules();

    // TODO -- update the database and return the result
}

But wait, what's this? We're inserting the event instance?

We need two pieces of data from the event -- the type and the id of the book being borrowed. It makes more sense to pass them into the rules as part of the event instead of as free floating items. I've never been a fan of passing just Strings or UUIDs or Integers by themselves into working memory because there's no indication of what they actually are -- they're just random pieces of data who have lost any semantics they might've once had associated with them when they had a variable name.

Notice also that we've still got a pretty big TODO left in this method -- we haven't designed ourselves a way to get the actual results out of these rules. We'll address that in the next section, when we write the rules.

The rules

OK now for the fun part.

We'll start with the easy bit, the RETURN type:



rule "Book is returned"
when
  BookEvent( type == BookEventType.RETURN )
then
  // TODO this is ok / successful
end

... Well that was boring. Now for the "error" cases:



rule "Book is already checked out by this person"
when
  BookEvent( type == BookEventType.BORROW,
             $id: bookId )
  Person( $books: checkedOutBooks )

  exists( CheckedOutBook( id == $id ) from $books )
then
  // TODO this is not ok
end

rule "Unknown person"
when
  BookEvent( type == BookEventType.BORROW )
  not( Person() )
then
  // TODO this is not ok
end

And finally, the actual meat-and-potatoes rules:



rule "Person has 3+ existing checked out books"
when
  BookEvent( type == BookEventType.BORROW )
  Person( checkedOutBooks.size >= 3 )
then
  // TODO this is not ok
end

rule "Person has an overdue book"
when
  BookEvent( type == BookEventType.BORROW )
  Person( $books: checkedOutBooks )

  $checkoutLimit: OffsetDateTime() from OffsetDateTime.now().withHour(12).withMinute(0).withSecond(0).minusDays(7)
  exists( CheckedOutBook( checkedOutOn.isBefore($checkoutLimit) ) from $books )
then
  // TODO this is not ok
end

Cool. We're almost done. Now we just need to get the results out to the caller. This is another commonly asked question topic in StackOverflow; I'll post about it some other time at length, but basically we have three options:

Create some sort of "result" object and pass it into working memory; in the 'then' clause, update this result object with the results from the rule.
Create some sort of "result" object and set it as a global variable. This is nearly the same as 1, but more "old school" and you can't key off of the state of the result object in your "when" clause (not that we need to in our current application.)
Invoke a method or other action which has a side effect that is visible to the caller.

For the sake of simplicity, we're going to go with option 2. Our results object will look like this:



public class BookEventResult {
    private boolean success = true;
    private String reason;
    // getters and setters
}

And now all we have to do is update our rules to set success to false and a reason in the right hand side of each rule. We'll also want to declare the global at the top of the DRL. Here's one rule done as an example:



global BookEventResult result;

rule "Person has an overdue book"
when
  BookEvent( type == BookEventType.BORROW )
  Person( $books: checkedOutBooks )

  $checkoutLimit: OffsetDateTime() from OffsetDateTime.now().withHour(12).withMinute(0).withSecond(0).minusDays(7)
  exists( CheckedOutBook( checkedOutOn.isBefore($checkoutLimit) ) from $books )
then
  result.setSuccess(false);
  result.setReason("User has overdue book(s)");
end

To tie it all together, we update the calling function and it's done. Huzzah!



boolean processEvent(BookEvent event) {
    // First, get the necessary data from the database
    Person person = getUserFromEvent(event);

    // Create the result object
    BookEventResult result = new BookEventResult();

    // Call the rules
    KieServices kieServices = KieServices.Factory.get();
    KieContainer kContainer = kieServices.getKieClasspathContainer();
    KieBase kBase = kContainer.getKieBase(kbaseName);

    KieSession session = kBase.newKieSession();
    session.insert(person); 
    session.insert(event); 
    session.setGlobal("result", result);
    session.fireAllRules();

    // handle the result
    if (!result.isSuccess()) {
        logger.info("Failed to checkout book. Reason = "+result.getReason());
        return false;
    }

    if (event.getType() == BookEventType.BORROW){
        borrowBook(event);
    } else {
        returnBook(event);
    }
    return true;
}

The implementation of the borrowBook and returnBook functions left as an exercise for the reader. In these methods, we should actually implement the business logic involved in borrowing or returning a book, which will involve updating the appropriate rows in the database.

Um, but wait ...

Yes, this is a toy example. Yes we've got some pretty sizable gaps in our error handling. Consider these following use cases:

What if the book that we're trying to check out is already checked out by someone else?
What if a concurrent request comes in for the same user which puts them over the checked out limit?
What if the validity of the action changes between the rules determining go/no-go and the actual invocation of the borrowBook/returnBook methods?

These are all valid questions. But here's the thing -- we can handle them. And when we handle them they will be handled in code. Not rules. Because the whole point of this exercise was to implement our validations in the rules without having to touch the database from the DRL itself.

And in that regard we've been successful. Before invoking the rules, we get our data from the database. We are then able to invoke the database with a snapshot of the data at the current point in time. When we finish, we have our result, and can now update the database.

At minimum, what we should be doing is checking that the state that we ran against the rules is the same state that the database is in when we try to apply the user action. If something has changed in the meantime, we can terminate the request with an HTTP 409 Conflict (or equivalent if not in an HTTP API or not using HTTP status codes.) This sort of response will indicate that yes, we tried to do the thing you asked, but there was a conflict that occurred -- likely a conflicting request that changed state before we were able to apply our changes.

The end of the road

And that is that. Hopefully you now understand why interacting with a database from inside of a DRL directly is bad, interacting with a DAO from inside of a DRL is only marginally better, and what you should actually be doing is doing all of your database GETs/READs first and then calling the rules and then doing any necessary SAVEs for your updated state.

(Now if you really want to kick over an anthill, find a bunch of DBAs and ask them their opinion on saving derived or calculated data in a database. If they had their way, we'd never be saving anything that we "calculated" in rules into the DB.)

Thanks for sticking with me on this extended rant about Drools and databases. Hopefully it made sense; if not feel free to leave a message. All sample applications were made up on the fly and do not represent any real production application that I know of or have worked on.

And, as always, happy coding!

Cover image attribution: ulleo @ Pixabay

Drools & Databases, Part 2: Don't Call the DAO from the DRL

Roddy — Wed, 25 Aug 2021 06:41:34 +0000

One of the most common questions on StackOverflow in the Drools tag is something along the lines of "how do I call my database from my rules?"

The short answer is "you shouldn't." The longer answer is "it's complicated." This is the second in a series of posts explaining why.

Last time, we discussed why you shouldn't be writing database access functions directly in your DRL files. Today we're going to talk about why even a properly designed and hardened database access layer shouldn't be invoked from Drools.

Before we begin, let me define what I mean by "properly designed and hardened database access layer." We're going to assume an application has been implemented that has:

transaction management for all database access methods (for example, consider Hibernate's @Transactional annotation and similar mechanisms)
query sanitization of all inputs into queries
connection pooling for all connections (eg. C3P0 or Apache Commons DBCP)
appropriate security, access restrictions, credential management, and role management around database access

Further, we will assume that the application exposes a service class or interface through which we can interact with the database. This might be something like a Spring Data JPA repository, or some other form of DAO. For the purpose of this article, we'll assume that this class or interface exposes methods which return simple Java objects or collections of objects that model the results of the query. We make no assumptions about the design of those returned objects -- maybe they're annotated DTOs or entities; maybe they're just POJOs or JDK 14 Records.

Now that we've got that squared away, we'll get down to the meat of the problem ... namely:

Drools doesn't execute stuff like you think it does

Let's begin with a caveat. Technically if you take the time and effort to properly design your data access layer and address the concerns listed in the introduction and in the previous article, then you technically can go about calling your database from within your rules.

But you shouldn't, and this time, the reason has less to do with your database and more to do with Drools itself.

Namely this: most people don't properly understand the Drools lifecycle.

Why does this matter? Because unless you maintain extremely strict standards in your rule design forever, and familiarize yourself with how the Drools engine works in terms of execution and rule matching, you're going to be executing your queries a lot more often than you think. This leads to a bunch of potential issues:

Latency. Database calls aren't "free". Every time a thread executes the rules and makes queries, they'll be using a connection and preparing a statement and executing it and parsing the results. If you have one thread, that's probably not so noticeable. But once you scale up, even with the best connection pooling, you're going to be seeing lag. You'll be seeing even more lag if you implement your rules naively (that is, without understanding how the Drools engine works.)
Data consistency. A database is a shared resource. When you make query, you get a snapshot of the data at a specific point in time. Every time you repeat the query, you're adding the potential that the records have changed out from under you -- what was necessarily true at the time you made the previous query may no longer be true anymore.

There are other issues as well, but these are the top two.

Let's look at an example which illustrates the issue.

A simple example

Our example application provides order tracking and invoice generation via some rules. I'm going to use JDK14 Records to model the data, but you can easily imagine them as POJOs instead.

For the purpose of our rules, our application has three models:

record Customer( UUID id, Collection<Invoice> invoices, ... )
record Order( UUID id, UUID customerId, Float appliedTax, ... )
record Invoice( ... )

We've also abstracted all of our database logic into a service, CustomerOrdersService. Our particular example will only make use of two methods:

public Order getLatestOrderForCustomer(UUID customerId);
public void updateOrder(Order order);

Note that updateOrder will perform a database save. We'll assume that any auditing (eg. user/date updated) are out of scope and happen somewhere offscreen.

And finally, we have a utility class TaxUtils for some tax-related logic. We also have a factory InvoiceFactory for generating invoices.

So, for our application we've got the following use cases:

If the latest order for the customer is missing tax information, calculate the applied tax and update the order.
If the latest order for the customer has all required information (in this example, tax info), generate an invoice for the order and apply it to the customer account.

As a first pass, our rules might look something like this

rule "Customer Order is Missing Tax Information"
when
  $svc: CustomerOrdersService()

  Customer( $id: id != null )
  $order: Order( appliedTax == null) from $svc.getLatestOrderForCustomer($id)
then
  // calculate and apply the tax using some external utility
  TaxUtils.applyTax($order)

  // update the database record
  $svc.updateOrder($order)
end

rule "Generate Invoice for Customer Order"
when
  $svc: CustomerOrdersService()

  $customer: Customer( $id: id != null )
  $order: Order( appliedTax != null) from $svc.getLatestOrderForCustomer($id)
then
  Invoice invoice = InvoiceFactory.generate($order)
  $customer.addInvoice(invoice)
end

There are two big problems here.

Problem 1: we call getLatestOrderForCustomer twice. We incur the latency of two database calls where one would have sufficed. Further, it is possible that a new Order may have been added to the database while Drools was evaluating these rules. That means that the first rule may be working on Order 5, and the second rule may be working with Order 6.

Problem 2: the order update is invisible. The first rule handles a use case where we're missing some necessary data (tax). On the left hand side we check that we're missing it, and on the right hand side we calculate it and save the results to the database. Now our data is good in the database, but what about the rule to generate invoices? It will never fire because we checked previously that there was tax information present when we were deciding whether or not to fire the rules -- and at that time there was no tax information. In order to actually get the second rule to fire in this case, we'd need to do an update call to re-trigger the evaluation of all rules, which means we'll be calling the database two more times and thus exacerbating problem 1.

If you look at a Drools rule file, it's easy to fall into the trap of thinking that Drools will evaluate each rule sequentially: read the when, and execute the then; then read the next when, and execute its then. That's not actually how Drools works, however, and that's the trap that these example rules have fallen into.

The Drools lifecycle has various "phases", but we're specifically going to just talk about the 'Matching' phase. When you call "fire rules", Drools first gathers up all of the available rules, sorts them by salience and natural order, and then iterates across them evaluating the "when" clause. For a given rule, if all the statements in the "when" clause evaluate successfully then the rules engine considers this a "match" and it adds it to an ordered list of "matched" rules. (Hint: if you implement a rule listener, you can watch for the 'Match Created' event.)

Once all of the rules' left hand sides have been evaluated, the rules engine takes that list of matches and iterates over them, evaluating the right hand side.

This is why database access on the left hand side of the rule is so problematic -- the database queries are run during the 'match' phase, which means that any changes to the database values will be "invisible" unless the rule engine re-evaluates its previous matches. Drools does make such reevaluation possible through the use of some built-in functions -- update, insert, modify, etc. Once you do call these methods, Drools will reevaluate the matches, redoing those database calls and incurring the inherent latency and danger of data inconsistency therein.

A simple refactor

Let's attempt to refactor our rules to address the problems we previously identified.

For data consistency and latency concerns, we'll want to call our getLatestOrderForCustomer exactly one time. Further, we'll need to make sure our changes to the order instance (the calculated tax applied) to be visible to all rules without requiring a new call to the database.

Here's what we get by applying these requirements:

rule "Get Latest Order"
when
  $svc: CustomerOrdersService()
  not( Order() )
  Customer( $id: id != null )
then
  Order $order = $svc.getLatestOrderForCustomer($id);
  insert($order)
end

rule "Customer Order is Missing Tax Information - v2"
when
  $svc: CustomerOrdersService()

  // Check the order in working memory
  $order: Order( appliedTax == null )
then
  // calculate and apply the tax using some external utility
  TaxUtils.applyTax($order)

  // update the database record
  $svc.updateOrder($order)

  update($order)
end

rule "Generate Invoice for Customer Order - v2"
when
  $customer: Customer( $id: id != null )
  $order: Order( appliedTax != null )
then
  Invoice invoice = InvoiceFactory.generate($order)
  $customer.addInvoice(invoice)
end

This is a little more complex, but we should have addressed the biggest concerns.

First, we've added a new rule -- "Get Latest Order". This rule checks to see if there's an order in working memory; if there's not, it does a lookup and adds it. It calls insert, which will tell Drools to re-evaluate subsequent rules to see if they're now matches.

You'll also notice that the other two rules have been updated to use the Order information in working memory instead of doing the database lookup themselves. This restructuring means that we should be calling the getLatestOrderForCustomer method only once per execution, which should take care of most of our problems regarding latency and data integrity.

Problem 1. We only call getLatestOrderForCustomer once, and that's on the right hand side (RHS) of the rules, so it's not going to be called extraneously during the matching phase. Only when this specific rule matches will the rule be called; the consequences in this rule also adjust working memory so that the rule will no longer be eligible to fire a second time.

Problem 2. Since we're doing work on an Order instance in working memory, we no longer need to be concerned about the tax changes being invisible to the invoice rule. Because the Order is in working memory, we can call update on it after we make the changes and that will reevaluate the rules and trigger the invoice rule because of the newly non-null value.

So what problems do we have left?

Maintenance: the hidden debt

Technically, without adding any more color to the use cases and methods I've presented, there's nothing wrong with the rules as written. Without any more complexities or "gotachas", the rules as written would suffice, and we could use them as-is in our application. But should we?

I would argue that no, you shouldn't. Not because the rules are bad -- because they're really not -- but because they're fragile.

From now on, any engineer who you ask to maintain these rules will need to:

understand the intricacies of the Drools lifecycle, namely how the "matching" phase works and how different actions (eg. update, modify, insert, etc) cause the phase to be re-evaluated
be intimately familiar with your data access layer and models, especially regarding how data is fetched and modelled
understand how the two interact with each other

This is further complicated by the fact that it's not really feasible to create test scenarios -- automated or otherwise -- against the problems we're guarding against. Recall that one of the problems I initially called out was that we get the "latest" order twice; thus if a new order was inserted into the database during the matching phase, it would be possible that two different orders were being evaluated simultaneously in different rules. This would be spectacularly difficult to test -- honestly, I can't think of how to induce the scenario consistently (at least, not without significantly modifying the Drools runtime and the production code.)

It's not unreasonable to ask a software engineer to consider race conditions, of course. If we had a method where we called "get latest" twice, it would be reasonable to expect an experience engineer to recognize that potentially the second call may return a different value than the first. A junior engineer may not notice this weakness, however, and may implement the sequential calls, even in a simple code context. (After all, you want to make sure your data isn't stale, right? What better than to get a fresh copy every time?) Personally, I wouldn't fault an entry-level engineer for overlooking this; with experience comes seniority -- but do you really want to restrict your rules to only being updated by senior engineers? And, given the inability to test for the problems that arise from not understanding these complexities -- do you really want to have to rely on equally senior but still very human code reviewers to notice, identify, and flag potential workflow issues?

That's the crux of the problem really. You're relying on two very particular and complex technologies -- Drools rule execution and database access -- both of which have their own distinct problems, but which together present an additional set of combined challenges that one can only readily identify and work with if an engineer understands both knowledge domains. And the worst part? You can't reliably produce automated tests for these issues, so new problems are likely to be identified only in production.

At the end of the day, my argument really boils down to some software engineering principles. KISS -- Keep It Simple, Stupid -- and the Single Responsibility Principle. The purpose of a database is to provide data access; the purpose of Drools or any rules system is to make decisions against inbound data; access your data first, and then you can make decisions at your leisure. And, honestly, most software engineering could benefit from KISS: not only does it make your life simpler today, but it reduces your maintenance overhead tomorrow. If your rules don't interact with your database, you don't need to worry about a well meaning intern showing up several years down the road and making an innocent change in response to a bug that causes millions of dollars' worth of consequences. (True story.)

The End

Honestly, my examples weren't even as complex as they could be. Consider, for example, what might happen if the model returned by our data access layer included a Hibernate lazily loaded collection. Such a collection can only be loaded if we have an active transaction (eg. we need to be inside the @Transactional context); we're safe as long as the rules never interact with that collection ... but what if they potentially need to? Should we eagerly load the collection, just in case, and take the performance hit? Or do we need to somehow get a transaction to do the lazy-load only when we need it?

At this point I'm belaboring the point, and I hope that I've made it clear why you shouldn't access databases from Drools even with a hardened, production-ready data access layer.

Like part 1, this isn't actually the end of my extended rant against database access in Drools -- it's just the wrap-up of part 2 this monologue. Stay tuned for part 3!

Cover image attribution: cowins @ Pixabay

Drools & Databases, Part 1: Don't Call the DB from the DRL

Roddy — Fri, 14 May 2021 05:53:42 +0000

One of the most commonly asked Drools questions that I see on StackOverflow is something along the lines of "How do I do a database query from Drools?"

The answer is simply: you don't.

OK, to be entirely truthful, you can but you shouldn't. This is the first in a series of posts in which I'm going to explain why.

Today we're going to talk about the problems on the database side of things; namely:

Database Access is Hard (to do right)

It's actually pretty easy to interact with a database in Java. Even if you're using the built-in JDBC APIs (eg. java.sql.Statement, etc.), it's just not that hard to open a connection, execute a statement, and get results. Libraries like Spring Data and JPA abstract even more away, sometimes going as far as automatically generating your queries for you.

But like many things, just because it's easy to do doesn't necessarily mean you're doing it right. For a toy application, you can get away with a lot of stuff. But as soon as you scale to production, now you need to think about things like connection pooling, transaction management & rollbacks, distributed transactions, backups, audits, and all the other fun stuff that comes with exposing an application to Actual Users.

What this means is that doing a naive JDBC query like this in your Drools ...

// in a DRL file ...
// DO NOT DO THIS. PLEASE.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.ResultSet;

function String getCustomerNameFromDatabase(UUID customerId) {
  Class.forName("com.mysql.jdbc.Driver");
  Connection connection = DriverManager.getConnection(
      "jdbc:mysql://my-db.example.com:33000/something",
      "admin",
      "Secr3t P@ssWd!"
  );
  Statement statement = connection.createStatement();
  ResultSet results = statement.executeQuery("select * from Customer where customer_id='" + customerId + "'");
  results.first();
  return results.getString("customer_name");
}

rule "Example Rule"
when
  $invoice: Invoice( $customerId: custId != null)
  $customerName: String() from getCustomerNameFromDatabase($customerId)
  Receipt( purchaser != $customerName)
then
  // simple use case: the customer name for the customer on the invoice
  // does not match the customer name on the receipt.
end

... is an absolute no-no.

Will it work? Yes, technically. In a toy app or a proof-of-concept that will be run once and never again, then maybe it is "ok". But is it OK for production? Absolutely not.

Ignoring the fact that we're not doing any connection pooling or transaction management -- where's your error handling? What happens if your SQL statement is malformed? What happens if your connection is closed from under you? Heck, what happens if one of these statements throws an exception?

Where do you make sure your connection actually gets closed?

And don't even get me started about the hard-coded database credentials saved in plaintext in your DRL file. Or the fact that we're not sanitizing our inputs.

A slightly more realistic example

... But ok, yes, I admit that's a contrived example. Hopefully by now you know that you need to handle things like Exceptions and do things like close your connections, statements, etc. after you use them, even if an exception occurs.

If we were doing this in Java, I would absolutely suggest using try-with-resources for these elements, since the major classes do properly implement AutoCloseable. However DRL files don't support the full Java SDK, primarily because they're interpreted files rather than compiled -- the Drools framework parses these files as strings and then compiles the bits as needed, with a little DSL magic. (That's a topic for another post, though!)

The point is that while try-with-resources is the modern Java 8+ way of doing our connection management to make sure we properly close our connections, statements, and other sockets, we can't use that in our DRL declared function. As a result, we're going to have to go use something that looks more "old school" ... and I'm meaning Java 5-style "old school".

function String getCustomerNameFromDatabase(UUID customerId) {
  Class.forName("com.mysql.jdbc.Driver");

  Connection connection = null;
  Statement statement = null;
  ResultSet results = null;
  try {
    connection = DriverManager.getConnection(
        "jdbc:mysql://my-db.example.com:33000/something",
        "admin",
        "Secr3t P@ssWd!"
    );
    statement = connection.createStatement();
    results = statement.executeQuery("select * from Customer where customer_id='" + customerId + "'");
    results.first();
    return results.getString("customer_name");
  } catch (Exception e) {
    if (results != nill) {
      try { results.close(); } catch (Exception ignored) {}
    }
    if (statement != null) {
      try { statement.close(); } catch (Exception ignored{}
    }
    if (connection != null) {
      try { connection.close(); } catch (Exception ignored) {}
    }
  }

  return null;
}

And there we go, that's our dangling Connection, Statement, and ResultSet taken care of. Nasty, eh?

But wait! There's more! After all, we're still vulnerable to SQL injection -- look at those unsanitized SQL queries!

Some basic cleanup

The simplest way to go about this would be to replace our naive string-concatenation with a PreparedStatement. This isn't actually that big of a deal to change, all things considered.

function String getCustomerNameFromDatabase(UUID customerId) {  
  Connection connection = null;
  PreparedStatement statement = null;
  ResultSet results = null;
  try {
    Class.forName("com.mysql.jdbc.Driver");
    connection = DriverManager.getConnection(
        "jdbc:mysql://my-db.example.com:33000/something",
        "admin",
        "Secr3t P@ssWd!"
    );
    String query = "select * from Customer where customer_id=?";
    statement = connection.prepareStatement(query);
    statement.setString(1, customerId.toString());
    results = statement.executeQuery();
    results.first();
    return results.getString("customer_name");
  } catch (Exception e) {
    if (results != nill) {
      try { results.close(); } catch (Exception ignored) {}
    }
    if (statement != null) {
      try { statement.close(); } catch (Exception ignored{}
    }
    if (connection != null) {
      try { connection.close(); } catch (Exception ignored) {}
    }
  }

  return null;
}

Honestly this is getting tedious, so fixing the ResultSet is left as an exercise for the reader. Consider: what do we do if the query returns no rows? more than one row?

The last part we'll address is the exposed database credentials. Here we're unfortunately trying to work around a limitation of the subset of languge features available in DRL functions: we can't use the DriverManager in its entirety because it relies on properties being automatically loaded from the classpath. This isn't available natively in DRL functions, so we're using the old-school Class.forName method which went out of vogue with Java 7.

A better design would be, admittedly, to pass the credentials into working memory. That would, at least, keep them from being hard-coded in plain-text in the DRL file itself.

So we'll create a simple POJO to pass into the rules:

class DbConnectionInfo {
  private String url;
  private String username;
  private String password;
  // getters, setters omitted for brevity
}

... and then pass that from the rules to the function:

function String getCustomerNameFromDatabase(DbConnectionInfo connectionInfo, UUID customerId) {  
  Connection connection = null;
  PreparedStatement statement = null;
  ResultSet results = null;
  try {
    Class.forName("com.mysql.jdbc.Driver");
    connection = DriverManager.getConnection(
        connectionInfo.getUrl(),
        connectionInfo.getUsername(),
        connectionInfo.getPassword()
    );
    String query = "select * from Customer where customer_id=?";
    statement = connection.prepareStatement(query);
    statement.setString(1, customerId.toString());
    results = statement.executeQuery();
    results.first();
    return results.getString("customer_name");
  } catch (Exception e) {
    if (results != nill) {
      try { results.close(); } catch (Exception ignored) {}
    }
    if (statement != null) {
      try { statement.close(); } catch (Exception ignored{}
    }
    if (connection != null) {
      try { connection.close(); } catch (Exception ignored) {}
    }
  }

  return null;
}

rule "Example Rule"
when
  $cxnInfo: DbConnectionInfo()

  $invoice: Invoice( $customerId: custId != null)
  $customerName: String() from getCustomerNameFromDatabase($cxnInfo, $customerId)
  Receipt( purchaser != $customerName)
then
  // simple use case: the customer name for the customer on the invoice
  // does not match the customer name on the receipt.
end

And there we go. Still awful, but marginally less so.

But that doesn't mean it's any good

Even with all that work, and 31 lines of code (not counting the not-yet-implemented ResultSet cleanup), this is just one database query, and it's spectacularly not production-ready.

For any production-grade database access, there's a certain minimum of hardening we need to do to properly prepare for the transactional volumes inherent in real-world applications.

Looking back at our example, you'll notice that we've got a pretty basic implementation, courtesy of the JDBC API. This is fine for when you're learning, but nobody that I know of actually uses pure JDBC like this in production. Our implementation doesn't support connection pooling or transaction management. And, honestly, the only way I know how to implement either of those is with libraries that aren't compatible with DRL functions.

I mentioned this previously, but the Java language features available for use in the DRL are a very restricted subset of the full Java language. Database interactions with modern libraries rely on features that are simply not available in DRL functions. We can't just slap a @Transactional annotation on the DRL function (no annotations in DRL! Or, well, not like this.) I suppose it might be possible to implement connection pooling via something like c3p0, but it would be likely a hundred or more lines of code and the results would be sub-optimal.

Moving beyond database hardening, the other major issue here is that this approach simply does not scale. In our example, all we're trying to do is get the Customer's name from the database. That's one very simple query, for one very distinct use case. No application ever stays that small for long. As the application grows and additional rules are implemented, each subsequent query will need to go through this same process and face these same issues.

Just one database "getter" function and its dependent rule was already a 31-to-5 line ratio. Can you just imagine how huge your DRL files will be as your application and number of queries scale?

Not only that, but imagine more complex queries. Our example was a trivial SELECT * to retrieve one row per certain conditions. Now imagine what update calls would look like, and how you'd have to implement (manually) roll backs. Or consider a delete call that cascades across multiple tables.

At the end of the day, DRL functions have a place and a purpose -- and this is not it.

The End

This isn't actually the end, we're just wrapping up this first section of my extended rant about why you shouldn't be trying to access the database from your rules. In this post we covered why you shouldn't try to do this by implementing DRL functions directly. Next time I'll talk about why even a properly implemented data access layer shouldn't be invoked from the rules.

Happy coding!

Cover image attribution: trondmyhre4 @ Pixabay