Over a million developers have joined DZone.

Direct Execution of Database Schemas

DZone's Guide to

Direct Execution of Database Schemas

· Database Zone
Free Resource

Learn how to create flexible schemas in a relational database using SQL for JSON.

Lets start with a simple example:

There is an imaginary App which gives Users access to two database tables:
Departments(3 columns) and Employees(4 columns), which are related as one-to-many.

We have to create code to service the widest possible set of operations upon those Tables: all the Inserts, Selects, Updates and such. An App must generate a generic UI and provide the means to do comprehensive generic reporting.

An App has to be multi-client enabled, with real-time clients updates, and it has to keep the Database ACID. And just in case, there must be an API in place to program some custom magic beyond supported generic operations and reports.

Then there is another App with, again, 2 Database Tables:
Warehouses(3 columns) and Parts(4 columns).

Requirements are the same as App number one.

It feels like the same code could serve both Applications. Just feed that code the parameters with actual Tables/Columns names. Optionally feed more parameters to customize UIs.

And that code might look like that:

Table1(name)(3 columns[names, types]) -->> Table2(name)(4 columns[names, types])

so essentially a single declarative line + some possible UI mark up.

Now that exercise generates more questions:
what if there are different sets of columns?
what if there are different number of Tables?
what if tables related in different ways?
where is the border of schemas complexity to produce that generic code to serve all?

For many years I was building tools to extend variety of Tables, their relations and their UIs. I was even trying to market those tools. Without much success though. Nothing wrong with the tools or implemented Apps - I'm just that kind of a marketer.

The point is:

looks like I've built an interpreter to directly "execute" database schemas, well, maybe slightly modified schemas.

It is obvious that same schemas could be expressed in text form or as "boxes-and-lines" diagrams. I do prefer the latest.

So the development works like that: as quickly as Visio-like schemas' editor prepares a schema[or part of it], it is fed to an App server, which instantly gives eligible users access  to those newly generated tables. Further [re]development is possible on already deployed live Apps - the interpreter allows that.

There is a full set of admin functions to govern those Apps and API and other stuff.
There are some limitations - my implementation is not an ideal one(but close :-)).

But again the point is: an App is generated as fast as one draws a schema.
I did a few Line Of Business Apps [50+ tables, pics, PDF, HTML supported],  they work fine, it proves the concept.

The First Big Question is: Have I reinvented the wheel?

One of the subtle attempts to automate data relations I've heard about is FileMaker with the concept of a portal. Drop-down lists are another example. Too little, too shy. Still my interpreter is much more generic and can run very generic schemas. And it is hard to believe how much of imperative SQL could be replaced by a few declarative lines.

The Second Big Question: What do I do now?

The technology if proved is pretty disruptive and the area of application is pretty big. By many reasons that is not a task for a single developer.
OpenSource? Let it go? - I do have another sources of income.

6 months ago I shut down my "production" server - zero traction syndrome - and took some healing in doing physical work. So that FancyData.com I used to run my servers is offline now - please do not blame me for that.

Waiting for your input here,
with respect Alex Semenov.

Create flexible schemas using dynamic columns for semi-structured data. Learn how.


Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}