1

I am trying to send pictures and videos to my online mySQL database and store them as blobs. First I compress them and store them as base64 encoded strings. To do so I have the following two functions :

FOR PHOTOS : 

 func compressImage(image:UIImage) -> NSData {
    // Reducing file size to a 10th

    var actualHeight : CGFloat = image.size.height
    var actualWidth : CGFloat = image.size.width
    let maxHeight : CGFloat = 1136.0
    let maxWidth : CGFloat = 640.0
    var imgRatio : CGFloat = actualWidth/actualHeight
    let maxRatio : CGFloat = maxWidth/maxHeight
    var compressionQuality : CGFloat = 0.5

    if (actualHeight > maxHeight || actualWidth > maxWidth){
        if(imgRatio < maxRatio){
            //adjust width according to maxHeight
            imgRatio = maxHeight / actualHeight;
            actualWidth = imgRatio * actualWidth;
            actualHeight = maxHeight;
        }
        else if(imgRatio > maxRatio){
            //adjust height according to maxWidth
            imgRatio = maxWidth / actualWidth;
            actualHeight = imgRatio * actualHeight;
            actualWidth = maxWidth;
        }
        else{
            actualHeight = maxHeight;
            actualWidth = maxWidth;
            compressionQuality = 1;
        }
    }

FOR VIDEOS : 

func compressVideo(inputURL: NSURL, outputURL: NSURL, handler:(session: AVAssetExportSession)-> Void)
{
    let urlAsset = AVURLAsset(URL: inputURL, options: nil)

    let exportSession = AVAssetExportSession(asset: urlAsset, presetName: AVAssetExportPresetMediumQuality)

    exportSession!.outputURL = outputURL

    exportSession!.outputFileType = AVFileTypeQuickTimeMovie

    exportSession!.shouldOptimizeForNetworkUse = true

    exportSession!.exportAsynchronouslyWithCompletionHandler { () -> Void in

        handler(session: exportSession!)
    }

}

I then execute the following lines :

FOR PHOTOS : 

 let compressedImageNSData = self.compressImage(currImage)
           let base64encodedImage = compressedImageNSData.base64EncodedStringWithOptions(NSDataBase64EncodingOptions(rawValue: 0))

FOR VIDOES : 

let data = NSData(contentsOfURL: self.uploadUrl)
let base64encodedVideo = data!.base64EncodedStringWithOptions(NSDataBase64EncodingOptions(rawValue: 0))

Finally I place the encoded strings as a parameter in a POST request using NSURLSession. I have tested this using my iPad2 and photos seem to post fine, videos up to 5 seconds seem to work fine as well, however anything longer than that results in the INSERT INTO SQL query in my PHP script failing. Am I doing this correctly or is there a way to further reduce the size of the string, or maybe a different method I should use completely.

UPDATE :

I have found the following limitation in phpmyAdmin : enter image description here

Is there a way I can further reduce the file size of the videos?

Alk
  • 5,215
  • 8
  • 47
  • 116
  • 1. Is your blob column a Blob, MediumBlob or LargeBlob? It may fail due to the blob column limit. 2. There are multiple limits involved: the POST size or upload (multipart) limit, the actual blob column limit, the db query size limit (max. packet size). For videos with arbitrary length you will hit one or another if there is no size restriction. If you need more flexibility/support for bigger videos it's probably better to use some kind of chunked transfer, store the actual video to the filesystem and only hold metadata in the database. – makadev Jun 08 '16 at 15:58
  • I'm using largeblob, I read that the POST limit is 2MB, my encoded video is of size 1.2 MB, I'm not sure how to check the max packet size, I'm running phpmyadmin on an online host (000webhost.com). The thing is my vidoes are limited to 10 seconds in length, that is why I think it is better to somehow limit the size through further compression etc rather than using file system. – Alk Jun 08 '16 at 16:01
  • @makadev Check the update – Alk Jun 08 '16 at 16:05
  • Another vote for storing just the name of the file in the database and the actual photo/video in the filesystem. – user3741598 Jun 08 '16 at 16:24
  • That's not something I am going to consider, what I want to do is further compress the Base 64 string – Alk Jun 08 '16 at 16:25
  • @mankee Since (lossless) compression is limited I don't see any more way's then further reduce the Quality of the video. Maybe [this SO Entry+comments is more helpful](http://stackoverflow.com/a/29938105/3828957) if a bit technical. It uses an [external component](https://github.com/rs/SDAVAssetExportSession) but looks promising (it's not swift tho). You could set the average bitrates of Audio/Video to match the needs for a maximum of 10secs or use some kind of algorithm that compresses the file in a loop until - stepwise reducing quality - until it meets the size limits. – makadev Jun 09 '16 at 08:45

0 Answers0