Proposal for GNUe Package Extensions
====================================

by 
Jason Cater


PREFACE
=======

First, a few assumptions... 

1. Being an open solution, GNUe should not be tied to a particular brand of 
   database. Many users will opt to use MySQL, while others will prefer 
   PostgreSQL.  Many corporate users will want to incorporate GNUe into their
   corporate commercial databases, such as Oracle.

2. End users should not need to recreate the entire database and/or reimport
   their data whenever new releases of GNUe (or a particular package) 
   come out.

3. The end user should have the option of installing any combination of 
   packages and templates.  If the user opts to use the Accounts Payable 
   system, but has no need for Accounts Receivable, then the Accounts 
   Receivable package should not be required for Accounts Payable to run. 

4. If the end user's needs change, or a new package comes available that was
   not available initially, the user should have the option of adding the 
   new package over an existing installation.

My proposal is to keep the database definitions separate from the creation 
logic.  Table definitions should be kept in a standard (XML) marked-up file 
and this file should be loaded into a database-specific "preprocessor(?)"
that interprets the standard definition file into database-specific 
create (or alter) statements.

For example, the following XML file might be used to define an Accounts 
Payable module...
----------------------------------------------------------------------------
<package name="AccountsPayable" version="0.0.1">

  <requirements>
    <requires package="GNUe-Base" version="0.0.1"/>
    <requires package="Accounting-Base" version="0.0.1">
      <table name="test">
        <column name="test_ap_field" type="number" size=10 precision=0>
      </table>
    </package>
  </requirements>

  <definitions>

    <!-- Payment Terms (e.g., Net 10,Net 15,etc) -->
    <table name="ap_payment_terms"> 
      <column name="payment_terms" type="text" size="3" primarykey/>
      <column name="days" type="number" size=3 precision=0/>
      <column name="description" type="text" size="20"/>
      <data>
        <row>
         <column name="payment_terms">000</column>
         <column name="days">0</column>
         <column name="description">Cash/Prepayment</column>
        </row>
        <row>
         <column name="payment_terms">010</column>
         <column name="days">10</column>
         <column name="description">Net 10 Days</column>
        </row>
        <row>
         <column name="payment_terms">010</column>
         <column name="days">15</column>
         <column name="description">Net 15 Days</column>
        </row>
        <row>
         <column name="payment_terms">030</column>
         <column name="days">30</column>
         <column name="description">Net 30 Days</column>
        </row>
      </data>
    </table>


  </definitions>
</package>
----------------------------------------------------------------------------

What this is saying: 
 
    <package name="AccountsPayable">...<package>

  We are defining a package named "AccountsPayable".


    <requirements>...</requirements>

  We are about to list the other packages that this package 
  depends upon.


    <requires package="GNUe-Base" version="0.0.1"/>

  Our package depends on at least GNUe-Base version 0.0.1 
  in order to run.


    <requires package="Accounting-Base" version="0.0.1">
      <table name="test">
        <column name="test_ap_field" type="number" size=10 precision=0>
      </table>
    </package>

  Our package depends on at least Accounting-Base version 0.0.1 
  in order to run.  Also, if the table "test" does not contain 
  a field name "test_ap_field" of at least (10,0) digits, either 
  add this field or make it large enough to accomodate us.


    <definitions>...</definitions>
  We are defining any tables (et al) that are specific to our package.


    <table name="ap_payment_terms"> 
      <column name="payment_terms" type="text" size="3" primarykey/>
      <column name="days" type="number" size=3 precision=0/>
      <column name="description" type="text" size="20"/>
      ...
    </table>

  We are defining a table called "ap_payment_terms" with three fields. 
  The first field is a primary key.


     <data><row><column>...</column></row></data>
  We are defining any rows to be inserted into this table.
  
******

Some considerations...

  We could define primitive data types (text, number, date, boolean??) 
  and specify the minimum size that our package needs.  A database 
  definition loader specific to a particular brand of database could 
  determine the appropriate data type to represent this field. (For 
  example, a number[5,0] might be represented as an integer in MySQL, 
  but as number(5,0) in Oracle.

  This model would also work for templates.  Since many templates will 
  need similar fields (fields that are not part of the base package), 
  the markup file would define the minimum requirements for itself. 
  If the field already existed in the table, then nothing would happen.
  If the field existed, but was not large enough, an "alter table modify" 
  would be executed.  If the field did not exist at all, then an 
  "alter table add" would be executed.