Interface FunSQL with LibPQ using DBInterface

General Usage
1

I am using LibPQ.jl to interface Julia with Postgres as follows:

using FunSQL
using DBInterface
dbread = "Test2"
user = "postgres"
password = "password"
conn = LibPQ.Connection("dbname=$dbread user=$DATABASE_USER password=$DATABASE_PASSWORD")

This is working well.

To interface with DBInterface, required for FunSQL according to the documentation, I should do something like:

DBInterface.connect(::Type{LibPQ.Connection}, "dbname=$dbread user=$DATABASE_USER password=$DATABASE_PASSWORD") = 
LibPQ.Connection("dbname=$dbread user=$DATABASE_USER password=$DATABASE_PASSWORD")

ERROR: syntax: ""$("dbname=")$dbread$(" user=")$DATABASE_USER$(" password=")$DATABASE_PASSWORD"" is not a valid function argument name around REPL[43]:1
Stacktrace:
 [1] top-level scope
   @ REPL[43]:1

julia>

What am I doing wrong?

2

What am I doing wrong?

You are trying to define a function, but the function arguments you specified are wrong ("dbname=$dbread user=$DATABASE_USER password=$DATABASE_PASSWORD" on the left side is neither a variable name or a type).

About the actual problem: I guess you are reading to this part of FunSQL.jls docs: Examples · FunSQL.jl

IIUC then what the docs are trying to tell you is is to copy this code here into your project

using LibPQ
using DBInterface

DBInterface.connect(::Type{LibPQ.Connection}, args...; kws...) =
    LibPQ.Connection(args...; kws...)

DBInterface.prepare(conn::LibPQ.Connection, args...; kws...) =
    LibPQ.prepare(conn, args...; kws...)

DBInterface.execute(conn::Union{LibPQ.Connection, LibPQ.Statement}, args...; kws...) =
    LibPQ.execute(conn, args...; kws...)

and then use the FunSQL.jl package like it is described in the usage section, but substitute all FunSQL.DB{LibPQ.Connection} in all places where you would use FunSQL.DB{SQLite.DB} (or so).

1 Like
3

Okay I copied the code just as you copied it from the docs. Then I put in

const conn = DBInterface.connect(FunSQL.DB{LibPQ.Connection}, "dbname=$dbread user=$DATABASE_USER password=$DATABASE_PASSWORD")

And lo and behold it worked!

Thanks for your help.

1 Like
4

Now that I have the database open and can interact with it, I am having problems closing the Database. I am not sure where to go from here.

julia> close(conn)
ERROR: MethodError: no method matching close(::FunSQL.SQLConnection{LibPQ.Connection})

or 

julia> DBInterface.close(conn)
ERROR: MethodError: no method matching close(::FunSQL.SQLConnection{LibPQ.Connection})

or 

julia> DBInterface.close!(conn)
ERROR: MethodError: no method matching close!(::LibPQ.Connection)
5

Hey @Jake , I am about to finish adding DBInterface support to LibPQ via this PR: https://github.com/iamed2/LibPQ.jl/pull/271 (the original author went missing and I am in the process of rebasing and opening a new PR). If need be, I think the forked version of LibPQ with this functionality may fix your issue.

6

Thanks. I am not proficient with loading pull requests but was able to look over the source code of the changes made to LibPQ. I then added the new function ‘DBInterface.close!’ that was not included in the FunSQL manual. For reference for others looking at this my solution until the new LibPQ is released is below. This is similar to the PR.

using DBInterface
using FunSQL:
    FunSQL, Agg, Append, As, Asc, Bind, CrossJoin, Define, Desc, Fun, From,
    Get, Group, Highlight, Iterate, Join, LeftJoin, Limit, Lit, Order,
    Partition, Select, Sort, Var, Where, With, WithExternal, render
using LibPQ

#  bring libraries and helper functions into scope for integrating FunSQL and LibPQ

DBInterface.connect(::Type{LibPQ.Connection}, args...; kws...) =
LibPQ.Connection(args...; kws...)

DBInterface.prepare(conn::LibPQ.Connection, args...; kws...) =
LibPQ.prepare(conn, args...; kws...)

DBInterface.execute(conn::Union{LibPQ.Connection, LibPQ.Statement}, args...; kws...) =
LibPQ.execute(conn, args...; kws...)

DBInterface.close!(conn::LibPQ.Connection) = close(conn)
7

@TheCedarPrice do I still need to copy the code from the FunSQL manual to bridge DBInterface with LibPQ? I note that DBInterface is now a dependency of LibPQ, but when I try to duplicate the FunSQL commands for SQLite but substitute LibPQ for SQLite, I get an error. (Edited for clarity)

julia> using LibPQ

julia> using FunSQL

julia> conn = DBInterface.connect("dbname=$dbread user=$DATABASE_USER password=$DATABASE_PASSWORD")
ERROR: UndefVarError: `DBInterface` not defined
Stacktrace:
 [1] top-level scope
   @ REPL[28]:1

julia>
8

The code posted here works fine and without error when used from the REPL.

In my use case I have a package MyPackage with the Module also called MyPackage that includes a submodule called MySubModule. MySubModule uses this code through an include statement. When I try to precompile my package it fails with the error shown below, but it executes fine. I suspect that the PR referred to by @TheCedarPrince means I don’t need all this code but have not been able to sort out what I need to do.

julia> using MyPackage
Precompiling MyPackage...
Info Given MyPackage was explicitly requested, output will be shown live
WARNING: Method definition connect(Type{LibPQ.Connection}, Any...) in module LibPQ at C:\Users\jakez\.julia\packages\LibPQ\i4yBe\src\dbinterface.jl:5 overwritten in module MySubModule at C:\Users\jakez\.julia\dev\MyPackage\src\general\FunPQ.jl:13.
ERROR: Method overwriting is not permitted during Module precompilation. Use `__precompile__(false)` to opt-out of precompilation.
  ? MyPackage
[ Info: Precompiling MyPackage[8dcd7bc5-9351-4069-b6f5-927426629c70] (cache misses: include_dependency fsize change (8))
WARNING: Method definition connect(Type{LibPQ.Connection}, Any...) in module LibPQ at C:\Users\jakez\.julia\packages\LibPQ\i4yBe\src\dbinterface.jl:5 overwritten in module Mcc172Acquire at C:\Users\jakez\.julia\dev\MyPackage\src\general\FunPQ.jl:13.
ERROR: Method overwriting is not permitted during Module precompilation. Use `__precompile__(false)` to opt-out of precompilation.
┌ Info: Skipping precompilation due to precompilable error. Importing MyPackage [8dcd7bc5-9351-4069-b6f5-927426629c70].
└   exception = Error when precompiling module, potentially caused by a __precompile__(false) declaration in the module.

julia>

When I comment out the code below the using LibPQ statement, MyPackage precompiles but does not execute giving this error.

julia> foo()
ERROR: MethodError: no method matching connect(::Type{LibPQ.Connection}, ::String)
The function `connect` exists, but no method is defined for this combination of argument types.

Closest candidates are:
  connect(::Type{FunSQL.SQLConnection{RawConnType}}, ::Any...; schema, dialect, cache, kws...) where RawConnType
   @ FunSQL C:\Users\jakez\.julia\packages\FunSQL\FhUx7\src\connections.jl:77

Stacktrace:
 [1] connect(::Type{FunSQL.SQLConnection{…}}, args::String; schema::Nothing, dialect::Nothing, cache::Int64, kws::@Kwargs{})
   @ FunSQL C:\Users\jakez\.julia\packages\FunSQL\FhUx7\src\connections.jl:82
 [2] connect(::Type{FunSQL.SQLConnection{LibPQ.Connection}}, args::String)
   @ FunSQL C:\Users\jakez\.julia\packages\FunSQL\FhUx7\src\connections.jl:77

The FunSQL documentation indicates that I still need the commands that won’t precompile.

I am not able to figure out what I should be doing to allow precompilation. Help is appreciated.

9

It looks like you are overwriting (i.e. replacing) the LibPQ function definition for connect, which is probably not what you want to do.

10

I am wondering if the FunSQL documentation is old documentation and needs to be updated based on the comment from @TheCedarPrince and I should be doing something differently. But I have not been able to figure out what.