How to modify nx_update_read_acls permanently

Hi,

I had to modify the nx_update_read_acls in SQL Server. I posted a question two weeks ago: http://answers.nuxeo.com/questions/10914/nuxeo-6-nx_update_read_acls-never-ending-process-and-high-cpu-usage-nx-freezes

I found that the problem was the procedure itself. I modified the procedure code on his step 2 and now I can add new users and rights to folders very very fast. Before: 50-70-infinite minutes to add rights to a folder with a lot of files. Now: 30 seconds.

So, now the problem is that every time I restart Nuxeo Server, it overwrites this procedure and our problem returns. How do I change it permanently?

Thank you, Gabriel

0 votes

2 answers

1347 views

ANSWER



hello,

I guess there are 3 main possibilities:

  • set the noDDL flag to true in the repository configuration
  • patch nuxeo-core-storage-sql jar to apply your changes to sqlserver.sql.txt
  • tell to Nuxeo what you changed in this stored procedure to apply your fix to Nuxeo source code, and then the fix will be available in a hotfix

kind regards, Thierry

2 votes



gsc
Thank you Thierry

nuxeo-core-storage-sql patched.

I'll send a bug report to Nuxeo with the problem and modifications.

02/02/2015

Please don't forget to provide us with your changes otherwise we can't help.
02/03/2015


Our implementation of nx_update_read_acls. The changes are

  1. Use of a table instead a variable table. Thats why we want to use a nonclustered non unique index to speed up the join and search processes.
  2. New implementation of step 2. Now we a recursive procedure.

We use a new recursive procedure called nx_prepare_lists.

CREATE PROCEDURE [dbo].[nx_update_read_acls]
  -- Rebuild only necessary read acls
AS
BEGIN
  SET NOCOUNT ON;
  -- NOW USING A TABLE INSTEAD A VARIABLE TABLE. WE CAN USE NOW A NONCLUSTERED INDEX NON UNIQUE
  IF OBJECT_ID('IDSTEST', 'U') IS NOT NULL
              DROP TABLE IDSTEST
  CREATE TABLE IDSTEST (id NVARCHAR(36), acl_id CHAR(32));
  CREATE NONCLUSTERED INDEX IX_IDSTEST ON IDSTEST (id);
  CREATE NONCLUSTERED INDEX ID_ACL_ID ON IDSTEST (acl_id);
  --
  -- 0/ Clean aclr
  INSERT INTO IDSTEST SELECT NULL, r.acl_id FROM aclr r
    LEFT JOIN hierarchy_read_acl h ON r.acl_id=h.acl_id
    WHERE h.acl_id IS NULL ORDER BY 1;
  DELETE a FROM aclr a JOIN IDSTEST i ON a.acl_id = i.acl_id;
  DELETE FROM IDSTEST;
  --
  -- 1/ Get new doc to insert into hierarchy_read_acl
  DELETE FROM aclr_modified OUTPUT DELETED.hierarchy_id, NULL INTO IDSTEST WHERE spid = @@SPID AND is_new = 1;
  INSERT INTO hierarchy_read_acl SELECT i.id, dbo.nx_get_read_acl_id(i.id) FROM IDSTEST i
    JOIN hierarchy h ON i.id = h.id
    LEFT JOIN hierarchy_read_acl r ON r.id = i.id
    WHERE r.acl_id IS NULL;
  DELETE FROM IDSTEST;
  --
  -- 2/ Get the list of doc to update
  DELETE FROM aclr_modified OUTPUT DELETED.hierarchy_id, NULL INTO IDSTEST WHERE spid = @@SPID AND is_new = 0;
  -----------------------------------------------------------------------
  -- NEW IMPLEMENTATION ----------------------------------
  ----------------------------------------------------------------------
  DECLARE @id NVARCHAR(36)
  SELECT DISTINCT @id = id FROM IDSTEST WHERE id IS NOT NULL
    EXEC nx_prepare_lists @id
  ---------------------------------------------------------------------------
  ---------------------------------------------------------------------------
  -- 3/ Compute the read ACLs for updated documents
  UPDATE IDSTEST SET acl_id = dbo.nx_get_read_acl_id(id);
  UPDATE h SET acl_id = i.acl_id
    FROM IDSTEST i
    JOIN hierarchy_read_acl h ON i.id = h.id;
 END;


CREATE PROCEDURE [dbo].[nx_prepare_lists]
    @idPadre NVARCHAR(36)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @id NVARCHAR(36)
    DECLARE cursordb    CURSOR LOCAL STATIC FOR
        SELECT h.id FROM hierarchy h 
        WHERE h.parentid = @idPadre AND h.isproperty = 0;
    OPEN cursordb
    FETCH NEXT FROM cursordb INTO @id
    WHILE @@FETCH_STATUS = 0
    BEGIN
        INSERT INTO IDSTEST VALUES (@id, NULL)
        -- Recursive procedure
        EXEC nx_prepare_lists @id
        -- Next subfolder
        FETCH NEXT FROM cursordb INTO @id
    END
END
0 votes



Thanks. Wouldn't making IDSTEST a temporary table be better though?
02/06/2015