generalNote & alternativeIdentifier deletion

[Ad Axem]

Hi everyone,

I would like some help in putting together an SQL script or PHP script to be run using php symfony tools:run in order to delete all values of generalNote and alternativeIdentifier in an AtoM instance.

These values cannot be updated in any other way using the available CLI tools, so we are only left with the option to delete all existing values for all archival descriptions and then import the new values using the archival description CSV import.

Many thanks in advance for your help!

[Johan Pieterse]

Hi

Try this but backup your database or run on test database. No warranties...

cat > /usr/share/nginx/archive/lib/task/deleteNotesAndAltIdsTask.class.php << 'ENDOFFILE'
<?php

class deleteNotesAndAltIdsTask extends arBaseTask
{
    protected function configure()
    {
        $this->addOptions([
            new sfCommandOption('application', null, sfCommandOption::PARAMETER_OPTIONAL, 'The application name', 'qubit'),
            new sfCommandOption('env', null, sfCommandOption::PARAMETER_REQUIRED, 'The environment', 'cli'),
            new sfCommandOption('connection', null, sfCommandOption::PARAMETER_REQUIRED, 'The connection name', 'propel'),
            new sfCommandOption('dry-run', null, sfCommandOption::PARAMETER_NONE, 'Dry run - show what would be deleted'),
            new sfCommandOption('note-type', null, sfCommandOption::PARAMETER_OPTIONAL, 'Note type to delete (default: General note)', 'General note'),
        ]);

        $this->namespace = 'tools';
        $this->name = 'delete-notes-altids';
        $this->briefDescription = 'Delete all general notes and alternative identifiers';
        $this->detailedDescription = <<<EOF
Delete all general notes and alternative identifiers from all archival descriptions.
Use --dry-run to see what would be deleted without actually deleting.

  php symfony tools:delete-notes-altids
  php symfony tools:delete-notes-altids --dry-run
  php symfony tools:delete-notes-altids --note-type="Display note"
EOF;
    }

    protected function execute($arguments = [], $options = [])
    {
        parent::execute($arguments, $options);

        $dryRun = $options['dry-run'];
        $noteType = $options['note-type'];
        $conn = Propel::getConnection();

        // Find the note type_id by name
        $sql = "SELECT t.id FROM term t
                JOIN term_i18n ti ON t.id = ti.id
                WHERE ti.name = ? AND ti.culture = 'en'
                LIMIT 1";
        $stmt = $conn->prepare($sql);
        $stmt->execute([$noteType]);
        $noteTypeId = $stmt->fetchColumn();

        if ($noteTypeId) {
            $this->logSection('notes', "Found '$noteType' with type_id = $noteTypeId");

            // Count notes of this type
            $sql = "SELECT COUNT(*) FROM note WHERE type_id = ?";
            $stmt = $conn->prepare($sql);
            $stmt->execute([$noteTypeId]);
            $noteCount = $stmt->fetchColumn();

            $this->logSection('notes', "Found $noteCount notes of type '$noteType'");

            if (!$dryRun && $noteCount > 0) {
                // Delete note_i18n first
                $sql = "DELETE ni FROM note_i18n ni JOIN note n ON ni.id = n.id WHERE n.type_id = ?";
                $stmt = $conn->prepare($sql);
                $stmt->execute([$noteTypeId]);
                $this->logSection('notes', "Deleted note_i18n records");

                // Delete from object table (notes inherit from object)
                $sql = "DELETE o FROM object o JOIN note n ON o.id = n.id WHERE n.type_id = ?";
                $stmt = $conn->prepare($sql);
                $stmt->execute([$noteTypeId]);
                $this->logSection('notes', "Deleted object records");

                // Delete notes
                $sql = "DELETE FROM note WHERE type_id = ?";
                $stmt = $conn->prepare($sql);
                $stmt->execute([$noteTypeId]);

                $this->logSection('notes', "Deleted $noteCount notes of type '$noteType'");
            }
        } else {
            $this->logSection('notes', "Note type '$noteType' not found");
           
            // Show available note types
            $sql = "SELECT DISTINCT ti.name, n.type_id, COUNT(*) as cnt
                    FROM note n
                    JOIN term_i18n ti ON n.type_id = ti.id AND ti.culture = 'en'
                    GROUP BY ti.name, n.type_id
                    ORDER BY cnt DESC";
            $stmt = $conn->query($sql);
            $this->logSection('notes', 'Available note types:');
            while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
                $this->logSection('notes', "  - {$row['name']} (type_id={$row['type_id']}, count={$row['cnt']})");
            }
        }

        // Count alternative identifiers in other_name (taxonomy 44)
        $sql = "SELECT COUNT(*) FROM other_name o
                JOIN term t ON o.type_id = t.id
                WHERE t.taxonomy_id = 44";
        $stmt = $conn->query($sql);
        $altIdCount = $stmt->fetchColumn();

        $this->logSection('alt-ids', "Found $altIdCount alternative identifiers in other_name table");

        if (!$dryRun && $altIdCount > 0) {
            // Delete other_name_i18n first
            $sql = "DELETE oni FROM other_name_i18n oni
                    JOIN other_name o ON oni.id = o.id
                    JOIN term t ON o.type_id = t.id
                    WHERE t.taxonomy_id = 44";
            $conn->exec($sql);

            // Delete from object table
            $sql = "DELETE obj FROM object obj
                    JOIN other_name o ON obj.id = o.id
                    JOIN term t ON o.type_id = t.id
                    WHERE t.taxonomy_id = 44";
            $conn->exec($sql);

            // Delete other_name
            $sql = "DELETE o FROM other_name o
                    JOIN term t ON o.type_id = t.id
                    WHERE t.taxonomy_id = 44";
            $conn->exec($sql);

            $this->logSection('alt-ids', "Deleted $altIdCount alternative identifiers");
        }

        // Check property table for alternativeIdentifiers
        $sql = "SELECT COUNT(*) FROM property WHERE name = 'alternativeIdentifiers'";
        $stmt = $conn->query($sql);
        $propCount = $stmt->fetchColumn();

        if ($propCount > 0) {
            $this->logSection('properties', "Found $propCount alternativeIdentifiers properties");

            if (!$dryRun) {
                $sql = "DELETE pi FROM property_i18n pi
                        JOIN property p ON pi.id = p.id
                        WHERE p.name = 'alternativeIdentifiers'";
                $conn->exec($sql);

                $sql = "DELETE FROM property WHERE name = 'alternativeIdentifiers'";
                $conn->exec($sql);

                $this->logSection('properties', "Deleted $propCount properties");
            }
        } else {
            $this->logSection('properties', "No alternativeIdentifiers properties found");
        }

        if ($dryRun) {
            $this->logSection('info', 'DRY RUN - no changes made');
        } else {
            $this->logSection('info', 'Done! Run php symfony search:populate to update the search index.');
        }
    }
}
ENDOFFILE

# Dry run first to see what would be deleted
php symfony tools:delete-notes-altids --dry-run
# Actually delete
php symfony tools:delete-notes-altids
# Rebuild search index after
php symfony search:populate

php symfony tools:delete-notes-altids --note-type="Display note" --dry-run
php symfony tools:delete-notes-altids --note-type="Display note"

[Daniel Lovegrove]

Hi there,

I have a pull request open to the atom repository for issues exactly like this. This is a new feature I'm proposing that would allow you to update archival descriptions in-place with the ability to re-define any field, such as general notes or alternative identifiers. If you'd like to support this feature I'd appreciate hearing any feedback.

Issue #2208, PR #2256

In the meantime until that feature is added, in addition to Johan's method (he beat me to it!), here is an alternate method to select all general notes and alternative identifiers and delete them in AtoM:

// Delete general notes
$criteria = new Criteria();
$criteria->add(QubitNote::TYPE_ID, QubitTerm::GENERAL_NOTE_ID);
$notes = QubitNote::get($criteria);
foreach($notes as $note) {
    $note->delete();
}

// Delete alternative identifiers
$criteria = new Criteria();
$criteria->add(QubitProperty::SCOPE, 'alternativeIdentifiers');
$properties = QubitProperty::get($criteria);
foreach($properties as $property) {
    $property->delete();
}

Cheers!

-Daniel

[Efthimios Mavrikas]

Thank you Johan, thank you Daniel!

I’ll definitely support this feature, as it is an extremely common case in mass updates.

Would you happen to have a similar method for the archivistNotes field?

--
You received this message because you are subscribed to a topic in the Google Groups "AtoM Users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/ica-atom-users/SSiAE7LzaL4/unsubscribe.
To unsubscribe from this group and all its topics, send an email to ica-atom-user...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/ica-atom-users/e0b40690-c05b-4241-9f96-03e6fcef7a1an%40googlegroups.com.