Images in SQL Server

A simple .NET class to dump image data into/from SQL Server. I'd like to explore a potential alternative to this using FILESTREAM.

In T-SQL:

update version set [file] = BulkColumn from
openrowset(bulk 'e:\....jpg', single_blob) as [file]
where ...;

---

In Python/MySQL, this is done like this: (the image column, image_data, is defined as BLOB in MySQL)

>>> import MySQLdb
>>> connection = MySQLdb.connect('','root','','RTest')
>>> blob = open('d:\\pic1.jpg', 'rb').read()
>>> sql = 'INSERT INTO rtest.mm_image(image_data, mm_person_id_mm_person) VALUES(%s, 1)'
>>> args = (blob,)
>>> cursor = connection.cursor()
>>> cursor.execute(sql, args)
>>> connection.commit()

blob is a string type.

CMSRDBMSWT..?

There seems to be precious little information on data management in the context of CMS (Content Management Systems). To me, this would seem very important - data admin, modeling, etc, all should be hugely important, and yet some of the CMS I come across are little more than bit buckets with a data dictionary. While web-based CMS have certainly driven the development of NoSQL (due to performance reasons, given the underoptimal use of the RDBMS by the CMS), certainly some thought has gone into the data layer of the CMS... where is it at?

---

Speaking of CMS: Here's Plone, which is running on a NoSQL incidentally.

Python ORM

A first shot at ORMing in Python.

The second, improved shot. Inheritance/polymorphism in weakly-typed languages such as Python is a bit hard to grasp at first. Anyway, this seems quite cool.

Class diagram: (I am not an expert @ UML)




Existing solutions:
- for PHP
- for Python

Collaboration tools

Great info:

BLOBing in Mongo

SQL Server, Oracle, Cache, etc, all have binary streams, and offer various ways of storing binary data (such as images) directly into the database. I was curious to see how this would work with Mongo, and here is the result:

import pymongo
import urllib2
import wx
import sys
from pymongo import Connection

class Image:

    def __init__(self):
        self.connection = pymongo.Connection()
        self.database = self.connection.newStore
        self.collection = self.database.newColl
        self.imageName = "Uninitialized"
        self.imageData = ""

    def loadImage(self, imageUrl, imageTitle = "Undefined"):
        try:
            ptrImg = urllib2.Request(imageUrl)
            ptrImgReq = urllib2.urlopen(ptrImg)
            imageFeed = ptrImgReq.read()
            self.imageData = pymongo.binary.Binary(imageFeed, pymongo.binary.BINARY_SUBTYPE)
            self.imageName = imageTitle
            ptrImgReq.close()
       
        except:
            self.imageName = "Error " + str(sys.exc_info())
            self.imageData = None

    def persistImage(self):
        if self.imageData == None:
            print 'No data to persist'
        else:
            print 'Persisting ' + self.imageName
            self.collection.insert({"name":self.imageName, "data":self.imageData})
            self.imageData = None
   
    def renderImage(self, parm = None):
        if parm == None:
            self.imageData = self.collection.find_one({"name":self.imageName})
        else:
            self.imageName = parm
            self.imageData = self.collection.find_one({"name":self.imageName})
   
        ptrApp = wx.PySimpleApp()
        fout = file('d:/tmp.jpg', 'wb')
        fout.write(self.imageData["data"])
        fout.flush()
        fout.close()
        wximg = wx.Image('d:/tmp.jpg',wx.BITMAP_TYPE_JPEG)
        wxbmp = wximg.ConvertToBitmap()
        ptrFrame = wx.Frame(None, -1, "Show JPEG demo")
        ptrFrame.SetSize(wxbmp.GetSize())
        wx.StaticBitmap(ptrFrame, -1, wxbmp, (0,0))
        ptrFrame.Show(True)
        ptrApp.MainLoop()


img = Image()
img.loadImage('http://i208.photobucket.com/albums/bb82/julianzzkj/Acapulco/e614.jpg', 'Acapulco at night')
img.persistImage()
img.renderImage('Acapulco at night')

I have had some problems with installing PIL, so this is certainly not optimal (I have to use wx for image rendering instead, and I have not found a way of feeding a JPG datastream to an image constructor, hence the ugly recourse to a temporary file). However, the idea was to test how the database can store an image, which seems to work quite well, despite taking a few seconds to load a 300kb file.

A findOne query returns:

> db.newColl.findOne()
{
        "_id" : ObjectId("4be82f74c7ccc11908000000"),
        "data" : BinData type: 2 len: 345971,
        "name" : "Acapulco at night"
}
>

Thanks are due for some of the wx code.