At the Forge - Databases and Calendars
Now that we have a defined table and indexes, we can start to populate our database table with some events. We can, as always, INSERT new events into our table with the following syntax:
INSERT INTO Events (event_summary, event_location, event_start, event_end) VALUES ('Ides of March', 'Everywhere', '2005-March-15 00:00', '2005-March-15 23:59:59')
As you can see, the above INSERT statement names only four of the six columns defined in Events. When we check our new row, we find the following:
atf=# select * from events; -[ RECORD 1 ]---+--------------------------- event_id | 1 event_summary | Ides of March event_location | Everywhere event_start | 2005-03-15 00:00:00 event_end | 2005-03-15 23:59:59 event_timestamp | 2005-04-04 01:20:15.575032
As you can see, event_id (which we defined to be of type SERIAL) has automatically received a value of 1. Furthermore, event_timestamp has been set with the date and time at which we executed the query.
It's easy to imagine how we could invoke this INSERT statement with a Web-based program using CGI or a more advanced system, such as mod_perl or Zope. Indeed, we really don't have to think much about how the data has arrived in the database, particularly if we have set appropriate constraints on our data. We can assume that whatever resides in the database is reliable, and that the server has rejected any entries that would violate our rules.
Now that we have some activities in our database table, we can retrieve them into a CGI program. That program then produces output in iCalendar format, allowing iCalendar clients to retrieve its data. Listing 1 contains the program, which is a modified version of last month's dynamic-calendar.py program. As I mentioned last month, I wrote this program in Python in no small part because of the relative dearth of modules to create iCalendar-format files. Fortunately, there is such a module for Python, and I have taken advantage of that fact in this program.
Listing 1. db-calendar.py
#!/usr/bin/python # Grab the CGI module import cgi import psycopg from iCalendar import Calendar, Event from datetime import datetime from iCalendar import UTC # timezone # Log any problems that we might have import cgitb cgitb.enable(display=0, logdir="/tmp") # Send a content-type header print "Content-type: text/calendar\n\n" # Create a calendar object cal = Calendar() # What product created the calendar? cal.add('prodid', '-//Python iCalendar 0.9.3//mxm.dk//') # Version 2.0 corresponds to RFC 2445 cal.add('version', '2.0') # Create the database connection db_connection = psycopg.connect('dbname=atf user=reuven') db_cursor = db_connection.cursor() db_cursor.execute ('''SELECT event_id, event_summary, event_location, event_start, event_end, event_timestamp FROM Events ORDER BY event_start''') result_rows = db_cursor.fetchall() for row in result_rows: # Create one event event = Event() # Set the event ID event['uid'] = str(row) + 'id@ATF' # Set the description and location event.add('summary', row) event.add('location', row) # Transform the dates appropriately event.add('dtstart', datetime(tzinfo=UTC(), *row.tuple()[0:5])) event.add('dtend', datetime(tzinfo=UTC(), *row.tuple()[0:5])) event.add('dtstamp', datetime(tzinfo=UTC(), *row.tuple()[0:5])) # Give this very high priority! event.add('priority', 5) # Add the event to the calendar cal.add_component(event) # Ask the calendar to render itself as an iCalendar # file, and return that file in an HTTP response print cal.as_string()
As you can see in Listing 1, the program is fairly straightforward. After importing a number of modules, we create a calendar object and insert the iCalendar-mandated fields indicating the source of the calendar.
We then connect to a PostgreSQL server, which is presumed to be on the local computer. Although several database adaptors exist in Python for PostgreSQL access, I have long used psycopg, which is both fast and stable. To connect to PostgreSQL with psycopg, we use the following syntax:
db_connection = psycopg.connect ('dbname=atf user=reuven')
The above indicates that the database name is atf and the user name is reuven. You also might need to specify the server and a password as additional arguments, especially if you are working on a production system.
Once we have connected to the database, we get a cursor, which allows us to submit queries and get their results:
db_cursor = db_connection.cursor()
With a cursor in hand, we now can send our SQL query to the database, using Python's triple-quote functionality to make our SQL more readable. Now we retrieve our results. If we were expecting to retrieve dozens or hundreds of rows, we probably would want to get them one at a time, or perhaps in batches. But I know that this calendar will contain only a few events, so I use the fetchall() method to get them in one large sequence:
result_rows = db_crsor.fetchall()
Each element of result_rows is a row from our PostgreSQL database. We thus iterate (in a for loop) over the rows, retrieving the different elements that appear.
For the most part, this is pretty straightforward. However, things get a bit tricky when we are working with dates and times—important elements of any calendar of events! The problem is that psycopg uses the open-source mxDateTime module from eGenix.com, which makes working with dates extremely easy. But mxm's iCalendar module uses Python's datetime module, which is different. We thus need to retrieve each of the dates (for the event's starting time, ending time and stamp), turn them from an instance of mxDateTime into a datetime-compatible tuple, use that tuple to create an instance of datetime and then pass that to event.add, using the three calls starting with:
event.add('dtstart', datetime(tzinfo=UTC(), event.add('dtend', datetime(tzinfo=UTC(), *row.tuple()[0:5]))
The second argument to datetime() in the above three rows of code does exactly what we said. It retrieves one column from the returned row and turns it into a tuple. We then take a slice of the sequence (with Python's convenient [0:5] notation) to grab a subset of the items returned by tuple().
But we can't pass datetime() a sequence; rather, it is expecting a number of individual elements. In other words, datetime() wants several numbers, not a reference or pointer to a list of numbers. We turn the tuple into its individual elements with Python's * operator. Finally, sharp-eyed readers will notice that we have passed the tzinfo argument before the individual elements of the tuple; this is because Python requires that we pass named arguments before the * operator.
Practical Task Scheduling Deployment
July 20, 2016 12:00 pm CDT
One of the best things about the UNIX environment (aside from being stable and efficient) is the vast array of software tools available to help you do your job. Traditionally, a UNIX tool does only one thing, but does that one thing very well. For example, grep is very easy to use and can search vast amounts of data quickly. The find tool can find a particular file or files based on all kinds of criteria. It's pretty easy to string these tools together to build even more powerful tools, such as a tool that finds all of the .log files in the /home directory and searches each one for a particular entry. This erector-set mentality allows UNIX system administrators to seem to always have the right tool for the job.
Cron traditionally has been considered another such a tool for job scheduling, but is it enough? This webinar considers that very question. The first part builds on a previous Geek Guide, Beyond Cron, and briefly describes how to know when it might be time to consider upgrading your job scheduling infrastructure. The second part presents an actual planning and implementation framework.
Join Linux Journal's Mike Diehl and Pat Cameron of Help Systems.
Free to Linux Journal readers.Register Now!
- SUSE LLC's SUSE Manager
- Murat Yener and Onur Dundar's Expert Android Studio (Wrox)
- My +1 Sword of Productivity
- Managing Linux Using Puppet
- Non-Linux FOSS: Caffeine!
- Doing for User Space What We Did for Kernel Space
- SuperTuxKart 0.9.2 Released
- Parsing an RSS News Feed with a Bash Script
- Google's SwiftShader Released
- Rogue Wave Software's Zend Server
With all the industry talk about the benefits of Linux on Power and all the performance advantages offered by its open architecture, you may be considering a move in that direction. If you are thinking about analytics, big data and cloud computing, you would be right to evaluate Power. The idea of using commodity x86 hardware and replacing it every three years is an outdated cost model. It doesn’t consider the total cost of ownership, and it doesn’t consider the advantage of real processing power, high-availability and multithreading like a demon.
This ebook takes a look at some of the practical applications of the Linux on Power platform and ways you might bring all the performance power of this open architecture to bear for your organization. There are no smoke and mirrors here—just hard, cold, empirical evidence provided by independent sources. I also consider some innovative ways Linux on Power will be used in the future.Get the Guide