Re: SF.net SVN: ledger-smb:[3290] trunk

[John Locke <..hidden..>]

Hi, Erik,

See below...

On 06/19/2011 12:18 PM, Erik Huelsmann wrote:
> I'm not really used to the system where you extract relevant changes
> into the updates/ directory. Can someone walk me through how that
> would work out fro this commit?
>

Since I just had to do this to update our production system, I'll walk
through the steps I had to take...


> Thanks in advance!
>
> Bye,
>
> Erik.
>
> On Sun, Jun 19, 2011 at 8:42 PM,  <..hidden..> wrote:
>> Revision: 3290
>>          http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=3290&view=rev
>> Author:   ehuelsmann
>> Date:     2011-06-19 18:42:17 +0000 (Sun, 19 Jun 2011)
>>
>> Log Message:
>> -----------
>> First step at resolving 3321964: Add a 'tax' boolean to the 'account' table,
>> which serves to tell the application the account is tax related.
>>
>>
>>  * sql/Pg-database.sql
>>   (account [TABLE]): Add 'tax' bool-typed field.

ALTER TABLE account ADD tax (bool not null default 0); -- not sure if
that's the exact syntax...

>>   (chart [VIEW]): Add 'tax' field.

Alter did not work -- got error that you cannot change the number of
columns in a view.

So I tried "DROP VIEW chart", which did not allow a drop due to
dependent functions/views.

DROP VIEW chart RESTRICT

... reported all of the dependencies, which were all defined in
modules/Account.sql, modules/chart.sql, and modules/payment.sql.

DROP VIEW chart CASCADE

... then dropped the view with all the dependencies.

CREATE VIEW chart AS ... - insert the new definition

\i sql/modules/Account.sql
\i sql/modules/chart.sql
\i sql/modules/payment.sql

... later I got an access denied on relation chart, so I had to re-run
my generated roles.sql as well, which fixed it.
>>  * sql/modules/Account.sql
>>   (account_save [FUNCTION]): Add a 'in_tax' parameter,
>>     to be stored in the 'account.tax' field.
>>   (chart_i [RULE]): Give 'tax' field same treatment as 'contra'.

These got loaded by doing the above.
>>  * UI/accounts/edit.html: Add checkbox for 'tax' field, copied from 'contra'.

No database change necessary -- code update is sufficient.
>>  * LedgerSMB/DBObject/Account.pm
>>   (save): Set the value for the 'tax' field to 0 (false) if
>>     the form doesn't specify a value  -- same as 'contra'.

No database change.
>>  * scripts/account.pl
>>   (_display_account_screen): Set the value of the Tax checkbox, like 'contra'.

No database change.
>>  * sql/coa/*/chart/*.sql:
>>   Change account_save() invocations to include the 'tax' parameter (always
>>    false).  At the end, insert a query to set the 'tax' field to true
>>    for those accounts which have tax related links.

coa changes are not relevant for an update of existing data -- aside
from the query to set the tax field to true for appropriate accounts.


... so I am still thinking there are 3 parts to an update, and this is a
good sample to provide:

1. sql statements to alter the table schemas, report dependencies (with
drop xxx xxx restrict) and verify that we're not going to cascade delete
something we don't know about -- in that case we should not drop and
instead exit with an error.

2. perl script to parse the dependencies/make sure all are known, and
build list of sql files to reload. Then reload them.

3. post-update scripts to do any data changes necessary, such as
populate data in new columns with sane defaults.


For permissions-related updates, including any dropped/recreated items,
#2 should regenerate the roles sql file and #3 should execute it.

Cheers,
-- 
John Locke
http://freelock.com