4 ms·
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 stro
by 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 agoI'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 agocheck (typeof(column) = 'integer')
- dotancohen 5y agoSQLite 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 agoSQLite fixed that a few releases ago with STRICT tables: https://www.sqlite.org/stricttables.html https://www.sqlite.org/stricttables.html
- magicalhippo 5y agoFormulas 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 agoHow 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 :)
- dotancohen 5y ago> As long as it's not tedious to set a few thousand that sounds great. How are you setting them? Grab and drag? I can support that. > SUM, COUNT and IF (if-then) Perfect, thank you. Right now I've started a Git repo with a roadmap but I've not uploaded any code yet as I'm still deciding on the architecture: https://github.com/dotancohen/structuresheet https://github.com/dotancohen/structuresheet
- kergonath 5y agoThat’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 agoFor 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?