Homebrew Question Help compiling sqlite3 for Switch (and getting it working)

[tallbl0nde]

This may be a long shot - so fingers crossed someone can help me out!

I've started working on a project where having a database to store metadata about files and such would be extremely helpful. Google led me to use sqlite given the nature of homebrew (so if there's an alternative I'm happy to give it a shot!). I was able to implement all the functionality/schemas on my pc and use the C api to access the database.

Problems started to arise when I wanted to move this code over to the Switch. I downloaded the sqlite source code and put together a Makefile based off one I'd used before for compiling a library. (See below). At first it wouldn't compile due to some headers missing (I'm guessing not implemented in devkitpro) so I added defines for the flags SQLITE_OMIT_WAL and SQLITE_OMIT_LOAD_EXTENSIONS as by disabling these features it would progress to the linking stage.

However now it wouldn't link due to the following errors:

/opt/devkitpro/devkitA64/lib/gcc/aarch64-none-elf/9.2.0/../../../../aarch64-none-elf/bin/ld: /mnt/d/Code/TriPlayer/Application/libs/SQLite/lib/libSQLite.a(sqlite3.o):(.data.rel.aSyscall+0x1e8): undefined reference to `fchown'
/opt/devkitpro/devkitA64/lib/gcc/aarch64-none-elf/9.2.0/../../../../aarch64-none-elf/bin/ld: /mnt/d/Code/TriPlayer/Application/libs/SQLite/lib/libSQLite.a(sqlite3.o):(.data.rel.aSyscall+0x200): undefined reference to `geteuid'
/opt/devkitpro/devkitA64/lib/gcc/aarch64-none-elf/9.2.0/../../../../aarch64-none-elf/bin/ld: /mnt/d/Code/TriPlayer/Application/libs/SQLite/lib/libSQLite.a(sqlite3.o):(.data.rel.aSyscall+0x278): undefined reference to `readlink'

To resolve these linking errors I simply commented out some defines (HAVE_FCHOWN and HAVE_READLINK) at lines 932 and 933 in sqlite3.c as these functions all relate to file ownership/symbolic links, both of which fat32 doesn't handle. This could be what's causing my problem but who knows.

At least at this point it would compile and work on the Switch... so I thought. While there are no crashes/segfaults I can't actually manipulate the database file. This is the code I'm using:

sqlite3_open("/database.db", &this->db);
std::cout << "Status 0: " << std::to_string(sqlite3_extended_errcode(this->db)) << std::endl;
std::cout << sqlite3_errmsg(this->db) << std::endl;

// Use foreign keys
sqlite3_prepare_v2(this->db, "PRAGMA foreign_keys = ON;", -1, &this->cmd, nullptr);
if (this->cmd != nullptr) {
    sqlite3_step(this->cmd);
}
sqlite3_finalize(this->cmd);

// Try creating table
sqlite3_prepare_v2(this->db, "CREATE TABLE Artists (id INTEGER NOT NULL PRIMARY KEY, name TEXT UNIQUE);", -1, &this->cmd, nullptr);
std::cout << "Status 1: " << std::to_string(sqlite3_extended_errcode(this->db)) << std::endl;
std::cout << sqlite3_errmsg(this->db) << std::endl;
if (this->cmd != nullptr) {
    sqlite3_step(this->cmd);
    std::cout << "Status 1.1: " << std::to_string(sqlite3_extended_errcode(this->db)) << std::endl;
    std::cout << sqlite3_errmsg(this->db) << std::endl;
}
sqlite3_finalize(this->cmd);

And I get the following output:

Status 0: 0
not an error
Status 1: 3850
disk I/O error

The error code 3850 is SQLITE_IOERR_LOCK, meaning it can't lock the file. So to avoid this I added the line:

sqlite3_vfs_register(sqlite3_vfs_find("unix-none"), 1);

Which tells sqlite to not perform any file locking operations. When running it with this config I get these errors:

Status 0: 0
not an error
Status 1: 0
not an error
Status 1.1: 266
disk I/O error

This time the error code 266 indicates SQLITE_IOERR_READ, meaning it can't read the file. This is where I'm stumped. It can create the file as it does so when it doesn't exist. However while my app is running I'm also unable to get the directory structure for the directory the database is in over FTP, almost as if sqlite is 'locking' the entire folder and thus can't access the file. I know this 'locking' behaviour is due to sqlite as it doesn't happen if I comment out the above code.

The important part of my Makefile is this:

#---------------------------------------------------------------------------------
# options for code generation
#---------------------------------------------------------------------------------
ARCH    :=    -march=armv8-a -mtune=cortex-a57 -mtp=soft -fPIC -ftls-model=local-exec

CFLAGS    :=    -g -w -D__SWITCH__ \
            -ffunction-sections \
            -fdata-sections \
            $(ARCH) \
            -DSQLITE_OMIT_WAL -DSQLITE_OMIT_LOAD_EXTENSION

CFLAGS    +=    $(INCLUDE)

CXXFLAGS    := $(CFLAGS) -fno-rtti -fno-exceptions -std=gnu++17

ASFLAGS    :=    -g $(ARCH)
LDFLAGS    =    -specs=${DEVKITPRO}/libnx/switch.specs -g $(ARCH) -Wl,-r,-Map,$(notdir $*.map)

LIBS    :=  -lnx

#---------------------------------------------------------------------------------
# list of directories containing libraries, this must be the top level containing
# include and lib
#---------------------------------------------------------------------------------
LIBDIRS    := $(PORTLIBS) $(LIBNX)

I know that's a long read so...
TLDR - Has anyone managed to get sqlite3 operating on the Switch? If so could you please give me a hand with getting it up and running

 

[emmauss]

Hey there, did you get this to work?

 

[tallbl0nde]

Hey there, did you get this to work?

I did, as I'm using SQLite in TriPlayer. If you follow everything I've done above but use a database file that has a 'dummy' table created (i.e. it isn't empty/new), it should work properly. You can see the source code for TriPlayer for how I'm actually using it.