Data Modeling with Alzabo
Now that we have installed Alzabo, let's create a simple database schema using the browser-based design tool. Admittedly this is not as slick as commercial or client-side tools, but it does the job rather well.
Begin by creating a new schema (known in PostgreSQL and MySQL parlance as a database) to which you must give a name. The schema must be a legitimate database name within either PostgreSQL or MySQL. I choose to work with PostgreSQL because of its built-in referential integrity, foreign keys, views and triggers, as well as a more standard dialect of SQL and the ability to write stored procedures in a variety of languages.
Let's create a simple phone book and appointment calendar using Alzabo. We will keep track of people we know, their addresses and telephone numbers, and appointments we have scheduled with them. Using this database, we can learn about the people with whom we're meeting on a given day or about all of the appointments with a given person.
To create this schema, we point our web browser at the URL alzabo/schema under the Mason directory we mentioned earlier (on my computer, I pointed the browser to http://localhost/mason/alzabo/schema.) This brings up the schema creation/editing page that allows us to edit an existing schema, create a new one or reverse-engineer an existing one. While the last option is the most interesting, allowing you to access legacy databases using Alzabo, we will create a new schema. I entered the name (I chose addressbook, for lack of a better idea) and indicated that we wish to use PostgreSQL as our back-end database.
After clicking on “submit”, several possibilities were presented: I could add a new table to this schema, delete the entire schema or examine the SQL that Alzabo will generate automatically. Right now, of course, there isn't any SQL to display. Over time, we will see this SQL grow considerably.
However, because Alzabo has not created any SQL doesn't mean that no work has been done on the back end. Indeed, Alzabo automatically created the addressbook directory within /usr/local/alzabo/schemas, containing three files: addressbook.create.alz and addressbook.runtime.alz (both are stored in a binary format) and addressbook.rdbms, which contains the single word PostgreSQL. In this way, Alzabo tracks the database server in which the schema is stored.
Once inside the addressbook schema, I added a “People” table by entering “People” in the “add a table” text field and clicking on “submit”. (PostgreSQL ignores case in table and column names, but I like Joe Celko's convention of initial caps for Table Names, all lowercase for column names and all caps for SQL RESERVED WORDS.)
Within my People table, I created columns, each of a different data type. Alzabo offers a menu of potential data types, but we can enter our own if we want; this can be particularly useful in PostgreSQL, which allows us to create our own data types.
I generally prefer to work with synthetic primary keys in such a table, giving each row its own value. In PostgreSQL, we accomplish this using the SERIAL data type. But you will notice that no such data type exists in the Alzabo selection list. You might be tempted to indicate that this is an INTEGER column and to mark the “sequenced” check box at the bottom of the column editor. Doing so, however, will create an INTEGER column, as well as a totally unrelated PostgreSQL sequence object. Rather, to get a synthetic primary key you must manually enter SERIAL in the text field below the <select> list of column types.
An additional check box lets you indicate if a column is the primary key and automatically marks it with “pk” in column listings. And a third check box allows you to indicate if a column may contain NULL values, a subtle way of reminding new database designers that NULLs complicate life and should be avoided whenever possible.
To create a foreign key (REFERENCES) or CHECK clause, add it in the “attributes” text fields toward the bottom of the HTML form. Remember that you're only modeling the schema in Perl at this point, meaning that you will be free to add and remove such clauses in the future without having to send ALTER TABLE queries to the database. You also can create indices on one or more columns using the Alzabo editor.
You can use the Alzabo table and column editors to create many tables and columns, moving between them using a set of hierarchical menus and listings. The Alzabo display even places “<” and “>” marks next to each column, allowing you to move them relative to each other within a particular definition.
As you work with the browser-based schema editor, I suggest that you occasionally preview the SQL that Alzabo generates. Not only will this ensure that Alzabo is doing the right thing (as we saw with the SERIAL column), but it will give you a better sense of the low-level details your schema is creating.
After you have finished creating the schema, use the “execute SQL” button from within the “SQL preview” page to send your SQL to the database server. If the database server returns any errors, Alzabo will produce a lengthy and detailed error message describing what happened.
In some cases, you may need to fix your table or column definitions, while in others you may need to ensure that the server is running with the correct permissions. Also ensure that you have defined a PostgreSQL user (created with the command-line createuser program) whose name matches the username under which Apache runs, unless you explicitly name another user in the HTML form.
Fast/Flexible Linux OS Recovery
On Demand Now
In this live one-hour webinar, learn how to enhance your existing backup strategies for complete disaster recovery preparedness using Storix System Backup Administrator (SBAdmin), a highly flexible full-system recovery solution for UNIX and Linux systems.
Join Linux Journal's Shawn Powers and David Huffman, President/CEO, Storix, Inc.
Free to Linux Journal readers.Register Now!
- Download "Linux Management with Red Hat Satellite: Measuring Business Impact and ROI"
- Sony Settles in Linux Battle
- Peppermint 7 Released
- Libarchive Security Flaw Discovered
- Profiles and RC Files
- Maru OS Brings Debian to Your Phone
- Snappy Moves to New Platforms
- The Giant Zero, Part 0.x
- Git 2.9 Released
- Understanding Ceph and Its Place in the Market
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