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

John Avis by | March 28, 2017 | web+db

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.
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)

Related Posts

web+db

Further refining the Web+DB concept

by John Avis | June 17, 2016

I'm not ready to give up on this concept.


web+db

Web and database development and servers should become one system

by John Avis | December 9, 2015

Recently I've been researching and experimenting with alternate methods to various things involving development, particularly database access, and have come to the conclusion that the systems that we have evolved to today are not ideal.


web+db

In this day and age why are web and database separate systems?

by John Avis | December 3, 2015

Any website other than a very simple one uses a database these days, right? Well then why is there a separation between web server and database server? Surely there has to be an advantage in integrating these two systems into one?

Comments

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

Leave a Comment
Tags
ASP.NET Html Forms ASP.NET MVC ASP.NET Web Forms ASP.NET Web Pages Bootstrap C# Classic ASP Cool Websites Databases eBay and PayPal Electrical Repairs General Hardware HTML/CSS Jquery/Javascript Media Center Mobile Phones Responsive Web Design SEO and Social Networking Web Design Web Development Web Security web+db Website Hosting Windows XP

About me

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

Subscribe

Get the latest posts delivered to your inbox.