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'