Skip to content

DataBase Recommended Usage

Jump to bottom Edit New page
Gustavo Sverzut Barbieri edited this page Dec 11, 2013 · 1 revision

Table: Files

This table is the one that associates an entry with the filesystem, giving its modification, insertion or deletion time, size in bytes and path.

Columns:

  • id: the unique file identifier, this 64bit integer is auto-incremented for new entries.
  • path: full file path identifying the entry.
  • mtime: last modification time, as per stat(2) st_mtime.
  • dtime: if file doesn't exist in the filesystem anymore, contains the time when the lms_check() happened. If the file exists in the filesystem, then this field must be zero.
  • itime: the time when the file was inserted into the database. Useful to show "files added today" or similar.
  • size: file size in bytes, as per stat(2) st_size;
  • update_id: the update_id at the time of last file modification. See table lms_internal.

Examples:

  • List all known paths: `SELECT path FROM files;``
  • List all live (non-deleted) paths: SELECT path FROM files WHERE dtime = 0;
  • List all deleted paths before last week: SELECT path FROM files WHERE dtime > 0 AND dtime <= $LASTWEEK; (where $LASTWEEK is the Unix time of today subtracted from 7 * 24 * 60 * 60).
  • List all live files inserted today: SELECT path FROM files WHERE dtime = 0 AND itime >= $TODAY; (where $TODAY is the Unix time of today at 00h00).

Note:

Remember to always check dtime = 0 when doing queries to show available media. When a file is gone from the file system LightMediaScanner will not delete the entries from its database, instead will flag the file as gone (dtime = time(NULL);). This will allow fast restore of files if they come back with the same path, mtime and size -- which is common with removable media!

Table: images

This table contains image entries and their properties. The file properties can be obtained by doing a JOIN with the files table.

Columns:

  • id: the unique file identifier, see files.id description.
  • title: tile (if none will be created from file base name).
  • artist: artist or author.
  • date: creation time (Unix-time).
  • width: width in pixels.
  • height: height in pixels.
  • orientation: image intended display orientation, same as EXIF Orientation.
  • gps_lat: GPS latitude, in degrees.
  • gps_long: GPS longitude, in degrees.
  • gps_alt: GPS altitude, in meters.
  • dlna_profile:_ if the image matches any DLNA profile, this is set to it.
  • dlna_mime: if the image matches any DLNA mime, this is set to it.

Examples:

  • List all path of live images: SELECT files.path FROM images, files WHERE files.id = images.id AND files.dtime = 0;

  • List all path, width and height of live images with dlna_profile "image/jpeg":

     SELECT files.path, images.width, images.height 
 FROM images, files
 WHERE files.id = images.id AND files.dtime = 0 AND dlna_mime = 'image/jpeg'

Tables: audios, audio_albums, audio_artists and audio_genres

This set of tables relate to audio files. The mandatory one is audios, which can have an album (relation to audio_albums), have an artist (relation to audio_artists) and genre (relation to audio_genres). The file properties can be obtained by doing a JOIN with the files table.

Columns of audios:

  • id: the unique file identifier, see files.id description.
  • title: tile (if none will be created from file base name).
  • album_id: unique album identifier so it can be related to audio_albums.
  • artist_id: unique artist identifier so it can be related to audio_artists.
  • genre_id: unique genre identifier so it can be related to audio_genres.
  • trackno: track number inside an album.
  • rating: user rating (also known as "stars" or "like", not to be confused as content age rating).
  • playcnt: play count.
  • length: length/duration in seconds.
  • container: file type container, such as "asf", "mp4", etc.
  • codec: audio codec, such as "mpeg1layer3", "mpeg4aac-main", etc.
  • channels: number of audio channels, mono is 1, stereo is 2, etc.
  • sampling_rate: audio sampling rate, such as 44100, 48000, etc.
  • bitrate: average bits per sample, such as 32000, 8000, etc.
  • dlna_profile:_ if the audio matches any DLNA profile, this is set to it.
  • dlna_mime: if the audio matches any DLNA mime, this is set to it.

Columns of audio_artists:

  • id: the unique artist identifier, see audios.artist_id description.
  • name: artist name.

Columns of audio_albums:

  • id: the unique album identifier, see audios.album_id description.
  • artist_id: unique artist identifier so it can be related to audio_artists.
  • name: album name.

Columns of audio_genres:

  • id: the unique genre identifier, see audios.genre_id description.
  • name: genre name.

Examples:

  • List all path of live audio: SELECT files.path FROM audios, files WHERE files.id = audios.id AND files.dtime = 0;

  • List all path, title, artist, album and genre names of live audio:

     SELECT files.path, audios.title, audio_artists.name, audio_albums.name, audio_genres.name 
 FROM audios, files, audio_artists, audio_albums, audio_genres
 WHERE files.id = audios.id AND files.dtime = 0
       AND audio_artists.id = audios.artist_id
       AND audio_albums.id = audios.album_id
       AND audio_genres.id = audios.genre_id

Table: videos, videos_audios, videos_subtitles, videos_videos

This set of tables relate to video files. The mandatory one is videos, which can have multiple streams of audio, video or subtitle. The file properties can be obtained by doing a JOIN with the files table.

Columns of videos:

  • id: the unique file identifier, see files.id description.
  • title: tile (if none will be created from file base name).
  • artist: artist or author.
  • length: length/duration in seconds.
  • container: file type container, such as "asf", "mp4", etc.
  • dlna_profile:_ if the video matches any DLNA profile, this is set to it.
  • dlna_mime: if the video matches any DLNA mime, this is set to it.

Columns of videos_audios:

  • id: the unique audio stream identifier.
  • video_id: the unique video identifier, see videos.id description.
  • stream_id: the stream identifier inside the video.
  • lang: the language, such as "en", "pt", "es", etc.
  • codec: audio stream codec, such as "mpeg1layer3", "mpeg4aac-main", etc.
  • channels: number of audio channels, mono is 1, stereo is 2, etc.
  • sampling_rate: audio sampling rate, such as 44100, 48000, etc.
  • bitrate: average bits per sample, such as 32000, 8000, etc.

Columns of videos_subtitles:

  • id: the unique subtitle stream identifier.
  • video_id: the unique video identifier, see videos.id description.
  • stream_id: the stream identifier inside the video.
  • lang: the language, such as "en", "pt", "es", etc.
  • codec: subtitle stream codec, such as "srt", "ssa", "sub", etc.

Columns of videos_videos:

  • id: the unique video stream identifier.
  • video_id: the unique video identifier, see videos.id description.
  • stream_id: the stream identifier inside the video.
  • lang: the language, such as "en", "pt", "es", etc.
  • codec: audio stream codec, such as "mpeg4-simple-l1", "mpeg4-main-l4", etc.
  • aspect_ratio: aspect ratio encoded as a string, such as "4:3", "16:9", etc.
  • bitrate: average bits per sample, such as 500000, 800000, etc
  • framerate: frames per second, such as 30.0, 29.94, 60.0, etc.
  • interlaced: 1 if interlaced, otherwise.
  • width: width in pixels.
  • height: height in pixels.

Examples:

  • List all path of live audio: SELECT files.path FROM videos, files WHERE files.id = videos.id AND files.dtime = 0;

  • List all audio streams from a video:

     SELECT videos_audios.id, videos_audios.stream_id, videos_audios.lang, videos_audios.codec,
        videos_audios.channels, videos_audios.sampling_rate, videos_audios.bitrate
 FROM videos_audios WHERE videos_audios.id = $VIDEOID

Table: lms_internal

This table is for internal bookkeeping and stores the version of tables. If a table is modified and receives more columns, the version of such table is modified in this table.

Update ID

There is a special row tab = 'update_id' that indicates the global update_id integer. This integer is incremented every time lms_process() or lms_check() modifies the database, either by inserting, modifying or deleting a file. This value should be used to allow readers to synchronize their contents.

The naïve method to synchronize content is to store the global update_id at the time of a query and once the global value changes all the queries should be re-executed.

The most efficient method is to do the query limiting files with update_id > $OLD_UPDATE_ID and then merge the results with the previous. This requires one to remember at least the file.id so one can update modified entries, remember to read new files.dtime to see if they were gone from filesystem and the newly added results must be properly inserted respecting sort criteria (this is usually the painful bit of this method).

Add a custom footer
Add a custom sidebar
Clone this wiki locally