Transcript PHP_Bible
PHP Bible Chapter 16: Choosing a Database for PHP _______________________________________________________________________________________________________________ PHP Bible, 2nd Edition 1 Wiley and the book authors, 2002 Summary What is a database? Why a database? Choosing a database Advanced features PHP-supported databases Database abstraction (or not) Our focus: MySQL and Oracle _______________________________________________________________________________________________________________ PHP Bible, 2nd Edition 2 Wiley and the book authors, 2002 What is a database? Perhaps the single greatest advantage of PHP over similar products is the unsurpassed choice and ease of database connectivity it offers. PHP supports native connections to a number of the most popular databases, open source and commercial alike. Almost any database that will open its API to the public seems to be included. For any unsupported databases, there’s generic ODBC support A database is a separate application that stores a collection of data Each database has one or more distinct APIs for creating, accessing, managing, searching, and replicating the data it holds _______________________________________________________________________________________________________________ PHP Bible, 2nd Edition 3 Wiley and the book authors, 2002 Why a database? If you’re going to the trouble to use PHP at all, you’re likely to need a database sooner or later. Even for something small, like a personal Weblog, you want to think hard about the advantages of using a database instead of static pages or included text files Maintainability and scalability Having PHP assemble your pages on-the-fly from a template and a database is an addictive experience. Once you enjoy it, you’ll never go back to managing a static HTML site of any size. For the effort of programming one page, you can produce an infinite number of uniform pages. Change one and you’ve changed them all. Portability Because a database is an application rather than a part of the OS, you can easily transfer its structure and contents from one machine to another or even from one platform to another (we moved our MySQL database from Windows NT to FreeBSD with very little effort) _______________________________________________________________________________________________________________ PHP Bible, 2nd Edition 4 Wiley and the book authors, 2002 Why a database (cont.) Searching Although it’s possible to search multiple text files for strings (especially on Unix platforms) it’s not something most Web developers will want to do all the time. After you search a few hundred files, the task becomes slow and hard to manage. Databases exist to make searching easy. With a single command, you can find anything from one ID number to a large text block, to a JPEG image. In some cases, information attains value only when you put it into a searchable database (e.g. a long list of movies with actors becomes much more valuable if you can search for all of the movies that contain a particular actor, etc.) _______________________________________________________________________________________________________________ PHP Bible, 2nd Edition 5 Wiley and the book authors, 2002 Why a database (cont.) Security A database adds another layer of security if uses with its own password or passwords If you have a content site with a large number of visitors, a smaller number of writers, and a handful of editors, you can easily set database permission levels for each group so that visitors can just look at the database content, writers can browse and change only their own entries, and editors can browse/change/delete anything on the site _______________________________________________________________________________________________________________ PHP Bible, 2nd Edition 6 Wiley and the book authors, 2002 Choosing a database Although databases (even relational ones) have been around for a long time, they were quite expensive or limited in functionality until very recently You may not have a choice for the database used for your site You may have legacy databases to work with Your OS may dictate what DB is used (or cannot be used) _______________________________________________________________________________________________________________ PHP Bible, 2nd Edition 7 Wiley and the book authors, 2002 Choosing a database (types of databases) There are 3 main types of databases used with PHP Flat-file: (e.g. Gnu DBM and Berkeley DB) are used by or within other programs such as e-mail servers. They provide the lightest weight and fastest means of storing and searching for data like username/password pairs or dated e-mail messages. Relational: most common and widely used type of database. Generally speaking, databases that speak fluent SQL can be considered relational It’s important to note that some databases, although relational, are not intended to be used in a multi-user environment (e.g. MS Access & FileMaker Pro) Objected-oriented/Object-relational: new and still developing models of data access. Intended to work smoothly with objectoriented programming _______________________________________________________________________________________________________________ PHP Bible, 2nd Edition 8 Wiley and the book authors, 2002 Choosing a database (APIs) There are two generic standard APIs for database access: Open Database Connectivity (ODBC) and Java Database Connectivity (JDBC). ODBC is closely associated with Microsoft and JDBC is most closely associated with Sun. Many other companies have incorporated these standards into their own products. ODBC and JDBC support are more or less mutually exclusive. There are bridges which allow applications to access an API of the other type but they generally perform fairly slowly Some databases provide their own APIs which are much faster since they have fewer layers to go through. They may also support ODBC or JDBC but access should be through their own APIs if possible Although ODBC and JDBC are slower than the native APIs, they do have the advantage of being an open standard so PHP code written to the generic APIs will mostly work with any ODBC or JDBC-compliant database so the backend can be changed without modifying any of the code A better solution would be to write PHP-wrapper functions around the database access functions which could then be swapped out to another database-specific API while modifying only a minimal amount of PHP code _______________________________________________________________________________________________________________ PHP Bible, 2nd Edition 9 Wiley and the book authors, 2002 Advanced features to look for GUI (Graphical User Interface) Databases vary enormously in their user interface tools. Choices range from command-line interactions to massive Java-powered development toolkits. You should look for the lightest interface that meets your needs because a GUI can add substantially to overhead costs Some databases have available a web interface for administration and maintenance which may be available from the manufacturer or from a third-party. MySQL has several freely available interfaces (notably PHPMyAdmin from www.phpmyadmin.org) Subqueries A subquery or subselect is an embedded select statement like SELECT * FROM table1 WHERE id IN (SELECT id FROM table2); This can be worked around if not directly supported by the database, but support can save some time _______________________________________________________________________________________________________________ PHP Bible, 2nd Edition 10 Wiley and the book authors, 2002 Advanced features to look for (cont.) SELECT INTO A handy feature allowing you to move data from one table to another frequently SELECT INTO table2(col2,col3,col7) lastname, firstname, state FROM table1 WHEE col5 = NULL; INSERT INTO table2(col2,col3,col7) SELECT lastname, firstname, state FROM table1 WHERE col5 = null This should be avoided except under very special circumstances since it will usually result in unnecessary data duplication Complex joins A join is a way of searching for or retrieving information from across multiple tables using shared values (foreign keys) to match up the tables SELECT * FROM table1,table2 WHERE table1.id=table2.table1_id Join operations can be specified as left or right, straight or cross, inner or outer or self. Not all of these may be supported by all databases. _______________________________________________________________________________________________________________ PHP Bible, 2nd Edition 11 Wiley and the book authors, 2002 Advanced features to look for (cont.) Threading and locking Threading and locking are very important for multiple-tier sites and two-tier sites that have many contributors. They prevent two database calls from conflicting with each other (e.g. preventing 2 people from purchasing the same seat at an event at the same time) Transactional databases This is a database design that seeks to maximize data integrity. The transactional paradigm relies on commits and rollbacks. Transactions that are concluded successfully will be committed to the database. Those that are not successfully concluded will not be saved (rolled back). This is especially important if you have queries which update multiple tables at about the same time and are dependant on each other (e.g. updating bank account data) _______________________________________________________________________________________________________________ PHP Bible, 2nd Edition 12 Wiley and the book authors, 2002 Advanced features to look for (cont.) Procedures and triggers Procedures are stored, precompiled queries or routines on the database server. A common procedure would be one that selects out all the e-mail addresses of customers who make purchases on a particular day. Triggers are procedures that occur when some event is registered by the database (e.g. sending an account statement e-mail to customers every Sunday). Triggers are rarely supported in databases Indexes Indexes are a way to speed up searches of large data sets. Indexes maintain the equivalent of card catalogs to the fields you specify If you have millions of customers and you wanted to find the ones whose last name were Smith, without the index the database would have to look at each of the millions of records comparing the last name field to Smith but an index on the last name field would allow a quick search on a small subset of the database Indexes should not be used for every table or for every field in a table since they slow down writes into the database and they don't speed up every type of query. Indexes also can take a significant amount of disk space. _______________________________________________________________________________________________________________ PHP Bible, 2nd Edition 13 Wiley and the book authors, 2002 Advanced features to look for (cont.) Foreign keys and integrity constraints The relational structure of a database is often implicit in the ways fields of one table refer to row IDs of another, but your database won't necessarily do anything helpful to make sure that the structure is respected as changes are made (especially when deleting records). One way the database can help is via cascading deletes – automatically deleting records that depend on other records in other tables that have been deleted Database replication As your data store expands and your business becomes more dependant on its availability and performance, it is imperative to maintain the database on more than one server. This will require a way to automatically keep the different servers synched up. This will usually involve a journaling system and often a master-slave relationship where one server is designated the master into which all new data is added. A journal keeps track of these changes in chronological order. The slaves then read the master journal and make the same changes in themselves. Select queries can then be made against the multiple slaves improving performance and reliability. Another feature would be when if the master were to fail, a slave could automatically be promoted to the master _______________________________________________________________________________________________________________ PHP Bible, 2nd Edition 14 Wiley and the book authors, 2002 Database abstraction (or not) Database abstraction refers to writing wrapper functions or classes instead of using the bare PHP commands directly from your code. This methodology is supported by many experts in application development and simultaneously decried by many other experts. This issue is more apparent in PHP than other development languages because of the huge numbers of databases and interfaces that are supported Argument for database abstraction mainly consists of the ability to swap databases without having to change a lot of code Argument against abstraction is that if you were to switch databases it may dissuade you from taking advantage of some features available on the new system Oracle will allow you to retrieve an entire data set from a query into an array in a single operation. No other DB has that option. This could be overcome by writing a wrapper function which retrieves the entire dataset and creates an array with all of the records returned Other advantages with writing wrappers include a consistent error reporting methodology, taking advantage of previous queries, and reducing the total amount of code in your application _______________________________________________________________________________________________________________ PHP Bible, 2nd Edition 15 Wiley and the book authors, 2002 MySQL and Oracle MySQL if quite likely the fastest, cheapest, simplest, and most reliable database that also has most of the features you'd want and comes in Unix and Windows implementations There are certain situations in which you need the power of an enterprise-ready transactional system like Oracle, however E.g. if your business requires of the vendor on-site support or the ability to sue their pants off if something fails because of a problem with the database MySQL and Oracle pretty much represent the low and high ends of the Web database server market. If you can write your code to both of these, you should be able to write it to anything else _______________________________________________________________________________________________________________ PHP Bible, 2nd Edition 16 Wiley and the book authors, 2002