SQL Snippets¶

Although an MDID3 system administrator will ultimately be better served by becoming familiar with python/django and interacting with MDID3 via shell_plus, it is possible (and sometimes more convenient) to query the MDID database with SQL. Here are some sample queries to help discovery of the MDID schema.

Alphabetical list of collections¶

SELECT
    data_collection.id,
    data_collection.title,
    data_collection.name,
    data_collection.owner_id,
    data_collection.hidden,
    data_collection.description,
    data_collection.agreement,
FROM
    data_collection
ORDER BY data_collection.title ASC

Getting the database id of a specific user by username¶

SELECT id, username
FROM `rooibos`.`auth_user`
WHERE auth_user.username = 'llux'

How many presentations does that user have?¶

SELECT
    COUNT(presentation_presentation.id)
FROM
    presentation_presentation
WHERE
    presentation_presentation.owner_id = 60

getting information about records¶

If searching for information in your individual records, you might try something like:

SELECT
    id, created, modified, name, owner_id
FROM
    data_record
WHERE
    data_record.id = 41588

This gets some information about a record, but it will be missing the metadata, here’s the output:

id	created	modified/td>	name	owner_id
41588	2007-10-04 13:32:23	2010-10-20 09:15:58	r-6165371	60

If you want detailed information, maybe it’s unsurprising that some JOINs are necessary:

SELECT
    data_record.id,
    data_record.name,
    data_fieldvalue.label,
    data_fieldvalue.value
FROM
    rooibos.data_record
        INNER JOIN
    rooibos.data_fieldvalue ON data_fieldvalue.record_id = data_record.id
WHERE
    data_record.id = 41588

41588	r-6165371	Date	1964
41588	r-6165371	Description	mixed material assemblage
41588	r-6165371	Creator	Kienholz, Edward
41588	r-6165371	Title	Back Seat Dodge, '38