Wednesday, 7 January 2015

Version 5.2 Beta

Since early April 2014 I have been working on this new version of Pyrrho DBMS. The open source edition of this version is available today in beta form for download, together with a set of unit tests.
The new version retains the same SQL language and file format as previous versions, so should be fully backwards compatible with databases created by them.
But the query processing system has been completely rewritten to be more robust for supporting developments such as MongoDB, and enhancements planned for the HTTP/REST service. I will start working on these soon.
The comments in the code also need to be completed and revised.
The new version is better able to process some standard SQL syntax (I give some examples below), and also some innovations such as the following:
  • FROM STATIC: similar to the use of "DUAL" in other DBMS, this helps meet the requirement for the FROM keyword in the SQL standard while allowing the evaluation of expressions, e.g. SELECT SESSION_ROLE FROM STATIC.
  • The ability to index on fields within structured types, e.g.
create type mrw as (c int)
create table e(f mrw, primary key(f.c)
  • The ability to rename database objects with automatic consequential modification to stored procedures, constraints, views etc that reference the modified objects.
One of the main motivations for the new version was to get triggers working fully according to the SQL 2011 standard. Example:

create trigger sdai instead of delete on a referencing old table as ot for each statement begin atomic insert into c (select b,c from ot) end
create trigger riab before insert on a referencing new as nr for each row begin atomic set nr.c=nr.b+3; update b set tot=tot+nr.b end
create trigger ruab before update on a referencing old as mr new as nr for each row begin atomic update b set tot=tot-mr.b+nr.b; set nr.d='changed' end

Other examples from the test suite:
select bb,dd from aa order by bb+cc
select a[b] from (select array(bb,cc,dd) as a,ee as b from aa)
select array(select bb from aa order by cc)[1] from static

Some examples withdrawn from the test suite include

select bb as a,(select max(bb) from aa t where>a) from aa
insert into d (select 17,(select a from b where c=1) from static)
These look like SQL, but I don't think they are valid. In the first, left-to-right processing of SQL as required by the standard means the type of bb is unknown until we reach "from aa". In the second I believe SQL requires the column names to be correct for INSERT SELECT: the test could be improved by inserting the VALUES keyword. I'll be interested in comments about these tests. 

No comments:

Post a Comment