Thursday, May 31, 2012

Wonderful Uses of Unit Testing in PostgreSQL DB's

I am sure many readers will read the headline of this post and immediately think of pgTAP, which has become the de facto way to write unit tests in PostgreSQL db's.  The sad part is that we on the LedgerSMB team have not yet gotten around to porting our unit tests to pgTAP.  We have a slightly different approach which works in a similar way.  But this post isn't really about frameworks, but rather applications of the concept of unit testing.

Just to give folks some background, we started adding database-level unit tests to LedgerSMB back in 2007, at a time when pgTAP was still an alpha, and at the time we thought that we should wait for pgTAP to prove itself (pun intended) before relying on it as a testing infrastructure.  It has certainly matured since then.  Our approach instead is to write unit tests in SQL which write to a temporary table called test_result.  At the end of the test run, we select from test_result, provide a report of successes and failures, and roll back the transaction.  Of course if you encounter an exception part way through, your test cases will die and you won't have access to the test results until it is able to run through to completion. 

The test results are then parsed in Perl and the results fed back through Perl's test harness.  This approach has a few advantages and a few disadvantages compared to other fraeworks.  I like the fact that all the results are visible at the end.  It makes debugging easy when there is a failure because you can look at the full pattern of successes and failures and then scroll back to find any debugging information that the developer felt like outputting between test cases.

However what I really want to talk about is how these are or can be used in a mission critical environment.  I think most people would agree that software that tracks money is mission critical at least in terms of its data integrity.  If the data integrity suffers, everything in the business can fall apart quite quickly.  So ensuring data integrity for accounting software that may be customized by third parties is both a hard job and one that is absolutely necessary.

One key aspect of unit tests in this case is that they don't provide relevant lasting impacts on the database they are run against.  While there are some exceptions that are outside transactional control (sequences and the like) usually we don't care so much as to whether those are in the state they would have been for but for the test run.,  The ability to run unit tests on a live system without the possibility of screwing something up makes customization and rapid development possible in environments that it would not otherwise be.  (In actuality we have conventions to offer multiple layers of protection against data corruption.  For example serial values are typically assigned in the test case from a range of negative numbers, thus unlikely to conflict with existing real values.    However, this is nothing compared to the ability to simply roll back the transactions.)

Consequently you can use unit tests on a live system as a measure of ensuring that everything is ok.  A very useful corollary here is that you can use unit tests as a troubleshooting mechanism.  Something not working as expected?  Maybe the first thing that should be done is running the unit tests and see if anything fails.  You can thus add all sorts of checks that are useful for support and deployment purposes.

One key example here is the fact that behavior of LedgerSMB's query mapper is undefined where the target function is overloaded and no arguments defined.    Consequently it is very important to know which functions are overloaded that shouldn't be.   We actually have a test for overloaded functions, and we run this typically against misbehaving databases to see if there are issues there.

Occasionally I have recommended to customers that they wait to install add-ons until unit tests have been written.  This has never yet been the wrong call.

I am a firm believer in unit testing in the db.  They are just useful and not only in a QA environment.  These tests are just as useful as general diagnostic tools frameworks.  Therefore I think every database application should be using them.

Monday, May 21, 2012

LedgerSMB now in Debian!


As Robert James Clay announced today:

 The 1.3.15-2 package for LedgerSMB has been accepted into Debian

unstable.  I expect that version of the package to migrate to Debian

testing (wheezy) by the end of this month.

       LedgerSMB Debian source package page:

http://packages.qa.debian.org/l/ledgersmb.html

       Besides becoming available in the Debian repositories, information

about downloading the current package manually is also available at:

http://packages.debian.org/sid/all/ledgersmb/download

Saturday, May 12, 2012

Thoughts on VoltDB and performance vs flexibility

VoltDB is the latest project of Mike Stonebreaker (original founder of Postgres) aimed at very high performance OLTP.  It is fairly clearly a niche product however, and not really usable in the ERP space for reasons I will discuss below.

Stonebreaker's own presentations (one example, at 11:25) suggest he is aware of this issue given his triangle framework.

In the vertices of the triangle he places NoSQL, high performance OLTP (like VoltDB) and column-oriented databases, while in the second, as lower performance generalists, what he calls the legacy databases or elephants are in the middle.  The argument is that you can get a significant performance boost by specializing your systems beyond what you could when you have every system doing everything.  This is a fair argument to a point, but the implementation details show that it is true only in some areas.

Stonebreaker's analysis of processing time (see above presentation, at 14:36) in traditional databases places virtually all processing time in four areas, namely buffer management, locking, thread locks/semaphores, and recovery tasks, and he suggests that in order to get high degrees of performance one must eliminate these tasks.  This requires, however, rethinking how we address concurrency (the locking issues) and durability (recovery and disk-based storage).  The VoltDB approach is to get rid of concurrency entirely and see durability as a network, rather than a system, property.  Concurrency elimination accepted because the db is fast enough that queries can be run one at a time, and intraquery parallelism can be used instead of interquery parallelism.  However, this imposes significant limitations on the database because it means that every transaction is limited to a single query.  You can't do round-tripping in your transactions because this would impose locking requirements on the database.

This approach works very well for certain kinds of processing, such as consuming rapid data feeds and then feeding that information into a data warehouse at specified intervals.,  However one thing that is generally missing from the discussion is that the more complex the application, the more general the database solution needs to be.  One solution might be to separate data entry from reporting and use multiple tools, but who wants to pull their trial balance from a different system than they enter invoices on?  Since ERP systems usually tightly integrate decision support and OLTP, there isn't really room here to use specialized databases like VoltDB.

As the database market has expanded, it has created niches for products like VoltDB.  These niches may indeed grow with time.  However, I think it is incorrect to treat generalists as legacy, outdated approaches.

In essence VoltDB typifies what I call a specialism paradox, namely that to perform well in a specialist niche one must give up generalist solutions.  It is true that sometimes things improve and supplant older systems but at least as often they only do when the systems are not vastly more complex than the ones they are replacing.  For example, Stonebreaker's comparison of high performance OLTP to the shift from cable-operated digging excavation equipment to  hydrolics misses the fact that hydrolic systems are not that much more complex than cables and such.  There is some additional complexity, but it isn't a huge gap.  A better comparison might be comparing semi trucks to race cars and locomotives.  These are all highly engineered systems, but the basic design considerations are very different, and specialization comes at the cost of flexibility.  You will never see race cars hauling twenty-foot containers of furniture cross-country, and you will never see travelling along our nation's highways to reach out-of-the-way towns.  The semi however will never pull the loads that the locomotive can, and will never win the Indie 500......   The semi truck, however, can go fast enough, and pull the loads it needs to pull.....

Saturday, March 17, 2012

Why LedgerSMB is Headed for Greatness

Edited 2012-03-21 based on feedback from Ned Lilly.

Ok, so this post is going to be about the ERP market, and why LedgerSMB is headed for greatness.  We are a long-standing open source project and are not going anywhere soon.  There are significant strategic reasons to choose LedgerSMB over others.

We have made a lot of progress since we started, and that progress is expected to continue to accelerate.  Here's why....


PostgreSQL

First, we are great because of PostgreSQL.  There is no getting around that.  PostgreSQL is an awesome database management system for ERP's.  While there are some things it doesn't do yet as well as I would like, no other RDBMS does these either (see my recent post about this).

However, what PostgreSQL offers is an extremely powerful set of tools for addressing an ERP environment, and without per connection license costs.  The latter is especially important because it allows for additional utility programs to connect to the database and perform tasks without this requiring additional client access licenses and there is a lot that can be done with the flexibility that results.

Currently we take heavy advantage of PL/PGSQL and various built-in types and functions.   While there has been some talk of using PL/Perl and other stored procedure languages, we haven't yet found a case where this makes sense.

It's worth remembering that most of the work in an ERP system is in storing, retrieving, and displaying information.  The database is the ideal place to put all of the logic regarding storing and retrieving the information, as well as some of the display logic.  Consequently we are becoming more database-heavy and lighter-weight on the Perl side.

Less is More

In LedgerSMB we focus aggressively on overall architecture, with the idea of flexible, re-usable modules which get the logic right the first time around.  For 1.4, we are removing the project and department system and replacing it with a more general "business reporting unit" system.  This allows us to offer project accounting, department reports, manufacturing job costing, and more, without dedicated code to each task.

Differences are then handled by workflow scripts rather than by the model.  This ensure that the application can eventually be a simple yet powerful ERP development environment rather than an application which must be everything to everybody.

Each major release of LedgerSMB has had more features but a smaller code base than the previous version.

This approach is good for a number of reasons.  First it places emphasis on where the emphasis needs to be:  on LedgerSMB as a platform.  Secondly it encourages flexibility in implementation while focusing on getting the details regarding accounting right.  It also gives us fewer maintenance points and hence a better, higher quality codebase.  There are many more reasons why that is the case, but in general we try hard to do more with less.

LedgerSMB is a Multi-Vendor Project

 Most open source ERP's are single-vendor products that make money selling enterprise versions.  This means that the community version is basically a sales tool and many features will not be added for fear of hurting the vendor who sponsors the tool.

LedgerSMB began as a joint project between Metatron Technology Consulting and Command Prompt Inc, with a number of other consultants joining in early.  While the ecosystem is constantly in flux as new individuals enter the project and old ones leave for one reason or another, one theme remains the same:  you can get top-notch support from a variety of sources, and no company retains a monopoly on commit privileges.

This changes the dynamics of the project.  Instead of releasing a moderately functional tool open source and then charging for advanced features, we release most things open source and only where that model breaks down do we look at subscriptions.  This allows us to do things that other open source projects in the area have not yet been able to really try to do in a significant way, such as good working payroll systems with maintained rules for various tax jurisdictions (forthcoming in 1.4 at least for a couple of jurisdictions with more coming).  Instead of paying for fixed asset management, you can save that money and put it towards things that matter, like maintaining payroll logic.  The logic "sold" under subscription can even remain open source, since the major problem is just that of keeping the code up to compliance with the latest regulations.

LedgerSMB is a Development Platform

 LedgerSMB 1.3 and higher isn't intended to be just an ERP application, but rather a development platform for helping businesses implement ERP applications in accordance to their workflows.  The framework while minimalist is designed to facilitate rather than get in the way.

The framework is designed to get your teams of developers up and running as quickly as possible.  The object model is designed to be clean, and most of the actual work is shipped off the the database via stored procedures, or handed off to modules to parse templates, or handed off to various CPAN modules.  The actual Perl code is basically glue that holds all the rest together.

One advantage to this sort of platform is that reference implementations can be created in other platforms as well, and the basic framework can be set up such that it can be used for non-interactive batch jobs or thick clients, not just web applications.  In fact that has been a major guiding feature of our framework.

Secure by Default

LedgerSMB  has made security a top priority since the beginning of the project.  By default users cannot do anything in LedgerSMB, but beyond that even if they find and exploit holes in the web application, the security is still further enforced on the backend.

We are trying hard to provide industry-leading security, and such that can be enforced even when integration with other software is at issue.   We have plans to support Kerberos 5 authentication as soon as it is needed, as well.


Comparisons to xTuple

LedgerSMB and xTuple share a common approach to the database.  We are both heavily stored-procedure centric.   Both of us focus heavily on the database as an engineering discipline in itself.  This means that both of us focus heavily on interoperability with other applications and a good API.


There are however two important differences.  xTuple is a C++/QT application while LedgerSMB is written in Perl.  This means that LedgerSMB can deliver rapid application development possibilities in a way that xTuple cannot.  Rapid application around xTuple would require using other existing frameworks.  Rapid application around LedgerSMB can be done in within the LedgerSMB framework.   This first difference is critical because larger businesses fundamentally buy ERP frameworks as development platforms, while smaller businesses haven't yet found the value in doing this (something LedgerSMB hopes to change).

Secondly, xTuple is a single-vendor solution.  It is offered by a single company, which owns the codebase, and sells add-ons.  This means that the incentive for the company is to release as little as they can and remain viable while selling licenses to as much as they can.  Since LedgerSMB is multi-vendor, the incentive is to provide as much open source as possible and sell as few addons as possible.  One can see already that LedgerSMB comes with fixed asset management for which xTuple charges about $700.  As we continue to develop, those areas where xTuple is currently ahead (such as manufacturing) will probably disappear, and the areas where we offer features that xTuple sells for profit will likely increase.

The above paragraph has been stricken because, as Ned Lilly points out it is inaccurate.  In fact the fixed asset tool was developed by a third party, and this is one area they cultivate around their market.  He points out further that this is an example of rapid application development in Postbooks and therefore I am incorrect there.  He further asks me to suggest that their incentives are not to reduce a minimalistic product.  If their marketplace materials are not mostly made by them, then I would agree on all these points.  Points accepted.

I will however stand by the point that generally single-vendor solutions tend to have incentives to release minimalistic solutions to encourage folks to buy licenses.  Kudos to xTuple for not falling in that trap.

I will also have to look a lot more closely at how this Javascript automation is doable.  It may be possible in essence to do a fair bit more than I had considered here.


Comparisons to OpenERP

OpenERP is an open source, single-vendor ERP solution written in Python.  It features a pleasing interface, a modular structure, and so forth. However the same problem occurs relating to single vendor management.

OpenERP offers rapid application development possibilities both in Python using the framework provided or in it's "application builder."  However, what OpenERP as a business is really interested in selling you is a software license for the full version, which gives you exemptions from the requirements of the AGPL v3 as well as technical support.  This makes it harder, I think, to leverage a larger development community around the software.

Comparisons to OpenTAPS

 OpenTAPS is an ERP application and framework that is multi-vendor, built in Java, and supports multiple databases.  It is based on the Apache Open for Business ERP framework.

OpenTAPS is impressive, features-wise, and it is used by some major corporations, but the choice of Java as a language means that development is less rapid than it would be in Perl or Python, and therefore the framework capabilities are more within the reach of large businesses, which likely have internal development teams familiar with Java, than small ones.  This means that small businesses are still faced with the necessity of building business processes around the application rather than the other way around.  It is therefore more of an upper-market product, while LedgerSMB currently shoots primarily for the midrange.

Tuesday, March 13, 2012

3 Things I Wish PostgreSQL Did

There are a few times when I work with PostgreSQL when I wish it was able to do some things that neither it nor other relational databases are able to do at present.  In my view (and I will defend each of these feature requests), these all fit well within a relational db centric world.


3:  XML/JSON to tuple type

One of the problems that one runs into in a stored-procedure centric application is managing input and output of an application.   An ability to convert XML or JSON to a tuple type (remember that tuples can have members that are tuples, or arrays of tuples).  The formats are semantically equivalent, so why not allow for conversion?


2:  Nested Namespaces

Managing hundreds or thousands of stored procedures  can be a problem in a flat namespace.  We end up having to semantically create hierarchical function names or hierarchically named namespaces in a flat namespace world.  Even if one cannot do it for tables, there ought to be some way to do this for functions.

Part of the problem here is that the SQL standards give semantic value to different namespace lengths, so this would have to be solved.  It is not an easy problem to solve and it may not have a solution.  Perhaps a package delimiter within a namespace would be helpful?  Maybe a character like# or $?

1:  Rich Declarative Constraints for Accounting Applications

It seems strange to me that accounting applications have been one of the primary uses of RDBMS's since their inception, and yet there is no type of declarative constraint to handle ensuring that transactions are balanced.

Whether a transaction is balanced or not is fundamentally a set-based operation.  A transaction is balanced when the sum of the debits of the rows in each transaction is equal to the sum of hte credits of the rows in each transaction.  In LedgerSMB, debits are negative amounts, and credits are positive amounts (they are just presented to the user as fundamentally different).  So I would like the ability to do something like:

CHECK FOR EACH TRANSACTION (SUM(amount) = 0) GROUP BY trans_id;

Part of the problem I suppose is that getting this right in a row-locking environment is hard.  However, in an MVCC environment it should be possible to check if this constraint matches the deleted and inserted rows, and if it does (0 + 0 = 0), we know we are balanced (deletions would only happen for unapproved transactions, i.e. ones that have not yet hit the books).  Such a check would only fire once per transaction, and only check rows modified by the transaction.  It therefore shouldn't be problematic in the way an aggregate check would be over the entire table.

The other two are nice, but if PostgreSQL could do this very well, it would be the king of databases for accounting software, and I am sure they feature would find new uses elsewhere.

So, what's on your list?

Saturday, March 10, 2012

An Experience Developing custom LedgerSMB extension

There as a recent email to the development list from Erik Huelssman describing his experience extending LedgerSMB.  In my view this demonstrates better than anything else that we are getting right what an ERP framework needs to do.

The email is reposted here with permission.

Hi all,

For one of my businesses, I needed to create an application to store some data on the services and products we provide. Basically, there would be one central table with some references to a few other tables, mostly to provide (short) selection lists. Also, there's one bigger complexity, because we wanted to link to our customer data.

The customer data is all stored in LSMB, so, developing this database inside LSMB's database seemed like a logical choice.

Since my colleague and I were completely unfamiliar with the web development framework for LSMB, we first had a start with the tools we did know. (Internally developed stuff, nothing particularly fancy.) However, due to other priorities, things didn't quite roll forward as we wanted; it was simply too much work to get it done.


Last week we scrapped our own development. As a replacement I started looking at the tools that LSMB provides to get the same job done. Again, without prior knowledge how this should be done, I simply studied the budgeting module. I figured that if budgeting was an extension, we might consider our own administration as an extension and that it should probably work (roughly) the same way.

And it did: in 1,5 day I had our administration up and running, featuring a data entry screen and a search screen. The current version of the data entry screen searches and links to a customer only if it finds an exact match. [Chris and I are working on functionality every module can use to look up cross table references like these, so it'll be replaced by something better soon.]

The steps I had to take were really simple:

* Develop 2 main stored procedures:
   - <mymodule>__search()
   - <mymodule>__save()
  and 1 stored procedure for each drop down in the data-entry screen:
   - <mymodule>__list_<characteristic1>()
   - <mymodule>__list_<characteristic2>()
   - ... etc...

* Create a class in LedgerSMB/DBObject/<objectname>.pm
  which contains functions callable from perl and maps those
  to calls to the stored procedures in the database as simple as
  containing:

  sub search {
   my ($self) = @_;
   my $results = $self->execmethod({funcname=>'<mymodule>__search'});
   $self->merge($results);

   return $results;
  }

* Create two Template Toolkit template file for my module:
  - ./UI/services/entry.html
  - ./UI/services/search_criteria.html

* Create an LedgerSMB 'module' <mymodule>.pl, with three entry points:
  - edit
  - search
  - update

* Create a top level handler file by copying ./file.pl to ./<mymodule>.pl

* Add menu items


Basically, the out of the 1,5 days I've spent, most time was spent learning what I had to do. The actual time spent coding was negligeable.


In summary: adding to or changing LedgerSMB can be extremely easy. It was a refreshing surprise to me how easy. We'll definitely develop the rest of our application using LedgerSMB's own framework and when it's done, we might be able to donate it to the project.

Anyway, I hope the above inspires others to try it out themselves. If you have any questions, both Chris and me will be glad to help you out!


Bye,


Erik.

Friday, March 2, 2012

At the edges: Relational, Hierarchical, and Key-Value models in LSMB using PostgreSQL

Introduction

It is no secret that I think of NoSQL as a kind of cool set of technologies applicable to a fairly narrow niche market.  My view is that relational databases are still (and will be for the long term) the bread and butter of data management.  NoSQL provides a useful set of tools for cases which have very different data requirements.  There are edge cases where many NoSQL techniques make sense in a relational environment though.


Data in an ERP application can be conceptualized as sets of sections of tables, each of which has various write vs read load balances.  Some sections may be updated only rarely, others may prevent updates and deletes, but allow inserts and selects, and receive data constantly.  Data may migrate from one area to another over time, such as when the books are closed.

LedgerSMB tends to currently use a variety of models for different sections of the application.  Settings and menu attributes (what a menu item does) are modelled using key-value modelling because that best semantically fits the sort of data being stored.  The menu structure is modelled hierarchical, as (in 1.4) are projects, departments, and account classifications.  Hierarchical modelling is done currently through a simple self-join.

Virtually all of the rest of the data is modelled using simple relations with no self-joins.  I don't see that changing.  However, for the sections that either do self-joins or key-value modelling, I am always interested in seeing how these can be optimized, and so when I read a blog post on NoSQL data modelling, it occurred to me that as time goes on some of the models discussed there may eventually become more applicable to relational systems.  And so I will go over my thoughts as to how these may be helpful or not.

As PostgreSQL gets native JSON support, some parts may in fact be moved to that.

Key-Value and Schemaless Modelling

Key-value modelling is generally something to be avoided in relational design.   There are, however, times when key-value pairs are in fact what one is modelling, and hence this is a good way forward.  The best example in LedgerSMB is the settings table (called "defaults").  Here the data is semantically keys and values and hence there is no other clean way to model the data.

There are some other cases where data can be modelled well using key-value modelling, but where this is not quite so semantically clear cut.  Consider the LedgerSMB menu, for example:

CREATE TABLE menu_node (
    id serial NOT NULL,
    label character varying NOT NULL,
    parent integer,
    "position" integer NOT NULL
);

and

 CREATE TABLE menu_attribute (
    node_id integer NOT NULL references menu_node(id),
    attribute character varying NOT NULL,
    value character varying NOT NULL,
    id serial NOT NULL,
    primary key(node_id, attribute)
);

The second table effectively models the keys and values submitted back to the application when  link on the menu is clicked.  While this works ok with KVM, it is not really clean.  Ideally any common attributes would be moved to menu_node and free-form ones would be added to this table.

But here's a case where JSON would be cleaner, per node.  The data here is not getting updated frequently (unlike defaults).  Indeed it is typically written once unless a fix needs to be made.  The fixes would be cleaner if it was all in one field.  So here, a NoSQL model would work very well, albeit incorporated into a relational database.

Hierarchical Modelling

Since LedgerSMB 1.3, we have started to do hierarchical modelling in the database.  See the above menu_node table as an example.  1.4 adds hierarchical business reporting units (for projects and departments) as well.

Currently these are entirely handled through simple self-joins and no clustered indexes are added.  When the menu is generated in 1.3, we use connectby() in tablefunc (we support PostgreSQL 8.3 and above), but for trunk (will be 1.4) we have gone to WITH RECURSIVE which has shown a significant speed improvement (2-70 times depending on various factors).

Initially when I read the article, I thought maybe some of the ideas might be applicable to my work with LedgerSMB, but as I think about it I am not sure.  It may be that PostgreSQL's approach may perform well enough not to ever have to move to something else.  WITH RECURSIVE performs very well, and so the major options for improving performance (namely clustered indexes) may be sufficient.

Outside of clustering on indexes, I am hard-pressed to think of ways to optimize hierarchical lookups in ways that would significantly beat other forms of hierarchical storage.  Perhaps someone could explain what the benefits are to storing child node id's etc in the table.  I sure don't see it.


OLAP

In additional to standard relational operations on a well-normalized database, there are also cases where analytics need to be run relatively quickly and in semi-flexible manners, over increasingly large datasets, and they must perform well.

Unfortunately in an ERP application, there is rarely a hard distinction between analytic and transactional workloads.  Bank account reconciliation may enter data, but the workload depends on getting good data regarding reconciled transactions from the past, and that means aggregating potentially large datasets, and those datasets could increase in size over time.

The first forays into this area for LedgerSMB in fact were aimed to solve mixed transactional/reporting workflows.    Our current approach uses "checkpoints" which effectively close the books on all previous dates.   When one closes the books the checkpoints are created, and from that point forward, no transactions can be entered for previous dates.

This allows us eventually to tie a number of other information on to checkpoints. Various reporting views could be materialized on closing (asynchronously even), and thus provide better performance for all kinds of reports on these static data sets.

These areas have not really been fully explored in LedgerSMB yet.  However for some upcoming features (such as graphs in reports), I think it will be necessary to offer this sort of thing.  Referential integrity constraints can tie a set of reports to a specific checkpoint so that the reports cannot get out of sync when books are re-opened (which happens on rare occasion).