generalNote & alternativeIdentifier deletion
59 views
Skip to first unread message
Ad Axem
Dec 11, 2025, 9:29:56 AM12/11/25
to AtoM Users
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
Dec 11, 2025, 10:33:53 AM12/11/25
to AtoM Users
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"
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
Dec 11, 2025, 12:50:19 PM12/11/25
to AtoM Users
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.
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();
}
$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
Dec 11, 2025, 1:21:49 PM12/11/25
to ica-ato...@googlegroups.com
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.
Ad Axem
Dec 22, 2025, 6:26:34 AM12/22/25
to AtoM Users
Hi everyone,
Apologies for reposting, but would anyone happen to know of a similar method to delete all values for the archivistNotes field?
Daniel Lovegrove
Dec 22, 2025, 10:05:27 AM12/22/25
to AtoM Users
Hi Ad,
The names of note types can be found here: QubitTerm.php
Deleting all archivist notes is similar to deleting general notes. You can run the following to delete all archivist notes:
// Delete archivist notes
$criteria = new Criteria();
$criteria->add(QubitNote::TYPE_ID, QubitTerm::ARCHIVIST_NOTE_ID);
$criteria = new Criteria();
$criteria->add(QubitNote::TYPE_ID, QubitTerm::ARCHIVIST_NOTE_ID);
$notes = QubitNote::get($criteria);
foreach($notes as $note) {
$note->delete();
}
Best,
-Daniel
Efthimios Mavrikas
Dec 22, 2025, 10:06:40 AM12/22/25
to ica-ato...@googlegroups.com
Thank you so much Daniel!
--
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/fdf0d40a-4900-40a0-b090-c52f51c0a770n%40googlegroups.com.
Ad Axem
Dec 23, 2025, 6:58:43 AM12/23/25
to AtoM Users
How do I run the following code to delete all archivist notes?
// Delete archivist notes
$criteria = new Criteria();
$criteria->add(QubitNote::TYPE_ID, QubitTerm::ARCHIVIST_NOTE_ID);
$notes = QubitNote::get($criteria);
foreach($notes as $note) {
$note->delete();
}
$criteria = new Criteria();
$criteria->add(QubitNote::TYPE_ID, QubitTerm::ARCHIVIST_NOTE_ID);
$notes = QubitNote::get($criteria);
foreach($notes as $note) {
$note->delete();
}
Do I save it in a PHP file and then run php symfony tools:myphpfiletodeletearchivistnotes?
Many thanks again for your help!
Daniel Lovegrove
Dec 23, 2025, 9:40:05 AM12/23/25
to AtoM Users
Hi there,
To run that code, yes, you would put it inside a .php file. Make sure to put <?php on the first line of the file. Also, always make sure to back up your database before making any changes with custom PHP.
You can run ad-hoc logic in PHP files with the tools:run task. You can see the documentation here for steps to use the tools:run task:
Best,
-Daniel
Efthimios Mavrikas
Dec 23, 2025, 10:07:41 AM12/23/25
to ica-ato...@googlegroups.com
That’s very helpful, many thanks Daniel!
--
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/bfa83387-e785-4748-b10d-1679f95d984fn%40googlegroups.com.
Reply all
Reply to author
Forward
0 new messages