nur auf Microsoft SQL 2005 getestet.

getorphanedfiles.sql

--liste die Anzahl aller aus dem Papierkorb gelöschten, aber noch vorhandenen Dateien auf. / rb / 2023
USE Alfresco;
 
SELECT COUNT(*) AS "Dateien"
--Select *
FROM alf_content_url
WHERE orphan_time IS NOT NULL;

getTrashcanFiles.sql

--liste alle im Papierkorb vorhandenen Dateien auf. / rb / 2023
-- Anzeige ist standardmäßig auf 1000 begenzt.
USE Alfresco;
 
SELECT *
FROM alf_node
WHERE store_id=6
AND type_qname_id=51;

create database alfresco231 default character set utf8 collate utf8_bin;
create user alfresco@localhost identified by 'geheim';
grant all on alfresco231.* to 'alfresco'@'localhost';

missedtables.sql

### fehlende Tabellen IN mysql einmalig zufuegen
 
CREATE TABLE ACT_HI_ACTINST (
    id_ VARCHAR(64) NOT NULL,
    proc_def_id_ VARCHAR(64) NOT NULL,
    proc_inst_id_ VARCHAR(64) NOT NULL,
    execution_id_ VARCHAR(64) NOT NULL,
    act_id_ VARCHAR(255) NOT NULL,
    task_id_ VARCHAR(64),
    call_proc_inst_id_ VARCHAR(64),
    act_name_ VARCHAR(255),
    act_type_ VARCHAR(255) NOT NULL,
    assignee_ VARCHAR(255),
    start_time_ TIMESTAMP,
    end_time_ TIMESTAMP,
    duration_ BIGINT,
    tenant_id_ VARCHAR(255)
);
 
 
GRANT ALL ON alfresco.ACT_HI_ACTINST  TO alfresco@localhost;
 
ALTER TABLE ACT_HI_ACTINST
    ADD CONSTRAINT act_hi_actinst_pkey PRIMARY KEY (id_);
CREATE INDEX act_idx_hi_act_inst_end ON ACT_HI_ACTINST(end_time_);
CREATE INDEX act_idx_hi_act_inst_exec ON ACT_HI_ACTINST(execution_id_, act_id_);
CREATE INDEX act_idx_hi_act_inst_procinst ON ACT_HI_ACTINST(proc_inst_id_, act_id_);
CREATE INDEX act_idx_hi_act_inst_start ON ACT_HI_ACTINST(start_time_);
 
 
CREATE TABLE ACT_HI_DETAIL (
    id_ VARCHAR(64) NOT NULL,
    type_ VARCHAR(255) NOT NULL,
    proc_inst_id_ VARCHAR(64),
    execution_id_ VARCHAR(64),
    task_id_ VARCHAR(64),
    act_inst_id_ VARCHAR(64),
    name_ VARCHAR(255) NOT NULL,
    var_type_ VARCHAR(64),
    rev_ INT,
    time_ TIMESTAMP NOT NULL,
    bytearray_id_ VARCHAR(64),
    double_ DOUBLE PRECISION,
    long_ BIGINT,
    text_ VARCHAR(4000),
    text2_ VARCHAR(4000)
);
 
GRANT ALL ON alfresco.ACT_HI_DETAIL TO alfresco@localhost;
 
ALTER TABLE ACT_HI_DETAIL
    ADD CONSTRAINT act_hi_detail_pkey PRIMARY KEY (id_);
 
CREATE INDEX act_idx_hi_detail_act_inst ON ACT_HI_DETAIL(act_inst_id_);
CREATE INDEX act_idx_hi_detail_name ON ACT_HI_DETAIL(name_);
CREATE INDEX act_idx_hi_detail_proc_inst ON ACT_HI_DETAIL(proc_inst_id_);
CREATE INDEX act_idx_hi_detail_task_id ON ACT_HI_DETAIL(task_id_);
CREATE INDEX act_idx_hi_detail_time ON ACT_HI_DETAIL(time_);
 
CREATE TABLE ACT_HI_IDENTITYLINK (
    id_ VARCHAR(64) NOT NULL,
    group_id_ VARCHAR(255),
    type_ VARCHAR(255),
    user_id_ VARCHAR(255),
    task_id_ VARCHAR(64),
    proc_inst_id_ VARCHAR(64)
);
 
GRANT ALL ON alfresco.ACT_HI_IDENTITYLINK TO alfresco@localhost;
 
ALTER TABLE ACT_HI_IDENTITYLINK
    ADD CONSTRAINT act_hi_identitylink_pkey PRIMARY KEY (id_);
 
CREATE INDEX act_idx_hi_ident_lnk_procinst ON ACT_HI_IDENTITYLINK (proc_inst_id_);
CREATE INDEX act_idx_hi_ident_lnk_task ON ACT_HI_IDENTITYLINK (task_id_);
CREATE INDEX act_idx_hi_ident_lnk_user ON ACT_HI_IDENTITYLINK (user_id_);
 
CREATE TABLE ACT_HI_PROCINST (
    id_ VARCHAR(64) NOT NULL,
    proc_inst_id_ VARCHAR(64) NOT NULL,
    business_key_ VARCHAR(255),
    proc_def_id_ VARCHAR(64) NOT NULL,
    start_time_ TIMESTAMP,
    end_time_ TIMESTAMP,
    duration_ BIGINT,
    start_user_id_ VARCHAR(255),
    start_act_id_ VARCHAR(255),
    end_act_id_ VARCHAR(255),
    super_process_instance_id_ VARCHAR(64),
    delete_reason_ VARCHAR(4000),
    tenant_id_ VARCHAR(255),
    name_ VARCHAR(255)
);
 
GRANT ALL ON  ACT_HI_PROCINST  TO alfresco@localhost;
 
ALTER TABLE ACT_HI_PROCINST
    ADD CONSTRAINT act_hi_procinst_pkey PRIMARY KEY (id_);
 
ALTER TABLE ACT_HI_PROCINST
    ADD CONSTRAINT act_hi_procinst_proc_inst_id__key UNIQUE (proc_inst_id_);
 
CREATE INDEX act_idx_hi_pro_i_buskey ON ACT_HI_PROCINST(business_key_);
CREATE INDEX act_idx_hi_pro_inst_end ON ACT_HI_PROCINST(end_time_);
 
 
CREATE TABLE ACT_HI_VARINST (
    id_ VARCHAR(64) NOT NULL,
    proc_inst_id_ VARCHAR(64),
    execution_id_ VARCHAR(64),
    task_id_ VARCHAR(64),
    name_ VARCHAR(255) NOT NULL,
    var_type_ VARCHAR(100),
    rev_ INT,
    bytearray_id_ VARCHAR(64),
    double_ DOUBLE PRECISION,
    long_ BIGINT,
    text_ VARCHAR(4000),
    text2_ VARCHAR(4000),
    create_time_ TIMESTAMP,
    last_updated_time_ TIMESTAMP
);
 
 
GRANT ALL ON alfresco.ACT_HI_VARINST TO alfresco@localhost;
ALTER TABLE  ACT_HI_VARINST
    ADD CONSTRAINT act_hi_varinst_pkey PRIMARY KEY (id_);
 
CREATE INDEX act_idx_hi_procvar_name_type ON act_hi_varinst(name_, var_type_);
CREATE INDEX act_idx_hi_procvar_proc_inst ON act_hi_varinst(proc_inst_id_);
CREATE INDEX act_idx_hi_procvar_task_id ON act_hi_varinst(task_id_);
 
CREATE TABLE ACT_ID_GROUP (
    id_ VARCHAR(64) NOT NULL,
    rev_ INT,
    name_ VARCHAR(255),
    type_ VARCHAR(255)
);
GRANT ALL ON  alfresco.ACT_ID_GROUP TO alfresco@localhost;
 
ALTER TABLE ACT_ID_GROUP
    ADD CONSTRAINT ACT_ID_GROUP_pkey PRIMARY KEY (id_);
 
CREATE TABLE ACT_ID_MEMBERSHIP (
    user_id_ VARCHAR(64) NOT NULL,
    group_id_ VARCHAR(64) NOT NULL
);
 
GRANT ALL ON  ACT_ID_MEMBERSHIP TO alfresco@localhost;
 
ALTER TABLE ACT_ID_MEMBERSHIP
    ADD CONSTRAINT act_id_membership_pkey PRIMARY KEY (user_id_, group_id_);
CREATE INDEX act_idx_memb_group ON ACT_ID_MEMBERSHIP (group_id_);
CREATE INDEX act_idx_memb_user ON ACT_ID_MEMBERSHIP (user_id_);
ALTER TABLE ACT_ID_MEMBERSHIP
    ADD CONSTRAINT act_fk_memb_group FOREIGN KEY (group_id_) REFERENCES act_id_group(id_);
ALTER TABLE ACT_ID_MEMBERSHIP
    ADD CONSTRAINT act_fk_memb_user FOREIGN KEY (user_id_) REFERENCES act_id_user(id_);