Embperl and Databases
This month, we will create a database consisting of a single table, a list of clients for a consulting practice. One of the central tables in this system is the Clients table, which contains basic information about each client.
Here is the SQL necessary to create this table:
CREATE TABLE Clients ( id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(40) NOT NULL, address1 VARCHAR(40) NOT NULL, address2 VARCHAR(40) NULL, city VARCHAR(40) NOT NULL, state VARCHAR(40) NULL, country VARCHAR(40) NOT NULL, zip VARCHAR(40) NULL, contact_name VARCHAR(40) NOT NULL, contact_phone1 VARCHAR(40) NOT NULL, contact_phone2 VARCHAR(40) NULL, contact_fax VARCHAR(40) NULL, initial_contact_date DATE NULL, dollars_per_hour TINYINT NOT NULL, UNIQUE (name) );
Again, we cannot enter this SQL directly into a relational database server; we must use a program that has been compiled with the correct client libraries. MySQL comes with a program (mysql) that allows interactive communication with the database; alternatively, we can use DBI to send the above SQL.
Each column in Clients is defined as a VARCHAR, that is, a variable-length text field. The length of the field is determined by the number in parentheses, which I set to 40 mostly to make other elements of the programming easier. (Over time, I expect to make most of these fields quite a bit shorter.)
The id field is special, not only because we define it as an unsigned integer (giving us the option of including up to 16 million different clients), but because it is set to be the “primary key”. As far as the database is concerned, each row can be identified uniquely with the primary key alone. We set id to AUTO_INCREMENT, meaning that MySQL will give the first client an ID of 1 in the archive file, the second an ID of 2 and so forth. Each client will receive an automatically generated, unique ID number.
We also declare the name column to be unique, since having more than one client with a given name could be confusing for the people involved. The database would accept several identically named columns, as long as the ID numbers were different. However, we will avoid the possibility of having two clients named “IBM” by checking for this in the database.
You may wonder why we didn't use name as the primary key, since it is guaranteed to be unique. We could have done so, and everything would work fine (perhaps a bit slower, since text strings are larger than integers). But consider what will happen if a client changes its name—we would have to update all of the references to that client, since old ones will no longer point to the right place. By making our primary key independent of any information the client changes, we can continue to keep track of the client regardless of what information changes.
Now that we have defined our table, we will create an Embperl document that will let us insert new records. (Right now, our table is empty.) An Embperl document is largely the same as an HTML document, so you can use the <H1>, <P> and <Blink> tags as well as regular text, and it will work just fine.
However, you can insert Perl code within the Embperl document by putting it within special square brackets. Here are the four types of square brackets that Embperl understands:
[- CODE -]: Evaluate CODE.
[+ CODE +]: Evaluate CODE, inserting the final value into the HTML document.
[! CODE !]: Evaluate CODE as [- CODE -], but only once.
[$ Meta-code $]: Evaluate Embperl meta-commands.
Thus, we can include this statement:
[- $foo = 5; -]and $foo will be set to 5—a value that persists over multiple invocations, since mod_perl and Embperl cache such values. If instead we include:
[+ $foo = 5; +]then a “5” will appear in the document where the brackets were. If you are unfamiliar with the idea of a “final value from an expression”, you might want to end every Embperl block with the name of a variable. Variables return their values, so if you type:
[+ @reverse_list = reverse @list; $foo +]then a “5” will be inserted into the HTML document at that point.
Listing 1, add-client.html, is a simple Embperl document that adds a client to the database. It does not check the data we hand it—since MySQL will do much of that for us—although it will show the user any database errors that might occur.
|Designing Electronics with Linux||May 22, 2013|
|Dynamic DNS—an Object Lesson in Problem Solving||May 21, 2013|
|Using Salt Stack and Vagrant for Drupal Development||May 20, 2013|
|Making Linux and Android Get Along (It's Not as Hard as It Sounds)||May 16, 2013|
|Drupal Is a Framework: Why Everyone Needs to Understand This||May 15, 2013|
|Home, My Backup Data Center||May 13, 2013|
- Linux Systems Administrator
- New Products
- Senior Perl Developer
- Technical Support Rep
- UX Designer
- Designing Electronics with Linux
- Dynamic DNS—an Object Lesson in Problem Solving
- Using Salt Stack and Vagrant for Drupal Development
- Making Linux and Android Get Along (It's Not as Hard as It Sounds)
- Favorite (and easily brute-forced) pw's
24 min 15 sec ago
- Have you tried Boxen? It's a
6 hours 16 min ago
- seo services in india
10 hours 47 min ago
- For KDE install kio-mtp
10 hours 48 min ago
- Evernote is much more...
12 hours 48 min ago
- Reply to comment | Linux Journal
21 hours 33 min ago
- Dynamic DNS
22 hours 7 min ago
- Reply to comment | Linux Journal
23 hours 6 min ago
- Reply to comment | Linux Journal
23 hours 56 min ago
- Not free anymore
1 day 3 hours ago
Enter to Win an Adafruit Pi Cobbler Breakout Kit for Raspberry Pi
It's Raspberry Pi month at Linux Journal. Each week in May, Adafruit will be giving away a Pi-related prize to a lucky, randomly drawn LJ reader. Winners will be announced weekly.
Fill out the fields below to enter to win this week's prize-- a Pi Cobbler Breakout Kit for Raspberry Pi.
Congratulations to our winners so far:
- 5-8-13, Pi Starter Pack: Jack Davis
- 5-15-13, Pi Model B 512MB RAM: Patrick Dunn
- 5-21-13, Prototyping Pi Plate Kit: Philip Kirby
- Next winner announced on 5-27-13!
Free Webinar: Hadoop
How to Build an Optimal Hadoop Cluster to Store and Maintain Unlimited Amounts of Data Using Microservers
Realizing the promise of Apache® Hadoop® requires the effective deployment of compute, memory, storage and networking to achieve optimal results. With its flexibility and multitude of options, it is easy to over or under provision the server infrastructure, resulting in poor performance and high TCO. Join us for an in depth, technical discussion with industry experts from leading Hadoop and server companies who will provide insights into the key considerations for designing and deploying an optimal Hadoop cluster.
Some of key questions to be discussed are:
- What is the “typical” Hadoop cluster and what should be installed on the different machine types?
- Why should you consider the typical workload patterns when making your hardware decisions?
- Are all microservers created equal for Hadoop deployments?
- How do I plan for expansion if I require more compute, memory, storage or networking?