Thursday, 4 January 2007

Manifestation type

You may or may not have heard of it, SQLite has them, but what is a manifestation type (I'll call them mtypes from now on)?

You can think of an mtype as a untyped variable with a twist. That is you can assign anything you want to it without having to first declare its type OR size. But first lets look at a typed variable example using the C programming language. In C if you want to store a string you will need to declare a fixed char array or create a char pointer to some malloc'd memory cast as char:

// fixed length array
char str[28];

or

// malloc'd fixed length array
char *str;
str = (char)malloc(sizeof(char)*28);

You need to know ahead of time how much space you will need to store a string. If your string was longer than 28 characters you would lose everything after character 28, safely off course by being sure not to write more than 28 bytes ;) This is very much the same principle in the database world, you must know the type and size before hand of a column.

Now lets use PHPs untyped variables to show an example of a more mtype way of doing things:

$str = "Hello World!\n"

and

$str .= "from Andrew Roses blog!\n";

The first line of code created the variable without being told what type it is or how big it is or is going to be and stored the string, the second line adds more to it (showing how there's no need to specify size). Now, watch this!

$str = 42;

Try doing something like that in C ;) What we have done it assigned what was once a string an integer.

Lets move to the database world now with the examples before in mind. First lets create a table:

create table test (
id int,
name varchar(32)
);

Now if we insert some data which sticks to the types(I'm using set in insert so things are clearer):

insert into test set id = 42, name = "Andrew Rose";*

This works, because we stuck to the types, but if we try this instead:

insert into test set id = "Andrew Rose", name = 42;*

If your DBMS does not support mtypes id will become 0 and name will be "42", but not the number 42, but the string. Most DBMSs will convert numeric to string and vica-versa. id became 0 because it has no logical numeric value.

A DBMS that supports mtypes would have ended up putting "Andrew Rose" in id and 42 most likely as a string in id, but possibly as an integer.

In fact in SQLite the table we created can be created without declaring the types at all:

create table test(
id,
name
);

So are mtypes good or evil? That all depends on the user I believe.

*SQLite does not support the set in insert syntax.

No comments: