pntables.php and dynamic columns :: Zikula Community

Try Zikula 1.3.1!New core, modules & more

Download Zikula!Version 1.2.8 (stable)

pntables.php and dynamic columns

Print topic

ph

Rank: Team Member

Registered: Jul 21, 2007

Last visit: May 24, 2010

Posts: 67
Posted: Aug 23, 2007 - 01:39 AM

hi,
i am currently working on a module, where i want to create tables and columns dynamically. for the tables this is now problem, because i can define them in pntables.php. but i also need to create columns, that i need to append to the pntable['tbl_column'] and pntable['tbl_column_def'] arrays in pntables.php.
i already searched the forums and found a few hints, but actually nothing that i tried really worked.
first i tried to call an API function from within pntables.php, but since i need a mysql select, when using DBUtil it gives me an error. i think the reason is, because DBUtil itself needs/depends on pntables.php, and therefore ends in some kind of loop.
so i tried to use DBUtil:metaColumns, since this seemed more promising, but again - it didnt work.
so now im stuck.is there an easy way to dynamically generate the necessary information and append it to the pntable array?

so this is what i currently have:
for my defense: its my first real PHP project, and i pray to the PN devs for this wonderfully easy API :)

Code
function pnAddressBook_pntables()
{
// Initialise table array
$pntable = array();

// Full table definition
$pntable['pnaddressbook_address'] = DBUtil::getLimitedTablename('pnaddressbook_address');
$pntable['pnaddressbook_address_column'] = array('adrid' => 'adr_id',
'adrprefix' => 'adr_prefix',
'adrname' => 'adr_name',
'adrfname' => 'adr_fname',
'adrsortname' => 'adr_sortname',
'adrtitle' => 'adr_title',
'adrcompany' => 'adr_company',
'adrsortcompany' => 'adr_sortcompany',
'adrimg' => 'adr_img',
'adrzip' => 'adr_zip',
'adrcity' => 'adr_city',
    'adraddress1' => 'adr_address1',
'adraddress2' => 'adr_address2',
'adrstate'   => 'adr_state',
'adrcountry' => 'adr_country',
/* 'adrcontact_1' => 'adr_contact_1',
'adrcontact_2' => 'adr_contact_2',
'adrcontact_3' => 'adr_contact_3',
'adrcontact_4' => 'adr_contact_4',
'adrcontact_5' => 'adr_contact_5',
'adrc_label_1' => 'adr_c_label_1',
'adrc_label_2' => 'adr_c_label_2',
'adrc_label_3' => 'adr_c_label_3',
'adrc_label_4' => 'adr_c_label_4',
'adrc_label_5' => 'adr_c_label_5',
*/ 'adrc_main' => 'adr_c_main',
/* 'adrcustom_1' => 'adr_custom_1',
'adrcustom_2' => 'adr_custom_2',
'adrcustom_3' => 'adr_custom_3',
'adrcustom_4' => 'adr_custom_4',
*/    'adrnote' => 'adr_note',
'adruser_id' => 'adr_user',
'adrluser_id' => 'adr_luser',
'adrprivate' => 'adr_private',
'adrdate' => 'adr_date');

$pntable['pnaddressbook_address_column_def'] = array('adrid' => "I(11) NOTNULL AUTOINCREMENT PRIMARY",
'adrprefix' => "I(11)",
'adrname' => "C(100) DEFAULT ''",
'adrfname' => "C(100) DEFAULT ''",
'adrsortname' => "C(200) DEFAULT ''",
'adrtitle' => "C(100) DEFAULT ''",
'adrcompany' => "C(255) DEFAULT ''",
'adrsortcompany' => "C(255) DEFAULT ''",
'adrimg' => "C(100) DEFAULT ''",
'adrzip' => "C(30) DEFAULT ''",
'adrcity' => "C(100) DEFAULT ''",
    'adraddress1' => "C(255) DEFAULT ''",
'adraddress2' => "C(255) DEFAULT ''",
'adrstate'   => "C(60) DEFAULT ''",
'adrcountry' => "C(60) DEFAULT ''",
/* 'adrcontact_1' => "C(200) DEFAULT ''",
'adrcontact_2' => "C(200) DEFAULT ''",
'adrcontact_3' => "C(200) DEFAULT ''",
'adrcontact_4' => "C(200) DEFAULT ''",
'adrcontact_5' => "C(200) DEFAULT ''",
'adrc_label_1' => "I(4) DEFAULT",
'adrc_label_2' => "I(4) DEFAULT",
'adrc_label_3' => "I(4) DEFAULT",
'adrc_label_4' => "I(4) DEFAULT",
'adrc_label_5' => "I(4) DEFAULT",
*/ 'adrc_main' => "I(4) DEFAULT",
/* 'adrcustom_1' => "C(200) DEFAULT ''",
'adrcustom_2' => "C(200) DEFAULT ''",
'adrcustom_3' => "C(200) DEFAULT ''",
'adrcustom_4' => "C(200) DEFAULT ''",
*/    'adrnote' => "X DEFAULT ''",
'adruser_id' => "I(11) NOTNULL",
'adrluser_id' => "I(11) NOTNULL",
'adrprivate' => "I(4) NOTNULL",
'adrdate' => "datetime");

// this is really no good solution i fear, but it might work.
// could also use array_push
$custs = DBUtil::metaColumns('pnaddressbook_address');

// $carray = array();
// $darray = array();
foreach ($custs as $cust) {
$tmp = get_object_vars($cust);

if (strpos($tmp['name'], 'custom') || strpos($tmp['name'], 'contact') || strpos($tmp['name'], 'label')) {
$tmp2 = preg_replace('/^adr_/', 'adr', $tmp['name']);
$pntable['pnaddressbook_address_column'][$tmp2] = $tmp['name'];

// needs to be adjusted a little more
if ($tmp['type'] == 'text') {
$pntable['pnaddressbook_address_column_def'][$tmp2] = $tmp['type'];
} else {
$pntable['pnaddressbook_address_column_def'][$tmp2] = "{$tmp['type']}({$tmp['max_length']})";
}
}
}

return $pntable;
}

thanks in advance!
philipp
Guite

Rank: Team Member

Registered: Jan 19, 2003

Last visit: Apr 06, 2010

Posts: 432
Posted: Aug 23, 2007 - 04:23 AM

One very easy approach for storing additional data for a particular table is taking usage from the attributes functionality DBUtil offers. You can enable attributes the same way like it goes for categorisation. Just have a look into ObjectUtil for some helpful methods.

Attributes are also helpful for developers to add data to the tables of other modules. So you can easily enhance the Users table for example...

--
Guite | ModuleStudio
ph

Rank: Team Member

Registered: Jul 21, 2007

Last visit: May 24, 2010

Posts: 67
Posted: Aug 27, 2007 - 06:59 AM

hi, thanks for your help!

i really tried to understand your explanation, but it seems harder than i thought :D
actually, im lost.
do you know of any module, which uses this 'technique' so i have an example to work with?
this would make life at lot easier for me :)

thx!
ph
Guite

Rank: Team Member

Registered: Jan 19, 2003

Last visit: Apr 06, 2010

Posts: 432
Posted: Aug 27, 2007 - 09:53 AM

Hi,

all you must do to enable attribution for a particular table is putting the following line into the pntables.php:

Code
$pntables['mytable_db_extra_enable_attribution'] = true;

To manage attributes for a table row ObjectUtil offers the following methods:

Code
$obj = ObjectUtil::expandObjectWithAttributes($obj, 'mytable', 'myidfield');
$obj['attributefield'] = 'newvalue';
ObjectUtil::storeObjectAttributes($obj, 'mytable', 'myidfield');

--
Guite | ModuleStudio
ctimmer

Rank: Softmore

Registered: Feb 07, 2003

Last visit: Jun 11, 2008

Posts: 225
Posted: Aug 29, 2007 - 12:48 PM

If you need truly dynamic data in a table row you could declare a column:

Code
dynam VARCHAR(1000),
...

and then populate it with XML formatted text defined as needed.

As an alternative you could populate 'dynam' with a serialized PHP array but then your data would always be PHP specific and may not survive a PHP version change.

Curt
ph

Rank: Team Member

Registered: Jul 21, 2007

Last visit: May 24, 2010

Posts: 67
Posted: Nov 07, 2007 - 02:08 AM

hi,
thank you both for your answers, and sorry for being so late :)

in the meantime i tried to achieve the above with attributes, as suggested by Guite. and i still have some problems there. though i am not sure if its the best solution.

the situation is the following.
i have a table pnaddressbook_address and pnaddressbook_customfields.
now if the admin creates a new customfield, it gets recorded in pnaddressbook_customfields and additionally i want to create a new field in pnaddressbook_address of the format 'custom_$id', where $id is the id from the entry in pnaddressbook_customfields.

so is there a way to declare those fields (from pnaddressbook_address) in the pntables.php somehow, or should i really use ObjectUtil::expandObjectWithAttributes where those information would instead be stored in objectdata_attributes (am i right here?)? i am asking this, because there would be a lot of data to store outside the module tables then. for every single addressbook entry in my case.

i hope you understand what i mean, if not then im sorry for not being able to explain myself

thanks again for your help!

regards
philipp the 2nd
rgasch

Rank: Team Member

Registered: Jan 05, 2003

Last visit: May 28, 2010

Posts: 766
Posted: Nov 07, 2007 - 06:32 AM

Quote
so is there a way to declare those fields (from pnaddressbook_address) in the pntables.php somehow, or should i really use ObjectUtil::expandObjectWithAttributes where those information would instead be stored in objectdata_attributes (am i right here?)?

IMHO you should use the Attribute system for this. It was designed to handle this type of stuff but reduce the required effort to some template changes. You can still add a system to let the user/admin define custom fields, but leave the task of storing the data to the attribution system.

Quote
i am asking this, because there would be a lot of data to store outside the module tables then. for every single addressbook entry in my case.

So what? Databases are designed to efficiently fetch data from millions of rows. This is nothing special. Your code will probably run faster if you hardcode your changes into your addressbook tables, but you will pay for it developer time. You must decide what is more important to you (I typically opt for minimizing developer time, which to me is more valuable than CPU time). Unless you have very special requirements, I would not be overly concerned by the fact that your attribute data will be stored in a different table ...

Greetings
R
ph

Rank: Team Member

Registered: Jul 21, 2007

Last visit: May 24, 2010

Posts: 67
Posted: Nov 07, 2007 - 11:28 PM

great, thanks!

well, i tried what Guite suggested, i put $pntables['pnaddressbook_address_db_extra_enable_attribution'] = true; in my pntables.php and wanted to try it out first, before 'fully implementing' it:

Code
function pnAddressBook_userapi_attr($args)
{

$obj = pnModAPIFunc('pnAddressBook', 'user', 'get', array('id' => $args['id']));

print_r($obj);

$obj = ObjectUtil::expandObjectWithAttributes($obj, 'pnaddressbook_address', 'id');

$obj['bla'] = 'test';

$obj = ObjectUtil::storeObjectAttributes($obj, 'pnaddressbook_address', 'id');

return true;
}

i get the following error:

Quote

Array ( [id] => 2 [prefix] => [name] => dsd [fname] => sds [sortname] => dsd, sds [title] => [company] => [sortcompany] => [img] => [zip] => [city] => [address1] => [address2] => [state] => [country] => [c_main] => [note] => [private] => 0 [obj_status] => A [cr_date] => 2007-11-08 10:48:10 [cr_uid] => 2 [lu_date] => 2007-11-08 10:48:10 [lu_uid] => 2 [__CATEGORIES__] => Array ( ) ) Exit-Handler: storeObjectAttributes: Unable to determine object ID ...
Stacktrace:

1. (array) 0:
1. (string) file = /var/www/localhost/htdocs/pn/includes/pnobjlib/ObjectUtil.class.php
2. (integer) line = 503
3. (string) function = pn_exit
4. (array) args:
1. (string) 0 = storeObjectAttributes: Unable to determine object ID ...
2. (array) 1:
1. (string) file = /var/www/localhost/htdocs/pn/modules/pnAddressBook/pnuserapi.php
2. (integer) line = 357
3. (string) function = storeObjectAttributes
4. (string) class = ObjectUtil
5. (string) type = ::
6. (array) args:
1. (array) 0:
1. (string) bla = test
2. (string) 1 = pnaddressbook_address
3. (string) 2 = id
3. (array) 2:
1. (string) file = /var/www/localhost/htdocs/pn/includes/pnMod.php
2. (integer) line = 631
3. (string) function = pnAddressBook_userapi_attr
4. (array) args:
1. (array) 0:
1. (string) id = 2
[...]

the object comes from DBUtil:selectObjectByID.
what am i doing wrong here?

thanks in advance!
ph
Topiatic

Rank: Expert

Registered: Nov 23, 2003

Last visit: Dec 13, 2009

Posts: 1487
Posted: Nov 08, 2007 - 01:08 AM

This one buggered me for a bit too... It seems the Object Attribute array is an associative array which stores the attributes in question under the key '__ATTRIBUTES__' (completely undocumented, I had to follow the code to figure it out). This is how I used it (the users modules pntables files allready has the added line for the users table).

Code
// checks stripped for brevity
function userObjID ($ObjID)
{
$usrObj['id'] = SessionUtil::getVar('uid');

if (!$ObjID) {
$objAttArr = ObjectUtil::expandObjectWithAttributes ($usrObj, 'users'); // third param defaults to 'id'
$ObjID = $objAttArr['__ATTRIBUTES__']['lastRMObjID']);
} else {
$usrObj['__ATTRIBUTES__']['lastRMObjID'] = $ObjID;
ObjectUtil::storeObjectAttributes($usrObj, 'users');
}
return $ObjID;
}

--
Under Construction!
ph

Rank: Team Member

Registered: Jul 21, 2007

Last visit: May 24, 2010

Posts: 67
Posted: Nov 08, 2007 - 01:16 AM

yay, thanks a lot!
you saved my day :)

greetings
philipp