Software Refactoring

The following presentation is a joint work with Giorgio Orsi on Software Refactoring, is one of the intermediate product of the course Argomenti Avanzati in Ingegneria del Software (Advanced Topic of Software Engineering). It is just a summa of the existing approaches, and some of our opinions on how a disciplined Refactoring can help to reduce the gap between Research and Didactic, by help in the integration of student activities (Projects and Thesis) in the main stream of professors research. Refactoring can indeed fill up the gap from the so called student-ware and the final software.

refactoring.pdf 

NSPACE = CO-NSPACE

For the course ADVANCED TOPICS ON ANALYSIS AND DESIGN OF CRITICAL SYSTEMS    i attended in Politecnico di Milano in 2007, I had to study and present the Neil Immerman proof of NSPACE = CO-NSPACE. This is the small presentation i prepared with Simone Campanoni. We present the two available proofs Szelepcsènyi’s and Immerman’s. I hope this will make someone life easier.

 nspace.pdf 

DB2: INSTEAD OF TRIGGERS

Reading about DB2 i discovered an interesting functionality, the INSTEAD OF triggers, which can be used to support insert, update and delete against complex views. I have not investigated their use yet, but they look a powerful mean. I’ll keep you posted on my experiments.
INSTEAD OF TRIGGER:
INSTEAD OF triggers describe how to perform insert, update, and delete operations against views that are too complex to support these operations natively. INSTEAD OF triggers allow applications to use a view as the sole interface for all SQL operations (insert, delete, update and select). Usually, INSTEAD OF triggers contain the inverse of the logic applied in a view body.

DDL TRIGGERS: Oracle is the way.

For a project I’m currently working on I need to use DDL triggers, it seems (at the best of my current understanding) that:

MySQL: no support for DDL triggers yet, they are part of the “remote”
TODOs, we will probable not see them for a long time. ( I tried to
install trigger on the <tt>information_schema</tt> with no results,
since the information_schema is in general a virtual DB.)
DB2: has no direct support for DDL triggers, workaround are possible
using the tracing functionalities http://database.ittoolbox.com/groups/technical-functional/db2-l/ddl-triggers-1147710

. As discussed here also in DB2 triggers on the
<tt>information_schema</tt> are not possible.
Oracle and SQL Server 2005: support DDL triggers.

The server environment we are operating is Linux based, I’ll go for
Oracle.

Here there is a short reference to DDL-triggers in Oracle:
http://www.psoug.org/reference/ddl_trigger.html

 
I will install oracle on the UBUNTU 6.06 LTS server we got at UCLA. This seems a good guide: 
 http://linux.togaware.com/survivor/Oracle_10g.html to install Oracle 10g on
Debian sid. I’m going to use to actually install Oracle 11g on Ubuntu
6.06 LTS.

MySQL views: 200 views chain limit?

I’m doing some stress test with MySQL on views…

– i created a chain of very simple views:
CREATE VIEW view1 AS SELECT * FROM `table1`;
CREATE VIEW view2 AS SELECT * FROM `view1`;
CREATE VIEW view3 AS SELECT * FROM `view2`;
….
(table1 contains 1000 records) i created 200 of them and it seems that MySQL manages them very good…
mysql> SELECT empno FROM table1 WHERE birthdate=”1953-04-08″;
2 rows in set (0.00 sec)
mysql> SELECT empno FROM view1 WHERE birthdate=”1953-04-08″;
2 rows in set (0.00 sec)
mysql> SELECT empno FROM view50 WHERE birthdate=”1953-04-08″;
2 rows in set (0.00 sec)
mysql> SELECT empno FROM view100 WHERE birthdate=”1953-04-08″;
2 rows in set (0.00 sec)
mysql> SELECT empno FROM view200 WHERE birthdate=”1953-04-08″;
2 rows in set (0.01 sec)
this were the first executions of the views… the system might have cached something but not a lot…
– then i created other 300 views (all the way to view500)… MySQL tends to panic… lost connection, or sometimes gets extremely slow…
(here i change the date in the WHERE to avoid cache, this is why we have empty set)
mysql> SELECT empno FROM view100 WHERE birthdate=”1953-04-09″;
Empty set (0.00 sec)
mysql> SELECT empno FROM view200 WHERE birthdate=”1953-04-09″;
Empty set (0.01 sec)
mysql> SELECT empno FROM view300 WHERE birthdate=”1953-04-09″;
ERROR 2013 (HY000): Lost connection to MySQL server during query

mysql> SELECT empno FROM view300 WHERE birthdate=”1953-04-09″;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect…
Connection id: 1
Current database: viewstresstest
ERROR 2013 (HY000): Lost connection to MySQL server during query


I tested a little further and the problem seems to be in the chain somewhere between 200 and 205 views…
My feeling is that the problem relies in the implementation and is not a theoretical limitation of the scalability, cause the performance drop is too sudden to be intrinsic in the approach…
I posted it on the MySQL forum but I’ve got no answer so far…

If someone plans to repeat the test , this is the script for generating the views:

#!/bin/bash
numberofview=$1;
echo “CREATE VIEW view1 AS SELECT * FROM \`table1\`;”;
for((cv=2;$cv<=$numberofview;cv=$(($cv+1))));
do
cvold=$(($cv-1));
echo “CREATE VIEW view$cv AS SELECT * FROM \`view$cvold\`;”
done