SQLite backup

General Usage
1

Hi,

Is there a way of using sqlite.jl to execute the API commands like
sqlite3_backup_init() ?

2

It seems so, I don’t know if it’s exported, or then how to access it, don’t know too much about this/SQLite:

3

Thanks Palli. I’m sure someone can point me in the right direction.

4

Well I thought I did already.

Since I found function sqlite3_backup_init there I thought it might be useful, i.e. you could call it, but that doesn’t seem to be the case.

But I was maybe helpful in (indirectly) pointing out you can call any C function, in general, with @ccall e.g. that one. To do that you could copy that function and make your own, or just use its contents.

But why can’t you call it, in the wrapper:

julia> SQLite.C.sqlite3_expanded_sql
ERROR: UndefVarError: C not defined

First of, the C module, and capi.jl file isn’t define in the last version of SQLite.jl, only on its master branch, so for any change (right now) you would have to do:

(@v1.8) pkg> add SQLite#master

It could be this capability is just in development, I thought the function might be there for SQLite itself to use (but it’s not used), or then for the user to use it by calling it, but I don’t think you can, since it isn’t exported.

Note, maybe the function was never meant to be used, I see it’s just one of many that were simply auto-generated:

The SQLite.jl file does:

include("capi.jl")

import .C

[..]

const DBHandle = Ptr{C.sqlite3}

I.e. it can refer to the C module, as there C.sqlite3, and could call the function you want. Often (as with Base) you can call functions even if they are not exported (but then maybe you shouldn’t, i.e. they are not part of the documented API). I thought that might be the case here but just don’t see how. I think import . means you can’t use what comes after the period, here C [module].

5

Thanks Palli,

I think I understand you. This is all beyond my skill level so unless someone can address the questions you raised, I’m at a dead end.

6

If you simply want to do a backup you don’t need to do it from Julia. I see here:

https://docs.kanboard.org/en/1.2.21/admin_guide/sqlite.html

Technically, the database is just a single file located inside the directory data and named db.sqlite.

Export/Backup

Command line

Doing a backup is very easy, just copy the file data/db.sqlite somewhere else when nobody use the software.

If you want to do a backup while users are connected, you can use sqlite3 to create the backup.

I put in the bold, with SQLite, as with other databases (e.g. PostgreSQL), it’s very dangerous to take a file-backup of you database (but not when you’ve shut it off for sure, meaning in case of SQLIte, your program using it).

I know PostgreSQL well (and SQLite just in theory), and you have online backups. I wasn’t expecting it for an embedded database like SQLite. [If you have many users,] also consider a different database, e.g. PostgreSQL, and LibPQ.jl.

https://www.sqlite.org/backup.html