Database Relations in Acumen

From UA Libraries Digital Services Planning and Documentation
Revision as of 12:15, 5 July 2017 by Jlderidder (Talk | contribs)

Jump to: navigation, search

Acumen has numerous tables, some of which I'm not even sure are in use. First I'll list the tables, and then I'll talk about the ones that I've worked with to sort out problems. Here's the tables (remember, this is from all 3 acumen databases -- one for live, one for staging, and one for dev). The ones in bold I will describe further:

| aauth_group_to_group | (empty)
| aauth_groups         | (3 entries; clearly Will or Tonio was hoping to set up multiple levels of access)
| aauth_login_attempts | (empty)
| aauth_perm_to_group  | (empty)
| aauth_perm_to_user   | (empty)
| aauth_perms          | (empty)
| aauth_pms            | (empty)
| aauth_user_to_group  | (2 entries)
| aauth_user_variables | (empty)
| aauth_users          | (1 entry)
| asset                |
| asset_orphan         | (empty)
| asset_type           |
| authority            |
| authority_type       |
| crowd_status         | (to be used if anyone bothered to review tags/transcripts and approve them)
| featured             |  (to be used with what Will was setting up in dev, to showcase collections)
| file                 |
| file_type            |
| note                 | (empty)
| note_type            | (empty)
| privilege            | only one entry
| privilege_type       | only one entry
| scan_task            | (empty)
| scratch              |  (not used often, and not sure how/why it's used)
| search_category      | (empty)
| status_type          |
| tags                 |
| tags_assets          |
| transcripts          |
| user                 | (two test entries)
| user_action          | (empty)
| user_action_type     | (empty)

Tables of Interest

  • asset
  • asset_type
  • authority
  • authority_type
  • file
  • file_type
  • status_type
  • tags
  • tags_assets
  • transcripts

asset_type table

This table lists the types of digital content (apart from metadata) that we currently support in Acumen, with the extensions to be expected for the delivery file and the extensions to be expected for the thumbnails. Each type of asset has an assigned ID:

| id | type     | asset_tails               | thumb_tails                     | parent_folder |
+----+----------+---------------------------+---------------------------------+---------------+
|  1 | image    | .tif,.tiff,_2048.jpg      | _512.jpg,_128.jpg,.txt,.ocr.txt | NULL          |
|  3 | video    | _512kb.mp4,.mp4           | _128v.jpg,_800s.jpg             | NULL          |
|  4 | audio    | _64kb.mp3,_256kb.mp3,.mp3 | .txt,.ocr.txt                   | NULL          |
|  2 | document | .pdf                      | _128c.jpg                       | NULL          |
|  6 | ignore   | .icon.jpg                 |                                 | NULL          |

If you need Acumen to support new types of files, add them here. If they will exist in a subdirectory (such as SupplementalFiles), I think that would go into the parent_folder field.

asset table

Here's the columns in the asset table. This table tracks every single image, audio, video and text file, each of which is considered an "asset" to a metadata record somewhere, in some capacity:

| Field              | Type                 | Null | Key | Default | Extra          |
+--------------------+----------------------+------+-----+---------+----------------+
| id                 | bigint(20)           | NO   | PRI | NULL    | auto_increment | This is referred to in other tables as file_id
| asset_type_id      | int(11)              | NO   |     | NULL    |                | See asset_type ID values in asset_type table above
| name               | varchar(255)         | NO   | UNI | NULL    |                | This is of the form: u0003_0000581_0000001
| orig_path          | varchar(255)         | NO   |     | NULL    |                | This is the acumen path for web delivery, including file name
| thumb_path         | varchar(255)         | NO   |     | NULL    |                | This is the acumen path for web delivery of thumb (no file name)
| file_id            | bigint(20)           | YES  | MUL | NULL    |                | See file_type ID values in file_type table below
| file_size          | bigint(20)           | NO   |     | NULL    |                | size in bytes
| file_last_modified | bigint(20)           | NO   |     | NULL    |                | this is in seconds since the Epoch
| status_type_id     | int(11)              | NO   | MUL | NULL    |                | See status_type ID values in status_type table below
| found              | smallint(5) unsigned | NO   | MUL | NULL    |                | If "1" the system found the file; if "0" there's a problem.

As you can see, fields in some tables refer to fields in others. Notably, the different types of IDs are used to pull information together. That's why this is called a relational database.

Personal tools