I want to support both the latest release of the official SQLite and the latest release of SQLeet, selected using built tags. This will probably require two of these c "dummy" packages: one for the sqleet C source and one for the official SQLite source. Then two separate files in the main sqlite package will include each of the "dummy" packages. Build tags will then target one of these files.

I can push a commit that implements this with the right build tags if you want my help with this aspect. Let me know if you have any questions.

I would need your help on this one as it requires touching c and go. Sqleet is on 3.31.1 version. I was tried to build with new sqlite but got into some rough compile issue in c. I'm using mac and some of the sed command is not working properly and what I ended up was running the code inside a docker.

I really appreciate that you included an example. However are you familiar with Golang test examples? I'd like to see this example changed into a golang test example. https://blog.golang.org/examples

Let's keep examples as minimal as possible to demonstrate a feature. Let's not use a pool here, but just a single connection created with sqlite.OpenConn. Foreign key support has nothing to do with encryption so it should not be used or mentioned in this example either.

Another note for your own information regarding enabling foreign keys or other PRAGMA features. Since you already specified it in the URI, this loop to call the PRAGMA is not needed.

I did try using foreign_keys connection string and it did not work. then I tried using PRAGMA and it didn't work until I filed an issue #91 and you answered it.

I know this is a work in progress but let's avoid keeping commented out code around.

cleaned up the example

These steps (PRAGMA key and sqlite.Crypto()) are redundant and use different keys. Also note that PRAGMAs never return errors, which is why it is generally better to use a programmatic API where possible. We should not use the PRAGMA, and use the programmatic API in examples to guide users to best practices.

Update the example. couple of observation,

• if I use connection string key, it does encrypt and decrypt the database properly
• if I use sqlite.ApplyKey it does not encrypt database and it errors out: sqlite.Conn.Unlock: SQLITE_AUTH
• if I use sqlite.ApplyRekey it does encrypt an unencrypted database or encrypt with the new key. However I have to use connection string to connect back to database.

I think you are missing some nuances to how the encryption API works. I recommend reviewing the documentation for SQLeet, and use examples that exclusively use the API. Don't confuse the issue by using settings in the connection string.

The current flaw with your example, which still persists, is that it does not distinguish between an already encrypted database, and a new, or not yet encrypted, database.

You cannot encrypt an unencrypted or new database as encrypted by using a setting in the connection string. Thus if you open a new database with the URI file:./database.db?key=swordfish, the key param will have no effect and you will still be working with an unencrypted database. Only if the database was already encrypted, and this is the correct key, will it be opened already unlocked.

Anytime you wish to encrypt an un-encrypted or new database file, you MUST use PRAGMA rekey or preferably the API equivalent. This initially will set up the encryption on the file. Thereafter you must use PRAGMA key to unlock the file before reading or writing from it on each connection. This appears to support using the param in the connection string. Only after this occurs, can PRAGMA rekey be successfully called to change the key on an already encrypted database.

I hope this clarifies the correct use of the APIs. Really the example needs a flag that controls whether we are unlocking an encrypted database or creating a new encrypted database. Ultimately though, I want these in golang test examples so they should up in the documentation. There should be two examples for each case I just mentioned.

@alinz I experimented a bit more and I discovered that I was somewhat incorrect about the use of the key param in the connection string. I was not specifying the connection string properly in my own experimentation, which threw me off. You can in fact initialize a new database to be encrypted with a given key using key= in the connection string. The rest of what I said about the use of the API is correct but the SQLeet README describes many advanced settings that can only be set in the URI string. https://github.com/resilar/sqleet/tree/v0.31.1#uri-configuration-interface This is easy to get wrong so in the future I plan to introduce some helper types/functions for building a URI with the correct settings.

Thanks for the update.

I appreciate you documenting the technique, but CGo basics are in the space of assumed knowledge for this package and don't need to be documented.

Understood, removed the comment

There are two things wrong with this call that were likely causing you some issues. First, the function signature in C is this

int sqlite3_key(
   sqlite3 *db,        /* The connection from sqlite3_open() */
   const void *pKey,   /* The key */
   int nKey            /* Number of bytes in the key */
);

First note that by passing ckey is already a pointer. So by passing &ckey you are passing a pointer to a pointer. This is not something C can prevent you from. It has no idea you passed it the wrong thing, but this will produce unexpected behavior.

The next parameter needs to be the length of the string or byte slice. By passing zero you are using an empty key.

Thanks for pointing this out, I don't know what I was thinking. I have cleaned that up and also implemented Rekey. I came up with ApplyKey and ApplyRekey. I would gladly change it if you have a better names.

At minimum errors need to indicate what went wrong or where. In this package we have a function reserr to help format errors. This should be return reserr("Conn.Unlock", "", "", res).

Fixed the error issue

Similar to the C files we'll have to somehow use build tags to control which header file to use.

I need your help on this

Why did you remove this test? This should be restored.

The test was failing and it was kind of confusing:

--- FAIL: TestJournalMode (0.00s)
    sqlite_test.go:534: journal_mode not set properly, got: memory, want: delete

I figured out the root cause here. Basically SQLeet uses a different default for temp_store than SQLite3 uses. This caused this to be a different journal_mode than expected for temporary database files. I have a commit coming that addresses this for this test.

In the future if a test is failing please just let the failure be known so it can be addressed.