Edit on GitHub

QuerySQLite

User documentation

QuerySQLite is an experimental package sponsored by Google Summer of Code. It's finally ready for public use. Although QuerySQLite is only tested using SQLite, it's been purposefully designed to easily incorporate other database software.

Use Database to wrap an external database. Then, you can access its tables using ., and conduct most Query operations on them. In theory, most operations should "just work". There are a couple of exceptions.

Non-overloadable syntax and functions

Patterns like if and functions like ifelse and typeof can't be overloaded. Instead, QuerySQLite exports the if_else and type_of functions and overloads them instead.

No SQL arguments

If you would like to translate code to SQL, but you do not pass any SQL arguments, you will need to use BySQL to pass a dummy SQL object instead. See the BySQL docstring for more information.

Pattern matching

Use SQLite syntax, not Julia syntax, for pattern matching for regular expressions and date formats.

Developer documentation

QuerySQLite hijacks Julia's multiple dispatch to translate external database commands to SQL instead of evaluating them. To do this, it constructs a "model_row" that represents the structure of a row of data. If you would like to add support for a new function, there are only a few steps:

  • Use the @code_instead macro to specify the argument types for diversion into SQL translation.
  • If your function will modify the row structure of the table, define a model_row_call method.
  • Use the @translate_default macro to name the matching SQL function. If more involved processing is required, define a translate_call method instead.
  • If you would like to show your SQL expression in a non-standard way, edit the show method for SQLExpressions.
QuerySQLite.BySQL — Type
struct BySQL{Source}

If you would like a statement to be evaluated by SQL, not Julia, and none of the arguments are SQL code, you can use BySQL to hack dispatch.

julia> using QuerySQLite

julia> using Query: @map

julia> using DataValues: DataValue

julia> database = Database(joinpath(pathof(QuerySQLite) |> dirname |> dirname, "test", "Chinook_Sqlite.sqlite"));

julia> result = database.Track |> @map({a = rand(BySQL(_), Int)});

julia> collect(result)[1].a isa DataValue{Int}
true
source
QuerySQLite.Database — Method
Database(filename::AbstractString)

Guess the database software from the filename.

julia> using QuerySQLite

julia> database = Database(joinpath(pathof(QuerySQLite) |> dirname |> dirname, "test", "Chinook_Sqlite.sqlite"));

julia> database.Track
?x9 SQLite query result
TrackId │ Name                                      │ AlbumId │ MediaTypeId
────────┼───────────────────────────────────────────┼─────────┼────────────
1       │ "For Those About To Rock (We Salute You)" │ 1       │ 1
2       │ "Balls to the Wall"                       │ 2       │ 2
3       │ "Fast As a Shark"                         │ 3       │ 2
4       │ "Restless and Wild"                       │ 3       │ 2
5       │ "Princess of the Dawn"                    │ 3       │ 2
6       │ "Put The Finger On You"                   │ 1       │ 1
7       │ "Let's Get It Up"                         │ 1       │ 1
8       │ "Inject The Venom"                        │ 1       │ 1
9       │ "Snowballed"                              │ 1       │ 1
10      │ "Evil Walks"                              │ 1       │ 1
... with more rows, and 5 more columns: GenreId, Composer, Milliseconds, Bytes, UnitPrice
source
QuerySQLite.char — Method
char(numbers...)

Convert a list of numbers to a string with the corresponding characters

julia> using QuerySQLite

julia> char(65, 90)
"AZ"
source
QuerySQLite.get_column_names — Method
get_column_names(source, table_name)::Tuple{Symbol}

Get the names of the columns in table_name in source.

julia> using QuerySQLite

julia> database = Database(joinpath(pathof(QuerySQLite) |> dirname |> dirname, "test", "Chinook_Sqlite.sqlite"));

julia> get_column_names(getfield(database, :source), :Album)
(:AlbumId, :Title, :ArtistId)
source
QuerySQLite.get_sql — Method
get_sql(it)

Use get_sql if you would like to see the SQL code generated by an SQLite query.

julia> using QuerySQLite

julia> database = Database(joinpath(pathof(QuerySQLite) |> dirname |> dirname, "test", "Chinook_Sqlite.sqlite"));

julia> get_sql(database.Track)
SELECT * FROM (Track)
source
QuerySQLite.get_table_names — Method
get_table_names(source)::Tuple{Symbol}

Get the names of the tables in source.

julia> using QuerySQLite

julia> database = Database(joinpath(pathof(QuerySQLite) |> dirname |> dirname, "test", "Chinook_Sqlite.sqlite"));

julia> get_table_names(getfield(database, :source))
(:Album, :Artist, :Customer, :Employee, :Genre, :Invoice, :InvoiceLine, :MediaType, :Playlist, :PlaylistTrack, :Track)
source
QuerySQLite.hex — Method
hex(it)

Uppercase hexadecimal representation

julia> using QuerySQLite

julia> hex("hello")
"68656C6C6F"
source
QuerySQLite.if_else — Method
if_else(switch, yes, no)

ifelse that you can add methods to.

julia> using QuerySQLite

julia> if_else(true, 1, 0)
1

julia> if_else(false, 1, 0)
0
source
QuerySQLite.instr — Method
instr(haystack, needle)

Find the first index of needle in haystack.

julia> using QuerySQLite

julia> instr("QuerySQLite", "SQL")
6
source
QuerySQLite.type_of — Method
type_of(it)

typeof that you can add methods to.

julia> using QuerySQLite

julia> type_of('a')
Char
source