Re: [idempiere] UUID and attachment.record_uu
Carlos Antonio Ruiz Gomez
It is expected that any error in migration scripts is reviewed and fixed
before continuing with next script.
Does the script 202305291332_IDEMPIERE-5567.sql error in such case? Or
it just fails silently?
> Regarding the second point, unless I'm mistaken, UUI Generator
doesn't touch table structures without the <tablename>_uu column,
> but it does generate UUID data. I ran it and didn't notice the
addition of UUID columns.
The code to create the UUID column is there, which parameters did you
use to run it?
Regards,
Carlos Ruiz
On 9/10/25 21:31, medovitch kaspy wrote:
> Hi Carlos,
>
> I would like to thank you for your attention and response.
>
> Indeed, I noticed that the migration did not work correctly, the
> application of the migration script 202305291332_IDEMPIERE-5567.sql
> could not be completed, because we did not respect the UUID
> requirements of idempiere. Some custom tables were created previously,
> without the <tablename>_uu column and which have attachments
> (ad_table_id, record_id) inserted in the ad_attachment table. Line 240
> of the script does indeed update ad_attachment.record_uu, but when
> trying to retrieve the UUID of the record there is a break and allow
> me to make a small remark the script does not appear to me, secure in
> the sense that it is not written on the defensive with exception
> management but by trusting the personalized tables, it is perhaps
> desirable to report the error and continue. In my case anyway I pay
> the price of our indiscipline, we must calmly go back, audit then
> review the migration operation.
>
> Regarding the second point, unless I'm mistaken, UUI Generator doesn't
> touch table structures without the <tablename>_uu column,
> but it does generate UUID data. I ran it and didn't notice the
> addition of UUID columns.
>
> Regarding storage, this was a rather technical question for those with
> sufficient experience in the field. For example, regarding response
> time, is there a difference between queries to extract information
> from the database and disk access?
>
> Regards.
>
> LezzoDev.
>
medovitch kaspy
Hi Carlos,
Sorry I didn't explain properly,
1.
yes the scripts run one after the other even if
there is an execution error in one of them. I was talking about the script
itself: 202305291332_IDEMPIERE-5567.sql line 240 which executes the update of
the record_uu. As soon as there is a table whose record_id exists in
ad_attachment, but the related table does not have the <tablename>_uu
column, then the anonymous PL/pgSQL block for updating the Record_UU from the
Record_ID does not continue to work for the other records in ad_attachment
because the block does not check the existence of the <tablename>_uu
column and we get the error:
--( ERROR: column "sys_comdedetailrest_uu" does not exist LINE
1: UPDATE AD_Attachment SET Record_UU=(SELECT SYS_Comdedetailre...
HINT: Perhaps you meant to reference the column "sys_comdedetailrest.sys_comdedetailrest_id".
QUERY: UPDATE AD_Attachment SET Record_UU=(SELECT
SYS_Comdedetailrest_UU
FROM SYS_Comdedetailrest WHERE SYS_Comdedetailrest_ID=AD_Attachment.Record_ID)
WHERE AD_Table_ID=1000177 AND Record_ID IS NOT NULL AND Record_UU IS NULL
CONTEXT: PL/pgSQL function inline_code_block line 16 at EXECUTE )—
Here SYS_Comdedetailrest.SYS_Comdedetailrest_UU
Does not exist
-- do this maybe
FOR r IN
SELECT DISTINCT t.TableName,
at.AD_Table_ID, COUNT(at.AD_Attachment_ID) as nb_records
FROM AD_Attachment at
JOIN AD_Table t ON
(at.AD_Table_ID=t.AD_Table_ID AND EXISTS (SELECT 1 FROM AD_Column WHERE
ColumnName=t.TableName||'_ID'))
WHERE at.Record_UU IS NULL AND
at.Record_ID IS NOT NULL
GROUP BY t.TableName,
at.AD_Table_ID
ORDER BY t.TableName
LOOP
-- defensive
method may be more secure if I may say so
v_table_name := r.TableName;
v_column_uu := v_table_name || '_UU';
Check if the UU column exists
IF EXISTS (
SELECT 1 FROM
information_schema.columns
WHERE table_schema = 'adempiere'
AND LOWER(table_name) =
LOWER(v_table_name)
AND LOWER(column_name) =
LOWER(v_column_uu)
) THEN
….
…. Continue
work …
ELSE
-- UU Column does not exist
RAISE NOTICE ' %: Column % does not exist ….)', r.TableName,
v_column_uu, r.nb_records;
2. UUID generator asks for the table name, so either you give the table name or part of the table names (for example, sys_%, (I don't know if it requires case sensitivity), or you leave it blank and it reviews all the tables. For example, I tested the table reported in the error: sys_comdedetailrest, which doesn't have the sys_comdedetailrest_uu column. Did I miss something?
Thank you for reading and for your advice and explanations.
--
You received this message because you are subscribed to the Google Groups "iDempiere" group.
To unsubscribe from this group and stop receiving emails from it, send an email to idempiere+...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/idempiere/cf2e608d-5c96-4174-a6eb-92564febea93%40gmail.com.
Carlos Antonio Ruiz Gomez
Hi Carlos,
Sorry I didn't explain properly,