omeka snippets

omeka.sql

/* grab all files of item */
SELECT `id`  FROM `omeka`.`files` WHERE `item_id` LIKE '4342';

/* count the files in an item */
SELECT COUNT(*) FROM `omeka`.`files` WHERE `item_id` LIKE '4342';

/* get the record_id of a file by id */
SELECT `record_id` FROM `omeka`.`element_texts` WHERE `id` = 126560;

/* create tags automatically from dc metadata fields. In this case ‘Audience’ (id 124) */
INSERT INTO tags (name) SELECT DISTINCT 'text'  FROM `omeka`.`element_texts` WHERE element_id = 124;

/* export all transcriptions in a file range */
SELECT `text` FROM `element_texts` WHERE `record_id` >= 128830 AND `record_id` <= 128935 AND `element_id` = 136 ORDER BY `element_texts`.`record_id` ASC;

/* set all items in a collection to private, change db name and collection_id. change to 1 for public */
UPDATE `omeka`.`items` SET `public` = '0' WHERE `collection_id` = 18;

/* find pages transcribed between a date range */
SELECT DISTINCT file_name FROM `Scriptus_changes` WHERE time_changed > '2015-04-01 00:00:00' AND time_changed < '2016-04-01 00:00:00';

/* change a field name */
UPDATE `omeka`.`elements` SET `name` = 'Location' WHERE `elements`.`id` = 119;

/* find all new users registered between a date range */
SELECT * FROM `users_activations` WHERE added > '2017-12-01 00:00:00' AND added < '2017-12-31 00:00:00';

/* change a users password */
UPDATE `omeka_users` SET `password`=sha1(concat(`salt`, 'password')) WHERE `username`='admin'

/* get latest new transcription */
SELECT * FROM `Scriptus_changes` WHERE `new_transcription` = 1 ORDER BY `Scriptus_changes`.`time_changed` DESC LIMIT 1
                                                
/* get all transcriptions from a user in a date range */
SELECT * FROM `Scriptus_changes` WHERE `username` = "mtbutler" AND `time_changed` > '2018-01-01 00:00:00' AND `time_changed` < '2018-12-31 00:00:00';

/* get all file names for an item */
SELECT `filename` FROM `files` WHERE `item_id` = 4614;
                                                
/* get status of record_id */
SELECT `text` FROM `omeka`.`element_texts` WHERE `record_id` = 126560 AND `element_id` = 137
SELECT `record_id` FROM `omeka`.`element_texts` WHERE `id` = 126560
SELECT `text` FROM `omeka`.`element_texts` WHERE file = 115679 AND `element_id` = 137;                                                

/* change status of all 'Completed' to 'Started' */
UPDATE `omeka`.`element_texts` SET `text` = 'Started' WHERE `element_texts`.`element_id` = 137 AND `element_texts`.`text` = 'Completed' ;
UPDATE `omeka`.`element_texts` SET `text` = 'Started' WHERE `element_texts`.`element_id` = 137 AND `element_texts`.`text` = 'Completed' AND `element_texts`.`record_type` = 'File';
SELECT * FROM `element_texts` WHERE `element_texts`.`element_id` = 137 AND `element_texts`.`record_type` = 'File';
                                                
/* get all items in a collection */
SELECT *  FROM `omeka`.`items` WHERE `collection_id` = 18

/* get user with email */
SELECT * FROM `omeka`.`users` WHERE `email` LIKE 'matthewtbutler@gmail.com' 
                                                
/* change user's role */
UPDATE omeka.users SET role = 'researcher' WHERE email LIKE 'matthewtbutler@gmail.com'
                                                
/*Exports transcriptions with item titles, file titles, and filenames for all files in collection */
SELECT 
    i.id AS item_id,
    MAX(CASE WHEN et_item.element_id = 50 THEN et_item.text END) AS item_title,
    f.id AS file_id,
    f.filename AS file_name,
    MAX(CASE WHEN et_file.element_id = 50 THEN et_file.text END) AS file_title,
    et_trans.text AS transcription
FROM `omekaprod`.`items` i
JOIN `omekaprod`.`files` f ON i.id = f.item_id
JOIN `omekaprod`.`element_texts` et_trans ON f.id = et_trans.record_id
LEFT JOIN `omekaprod`.`element_texts` et_item ON i.id = et_item.record_id AND et_item.element_id = 50 AND et_item.record_type = 'Item'
LEFT JOIN `omekaprod`.`element_texts` et_file ON f.id = et_file.record_id AND et_file.element_id = 50 AND et_file.record_type = 'File'
WHERE i.collection_id = 7
AND et_trans.element_id = 136
AND et_trans.record_type = 'File'
GROUP BY i.id, f.id, et_trans.text, f.filename
ORDER BY i.id, f.id;