qcubed table per subclass
Warmest Greetings to everyone,
I am new to qcubed and I am trying to design an application for inventory management based on a table per subclass OO design. In more detail, the possible transactions have very different attributes based on their type. Attributes are fixed for each type and types are NOT manageable at all (3 of them only). The design of the db I thought is the following: (please notice that the ID in each sub-class table IS the FK value to the parent table in the hierarchy).
CREATE TABLE IF NOT EXISTS `transaction_types` (
`transaction_type_id` int(11) NOT NULL auto_increment,
`transaction_type_name` varchar(64) NOT NULL default '',
PRIMARY KEY (`transaction_type_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `inventory_transactions` (
`transaction_id` int(11) NOT NULL auto_increment,
`products_id` int(11) NOT NULL,
`quantity` float NOT NULL,
`transaction_type_id` int(11) NOT NULL,
`transaction_date` datetime NOT NULL,
PRIMARY KEY (`transaction_id`),
FOREIGN KEY (`products_id`) REFERENCES `products`(`products_id`),
FOREIGN KEY (`transaction_type_id`) REFERENCES `transaction_types`(`transaction_type_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `import` (
`transaction_id` int(11) NOT NULL,
`supplier_id` int(11) default NULL,
`invoice` varchar(255) default NULL,
PRIMARY KEY (`transaction_id`),
FOREIGN KEY (`transaction_id`) REFERENCES `inventory_transactions`(`transaction_id`) ON DELETE CASCADE,
FOREIGN KEY (`supplier_id`) REFERENCES `suppliers`(`supplier_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `move` (
`transaction_id` int(11) NOT NULL,
`current_location_id` int(11) NOT NULL,
`next_location_id` int(11) NOT NULL,
PRIMARY KEY (`transaction_id`),
FOREIGN KEY (`transaction_id`) REFERENCES `inventory_transactions`(`transaction_id`) ON DELETE CASCADE,
FOREIGN KEY (`current_location_id`) REFERENCES `locations`(`location_id`),
FOREIGN KEY (`next_location_id`) REFERENCES `locations`(`location_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `destruction` (
`transaction_id` int(11) NOT NULL,
`location_id` int(11) NOT NULL,
`protocol_number` varchar(32) NOT NULL default '',
PRIMARY KEY (`transaction_id`),
FOREIGN KEY (`transaction_id`) REFERENCES `inventory_transactions`(`transaction_id`) ON DELETE CASCADE,
FOREIGN KEY (`location_id`) REFERENCES `locations`(`location_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
My problem is that I don't know how to move on from here. Normally there should be a form to create/edit transactions, where, based on the selected transaction_type (`transaction_types` tbl has to be ENUM of some kind ???), the subclass form should appear on the same screen. Or am I getting the whole thing wrong ?
Will the system manage all CRUD operations or not ?
Any help/suggestions/guidelines on how to proceed would be so welcomed.
I thank you all so much in advance for your time
Ioannis
Hello Ioannis,
When you run the code generator QCubed does not create sub-classes on the foreign keys. In other words QCubed dos not support table inheritance. It does, though, represent the table relationships in form of class members and aiding class methods. In your case e.g.: The class Import will contain a member Transaction. That class will also have a static method that will return all Imports of a certain transaction_id (likely Import::LoadByTransactionId).
What QCubed also does is creating form drafts for each table. That form basically supports the CRUD actions.
An easy solution in your case could be taking these drafts copying them to an appropriate location and modifying them. You can add a selection at the top that represents the transaction types. On an onchange server action you load the corresponding draft page.
For your transaction types it might be worth looking into 'type tables':
http://examples.qcu.be/assets/_core/php/examples/more_codegen/type_table...
I hope that helps a bit
Helge
Dear Helge,
I cannot thank you enough for your time, help and explanation. Your note that QCubed does not support table inheritance cleared many questions of mine.
Ok, so I dived into the code to see what it does and (unfortunately) I have more questions than before, so again I ask for your guidance.
When I loaded the db and code gen it I came up with this form, regarding InventoryTransactions :
Firstly, I was puzzled regarding the 3 last 3 listboxes. I did not expect to see them because the FK relationship is on the "wrong" side....Then looking into the code I understood they must be unique (?) reverse relationships and the system has generated properties on the reverse side of the relationship. Yet, they are still LOGICALLY WRONG! They should not be there ! And that is because the subclasses should not be selectable since they share the primary key with their parent class. In other words, the instantiation of the parent object defines uniquely one of the three child classes by giving them its primary key as their primary key.
Is it a bug of the system that needs to be corrected ? I guess it should understand that the FK is also the PK and thus not try to create reverse properties. Am I right or am I missing something ?
Lastly I found the code in the gen files that saves the object but was not able to understand what it does:
public function SaveInventoryTransactions() {
try {
// Update any fields for controls that have been created
//...
// Update any UniqueReverseReferences (if any) for controls that have been created for it
if ($this->lstDestruction) $this->objInventoryTransactions->Destruction = Destruction::Load($this->lstDestruction->SelectedValue);
if ($this->lstImport) $this->objInventoryTransactions->Import = Import::Load($this->lstImport->SelectedValue);
if ($this->lstMove) $this->objInventoryTransactions->Move = Move::Load($this->lstMove->SelectedValue);
// Save the InventoryTransactions object
$this->objInventoryTransactions->Save();
I guess I have to take it from the start and create all the forms by myself, right ? Or is there an alternative ?
So many thanks again
Ioannis
Hello Ioannis,
Yes, I had to digest that with the list controls myself and I am not quite sure what the logic behind that is, but, the reason that they appear is that you named the primary keys in move, destruction and import the same way as in inventory_transaction (inventory_id). These tables will likely need their 'own' primary key. If you give the primary key field a different name like destruction_id in table destruction you will see that these lists will disappear after the codegen. As I said, I am not quite sure what the logic behind this behaviour is, but there could be a good reason I do not see at the moment.
Hope I am talking sense ;-)
Cheers
Helge
P.S.: Happy to be of help. I found the idea of having table inheritance quite intriguing actually. :-)
Ioannis, please take note of Helge's advice to copy the "drafts" to another folder, which you've created, and do your modifications there. This is because if you need to rerun codegen, all the drafts are regenerated, even if the table had no modifications, thus wiping out any changes you might have made.
Also note, the metacontrol and model folders have sub-folders named generated. The classes in the "generated" folder are overwritten each time you run a codegen. So, for example, if you have a table named Transactions, you'll find a class file in model/generated named transactionsgen.class.php. In the model folder, you'll find a corresponding file named transactions.class.php. Make your modifications to the transactions.class.php class located in the model folder. The same holds true for metacontrols.
Good luck. I hope you have as great an experience as I have. QCubed is a great framework!
Dear Helge,
Greetings and so many thanks again,
I followed your advice and changed the 3 primary keys of the 3 tables like this:
CREATE TABLE IF NOT EXISTS `inventory_transactions` (
`transaction_id` int(11) NOT NULL auto_increment,
`products_id` int(11) NOT NULL,
`quantity` float NOT NULL,
`transaction_type_id` int(11) NOT NULL,
`transaction_date` datetime NOT NULL,
PRIMARY KEY (`transaction_id`),
FOREIGN KEY (`products_id`) REFERENCES `products`(`products_id`),
FOREIGN KEY (`transaction_type_id`) REFERENCES `transaction_types`(`transaction_type_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `import` (
`import_id` int(11) NOT NULL,
`invoice` varchar(255) default NULL,
PRIMARY KEY (`import_id`),
FOREIGN KEY (`import_id`) REFERENCES `inventory_transactions`(`transaction_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
...the same with the move (move_id) and destruction (destruction_id)
The 3 ListBoxes KEEP APPEARING even after doing code gen in a fresh system.
Any ideas ?
Many many thanks again for your time and help
Ioannis
Hello Ioannis,
Happy to help :-) Have you checked on the generated drafts? When I congen here the controls disappear.
Cheers
Helge
Basically this boils down to the fact that there may or may not be a corresponding record in those child tables, so we build a dropdown to show that status. This may not be the most useful GUI for it, perhaps having an add/edit link instead would be better.
Either way, you can edit the codegen template files in order to accomplish any GUI you want for this relationship.
Good morning Helge,
It has been a while but I was too busy doing things that might earn me some money :-) ...I am back now with many news.
Ok, lets go for it:
I did some debugging in qcubed\_core\codegen\QDatabaseCodeGen.class.php
and it seems that the same name is NOT the reason behind the infamous lists but something else and is very interesting:
Lines 930-940 on this file are (modified a little by me):
<?php
//--IA--
$this->strErrors .= sprintf("Analyzing REVERSE reference key name %s and property name %s \r\n", $objReverseReference->KeyName, $objReverseReference->PropertyName);
$this->strErrors .= sprintf("Analyzing REVERSE reference variable name %s \r\n", $objReverseReference->VariableName);
// For Special Case ReverseReferences, calculate Associated MemberVariableName and PropertyName...
// See if ReverseReference is due to an ORM-based Class Inheritence Chain
if ((count($objTable->PrimaryKeyColumnArray) == 1) && ($objColumn->PrimaryKey)) {
//--IA--
$this->strErrors .= sprintf("Analyzing ORM-based Class Inheritence Chain with property name %s \r\n", $objReverseReference->VariableType);
?>
The line with the comment ORM-based Class Inheritence Chain is the one that is producing the reverse reference. Lets see what the code generator outputs:
---------------------------------------------------------------------------
Getting Foreign keys for table destruction
strColumnName is destruction_id . Creating object objColumn
Checking to see if table inventory_transactions being referenced actually exists
Yeap, it exists...creating new reference object
Analyzing reference key name destruction_ibfk_1 and referenced table name inventory_transactions
Analyzing REVERSE reference key name destruction_ibfk_1 and property name DestructionId
Analyzing REVERSE reference variable name objDestruction
Analyzing ORM-based Class Inheritence Chain with property name Destruction
strColumnName is location_id . Creating object objColumn
Checking to see if table locations being referenced actually exists
Yeap, it exists...creating new reference object
Analyzing reference key name destruction_ibfk_2 and referenced table name locations
Analyzing REVERSE reference key name destruction_ibfk_2 and property name LocationId
Analyzing REVERSE reference variable name objDestruction
Getting Foreign keys for table import
strColumnName is import_id . Creating object objColumn
Checking to see if table inventory_transactions being referenced actually exists
Yeap, it exists...creating new reference object
Analyzing reference key name import_ibfk_1 and referenced table name inventory_transactions
Analyzing REVERSE reference key name import_ibfk_1 and property name ImportId
Analyzing REVERSE reference variable name objImport
Analyzing ORM-based Class Inheritence Chain with property name Import
Getting Foreign keys for table inventory_transactions
strColumnName is products_id . Creating object objColumn
Checking to see if table products being referenced actually exists
Yeap, it exists...creating new reference object
Analyzing reference key name inventory_transactions_ibfk_1 and referenced table name products
Analyzing REVERSE reference key name inventory_transactions_ibfk_1 and property name ProductsId
Analyzing REVERSE reference variable name objInventoryTransactions
strColumnName is transaction_type_id . Creating object objColumn
Checking to see if table transaction_types being referenced actually exists
Yeap, it exists...creating new reference object
Analyzing reference key name inventory_transactions_ibfk_2 and referenced table name transaction_types
Analyzing REVERSE reference key name inventory_transactions_ibfk_2 and property name TransactionTypeId
Analyzing REVERSE reference variable name objInventoryTransactions
Getting Foreign keys for table locations
Getting Foreign keys for table move
strColumnName is move_id . Creating object objColumn
Checking to see if table inventory_transactions being referenced actually exists
Yeap, it exists...creating new reference object
Analyzing reference key name move_ibfk_1 and referenced table name inventory_transactions
Analyzing REVERSE reference key name move_ibfk_1 and property name MoveId
Analyzing REVERSE reference variable name objMove
Analyzing ORM-based Class Inheritence Chain with property name Move
strColumnName is current_location_id . Creating object objColumn
Checking to see if table locations being referenced actually exists
Yeap, it exists...creating new reference object
Analyzing reference key name move_ibfk_2 and referenced table name locations
Analyzing REVERSE reference key name move_ibfk_2 and property name CurrentLocationId
Analyzing REVERSE reference variable name objMove
strColumnName is next_location_id . Creating object objColumn
Checking to see if table locations being referenced actually exists
Yeap, it exists...creating new reference object
Analyzing reference key name move_ibfk_3 and referenced table name locations
Analyzing REVERSE reference key name move_ibfk_3 and property name NextLocationId
Analyzing REVERSE reference variable name objMove
Getting Foreign keys for table products
Getting Foreign keys for table transaction_types
------[---------------------------------------------------------------------
It seems that somehow the system is taking into account this class inheritance.
I am writing this here just for educational purposes now. I will modify the files to load in a QPanel the correct type based on the type of the transaction.
It might be a good idea, since the system has taken this into account to modify it to appear correctly.
Do you know who manages the contributions/proposed changes ?
Many thanks to all for your time and help in this.
This has become a very interesting discussion....thanx again