TechNote

Queries required to clean up the LiveCycle Content Services ES database tables

This article describes the SQL queries that may be required in order to fully clear the LiveCycle Content Services ES tables from your database. These queries are required because the Content Services ES database contains tables with circular dependencies, which make it difficult to use the standard drop table queries. In order to fully clear the database tables, use the SQL queries or stored procedures below as appropriate for your database.

MySQL

Delete Dependencies

The following stored procedure takes the schema name (that is, database name) and a table name and deletes all dependencies of that table.

delimiter // CREATE PROCEDURE delDependencies(pSchema VARCHAR(64), pTable VARCHAR(64)) DETERMINISTIC MODIFIES SQL DATA SQL SECURITY INVOKER BEGIN DECLARE done BOOLEAN DEFAULT FALSE; DECLARE rSchema, rTable, rConstraint VARCHAR(64); DECLARE dependencies CURSOR FOR SELECT TABLE_SCHEMA, TABLE_NAME, CONSTRAINT_NAME FROM information_schema.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_SCHEMA = pSchema AND REFERENCED_TABLE_NAME = pTable; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = TRUE; OPEN dependencies; dependency_loop: LOOP FETCH dependencies INTO rSchema, rTable, rConstraint; IF `done` THEN LEAVE dependency_loop; END IF; SET @delDeps_query := CONCAT('ALTER TABLE ', rSchema, '.', rTable,' DROP FOREIGN KEY ', rConstraint); PREPARE delDeps_stmt FROM @delDeps_query; EXECUTE delDeps_stmt; DEALLOCATE PREPARE delDeps_stmt; END LOOP dependency_loop; CLOSE dependencies; END //

Drop Content Services Tables

The following stored procedure takes the schema name and drops all tables managed by Content Services ES. Call this stored procedure using the Content Services Schema name to delete all Content Services ES tables (for example, call deleteCSTables('adobe')).

delimiter // CREATE PROCEDURE deleteCSTables(pSchema VARCHAR(64)) DETERMINISTIC MODIFIES SQL DATA SQL SECURITY INVOKER BEGIN CALL delDependencies(pSchema, 'alf_access_control_entry'); CALL delDependencies(pSchema, 'alf_access_control_list'); CALL delDependencies(pSchema, 'alf_applied_patch'); CALL delDependencies(pSchema, 'alf_attributes'); CALL delDependencies(pSchema, 'alf_audit_config'); CALL delDependencies(pSchema, 'alf_audit_date'); CALL delDependencies(pSchema, 'alf_audit_fact'); CALL delDependencies(pSchema, 'alf_audit_source'); CALL delDependencies(pSchema, 'alf_auth_ext_keys'); CALL delDependencies(pSchema, 'alf_authority'); CALL delDependencies(pSchema, 'alf_child_assoc'); CALL delDependencies(pSchema, 'alf_global_attributes'); CALL delDependencies(pSchema, 'alf_list_attribute_entries'); CALL delDependencies(pSchema, 'alf_map_attribute_entries'); CALL delDependencies(pSchema, 'alf_node'); CALL delDependencies(pSchema, 'alf_node_aspects'); CALL delDependencies(pSchema, 'alf_node_assoc'); CALL delDependencies(pSchema, 'alf_node_properties'); CALL delDependencies(pSchema, 'alf_node_status'); CALL delDependencies(pSchema, 'alf_permission'); CALL delDependencies(pSchema, 'alf_server'); CALL delDependencies(pSchema, 'alf_store'); CALL delDependencies(pSchema, 'alf_transaction'); CALL delDependencies(pSchema, 'alf_version_count'); CALL delDependencies(pSchema, 'avm_aspects'); CALL delDependencies(pSchema, 'avm_aspects_new'); CALL delDependencies(pSchema, 'avm_child_entries'); CALL delDependencies(pSchema, 'avm_history_links'); CALL delDependencies(pSchema, 'avm_issuer_ids'); CALL delDependencies(pSchema, 'avm_merge_links'); CALL delDependencies(pSchema, 'avm_node_properties'); CALL delDependencies(pSchema, 'avm_node_properties_new'); CALL delDependencies(pSchema, 'avm_nodes'); CALL delDependencies(pSchema, 'avm_store_properties'); CALL delDependencies(pSchema, 'avm_stores'); CALL delDependencies(pSchema, 'avm_version_layered_node_entry'); CALL delDependencies(pSchema, 'avm_version_roots'); SET @dropTable_query := CONCAT('DROP TABLE ', pSchema, '.alf_access_control_entry'); PREPARE dropTable_stmt FROM @dropTable_query; EXECUTE dropTable_stmt; DEALLOCATE PREPARE dropTable_stmt; SET @dropTable_query := CONCAT('DROP TABLE ', pSchema, '.alf_access_control_list'); PREPARE dropTable_stmt FROM @dropTable_query; EXECUTE dropTable_stmt; DEALLOCATE PREPARE dropTable_stmt; SET @dropTable_query := CONCAT('DROP TABLE ', pSchema, '.alf_applied_patch'); PREPARE dropTable_stmt FROM @dropTable_query; EXECUTE dropTable_stmt; DEALLOCATE PREPARE dropTable_stmt; SET @dropTable_query := CONCAT('DROP TABLE ', pSchema, '.alf_attributes'); PREPARE dropTable_stmt FROM @dropTable_query; EXECUTE dropTable_stmt; DEALLOCATE PREPARE dropTable_stmt; SET @dropTable_query := CONCAT('DROP TABLE ', pSchema, '.alf_audit_config'); PREPARE dropTable_stmt FROM @dropTable_query; EXECUTE dropTable_stmt; DEALLOCATE PREPARE dropTable_stmt; SET @dropTable_query := CONCAT('DROP TABLE ', pSchema, '.alf_audit_date'); PREPARE dropTable_stmt FROM @dropTable_query; EXECUTE dropTable_stmt; DEALLOCATE PREPARE dropTable_stmt; SET @dropTable_query := CONCAT('DROP TABLE ', pSchema, '.alf_audit_fact'); PREPARE dropTable_stmt FROM @dropTable_query; EXECUTE dropTable_stmt; DEALLOCATE PREPARE dropTable_stmt; SET @dropTable_query := CONCAT('DROP TABLE ', pSchema, '.alf_audit_source'); PREPARE dropTable_stmt FROM @dropTable_query; EXECUTE dropTable_stmt; DEALLOCATE PREPARE dropTable_stmt; SET @dropTable_query := CONCAT('DROP TABLE ', pSchema, '.alf_auth_ext_keys'); PREPARE dropTable_stmt FROM @dropTable_query; EXECUTE dropTable_stmt; DEALLOCATE PREPARE dropTable_stmt; SET @dropTable_query := CONCAT('DROP TABLE ', pSchema, '.alf_authority'); PREPARE dropTable_stmt FROM @dropTable_query; EXECUTE dropTable_stmt; DEALLOCATE PREPARE dropTable_stmt; SET @dropTable_query := CONCAT('DROP TABLE ', pSchema, '.alf_child_assoc'); PREPARE dropTable_stmt FROM @dropTable_query; EXECUTE dropTable_stmt; DEALLOCATE PREPARE dropTable_stmt; SET @dropTable_query := CONCAT('DROP TABLE ', pSchema, '.alf_global_attributes'); PREPARE dropTable_stmt FROM @dropTable_query; EXECUTE dropTable_stmt; DEALLOCATE PREPARE dropTable_stmt; SET @dropTable_query := CONCAT('DROP TABLE ', pSchema, '.alf_list_attribute_entries'); PREPARE dropTable_stmt FROM @dropTable_query; EXECUTE dropTable_stmt; DEALLOCATE PREPARE dropTable_stmt; SET @dropTable_query := CONCAT('DROP TABLE ', pSchema, '.alf_map_attribute_entries'); PREPARE dropTable_stmt FROM @dropTable_query; EXECUTE dropTable_stmt; DEALLOCATE PREPARE dropTable_stmt; SET @dropTable_query := CONCAT('DROP TABLE ', pSchema, '.alf_node'); PREPARE dropTable_stmt FROM @dropTable_query; EXECUTE dropTable_stmt; DEALLOCATE PREPARE dropTable_stmt; SET @dropTable_query := CONCAT('DROP TABLE ', pSchema, '.alf_node_aspects'); PREPARE dropTable_stmt FROM @dropTable_query; EXECUTE dropTable_stmt; DEALLOCATE PREPARE dropTable_stmt; SET @dropTable_query := CONCAT('DROP TABLE ', pSchema, '.alf_node_assoc'); PREPARE dropTable_stmt FROM @dropTable_query; EXECUTE dropTable_stmt; DEALLOCATE PREPARE dropTable_stmt; SET @dropTable_query := CONCAT('DROP TABLE ', pSchema, '.alf_node_properties'); PREPARE dropTable_stmt FROM @dropTable_query; EXECUTE dropTable_stmt; DEALLOCATE PREPARE dropTable_stmt; SET @dropTable_query := CONCAT('DROP TABLE ', pSchema, '.alf_node_status'); PREPARE dropTable_stmt FROM @dropTable_query; EXECUTE dropTable_stmt; DEALLOCATE PREPARE dropTable_stmt; SET @dropTable_query := CONCAT('DROP TABLE ', pSchema, '.alf_permission'); PREPARE dropTable_stmt FROM @dropTable_query; EXECUTE dropTable_stmt; DEALLOCATE PREPARE dropTable_stmt; SET @dropTable_query := CONCAT('DROP TABLE ', pSchema, '.alf_server'); PREPARE dropTable_stmt FROM @dropTable_query; EXECUTE dropTable_stmt; DEALLOCATE PREPARE dropTable_stmt; SET @dropTable_query := CONCAT('DROP TABLE ', pSchema, '.alf_store'); PREPARE dropTable_stmt FROM @dropTable_query; EXECUTE dropTable_stmt; DEALLOCATE PREPARE dropTable_stmt; SET @dropTable_query := CONCAT('DROP TABLE ', pSchema, '.alf_transaction'); PREPARE dropTable_stmt FROM @dropTable_query; EXECUTE dropTable_stmt; DEALLOCATE PREPARE dropTable_stmt; SET @dropTable_query := CONCAT('DROP TABLE ', pSchema, '.alf_version_count'); PREPARE dropTable_stmt FROM @dropTable_query; EXECUTE dropTable_stmt; DEALLOCATE PREPARE dropTable_stmt; SET @dropTable_query := CONCAT('DROP TABLE ', pSchema, '.avm_aspects'); PREPARE dropTable_stmt FROM @dropTable_query; EXECUTE dropTable_stmt; DEALLOCATE PREPARE dropTable_stmt; SET @dropTable_query := CONCAT('DROP TABLE ', pSchema, '.avm_aspects_new'); PREPARE dropTable_stmt FROM @dropTable_query; EXECUTE dropTable_stmt; DEALLOCATE PREPARE dropTable_stmt; SET @dropTable_query := CONCAT('DROP TABLE ', pSchema, '.avm_child_entries'); PREPARE dropTable_stmt FROM @dropTable_query; EXECUTE dropTable_stmt; DEALLOCATE PREPARE dropTable_stmt; SET @dropTable_query := CONCAT('DROP TABLE ', pSchema, '.avm_history_links'); PREPARE dropTable_stmt FROM @dropTable_query; EXECUTE dropTable_stmt; DEALLOCATE PREPARE dropTable_stmt; SET @dropTable_query := CONCAT('DROP TABLE ', pSchema, '.avm_issuer_ids'); PREPARE dropTable_stmt FROM @dropTable_query; EXECUTE dropTable_stmt; DEALLOCATE PREPARE dropTable_stmt; SET @dropTable_query := CONCAT('DROP TABLE ', pSchema, '.avm_merge_links'); PREPARE dropTable_stmt FROM @dropTable_query; EXECUTE dropTable_stmt; DEALLOCATE PREPARE dropTable_stmt; SET @dropTable_query := CONCAT('DROP TABLE ', pSchema, '.avm_node_properties'); PREPARE dropTable_stmt FROM @dropTable_query; EXECUTE dropTable_stmt; DEALLOCATE PREPARE dropTable_stmt; SET @dropTable_query := CONCAT('DROP TABLE ', pSchema, '.avm_node_properties_new'); PREPARE dropTable_stmt FROM @dropTable_query; EXECUTE dropTable_stmt; DEALLOCATE PREPARE dropTable_stmt; SET @dropTable_query := CONCAT('DROP TABLE ', pSchema, '.avm_nodes'); PREPARE dropTable_stmt FROM @dropTable_query; EXECUTE dropTable_stmt; DEALLOCATE PREPARE dropTable_stmt; SET @dropTable_query := CONCAT('DROP TABLE ', pSchema, '.avm_store_properties'); PREPARE dropTable_stmt FROM @dropTable_query; EXECUTE dropTable_stmt; DEALLOCATE PREPARE dropTable_stmt; SET @dropTable_query := CONCAT('DROP TABLE ', pSchema, '.avm_stores'); PREPARE dropTable_stmt FROM @dropTable_query; EXECUTE dropTable_stmt; DEALLOCATE PREPARE dropTable_stmt; SET @dropTable_query := CONCAT('DROP TABLE ', pSchema, '.avm_version_layered_node_entry'); PREPARE dropTable_stmt FROM @dropTable_query; EXECUTE dropTable_stmt; DEALLOCATE PREPARE dropTable_stmt; SET @dropTable_query := CONCAT('DROP TABLE ', pSchema, '.avm_version_roots'); PREPARE dropTable_stmt FROM @dropTable_query; EXECUTE dropTable_stmt; DEALLOCATE PREPARE dropTable_stmt; END //

SQL Server

Delete Dependencies

The following stored procedure takes the schema name and a table name and deletes all dependencies of the specified table.

CREATE PROCEDURE DROPDEPENDENCIES @schemaName VARCHAR(50), @tableName VARCHAR(50) AS BEGIN DECLARE @schemaId INT SET @schemaId = (SELECT schema_id FROM sys.schemas WHERE (name=@schemaName)) DECLARE @objectId INT SET @objectId = (SELECT object_id FROM sys.tables WHERE schema_id = @schemaId and name=@tableName) DECLARE @fKeyName varchar(100) DECLARE curFkeys CURSOR FOR SELECT name FROM sys.foreign_keys WHERE parent_object_id = @objectId OPEN curFkeys FETCH NEXT FROM curFkeys INTO @fKeyName WHILE (@@FETCH_STATUS = 0) BEGIN exec('ALTER TABLE ' + @schemaName + '.' + @tableName + ' DROP CONSTRAINT ' + @fKeyName) PRINT 'Dropping ' + @tableName + ':' + @fKeyName FETCH NEXT FROM curFkeys INTO @fKeyName END CLOSE curFkeys DEALLOCATE curFkeys END GO

Drop Content Services Tables

CREATE PROCEDURE DROPCSTABLES @schemaName varchar(50) = 'adobe' AS BEGIN exec DROPDEPENDENCIES @schemaName, 'alf_access_control_entry' exec DROPDEPENDENCIES @schemaName, 'alf_access_control_list' exec DROPDEPENDENCIES @schemaName, 'alf_applied_patch' exec DROPDEPENDENCIES @schemaName, 'alf_attributes' exec DROPDEPENDENCIES @schemaName, 'alf_audit_config' exec DROPDEPENDENCIES @schemaName, 'alf_audit_date' exec DROPDEPENDENCIES @schemaName, 'alf_audit_fact' exec DROPDEPENDENCIES @schemaName, 'alf_audit_source' exec DROPDEPENDENCIES @schemaName, 'alf_auth_ext_keys' exec DROPDEPENDENCIES @schemaName, 'alf_authority' exec DROPDEPENDENCIES @schemaName, 'alf_child_assoc' exec DROPDEPENDENCIES @schemaName, 'alf_global_attributes' exec DROPDEPENDENCIES @schemaName, 'alf_list_attribute_entries' exec DROPDEPENDENCIES @schemaName, 'alf_map_attribute_entries' exec DROPDEPENDENCIES @schemaName, 'alf_node' exec DROPDEPENDENCIES @schemaName, 'alf_node_aspects' exec DROPDEPENDENCIES @schemaName, 'alf_node_assoc' exec DROPDEPENDENCIES @schemaName, 'alf_node_properties' exec DROPDEPENDENCIES @schemaName, 'alf_node_status' exec DROPDEPENDENCIES @schemaName, 'alf_permission' exec DROPDEPENDENCIES @schemaName, 'alf_server' exec DROPDEPENDENCIES @schemaName, 'alf_store' exec DROPDEPENDENCIES @schemaName, 'alf_transaction' exec DROPDEPENDENCIES @schemaName, 'alf_version_count' exec DROPDEPENDENCIES @schemaName, 'avm_aspects' exec DROPDEPENDENCIES @schemaName, 'avm_aspects_new' exec DROPDEPENDENCIES @schemaName, 'avm_child_entries' exec DROPDEPENDENCIES @schemaName, 'avm_history_links' exec DROPDEPENDENCIES @schemaName, 'avm_issuer_ids' exec DROPDEPENDENCIES @schemaName, 'avm_merge_links' exec DROPDEPENDENCIES @schemaName, 'avm_node_properties' exec DROPDEPENDENCIES @schemaName, 'avm_node_properties_new' exec DROPDEPENDENCIES @schemaName, 'avm_nodes' exec DROPDEPENDENCIES @schemaName, 'avm_store_properties' exec DROPDEPENDENCIES @schemaName, 'avm_stores' exec DROPDEPENDENCIES @schemaName, 'avm_version_layered_node_entry' exec DROPDEPENDENCIES @schemaName, 'avm_version_roots' exec('DROP TABLE ' + @schemaName + '.alf_access_control_entry') exec('DROP TABLE ' + @schemaName + '.alf_access_control_list') exec('DROP TABLE ' + @schemaName + '.alf_applied_patch') exec('DROP TABLE ' + @schemaName + '.alf_attributes') exec('DROP TABLE ' + @schemaName + '.alf_audit_config') exec('DROP TABLE ' + @schemaName + '.alf_audit_date') exec('DROP TABLE ' + @schemaName + '.alf_audit_fact') exec('DROP TABLE ' + @schemaName + '.alf_audit_source') exec('DROP TABLE ' + @schemaName + '.alf_auth_ext_keys') exec('DROP TABLE ' + @schemaName + '.alf_authority') exec('DROP TABLE ' + @schemaName + '.alf_child_assoc') exec('DROP TABLE ' + @schemaName + '.alf_global_attributes') exec('DROP TABLE ' + @schemaName + '.alf_list_attribute_entries') exec('DROP TABLE ' + @schemaName + '.alf_map_attribute_entries') exec('DROP TABLE ' + @schemaName + '.alf_node') exec('DROP TABLE ' + @schemaName + '.alf_node_aspects') exec('DROP TABLE ' + @schemaName + '.alf_node_assoc') exec('DROP TABLE ' + @schemaName + '.alf_node_properties') exec('DROP TABLE ' + @schemaName + '.alf_node_status') exec('DROP TABLE ' + @schemaName + '.alf_permission') exec('DROP TABLE ' + @schemaName + '.alf_server') exec('DROP TABLE ' + @schemaName + '.alf_store') exec('DROP TABLE ' + @schemaName + '.alf_transaction') exec('DROP TABLE ' + @schemaName + '.alf_version_count') exec('DROP TABLE ' + @schemaName + '.avm_aspects') exec('DROP TABLE ' + @schemaName + '.avm_aspects_new') exec('DROP TABLE ' + @schemaName + '.avm_child_entries') exec('DROP TABLE ' + @schemaName + '.avm_history_links') exec('DROP TABLE ' + @schemaName + '.avm_issuer_ids') exec('DROP TABLE ' + @schemaName + '.avm_merge_links') exec('DROP TABLE ' + @schemaName + '.avm_node_properties') exec('DROP TABLE ' + @schemaName + '.avm_node_properties_new') exec('DROP TABLE ' + @schemaName + '.avm_nodes') exec('DROP TABLE ' + @schemaName + '.avm_store_properties') exec('DROP TABLE ' + @schemaName + '.avm_stores') exec('DROP TABLE ' + @schemaName + '.avm_version_layered_node_entry') exec('DROP TABLE ' + @schemaName + '.avm_version_roots') END GO

DB2

The following query can be used to drop all tables used by Content Services ES.

drop table alf_access_control_entry; drop table alf_access_control_list; drop table alf_applied_patch; drop table alf_attributes; drop table alf_audit_config; drop table alf_audit_date; drop table alf_audit_fact; drop table alf_audit_source; drop table alf_auth_ext_keys; drop table alf_authority; drop table alf_child_assoc; drop table alf_content_url; drop table alf_global_attributes; drop table alf_list_attribute_entries; drop table alf_map_attribute_entries; drop table alf_node; drop table alf_node_aspects; drop table alf_node_assoc; drop table alf_node_properties; drop table alf_node_status; drop table alf_permission; drop table alf_server; drop table alf_store; drop table alf_transaction; drop table alf_version_count; drop table avm_aspects; drop table avm_aspects_new; drop table avm_child_entries; drop table avm_history_links; drop table avm_issuer_ids; drop table avm_merge_links; drop table avm_node_properties; drop table avm_node_properties_new; drop table avm_nodes; drop table avm_store_properties; drop table avm_stores; drop table avm_version_layered_node_entry; drop table avm_version_roots;

Oracle

The following query can be used to drop all tables used by Content Services ES.

drop table alf_access_control_entry cascade constraints; drop table alf_access_control_list cascade constraints; drop table alf_applied_patch cascade constraints; drop table alf_attributes cascade constraints; drop table alf_audit_config cascade constraints; drop table alf_audit_date cascade constraints; drop table alf_audit_fact cascade constraints; drop table alf_audit_source cascade constraints; drop table alf_auth_ext_keys cascade constraints; drop table alf_authority cascade constraints; drop table alf_child_assoc cascade constraints; drop table alf_content_url cascade constraints; drop table alf_global_attributes cascade constraints; drop table alf_list_attribute_entries cascade constraints; drop table alf_map_attribute_entries cascade constraints; drop table alf_node cascade constraints; drop table alf_node_aspects cascade constraints; drop table alf_node_assoc cascade constraints; drop table alf_node_properties cascade constraints; drop table alf_node_status cascade constraints; drop table alf_permission cascade constraints; drop table alf_server cascade constraints; drop table alf_store cascade constraints; drop table alf_transaction cascade constraints; drop table alf_version_count cascade constraints; drop table avm_aspects cascade constraints; drop table avm_aspects_new cascade constraints; drop table avm_child_entries cascade constraints; drop table avm_history_links cascade constraints; drop table avm_issuer_ids cascade constraints; drop table avm_merge_links cascade constraints; drop table avm_node_properties cascade constraints; drop table avm_node_properties_new cascade constraints; drop table avm_nodes cascade constraints; drop table avm_store_properties cascade constraints; drop table avm_stores cascade constraints; drop table avm_version_layered_node_entry cascade constraints; drop table avm_version_roots cascade constraints;