Saturday, March 2, 2013

Sqlite Manager for Mac

This post describes a solution for Mac, but can also be used for Windows and Linux with some modifications.



If you want a free solution to manage your not so big Sqlite databases probably the best option for you is sqlite-manager which initially was just a Firefox add-on. Now it is also shipped as a xulrunner application. A complete list of supported applications can be found here.

Firefox or Thunderbird fans can easily install a plugin and be all set, but if you don't want to have some arbitrary software which main purpose is not managing databases, then you might like to go with standalone xulrunner application.

Kiveo's intention has been good, but is now outdated and in fact I couldn't set up anything. Here is how the process works for sqlitemanager-xr-0.7.7-all.zip.

Installation

  1. Get sqlitemanager-xr-0.7.7-all.zip
  2. Unpack sqlitemanager-xr-0.7.7-all.zip
  3. Install xulrunner-2.0.en-US.mac-pkg.dmg
  4. Run
    /Library/Frameworks/XUL.framework/xulrunner-bin --install-app /absolute/path/to/unpacked/sqlitemanager-xr-0.7.7-all
    This will install sqlite-manager application to /Applications/lazierthanthou/sqlite-manager.app which can be run from Applications if you open it in Finder
    Note: path must be absolute
  5. For convenience, move sqlite-manager to Applications root. Just open Applications in Finder, drag sqlite-manager from lazierthanthou folder and drop into Applications root. lazierthanthou  folder can be deleted
  6. That's it. Now sqlite-manager can be accessed from Launchpad
  7. xulrunner-2.0.en-US.mac-pkg.dmg and sqlitemanager-xr-0.7.7-all.zip and unpacked folder can be deleted.
Notes: sqlitemanager-xr-0.7.7-all in its application.ini says that it can be run with xulrunner starting from version 2.0 up to version 11.0a1. I tried to run sqlite manager with xulrunner 11.0 and 10.0, but that didn't work. 2.0 works for sure. 

Deinstallation

To completely uninstall sqlite-manager and xulrunner do this:
  1. For sqlite-manager just open Finder and drag sqlite-manager to the Trash
  2. To remove xulrunner, open up Terminal and:
    1. sudo rm /private/var/db/receipts/org.mozilla.xulrunner.bom  /private/var/db/receipts/org.mozilla.xulrunner.plist
    2. sudo rm -fr /Library/Frameworks/XUL.framework/

Troubleshooting

sqlite-manager is a very nice tool which supports in-memory databases. It supports import from SQL, XML and CSV. It also supports export to SQL, XML and CSV, but as for version sqlitemanager-xr-0.7.7-all it doesn't support export from in-memory database, only from disk database. If you try to export from in-memory database sqlite-manager will do nothing. Quietly. To somehow understand what's going on (not only in this case), go to Tools->Options, open User Interface tab and under Toolbar Settings tick Show the Debug toolbar. After that's done you may notice that gear and terminal icons have appeared on the top right corner of sqlite-manager main window. Click on gear icon will bring you to options menu which developer doesn't recommend to alter. Click on terminal window will show an informational window with everything what happens in sqlite-manager. Log viewer in other words. So, if you don't understand what's going on in your sqlite-manager, just open up terminal and view the logs. Those are fairly informative and you'll have a big chance to understand the problem.

9 comments:

  1. YOU can use on MAC OS X - free tool Valentina Studio, does everything you need, and does it very well http://www.valentina-db.com/en/valentina-studio-overview
    You can install Valentina Studio (FREE) directly from Mac App Store: https://itunes.apple.com/us/app/valentina-studio/id604825918?ls=1&mt=12

    ReplyDelete
    Replies
    1. This is an interesting alternative, thank you for sharing. It features three ways of representing the layout. An interesting one is a diagram which can show how the database look like and how different tables relate to each other.

      Another thing which caught my attention was building a query by simply dropping the tables on a diagram layout and selecting which fields you'd like to see. A flip side of that really cool feature is that I couldn't find a way to set LEFT OUTER JOIN. The issue is reported here http://www.valentina-db.com/bt/view.php?id=6448.

      Also I submitted a feature request (http://www.valentina-db.com/bt/view.php?id=6449) to have a possibility to edit the query after you've built it with SQL Builder.

      I wish the table layout could be scrolled in a native Mac OS way (two fingers slide), but it can only be scrolled by drag and drop of the scrollbar. And the last thing I'd like to see there is a colored representation of different field types and values, for instance NULL values are shown up as red cells in SQLite Manager and other colors are used to indicate other things. Otherwise, the tool is good. Thanks for sharing once again.

      Delete
    2. Hi Sergey,

      yes we have see your reports in the mantis :) Thank you for points!
      Something was answered. Some more answers here:

      1) LEFT OUTER really a bug, guys fixing it now
      2) Scroll of table list -- already was in mantis. So also on TODO
      3) You cannot edit result SQL and copy paste it, because Query Builder is feature of Valentina Studio PRO. In free version you can play with it, you can see result query, but you cannot copy paste you. But even in this mode this QB can be helpful for people to see how query should looks

      4) About coloring ... hmm ... we need discuss this here. May be only as something on user request ...
      Can you report this also as feature request from your Mantis account, so we can discuss this via Mantis all together.


      5) You do not mention yet absolutely super-powerful Data Editor in Valentina Studio :-)
      please check it. Related Data Editor -- is a killer feature.

      6) in nearest days we will upload english version of "Vstudio Introduction", 11 min movie on youtube.

      Delete
    3. Thanks for following on that Ruslan. Great to know some points from the list are assigned and on track.
      As for #3 I absolutely understand that should be a paid thing. Valentina Studio offers many interesting features apart from that.
      As for #4 I created a feature request - http://www.valentina-db.com/bt/view.php?id=6451
      As for #5 I couldn't find a way to open Data Editor. I have to say I only tried SQLite with Valentina Studio. Could you write a couple of lines on what it is and how to use it. When I tried to open Data Editor with SQLite, the menu was grayed out and the icon in toolbar was disabled.
      As for #6 this is wonderful. We can share a link to introduction here since we've started a conversation on that.

      Delete
    4. #2 - FIXED
      #4 - Something added
      #5 - To open data editor for a table for any DB engine, just double click table in the schema editor. OR open contextual menu (right click) you will see command "Open in DataEditor"

      #6 - video can be found here: http://www.youtube.com/watch?v=1wAd1IrwKAs

      Delete
    5. Docs about Data Editor and Related Data Editor you can see here in Valentina WIKI:

      Delete
    6. Thank you for keeping this up to date. Three questions:

      1. Have this changes been published already?
      2. How can I update an existing version of Valentina Studio? I opened the application, looked around and couldn't find update menu item.
      3. The last post about Valentina WIKI has no link.

      Delete
    7. 1) beta build of 5.3 is expected today evening. Link of betas: http://www.valentina-db.com/download/beta/

      2) App itself have Check for Updates to inform when next release is ready. It will show you beta builds only if you enable this option in prefs. But download and install is external.

      3) Ops, probably because I have wrap by < / >
      Here it is again

      http://www.valentina-db.com/docs/dokuwiki/v5/doku.php?id=valentina:products:vstudio:help:data_editor:data_editor

      Delete
    8. Could you write a full path in preferences where I can tick the option to download betas? I looked around twice and couldn't find the "download betas" option

      Delete