How-To Tutorials - Data

  MySQL for Python Integrate the flexibility of Python and the power of MySQL to boost the productivity of your Python applications Implement the outstanding features of Python's MySQL library to their full potential See how to make MySQL take the processing burden from your programs Learn how to employ Python with MySQL to power your websites and desktop applications Apply your knowledge of MySQL and Python to real-world problems instead of hypothetical scenarios A manual packed with step-by-step exercises to integrate your Python applications with the MySQL database server Read more about this book (For more resources on Phython see here.) Hello() To create a function, we necessarily have to go back to the CREATE statement. As in a Python function definition, MySQL expects us to declare the name of the function as well as any arguments it requires. Unlike Python, MySQL also wants the type of data that will be received by the function. The beginning of a basic MySQL function definition looks like this: CREATE FUNCTION hello(s CHAR(20)) MySQL then expects to know what kind of data to return. Again, we use the MySQL data type definitions for this. RETURNS CHAR(50) This just tells MySQL that the function will return a character string of 50 characters or less. If the function will always perform the same task, it is best for the sake of performance to include the keyword DETERMINISTIC next. If the behavior of the function varies, use the keyword NON-DETERMINISTIC. If no keyword is set for the characteristic of the function, MySQL defaults to NON-DETERMINISTIC. You can learn more about the characteristic keywords used in function definitions at: http://dev.mysql.com/doc/refman/5.5/en/create-procedure.html Finally comes the meat of the function definition. Here we can set variables and perform any calculations that we want. For our basic definition, we will simply return a concatenated string: RETURN CONCAT('Hello, ', s, '!'); The function obviously concatenates the word 'Hello' with whatever argument is passed to it and appends an exclamation point at the end. To call it we use SELECT as with the other functions: mysql> SELECT hello('world') as Greeting; Capitalise() A function to capitalize every initial letter in a string follows the same pattern. The main point of the function is to walk through the string, character by character, and use UPPER() on every character that does not follow a letter. DELIMITER Obviously, we need a way to pass the entire function to MySQL without having any of the lines evaluated until we call it. To do this, we use the keyword DELIMITER. DELIMITER allows users to tell MySQL to evaluate lines that end in the character(s) we set. So the process for complex function definitions becomes: Change the delimiter. Pass the function with the usual semicolons to indicate the end of the line. Change the delimiter back to a semicolon. Call the function. The DELIMITER keyword allows us to specify more than one character as the line delimiter. So in order to ensure we don't need to worry about our code inadvertently conflicting with a line delimiter, let's make the delimiter @@: DELIMITER @@ The function definition From here, we are free to define a function to our specification. The definition line will read as follows: CREATE FUNCTION `Capitalise`(instring VARCHAR(1000)) The function will return a character string of similar length and variability: RETURNS VARCHAR(1000) When MySQL functions extend beyond the simplest calculations, such as hello(), MySQL requires us to specify the beginning and ending of the function. We do that with the keywords BEGIN and END. So let's begin the function: BEGIN Next, we need to declare our variables and their types using the keyword DECLARE: DECLARE i INT DEFAULT 1;DECLARE achar, imark CHAR(1);DECLARE outstring VARCHAR(1000) DEFAULT LOWER(instring); The DEFAULT keyword allows us to specify what should happen if outstring should fail for some reason. Next, we define a WHILE loop: WHILE i <= CHAR_LENGTH(instring) DO The WHILE loop obviously begins with a conditional statement based on the character length of instring. The resulting action begins with the keyword DO. From here, we set a series of variables and express what should happen where a character follows one of the following: blank space & '' _ ? ; : ! , - / ( . The operational part of the function looks like this: SET achar = SUBSTRING(instring, i, 1); SET imark = CASE WHEN i = 1 THEN ' ' ELSE SUBSTRING(instring, i - 1, 1) END CASE; IF imark IN (' ', '&', '''', '_', '?', ';', ':', '!', ',', '-', '/', '(', '.') THEN SET outstring = INSERT(outstring, i, 1, UPPER(achar)); END IF; SET i = i + 1; Much of this code is self-explanatory. It is worth noting, however, that the apodosis of any conditional in MySQL must end with the keyword END. In the case of IF, we use END IF. In the second SET statement, the keyword CASE is an evaluative keyword that functions similar to the try...except structure in Python. If the WHEN condition is met, the empty THEN apodosis is executed. Otherwise, the ELSE exception applies and the SUBSTRING function is run. The CASE structure ends with END CASE. MySQL will equally recognize the use of END instead. The subsequent IF clause evaluates whether imark, defined as the character before achar, is one of the declared characters. If it is, then that character in instring is replaced with its uppercase equivalent in outstring. After the IF clause is finished, the loop is incremented by one. After the entire string is processed, we then end the WHILE loop with: END WHILE; After the function's operations are completed, we return the value of outstring and indicate the end of the function: RETURN outstring;END@@ Finally, we must not forget to return the delimiter to a semicolon: DELIMITER ; It is worth noting that, instead of defining a function in a MySQL session we can define it in a separate file and load it on the fly with the SOURCE command. If we save the function to a file called capfirst.sql in a directory temp, we can source it relatively: We can also use: SOURCE /home/skipper/temp/capfirst.sql;

Why errors and warnings are good for you The value of rigorous error checking is exemplified in any of the several catastrophes arising from poor software engineering. Examples abound, but a few are particularly illustrative of what happens when bad data and design go unchallenged. On 4 June 1996, the first test flight of the Ariane 5 rocket self-destructed 37 seconds after its launch. The navigation code from Ariane 4 was reused in Ariane 5. The faster processing speed on the newer rocket caused an operand error. The conversion of a 64-bit floating-point value resulted in a larger-than-expected and unsupported 16-bit signed integer. The result was an overflow that scrambled the flight's computer, causing too much thrust to be passed by the rocket itself, resulting in the crash of US$370 million worth of technology. Widely considered to be one of the most expensive computer bugs in history, the crash arose due to mistakes in design and in subsequent error checking. On 15 January 1990, the American telecommunications company AT&T installed a new system on the switches that controlled their long-distance service. A bug in the software caused the computers to crash every time they received a message from one of their neighboring switches. The message in question just happened to be the same one that the switches send out when they recover from a system crash. The result: Within a short time, 114 switches across New York City were rebooting every six seconds, leaving around 60,000 people without long distance service for nine hours. The system ultimately had to be fixed by reinstalling the old software. On the Internet, a lack of proper error-checking still makes it possible for a malformed ping request to crash a server anywhere in the world. The Computer Emergency Response Team (CERT) Advisory on this bug, CA-1996-26, was released in 1996, but the bug persists. The original denial-of-service attack has thus evolved into the distributed denial-of-service attack employing botnets of zombie machines worldwide. More than any other part of a computing system, errors cost significantly more to fix later than if they were resolved earlier in the development process. It is specifically for this reason that Python outputs error messages to the screen, unless such errors are explicitly handled otherwise. A basic dynamic of computing is that the computer does not let anyone know what is happening inside itself. A simple illustration of this dynamic is as follows: x = 2 if x == 2: x = x + x Knowing Python and reading the code, we understand that the value of x is now 4. But the computer has provided us no indication of the value of x. What's more, it will not tell us anything unless we explicitly tell it to do so. Generally speaking, there are two ways you can ask Python to tell you what it's thinking: By outputting values to the screen By writing them to a file Here, a simple print statement would tell us the value of x. Output displayed on the screen or saved to a file are the most common ways for programs to report their status to users. However, the similar effect is done by indicator lights and other non-verbal forms of communication. The type of output is necessarily dependent on the hardware being used. By default, Python outputs all errors and warnings to the screen. As MySQL for Python is interpreted by Python, errors passed by MySQLdb are no different. This naturally gives the debugging programmer information for ironing out the performance of the program—whether determining why a program is not executing as planned or how to make it execute faster or more reliably. However, it also means that any information needed for tracing the error, along with parts of the code, is passed to the user, whoever they may be. This is great for debugging, but makes for terrible security. That is why the Zen of Python reads: Errors should never pass silently Unless explicitly silenced One needs the error messages to know why the program fails, but it is a security hazard to pass raw error messages to the user. If one wants the user to handle an error message, it should be sanitized of information that may compromise the security of the system. Handling exceptions correctly takes a lot of code. At the risk of sounding like a hypocrite, it should be noted that the exigencies of a printed book do not allow for the reproduction of constant, rigorous error-handling in the code examples such as this article espouses. Therefore, while I state this principle, the programming examples do not always illustrate it as they should. If they did, the book would be significantly thicker and heavier (and probably cost more too!). Further, the more complicated an application, the more robust the error-handling should be. Ultimately, every kind of error is covered by one of the several types that can be thrown by MySQL for Python. Each one of them allows for customized error messages to be passed to the user. With a bit of further coding, one can check the authentication level of the user and pass error messages according to their level of authorization. This can be done through a flag system or by using modules from the Python library. If the former is used, one must ensure that knowledge of the flag(s) used is guarded from unauthorized users. Alternatively, one can employ both systems by checking the authentication level of users or programs that pass a particular flag to the program. Errors versus warnings: There's a big difference As with Python in general, the main difference between errors and warnings is that warnings do not cause a program to terminate. Errors do. Warnings provide notice of something we should note; errors indicate the reason the program cannot continue. If not handled appropriately, warnings therefore pass process information to the user without interrupting the execution of the program. This lack of detectability makes warnings more dangerous to the security of an application, and the system in general, than errors. Consequently, the error-handling process of an application must account for both errors and warnings. While Python handles warnings and exceptions differently by default, especially with regard to program execution, both are written to stderr. Therefore, one handles them the same way that one handles standard errors. Additionally, one can set warnings to be silenced altogether or to carry the same gravity as an error. This level of functionality was introduced in Python 2.1. The two main errors in MySQLdb Python generally supports several kinds of errors, and MySQL for Python is no different. The obvious difference between the two is that MySQLdb's errors deal exclusively with the database connection. Where MySQLdb passes warnings that are not MySQL-specific, all exceptions are related to MySQL. The MySQL-specific exceptions are then classified as either warnings or errors. There is only one kind of warning, but MySQLdb allows two categories of errors—DatabaseError and InterfaceError. Of the former, there are six types that we will discuss here. DatabaseError When there is a problem with the MySQL database itself, a DatabaseError is thrown. This is an intermediate catch-all category of exceptions that deal with everything from how the data is processed (for example, errors arising from division by zero), to problems in the SQL syntax, to internal problems within MySQL itself. Essentially, if a connection is made and a problem arises, the DatabaseError will catch it. Several types of exceptions are contained by the DatabaseError type. We look at each of these in the section Handling exceptions passed from MySQL. InterfaceError When the database connection fails for some reason, MySQLdb will raise an InterfaceError. This may be caused from problems in the interface anywhere in the connection process.

  MySQL for Python Integrate the flexibility of Python and the power of MySQL to boost the productivity of your Python applications Implement the outstanding features of Python's MySQL library to their full potential See how to make MySQL take the processing burden from your programs Learn how to employ Python with MySQL to power your websites and desktop applications Apply your knowledge of MySQL and Python to real-world problems instead of hypothetical scenarios A manual packed with step-by-step exercises to integrate your Python applications with the MySQL database server Read more about this book (For more resources on Phython, see here.) The purpose of the archiving methods covered in this article is to allow you, as the developer, to back up databases that you use for your work without having to rely on the database administrator. As noted later in the article, there are more sophisticated methods for backups than we cover here, but they involve system-administrative tasks that are beyond the remit of any development post and are thus beyond the scope of this article. Every database needs a backup plan When archiving a database, one of the critical questions that must be answered is how to take a snapshot backup of the database without having users change the data in the process. If data changes in the midst of the backup, it results in an inconsistent backup and compromises the integrity of the archive. There are two strategic determinants for backing up a database system: Offline backups Live backups Which you use depends on the dynamics of the system in question and the import of the data being stored. In this article, we will look at each in turn and the way to implement them. Offline backups Offline backups are done by shutting down the server so the records can be archived without the fear of them being changed by the user. It also helps to ensure the server shut down gracefully and that errors were avoided. The problem with using this method on most production systems is that it necessitates a temporary loss of access to the service. For most service providers, such a consequence is anathema to the business model. The value of this method is that one can be certain that the database has not changed at all while the backup is run. Further, in many cases, the backup is performed faster because the processor is not simultaneously serving data. For this reason, offline backups are usually performed in controlled environments or in situations where disruption is not critical to the user. These include internal databases, where administrators can inform all users about the disruption ahead of time, and small business websites that do not receive a lot of traffic. Offline backups also have the benefit that the backup is usually held in a single file. This can then be used to copy a database across hosts with relative ease. Shutting down a server obviously requires system administrator-like authority. So creating an offline backup relies on the system administrator shutting down the server. If your responsibilities include database administration, you will also have sufficient permission to shut down the server. Live backups Live backups occur while the server continues to accept queries from users, while it's still online. It functions by locking down the tables so no new data may be written to them. Users usually do not lose access to the data and the integrity of the archive, for a particular point in time is assured. Live backups are used by large, data-intensive sites such as Nokia's Ovi services and Google's web services. However, because they do not always require administrator access of the server itself, these tend to suit the backup needs of a development project. Choosing a backup method After having determined whether a database can be stopped for the backup, a developer can choose from three methods of archiving: Copying the data files (including administrative files such as logs and tablespaces) Exporting delimited text files Backing up with command-line programs Which you choose depends on what permissions you have on the server and how you are accessing the data. MySQL also allows for two other forms of backup: using the binary log and by setting up replication (using the master and slave servers). To be sure, these are the best ways to back up a MySQL database. But, both of these are administrative tasks and require system-administrator authority; they are not typically available to a developer. However, you can read more about them in the MySQL documentation. Use of the binary log for incremental backups is documented at: http://dev.mysql.com/doc/refman/5.5/en/point-in-time-recovery.html Setting up replication is further dealt with at: http://dev.mysql.com/doc/refman/5.5/en/replication-solutions-backups.html Copying the table files The most direct way to back up database files is to copy from where MySQL stores the database itself. This will naturally vary based on platform. If you are unsure about which directory holds the MySQL database files, you can query MySQL itself to check: mysql> SHOW VARIABLES LIKE 'datadir'; Alternatively, the following shell command sequence will give you the same information: $ mysqladmin variables | grep datadir| datadir | /var/lib/mysql/ | Note that the location of administrative files, such as binary logs and InnoDB tablespaces are customizable and may not be in the data directory. If you do not have direct access to the MySQL server, you can also write a simple Python program to get the information: #!/usr/bin/env pythonimport MySQLdbmydb = MySQLdb.connect('<hostname>', '<user>', '<password>')cursor = mydb.cursor()runit = cursor.execute("SHOW VARIABLES LIKE 'datadir'")results = cursor.fetchall()print "%s: %s" %(cursor.fetchone()) Slight alteration of this program will also allow you to query several servers automatically. Simply change the login details and adapt the output to clarify which data is associated with which results. Locking and flushing If you are backing up an offline MyISAM system, you can copy any of the files once the server has been stopped. Before backing up a live system, however, you must lock the tables and flush the log files in order to get a consistent backup at a specific point. These tasks are handled by the LOCK TABLES and FLUSH commands respectively. When you use MySQL and its ancillary programs (such as mysqldump) to perform a backup, these tasks are performed automatically. When copying files directly, you must ensure both are done. How you apply them depends on whether you are backing up an entire database or a single table. LOCK TABLES The LOCK TABLES command secures a specified table in a designated way. Tables can be referenced with aliases using AS and can be locked for reading or writing. For our purposes, we need only a read lock to create a backup. The syntax looks like this: LOCK TABLES <tablename> READ; This command requires two privileges: LOCK TABLES and SELECT. It must be noted that LOCK TABLES does not lock all tables in a database but only one. This is useful for performing smaller backups that will not interrupt services or put too severe a strain on the server. However, unless you automate the process, manually locking and unlocking tables as you back up data can be ridiculously inefficient. FLUSH The FLUSH command is used to reset MySQL's caches. By re-initiating the cache at the point of backup, we get a clear point of demarcation for the database backup both in the database itself and in the logs. The basic syntax is straightforward, as follows: FLUSH <the object to be reset>; Use of FLUSH presupposes the RELOAD privilege for all relevant databases. What we reload depends on the process we are performing. For the purpose of backing up, we will always be flushing tables: FLUSH TABLES; How we "flush" the tables will depend on whether we have already used the LOCK TABLES command to lock the table. If we have already locked a given table, we can call FLUSH for that specific table: FLUSH TABLES <tablename>; However, if we want to copy an entire database, we can bypass the LOCK TABLES command by incorporating the same call into FLUSH: FLUSH TABLES WITH READ LOCK; This use of FLUSH applies across the database, and all tables will be subject to the read lock. If the account accessing the database does not have sufficient privileges for all databases, an error will be thrown. Unlocking the tables Once you have copied the files for a backup, you need to remove the read lock you imposed earlier. This is done by releasing all locks for the current session: UNLOCK TABLES; Restoring the data Restoring copies of the actual storage files is as simple as copying them back into place. This is best done when MySQL has stopped, lest you risk corruption. Similarly, if you have a separate MySQL server and want to transfer a database, you simply need to copy the directory structure from the one server to another. On restarting, MySQL will see the new database and treat it as if it had been created natively. When restoring the original data files, it is critical to ensure the permissions on the files and directories are appropriate and match those of the other MySQL databases. Delimited backups within MySQL MySQL allows for exporting of data from the MySQL command line. To do so, we simply direct the output from a SELECT statement to an output file. Using SELECT INTO OUTFILE to export data Using sakila, we can save the data from film to a file called film.data as follows: SELECT * INTO OUTFILE 'film.data' FROM film; This results in the data being written in a tab-delimited format. The file will be written to the directory in which MySQL stores the sakila data. Therefore, the account under which the SELECT statement is executed must have the FILE privilege for writing the file as well as login access on the server to view it or retrieve it. The OUTFILE option on SELECT can be used to write to any place on the server that MySQL has write permission to use. One simply needs to prepend that directory location to the file name. For example, to write the same file to the /tmp directory on a Unix system, use: SELECT * INTO OUTFILE '/tmp/film.data' FROM film; Windows simply requires adjustment of the directory structure accordingly. Using LOAD DATA INFILE to import data If you have an output file or similar tab-delimited file and want to load it into MySQL, use the LOAD DATA INFILE command. The basic syntax is: LOAD DATA INFILE '<filename>' INTO TABLE <tablename>; For example, to import the film.data file from the /tmp directory into another table called film2, we would issue this command: LOAD DATA INFILE '/tmp/film.data' INTO TABLE film2; Note that LOAD DATA INFILE presupposes the creation of the table into which the data is being loaded. In the preceding example, if film2 had not been created, we would receive an error. If you are trying to mirror a table, remember to use the SHOW CREATE TABLE query to save yourself time in formulating the CREATE statement. This discussion only touches on how to use LOAD DATA INFILE for inputting data created with the OUTFILE option of SELECT. But, the command handles text files with just about any set of delimiters. To read more on how to use it for other file formats, see the MySQL documentation at: http://dev.mysql.com/doc/refman/5.5/en/load-data.html

  MySQL for Python Integrate the flexibility of Python and the power of MySQL to boost the productivity of your Python applications Implement the outstanding features of Python's MySQL library to their full potential See how to make MySQL take the processing burden from your programs Learn how to employ Python with MySQL to power your websites and desktop applications Apply your knowledge of MySQL and Python to real-world problems instead of hypothetical scenarios A manual packed with step-by-step exercises to integrate your Python applications with the MySQL database server Read more about this book (For more resources on Phython see here.) When we deal with large amounts of data that are all going into the same database, running single instances of individual INSERT commands can take a ridiculous amount of time and waste a considerable amount of I/O. What we need is a way to insert multiple items in one go. In this article, Albert Lukaszewski, PhD, author of MySQL for Python, we will look at the following: How iteration can help us execute several individual INSERT statements rapidly Using executemany() to submit several INSERT statements in one go When not to use executemany() Throttling how much data is inserted at a time The problem You need to collate and rearrange the contents of several databases into one table whenever a given indicator is achieved (the indicator may be, among other things, a stock price, a value in one of the databases, or the flight pattern of African swallows). The format and length of the tables are predictable. There are 5,000 records in each table so manually running a separate INSERT statement for each record is not a viable option even with the programs used. The problem calls for a means of iterating through a series and changing the INSERT statement automatically. We could pursue one of the following two ways to do this: Write a MySQL script to insert the data in batch mode Iterate over the data to form and execute a MySQL INSERT statement accordingly None of these are a very good solution to the present problem. Why not a MySQL script? As we have seen when we created the world and Sakila databases, a MySQL script can contain the schema of a database, the values of the database tables, or both. To create data quickly, there is nothing better. However, following are the several drawbacks to using a script in this scenario: Lack of automation Debugging the process Inefficient I/O Lack of automation Barring the use of an automation daemon (for example, cron) to run a cron job or a similar scheduled task, a DBA or their designate would have to run the script. This unnecessarily consumes time. It is comparable to swapping tape backups when automated backup services are available and proven. Most modern computing systems support automatic task scheduling. On Unix-based systems like Linux and Mac OS X, one can schedule processes to be run at set times and on a regular rotation. One of the most widely used programs for such scheduling is cron. A single scheduled task in cron has thus come to be known as a cron job. Debugging the process Creating a MySQL script can be a very tedious task, and the slightest error or oversight can ruin the entire process. Using the --force flag causes MySQL to ignore errors in the source file. It is therefore not something that should be used regularly if one values the integrity of data. If the script is malformed for any reason, a two minute data insertion job can quickly become a two hour (at least!), unscheduled debugging process. Inefficient I/O Dumping large amounts of data on MySQL can create latency across the network. If the script were run by a DBA or similar, that person should rightly evaluate the state of the network before running it. Regardless of experience and training, judgment calls naturally require estimation and can result in ambiguous decision-making. While this is unavoidable, it should be minimized where possible. If the server is experiencing high traffic, the DBA would need to find something else to do and reschedule the running of the script. This randomly postpones the time of execution and the availability of the results. Also, it runs the risk of the DBA forgetting to execute the script. If the script is automated with a cron job or similar, we risk dumping a lot of data onto MySQL at a time when others are doing more time sensitive tasks. On most servers, we can background the process, so it does not adversely impact the client processes. This, however, only ensures that the process will be run. It does not guarantee that the process will be finished by a particular time. Why not iterate? Every time a program iterates to read or insert data, it creates a certain amount of I/O processing. Depending on how a program is written will determine how much I/O is included in each loop. A test sample: generating primes To illustrate this, consider a program that accepts a series of numbers, generates the prime numbers that are equal to or less than each of those numbers and inserts those numbers into a database called primes with a table of the same name. The table has the following description: mysql> describe primes; and can be created with the following statement: CREATE TABLE `primes` (`ID` int(11) NOT NULL auto_increment, `NUMBER` int(11) NOT NULL default '0', `PRIMES` varchar(300) NOT NULL default '0', PRIMARY KEY (`ID`)) ENGINE=MyISAM DEFAULT CHARSET=latin1; Using the sys module to handle user input, the string module to split the series of entries, and MySQLdb to handle the database connection, we get the following preamble: #!/usr/bin/env pythonimport MySQLdb, string, sys Next, we need a function that will generate the prime numbers to a certain limit called n. def primes(n): """Returns a list of prime numbers up to n using an algorithm based onthe Sieve of Eratosthenes.""" if n < 2: return ['A number less than two is not prime by definition.']else: s = range(3,n+1,2) maxfactor = n ** 0.5 half = (n+1)/2-1 i = 0 m = 3 while m <= maxfactor: if s[i]: j = (m*m-3)/2 s[j] = 0 while j < half: s[j] = 0 j += m i = i + 1 m = 2 * i + 3return str([2] + [x for x in s if x]) This algorithm is based on the Sieve of Eratosthenes, one of the simplest ways of generating prime numbers. It uses the following steps: Generate a list of integers from 2 to n. Discount the multiples of each number that remains and that has a square less than or equal to n; this leaves all the prime factors of n. Stop when the square of the number in the series is greater than n. Prime numbers by definition have no other factor, but themselves and one. The lowest prime number is therefore 2. For this reason, we check whether n is less than 2 and return a message accordingly. For this program, we want a string returned so we convert the results before we return them. For more on the Sieve of Eratosthenes and how it works, see the entry on Wikipedia: http://en.wikipedia.org/wiki/Sieve_of_Eratosthenes The previous algorithm can be found in many forms and in many languages on the Internet. Two of the best that informed this discussion are as follows: http://www.maths.abdn.ac.uk/~igc/tch/mx3015/notes/node79.html http://code.activestate.com/recipes/366178/ Next, we create a function to form and execute the INSERT statement. def insert(n, p, cur): statement = """INSERT INTO primes(number, primes) VALUES("%s", "%s")""" %(n, p) cur.execute(statement) return statement This function takes the number, the primes, and the cursor object handle as arguments. Finally, our main() function looks like this: def main(): numbers = sys.argv[1] iterations = numbers.split(',') for n in iterations: mydb = MySQLdb.connect(host = 'localhost', user = 'skipper', passwd = 'secret', db = 'primes')cur = mydb.cursor() n = int(n)try: p = primes(n) if p.isalpha(): raise ValueErrorelse: statement = insert(n, p, cur) print "Data entered with the following statement:n", statement except: raise We split the values passed by the user and iterate through them with a for loop. Note that we include the database connection and cursor object creation as part of the iteration. We then include the usual if clause to call main(): if __name__ == '__main__': main() Comparing execution speeds We can test the speed of this program by giving it a series of simple primes to generate—even of the same number. Here we call it with a series of sevens to process and with the Unix command time to measure its speed. time ./primes.py "7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7" Given this series of sevens, we get a real execution time of 0.175 seconds. If we rearrange the program by moving the mydb and cur assignment lines to follow immediately after the preamble (remembering to adjust the indentation), we get an execution speed of 0.138 seconds. This difference in speed (0.037 seconds) is not particularly significant on a local system with a single user, but it would be magnified synergistically on a server with hundreds or thousands of users. So, we see that, if the connection and cursor is created each time, the program will be less efficient than if the loop contained only the execution of the statement and the connection and cursor were persistent in the cycle of the program. However, even just passing a series of single statements to MySQL through Cursor.execute() will consume more in protocol than necessary. Excessive iteration consumes resources needlessly.

(For more resources on this subject, see here.) Planning While planning is of course the first step, this is something that any IT shop should not take lightly since this is the step where you decide what vendor to go with. All cloud vendors have snazzy marketing material that tell you how seamless and highly functional a migration will be, but that simply cannot always be guaranteed. How many times have you been sold on a technology in the past that did not live up to expectations? It happens more often than you think, and it is part of the overall sales process by these companies. Instead of relying on their word, you could consider a few options. One would be to work with a company you have experience with. If you’re familiar with working with Microsoft, go with their Azure platform. If you’re already using Salesforce to some degree, continue to talk with them about a more full-scale migration. If you’re planning on working with a non-traditional vendor such as Amazon or Google, ask for references or do a bit of investigating on your own. You might learn a thing or two. Security Keeping data secure is of course very important, and it’s wise to make an assessment of your security prior to any sort of large-scale migration. Performing audits and stress tests are a great way to make sure that your organization and/or system is ready for a large scale change. The unfortunate reality to any sort of migration is that there are inevitable vulnerabilities to any large scale process such as this. With that being said, the more you know about your strengths and weaknesses prior to a cutover, the better off you will be. Any sort of large project is sure to experience bumps in the road, and you might as well prepare yourself for uncomfortable findings prior to commencement. Testing At this point, you’ve already made some critical decisions, but hopefully you’ve reached a testing stage where you will be able to take a look at more than one provider for assessment of what they can do for your organization. Having the redundancy to fall back on a "plan B" in case one provider doesn’t work out is ideal. You may not have that option depending on your particular deployment, but again it’s a more productive scenario. Deployment After the testing point, you’ve reach deployment. If the planning, security and testing phases have proven to be successful (at least optimistically so) you’ll probably realize that cloud deployments require more re-engineering and code changes than any vendor is willing to tell you. Moving an environment outside of your own hosted space takes a lot more in-house development than most people realize. Summary In the long run, however, cloud services are beneficial to both the vendor and the customer. What headaches that go into cloud migration usually pay off over time. Perhaps you might not see the immediate benefit, but once a deployment is successful, the amount of time and money that is saved by adopting a cloud strategy clearly makes all the hard work required worth. Perseverance is key, and in time it’s easy to see the overall benefits. Further resources on this subject: Hands-on Tutorial for Getting Started with Amazon SimpleDB Ground to SQL Azure migration using MS SQL Server Integration Services Microsoft LightSwitch Application using SQL Azure Database

(For more resources on Solr, see here.) The classic plugin for Rails is acts_as_solr that allows Rails ActiveRecord objects to be transparently stored in a Solr index. Other popular options include Solr Flare and rsolr. An interesting project is Blacklight, a tool oriented towards libraries putting their catalogs online. While it attempts to meet the needs of a specific market, it also contains many examples of great Ruby techniques to leverage in your own projects. You will need to turn on the Ruby writer type in solrconfig.xml: <queryResponseWriter name="ruby" class="org.apache.solr.request.RubyResponseWriter"/> The Ruby hash structure has some tweaks to fit Ruby, such as translating nulls to nils, using single quotes for escaping content, and the Ruby => operator to separate key-value pairs in maps. Adding a wt=ruby parameter to a standard search request returns results in a Ruby hash structure like this: { 'responseHeader'=>{ 'status'=>0, 'QTime'=>1, 'params'=>{ 'wt'=>'ruby', 'indent'=>'on', 'rows'=>'1', 'start'=>'0', 'q'=>'Pete Moutso'}}, 'response'=>{'numFound'=>523,'start'=>0,'docs'=>[ { 'a_name'=>'Pete Moutso', 'a_type'=>'1', 'id'=>'Artist:371203', 'type'=>'Artist'}]}} acts_as_solr A very common naming pattern for plugins in Rails that manipulate the database backed object model is to name them acts_as_X. For example, the very popular acts_as_list plugin for Rails allows you to add list semantics, like first, last, move_next to an unordered collection of items. In the same manner, acts_as_solr takes ActiveRecord model objects and transparently indexes them in Solr. This allows you to do fuzzy queries that are backed by Solr searches, but still work with your normal ActiveRecord objects. Let's go ahead and build a small Rails application that we'll call MyFaves that both allows you to store your favorite MusicBrainz artists in a relational model and allows you to search for them using Solr. acts_as_solr comes bundled with a full copy of Solr 1.3 as part of the plugin, which you can easily start by running rake solr:start. Typically, you are starting with a relational database already stuffed with content that you want to make searchable. However, in our case we already have a fully populated index available in /examples, and we are actually going to take the basic artist information out of the mbartists index of Solr and populate our local myfaves database with it. We'll then fire up the version of Solr shipped with acts_as_solr, and see how acts_as_solr manages the lifecycle of ActiveRecord objects to keep Solr's indexed content in sync with the content stored in the relational database. Don't worry, we'll take it step by step! The completed application is in /examples/8/myfaves for you to refer to. Setting up MyFaves project We'll start with the standard plumbing to get a Rails application set up with our basic data model: >>rails myfaves>>cd myfaves>>./script/generate scaffold artist name:string group_type:string release_date:datetime image_url:string>>rake db:migrate This generates a basic application backed by an SQLite database. Now we need to install the acts_as_solr plugin. acts_as_solr has gone through a number of revisions, from the original code base done by Erik Hatcher and posted to the solr-user mailing list in August of 2006, which was then extended by Thiago Jackiw and hosted on Rubyforge. Today the best version of acts_as_solr is hosted on GitHub by Mathias Meyer at http://github.com/ mattmatt/acts_as_solr/tree/master. The constant migration from one site to another leading to multiple possible 'best' versions of a plugin is unfortunately a very common problem with Rails plugins and projects, though most are settling on either RubyForge.org or GitHub.com. In order to install the plugin, run:  >>script/plugin install git://github.com/mattmatt/acts_as_solr.gitt We'll also be working with roughly 399,000 artists, so obviously we'll need some page pagination to manage that list, otherwise pulling up the artists /index listing page will timeout:  >>script/plugin install git://github.com/mislav/will_paginate.git Edit the ./app/controllers/artists_controller.rb file, and replace in the index method the call to @artists = Artist.find(:all) with: @artists = Artist.paginate :page => params[:page], :order => 'created_at DESC' Also add to ./app/views/artists/index.html.erb a call to the view helper to generate the page links: <%= will_paginate @artists %> Start the application using ./script/server, and visit the page http://localhost:3000/artists/. You should see an empty listing page for all of the artists. Now that we know the basics are working, let's go ahead and actually leverage Solr. Populating MyFaves relational database from Solr Step one will be to import data into our relational database from the mbartists Solr index. Add the following code to ./app/models/artist.rb: class Artist < ActiveRecord::Base acts_as_solr :fields => [:name, :group_type, :release_date]end The :fields array of hashes maps the attributes of the Artist ActiveRecord object to the artist fields in Solr's schema.xml. Because acts_as_solr is designed to store data in Solr that is mastered in your data model, it needs a way of distinguishing among various types of data model objects. For example, if we wanted to store information about our User model object in Solr in addition to the Artist object then we need to provide a type_field to separate the Solr documents for the artist with the primary key of 5 from the user with the primary key of 5. Fortunately the mbartists schema has a field named type that stores the value Artist, which maps directly to our ActiveRecord class name of Artist and we are able to use that instead of the default acts_as_solr type field in Solr named type_s. There is a simple script called populate.rb at the root of /examples/8/myfaves that you can run that will copy the artist data from the existing Solr mbartists index into the MyFaves database: >>ruby populate.rb populate.rb is a great example of the types of scripts you may need to develop to transfer data into and out of Solr. Most scripts typically work with some sort of batch size of records that are pulled from one system and then inserted into Solr. The larger the batch size, the more efficient the pulling and processing of data typically is at the cost of more memory being consumed, and the slower the commit and optimize operations are. When you run the populate.rb script, play with the batch size parameter to get a sense of resource consumption in your environment. Try a batch size of 10 versus 10000 to see the changes. The parameters for populate.rb are available at the top of the script: MBARTISTS_SOLR_URL = 'http://localhost:8983/solr/mbartists'BATCH_SIZE = 1500MAX_RECORDS = 100000 # the maximum number of records to load, or nil for all There are roughly 399,000 artists in the mbartists index, so if you are impatient, then you can set MAX_RECORDS to a more reasonable number. The process for connecting to Solr is very simple with a hash of parameters that are passed as part of the GET request. We use the magic query value of *:* to find all of the artists in the index and then iterate through the results using the start parameter: connection = Solr::Connection.new(MBARTISTS_SOLR_URL) solr_data = connection.send(Solr::Request::Standard.new({ :query => '*:*', :rows=> BATCH_SIZE, :start => offset, :field_list =>['*','score'] })) In order to create our new Artist model objects, we just iterate through the results of solr_data. If solr_data is nil, then we exit out of the script knowing that we've run out of results. However, we do have to do some parsing translation in order to preserve our unique identifiers between Solr and the database. In our MusicBrainz Solr schema, the ID field functions as the primary key and looks like Artist:11650 for The Smashing Pumpkins. In the database, in order to sync the two, we need to insert the Artist with the ID of 11650. We wrap the insert statement a.save! in a begin/rescue/end structure so that if we've already inserted an artist with a primary key, then the script continues. This just allows us to run the populate script multiple times: solr_data.hits.each do |doc| id = doc["id"] id = id[7..(id.length)] a = Artist.new(:name => doc["a_name"], :group_type => a["a_type"], :release_date => doc["a_release_date_latest"]) a.id = id begin a.save! rescue ActiveRecord::StatementInvalid => ar_si raise ar_si unless ar_si.to_s.include?("PRIMARY KEY must be unique") #sink duplicates endend Now that we've transferred the data out of our mbartists index and used acts_as_solr according to the various conventions that it expects, we'll change from using the mbartists Solr instance to the version of Solr shipped with acts_as_solr. Solr related configuration information is available in ./myfaves/config/solr.xml. Ensure that the default development URL doesn't conflict with any existing Solr's you may be running: development: url: http://127.0.0.1:8982/solr Start the included Solr by running rake solr:start. When it starts up, it will report the process ID for Solr running in the background. If you need to stop the process, then run the corresponding rake task: rake solr:stop. The empty new Solr indexes are stored in ./myfaves/solr/development. Build Solr indexes from relational database Now we are ready to trigger a full index of the data in the relational database into Solr. acts_as_solr provides a very convenient rake task for this with a variety of parameters that you can learn about by running rake -D solr:reindex. We'll specify to work with a batch size of 1500 artists at a time: >>rake solr:start>>% rake solr:reindex BATCH=1500(in /examples/8/myfaves)Clearing index for Artist...Rebuilding index for Artist...Optimizing... This drastic simplification of configuration in the Artist model object is because we are using a Solr schema that is designed to leverage the Convention over Configuration ideas of Rails. Some of the conventions that are established by acts_as_solr and met by Solr are: Primary key field for model object in Solr is always called pk_i. Type field that stores the disambiguating class name of the model object is called type_s. Heavy use of the dynamic field support in Solr. The data type of ActiveRecord model objects is based on the database column type. Therefore, when acts_as_solr indexes a model object, it sends a document to Solr with the various suffixes to leverage the dynamic column creation. In /examples/8/myfaves/vendor/plugins/acts_as_solr/solr/solr/conf/ schema.xml, the only fields defined outside of the management fields are dynamic fields: <dynamicField name="*_t" type="text" indexed="true" stored="false"/> The default search field is called text. And all of the fields ending in _t are copied into the text search field. Fields to facet on are named _facet and copied into the text search field as well. The document that gets sent to Solr for our Artist records creates the dynamic fields name_t, group_type_s and release_date_d, for a text, string, and date field respectively. You can see the list of dynamic fields generated through the schema browser at http://localhost:8982/solr/admin/schema.jsp. Now we are ready to perform some searches. acts_as_solr adds some new methods such as find_by_solr() that lets us find ActiveRecord model objects by sending a query to Solr. Here we find the group Smash Mouth by searching for matches to the word smashing: % ./script/consoleLoading development environment (Rails 2.3.2)>> artists = Artist.find_by_solr("smashing")=> #<ActsAsSolr::SearchResults:0x224889c @solr_data={:total=>9, :docs=>[#<Artist id: 364, name: "Smash Mouth"...>> artists.docs.first=> #<Artist id: 364, name: "Smash Mouth", group_type: 1, release_date: "2006-09-19 04:00:00", created_at: "2009-04-17 18:02:37", updated_at: "2009-04-17 18:02:37"> Let's also verify that acts_as_solr is managing the full lifecycle of our objects. Assuming Susan Boyle isn't yet entered as an artist, let's go ahead and create her:  >> Artist.find_by_solr("Susan Boyle")=> #<ActsAsSolr::SearchResults:0x26ee298 @solr_data={:total=>0, :docs=>[]}>>> susan = Artist.create(:name => "Susan Boyle", :group_type => 1, :release_date => Date.new)=> #<Artist id: 548200, name: "Susan Boyle", group_type: 1, release_date: "-4712-01-01 05:00:00", created_at: "2009-04-21 13:11:09", updated_at: "2009-04-21 13:11:09"> Check the log output from your Solr running on port 8982, and you should also have seen an update query triggered by the insert of the new Susan Boyle record: INFO: [] webapp=/solr path=/update params={} status=0 QTime=24 Now, if we delete Susan's record from our database: >> susan.destroy=> #<Artist id: 548200, name: "Susan Boyle", group_type: 1, release_date: "-4712-01-01 05:00:00", created_at: "2009-04-21 13:11:09", updated_at: "2009-04-21 13:11:09">=> #<Artist id: 548200, name: "Susan Boyle", group_type: 1, release_date: "-4712-01-01 05:00:00", created_at: "2009-04-21 13:11:09", updated_at: "2009-04-21 13:11:09"> Then there should be another corresponding update issued to Solr to remove the document: INFO: [] webapp=/solr path=/update params={} status=0 QTime=57 You can verify this by doing a search for Susan Boyle directly, which should return no rows at http://localhost:8982/solr/select/?q=Susan+Boyle.

Once your PL/SQL code has successfully compiled, it is important to review it to make sure it does what is required and that it performs well. You can consider a number of approaches when tuning and testing code. These approaches include: Debugging—run the code and add break points to stop and inspect areas of concern. SQL performance—use Explain Plan results to review the performance. PL/SQL performance—use the PL/SQL Hierarchical Profiler to identify bottlenecks. Unit testing—review edge cases and general function testing. Does the code do what you intended it to do? In this article by Sue Harper, author of Oracle SQL Developer 2.1, we'll review the debugger. We will see how to debug PL/SQL packages, procedures, and functions. Debugging PL/SQL code SQL and PL/SQL code may execute cleanly, and even produce an output. PL/SQL code may compile and produce results, but this is part of the task. Does it do what you are expecting it to do? Are the results accurate? Does it behave as expected for high and low values, odd dates or names? Does it behave the same way when it's called from within a program as it does when tested in isolation? Does it perform as well for massive sets of data as it does for a small test case? All of these are aspects to consider when testing code, and many can been tracked by debugging the code. Using the debugging mechanism in SQL Developer You will need a piece of compiled, working code. For this exercise, we will use the following piece of code: PROCEDURE EMP_DEPTS(P_MAXROWS VARCHAR2)ASCURSOR EMPDEPT_CURSOR ISSELECT D.DEPARTMENT_NAME, E.LAST_NAME, J.JOB_TITLEFROM DEPARTMENTS D, EMPLOYEES E, JOBS JWHERE D.DEPARTMENT_ID = E.DEPARTMENT_IDAND E.JOB_ID = J.JOB_ID;EMP_RECORD EMPDEPT_CURSOR % ROWTYPE;TYPE EMP_TAB_TYPE IS TABLE OF EMPDEPT_CURSOR % ROWTYPE INDEX BYBINARY_INTEGER;EMP_TAB EMP_TAB_TYPE;I NUMBER := 1;BEGINOPEN EMPDEPT_CURSOR;FETCH EMPDEPT_CURSORINTO EMP_RECORD;EMP_TAB(I) := EMP_RECORD;WHILE((EMPDEPT_CURSOR % FOUND) AND(I <= P_MAXROWS))LOOP I := I + 1;FETCH EMPDEPT_CURSORINTO EMP_RECORD;EMP_TAB(I) := EMP_RECORD;END LOOP;CLOSE EMPDEPT_CURSOR; FOR J IN REVERSE 1 .. ILOOP DBMS_OUTPUT.PUT_LINE('THE EMPLOYEE '|| EMP_TAB(J).LAST_NAME ||' WORKS IN DEPARTMENT '|| EMP_TAB(J).DEPARTMENT_NAME);END LOOP;END; Before you can debug code, you need to have the following privileges: EXECUTE and DEBUG—you need to be able to execute the required procedure DEBUG CONNECT SESSION—to be able to debug procedures you execute in the session Note, when granting the system privilege DEBUG ANY PROCEDURE, you are granting access to debug any procedure that you have execute privilege for and has been compiled for debug. Using the Oracle debugging packages Oracle provides two packages for debugging PL/SQL code. The first, DBMS_DEBUG, was introduced in Oracle 8i and is not used by newer IDEs. The second, DBMS_DEBUG_JWP, was introduced in Oracle 9i Release 2, and is used in SQL Developer when debugging sub-programs. Debugging When preparing to debug any code, you need to set at least one break point, and then you should select Compile for Debug. In the following screenshot, the breakpoint is set at the opening of the cursor, and the Compile for Debug option is shown in the drop-down list: Instead of using the drop-down list to select the Compile or Compile for Debug options, just click the Compile button. This compiles the PL/SQL code using the optimization level set in the Preferences. Select Database PL/SQL Compiler|. By setting the Optimization Level preference to 0 or 1 the PL/SQL is compiled with debugging information. Any PL/SQL code that has been compiled for debugging will show the little green bug overlaying the regular icon in the Connections navigator. The next screenshot shows the EMP_DEPTS procedure and the GET_SALARY function have both been compiled for debug: Compile for debug Once you have completed a debugging session, be sure to compile again afterwards to remove any debug compiler directives. While negligible, omitting this step can have a performance impact on the PL/SQL program. You are now ready to debug. To debug, click the Debug button in the toolbar. SQL Developer then sets the sessions to a debug session and issues the command DBMS_DEBUG_JDWP.CONNECT_TCP (hostname, port); and sets up the debug windows as shown in the following screenshot: This connects you to a debugger session in the database. In some instances, the port selected is not open, due to firewall or other restrictions. In this case, you can have SQL Developer prompt you for the port. To set this option, open the Preferences dialog, and select the Debugger node. You can also specify the port range available for SQL Developer to use. These options mean that you can have more control over the ports used. Navigating through the code The PL/SQL debugger provides a selection of buttons (or menu items) to step through individual lines of code, or to step over blocks of code. You can step through or over procedures, navigating to the point of contention or the area you wish to inspect. Once you start stepping into the code, you can track the data as it changes. The data is displayed in a second set of tabbed dialogs. In this example, we are looping through a set of records in order for you to see how each of the windows behaves. As you start stepping into the code, the Data tab starts to display the values: This Data tab continues to collect all of the variables as you continue to step through the code. Even if you step over and skip blocks of code, all of the code is executed and the results are gathered here. The Smart Data tab keeps track of the same detail, but only the values immediately related to the area you are working in. This is more useful in a large procedure than in a small one like the example shown. The context menu provides you with a set of options while debugging. These include: Run to Cursor—allows you to start debugging and then to quickly move to another part of the code. The code in-between is quickly executed and you can continue debugging. Watch—allows you to watch an expression or code while you are debugging. Inspect—allows you to watch values as you debug. In the following screenshot, the current execution point is at the start of the WHILE loop. If the loop is required to loop multiple times, you can skip that and have the code execute to a point further down in the code, in this case after the cursor has been completed and closed: The Watch and Inspect options remain set up if you stop and restart the debug session. This allows you to stop, change the input values, and start debugging and these will change according to the new parameters. You do not need to set up watch or inspector values each time you debug the procedure. The values appear in dockable windows, so you can dock or float them near the code as required: You can modify values that you are watching. In the following example, 'i' is the counter that we're using in the loop. You can modify this value to skip over chunks of the loop, and then continue from a particular point. Modifying values in the middle of the code can be useful, as you might want to test how the program reacts in certain circumstances. For example, before the millennium, testers may have wanted to see how code behaved, or output changed once the date switched over to the year 2000.

(For more resources on MySQL, see here.) A Hello World! Daemon plugin Now, let's look at our first complete plugin example. This plugin is probably the most basic plugin we can have. It simply prints a message into the MySQL error log when loaded: #include <stdio.h> #include <mysql/plugin.h> #include <mysql_version.h> These are the basic includes required for most Daemon plugins. The most important being mysql/plugin.h, which contains macros and data structures necessary for a MySQL plugin. static int hello_world_plugin_init(void *p) { fprintf(stderr, "Hello World: " "This is a static text daemon example plugin!n"); return 0; } In the plugin initialization function we simply write a message to stderr. MySQL redirects stderr to the error log (if there is one) so our message will end up there. We then return 0 to indicate that the initialization was successful. struct st_mysql_daemon hello_world_info = { MYSQL_DAEMON_INTERFACE_VERSION }; This structure is used for the info part of the plugin declaration. In Daemon plugins it simply contains the API version that this plugin was compiled against. The Daemon plugin API version matches the MySQL server version, which means MySQL Daemon plugins can only be used with a MySQL server version they have been compiled against. Indeed, for a Daemon plugin to do something non-trivial it will invariably need access to the server's internal functions and data structures that change with every MySQL version. Other plugins that are implemented according to a certain functionality API are separated from the server internals and are binary compatible with a wide range of server releases. Having defined all of the functions and auxiliary structures, we can declare a plugin: mysql_declare_plugin(hello_world) { This is a Daemon plugin so we need to specify it as such with this defined constant: MYSQL_DAEMON_PLUGIN, info points to the structure declared earlier. With other plugin types this may contain additional information valuable to the plugin functionality: &hello_world_info, We are calling this plugin "hello_world". This is its name for the INSTALL PLUGIN command and any plugin status: "hello_world", The author string, is useful for providing contact information about the author of the plugin: "Andrew Hutchings (<a href="Andrew.Hutchings@Sun.COM" target="_blank">Andrew.Hutchings@Sun.COM)", A Simple line of text that gives a basic description of what our plugin does: "Daemon hello world example, outputs some static text", This plugin is licensed under GPL so we set the license type to this: PLUGIN_LICENSE_GPL, This is our initialization function that has been defined earlier in the code: hello_world_plugin_init, As our simple plugin does not need a de-initialization function, we put NULL here: NULL, This plugin is given version 1.0 because it is our first GA release of the plugin. In future versions we can increment this: 0x0100, There are no status or system variables in this example. Hence, everything below the version is set to NULL: NULL, NULL, NULL } mysql_declare_plugin_end; We can now install this plugin using the INSTALL PLUGIN syntax Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 2 Server version: 5.1.47 Source distribution Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. mysql> INSTALL PLUGIN hello_world SONAME 'hello_world.so'; Query OK, 0 rows affected (0.00 sec) Going to the error log we see: 090801 22:18:00 [Note] /home/linuxjedi/Programming/Builds/mysql-5.1.47/ libexec/mysqld: ready for connections. Version: '5.1.47' socket: '/tmp/mysql.sock' port: 3306 Source distribution Hello World: This is a static text daemon example plugin! A system and status variables demo plugin Let's see a more complex example. This plugin shows how to create system and status variables. It has one global system variable and one status variable, both defined as long long. When you set the global system variable, its value is copied into the status variable. #include <stdio.h> #include <mysql/plugin.h> #include <mysql_version.h> long long system_var = 0; long long status_var = 0; struct st_mysql_show_var vars_status_var[] = { {"vars_status_var", (char *) &status_var, SHOW_LONGLONG}, {0, 0, 0} }; We have one status variable in this plugin called vars_status_var which is bound to the status_var variable defined near the top of this source code. We are defining this variable as long long so we use the SHOW_LONGLONG type. int sysvar_check(MYSQL_THD thd, struct st_mysql_sys_var *var, void *save, struct st_mysql_value *value) { This function is to be called before our system variable is updated. A plugin is not required to provide it but it can be used to check if the data entered is valid and, as an example, we will only allow values that are not too close to status_var. long long buf; value->val_int(value, &buf); First we retrieve the new value-to-be and store it in buf. *(longlong*) save = buf; We then set save to the contents of buf, so that the update function could access it and store the value in our system_var variable. If we do not implement our own sysvar_check() function for our system variable, MySQL will provide a default one that performs all of the above (but nothing of the following). if (buf * 2 < status_var || buf > status_var * 3) return 0; else return 1; } This is our special condition. In this example we allow an update only if the new value is either less than a half of or three times bigger than the value of status_var. We return 0 when the new value is valid, and an update should be allowed, and 1 when an update should be canceled. In our update function we copy the value of the system_var to a status_var, to see how its value changes in SHOW STATUS and to get a different range on valid values for the system_var on every update. Note that the update function cannot return a value. It is not supposed to fail! void sysvar_update(MYSQL_THD thd, struct st_mysql_sys_var *var, void *var_ptr, const void *save) { system_var = *(long long *)save; status_var = system_var; } We update our system_var variable without any mutex protection, even though many threads may try to execute the SET statement at the same time. Nevertheless, it is safe. MySQL internally guards all accesses to global system variables with a mutex, which means we do not have to. MYSQL_SYSVAR_LONGLONG(vars_system, system_var, 0, "A demo system var", sysvar_check, sysvar_update, 0, 0, 123456789, 0); This is the declaration for our system variable. It is a long long and is called vars_system. In fact as this is a variable for the vars plugin, the full name will be vars_vars_system in SHOW VARIABLES. It is associated with the system_var variable in the code, has the check function sysvar_check() and an update function sysvar_update() as defined above, and it can only take values between 0 and 123456789. struct st_mysql_sys_var* vars_system_var[] = { MYSQL_SYSVAR(vars_system), NULL }; This is the structure which stores all system variables to be passed to the declaration for this plugin. As we only have one variable we shall only include that. struct st_mysql_daemon vars_plugin_info= { MYSQL_DAEMON_INTERFACE_VERSION }; mysql_declare_plugin(vars) { MYSQL_DAEMON_PLUGIN, &vars_plugin_info, "vars", "Andrew Hutchings", "A system and status variables example", PLUGIN_LICENSE_GPL, NULL, NULL, 0x0100, vars_status_var, vars_system_var, NULL } mysql_declare_plugin_end; This is very similar to the declaration of our first plugin, but this one has structures for the status variables and system variable listed. When putting our new plugin into action we should see the following: mysql> INSTALL PLUGIN vars SONAME 'vars.so'; Query OK, 0 rows affected (0.00 sec) mysql> SHOW STATUS LIKE 'vars_%'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | vars_status_var | 0 | +-----------------+-------+ 1 row in set (0.00 sec) mysql> SHOW VARIABLES LIKE 'vars_%'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | vars_vars_system | 0 | +------------------+-------+ 1 row in set (0.00 sec) Our status and system variables are both set to 0 by default. mysql> SET GLOBAL vars_vars_system=2384; Query OK, 0 rows affected (0.00 sec) mysql> SHOW STATUS LIKE 'vars_%'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | vars_status_var | 2384 | +-----------------+-------+ 1 row in set (0.00 sec) mysql> SHOW VARIABLES LIKE 'vars_%'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | vars_vars_system | 2384 | +------------------+-------+ 1 row in set (0.00 sec) Setting our system variable to 2384 has altered both the system variable and the status variable, so we have success! mysql> SET GLOBAL vars_vars_system=2383; ERROR 1210 (HY000): Incorrect arguments to SET Our special check function works too. The variable cannot be updated to a value that is too close to its old value!

A look inside a Daemon plugin Unlike UDFs, MySQL plugins store all of the metadata in the plugins shared library. So when installing a plugin you only need to specify the name of the plugin and its shared library filename. This eliminates much of the user error while installing. With UDFs it is very easy to choose the wrong return type or forget the AGGREGATE keyword, but with plugins this is not possible. Why write a Daemon plugin Just like UDFs and other MySQL plugin types the Daemon plugin can be used to add extra functionality to MySQL with the same advantages and disadvantages. Daemon plugins are ideal for writing code that needs to reside in the server but does not need to communicate with it—such as a heartbeat plugin or monitoring plugins—because the simple Daemon plugin API does not provide any means for a server and a plugin to communicate with each other. Installing and using Daemon plugins Installing plugins is relatively easy because all of the information about a plugin is stored inside it. To install a plugin we can use the INSTALL PLUGIN statement as follows: mysql> INSTALL PLUGIN my_plugin SONAME 'my_plugin.so'; Likewise, to remove a plugin we use: mysql> UNINSTALL PLUGIN my_plugin; When a plugin is installed it is initialized instantly and this means that the code we write will start automatically when our plugin is installed. Upon installing a plugin it is added to the mysql.plugin table so MySQL knows it is installed and can load it again on startup. In other words, similar to UDFs, all installed plugins are loaded automatically when a server is started. A plugin is de-initialized when either it is uninstalled or the MySQL server is being shut down. It is worth noting at this time that if the MySQL server crashes for any reason the de-initialization of the plugin will not happen. If a plugin is installed, we can prevent it from being loaded and executed at startup with the --disable-plugin-my-plugin or --plugin-my-plugin=OFF commands. If we do not do that MySQL will try to load it because the default behavior is --plugin-my-plugin=ON. If the plugin fails to load, MySQL will note that fact in the error log and will continue without this plugin. If we want to be sure that a plugin is absolutely loaded in the server, and that the server will not simply ignore a plugin failure, we can use --plugin-my-plugin=FORCE. In this mode the server will exit if our plugin fails to load. As we can see below, the mysql.plugin table simply contains the plugin name and the filename for the shared library containing the plugin: mysql> SELECT * FROM mysql.plugin;+-----------+--------------+| name | dl |+-----------+--------------+| my_plugin | my_plugin.so |+-----------+--------------+1 row in set (0.01 sec) MySQL has a SHOW command to give us information about installed plugins. This is very useful to see if a plugin is actually running. If there was a problem during initialization then the status for the plugin will be marked as DISABLED. A sample output for SHOW PLUGINS can be seen below: mysql> SHOW PLUGINSG....*************************** 11. row *************************** Name: my_plugin Status: ACTIVE Type: DAEMONLibrary: my_plugin.soLicense: GPL11 rows in set (0.00 sec) Information Schema also includes a table for use with plugins, and it contains more detail than SHOW PLUGINS. It shows version information supplied by the plugin as well as the plugin description: mysql> SELECT * FROM information_schema.plugins WHERE PLUGIN_NAME='my_plugin'G*************************** 1. row *************************** PLUGIN_NAME: my_plugin PLUGIN_VERSION: 1.0 PLUGIN_STATUS: ACTIVE PLUGIN_TYPE: DAEMON PLUGIN_TYPE_VERSION: 50147.0PLUGIN_LIBRARY: my_plugin.so PLUGIN_LIBRARY_VERSION: 1.0 PLUGIN_AUTHOR: Andrew Hutchings PLUGIN_DESCRIPTION: Daemon example, shows a declaration PLUGIN_LICENSE: GPL1 row in set (0.00 sec) Technically, loading of plugins is very similar to loading of UDFs. Problems that can arise, ways of solving them, and error messages are similar to those of UDFs. The role of a version As we have seen, there are three two-component version numbers in the INFORMATION_SCHEMA.PLUGINS table. One of them, PLUGIN_VERSION, is purely informational. It is a number that a plugin author can specify arbitrarily, and MySQL itself does not do anything with it. The other two are very important though. They are used to protect the API, to make sure that if a plugin is loaded it uses the same API version that the server provides. This is one of the main differences to UDFs. UDF API is not versioned. Hence, it was not developed and still has only those features that it did in 3.21.24. Extending UDF API is risky; any change and old UDFs may start crashing the server. Plugin API, on the other hand, is safe. It is protected by a version, and this version is part of every plugin library, the API version that the plugin was compiled against. When a plugin is loaded the server verifies that the version is supported by the server and refuses to load a plugin otherwise. That is, the server can guarantee that all loaded plugins are fully compatible with the server, and no crash can happen because of API mismatch. The API is protected with two version numbers, as it contains two parts—one is common to all plugin types. It is version 1.0, as can be seen in the PLUGIN_LIBRARY_ VERSION column above. The other one is specific to each plugin type. For Daemon plugins this version is 50147.0, as seen in the PLUGIN_TYPE_VERSION column, and it is derived from the MySQL server version (which was 5.1.47 in my examples). Defining Daemon plugins The most basic of Daemon plugins needs no code at all; only a declaration is required. A plugin declaration is an instance of a st_mysql_plugin structure: struct st_mysql_plugin{ int type; void *info; const char *name; const char *author; const char *descr; int license; int (*init)(void *); int (*deinit)(void *); unsigned int version; struct st_mysql_show_var *status_vars; struct st_mysql_sys_var **system_vars; void *__reserved1}; The type defines what type of plugin this will be, which in turn defines what it can do. In MySQL 5.1, type can be set to one of the following enumerated values: Here we are talking about Daemon plugins so this should be set to MYSQL_DAEMON_PLUGIN. The info member is a pointer to the descriptor of the plugin and its members. It contains the information specific to this particular plugin type (while the st_mysql_plugin structure itself contains the information applicable to any plugin, independently of its type). It always starts with an API version number and for Daemon plugins this is all it contains. For other plugin types it may also contain plugin methods that the server will call, but Daemon plugins are not designed to communicate with the server, and their descriptor structure contains nothing besides the version: struct st_mysql_daemon my_daemon_plugin ={ MYSQL_DAEMON_INTERFACE_VERSION }; Next we have the name member, which specifies the name of the plugin as it will be used by MySQL. This is the name that needs to be used in the INSTALL PLUGIN statement, the name that will be seen in SHOW PLUGINS and SHOW ENGINES, the name that all plugin configuration variables and command-line options will start from. That is, the name of the plugin should be a valid SQL identifier and should be good for the command line too. A safe choice would be a name that consists of only Latin letters, digits, and an underscore. Plugin names are not case-sensitive. The author member is a string containing details about the author; it can contain anything we wish. It must be in UTF-8 and can be arbitrarily long, but MySQL will only show the first 64 characters of it. The final string member is descr, which should contain a description of the plugin. Again we are free to put whatever we like here, but we would normally put a short line stating what the plugin does. Again, it is supposed to be UTF-8, but it can be as long as you want. In the next member, each plugin specifies its license. This does not strictly do anything as such, but should help with accidental distribution of a plugin under the wrong license. There are currently three possible values for the license member: Then we come to the init and deinit members, which are pointers to the plugin initialization and de-initialization functions. The initialization function is called when the plugin is loaded during INSTALL PLUGIN or server startup. The de-initialization function is called when a plugin is unloaded, which, again, can happen for two reasons, UNINSTALL PLUGIN or server shutdown. In a Daemon plugin the initialization function is often used to fork a thread to run the main function of the plugin. Both the initialization and the de-initialization functions should return 0 on success or 1 on failure. The version member should be used for the current version of our plugin. A two-component version is encoded as a hexadecimal number, where the lower 8 bits store the second component (minor version) and all others bits store the first component (major version). For example, if the version is set to 0x205, MySQL will show it as "2.5", and if the version is set to 0x123FF, MySQL will show it as "291.255". Unfortunately, there is no way to store in this member a more complex version such as "1.10.14b-RC2". MySQL has many status variables that can be seen with the SHOW STATUS statement, and there are different third-party tools that analyze this data, how the status variables change over time, draw graphs, and so on. A plugin can benefit from that and make its status and various statistics and performance values visible as MySQL status variables. A pointer to the list of the plugin status variables is stored in the status_vars member. Similarly, there is a SHOW VARIABLES statement. It lists all MySQL system variables, variables that are used to alter the behavior of the server. They can have serverwide or session-only effect, some of them can be set on the command line or in the configuration file. They can be modifiable run-time or read-only. This is all available to plugins too. A plugin can add new system variables to the server, global or session, with or without command-line option support, modifiable or read-only. As we would expect, a pointer to the array of these variables goes into the system_vars member. Finally there is one __reserved1 member, which is unused in MySQL 5.1 and should be set to NULL. MySQL provides two macros that help to declare plugins. A plugin declaration starts from the mysql_declare_plugin() macro. It takes one argument, a unique identifier for this plugin library, it will be used automatically as needed to avoid name clashes when plugins are linked statically into the server. This identifier must be the same one that was used as a plugin name in the plug.in file. We can put many plugins in one library, but they all need to be declared in one place, after the mysql_declare_plugin() macro, separated by commas. We end the list of plugin declarations with a mysql_declare_plugin_end macro. A complete example of the plugin declarations can be seen as follows: mysql_declare_plugin(my_plugin){ MYSQL_DAEMON_PLUGIN, &my_plugin_info, "my_plugin", "Andrew Hutchings (Andrew.Hutchings@Sun.COM)", "Daemon example, shows a declaration", PLUGIN_LICENSE_GPL, my_plugin_init, my_plugin_deinit, 0x0100, NULL, NULL, NULL},{ MYSQL_DAEMON_PLUGIN, &my_plugin2_info, "my_plugin2", "Sergei Golubchik (serg@mariadb.org)", "Another Daemon example, shows a declaration", PLUGIN_LICENSE_GPL, my_plugin2_init, NULL, 0xC0FFEE, status, vars, NULL}mysql_declare_plugin_end; This declares two plugins. We can see that the first one: is a Daemon plugin has an info structure called my_plugin_info is called my_plugin and was written by me (Andrew Hutchings) is described as an example plugin is GPL licensed has initialization and de-initialization functions is of version 1.0 has no system or status variables The second plugin can be interpreted similarly. It is also a Daemon plugin of version 49407.238 with initialization function, without de-initialization function, with both status and system variables.

(For more resources on MySQL, see here.) An idea of the HTML engine Ever thought about what your tables might look like? Why not represent a table as a <TABLE>? You would be able to see it, visually, in any browser. Sounds cool. But how could we make it work? We want a simple engine, not an all-purpose Swiss Army Knife HTML-to-SQL converter, which means we will not need any existing universal HTML or XML parsers, but can rely on a fixed file format. For example, something like this: <html><head><title>t1</title></head><body><table border=1><tr><th>col1</th><th>other col</th><th>more cols</th></tr><tr><td>data</td><td>more data</td><td>more data</td></tr><!-- this row was deleted ... --><tr><td>data</td><td>more data</td><td>more data</td></tr>... and so on ...</table></body></html> But even then this engine is way more complex than the previous example, and it makes sense to split the code. The engine could stay, as usual, in the ha_html.cc file, the declarations in ha_html.h, and if we need any utility functions to work with HTML we can put them in the htmlutils.cc file. Flashback A storage engine needs to declare a plugin and an initialization function that fills a handlerton structure. Again, the only handlerton method that we need here is a create() method. #include "ha_html.h"static handler* html_create_handler(handlerton *hton, TABLE_SHARE *table, MEM_ROOT *mem_root){ return new (mem_root) ha_html(hton, table);}static int html_init(void *p){ handlerton *html_hton = (handlerton *)p; html_hton->create = html_create_handler; return 0;}struct st_mysql_storage_engine html_storage_engine ={ MYSQL_HANDLERTON_INTERFACE_VERSION };mysql_declare_plugin(html){ MYSQL_STORAGE_ENGINE_PLUGIN, &html_storage_engine, "HTML", "Sergei Golubchik", "An example HTML storage engine", PLUGIN_LICENSE_GPL, html_init, NULL, 0x0001, NULL, NULL, NULL}mysql_declare_plugin_end; Now we need to implement all of the required handler class methods. Let's start with simple ones: const char *ha_html::table_type() const{ return "HTML";}const char **ha_html::bas_ext() const{ static const char *exts[] = { ".html", 0 }; return exts;}ulong ha_html::index_flags(uint inx, uint part, bool all_parts) const{ return 0;}ulonglong ha_html::table_flags() const{ return HA_NO_TRANSACTIONS | HA_REC_NOT_IN_SEQ | HA_NO_BLOBS;}THR_LOCK_DATA **ha_html::store_lock(THD *thd, THR_LOCK_DATA **to, enum thr_lock_type lock_type){ if (lock_type != TL_IGNORE && lock.type == TL_UNLOCK) lock.type = lock_type; *to ++= &lock; return to;} These methods are familiar to us. They say that the engine is called "HTML", it stores the table data in files with the .html extension, the tables are not transactional, the position for ha_html::rnd_pos() is obtained by calling ha_html::position(), and that it does not support BLOBs. Also, we need a function to create and initialize an HTML_SHARE structure: static HTML_SHARE *find_or_create_share( const char *table_name, TABLE *table){ HTML_SHARE *share; for (share = (HTML_SHARE*)table->s->ha_data; share; share = share->next) if (my_strcasecmp(table_alias_charset, table_name, share->name) == 0) return share; share = (HTML_SHARE*)alloc_root(&table->s->mem_root, sizeof(*share)); bzero(share, sizeof(*share)); share->name = strdup_root(&table->s->mem_root, table_name); share->next = (HTML_SHARE*)table->s->ha_data; table->s->ha_data = share; return share;} It is exactly the same function, only the structure is now called HTML_SHARE, not STATIC_SHARE. Creating, opening, and closing the table Having done the basics, we can start working with the tables. The first operation, of course, is the table creation. To be able to read, update, or even open the table we need to create it first, right? Now, the table is just an HTML file and to create a table we only need to create an HTML file with our header and footer, but with no data between them. We do not need to create any TABLE or Field objects, or anything else—MySQL does it automatically. To avoid repeating the same HTML tags over and over we will define the header and the footer in the ha_html.h file as follows: #define HEADER1 "<html><head><title>"#define HEADER2 "</title></head><body><table border=1>\n"#define FOOTER "</table></body></html>"#define FOOTER_LEN ((int)(sizeof(FOOTER)-1)) As we want a header to include a table name we have split it in two parts. Now, we can create our table: int ha_html::create(const char *name, TABLE *table_arg, HA_CREATE_INFO *create_info){ char buf[FN_REFLEN+10]; strcpy(buf, name); strcat(buf, *bas_ext()); We start by generating a filename. The "table name" that the storage engine gets is not the original table name, it is converted to be a safe filename. All "troublesome" characters are encoded, and the database name is included and separated from the table name with a slash. It means we can safely use name as the filename and all we need to do is to append an extension. Having the filename, we open it and write our data: FILE *f = fopen(buf, "w"); if (f == 0) return errno; fprintf(f, HEADER1); write_html(f, table_arg->s->table_name.str); fprintf(f, HEADER2 "<tr>"); First, we write the header and the table name. Note that we did not write the value of the name argument into the header, but took the table name from the TABLE_SHARE structure (as table_arg->s->table_name.str), because name is mangled to be a safe filename, and we would like to see the original table name in the HTML page title. Also, we did not just write it into the file, we used a write_html() function—this is our utility method that performs the necessary entity encoding to get a well-formed HTML. But let's not think about it too much now, just remember that we need to write it, it can be done later. Now, we iterate over all fields and write their names wrapped in <th>...</th> tags. Again, we rely on our write_html() function here: for (uint i = 0; i < table_arg->s->fields; i++) { fprintf(f, "<th>"); write_html(f, table_arg->field[i]->field_name); fprintf(f, "</th>"); } fprintf(f, "</tr>"); fprintf(f, FOOTER); fclose(f); return 0;} Done, an empty table is created. Opening it is easy too. We generate the filename and open the file just as in the create() method. The only difference is that we need to remember the FILE pointer to be able to read the data later, and we store it in fhtml, which has to be a member of the ha_html object: int ha_html::open(const char *name, int mode, uint test_if_locked){ char buf[FN_REFLEN+10]; strcpy(buf, name); strcat(buf, *bas_ext()); fhtml = fopen(buf, "r+"); if (fhtml == 0) return errno; When parsing an HTML file we will often need to skip over known patterns in the text. Instead of using a special library or a custom pattern parser for that, let's try to use scanf()—it exists everywhere, has a built-in pattern matching language, and it is powerful enough for our purposes. For convenience, we will wrap it in a skip_html() function that takes a scanf() format and returns the number of bytes skipped. Assuming we have such a function, we can finish opening the table: skip_html(fhtml, HEADER1 "%*[^<]" HEADER2 "<tr>"); for (uint i = 0; i < table->s->fields; i++) { skip_html(fhtml, "<th>%*[^<]</th>"); } skip_html(fhtml, "</tr>"); data_start = ftell(fhtml); We skip the first part of the header, then "everything up to the opening angle bracket", which eats up the table name, and the second part of the header. Then we skip individual row headers in a loop and the end of row </tr> tag. In order not to repeat this parsing again we remember the offset where the row data starts. At the end we allocate an HTML_SHARE and initialize lock objects: share = find_or_create_share(name, table); if (share->use_count++ == 0) thr_lock_init(&share->lock); thr_lock_data_init(&share->lock,&lock,NULL); return 0;} Closing the table is simple, and should not come as a surprise to us: int ha_html::close(void){ fclose(fhtml); if (--share->use_count == 0) thr_lock_delete(&share->lock); return 0;}

(For more resources on similar content, see here.) If all you need in a SELECT query is ordering all the data in a table by just one of its column, all you need to do is putting the column in the query’s ORDER BY clause. SELECT TITLE, ISBN, PUBLISH_DATEFROM BOOKORDER BY ISBN DESC What if you need to order by more than column, i.e. hierarchical ordering? Again, just have all the ordering columns in the ORDER BY. You need to sequence them to command the rank of ordering: The first column is the primary; the second column, secondary; and so forth, next columns down the ordering rank. SELECT TITLE, ISBN, PUBLISH_DATEFROM BOOKORDER BY TITLE, ISBN, PUBLISH_DATE DESC But, what if the PUBLISH_DATE’s data format is MM-YYYY-DD? Which one of 12-2010-20, 12-2009-30, and 10-2010-01, is higher or lower than the others? This format is not good for ordering, as you need to order the dates by their year, then by month, and lastly by date. This article shows how to solve various ordering cases in SELECT queries. Derived Let’s first have a look at the SELECT query for solving the PUBLISH_DATE ordering. (All SELECT queries in this article are tested to run correctly in Oracle database.) If you run: SELECT PUBLISH_DATEFROM BOOKORDER BY PUBLISH_DATE DESC On the following publish dates: Its query output is: While we expect its output should be: We need to order by year (YYYY), then by month (MM), and lastly by date (DD). SELECT * FROM BOOKORDER BY SUBSTR(PUBLISH_DATE, 4, 4) DESC, SUBSTR(PUBLISH_DATE, 1, 2) DESC, SUBSTR(PUBLISH_DATE, 8,2) DESC This kind of derived 'column' for ordering can be applied to other than date. Translated What if the data format of the PUBLISH_DATE is MMM-YYYY-DD? The month is its first three characters name, not its number. Is then JAN-2009-01 higher or lower than APR-2010-01? We need to translate the month name into number using a translation reference table: Our SELECT query now becomes: SELECT PUBLISH_DATE, MONTH_NAME, MONTH_NO FROM BOOK, MONTH_TRANSLATION_REFWHERE SUBSTR(PUBLISH_DATE,1,3) = MONTH_NAMEORDER BY SUBSTR(PUBLISH_DATE, 4, 4) DESC, MONTH_NO DESC, SUBSTR(PUBLISH_DATE, 8,2) DESC If you run the query on the following publish dates: The query output is: You might have noticed that the query also applies the 'derived' ordering from the previous example. The month translation reference table we use in this example is based on a common predefined convention, that Jan is first (1); February is second (2), and so on. You are not limited to this kind of table; you can set up any custom translation reference table to suit your ordering need.

You can listen to the podcast here, or hit play in the media player below.

ePub (short for electronic publication) is a free and open eBook standard by the International Digital Publishing Forum (IDPF). ePub allows greater flexibility with content. With an ePub-formatted book, the display of text can be optimized for the reader's device.  Other ePub features include improved design and layout, Inline raster and vector images, better search functionality, while enabling DRM protection and embedded metadata. The company’s marketing manager Damian Carvill said “Packt listens to all feedback from customers and attempts to act on it accordingly. After receiving a number of requests, we made it a priority to convert all of our existing eBooks into the ePub format. All of our future books, and we hope to publish over 20 in July, will also be available in this popular eBook format.” Packt is inviting all customers, who have purchased an eBook, to download the ePub version of the book and enjoy its features and great flexibility. Alternatively, customers can sample the ePub format by accessing one of Packt’s free eBooks. All purchases of new eBooks from today will be made available to download as an ePub file, as well as the standard PDF. Packt ePub formatted eBooks are available from Tuesday 20th July 2010. To access your first Packt ePub eBook, either previously purchased or one of Packt’s free eBooks, please log into www.PacktPub.com and go to My account.  

Templates Templates are predesigned report layouts. Building a report from a template Templates, for the most part, take the leg work out of having to recreate the same layout over and over again.With templates, we could either use the existing, canned Listing Report template or build our own, saving your time for more complex tasks. It's as simple as creating a new report, using a template, and following the Cheat Sheets. In this example, we are going to use a Grouped Listing report template to create a simple Employee Sales Detail report, which we will use as a target for a drill down report. Create a new report called Employee Sales Details Report.rptDesign.Do not go through the wizard; stop at the template selection dialog. On the New Report – Template screen, select Grouped Listing report. When the report design opens, the Cheat Sheet is open on the right-hand side. It lays out a series of steps to create a basic report. As we already have the data source created in our library, go ahead and add the data source from the library, and click on the skip option for the data source hint. For the dataset, click on the Click to perform option. Name the dataset Get Employee Sales, and use the following query: select EMPLOYEES.EMPLOYEENUMBER, EMPLOYEES.LASTNAME || ', ' || EMPLOYEES.FIRSTNAME name, ORDERDETAILS.PRICEEACH sales, ORDERS.ORDERDATEfrom EMPLOYEES, CUSTOMERS, ORDERS, ORDERDETAILSwhere ORDERS.ORDERNUMBER = ORDERDETAILS.ORDERNUMBER and EMPLOYEES.EMPLOYEENUMBER = CUSTOMERS. SALESREPEMPLOYEENUMBER and ORDERS.CUSTOMERNUMBER = CUSTOMERS.CUSTOMERNUMBER and ORDERS.ORDERDATE between ? and ? Create two report parameters called startDate and endDate, and bind them to the data set parameters. For startDate, use the default value of 2005-01-01, and for endDate, use the default parameter of 2005-04-01. When we are back at the Cheat Sheet, we click on Click to Complete for the dataset. For the Edit Date Binding cheat sheet section, drag the fields over like next screenshot. Make the Group Header row appear with a silver background and the text appear in bold. When we drag EmployeeNumber over, it will automatically create the group on the EmployeeNumber express. Just be sure to drag it to the GroupHeader section as illustrated in the screenshot. Select the column with the Name and OrderDate fields, and select Suppress duplicates.

Stylesheets BIRT, being a web-based reporting environment, takes a page from general web development toolkits by importing stylesheets. However, BIRT stylesheets function slightly differently to regular stylesheets in a web development environment. We are going to add on to the Customer Orders report we have been working with, and will create some styles that will be used in this report. Open Customer Order.rptDesign. Right-click on the getCustomerInformation dataset and choose Insert into Layout. Modify the table visually to look like the next figure. Create a new dataset called getCustomerOrders using the following query: //insert code 1 Link the dataset parameter to rprmCustomerID. Save the dataset, right-click on it, and select Insert to layout. Select the first ORDERNUMBER column. Under the Property Editor, Select Advanced. In the Property Editor, go to the Suppress duplicates option, and change it to true. This will prevent the OrderNumber data item from repeating the value it displays down the page. In the Outline, right-click on Styles and choose New Style…. In the Pre-Defined Style drop down, choose table-header. A predefined style is an element that is already defined in the BIRT report. When selecting a predefined style, this will affect every element of that type within a report. In this case, for every table in the report, the table header will have this style applied. Under the Font section, apply the following settings: Font: Sans-Serif Font Color: White Size: Large Weight: Bold Under the Background section, set the Background Color to >b>Black. Click OK. Now, when we run the report, we can see that the header line is formatted with a black background and white font. Custom stylesheets In the example we just saw, we didn't have to apply this style to any element, it was automatically applied to the header of the order details table as it was using a predefined style. This would be the case for any table that had the header row populated with something and the same is the case for any of the predefined styles in BIRT. So next, let's look at a custom defined style and apply it to our customer information table. Right-click on the Styles section under the Outline tab and create a new style. Under the Custom Style textbox, enter CustomerHeaderInfo. Under the Font section, enter the following information: Font: Sans Serif Color: White Size: Large Weight: Bold Under the Background section, set the Background Color to Gray. Under the Box section, enter 1 points for all sections. Under the Border section, enter the following information: Style (All): Solid Color (All): White Width (All): Thin Click OK and then click Save. Select the table which contains the customer information. Select the first column. Under the Property Editor, in the list box for the Styles, select CustomerHeaderInfo. The preview report will look like the following screenshot: Right-click on the Styles section, and create a new custom style called CustomerHeaderData. Under Box, put in 1 points for all fields. Under Border, enter the following information: Style – Top: Solid Style – Bottom: Solid Color (All): Gray Click OK. Select the Customer Information table. Select the second column. Right-click on the column selector and select Style | Apply Style | CustomHeaderData. The finished report should look something like the next screenshot: