SQLite Managers for Mac OS #sqlitemanagers

There is growing interest in the RootsMagic user community in being able to run SQLite Tools for RootsMagic scripts under the Apple MacIntosh operating system. If RootsMagic Inc succeeds in releasing a multi-platform version, then there will undoubtedly be a surge in the number of native-Mac OS users of RootsMagic and corresponding pursuit by some of them of a good SQLite manager. Neither of the SQLIte managers currently favoured by this wiki for the Windows OS are available for the Mac OS. Moreover, the only comprehensive list of SQLite Management Tools for various operating systems has not been updated since 2011. So this page is intended to consolidate info about potential candidates for the Mac OS to parallel that for Windows on this wiki’s page SQLite Managers.

Important Criteria

Based on the experience with SQLite script development so far for RootsMagic databases, these are factors that vary from manager to manager and would, ideally, be found in one:

  • low cost
  • extension for RMNOCASE collation
  • REGEXP() function for search and filtering
  • run-time parameters for user input (RTP)
  • FTS3, FTS4 (full text search)
  • high level scripting language (Lua, Qt, Tcl, …) for custom functions, more powerful, user-friendly utilities

 

Criterion SQLiteStudio 3
Cost free
RMNOCASE auto substitutes NOCASE
REGEXP() Y
RTP Promised for v3.1
FTS FTS3, FTS4
Scripting Qt, SQL, Tcl custom functions

4 Replies to “SQLite Managers for Mac OS #sqlitemanagers

  1. OK, first things first hello there!
    Just came across the site by accident and joined pretty much straight away.

    I have just downloaded “Native SQLite Manager” for Mac OS from the App Store.

    It seems to handle the RM7.5 database without any problems, no issue with RMNOCASE warnings.

    Very light weight and simple to use.

    HTH

    Cheers

    Chris

  2. Thanks, Chris. Good to have a MacOS user on board. I’m surprised that RMNOCASE does not rear its head. One thing to check would be the REINDEX command. If that goes without protest, then go back to RM and have it do the Database Integrity test on the same database. If it reports no errors, then there is something miraculous happening.

    Tom

  3. Hi Tom

    Well that stopped it dead!
    REINDEX throws out the RMNOCASE error.

    However, all is not lost, at least in 7.5 (I’ll retest the following in 8 when it is released).

    Thanks to mooredan I have compiled and loaded UNIFUZZ as an extension in the app and it works.

    I am running Mac OS Catalina 10.15.5 on an early 2015 13″ MacBook Pro.
    The app is Native SQLite Manager version 1.9.4 (697) downloaded from the App Store.

    1. I followed mooredan’s instructions on his GitHub page, https://github.com/mooredan/unifuzz, to create a folder named UNIFUZZ in my home directory and this contained the UNIFUZZ.dylib file.
    2. Opened Native SQLite Manager and selected Preferences -> Components -> SQLite Extensions then clicked on the Add Extension button.
    3. Navigated to and opened the UNIFUZZ folder and selected the file as above, then ensured it was selected in the list of extensions.
    4. As the extension is unsigned Mac OS will issue a warning message and not open the app, this can be circumvented in the Mac OS Security & Privacy settings after you have tried to open it (full instructions available if needed).
    5. Open the app and run REINDEX NameTable; works perfectly!
    6. Back into Preferences and deselect the UNIFUZZ extension and try re-running REINDEX (may need to restart the app first) and it throws the RMNOCASE error, so re-select, rcycle the app and try again, it works!
    7. Checked database integrity in RM after each REINDEX and no issues whatsoever.

    I will put together a document with links to the pages on your site, clearer instructions and screen prints if anyone needs.

    Last but not least, there are a few warnings when compiling the extension but they do not stop it from compiling and working. I will try and dust off my coding skills and see if I can create a version that compiles cleanly but don’t hold your breath, it’s been a long time since I cut any code!

    So thanks to mooredan, Jean-Chistophe Deschamps, Yourself, et al, there is a working SQLite app on Mac OS that supports RM, so much easier than using a cli.

    Cheers

    Chris

  4. Just a quick update…

    Running REINDEX against idxSourceTemplateName in SourceTemplateTable results in a number of rows being reported as missing. This seems to be the only table impacted by the lack of the RMNOCASE collation in SQLite tools after adding UNIFUZZ.

    Easiest workaround is to not run REINDEX against this index outside RM, or to rerun REINDEX within RM after finishing work in the database.

    Short of figuring out the rules applied in RMNOCASE I don’t think there will be a way around it anytime soon.

    Cheers

    Chris

Leave a Reply