topic Re: How to modify nx_update_read_acls permanently in Nuxeo Forum

How to modify nx_update_read_acls permanently

gsc_ — Mon, 02 Feb 2015 10:06:00 GMT

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

Re: How to modify nx_update_read_acls permanently

Thierry_Martins — Mon, 02 Feb 2015 10:10:32 GMT

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

Re: How to modify nx_update_read_acls permanently

gsc_ — Mon, 02 Feb 2015 14:02:24 GMT

Thank you Thierry

Re: How to modify nx_update_read_acls permanently

Florent_Guillau — Tue, 03 Feb 2015 11:08:20 GMT

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

Re: How to modify nx_update_read_acls permanently

gsc_ — Wed, 04 Feb 2015 16:53:45 GMT

Our implementation of nx_update_read_acls. The changes are

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.
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

Re: How to modify nx_update_read_acls permanently

Florent_Guillau — Fri, 06 Feb 2015 13:30:08 GMT

Thanks. Wouldn't making IDSTEST a temporary table be better though?