More on web+db: An SQL based web programming language

I still haven't given up on my belief that there is a place for a new programming language/environment where your application database logic are in a single system.

My latest thoughts are that this should be a derivative of the SQL language.

Below is my idea of how some very simple pages might look.

The first one is a paged list of results from the database:

 <?
DECLARE @page INT = CAST(REQUEST.GET("page") AS int);

IF @page < 0 THEN @page = 1;

DECLARE @rowsperpage = 10;

DECLARE @count INT = SELECT
		COUNT(*)
		FROM tablea
		INNER JOIN tableb ON tablea.id = tableb.tableaid
		WHERE tablea.deleted = 0;

IF @page > (@count / @rowsperpage) THEN @page = (@count / @rowsperpage);

DECLARE @result RESULTSET = SELECT
		tablea.field1, tableb.field2
		FROM tablea
		INNER JOIN tableb ON tablea.id = tableb.tableaid
		WHERE tablea.deleted = 0
		ORDER BY tableb.field2
		LIMIT (@page - 1) * @rowsperpage, @rowsperpage;
?>

<table>
	<tr>
		<th>Column A</th>
		<th>Column B</th>
	</tr>

	<? WHILE (@result <> NULL) BEGIN ?>

	<tr>
		<td><?= @row.field1 ></td>
		<td><?= @row.field2 ></td>
	</tr>

	<? @result.MOVENEXT() ?>

	<? END ?>

</table>

<p>Showing page <?= @page ?> of <?= (@count / @rowsperpage) ?> 

The next is a simple add/edit form:

 <?

DECLARE @id INT;
DECLARE @title VARCHAR(50);

DECLARE @error VARCHAR = '';

IF NOT REQUEST.POST BEGIN

	@id = CAST(REQUEST.GET("id") AS int);

	IF @id != 0 BEGIN

		DECLARE @row RESULTSET = SELECT title FROM tablea WHERE id = @id;

		IF (@row == NULL)
			@error = '<li>Row not found</li>';
		ELSE
			@title = @row.title;

	ELSE BEGIN

		@title = '';

	END

END
ELSE BEGIN

	@id = CAST(REQUEST.POST("id") AS int);
	@title = REQUEST.POST("title");

	IF (TRIM(@title)) = '' @error += '<li>Title is a required field</li>';

	IF @error = 0 BEGIN

		DECLARE @rowsaffected int;

		IF @id != 0 BEGIN

			@rowsaffected = UPDATE tablea SET title = @title WHERE id = @id;

		END
		ELSE BEGIN

			@rowsaffected = INSERT INTO tablea (title) VALUES (@title);

		END
		
		IF @rowsaffected != 1
			@error += '<li>There was an error when saving record</li>';
		ELSE
			RESPONSE.REDIRECT REQUEST.URL;
	END

END
?>

<form action="<?= REQUEST.URL ?>" method="post">

<? IF @error != '' BEGIN ?>
<p>Errors:</p>
<ul><?= @error ?></ul>
<? END ?>

<label>Title</label>
<input name="title" type="text" value="<?= HTMLENCODE(@title) ?>" />
<input type="submit" value="SUBMIT" />
</form>

These samples introduce a variable type called RECORDSET which contains the current row, and can be used to navigate through all results in the RECORDSET. I haven't fully thought his out but imagine it would be something like cursors are in SQL.

I've also added some ways of properties and methods for HTTP requests and responses as you would typically need in a web application, eg. POST and GET parameters, which method is used, etc. Also redirect, HTML encoding, etc.

A way of building dynamic queries would also be needed, so I envisaged a QUERY variable type. Here is how it might typically be used:

DECLARE @keywords VARCHAR = REQUEST.GET('keywords');

DECLARE @keyword LIST(VARCHAR) = SPLIT(@keywords, ' ');

DECLARE @query QUERY = SELECT tablea.column1 FROM tablea INNER JOIN tableb ON tablea.id = tableb.tableaid;

FOREACH (DECLARE @word IN @keyword) BEGIN

	@query += WHERE tablea.column2 LIKE %@word%;

END

IF @order = 1
	@query += ORDER BY tablea.column1 ASC;
ELSE
	@query += ORDER BY tablea.column2 ASC;

DECLARE @result RESULTSET = @query.GO();

So this is a simple example of how you might do a keyword search using a variable number of keywords, and also changing the order by field. First we build our QUERY object, then we get a RESULTSET by executing the query. Not sure about the "+=", this is just an example.

I also imagine ability to create classes like other OOP languages, including partial classes for tables (eg. add a property for a calculated value to a table).

I would expect that rather than CREATE/ALTER as we do for SQL now, all objects would be stored in some sort of file/folder structure. For example you would have your tables defined in files in a folder and as you deployed these from a development environment to a production environment the table structure would be altered accordingly. An example of file structure would be:

/tables/ (tables defined here)
/views/ (views defined here)
/triggers/ (triggers defined here)
/procs/ (library of code here, including common code, utility functions, business layer for multi-tier applications)
/public_procs/ (this would be the public web folder, which could call code libraries from the “/procs” folder)