The future of queries against non-first-normal-form data

[Dawn Wolthuis]

There are multiple Non-first-normal form (NF2) query languages now. Two of interest to me are GraphQL (for CRUD) and Gremlin (BI). I'll flip to BI again at some point, I'm guessing, but right now I'm really interested in GraphQL. What I think GraphQL does is what both Chandru (here) and Eggers (texts) have recently mentioned as something they would have liked to have completed -- it includes writes (mutations), not just reads (queries). You can do the full range of CRUD with GraphQL (on the front-end/middle-tier, with, e.g. api's/connectors to MV DBMS in the middle/back).

It might not make any sense to write an MV Query statement and have it translate to a GraphQL query, but I'd like to do that anyway...someday in real, production code. Ridiculous, right? I can code JSON GraphQL statements without having this additional translation, of course, but I just don't find them comparatively elegant. When we are talking GraphQL Mutations, however, how lovely is that! 

I would like to see GraphQL become the "what's next?" for SQL and also for the MV Query language wrt CRUD. I (and many others) are the end-users of tools/libraries written to make app development easier. What tools might be useful toward that end? Does anyone else see possibilities wrt MV and GraphQL?

--Dawn

Dawn M. Wolthuis

President, Tincat Group

Take and give some delight today

[Tony Gravagno]

Dawn, you'll recall that about two years ago I started writing a product that facilitates bi-directional exchanges between MV and RDBMS platforms. That product, "PREMEUS" v1, is complete and I've been dilly-dallying with documentation, enhancements, website, etc. In short, from TCL and BASIC we can now create and delete remote databases and tables, define remote table schema using MV dict items, and of course perform CRUD against a local or remote RDBMS. The roadmap includes support for multiple MVDBMS and RDBMS over multiple OS. While not intended for MV/MV or MV/NoSQL, these options may fit in the roadmap. The primary goal of PREMEUS (Pick Relational Exchanges Made Easy Using SQL) is to make it easy to export and maintain data in a RDBMS so that any reporting/BI tool of choice can be used against MV data. As a by-product of building this, it also supports pulling data from a RDBMS for integration into an MV application.

I have not considered in any way support for GraphQL but that is one of those techs that's been around long enough that it should be on our radar.

The same with ODATA and JSON, which are both supported by another fine product, phiReport (http://www.3phi.solutions/phireport.html). In this case, I need to ask about the precise intent of this thread. Do you really mean "queries against non-first-normal-form data"? Or can we include in that any mechanism which either exports data for a NFNF server to consume? The difference is : Do we really need, want, or care about a direct interface between MV and these other environments? Or are we really just concerned with what we do with those other environments for reporting and BI? If the latter, then direct queries are a joyful luxury but not a business requirement. I believe and suggest that in this industry that site admins focus more on the business challenges that are presented to them by management, so How we get "there" is nowhere near as important as the fact that we simply do get there. PREMEUS and phiReport are focused simply on getting there, though PREMEUS is bi-directional with SQL from TCL and BASIC, and phiReport is export-only from TCL and BASIC. These products support different features and go about how they do things differently, but they have a common purpose, to ensure that Pick remains valued in a multi-platform installation.

Thoughts?

Best,

T

[Dawn Wolthuis]

While old technologies often take a long time to die out, we are at the start of the return to NF2 data modeling. Microsoft database experts do their lab work in MongoDB on their machines, with CosmosDB in the cloud. Microsoft can't say it too loudly, but the don't think the future of DBMS technology has much, if anything, to do with SQL other than for support (and milking the installed base for decades to come, of course).  Similarly for Google and AWS software developers. So, I'm curious what languages and libraries they are using for CRUD. For a while, it seemed that everyone was doing their own thing, but I'm starting to think that GraphQL is coming on stronger, especially now with a standards body associated with it.

For some people, a move to a SQL DBMS meets the business needs they have, particularly if migrating to some relatively new application software package running on SQL Server, MySQL, PostgreSQL or even Oracle. But if writing new software applications, especially for those software companies that already have experience with MV, going the NoSQL route will make a lot of sense especially if/when they have some of the same features that SQL gave the industry: standards for BI tools, standard means of doing CRUD, ability to write somewhat database-independent(ish) applications, at least in theory.

My vote right now is for GraphQL to be the successor to the MV Query language (and then some, as it has the coveted "update" capability) for industry standard CRUD. The purpose of using GraphQL in a new application would be to model data in non-first-normal-form and with boolean logic (not the 3-valued logic of SQL). "Screens" (Pages/Views) and "Reports" often have NF2 modeling requirements, so now the persistence model can align better, as is often the case in MV applications.

End-to-end NF2 data modeling for the next generation of business applications that will include everything from video, graphical, audio media to AI to device integration to many shapes of BI and likely also to good old fashioned high volume human input. We can start talking about "legacy SQL applications" once we have CRUD and BI handled nicely for NoSQL applications.

I'm happy to hear other thoughts on this. Of course there will be business requirements to interface with SQL DBMS products, just as the relational and Pick folks had to communicate. This time around, it will be those saddled with first normal form that can do the hand stands, perhaps, while everyone else moves nested JSON documents around handily. This isn't a no-brainer for application development at this point. If you ditch SQL, what do you put in its place for data security, for example? Plenty to think about, but I think GraphQL is worth following, at least, and maybe doing some strategic thinking.  --Dawn

Dawn M. Wolthuis

President, Tincat Group

Take and give some delight today

--
You received this message because you are subscribed to
the "Pick and MultiValue Databases" group.
To post, email to: mvd...@googlegroups.com
To unsubscribe, email to: mvdbms+un...@googlegroups.com
For more options, visit http://groups.google.com/group/mvdbms
---
You received this message because you are subscribed to the Google Groups "Pick and MultiValue Databases" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mvdbms+un...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/mvdbms/f1bcbab4-edd0-45e6-ac68-5e855cc15fb3%40googlegroups.com.

[Tony Gravagno]

I have to apologize. I wasn't fully on to your theme. I think I get it now, but to clarify...

Were you suggesting GraphQL in MV for accessing external, non-MV resources? This is why I mentioned products rather than built-in solutions.

Or are you suggesting that MV would do well to adopt GraphQL for its own purposes, so that anyone who knows the QL can thus interact with MV using a familiar tool?

The first option is much easier than the second:

With PREMEUS for example, I'm accepting a SQL query at TCL, or building one based on simplified syntax, and passing it to the RDBMS, returning the results, parsing for data and errors, and then rendering or saving the results. Included in there is the concept that one can reference a local ORDERS file to query a remote Orders table, the RDBMS database info is stored "in state" so that a SQL query to the remote environment has the appearance of an AQL query in a local account, and there are a number of other conveniences. But the fundamental premise there is that we're not using SQL to access the local MV, but facilitating CRUD on a remote environment. Another example there would be a command like UPDATE ORDERS '123' which takes schema-defined fields from the local ORDERS file, and sends them to the RDBMS, first with an Update query, but if the record doesn't exist it ends an Insert query. It then also sends ORDER_DETAIL records, for example, to the corresponding OrdersDetail table, and breaks up MV data in the local ORDERS header file into associated tables like OrdersShipToInfo or OrdersComments. All transactions are bracketed in case something goes wrong.

Please forgive what's a blatent AD there but my intent is sincerely to clarify deeper integration between environments than merely sending a verbatim SQL command to a remote server and returning the verbatim results. I created PREMEUS because I think we need it.

Now, can we do the same for GraphQL? Yes and no. I would not write a GraphQL engine in BASIC any more than I would write a SQL engine. It would be a waste of time because it's already done outside the MV box. Such an effort would never be Done because it would always be in a catch-up mode with core GraphQL syntax. However, I/we can write a wrapper that accepts GraphQL queries, from the command line or constructed in BASIC, pass them to a remote interpreter which executes them against a remote NoSQLDB, and return and parse the JSON results. I wouldn't even parse the JSON in MV for the same reason - other great tools exist outside the box for the purpose, which can return a flat representation of data which is much more easily processed in BASIC where we are limited to AM/VM/SM trees.

[  Pick NoSQL Exchanges Made Easy Using GraphQL = PNSEMEUGQL = really bad product name :)  ]

If we try to get a MVDBMS provider to do that work, they will come up with their own paradigms, every vendor who does this will do it differently, and we in this industry will once again be stuck with application code that's different across platforms. Competition and differentiation are good. But as we see with iOS vs Android, IE vs Firefox vs Chrome, and so many other examples over the last 30 years, when companies brand what should be standard paradigms, a lot of resentment results, there is a huge expense at the developer tier, and we eventually see cross-platform options arise to lower costs and expand the audience for offerings. My preference is to start with third-party, cross-platform solutions, and get DBMS companies to focus on their DBMS, rather than asking them to do what can be done outside the box.

That leads to GraphQL used against MV:

At the most fundamental level, GraphQL essentially uses a JSON string of arguments as a query to return a JSON string of results. The datastore on which it operates is largely irrelevant. We could use a couple approaches to access MV data with GraphQL. One would be to process a query outside of the box, pass tokens into MV with a more digestable syntax, and return JSON. Another approach would be to parse and process the entire JSON query in MV itself. Either of those approaches can be tackled in BASIC where we're already intimate with string manipulation. We don't need the MVDBMS providers to do this, and as above I would argue that we don't want them to do it either. As with SQL, I would suggest that we would not want to write a full GraphQL engine inside MV, simply because that already exists outside the box.

A problem with this is that GraphQL is much more than just a query language, which is why it's gaining popularity. It's more related to Stored Procedures than simple queries, more like a full programming language. This is why I suggest tokenizing data requests rather than trying to process full queries. I see MV in this respect, and most, as a database, for input, storage, and output of data. If we just do that then we don't need to worry about the full capabilities of new languages and tools, we just need to hook into the request and response components of those language. In other words, where GraphQL requires data, let's give it data, but for all other things that it does, let an existing GraphQL engine process it.

If we follow these practices of getting MV to be an excellent OLTP platform, readily accepting atomic requests and returning raw data, then we will be able to handle every new challenge, whether for webservices, query languages, OS integration, or anything else that comes up. If we try to build everything into MV, we're always going to be chasing the curve, getting it wrong, getting frustrated, incurring overhead costs of time and money.

Or in summary, I think the future of NF2 with MV is the exact same as the future of any interface with MV. What's on the outside will always be irrelevant if we focus on making this one component as versatile as possible.

Um, so is any of that relevant to your OP? LOL

T

[Dawn Wolthuis]

I always appreciate the free-flowing ideas, Tony. I will admit up front that I am not ready to enumerate a full stack that includes GraphQL and really sings with MV, but you are correct that we might not need the DBMS vendors to do anything to make something sweet, especially if they have or provide in the future excellent connectivity, e.g. for APIs).

I’m fiddling with Vue (not much to date, but planning to bump it up soon). The front end is where it’s happening, even if the need for UI aesthetics can drive old-style developers nuts. 

Issue CRUD statements from the front-end dev that execute in a middle GraphQL tier and you can put any API behind them. The security needs to be in order (overly ignorant here as I’m not even up to the point of writing demo-ware), but then having a set of APIs that correspond with FILES and DICT items (automates?), adding to those with more specific APIs as needed could allow GraphQL CRUD on the front end with API’s for MV CRUD to be just as good as using MongoDB or Cosmos DB, for example. 

In non-MV cases, products like MongoDB can accommodate what I would call more direct CRUD, sans APIs, I think, maybe. I haven’t a clue what I’m saying in that regard, however, nor whether an MV vendor could make it “act like” whatever a MongoDB / Cosmos DB connector between the GraphQL server and the DBMS might do.

I’m just thinking out loud here. React, Angular, Vue, and/or anything that comes next on the front-end can be written without any SQL and without any skewed SQL-thinking (1NF) in the mix for the app developers. Many of us here know that applications built with NF2 data models tend to be more feature-rich and intuitive for users. We are positioned to help the next generation of NF2 business application developers. Of course, it is something like the old cartoon of “I must figure out where they are going because I’m their leader.” So, maybe the best we can do is ...?

—Dawn

Sent from my iPad

--

You received this message because you are subscribed to
the "Pick and MultiValue Databases" group.
To post, email to: mvd...@googlegroups.com
To unsubscribe, email to: mvdbms+un...@googlegroups.com
For more options, visit http://groups.google.com/group/mvdbms
---
You received this message because you are subscribed to the Google Groups "Pick and MultiValue Databases" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mvdbms+un...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/mvdbms/ac269109-5ee5-4585-98f4-4549acaf8898%40googlegroups.com.

[Tony Gravagno]

It might be helpful if you parcelled out specific focused thoughts into sub-threads that can be considered and commented by our colleagues. :)

[Dawn Wolthuis]

It's a big topic, to be sure, and a strategic one requiring visionaries to help shape the future of the industry, and I know that isn't everyone's cup of tea. Maybe this topic is only for the MBTI INTP folks, Enneagram 5. I see all you all rolling your eyes.

I'm happy to hear what you and others think regarding anything related to the subject of the post. I didn't intend to wander too far from that topic, and I don't think I did prior to introducing personality types above. With the industry on the cusp of new business applications that would once have been written with SQL CRUD heading to the place where software application businesses will be (back to) using NoSQL products instead, what standards are evolving for non-first-normal form CRUD queries/updates? 

I've looked at various products on the landscape, with this slide one I used in my slide deck about a year ago (a happier slide when doing a build of it -- it looks a bit sloppy here). I highlighted two predictions: GraphQL (at least for CRUD) and Gremlin (at least for BI) seem to be rising above the rest, but there are and have been many other attempts to move beyond SQL for handling NF2 data, and there could be many more. If "we" had an update processor, I might even suggest the MV Query language would fit the bill, but GraphQL looks pretty much on point to me (still from a bit of a distance, not having written product-ready software with it).

SQL has had extensions for NF2 too, with STRING_SPLIT for MS SQL Server, for example. Products such as Cache' have built extensions into their SQL implementations for NF2 data. Products like PostgreSQL have done more work than some others with JSON CLOBS and otherwise has tried to accommodate persisting data not in first normal form. There is so much SQL out there that it makes sense to extend that language for NF2, but within the coming decade the industry will start to put SQL on the "legacy" heap wherever we are not already stuck with it. 

For those who never "went there" (to SQL, first normal form) to any great extent, I'd use what I was told was an "old Chinese proverb" -- never leap a chasm in two bounds. We can stick with NF2 CRUD and be right in the swing of things. 

There is more than one way to address NoSQL CRUD, and I'm thinking that the industry will standardize some things, even if not in the same way it did with SQL.

Some Sub-topics:

GraphQL

    Connectivity to the back-end via API's and possibly otherwise (?) for MV DBMS products

    Front-ends including Vue, React, Angular working with NoSQL CRUD requirements

    Full stacks enumerated for working with data in MV or other NoSQL products and writing web apps (e.g. PWA's) with some consistency across sites, developers, and apps for CRUD

    Possible tools to help with the projection and maintenance of metadata with DICTs, APIs, GraphQL, JSON/JavaScript all in the mix.

    Standards organizations and what they are working on, such as https://foundation.graphql.org/

I'm sure there are other topics relevant to this big topic. Thoughts?  --Dawn

Dawn M. Wolthuis President, Tincat Group, Inc. 616-901-6293 | dw...@tincat-group.com

Take and give some delight today

On Mon, Aug 19, 2019 at 11:50 AM Tony Gravagno <bacj8...@snkmail.com> wrote:

It might be helpful if you parcelled out specific focused thoughts into sub-threads that can be considered and commented by our colleagues. :)

--

You received this message because you are subscribed to
the "Pick and MultiValue Databases" group.
To post, email to: mvd...@googlegroups.com
To unsubscribe, email to: mvdbms+un...@googlegroups.com
For more options, visit http://groups.google.com/group/mvdbms
---
You received this message because you are subscribed to the Google Groups "Pick and MultiValue Databases" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mvdbms+un...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/mvdbms/2ceeeab2-f32d-4973-8ce2-246485b33c3e%40googlegroups.com.

[Dawn Wolthuis]

For those who have not played around at all with GraphQL, here is something simple to do:

Step 1. Go to http://graphql.github.io/swapi-graphql

Step 2: Click on the Docs arrow on the right hand top of the page to show the schema 

Step 3: Copy and paste the query below (starting with { ending with } You can put it after the # comments but I just highlighted and deleted them in the left hand pane before typing this. 

Step 4: Click the triangle above (in a circle) to execute the query

DONE. It should look like the image in this email

{
  person(personID: 2) {
    name
    gender
    height
    birthYear
  }
}

Sure, I would prefer 

list person with personID = 2 name gender height birthYear

or maybe I even have a fondness for

LIST PERSON WITH PERSON.ID = 2 NAME GENDER HEIGHT BIRTH.YEAR

but this GraphQL JSON string is pretty clear and the JSON result is too. Pretty groovy that non-first-normal-form queries are back in, right? 

I would like to see 1) superb full-stack metadata management 2) good ways to get server-side logic that is relevant to the UI to the front, such as extended metadata repository, e.g. virtual fields, constraints, rules [more complex to also get site-specific user-defined business logic described on the front-end, into the back-end, requiring a significant metadata management/rules processing approach] 3) data and application security addressed in some consistent way for NF2, sans SQL and 4) fast, reliable, secure connectivity to any DBMS of choice, including MV, by way of APIs or more "direct"

So, every application development site can roll their own when it comes to this kinda stuff, but the timing looks just right for some consolidation of approaches.   --Dawn

Dawn M. Wolthuis President, Tincat Group, Inc. 616-901-6293 | dw...@tincat-group.com Take and give some delight today