Null values in registration

[alphaville]

The general format of a prepared statement does not allow for null values lookup using the equals (=) or LIKE operator. So, if one registers a null value in the database, it will not be ever returned using the method SearchEngine.search(DeciBell db).

The same happens with the numeric values. If one registers a value which is equal to the NumericNull value of a field, it will RUIN the whole database, as no other component will be able to be retrieved! Is such a case, an Exception should be thrown and users should be discouraged to use the value reserved for NumericNull in a registration.

The same schema can be exploited when registering string values in the database. We can introduce a Java Annotation, called @whatever which will generalize the annotation @NumericNull to both numerics (int, long etc) and Strings!

[alphaville]

Proposal:

1. Create a new Annotation Type, namely @whatever (see https://gist.github.com/050622abef93188fd4d3#file_what_ever.java ), which identifies a value (normally one the field will never need to have, e.g. population = -1 or firstname="asdf123"). Every field used by DeciBell should have a @whatever annotation. This will be used for database lookup purposes.
2. Throw an ImproperRegistration Exception (see http://github.com/hampos/DeciBell/blob/master/src/org/kinkydesign/decibell/exceptions/ImproperRegistration.java ) if one attempts to register in the database the value backed up by @whatever for searching only.
3. No NULL values should be in the database; for this purpose, if the user attempts to register a null value, it is converted into a pseudo-null one (e.g. for String values use "NULL". (Note that there is no way for the user to set a numeric field to null).
4. If one searches for all Users with name "... LIKE '%%' ..." the NULL names will be also returned. This is not the case is one searches for all Users with name "John" or "Jo%". This formulation meets the user requirements.
5. While performing search operations, if we run into a "NULL" value, we convert it back into a null one!
6. In the most complex case of self-referencing tables (which is the one we might need to discuss a little bit more), the first entry should either point to it self or be allowed to point to NULL (in the SQL sense). It is possible to do that in a way that search will be feasible but we have to amend the prepared statements for self-referencing tables. Let me give an example on that:

Consider of a table "SRT" with the following structure:
x INT PRIMARY KEY,
y VARCHAR(255),
z INT, FOREIGN KEY(z) REFERENCING STR(x)

The prepared statement we use now is:

SELECT * FROM SRT WHERE X<=? AND X>=? AND Y LIKE ? AND Z<=? AND Z>=?

And note that z is a self-referencing column. This prepared statement will never return those entries that have z=NULL. So, I think the problem is solved if we modify the prepared statement in the following way:

SELECT * FROM SRT WHERE X<=? AND X>=? AND Y LIKE ? AND Z<=? AND Z>=? OR Z IS NULL

Note also that the columns X and Y will never be NULL. One the one hand because X is a primary key for SRT and because of #3 in the proposal.

[alphaville]

Plan B:
Reserve a pseudo-NULL for EVERY entry and every foreign key! Retain the idea about @whatever and maybe introduce an abstract method in Component (just for convenience) :

public abstract T searchPrototype();

which returns a T (subclass of component) with all field values set to the corresponding WhatEver-value :-)

Allow for NULL foreign key values but on startup create the corresponding pseudo-nulls and write them in the database (These should be never be deleted unless one invokes the method DeciBell.reset() )...

Well we have more way to go to the next milestone...