Classification of Mimetypes Update

Mimetype_Classification.sql

SELECT 
	 MIME_UID
	,MIME_Name
	,MIME_FileExtension
	,MIME_FileExtensionNoPoint
	,MIME_Type
	,MIME_Details
	,MIME_IsAllowed
	,MIME_IsTextFile
	,Mime_IsImage
	,Mime_Category
FROM T_SYS_Ref_MimeTypes

--WHERE (1=2) OR 

WHERE (1=1)
AND NOT 
( 
	(1=2) 
	OR MIME_Type LIKE 'text/%' 
	OR MIME_Type LIKE '%+xml%' 
	OR MIME_Type LIKE '%ecma%' 
	OR MIME_Type LIKE '%javascript%'  
	OR MIME_Type LIKE '%json%' 
	OR MIME_Type IN 
	(
		'application/x-tex', 'application/x-latex', 'application/mathematica'
		, 'application/xml', 'application/x-texinfo', 'image/vnd.dxf'
		-- 'message/rfc822', 'application/pgp-signature'
	)
)

ORDER BY MIME_FileExtension 



UPDATE T_SYS_Ref_MimeTypes SET Mime_Category = SUBSTRING(MIME_Type, 0, CHARINDEX('/', MIME_Type) )


UPDATE T_SYS_Ref_MimeTypes
   SET Mime_IsImage = 'true' 
WHERE MIME_Type LIKE 'image/%'
AND  MIME_FileExtensionNoPoint <> 'dwg' 
AND  MIME_FileExtensionNoPoint <> 'dxf' 


UPDATE T_SYS_Ref_MimeTypes
   SET MIME_IsTextFile = 'true' 
WHERE (1=2) 
OR MIME_Type LIKE 'text/%' 
OR MIME_Type LIKE '%+xml%' 
OR MIME_Type LIKE '%ecma%' 
OR MIME_Type LIKE '%javascript%'  
OR MIME_Type LIKE '%json%' 
OR MIME_Type IN 
(
	'application/x-tex', 'application/x-latex', 'application/mathematica'
	,'application/xml', 'application/x-texinfo', 'image/vnd.dxf'
	-- 'message/rfc822', 'application/pgp-signature'
)



/*
;WITH CTE AS 
(
	SELECT 
		 Mime_ID
		,MIME_FileExtension
		,ROW_NUMBER() OVER (ORDER BY MIME_FileExtension) as rn 
	FROM T_SYS_Ref_MimeTypes
) 

UPDATE CTE SET Mime_ID = rn 
*/