SchemaGenerator additional VARCHAR field, why?

Question

When generating a table from this model:

function init()
{
    parent::init();
    $this->addField('person_id')->refModel('Model_Person')->mandatory(true);
    $this->addField('username')->mandatory(true);       
    $this->addField('password')->mandatory(true);       
}

I get this SQL statement:

create table users (     
    id int auto_increment not null primary key,
    person_id varchar(255),
    person int(11),
    username varchar(255),
    password varchar(255));

In this SQL statement i get the opposite of what is said in the tutorial:

Calling refModel with a field name ending in "_id" will actually create 2 field definitions. "publisher_id", for instance, will be defined as integer and will have type "reference", and a field "publisher" will also be added, with exactly same properties - but it will be a calculated field and will use sub-select to determine the value.

I want to know:

1. Is the generated SQL statement correct?
2. What does this VARCHAR additional generated field do? (I made CRUD and when added new records, the value of this field was saved as NULL).
3. When using refModel(), if i used the model name only ('Person') i got an error (Unable to include Person.php), i had to use the complete class name ('Model_Person'). Is this ok? shouldn't i be able to use the model name only?
4. The mandatory() doesn't use NOT NULL, is there way to do this?

Answer 1

The generated SQL statement is not correct. It's a bug in generator. You need just one field o type "int" ending with the _id.

The reason why it does this, is because refModel() actually creates two fields in the model, one of which is used for editing (_id) and other is used for listing data (as a sub-query)

When you use refModel, you should use "Model_Person". The consistency between refModel, setModel and other fields will be improved in 4.2, it's not done due to compatibility reasons.

The SQL Generator by it's nature is incomplete and it can't be complete, so it's better that schema is reviewed anyway. For instance you might have some fields which are not defined in "Model". Also I prefer that developers pay attention to SQL as it might not reflects models precisely, one model may use multiple tables through join or models may inherit each then add more field definitions there.

mandatory() is a model-level requirement which works similarly to other validations. While MySQL could handle the "mandatory" condition, it wouldn't be able to handle others. Besides, you may remove "mandatory" when you inherit models.

I'll try to add a guide on effective use of Models in Agile Toolkit.