search

datacarpentry / sql-ecology-lesson

Data Management with SQL for Ecologists
http://datacarpentry.github.io/sql-ecology-lesson
Other
49 stars 145 forks source link

SQLite Manager and new Firefox #199

Closed kcranston closed 6 years ago

kcranston commented 6 years ago

Amy Hodge points out on this thread that the new release of Firefox does not support the SQLite Manager plugin. Couple of things we should do:

liz-is commented 6 years ago

Would the Firefox Extended Support Release be useful here in the short term? It's intended to provide extended support for Java-based plugins, and may still support SQLite Manager.

twitwi commented 6 years ago

ESR are probably ok in the short term, up to june 2018: https://www.mozilla.org/en-US/firefox/organizations/faq/

swaldman3 commented 6 years ago

This is correct - the ESR will allow old extensions, inclding SQLite Mgr. However, this is only a short-term option, as it reaches end of life in mid-2018. Also: Firefox does not make it easy to have multiple versions installed, and I think we would get a lot of pushback if we asked learners to "downgrade" their browser version for the sake of the software that we want them to use.

naupaka commented 6 years ago

As an alternative to Firefox, there's DBeaver. Apache license, works on all platforms. Also will work with many different types of database engines, not just SQLite.

kcranston commented 6 years ago

It looks like there are some alternatives out there (and perhaps some better UI than the current SQLIte Manager plugin!). What we need now is some testing. Anyone interested in participating? Maybe we could pick a day, have 1-2 people run through the lesson with each tool, and report back on the experience. Thoughts? Particularly @tpoisot and @orchid00

If you are interested, leave a comment below.

naupaka commented 6 years ago

Happy to go through the lesson with DBeaver. I've been impressed with it for my own work.

tpoisot commented 6 years ago

I use SQLite browser, but DBeaver looks nice.

tpoisot commented 6 years ago

And of course, there is a SQLite backend for Jupyter.

PeterSmyth12 commented 6 years ago

DB Browser for SQLite from http://sqlitebrowser.org/ which a few people have mentioned seems to have all the required functionality, it even has a small graphing pane which seems like overkill to me.

I have just written the SQL lesson for the forthcoming Social Sciences curriculum, with several screenshots of the plugin and a tour of the facilities.

I feel I will have to re-write it now, I could just leave out the details of the environment in use, but I would rather not. For example creating a table using the GUI is likely to be slightly different depending on choice.

DBeaver which I have only just looked at would seem to have the advantage that it could be used with different database types, so presumably if anyone developed a lesson for MySQL or MariaDB the same interface could be used. On the downside (I'm speculating, as I haven' actually tried it) it could be more complex to use which wouldn't be good for people new to SQL in the first place.

As we expect the attendees to install the necessary s/w in advance I don't think we can go for any complicated solutions.

I will try out the Social Sciences SQL lesson with DB Brower for SQLite and report back.

mkweskin commented 6 years ago

Hi all- I just completed the Carpentries Instructor Training yesterday and used the "SQL for Ecology: Basic Queries" as the episode for my teaching exercises. This issue with Firefox dropping support for the SQLite Manager plugin came up during my training.

Today I tried both DBeaver and "DB Browser for SQLite" on my Mac with the beginning of the SQL for Ecology lesson and concur with @PeterSmyth12 that the DB Browser seems like the way to go. I found DB Browser very intuitive and was quickly able to create a new SQLite DB and import the CSVs. It looks like it has the about the same functionality as the Firefox plugin and I think the interface is easier (and the error messages less scary for novices). Full disclosure: I used an early version DB Browser years ago, so I wasn't a complete novice with it.

DBeaver is clearly extremely powerful, and I might check it out to work with some of my MySQL DB. However, I found it quite frustrating to figure how to create a new SQLite database and import the CSV files.

naupaka commented 6 years ago

Having now played with both, I agree with @mkweskin about it being quite challenging to make a new SQLite database from CSV files in DBeaver and that is probably reason enough not to use it for an intro workshop...

PeterSmyth12 commented 6 years ago

Both DBeaver and DB Browser seem to have problems with importing csv files.

There are instruction for DBeaver here ( a six point plan about half way down). There is also an outstanding request outlined here.

dbeaver_csv

The version I downloaded is 4.2.5 so I guess this will be the next release.

Asking for the ability to change the datatypes on the load is in keeping with what you would expect and is/was available with the Firefox plugin. Currently I cannot get it to let me change the datatypes even after I have imported the file (as all TEXT fields) - The properties screen won't let me change anything.

DB Browser also doesn't give the option to change the datatypes on load, but at least it does allow me to modify the table structure afterwards.

Another slight issue I have with DB Browser is that it seems to treat the whole session as a transaction, if you don't say you want to save the database at the end you lose all of your changes.

What both have over the Firefox plugin though is the ability to save and load files of SQL and execute multiple statements at a time.

mkweskin commented 6 years ago

I checked out another open source, cross platform tool I found: SQLiteStudio.

orchid00 commented 6 years ago

Question: what do we do in the mean time, while deciding the next tool?

tpoisot commented 6 years ago

Let me summarize

Tool Installation Multi-platform CSV import SQLite only
SQLiteStudio standalone yes yes yes
sqlite3-kernel Jupyter yes no yes
DBBrowser standalone yes yes yes
DBeaver standalone yes yes no
danielsmaxwell commented 6 years ago

I've started working with SQLiteStudio and like the interface. It appears to be a rather capable tool.

remram44 commented 6 years ago

Those are all opensource and multiplatform. sqlite3-kernel really stands apart (and is definitely harder to setup, though could be hosted via JupyterHub), I guess we'll need to look into the individual features...

ToolLicense
SQLiteStudioGPLv2
sqlite3-kernelBSD-3
DBBrowser for SQLiteMozilla & GPL
DBeaverApache 2.0
FalconMIT
PeterSmyth12 commented 6 years ago

For the SQL lesson in the Social Sciences curriculum I have decided to use DB Browser. Perhaps not perfect, but straightforward to install and use.

I have attached word documents covering the installation, launching and creating a table. Installing db browser.docx Launching DB Browser.docx Using the DB Browser application to create tables.docx

All SQL runs as expected, although you need to be quite particular about including the ';' at the end of the statements. (even if you are just selecting a single statement to execute from the pane, if any of the previous statements are not terminated with a ';' it gets upset).

Although several of us have moaned about having to change data-types after creating a table. The modify table window does have the nice facility whereby as you change the options in the upper pane, you can see the DDL statement being modified in the lower pane. I see this as a great aid for teaching.

justinclift commented 6 years ago

@PeterSmyth12 As one of the DB Browser for SQLite developers, that sounds pretty awesome. :smile:

Not sure if it's useful for your audience, but we've rewritten large parts of our CSV import code since our last major release. It now imports significantly faster (at least 3x as fast in most cases), and field data types are auto-detected on the way through.

Naturally though, you can still (manually) change the data types afterwards if you want. :smile:

If that sounds interesting, we have nightly builds:

    https://nightlies.sqlitebrowser.org/latest/

We're also fairly responsive to bug reports, feature requests, and that kind of thing too:

    https://github.com/sqlitebrowser/sqlitebrowser/issues

Hopefully that helps. :smile:

remram44 commented 6 years ago

sqlitebrowser

sqlitebrowser ("DBBrowser for SQLite") seems very good. The interface is not too cluttered, it is easy to import and export CSV, viewing/changing the data is intuitive. The schema editor is available under a button, it seems complete enough (though I was confused for a moment trying to create an index -- that is a separate button from the main view). There are obvious commit/rollback buttons, and plotting functionality that I guess is useful in some cases. Mac download is 14MB.

screenshots screen shot 2017-12-09 at 20 44 24

SQLiteStudio

SQLiteStudio is also very complete. The interface is a bit more cluttered (multiple toolbars with 10+ icon-only buttons; I'm also unsure about the multi-window feature, with the navbar at the bottom). It seems to be putting more emphasis on editing the schema (default view) but the data is one-click away in another tab. Can open multiple databases at the same time. Has a user-defined-function editor. Mac download is 25MB.

screenshots

DBeaver

DBeaver is Java, based on Eclipse. Takes a while to load, goes through scary assistant to create a "connection", asks to download SQLite3 JDBC driver. Interface is based on Eclipse, with multiple panes such as "projects", "database navigator". Seems to be aimed at power users more than being a GUI for beginners. Unfortunately I couldn't get past the JDBC driver download because that kept failing. Mac download is 43MB.

screenshots

Falcon

Falcon is a multi-backend (like DBeaver) SQL client by plotly. Making plot seems to be the primary application of this tool. I could not see a way to edit the data nor to view or edit the schema of a database, I couldn't display the content of a table without typing the SELECT as SQL. All it does is plot, send data to plotly, or export to CSV. Mac download is 90MB.

screenshot

My vote so far is SQLitebrowser.

justinclift commented 6 years ago

As a data point, we use the abbreviation "DB4S" for "DB Browser for SQLite". Much easier to type. 😁

If anyone's wondering why the mismatch between our domain name "sqlitebrowser.org" and the project name... we started out with the project name of "SQLite Browser" too, which became fairly widely known.

But it also turned out to cause support issues for the official / main SQLite project iself. Thus a name change was needed and the easier ones were already taken. :wink:

amyehodge commented 6 years ago

I have submitted a PR that addresses this in the short-term, i.e. notifies people of the problem and changes the download links to the Firefox ESR page.

ChristinaLK commented 6 years ago

one data point: we used db browser for sqlite this week and it worked great.

justinclift commented 6 years ago

Possibly useful, one of our team members is adding bar charts to our "Plot" panel. eg:

"Stacked bars" checked and "Show legend" checked

imagen

"Stacked bars" unchecked (grouped) and "Show legend" unchecked (defaults)

imagen

Kind of thinking that might be useful both in classes, and in general use. :smile:

The feature code itself is working and ready for review (not yet started). Hopefully that'll be done in the next few days, after which it'll be available in our nightly builds (and next release onwards).

In the meantime, if anyone wants to give it a whirl we've made Windows (32 and 64-bit) builds available for testing:

remram44 commented 6 years ago

@justinclift Plotting is not really the focus of the SQL lessons, but it's good to see that DB4S is so actively developed.

Maintainers: any objections to using DB4S? If people are already using it for teaching, we should go ahead and officially request a PR to update the lesson. Thanks! I can take a crack at it, but not in the next few days see #203.

justinclift commented 6 years ago

@remram44 No worries at all. :smile:

dannguyen commented 6 years ago

OK I just stumbled upon this thread when searching for the state of SQLite3 in Firefox and other browsers (I had heard that SQLite Manager had stopped working, from other data journalism editors). I just want to say that I was pleasantly surprised to discover that DB Browser for SQLite, which is what I've used for a few years (with great satisfaction) for teaching, apparently has visualization functions!

Very cool. I like DB4S because of how good it is as just a SQLite client. I teach that visualization (and many other data processes) should involve porting data to other tools, but just wanted to echo the sentiment of how cool it is that DB4S is so actively and well-maintained. It's one of my favorite and most personally-used open source projects, and data journalism orgs should be giving the DB4S team awards :)

justinclift commented 6 years ago

Thanks @dannguyen, that's very much appreciated.

As a thought, we recently created a Patreon account. If you know of people who would be up for sponsoring us, that would be very welcome too. :grin: