Home » RADICORE development » Transaction Patterns » Virtual Private Database and List 2 pattern (Entries from child table not visible)
| Virtual Private Database and List 2 pattern [message #4194] |
Mon, 02 June 2014 10:48  |
htManager
Messages: 439
Registered: May 2014
|
Senior Member |
|
|
Hi Tony,
I am struggling with the implementation of the virtual private database system as you discribed in Implementing Virtual Private Databases and I have problems with a list 2 pattern.
I also read your article Technical Keys - their Uses and Abuses and I followed your proposal to avoid technical keys when possible.
I created tables with up to six or seven columns as Primary key. I don't know if this is the best way but all the tables are related in a one to many relation. (Cascading)
I mention this because the first colomn of these tables is always the field rdcaccount_id which is used for account access or shared access.
You described how to deal with a list 1 pattern and this works fine in my application, I see all the occourences made by a special account.
But if I want to see an account occurence in a list 2 form, I only see the entries of the shared account.
I inserted the code in the _getData() function after the code for checking the primary key:
if ($this->checkPrimaryKey AND !$this->allow_empty_where) {
// check that full primary key (or candidate key) has been supplied
I also tried inserting the code at the end of the function - same result.
So please, can you tell me where to insert the code at the right place?
Best regards,
Juergen
|
|
|
|
|
|
| Re: Virtual Private Database and List 2 pattern [message #4196 is a reply to message #4195] |
Tue, 03 June 2014 05:42  |
htManager
Messages: 439
Registered: May 2014
|
Senior Member |
|
|
Here they are.
Parent table:
<?php
// file created on June 2, 2014, 12:22 pm
// field specifications for table htm.htm_db_vereine
$fieldspec['rdcaccount_id'] = array('type' => 'integer',
'type_native' => 'int',
'size' => 10,
'minvalue' => 0,
'maxvalue' => 4294967295,
'pkey' => 'y',
'required' => 'y',
'default' => '1',
'nodisplay' => 'y',
'nosearch' => 'y');
$fieldspec['verbaende_art_id'] = array('type' => 'string',
'size' => 5,
'pkey' => 'y',
'required' => 'y',
'default' => 'LV',
'uppercase' => 'y',
'control' => 'dropdown',
'optionlist' => 'verbaende_art_id');
$fieldspec['verbaende_kuerzel'] = array('type' => 'string',
'size' => 10,
'pkey' => 'y',
'required' => 'y',
'uppercase' => 'y',
'control' => 'dropdown',
'optionlist' => 'verbaende_kuerzel');
$fieldspec['vereine_kuerzel'] = array('type' => 'string',
'size' => 20,
'pkey' => 'y',
'required' => 'y',
'uppercase' => 'y');
$fieldspec['node_id'] = array('type' => 'integer',
'type_native' => 'int',
'size' => 5,
'minvalue' => 0,
'maxvalue' => 4294967295,
'default' => '0');
$fieldspec['vereine_name'] = array('type' => 'string',
'size' => 255,
'required' => 'y');
$fieldspec['vereine_name_zusatz'] = array('type' => 'string',
'size' => 255);
$fieldspec['vereine_strasse'] = array('type' => 'string',
'size' => 255);
$fieldspec['vereine_lkz'] = array('type' => 'string',
'size' => 10);
$fieldspec['vereine_plz'] = array('type' => 'string',
'size' => 10);
$fieldspec['vereine_ort'] = array('type' => 'string',
'size' => 100);
$fieldspec['vereine_telefon'] = array('type' => 'string',
'size' => 30);
$fieldspec['vereine_fax'] = array('type' => 'string',
'size' => 30);
$fieldspec['vereine_email'] = array('type' => 'string',
'size' => 100);
$fieldspec['vereine_website'] = array('type' => 'string',
'size' => 255);
$fieldspec['created_date'] = array('type' => 'datetime',
'size' => 20,
'required' => 'y',
'default' => '2014-01-01 00:00:00',
'autoinsert' => 'y',
'noedit' => 'y',
'nosearch' => 'y');
$fieldspec['created_user'] = array('type' => 'string',
'size' => 16,
'required' => 'y',
'autoinsert' => 'y',
'noedit' => 'y',
'nosearch' => 'y');
$fieldspec['revised_date'] = array('type' => 'datetime',
'size' => 20,
'autoupdate' => 'y',
'noedit' => 'y',
'nosearch' => 'y');
$fieldspec['revised_user'] = array('type' => 'string',
'size' => 16,
'autoupdate' => 'y',
'noedit' => 'y',
'nosearch' => 'y');
// primary key details
$this->primary_key = array('rdcaccount_id',
'verbaende_art_id',
'verbaende_kuerzel',
'vereine_kuerzel');
// unique key details
$this->unique_keys = array();
// child relationship details
$this->child_relations[] = array('child' => 'htm_db_saisons',
'type' => 'RES',
'fields' => array('rdcaccount_id' => 'rdcaccount_id',
'verbaende_art_id' => 'verbaende_art_id',
'verbaende_kuerzel' => 'verbaende_kuerzel',
'vereine_kuerzel' => 'vereine_kuerzel'));
// parent relationship details
$this->parent_relations[] = array('parent' => 'htm_db_verbaende',
'parent_field' => 'verbaende_name',
'fields' => array('rdcaccount_id' => 'rdcaccount_id',
'verbaende_art_id' => 'verbaende_art_id',
'verbaende_kuerzel' => 'verbaende_kuerzel'));
$this->parent_relations[] = array('parent' => 'mnu_account',
'dbname' => 'menu',
'subsys_dir' => 'menu',
'parent_field' => 'rdcaccount_id',
'fields' => array('rdcaccount_id' => 'rdcaccount_id'));
// determines if database updates are recorded in an audit log
$this->audit_logging = TRUE;
// default sort sequence
$this->default_orderby = '';
// alternative language options
$this->alt_language_table = '';
$this->alt_language_cols = '';
// alias names
$this->nameof_start_date = '';
$this->nameof_end_date = '';
// finished
?>
Child table:
<?php
// file created on June 1, 2014, 11:59 am
// field specifications for table htm.htm_db_saisons
$fieldspec['rdcaccount_id'] = array('type' => 'integer',
'type_native' => 'int',
'size' => 10,
'minvalue' => 0,
'maxvalue' => 4294967295,
'pkey' => 'y',
'required' => 'y',
'default' => '1',
'nodisplay' => 'y',
'nosearch' => 'y');
$fieldspec['verbaende_art_id'] = array('type' => 'string',
'size' => 5,
'pkey' => 'y',
'required' => 'y',
'uppercase' => 'y');
$fieldspec['verbaende_kuerzel'] = array('type' => 'string',
'size' => 10,
'pkey' => 'y',
'required' => 'y',
'uppercase' => 'y');
$fieldspec['vereine_kuerzel'] = array('type' => 'string',
'size' => 20,
'pkey' => 'y',
'required' => 'y',
'uppercase' => 'y');
$fieldspec['saison_id'] = array('type' => 'string',
'size' => 10,
'pkey' => 'y',
'required' => 'y');
$fieldspec['saison_start'] = array('type' => 'date',
'size' => 12,
'required' => 'y',
'default' => '2014-07-01');
$fieldspec['saison_ende'] = array('type' => 'date',
'size' => 12,
'required' => 'y',
'default' => '2015-06-30');
$fieldspec['created_date'] = array('type' => 'datetime',
'size' => 20,
'required' => 'y',
'default' => '2014-01-01 00:00:00',
'autoinsert' => 'y',
'noedit' => 'y',
'nosearch' => 'y');
$fieldspec['created_user'] = array('type' => 'string',
'size' => 16,
'required' => 'y',
'autoinsert' => 'y',
'noedit' => 'y',
'nosearch' => 'y');
$fieldspec['revised_date'] = array('type' => 'datetime',
'size' => 20,
'autoupdate' => 'y',
'noedit' => 'y',
'nosearch' => 'y');
$fieldspec['revised_user'] = array('type' => 'string',
'size' => 16,
'autoupdate' => 'y',
'noedit' => 'y',
'nosearch' => 'y');
// primary key details
$this->primary_key = array('rdcaccount_id',
'saison_id',
'verbaende_art_id',
'verbaende_kuerzel',
'vereine_kuerzel');
// unique key details
$this->unique_keys = array();
// child relationship details
$this->child_relations[] = array('child' => 'htm_db_mannschaften',
'type' => 'RES',
'fields' => array('rdcaccount_id' => 'rdcaccount_id',
'saison_id' => 'saison_id',
'verbaende_art_id' => 'verbaende_art_id',
'verbaende_kuerzel' => 'verbaende_kuerzel',
'vereine_kuerzel' => 'vereine_kuerzel'));
// parent relationship details
$this->parent_relations[] = array('parent' => 'htm_db_vereine',
'parent_field' => 'vereine_name',
'fields' => array('rdcaccount_id' => 'rdcaccount_id',
'verbaende_art_id' => 'verbaende_art_id',
'verbaende_kuerzel' => 'verbaende_kuerzel',
'vereine_kuerzel' => 'vereine_kuerzel'));
$this->parent_relations[] = array('parent' => 'mnu_account',
'dbname' => 'menu',
'subsys_dir' => 'menu',
'parent_field' => 'rdcaccount_id',
'fields' => array('rdcaccount_id' => 'rdcaccount_id'));
// determines if database updates are recorded in an audit log
$this->audit_logging = TRUE;
// default sort sequence
$this->default_orderby = '';
// alternative language options
$this->alt_language_table = '';
$this->alt_language_cols = '';
// alias names
$this->nameof_start_date = '';
$this->nameof_end_date = '';
// finished
?>
|
|
|
|
|
|
| Re: Virtual Private Database and List 2 pattern [message #4198 is a reply to message #4197] |
Tue, 03 June 2014 08:33 |
htManager
Messages: 439
Registered: May 2014
|
Senior Member |
|
|
Sorry, do you mean the table structure? I sometimes do not know the exact meaning in german.
I also had some problems with difficult character sets, so I changed all character sets to utf8_unicode_ci.
Here is the SQL statement:
CREATE TABLE IF NOT EXISTS `htm_db_saisons` (
`rdcaccount_id` int(10) unsigned NOT NULL DEFAULT '1',
`verbaende_art_id` varchar(5) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`verbaende_kuerzel` varchar(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`vereine_kuerzel` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`saison_id` varchar(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`saison_start` date NOT NULL DEFAULT '2014-07-01',
`saison_ende` date NOT NULL DEFAULT '2015-06-30',
`created_date` datetime NOT NULL DEFAULT '2014-01-01 00:00:00',
`created_user` varchar(16) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`revised_date` datetime DEFAULT NULL,
`revised_user` varchar(16) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (`rdcaccount_id`,`verbaende_art_id`,`verbaende_kuerzel`,`ver eine_kuerzel`,`saison_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- --------------------------------------------------------
--
-- Tabellenstruktur für Tabelle `htm_db_vereine`
--
CREATE TABLE IF NOT EXISTS `htm_db_vereine` (
`rdcaccount_id` int(10) unsigned NOT NULL DEFAULT '1',
`verbaende_art_id` varchar(5) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'LV',
`verbaende_kuerzel` varchar(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`vereine_kuerzel` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`node_id` int(5) unsigned DEFAULT '0',
`vereine_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`vereine_name_zusatz` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
`vereine_strasse` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
`vereine_lkz` varchar(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
`vereine_plz` varchar(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
`vereine_ort` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
`vereine_telefon` varchar(30) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
`vereine_fax` varchar(30) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
`vereine_email` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
`vereine_website` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
`created_date` datetime NOT NULL DEFAULT '2014-01-01 00:00:00',
`created_user` varchar(16) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`revised_date` datetime DEFAULT NULL,
`revised_user` varchar(16) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (`rdcaccount_id`,`verbaende_art_id`,`verbaende_kuerzel`,`ver eine_kuerzel`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
|
|
|
|
|
|
|
|
| Re: Virtual Private Database and List 2 pattern [message #4201 is a reply to message #4200] |
Tue, 03 June 2014 10:54 |
AJM
Messages: 2371
Registered: April 2006
Location: Surrey, UK
|
Senior Member |
|
|
The number of columns in a primary or foreign key should be irrelevant.
You said in your original post that you inserted the code in the _getData() function, but this should not be necessary. The primary key of the parent table is automatically passed down to the child table so that it can retrieve only those records which belong to the parent record. The framework knows from the table's structure if the column 'rdcaccount_id' exists or not, and so deals with it automatically.
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|
|
|
|
|
|
| Re: Virtual Private Database and List 2 pattern [message #4204 is a reply to message #4203] |
Tue, 03 June 2014 11:32 |
AJM
Messages: 2371
Registered: April 2006
Location: Surrey, UK
|
Senior Member |
|
|
The tutorial does not show the code that *you* have to change in the framework as that code is already there. All you have to do is include a column called 'rdcaccount_id' in your table structure and the framework will do the rest.
When the value for rdcaccount_id is '1' this indicates the shared account which will be viewed by everybody. When the value is greater than '1' you will see those records for that account plus the shared account.
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|
|
|
|
|
|
|
|
| Re: Virtual Private Database and List 2 pattern [message #4210 is a reply to message #4209] |
Wed, 04 June 2014 05:35 |
AJM
Messages: 2371
Registered: April 2006
Location: Surrey, UK
|
Senior Member |
|
|
I am not seeing this at all in the VPD application which I set up.
It has the following users:
VPD1 with rdcaccount_id = 1
VPD2 with rdcaccount_id = 2
VPD4 with rdcaccount_id = 3
If I logon as VPD1 and go to the 'List X Option' screen I can only see those options with rdcaccount_id = 1.
With VPD2 I see records with rdcaccount_id = 1 or 2.
With VPD3 I see records with rdcaccount_id = 1 or 3.
If I then go to the 'List Person by Option' screen (which is a List2 screen) then I see what I expect to see:
VPD1 - records with rdcaccount_id = 1
VPD2 - records with rdcaccount_id = 1 or 2
VPD3 - records with rdcaccount_id = 1 or 3
You should be able to duplicate this for yourself with the copy that I sent you.
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|
|
|
|
| Re: Virtual Private Database and List 2 pattern [message #4212 is a reply to message #4211] |
Wed, 04 June 2014 13:01 |
AJM
Messages: 2371
Registered: April 2006
Location: Surrey, UK
|
Senior Member |
|
|
I have created the two tables from the schema which you sent, created the transactions to maintain those tables and then created some test data.
I have created data on the htm_db_vereine (parent) table using username VPD1 and VPD2. Then I created entries on the htm_db_saisons table (child) also using username VPD1 and VPD2.
If I logon as VPD1 I can only see the entries for account #1.
If I logon as VPD2 I can see the entries for accounts #1 and #2.
I think the problem you are having is because you created a parent entry with account #1 and then added a child entry using account #2. Although the framework allowed this, it is technically invalid because the column rdcaccount_id on the parent table is part of the primary key, which means that all child entries MUST have the same value for rdcaccount_id.
The way that I got around this is to take rdcaccount_id out of the primary key for the parent table but leave it in the table.
If you read the documentation you will see that after adding the rdcaccount_id column to a table you may add it to the primary key or leave it as a non-key field. If it is part of the primary key then all child entries MUST have the same value for rdcaccount_id. If it is not part of the primary key then the child table can contain entries with different values for rdcaccount_id.
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Thu Dec 12 08:28:53 EST 2024
Total time taken to generate the page: 0.01600 seconds
|
Radicore Forum
Search
Help
Members
Register
Login
Home
