2

Setting:

1) SQLite3 database file gets uploaded to a fileserver

2) Files get stored into virtual filesystem

3) I want to execute sql queries on that database

Currently I am using go-sqlite3 with a database file located in afero.

Question:

Is there are possbility to load a database file into go-sqlite3 with another source than a file from the os filesystem.

Current approach:

My current solution is to copy the file from afero into the os.TempDir() directory, but that fails with our ci and is no actual solution as it does not use the dedicated filesystem anymore.

Simon Frey
  • 2,539
  • 2
  • 11
  • 20
  • I know SQLite itself supports this. I don't see that functionality exposed in the Go driver. But if you know CGO, I'm sure you could hack the Go driver to expose it. – Jonathan Hall Aug 29 '18 at 13:06

1 Answers1

2

After a little source diving I see this in sqlite3.go

name := C.CString(dsn)
defer C.free(unsafe.Pointer(name))
rv := C._sqlite3_open_v2(name, &db,
    mutex|C.SQLITE_OPEN_READWRITE|C.SQLITE_OPEN_CREATE,
     nil)

So this answers your question, the actual opening of the database happens "outside" Go and "inside" the sqlite3 library. If we check the function sqlite3_open_v2 we can see that it requires a file name:

int sqlite3_open_v2(
  const char *filename,   /* Database filename (UTF-8) */
  sqlite3 **ppDb,         /* OUT: SQLite db handle */
  int flags,              /* Flags */
  const char *zVfs        /* Name of VFS module to use */
);

I think the only way here is to implement your own Sqlite VFS that somehow interacts with the afero abstractions.

yorodm
  • 4,359
  • 24
  • 32
  • Thanks for your answer. Do you maybe have a clue how to do that? (Or another go vfs package to orient on - maybe even fork it) – Simon Frey Aug 29 '18 at 13:41
  • Sorry, I know C and a little Go, but my knowledge of CGo is lacking in a lot of ways. – yorodm Aug 29 '18 at 13:42
  • sqlite3 is a C library. Every Go implementation that provides full read-write functionality are only calling the C library with C binding. There is no way that C library can directly use a filesystem emulated written in Go and for Go. – Koala Yeung Aug 30 '18 at 16:19
  • That's why I suggested using a VFS, take a look at this neat VFS that allows you to append a SQLite database to [an existing file](https://www.sqlite.org/src/file/ext/misc/appendvfs.c). You could do the same with, let's say a memory buffer or something – yorodm Aug 30 '18 at 17:52