Brave New World databaspreadsheet
Quote from Erwin on August 9, 2019, 6:54 amI'm having visions of an excel (/office calc) where only the file-open and file-save are different.
File-Open connects to a database, and displays a "workbook" with as many tabs as the database has tables (oops relvars). Or perhaps the Open... dialog allows the user to select which ta...relvars to display. Perhaps the Open... dialog also checks the read authority the user has on each relvar and won't even show the ones for which he doesn't.
If the policy on the database is such that all changes must go through "stored procedures" (not an uncommon policy in SQL environments) then the sheets are shown in read-only and editing is not allowed.
File-Save writes all unsaved edits to the database. As a consequence, constraints are only checked at save time. Hmmmmmm. And using optimistic locking on the tuple value of course, so File-Save must now be prepared to handle rejections from the DBMS. But that still leaves my formatting unsaved. So I'd guess I'd still write that in an xlsx-style workbook file. So now there's a dependency between the workbook file and the database which requires proper maintenance (and of course the File-Open dialog must allow to pick the formatting workbook from local store too). Of course the formatting info cannot be of the kind "row 7 has height xyz", but it must be of the kind "the row whose abc key value is pqr, has height xyz". My idea is beginning to fall apart.
Database Listeners delete rows in the spreadsheet and change cell values as updates are done to the database by other users, and also if such updates follow from CASCADE DELETES and such initiated by the spreadsheet user. That requires unrestricted updatable view technology, which is the same as CREATE ASSERTION technology, which is not broadly offered by DBMS products. Also, a DELETE to be reflected in the spreadsheet might clash with an edit the spreadsheet user has pending. I think I'll just stop dreaming.
A lot of database admin tools -- TOAD, pgAdmin, MySQL Workbench, etc. -- essentially do (some of) this (as does MS Access) but all do it as awkwardly as possible, like their respective developers were instructed to meet an "update data in a database" requirement but were never required to use their tool to update data in a database.
Quote from Erwin on August 9, 2019, 6:54 amI'm having visions of an excel (/office calc) where only the file-open and file-save are different.
File-Open connects to a database, and displays a "workbook" with as many tabs as the database has tables (oops relvars). Or perhaps the Open... dialog allows the user to select which ta...relvars to display. Perhaps the Open... dialog also checks the read authority the user has on each relvar and won't even show the ones for which he doesn't.
My preference is that file-open is the place where you choose a non-tabular data source: a database, a schema, perhaps a REST interface or a folder on disk. What you open first is always a 'table of tables'. You navigate from that to individual tables, and from table to table, via links. You can create/delete tables by creating/deleting a row in the 'table of tables'.
If the policy on the database is such that all changes must go through "stored procedures" (not an uncommon policy in SQL environments) then the sheets are shown in read-only and editing is not allowed.
File-Save writes all unsaved edits to the database. As a consequence, constraints are only checked at save time. Hmmmmmm. And using optimistic locking on the tuple value of course, so File-Save must now be prepared to handle rejections from the DBMS.
I shall be happy if I can create a product that works for a single user, with no multi-user concurrency. After all, I'm replacing Excel and Access on my desktop, not a major enterprise line of business database system. The single user version is hard enough.
I think I'll just stop dreaming.
I haven't. But it's a tough gig.
Quote from Erwin on August 9, 2019, 6:54 amI'm having visions of an excel (/office calc) where only the file-open and file-save are different.
Sounds like a Good Thing, provided there are ways of importing and exporting local data stores too.
But that still leaves my formatting unsaved. So I'd guess I'd still write that in an xlsx-style workbook file.
There really is no reason why formatting for table T can't be saved in one or a few formatting tables associated with T. If you're not allowed to write them in the database, put them in some other data store.
Looking at Google Sheets, which is all the formatting I've ever felt the need for, you need the following things. Each one can be defined for an attribute (column format), a primary key (row format), or the combination of both (cell format).
- A numeric or date format string. These strings are quite portable and go back to Lotus 1-2-3 and beyond.
- Font information: font name, font size, bold, italic
- Alignment (left/center/right, top/middle/bottom)
- Text wrapping (overflow, wrap, clip)
- Possibly text rotation (by angle). I've never used this, and it may be overkill
Representing all this relationally is easy.
Database Listeners delete rows in the spreadsheet and change cell values as updates are done to the database by other users, and also if such updates follow from CASCADE DELETES and such initiated by the spreadsheet user.
This is where I think you go off the rails. It's tolerable to have someone else changing a Google Document or Sheet as you edit it; you can see what they are doing and if necessary tell them to stop it. But having a row vanish as you are working on it would be extremely unsettling. Changes to the schema would be evn worse. The local operations should be recorded as a series of database changes and then played back either one-by-one or in a transaction at File Save time. If they fail, you help the user cherry-pick the changes and try again, or save to another data store.
I think I'll just stop dreaming.
"Make no little plans; they have no magic to stir men's blood and probably themselves will not be realized. Make big plans; aim high in hope and work, remembering that a noble, logical diagram once recorded will never die, but long after we are gone be a living thing, asserting itself with ever-growing insistency. Remember that our sons and our grandsons are going to do things that would stagger us. Let your watchword be order and your beacon beauty." —Daniel Burnham, architect and urban planner (ca. 1910)
Quote from johnwcowan on August 9, 2019, 2:23 pmQuote from Erwin on August 9, 2019, 6:54 amI'm having visions of an excel (/office calc) where only the file-open and file-save are different.
Sounds like a Good Thing, provided there are ways of importing and exporting local data stores too.
But that still leaves my formatting unsaved. So I'd guess I'd still write that in an xlsx-style workbook file.
There really is no reason why formatting for table T can't be saved in one or a few formatting tables associated with T. If you're not allowed to write them in the database, put them in some other data store.
Looking at Google Sheets, which is all the formatting I've ever felt the need for, you need the following things. Each one can be defined for an attribute (column format), a primary key (row format), or the combination of both (cell format).
- A numeric or date format string. These strings are quite portable and go back to Lotus 1-2-3 and beyond.
- Font information: font name, font size, bold, italic
- Alignment (left/center/right, top/middle/bottom)
- Text wrapping (overflow, wrap, clip)
- Possibly text rotation (by angle). I've never used this, and it may be overkill
Representing all this relationally is easy.
Any format settings you can specify programmatically can be represented relationally.
Quote from johnwcowan on August 9, 2019, 2:23 pmQuote from Erwin on August 9, 2019, 6:54 amI'm having visions of an excel (/office calc) where only the file-open and file-save are different.
Sounds like a Good Thing, provided there are ways of importing and exporting local data stores too.
As I said, the principal purpose of FileOpen is to select a collection of tables, aka a database, local or remote. Each collection opens as a 'table of tables'.
But that still leaves my formatting unsaved. So I'd guess I'd still write that in an xlsx-style workbook file.
There really is no reason why formatting for table T can't be saved in one or a few formatting tables associated with T. If you're not allowed to write them in the database, put them in some other data store.
The field/column/attribute information is stored in a single 'table of fields'. It can be edited directly, but usually should not. The UI should provide all the table editing needed.
Looking at Google Sheets, which is all the formatting I've ever felt the need for, you need the following things. Each one can be defined for an attribute (column format), a primary key (row format), or the combination of both (cell format).
- A numeric or date format string. These strings are quite portable and go back to Lotus 1-2-3 and beyond.
- Font information: font name, font size, bold, italic
- Alignment (left/center/right, top/middle/bottom)
- Text wrapping (overflow, wrap, clip)
- Possibly text rotation (by angle). I've never used this, and it may be overkill
Representing all this relationally is easy.
Indeed, but this is not quite enough. You need a notional 'type':
- They include bool, text, number (various), date (various), list of values (lookup), link, html, json, image, formula (calculated).
- The user sees it and understands what it means. It contributes to formatting, but can be overridden.
- The system acts on it, for example click on a link, expand a thumbnail into an image; or a custom editor (like JSON).
Database Listeners delete rows in the spreadsheet and change cell values as updates are done to the database by other users, and also if such updates follow from CASCADE DELETES and such initiated by the spreadsheet user.
This is where I think you go off the rails. It's tolerable to have someone else changing a Google Document or Sheet as you edit it; you can see what they are doing and if necessary tell them to stop it. But having a row vanish as you are working on it would be extremely unsettling. Changes to the schema would be evn worse. The local operations should be recorded as a series of database changes and then played back either one-by-one or in a transaction at File Save time. If they fail, you help the user cherry-pick the changes and try again, or save to another data store.
But first get it all working for single user, on unconstrained backend tables.
I think I'll just stop dreaming.
"Make no little plans; they have no magic to stir men's blood and probably themselves will not be realized. Make big plans; aim high in hope and work, remembering that a noble, logical diagram once recorded will never die, but long after we are gone be a living thing, asserting itself with ever-growing insistency. Remember that our sons and our grandsons are going to do things that would stagger us. Let your watchword be order and your beacon beauty." —Daniel Burnham, architect and urban planner (ca. 1910)
Couldn't have put it better. I've been dreaming about this one for 40 years, spent time and money trying too. Show and tell time...
Quote from Dave Voorhis on August 8, 2019, 2:58 pmQuote from johnwcowan on August 8, 2019, 2:00 pmQuote from AntC on August 8, 2019, 6:06 amA significant proportion of the bloat is formatting (including graphing) -- which has nothing to do with the 'business' of spreadsheets -- that is, manipulating values.
Studies show that the actual business of spreadsheets is mostly a convenient UI for table editing and display.
Yes. Experience from my erstwhile workplace suggested that spreadsheets were almost entirely about arranging text in columns, and sometimes rows, where a word processor would have been too confusing or too much work. The admin team didn't even call them spreadsheets. Spreadsheet documents were called "trackers". E.g., "We've sent you a tracker with your new students. Please assign them personal tutors immediately and return the tracker by 2pm tomorrow or your line manager will be notified."
I've no idea how that terminology started. Referring to a "tracker" as a spreadsheet in front of the right (wrong?) people drew dirty looks. Trackers sometimes contained formulae, but it wasn't unusual to see columns that could have held formulae filled in manually, presumably calculated using a pocket calculator or another spreadsheet (each set of values and operators typed individually into the same cell), or perhaps on paper.
You're confusing medium with function: the function is to *track* your performing tasks. The medium is as a spreadsheet; but could equally be some sort of interactive checklist.
When you say "columns that could have held formulae [but don't]" my immediate suspicion is the admin team are using some sort of project management tool, that generates "trackers" with all the fields filled in, and emails them to you, then when you email back can parse the format and automatically update the project tracking. MsProject (server edition) does that, for example. It can communicate via spreadsheet attachment or via HTML form embedded in the email. It can even send you a timesheet form with the tasks you've been allocated this week; a field to enter the hours you spend on it each day; a checkbox to say it's finished or a %done.
I'm dubious anybody is either filling in the thing manually or calculating manually. (Or at least if they are, they're not using the tracking tool properly.)
How many of the other claims on this thread of manually keying/calculating spreadsheets are similarly misunderstood?
BTW re an earlier comment from John: by "values" in cells, I wasn't expecting necessarily numbers. Characters and Strings are just as much "values", and just as much manipulable by spreadsheet formulas.
Quote from AntC on August 10, 2019, 10:49 amQuote from Dave Voorhis on August 8, 2019, 2:58 pmQuote from johnwcowan on August 8, 2019, 2:00 pmQuote from AntC on August 8, 2019, 6:06 amA significant proportion of the bloat is formatting (including graphing) -- which has nothing to do with the 'business' of spreadsheets -- that is, manipulating values.
Studies show that the actual business of spreadsheets is mostly a convenient UI for table editing and display.
Yes. Experience from my erstwhile workplace suggested that spreadsheets were almost entirely about arranging text in columns, and sometimes rows, where a word processor would have been too confusing or too much work. The admin team didn't even call them spreadsheets. Spreadsheet documents were called "trackers". E.g., "We've sent you a tracker with your new students. Please assign them personal tutors immediately and return the tracker by 2pm tomorrow or your line manager will be notified."
I've no idea how that terminology started. Referring to a "tracker" as a spreadsheet in front of the right (wrong?) people drew dirty looks. Trackers sometimes contained formulae, but it wasn't unusual to see columns that could have held formulae filled in manually, presumably calculated using a pocket calculator or another spreadsheet (each set of values and operators typed individually into the same cell), or perhaps on paper.
You're confusing medium with function: the function is to *track* your performing tasks. The medium is as a spreadsheet; but could equally be some sort of interactive checklist.
When you say "columns that could have held formulae [but don't]" my immediate suspicion is the admin team are using some sort of project management tool, that generates "trackers" with all the fields filled in, and emails them to you, then when you email back can parse the format and automatically update the project tracking. MsProject (server edition) does that, for example. It can communicate via spreadsheet attachment or via HTML form embedded in the email. It can even send you a timesheet form with the tasks you've been allocated this week; a field to enter the hours you spend on it each day; a checkbox to say it's finished or a %done.
I'm dubious anybody is either filling in the thing manually or calculating manually. (Or at least if they are, they're not using the tracking tool properly.)
How many of the other claims on this thread of manually keying/calculating spreadsheets are similarly misunderstood?
Use of the term "tracker" probably originated with some specific task-tracking application, but it had clearly grown over the years (decades?) as a misnomer for every spreadsheet, regardless of origin or purpose, to the point of even correcting communications from other departments that used the term spreadsheet. E.g., (made up, but accurate), "Please note that the document Finance described as the 'I100 budget spreadsheet' in the email from Tuesday the 23rd at 9.10AM is a tracker with the name I100Budget2018.xlsx which is attached to this email ..."
There was no evidence of using MS Project or any similar tool, and there were instances of knowledgeable users helpfully filling in formulae to simplify tasks and admin people helpfully (!?) removing them and replacing them with the calculated values because "Myrna [or whoever] doesn't like formulas."
Quote from dandl on August 10, 2019, 1:28 amQuote from johnwcowan on August 9, 2019, 2:23 pmQuote from Erwin on August 9, 2019, 6:54 amI'm having visions of an excel (/office calc) where only the file-open and file-save are different.
Sounds like a Good Thing, provided there are ways of importing and exporting local data stores too.
... I've been dreaming about this one for 40 years, spent time and money trying too. Show and tell time...
From this couple of threads I've finally twigged: this is the DBMS implementors' equivalent of update-through-view.
There's a swag of might-be-symptoms; not much in common between them; not much evidence they're symptoms of one underlying problem. There's a swag of might-be-solutions; not much in common between them; not much evidence they'll 'solve' more than one of the symptoms. Meanwhile ...
A lot of people are getting by fine. They're throwing data at grids. Some are quite nifty at adding formulas. Some won't touch formulas.
For those who don't like formulas, there's no evidence they're going to like database structure any more; especially not those pesky validation rules and referential integrity.That's a personnel/training problem. No amount of technology is going to 'solve' it.
For the brogrammers building stuff out of toothpicks, that's not because they've surveyed the market and found no tools. It's because their hobby is building stuff with toothpicks (perhaps the balsa cement gives them a high). For their managers, it's all such a small part of the budget/small business benefit that it flies under the radar. Not worth kicking off a project to aggregate all the business need and look for a toolset. Most of the cost is borne by the user departments tinkering with spreadsheets -- either in data prep or in prettying-up.
If you tot up all the tinkering and the brogrammers and the dysfunction of working round those who won't touch formulas, it still doesn't add up to a hill of beans. Not one problem but many. No single solution but many niches. Plenty of niche solutions have been offered, but there's not enough of a target market to get critical mass. (Financial markets excepted, where even pocket change is the GDP of a small country.)
Quote from AntC on August 10, 2019, 3:22 pmQuote from dandl on August 10, 2019, 1:28 amQuote from johnwcowan on August 9, 2019, 2:23 pmQuote from Erwin on August 9, 2019, 6:54 amI'm having visions of an excel (/office calc) where only the file-open and file-save are different.
Sounds like a Good Thing, provided there are ways of importing and exporting local data stores too.
... I've been dreaming about this one for 40 years, spent time and money trying too. Show and tell time...
From this couple of threads I've finally twigged: this is the DBMS implementors' equivalent of update-through-view.
There's a swag of might-be-symptoms; not much in common between them; not much evidence they're symptoms of one underlying problem. There's a swag of might-be-solutions; not much in common between them; not much evidence they'll 'solve' more than one of the symptoms. Meanwhile ...
A lot of people are getting by fine. They're throwing data at grids. Some are quite nifty at adding formulas. Some won't touch formulas.
For those who don't like formulas, there's no evidence they're going to like database structure any more; especially not those pesky validation rules and referential integrity.That's a personnel/training problem. No amount of technology is going to 'solve' it.
For the brogrammers building stuff out of toothpicks, that's not because they've surveyed the market and found no tools. It's because their hobby is building stuff with toothpicks (perhaps the balsa cement gives them a high). For their managers, it's all such a small part of the budget/small business benefit that it flies under the radar. Not worth kicking off a project to aggregate all the business need and look for a toolset. Most of the cost is borne by the user departments tinkering with spreadsheets -- either in data prep or in prettying-up.
If you tot up all the tinkering and the brogrammers and the dysfunction of working round those who won't touch formulas, it still doesn't add up to a hill of beans. Not one problem but many. No single solution but many niches. Plenty of niche solutions have been offered, but there's not enough of a target market to get critical mass. (Financial markets excepted, where even pocket change is the GDP of a small country.)
Perhaps. Though in my case the main target market is a population of one, me. If anyone else gains benefit from it, that's a bonus.
Quote from AntC on August 10, 2019, 3:22 pmQuote from dandl on August 10, 2019, 1:28 amQuote from johnwcowan on August 9, 2019, 2:23 pmQuote from Erwin on August 9, 2019, 6:54 amI'm having visions of an excel (/office calc) where only the file-open and file-save are different.
Sounds like a Good Thing, provided there are ways of importing and exporting local data stores too.
... I've been dreaming about this one for 40 years, spent time and money trying too. Show and tell time...
From this couple of threads I've finally twigged: this is the DBMS implementors' equivalent of update-through-view.
Not at all. That's a problem you only get to after you've already solved a dozen others. I'm interested in the problem of the blank canvas and the urge to do something with my data now.
There's a swag of might-be-symptoms; not much in common between them; not much evidence they're symptoms of one underlying problem. There's a swag of might-be-solutions; not much in common between them; not much evidence they'll 'solve' more than one of the symptoms. Meanwhile ...
A lot of people are getting by fine. They're throwing data at grids. Some are quite nifty at adding formulas. Some won't touch formulas.
Indeed they are, and they're using Excel to do it despite how poorly it matches their problem.
For those who don't like formulas, there's no evidence they're going to like database structure any more; especially not those pesky validation rules and referential integrity.That's a personnel/training problem. No amount of technology is going to 'solve' it.
For the brogrammers building stuff out of toothpicks, that's not because they've surveyed the market and found no tools. It's because their hobby is building stuff with toothpicks (perhaps the balsa cement gives them a high). For their managers, it's all such a small part of the budget/small business benefit that it flies under the radar. Not worth kicking off a project to aggregate all the business need and look for a toolset. Most of the cost is borne by the user departments tinkering with spreadsheets -- either in data prep or in prettying-up.
Please be very careful to distinguish between smart people finding ways to solve their own problems, and any kind of developer getting paid to solve other people's problems. Dave may veer towards the latter, but that's not the way most people use Excel and it's not my focus.
If you tot up all the tinkering and the brogrammers and the dysfunction of working round those who won't touch formulas, it still doesn't add up to a hill of beans. Not one problem but many. No single solution but many niches. Plenty of niche solutions have been offered, but there's not enough of a target market to get critical mass. (Financial markets excepted, where even pocket change is the GDP of a small country.)
I'm not in a hurry to make money out of anything. Been there, done that. Bennett's Law of Software Product Development says that, for every useful idea to ever turn into a profitable new business, you have to go through these stages:
- Write software, get it to work, etc, etc.
- Get noticed by a few people, and get some to use it (free). Work on software.
- Make a few sales (at a net loss). Work on software.
- Magic happens. Work on software.
- Make a lot of sales (at a net profit). Work on software.
I'll be perfectly happy to get to step 2.