The Forum for Discussion about The Third Manifesto and Related Matters

Please or Register to create posts and topics.

NoSQL and SQL and 'relational' -- a mess of disinformation

 

  1. IBM System/36 Query.
  2. dBase III (and all its clones).
  3. Jim Button's "PC File", which was all the rage in the 1980's.
  4. Microsoft Access.

Though all exhibited the usual problem with such systems: they were great for simple tasks but hit a wall for more complex ones. Furthermore, most simple tasks have at least one more complex requirement that turns what should be a simple job into an aggravating one.

Though MS Access would let you break through the wall with its inbuilt Visual Basic for Applications (VBA), but it was no better than any other programming environment.

dBase III and friends would let you break through the wall with its inbuilt godawful whatever-it-was language, which was worse than every other programming language by a long shot.

FYI my product Powerflex is a 4GL most easily described as 'dBase for Windows and Unix'. We've been selling it since 1989, and we're still in business. The language is a mix of Cobol & Pascal with macros and some 4-GL features, and it's pretty capable. We have customers with large enterprise applications, millions of lines of source code. There is no 'wall'. But it won't do the things I listed above.

I disagree with your proposition. It's actually dead easy to add in a programming language to deal with the oddball stuff, the bit we don't know how to do is the stuff that should be easy. Instead of being driven by a declarative data model, we find developers use the programming language for everything. Hence the millions of lines of source code.

No, that isn't the answer.

Andl - A New Database Language - andl.org

It still doesn't look that hard, just quite a lot of work, so why is no-one trying this? There have been on-line offerings: one called Knack is quite close. But why don't we build apps this way? Really?

I think the short answer is because you always want a little more that is not covered by the framework.

I have built and sold applications built on products like this back in the 80s, I suppose it worked well enough for simple business needs.

No, that isn't it. If it were possible to get 90% of the basic CRUD stuff generated automatically, it's really easy to add in a programming language for the other 10%. Why can't we get to 90%?

In my current assignment we have at least two bespoke frameworks trying to do something like this and everybody hates them. The problem is that it becomes a whole new language to learn before it gets easy to work with and you tend to forget everything before the next time you need to change something. If a bug happens, finding and fixing it is extremely painful.

The typical problem with frameworks is the size of their surface area. Rails suffers from exactly that problem.

But do you actually get to 90% using on purely declarative terms? [What are they, by the way?]

I don't know if it's inevitable, but so far we always seem to end up with an EAV table, making it harder to verify the data outside the user interface.

You have my sympathy, but does it have to be that way?

 

Andl - A New Database Language - andl.org
Quote from dandl on January 30, 2021, 8:10 am

It still doesn't look that hard, just quite a lot of work, so why is no-one trying this? There have been on-line offerings: one called Knack is quite close. But why don't we build apps this way? Really?

I think the short answer is because you always want a little more that is not covered by the framework.

I have built and sold applications built on products like this back in the 80s, I suppose it worked well enough for simple business needs.

No, that isn't it. If it were possible to get 90% of the basic CRUD stuff generated automatically, it's really easy to add in a programming language for the other 10%. Why can't we get to 90%?

The 90% -- which is probably actually 80%, per the Pareto principle -- is easy in everything. It doesn't matter whether it's auto-generated or easy-to-write, it takes little time and effort.

It's the other 10% - 20% that takes all the time and effort, making whatever focus there is on trivial CRUD stuff -- whether generated automatically or not -- largely irrelevant.

In my current assignment we have at least two bespoke frameworks trying to do something like this and everybody hates them. The problem is that it becomes a whole new language to learn before it gets easy to work with and you tend to forget everything before the next time you need to change something. If a bug happens, finding and fixing it is extremely painful.

The typical problem with frameworks is the size of their surface area. Rails suffers from exactly that problem.

But do you actually get to 90% using on purely declarative terms? [What are they, by the way?]

I have seen some purely declarative systems, based on rather Datalog(-ish) cores, that are very impressive for being able to express quite large systems in a very small number of composable declarative terms. They're highly specialist, though, intended for niche application in a rarified specialist enterprise market that most of us never see or even know exists; where the number of competitors can be counted on one hand but the stakes are billions in corporate buy-out by... Each other, I guess.

In that weird, rarified arena, purely declarative systems seem to work.

But there the expectations are completely different. Nobody in that world makes a fuss about the search box being at the bottom of the form instead of the top, or the side, or drop-down instead of pop-up, or green instead of blue or whatever, or autocompletes this way instead of that way, because the value and functionality of the system has almost nothing to do with the search box.

If it even has a search box.

But in the mainstream application world, those picayune details are what everyone cares about. If your declarative, auto-generated whatnot doesn't meet the corporate standard look-and-feel-and-behaviour for whatever it is, you'd better go back and do it right this time. Etc.

Find-grained, handwritten code lets us do that. Autogenerated code can produce a starting point -- and only a starting point -- but so can copying over a snippet from your last project, or using a framework -- either off-the-shelf or bespoke (or both) -- that does it.

In practice, autogeneration isn't a big saving.

I'm the forum administrator and lead developer of Rel. Email me at dave@armchair.mb.ca with the Subject 'TTM Forum'. Download Rel from https://reldb.org

No, that isn't it. If it were possible to get 90% of the basic CRUD stuff generated automatically, it's really easy to add in a programming language for the other 10%. Why can't we get to 90%?

The 90% -- which is probably actually 80%, per the Pareto principle -- is easy in everything. It doesn't matter whether it's auto-generated or easy-to-write, it takes little time and effort.

It's the other 10% - 20% that takes all the time and effort, making whatever focus there is on trivial CRUD stuff -- whether generated automatically or not -- largely irrelevant.

So take a specific example: Chinook or NorthWind, they're both about a dozen tables. Assume we have a data dictionary of display field names and types for each, some really basic field validation, a list of data entry forms and a list of reports. How do you go about creating that, and how long does it take?

In my current assignment we have at least two bespoke frameworks trying to do something like this and everybody hates them. The problem is that it becomes a whole new language to learn before it gets easy to work with and you tend to forget everything before the next time you need to change something. If a bug happens, finding and fixing it is extremely painful.

The typical problem with frameworks is the size of their surface area. Rails suffers from exactly that problem.

But do you actually get to 90% using on purely declarative terms? [What are they, by the way?]

I have seen some purely declarative systems, based on rather Datalog(-ish) cores, that are very impressive for being able to express quite large systems in a very small number of composable declarative terms. They're highly specialist, though, intended for niche application in a rarified specialist enterprise market that most of us never see or even know exists; where the number of competitors can be counted on one hand but the stakes are billions in corporate buy-out by... Each other, I guess.

In that weird, rarified arena, purely declarative systems seem to work.

Probably the wrong term, I didn't mean a declarative language, I meant no code,  just data in tables. And nothing that pretends to be code either.

But there the expectations are completely different. Nobody in that world makes a fuss about the search box being at the bottom of the form instead of the top, or the side, or drop-down instead of pop-up, or green instead of blue or whatever, or autocompletes this way instead of that way, because the value and functionality of the system has almost nothing to do with the search box.

If it even has a search box.

But in the mainstream application world, those picayune details are what everyone cares about. If your declarative, auto-generated whatnot doesn't meet the corporate standard look-and-feel-and-behaviour for whatever it is, you'd better go back and do it right this time. Etc.

I'll give you that one: laying out a UI form is time-consuming at best, but why do we not (at least in principle) treat the picky layout as a separate styling exercise layered over the top of working code?

Find-grained, handwritten code lets us do that. Autogenerated code can produce a starting point -- and only a starting point -- but so can copying over a snippet from your last project. In practice, autogeneration isn't a big saving.

One thing I know: don't edit generated code. Without trying to specify an implementation, I had in mind 'generated' only in the sense of cross-compiling, never for editing. Generate the SQL, but don't edit it. WPF generates code behind the scenes, but not for editing.

 

Andl - A New Database Language - andl.org
Quote from dandl on January 30, 2021, 1:31 pm

No, that isn't it. If it were possible to get 90% of the basic CRUD stuff generated automatically, it's really easy to add in a programming language for the other 10%. Why can't we get to 90%?

The 90% -- which is probably actually 80%, per the Pareto principle -- is easy in everything. It doesn't matter whether it's auto-generated or easy-to-write, it takes little time and effort.

It's the other 10% - 20% that takes all the time and effort, making whatever focus there is on trivial CRUD stuff -- whether generated automatically or not -- largely irrelevant.

So take a specific example: Chinook or NorthWind, they're both about a dozen tables. Assume we have a data dictionary of display field names and types for each, some really basic field validation, a list of data entry forms and a list of reports. How do you go about creating that, and how long does it take?

A dozen tables is roughly an afternoon's work in pure code if you've got reasonable UI libraries.

The usual Java UI libraries -- swing or SWT -- make that easy, because they have layout managers that do all the pixel-nudging for you. You specify (via Java code) that you want this bunch of widgets in a grid inside another grid inside a set of columns or whatever, and it lays them out sensibly for you. You can specify explicit pixel positions of you're masochistic, but you try to avoid that.

I tend to write helpers and subclasses to embody typical look-and-feel, so if the desired new form layouts fit within those, it becomes no more than invoking a bunch of constructors with text prompts and defaults.

Though there's always bits where somebody wants something out-of-band, e.g., "Everything is fine as it is [i.e., simple and trivial to code], but when you click that radio button, we want this to move over there and that to disappear and this to appear and a thing to slide down from the top to fill in the calculation from that over here and make this move down and disable all these menu items and enable those menu items and allow the user to right-click to get a popup but only if the value in that field is filled in. There are some more rules but I'll have Sam in Accounting send you those on Tuesday morning so you'll have time to implement them all before we deploy Tuesday afternoon."

There's always one of those.

Reports, for me at least, tend to be emit-a-CSV-file or emit-an-Excel-spreadsheet rather than layout-based. I haven't had to do fiddly text-layout on reports in years, but I imagine it would still be tedious and slow for custom formatted layouts. For the usual columnar stuff, emit-a-CSV-file or emit-an-Excel-file seems to be fine, since reporting is increasingly a matter of generating data to be ingested by other systems -- and only casually eyeballed by humans in between systems, if at all -- rather than generating formatted text to be printed and read.

The tedious and slow bit tends to be the business logic behind the forms and reports, which is code-writing same as it ever was.

In my current assignment we have at least two bespoke frameworks trying to do something like this and everybody hates them. The problem is that it becomes a whole new language to learn before it gets easy to work with and you tend to forget everything before the next time you need to change something. If a bug happens, finding and fixing it is extremely painful.

The typical problem with frameworks is the size of their surface area. Rails suffers from exactly that problem.

But do you actually get to 90% using on purely declarative terms? [What are they, by the way?]

I have seen some purely declarative systems, based on rather Datalog(-ish) cores, that are very impressive for being able to express quite large systems in a very small number of composable declarative terms. They're highly specialist, though, intended for niche application in a rarified specialist enterprise market that most of us never see or even know exists; where the number of competitors can be counted on one hand but the stakes are billions in corporate buy-out by... Each other, I guess.

In that weird, rarified arena, purely declarative systems seem to work.

Probably the wrong term, I didn't mean a declarative language, I meant no code,  just data in tables. And nothing that pretends to be code either.

I'm not sure that saves much if anything. Is filling in a bunch of tables with text using pgadmin4 or whatever that much easier and faster than writing the same content via lean code, even in a relatively verbose language like C# or Java?

And what about when I have ten or twenty or fifty rows with almost the same thing but for some changing value. I would far rather implement that with .ForEach(...) or a loop than pound out ten/twenty/fifty almost-identical-except-for-some-changing-value rows in the database, ugh.

Though I could write a query to do it. Except... If it's ten/twenty/fifty rows and I need to change them after I've generated them, now I've got to delete them and regenerate them and... Oh, but they've been customised...

Ugh.

We're back to exactly where we should have started and stayed: writing code.

But there the expectations are completely different. Nobody in that world makes a fuss about the search box being at the bottom of the form instead of the top, or the side, or drop-down instead of pop-up, or green instead of blue or whatever, or autocompletes this way instead of that way, because the value and functionality of the system has almost nothing to do with the search box.

If it even has a search box.

But in the mainstream application world, those picayune details are what everyone cares about. If your declarative, auto-generated whatnot doesn't meet the corporate standard look-and-feel-and-behaviour for whatever it is, you'd better go back and do it right this time. Etc.

I'll give you that one: laying out a UI form is time-consuming at best, but why do we not (at least in principle) treat the picky layout as a separate styling exercise layered over the top of working code?

We kind of do. That's what style sheets specify, isn't it?

Find-grained, handwritten code lets us do that. Autogenerated code can produce a starting point -- and only a starting point -- but so can copying over a snippet from your last project. In practice, autogeneration isn't a big saving.

One thing I know: don't edit generated code. Without trying to specify an implementation, I had in mind 'generated' only in the sense of cross-compiling, never for editing. Generate the SQL, but don't edit it. WPF generates code behind the scenes, but not for editing.

I've yet to see much benefit in generating Java or C# over writing Java or C#, particularly when whatever meta-language is used to generate Java/C# is specifying the same thing we'd have specify anyway. In other words, it's syntactically different but not semantically distinct.

Likewise, I've yet to see much benefit in generating SQL from Java/C#/"ORM-"QL vs writing SQL in the first place...

...Except for SQL INSERT and UPDATE, which tends to be grindingly tedious and error-prone to manually write. Having that at least mostly generated for you is helpful.

 

I'm the forum administrator and lead developer of Rel. Email me at dave@armchair.mb.ca with the Subject 'TTM Forum'. Download Rel from https://reldb.org
Quote from Dave Voorhis on January 30, 2021, 2:46 pm

I've yet to see much benefit in generating Java or C# over writing Java or C#, particularly when whatever meta-language is used to generate Java/C# is specifying the same thing we'd have specify anyway. In other words, it's syntactically different but not semantically distinct.Likewise, I've yet to see much benefit in generating SQL from Java/C#/"ORM-"QL vs writing SQL in the first place...

...Except for SQL INSERT and UPDATE, which tends to be grindingly tedious and error-prone to manually write. Having that at least mostly generated for you is helpful.

Thinking on this, I think we've got it all almost completely backward.

So much of the industry is happy to hide and generate code for one end of the application chain -- database access -- but insists on (mostly) exposing and hand-coding the other end, HTML/CSS/JavaScript user interfaces.

It should be the other way around: Hand-coded database access is fine. Exposing SQL in a database access layer (but not outside of it) is good.

Hand-coded UIs built with HTML/CSS/JavaScript are bad. Hide it; wrap it in code generation. Make it go away.

I'm the forum administrator and lead developer of Rel. Email me at dave@armchair.mb.ca with the Subject 'TTM Forum'. Download Rel from https://reldb.org
Quote from Dave Voorhis on January 30, 2021, 2:46 pm
Quote from dandl on January 30, 2021, 1:31 pm

No, that isn't it. If it were possible to get 90% of the basic CRUD stuff generated automatically, it's really easy to add in a programming language for the other 10%. Why can't we get to 90%?

The 90% -- which is probably actually 80%, per the Pareto principle -- is easy in everything. It doesn't matter whether it's auto-generated or easy-to-write, it takes little time and effort.

It's the other 10% - 20% that takes all the time and effort, making whatever focus there is on trivial CRUD stuff -- whether generated automatically or not -- largely irrelevant.

So take a specific example: Chinook or NorthWind, they're both about a dozen tables. Assume we have a data dictionary of display field names and types for each, some really basic field validation, a list of data entry forms and a list of reports. How do you go about creating that, and how long does it take?

A dozen tables is roughly an afternoon's work in pure code if you've got reasonable UI libraries.

The usual Java UI libraries -- swing or SWT -- make that easy, because they have layout managers that do all the pixel-nudging for you. You specify (via Java code) that you want this bunch of widgets in a grid inside another grid inside a set of columns or whatever, and it lays them out sensibly for you. You can specify explicit pixel positions of you're masochistic, but you try to avoid that.

So take me through that.

  • What specs are you using (other than the SQL DDL)?
  • What libraries are those?
  • How many lines of code do you write in your 4 hours?

[And BTW: if it's just a series of API calls, why does it need the power of a GP language instead of just some tables to fill in?]

I tend to write helpers and subclasses to embody typical look-and-feel, so if the desired new form layouts fit within those, it becomes no more than invoking a bunch of constructors with text prompts and defaults.

Though there's always bits where somebody wants something out-of-band, e.g., "Everything is fine as it is [i.e., simple and trivial to code], but when you click that radio button, we want this to move over there and that to disappear and this to appear and a thing to slide down from the top to fill in the calculation from that over here and make this move down and disable all these menu items and enable those menu items and allow the user to right-click to get a popup but only if the value in that field is filled in. There are some more rules but I'll have Sam in Accounting send you those on Tuesday morning so you'll have time to implement them all before we deploy Tuesday afternoon."

There's always one of those.

Perhaps, but not this time. We've quoted $400 for this job (4 hours, $100/hr) and every variation is $10K, if they want to pay it. They won't.

Reports, for me at least, tend to be emit-a-CSV-file or emit-an-Excel-spreadsheet rather than layout-based. I haven't had to do fiddly text-layout on reports in years, but I imagine it would still be tedious and slow for custom formatted layouts. For the usual columnar stuff, emit-a-CSV-file or emit-an-Excel-file seems to be fine, since reporting is increasingly a matter of generating data to be ingested by other systems -- and only casually eyeballed by humans in between systems, if at all -- rather than generating formatted text to be printed and read.

Not this time. CSV is good, but it has to let you choose the fields, selection criteria and joins. Also sub-totaling, because Excel is really bad at that.

The tedious and slow bit tends to be the business logic behind the forms and reports, which is code-writing same as it ever was.

Not this time. Every quote starts at $10K, so they won't ask for many of those.

Probably the wrong term, I didn't mean a declarative language, I meant no code,  just data in tables. And nothing that pretends to be code either.

I'm not sure that saves much if anything. Is filling in a bunch of tables with text using pgadmin4 or whatever that much easier and faster than writing the same content via lean code, even in a relatively verbose language like C# or Java?

And what about when I have ten or twenty or fifty rows with almost the same thing but for some changing value. I would far rather implement that with .ForEach(...) or a loop than pound out ten/twenty/fifty almost-identical-except-for-some-changing-value rows in the database, ugh.

Though I could write a query to do it. Except... If it's ten/twenty/fifty rows and I need to change them after I've generated them, now I've got to delete them and regenerate them and... Oh, but they've been customised...

I don't really care what tool we use to generate the data, as long as we recognise it's data, not code. I'm putting forward the hypothesis that you can generate filing cabinet window-on-date apps from data without writing application code. If not, I would like to know what falsifies the hypothesis.

I'll give you that one: laying out a UI form is time-consuming at best, but why do we not (at least in principle) treat the picky layout as a separate styling exercise layered over the top of working code?

We kind of do. That's what style sheets specify, isn't it?

I write it with that in mind, and why not? Why not hive that off as a separate step? Why not quote $400 for the working app, and another $40K for making it look nice?

Find-grained, handwritten code lets us do that. Autogenerated code can produce a starting point -- and only a starting point -- but so can copying over a snippet from your last project. In practice, autogeneration isn't a big saving.

One thing I know: don't edit generated code. Without trying to specify an implementation, I had in mind 'generated' only in the sense of cross-compiling, never for editing. Generate the SQL, but don't edit it. WPF generates code behind the scenes, but not for editing.

I've yet to see much benefit in generating Java or C# over writing Java or C#, particularly when whatever meta-language is used to generate Java/C# is specifying the same thing we'd have specify anyway. In other words, it's syntactically different but not semantically distinct.

I have several specific examples where there is a well-specific higher-language. One that comes to mind is Pegasus, which implements a PEG parser. The generated parsers are big, and you don't want to touch them.

The advantage has to be in the ratio of what you write to what you generate. 12 tables might run to around 100 columns, which might be 10 entry screens, 100 display items, 10 reports and 100 report columns, etc. The total number of decisions might easily be 3-500 items, but what should that be 5000 lines of code? It looks like just data.

Likewise, I've yet to see much benefit in generating SQL from Java/C#/"ORM-"QL vs writing SQL in the first place...

...Except for SQL INSERT and UPDATE, which tends to be grindingly tedious and error-prone to manually write. Having that at least mostly generated for you is helpful.

If it can be generated from data, it should be.

 

 

Andl - A New Database Language - andl.org
Quote from dandl on January 31, 2021, 9:59 am
Quote from Dave Voorhis on January 30, 2021, 2:46 pm
Quote from dandl on January 30, 2021, 1:31 pm

No, that isn't it. If it were possible to get 90% of the basic CRUD stuff generated automatically, it's really easy to add in a programming language for the other 10%. Why can't we get to 90%?

The 90% -- which is probably actually 80%, per the Pareto principle -- is easy in everything. It doesn't matter whether it's auto-generated or easy-to-write, it takes little time and effort.

It's the other 10% - 20% that takes all the time and effort, making whatever focus there is on trivial CRUD stuff -- whether generated automatically or not -- largely irrelevant.

So take a specific example: Chinook or NorthWind, they're both about a dozen tables. Assume we have a data dictionary of display field names and types for each, some really basic field validation, a list of data entry forms and a list of reports. How do you go about creating that, and how long does it take?

A dozen tables is roughly an afternoon's work in pure code if you've got reasonable UI libraries.

The usual Java UI libraries -- swing or SWT -- make that easy, because they have layout managers that do all the pixel-nudging for you. You specify (via Java code) that you want this bunch of widgets in a grid inside another grid inside a set of columns or whatever, and it lays them out sensibly for you. You can specify explicit pixel positions of you're masochistic, but you try to avoid that.

So take me through that.

  • What specs are you using (other than the SQL DDL)?

It depends on the project. It might be nothing more than SQL DDL, or it might be my notes from a meeting with the client, or a mobile phone pic of the whiteboard from a meeting with a client, or the client's notes for me, or some pre-existing software, or some mock-up by a design person, or an old-skool pixel-by-pixel-layout sketch on graph paper from an old-skool designer, or I'm left to do whatever I think is appropriate, or you-name-it.

  • What libraries are those?

Most of the stuff I do these days (that has any front-end at all) is Java, using either Swing (https://en.wikipedia.org/wiki/Swing_(Java)) for pure desktop stuff or RAP/RWT (which is essentially SWT) for Web front-ends (https://www.eclipse.org/swt/ and https://wiki.eclipse.org/RAP/RWT) or cross-platform desktop.

It's that plus I have a gaggle of pre-written code -- libraries that get used, and previously-written code as starting points that gets copy-n-pasted from project to project -- for creation of (usually) database-driven forms and ancillary infrastructure. In its simplest form, it's driven by database metadata and I just instantiate a home-made TableEditor with database connection and table name parameters, and it gives the user a table grid with usual facilities.

Though when that sort of default edit-a-table functionality is sufficient, increasingly it's typical to just set up the database and hand it over to the user to edit via pgAdmin4 or Microsoft Access via linked tables, along with a bunch of predefined queries (as *.sql scripts) for producing reports.

There was a time when users wouldn't put up with that -- too hard, too technical -- but now average IT ability is far stronger, so being pointed at a database admin tool (or MS Access) is fine. For some, it's just like the other eight or so (or whatever) raw databases they're already editing to do their job, so no problem.

  • How many lines of code do you write in your 4 hours?

I haven't measured it that way. The trivial stuff I can hammer out at an alarming rate, for some undefined value of "alarming."

I haven't measured a lines-per-hour as such, because the easy stuff is negligible time and effort compared to the hard(er) stuff. It's the hard(er) specific, custom requirements that take time, effort, and attention.

But in terms of code volume, there's ideally one line per database table field unless I'm lucky enough to be able to use TableEditor (see above), then it's one line per table, plus whatever application scaffolding is needed. That can often be mostly copied from a previous project and modified. Realistically, it's one line per table for some tables, one line per database table field for other tables, and n lines for a bunch of customisations because requirements, plus fairly minimal scaffolding overhead.

The customisations take the bulk of time, effort, and lines.

[And BTW: if it's just a series of API calls, why does it need the power of a GP language instead of just some tables to fill in?]

Composability, modifiability, customisability, and the fact that it can all be automated as desired.

(That last bit needs emphasis: as desired is as I desire, not as some tool developer desires.)

I.e., the reasons we use code for anything.

Sometimes I can't simply use the database table metadata to auto-generate a form, but I can use a loop to emit a gaggle of field definitions from two or three lines of code. Sometimes I need to customise the form fields in unanticipated ways, like all the previous projects could use default system colours but this one needs colours specified and we'll never need that facility again. Or whatever.

If I'm relying on table-based definitions, I'm either forced to adhere to whatever the table-driven-code-generator facility does, or I have to modify the table-driven-code-generator facility every time I need to meet a new kind of requirement. In the long run, that turns into more work to maintain because I'm maintaining application code and libraries and a table-driven code generator, compared to defining interfaces with code (maintaining just libraries and application code.)

In the 1980's, I developed a tool that worked that way, essentially, though it used a meta-language instead of tables, but the idea was similar. It generated code.  We replaced it with a pure-target-code solution written in C++. The slight increase in verbosity (and, for lack of a better term, C++ishness) was offset by a significant increase in flexibility, programmability, extensibility, and customisability.

I tend to write helpers and subclasses to embody typical look-and-feel, so if the desired new form layouts fit within those, it becomes no more than invoking a bunch of constructors with text prompts and defaults.

Though there's always bits where somebody wants something out-of-band, e.g., "Everything is fine as it is [i.e., simple and trivial to code], but when you click that radio button, we want this to move over there and that to disappear and this to appear and a thing to slide down from the top to fill in the calculation from that over here and make this move down and disable all these menu items and enable those menu items and allow the user to right-click to get a popup but only if the value in that field is filled in. There are some more rules but I'll have Sam in Accounting send you those on Tuesday morning so you'll have time to implement them all before we deploy Tuesday afternoon."

There's always one of those.

Perhaps, but not this time. We've quoted $400 for this job (4 hours, $100/hr) and every variation is $10K, if they want to pay it. They won't.

Reports, for me at least, tend to be emit-a-CSV-file or emit-an-Excel-spreadsheet rather than layout-based. I haven't had to do fiddly text-layout on reports in years, but I imagine it would still be tedious and slow for custom formatted layouts. For the usual columnar stuff, emit-a-CSV-file or emit-an-Excel-file seems to be fine, since reporting is increasingly a matter of generating data to be ingested by other systems -- and only casually eyeballed by humans in between systems, if at all -- rather than generating formatted text to be printed and read.

Not this time. CSV is good, but it has to let you choose the fields, selection criteria and joins. Also sub-totaling, because Excel is really bad at that.

The old days of formatted-for-printing reports with detail lines interspersed with subtotal and total lines seem to be going away, even though at least the data is easy to generate -- just union the detail CSV records with the summary CSV records. But increasingly I'm asked for the detail and the totals to be separate -- usually ingested by distinct systems rather than read by humans -- which makes report development easy.

Easy enough to parametrise, too. It's just forms to get parameter values and parametric queries to generate them.

The tedious and slow bit tends to be the business logic behind the forms and reports, which is code-writing same as it ever was.

Not this time. Every quote starts at $10K, so they won't ask for many of those.

Probably the wrong term, I didn't mean a declarative language, I meant no code,  just data in tables. And nothing that pretends to be code either.

I'm not sure that saves much if anything. Is filling in a bunch of tables with text using pgadmin4 or whatever that much easier and faster than writing the same content via lean code, even in a relatively verbose language like C# or Java?

And what about when I have ten or twenty or fifty rows with almost the same thing but for some changing value. I would far rather implement that with .ForEach(...) or a loop than pound out ten/twenty/fifty almost-identical-except-for-some-changing-value rows in the database, ugh.

Though I could write a query to do it. Except... If it's ten/twenty/fifty rows and I need to change them after I've generated them, now I've got to delete them and regenerate them and... Oh, but they've been customised...

I don't really care what tool we use to generate the data, as long as we recognise it's data, not code. I'm putting forward the hypothesis that you can generate filing cabinet window-on-date apps from data without writing application code. If not, I would like to know what falsifies the hypothesis.

Customisation. There is always customisation, sometimes big, sometimes small. Otherwise, if they just need to edit/search/enter/update data in tables, throw pgAdmin4 or MS Access (perhaps with the Wizard-generated forms) at the client and let them edit the data.

It sounds like you're looking for old-skool CASE tools, which were (notoriously) table-driven and which still seem to be around. Even just yesterday, I got a pseudo-salespitch on Quora from https://parametricsystems.com.au, which makes something called ENIGMA that seems like a fill-in-the-tables CASE tool. It apparently generates code.

I'm the forum administrator and lead developer of Rel. Email me at dave@armchair.mb.ca with the Subject 'TTM Forum'. Download Rel from https://reldb.org
Quote from Dave Voorhis on January 31, 2021, 3:58 pm
Quote from dandl on January 31, 2021, 9:59 am
Quote from Dave Voorhis on January 30, 2021, 2:46 pm
Quote from dandl on January 30, 2021, 1:31 pm

No, that isn't it. If it were possible to get 90% of the basic CRUD stuff generated automatically, it's really easy to add in a programming language for the other 10%. Why can't we get to 90%?

The 90% -- which is probably actually 80%, per the Pareto principle -- is easy in everything. It doesn't matter whether it's auto-generated or easy-to-write, it takes little time and effort.

It's the other 10% - 20% that takes all the time and effort, making whatever focus there is on trivial CRUD stuff -- whether generated automatically or not -- largely irrelevant.

So take a specific example: Chinook or NorthWind, they're both about a dozen tables. Assume we have a data dictionary of display field names and types for each, some really basic field validation, a list of data entry forms and a list of reports. How do you go about creating that, and how long does it take?

A dozen tables is roughly an afternoon's work in pure code if you've got reasonable UI libraries.

The usual Java UI libraries -- swing or SWT -- make that easy, because they have layout managers that do all the pixel-nudging for you. You specify (via Java code) that you want this bunch of widgets in a grid inside another grid inside a set of columns or whatever, and it lays them out sensibly for you. You can specify explicit pixel positions of you're masochistic, but you try to avoid that.

So take me through that.

  • What specs are you using (other than the SQL DDL)?

It depends on the project. It might be nothing more than SQL DDL, or it might be my notes from a meeting with the client, or a mobile phone pic of the whiteboard from a meeting with a client, or the client's notes for me, or some pre-existing software, or some mock-up by a design person, or an old-skool pixel-by-pixel-layout sketch on graph paper from an old-skool designer, or I'm left to do whatever I think is appropriate, or you-name-it.

But somewhere you will need field specific info such as friendly display name, display formatting, simple validation?

  • What libraries are those?

Most of the stuff I do these days (that has any front-end at all) is Java, using either Swing (https://en.wikipedia.org/wiki/Swing_(Java)) for pure desktop stuff or RAP/RWT (which is essentially SWT) for Web front-ends (https://www.eclipse.org/swt/ and https://wiki.eclipse.org/RAP/RWT) or cross-platform desktop.

I've used Swing and SWT (Knime uses both!), and they are verbose.  So you have pre-written templates, and some kind of meta data at the field level?

It's that plus I have a gaggle of pre-written code -- libraries that get used, and previously-written code as starting points that gets copy-n-pasted from project to project -- for creation of (usually) database-driven forms and ancillary infrastructure. In its simplest form, it's driven by database metadata and I just instantiate a home-made TableEditor with database connection and table name parameters, and it gives the user a table grid with usual facilities.

But isn't an automation of that process exactly what I've been proposing? Templates, metadata, generated code?

Though when that sort of default edit-a-table functionality is sufficient, increasingly it's typical to just set up the database and hand it over to the user to edit via pgAdmin4 or Microsoft Access via linked tables, along with a bunch of predefined queries (as *.sql scripts) for producing reports.

There was a time when users wouldn't put up with that -- too hard, too technical -- but now average IT ability is far stronger, so being pointed at a database admin tool (or MS Access) is fine. For some, it's just like the other eight or so (or whatever) raw databases they're already editing to do their job, so no problem.

Ugh! Yes, I'm assuming a user who will adapt to the software and not write big cheques for fancy customisation. But no, I'm assuming existing CRUD editors are not enough, we want the next level up. But we don't want to use a visual editor like Access, and we want to target other runtime environments.

  • How many lines of code do you write in your 4 hours?

I haven't measured it that way. The trivial stuff I can hammer out at an alarming rate, for some undefined value of "alarming."

I haven't measured a lines-per-hour as such, because the easy stuff is negligible time and effort compared to the hard(er) stuff. It's the hard(er) specific, custom requirements that take time, effort, and attention.

But in terms of code volume, there's ideally one line per database table field unless I'm lucky enough to be able to use TableEditor (see above), then it's one line per table, plus whatever application scaffolding is needed. That can often be mostly copied from a previous project and modified. Realistically, it's one line per table for some tables, one line per database table field for other tables, and n lines for a bunch of customisations because requirements, plus fairly minimal scaffolding overhead.

So you like to do it with code, but that's personal preference. There is no reason you couldn't be writing one line of metadata per field, table, report, etc, is there? Or is there?

The customisations take the bulk of time, effort, and lines.

[And BTW: if it's just a series of API calls, why does it need the power of a GP language instead of just some tables to fill in?]

Composability, modifiability, customisability, and the fact that it can all be automated as desired.

(That last bit needs emphasis: as desired is as I desire, not as some tool developer desires.)

I.e., the reasons we use code for anything.

Sometimes I can't simply use the database table metadata to auto-generate a form, but I can use a loop to emit a gaggle of field definitions from two or three lines of code. Sometimes I need to customise the form fields in unanticipated ways, like all the previous projects could use default system colours but this one needs colours specified and we'll never need that facility again. Or whatever.

If I'm relying on table-based definitions, I'm either forced to adhere to whatever the table-driven-code-generator facility does, or I have to modify the table-driven-code-generator facility every time I need to meet a new kind of requirement. In the long run, that turns into more work to maintain because I'm maintaining application code and libraries and a table-driven code generator, compared to defining interfaces with code (maintaining just libraries and application code.)

In the 1980's, I developed a tool that worked that way, essentially, though it used a meta-language instead of tables, but the idea was similar. It generated code.  We replaced it with a pure-target-code solution written in C++. The slight increase in verbosity (and, for lack of a better term, C++ishness) was offset by a significant increase in flexibility, programmability, extensibility, and customisability.

So that's the part I'm trying to get a handle on. Yes, I know very well how hard it is to create and maintain tools, that's what I do.

I tend to write helpers and subclasses to embody typical look-and-feel, so if the desired new form layouts fit within those, it becomes no more than invoking a bunch of constructors with text prompts and defaults.

Though there's always bits where somebody wants something out-of-band, e.g., "Everything is fine as it is [i.e., simple and trivial to code], but when you click that radio button, we want this to move over there and that to disappear and this to appear and a thing to slide down from the top to fill in the calculation from that over here and make this move down and disable all these menu items and enable those menu items and allow the user to right-click to get a popup but only if the value in that field is filled in. There are some more rules but I'll have Sam in Accounting send you those on Tuesday morning so you'll have time to implement them all before we deploy Tuesday afternoon."

There's always one of those.

Perhaps, but not this time. We've quoted $400 for this job (4 hours, $100/hr) and every variation is $10K, if they want to pay it. They won't.

Reports, for me at least, tend to be emit-a-CSV-file or emit-an-Excel-spreadsheet rather than layout-based. I haven't had to do fiddly text-layout on reports in years, but I imagine it would still be tedious and slow for custom formatted layouts. For the usual columnar stuff, emit-a-CSV-file or emit-an-Excel-file seems to be fine, since reporting is increasingly a matter of generating data to be ingested by other systems -- and only casually eyeballed by humans in between systems, if at all -- rather than generating formatted text to be printed and read.

Not this time. CSV is good, but it has to let you choose the fields, selection criteria and joins. Also sub-totaling, because Excel is really bad at that.

The old days of formatted-for-printing reports with detail lines interspersed with subtotal and total lines seem to be going away, even though at least the data is easy to generate -- just union the detail CSV records with the summary CSV records. But increasingly I'm asked for the detail and the totals to be separate -- usually ingested by distinct systems rather than read by humans -- which makes report development easy.

Easy enough to parametrise, too. It's just forms to get parameter values and parametric queries to generate them.

The tedious and slow bit tends to be the business logic behind the forms and reports, which is code-writing same as it ever was.

Not this time. Every quote starts at $10K, so they won't ask for many of those.

Probably the wrong term, I didn't mean a declarative language, I meant no code,  just data in tables. And nothing that pretends to be code either.

I'm not sure that saves much if anything. Is filling in a bunch of tables with text using pgadmin4 or whatever that much easier and faster than writing the same content via lean code, even in a relatively verbose language like C# or Java?

And what about when I have ten or twenty or fifty rows with almost the same thing but for some changing value. I would far rather implement that with .ForEach(...) or a loop than pound out ten/twenty/fifty almost-identical-except-for-some-changing-value rows in the database, ugh.

Though I could write a query to do it. Except... If it's ten/twenty/fifty rows and I need to change them after I've generated them, now I've got to delete them and regenerate them and... Oh, but they've been customised...

I don't really care what tool we use to generate the data, as long as we recognise it's data, not code. I'm putting forward the hypothesis that you can generate filing cabinet window-on-date apps from data without writing application code. If not, I would like to know what falsifies the hypothesis.

Customisation. There is always customisation, sometimes big, sometimes small. Otherwise, if they just need to edit/search/enter/update data in tables, throw pgAdmin4 or MS Access (perhaps with the Wizard-generated forms) at the client and let them edit the data.

It sounds like you're looking for old-skool CASE tools, which were (notoriously) table-driven and which still seem to be around. Even just yesterday, I got a pseudo-salespitch on Quora from https://parametricsystems.com.au, which makes something called ENIGMA that seems like a fill-in-the-tables CASE tool. It apparently generates code.

So really this is the nub of the problem. We can all see the template/metadata/generation part of the solution solving 80% of the problem. What we can't see is a way to leverage that but leave room for the bits that don't quite fit.

Let's say an application that genuinely solves a reasonable customer problem is complex. Within the essential complexity of the solution there is routinely:

  • 80% of regular complexity
  • 20% of irregular complexity.

The regular complexity is seductively easy, because we can easily capture the regularities in tables, metadata, templates, code generation and so on. We think we're nearly done. We're not.

The irregular complexity is intractably hard. We can:

  1. Add features to our 'regularity' solution. That's hard work, and at the end of the day it comes to resemble code. Then it's just another language and most likely it won't get used.
  2. Find ways to plug in bits of custom code. Learning about all those special plug in places is hard, and mostly likely too hard.
  3. Go back to code first, but retain bits of the 'regularity' solution to reduce code volume. This is state of the art, this is you.

So the heart of the problem as I first stated it is that we just don't know how to integrate a regularity-based system to handle regular complexity with a code-based system to handle irregular complexity, other than method (c). And even if we know parts of the answer, and try to move up a level, it rapidly gets so (accidentally) complex we give up and go back to method (c).

Andl - A New Database Language - andl.org

I think if you could just specify a database table and have everything generated from that, fine

As soon as you have to add any other data to control how things are generated, you have essentially created a programming language. One that I'm not good at and only use a few days a year at best, and it generally isn't a very capable language either.

But if everything just follows from the table definition, why not just hand the users SQLdeveloper or some such tool to just look directly at the data? Even let them write a little SQL.