Friday, August 24, 2012

PostgreSQL OR Modelling Part 2: Intro to Object Relational Classes in PostgreSQL

In the last post we went briefly over the considerations and concerns of object-relational programming in PostgreSQL.  Now we will put this into practice.  While LedgerSMB has not fully adopted this approach I think it is likely to be the long-term direction for the project.

In object relational thinking classes have properties and methods, and sets of objects are retrieve and even manipulated using a relational interface.  While relational systems look at sets of tuples, object-relational systems look at relational manipulation of sets of objects.  A table (and to a lesser extent, a view of a composite type) is hence a class and can have various forms of behavior associated with it the rows, which act as objects.  This means a number of considerations change.  Some differences include:

  • SELECT * is often useful to ensure one receives a proper data type
  • Derived values can be immitated by methods, as can dereferences of keys
  • Common filter conditions can be centralized, as can common queries
In general, understanding this section is important to understanding further sections in this series.

Basic Table Structure

Our current example set will use a very simplified schema for storing inventory.  Consider the (greatly simplified) chart of accounts table:

 CREATE TABLE account (
     id int not null unique,
     control_code text primary key, -- account number
     description text not null
);

Populated by:

INSERT INTO account (id, accno, description)
VALUES (1, '1500', 'Inventory'),
       (2, '4500', 'Sales'),
       (3, '5500', 'Purchase'); 

Of course in a real system the chart of account (and inventory) tables would be more complex.

CREATE TABLE inventory_item (
    id serial primary key,
    cogs_account_id int references account(id),
    inv_account_id int references account(id),
    income_account_id int references account(id),
    sku text not null,
    description text,
    last_cost numeric, -- null if never purchased
    sell_price numeric not null,
    active bool not null default true
);

Now, we'd also want to make sure only one active part can be associated with a sku at any given time, so we'd:

CREATE UNIQUE INDEX inventory_item_sku_idx_u
ON inventory_item (sku) WHERE active IS TRUE;

The create table statement also creates a complex type with the same structure, and thus it defines a data structure.  The idea of relations as data structures in themselves will come up again and again in this series.  It is the fact that tables are data structures which allows us to do interesting things here.

Method 1:  Derived Value called "markup"

The first method we may want to add to this table is a markup method, for calculating our markup based on current sell price and last cost.  Since this value will always be based on two other stored values, there is no sense in storing it (other than possibly in a pre-calculated index).  To do this, we:

CREATE FUNCTION markup(inventory_item) RETURNS numeric AS
$$ SELECT CASE WHEN $1.last_cost = 0 THEN NULL

               ELSE  ($1.sell_price - $1.last_cost)
                     / $1.last_cost

           END;

$$ LANGUAGE SQL IMMUTABLE;

Looking through the syntax here, this is a function named "markup" which receives a single input of the table type.  It then calculates a value and returns it based solely on inputs and returns a value.  The fact that it function always returns the same value when the same input is passed is reflected in the IMMUTABLE designation.  The planner uses this to plan queries, and PostgreSQL will not index function outputs unless they are marked immutable.

Once this is done, we can:

SELECT sku, description, i.markup from inventory_item i;

Note that you must include the table designation in calling the method.  PostgreSQL converts the i.markup into markup(i).

Of course, our table being empty, no values will be returned.  However if you try to omit the i. before markup, you will get an error.

Not only can we include this in the output we can search on the output:

SELECT sku, description from inventory_item i 
 where i.markup  < 1.5;

If we find ourselves doing a lot of such queries and need to index the values we can create an index:

CREATE INDEX inventory_item_markup_idx 
ON inventory_item (markup(inventory_item));

If you use object.method notation, you must add extra parentheses because of create index semantics [see comment added below]:

 CREATE INDEX inventory_item_markup_idx ON inventory_item ((inventory_item.markup));

Instead of adding columns and using triggers to maintain them, we can simply use functions to calculate values on the fly.  Any value you can derive directly from values already stored in the table can thus be calculated on output perhaps even with the values indexed.  This is one of the key optimizations that ORDBMS's allow.

Method 2:  Dereferencing  an Account

The above table is defined in a way that makes it easy to do standard, relational joins.  More specialized Object-Relational-friendly references will be covered in a future posting.   However suppose we want to create a link to the accounts.  We might create a method like:

CREATE OR REPLACE FUNCTION cogs_account(inventory_item) 
RETURNS account
LANGUAGE SQL AS
$$ SELECT * FROM account where id = $1.cogs_account_id $$;

We can now:

or_examples=# select (i.cogs_account).* FROM inventory_item i;

We will get an empty row with the chart of accounts structure.  This gives us the beginnings of a path system for LedgerSMB (a real reference/path system will be discussed in a future posting).

We can even:

select * from inventory_item i 
 where (i.cogs_account).control_code = '5500';

Note that in many of these cases, parentheses are necessary to ensure that the system can tell that we are talking about an object instead of a schema or other system.  This is true generally wherever complex data types are used in PostgreSQL (otherwise the i might be taken to be a schema name).  Unlike Oracle, PostgreSQL does not make assumptions of this sort, and unlike DB2 does not have a separate dereferencing operator.

Warning: Dereferencing objects in this way essentially forces a nested loop join.  It is not recommended when working with large return sets.  For example the previous has a plan (given that I already have one row in this table) as:

or_examples=# explain analyze
or_examples-# select * from inventory_item i where (i.cogs_account).control_code = '5500';
                                                 QUERY PLAN                    
                           
--------------------------------------------------------------------------------
----------------------------
 Seq Scan on inventory_item i  (cost=0.00..1.26 rows=1 width=145) (actual time=0
.174..0.175 rows=1 loops=1)
   Filter: ((cogs_account(i.*)).control_code = '5500'::text)
 Total runtime: 0.199 ms
(3 rows)

Note that the filter is not expanded to a join.  This means that for every row filtered upon, it is executing a query to pull the resulting record from the account table.  Depending on the size of that table and the number of pages containing rows referenced, this may perform badly.  For a few rows, however, it will perform well enough.

Method 3:  Computer-discoverable save method

One application for object-relational modeling is to provide a top-level machine-discoverable API which software programs can use, creating more autonomy and such between the application and the database.  We will create here a machine-discoverable save function which only updates values that may be updated, and returns the values as saved back to the application.  We can consider this sort of loose coupling a "dialog" rather than "remote command" interface because both are assumed to be autonomous and try to provide meaningful responses to the other where appropriate.

Our founction looks like this (not using writeable CTE's for backward compatibility):

CREATE FUNCTION save(inventory_item)
RETURNS inventory_item
LANGUAGE PLPGSQL STABLE AS
$$
  DECLARE out_item inventory_item;

 BEGIN
 -- we don't want to allow accounts to change on existing items
 UPDATE inventory_item
    SET sku = in_item.sku,
        description = in_item.description,
        last_cost = in_item.last_cost,
        sell_price = in_item.sell_price,
        active = in_item.active
  WHERE id = in_item.id;

 IF FOUND THEN
     SELECT * INTO out_item FROM inventory_item
      WHERE id = in_item.id;
     RETURN out_item;
 ELSE
 INSERT INTO inventory_item
                 (cogs_account_id,
                  inv_account_id,
                  income_account_id,
                  sku,
                  description,
                  last_cost,
                  sell_price,
                  active)

          VALUES (in_item.cogs_account_id,
                  in_item.inv_account_id,
                  in_item.income_account_id,
                  in_item.sku,
                  in_item.description,
                  in_item.last_cost,
                  in_item.sell_price,
                  in_item.active);
 SELECT * INTO out_item
      FROM inventory_item
     WHERE id = currval('inventory_item_id_seq');
    RETURN out_item;

  END IF;

 END;
$$;

This function is idempotent and it returns the values as saved to the application so it can determine whether to commit or rollback the transaction.  The structure of the tuple is also discoverable using the system catalogs and so an application can actually look up how to construct a query to save the inventory item.  However it certainly cannot be inlined and it will be slow on large sets.  If you have thousands of inventory parts, doing:

SELECT i.save FROM inventory_item i;

Will be painful and do very little other than generate dead tuples.  Don't do it.

On the other hand a software program (either at code generation or run-time) can look up the structure of the type and generate a call like:

 SELECT (i.save).* 
   FROM (SELECT (row(null, 3, 1, 2, 'TEST123', 'Inventory testing item', 1, 2, true)::inventory_item).save) i;

Obviously this is a sub-optimal interface for humans but it has the advantage of discoverability for a computer.  Note the "::inventory_item" may be unnecessary in some cases, however it is required for all practical purposes on all non-trivial databases because it avoids ambiguity issues.  We really want to make sure that it is an inventory_item we are saving especially as data types may change.  This then allows us to control application entry points to the data.

Note that the application has no knowledge of of what is actually happening under the hood of the save function.  We could be saving it in unrelated relations (and this may be a good way to deal with updateable views in an O-R paradigm where single row updates are the primary use case).

Caveats:  In general I think it is a little dangerous to mix imperative code with declarative SQL in this way.   Object-relational modelling is very different from object-oriented programming because with object-relational modelling we are modelling information, while with object-oriented programming we are encapsulating behavior.  This big difference results in endless confusion.

The most obvious way around this is to treat all SQL queries as questions and treat the transactional boundaries as imperative frames to the declarative conversation.  A human-oriented translation of the following exchange might be:

BEGIN;  -- Hello.  I have some questions for you.

SELECT (i.save).*
  FROM (SELECT (row(null, 3, 1, 2, 'TEST124', 
                'Inventory testing item 2', 1, 2, 
                true)::inventory_item).save) i;

-- Suppose I ask you to save an inventory item with the following info for me.
-- What will be saved?


  id | cogs_account_id | inv_account_id | income_account_id |   sku   |       desc
ription        | last_cost | sell_price | active
----+-----------------+----------------+-------------------+---------+-----------
---------------+-----------+------------+--------
  4 |               3 |              1 |                 2 | TEST124 | Inventory
testing item 2 |         1 |          2 | t


-- The above information will be saved if you ask me to.

COMMIT; -- Do it.

This way of thinking about the overall framework helps prevent a lot of problems down the road.  In particular this helps establish a separation of concerns between the application and the database.  The application is responsible for imperative logic (i.e. what must be done) while the database answers declarative queries and only affirmatively stores data on commit.  Imperative changes to data only occur when the application issues the commit command.

In this regard object behavior (outside of storage which is an odd fit for the model) really doesn't belong in the database.  The database is there to provide answers to questions and update stored information when told to commit changes.  All other behavior should be handled by other applications.  In a future post we will look at some ways to broaden the ways applications can receive data from PostgreSQL.  Object-relational modelling then moves beyond the question of "what information do I have and how can I organize it to get answers" to "what derivative information can be useful and how can I add that to my otherwise properly relational database?"

Alternate Constructor inventory_item(int)

Now in many cases we may not want to have to provide the whole object definition in order to instantiate it.  In fact we may want to be able to ask the database to instantiate it for us.  This is where alternate constructors come in.  Alternate constructors furthermore can be for single objects or for sets of objects.  We will look at the single objects first, and later look at the set-based constructors.

This constructor looks like:

CREATE OR REPLACE FUNCTION inventory_item(int)
RETURNS inventory_item
LANGUAGE SQL
AS $$

SELECT * FROM inventory_item WHERE id = $1

$$;

If I have an item in my db with an id of two (saved in with the previous method call) I can:

 or_examples=# select * from inventory_item(2);
 id | cogs_account_id | inv_account_id | income_account_id |   sku   |      descr
iption       | last_cost | sell_price | active
----+-----------------+----------------+-------------------+---------+-----------
-------------+-----------+------------+--------
  2 |               3 |              1 |                 2 | TEST123 | Inventory
testing item |         1 |          2 | t
(1 row)

I can also chain this together with other methods.  If all I want is the markup for item 2, I can:

or_examples=# select i.markup from inventory_item(2) i;
         markup        
------------------------
 1.00000000000000000000
(1 row)

I can even:

or_examples=# select (inventory_item(2)).markup;
         markup        
------------------------
 1.00000000000000000000
(1 row)

An application can then use something like this to traverse in-application links and retrieve new objects.

Alternate Constructor inventory_item(text)

Similarly we can have a constructor which constructs this from a  text field, looking up by active SKU:

CREATE OR REPLACE FUNCTION inventory_item(text)
RETURNS inventory_item
LANGUAGE sql AS $$

SELECT * FROM inventory_item WHERE sku = $1 AND active is true;

$$;

We can then:

SELECT (inventory_item('TEST123')).markup;

and get the same result as before.

Warning:  Once you start dealing with text and int constructors, you have the possibility of ambiguity on queries in.  For example, SELECT inventory_item('2') will run this on the text constructor instead of the integer constructor, giving you no results.  For this reason it is a very good idea to explicitly cast your inputs to the constructor.

Set Constructor inventory_item(tsquery)

Not only can this be used to create a constructor for a single item.  Whole sets can be constructed this way.  For example we could create a text search constructor (and this allows the default search criteria to change over time in a centrally managed way):

CREATE OR REPLACE FUNCTION inventory_item(tsquery)
RETURNS SETOF inventory_item
LANGUAGE SQL AS $$

SELECT * FROM inventory_item WHERE description @@ $1;

$$;

This allows some relatively powerful searches to be done, without the application having to worry about exactly what is searched on.  For example, we can:

or_examples=# select * from inventory_item(plainto_tsquery('test')); id | cogs_account_id | inv_account_id | income_account_id |   sku   |      descr
iption       | last_cost | sell_price | active
----+-----------------+----------------+-------------------+---------+-----------
-------------+-----------+------------+--------
  2 |               3 |              1 |                 2 | TEST123 | Inventory
testing item |         1 |          2 | t
(1 row)

Here test has been found to match testing because testing is a form of the word test.

Of coruse if we have more than one item in the table, we get more than one result:

 or_examples=# select * from inventory_item(plainto_tsquery('test'));
 id | cogs_account_id | inv_account_id | income_account_id |   sku   |       desc
ription        | last_cost | sell_price | active
----+-----------------+----------------+-------------------+---------+-----------
---------------+-----------+------------+--------
  2 |               3 |              1 |                 2 | TEST123 | Inventory
testing item   |         1 |          2 | t
  4 |               3 |              1 |                 2 | TEST124 | Inventory
testing item 2 |         1 |          2 | t
(2 rows)


These provide examples, I hope, provide some ideas for how one can take the object-relational concepts and apply them towards building more sophisticated, robust, and high-performance databases, as well as better interfaces for object-oriented programs.

Next Week:  Table Inheritance in PostgreSQL

Tuesday, August 21, 2012

O/R Modelling Part 1: Intro to PostgreSQL as Object-Relational Database Management System

This is a very brief intro to PostgreSQL as an object-relational database management system.   In future blog posts, we will look at more hands-on examples of these features in action.  Keep in mind these are advanced features typically used by advanced applications.

This is a very brief guide to the concepts we will be looking at more deeply in future posts, tying together in recipes and examples.   While PostgreSQL was initially designed to explore object-relational modelling possibilities, the toolkit today is somewhat different than it was initially intended, and therefore the focus of this series will be how to use PostgreSQL in an Object-Relational manner, rather than tracking the history of various components.

How is PostgreSQL "Object-Relational?"

The term Object-Relational has been applied to databases which attempt to bridge the relational and object-oriented worlds with varying degrees of success.  Bridging this gap is typically seen as desirable because object-oriented and relational models are very different paradigms and programmers often do not want to switch between them.  There are, however, fundamental differences that make this a very hard thing to do well.  The best way to think of PostgreSQL in this way is as a relational database management system with some object-oriented features.

By blending object-primative and relational models, it is often possible to provide much more sophisticated data models than one can using the relatively limited standard types in SQL.  This can be done both as an interface between an application and database, and as intra-query logic.  In future posts I will offer specific examples of each concept, explore how PostgreSQL differs from Oracle, DB2, and Informix in this area.

PostgreSQL is a development platform in a box.  It supports stored procedures written in entirely procedural languages like PL/PGSQL or Perl without loaded modules, and more object-oriented languages like Python or Java, often through third party modules.  To be sure you can't write a graphical interface inside PostgreSQL, and it would not be a good idea to write additional network servers, such as web servers, directly inside the database.  However the environment allows you to create sophisticated interfaces for managing and transforming your data. Because it is a platform in a box the various components need to be understood as different and yet interoperable.  In fact the primary concerns of object-oriented programming are all supported by PostgreSQL, but this is done in a way that is almost, but not quite, entirely unlike traditional object oriented programming.  For this reason the "object-relational" label tends to be a frequent source of confusion.

Data storage in PostgreSQL is entirely relational, although this can be degraded using types which are not atomic, such as arrays, XML, JSON, and hstore.  Before delving into object-oriented approaches, it is important to master the relational model of databases.  For the novice, this section is therefore entirely informational.  For the advanced developer, however, it is hoped that it will prove inspirational.

In object-oriented terms, every relation is a class, but not every class is a relation.  Operations are performed on sets of objects (an object being a row), and new row structures can be created ad-hoc.  PostgreSQL is, however, a strictly typed environment and so in many cases, polymorphism requires some work.

Data Abstraction and Encapsulation in PostgreSQL


The relational model itself provides some tools for data abstraction and encapsulation, and these features are taken to quite some length in PostgreSQL.  Taken together these are very powerful tools and allow for things like calculated fields to be simulated in relations and even indexed for high performance.

Views are the primary tool here.  With views, you can create an API for your data which is abstracted from the physical storage.  Using the rules system, you can redirect inserts, updates, and deletes from the view into underlying relations, preferably using user defined functions.  Being relations, views are also classes and methods.  Views cannot simply be inherited and workarounds cause many hidden gotchas.

A second important tool here is the ability to define what appear to be calculated fields using stored procedures.  If I create a table called "employee" with three fields (first_name, middle_name, last_name) among others, and create a function called "name" which accepts a single employee argument and concatenates these together as "last_name, first_name middle_name" then if I submit a query which says: 

select e.name from employee e;

it will transform this into:

select name(e) from employee e;

This gives you a way to do calculated fields in PostgreSQL without resorting to views. Note that these can be done on views as well because views are relations.  These are not real fields though.  Without the relation reference, it will not do the transformation (so SELECT name from employee will not have the same effect).

Messaging and Class API's in PostgreSQL


A relation is a class.  The class is accessed using SQL which defines a new data structure in its output.  This data structure unless defined elsewhere in a relation or a complex type cannot have methods attached to it and therefore can not be used with the class.method syntax described above.  There are exceptions to this rule, of course, but they are beyond the scope of this introduction.  In general it is safest to assume that the output of one query, particularly one with named output fields, cannot safely be used as the input to another.

A second messaging apparatus in PostgreSQL is the LISTEN/NOTIFY framework which can be used along with triggers to issue notifications to other processes when a transaction commits.  This approach allows you to create queue tables, use triggers to move data into these tables (creating 'objects' in the process) and then issuing a notification to another process when the data commits and becomes visible.  This allows for very complex and and interactive environments to be built from modular pieces.

Polymorphism in PostgreSQL


PostgreSQL is very extensible in terms of all sorts of aspects of the database.  Not only can types be created and defined, but also operators can be defined or overloaded.

A more important polymorphism feature is the ability to cast one data type as another.  Casts can be implicit or explicit.  Implicit casts, which have largely been removed from many areas of PostgreSQL, allow for PostgreSQL to cast data types when necessary to find functions or operators that are applicable.  Implicit casting can be dangerous because it can lead to unexpected behavior because minor errors  can lead to unexpected results.  '2012-05-31' is not 2012-05-31.  The latter is an integer expression that reduces to 1976.  If you create an implicit cast that turns an integer into a date being the first of the year, the lack of quoting will insert incorrect dates into your database without raising an error ('1976-01-01' instead of the intended '2012-05-31').  Implicit casts can still have some uses.

Inheritance in PostgreSQL


In PostgreSQL tables can inherit from other tables.  Their methods are inherited but implicit casts are not chained, nor are their indexes inherited.  This allows you develop object inheritance hierarchies in PostgreSQL.  Multiple inheritance is possible, unlike any other ORDBMS that I have looked at on the market (Oracle, DB2, and Informix all support single inheritance).

Table inheritance is an advanced concept and has many gotchas.  Please refer to the proper sections of the manual for more on this topic.  On the whole it is probably best to work with table inheritance first in areas where it is more typically used, such as table partitioning, and later look at it in terms of object-relational capabilities.

Overall the best way to look at PostgreSQL as an object-relational database is a database which provides very good relational capabilities plus some advanced features that allows one do create object-relational systems on top of it.  These systems can then move freely between object-oriented and relational worldviews but are still more relational than object-oriented.  At any rate they bear little resemblance to object-oriented programming environments today.  With PostgreSQL this is very much a toolkit approach for object-relational databases building on a solid relational foundation.  This means that these are advanced functions which are powerful in the hands of experienced architects, but may be skipped over at first.

Next week:   An intro to object-relational "classes."  We will build a simple class that represents items in inventory.


Forthcoming Posts:
  • An intro to object-relational "classes" looking at tables with extended functionality
  • Table inheritance in PostgreSQL (including how to solve key problems)
  • Composite and row types in tables, including references to other tables
  • Interlude:  Comparison to MySQL as a way of questioning how we think about databases (no flames!)
  • Nested Data Structures (including things to avoid)
  • Polymorphism in PostgreSQL 
  • Messaging in PostgreSQL between client applications
  • General Design Patterns and Anti-Patterns

Monday, August 13, 2012

ACID as a basic building block of eventually consistent, distributed transactions

Anything worth doing is worth doing well.  It therefore follows that anything worth tracking for a business is worth tracking well.  While this involves tradeoffs which are necessarily business decisions, consistency and accuracy of data are always important considerations.

In a previous post, I looked at a the the possible origin of double entry accounting in stock and foil split tally sticks.  Now let's look at how financial systems of today might provide a different way of looking at distributed transactions in loosely coupled systems.  This  approach recognizes that in human systems all knowledge is fundamentally local and applies this to distributed computing environments, broadly defined.

Eventual Consistency as Financial Firewall

In the non-computer world there are very few examples of tightly coupled distributed transactions.  Dancing, perhaps comes to mind as does a string quartet playing a piece of music.  However most important work is done using loosely coupled systems.  Loosely coupled systems done right are more robust and avoid some of the problems associated with the CAP theorem.  If the first violinist suffers a mishap part way through a quartet and is unable to continue, you probably will have to stop playing.  If the petty cash manager suddenly falls ill after you have withdrawn money from the petty cash drawer to go buy urgently needed office supplies you can continue on your way.  You might have to wait until someone else can take over before you can give your receipt and change back, however.

Such systems however have two things in common:  first they are always locally consistent and this is extremely important.  The petty cash drawer and all cash vouchers are together in a consistent state.  Secondly counterparties are all locally consistent and transactions can be tied clearly back to such counterparties.  The party and counterparty together provide a basis for long-term conflict resolution in the form of an audit.  Eventual consistency is a property of the global system, not of any local component.

Moreover there are times when globally eventual consistency is actually desirable, but this business need is premised on absolute local consistency.  For example , if I am processing credit cards, the processing systems will be locally consistent and my accounting systems will be locally consistent, but these will not always be in sync.  My accounting department will have control over the data coming into the books.  Similarly if my inventory is stored by a third party and shipped, they may send me a report every day, but it is not going to hit the books until it is reviewed by a person.  Both of these approaches use global eventual consistency as a firewall against bad financial data entering the books.  In other words this is a control point where humans can review and correct the problems.  In addition to the performance issues, this is a major reason why you will rarely see two phase commit used to synchronize transactions between financial accounting systems.  Instead these will be moved in, in ways which are not globally consistent but only can become such after human review and approval.  The computer, in essence, is treated like a person which can make mistakes or worse.

If this is the case internally it is even more the case between businesses.  If I run a bank, I am not giving your bank direct access to my financial database, and I am not going to touch your database.  The need for eventual consistency in transferring money between our banks will have to take the form of messages exchanged, human review, and more.

Why SQL?  Why ACID?  Why not a NoSQL ERP?

I have often said that NoSQL is an extraordinarily poor choice for ERP software.  In addition to the difficulties in doing ad hoc reporting, you have a need for absolute, local consistency which is not a goal of NoSQL software.  Without local consistency, you can't audit your books, and you cannot determine where something went wrong.  ACID is not a global property of the business IT infrastructure and it shouldn't be (if you try to make it you run into a dreaded brick wall which is called "The CAP Theorem."  It is a property of local data stores and all your internal controls are based on the assumption that data is locally consistent and knowledge is local.

Example 1: An Eventually Consistent Cash Register

The first example of how we might look at this might be an eventually consistent retail environment.  In this environment we aren't taking materials out of the store to ship, or if we do we are going down and pulling them off the shelf before entry.  The inventory on the shelf is thus authoritative here.  The books are also reviewed every day and transactions reviewed/posted.

Since nobody can pull a product off the shelf that doesn't exist, we don't have to worry about real-time stock tracking.  If we did though there would be ways to handle this.  See example 2 below.

In this example the cash register would locally be running an ACID-compliant database engine and store the data through the day locally. At the end of the day it would export the transactions it did to the accounting system where the batch would be reviewed by a person before posting to the books.  Both the cash register and the accounting system would retain records of the transfer and synchronization, making an audit possible.  Because of the need for disconnected operation I am considering building such a cash register for LedgerSMB 1.5

Example 2:  Real-time inventory tracking for said cash register.

So business needs change, and now the disconnected cash register needs to report inventory changes on as real-time basis as possible to the main ERP system.  This is made easier if the cash register is running PostgreSQL. 

So we add a trigger to the table that stores the inventory movements which queues these for processing.  A trigger on the queue table issues a NOTIFY to another program which attempts to contact the ERP system.  It sends info on the inventory plus invoice number.  This is digitally signed with a key for the cash register.  The ERP stores this information and uses it for reporting and reconciliation at the end of the day, but treats it as a signed voucher checking out inventory.  At the end of the day these are reconciled and errors flagged.  If the message exchange fails, it tries again later.

Now, here you have three locally consistent systems: the POS, the ERP, and the messaging module.   Eventual consistency is a global property arising from it, and is preferable, business-wise, to absolute consistency because it gives an opportunity for human oversight.

These systems draw their inspiration from the paper accounting world.  They are partition-tolerant, available, and guaranteed, absent hardware destruction, to be eventually consistent.  Not only does this get around the problems flagged in the CAP theorem but they also provide opportunities for humans to be in control of the business.

Unlike BASE systems, these systems, built on ACID provide a basic framework where business process controls are consistently enforced.

Considerations

For such a system to work a few very specific requirements must be met (ACID allows us to meet them but we still have to design them into a system):
  • Messages must be durable on the receiving side.
  • Messages must be reproducible on the sending side.,
  • Each side must be absolutely consistent at all times.
  • Sender and receiver do not require knowledge of the operations handled by the other side, just of the syntax of the messages.  The sender must have knowledge that the message was received but need not have knowledge that it was durably stored (such knowledge can be helpful but it is not required.
  • Humans, not machines, must supervise the overall operation of the system.


Summary and thoughts on the CAP theorem

 In the off-line world, the CAP theorem not only enforces real limits in coordinated activities, but it also provides solutions.  Individuals are entrusted with autonomy, but controls are generally put in place to ensure both local consistency and the ability to guarantee eventual consistency down the road.  Rather than building on the BASE type approach, these build on the fundamental requirements of ACID compliance.   Paper forms are self-contained, and are atomic for practical reasons.  They maintain consistent state.  The actor's snapshots of information are inherently limited regarding what information they receive about the state on the other side, providing something akin to isolation.  And finally the whole point of a paper trail requires some degree of durability.  Thus the BASE approach resembles the global system but all components are in fact ACID-compliant.

This provides a different way to think of distributed computing, namely that functional partitions are sometimes desirable and can often be made useful, thus allowing one to move from the CAP theorem as a hard limit to viewing the CAP theorem as a useful reminder of what is inherently true, that loosely coupled systems create more robust global environments than tightly coupled ones, and that people rather than computers are often necessary for conflict resolution and business process enforcement.  The basic promise is that global consistency will be eventually maintained, and that the system will continue to offer basic services despite partitions that form.  This is the sort of thing the BASE/NoSQL proponents are keen on offering, but their solutions make it difficult to enforce business requirements because individual applications may see eventual consistency.  Here each application is absolutely consistent, but the entire environment is eventually consistent.  This matches off-line realities more closely.

Instead human systems, perhaps because they are not scalable in the CAP sense (having limited communications bandwidth), have developed very sophisticated systems of eventual consistency.  These systems require absolute consistency on the node (person) level and then coordination and reconciliation between systems.  In my accounting work the approach I have usually taken is to put the human in control but do whatever you can to simplify and streamline the human's workflow.  For example, when trying to reconcile a checking account it may be useful to get data from the bank.  This data is then matched to what's in the database using a best guess approach (in some cases we can match on check number but for wires and transfers we cannot and so we guess based on date and amount)  and the human is left to resolve the inevitable differences as well as review and approve. 

The nice thing about this model (and it differs from BASE considerably), is that you can expect absolute consistency at all times on every node, and availability of the global environment does not depend on every individual component functioning.  In the cash register example, the cash register can go down without the ERP going down and vice versa.  This means that you can achieve basic availability and eventual consistency without sacrificing ACID on a component level.  The key however is to be able to go back to the components and be able to generate a transaction history if needed, so if something failed to come through you can re-run it.

Because this is based on the ACID rather than the BASE model, I would therefore offer as a cute name:  Locally Available and Consistent Transaction and Integrity Control ACID as a name for this model of consistency.  This of course can be referred to by the cute shorthand of LACTIC ACID, or better "the LACTIC ACID model of eventual consistency."  It is a local knowledge model, rather than the more typical global knowledge model, and assumes that components, like people, have knowledge only of the things they need to know, that they are capable of functioning independently in disconnected mode, and that they are capable of being generating consistent, and accurate, pictures of the business later on demand.

This approach further relegates traditional tools like two-phase commit to the role of tools, which can be very helpful in some environments (particularly replication for high availability), but are not needed to ensure consistency of the environment when the systems are loosely coupled.  They may still be helpful in order to handle some sorts of errors, but they are one tool among many.

Finally although financial systems are not transportation vehicles, the automation paradox applies there as well.  If humans rely on too much automation, they are inclined to trust it, and therefore when problems arise are ill-prepared to deal with them.  In this approach, humans are integral parts of the operation of the distributed computing environment and therefore are never out of the loop.  This may sound less than desirable but in my experience every case of embezzlement I have heard of could have been prevented by more human eyes on the books and less, rather than more, immediate consistency.  There is nothing more consistent than having a single employee with full access to the money.  Separation of duties implies less consistency between human actors but this is why it is useful.

This approach is intended to be evolutionary rather than revolutionary.  Rather than try to create something new, it is an attempt to existing mature processes and apply them in new ways.

Friday, August 10, 2012

A Software Architect's View of the Design of Double Entry Paper Accounting Systems

Taking a brief break from the computer side for now, I figured it would be worth describing the basic design considerations of double entry.  This post is the result of my work studying history and anthropology far more than working on LedgerSMB but of course working on the software played a role too.  Most of what is presented here is my own original research.

Also I am sure that accounting students or those who have studied accounting in school will find some aspects of this challenging since I explore some areas that accountants are not taught about and because, as a history buff, I refuse to believe that certain designations are arbitrary.

I think that the apparent evolution of double entry accounting shows a number of potential approaches for dealing with the very difficult distributed transaction issues of today, not so much in what to do but where to look for answers.

The Tally Stick System and the Origins of Double Entry

In medieval Europe there were two basic economic constraints.  First most people could not read or write or perform arithmetic on paper, which limited the forms of exchange possible.  Secondly there was a perpetual shortage of currency which meant that official money was not really possible to use money as a simple medium of exchange.  These problems were resolved by the development of the split tally stick.  The split tally stick evolved through the Middle Ages into a very highly developed system and was still in active use in some places into the 19th century.  Ironically it was an effort to destroy these sticks held by the British Government in 1836 which burned down both houses of parliament.

At its fully fully developed form, a split tally stick consisted of a stick, usually of hazel, which had notches carved in it.  The stick would be split length wise and one side would be cut short.  The long side would be called the stock (trunk) and represented the debt.  It would be held by the creditor.  The short side, called the foil ('leaf') would follow the loan.

The creditor, at the allotted day, could show up and present his stock and demand payment.  If the creditor tried to pad the debt by adding more notches, this would be immediately apparent, and there was no way to erase notches on the other side.  Additionally it was immediately apparent if the sides were from different tally sticks.

As the Middle Ages progressed, literacy became somewhat more widespread.  From the days of the Merovingian king, Charles Martel,  through the "Carolingian Renaissance" in to the high Middle Ages, literacy began to become more open beginning with kings and eventually being available to the children of wealthy merchants.  With literacy came basic knowledge of arithmetic and eventually algebra, and the efforts to track tally sticks on paper may have given rise to double entry systems of bookkeeping.

The first step of course is a journal.  Here the tally sticks could be recorded.  The bigger stocks (like bigger numbers) would be reported on the left, while the smaller foils would be reported on the right.  One could then get a quick breakdown of one's position in terms of debt collectable vs debt owing.  Within a few hundred years, this develops into general ledgers and the full double entry accounting system that has not changed in its outlines since Luca Pacioli wrote about it in the 15th century.

However it is worth noting that split tally sticks are inherently double entry in the sense that every debit has a corresponding credit, and they are inherently accrual since income and formal payment could not be as well correlated as could invoice and income.  Moreover the rules for stock and foil appear to have tracked the rules for debit and credit today.

It is finally worth noting that even in Pacioli's day, and even a hundred years later, tally sticks were in common use in this way.  Therefore, it seems to me that Pacioli himself was probably familiar with these and so his use of Latin terms corresponds almost certainly to the tally stick approach.


Business as Owning Nothing for Itself

Many of the principles I have found while investigating the origins of double entry accounting are challenging to us today and force us to think about businesses differently.  Exploring the development of designs of older functional systems also better prepares us to think about design ourselves in areas of software where we are building complex functional systems today.

In a double entry accounting system, a business owns nothing for itself.  Everything it owns, it owes to its owners.  This is why the books always balance and why debits always equal credits.  Corporations are a legal fiction which post-date the development of double-entry accounting systems but even they don't own things for themselves.  Their equity is owned entirely by their owners.  A corporation owns things only on behalf of its shareholders.  Limited liability only means that the effective equity cannot drop below 0 and a corporation has no possessory interest distinct from those of its stockholders.

This understanding is also behind the fundamental accounting equation that assets - liabilities = equity.

As long as the books are guaranteed to balance, then it is possible to detect errors by balancing the books (using a trial balance, as Pacioli suggests).  This is only possible if the intrinsic net worth of the business is always 0 (when we talk about the net worth of the business, we are talking about the extrinsic net worth, namely the equity balance, see below, but this is actually the net balance of debt owed by the business to its owners--- while an individual may on balance be worth a million dollars, this means something very different than if a business is worth a million dollars because the business, unlike the individual, owes that money to someone).

While stock and foil tally sticks were originally used to track debt, Henry I of England required that they be used for receipts for taxes in the 11th century.  The process followed the pre-existing use, where the stock follows the person who has given money, while the foil was retained by the exchequer as a receipt of money retained.  The continued use of this sort suggests that the basic principles of double entry accounting were  already known to some extent.  If tax liability is a debt owed, then the payment is a debit and one receives a stock, while the foil follows the money given.

It is my belief that the basic principles of double entry accounting were explored first with tally sticks and later, as literacy became somewhat more widespread, on paper.

What Accounting Systems are Designed to Do

Accounting systems are designed to do one thing only, to track who owes what to whom.  Even tracking of current assets is a part of that since all assets of a business are effectively owed to the owners.

This may sound overly simplistic but the anthropological assessment on the origin of money is that money itself arises as a way to quantify debt.  Debt pre-exists money systems, and all economies are powered by debt, and this is particularly noticeable in gift economies where debt, in the form of honor, is the primary currency.    Everywhere debt precedes payment, and everywhere debt precedes money.  Therefore not only is accrual-basis accounting better in terms of reporting, but it better represents what actually is happening economically.

Debits and Credits

In the 15th Century, Luca Pacioli wrote his now-famous book on arithmetic, including a section on double-entry accounting.  Pacioli did not invent the system.  Instead his description is that of the Venetian system which was already in use at the time.  In general, the design is indicative of accrual-based accounting however in part because assets include debts owed to the business.  This actually is clearer when looking at the Latin terms Pacioli uses in his descriptions and how these fit together,  Namely he uses the terms "debit" and "credit" to refer to financial units which are clearer in Latin than they are in English and much of our accounting terminology derives from his work.

Pacioli's use of debit and credit are specifically of distinct concepts, and when every accounting student is taught these are arbitrary, every accounting student is taught wrong.

In Latin, debit refers to something which is owed and indeed it leads to our modern English word debt (via Old French).  The word derives from early Latin roots meaning "to take away something you have" and so it denotes a loss of a possessory interest in something.

A credit is the opposite side of a debit.  A debit is something owed.  A credit (from credere, to believe or trust, related to "creed" in Modern English) is something entrusted to someone else or loaned to them.  This term denotes a continued possessory interest in something, even as it is entrusted to someone else.  So we can most simply translate debit and credit as debt and investment or loan, respectively.  Being opposites, a credit abates a debit and vice versa.

Also being opposite sides of the transaction every transaction inherently balances.  One person's debit must by nature correspond to someone else's credit.  By entering everything from the perspective of the counterparty, whether customer, vendor, or owner, the books will be guaranteed to balance, and this allows one to detect errors because the total intrinsic value of the business will remain 0.

For this reason both debits and credits have two distinct meanings.  One is to off-set the other (paying back a loan is a debit against the corresponding credit), and the other is to represent debts (debits) and assets entrusted to the business but possessed by others (credits).  Every type of account furthermore has a specific type of counter-party but these fall into two categories:  owners (equity, and change-in-equity accounts, namely accounts for tracking income and expenses) and non-owners (assets and liabilities).

From this point we can derive the normal balance of every account because we understand the basic structure and functions of the system:

  • Asset accounts track debt and debt payments by those who owe money to the business.  Therefore they use debits for positive balances.  Being debits, they can be used to pay off loans made to the business (credits) or investors (investments are credits).  The perspective is that of the debtor.
  • Liability accounts track loans and loan payments to those the business owes money to.  Therefore they use credits for positive balances.  The perspective is that of the lender.
  • Equity accounts track investments in a business which reflect the value of the business to the owners.  Therefore they use credits for positive balances.  The perspective is that of the owner.
  • Income accounts track positive changes in equity.  Therefore they use credits for positive balances.  The perspective is that of the owner.
  • Expense accounts track negative changes in equity.   Therefore they use debits for positive balances.  The perspective is that of the owner.

What Software Engineers can Learn from Accounting Systems

Most of the time, those of us who design and write software find that our approaches are relatively orderly but fragile.  Aspects of a system fail to support each other, and we basically add complexity to the system in order to hold it together.  Highly engineered systems are thus brittle, or to the extent that they are not, require layer upon layer of complexity in order to keep working.  These systems often cannot continue to be maintained properly once people have forgotten why certain design decisions were made in the first place.

In contrast, double entry accounting systems (particularly accrual-based systems), which probably arose organically during the Late Middle Ages until they became important enough for Pacioli to write about, is a highly evolved system.  The overall approach, while difficult to grasp at first, can be maintained without any understanding of the reasons behind the design decisions.  Accounting systems have further evolved from Pacioli's day, presumably through the same process they evolved before then.  People understand, in general terms, principles required to get meaningful information out of the system, are confronted by new problems, and respond by experimenting and sharing results, until new approaches take root.

One thing we as engineers can do is look to some of these highly evolved systems, and how they change over time, and recognize that they show us what may be a better way to create highly robust systems that tolerate and detect errors well.  In double entry accounting, we look to owners 'interest in the business vs the business's interest in everyone else's assets and make sure they are equal.  This adds redundancy in entering information but it also adds richness in reporting that is not possible otherwise.  Perhaps there are opportunities for things like this elsewhere.

Here, with double entry accounting, the perspective chosen, namely the value of a business to itself, is an easy one to check.  Such a business will inherently have no value to itself.  Choice of perspective makes some difficult problems (like catching data entry errors when tracking money) relatively simple to catch, locate, and correct.   Sometimes the most elegant solutions are not in what you do but how you look at things.  This is very clear when it comes to this specific sort of system.

The real challenge going forward in my view is how we look at distributed systems and what perspectives we can find which make these problems elegantly solvable.  As with double entry accounting, this will probably have to arise from looking at pre-computing methods, for example petty cash management as a basis for a guarantee of eventual consistency in a distributed transaction.  Current approaches like those adopted by the NoSQL community don't get us there.  Approaches that draw from our experiences doing non-distributed transactions well as well as paper forms of distributed transactions may, however provide something a lot more robust and usable.

Monday, August 6, 2012

Still pushing for 1.4 beta 1 by end of month

We are still pushing for LedgerSMB 1.4, beta 1 by the end of the month.  We have a lot of work to do to get there but are closing the gap fast.  I will put out a longer post at that time with a list of all of the improvements we have made, but for now I want to highlight only a few.
First, we have ditched tablefunc and gone to WITH RECURSIVE common table expressions, which means we now will require PostgreSQL 8.4 and higher.  Readers will recall a previous post discussing this in greater detail as well as the benefits we have seen.

One area I am particularly excited by is the new reporting framework.  This allows one to quickly and easily turn a stored procedure into a report using a common set of interface components.  Reports support subtotals on various criteria,  ordering of results by users clicking columns, and more.  Most code for specific reports is boilerplate code.  Our main modules do most of the work.  Some reports like the income statement will have to override this and supply their own interface components, but this is relatively rare.

Reports can be exported to CSV, ODS, and PDF files and contain identifying information so that one does not get confused as to where a report came from.  A future post will look inside our reporting engine.

Additionally files can be attached to persons and companies, which means that this goes for customers, vendors, employees, leads, etc.  This brings full-fledged CRM a step closer.  The files are stored in the database as bytea types.

The final feature I want to mention here is that projects and departments are being replaced by a system of reporting dimensions.  These reporting dimensions can be nested, so projects can now contain other projects, and departments can contain other departments.  Funds etc can be added by configuring the application rather than by customization.

This feature makes heavy use of common table expressions.  We can now check for business reporting units and descendants within a report.  This allows us to do more flexible reporting than we would be able to do without many of the features found first in PostgreSQL 8.4.

Thursday, August 2, 2012

Patterns and Anti-Patterns in Code Comments

Code commenting is something which takes a lot of introspection and practice to get right.  As a heads-up when I am looking at hiring someone, I will be asking about commenting style and if you can't talk intelligently about the topic, you won't get hired.  I wouldn't want you to just agree with me.  I would expect you to demonstrate you have thought about the issue enough to have your own opinions and reasons for thinking the way you do.

We started LedgerSMB as a fork of SQL-Ledger and one of the immediate challenges we had was that the only comments in SQL-Ledger were brief descriptions of what files did, copyright notices, and strings for translation or other meta-information actively used by the software.  Debugging uncommented code is not fun, and it took us a long time to get an acceptable number of comments and/or POD in place.   Over time, working in a collaborative environment, I have come to have very strong opinions on code commenting.

In this post I will pull examples from PostgreSQL's source files (using 9.1.2 because I have the source handy for that) as well as from LedgerSMB.

Comments are extremely important as they represent an important collaborative tool, but like all powerful tools, it can be misused.

Commenting Pitfalls and Anti-patterns

In general, when debugging an application we have to read the code, not the comments.  Sparse, helpful comments are very helpful and will be read.  Copious comments that appear to describe the inner workings of the code get ignored by necessity.  Being ignored, they are not updated with the code, and consequently  become out of date.  Three or four people work on a code section and eventually the comments and the code don't match.  This is a real problem and it is one reason why over-commenting needs to be avoided.

A second problem with this approach is that people put faith in comments, rather than code, to explain what the program is doing to other developers.  This can lead to unclear code.  In general if you have to describe how it works, and you can't find a better way to write it, at least do everyone a favor by flagging the section of code as FIXME so that the comment and the code will be read together and the explanation deleted as clarity emerges.

A third problem is the anti-pattern of magic comments which I have seen crop up in more than one place.  Comments are designed to be ignored by the program as a way to annotate code.  When you start turning them into code, individuals may not be able to maintain the files properly and may delete a magic comment by accident.  Similarly using database-level COMMENT ON statements to add info for programs throws off documentation tools and the like.  Don't do it.  Keep code and comments separate.  If you really must use magic comments, at least do us a favor and note clearly that they are important and must not be deleted.

Finally if there are too many comments in code, the comments will not get read and so helpful comments will get missed.

Alternatives to Comments (Use Them!)

Comments are not the only way to make code readable, and often they are not even the best.  It's important also to pay attention to things like whitespace, use blank lines to separate blocks that logically belong together, and otherwise organize the code well.  Additionally enforcing design patterns which include things like declaring all variable for a function or code block at the earliest possible opportunity are helpful.

Similarly care in variable names goes a long way to rendering code readable.  In an SQL query for example, table aliases should be abbreviations of the table name, not arbitrary letters.

Finally to the extent you can structure your code you can make it easier to read.  Design patterns and coding patterns help organize code and make it easier to read.  To the extent that the code is easy to read, comments are less necessary.  Again, comments shouldn't ever be necessary to explain how code works, but sometimes they are.  In general it's good to keep these minimal in frequency and flagged with a note that one recognizes that this section of the code is unclear.

The above three boil down to the mentality of writing code with two distinct intended audiences:  the computer and other coders.

The Documentation Comment Pattern

Functions should be documented.  This helps programmers understand what a function is supposed to do but it has a number of more important functions as well.  By documenting a function call, you establish an API.  The code is expected to conform to the API, not the other way around.  These code comments are important but the code implements what is described, not the other way around.  It's hard to think of these as comments because they do not annotate the code per se, but rather describe the interfaces for interacting with it.

In most languages, we place the documentation at the front of the file.  In PostgreSQL with stored procedures, however, we have to put the comments after if we want to use COMMENT ON and therefore allow documentation tools to work properly.  We use POD for Perl, and COMMENT ON for SQL, and so these are distinguished from the other types of comments listed below in accordance with the literate programming pattern.

These comments may be extensive or not but should be seen as specifications rather than comments.


The Section Heading Comment Pattern

In a function or other code block of significant length, debugging requires being able to find a relevant section of code quickly.  Comments can help organize code  and make it easy to find a given section.

For example, in the PostgreSQL code, it is common to see a comment stating:

/* local functions */

This is very helpful.  If you know the header is there and you want a list of local functions that are declared for function re-use purposes, you find that definition quickly.  if you need to add a function name, it is easy to add it where it will be noticed not only by the compiler but also by fellow programmers.  The whole goal of these comments is just to help enforce organization of code and help the next programmer find the appropriate section of code quickly.

In general, my experience is that, like section headers in a blog post, these should be short, concise, and descriptive.


The Footnote Comment Pattern

A footnote comment is one which  annotates the code, by providing descriptions of why a given approach was taken, references to other works or algorithms used, or the like.  In general, my experience is that these comments should be short but provide information that cannot be deduced from the code.  For example one might  state why a given approach was taken or allude to an external document or standard for more information.

I prefer to inline my footnote comments because then it is clear exactly what the comment refers to, but other people may disagree.  For example the following comment from analyzejoins.c is a good footnote comment and not inlined (it explains the use of a goto statement):

/*
 * Restart the scan.  This is necessary to ensure we find all
 * removable joins independently of ordering of the join_info_list 
 * (note that removal of attr_needed bits may make a join appear
 * removable that did not before).      Also, since we just deleted the
 * current list cell, we'd have to have some kluge to continue the
 * list scan anyway.
 */

The comment clearly and concisely describes why a given statement is there.  It explains what it does to but only as necessary to get into why it is necessary.

The Warning Comment Pattern

Sometimes comments can and should be used to warn programmers about a certain segment of code.  For example in LedgerSMB we have the following comment near the top of the admin.sql which defines functions for user management.  Because user management functions are not parameterized we have to build them through string interpolation and run them with elevated privileges, which leads to dangers of in-stored-proc SQL injection as a database superuser.

-- README:  This module is unlike most others in that it requires most functions
-- to run as superuser.  For this reason it is CRITICAL that the following
-- practices are adhered to:
-- 1:  When using EXECUTE, all user-supplied information MUST be passed through
--     quote_literal.
-- 2:  This file MUST be frequently audited to ensure the above rule is followed
--
-- -CT

Similarly inside the admin__save_user function we include the comment:

-- WARNING TO PROGRAMMERS:  This function runs as the definer and runs 
-- utility statements via EXECUTE. 
-- PLEASE BE VERY CAREFUL ABOUT SQL-INJECTION INSIDE THIS FUNCTION.

Of course this is only required because the utility statements in question (like CREATE ROLE) are not parameterized and so parameterized queries are not an option.

The function of these comments is to call attention to pitfalls specific to certain blocks of code.  In this case we are warning about security implications.  In others we are warning about potential other problems. 

TODO, XXX, and FIXME comments are all very helpful and part of this pattern.  Typically I find it useful to include sections that need to be in caps in caps where a warning is critical, but otherwise leave it lower case.

Signed Comments

One thing we have found helpful in a collaborative environment is to sign comments.   This is not something we have seen people do elsewhere but we have found we get a lot of benefit from it.  In a typical woven code approach (below), the idea is that you get an autonomous discourse which is the code, documentation,and comments all together, but as things change, the discourse becomes less autonomous, as it should.  People may see a TODO or a FIXME and have different ideas as to what should be done.  Therefore signing a comment fills a number of rolls.

First it provides a clear record as to who one should talk to about a possible code change, and helps give people credit for their work.  However beyond this it sets up individuals as people who are publicly thinking about problems in the code and this provides some additional conversation points.  Finally a signed comment comes across as less authoritative than an unsigned one and so it allows for dialog in the comments between individuals.  So consider this:

# we need to add checks here for .... --CT

# As we move this to Moose, the problem may go away. -- YT

The above dialog of course is fictional but there are examples in the LedgerSMB code where you can see this sort of dialog develop.  It becomes helpful to a programmer because they can see what different programmers are thinking about an issue and what sorts of solutions may be available.  Also each programmer can speak for him/herself and doesn't have to amend the master, autonomous discourse in order to do so.

The Woven Code Pattern

Borrowed in part from Donald Knuth's concept of literate programming, this pattern involves writing the documentation and code as a single work intended to be read by other people.  Typically when I employ this I write my Perl POD first, then annotate the POD with code, and then add non-POD comments as needed to draw other programmers' attention to some things.   In this pattern four things are the case:
  1. Documentation is Authoritative and Done First
  2. Code Implements and Explains Documentation
  3. Footnote/Warning Comments Annotate Code, but only as needed.
  4. The above three components are woven together to create a text for the next programmer.  All components of that text evolve together.
Conclusions

Comments are essential to long-term maintainability and  collaboration in software development.  In general, I have found that they are more helpful when used to annotate, rather than describe, the source code.  The source code should be able to speak for itself but is necessarily an incomplete glimpse into the mind of the programmers involved.  Comments bridge this gap, but poorly used comments get in the way more than they help.

Any others?

What patterns do you like to push for when commenting in an environment where others will read your code and comments?

Saturday, July 28, 2012

One advantage of Logic in the DB: PHP classes for LedgerSMB coming soon!

The perpetual argument over logic in the db will continue forever, but here's one advantage that is often overlooked:  stored procedures provide a powerful way to integrate programs written in different environments.

Case in point:  Yesterday when I got tired of staring at Perl code, I wrote some basic PHP classes to provide integration logic for LedgerSMB.  The basic interface which performs query mapping functions, took me a couple of hours including debugging, despite the fact that I haven't programmed in PHP since 4.2 was current, and includes an additional class for retrieving/saving company records as well.  Not everything works yet but this will probably be finished up today.

These classes will make writing integration logic with portions of the software which have been moved over to stored procedures quite easy, and such code that calls stored procedures will be provably free of SQL injection and privilege escalation.

I haven't decided where to put this, whether in the main LedgerSMB project or in another Sourceforge project but it is coming soon.  Once these are working I intend to teach myself enough Java to write the same in that language,

Long-run there is a lot of boilerplate code that will probably be able to be generated by code generators in all these languages.  The code generators will eventually have to query system catalogs, and some custom application catalogs.  This will minimize the amount of code that will have to be written by hand.