background image

Significato Journal
Subscribe to our FREE E-Newsletter!
 
Normal Version Print Version

Contact Management with SQL Power: A Review of SQLite Maestro

Feb 20, 2012
Peter Falkenberg Brown

In 2011, I reviewed a simple database program called “EfficientAddress Book”, focused on its viability as a contact management program fornames, addresses, phone numbers and notes. I still recommend it for anyone looking for a fast and easy to use program.

However, I found that my years of programming databasesleft me wanting more power and more flexibility. I thus moved on from the above program and started searching for a small, fast and simple to use database program that still had plenty of power under the hood, and flexibility in daily use.

I’ve been using MySQL, Perl and PHP for quite a long time,for web databases, and I think that’s a great model, especially for a multi-user, simple to set up environment like the web. Personal contact management, however, should be vastly simpler, in my opinion, and not require an Internet connection at all.

SQLite

Coming from MySQL, I wanted to find something based on theSQL query language, rather than dBASE, xBASE, or some other proprietary file format. So, I searched and searched, and then one day, I looked again at “SQLite” (http://www.sqlite.org). It’s pronounced “Es Kue El - Ite” by its author, D.Richard Hipp. He started writing SQLite in the year 2000 while he was working at General Dynamics, designing software for the Navy to be used on board guided missile destroyer ships. I found this interesting since it demonstrated that SQLite was not programmed by a novice (despite its moniker of “Lite”), since one could assume that novices aren’t hired to work with guided missiles. At least one hopes not.

SQLite is very different from MySQL, because it doesn’trequire a “client-server” set up. In a client-server environment, the server (like MySQL) continually waits in the background for data requests from the client, in the foreground (like a web browser). SQLite doesn’t use a server. Instead, all of its data is stored in one compact data file, rather like a dBASE file, except it’s even simpler (i.e. memos and indexes are stored in the main file, unlike dBASE). Then, one fires up a “client” program, and opens the data file, and works with the data.

The client program uses a free, public domain library ofSQLite code. Based on that concept, you can store your data in an SQLite data file, and access it with any one of a large number of SQLite client programs; some free, and some not. Since the data format is open source, its very safe to store your data in the file, because you’re not tied to any one software program when you need to read or manage the data.

I don’t have that many “contacts”. (Well, what’s a lot?Dozens, hundreds?) “Contacts” is a generic, albeit rather chilly word to use for family members, friends, business associates, etc. Anyway, it’s just a word. But getting back to “a lot”. Even though my contacts aren’t in the millions, it’s nice to know that one’s database could support millions if necessary, right? SQLite can do so, with a maximum database size of 14 terabytes. See http://www.sqlite.org/limits.html for more info.

Having satisfied my power user needs, I then went lookingfor SQLite “clients” (software programs) that would allow me to quickly and easily manage my list of contacts. I’ll probably review more than one program in this space, but the first one I’m reviewing is “SQLite Maestro”.

SQLite Maestro

SQLite MaestroThe program is on the web at http://www.sqlmaestro.com/products/sqlite/maestro/ and costs $139 for a singlebusiness license. The SQL Maestro Group makes a number of other programs as well, and is located in New York City.

I looked at an assortment of free programs before Idownloaded SQLite Maestro, but was generally unsatisfied. SQLite Expert, at http://www.sqliteexpert.com, was interesting, as well as Navicat for SQLite, at http://navicat.com/en/products/navicat_sqlite/sqlite_overview.html. I mayreview those two programs at some point.

I like the interface a lot, in SQLite Maestro. It may notmatter to some, but I like my software programs to be pleasing to the eye, as well as conveniently laid out. SQLite Maestro fills both bills quite nicely. Some of the programs I looked at were just plain ugly. I shall not mention them, of course.

Speed

SQLite Maestro seems quite fast in most of its operations,on my Win7 64 bit, 8 gig laptop. However, the startup time is a bit slow. By slow, I mean about 4 seconds to load after having been started already once before. I believe that the first load after boot up is a bit longer. But, if I really used my contact list all day, every day, I would keep it minimized, and then pulling the program up would be instantaneous. The speed is certainly not a deal breaker.

The Grid View

The “Grid” view, also known as the “Browse” view in dBASE,is the row and column interface to the data that resembles a spreadsheet. This view is very important to me, because it’s the fastest way to get a bird’s eye view of the data. Happily, SQLite Maestro will open up to the grid view by default, so once the program is open, you’re looking right at your data, in the sort order that you left it in when you last looked at it. The grid view is clean and attractive, and columns can be moved around at will.

Filter Searching

Searching can be done in one of two ways. The first isvery fast, and usually suffices. At the top of the grid view there’s a “filter row”. When I type “Brown” in the last name column, in the filter cell, and hit enter, I’m immediately presented with all the rows that match that name. If I wanted to filter on more columns, I would just type in a value in the next filter cell, and narrow the search.

SQL Searching

SQL searching is one reason why I wanted to find a morepowerful contact manager. SQL searching is useful when you want to visually examine a set of rows based on simple or complex criteria, but it’s also very, very useful for reports. For me, the only report that I currently use is an HTML export of my most important contacts, with their family members column and their phone numbers.

You see, I’m a Neanderthal programmer. I’m verycomfortable typing in this SQL query:

select
      case when
           organization != ''
           then
           first_name || ' ' || last_name || ', ' ||
           organization || ', ' || department
           else
           first_name || ' ' || last_name
           end
           as name,
family,
contact_type,
phones
from contacts
where
print = 'yes'
order by last_name, first_name

(which produces a list of names for my HTML printout),BUT...

I don’t have an IPhone, or an IPad, or a Smartphone, tokeep my contact list on when I’m outside of the office. Thus, GASP, I carry my list on a piece of paper in my shirt pocket. Why don’t I have those lovely devices? Simply because I’m not out of the office that much, so I don’t need one. Yes, you can sigh, but it all works for me. Although I can see myself breaking down and buying one someday. One can’t be a Neanderthal for too long.

HTML Exports andPrintouts

So, in my own caveman like way, I run the above query, andthen I go to the handy, dandy Export function in SQLite Maestro. It exports whatever rows you have in your search, and then allows you to select the data fields (columns) that you wish to include in the export. Since my query already took care of that issue, I select all columns.

One can also use the Print function, instead of exportingto an HTML file, but I like using the HTML export, and then printing the pages from my browser, because I end up with a saved HTML file of the names, and also because the HTML export allows one to attach a CSS file which gives one complete control over the appearance. I found that especially useful with the fields I had that had multiple rows of data, such as a phone number field. Because I was using HTML, the vertical size of the rows expanded to accommodate the extra data.

The program provides the option to save the reporttemplate, which is convenient, and also has a function to save multiple tabs of SQL queries. It’s thus very easy to run the query, and then print to an HTML file when needed.

Imports and Exports

SQLite Maestro has a healthy list of file formats that itcan import from and export to, including dBASE, which I immediately made use of, since I had a file of dBASE records from the past. When I imported my dBASE data, I was pleased to see that you could map the external file’s columns to the SQLite data columns, which was extremely convenient.

Viewing, Adding,Editing and Deleting Data

It’s quite easy to add or edit data in grid mode, but theprogram also has a form view which is often faster. You right click on a row, and select form view, and then simply tab through the fields. You can also insert new records in form view, as well as move from record to record.

In grid view, I found it handy that I could click on thephone number field and view a drop down box with all the extra phone numbers for that contact, that couldn’t be seen in one row.

Deleting data is managed by selecting a row in grid viewand clicking on a toolbar icon.

Multi-TableOperations

SQLite Maestro supports links to additional tables viaforeign keys, and provides a way to view lists of related data as drop downs. Of course, one could also view a grid of data based on a relational SQL query.

Frankly, although I use relational tables almost every dayin my normal programming work, I didn’t need that level of complexity for my contact list.

A Database AdminTool

It’s important to mention that SQLite is used by verylarge companies, for mission critical tasks, including flight software for the A350 XWB family of aircraft designed by Airbus. In other words, it’s not just database software for a list of family members and their phone numbers.

You can view some of the more famous companies that useSQLite, here: http://www.sqlite.org/famous.html.

High level uses of SQLite are accomplished by coding largeapplications in a variety of programming languages which all hook into SQLite’s data files. SQLite Maestro differs from that type of usage, in that SQLite Maestro is a “front-end” program to edit and manipulate data. It’s also a very handy administrative tool that can be used to design and create the databases and tables that other applications work with, via a programming language. For example, I could use SQLite Maestro to design a set of relational data tables, then upload the data file to a web server, and then program a web interface and set of functions to work with those database tables, using the Perl or PHP programming language.

Once again, it’s useful to point out that placing SQLitedata files on a web server doesn’t require a database server like MySQL to run them. A web based PHP or Perl script simply opens the data file and manipulates the data. Very simple. Very cool.

SQLite Maestro:Room for Improvement

I like SQLite Maestro, quite a lot. That said, there’sroom for improvement. The print function and the Options / Directories functions are a bit cranky. However, the programmers are responsive, and a slew of issues were fixed right before I started this review. So, I expect those minor issues to be fixed soon.

A speedier startup time (like, Instant, man) would begreat. It may not be possible, which is ok.

One thing I found vexing is that the HTML export replacesspaces in the data with the numeric HTML entity code for a no-break space, i.e.  . I suppose the authors were trying to create a method of wysiwyg report generation, but it would be nice to be able to turn that off, and leave the spaces between words as genuine spaces, because spaces allow a long string of words to wrap inside an HTML table cell, whereas the no-break spaces do not. I could open a text editor and replace those codes with spaces, but I’d rather not.

The program can store photos and images in blob fields(since that’s a function of SQLite), but the grid and form views don’t show the images right off the bat. You have to click a button to view the image. It would be a more powerful program if you could integrate images right into the grid and form views.

A full-fledged relational form view, with a screendesigner and action script language, is the holy grail of SQL desktop database programs, in my opinion. After using Alpha Five for Windows, I have to say that nothing else comes close, when it comes to rapid application design of database systems.

But... sadly, (very sadly, actually), Alpha Five’s nativefile format is dBASE (now referred to as xBASE). Although I started with dBASE, and went through Clipper Summer ‘87 and then Alpha Five for Windows (and DOS), I departed from the fold in 1996, when I adopted MySQL and Perl. Since that day, I’ve been longing, yes, longing, for a desktop program like Alpha Five to natively support SQL, MySQL, and now SQLite. Alpha Five does not. Sigh. Another sigh. (I’m not talking about ODBC support of SQL files -- I’m talking about native and direct support.)

Will SQLite Maestro become the Alpha Five of the MySQL andSQLite world? I’d be happy if it did. They’d make a LOT of bucks if they accomplished that goal. Why? Because that type of program doesn’t exist yet, and is desperately needed, in my humble opinion.

Conclusion

I recommend SQLite Maestro. I haven’t even scratched thesurface with some of its database admin capability, but so far, it’s accomplishing pretty much exactly what I need, which is to store the names and details of my family, friends, business associates and other important individuals.

Will I find something better? Maybe. But for now, it’s aGood Thing.

Peter Falkenberg Brown is passionate about writing, publishing, public speaking and film. He hopes that someday he can live up to his favorite motto: “Expressing God’s kind and compassionate love in all directions, every second of every day, creates an infinitely expanding sphere of heart.”

~ Deus est auctor amoris et decoris. ~

Follow Peter on Twitter or Facebook:
@falkenbrown - https://twitter.com/falkenbrown
https://www.facebook.com/peterfalkenbergbrown

For news about his books:
http://peterfalkenbergbrown.com or: http://worldcommunitypress.com

Visit Peter's LinkedIn Profile at http://www.linkedin.com/in/peterfalkenbergbrown

View Peter Falkenberg Brown's profile on LinkedIn

Did you like what you read?

If so, leave a Tip, below, and join the ranks of our Renaissance Patrons!
>> Read More about becoming a Renaissance Patron

Standard
Patron
Contribution

Select Amount on the Next Page

Recurring Patron
Contribution


Select a Monthly Amount from the
DropDown List Below


Recurring Patron Levels



(Comments are moderated and must be approved.)
comments powered by Disqus

More Headlines of Interest

“The Epiphany of Zebediah Clump”
Watch our first film right here.
logo
Feel good about life and feed your soul some vittles...
from the columns and essays of Significato.
Transport your soul...
by curling up with a short story or poem.
Increase your bliss and nourish your soul...
with tidbits on nature, music, books, films, health and writings from bygone days.
Feel good about life...
Become a Significato Journal Renaissance Patron
Programming
Liquid Web Dedicated Servers
Help end child hunger