# Version SVN: $Id: 40_medias_schema.yml 6812 2019-06-06 11:09:43Z stefan.ganz $
propel:
  media:
    _attributes:                { phpName: Media, allowPkInsert: true }
    mid:                        { type: integer, size:  11, required: true, primaryKey: true, autoIncrement: true }
    uid:                        { type: integer, size:  11, required: true }
    mediakey:                   { type: varchar, size:  32, required: true }
    mediahash:                  { type: varchar, size: 128, required: true }
    mediatype:                  { type: integer, size:  11, required: true } # 0=video, 1=photo, 2=audio, 3=document
    mediasubtype:               { type: integer, size:  11, required: true } # 0=default, 1=embeded, 2=youtube
    first:                      { type: boolean, size:   1, required: true, default: false } # media is a msn media and it appears only in activity
    hidden:                     { type: boolean, size:   1, required: true, default: false } # media is private, only uid can see it
    secret:                     { type: boolean, size:   1, required: true, default: false }
    status:                     { type: integer, size:  11, required: true, default: 0 } # 0=uploaded, 1=converting, 2=error, 3=verify, 4=illegal, 5=legal, 6=denied, 7=deleted
    featured:                   { type: boolean, size:   1, required: true, default: false }
    culture:                    { type: varchar, size:   7, required: true }
    properties:                 { type: longvarchar, required: true }
    childwelfare:               { type: boolean, size:   1, reuqired: true, default: false }
    title:                      { type: varchar, size: 255, required: true }
    title_clean:                { type: varchar, size: 255, required: true }
    sid:                        { type: integer, size:  11 } # references statictext
    duration:                   { type: integer, size:  11, required: true, default: 0 } # playlength in seconds for video and audio
    filesize:                   { type: bigint,  size: 20, required: true, default: 0 } # filesize of original media file
    thumbnail_filename:         { type: varchar, size: 255 } # filename of the thumbnail
    thumbnail_custom_filename:  { type: varchar, size: 255 } # custom filename of thumbnail
    source_filename:            { type: varchar, size: 255 } # filename of the source media
    original_filename:          { type: varchar, size: 255 } # original filename of the source media
    scrambled_filename:         { type: varchar, size: 255 } # filename of the converted media, scrambled
    server:                     { type: varchar, size: 255 }
    transcoder:                 { type: varchar, size: 255 }
    file_extension:             { type: varchar, size:   4, required: true }
    meta_title:                 { type: varchar, size: 255, required: false }
    meta_description:           { type: longvarchar, required: false }
    meta_keywords:              { type: longvarchar, required: false }
    meta_author:                { type: varchar, size: 255, required: false }
    meta_copyright:             { type: varchar, size: 255, required: false }
    conversion_notice:          { type: varchar, size: 255, required: false } # info output generated by the conversion script
    sum_rating:                 { type: float,   size:   8, scale: 2, required: true, default: 0.00 } # sums up media_rating table
    sum_likes:                  { type: integer, size:  11, required: true, default: 0 } # sums up media_like table
    count_views:                { type: integer, size:  11, required: true, default: 0 } # sums up media_view table
    count_views_embed:          { type: integer, size:  11, required: true, default: 0 } # sums up media_view table
    count_views_webtv:          { type: integer, size:  11, required: true, default: 0 } # sums up media_view table
    count_rating:               { type: integer, size:  11, required: true, default: 0 } # sums up media_rating table
    count_likes:                { type: integer, size:  11, required: true, default: 0 } # sums up media_like table
    count_favorites:            { type: integer, size:  11, required: true, default: 0 } # sums up all user favorites by using media_favorite table
    count_comments:             { type: integer, size:  11, required: true, default: 0 } # sums up media_comment table
    count_flags:                { type: integer, size:  11, required: true, default: 0 } # sums up media_flag table
    accept_comment:             { type: boolean, size:   1, required: true, default: true }
    accept_rating:              { type: boolean, size:   1, required: true, default: true }
    enable_download:            { type: boolean, size:   1, required: false, default: false }
    enable_download_for_users:  { type: boolean, size:   1, required: false, default: true }
    priority_transcode:         { type: boolean, required: false, default: false}
    smil_exists:                { type: boolean, size:   1, required: false, default: false }
    created_at:                 { type: timestamp, required: true }
    updated_at:                 { type: timestamp, required: true }
    uploaded_at:                { type: timestamp, required: true }
    converted_at:               { type: timestamp }
    _foreignKeys:
      m_fk_uid:                 { foreignTable: user, references: [ {local: uid, foreign: uid} ] }
      m_fk_sid:                 { foreignTable: statictext, references: [ {local: sid, foreign: sid} ] }
    _indexes:
      m_i_uid:                  [uid]
      m_i_created_at:           [created_at]
      m_i_uploaded_at:          [created_at]
      m_i_type_status_hidden:   [mediatype, status, hidden]
      m_i_status:               [status]
      m_i_status_created:       [status, created_at]
      m_i_status_uploaded:      [status, uploaded_at]
      m_i_key_status:           [mediakey, status]


  media_file:
    _attributes:                { phpName: MediaFile, allowPkInsert: true }
    mfiid:                      { type: integer, size:  11, required: true, primaryKey: true, autoIncrement: true }
    mid:                        { type: integer, size:  11, required: true }
    filetype:                   { type: varchar, size:  20, required: true }
    mfoid:                      { type: integer, size:  11, required: false, default: null }
    status:                     { type: integer, size:  11, required: true, default: 0 } # 0=created, 1=verify, 2=illegal, 3=legal, 4=deleted
    title:                      { type: varchar, size: 255, required: true }
    filename:                   { type: varchar, size: 255, required: true } # filename of the converted media, scrambled
    created_at:                 { type: timestamp, required: true }
    updated_at:                 { type: timestamp, required: true }
    converted_at:               { type: timestamp }
    _foreignKeys:
      mfi_fk_mid:               { foreignTable: media, references: [ {local: mid, foreign: mid} ] }
      mfi_fk_mfoid:             { foreignTable: media_format, references: [ {local: mfoid, foreign: mfoid} ] }
    _indexes:
      mfi_i_mid:                [mid]
      mfi_i_mfoid:              [mfoid]
      mfi_i_mid_mfoid:          [mid, mfoid]


  media_format:
    _attributes:                { phpName: MediaFormat, allowPkInsert: true }
    mfoid:                      { type: integer, size:  11, required: true, primaryKey: true, autoIncrement: true }
    status:                     { type: integer, size:  11, required: true, default: 0 } # 0=created, 1=verify, 2=illegal, 3=legal, 4=deleted
    title:                      { type: varchar, size: 255, required: true }
    resolution:                 { type: varchar, size:  20, required: true }
    bitrate_video:              { type: integer, size:  11, required: true }
    bitrate_audio:              { type: integer, size:  11, required: true }
    bitrate_video_mobile:       { type: integer, size:  11, required: true }
    bitrate_audio_mobile:       { type: integer, size:  11, required: true }
    weight:                     { type: integer, size:  11, required: true, default: 0 }
    created_at:                 { type: timestamp, required: true }
    updated_at:                 { type: timestamp, required: true }
    _indexes:
      mfo_i_resolution:         [resolution]
      mfo_i_vbitrate:           [bitrate_video]
      mfo_i_abitrate:           [bitrate_audio]
      mfo_i_res_vrate:          [resolution, bitrate_video]
      mfo_i_res_arate:          [resolution, bitrate_audio]
      mfo_i_res_vrate_arate:    [resolution, bitrate_video, bitrate_audio]


  media_category:
    _attributes:                { phpName: MediaCategory, allowPkInsert: true }
    mid:                        { type: integer, size:  11, required: true, primaryKey: true }
    cid:                        { type: integer, size:  11, required: true, primaryKey: true }
    _foreignKeys:
      mc_fk_mid:                { foreignTable: media, references: [ {local: mid, foreign: mid} ] }
      mc_fk_cid:                { foreignTable: category, references: [ {local: cid, foreign: cid} ] }
    _indexes:
      mc_i_mid:                 [mid]
      mc_i_cid:                 [cid]


  media_tag:
    _attributes:                { phpName: MediaTag, allowPkInsert: true }
    mid:                        { type: integer, size:  11, required: true, primaryKey: true }
    tid:                        { type: integer, size:  11, required: true, primaryKey: true }
    pos:                        { type: integer, size:   3, required: true }
    _foreignKeys:
      mt_fk_mid:                { foreignTable: media, references: [ {local: mid, foreign: mid} ] }
      mt_fk_tid:                { foreignTable: tag, references: [ {local: tid, foreign: tid} ] }
    _indexes:
      mt_i_mid:                 [mid]
      mt_i_tid:                 [tid]


  media_tag_title:
    _attributes:                { phpName: MediaTagTitle, allowPkInsert: true }
    mid:                        { type: integer, size:  11, required: true, primaryKey: true }
    ttid:                       { type: integer, size:  11, required: true, primaryKey: true }
    pos:                        { type: integer, size:   3, required: true }
    _foreignKeys:
      mtt_fk_mid:                { foreignTable: media, references: [ {local: mid, foreign: mid} ] }
      mtt_fk_ttid:               { foreignTable: tag_title, references: [ {local: ttid, foreign: ttid} ] }
    _indexes:
      mtt_i_mid:                 [mid]
      mtt_i_ttid:                [ttid]


  media_rating:
    _attributes:                { phpName: MediaRating, allowPkInsert: true }
    mid:                        { type: integer, size:  11, required: true, primaryKey: true }
    uid:                        { type: integer, size:  11, required: true, primaryKey: true }
    hostname:                   { type: varchar, size: 255, required: ture, primaryKey: true }
    rating:                     { type: integer, size:   3, required: true }
    created_at:                 { type: timestamp, required: true, primaryKey: true }
    updated_at:                 { type: timestamp, required: true }
    _foreignKeys:
      mr_fk_mid:                { foreignTable: media, references: [ {local: mid, foreign: mid} ] }
      mr_fk_uid:                { foreignTable: user, references: [ {local: uid, foreign: uid} ] }
    _indexes:
      mr_i_mid:                 [mid]
      mr_i_uid:                 [uid]


  media_like:
    _attributes:                { phpName: MediaLike, allowPkInsert: true }
    mid:                        { type: integer, size:  11, required: true, primaryKey: true }
    uid:                        { type: integer, size:  11, required: true, primaryKey: true }
    hostname:                   { type: varchar, size: 255, required: ture, primaryKey: true }
    like:                       { type: integer, size:   3, required: true }
    created_at:                 { type: timestamp, required: true, primaryKey: true }
    updated_at:                 { type: timestamp, required: true }
    _foreignKeys:
      ml_fk_mid:                { foreignTable: media, references: [ {local: mid, foreign: mid} ] }
      ml_fk_uid:                { foreignTable: user, references: [ {local: uid, foreign: uid} ] }
    _indexes:
      ml_i_mid:                 [mid]
      ml_i_uid:                 [uid]


  media_comment:
    _attributes:                { phpName: MediaComment, allowPkInsert: true }
    mcid:                       { type: integer, size:  11, required: true, primaryKey: true, autoIncrement: true }
    mid:                        { type: integer, size:  11, required: true }
    pid:                        { type: integer, size:  11, required: true, default: 0 }
    uid:                        { type: integer, size:  11, required: true }
    ano_username:               { type: varchar, size: 255 }
    text:                       { type: longvarchar, required: true }
    status:                     { type: integer, size:  11, required: true, default: 0 } # 0=created, 1=verify, 2=illegal, 3=legal, 4=deleted
    created_at:                 { type: timestamp, required: true }
    updated_at:                 { type: timestamp, required: true }
    _foreignKeys:
      mco_fk_mid:               { foreignTable: media, references: [ {local: mid, foreign: mid} ] }
      mco_fk_uid:               { foreignTable: user, references: [ {local: uid, foreign: uid} ] }
    _indexes:
      mco_i_mid:                [mid]
      mco_i_pid:                [pid]
      mco_i_uid:                [uid]


  media_view:
    _attributes:                { phpName: MediaView, allowPkInsert: true }
    mvid:                       { type: integer, size:  11, required: true, primaryKey: true, autoIncrement: true }
    mid:                        { type: integer, size:  11, required: true }
    uid:                        { type: integer, size:  11, required: true }
    context:                    { type: integer, size:  11, required: true } # 0 = normal, 1 = embed, 2 = webtv
    sessid:                     { type: varchar, size: 128, required: true }
    hostname:                   { type: varchar, size: 244, required: true }
    external:                   { type: boolean, size:   1, required: true, default: false }
    external_url:               { type: varchar, size: 1024 }
    created_at:                 { type: timestamp, required: true }
    _foreignKeys:
      mv_fk_mid:                { foreignTable: media, references: [ {local: mid, foreign: mid} ] }
      mv_fk_uid:                { foreignTable: user, references: [ {local: uid, foreign: uid} ] }
    _indexes:
      mv_i_mid:                 [mid]
      mv_i_context:             [context]
      mv_i_uid:                 [uid]
      mv_i_created_at:          [created_at]
      mv_i_mid_context:         [mid, context]
      mv_i_user_views:          [uid, context, sessid(128)]
      mv_i_externals:           [mid, external_url(128), external]


  media_history:
    _attributes:                { phpName: MediaHistory, allowPkInsert: true }
    mid:                        { type: integer, size:  11, required: true, primaryKey: true }
    uid:                        { type: integer, size:  11, required: true, primaryKey: true }
    created_at:                 { type: timestamp, required: true, primaryKey: true }
    _foreignKeys:
      mh_fk_mid:                { foreignTable: media, references: [ {local: mid, foreign: mid} ] }
      mh_fk_uid:                { foreignTable: user, references: [ {local: uid, foreign: uid} ] }
    _indexes:
      mh_i_mid:                 [mid]
      mh_i_uid:                 [uid]


  media_flag:
    _attributes:                { phpName: MediaFlag, allowPkInsert: true }
    flagid:                     { type: integer, size: 11, required: true, primaryKey: true, autoIncrement: true }
    mid:                        { type: integer, size: 11, required: true }
    uid:                        { type: integer, size: 11, required: true }
    reason:                     { type: longvarchar, required: true }
    status:                     { type: integer, size:  11, required: true, default: 0 } # 0=flagged, 1=confirmed, 2=rejected
    created_at:                 { type: timestamp, required: true }
    updated_at:                 { type: timestamp, required: true }
    _foreignKeys:
      mf_fk_mid:                { foreignTable: media, references: [ {local: mid, foreign: mid} ] }
      mf_fk_uid:                { foreignTable: user, references: [ {local: uid, foreign: uid} ] }
    _indexes:
      mf_i_mid:                 [mid]
      mf_i_uid:                 [uid]
      mf_i_status:              [status]


  media_favorite:
    _attributes:            { phpName: MediaFavorite, allowPkInsert: true }
    uid:                    { type: integer, size:  11, required: true, primaryKey: true }
    mid:                    { type: integer, size:  11, required: true, primaryKey: true }
    created_at:             { type: timestamp, required: true }
    updated_at:             { type: timestamp, required: true }
    _foreignKeys:
      mfav_fk_uid:          { foreignTable: user, references: [ {local: uid, foreign: uid} ] }
      mfav_fk_mid:          { foreignTable: media, references: [ {local: mid, foreign: mid} ] }
    _indexes:
      mfav_i_uid_mid:       [uid, mid]
      mfav_i_uid:           [uid]
      mfav_i_mid:           [mid]
