-- MediaTypeReset.sql
/*
2012-12-04 Tom Holden ve3meo
2012-12-09 restricted Image file types to those rendered by Viewer/Reporter
2023-02-19 MP4 added

Users can mistakenly add image files to the Media Gallery with the 
wrong MediaType which prevents the images from being used in reports.
MediaType is normally set by the user selection in the Add Media Item dialog
and cannot be changed from within RootsMagic 6.0.0.2 and earlier.
This script sets the MediaType according to the file extension of the media file.
*/

-- Clear all previous settings
UPDATE MultimediaTable
 SET MediaType=0
;
-- Set Image files (only RM Add Media filters + .targ, .tiff because of viewer/reporter limitations)
UPDATE MultimediaTable
 SET MediaType=1
  WHERE LOWER(SUBSTR(MediaFile,-4)) 
  IN ('.bmp','.gif','.jpg','jpeg','.png','.tga','targ','.tif','tiff')
; 
-- Set Sound files (RM Add Media filters on just .wav, .mid, .mp3 but no player to set constraints)
UPDATE MultimediaTable
 SET MediaType=3
  WHERE LOWER(SUBSTR(MediaFile,-4)) 
  IN ('.wav','.mid','.mp3','.ogg','.gsm','.dct','flac','aiff','.vox','.raw','.wma','.aac')
  OR LOWER(SUBSTR(MediaFile,-3)) 
  IN ('.au','.ra','ram','dss','msv','dvf','ape')
;
-- Set Video files (RM filters on Add Media only for .avi, .mov, .mpg, .mpeg, .wmv but no player to set constraints)
UPDATE MultimediaTable
 SET MediaType=4
  WHERE LOWER(SUBSTR(MediaFile,-4)) 
  IN ('.3gp','.asf','.avi','.dat','.flv','.m4v','.mkv','.mov','mp4','mpeg','.mpg','.mpe','.swf','.wmv')
;
-- Set files, other than previously set Image, Sound, Video types, to File type
UPDATE MultimediaTable
 SET MediaType=2
  WHERE MediaType=0
;