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

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