Personal computing discussed

Moderators: renee, SecretSquirrel, just brew it!

 
titan
Grand Gerbil Poohbah
Topic Author
Posts: 3376
Joined: Mon Feb 18, 2002 7:00 pm
Location: Great Smoky Mountains
Contact:

Storing Images in a Database

Fri Sep 12, 2008 11:20 pm

The subject pretty much states my intention. I want to store images in a database. Specifically, I want to store photos in a database. The reason is that I want to keep these photos inaccessible unless the user has sufficient rights to a photo.

The database I'd prefer to use is PostgreSQL. The webapp I'm designing is centering around PostgreSQL, but if I have to use a different DB to accomplish my goals, I won't mind so much since I haven't started writing any code yet.

I don't want to have the image stored to the hard drive at any point, so I want it to reside in memory until it has been uploaded and then destroyed immediately upon completion.

I've seen two possible ways to do this: bytea or large object/blob. Is bytea what I want to use? If so, how do I get it to work in PHP?

I'll be using the latest PHP and PostgreSQL. The data will only be read be users, and the administrators -- myself and another...basically people I trust -- will be inserting the data. Any tips?
The best things in life are free.
http://www.gentoo.org
Guy 1: Surely, you will fold with me.
Guy 2: Alright, but don't call me Shirley.
 
notfred
Maximum Gerbil
Posts: 4610
Joined: Tue Aug 10, 2004 10:10 am
Location: Ottawa, Canada

Re: Storing Images in a Database

Sat Sep 13, 2008 9:09 pm

You are going about this the wrong way, what you really need to do is store the image on the drive but without permissions for users to access, then have the database grant permissions to an application to access the image.

If you store the image in a database, the database gets stored on disk and there is usually minimal encoding on it so it would be pretty easy to recover the information if you have access to the disk.

Step back a bit, what exactly are you trying to accomplish? Hide your pron stash from others on the computer? :)

If all you want to do is hide stuff on the computer from others with access to the computer then look at something like TrueCrypt which can hide a drive image in a file and even has plausible deniability if someone gets really agitated about that file.

If you are trying to regulate network access to certain files, see my first point.
 
titan
Grand Gerbil Poohbah
Topic Author
Posts: 3376
Joined: Mon Feb 18, 2002 7:00 pm
Location: Great Smoky Mountains
Contact:

Re: Storing Images in a Database

Mon Sep 15, 2008 3:54 pm

Nope, no pron stash. My girlfriend finds them too easily, and she isn't even a computer geek. I should put more effort into hiding the stash.

What I am really trying to do is prevent access via the Internet. This is for a Web site that I'm designing, so the path I'm concerned about is Apache related. I am not concerned about physical access to the machine. Just Internet access.

I haven't really though about it, but I suppose I could have the files outside of the document root directory. I could have PHP pull in the images that the user has rights to view and download. That would probably save a lot of processing time, too. The only thing stored in the database is the location of the image files, right?
The best things in life are free.
http://www.gentoo.org
Guy 1: Surely, you will fold with me.
Guy 2: Alright, but don't call me Shirley.
 
steelcity_ballin
Gerbilus Supremus
Posts: 12072
Joined: Mon May 26, 2003 5:55 am
Location: Pittsburgh PA

Re: Storing Images in a Database

Mon Sep 15, 2008 4:03 pm

Unless a user knows where the folders are, and the specific names of the folders AND the images, how could they locate them anyhow? By default, all the directories on a remote server do not allow folder browsing. I'd just store a path to the images with a "hasAccess" bit field of 1 or 0, then from there run your query.
 
UberGerbil
Grand Admiral Gerbil
Posts: 10368
Joined: Thu Jun 19, 2003 3:11 pm

Re: Storing Images in a Database

Mon Sep 15, 2008 4:08 pm

The path and whatever access rights (eg public / private / top secret) you want to associate with each image (unless you're going to keep a separate table maintaining a many-to-many mapping of users and files, or at least group users into roles and do it that way). You also may want to store other metadata you might have for the image (eg for the purposes of your web code you might find it handy to pull things like the pixel dimensions out of the image and keep them in the db so you can more easily put them into tags).

You may not want to store the actual path for each image though, especially if you're going to have a lot of images in each directory. Just use a long integer, that you can then use to look up the actual path in another table. That way if you rename or move a directory you only have to change one record; saves some redundant text in the db too.

But you realize this kind of thing has been done a million times before for various CMS and "web gallery" systems that you could use instead.
 
titan
Grand Gerbil Poohbah
Topic Author
Posts: 3376
Joined: Mon Feb 18, 2002 7:00 pm
Location: Great Smoky Mountains
Contact:

Re: Storing Images in a Database

Mon Sep 15, 2008 4:54 pm

I just realized a little earlier that PHP doesn't work within the bounds of Apache, but rather within the bounds of php.ini or the OS.

The images I'm trying to restrict access to are for sale. So, it isn't a matter of roles as it is a matter of whether the user has purchased the photo. I think a many-to-many might be a little overburdensome, especially as the number of photos increases over time. Also, because I'm trying to sell these, it's important that that it isn't just that they're hidden from the users, but actually inaccesible unless the application pulls it in.

My idea here is that I'd have a table with one user per row and have a several text fields with a CSV list for each. I don't think that this will become too much of a nightmare as I only anticpate each user purchasing, at most, a handful of photos.
The best things in life are free.
http://www.gentoo.org
Guy 1: Surely, you will fold with me.
Guy 2: Alright, but don't call me Shirley.
 
mattsteg
Gerbil God
Posts: 15782
Joined: Thu Dec 27, 2001 7:00 pm
Location: Applauding the new/old variable width forums
Contact:

Re: Storing Images in a Database

Mon Sep 15, 2008 4:57 pm

titan wrote:
I just realized a little earlier that PHP doesn't work within the bounds of Apache, but rather within the bounds of php.ini or the OS.

The images I'm trying to restrict access to are for sale. So, it isn't a matter of roles as it is a matter of whether the user has purchased the photo. I think a many-to-many might be a little overburdensome, especially as the number of photos increases over time. Also, because I'm trying to sell these, it's important that that it isn't just that they're hidden from the users, but actually inaccesible unless the application pulls it in.

My idea here is that I'd have a table with one user per row and have a several text fields with a CSV list for each. I don't think that this will become too much of a nightmare as I only anticpate each user purchasing, at most, a handful of photos.

Still, that's something that's been done before. I imagine gallery2 + a plugin or 2 would do exactly that, for example.
...
 
titan
Grand Gerbil Poohbah
Topic Author
Posts: 3376
Joined: Mon Feb 18, 2002 7:00 pm
Location: Great Smoky Mountains
Contact:

Re: Storing Images in a Database

Mon Sep 15, 2008 4:58 pm

I'll take a peek around and see what I can find.
The best things in life are free.
http://www.gentoo.org
Guy 1: Surely, you will fold with me.
Guy 2: Alright, but don't call me Shirley.
 
emorgoch
Gerbil Elite
Posts: 719
Joined: Tue Mar 27, 2007 11:26 am
Location: Toronto, ON

Re: Storing Images in a Database

Mon Sep 15, 2008 6:55 pm

I'd just store the images in the database and be done with it.

Storing the images as a file saves a little bit of I/O and CPU overhead, which is useful when the image is getting accessed repeatedly (i.e. a logo at the top of every page, etc.). However, when you have images that have metadata about them also stored in a database, you then have to worry about keeping the image data and the image files in sync with each other. This is exactly what an database is designed to do, so why re-invent the wheel. Plus, with modern CPUs and efficient caching, the overhead is negligible.

I've never heard an argument that's a silver bullet to end the file-store vs. database debate, but most of what I've read has really come down to ease of development & syncing vs. performance. Having done development trying to keep file stores & databases in sync, my personal feeling is that unless there's a very specific reason to keep the file on the file store, just put it in the database and save yourself the headache.
Intel i7 4790k @ stock, Asus Z97-PRO(Wi-Fi ac), 2x8GB Crucial DDR3 1600MHz, EVGA GTX 1080Ti FTW3
Samsung 950 Pro 512GB + 2TB Western Digital Black
Dell 2408WFP and Dell 2407WFP-HC for dual-24" goodness
Windows 10 64-bit
 
notfred
Maximum Gerbil
Posts: 4610
Joined: Tue Aug 10, 2004 10:10 am
Location: Ottawa, Canada

Re: Storing Images in a Database

Mon Sep 15, 2008 9:06 pm

One thing is here it seems the only thing that is at all database like is whether the user has access to them or not and not so much the properties associated with the photo. Unless you are doing some serious database stuff or large datasets (>10000), often flat files work just as well as a real database.

I think gallery2 supports private albums that users have to be enabled to see, but if you are getting down to a per-photo thing then you may need to go something different.
 
titan
Grand Gerbil Poohbah
Topic Author
Posts: 3376
Joined: Mon Feb 18, 2002 7:00 pm
Location: Great Smoky Mountains
Contact:

Re: Storing Images in a Database

Tue Sep 16, 2008 8:00 am

notfred wrote:
One thing is here it seems the only thing that is at all database like is whether the user has access to them or not and not so much the properties associated with the photo. Unless you are doing some serious database stuff or large datasets (>10000), often flat files work just as well as a real database.

I think gallery2 supports private albums that users have to be enabled to see, but if you are getting down to a per-photo thing then you may need to go something different.

I'm not concerned about image properties other than height and width. I'll have a backup that stores the original images, so if I discover I need something later, I'll have the data source available to me still.

Eventually, I will have greater than 10,000 photos. As for users, I can't say. Like I've said before, I'm not concerned about physical access. I have the server in my possesion, and I'm out in the boondocks. Somebody will have to go through quite a bit of inconvenience to get to my server. Are pointers really going to be the best way to go still? Or, as emogorch stated, should I just store the files in the database with this taken into consideration?

I'll have two interfaces, one for the world to see, and one for me where I upload the photos. So, a public interface and an administrative interface. I'd really like it to be as simple as me going to the administrative page and point to the data source and batch upload and batch process the photos. I don't want to have to deal with albums. That's more than what I need. What I need is per photo access.
The best things in life are free.
http://www.gentoo.org
Guy 1: Surely, you will fold with me.
Guy 2: Alright, but don't call me Shirley.
 
cubical10
Gerbil First Class
Posts: 184
Joined: Fri Mar 03, 2006 2:52 pm
Location: Montreal

Re: Storing Images in a Database

Tue Sep 16, 2008 11:29 am

I would encourage you to store the images in the db to simplify system maintenance and DR.
With everything in the db, you can dump/export the db from one system to another. You do not have to worry about recreating file paths or updating pointers.
If you ever wanted to change from your local system, to using a hosted solution, you would just have to upload your db back up.


On a tangent, you can use Exif functions in PHP to pull image meta data during file upload.
And the GD functions to build thumbnails and/or resize images to a common standard so that no matter what the image size, its will display nicely in your layout.

This caught my attention.
titan wrote:
What I am really trying to do is prevent access via the Internet.

No matter how you store the images, at some point it will have to be reconstituted into a jpg/png and delivered to the browser. At this point, any user can simply right-click and "Save Image As".
So if you want provide your users the ability to preview an image before buying it, then you can either provide a super low-res copy with a watermark for preview (which you do with GD), or use flash.
 
titan
Grand Gerbil Poohbah
Topic Author
Posts: 3376
Joined: Mon Feb 18, 2002 7:00 pm
Location: Great Smoky Mountains
Contact:

Re: Storing Images in a Database

Tue Sep 16, 2008 2:52 pm

cubical10 wrote:
...
This caught my attention.
titan wrote:
What I am really trying to do is prevent access via the Internet.

No matter how you store the images, at some point it will have to be reconstituted into a jpg/png and delivered to the browser. At this point, any user can simply right-click and "Save Image As".
So if you want provide your users the ability to preview an image before buying it, then you can either provide a super low-res copy with a watermark for preview (which you do with GD), or use flash.

At that point, when the users have paid for the access to the image, I don't care what happens to it then. Right-click-save is fine. It's just that I want to prevent access before it's paid. I have always intended that there would be thumbnails with a watermark of some sort, which I would make as part of the batch upload/process, so that the users can see a preview before they purchase anything.

So, what is the best way to store the images? I was thinking bytea might be the best way to go, and I could use a prepared statement so I wouldn't have to go through the process of escaping the data. I know I'll have to store the image name and type in a separate field, and specify it when the image is sent.

Additionally, thanks for the links, cubical10. :D
The best things in life are free.
http://www.gentoo.org
Guy 1: Surely, you will fold with me.
Guy 2: Alright, but don't call me Shirley.
 
cubical10
Gerbil First Class
Posts: 184
Joined: Fri Mar 03, 2006 2:52 pm
Location: Montreal

Re: Storing Images in a Database

Wed Sep 17, 2008 8:25 am

I have very little experience with PostgreSQL, I (for no particular reason) prefer MySQL.
In MySQL, I would use a longblob data type.

In PostgreSQL, I think that you want to look into large objects.
Cubical 10
I only know enough to be dangerous.

Do ubuntu? pfsense
 
titan
Grand Gerbil Poohbah
Topic Author
Posts: 3376
Joined: Mon Feb 18, 2002 7:00 pm
Location: Great Smoky Mountains
Contact:

Re: Storing Images in a Database

Wed Sep 17, 2008 3:40 pm

cubical10 wrote:
I have very little experience with PostgreSQL, I (for no particular reason) prefer MySQL.
In MySQL, I would use a longblob data type.

In PostgreSQL, I think that you want to look into large objects.

After reading that for a bit, I have come to the conclusion that Large Objects are not how I would store the image data. I need to work with the image as a whole, and the manual makes it sound like it will stream the data and break it down into chunks, which in turn each take their own row. The image data will not be that large -- a few megabytes at most -- so bytea really sounds like the way to go now.
The best things in life are free.
http://www.gentoo.org
Guy 1: Surely, you will fold with me.
Guy 2: Alright, but don't call me Shirley.
 
IntelMole
Grand Gerbil Poohbah
Posts: 3506
Joined: Sat Dec 29, 2001 7:00 pm
Location: The nearest pub
Contact:

Re: Storing Images in a Database

Wed Sep 24, 2008 6:10 pm

titan wrote:
After reading that for a bit, I have come to the conclusion that Large Objects are not how I would store the image data. I need to work with the image as a whole, and the manual makes it sound like it will stream the data and break it down into chunks, which in turn each take their own row. The image data will not be that large -- a few megabytes at most -- so bytea really sounds like the way to go now.


10,000 images * a few megabytes (let's say 3) = about 30 GB of data stored. How important is the speed of this database? I'm no expert on database indexing and the theory behind it, but damn that's still a lot of data to sort through to get (typically?) one image out at a time.
Living proof of John Gabriel's theorem
 
titan
Grand Gerbil Poohbah
Topic Author
Posts: 3376
Joined: Mon Feb 18, 2002 7:00 pm
Location: Great Smoky Mountains
Contact:

Re: Storing Images in a Database

Wed Sep 24, 2008 9:51 pm

IntelMole wrote:
titan wrote:
After reading that for a bit, I have come to the conclusion that Large Objects are not how I would store the image data. I need to work with the image as a whole, and the manual makes it sound like it will stream the data and break it down into chunks, which in turn each take their own row. The image data will not be that large -- a few megabytes at most -- so bytea really sounds like the way to go now.


10,000 images * a few megabytes (let's say 3) = about 30 GB of data stored. How important is the speed of this database? I'm no expert on database indexing and the theory behind it, but damn that's still a lot of data to sort through to get (typically?) one image out at a time.

PostgreSQL allows me to create an index on a per column basis. I'll have to study this a bit more, but it seems similar to MySQL. In MySQL, however, I was able to specify multiple columns to be a part of the index, but when the query accesses the table it needs to search the columns sequentially.

For example, let's assume a table that has three columns and the first two columns are what make each row unique. An index is created for the first two columns. A query that is structure as:

SELECT * FROM table WHERE column1=1 AND column2=2

or
SELECT * FROM table WHERE column1=1


Will take full advantage of the index. In fact, MySQL automatically marks the primary key for indexing. I'll be able to access the data from the entire row without a problem, and theoretically quicker than if it didn't have an index. Whereas, a query structured as:

SELECT * FROM table WHERE column2=2

or
SELECT * FROM table WHERE column2=2 AND column1=1


will not take full advantage of the index. In fact, it won't take advantage of the index at all.

So, in short, my first column will be the unique ID of each image. Also, the files that will be accessed the most are the preview images. Those will be stored on the disk and not in the database. Again, I'll have to do some studying to make sure that PostgreSQL performs in a similar manner.
The best things in life are free.
http://www.gentoo.org
Guy 1: Surely, you will fold with me.
Guy 2: Alright, but don't call me Shirley.

Who is online

Users browsing this forum: No registered users and 1 guest
GZIP: On