SQL needs a new keyword called FORM (not From)

I would like to email details about below thought. It includes a .PDF
file that explains in depth. Basically, consider -
CREATE FORM <form_name> AS SELECT <ALL or col_name> FROM <table_name>;

Then, by-magic, we have an Input/Query form which includes drop-down
lists for every Foreign Key, etc. All based on the INFORMATION_SCHEMA.

I.E., A pretty good form in 5 seconds.

Possibly, a good project for an MIT nerd?

-- Phil Olson, April 29, 2008

Answers

People have certainly built things like this, i.e., automatically generated forms from SQL table schema. I don't see how it could work as SQL, though. The database server is a machine deep inside a network somewhere and far away from the user, often separated by a firewall from the user.

As soon as you say "Input" you are presupposing a standard interface, which doesn't exist as far as I know. The user could be on a Web browser, a PC, a mobile phone, a telephone voice prompting system, etc. The database management system has no way to know.

-- Philip Greenspun, April 29, 2008

Readers, ignore this comment, simply checking that I do get notification. Phil

-- Phil Olson, April 29, 2008

You say "people have certainly built things like this". I have not found any such product. People have built many ways to "reverse engineer" a schema, therefore, we know it can be done. But, they fail to see that "The database contains the data defintion and the data." In my opinion Input Forms are also part of the data definition.

1. How would it work through SQL?

1.1 Oracle would most likely use SQL*Plus. They currently have reserved words like CONNECT BY and a host of words for report creation. The link below is one reference of their willingness to have more words in SQL. http://cis.bentley.edu/mrobbert/CS651/orarep.htm

1.2 MySQL would most like use the MySQL Browser.

1.3 Oracle also has a Browser.

2. How about the "Input"? What is the standard interface?

2.1 The user is logged into the schema. The interface is SQL.

2.2 User types CREATE FORM SELECT * FROM <table_name>; AND instantly

2.3 The form is in the Browser window. User keys a a button to either INSERT, UPDATE, DELETE, or SELECT.

2.4 User fills out the form, keys the COMMIT button. The Browser generates the appropriate SQL script, receives and interprets SQL error messages.

3. What if user is not on a web browser?

3.1 Not my problem. My goal is to provide Forms to support the following activities --

3.1.A Rapid development. Speaking as a Data Architech I am tired of writing scripts to INSERT test data into a database. I am tired of waiting three weeks for the Forms Programmer to generate forms. My clients would be quite happy to test out the design with "ugly" forms.

3.1.B Tiny "home-based" uses of SQL. MicroSoft Access is not a great database tool. But, it provides small scale users with a means to Input data. MySQL has a free version with a free Browser. Users would very likely use it more - if they could input data.

3.1.C And the reason that started this whole idea is -- My PhD son is doing medical research. Two million rows of bedside monitoring data via flash-drive; no problem - the data is always perfect. But he also has 200 rows (30 columns) of EXCEL spreadsheet data. The grant is not so big that he will pay for design of forms. So, 4 team members making four spreadsheets and tragically load into MySQL via .CSV files. Even though they all have MySQL network access. His next project will span four hospitals with 8 team members.

Multiply these stories by Develops, Home-userss, Students, and researchers around the world.

2. Oracle also has a Browser capability. I.E. log on to a database and submit SELECT, INSERT, UPDATE, DELETE statements and even send SELECT results off to an EXCEL spreadsheet. All while connected to the server.

-- Phil Olson, April 29, 2008

Your son could write a perl script to parse those 200 rows and get them in. Even if the parse is a tricky one, it would take a day or two at most.

I just finished parsing about 6000 records describing spinal interventions. The data was free text, came in word files and was very difficult to parse, but it can be done. Your son's case sounds much easier.

-- Adam Dobts, April 29, 2008

My son write Perl script? Ho Ho, He is a PhD nurse, knows statistics, still on the ICU floor 2 days a week (Duke Medical feels researchers should still practice). It was jolly hell teaching him SQL. And his colleague - Dr. Kawasaki who in all honesty says "I am the Tiger Woods of brain surgery" finally allows MySQL on his PC. We all have our areas of expertise and the goal of us "Programmers" for 50 years (in my case) has been to develop ways to make it easier on the non-nerd.

-- Phil Olson, April 29, 2008

This seems to be a common problem with medical research. The projects are too small to justify hiring a programmer to do anything really sophisticated, but too esoteric to be helped by anything you can grab off the shelf or open source.

Some efforts have been made to achieve generalized parsing of medical text, notably BioLG (http://mars.cs.utu.fi/biolg/), but again the effort involved in just figuring out what it does is hard to justify for a project that runs just a few hundred records. On the other hand, if there's going to be further research using the same data (or the same field specific language), it might be worth the effort.

-- Adam Dobts, April 29, 2008