Re: Error when upgrading 1.2 db to 1.3 db

[Erik Huelsmann <..hidden..>]

Hi Nigel,

>>>> Many thanks... I'll work through and find out which account is causing
>>>> the problem.
>>>>
>>> Hmm, this fails at the first fence, with offset 0. I think its a more
>>> fundamental error with the "account_save" procedure.
>>>
>> I'm still no further forward with this. Can someone put me in touch with
>> the author of the migration script? I can't really move forward with
>> testing 1.3 until we get an example database migrated across. And if we
>> are having problems with the script, it's likely that someone else will
>> have too.
>
> Could you send me a dump of the 'chart' table in your database? I'd
> like to see what's happening here and what expectations of
> 'account_save' in your table aren't met. We might want to add
> pre-migration checks based on it, or we might want to change
> account_save to compensate.

Looking at the code more closely, I think the migration code expects
every account to be associated with a header of which the number
alphanumerically precedes the account number.

Maybe that expectation is violated? If you replace the account_save
function in the distribution with the one you'll find below, an
exception will be raised if that expectation is violated.

HTH,


Erik.


CREATE OR REPLACE FUNCTION account_save
(in_id int, in_accno text, in_description text, in_category char(1),
in_gifi_accno text, in_heading int, in_contra bool, in_tax bool,
in_link text[])
RETURNS int AS $$
DECLARE
	t_heading_id int;
	t_link record;
	t_id int;
        t_tax bool;
BEGIN

    SELECT count(*) > 0 INTO t_tax FROM tax WHERE in_id = chart_id;
    t_tax := t_tax OR in_tax;
	-- check to ensure summary accounts are exclusive
        -- necessary for proper handling by legacy code
    FOR t_link IN SELECT description FROM account_link_description
    WHERE summary='t'
	LOOP
		IF t_link.description = ANY (in_link) and array_upper(in_link, 1) > 1 THEN
			RAISE EXCEPTION 'Invalid link settings:  Summary';
		END IF;
	END LOOP;
	-- heading settings
	IF in_heading IS NULL THEN
		SELECT id INTO t_heading_id FROM account_heading
		WHERE accno < in_accno order by accno desc limit 1;

                IF NOT FOUND THEN
                   RAISE EXCEPTION 'No header number preceeding %', in_accno
                END IF;
	ELSE
		t_heading_id := in_heading;
	END IF;

    -- don't remove custom links.
	DELETE FROM account_link
	WHERE account_id = in_id
              and description in ( select description
                                    from  account_link_description
                                    where custom = 'f');

	UPDATE account
	SET accno = in_accno,
		description = in_description,
		category = in_category,
		gifi_accno = in_gifi_accno,
		heading = t_heading_id,
		contra = in_contra,
                tax = t_tax
	WHERE id = in_id;

	IF FOUND THEN
		t_id := in_id;
	ELSE
		INSERT INTO account (accno, description, category, gifi_accno,
			heading, contra, tax)
		VALUES (in_accno, in_description, in_category, in_gifi_accno,
			t_heading_id, in_contra, in_tax);

		t_id := currval('account_id_seq');
	END IF;

	FOR t_link IN
		select in_link[generate_series] AS val
		FROM generate_series(array_lower(in_link, 1),
			array_upper(in_link, 1))
	LOOP
		INSERT INTO account_link (account_id, description)
		VALUES (t_id, t_link.val);
	END LOOP;

	
	RETURN t_id;
END;
$$ language plpgsql;