Quote from Dave Voorhis on February 3, 2021, 9:59 am

Quote from dandl on February 3, 2021, 1:09 am

Compiler-comiler tools like yacc, lex and Pegasus are not programming languages. In each case they construct a data model that conforms to a well-specified formal structure (LR, regex, PEG, etc) and then generate code to compare incoming source code to that model. The code is highly repetitive and not suitable for editing. As well, they insert snippets of user-written code to add extra checks and/or do useful work. It is perfectly possible to write lexers and parsers by hand, but you get better results using these tools. I know, I've done it both ways.

SQL is not a programming language. It is arguably a specification for a data structure that will be interpreted by the RDBMS query optimiser and query executor. It has an embedded expression evaluator, but the rest is just data.

Xml, JSON and Yaml are not programming languages. Each is a language for specifying structured data.

Exactly the problem.

We've arbitrarily divided languages into "not programming language" / "data language" vs "programming language", when we should have listened to the LISP (and Forth, and later, Haskell and other) folks who went down this path before us and realised -- or knew to begin with -- that every non-programmable language is a lesser thing than an equivalent programming language. Thus, LISPers don't use (for example) a separate data or configuration language, they use LISP.

Not arbitrarily, very precisely. A Turing complete language can do anything, but it cannot be proved to complete or be correct, and if it expresses an algorithm it cannot (in general) be proven to do so, or be optimised to do it better. SQL exists because it cannot do everything, but it can be proved to complete, and it can be optimised. These are strong benefits you lose when you insist on introducing arbitrary code.

A data model language can be proved to produce precisely that data model and no other. And the Lisp guys were wrong. If the language chain of HTML/CSS/JS/JSON/XML/SQL teaches us anything, it's that one language does not rule them all.

We already have that "data model" language, which is indirectly why we're on this forum -- it's SQL.

Actually, precisely what the proliferation of HTML/CSS/JSON/XML compared to LISP tells us... Is all that could and should have been LISP and it would have been much better in every respect.

For one thing, we wouldn't have needed JavaScript to orchestrate it, given it would have been LISP.

SQL is the "other end" of the application chain, so a different model -- ideally, the relational model -- distinct from data presentation and data exchange. We could have done it in LISP, I suppose, and gained the benefits of orchestration/macros/homoiconicity/etc., but it is what it is.

That, of course, is true of HTML/CSS/JSON/XML too.

But SQL does give us a data model language. That's precisely what it is.

They extend the base language, rather than trying to generate it or replace it, which only creates a rigidity, inflexibility, and impedance mismatch between the host language and the sublanguage.

I have no problem with XML/YAML/JSON/whatever if I never have to see it. If it lives purely at the serialisation/deserialisation level, then that's fine -- I'm no more likely to encounter it than the raw TCP/IP packets travelling over my WiFi and Ethernet network.

The moment I'm expected to intelligently manipulate them, consider their structure -- either directly or indirectly -- give me a full-fledged programming language to do it.

There are few things worse to my mind than having to edit thousands of lines of data language -- knowing that it could be represented in a few lines of programming language -- because some other developer decided to make things "easier" for me.

I understand perfectly that you are 100% code-centric, so we are not talking about tools for you to use. This is about tools (like pgAdmin) that can be used by non-programmers to design, maintain and extend window-on-data applications.

Yes, that's pgAdmin, SQL Developer, TOAD, MS Access, and, hugely (yuck) Excel on the data side. It's KNIME, Tableau, Grafana, Qlikview and literally hundreds of 2nd-tier runners on the end-user side.

The proposition is that:

• There are benefits from using the least powerful available language for each identifiable situation (Pegasus rather than hand-coded PEG parser)
• 80% of all applications can be completely specified by a suitable constructed data model (not necessarily tables)
• The other 20% is easily handled by code
• We currently don't know any good way to put them together (but we know lots of bad ways).

As I've noted, these days I see a lot of:

1. A database developer sets up a database with a full set of constraints. If the requirements are minimal, the user is handed pgAdmin or SQLDeveloper, some training if they haven't used pgAdmin or SQLDeveloper before (though they usually have), maybe are given a bunch of pre-written *.sql scripts in a folder, and it's job done. There's your database, call us if you need anything.

The tools I'm proposing can do better than that.

1. As above, but with a thin user-friendly skin written in Python DJango (it auto-generates some simple but usable table editing stuff, mainly intended for admin use) or using (picking a typical example at random) Java Spring with Vaadin (if a bit more customisation is needed), or any of dozens of similar toolsets (including my own, if it's me.) That's a bit more work than handing over the database and pgAdmin, but it offers a slightly friendlier face and potentially more functionality.

Getting closer.

1. A thing that would have been a CRUD front-end a few years ago now has a front-end that is nothing like CRUD -- maybe it's a graph where you move the lines, maybe it's Google Maps where you stick pushpins, maybe it's a mobile app that tracks something you do -- and there just isn't a traditional table interface because in production it's all software talking to other software. Humans just monitor it with their Grafana dashboards and access the database via pgAdmin or SQLDeveloper if something goes wrong.

Not going there.

Data structuring languages are intrinsically easy to learn, because they do so little. If you already know the LR, SLR, LL, PEG, regex parser models, writing the grammar in one of those is easy.

They're easy to learn but hard to use, not because they're complicated, but because they're too simplistic. Inevitably, you want to (say) create a loop to generate these 100 nearly-identical-but-for-this records but... No loop.

So you write code in a more powerful programming language to generate code in the less powerful data language, which should tell you that the code you wrote to generate the code you need should have been all the code you need.

Only if you're code-centric. The better way (usually) is data-oriented tools: text editor, things like sed/grep/awk, table-editors. Improve the tool so people not as smart as you can understand and maintain the system, don't fail over to code.

Now I'm baffled.

Things like sed/grep/awk are programmer tools. Table editors like pgAdmin are shared by users and developers.

But please note: pgAdmin is absolutely not the tool for constructing an application data model. You need a new language because you need to write the model in text, supported by a brilliant dedicated editor. That way you can cut and paste, clone, search and replace just like you do for code. But it's not code, it's data, and the editor makes sure it's always valid.

What analyst jobs?

Those are mostly gone now. There are just developer jobs with different (or all -- i.e., "full stack") core skills. You write the model in SQL using SQL Developer or pgAdmin or whatever, the result is a database, hand over the credentials. Job done, and it's simpler than the Excel spreadsheet that did it before.

That is what's happening in the corporate world these days.

Could be, but that's not my proposition. There are non-programmers out there who can contribute to the design, maintenance and extension of application programs, This is about better tools for them.

My other half, Nikki, is one of those. She's not a programmer -- though occasionally pokes at code from the edges -- but a data specialist, and designs everything from databases to queries to whole reporting/analytical systems, in addition to doing project management / product owner stuff. Her world revolves around SQL admin tools and data visualisation tools (and Agile/Kanban boards.) There are a lot of such tools, and whilst they -- like all software -- are flawed in innumerable ways, I'm not quite seeing where your proposal fits among them.

In the typical corporate world, 80% is Excel. The remainder depends almost entirely on databases defined in SQL -- often by non-programmer data wranglers like Nikki -- and manually edited with pgAdmin or equivalent if manually edited in a CRUD sense at all (which is increasingly rare) and visualised/reported with a gaggle of data visualisation tools like KNIME, Tableau, Qlikview, you-name-it.

But as I mentioned, there's a constant move toward less manual data entry in the classic CRUD sense. Most of that has been -- or is being -- replaced with automated ingestion of data streams from this system to that system, and remaining bits of manual interaction are being mopped up by various "digital transformation" initiatives.

In practice, that means system X sends you a CSV file. You load it into Excel and edit Column P with your budget authorisations or whatever ("Documentation: Column P must contain 'approve' or 'disapprove' or the record will be rejected."), then emit it as an Excel file and upload it to system Y. Rinse and repeat.

It sounds like you're talking about CRUD automation, which would have been great in 1992 and probably still good in 2002.

It's a different (corporate, at least) world now.

So:

• What is the data model that completely specifies 80% of a NorthWinds/Chinook application? (*)
• What are the snippets needed for the other 20%?
• How can they be put together?

(*) It's likely that, as with SQL, the data model can be extended with expression evaluation, but that's phase II.

I think we've seen variations on that going back to the 1970's, and everything since has made stabs at the same idea, whether dBase x, PCFile, MS Access, IBM Query, spreadsheets (yes, they are), and every time -- aside from spreadsheets, from which I think we have a lot to learn a lot about agility and ease of use in an end-user data tool (and, yes, I'm still working on a spreadsheet/datasheet tool) -- we wind up either handing over the database (e.g., write some SQL and give 'em pgAdmin) or code is the right way to do it.

Actually, no. I've used those and many others, and they're a mish-mash of ideas. Back in the dBase days there were no languages of substance on PCs (that's why I created Powerflex), but now that's a solved problem. Data storage is a solved problem. There is almost nothing to learn from attempts going back more than about 20 years, because this is not the problem they (we) were trying to solve.

Sounds like you're trying to create something in between -- like dBase x, PCFile, MS Access, IBM Query, you-name-it -- and whilst I can't fault the desire ("this should be easy!") I think it suffers the same problems that propel developers to repeatedly attempt to create general-purpose no-code and low-code systems, which every time turn out to make the essential complexity of programming harder rather than easier.

So which of my 3 questions is hard to answer?

• A data model for 80% of the app?
• Code snippets for 20% in your favorite language?
• A way to put them together?

I guess I'm not sure what you're asking.

"A data model for 80% of the app" is a SQL schema. Since your idea is that it not be a developer tool, there aren't any code snippets aside from the SQL stored procedures you might have someone in IT help you write (if you can't.) They're all in the database along with the data, so the way to put them together is to access the database from pgAdmin or Python/Django, etc.

Or MS Access, which despite developer deprecation, was made for precisely this kind of thing and that's what it is still used for. A lot.

If you need more than that, you're almost certainly no longer dealing with an 80% data model / 20% snippets problem. More likely it's an 80% application / 20% data storage problem, so no longer end-user classic CRUD at all and thus squarely in the programmer / developer domain.

Quote from dandl on February 3, 2021, 9:59 am

Quote from tobega on February 3, 2021, 8:20 am

My intention is that the 80% part is executable in isolation, and can be readily ported across a wide range of technologies and platforms. The exact same data model will run on desktop or the Web, on Java or C# foundations, on any SQL or noSQL database. There is no defined host language.

The 20% snippet language either has to match the underlying technology or be transpiled. Look at Unity for an example of how brilliantly that works.

I think you have to explain more, because I'm confused. You're not making a tool that allows non-programmers to manage and display data, yet this tool shall manage and display data and be configured mainly by non-programmers?

The basic problem I propose to solve is the creation of applications that fall into the category of 'window-on-data' or 'filing cabinet'. The users sees it as a single application providing data-entry (CRUD), import/export, queries/reporting and similar features. There are tools to do this on single tables, but the target is data models of (say) 5-50 tables. Take Northwind or Chinook as examples.

The application provides data validation and ensures the database remains valid and consistent. There are no business rules, but it probably should provide navigation menus and basic user validation.

The core of the system is a data model (or meta-model) fully describing the application, which it is hoped will cover 80% of what the user asked for. It provides the ability to include code 'snippets' to handle the other 20%.

What does the tool do and for whom? For example, if I have a shop and want to sell stuff over the web, is this tool for me and what would I do?

A tool like this will maintain your catalog, but it is not an e-commerce system.

Bookkeeping system? Tax administration?

No, both of those require extensive business rules

I'm starting to think a bit of https://www.jetbrains.com/mps/ which apparently the Dutch Tax Administration has used to create a formalized language for tax regulation that generates the system out of rules that the domain experts can understand and write.

An excellent system for encoding business rules, but this is not one of those.

But I'm also thinking along the lines of https://www.palantir.com/

More business rules. Not going there. The natural language for writing business rules is a full feature programming language. There is justification for something different, but that's not what this is about. If you want business rules, you add them as snippets.

 

 

Quote from tobega on February 3, 2021, 12:11 pm

Quote from dandl on February 3, 2021, 9:59 am

Quote from tobega on February 3, 2021, 8:20 am

My intention is that the 80% part is executable in isolation, and can be readily ported across a wide range of technologies and platforms. The exact same data model will run on desktop or the Web, on Java or C# foundations, on any SQL or noSQL database. There is no defined host language.

The 20% snippet language either has to match the underlying technology or be transpiled. Look at Unity for an example of how brilliantly that works.

I think you have to explain more, because I'm confused. You're not making a tool that allows non-programmers to manage and display data, yet this tool shall manage and display data and be configured mainly by non-programmers?

The basic problem I propose to solve is the creation of applications that fall into the category of 'window-on-data' or 'filing cabinet'. The users sees it as a single application providing data-entry (CRUD), import/export, queries/reporting and similar features. There are tools to do this on single tables, but the target is data models of (say) 5-50 tables. Take Northwind or Chinook as examples.

The application provides data validation and ensures the database remains valid and consistent. There are no business rules, but it probably should provide navigation menus and basic user validation.

The core of the system is a data model (or meta-model) fully describing the application, which it is hoped will cover 80% of what the user asked for. It provides the ability to include code 'snippets' to handle the other 20%.

What does the tool do and for whom? For example, if I have a shop and want to sell stuff over the web, is this tool for me and what would I do?

A tool like this will maintain your catalog, but it is not an e-commerce system.

Bookkeeping system? Tax administration?

No, both of those require extensive business rules

I'm starting to think a bit of https://www.jetbrains.com/mps/ which apparently the Dutch Tax Administration has used to create a formalized language for tax regulation that generates the system out of rules that the domain experts can understand and write.

An excellent system for encoding business rules, but this is not one of those.

But I'm also thinking along the lines of https://www.palantir.com/

More business rules. Not going there. The natural language for writing business rules is a full feature programming language. There is justification for something different, but that's not what this is about. If you want business rules, you add them as snippets.

 

 

Thanks! I'm not sure it solves a problem that anyone feels they have, but I've never had any business sense so don't take my word for it.

When used as a catalog for an online shop, then I suppose we're talking about more than 20% other code to actually implement the shop? How would you get the data out to display on your web page and how would that be different or better than the SQL query (or PostgREST call) you could do today? What about when an online user places an order, we would have to update inventory?

Quote from Dave Voorhis on February 3, 2021, 12:44 pm

Quote from Dave Voorhis on February 3, 2021, 9:59 am

...

Or MS Access, which despite developer deprecation, was made for precisely this kind of thing and that's what it is still used for. A lot.

Or PowerApps, which sounds like it's a new flavour in that space.

Quote from dandl on February 3, 2021, 2:13 pm

But SQL does give us a data model language. That's precisely what it is.

No it isn't. But if you want to argue that we'll need a different thread.

I understand perfectly that you are 100% code-centric, so we are not talking about tools for you to use. This is about tools (like pgAdmin) that can be used by non-programmers to design, maintain and extend window-on-data applications.

Yes, that's pgAdmin, SQL Developer, TOAD, MS Access, and, hugely (yuck) Excel on the data side. It's KNIME, Tableau, Grafana, Qlikview and literally hundreds of 2nd-tier runners on the end-user side.

No, it really isn't. pgAdmin, Toad and similar can only edit a single table based on what's defined in the SQL. Eg: if a field is supposed to be a valid email, they can't check that.

Knime is totally process/visualisation oriented, no model at all. If you've got something that rises higher please name it and I'll gladly look into it.

Only if you're code-centric. The better way (usually) is data-oriented tools: text editor, things like sed/grep/awk, table-editors. Improve the tool so people not as smart as you can understand and maintain the system, don't fail over to code.

Now I'm baffled.

Things like sed/grep/awk are programmer tools. Table editors like pgAdmin are shared by users and developers.

They're command line tools which can be used with little or none of the skills needed by programmers. But I won't press the point.

Could be, but that's not my proposition. There are non-programmers out there who can contribute to the design, maintenance and extension of application programs, This is about better tools for them.

My other half, Nikki, is one of those. She's not a programmer -- though occasionally pokes at code from the edges -- but a data specialist, and designs everything from databases to queries to whole reporting/analytical systems, in addition to doing project management / product owner stuff. Her world revolves around SQL admin tools and data visualisation tools (and Agile/Kanban boards.) There are a lot of such tools, and whilst they -- like all software -- are flawed in innumerable ways, I'm not quite seeing where your proposal fits among them.

Than it probably doesn't. It's not my world, I wouldn't know. But data wranglers are certainly more likely to be my customer than programmers.

In the typical corporate world, 80% is Excel. The remainder depends almost entirely on databases defined in SQL -- often by non-programmer data wranglers like Nikki -- and manually edited with pgAdmin or equivalent if manually edited in a CRUD sense at all (which is increasingly rare) and visualised/reported with a gaggle of data visualisation tools like KNIME, Tableau, Qlikview, you-name-it.

In fact my interest in this whole topic springs from my total dissatisfaction with that world. Excel is awful at managing tables, but I use it almost every day because it kind of does the job. But what starts as a CSV file quickly grows into 3 or 4 tables, with internal consistency requirements, and Excel breaks every time. I never start with an existing SQL database, I have to create one and that's time-consuming and tedious. If I had this tool, I would use it all the time. If you can propose something better, please tell me about it. But don't make me write code just to wrangle some data.

But as I mentioned, there's a constant move toward less manual data entry in the classic CRUD sense. Most of that has been -- or is being -- replaced with automated ingestion of data streams from this system to that system, and remaining bits of manual interaction are being mopped up by various "digital transformation" initiatives.

In practice, that means system X sends you a CSV file. You load it into Excel and edit Column P with your budget authorisations or whatever ("Documentation: Column P must contain 'approve' or 'disapprove' or the record will be rejected."), then emit it as an Excel file and upload it to system Y. Rinse and repeat.

It sounds like you're talking about CRUD automation, which would have been great in 1992 and probably still good in 2002.

It's a different (corporate, at least) world now.

I don't even know what that is, but I don't think so. But you're right, that's not a world that interests me at all.

So which of my 3 questions is hard to answer?

• A data model for 80% of the app?
• Code snippets for 20% in your favorite language?
• A way to put them together?

I guess I'm not sure what you're asking.

"A data model for 80% of the app" is a SQL schema. Since your idea is that it not be a developer tool, there aren't any code snippets aside from the SQL stored procedures you might have someone in IT help you write (if you can't.) They're all in the database along with the data, so the way to put them together is to access the database from pgAdmin or Python/Django, etc.

Where in the SQL schema does it say this field is an email, that field is an image, this other one is a currency field with 4 decimal places in USD but that one is 0 decimal places in Rupiah? Where do you put the checks that this is so?

Or MS Access, which despite developer deprecation, was made for precisely this kind of thing and that's what it is still used for. A lot.

I'm sure Access can do it, if you like visual editing, and perhaps that's what one should use. It's been a while, I should check it out again. I seem to remember you don't get far without writing code.

If you need more than that, you're almost certainly no longer dealing with an 80% data model / 20% snippets problem. More likely it's an 80% application / 20% data storage problem, so no longer end-user classic CRUD at all and thus squarely in the programmer / developer domain.

 

Quote from Dave Voorhis on February 3, 2021, 3:24 pm

Quote from dandl on February 3, 2021, 2:13 pm

But SQL does give us a data model language. That's precisely what it is.

No it isn't. But if you want to argue that we'll need a different thread.

I guess your definition of "data model language" differs from mine. I don't know that it's worth a different thread.

I understand perfectly that you are 100% code-centric, so we are not talking about tools for you to use. This is about tools (like pgAdmin) that can be used by non-programmers to design, maintain and extend window-on-data applications.

Yes, that's pgAdmin, SQL Developer, TOAD, MS Access, and, hugely (yuck) Excel on the data side. It's KNIME, Tableau, Grafana, Qlikview and literally hundreds of 2nd-tier runners on the end-user side.

No, it really isn't. pgAdmin, Toad and similar can only edit a single table based on what's defined in the SQL. Eg: if a field is supposed to be a valid email, they can't check that.

They wouldn't check that. It would be a CHECK constraint on the email column in the database table.

Knime is totally process/visualisation oriented, no model at all. If you've got something that rises higher please name it and I'll gladly look into it.

Yes, sorry, I wasn't clear. I meant as output tools on the end-user side.

Only if you're code-centric. The better way (usually) is data-oriented tools: text editor, things like sed/grep/awk, table-editors. Improve the tool so people not as smart as you can understand and maintain the system, don't fail over to code.

Now I'm baffled.

Things like sed/grep/awk are programmer tools. Table editors like pgAdmin are shared by users and developers.

They're command line tools which can be used with little or none of the skills needed by programmers. But I won't press the point.

Could be, but that's not my proposition. There are non-programmers out there who can contribute to the design, maintenance and extension of application programs, This is about better tools for them.

My other half, Nikki, is one of those. She's not a programmer -- though occasionally pokes at code from the edges -- but a data specialist, and designs everything from databases to queries to whole reporting/analytical systems, in addition to doing project management / product owner stuff. Her world revolves around SQL admin tools and data visualisation tools (and Agile/Kanban boards.) There are a lot of such tools, and whilst they -- like all software -- are flawed in innumerable ways, I'm not quite seeing where your proposal fits among them.

Than it probably doesn't. It's not my world, I wouldn't know. But data wranglers are certainly more likely to be my customer than programmers.

In the typical corporate world, 80% is Excel. The remainder depends almost entirely on databases defined in SQL -- often by non-programmer data wranglers like Nikki -- and manually edited with pgAdmin or equivalent if manually edited in a CRUD sense at all (which is increasingly rare) and visualised/reported with a gaggle of data visualisation tools like KNIME, Tableau, Qlikview, you-name-it.

In fact my interest in this whole topic springs from my total dissatisfaction with that world. Excel is awful at managing tables, but I use it almost every day because it kind of does the job. But what starts as a CSV file quickly grows into 3 or 4 tables, with internal consistency requirements, and Excel breaks every time. I never start with an existing SQL database, I have to create one and that's time-consuming and tedious. If I had this tool, I would use it all the time. If you can propose something better, please tell me about it. But don't make me write code just to wrangle some data.

Yes, we've discussed that before, and I do appreciate the dissatisfaction. Indeed, I share it. I use Rel to do a lot of that day-to-day desktop data crunching and it works well for that, though it isn't as agile as I'd like. I'm working on the agility as I get time and inspiration. It is getting there, in its post-Rel, datasheet form. Slowly.

But it won't dispense with code, any more than (say) Excel dispenses with formulae -- which as an aside, brings to mind the number of times I've watched an Excel user do calculations on a pocket calculator and transcribe them manually into spreadsheet cells.

I intend it to facilitate the sort of data crunching I do, but rather than replace programming I want to make it more fluid for the data-ish things I do. Like my idea of the "SQL amplifier" is not to replace SQL but make it easier to integrate and diminish impedance mismatch, my datasheet isn't intended to replace Java, but amplify it -- make it easier to bring data in and out and diminish impedance mismatch -- for general data crunching.

If the end result is useful to anyone else, that's great, but mainly I want it to be useful to me.

But as I mentioned, there's a constant move toward less manual data entry in the classic CRUD sense. Most of that has been -- or is being -- replaced with automated ingestion of data streams from this system to that system, and remaining bits of manual interaction are being mopped up by various "digital transformation" initiatives.

In practice, that means system X sends you a CSV file. You load it into Excel and edit Column P with your budget authorisations or whatever ("Documentation: Column P must contain 'approve' or 'disapprove' or the record will be rejected."), then emit it as an Excel file and upload it to system Y. Rinse and repeat.

It sounds like you're talking about CRUD automation, which would have been great in 1992 and probably still good in 2002.

It's a different (corporate, at least) world now.

I don't even know what that is, but I don't think so. But you're right, that's not a world that interests me at all.

So which of my 3 questions is hard to answer?

• A data model for 80% of the app?
• Code snippets for 20% in your favorite language?
• A way to put them together?

I guess I'm not sure what you're asking.

"A data model for 80% of the app" is a SQL schema. Since your idea is that it not be a developer tool, there aren't any code snippets aside from the SQL stored procedures you might have someone in IT help you write (if you can't.) They're all in the database along with the data, so the way to put them together is to access the database from pgAdmin or Python/Django, etc.

Where in the SQL schema does it say this field is an email, that field is an image, this other one is a currency field with 4 decimal places in USD but that one is 0 decimal places in Rupiah? Where do you put the checks that this is so?

They're CHECK constraints invoking UDFs for field validation. I know some like field masks but I've always found them horrific, so I don't do them. I prefer to validate after entry, not during.

Or MS Access, which despite developer deprecation, was made for precisely this kind of thing and that's what it is still used for. A lot.

I'm sure Access can do it, if you like visual editing, and perhaps that's what one should use. It's been a while, I should check it out again. I seem to remember you don't get far without writing code.

If you like the Access Way (which is my term, not its term) then you can go a long way without code. Then it's a slick, fast, easy-to-use (if you accept the Access Way) automated front-end for enterprise databases.

If you don't like the Access Way, then it's an awkward IDE for VBA with a crappy database admin tool hung off the end, and that means it's code from the start, code in the middle, and code right to the end. Nasty code too, because it's VBA.

 

Quote from dandl on February 4, 2021, 5:37 am

Thanks! I'm not sure it solves a problem that anyone feels they have, but I've never had any business sense so don't take my word for it.

When used as a catalog for an online shop, then I suppose we're talking about more than 20% other code to actually implement the shop? How would you get the data out to display on your web page and how would that be different or better than the SQL query (or PostgREST call) you could do today? What about when an online user places an order, we would have to update inventory?

I wouldn't. A packaged e-commerce system would have its own data model. At this point we're into glue code and its own API requirements (and possibly its own CRUD system). Not my problem.

Quote from dandl on February 4, 2021, 6:08 am

Quote from Dave Voorhis on February 3, 2021, 3:24 pm

Quote from dandl on February 3, 2021, 2:13 pm

But SQL does give us a data model language. That's precisely what it is.

No it isn't. But if you want to argue that we'll need a different thread.

I guess your definition of "data model language" differs from mine. I don't know that it's worth a different thread.

I understand perfectly that you are 100% code-centric, so we are not talking about tools for you to use. This is about tools (like pgAdmin) that can be used by non-programmers to design, maintain and extend window-on-data applications.

Yes, that's pgAdmin, SQL Developer, TOAD, MS Access, and, hugely (yuck) Excel on the data side. It's KNIME, Tableau, Grafana, Qlikview and literally hundreds of 2nd-tier runners on the end-user side.

No, it really isn't. pgAdmin, Toad and similar can only edit a single table based on what's defined in the SQL. Eg: if a field is supposed to be a valid email, they can't check that.

They wouldn't check that. It would be a CHECK constraint on the email column in the database table.

Yes, SQL makes a token effort at validating user input, better than nothing. It's limited to what you can in SQL, it doesn't work until the record is actually submitted, and it doesn't provide useful help information.

I have a list of about 30 common user input types (you might well have even more) including field label, display format, validation, help text and so on. This forms part of the application data model, as distinct from SQL which only provides a storage data model.

In the typical corporate world, 80% is Excel. The remainder depends almost entirely on databases defined in SQL -- often by non-programmer data wranglers like Nikki -- and manually edited with pgAdmin or equivalent if manually edited in a CRUD sense at all (which is increasingly rare) and visualised/reported with a gaggle of data visualisation tools like KNIME, Tableau, Qlikview, you-name-it.

In fact my interest in this whole topic springs from my total dissatisfaction with that world. Excel is awful at managing tables, but I use it almost every day because it kind of does the job. But what starts as a CSV file quickly grows into 3 or 4 tables, with internal consistency requirements, and Excel breaks every time. I never start with an existing SQL database, I have to create one and that's time-consuming and tedious. If I had this tool, I would use it all the time. If you can propose something better, please tell me about it. But don't make me write code just to wrangle some data.

Yes, we've discussed that before, and I do appreciate the dissatisfaction. Indeed, I share it. I use Rel to do a lot of that day-to-day desktop data crunching and it works well for that, though it isn't as agile as I'd like. I'm working on the agility as I get time and inspiration. It is getting there, in its post-Rel, datasheet form. Slowly.

But it won't dispense with code, any more than (say) Excel dispenses with formulae -- which as an aside, brings to mind the number of times I've watched an Excel user do calculations on a pocket calculator and transcribe them manually into spreadsheet cells.

I intend it to facilitate the sort of data crunching I do, but rather than replace programming I want to make it more fluid for the data-ish things I do. Like my idea of the "SQL amplifier" is not to replace SQL but make it easier to integrate and diminish impedance mismatch, my datasheet isn't intended to replace Java, but amplify it -- make it easier to bring data in and out and diminish impedance mismatch -- for general data crunching.

If the end result is useful to anyone else, that's great, but mainly I want it to be useful to me.

Likewise. As an aside, it's dead easy to add an expression evaluator into these products without it becoming a programming tool, particularly if it's only the display side. Excel makes its own unique kind of trouble. And yes, I too have used a calculator rather than trying to find a safe place in someone else's spreadsheet.

So, do you know any tools that go anywhere near? I've not done a good survey, and many existing tools (eg Access) are rooted in a view of the problem that no longer applies. My son mentioned Django, but I've not looked into it. He's currently writing CRUD in JS and not enjoying it.

If I'm going to have another go at it, it won't be based on any Windows (or Java) UI toolkit I'm familiar with. Way too much baggage. I quite liked React, but not JS.

I see this as a new take on an old problem, that people think is either solved or insoluble. It would be nice to prove them wrong.

 

But as I mentioned, there's a constant move toward less manual data entry in the classic CRUD sense. Most of that has been -- or is being -- replaced with automated ingestion of data streams from this system to that system, and remaining bits of manual interaction are being mopped up by various "digital transformation" initiatives.

In practice, that means system X sends you a CSV file. You load it into Excel and edit Column P with your budget authorisations or whatever ("Documentation: Column P must contain 'approve' or 'disapprove' or the record will be rejected."), then emit it as an Excel file and upload it to system Y. Rinse and repeat.

It sounds like you're talking about CRUD automation, which would have been great in 1992 and probably still good in 2002.

It's a different (corporate, at least) world now.

I don't even know what that is, but I don't think so. But you're right, that's not a world that interests me at all.

So which of my 3 questions is hard to answer?

• A data model for 80% of the app?
• Code snippets for 20% in your favorite language?
• A way to put them together?

I guess I'm not sure what you're asking.

"A data model for 80% of the app" is a SQL schema. Since your idea is that it not be a developer tool, there aren't any code snippets aside from the SQL stored procedures you might have someone in IT help you write (if you can't.) They're all in the database along with the data, so the way to put them together is to access the database from pgAdmin or Python/Django, etc.

Where in the SQL schema does it say this field is an email, that field is an image, this other one is a currency field with 4 decimal places in USD but that one is 0 decimal places in Rupiah? Where do you put the checks that this is so?

They're CHECK constraints invoking UDFs for field validation. I know some like field masks but I've always found them horrific, so I don't do them. I prefer to validate after entry, not during.

Or MS Access, which despite developer deprecation, was made for precisely this kind of thing and that's what it is still used for. A lot.

I'm sure Access can do it, if you like visual editing, and perhaps that's what one should use. It's been a while, I should check it out again. I seem to remember you don't get far without writing code.

If you like the Access Way (which is my term, not its term) then you can go a long way without code. Then it's a slick, fast, easy-to-use (if you accept the Access Way) automated front-end for enterprise databases.

If you don't like the Access Way, then it's an awkward IDE for VBA with a crappy database admin tool hung off the end, and that means it's code from the start, code in the middle, and code right to the end. Nasty code too, because it's VBA.

 

 

Quote from Dave Voorhis on February 4, 2021, 9:42 am

Quote from dandl on February 4, 2021, 6:08 am

Quote from Dave Voorhis on February 3, 2021, 3:24 pm

Quote from dandl on February 3, 2021, 2:13 pm

But SQL does give us a data model language. That's precisely what it is.

No it isn't. But if you want to argue that we'll need a different thread.

I guess your definition of "data model language" differs from mine. I don't know that it's worth a different thread.

I understand perfectly that you are 100% code-centric, so we are not talking about tools for you to use. This is about tools (like pgAdmin) that can be used by non-programmers to design, maintain and extend window-on-data applications.

Yes, that's pgAdmin, SQL Developer, TOAD, MS Access, and, hugely (yuck) Excel on the data side. It's KNIME, Tableau, Grafana, Qlikview and literally hundreds of 2nd-tier runners on the end-user side.

No, it really isn't. pgAdmin, Toad and similar can only edit a single table based on what's defined in the SQL. Eg: if a field is supposed to be a valid email, they can't check that.

They wouldn't check that. It would be a CHECK constraint on the email column in the database table.

Yes, SQL makes a token effort at validating user input, better than nothing. It's limited to what you can in SQL, it doesn't work until the record is actually submitted, and it doesn't provide useful help information.

I find it helpful to choose good descriptive names for CHECK constraints, using the multi-column CHECK syntax that allows you to name the constraints.

Not invoking validation checks until the record is actually submitted isn't a problem, for me at least. I'd rather that than be hounded on a per-entry-field basis, which is just annoying.

Anyway, increasingly the data comes from importing a CSV (or whatever) file or is inserted by something else, rather than manually CRUDded, so this -- for me at least -- is a relatively minor issue.

I have a list of about 30 common user input types (you might well have even more) including field label, display format, validation, help text and so on. This forms part of the application data model, as distinct from SQL which only provides a storage data model.

I don't distinguish. There's just the stuff going into the database, which is either right (in it goes) or wrong (no go.)  I can appreciate having help text and whatnot for those things that require it, though.

In the typical corporate world, 80% is Excel. The remainder depends almost entirely on databases defined in SQL -- often by non-programmer data wranglers like Nikki -- and manually edited with pgAdmin or equivalent if manually edited in a CRUD sense at all (which is increasingly rare) and visualised/reported with a gaggle of data visualisation tools like KNIME, Tableau, Qlikview, you-name-it.

In fact my interest in this whole topic springs from my total dissatisfaction with that world. Excel is awful at managing tables, but I use it almost every day because it kind of does the job. But what starts as a CSV file quickly grows into 3 or 4 tables, with internal consistency requirements, and Excel breaks every time. I never start with an existing SQL database, I have to create one and that's time-consuming and tedious. If I had this tool, I would use it all the time. If you can propose something better, please tell me about it. But don't make me write code just to wrangle some data.

Yes, we've discussed that before, and I do appreciate the dissatisfaction. Indeed, I share it. I use Rel to do a lot of that day-to-day desktop data crunching and it works well for that, though it isn't as agile as I'd like. I'm working on the agility as I get time and inspiration. It is getting there, in its post-Rel, datasheet form. Slowly.

But it won't dispense with code, any more than (say) Excel dispenses with formulae -- which as an aside, brings to mind the number of times I've watched an Excel user do calculations on a pocket calculator and transcribe them manually into spreadsheet cells.

I intend it to facilitate the sort of data crunching I do, but rather than replace programming I want to make it more fluid for the data-ish things I do. Like my idea of the "SQL amplifier" is not to replace SQL but make it easier to integrate and diminish impedance mismatch, my datasheet isn't intended to replace Java, but amplify it -- make it easier to bring data in and out and diminish impedance mismatch -- for general data crunching.

If the end result is useful to anyone else, that's great, but mainly I want it to be useful to me.

Likewise. As an aside, it's dead easy to add an expression evaluator into these products without it becoming a programming tool, particularly if it's only the display side. Excel makes its own unique kind of trouble. And yes, I too have used a calculator rather than trying to find a safe place in someone else's spreadsheet.

So, do you know any tools that go anywhere near? I've not done a good survey, and many existing tools (eg Access) are rooted in a view of the problem that no longer applies. My son mentioned Django, but I've not looked into it. He's currently writing CRUD in JS and not enjoying it.

I think I mentioned DJango the other day. It has a nifty facility to auto-generate CRUD forms for all your tables, intended for back-end admin purposes. I've used it. I'd hardly describe it as wonderful -- it's not as full-featured as MS Access, or at least it wasn't last time I used it (well over a year ago) -- but it's certainly usable and a quick way to get an administrative Web front-end on a SQL back-end.