How to modify nx_update_read_acls permanently

[gsc_]

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

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

[Thierry_Martins · Accepted solution]

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

[gsc_]

Thank you Thierry

[Florent_Guillau]

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

[gsc_]

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

[Florent_Guillau]

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