One of my initial (and mistaken) observations on Community Server was that CSG images and image data were stored like earlier versions of nGallery: either exclusively in SQL or on the file server with XML storage. After more time poking around CS I was able to pose a more intelligent question on CS:Forums on how Gallery images and their associated data were stored. I also asked specifically what was in a binary cs_postattachments.content field. Ken Robertson of Telligent provided a detailed and very clear description of CSG storage logic. I was going to excerpt it but it was worthy of posting in its entirety. The full thread is here.
I should add that the CS:Forums as hoppin', baby! Way too much interesting activity to keep track of. (With so much acivity, robust forums searching can always be improved upon, Telligentsia Dudes. :-)
Ken's post on CSG storage.
You are correct. We don't use XML storage anymore. That was one problem in nGallery, since SQL and XML function fairly different and it used the XML files efficiently, but the SQL data wasn't always handled in the best manner.
For a picture, the data is actually spread out over 4 tables:
1) cs_Threads: Basic info on the start of a thread/picture posting. It is often used for listings because it is a smaller table than cs_Posts and has better indexes.
2) cs_Posts: Picture subject/title/post info. When ParentID = PostID, it is the first post, otherwise, it is a comment on the picture.
3) cs_PostAttachments: This is where the picture is stored, if file system storage is disabled. Even if you have filesystem storage enabled, it still creates an entry here. Gives quick access to stuff like mimetype, filesize, original filename, etc.
4) cs_PostMetadata: This is where all the EXIF data that is read out of the image is stored.
As for the content field, if file system storage is disabled, the content column contains the picture file itself as a byte array. If file system storage is enabled, the content column is just set to 0x. In this manner, when you say Pictures.GetPictureData(), it gets the cs_PostAttachment row from the DB. If the content field is set to 0x, then it is knows that this is a picture that is stored on the filesystem by "settingsID.sectionID.postID", so it opens that file and reads it into the content variable of the PostAttachment object.
By doing this you get two things:
1) Where files are stored is transparent to CS. It saves both of them the same and retrieves both of them the same.
2) You can disable FSS (file system storage), upload some pics, enable it, upload some pics, disabled it, upload some pics, and everything will work. The ones that were stored on the filesystem will continue to be read from there, and the ones in the DB will continue to be read from there.