21

The example below is totally artificial and its only purpose is to get my point across.

Suppose I have an SQL table:

CREATE TABLE rectangles (
  width int,
  height int 
);

Domain class:

public class Rectangle {
  private int width;
  private int height;

  /* My business logic */
  public int area() {
    return width * height;
  }
}

Now suppose that I have a requirement to show to the user the total area of all rectangles in the database. I can do that by fetching all the rows of the table, turning them to objects and iterating over them. But this looks just stupid, because I have lots and lots of rectangles in my table.

So I do this:

SELECT sum(r.width * r.height)
FROM rectangles r

This is easy, fast and uses the strengths of the database. However, it introduces duplicated logic, because I have the same calculation in my domain class also.

Of course, for this example the duplication of logic is not fatal at all. However, I face the same problem with my other domain classes, which are more complex.

  • 1
    I suspect the optimal solution will vary pretty wildly from codebase to codebase, so could you briefly describe one of the more complex examples that's giving you trouble? – Ixrec Jul 21 '15 at 20:07
  • 2
    @lxrec: Reports. A business application that has rules which I am capturing in classes, and I also need to create reports that show the same information, but condensed. VAT calculations, payments, earnings, such kind of stuff. – Escape Velocity Jul 21 '15 at 20:14
  • 1
    Isn't this also a question of distributing the load between server and clients? Sure, just dumping the cached result of the calculation to a client is your best bet, but if the data changes often and there are many requests, it might be advantageous to be able to just throw the ingredients and the recipe at the client instead of cooking the meal for them. I think it is not necessarily a bad thing to have more than one node in a distributed system that can provide a certain functionality. – null Jul 21 '15 at 21:19
  • I think the best way is to generate such codes. I will explain later. – Xavier Combelle Jul 27 '15 at 20:49
11

As lxrec pointed out, it'll vary from codebase to codebase. Some applications will allow you to put those kind of business logic into SQL Functions and/or queries and allow you to run those anytime you need to show those values to the user.

Sometimes it may seem stupid, but it's better to code for correctness than performance as a primary objective.

In your sample, if you're showing the value of the area for a user in a webform, you'd have to:

1) Do a post/get to the server with the values of x and y;
2) The server would have to create a query to the DB Server to run the calculations;
3) The DB server would make the calculations and return;
4) The webserver would return the POST or GET to the user;
5) Final result shown.

It's stupid for simple things like the one on the sample, but it may be necessary to more complex stuff like calculating the IRR of an investment of a client in a banking system.

Code for correctness. If your software is correct, but slow, you'll have chances to optimize where you need (after profiling). If that means keeping some of the business logic in the database, so be it. That's why we have refactoring techniques.

If it becomes slow, or unresponsive, than you may have some optimizations to do, like violating the DRY principle, which is not a sin if you surround yourself of the proper unit testing and consistency testing.

  • 1
    The trouble with putting (procedural) business logic in SQL is that is extremely painful to refactor. Even if you have top notch SQL refactoring tools, they usually don't interface with code refactoring tools in your IDE (or at least I have not yet seen such a toolset) – Roland Tepp Jul 23 '15 at 10:33
2

You say that the example is artificial, so I don't know if what I'm saying here suits your actual situation, but my answer is - use an ORM (Object-relational mapping) layer to define the structure and querying/manipulation of your database. That way you have no duplicated logic, since everything will be defined in the models.

For example, using the Django (python) framework, you would define your rectangle domain class as the following model:

class Rectangle(models.Model):
    width = models.IntegerField()
    height = models.IntegerField()

    def area(self):
        return self.width * self.height

To calculate the total area (without any filtering) you'd define:

def total_area():
    return sum(rect.area() for rect in Rectangle.objects.all())

As others have mentioned, you should first code for correctness, and only optimize when you really hit a bottleneck. So, if at a later date you decide, you absolutely have to optimize, you could switch to defining a raw query, such as:

def total_area_optimized():
    return Rectangle.objects.raw(
        'select sum(width * height) from myapp_rectangle')
1

I've written a silly example to explain an idea:

class BinaryIntegerOperation
{
    public int Execute(string operation, int operand1, int operand2)
    {
        var split = operation.Split(':');
        var opCode = split[0];
        if (opCode == "MULTIPLY")
        {
            var args = split[1].Split(',');
            var result = IsFirstOperand(args[0]) ? operand1 : operand2;
            for (var i = 1; i < args.Length; i++)
            {
                result *= IsFirstOperand(args[i]) ? operand1 : operand2;
            }
            return result;
        }
        else
        {
            throw new NotImplementedException();
        }
    }
    public string ToSqlExpression(string operation, string operand1Name, string operand2Name)
    {
        var split = operation.Split(':');
        var opCode = split[0];
        if (opCode == "MULTIPLY")
        {
            return string.Join("*", split[1].Split(',').Select(a => IsFirstOperand(a) ? operand1Name : operand2Name));
        }
        else
        {
            throw new NotImplementedException();
        }
    }
    private bool IsFirstOperand(string code)
    {
        return code == "0";
    }
}

So, if you have some logic:

var logic = "MULTIPLY:0,1";

You can re-use it in domain classes:

var op = new BinaryIntegerOperation();
Console.WriteLine(op.Execute(logic, 3, 6));

Or in your sql-generation layer:

Console.WriteLine(op.ToSqlExpression(logic, "r.width", "r.height"));

And, of course, you can change it easily. Try this:

logic = "MULTIPLY:0,1,1,1";
-1

As @Machado said, the easiest way to do it is to avoid it and doing all your processing in your main java. However, it is still possible to have to code base with similar code without repeating your self by generating the code for both of code base.

For example using cog enable to generate the three snippets from a common definition

snippet 1:

/*[[[cog
from generate import generate_sql_table
cog.outl(generate_sql_table("rectangle"))
]]]*/
CREATE TABLE rectangles (
    width int,
    height int
);
/*[[[end]]]*/

snippet 2:

public class Rectangle {
    /*[[[cog
      from generate import generate_domain_attributes,generate_domain_logic
      cog.outl(generate_domain_attributes("rectangle"))
      cog.outl(generate_domain_logic("rectangle"))
      ]]]*/
    private int width;
    private int height;
    public int area {
        return width * heigh;
    }
    /*[[[end]]]*/
}

snippet 3:

/*[[[cog
from generate import generate_sql
cog.outl(generate_sql("rectangle","""
                       SELECT sum({area})
                       FROM rectangles r"""))
]]]*/
SELECT sum((r.width * r.heigh))
FROM rectangles r
/*[[[end]]]*/

from one reference file

import textwrap
import pprint

# the common definition 

types = {"rectangle":
    {"sql_table_name": "rectangles",
     "sql_alias": "r",
     "attributes": [
         ["width", "int"],
         ["height", "int"],
     ],
    "methods": [
        ["area","int","this.width * this.heigh"],
    ]
    }
 }

# the utilities functions

def generate_sql_table(name):
    type = types[name]
    attributes =",\n    ".join("{attr_name} {attr_type}".format(
        attr_name=attr_name,
        attr_type=attr_type)
                   for (attr_name,attr_type)
                   in type["attributes"])
    return """
CREATE TABLE {table_name} (
    {attributes}
);""".format(
    table_name=type["sql_table_name"],
    attributes = attributes
).lstrip("\n")


def generate_method(method_def):
    name,type,value =method_def
    value = value.replace("this.","")
    return textwrap.dedent("""
    public %(type)s %(name)s {
        return %(value)s;
    }""".lstrip("\n"))% {"name":name,"type":type,"value":value}


def generate_sql_method(type,method_def):
    name,_,value =method_def
    value = value.replace("this.",type["sql_alias"]+".")
    return name,"""(%(value)s)"""% {"value":value}

def generate_domain_logic(name):
    type = types[name]
    attributes ="\n".join(generate_method(method_def)
                   for method_def
                   in type["methods"])

    return attributes


def generate_domain_attributes(name):
    type = types[name]
    attributes ="\n".join("private {attr_type} {attr_name};".format(
        attr_name=attr_name,
        attr_type=attr_type)
                   for (attr_name,attr_type)
                   in type["attributes"])

    return attributes

def generate_sql(name,sql):
    type = types[name]
    fields ={name:value
             for name,value in
             (generate_sql_method(type,method_def)
              for method_def in type["methods"])}
    sql=textwrap.dedent(sql.lstrip("\n"))
    print (sql)
    return sql.format(**fields)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.