Sherif's Tech Blog

Just another guy on the Internet with a keyboard…

Why You Need a Database

There are a lot of developers that start off building their applications with the notion that a database is only necessary if they have a lot of data to work with or that the data they have will be easier to manage if they can avoid the complexities of building and maintaining a database or dealing with a DBMS (Database Management System). In the area of web-based development, this is rarely the case. The reason for this is that web-based applications tend to grow very rapidly. This is easy, because there are billions of people with access to the Internet and virtually anyone with access to the Internet usually gains such access from a web-enabled device. Having access to the Internet has become synonymous with having access to the world-wide web. Since the number of potential users is so huge the potential for data is equally huge. Not only that, but beyond the sheer amount of data that maybe collected from users of the application software and stored for use by the system there is the factor of maintainability. Databases make organizing and maintaining long-term data easier. This comes in several forms. Without a database solution you have to worry about concurrency issues for replication. You would also have to consider race conditions, access time, permissions, and scalability among others.

Databases Are Overkill

For those who start off building small web-based applications or even trying to put together a tiny CMS (Content Management Systems) they sometimes fall victim to the illusion that having a very small amount data would mean that building a database for this data would be overkill. This is simply not true anymore. Today databases are easier than ever to build, grow, and manage. With lite-weight solutions like SQLite you actually improve on performance with small amounts of data and make it easier to manage. SQLite is actually a small foot-print library written in C that implements an embedded DBMS. It’s only a few hundred KB in size and implements most of the SQL standard. You can use it to store databases in memory or on disk and still get the full benefits that relational databases offer with a minimalistic foot-print and without compromising on performance for small data sets. It’s adopted by PHP, Python, Perl, Ruby and even Javascript as well as many other languages. So there really is no excuse to avoid using a database when the solution is widely available in so many popular platforms and especially in web development.

Databases Are Slow

This could not be farther from the truth. A relational database can maintain indexing for records across different tables. This means rather than looking through the entirety of the data set and then trying to expose some underlying structure in order to find a particular set of data the relational database takes advantage of composing structures as you build your data sets. These structures make things like fetching a record with a primary key much much faster than you would get by using a flat-file solution.

Lets examine the alternatives. Even if you had a very small amount of data – say just a few hundred lines of text. Even if the data structure was overly simplistic – we’ll assume each line represents what would be a single row in a database table. Even if the data will only ever be maintained by a single developer – you. You are still overlooking so many problems that are not easily solved by using a flat-file to maintain this data. First lets consider the race-condition. You have a script that opens a specific file on the server and appends a new line each time a record is added. The script can also open the file for reading and retrieve the entire contents of the file into memory. The script can then do any necessary sorting and filtering to return the required data sets to the user. The most apparent problem with this approach is the race condition. It is entirely plausible that two requests could be made simultaneously to the same script – one to open the file for writing and append a record and one to open the file for reading and retrieve the data. If the data is stored into memory before the line is appended the result is stale and potentially corrupt. If the new data is appended before the read, no problem. However, what happens when you want to delete a record. Now the problem is three-fold. If three individual requests all come in at the same time – one to read, one to write, and one to delete a record – it is now likely the case that your entire data structure has been corrupted. Remember that HTTP is built on a request-response model and no two requests are treated as if they are tied to any previous requests. So there’s no central point of control over your script’s ability to manage which process can access the data and to what extent.

In a DBMS, on the other hand, the control is transferred away from the script and to the central management system of the database. The DBMS then gets to decide how requests will be served and the order of treating the data. This creates more dependable data that has a far lesser chance of corruptibility. Now, it’s entirely possible that you may not be concerned the integrity of your data for a small application, but then you might as well not waste your time building it.

I’ll Use A File Now And Learn To Use A Database Later

If you’ve said this phrase it’s already too late. It doesn’t take a lot of time to get started with a database in the first place. If you’re using languages like PHP, Python, Perl, or Ruby you probably already have the necessary libraries installed on your system to work with a database. These libraries and drives are usually packaged with these software stacks as standard. It’s actually uncommon to not have some DBMS solution already available in most of these environments. So why would go out of your way to reinvent the wheel when the solution is already at your fingertips? Not only that, but it takes very little time to set these DBMS solutions up and get them to run smoothly on virtually any platform. You will probably spend more time trying to write a script that stores, retrieves, sorts, filters, locks, and validates data using a flat file then you would installing the DBMS and getting a simple schema started.

If you’re using PHP interfacing with a database has become easier than ever. It only requires a couple of lines of code to open a database connection to virtually any database you have a PDO driver installed and loaded for in PHP. So whether you’re using SQLite, MySQL, PgSQL, etc… you shouldn’t need to spend a lot of time learning how to interface with each of these databases if you simply stick with the PDO extension. You use the same functions regardless of the database. This is opposed to having to learn the individual database-specific extensions in PHP to interface with each of those databases. Not to mention PDO supports many of the popular new database features such as prepared statements and is a lot easier to learn and use than extensions like MySQLi.

PHP and Databases

Being a PHP developer, I also take notice of many PHP developers that tend to have the misconception that when they start using a database (usually it’s the case that their first database is MySQL) they should start by learning the old mysql extension in PHP. This is simply not true. Some of the underlying reasons this is widespread, however, is mainly due to the fact that the old mysql extension has been around for quite a long time in PHP and it’s fairly common to see a lot of PHP code demonstrating the use of a database in PHP using this extension. It’s also become familiar to a lot of old PHP developers and is bound to be present in their older applications. However, the use of the old mysql extension is highly discouraged for new development. It’s an old extension that’s no longer well-maintained and has been planned for deprecation for years. There’s no guarantee that if a new bug creeps up that someone will go back and fix it. This leaves your application vulnerable and exposed. If the code base gets large enough this might leave developers scrambling for migration path. Additionally, the extension does not support prepared statements or parameterized queries. This makes things like making sure you properly escape user data to avoid SQL injection, prone to error. The extension lacks in many areas that are not conducive to future development. Learning the old mysql extension first before you learn the new improved mysql extension in PHP or before you learn PDO will gain you nothing. In fact, it will cause you to have to unlearn some of the very poor design of the old extension and its implementation details in order to become more accustomed to the newer extensions.

Some developers also complain that PDO seems too complicated or more difficult to use than the old mysql extension. This might come from the lack of understanding as to what PDO even is or how its used. Since PDO can only be used with the newer OOP features of PHP (you have to use objects and methods instead of procedural-style functions) it makes it seem unapproachable or even scary to developers who aren’t used to OOP in PHP. There is also the idea that PDO has a lot more features due to its vendor-agnosticism and the fact that requires further configurations such as installing and loading the individual drivers needed for interfacing with your specific database (where the drivers aren’t already packaged or loaded). I can understand the intimidation, but most of this has been alleviated with new versions of PHP coming pre-packaged and loaded with most of the popular drivers and the documentation offering up examples that are now easier to follow and get started with. Most of the intimidation is actually coming from having to unlearn old habits that older extensions like the old mysql extension once taught.

Once you get past the initial intimidation phase and actually get started with a PDO and with a database you’ll find that it doesn’t take nearly as much time as you’d think to get up and running. Most of the reservations people have are 90% of what’s holding them back. Not that the investment to get started is actually that significant. Beyond that you’ll find that learning to build on data normalization not only make development easier, but makes your users happier. When you can organize and maintain data that’s more clearly structured and accessible you can serve your users more effectively and efficiently. That will keep users coming back and eventually help you grow your application!

Load Balancing Software as a Service

I’m sure many of you have seen this statue before, perhaps not the very same one in the picture, but possibly similar statues around the world. This one is located in New York City.

Statue of Atlas in NYC

This particular statue is the Titan Atlas (a God from ancient Greek Mythology) who was supposedly burdened with carrying the weight of the world – or the weight of the heavens – on his shoulders as a punishment from Zeus. Whether it was the weight of the world or something else is unclear, but most people seem to follow this same observation. In general it’s nothing more than a myth, but the lesson history teaches us is that it constantly likes to repeat itself. Clearly, no one can bear the entire weight of the world on their shoulders just like no one computer can either. If you are running SaaS (or Software as a Service) you are online 24/7 and so is your service. The problem is there are over two-billion users online (or with Internet access) today. What happens when too many of those users all start using your service at once?


What Is Load Balancing


The idea behind load balancing is that a single machine can only handle so much work at one time and you can only go vertical for so high. Notice that even in large cities you can only build so high before you have to start building out. Since on the Internet virtually anyone can be using your server at any time you run the risk of overloading without warning. If too many users all send requests to your server too quickly, the server will reach a point where the load is higher than its capacity and eventually crash. This particular vulnerability of typical client-server relationships on a network is exploited by what is commonly referred to as a DDoS attack or a Distributed Denial of Service attack. Basically, a number of clients (sometimes a bot-net controlled by one or more users) will attempt to send a lot of requests to a server or number of servers very fast in order to overload the server and prevent its intended users from being able to access the service. Sometimes this is done just to destabilize the service running on the server or for other malicious intents. There are ways to mitigate DoS attacks with firewall software/hardware or through other means depending on the service, but not all DoS attacks are malicious or even intentional in nature. Google, for example, experienced what was at first glance considered a DoS attack on its search service during one afternoon on June 25th of 2009. This actually wasn’t a malicious user or users at all. It was the world receiving the tragic breaking news of the death of Michael Jackson. Literally, millions and millions of users from all around the world flooded Google Search all at once with the same search phrase “Michael Jackson”. Google had never seen such a tremendous amount of traffic coming in all-at-once on a single search query, before, so their first thought was “ohnoes, we’re getting DdoSed!

Scaling Out - SaaS

Scaling Out - SaaS


Why Do I Need It


The fact remains that any number of users can suddenly surge the number of requests coming in to your servers at any given time and whether that is malicious or not is unimportant. What is important is that you are better prepared to handle such situations so that your service will suffer as little downtime and degradation as possible. So load balancing allows you to distribute the load on a particular service or services over a larger array of resources. It’s basically making your service, as a whole, more tolerant of failure by being able to efficiently make use of all available resources.

If you are running any kind of high availability service over the Internet you need load balancing. Though, even small applications with just a few thousand users can benefit deeply from load balancing, as well. The only potential down-side is that you may need more than just one node to it. This isn’t always necessary as load-balancing can come in many shapes and sizes. For example, you might be doing load balancing on the same host node using multiple guest nodes on the same machine. All of the major services you probably use on a regular basis like your email, search engines, or popular social networking apps all make use of load balancing because it keeps things running a lot more smoothly as the number of users grow. If you’re not on-board with this yet – you probably should get on board quick.


How Do I Use It


There are few broad categories you can place load balancing techniques in. The easiest form of load balancing relies on existing system already built on top of how most systems function over the Internet (or large networks in general) and that’s DNS. DNS is a distributed system so it relies on multiple components in the network to do their job in order to make things more efficient. It reduces bottle-necks like those created by routing enormous amounts of packets across the planet in fractions of a second. Like most complex systems everything starts off small and simple and grows both horizontally and vertically, but at the core the protocols are fundamentally very simple.

DNS Load Balancing is simply relying on the DNS system to take care of the most basic problems for you. The way this works is you set the DNS record for a particular domain name to multiple IP addresses (usually one for each server) using low TTL (or Time to Live). Since DNS is cached at various levels this makes things like geographical loads efficient for services like name servers. A name server tells the DNS where to send the request for a particular domain name and can route packets to different locations depending on the geographical origin of the request thus alleviating network latency and allowing packets to travel shorter distances. Once the request comes in and is routed effectively the DNS is cached at multiple levels so that future requests are made to the same place. This can be cached at the local level, the ISP level and other levels in the parent zone. The name server then doesn’t become a bottle-neck since not every single request has to rely on that name server entirely. There is a TTL involved that will let the caching servers know when the cache has become stale and that it’s time to refresh. Also when requests to a particular server are no longer getting through the DNS server will know to try a different IP. So if you have different servers with different IPs in the DNS record that ultimately means if one server becomes unresponsive (potentially having gone down) the load is directed to a different server. The inherent problems with this approach are that it isn’t making very efficient use of all of your resources. It doesn’t take into account which servers are currently busy and if the DNS record has already been cached to a server that is now down you end up potentially being stuck with a poorly responsive server until the cache is refreshed. Additionally, you are exposing your infrastructure to the outside world by revealing the public IPs of your servers with no way to control the flow of traffic to an internal network. It’s very easy to have an unstable system this way. Most services that use this approach are usually just creating what is known as mirrors (servers that back each other up so that in case one goes down a backup can still be reached).

Software Load Balancing is another approach to solve some of the short-comings of the DNS offloading techniques described earlier. Software load balancers attempt to keep track of the available resources and when an incoming request is received it determines how to best allocate those resources in-order-to service that request. The benefits of this technique are that you don’t have to reveal your network setup to the outside world. Everything can be done on the internal networking configuration setup (whether that’s a local area network or otherwise), or in other words, you won’t expose your communication channels directly. Also, you have a tighter hand on security and distribution since you can more easily control the flow of traffic over the network. Some examples of common open-source load balancing software are Pound, Varnish, mod_proxy for Apache’s httpd, and Gearman. There are all sorts of nifty ways to balance the load across your network. You can have the load balancers poll the servers and check on resources like CPU usage, available memory, storage space, network traffic or open TCP connection, etc… The load balancer can then use this information to figure out how to best direct the incoming requests and serve up the responses as quickly and as efficiently as possible. There are still a few problems inherent to this technique depending on how you use it. If you’re only relying on a single machine you have a single point of failure. If the host node goes down the load balancer and all of your resources go with it. If you’ve only got one load balancer and multiple servers you still have a single point of failure. Additionally the load balancer itself can be DoSed given an attack of enough magnitude and proficiency. Not only that, but you have to worry about things like session storage consistency across multiple servers, file-system access, database synchronization between different database servers, and some network bottle-necks that might not always be easy to resolve with load balancing – to name a few.

Hardware Load Balancing there are some hardware load balancers as well. You can actually buy very expensive firewall/routers that take care of many of these things for you. Most people usually just setup a dedicated node or two with software load balancers that pretty much do the same thing. These hardware load balancers might do a better job of handling security and high bandwidth loads like Cisco’s ASA, but they do come with a heavy price tag.


Some Load Balancing Tips


There are some pretty common approaches to some of the problem inherent to distributing a service over multiple servers. For example, take your session storage as the most obvious problem. If you’re using PHP you are probably using the built in session handler, which makes use of file-based sessions. If you have users being directed to different servers by the load balancer you end up with the user having multiple sessions across those servers (that might be a little problematic for your application and annoying to the user). Some people will try to avoid this by creating what’s called a sticky session. Once the session is generated for that user they’re sent a cookie that lets the load balancer know upon subsequent requests to direct the user to this particular server. There are a few minor problems with that, but nothing you couldn’t work out through a well-planned architectural approach. Another way to approach this is by creating a centralized session storage server where all the requests will look for the session. Depending on your infrastructure this may or may not be a good idea and keep in mind it also creates a single point of failure. For example, if your servers are built on stacks (you have several software-based servers running on the same node like a webserver, database server, application server, etc…) it takes some tinkering to configure each stack to work from a centralized session storage. You can use something like Redis where you can have master/slave replication across all stacks. This takes a little less configuration and puts the dynamic into the software stack layer – thereby removing it from the load-balancing layer.

The other obvious problem is file system storage. If you allow your users to upload files to your server, or you store large amounts of files that your application relies on heavily, there needs to be some system whereby your application layer can access those files considering the load balancing may send requests to different servers. Again there is a centralized approach like with session storage, but even with a replication approach – to avoid the single-point of failure down side – you might create the problem of over redundancy. If your servers are set up in stacks having four or five copies of each file (or more depending on how many servers you have) on each server stack is a bit of a waste, especially if you’re already using RAID arrays for redundancy. Even if you have a centralized set of servers for storage you still face the problem of network overload. For example, consider that if your backbone bandwidth capacity is at 100Mbps but your central network bandwidth capcity is at 10x100Mbps you eventually create a bottleneck with increased usage as your backbone can only serve up to 100 megabits per second of traffic at any given time.

Using a CDN (or Content Delivery Network) is one solution often used when large amounts of files need be shared across a network, but this can also be a bit costly depending on your needs. In its simplest form a CDN is really just a group of servers that store files or data objects for you and replicate them across multiple nodes allowing many other servers on the network to access that data with improvements in caching and high bandwidth to reduce latency. The servers in the CDN clusters are usually strategically located on the edges of the core network to minimize the bottlenecks involved in the centralized network loop. So you are redirecting the traffic to access file storage away from the central network and off to the edge servers expanding on bandwidth and minimizing on bottle neck traffic. This solves both the single-point of failure problem as well as taking the complexity mechanism away from the server stack which can ultimately help reduce loads and create more efficient load balancing. Most services that utilize CDNs are usually ones that need to offer high-bandwidth access to a large user base with consistency. For example, a service that offer Hi-Definition video streaming, large photo sharing web sites, or other media services with high availability needs. You don’t always have to build this infrastructure yourself. You can rely on services like amazon Cloud Front which is a pay-as-you-go CDN service offered by amazon. There are many other competitors, of course, that can offer cheap CDN solutions. Depending on the sensitivity of your data this may or may not be an option for your particular SaaS needs. Still something to consider.

Besides just file storage you probably have a lot of database concerns in a system that scales horizontally, as well. If you’re just using a single LAMP stack with little more than PHP, MySQL and Apache running your back-end it might seem easy to scale wide at first. The problem you’re likely to run into head-on is the data-replication across your MySQL servers. The database is almost always the biggest bottleneck in SaaS. It usually contains tons of data that virtually every one of your users will access with each hit. There’s only so much traffic a single database server can handle, but setting up two or more database servers can show some significant improvement. Your load balancer can also play a role in this. There can be data object caching mechanisms in place to ease off some of the load for the most frequented queries. There can also be network latency issues to deal with once you have several database servers all replicating (especially if these servers are geographically spaced out across different data centers, cities, countries or even on different continents). Chunking is definitely not something I’d advice. It throws way too many variables into the equation and presents more problems than solutions – for the most applications.

How To Build A Photo Sharing Application On The Web The Right Way

Personally, as a web developer, I’ve come across a number of clients that seem interested in doing some sort of web-based photo sharing application like flickr, or imgur, or photobucket. These are all pretty popular services on the web that allow you to share your photos with friends, family, colleagues, etc… They seem to be extremely popular and there’s no doubt that it’s evident people love sharing their photos online. Just take a look at facebook; possibly the world’s largest online photo sharing application that claims to get over 100 million uploads per day from it’s now more than 800 million users. With cameras found standard in such personal devices as phones, notebook computers, desktop computers, various other hand-held devices, and even (and don’t ask me why) TVs it’s no wonder we find it easy to store lots of digital photos and inevitably share them with others.

So I had to think long and hard about how I’d build an application or service like this so that it made good use of photos and made them easier to share and more accessible. The first thing that came to mind was checking out all the features these other services already had to offer and how they put them to good use. flickr allows you to do stuff like geotagging where you can tell people where the photo was taken. I remember either facebook or some app I may have tried a long time ago being able to do this as well. Since I log into my facebook account maybe two or three times a year I couldn’t say for sure, but what I do know is that facebook certainly got one thing right and that was not taking their users’ demand in wanting to share photos with friends for granted. When you’re able to point out who is in your photo that makes the information just that much more valuable and apprises the application for making photos more useful and accessible. There’s also a search value in tagging. But it can be pretty boring for a user to have to sit there and manually enter in all the information about each photo so we can rely on things like Exif where embedded information about the photo can be extracted by computers. You can get such information as GPS coordinates of where the photo was taken, a time stamp of when the photo was taken, the camera make and model that the photo was taken with, whether not the flash went off when the photo was taken, and even various other things such as focal length, exposure time, shutter speeds, etc… You can learn a little more about the Exif specifications here. However, keep in mind that not all cameras provide this format and not all of them are equipped to provide all of the different parts in the Exif header. Newer phones like the iPhone 4 are GPS capable and can embed GPS data into your photos if the GPS is turned on. There are also many digital cameras that either come with GPS devices embedded or can be purchased separately. Some other features you might want your application to have to make the process of having the user input information about each photo easier is facial recognition. This doesn’t have to be so sophisticated that it can automatically detect faces found in other pictures and tell you who’s who, but it can be helpful to let the software detect if a face exists in the photo and highlight it so that the user can simply type in who each person is for tagging purposes.

There are also some potential uses that I’ve found for OCR (or Optical Character Recognition) in photo sharing. If you can manage to extract enough significant textual data from the image you might be able to make certain aspects of search easier to locate photos. This is probably not going to be easy given that what little time I did spend fooling around with various OCR software proved that it has many setbacks and is clearly in the very early stages of development. Mostly OCR has trouble detecting text if the font size changes through the image, if the text encounters large skewed angles or if the text is rotated so that its orientation is not top-to-bottom and left-to-right. It’s also difficult to detect hand-written text, or text surrounded by other images, logos, or with too much depth or noise. There’s also quite a challenge trying to detect text written in various languages that don’t use the Latin alphabet. If it’s not a scanned image coming straight from the page of a book using a single evenly-spaced sans-serif font, with a fixed font size of around 12-20pt it will prove rather difficult to get any decent results from the OCR software.

So to give you an idea of what some good features of a photo sharing application might look like I put together a small working demo. My example makes use of most of the features I’ve discussed here, but leaves much to be desired, of course.

You can see the working Photo Application Demo here.

Here is a sample photo with embedded Exif information including GPS to demonstrate.

Here is a sample photo that demonstrates the applications ability to utilize some OCR techniques.

A Note About User Experience

OK, so I wanted to make sure this demo illustrates some of the basic functionalities a user might expect in a photo sharing app. The first thing you’ll notice is that uploading large photos and especially a lot of them can be a boring thing to do over HTTP. That’s because HTTP is built on a request/response model. You have to send a request first and then you eventually get a response back. Now if your request happens to be a 10 MB photo or even worse a 250 MB video (or even worse you don’t have high-speed Internet or your ISP offers lousy upload speeds) well… that’s a long wait with no indication at all to the user as to what’s happening on the other end. What’s even worse is if the user is uploading a file they aren’t aware is too large for you server to accept that can be a lot of waiting around for nothing. So I used some javascript combined with a script I found online at phpfileuploader.com and with a few modifications for security and improved user experience I hacked up a somewhat better interface for the user to deal with uploading multiple photos.

One thing you don’t want is to prohibit the user from being able to do anything else while they’re uploading their photos. Pop-up windows are just annoying, in my opinion at least, and forcing the user to wait until the entire upload is done before they can do anything else in the window is even worse. So if you try out my demo you can see that it doesn’t prevent you from browsing other photos on your computer to select for upload even while it’s doing the uploading simultaneously. You can even cancel an upload mid-way or cancel all pending uploads at once. It will also alert you ahead of time if you’ve selected too many files at once or if your images are too large or of the wrong file type. However, not all of these features are entirely reliable for security reasons as the user can easily bypass them. But they are their to improve the user experience. All the real security work is actually done on the server side. Even if you were to rename foobar.exe on your computer to foobar.gif and managed to upload it the server will still detect that it is an incorrect MIME type and reject the upload. This can be done relying on PHP’s File Info extension where it can check the file’s MIME type from the server’s supplied Magic MIME file.

Now, keep in mind that tracking the progress of the upload is just half of the battle. There’s also the part about viewing your photos after you’re doing uploading them. In my demo I used a javascript library called easybox which is based on the lightbox framework, but it plays nice with jQuery and seems to work a lot more smoothly in my opinion. You can download easybox from Google code and try it yourself. On the top right hand side of my demo where you see your gallery you can click on any of the thumbnails and it will use easybox to nicely let you view all of the photos in your gallery in a slide-show fashion without ever having to leave the page. You can also, of course, easily get a permanent link to each of your photos to share with others from the list of recently uploaded files below that. Your session will expire after 30 minutes of inactivity, however, and you will no longer be able to delete those photos. If you have cookies disabled you won’t be able to see what you’ve uploaded, but your photos will remain on the server indefinitely.

A Note About Using Javascript or Flash

One thing I wanted to point out was that your applications should definitely work better with javascript or flash or even Java Applets or whatever client-side components you may want to use to improve the functionality and user experience of your application. However, you should also consider that if your applications can’t work at all (if even with a degraded user experience or a limited use of features) then you’re not degrading your web applications in a graceful manner. In my demo, for example, I made sure even though I’m using a lot of javascript and even some flash to make the upload process a lot more user-friendly you can still use the application even without the javascript or if you don’t have flash installed. You can test this yourself and I even tested it on the Links web browser (which is a completely text-based browser with no javascript capabilities) and I could still manage to uplaod my photos and download them just fine with zero problems. This isn’t always possible, or easy, for every application, but it’s definitely a good idea to at least put in the effort to gracefully degrade your applications so that they can be somewhat usable to browsers of lesser capabilities. I even managed to get it to work on my phone (a Samsung) which is a pretty horrible phone with an even more horrible browser, but hey having a bad user experience is still better than having no experience at all.

A Note About Links

So I wanted to emphasize a little on how the link structure of an App like this should work. One thing is you certainly want to be able to provide permanent links to the uplaoded photos so your users can share their photos with others. The link should be as short as possible. If your links are 500 characters long it’s probably not going to look that great when you paste them in an email or an IM window for a friend to take a quick look. However, facebook and flickr don’t seem to mind too much about how long the link is. I do have to point out, however, that they probably store billions of photos and my system would certainly not scale for them. With that said in my demo you’ll notice every photo gets a random five character alpha-numeric (case sensitive) link that is directly pointing from the application’s web root. This is the same system imgur uses for their gallery links as well. Except that I noticed a few deficiencies in their method. For one thing they don’t seem to care much about the extension you use to directly view the image. For example, if I upload an image to imgur I might get a link that looks something like http://imgur.com/abcde which would give me a web page with my photo and some information about it (much like you see in my demo) and the direct link to my photo would probably look something like http://imgur.com/abcde.jpg or whatever the file extension was. However, if I were to visit http://imgur.com/abcde.gif or even http://imgur.com/abcde.pngabc I would still be able to see my image. However, I can’t go to http://imgur.com/abcde.exe because that seems to give me an image stating the requested image was not found or has been deleted. Upon some investigation I noticed that their servers return the Content-Type header based on whatever the extension you supplied is as long as it starts with an extension they accept such as jpg/gif/png but it doesn’t matter if it’s proceeded by anything else. This is actually pretty bad, because the file still comes back with the same exact MIME type I uploaded it in. So clearly they aren’t providing the same image in various formats just conforming to some loosely thought-out rewrite rules. Basically I have similar features in my demo where the webserver (in my case Apache) uses rewrite rules and conditions to verify the requested URL and route the request to the proper PHP script and with a little magic you have access to all of your photos from the webroot even though the image files themselves aren’t even stored in the same physical directory as the webroot on my server. So far this demo has only been up for a couple of weeks from the date of this blog post and at around 1,000 uploads and 30,000 views it seems to be reasonably responsive enough that it proves scalable with a little work. I’m using GOCR for the Optical Character Recognition stuff, which is an open source tool developed under the GNU Public license and you can visit their website here to download it or to get more information if you’d like.

A Final Note About Photos

Well, that about covers what I wanted to say about sharing photos on the web and building applications that can do this nicely. Just keep in mind there is a lot to be done with photos that we are yet to uncover. So be prepared to make some good use of these features in building your own applications. I hope my demo gave you some ideas to work with. They are all feasible and not incredibly difficult to implement as you can see this brief demo only took me a few hours of work to put together and works rather well for it’s purposes. Sorry for the horrible interface though that’s one thing I didn’t have time to actually work on. Do let me know what your thoughts are on this subject and if you have any photo applications you’ve built or ideas you’d like to share.

Using PDO with MySQL in PHP

The PHP community has been making an effort to steer people away from the old mysql_* functions (an extension that is no longer well maintained by core PHP developers) and encouraging the use of newer and more feature packed interfaces like MySQLi and PDO. MySQLi offers both Object Oriented and Procedural style coding; PDO does not and as far as I know there are no plans for that to change. So if you aren’t familiar with the OOP paradigm this is a good time to start in case you plan on using PDO in future development. This helps encourage more modular development as well. This is important where sometimes your implementation details can be hidden. The old mysql extension has been discouraged for developing new applications for a while now and since MySQL is a very popular Database Management System a lot of developers used to the old interface have been giving some push-back about switching. Personally, I must admit that I have been using mysql_* functions for years with MySQL databases and so I can understand some of the hesitation in switching, but I’d like to address some of the benefits and trade-offs involved. At least the ones I’ve found to stand out most during my encounters with other developers this year.

PDO Is Database Agnostic

This is probably the driving point of most developers – at least the one I’ve seen come up frequently since last year. The PDO extension in PHP is nothing more than a means to interface with various database-specific PDO drivers. In PHP the PDO extension supports a vast array of databases like Cubrid, FreeTDS / Microsoft SQL Server / Sybase, Firebird/Interbase 6, IBM DB2, IBM Informix Dynamic Server, MySQL 3.x/4.x/5.x, Oracle Call Interface, ODBC v3 (IBM DB2, unixODBC and win32 ODBC), PostgreSQL, SQLite 3 and SQLite 2, Microsoft SQL Server / SQL Azure, and 4D. This means you can use the same PDO functions to issue queries and fetch data from any of these databases. Some people were a little confused about this at first and seemed to think this implied you would not have to rewrite your SQL. This is certainly not the case. The SQL is still dependent on your database. However, you will not have to rewrite your PHP code should you chose to switch databases or use different databases in the same project or just reuse a PHP abstract database class in another project with a different database. The PHP code works the same way whether I’m using MySQL, PgSQL, or any other database. Obviously my database’s SQL syntax may vary here and there, but I’m not required to do large refactoring of my PHP code.

Now, in all fairness, the likely-hood of having to use different databases in the same project or switching databases in any project are fairly slim. So some developers don’t see this as such a great benefit. However, should that be the case you suddenly find yourself in a world of hurt if your application relies very heavily on the database and you have thousands of lines of code to rewrite. Which is why it makes sense to just learn an extension like PDO and use it everywhere so that you never have to relearn another extension should you start a project that uses a different database than what your existing extension was built for.

What PDO Is Not

Some developers also seem to think PDO is a full-blown database abstraction layer or even an ORM. Let me assure you that it’s not. You can’t actually perform any database functions using the PDO extension by itself. You have to install the database-specific PDO driver to access the database you want. These drivers basically implement the PDO interface and thus you get to use those database specific features as regular functions of the PDO extension. PDO is not a magical solution or replacement for you DBMS. It does not provide a tool for userland it simply provides an access layer in userland. So if you had any of these misconceptions before, now is a good time to get rid of them.

Developers Migrating to PDO for the MySQL Database

If you’re constantly developing with MySQL databases like me you probably either still use mysql_* functions in PHP or have tried or even switched to MySQLi. If you’re one of the few that have taken the leap to PDO great! If you still have a few reservations consider that whoever told you PDO is too complicated or harder to learn or use is lying to you. They may not even know they’re lying, but you ARE being lied to. First of all PDO is no more difficult to use or learn than MySQLi or mysql_* or any of the other database extensions in PHP. The fact remains that mysql_* functions have been in long-standing plans for deprecation and they will deprecate it. Eventually when mysql_* functions are gone you will be in a much more difficult position were you to continue developing new applications with it than if you were faced with legacy applications that still had a great deal of code relying on the old extension.

The truth is the old extension for MySQL is no longer well maintained. You’d be lucky if any of the core developers went back and made any major improvements or fixed any real bugs (should they happen to arise). For the most part the new MySQL Improved extension MySQLi is still being worked on and can expect further development, but with the old extension it’s just not likely to happen. There isn’t anything fundamentally wrong with the old mysql extension. The purpose of this blog post isn’t to knock on mysql_* functions, but rather to encourage developers to explore the pros and trade-offs that an extension like PDO has to offer. This mainly because I feel a lot of the developers I’ve come into contact with that have reservations about switching to PDO seem to have been completely mislead or just never bothered to learn about what PDO really does.

If you’d like a good tutorial to follow on migrating I recommend taking a look at the PDO Tutorial for MySQL Developers from the ops at hash php (##PHP on freenode on IRC). If you aren’t sure about just how easy it is to use PDO with MySQL I drafted up a small working example of using PDO with MySQL here as well. It’s using the MySQL world sample database and both the code and a working example are provided.

One of the first gatchyas you want to look out for is specifying the character encoding for your connection. In my example above I’m using MYSQL_ATTR_INIT_COMMAND to SET NAMES and SET CHARACTER SET telling MySQL to stick to UTF-8. This is so I don’t break the encoding during transport. Of course you still have to remember to specify the encoding to the client upon output, but this just demonstrates how you can go about making sure the connection to MySQL server uses the proper encoding. The escaping rules should be updated accordingly. Remember you had to do this with mysql_set_charset() in the old MySQL extension.

The first thing you notice when you start using an extension like PDO or MySQLi is that when you use prepared statements where you are binding parameters you don’t have to worry about escaping rules that are otherwise more pertinent in the old string concatenation style used to build SQL queries in mysql_* functions. PDO is actually separating the SQL from the parameters bound to that SQL. This not only prevents SQL injection (which mysql_real_escape_string() could do just as effectively), but it makes it easier for the developer to work the user data into their SQL. This an underlying benefit I’ve come to admire very much with PDO.