In 2011, I reviewed a simple database program called “Efficient
Address Book”, focused on its viability as a contact management program for
names, 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 databases
left 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 the
SQL 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’t
require 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 of
SQLite 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 looking
for 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
The program is on the web at
http://www.sqlmaestro.com/products/sqlite/maestro/ and costs $139 for a single
business 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 I
downloaded 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 may
review those two programs at some point.
I like the interface a lot, in SQLite Maestro. It may not
matter 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 is
very 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 more
powerful 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 very
comfortable 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, to
keep 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 and
Printouts
So, in my own caveman like way, I run the above query, and
then 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 exporting
to 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 report
template, 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 it
can 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 the
program 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 the
phone 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 view
and clicking on a toolbar icon.
Multi-Table
Operations
SQLite Maestro supports links to additional tables via
foreign 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 day
in my normal programming work, I didn’t need that level of complexity for my
contact list.
A Database Admin
Tool
It’s important to mention that SQLite is used by very
large 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 use
SQLite, here: http://www.sqlite.org/famous.html.
High level uses of SQLite are accomplished by coding large
applications 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 SQLite
data 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’s
room 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 be
great. It may not be possible, which is ok.
One thing I found vexing is that the HTML export replaces
spaces 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 screen
designer 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 native
file 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 and
SQLite 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 the
surface 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 a
Good Thing.
