johna's blog

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

2017-03-28T12:00:00+10:00

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)

Further refining the Web+DB concept

2016-06-17T12:00:00+10:00

I'm not ready to give up on this concept. Although I should change the name to app+db.

Here's the summary of issues I'm wanting to solve or improve:

1. My database is stored in the file system. My application running on an application server connects to the database server application which reads and writes these files.
2. My application only knows what I tell it about my database structure.
3. I want my database querying and manipulation to be strongly typed so I use Linq to construct queries (and other reasons), but I need to know Linq well to ensure my queries are efficient. I also need to know T-SQL well to ensure that the Linq translations are efficient.

Number 1 is not a big issue, but given that probably 99% of applications require a database I feel that there would be benefits in combining the application and database server applications.

Number 2 has kind of been solved with code first database creation. My thoughts are that the code should define the database structure so we can do away with having to keep our database and application in sync.

Number 3 is probably my biggest issue. I frequently work on older applications with SQL queries constructed as strings and it makes me appreciate what a great development environment we have in the Microsoft world with Visual Studio, and strongly-typed representations of our databases (eg. Linq to SQL).

Unfortunately, as much as I appreciate writing my database queries in Linq, it irks me that I now have to be an expert in two types of database querying. I have to know how to craft efficient Linq queries, and also know how to craft efficient T-SQL so I can trouble shoot and fine tune the queries that Linq generates.

Do we need a translator? Why can't we just write strongly typed T-SQL (or some native database language) like we do Linq?

Also, for large projects I usually break up my database into modules of related tables, with a Linq to Sql dbml file for each. But many queries need data from other modules. This means I either have to create a view (defeats the purpose of trying to keep all logic in the one place) or include the other table in multiple Linq to Sql dbml files.

Sorry to bring up early versions of dBase again, but I still see that as a good example (through rose coloured glasses): we create our applications in the database application.

Web and database development and servers should become one system

2015-12-09T12:00:00+10:00

I have to admit it but I am the kind of programmer (or Software Writer, thank you DHH) who is more about getting a job done, rather than producing code that adheres to whatever today's pattern of choice is, or writing code that will be easy for someone else to comprehend (or even myself a month later).

In my current position, and in most of my positions, I have often been the sole developer responsible for some new projects and modifications to existing applications, and time is usually critical. Had I ever worked in a larger team I probably would have picked up some better habits.

I mention this because it influences what I want and need from development environments and server systems.

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.

Looking at database access from a web perspective, let's look at the some of what we have that I think isn't ideal.

Firstly, we have a web server application (and hardware) and a database server application (and probably separate hardware). Two separate systems that need to have a connection and move data backwards and forth. Although I see the obvious advantage of the systems being separate in that each can do what it does best independently of the other, I wonder if it wouldn't be more efficient to have the functionality of these two in one application (on one or more servers).

Then from a development perspective, we create our database, create our website and then we either have to just code in SQL queries which is prone to errors, or use some sort of ORM so our web application can learn the structure of the database. To be efficient we do things like make sure our select queries return only the data needed so we can minimise data transfer between systems. For some complex operations we might create a stored procedure so as to minimise back and forth between these two systems. Now we are coding in two different development environments and this could open up issues with the development process, for example different skill requirements, deployment issues, and source control.

If the database was part of the same development environment we could directly access the database structure from our code.

If the database was part of the web server we could access any data needed without fear of inefficiency from just moving data.

In the "old days" I used to create some fairly complex information system applications using a DBMS called dBase (I used versions III and IV). This was a database first, but with a programming language for controlling how data is displayed and entered. Although these were desktop applications it's not a dissimilar situation to a database-driven website or application. Although you specified which databases you wanted to use, there was no connections between systems, and the programming language had database query and manipulation as a (the!) core feature.

I imagine a conceptually similar language for my ideal combined web and database system. Don't get me wrong, I don't want the dBase IV programming language. It wasn't a great system in hindsight, as every "table" was a database of its own and from memory there was no relationships.

I use Linq to SQL primarily in my day job and I think that this is a good example of how SQL-like queries could be integrated into a programming language, but I propose the language to be even more SQL-like. With L2S we often have to jump through a few hoops to get the resulting SQL query we want, which means that to be a good and efficient L2S developer, you have to be an expert in L2S and an expert in SQL. Again, why two systems?

If I were to improve on L2S I would better handle different types of database joins, and in my combined system concept, there would be no need to specify columns to be returned: just access any column from any specified table or joined table.

Doesn't it drive you crazy when you add some new functionality to an application and you need a different column set than you have needed elsewhere so you have to then create a new database view and model, or (gasp) return an anonymous type?

I've mentioned these thoughts in online discussion before and have almost universally been shot down, but I still see merit in these ideas. Personally, I think that although most developers welcome new technologies and techniques in general, that many are so set in their ways that they can't embrace something that goes against some of the things that the separation and pattern police have brainwashed them with.

All of these thoughts lead me to check out if there was actually something that did something like I was dreaming, and someone pointed me to Ruby on Rails which I had previously bought a book on but I think I lost interest after finding half the book relating to what appeared to be a very long winded process on how to install what's needed on Windows (was an early version, so has probably changed).

Although since I've never even done "Hello World!" in Rails, I have read a bit about it and watched a few online videos showing its features, and it looks very appealing in many ways.

I'm not so sure about going back to the command line, and I don't know if I like the idea of having so much CRUD code created automatically, but I really should give it a try and see rather than prejudging.

Also, having been a Microsoft developer for so long, I do love Visual Studio and all that goes with it like Intellisense, code completion, formatting, and debugging. I keep reading that a lot of this isn't needed in RoR but maybe I have too many bad memories of writing Classic ASP in Notepad.

I really like that in VS when I write some code with L2S that, being strongly typed, it tells me immediately when I get something wrong. I watched a video with one of the RoR developers showing some code and I wasn't impressed by having to save code, open in browser, check some sort of web server real time logging to see where things went wrong.

Also in my book on Rails, there is a bit of hand-coded SQL queries to do some of the things that can't be done automatically.

So as much as I'd like to try producing something in RoR, I don't think it's the answer to my prayers.

I don't have the details for how this would all work, I'm just thinking out loud on the concept, and hoping to stir up a bit of discussion and maybe someone reading sees some merit in the idea and develops the next big thing in web servers and development.

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

2015-12-03T12:00:00+10:00

I originally posted this as a question on the StackExchange wesbite ProWebmasters where it got put on hold for the legitimate reason that it wasn't an appropriate question for a Q&A website and then on Programmers where it also got put on hold for the same reason.

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?

When I develop a website, I create a database, start on my website coding, and I generally use an database object mapping tool to make my website understand my database structure. Changes in one system often mean changes in the other.

Then when my website runs, my website has to create connections to my database server to process data.

I propose that the web and database server become one!

Don't misunderstand me, I know that for reasons of performance and scalability the processing by these systems may need to be split over two or more physical servers and whatever I am suggesting would need to cater for this.

What I am suggesting is creating something like an IIS or Apache with a built-in SQL Server or MySQL.

The database structure should be created in the website coding. I don't just mean code first type database design. Changes to the database would be in the website code so would be instantaneous and wouldn't require updating models, etc.

I understand that databases are frequently used by multiple websites or other applications (desktop or mobile apps) so this would need to be catered for, maybe the database code could be some type of shared module. I haven't fully thought this part through but non websites (eg. desktop or mobile apps) could connect to the database through the website or shared module via a web API or perhaps directly, just like to a database server.

This new system would need a programming language that has database structure creation and query as an integral part of the language.

It would allow processing to be split across multiple servers.

On the StackExchange websites I tried to improve my question with an edit as it seemed many people might not have fully understand my idea...

Edit: I don't think I explained my concept very well, and I am struggling to explain it better, but here goes...

We have two different systems here, a web application which is developed in a programming language and is accessible via web server software (not interested in the hardware for the sake of hopefully better explanation). Then we have a database file/s which is accessible via database server software. When the web server needs to access data a connection is made between two systems and communication and translation between these two systems occurs so that data can be moved back and forth.

My suggestion is that the web server software could include the functionality of the database server software. I'm not talking about evolving and combining what we already have but something more revolutionary. And in addition to the server software, a programming language that has database access at its core rather than an add-on.

I'm a Microsoft developer and have dabbled a bit in other environments but I haven't found one where database access is as good as it could be. In Microsoft-land we either write our SQL queries and send them from our application to database server, or maybe use stored procedures, or maybe use a database object mapper, and possibly Linq which then has to be translated into SQL by the web server. We generally like to have things strongly typed. Then maybe when something changes in the database we have to update our models, etc, etc. It works but it doesn't seem ideal to me, and I think that somehow this needs to be better integrated.

And I'm not suggesting that this combined system shouldn't run on more than one physical server, and I'm sure that would be a necessity. However, I haven't given much thought to how that might work. That's a problem for later (if ever).

Or just maybe everyone understood perfectly and it's just a crap idea.

Although this idea was almost universally shot down by StackExchange members, I still think there's some merit to it, and I need to think it through more, and explain it better.

After a little thought, and people's suggestion to explain it as a solution to a problem, here is revision 3...

There are two problems I want to solve.

Number one is that because there is a separation between web server and database server that there is a loss of some efficiency and performance.

Number two is that when developing code, because of the separation between the application and the database, we either have to do away with strongly typed code, or maintain the database structure in two systems. Strongly typed code is essential for efficient development, but it is not efficient to have to maintain the same structure in two different systems.

My solution to problem number one is two combine the web server and database server applications into one. I don't mean on one piece of hardware, and I haven't considered how this could be split over multiple server hardware. But why does a web server need to connect to a database server, when it could also be the database server?

For a solution to problem number two, I propose a programming language and development environment that better integrates with databases.

If these two systems were better integrated, when I write a database query it would be strongly typed against the actual database structure, rather than a model.

Forget what I said about code first database design - it doesn't matter how the database is created, more that it is more integrated and there is nothing in the middle to slow down development.

The programming language would have more integrated database query and manipulation. I think Linq to SQL is on the right track for this, but would need to be closer aligned to T-SQL to avoid some of the work-arounds we have to do now. We should be able to do an outer join in less code, and we should be able to select columns from different tables without having to create a specific model, anonymous type, or a database view.

I know I'll cop more flack about some of these points but I encourage you to think outside of the way we do things now, a lot of which is because there is such separation between these two systems which are almost exclusively used together.

And maybe what I am talking about is more evolutionary rather than revolutionary after all.