7 ms·

What'd be wrong with using something like DBeaver, SqliteStudio, or PG-Admin? Or buying a tool like DataGrip? Microsoft Access even allows you to create your ow

by JustAQ 5y ago

What'd be wrong with using something like DBeaver, SqliteStudio, or PG-Admin? Or buying a tool like DataGrip? Microsoft Access even allows you to create your own UI forms for a database. You don't need to write your own GUI for databases. Loads exists.

smcl 5y ago
That solves one small part of the problem - viewing and editing the data in a UI. But can those tools address the problem raised by jasode? > "I just sent you an xlsx where the rows highlighted in red are problems and if you can just add your notes to column K, that would be great. Thanks!" This is a pretty common use-case, and neatly demonstrates a few of the reasons Excel is so popular. Sharing a self-contained DB with a colleague that they can view + edit with software they likely already have, modifying the schema easily on the fly, highlighting some rows. And that's not to mention the programmability - from having a simple "=SUM(...)" cell, to hacking some VBA or the newly introduced Lambda (https://www.microsoft.com/en-us/research/blog/lambda-the-ultimatae-excel-worksheet-function/ https://www.microsoft.com/en-us/research/blog/lambda-the-ult...) I wouldn't personally build anything important with Excel as a sort of DB, but I understand why some people would want to
jasode 5y ago
>What'd be wrong with using something like DBeaver, SqliteStudio, or PG-Admin? Or buying a tool like DataGrip? Microsoft Access 1) Those utilities are not typically included in the workstation image of laptops/desktops unlike MS Excel which is already part of Office 365. Millions are already familiar with the GUI of Excel. 2) The datagrid viewers in those tools are not powerful and feature-rich like Excel. They are often missing features that are taken for granted by Excel users: - formatting any arbitrary row or column with bold/italics and change the font color or background cell color. - pivot the data via drag & drop UI (instead of manually writing a SQL cross-tab query) - hide rows or collapse rows into outlines - cut rows 37 to 52 and paste them above row 5. That type of behavior is not easy in generic database viewers because most tables -- by typical design of RDBMS table row ids -- do not consider the visible spatial ordering of rows the way the end user wants to see them on the screen unless one adds an extra column to the table such as "gui_view_order_id". Excel has user specified row ordering as default out-of-the-box behavior. - ... tons of other GUI features like formulas, spell check, etc
- zozbot234 5y ago
  Add an extra table mapping id to gui_order, then just write an easy query/report (or edit the one you already have) to give you a view that's ordered by gui_order first. Not that hard, you just need to make the semantics of what you're doing explicit. This is the inherent tradeoff between raw editing on a spreadsheet (quick and dirty, very error prone) and working on something that has real structure to it, reflecting some underlying semantics.
- dotancohen 5y ago
  Please, keep adding to the "tons of other GUI features" list. I'm just started on a Python/Qt/Sqlite project for spreadsheet-like functionality backed by a strongly-typed database. The things that you mention are perfect features. I'd love to know more.
  - mort96 5y ago
    I'm curious, how do you achieve a strongly-typed database with sqlite? sqlite's lack of verifying that your data fits in your defined schema is by far my biggest problem with sqlite.
    - ElectricalUnion 5y ago
      check (typeof(column) = 'integer')
    - dotancohen 5y ago
      SQLite has a powerful data-validation function for each column, look for CHECK Constraints: https://www.sqlite.org/lang_createtable.html https://www.sqlite.org/lang_createtable.html But the real nice thing is that SQLite will allow spreadsheet-like behaviour by allowing one to enter an invalid data type. I could check that in Python or I can store it and warn "Invalid type blah blah blah". This will make life easier for those coming from a spreadsheet, or importing data. As the program matures, I can reevaluate what to do with invalid data as a default and what options to give the user. Additionally, because SQLite allows arbitrary data in any column, adding support for e.g. formulae is greatly simplified.
gompertz 5y ago
Going to piggyback off this comment to ask - does anyone know a lightweight GUI tool to edit/modify SharePoint Lists? Only way I ever found was through the browser which is painful on large data.
kergonath 5y ago
In an alternate universe we have nice specialised GUI front ends on things like netCDF or SQLite that enable what you say. Or hell, just a spreadsheet-like view over a real database (but with actual names as columns). All the GUIs I have seen are really not up to the task for typical scientific data. They are clunky, heavy, slow, and difficult to use for visualisation. We are not writing new front ends for every type of data, and nobody seems to have written a decent one that could be universal. Myself (not genomics, but Excel is also some kind of universal medium here as well), I store my data in SQLite files (extracts and summaries anyway; complete datasets take several terabytes), which makes retrieving complex information a breeze. But it needs to be documented and you need to be comfortable with the command line and do any kind of visualisation as a supplementary step. I know of a couple of colleagues doing the same, but we don’t use quite the same format, so data exchange is problematic. I use this setup mostly because I need it to work on remote HPC clusters in addition to a bunch of local workstations, and Excel is out of question there.

by JustAQ 5y ago

smcl 5y ago

That solves one small part of the problem - viewing and editing the data in a UI. But can those tools address the problem raised by jasode? > "I just sent you an xlsx where the rows highlighted in red are problems and if you can just add your notes to column K, that would be great. Thanks!" This is a pretty common use-case, and neatly demonstrates a few of the reasons Excel is so popular. Sharing a self-contained DB with a colleague that they can view + edit with software they likely already have, modifying the schema easily on the fly, highlighting some rows. And that's not to mention the programmability - from having a simple "=SUM(...)" cell, to hacking some VBA or the newly introduced Lambda (https://www.microsoft.com/en-us/research/blog/lambda-the-ultimatae-excel-worksheet-function/ https://www.microsoft.com/en-us/research/blog/lambda-the-ult...) I wouldn't personally build anything important with Excel as a sort of DB, but I understand why some people would want to

jasode 5y ago

>What'd be wrong with using something like DBeaver, SqliteStudio, or PG-Admin? Or buying a tool like DataGrip? Microsoft Access 1) Those utilities are not typically included in the workstation image of laptops/desktops unlike MS Excel which is already part of Office 365. Millions are already familiar with the GUI of Excel. 2) The datagrid viewers in those tools are not powerful and feature-rich like Excel. They are often missing features that are taken for granted by Excel users: - formatting any arbitrary row or column with bold/italics and change the font color or background cell color. - pivot the data via drag & drop UI (instead of manually writing a SQL cross-tab query) - hide rows or collapse rows into outlines - cut rows 37 to 52 and paste them above row 5. That type of behavior is not easy in generic database viewers because most tables -- by typical design of RDBMS table row ids -- do not consider the visible spatial ordering of rows the way the end user wants to see them on the screen unless one adds an extra column to the table such as "gui_view_order_id". Excel has user specified row ordering as default out-of-the-box behavior. - ... tons of other GUI features like formulas, spell check, etc

zozbot234 5y ago
Add an extra table mapping id to gui_order, then just write an easy query/report (or edit the one you already have) to give you a view that's ordered by gui_order first. Not that hard, you just need to make the semantics of what you're doing explicit. This is the inherent tradeoff between raw editing on a spreadsheet (quick and dirty, very error prone) and working on something that has real structure to it, reflecting some underlying semantics.
dotancohen 5y ago
Please, keep adding to the "tons of other GUI features" list. I'm just started on a Python/Qt/Sqlite project for spreadsheet-like functionality backed by a strongly-typed database. The things that you mention are perfect features. I'd love to know more.
- mort96 5y ago
  I'm curious, how do you achieve a strongly-typed database with sqlite? sqlite's lack of verifying that your data fits in your defined schema is by far my biggest problem with sqlite.
  - ElectricalUnion 5y ago
    check (typeof(column) = 'integer')
  - dotancohen 5y ago
    SQLite has a powerful data-validation function for each column, look for CHECK Constraints: https://www.sqlite.org/lang_createtable.html https://www.sqlite.org/lang_createtable.html But the real nice thing is that SQLite will allow spreadsheet-like behaviour by allowing one to enter an invalid data type. I could check that in Python or I can store it and warn "Invalid type blah blah blah". This will make life easier for those coming from a spreadsheet, or importing data. As the program matures, I can reevaluate what to do with invalid data as a default and what options to give the user. Additionally, because SQLite allows arbitrary data in any column, adding support for e.g. formulae is greatly simplified.

gompertz 5y ago

Going to piggyback off this comment to ask - does anyone know a lightweight GUI tool to edit/modify SharePoint Lists? Only way I ever found was through the browser which is painful on large data.

kergonath 5y ago

In an alternate universe we have nice specialised GUI front ends on things like netCDF or SQLite that enable what you say. Or hell, just a spreadsheet-like view over a real database (but with actual names as columns). All the GUIs I have seen are really not up to the task for typical scientific data. They are clunky, heavy, slow, and difficult to use for visualisation. We are not writing new front ends for every type of data, and nobody seems to have written a decent one that could be universal. Myself (not genomics, but Excel is also some kind of universal medium here as well), I store my data in SQLite files (extracts and summaries anyway; complete datasets take several terabytes), which makes retrieving complex information a breeze. But it needs to be documented and you need to be comfortable with the command line and do any kind of visualisation as a supplementary step. I know of a couple of colleagues doing the same, but we don’t use quite the same format, so data exchange is problematic. I use this setup mostly because I need it to work on remote HPC clusters in addition to a bunch of local workstations, and Excel is out of question there.