I split this off the other ticket so it can be discussed better.

Basically, I'd like to hear what else you want to see as default information for the different content types, as I'd love to see more standard fields for each type by default, for example credit, exif info, and notes on each image, bitrate, encryption, length, credit for audio and video, publication and release data for dang near all etc.

That would then work well for allowing many other ways to generate galleries, exposing data, sorting for use and such without everyone having to go in and create custom fields on each type, and naturally just ignoring them if they aren't used. I'll be happy to help contrib more too once I get this current site out the door and can breathe again.

The big problem I have with shoving it all in as related metadata table entries, is that searching and joining for large sets will become much slower. Drupal 5 is already notorious for not having enough or well formed indexes on tables, and so if we're able to find a nice superset of default tagging/metadata for each media that could greatly increase the throughput.

You might have guessed, but I'm testing prior to porting a very large data set from scattered repositories within our organization (think large public research group), and having to peg 2 tables just to get a caption, credit, publication and usage info for each piece of media adds up quickly.

Thoughts?

Comments

rhys’s picture

rhys commented

I agree on the basic idea of a default metadata. Having done extensive research into the question of large datasets, with speedy results through various indexing, I'm aware of the problem of constructed indexes like this. I would not be adverse to having an extra table, or tables, with default metadata information which personally I think should include:

  • Credits (Either source of media, or credit to author/photographer of the resulting media)
  • Other similar data concerning the process of who, what, where, when and how the media came into existance

On the question of details about data that is type specific, i.e image, audio and video data, I propose that seperate tables with the default data for these be used.
Then there would be somewhat a structure of

  • Image
    • Details about the Image that are not covered by the height/width or credit information, such as color ranges, but this should be looked at since unnecessary default data in a table record can drastically increase the size of the database, without a corresponding increase in usefulness of the data
  • Audio
    • Duration
    • Encryption style - although currently this will have to be manually imported, I hope to be able to generate it with a good set of library routines usable by the extensions.
    • Standard MP3 tags - some of these may be appropriate to have as a field in a record for audio
  • Video
    • Something similar I suspect to the audio, since a lot of these things are quite related, although video has not simply audio, but a visual component, and therefore would hold extra default information. In this case it may be easier to store the audio information in the audio table, and video specific things within this table

In all cases for the above table ideas, I propose an index of the media ID for reference, and since there could be both media information for audio and video in different tables, it would be possible to have a good set of information with well constructed indexes to allow fast searching for highly used information regarding the media.

This style of design is implicitly required of a media manager system, so I'm very much in favor of this idea. Feel free to make a comment on this.

konfuzed’s picture

konfuzed commented

I was thinking along the lines of any common information that crosses all media types should be in the primary media item line. The extras that are only related by content typed and the mid (plus probably a custom couple like biblio does) could then be related in the media_metadata.

That'd allow you to do faster searches for publication / release dates on the media itself all indexed in one table vs relating a couple tables just to get all of X media which aren't expired / past licensed use etc.

I'll compare with some other digital asset management software and internal dbs and see if I can help break down not just a more common set but also naming. I know what I had to add for basics already and what I'm importing later from others, and have been looking at other packages too, so no I'll let ya know what I find.

rhys’s picture

rhys commented

It would be possible to add default common items to the media table, but these things would have to be very common, and smallish. I'm not going to add, for instance, the "body" of a node to a media table, it will stay stored as either metadata, or possibly in a second table which can be indexed by MID.

My main concern is to keep potentially large data sets (100K+) of media, without having an overly large sized table, or database. While keeping the database in a format which maximizes it's capability of speed.

konfuzed’s picture

konfuzed commented

That's along the lines of what I was thinking.

Have the main media entry be fast and commonly referenced information, much like the node table for drupal.

Then have a more detailed table of common (plus custom) information based on the type of media. That would help roll the whole idea of detailed info and metadata together for a 'full view' of the media, while the overview/quick summary/index information is all in a single row pull (especially useful for converting the table to innoDB for row-level locking instead of table locks).

What I found useful for summary info for media, in addition to what is already in the media table, are dates and basic data for comparison, namely:

- PublicationDate
- ExpirationDate
- Title
- Creator
- Publication Bureau
- Caption / Summary / quick description -- however you want to title it, think 2-3 line caption, quick summary of an audio file's song/talk show, video file notes etc
- Source Reference -- especially useful for off-site linked media you're giving in summary views

Then in the metadata/more info table you'd have information based on type, whether serialized for some (performance issues?) or columns where most are allowed to be null. Basically start off with common fields for known needs per type which have parallel if not exactly the same use between the most popular media formats and then also allow a few outliers and customization:

- Licensing info
- Full description
- Bit rate / Gamut (for audio/video vs stills)
- Encryption / Quality
- Length (doubles as page length for pdfs, running time for audio & video)
- EXIF / mp3tags / embedded info (video)

I'm leary of having too many tables broken out so that each type of file gets its own metadata table. That becomes unwieldy and makes a lot of redundancy when a more encompassing column name and some good documentation & code comments will allow for reuse. All it would take is having a content type DOC for word docs vs content type PDF and although the files would use the exact same info for characterization, you'd have 3 separate tables to join to get a listing instead of 2.

And then take that to a project which would have images, audio, video, ppt, doc etc and you see how fast a simple join becomes a huge set of joins or repeated queries (can you tell I've been having to optimize dbs lately for legacy projects that have gone into creep so far they're almost unusable? :)

Basically without a lot of this basic overlap in information and column reuse, you WON'T be able to handle a record set of thousands, because the searching and categorization would become unwieldy. Hence why I'd see a reuse of fields based on type (and careful naming) a salve for the issue. This would also reduce the row count and total row size issues some dbs would have issues with, and allow for primary unique keys on the new 'metadata' table rather than having to select all with multiple mids, allowing for much better and faster indexing and joins. It could also then be tied into versioning easier as well if that was chosen down the line.

I can give you some more specific examples from larger apps if you'd like, but if you search around to compare digital asset management software for 'enterprise' deployments (there's a nebulous term) you'll see the fast ones have done some careful abstraction.

konfuzed’s picture

konfuzed commented

Ok so as promised, here's the site that just went live today:

http://www.gtri.gatech.edu/

Naturally all the audio video and pdfs are in mmedia, somewhat hacked ;)

A showcase of the types of information that many sites, galleries, attachments etc would need to reference quickly:

http://www.gtri.gatech.edu/newsroom

http://www.gtri.gatech.edu/casestudy/asthma-vest-helps-id-asthma-causes (good example).

Naturally, some of this is built off taxonomy as well as media_attach, but the combo is powerful.

We only put in 60 articles for initial launch, and have 10 years of historical articles, photos, audio, video to put in and link to each other. And as you can probably guess, I'll be itching for the gallery side to operate too.

So now you can see why I'm so keen on making sure the basic meta-data is robust. A few fields for indexed conditionals will make mmedia very useful to a lot of people.

rhys’s picture

rhys commented

You did a good job with that website.
I quite enjoyed seeing the module in use.

So here's what I would like if possible, since you are correct that a secondary table would be much more effective.
A stated list of potential indexes for commonly used metadata. If you think there is a useful way to get that to function, I'd be more than happy to consider it.

If you haven't already checked #229920: Move creation of db tables to mapi module, it has an updated version of both the MAPI and the main module for the MMEDIA.
I'll be doing the media_attach next, so I'd be very interested to hear your thoughts on it's development ASAP.

konfuzed’s picture

konfuzed commented

I'll check them out. I was so wiped out from getting that project launched I crashed for 16 hours and then had a regular night of sleep after some food. Will get back to you as soon as possible.

konfuzed’s picture

konfuzed commented

Hey just thought you might get some ideas from looking at what Typo3's DAM module does for editing files/metadata: http://typo3.org/development/projects/digital-asset-management/

Some useful things, some which we'd want to avoid etc (outside of the fundamental framework differences).

rhys’s picture

rhys commented
Status: Active » Fixed

So I have the basics added to the CVS version of the uploaded modules. If you've got more to add, please create a new issue.

konfuzed’s picture

konfuzed commented

First glance looked great (got pulled off on another project). If I notice anything else as I'm updating I'll start up a new ticket.

Anonymous’s picture

Anonymous (not verified) commented
Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for two weeks with no activity.