Switching from pysqlchipher3 to sqlcipher3
Closed this issue · 14 comments
Hey everyone,
I am currently working on the migration to SqlAlchemy 2.0. It seems like the currently used pysqlchipher3 package is using an too old version of SQLite. I found a more up-to-date implementation: sqlcipher3. This slightly changes the installation steps on Windows, which I am updating now. Can anyone confirm if this package also works on MacOS? Do the install instructions (except the GitHub URL) have to be changed?
git clone https://github.com/coleifer/sqlcipher3
cd sqlcipher3
SQLCIPHER_PATH=$(brew info sqlcipher | awk 'NR==4 {print $1; exit}'); C_INCLUDE_PATH="$SQLCIPHER_PATH"/include LIBRARY_PATH="$SQLCIPHER_PATH"/lib python setup.py build
SQLCIPHER_PATH=$(brew info sqlcipher | awk 'NR==4 {print $1; exit}'); C_INCLUDE_PATH="$SQLCIPHER_PATH"/include LIBRARY_PATH="$SQLCIPHER_PATH"/lib python setup.py installWould really appreciate some help on updating the installation docs!
On this note, having just yesterday followed the build process, I ran into a couple of speedbumps:
pysqlcipher3's setup.py has an additional problem beyond the one described - if the OpenSSL library path contains a space, extra_link_args improperly quotes the /LIBPATH: argument as "/LIBPATH:\"..." rather than /LIBPATH:"..." (this is separate to the problem with the quote_argument function) resulting in a linker error during the build. This is probably a bug in distutils. The default OpenSSL install location on Windows is in Program Files, so you're pretty much guaranteed to hit this issue. I got around this by using old 8.1 path formatting, i.e. setting the OPENSSL_CONF environment variable to C:\PROGRA~1\OpenSSL-Win64\bin\openssl.cfg. (Hey look, being old pays off sometimes!)
Installing OpenSSL also doesn't necessarily put the DLLs in PATH, which results in a DLL load error when the pysqlcipher3 egg (_sqlite3.cp311-win_amd64.pyd) is loaded. I had to copy libcrypto-3-x64.dll from the OpenSSL binaries to path\to\Python3\Lib\site-packages\pysqlcipher3-1.2.0-py3.11-win-amd64.egg\pysqlcipher3 to fix this. You can technically copy stuff to system32 to get the same result, but that's really poor practice since it will never get updated.
And a minor issue: the VS path listed in the instructions is for Microsoft Visual Studio 14.0, which is out of date; the correct path for VS2022 is C:\Program Files\Microsoft Visual Studio\2022\Community\VC\Tools\MSVC\[version]\include.
I don't know how much of this will be relevant after migration to sqlcipher3, but I figured I'd post here anyway since it's relevant to the current state of things and you might run into similar issues during the migration.
Hmm, didn't run into the issue with the space in the path of the OpenSSL library, just checked my PATH and OPENSSL_CONF variables and it points to Program Files. But i think it would help to clarify that in the docs. Also thanks for the hint with the 8.1 formatting, didn't know that one:D I will update the docs when i have some free time.
And yes, we should probably switch to the newest version of VS, i didn't update that in years to be honest since i never use it...
Migrating to sqlcipher3 only fixes the issues with the quotes, sadly...
Thanks for your help!
Hey, i updated Visual Studio to 2022 and am running into some different issues while building the amalgamation:
Generating code
Finished generating code
LINK : fatal error LNK1158: cannot run 'rc.exe'
error: command 'C:\\Program Files (x86)\\Microsoft Visual Studio 14.0\\VC\\BIN\\x86_amd64\\link.exe' failed with exit code 1158
Installing then fails with
...\.tmp\sqlcipher3\src\blob.h(4): fatal error C1083: Cannot open include file: 'sqlcipher/sqlite3.h': No such file or directory
error: command 'C:\\Program Files (x86)\\Microsoft Visual Studio 14.0\\VC\\BIN\\x86_amd64\\cl.exe' failed with exit code 2
maybe you have an idea? Should not be the link error you described above, tried with the fix you suggested.
Found this online, but that only results in a different error:
LINK : fatal error LNK1327: failure during running rc.exe
ah never mind, got it to work:)
I updated the installation instructions. I did not run into the DLL load error you were talking about, so i left that out of the docs. Could that be a miss-configuration? I also have C:\Program Files\OpenSSL-Win64\bin path set in the PATH environment variable. maybe that's the issue?
To the MacOS users: Still would appreciate some info if sqlcipher3 works so that i can merge the SqlAlchemy2.0 branch!
I compared the two packages and think there shouldn't be any problem. Closing this issue for now
Ive tried the osx instructions using sqlcipher3 and It doesn't seem to work, I keep getting the sqlalchemy.exc.DatabaseError: (sqlite3.DatabaseError) file is not a database error which indicates sqlalchemy is not trying to decrypt the db properly I believe
I have an m1 Mac and I believe this is part of this issue with building sqlcipher3
python setup.py build seems to be failing silently and I cant properly import it once running install so Im guessing this is causing sqlalchemy to fallback to the regular sqlite engine
ill keep digging
Hey @dadrake3,
thank you for responding and looking into it. Did installing the old pysqlcipher3 work for you?
I think the problem isn't SqlAlchemy, but a failed installation of sqlcipher3. If it cannot be imported, the regular sqlite3 package is imported as a fallback:
@dylanljones yes i was able to get pysqlcipher3 to install. I think part of the problem is im using an m1 mac.
Using the arm64 version of sqlcipher from brew and an arm64 conda env, i get missing header file errors when building and installing both pysqlcipher3 and sqlcipher (I had to use —force on the build step otherwise it fails silently)
when i switched to the x86 version of sqlcipher from x86 brew and used an x86 conda env I was able to successfully build and install pyscipher3. When I tried for sqlcipher3 the build appears to be successful (i.e no errors like before) but it doesn’t actually link the c binary and I can’t import it.
I opened a ticket on sqlcipher3 but the maintainer was less than helpful lol.
also side note when I pip install rekordbox it wants to default to sqlalchemy 1.4 rather than 2.0 but idt this is related since the behavior described above occurred both for pip installs and when I built from requirements.txt.
Thats weird, but i too had some problems building sqlcipher3 against the amalgamation on Windows. Sorry you are having issues, but the switch is kind of mandatory since SqlAlchemy 2.0 doesn't work with pysqlcipher3 and it doesn't seem like that package is still maintained. Hope we can figure this out soon for M1 macs. I just saw the issue you created, kind of had to laugh at the response of the maintainer:D He could be a little more helpful...
SqlAlchemy 2.0 will be used from version 0.2.1 on-wards, so the changes aren't on pip yet. If you install from GitHub it should install SqlAlchemy2.0 though!
ahh makes sense, Ill see if I can get sqlcipher3 to build correctly and then give you an update, hopefully I can do it without the need for the x86 libraries too because switching brews and condas isn't ideal lol
Hey @dadrake3, did you manage to get sqlcipher3 to work? If not, you could also try to build against the amalgamation!
@dylanljones Hey forgot to follow up, my bad. I was able to build and install sqlcipher3 against my home-brew install by adding some symlinks. Pyrekordbox also works properly now too
these 2 did the trick
ln -s /opt/homebrew/lib/libsqlcipher.a /usr/local/lib/libsqlcipher.a
ln -s /opt/homebrew/include/sqlcipher /usr/local/include/sqlcipher
No worries, thank you for the feedback!:) Glad everything works for you now. I will add the hint to the install docs.