ONLamp.com > Database Articles

Database Articles

Emulating Analytic (AKA Ranking) Functions with MySQL: Part 2 by Stephane Faroult
Oracle, DB2, and SQL Server all have analytic functions--ways to preserve the details in a record when you use an aggregate. MySQL lacks them, but you can emulate them if you want. Stephane Faroult continues his look at how to do this in the second part of his series. Apr. 13, 2007

Emulating Analytic (AKA Ranking) Functions with MySQL by Stephane Faroult
Oracle, DB2, and SQL Server all have analytic functions, ways to preserve the details in a record when you use an aggregate. MySQL lacks them, but you can emulate them if you want. Stephane Faroult shows some of the ways to go about this in the first of a two part series. Mar. 29, 2007

Managing Many-to-Many Relationships with PL/pgSQL by David Wheeler
SQL gives you plenty of options for handling relationships--you can use joins and database relations, or you can make multiple queries and write complex logic on the client. What are the benefits and drawbacks of each? David Wheeler recently experimented with moving complex relationship logic into PostgreSQL's PL/pgSQL language; the results were stunning. Jun. 29, 2006

Switching Back to Desktop Linux by chromatic
Almost everyone at O'Reilly owns an iBook or PowerBook and almost everyone runs Mac OS X. It's not everyone's ideal operating system, however. Recently, free software editor chromatic explained to Mac editor Derrick Story why he switched back to desktop Linux. Here's what he wants in a usable Unix desktop. Jun. 1, 2006

Writing PostgreSQL Functions with PL/pgSQL by David Wheeler
One of the most powerful features of PostgreSQL is its support for user-defined functions. The language to learn is PL/pgSQL, an unpronounceable but powerful way to write UDFs. David Wheeler introduces the language and demonstrates why UDFs are useful. May. 11, 2006

Advanced MySQL Replication Techniques by Giuseppe Maxia
MySQL Cluster is a powerful peering system to add high availability and replication across multiple database servers. It's not perfect, though. Using features of MySQL 5.0 and 5.1, it's possible to build a master/slave replication system with fail-over. Giuseppe Maxia shows how. Apr. 20, 2006

PHP Code Generation with Elisp by Zachary Kessin
There's plenty of near-repetition in software development; writing very similar code over and over again. Stop copying, pasting, and modifying, and start automating the process! Zachary Kessin shows how to use Emacs Lisp to generate useful and reusable database-access code for PHP. Nov. 23, 2005

Calculating Entropy for Data Mining by Paul Meagher
Eww, statistics. Right? Not necessarily--for example, calculating the entropy of your web statistics can help you analyze trends and correlations. Paul Meagher demonstrates statistical programming in PHP while explaining single-variable entropy. Jan. 6, 2005

Simplify Business Logic with PHP DataObjects by Darryl Patterson
Are you sick of writing the same SQL over and over in your application? Would you like to simplify and unify your access to the same tables in multiple places? DataObjects may be for you. Darryl Patterson demonstrates how to write and use DataObjects in PHP. Aug. 5, 2004

Why Write PostgreSQL Extension Functions? by Joe Conway
Relational databases are very powerful, but they don't do everything for you. In some cases, they don't do enough. Fortunately, many make it possible to write extension functions to add behavior you need. Joe Conway demonstrates why you might need this with C and PostgreSQL. Jun. 28, 2004

Tales of Optimization and Troubleshooting by Howard Feldman
Sometimes your software just isn't fast enough. Before reaching for your checkbook for the latest and greatest hardware, think for a minute. Can throwing brains, not money, at the problem really work? Howard Feldman demonstrates real optimization techniques from the bioinformatics world. Jun. 3, 2004

Pitfalls of Transactions with PHP by Kimberlee Jensen
Database transactions are important for data reliability and consistency. Used properly, they can prevent many types of errors. Used improperly, they can cause many other kinds of errors. Kimberlee Jensen demonstrates using transactions with PHP well. Dec. 18, 2003

Trip Mapping with PHP by David Sklar
Do PHP and cartography go together? David Sklar thinks so. In this article from the PHP Cookbook coauthor, he demonstrates how to plot your trips with PHP and census data. Nov. 7, 2002

Building a Simple Search Engine with PHP by Daniel Solin
Curious how a search engine works? Stuck on an intranet where you have to roll your own solution? In this article Daniel Solin shows how easy it is to build a simple search engine spider with PHP. Oct. 24, 2002

Which Table, Which Column? by Jonathan Gennick
Jonathan Gennick, O'Reilly editor and coauthor of Transact-SQL Cookbook, writes about why you should always use table aliases in SQL. Aug. 22, 2002

Simson Garfinkel on "Protecting Privacy with Translucent Databases" by Tara McGoldrick Walsh
Online version of the Linux Newsletter for August 5, 2002. Aug. 5, 2002

The IPC10 Python Gathering by Mark Lutz
Mark Lutz takes Python's pulse and reports from the Tenth International Python Conference. Feb. 12, 2002

Pear::DB Primer by Joao Prado Maia
Pear::DB provides a database abstraction layer for PHP allowing it to use most major databases, including MySQL, PostGreSQL, and MS SQL server. Joao Prado Maia explains how to use it and provides examples. Nov. 29, 2001

SkunkWeb Application Server by Stephen Figgins
The templating simplicity of PHP combined with the power of Python packs a wallop in SkunkWeb, an application server for Python developers.  Sep. 6, 2001

Transactional Persistence for Python by Stephen Figgins
The Z Object Database combines the simplicity of shelve with the transactional power of a relational database. Michael Pelletier's tutorial will get you started.  Jun. 27, 2001

PostgreSQL's Multi-Version Concurrency Control by Joseph Mitchell
Multi-Version Concurrency Control (MVCC) in PostgreSQL helps avoid the frustration that "locks" can cause by making users wait while working in a multi-user database environment. In this article, GreatBridge engineer Joseph Mitchell explains how MVCC works. May. 25, 2001

An Introduction to PEAR by Joao Prado Maia
Find yourself wishing PHP had an easy way to manage additional modules? Joao Prado Maia explains PEAR and shows how it fills this role.  May. 25, 2001

Creating Dynamic Next / Previous Buttons with PHP and MySQL by Joao Prado Maia
Need to add Next / Previous buttons to your web site? Joao Prado Maia shows us how to program these buttons with a dynamic, reusable class using PHP and MySQL or PostGreSQL. Nov. 2, 2000

Uploading, Saving and Downloading Binary Data in a MySQL Database by Joao Prado Maia
Joao Prado Maia explains how to upload and store binary data in a MySQL database using PHP. He includes the complete source code for all the scripts involved. Sep. 15, 2000