5 ms·

>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 typ

by 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.
  - simonw 5y ago
    SQLite fixed that a few releases ago with STRICT tables: https://www.sqlite.org/stricttables.html https://www.sqlite.org/stricttables.html
- magicalhippo 5y ago
  Formulas is a big one for me. As a programmer I often use Excel to generate insert/update SQL statements for example. So I'd need formulas with not just numerical but also string manipulation functions. Also filtering is essential. So nice to be able to see all the values in the filter dropdown, easy to quickly spot weird values.
  - dotancohen 5y ago
    How does this sound? Formulae are supported, but the cell (not column) must be set as the Formulae datatype. The first formula that I am supporting is SUM, as it is easy to code in SQL. What are the other most common formulae that you use? I'll only implement the integer / float formulae at first, but I'll add string manipulation later. > Also filtering is essential. Filtering, of could. I've already got a hidden column for all rows isDisplay. > So nice to be able to see all the values in the filter dropdown, easy to quickly spot weird values. Perhaps instead of filtering, you'd like to see outliers or the range of values?
    - magicalhippo 5y ago
      > Formulae are supported, but the cell (not column) must be set as the Formulae datatype As long as it's not tedious to set a few thousand that sounds great. > What are the other most common formulae that you use? I mostly use SUM, COUNT and IF (if-then), along with functions to check if a string value is a valid number. Also string formatting of numbers and dates (for concatenating with text). I've also used lookups, ie find the row matching this and extract the value from the given column in that row. Though I'm not super happy with the way Excel does that, surely some room for improvement. > Perhaps instead of filtering, you'd like to see outliers or the range of values? In addition. Sometimes I just want to view all the values matching X, other times I want to quickly see any outliers. Definitely ranges, especially for numbers (just larger than 0 for example, or between 5 and 10). Sounds like a very interesting project, if you got a link I'd be interested in tracking progress. If not, I'd be happy if you did a Show HN when you're ready :)
      - 5y ago
- kergonath 5y ago
  That’s awesome! Where can we subscribe for any announcement? ;) To me, a critical one is easy plots. Checking how an arbitrary column changes as a function of an arbitrary other column by adding a scatter plot in less than 5 seconds is fantastic. Formulae are also very useful. Adding numerical derivatives or integrals by just putting a formula in a new column is very useful as well. The point is not to have publication-quality, highly accurate numbers, but just quick and dirty operations to see if it warrants further investigation. I am happy to discuss my use cases if you are interested (it might be going a bit out of topic for this thread).
  - dotancohen 5y ago
    For right now I've started a Git repo but not uploaded any code until I decide on an architecture. https://github.com/dotancohen/structuresheet https://github.com/dotancohen/structuresheet I'll see what cross-platform plotting libraries I can include. Thank you for the idea, I agree it is important. Yes, I am very interested in your use cases! My Gmail username is the same as my HN username.
- Existenceblinks 5y ago
  > strongly-typed database Python / Qt / SQLite I don't see any part that gives a strongly-typed properties. Do I miss something?
pelasaco 5y ago
We are talking about scientists, which are for sure able and used to work through different tools during their career. And we are in 2022. We have good tools that with investment could be a much better fit. Said that, I don't know what one cannot do with i.e RStudio to use Excel for science. In a second moment, python, pandas and notebook are pretty accessible too..
- kergonath 5y ago
  > We are talking about scientists, which are for sure able and used to work through different tools during their career. And we are in 2022. Scientists are not superhuman. Just like anybody, they’ll jump through a lot of hoops if they think the results justify it, but they are sometimes quite resistant to change for the sake of change, and sometimes even to change itself. One can be a great chemist or know all there is to know about how purple long-tailed fruit flies from Siberia and have no clue about how computers work. Or be very proficient in a given piece of complex software to process NMR spectra and barely able to operate Outlook. But these people all make do with Excel. > In a second moment, python, pandas and notebook are pretty accessible too It’s much heavier, the IDEs are much more complex than Excel, and quite a lot of people on Earth are not natural programmers. Startup time, learning curve, steps to get a useful graph to check a trend, etc. All friction adds up. I’ve seen it countless times: when you show them the results of a complex workflow, they are excited. They start getting distracted when you talk about architecture, and they’re lost when you go into things like pandas and scipy. Then they nod politely, keep doing their stuff in Excel, and call you when they need a bit of wizardry for a paper. In short, they are regular users, even if the software they use can be highly specific. Ease of use and lack of friction are paramount.

by jasode 5y ago

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.
- simonw 5y ago
  SQLite fixed that a few releases ago with STRICT tables: https://www.sqlite.org/stricttables.html https://www.sqlite.org/stricttables.html
magicalhippo 5y ago
Formulas is a big one for me. As a programmer I often use Excel to generate insert/update SQL statements for example. So I'd need formulas with not just numerical but also string manipulation functions. Also filtering is essential. So nice to be able to see all the values in the filter dropdown, easy to quickly spot weird values.
- dotancohen 5y ago
  How does this sound? Formulae are supported, but the cell (not column) must be set as the Formulae datatype. The first formula that I am supporting is SUM, as it is easy to code in SQL. What are the other most common formulae that you use? I'll only implement the integer / float formulae at first, but I'll add string manipulation later. > Also filtering is essential. Filtering, of could. I've already got a hidden column for all rows isDisplay. > So nice to be able to see all the values in the filter dropdown, easy to quickly spot weird values. Perhaps instead of filtering, you'd like to see outliers or the range of values?
  - magicalhippo 5y ago
    > Formulae are supported, but the cell (not column) must be set as the Formulae datatype As long as it's not tedious to set a few thousand that sounds great. > What are the other most common formulae that you use? I mostly use SUM, COUNT and IF (if-then), along with functions to check if a string value is a valid number. Also string formatting of numbers and dates (for concatenating with text). I've also used lookups, ie find the row matching this and extract the value from the given column in that row. Though I'm not super happy with the way Excel does that, surely some room for improvement. > Perhaps instead of filtering, you'd like to see outliers or the range of values? In addition. Sometimes I just want to view all the values matching X, other times I want to quickly see any outliers. Definitely ranges, especially for numbers (just larger than 0 for example, or between 5 and 10). Sounds like a very interesting project, if you got a link I'd be interested in tracking progress. If not, I'd be happy if you did a Show HN when you're ready :)
    - 5y ago
kergonath 5y ago
That’s awesome! Where can we subscribe for any announcement? ;) To me, a critical one is easy plots. Checking how an arbitrary column changes as a function of an arbitrary other column by adding a scatter plot in less than 5 seconds is fantastic. Formulae are also very useful. Adding numerical derivatives or integrals by just putting a formula in a new column is very useful as well. The point is not to have publication-quality, highly accurate numbers, but just quick and dirty operations to see if it warrants further investigation. I am happy to discuss my use cases if you are interested (it might be going a bit out of topic for this thread).
- dotancohen 5y ago
  For right now I've started a Git repo but not uploaded any code until I decide on an architecture. https://github.com/dotancohen/structuresheet https://github.com/dotancohen/structuresheet I'll see what cross-platform plotting libraries I can include. Thank you for the idea, I agree it is important. Yes, I am very interested in your use cases! My Gmail username is the same as my HN username.
Existenceblinks 5y ago
> strongly-typed database Python / Qt / SQLite I don't see any part that gives a strongly-typed properties. Do I miss something?

pelasaco 5y ago

We are talking about scientists, which are for sure able and used to work through different tools during their career. And we are in 2022. We have good tools that with investment could be a much better fit. Said that, I don't know what one cannot do with i.e RStudio to use Excel for science. In a second moment, python, pandas and notebook are pretty accessible too..

kergonath 5y ago
> We are talking about scientists, which are for sure able and used to work through different tools during their career. And we are in 2022. Scientists are not superhuman. Just like anybody, they’ll jump through a lot of hoops if they think the results justify it, but they are sometimes quite resistant to change for the sake of change, and sometimes even to change itself. One can be a great chemist or know all there is to know about how purple long-tailed fruit flies from Siberia and have no clue about how computers work. Or be very proficient in a given piece of complex software to process NMR spectra and barely able to operate Outlook. But these people all make do with Excel. > In a second moment, python, pandas and notebook are pretty accessible too It’s much heavier, the IDEs are much more complex than Excel, and quite a lot of people on Earth are not natural programmers. Startup time, learning curve, steps to get a useful graph to check a trend, etc. All friction adds up. I’ve seen it countless times: when you show them the results of a complex workflow, they are excited. They start getting distracted when you talk about architecture, and they’re lost when you go into things like pandas and scipy. Then they nod politely, keep doing their stuff in Excel, and call you when they need a bit of wizardry for a paper. In short, they are regular users, even if the software they use can be highly specific. Ease of use and lack of friction are paramount.