Re: Implementing "thick"/"fat" databases

On Tue, Jul 26, 2011 at 1:04 AM, Sim Zacks <sim(at)compulab(dot)co(dot)il> wrote:

> No need for PL/Mono or any other client specific language. The GUI should be
> dumb, so all I really need to program design is the interface and input
> output methods. When you push a button, it should call the appropriate
> function. The functions on the barcode terminal (which is a Windows Mobile
> platform) can also be run on the client application written in wxpython.
> Keeping as much as possible off of the client allows me to share the work
> and be sure that both clients do the exact same thing when the user pushes
> the button.
>
> There is always functionality that needs to be on the client, however in my
> experience with multi-client applications, if it is business logic you will
> end up duplicating it and then needing to maintain 2 copies of the same
> thing.

I am not sure it applies to all business logic. For example suppose
the product team and the sales team both need to be able to be
assigned tickets relating to customer feedback. They may have very
different rules and processes for dealing with that. There is
commonality (how the data is stored, retrieved, presented to the
application), but if you want to enforce the different rules, you are
going to have to put the differences in business logic somewhere.

>
> So much of human interaction is definition of terms. Now I understand what
> you mean by data logic.
>
> To me data logic is strictly whether the data will be considered corrupt if
> the database allows an action to occur. For example, not enforcing
> relationships. If you erase the parent the child has no meaning and
> therefore you have data in the system which is suspect.
> As my db is designed with the business rules in mind, some of the business
> logic is included in the data logic, for example, 3 tables must be updated
> at the same time in order for the transaction to be considered valid. This
> is taken care of either through a function or triggers.
>
> An example of Business Logic is when my stock is updated to 0, I want the
> record deleted.
> This is done through a rule that on update that changes the value to 0
> delete instead.

Ok, that's a pretty simple example of logic that belongs in the database.

>
> The benefits of putting everything into the database is having one platform
> to maintain. Also, anything that can talk to the database can call the
> functions, so we have a defined interface for all our functions. If I wanted
> to use JBOSS, for example, how would my wxpython or my c# app talk to it?

That's not really what I am getting at. The question could be better phrased:
Why put workflow-specific logic in the database (your interactive
query idea is an example of that)?

Why not use the db procedures to create a well-formed API which
enforces business logic consistently across applications, but allows
the applications to manage their own workflow? Isn't this what you'd
want to use middleware for if you were going this route?

> IOW, I did not find a middleware that was more appropriate for a
> multi-client/multi-platform environment then the database server.
> Another big advantage of using the database for business logic, is that the
> plpython functions can be called from other functions. For example, in the
> python prompt function I posted earlier, I can have an plpgsql function say
> if python_prompt('Are you sure?"') then
>     process
> end if

So if arbitrary model function calls workflow functions, does that
mean you have already broken out of the MVC approach? I mean
does the MVC approach allow the model to control the controller?

I guess the approach I take is to put as much of the model in the
database as possible, and put the controllers on the client. Deciding
when to prompt the user for input seems to me it should be a
controller function.

> Any client that has the ability to listen to a port will be able to use this
> functionality.
> In C# for example, I would use System.Net.Sockets and the TcpListener class
> In Python I would use the socketserver module

Any application also has an ability to call database functions as an
API as well.

> What we do today (the reason I am playing with this interactivity) is have
> the database return an error code, which the client understands to be a
> question. It then prompts the user and the query is sent again with the
> answer as a parameter. It works but it is ugly (IMO).

That's what we do with LedgerSMB and the user creation workflow. I
think it has the advantage of a cleaner API.

For example, our function definition for the save_user function is:

CREATE OR REPLACE FUNCTION admin__save_user(
in_id int,
in_entity_id INT,
in_username text,
in_password TEXT,
in_import BOOL
) returns int AS $$

There are some nice things about this. It allows the client
application to specify at the outset whether the prospective user is
to be imported into the application (i.e. created as a user of the
application without having a new role created or whether this a new
user that needs to have these created. The actual checking is done
by:

PERFORM rolname FROM pg_roles WHERE rolname = in_username;
t_is_role := found;
t_is_user := admin__is_user(in_username);

IF t_is_role is true and t_is_user is false and in_import is false THEN
RAISE EXCEPTION 'Duplicate user';
END IF;

if t_is_role and in_password is not null then
execute 'ALTER USER ' || quote_ident( in_username ) ||
' WITH ENCRYPTED PASSWORD ' || quote_literal (in_password)
|| $e$ valid until $e$ ||
quote_literal(now() + '1 day'::interval);
elsif in_import is false AND t_is_user is false
AND in_password IS NULL THEN
RAISE EXCEPTION 'No password';
elsif t_is_role is false THEN
-- create an actual user
execute 'CREATE USER ' || quote_ident( in_username ) ||
' WITH ENCRYPTED PASSWORD ' || quote_literal (in_password)
|| $e$ valid until $e$ || quote_literal(now() +
'1 day'::interval);
END IF;

In other words....

if there is a problem we raise an exception and the client has to
check the exception code. In that case we don't return anything. The
client can then respond to the exception string and either tell the
user what happened or otherwise handle the error. So for example if
the "No password" exception is hit, the client application can tell
the user "I am sorry, but you must specify a password when creating a
new user." On the other hand the "Duplicate user" exception can be
checked and if that is the case, display a dialog:

"Username already taken by another postgresql user. Import that user
into LedgerSMB?" (yes/no).

"Yes" can resubmit without a password field, and with in_import set to true.

One major win of doing things this way is that the output can be
readily localized. With the PLPython function I am not at all sure it
can be without a lot of error-prone, manual extraction of strings.
This may not be a big deal for an in-house app used in one country,
but it could be a bigger issue as soon as national borders are crossed
unless you expect everyone to be using the application in one specific
language. And it keeps the API clean while keeping the client from
also having to be a server ;-)

Best Wishes,
Chris Travers