× {{alert.msg}} Never ask again
Get notified about new tutorials RECEIVE NEW TUTORIALS

Doing More In SQLite With User Functions

Michael G Schwern
Oct 30, 2016
<p><a href="https://sqlite.org/">SQLite</a> fills that awkward gap between "I have so much data I obviously need a database server" and "do I really need a full blown database server just to work with a CSV file?" It's so cheap and easy and powerful (assuming you know SQL) there's little reason to write your own code to search data, instead dump it into a SQLite database and query it with SQL.</p> <p>But that comes at a cost. SQLite is severely limited in what it can do compared so something like MySQL or PostgreSQL. It lacks most of the date, string, and other data manipulation functions people have become used to. What to do? The less than ideal solution is to pull the data out of the database, alter it in your program, and then update the database with the change. This is slow and error prone. There is a better way.</p> <p>User defined functions to the rescue! Like most databases, you can create functions to use in your queries. But what makes SQLite so unique is you can write them in the language of your choice!  Here's an example , in Ruby, of converting ad-hoc formatted dates like 2-AUG-15 to 2015-08-02. This is the <a href="https://en.wikipedia.org/wiki/ISO_8601">ISO 8601</a> format that SQLite and most utilities understand.</p> <pre>require "sqlite3" require "date" # Open a database db = SQLite3::Database.new "test.db" # Create a SQLite function date_to_iso() db.create_function( "date_to_iso", 1 ) do |proxy, date| # Convert 12-AUG-15 to 2015-08-12 (ISO 8601 format) iso = Date.strptime(date, "%d-%b-%y"); # This is how you return the result via a FunctionProxy. # It has to be converted to a string, SQLite won't do it for you. proxy.result = iso.to_s end # Now you can use date_iso_iso() in SQLite queries in this process. db.execute("UPDATE stuff SET date = date_to_iso(date)")</pre> <p>This makes use of <a href="http://ruby-doc.org/stdlib/libdoc/date/rdoc/Date.html#method-c-strptime">Date::strptime </a>to do the parsing quickly and easily. There's no need to first read the data from the database, your new function can be used like any other SQL function!</p> <p>Now you can use all the power of your own coding language with SQLite!</p>
comments powered by Disqus