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

By · March 28, 2017 · 0 comments

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)
Read more...
web+db Older Posts

Get the latest posts delivered to your inbox.

Comments

There are no comments yet. Be the first to leave a comment!

Leave a Comment

All comments are moderated and rel="nofollow" is in use. Avatars are sourced from gravatar.com – a globally recognised avatar.

Type the numbers from the picture above

About me
John Avis ...mostly about web development and programming, with a little bit of anything else related to the Internet, computers and technology.

profile for John at Stack Overflow, Q&A for professional and enthusiast programmers
Subscribe

Get the latest posts delivered to your inbox. *