Where are clipboards saved to in back-end?

[Vicky Phillips]

Hi,

Can anyone tell me which directory I can find the saved clipboards in the back-end please?  A user has saved a clipboard but forgotten to write the clipboard ID down.  I'm hoping that by looking in the back-end I can use the rough idea of titles given to find the correct clipboard and be able to provide them with the clipboard ID.

Thanks,

Vicky

Digital Standards Manager

National Library of Wales

[Dan Gillean]

Hi Vicky, 

Clipboards are saved in the database, in the following two tables: 

mysql> describe clipboard_save;
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int          | NO   | PRI | NULL    | auto_increment |
| user_id    | int          | YES  | MUL | NULL    |                |
| password   | varchar(255) | YES  |     | NULL    |                |
| created_at | datetime     | YES  |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> describe clipboard_save_item;
+-----------------+--------------+------+-----+---------+----------------+
| Field           | Type         | Null | Key | Default | Extra          |
+-----------------+--------------+------+-----+---------+----------------+
| id              | int          | NO   | PRI | NULL    | auto_increment |
| save_id         | int          | YES  | MUL | NULL    |                |
| item_class_name | varchar(255) | YES  |     | NULL    |                |
| slug            | varchar(255) | YES  |     | NULL    |                |
+-----------------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

We have information on how to access the MySQL command prompt in our documentation here: 

• https://www.accesstomemory.org/docs/latest/admin-manual/maintenance/common-atom-queries/#accessing-the-mysql-command-prompt
  

One of our developers has shared the following SQL query that you can potentially use to find what you need: 

SELECT u.username, cs.password as clipboard_id, cs.created_at, csi.item_class_name, csi.slug
FROM clipboard_save cs
LEFT JOIN user u ON u.id=cs.user_id
JOIN clipboard_save_item csi ON csi.save_id=cs.id
ORDER BY u.username, cs.created_at;

This query will list 1 row per saved record, so a clipboard with 3 saved items will have 3 rows, all with the same value in the clipboard_id column. 

You can also bound it by time if you want - here's an example returning only clipboard entries created in May 2022: 

SELECT u.username, cs.password as clipboard_id, cs.created_at, csi.item_class_name, csi.slug
FROM clipboard_save cs
LEFT JOIN user u ON u.id=cs.user_id
JOIN clipboard_save_item csi ON csi.save_id=cs.id
WHERE cs.created_at BETWEEN '2022-05-01' AND '2022-05-31'
ORDER BY u.username, cs.created_at;

If the user was a public user, then the entry in the username column will be NULL. However, if the user was logged in, then you can further narrow the results by username - here's an example query showing saved clipboard records from the demo user in the month of May 2022: 

SELECT u.username, cs.password as clipboard_id, cs.created_at, csi.item_class_name, csi.slug
FROM clipboard_save cs
LEFT JOIN user u ON u.id=cs.user_id
JOIN clipboard_save_item csi ON csi.save_id=cs.id
WHERE u.username='demo' AND cs.created_at BETWEEN '2022-05-01' AND '2022-05-31'
ORDER BY u.username, cs.created_at;

Alternatively, return only those clipboard entries created by public users in the month of May 2022: 

SELECT u.username, cs.password as clipboard_id, cs.created_at, csi.item_class_name, csi.slug
FROM clipboard_save cs
LEFT JOIN user u ON u.id=cs.user_id
JOIN clipboard_save_item csi ON csi.save_id=cs.id
WHERE u.username is NULL AND cs.created_at BETWEEN '2022-05-01' AND '2022-05-31'
ORDER BY u.username, cs.created_at;

I will try to add these queries to our SQL documentation soon, so they can be reused by others as needed. 

Cheers, 

Dan Gillean, MAS, MLIS
AtoM Program Manager
Artefactual Systems, Inc.
604-527-2056

@accesstomemory

he / him

--
You received this message because you are subscribed to the Google Groups "AtoM Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ica-atom-user...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/ica-atom-users/796ffee5-e216-4764-b28d-6ffee5463a9cn%40googlegroups.com.