Getting related records

One thing you'll want to do is to grab related records in Xataface. Say for example, you have an abstract record that belongs to a conference. In the relationships.ini file of your abstract table, you would include something like this:

[Conference]
conference.ConferenceID="$AbstractConferenceID"

Where the conference is the name of the conference table, ConferenceID is the name of the index of that table, and $AbstractConferenceID is the foreign ID in your abstract record that corresponds to the the particular record in the conference table. Note the name of the relationship is "Conference."

In Xataface, if you have an abstract record you can quickly retrieve the related conference by simply doing this:

$conference = $record->getRelatedRecord('Conference')

This will retrieve the conference as a xataface_related_record object. It is important to realize that this is not the same thing as a xataface_record. If you want to convert this related_record object into a xataface_record object you can use the toRecord() function:

$conference = $record->getRelatedRecord('Conference')->toRecord();

This is a powerful technique that can save a lot of time.

Xataface svn repository

One good thing you should do if you are planning to develop an application with Xataface is to actually checkout the svn repository of Xataface. The most recent code for Xataface which includes bug fixes and features will be added to this repository. I used tortoiseSVN as my svn client, and to checkout you simply use this address:

http://weblite.ca/svn/dataface/core/trunk

Keeping your database up to date throughout development

A major development issue in any computer project that requires a database is keeping your database structure keep to date with the different versions of the code. Keep database up to date is always a difficult because you cannot simply put it under version (thou technically you can because you can create an sql statement that represents the entire database). But Xataface provides an interesting way to handle this through the use of an Installer.php (make sure it's capital I) class in the conf folder.

The idea behind this is your application will be associated with a version number which you specify in the database. And then Xataface will run the Installer.php file which checks which version is in the database, and then it will call a bunch of functions that update the database table. The functions are named after the version number they should be applied to. So for example:

<?php
class conf_Installer {
function update_582(){
$sql[] = 'ALTER TABLE `webpage_sites` ADD `dynamic` TINYINT(1) AFTER `require_approval`';
$sql[] = 'ALTER TABLE `webpage_sites` ADD `irrelevant_get_params` TEXT NULL AFTER `aliases` , ADD `relevant_get_params` TEXT NULL AFTER `irrelevant_get_params`';
foreach ($sql as $q){
mysql_query($q, df_db());
}
}
?>

What happens is it will see if the database version is 582 or above and then it will apply this change to the database. The this general function format can be used for any version numbers.

To actually setup the database version, we don't actually need to setup a separate database table to handle this. We make a version.txt file in the application and xataface will create a version table in the database for us. The contents of the file will look like this:

0.1 680

The 0.1 really means nothing. It is simply used to note which major release we are on. It is the second number that means something. That is the exact version number that gets stored into the database.

And by setting up these two things, it becomes extremely easy to manage the database in xataface!

SVN committing

This isn't exactly a Xataface related issue, but I found myself continually forgetting about this that I thought it would be good to blog about this so I can remember. When I have ssh'ed onto the server and I need to do a svn commit, I get an error message about not having an editor to input my svn commit message. The following piece of code fixes this:

export EDITOR=vi

Where vi is the editor program we are using. If you have something else installed on your server (and hopefully you do because vi really blows), then you can just use that.

Update: You can actually avoid having to do this by adding that exact line into your .bash_profile file. This file gets executed upon log in and runs a set of commands. So if you do this, it will always just set your editor and you won't have to worry about it anymore.

Testing Xataface on IE6 when you have Vista

So I ran into a pretty annoying problem the other day. I had to test a Xataface application on IE6 with Vista as my OS. Which basically meant I had IE7 preinstalled, and I couldn't install IE6. Ughh...annoying. I don't understand why people still use IE6, but whatever.

The best way to actually shove this is to install a virtual machine with the OS set to XP and then you can use IE6 on it. To do this follow this tutorial.

One thing you need to note is that Virtual PC 2004 does NOT work on Vista. So you actually have to use Virtual PC (VPC) 2007 . This can be found here. The "Internet Explorer 6 Testing VPC Image" is still the same.

Another annoying thing is that VPC 2007 is NOT fully supported with Vista Home edition (UGHHH...why are things to complicated!). And you'll be faced with all these messages not non-compatibility. But you are just limited from some features which I didn't bother looking directly into. But if you just want to test Xataface sites on it, then just ignore the messages and install it.

After you have loaded the VPC image, then the next step is to actually setup internet connection from inside the VPC. If you are using a LAN connection, then do this:

1. Edit


2. Settings


3. Networking


4. Adaptor 1: NAT

Try to connect now, and if you have a firewall on then you will be prompted to allow the VPC to pass it. After you "unblock" it, then everything should work!

If you are using wireless, then use this tutorial.

Adding some text when no block/slot exists

Sometimes you need to add some text to a certain template, but Xataface doesn't have the specific block or slot to use. For example, say you wanted to include some text underneath the password field in the login page for Xataface. You can't do this with blocks or slots since nothing exists underneath that field. You can however do a workaround by using javascript and the DOM concept.

For example:

function block__after_login_form(){
<script language="javascript"><!--
var div = document.createElement('div');
div.className = 'formHelp';
var text = document.createTextNode('Please enter the correct password');
div.appendChild(text);

document.getElementById('Login-Password').appendChild(div);
//--></script>
}

First thing to notice is we are using the block__after_login_form() which simply a block on that template. We are using it to include our javascript of interest. We could have actually used any block.

var div = document.createElement('div');

The first thing we do is create a div element.

div.className = 'formHelp';

Here are just giving it the css class name of formHelp which gives it a nice styling feel. This is the same style used for widget:description values.

var text = document.createTextNode('Please enter the correct password');

And then we create a textnode which contains the text of interest.

div.appendChild(text);

With out div element, we are appending the text node we just created to it.

document.getElementById('Login-Password').appendChild(div);

And finally we add our div element to our element of interest which happens to have the id of "Login-Password" This id has been assigned by Xataface itself.

And if everything works out fine, then you should see the text appear below the password field!

You can find more information about how to manipulate DOM structure using javascript here.

Directly modifying a table's attributes

While using the fields.ini is a good way to set attributes such as widget:label, description, etc sometimes you need more control. This can be accomplished by loading the table in the code and then directly modifying the fields. For example:

$usersTable =& Dataface_Table::loadTable('users');
$myfield =& $usersTable->getField('password');
$myfield['widget']['description'] = "Hello world";

The first line of code is retrieving a reference to the users table which is a Xataface table object. The table object comes with some functions that allow you with. One of them being the getField() function which would then retrieve a reference to the password field variable. From there we can access the widget attributes using arrays.

Using Xataface to create forms

You can leverage off the power of Xataface to create forms allowing users to submit data to your tables. Here is some code to allow you do that, I'll go into more explanation after:

$app =& Dataface_Application::getInstance();
$record =& $app->getRecord();
$form =& df_create_edit_record_form($record, array('FirstName','LastName'));

$form->addElement('hidden','-action','myaction');
if ($form->validate() ){
$res = $form->process(array(&$form, 'save'), true);
// could return false or PEAR_Error to indicate error.
header('Location: '.DATAFACE_SITE_HREF.'?-action=foo&--msg=Success');
exit;
}
$form->display();

The first two lines are basically grabbing a reference to the xataface application and the current record loaded.

$form =& df_create_edit_record_form($record, array('FirstName','LastName'));

This line will create a xataface form object which represents the same table that this record is taken from. It will only display the FirstName and LastName fields though since the second parameter allows you to select which fields to display. Because it is a edit record form, it will fill in the values of the form with the values from the record that was sent in through the parameter. You can alternatively use the df_create_new_record_form() to create a new form entirely.

$form->addElement('hidden','-action','myaction&#

This line adds a hidden element to the form and then sets the name of the hidden element to be -action. It is basically used to allow the form to figure out what action to take after it has been submitted. Generally, we set the action to be the same action as the current page and then allow the form to be validate (explained below)

if ($form->validate() ){
$res = $form->process(array(&$form, 'save'), true);
// could return false or PEAR_Error to indicate error.
header('Location: '.DATAFACE_SITE_HREF.'?-action=foo&--msg=Success');
exit;
}

The form first gets validated for valid values. And then if it returns true, it will then process the form and basically save the data to the database. From there, we need to redirect the user to the next location. We always put an exit statement after the header() to make sure the code stops after the redirect. Because should the redirect for some reason fail, we don't want the user to be see the form again.

$form->display();

Finally, if the form was not validated (ie. not submitted before, or values wrong) we need to display form which is what this line does.

And there you go! That's how you can use Xataface to help you create forms!

Using Xataface to search for NULL

If there is a row in a table that contains a NULL value, you can use Xataface to search specifically for that record but it's not as trivial as searching for NULL. You have to set it to an equal sign which will basically look for exact matches to nothing. For example:

$temp_types = df_get_records_array('n1_tire_types', array('-limit'=>200, 'parent_id' => "="));

Will look for columns in the n1_type_types table with a parent_id of empty or NULL.

Secure Display

If you are using Xataface as your backend management, and also as a front end (through the use of actions and templates) this can save you lots of time. However, sometimes you set permissions on records that you don't want users seeing if they are in the backend. For example, the application could prevent any users not logged in from seeing the backend by returning:

return Dataface_PermissionsTool::NO_ACCESS();

However this could have adverse effects as in the front end some records are no longer viewable because of this. One way to solve this is to set in each of your smarty templates this small block of code which basically causes all records to ignore all permissions:

{php}
$pt =& Dataface_PermissionsTool::getInstance();
$pt->setContext($pt->PUBLIC_CONTEXT());
{/php}

output cache

One of the things you can do on a live xataface site is turn on caching to allow the server to just the information in RAM rather than continue to hit the database for information each time. You can do this by simply entering this in the conf.ini:

[_output_cache]
enabled=1

But there comes a problem sometimes where you need the data to appear right away and not have the server continue to use the cache (at least until the server refreshes the cache). If you ever need to do this, you can enter this into manually just modify the url query to include this:

&--refresh-apc=1

This cause the server to automatically hit the database to refresh the cache. A pretty nice hack.

Setting a MD5 hash on a password field

In your users table, you might want to store the user's password as a MD5 hash. When xataface is authenticating users, you make to tell them to encrypt the password they enter so that it can be properly compared to the value in the database. To do this, make a fields.ini file in your user's table delegate folder. And then enter this:

[password]
encryption=md5

[password] is the name of your password column field name.

Redirecting to a page

Say after an action in Xataface (eg. finished adding a new record), by default it goes to the edit action for that record you just added. But say you want it to go to the new action to add another another. There are a few ways to do this:

1. One way to do this is to grab the current query:
   
   
   The --msg query string variable allows you to display a confirmation message. We put an exit after the header function to make sure nothing else in the script runs.
   


2. The above way works, but it leaves the primary id of the record you just added in the url. Probably not the best thing to do, another way to do it is just use the header function and manually enter the query parameters:
   
   

   header('Location: '.DATAFACE_SITE_HREF.'?-table=tablename&-action=new

   
   
   I used the DATAFACE_SITE_HREF constant variable here which provides the path your application. You NEVER want to hardcode your application path since portable. You can put this function like in your after_action_new() trigger so that after you add a record, it allow you to add another record right away.
   

Displaying Xataface record values

Taking a look at the Xataface Record API presents you with several techniques to display the values of the record. At the lowest level, there is a function called val and can be called like this (assuming $rec is a Xataface record)

$rec->val('fieldname')

What this does is it displays the raw value of field. As in exactly what is stored in the database.

But what happens if the value is say a foreign key to another table say like a number of a company. We don't really want to display just the number, but rather the company name of the id. If a valuelist has been setup for this company_id in a valuelist.ini file, then you can easily display the field you really want by simply using the "display" function.

So say for example, in your application delegate's valuelists.ini file you have this:

[companies]
__sql__="select company_id,company_name from companies order by company_name"

So you are basically telling Xataface to use the company_name as a replacement for the company_id. Since this is the application's delegate, this valuelist is accessible by any table and can be overwritten the own table's valuelist. In any case, if in your other table you just call:

$rec->display('company_id')

Xataface will be smart enough to use the company_name in replacement for the company_id. This display function is also useful for database field types datetime, time, or date. If you were simply use the "val" function it would return the date as an array with keys "year", "month", "seconds", etc. And you would be left with having to piece together the information yourself. But by using the display() function you can quickly circumvent this problem.

Xataface: Setting a logo for a xataface record

When you go into the details view of a record, in addition to the all the different values in the database about the record, you can also specify a logo for that record and this logo will appear in the top left corner of the record.

You should first refer to this document on how to actually store images/file in xataface.

I used method two because I always find it much easier to manage when the files exist physically and not just as bytes in the database. So let's assume that the picture field in the database is what is storing the file name in the database, and you have a directory called picture in your delegate class.

The next step is to go into your delegate class that represents this record and include this function:

function friend_picture__htmlValue(&$record){
return '<img src="'.$record->display('picture').'"></img>';
}

What this does is define a custom field called friend_picture which will basically return the image. By using $record->display() we are returning the whole address of the picture. Xataface is smart enough to figure out how to return the address.

This is opposed to using $record->val('picture') which would return the raw value of the field in the database which would just be the filename.

And then final step is to go into the fields.ini file of this delegate class and include this:

[friend_picture]
logo = 1

This causes xataface to recognize this was a logo field for the record and then display the picture in the top left hand corner of the detail view of the record.

And volia!

Some things to note is that trying to include the logo = 1 as attribute for the field that holds the filename that's seem to work. For example, say in the fields.ini file you have:

[picture]
logo = 1

This would place this field to the top left hand corner, but it would just say "View Field Content In New Window ()" in the top left hand corner. You have to make a custom field.

Also, you can specify exactly records of which table should display logos by including this line of code in the ApplicationDelegate class' getPreferences() function:

//this is needed to determine which table records will have a logo on them
if ( !in_array($query['-table'], array('companies', 'dealers','news', 'strategies', 'offerings') ) ){
$prefs['hide_record_view_logo'] = 1;
}

What this basically did was check whether the current table viewing this record was either companies, dealer, etc. And if it wasn't, it would turn the hide_record_view_logo preference. So a modified line of code could be like this:

if ( !in_array($query['-table'], array('companies', 'dealers','news', 'strategies, 'offerings') ) ){
$prefs['hide_record_view_logo'] = 1;
}

Installing Zope/Plone

This took me nearly 2 hours to figure out how to deal not because it was hard, but because I had no idea how Zope/Plone worked and there was honestly no documentation on it. But basically you can go to the plone.org website and download the windows installer.

This installer will then install Zope, the application server, python and plone all at once. Plone actually becomes a Zope "product" The key thing is that Zope acts as your web server to host files, but it's more advanced than just a web server because it can serve client application calls (which is why it is a called an application server.

In any case, the installer will automatically create an instance of a zope for you and should be able to start this in the windows services menu. The instance will be in the folder Data of the directory where you installed everything. Once you have this instance started, you can access the zope management interface by going to http://localhost:8080/manage. This provides a backend to the zope application server.

To go to the plone installation go to http://localhost:8080/plone.

That's it!

Using smarty tags in a template

This is a tutorial to show how to specify some variables in a php class and then use them in the smarty template. Here is the situation: There is a below_header.html file needs to have a drop down menu which lists a bunch of offering_types as a classification of offerings.

The Dataface_Main_Template.html contains this code:

{block name="before_below_header"}
{define_slot name="below_header"}{/define_slot}
{block name="after_below_header"}

Notice the below_header slot. Since we want this slot to be replaced on every page of Dataface, we will use the function block__below_header() in the ApplicationDelegate.php file to replace this slot.

So the next step is to retrieve the variables that we will use to display in our below_header slot.

ApplicationDelegate.php

function block__below_header(){

//only broker dealers and broker customers should be able to see offerings
if (isBrokerDealer() or isBrokerCustomer()){
$offering_types = df_get_valuelist("offerings", "offering_types");
}

//any users can see the strategy types
$strategy_types = df_get_valuelist("strategies", "strategy_types");

$context = array('offering_types' => $offering_types, 'strategy_types' => $strategy_types);
df_display($context, 'below_header.html');
}

The if statements are just some fancy permission features where we only want like the offering_types to show up for broker dealers and customers. But the important lines are:

$offering_types = df_get_valuelist("offerings", "offering_types");

$strategy_types = df_get_valuelist("strategies", "strategy_types");

$context = array('offering_types' => $offering_types, 'strategy_types' => $strategy_types);
df_display($context, 'below_header.html');

Here we are using the df_get_valuelist() function in the dataface_public_api.php file which takes two parameters (tablename, valuelistname). And the valuelists are found in the valuelist.ini of the delegate classes:

valuelist.ini of offering/strategy tables
[strategy_types]
Managed Stocks = "Managed Stocks"
Managed Funds = "Managed Funds"
Managed ETFs = "Managed ETFs"
Managed Blend = "Managed Blend"
Managed Annuity = "Managed Annuity"
Currency = "Currency"
Derivatives = "Derivatives"
Commodities = "Commodities"
Unknown = "Unknown"

Nothing special here. Just a list of the strategy types. I didn't show the offering_types which is in the valuelists.ini of the offering table. But it's the same thing.

And so after we have retrieve the valuelist as variables, we place them into this context variable which we then send in as a parameter to the df_display function. The second parameter below_header.html is the file to display which will use the variables from the context variable.

below_header.html
The important code:

<select name="Offerings" onchange="window.location=this.options[this.selectedIndex].value">
{foreach from=$offering_types key=okey item=otype}
<option value="{$ENV.DATAFACE_SITE_HREF}?-table=offerings&offering_type={$otype}">{$okey}</option>
{/foreach}
</select>

The smarty tag foreach from is retrieving the offering_types variable which is an array (remember from the ApplicationDelegate.php file) and so we are getting the key and value (as item) and using them as the option value and as the labels.

One interesting thing is the onchange javascript event which automatically changes the page to the value of the option.

That's it!

Displaying actions in a html select object

Here is an interesting example of how to use actions in an html select from object.

In our actions.ini file (delegate or conf.ini) we have a set of actions each with the category attribute set to bd_company_filters. Like this:

[show_only_approved_news]
;; Show only companies that have been approved by this broker dealer
url="{$this->url('approval_status=2')}"
condition="$query['-table'] == 'news' and isBrokerDealer()"
label="Approved News"
category=bd_news_filters

In our delegate class, we can access these actions like this:

$at =& Dataface_ActionTool::getInstance();
$actions = $at->getActions(array('category'=>'bd_company_filters'));

The first line retrieves the Dataface_ActionTool object which we then use to return an array of actions underneath the category bd_company_filters. Each action in the array of actions will contain the attributes specified in the actions.ini and can be accessed by simply using the normal array key like this:

foreach ($actions as $action){
echo action['url']
}

Here we are accessing the url attribute of each action under this category.

So putting it all together, we can do something like this (NOTE: this code is in the block__before_result_list() function):

$at =& Dataface_ActionTool::getInstance();

$actions = $at->getActions(array('category'=>'bd_company_filters'));
echo '<select onchange="window.location=this.options[this.selectedIndex].value" class="company_filters">';

echo '<option value="#">Filter Resources in this Category</option>';

foreach ($actions as $action){
echo '<option value="'.$action['url'].'">'.$action['label'].'</option>';
}

echo '</select>';

Here we are creating a html select object and then we are placing the different actions as options for the select valuelist. The labels of the actions act as the labels in the select list, and the urls are used as the values of the options.

A little simple javascript is used to change the window.location to the value of the selected item in the list on change.

Using the df_display() function

You can use the function df_display() to actually include html files (or I think any other file) in your templates folder that can then be displayed in your delegate class functions. For example,

function block__before_header(){
df_display(array(), 'offeringApprovalForm.html');
}

Here we have included the offeringApprovalForm.html file into block at the top or the page. You can then like use javascript to get elements from the form if you need it.

Additionally, the 1st parameter for the df_display() function actually takes an array of variables that can be used in the template as a smarty tag. For example, if we had an array and sent it into the df_display() function

$context = array();
$context['approved'] = 3;
df_display($context, 'left_menu.html');

Then the left_menu.html file can access the approved variable using a smarty tag like this {$approved}. This will display 3 in the location you specified.

Smarty Tags

So Xataface employs smarty templates throughout its application to apply its styles and stuff. I am not 100% sure how it works, but there are list of ENV variables which are available to be used throughout any template file in xataface. These ENV variables can be found at this link.

Changing preferences in the conf.ini file

The conf.ini file there is a section called [_pref] which you can use set different preferences through the application. For example, show_search = 0 will turn off search. How this works is that the various template files the application uses has special "Smarty" tags which have a line of code like this:

{if $ENV.prefs.show_search}
// some code
{/if}

This example was taken from the Dataface_Main_Template.html file. The $ENV.pref.show_search line basically checks the conf.ini file to see if the preference show_search has been set to 0. If it hasn't, then it will show search.

Another example is in the Dataface_List_View_summary.html file (which is an alternative template to list view of a table) there is a line of code:

{if !$ENV.prefs.SummaryList_hideSort}

Notice however, that there is a ! symbol. So in order to actually turn it off, the preference must be SummarList_hideSort = 1.

This idea can be extended across all templates.

Changing the default template for list view

You can actually change the default template list view (or maybe any other template) by doing something really simply. For example, we can use the Dataface_List_View_summary.html file (it's in the Dataface/templates folder) to replace our normal view of the list. To do this, go to the actions.ini file (can be the application one or a delegate class one) and enter this code:

[list > list]
template=Dataface_List_View_summary.html

The code list > list just says inherit everything from the list action and then add this new code and call it the action list. We are just like overwriting the default list action.

And some we point our template attribute to the template file we want and volia!

The debug option

You have the option of turning on debug = 1 in the conf.ini file in the application folder of a Xataface application. What this will do is basically display all the possible blocks you can insert code/templates into.

Slots will not be displayed in the page, but rather they will be present in the html code in comments.

Dataface record checkPermission() function

The dataface record class has a function called the checkPermission($permission) where the parameter is a name of a permission specified in the permissions.ini file.

Different users will have different permissions and so if the user who is currently logged in does not have this permission they won't be access the block of code that requires this permission. For example:



The the user who is currently logged in must have the view_stats permission. So assume like the user who is logged in has the role "REP" So if you go the permissions.ini file in the main application folder, then open it up you should be able to find the role "REP" which has permissions like this:



Notice how the last line is view_stats = 1 meaning they have the permission view_stats. And so they will be able to access the block of code.

How to add custom stylesheets and javascript

Here was the tutorial on how to do it:

http://framework.weblite.ca/documentation/how-to/custom_javascripts

Basically you just add the code at the delegate class, or application class:

function block__custom_stylesheets()

OR or javascripts:

function block__custom_javascript()

You can also use the dataface application class object which has a function called addHeadContent, and what this does is it will add any thing you enter as a parameter into the slot in the head (the same slot that has the custom stylesheets and javascripts.

For example:

$app =& Dataface_Application::getInstance();
$app->addHeadContent("HELLO");

This will add the words 'HELLO' to the slot section of the html.

The main difference between using this addHeadContent function and the block__custom__stylesheet/javascript function is that you can use the addHeadContent function inside any other function. The block__custom__stylesheet function is isn't own separate function that can't be used in any other function.

But you can do something like this with the addHeadContent function. For example:

function field__recent_offerings(&$record){
$app =& Dataface_Application::getInstance();
$app->addHeadContent("HELLO");
}

So inside of this field function we call the addHeadContent. It has the same effect as the block__custom function, but you can be narrow it down to exactly when you want to include it. Like say you wanted to include the recent_offerings field and you want to apply a custom CSS class to it. You can just call the addHeadContent function.

This is opposed to calling the block_custom function and added the css style there. You would then probably have to add a comment to beside the style sheet there just to remind yourself why this style sheet is even in there.

So it's just for convenience sake.

Using the table relationship permissions function

Inside of each delegate class, we can use the function rel_nameOfRelationship__permissions(&$record) to specify the permissions on that relationship. The reason this works is because when we click on a tab to go to the relationship we are still in the original table.

For example, say we have a companies table that had a relationship to an offerings table. An offerings tab will be present. You can control the permissions on the tab (as in whether the user can add new offerings) by using that rel_nameOfRelationship__permissions(&$record) function.

In the event, that you want to actually remove that tab from the view, then you need to do this in the relationships.ini file of that table folder. Like this:

[offerings]
__sql__ = "SELECT * FROM offerings WHERE company_id ='$company_id'"
action:condition="isAdmin() or ($record->val('company_id') == getCompanyID()) or ( isBrokerDealer() and $record->val('company_id') == getDealerID())"
actions:addexisting=0
action:label="offerings"
section:sort="date_reported desc

1. The __sql__ is just the SQL statement to retrieve the records


2. action:condition is the part that determines whether the tab should appear or not.


3. actions:addexisting makes it so that the add existing option doesn't appear

I found this strange bug (not sure if it is a bug). But if the try to enter action:condition = 0 just by itself it won't hide the tab. You have to enter action:condition = "false" in order for it to work...

Setting up Drupal Editing in Eclipse

Found a cool article that teaches us how to setup Drupal editing inside of Eclipse. Pretty cool actually.

The idea of adding like extra content types for a language can probably be extended for future use.

Xataface - How to unset tabs in the table view

Inside of a delegate class' fields.ini file, you can specify the notion of "tabs" and actually put separate fields into their tabs.

Sometimes you don't want a tab to appear in the view of a record. You can set the permissions of this like this:

function __tabs__(&$record){
$user =& getUser();

$t =& $record->_table;
$tabs = $t->_tabs;
if ( $record and !isAdmin() ){

if ( !$record->val('people_approval_status') )
unset($tabs['__people__']);
if ( !$record->val('researcher_approval_status') )
unset($tabs['__gcms__']);
}
return $tabs;
}

Here we call the __tabs__ function to actually process the tabs before they are run. And then we unset the tabs that we don't want the user to see.

Creating multiple drupal sites on the local machine

This is supplementary information on the article listed at: http://drupal.org/node/120647

So there were a few things that I had trouble with:

1. Copy the database to a new database.
   This step kept failing saying that I was failing a constraint test (it said I had a duplicate key). I don't really really see why it failing considering that my users table only had 2 users. The first user was actually a blank row, that had only the default values. This seemed to have been the initialize drupal installation.
   
   So to solve this, I went into the database and literally just deleted that row. And when I now tried to copy the database to a new database, it worked.
   


2. Modifying the httpd-vhosts.conf and hosts file
   It says to comment out existing lines, but I was using the Apache 2.2.6 which had them uncommented anyways. This file is not used anyways unless you tell it to be used in the httpd.conf file which is configured in an early step.
   
   For each new site, you need to add this:
   

   
   <VirtualHost *:80>
   DocumentRoot /www/drupal/
   ServerName databasename
   </VirtualHost>
   

   
   
   What this actually does it set the location of the files to be accessed when someone tries to access the server name. This actually is in relationship with the hosts file in the /windows/system32/drivers/etc directory.
   
   

   I had this annoying problem with vista where I couldn't edit the file because I didn't have permissions. So I just copied and pasted the file on my desktop. Opened it, edited it and the pasting it back in the same directory effectively overwriting it

   
   
   If you look at this file you should see some lines like this:
   
   

   
   127.0.0.1 localhost
   ::1 localhost
   127.0.0.1 how_far_are_you
   127.0.0.1 drupal_test_database
   

   
   
   This maps IP address to host names (Servers). The number 127.0.0.1 means localhost, and then we say that this IP address can map to three servers: localhost, how_far_are_you, and drupal_test_database.
   
   Where are these locations/Servers? Well they are actually on your machine!!! These are specified in your httpd-vhosts.conf file above. Each server must be listed with:
   
   

   
   <VirtualHost *:80>
   DocumentRoot /www/drupal/
   ServerName databasename
   </VirtualHost>
   

   
   
   So for example:
   
   

   
   <VirtualHost *:80>
   DocumentRoot ""D:\web\webserver\htdocs\drupal-6.2"
   ServerName how_far_are_you
   </VirtualHost>
   

   
   
   This says that this server "how_far_are_you" is located at the file location specified under document root. For another drupal site, we do exactly the same thing, expect the servername is just different (it is the database name)
   
   So the interesting thing is that they both point to the same document root. In order to differentiate the two different sites, we use the database names.
   
   

   Actually now that I think about it. I think it just matches the name of the site folder (ie .drupal/sites/site_name). Because the settings.php file handles the database name connection. Haven't confirmed this yet though.

   
   
   One thing I want to mention is that because turn this virtual host thingy on, typing in http://localhost no longer works. You actually have to add the line:
   
   

   
   <VirtualHost *:80>
   DocumentRoot ""D:\web\webserver\htdocs"
   ServerName localhost
   </VirtualHost>
   

   
   
   For it to work. I think this was taking care of when the virtual host feature wasn't turned on.
   

This is a pretty interesting thing, and it also allows to just declare like domain names to point to different document roots on the local computer. This is essentially how virtual servers work (where one machine hosts multiple sites!)

One interesting thing I tried to do was skip the part about editing the host file, and see what happened if I just typed in http://name_of_servername. It didn't worked and it searched the web actually. I suspect that part is necessary, because typing in http://... causes it to first search your local machine for valid servername. So the host file is necessary

Drupal - Getting Started

Once I followed the video to install Drupal, I ran into this problem where when I tried to click on any link in the index.php file in the drupal directory it would lead me right back to a view of the directory.

I suspected this has something to do with the fact that I didn't my local installation of apache to serve the index.php file if it was present (similar to how index.html is the file to be served when the user first enters that directory.

So going into my httpd.conf file, I searched for the line "DirectoryIndex", and then modified it to include index.php:

DirectoryIndex index.html index.php

Note how index.html was already being served. And when I went back to my drupal directory and tried a link, it worked!

GD Library Not Found
Upon entering the administrator page, I found that I had some status errors right off the bat. Clicking on the link to see what status errors there were, I found one to say that my "GD Library" was not found. This is an image library that is required for drupal to do some image manipulation for some themes or maybe so content. Anyways, the version of PHP 5.2.5 that I am using actually has this library but it is turned off by default.

So go to the php.ini file, and search for the line "extension=php_gd2.dll" and then there should be a semicolon right before it. Just remove the semicolon and then try it again and that should fix the problem.

functions.inc.php

If you create this file in a folder called include, this file will be included in every page and the functions will be available. I've included like an example of this file with some functions that serve as an example of what it can be used for:

error_reporting(E_ALL);
ini_set('display_errors','On');
define('APPROVAL_LEVEL_APPROVED',2);
define('APPROVAL_LEVEL_CONDITIONAL',1);
define('APPROVAL_LEVEL_NOT_APPROVED',0);

define('APPROVAL_STATUS_APPROVED',2);
define('APPROVAL_STATUS_NOT_APPROVED',0);

function isLoggedIn(){
static $loggedIn = 0;
if ( $loggedIn === 0 ){
$user =& getUser();
if ( $user ) $loggedIn = true;
else $loggedIn = false;
}
return $loggedIn;
}

function &getUser(){
static $user = 0;
if ( $user === 0 ){
$auth =& Dataface_AuthenticationTool::getInstance();
$user = $auth->getLoggedInUser();
}
return $user;
}

// Checks whether the current user is an admin or not.
function isAdmin(){
$user =& getUser();
if ( !$user ) return false;
return ($user->val('role') == 'ADMIN');
}

function isBrokerCustomer(){
$user =& getUser();
if ( !$user ) return false;
return ($user->val('cookedRole') == 'BROKER CUSTOMER');
}

function getDealerID(){
static $dealer_id = -1;
$user =& getUser();
if ( !$user){
if ( $dealer_id == -1 ){
$res = mysql_query("select company_id from companies where host_name='".addslashes($_SERVER['HTTP_HOST'])."' limit 1", df_db());
if ( mysql_num_rows($res) == 0 ) $dealer_id = null;
else list($dealer_id) = mysql_fetch_row($res);
}
return $dealer_id;
}
return $user->val('dealerID');
}

function &getBrokerDealer(){
static $bd = 0;
if ( $bd === 0 ){
$id = getDealerID();
if ( $id ){
$bd = df_get_record('companies', array('company_id'=>$id));
}
}
return $bd;
}

function isBrokerDealer(){
$user =& getUser();
if ( !$user ) return false;
return ($user->val('cookedRole') == 'BROKER DEALER');
}

function isCompanyOwner(){
$user =& getUser();
if ( !$user ) return false;
return ($user->val('cookedRole') == 'COMPANY OWNER');
}

function isCompanyRep(){
$user =& getUser();
if ( !$user ) return false;
return ($user->val('cookedRole') == 'COMPANY REP');
}

function getCompanyID(){
$user =& getUser();
if ( !$user ) return null;
return $user->val('companyID');
}

function &getCompany(){
static $company = 0;
if ($company === 0 ){
$id = getCompanyID();
if ( $id ){
$company = df_get_record('companies', array('company_id'=>$id));
}
}
return $company;
}


function off($date){
$date = strtotime($date);
$offset = (strftime("%j")+strftime("%Y")*365)-
(strftime("%j",$date)+strftime("%Y",$date)*365);
if ($offset>7){
$offset = (strftime("%V")+strftime("%Y")*52)-
(strftime("%V",$date)+strftime("%Y",$date)*52);
$end=($offset!=0?($offset>1?$offset . " weeks ago":"a week ago"):"Today");
} else
$end=($offset!=0?($offset>1?"$offset days ago":"Yesterday"):"Today");
return strftime("%A, %B %e, %Y",$date)." - ". $end;
}

Here we have some common functions to help figure out what type of user is logged in.

Actions: Adding new actions at specific locations

One thing that seemed to escape me for the longest time was the fact that when I included an actions.ini, or like a permissions.ini file in my website's directory. I was actually overriding the actions and permissons.ini files in the main dataface directory.

My point of stating this was I wanted to figure out how to like add new actions to specific blocks/spaces in my website. Like for example, where the "list", "detail", "find" buttons are. In the actions.ini file, each action has an attribute called category and you can assign this a value. And then templates will uses these categories to figure out which actions to place where.

So my problem was trying to find out what categories were available. All this information is available in the dataface directory's actions.ini file.

Permissions

So I've been doing a lot of research into the permission in dataface and I think I have a pretty good understanding.

There is a global permission setting you can set for all tables in the conf/ApplicationDelegate.php class. So you can grant a particular user to say all the tables, or block them from all tables in this class. This is accomplished through the function getPermission() in the ApplicationDelegate.php class.

function getPermissions(&$record){
$auth =& Dataface_AuthenticationTool::getInstance();
$user =& $auth->getLoggedInUser(); //grabs the logged in user
if ( !isset($user) ) return Dataface_PermissionsTool::NO_ACCESS(); //if there is no user logged in, then they should not have access to anything.
$role = $user->val('role'); //grab the role of the user. the val function just returns the value of the parameter
if ( !$role ) return Dataface_PermissionsTool::NO_ACCESS(); //if no role, then they should get no permissions
return Dataface_PermissionsTool::getRolePermissions($role); //return the permissions of that role.
}

Now if you want to block them for specific tables, these permission settings are to be set at the delegate class table level. For example,

$auth =& Dataface_AuthenticationTool::getInstance();

$user =& $auth->getLoggedInUser();

//if user is not set and (no record OR no memberID) and action is new
//give them person to edit. Enter information into the fields
if ( !$user and ( !$record || !$record->val('memberid') ) and @$_GET['-action'] == 'new' ){

return Dataface_PermissionsTool::getRolePermissions('EDIT');

//if there is just no user then they should have no acess
} else if (!$user ) {

return Dataface_PermissionsTool::NO_ACCESS();

//if there is a user, then return the role permissions of that user
} else {

return Dataface_PermissionsTool::getRolePermissions($user->val('role'));

//return PEAR::raiseError('Let the app delegate class handle it', DATAFACE_E_REQUEST_NOT_HANDLED);

}

And if we want even more fine grained, then we can do it was the field level. The field level is a little more tricky thou. To provide permissions for a given field, it must follow this syntax:

function FIELDNAME__permissions(&$record)

The record represents the given record object in question (not 100% how this works...but it could mean a row in the database...but if it is a submission form is the row empty?).

The function body could be:

$auth =& Dataface_AuthenticationTool::getInstance();

$user =& $auth->getLoggedInUser();

if ( !$user or $user->val('role') != 'ADMIN'){

return Dataface_PermissionsTool::NO_ACCESS();

}

Here we are getting the logged in user, and it doesn't exist, or if it does exist, but role is NOT admin, then they should have no access and thus it will not appear in the form.

Installation

I've decided to update this post (done on Nov 29th) to add all the problems I've had with the installation of dataface and following that "Getting Started" tutorial. So below is a list of them:

Nothing shows up in the dataface_info.php file
So I followed the instruction on the dataface site (the getting started article), but nothing even shows up! What the? Turns out the actual file relies on short tags (ie.

session_save_path is not set
So when first setting up Dataface, I always ran into this stupid problem that it says that my session_save_path is never set...
And then it refers you to line 531 of the Application class. Here you see the code:

$sessdir = ini_get('session.save_path');
if ( !$sessdir ) $sessdir = '/tmp';
$sessdir .= "/dataface";

So basically it tries to grab the ini config value session.save_path from the php.ini file...and if it isn't set then it sets it to /tmp/dataface.

Now the thing is on windows, the session.save_path value is not set by default, so it attempts to set this to /tmp/dataface...only we are using windows and not linux (windows has paritions where linux treats the /tmp to be the root directory)

So the solution is basically just make some folder location on your windows hard drive and then in the php.ini file look for the variable session.save_path and set it to that location.

This should solve the problem.

Unable to insert a new record
So after getting past the initial steps of installing and having dataface create the front end. You can then start adding some records. But when you try to add a new record into the course table, I ended up with this sql error saying that the PDF outline column has no default value. I went into phpmyadmin and looked up the column and sure enough the column was set to not allow NULL. I guess this might have been an intended feature, but in the form no red box is beside that field to even indicate that. Maybe a bug? In any case, the solution is to simply make that column NULLABLE.

Complaining about invalid character (eg. /) in a file when it doesn't exis
I was on the trigger part of the tutorial making a course.php delegate file. But when I made the file, I originally set it as a Rich Text Document. I didn't think this would be a problem as I just renamed it into a .php extension. So did all my work and then tried to reload my wwebapp. Bam! These errors about invalid characters in my course.php. Odd...I was looked inside to find nothing and I even got rid of everything and had the basic:

class tables_Course {
}
?>

And still errors!!! And then it occurred to me that maybe when I made it as a rich text document it added some additional crap in the file that I can see. So I deleted the entire file and remade it as a plain text document (.txt) and everything worked!

Permission Authetication

I was doing some development on my Dataface IERG project and I was trying to login into backend of the system. I noticed that the conf.ini file had this section:

auth_type = cas
url = "https://sson.ierg.net"
users_table = users
username_column = uid
password_column = ""

So the auth_type variable was something I've seen but never really understood. But I noticed that when I accessed the backend of the system it always led me to this url https://sson.ierg.net. I had no idea how to log in and stuff. I emailed Steve and he had this to say:

It needs the users table in order to work. You can just copy the users table from the ierg_net_-_main database into your hybrid one. That should do it.
Then you just log in with the same username and password you use for the timesheets app.

So I just did a quick copy of the table from the membership database to the hybrid database. One thing I noticed was that there was no password column which I thought was weird. So I tried to login using the username and password I've using all the time for my ierg development (ie. fcc, fcc*password), but to no avail nothing worked. I took a quick look at the conf.ini file and noticed that the password column is "". Confused...so I just made a new column password in the users table, and then added this column name to the variable in the conf.ini file.

Hoping things would work, I tried to login but still nothing. I then got frustrated and removed the CAS references and lo and behold everything worked. So I emailed Steve again mentioning the CAS stuff and he emailed back with this:

I'd prefer to keep it as CAS authentication because we'll have to set it back to that anyways when we move the site live again.

CAS just uses the same authentication I use for the timesheets app. I was able to log into your application fine until you turned off CAS.

The best steps from here:

1. Return the [_auth] section of the conf.ini file to the way it was with auth_type = cas
2. Remove the password column from the users table.
3. Try to log in using your timesheet application username and password (username fcc)

Troubleshooting

If you still cannot log in, please let me know. Also describe what happens when you try to log in.

Best regards

Steve

The one thing that stuck out to me was the timesheet application username and password. I suddenly realized that my username and password for the timesheet was different from the one that I used for the IERG development site. So I quickly undid all my changes and tried with the username and password from the timesheet and it worked! So in conclusion:

1. I figure that the CAS authetication is some sort of system that stores the username and password in some separate place.


2. To use the CAS service, you set in your conf.ini file:
   

   auth_type = cas
   url = "https://sson.ierg.net"
   users_table = users
   username_column = uid
   password_column = ""

   
   


3. Create a user table with just a username and role if you want. I guess the username acts a foreign key to the CAS system which stores the username and password. This way we can keep this centralized location of users and if they need to access a particular dataface application they will have to first be listed in the users table of the application and then in the CAS system too.

Makesite problems

Ack...I spent so much time doing such a trivial task today. I was making a new dataface application and I had created all the tables in phpMyAdmin and then I wanted to run the dataface makesite script to quickly generate the outline of my site. Filling out the variables in the call of the makesite script, I made all the paths to be local to my machine. In other words, I had C:/web/htdocs...etc, etc. So that when the makesite script ran it included these values inside of the index.php.

The script ran with no problem, but when I tried to launch my application I noticed it had NO STYLE. I was so confused. I gather it was maybe having trouble accessing the templates folder of the dataface root directory because of the hardcoded path locations? So I then tried to change all the paths to be relative to my webserver...so I had http://localhost/ierg_news.....but then the makesite script started to complain about how it didn't have permission to create in this directory. So frustrating. So I just basically reverted back to the values that originally worked and then I manually changed the paths in the index.php to fit what I needed.

Creating approval levels for records through dynamic forms

This was done on the advisorpage website. I had so much trouble figuring how to do this, but I finally got it done. Before I show you how to do it, I want to give the situation this was applied. There a bunch of companies that needed to be approved. There were separate approval levels: approved, conditionally approved, and not approved. Rather than have the user have to go into the company detail page and select approve, disapprove, and stuff. The client wanted the ability to change the approval level in list view. The default list view of the companies was changed and the list of different approved options to change the approval level was displayed in the list view.

This was done through the actions.ini file and assigning actions to the category=record_actions. The list view displays any actions under this category in a specific location specified in the template. So there are several key things to note here:

1. All the companies information was stored in a companies table


2. The details of approval were stored in a separate table called dealers_companies. The reason this was done was because there were different dealers for the website, and each dealer wanted the ability to either approval or not approve the company. The net result is that each approval level is specific to a certain dealer. So that there "customers" will only see what companies THEIR dealer approved of and not others.
   

So here are the steps I followed to get it done:

This was done using actions. In the application's actions.ini file, there was three separate actions called bd_approve, bd_approve_record and bd_conditional_approve_record. Each of these actions corresponds to a separate thing that can be done to the company record. The content of one of these actions:

[bd_approve_record]
;; This action is available to Broker Dealers to change the approval level
;; of the current record to "Approved"
label="Approve"
category=record_actions
condition="$record and $record->_table->tablename == 'companies' and isBrokerDealer() and $record->val('approval_level') < url="">getId()}');"
url_condition="$record"
permission=bd_approve
icon="images/confirm_icon.gif"
description="Approve this company and all its news to be viewed by your agents."

1. The label attribute was used to display the text of the action


2. category was used to group this action under the record_actions category which is used by the template to show actions under this category at a location specified


3. condition is when this action appears. Here we say that there must be a record, the tablename must be companies, the user logged in must a broker dealer and the approval level of the record must be less than 2.
   
   Note that the companies doesn't include an approval level, this is actually found in a separate table called called "dealer_companies" This issue will be addressed later.
   


4. url_condition is like the condition the url has to satisfy in order for this action to be present. The reason why this is needed is because the condition attribute relies on like a record...so unless there is a record it won't be able to like apply the conditions. The url condition first checks to make sure that the url has a record...something along those lines.


5. permission is taken from the permissions.ini file. Here the user who is currently logged on must have the bd_approve permission for this action to appear.


6. icon is the image that appears beside the label


7. description is the text that appears when the label is mouseovered..

Here the url attribute is assigned to a javascript function called WLS_ApproveCompany(). This function is found in the script.js file in the folder js and it was included into the website in the ApplicationDelegate class by the function block__custom_javascripts():

function block__custom_javascripts(){
echo '<script src="%27.DATAFACE_SITE_URL.%27/js/scripts.js" type="text/javascript" language="javascript"></script>';
echo '<script src="%27.DATAFACE_SITE_URL.%27/js/menu.js" type="text/javascript" language="javascript"></script>';
}

If you to the script.js and find that function:

function WLS_ApproveCompany(level, id){
var form = document.getElementById('companyApprovalForm');
form.elements['--selected-ids'].value = id;
form.elements['--level'].value = level;
form.submit();
}

Here we are grabbing the html form companyApprovalForm (more on this later) assigning the elements --selected-ids and --level values to the function inputs.

The --selected-ids variable has to exactly named this way. This is because we use a Dataface function called df_get_records() which looks specify for the --selected-ids variable.

And this submitting the form using javascript. The form is located in the templates folder called companyApprovalForm.html:

{foreach from=$ENV.QUERY key=name item=val}
{if $name != '-action' and $name != '-table' and $name != '--level' and '--selected-ids'}
<input name="{$name}" value="{$val|escape:'html'}" type="hidden">
{/if}
{/foreach}
<input name="-action" value="bd_approve" type="hidden">
<input name="-table" value="companies" type="hidden">
<input name="--level" value="" type="hidden">
<input name="-from" value="{$ENV.action}" type="hidden">
<input name="--selected-ids" value="" type="hidden">
</form>

Here we are just including all the values in the query string into this form so that when we submit it they are included in the post request and can be used again. Note the --level and --selected-id elements. We changed this in the javascript function. Notice how the action of the form is itself, but there is an additional element called -action and with a value called "bd_approve." This action is in the actions folder and called bd_approve.php. It is called when the page is get reloaded because the query string will have ?-action=bd_approve.php will tells it to run this.

The key thing to note is that if something isn't in the query string it won't be put into the form. You have to include the selected_ids and level in the $name not equal because it will continue to add these hidden fields to the form when you refresh it

So I was trying to extend this to include a separate form that handled trust values. The problem was I wasn't including the approval level in the query string and so when I tried to modify the database, I was just overriding the approval level (you will see why in a bit). We need to include this form on the page of our interest. So we can use the particular delegate class of our interest, and insert this code:

function block__before_header(){
df_display(array(), 'abstract_approval_form.html');

The bd_approve.php class

class actions_bd_approve {

function handle(&$params){
$app =& Dataface_Application::getInstance();
$query =& $app->getQuery();
$records = df_get_selected_records($query);
//echo count($records).' records selected.';


$dealerid = getDealerID();


if ( !isset($dealerid) ){
return PEAR::raiseError("No dealer id set", DATAFACE_E_ERROR);
}

if ( !isBrokerDealer() ){
return PEAR::raiseError("Sorry, you must be a BD Administrator to approve companies for your BD.", DATAFACE_E_ERROR);

}

if ( !isset($_REQUEST['--level']) ){
$level = 0;
} else {
$level = $_REQUEST['--level'];
}

$sql = array();
$sql[] = "replace into dealer_companies (company_id,dealer_id,approval_level) values ";
$vals = array();

foreach ($records as $rec){
if ( $rec->_table->tablename != 'companies' ){
// we only approve companies
continue;
}

$vals[] = "('".$rec->val('company_id')."','$dealerid', '$level')";

}

if (count($vals) == 0 ) return PEAR::raiseError("No records were selected to be approved", DATAFACE_E_ERROR);

$sql[] = implode(',', $vals);
$sql = implode(' ',$sql);
$res = mysql_query($sql, df_db());
if ( !$res ) return PEAR::raiseError("A mysql error occurred while trying to approve companies: ".mysql_error(df_db()), DATAFACE_E_ERROR);

if ( isset($query['-from']) ){
$query['-action'] = $query['-from'];
} else {
$query['-action'] = 'list';
}
$url = $app->url($query).'&--msg='.urlencode('The selected companies have been succesfully approved for viewing by your representatives. '.count($vals).' companies approved.');
header("Location: $url");
exit;


}
}

The important line is the one with the sql statement

"$sql[] = "replace into dealer_companies (company_id,dealer_id,approval_level) values ";"

And then we include the values. So we've now changed the approval level. It is important to notice that any columns in that record that don't have a value will be set to their default...meaning it will get overwritten. For example, say the dealer_companies had another column called trust_level. If I just included the replace statement without specifying the trust_level, it will always set that value to the default which could be like 0.

So you have to include the trust value even if you are only modifying the approval level. The way to make the changes are:

$sql[] = "replace into dealer_companies (company_id,dealer_id, approval_level) values ";

BECOMES

$sql[] = "replace into dealer_companies (company_id,dealer_id, approval_level, trust_level) values ";

Have to add this in the foreach loop to grab the trust level of the corresponding record:

$trust_level = $rec->val('trust_level');

And finally:

$vals[] = "('$company_id','$dealerid', '$level')";
BECOMES
$vals[] = "('$company_id','$dealerid', '$level')";

The next step is getting that to be reflected in the actions. Remember in our actions.ini file, for each action there was an attribute called condition which had this:

record->val('approval_level')

function init(&$table)
This is a function that is called each time the companies table is run. Note that the parameter $table refers to the dataface company table (like a table class that dataface represents). This allows us to create like an extended version of the companies table in the sense that we are adding new columns.

What is done here is:

$app =& Dataface_Application::getInstance();

$sql = array();
$sql[] = "create temporary table MyCompanyPermissions (
company_id int(11) not null,
role varchar(32) not null,
approval_level int(5) default 0,
trust_level int(1) default 0,
Primary Key (company_id))";

We create a temporary table which grabs columns from other tables. We need to write the sql code to do this like this:

$sql[] = "replace into MyCompanyPermissions (company_id,role,approval_level)
select c.company_id, '$defaultRole' as role, ct.approval_level from companies c inner join company_types ct on c.resource_type=ct.id where ct.approval_level > '".APPROVAL_LEVEL_NOT_APPROVED."'";

I am not going to show all the code, but the idea is to store the sql queries in an array of sql statements. And then:

foreach ($sql as $query){
$res = mysql_query($query, df_db());
if ( !$res ){
trigger_error(mysql_error(df_db()).' while executing SQL query "'.$query.'"', E_USER_ERROR);
}
}

This will execute the sql statements for use. And then the MyCompanyPermissions table will be available for use each time the companies table is executed.

__sql__ function
This function is the function that is called to override the usual sql statement which grabs rows from the companies table (or in delegate class). By default it will grab all rows using the statement "SELECT * FROM table_name". But we are overriding it here. We are still grabbing all rows, but now we are inner joining it with the MyCompanyPermission table that we just created:

function __sql__(){

return "select c.*, mcp.role as MyRole, mcp.approval_level, mcp.trust_level from companies c left join MyCompanyPermissions mcp on c.company_id=mcp.company_id";

It is important to understand that the __sql__ function must still grab all the rows of the original table. It can only add new columns.

Note the use of the left join here. We can't just normally do the SELECT column1, column2, FROM A, B WHERE (A.a = B.b). Because we need to grab all companies. Some companies might not actually have an approval level...so if we just did that they would not be returned. But if we did a left join, it will always return a row from the companies table (or which ever table was on the left of the join...hence the name LEFT JOIN) regardless of whether there was a row in the right table.

So our companies table is actually expanded by some additionally columns, and thus we have access to MyRole, approval_level, and trust_level columns. Note the trust level column was actually something I was adding on to the system.

Adding new sections in a table view

Inside of the actions.ini file, if you want to use a xataface function you have to surround it with semicolons. So for example, {getDealerID()} would return the dealer id.

So the first thing I wanted to talk about is how to add custom sections to the viewing of a table. By default, the view of a record in a table will display all the columns for that record. But what happens if you want to display so other information from another table that had a relationship with that table record?

For example, say I have a table of companies and this company had a relationship to a table called news. I could setup a relationship in the relationship.ini file, but this would just create a tab in the record view to go click. But what if I wanted to view it would having to go into a tab. Like this:




The way to do this is to first create a function in the table delegate class to that will retrieve that field. So basically we make a custom field:

function field__recent_news(&$record){
//first parameter is table,
//second is the where part of the query
return df_get_records_array('news', array('company_id'=>$record->val('company_id'), 'owner_type'=>'COMPANY','-sort'=>'published_date desc','-limit'=>10));
}

The df_get_records_array function uses the first parameter as the table we are interested in, and the second parameter is the part used to in the where part of the query. So in this case, we are using the news table, where we want the company_id of the row equal to the record company id, etc, etc. Note, we use the -sort here because that is like an action as opposed to a simple additional of a query variable.

The next part is to add the section function:

function section__recent_news(&$record){

return array(
'class'=>'left',
'records'=>$record->val('recent_news'), //there is a calculated field from the function field_section
'label'=>'Recent Updates',
'url'=>DATAFACE_SITE_HREF.'?-action=list&-table=news&company_id='.$record->val('company_id').'&owner_type=COMPANY'
);
}

The name of this function can actually be section__XXX, but we just give it a good name. Here the class attribute means what side it will be displayed in. The records are what we are grabbing. So remember that function we made above that makes a custom field, we are now grabbing this field. I think by default it will display the first column of the table (that is not the primary key or foreign key). Label is the label of the section, and url is the condition I think the url has to fulfill to make sure that this function gets run.