SQLCipher Performance and SQLCipherSpeed

2010-05-06 20:00:00 -0400

Recently, we had a query on the the SQLCipher Users’ mailing list inquiring about the performance of a LIKE query, where the user was wondering if SQLCipher’s encryption engine was responsible for poor performance he was seeing in his code. “It depends,” is the cheapest and most accurate answer we could give without seeing his query and the EXPLAIN plan generated by SQLite (no index, for instance, could lead to a full table scan, thus requiring every page to be decrypted). What we do know is that performance of SQLCipher compared to SQLite is really pretty good, and certainly good enough for our needs as application developers.

If you’ve been wondering what kind of performance hit you can expect using SQLCipher as compared to vanilla-SQLite, we’ve published a new tool to help you get an idea. In the end, EXPLAIN and EXPLAIN QUERY PLAN cannot be replaced, but for a quick side-by-side reference to see that we’ve done a half-decent job, check out Stephen’s SQLCipherSpeed. It’s an iPhone OS project that rips through the various SQLite speed tests. I ran it on my crunky iPhone 3G and the results were about what I expected, and pretty interesting:

In this next one, note that there is no performance impact for 2500 selects on an index.

You’re highly encourage to check out the code yourself, and to fork it. It would be really cool if someone added an action button to the results screen to email the data off-device. More tests wouldn’t hurt either.

Some Quick Notes from Virginia

2010-04-09 20:00:00 -0400

I’m still at the RubyNation conference, and it’s going really well. Fantastic conference, really, with a huge presence and backing from Engine Yard. Kudos to all involved, my brain is Chock full of Goodness. Dave Thomas’ keynote was excellent, and all the talks I’ve attended have been exceptional. I’ve also had the pleasure of making the acquaintance of many of the speakers and numerous Ruby and Rails Ur-hackers.

In any event, it’s the break just after lunch on day two and I’m giving my brain a code break. Sitting in the sunshine, just inside the lobby, I’m listening to my friends’ new and unreleased album, tapping out a review on my iPhone. Specifically, iPod app is on in the background and I’m tapping away in our new app Codebook, because I like to keep my stupider/unrefined writings really private, and it beats the pants off using Apple’s Notes app.

I think that’s a good measure of whether or not your software is any good! If you don’t use it, why should anybody else?

Active development continues unabated here at Team Z, for those keeping track. An update to Strip Sync for Mac beta will be pushed soon, allows for bulk loading/editing of existing entries via CSV. I think this is the last thing currently missing piece for this utility app.

I’m finding that certain network / sleep events are causing Strip Sync to dump core. Still working out what’s going on there.

Strip Sync for Windows is on the way! Sit tight, we’ll be in touch soon.

Back to the conference. Enjoy the beautiful weather this weekend, temperatures should be up tomorrow ;-)

How Strip Sync CSV Import Works (beta)

2010-03-24 20:00:00 -0400

Updated: 6/6/2012 This blog post is out of date, and the software referenced in it, Strip Sync, has been discontinued in favor of Strip for Windows and Strip for OS X. For more information on our data import format, please see this newer blog post.

As mentioned here previously, we’re on the cusp of starting the beta test of Strip Sync, our new desktop companion tool for Strip. Among the application’s features are CSV import and export. The intent of this article is to describe the format used on export and required on import. This format is subject to change, but it won’t be changing much. Any changes will be announced here on this blog and noted as updates at the end of this article.

The General Gist

Each row in the CSV corresponds to an Entry in your Strip database. There’s a field indicating what Category the Entry belongs in, the name of the Entry, and every other column is considered a Field. The import process creates a new Entry for each row in the CSV file after the header row. Note: Bulk updating is now supported on SSM, requires use of EntryID column. Bulk updating will be made available soon in Strip Sync for Windows (SSW).

This is what a sample import CSV might look like:

Credit Card,Financial,3759 876613 21001,,"exp:12/12
Insurance Policy,Financial,3759 876613 21001,,secret: name of your first pet? spot,secret,1-800-123-4567,4,,mscott,http://myinsurance.com
jordie laforge,trekkers,,,,,,,nextgeneration|deep space nine,,
kirk,trekkers,,,,,,,star trek,,
patrick stewart,trekkers,,,,,,,star trek|next\|generation|voyager,,
Shopping Website,Personal,,mscott@mailinator.com,,secret,,,,,http://paypal.com

Header Row Required

Just like the subtitle says, a header row is currently required to describe the data in the spreadsheet you’re importing for Strip.

Header Specification

  • One column must be named “Entry”, and this is case-insensitive.
  • One column must be named “Category”, and this is also case-insensitive.
  • The name of every other column (for now) is considered the name of a Field.
  • No columns should be named “Guid”… Guid is no longer a restricted name.
  • One column may be named “EntryID” on SSM, making the row an update to an existing record.

When Strip Sync reads the header row of your import file, it looks up each Field name in your database to see if there’s already a label/type associated with it. If not, a Field with this label is created for you in your database, with the default mode set to “text”, and you can simply change this setting to URL or whatever you like by editing your labels in Strip.

When a row contains an EntryID, Strip Sync looks up the Entry in your database and replaces it’s name, category, and fields using the data in the rest of the row.

Bulk Updating

To do bulk updates via CSV import, you need to get the unique identifiers for your entries! Simply use the export feature of Strip Sync to export a CSV file containing all records in your database, with their EntryIDs.

Row Processing

During import, after the header row has been read, Strip Sync begins cranking through all the other rows, creating new Entries using the data in each row (bulk update via CSV import is not supported yet, but we plan to support that soon). Here’s how it works:

  • Strip Sync looks at the Category field and does a case-sensitive lookup to find a matching Category in your database. If no match is found, a new Category with this name is created.
  • The Entry column is used as the name of the new Entry, as indicated above.
  • For each additional column in the row:
    • If the column is empty, it is ignored
    • If the column is not empty, a Field is created on the Entry, with a type/label corresponding to the column’s header name.
    • Field columns may contain multiple values, separated by the ‘pipe’ character, ‘|’. If multiple values are detected, multiple Fields will be created on the Entry, labeled according to the column’s header name.
    • If your Field needs to contain a pipe character as part of the Field value, you may escape it with a backslash character (i.e. ‘\|’).
  • If an Entry or Category column is blank, the entire import will be rolled back, and an error message will display detailing the problem and the line number where the problem was found.

Our import and CSV processing is based on the scanning technique and EBNF outlined by Matt Gallagher to fully support properly escaped CSV data.

If one were to extend that EBNF definition to take into account our use of | to separate multiple field values, we think it would look like this:

file = [header lineSeparator] record {lineSeparator record}
header = name {separator name}
record = field {separator field}
name = field
field = escaped | nonEscaped
escaped = doubleQuote {innerField | separator | lineSeparator | twoDoubleQuotes} doubleQuote
nonEscaped = innerField
doubleQuote = '"'
twoDoubleQuotes = '""'
separator = ','
lineSeparator = ('\r' | '\n') {'\r' | '\n'}
innerField = textData { innerFieldSeparator | textData }
innerFieldSeparator = '|'
textData = {characters up to the next double quote character, un-escaped innerFieldseparator, separator string, or lineSeparator}

Obviously, commentary and corrections are welcome (as well as bug reports).


Tempo Updates: Tagging, Status, UI tweaks

2010-03-23 20:00:00 -0400

Last night I pushed a couple of updates out to our Tempo time tracking service that were a touch over-due:

  • Tweaked date field on time entry to fix text-obscuring problem
  • Tweaked order receipt for providing copies of past invoices
  • Improved navigation and visibility for Team → Status
  • Fixed display margins in module boxes site-wide
  • Adds a “add new user” link in project team settings for convenience
  • Allows meta-characters in tags, no longer restricted to [A-z0-9-_]

The tags thing was getting annoying, I will admit. It’s pretty useful to be able to, for instance, begin certain tags with a * character to mark them as project defaults.

The Team Status thing was a visibility issue. I think most administrators and managers of Tempo accounts were unaware that in addition to the basic Team view (a simple list of your users), there’s a Status view indicating who is working on what:

Aaaaand it even has a handy mobile view that you could bookmark:

If you notice any bugs, or there’s anything bothering you about the system, please get in touch.

In Search of a Pragmatic Programmer

2010-03-22 20:00:00 -0400

We’ve been really busy here at Zetetic lately and we’re looking for a new teammate to help us develop and support client systems and our own products. As a small and flexible software development consultancy, we’re mostly interested in personal qualities, not resume buzzwords:

  1. Natural or Artificial intelligence (robots welcome)
  2. Intense desire to learn new things & hack on technology
  3. An unflappably positive attitude
  4. Plays well with others

A background in one or more of the following goes a long way too!

  • Strength in at least one Object Oriented Programming Language (C#, Ruby, Java, Obj-c). Anything except VB.
  • Desire to “switch things up” and work on multiple programming languages
  • Experience with databases, relational SQL, or otherwise (mongo, couch, etc)
  • Security Technology (i.e LDAP authentication, Single Sign-on, PKI)
  • Linux and general networking
  • 2-5 years of software development experience
  • Located in Philadelphia, NYC, or Central NJ to allow collaborative work with our current team members

In short, we mostly care that you’re smart, love to hack, and get things done.

If this sounds like you, or someone you know, please reach out and let us know: sjlombardo@zetetic.net.