Running SQL to fix text fields


#1

We had a few grad students put in a bunch of dates (100+) in items in an exhibit using the wrong format and I can easily fix this with an SQL query. I’ve worked with some Oracle databases in the past that maintain tight edit records and edit relationships that can get broken by running a raw query to correct something in a table. Is this the case in Omeka or can I just run the query to fix the dates to avoid having to have the group manually correct them all?


#2

Editing text (like element text or exhibit text) in the database directly shouldn’t break anything, though you’d probably have to run a search reindex afterward to pick up the changes.

As with any manual editing of the database, I’d highly suggest taking a backup first.


#3

The item metadata is stored in omeka_element_texts table like so:

id, record_id, record_type, element_id, html (flag), text
589816 56 Item 40 0 1911-03-05

So there should be no issue with running a query on a range of ID or RECORD_ID or even all of ELEMENT_ID=40 (in my case, maybe not yours) to adjust date format.

There is no required data format, of course. Ours is formatted YYYY-MM-DD for use with the Date Search plugin which allows for search over date ranges.


#4

Cool. Thanks. Just checking.