Problem with relationship

Archived from the Xataface Users forum.

HWT — Tue Dec 29, 2009 12:17 pm

I’m new to Xataface and try to set a small DB to administer members of a alumni assocation.
I need a 1:n relationship for members to activities. So i tried both the ini-style and the SQL-style way

Code: Select all: [betaetigung] __sql__ = "SELECT * FROM betaetigung WHERE Mitgliedsnummer='$Mitgliedsnummer'"

In the view perspective of the details of one row I can see the associated rows, but when clicking on the newly displayed tab I get a Permission denied:

* You don’t have permission to view this relationship.

For permissions I only adopted the simple example from the tutorial so far but the problem existed also without custom permissions.

I’m testing Xataface with the most current XAMPPlite package which uses PHP5.3.1 (I commented line 44 in dataface-public-api.php to supress the deprecated warnings as I found in one thread here).
Have you an idea, what I made wrong?
Thanks!

shannah — Wed Dec 30, 2009 11:07 am

What was on line 44 that you commented? It might have been critical.

Strange that without custom permissions you get this permission denied message. Default permissions are full permissions.

HWT — Sat Jan 02, 2010 6:17 am

Line 44 activates the custom error reporting:

Code: Select all: error_reporting(E_ALL^E_NOTICE);

I disabled this (according to a thread somewhere here) not to get these loads of compatibility warnings of PHP 5.3.1 I guess that shouldn’t be the main problem.

Anyway I haven’t found the mistake so far. Do you have a clou where the thing might go wrong? Maybe I need to provide furtehr information, but don’t know what…

shannah — Sat Jan 02, 2010 10:03 am

OK.. That’s fine.

The permissions problem doesn’t make sense to me if it is occurring when no custom permissions have been defined. Default permissions are full permissions so you shouldn’t ever see a permission denied error if you’re not customizing permissions in some way.

HWT — Sat Jan 02, 2010 12:55 pm

Hm, to me to. What can I try?

shannah — Sat Jan 02, 2010 3:37 pm

Can you post your conf.ini, application delegate, and relevant table delegate classes?

00Davo — Sun Jan 03, 2010 2:21 am

I’m having exactly the same issue, so my conf.ini might be handy.

Code: Select all: [_database] host = localhost name = *** user = *** password = *** [_tables] student = Students tutor = Tutors parent = Parents class = Classes

I don’t really see what in here’s going to cause such an issue.

Also, I don’t know what the delegate classes are. I haven’t edited any PHP files, so I believe they’re still defaults.

shannah — Sun Jan 03, 2010 2:42 am

how about the relationships.ini file?

00Davo — Sun Jan 03, 2010 3:14 am

shannah wrote:how about the relationships.ini file?

Which one? I have a few…
tables/Class/relationships.ini

Code: Select all: [Students] Student.Class = "$ID"

tables/Student/relationships.ini

Code: Select all: [Parents] Parent.ID = Family.ParentID Family.StudentID = "$ID"

tables/Parent/relationships.ini

Code: Select all: [Students] Student.ID = Family.StudentID Family.ParentID = "$ID"

HWT — Sun Jan 03, 2010 7:21 am

conf.ini

Code: Select all

`[_database]
host = “localhost”
user = “”
password = “”
name = “alumnidb”

[_tables]
mitglied = “mitglied”
betaetigung = “betaetigung”

[_auth]
users_table = Users
username_column = UserName
password_column = Password`

ApplicationDelegate (haven’t created table delegates so far):

Code: Select all

`<?php
/**

A delegate class for the entire application to handle custom handling of
some functions such as permissions and preferences.
/
class conf_ApplicationDelegate {
/**
Returns permissions array. This method is called every time an action is
* performed to make sure that the user has permission to perform the action.
* @param record A Dataface_Record object (may be null) against which we check
permissions.
@see Dataface_PermissionsTool
* @see Dataface_AuthenticationTool
*/
function getPermissions(&$record){
$auth =& Dataface_AuthenticationTool::getInstance();
$user =& $auth->getLoggedInUser();
if ( !isset($user) ) return Dataface_PermissionsTool::NO_ACCESS();
// if the user is null then nobody is logged in… no access.
// This will force a login prompt.
$role = $user->val(‘Role’);
return Dataface_PermissionsTool::getRolePermissions($role);
// Returns all of the permissions for the user’s current role.
}
}
?>`

relationships.ini of “mitglied”

Code: Select all: [betaetigungen] betaetigung.Mitgliedsnummer = "$Mitgliedsnummer"

Columnname are german so might sound weird to you, but anything else seems to be standard for me.

00Davo — Mon Jan 04, 2010 6:34 pm

It’s not really a proper solution, but if you comment out line 15 in /xataface/actions/related_records_list.php, it works fine.

Code: Select all: //if ( !@$perms['view related records'] ) return Dataface_Error::permissionDenied('You don\'t have permission to view this relationship.');

The reason why this isn’t a proper solution is that if you have permissions implemented, they’ll be ignored when it comes to related records.

shannah — Mon Jan 04, 2010 6:48 pm

If you add the following just before that line, what is the output?

Code: Select all: print_r($perms);

I’m curious if it isn’t picking up any of the permissions or just some of them.

00Davo — Mon Jan 04, 2010 6:49 pm

Adding that line reveals the following messages above the Xataface interface.

Code: Select all: Array ( [view] => 1 [link] => 1 [rss] => 1 [list] => 1 [calendar] => 1 [edit] => 1 [new] => 1 [select_rows] => 1 [post] => 1 [copy] => 1 [update_set] => 1 [update_selected] => 1 [record] => 1 [delete] => 1 [selected] => 1 [found] => 1 [all] => 1 [records] => 1 [feed] => 1 [find] => 1 [import] => 1 [export_csv] => 1 [export_xml] => 1 [export_json] => 1 [translate] => 1 [history] => 1 [edit_history] => 1 [navigate] => 1 [reorder_related_records] => 1 [ajax_save] => 1 [ajax_load] => 1 [ajax_form] => 1 [find_list] => 1 [find_multi_table] => 1 [register] => 1 [xml_view] => 1 [xml] => 1 [manage_output_cache] => 1 [manage_migrate] => 1 [manage] => 1 [manage_build_index] => 1 [install] => 1 [expandable] => 1 )

shannah — Mon Jan 04, 2010 6:56 pm

Thanks.. that’s interesting… it’s like it’s not picking up the view related record permission.

If you check the permissions.ini file, can you confirm that there is a permission listed for ‘view related record’?

00Davo — Mon Jan 04, 2010 6:59 pm

Looks like there is.

Code: Select all: view related records=1

Here’s the whole file, just in case.

Code: Select all

`;;First we will list the permissions and their associated labels.

;; view : Allowed to view an element
view = View

;; link : Allowed to access the link to a record
link = Link

view in rss = View record as part of RSS feed

;; list : Allowed to see the list tab
list = List

calendar = calendar

;; edit : Allowed to edit information in an element
edit = Edit

;; new : Create a new record
new = New

;; select_rows : Allows to select rows in the result set to perform actions
;; to them
select_rows = Select Rows

;; post : Post a record using HTTP post
post = Post

;; Access to the Copy action
copy = Copy

;; Access to update set action
update_set = Update Set

update_selected=Update selected records

;; Ability to add a new related record to a relationship of a record.
add new related record = Add New Related Record

;; Ability to add an existing related record to a relationship of a record.
add existing related record = Add Existing Related Record

;; delete: Allowed to delete a record
delete = Delete

;; Ability to delete selected records from the database.
delete selected = Delete selected

;; Ability to add a new record to a table // Deprecated.. see if this breaks anything!!
;;add new record = Add New Record

;; Access to delete found records (the delete found records menu option)
delete found = Delete Found

;; Access to the show all action (to show all records in a table)
show all = Show All

;; Access to remove a related record from a relationship. This does not
;; delete the related record from the database - just from the relationship.
remove related record = Remove Related Record

;; Access to delete a related record from the database. This permission
;; will override delete access of the actual related record to provide
;; some elevated permissions for the owner of the parent record.
delete related record = Delete Related Record

;; View the records of a relationship
view related records = View related records

;; Access to RSS feed for related records
related records feed = Related Records RSS Feed

;; Access to the “find” tab
find = Find

;; Ability to import records
import = Import

;; Ability to export records in CSV format
export_csv = Export CSV

;; Ability to export records as XML
export_xml = Export XML

;; Ability to export records as JSON
export_json = Export JSON

;; Ability to translate records
translate = translate

;; Ability to see history information
history = View history information

edit_history = Edit history information

;; Ability to navigate through the records of this table.
navigate = Navigate

;; Ability to reorder the records in a relationship
reorder_related_records = Reorder related records

ajax_save = AJAX Save
ajax_load = AJAX Load
ajax_form = AJAX Form

find_list = Search current table
find_multi_table = Perform multi-table searches

rss = RSS

xml_view = XML View ;; This should not be confused with view xml. This is
            ;; more of a management permission for a more complex
            ;; XML action.

view xml = View XML ;; This permission allows a record to be viewed as XML
            ;; It corresponds with the xml_feed action - but
            ;; works on individual records - rather than the entire
            ;; action

manage_output_cache = “Manage Output Cache”
manage_migrate = “Manage Migrations”
manage = “Manage Site”
manage_build_index=”Manage Build Search Index”
install = “Install and update applications. Administrator only”
expandable = “Whether the record can be expanded in the left nav menu”

;;=============================================================================
;;
;; Roles :
;; ——–
;;
;; The following are roles. Roles are basically just grouped permissions
;; that allow you to easily assign a group of permissions to an action or
;; record.
;;
;; Guidelines for Roles:
;; ———————
;; Role names should be in all caps (to differentiate them from permissions),
;; and cannot contain any commas or punctuation of any kind.
;; You can “extend” another role with the “extends” keyword. For example:
;; [READ]
;; view = 1
;;
;; [EDIT]
;; edit = 1
;;
;; [READ AND EDIT extends READ, EDIT]
;;
;; In the above example, we defined a READ role and an EDIT role. Then we
;; defined a “READ AND EDIT” role that extends READ and EDIT. What this means
;; is that the READ AND EDIT role contains all of the permissions contained
;; in the READ permission and the EDIT permission, and allows other permissions
;; to be added also.

[NO ACCESS]
register=1

;;——————————————————————————
;; The READ ONLY role is allowed to view records and perform the show all
;; and find actions. Basically, anything that doesn’t require making changes
;; is allowed with the READ ONLY permission

[READ ONLY]
view in rss=1
view = 1
link = 1
list = 1
calendar = 1
view xml = 1
show all = 1
find = 1
navigate = 1
ajax_load = 1
find_list = 1
find_multi_table = 1
rss = 1
export_csv = 1
export_xml = 1
export_json = 1
view related records=1
related records feed=1
expandable=1

;;——————————————————————————
;; The EDIT role extends the READ ONLY role so that anyone who can edit can also
;; READ. It is pretty far reaching, as it provides permissions to edit records,
;; and manipulate the records’ relationship by adding new and existing records
;; to the relationship.

[EDIT extends READ ONLY]
edit = 1
add new related record = 1
add existing related record = 1
add new record = 1
remove related record = 1
reorder_related_records = 1
import = 1
translate = 1
new = 1
ajax_save = 1
ajax_form = 1
history = 1
edit_history = 1
copy = 1
update_set = 1
update_selected=1
select_rows = 1

;;——————————————————————————
;; The DELETE role extends the EDIT role but adds the ability to delete
;; records and related records also. Notice that the EDIT permission allows
;; the removal of related records but not the deletion of the records. This is
;; relevant with ONE TO MANY relationships in which a record can only be removed
;; if the related record is deleted.

[DELETE extends EDIT]
delete = 1
delete found = 1
delete selected = 1

;;——————————————————————————
;; The EDIT AND DELETE role is basically an alias of the DELETE role.

[EDIT AND DELETE extends EDIT, DELETE]

;;——————————————————————————
;; The OWNER role is encapsulates the permissions that the owner of a record
;; should have. It allows full access to the current record, but not necessarily
;; full access to the table.

[OWNER extends EDIT AND DELETE]
navigate = 0
new = 0
delete found = 0

;;——————————————————————————
;; The REVIEWER role contains a subset of the EDIT role. It basically just allows
;; editing of the record itself, but not adding or removing related records.

[REVIEWER extends READ ONLY]
edit = 1
translate = 1

;;——————————————————————————
;; The USER role allows for reading and adding new related records. This would
;; be useful for users to be able to add comments to the comments relationship
;; but not be able to edit the record itself.
[USER extends READ ONLY]
add new related record = 1

;;——————————————————————————
;; The ADMIN role allows full acccess .. kind of like ALL
[ADMIN extends EDIT AND DELETE]
xml_view=1

[WEB SERVICE BROWSER extends READ ONLY]
xml_view=1

[WEB SERVICE ADMIN extends WEB SERVICE BROWSER, ADMIN]

[MANAGER extends ADMIN]
manage=1
manage_output_cache=1
manage_migrate=1
manage_build_index=1
install = 1`

You’re being very, very helpful, incidentally. Thank you.

00Davo — Mon Jan 04, 2010 6:34 pm

It’s not really a proper solution, but if you comment out line 15 in /xataface/actions/related_records_list.php, it works fine.

Code: Select all: //if ( !@$perms['view related records'] ) return Dataface_Error::permissionDenied('You don\'t have permission to view this relationship.');

The reason why this isn’t a proper solution is that if you have permissions implemented, they’ll be ignored when it comes to related records.

shannah — Mon Jan 04, 2010 6:48 pm

If you add the following just before that line, what is the output?

Code: Select all: print_r($perms);

I’m curious if it isn’t picking up any of the permissions or just some of them.

00Davo — Mon Jan 04, 2010 6:49 pm

Adding that line reveals the following messages above the Xataface interface.

Code: Select all: Array ( [view] => 1 [link] => 1 [rss] => 1 [list] => 1 [calendar] => 1 [edit] => 1 [new] => 1 [select_rows] => 1 [post] => 1 [copy] => 1 [update_set] => 1 [update_selected] => 1 [record] => 1 [delete] => 1 [selected] => 1 [found] => 1 [all] => 1 [records] => 1 [feed] => 1 [find] => 1 [import] => 1 [export_csv] => 1 [export_xml] => 1 [export_json] => 1 [translate] => 1 [history] => 1 [edit_history] => 1 [navigate] => 1 [reorder_related_records] => 1 [ajax_save] => 1 [ajax_load] => 1 [ajax_form] => 1 [find_list] => 1 [find_multi_table] => 1 [register] => 1 [xml_view] => 1 [xml] => 1 [manage_output_cache] => 1 [manage_migrate] => 1 [manage] => 1 [manage_build_index] => 1 [install] => 1 [expandable] => 1 )

shannah — Mon Jan 04, 2010 6:56 pm

Thanks.. that’s interesting… it’s like it’s not picking up the view related record permission.

If you check the permissions.ini file, can you confirm that there is a permission listed for ‘view related record’?

00Davo — Mon Jan 04, 2010 6:59 pm

Looks like there is.

Code: Select all: view related records=1

Here’s the whole file, just in case.

Code: Select all

`;;First we will list the permissions and their associated labels.

;; view : Allowed to view an element
view = View

;; link : Allowed to access the link to a record
link = Link

view in rss = View record as part of RSS feed

;; list : Allowed to see the list tab
list = List

calendar = calendar

;; edit : Allowed to edit information in an element
edit = Edit

;; new : Create a new record
new = New

;; select_rows : Allows to select rows in the result set to perform actions
;; to them
select_rows = Select Rows

;; post : Post a record using HTTP post
post = Post

;; Access to the Copy action
copy = Copy

;; Access to update set action
update_set = Update Set

update_selected=Update selected records

;; Ability to add a new related record to a relationship of a record.
add new related record = Add New Related Record

;; Ability to add an existing related record to a relationship of a record.
add existing related record = Add Existing Related Record

;; delete: Allowed to delete a record
delete = Delete

;; Ability to delete selected records from the database.
delete selected = Delete selected

;; Ability to add a new record to a table // Deprecated.. see if this breaks anything!!
;;add new record = Add New Record

;; Access to delete found records (the delete found records menu option)
delete found = Delete Found

;; Access to the show all action (to show all records in a table)
show all = Show All

;; Access to remove a related record from a relationship. This does not
;; delete the related record from the database - just from the relationship.
remove related record = Remove Related Record

;; View the records of a relationship
view related records = View related records

;; Access to RSS feed for related records
related records feed = Related Records RSS Feed

;; Access to the “find” tab
find = Find

;; Ability to import records
import = Import

;; Ability to export records in CSV format
export_csv = Export CSV

;; Ability to export records as XML
export_xml = Export XML

;; Ability to export records as JSON
export_json = Export JSON

;; Ability to translate records
translate = translate

;; Ability to see history information
history = View history information

edit_history = Edit history information

;; Ability to navigate through the records of this table.
navigate = Navigate

;; Ability to reorder the records in a relationship
reorder_related_records = Reorder related records

ajax_save = AJAX Save
ajax_load = AJAX Load
ajax_form = AJAX Form

find_list = Search current table
find_multi_table = Perform multi-table searches

rss = RSS

[NO ACCESS]
register=1

[DELETE extends EDIT]
delete = 1
delete found = 1
delete selected = 1

;;——————————————————————————
;; The EDIT AND DELETE role is basically an alias of the DELETE role.

[EDIT AND DELETE extends EDIT, DELETE]

[OWNER extends EDIT AND DELETE]
navigate = 0
new = 0
delete found = 0

[REVIEWER extends READ ONLY]
edit = 1
translate = 1

;;——————————————————————————
;; The ADMIN role allows full acccess .. kind of like ALL
[ADMIN extends EDIT AND DELETE]
xml_view=1

[WEB SERVICE BROWSER extends READ ONLY]
xml_view=1

[WEB SERVICE ADMIN extends WEB SERVICE BROWSER, ADMIN]

[MANAGER extends ADMIN]
manage=1
manage_output_cache=1
manage_migrate=1
manage_build_index=1
install = 1`

You’re being very, very helpful, incidentally. Thank you.

00Davo — Mon Jan 04, 2010 8:02 pm

I think the permissions problem goes a bit deeper than this one issue. From the documentation, I discover that there should be buttons in the relationship tab to let me add new related records, or associate existing records, and things like that. But no such buttons exist. I suspect that I lack the “add related record” permission too.

HWT — Tue Jan 05, 2010 9:21 am

I think OODavo has the exactly same problem. I have no news so far but I keep up following this thread.

shannah — Tue Jan 05, 2010 9:36 am

I suspect that this may be related to the PHP 5.3 parse_ini_file bug, but I won’t know for sure until I’m able to set up a PHP 5.3 instance. One other peculiar thing is in the print_r($perms) output you provided, it included a permission ‘all’. There is no such permission…. so somewhere along the line it is loading a phantom permission….

HWT — Tue Jan 05, 2010 10:02 am

shannah wrote:I suspect that this may be related to the PHP 5.3 parse_ini_file bug, but I won’t know for sure until I’m able to set up a PHP 5.3 instance. One other peculiar thing is in the print_r($perms) output you provided, it included a permission ‘all’. There is no such permission…. so somewhere along the line it is loading a phantom permission….

I support your guess that it’s in some way an incompatibility with PHP 5.3: I had the opportunity to move to an 5.3.x environment and the completely equal installation works.

shannah — Tue Jan 05, 2010 10:52 am

I had the opportunity to move to an 5.3.x environment and the completely equal installation works.

Do you mean 5.2.x?

shannah — Tue Jan 05, 2010 10:53 am

I’m installing Xamp right now with PHP 5.3.1 so I’ll be able to test and see what is happening some time today.

-Steve

HWT — Tue Jan 05, 2010 1:10 pm

shannah wrote:

I had the opportunity to move to an 5.3.x environment and the completely equal installation works.

Do you mean 5.2.x?

Yes, sorry for the typo

shannah — Tue Jan 05, 2010 6:08 pm

I finally got Xampp installed with PHP 5.3.0. It allowed me to see the mess. I have made some changes to work around the parse_ini_file bug, and I’ve done some work to remove all of those pesky deprecation warnings.

Note, that I haven’t done a great deal of testing on this release (hence the b1 marking), but nothing critical was really changed, so I think it should be ok.

You can find the download link here:

viewtopic.php?f=4&t=5101&p=24842#p24842

00Davo — Tue Jan 05, 2010 6:24 pm

I think the permissions were fixed, but I still have issues.

Code: Select all: Warning: defined() expects parameter 1 to be string, resource given in C:\xampp\xampp\htdocs\newypt\xataface\Dataface\QuickForm.php on line 213

I now have all the missing buttons, but when I try one (adding a existing related record), I just get the above error and this one:

Code: Select all: Warning: Cannot modify header information - headers already sent by (output started at C:\xampp\xampp\htdocs\newypt\xataface\Dataface\QuickForm.php:213) in C:\xampp\xampp\htdocs\newypt\xataface\actions\existing_related_record.php on line 107

The change is not actually made.

This is not good.

EDIT: It’s a many-to-many. The central linking table does receive a new record when I try to relate them, but it doesn’t include the foreign ID:

Code: Select all

`` –
– Table structure for table family
–

CREATE TABLE IF NOT EXISTS family (
ID int(11) NOT NULL AUTO_INCREMENT,
ParentID int(11) DEFAULT NULL,
StudentID int(11) DEFAULT NULL,
PRIMARY KEY (ID)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

–
– Dumping data for table family
–

INSERT INTO family (ID, ParentID, StudentID) VALUES
(1, 0, 8),
(2, 0, 10),
(3, 0, 10),
(4, 0, 10),
(5, 0, 10),
(6, 0, 10); ``

shannah — Tue Jan 05, 2010 6:39 pm

Change the line

Code: Select all: if ( !$db and defined(DATAFACE_DB_HANDLE) ){

Code: Select all: if ( !$db and defined('DATAFACE_DB_HANDLE') ){

(i.e. line 213 or Dataface/QuickForm.php)

See if that corrects the problem.

shannah — Tue Jan 05, 2010 10:53 am

I’m installing Xamp right now with PHP 5.3.1 so I’ll be able to test and see what is happening some time today.

-Steve

HWT — Tue Jan 05, 2010 1:10 pm

shannah wrote:

I had the opportunity to move to an 5.3.x environment and the completely equal installation works.

Do you mean 5.2.x?

Yes, sorry for the typo

shannah — Tue Jan 05, 2010 6:08 pm

Note, that I haven’t done a great deal of testing on this release (hence the b1 marking), but nothing critical was really changed, so I think it should be ok.

You can find the download link here:

viewtopic.php?f=4&t=5101&p=24842#p24842

00Davo — Tue Jan 05, 2010 6:24 pm

I think the permissions were fixed, but I still have issues.

Code: Select all: Warning: defined() expects parameter 1 to be string, resource given in C:\xampp\xampp\htdocs\newypt\xataface\Dataface\QuickForm.php on line 213

I now have all the missing buttons, but when I try one (adding a existing related record), I just get the above error and this one:

Code: Select all: Warning: Cannot modify header information - headers already sent by (output started at C:\xampp\xampp\htdocs\newypt\xataface\Dataface\QuickForm.php:213) in C:\xampp\xampp\htdocs\newypt\xataface\actions\existing_related_record.php on line 107

The change is not actually made.

This is not good.

EDIT: It’s a many-to-many. The central linking table does receive a new record when I try to relate them, but it doesn’t include the foreign ID:

Code: Select all

`` –
– Table structure for table family
–

–
– Dumping data for table family
–

INSERT INTO family (ID, ParentID, StudentID) VALUES
(1, 0, 8),
(2, 0, 10),
(3, 0, 10),
(4, 0, 10),
(5, 0, 10),
(6, 0, 10); ``

shannah — Tue Jan 05, 2010 6:39 pm

Change the line

Code: Select all: if ( !$db and defined(DATAFACE_DB_HANDLE) ){

Code: Select all: if ( !$db and defined('DATAFACE_DB_HANDLE') ){

(i.e. line 213 or Dataface/QuickForm.php)

See if that corrects the problem.

00Davo — Tue Jan 05, 2010 6:41 pm

The errors no longer show up, and it claims to work correctly. Except it doesn’t, because the table still isn’t getting the IDs correctly.

shannah — Tue Jan 05, 2010 7:09 pm

Please post the relationship definition in the relationships.ini file, and the associated table definitions.

00Davo — Tue Jan 05, 2010 7:14 pm

/tables/Parent/relationships.ini

Code: Select all: [Children] Student.ID = Family.StudentID Family.ParentID = "$ID"

/tables/Student/relationships.ini

Code: Select all: [Parents] Parent.ID = Family.ParentID Family.StudentID = "$ID" [Class] Class.ID = "$Class"

Structure for Parent:

Code: Select all: `` –
– Table structure for table parent
–
CREATE TABLE IF NOT EXISTS parent (
ID int(11) NOT NULL AUTO_INCREMENT,
LName varchar(50) DEFAULT NULL,
FName varchar(50) DEFAULT NULL,
HouseNo int(11) DEFAULT NULL,
Street varchar(50) DEFAULT NULL,
Suburb varchar(50) DEFAULT NULL,
Postcode int(11) DEFAULT NULL,
Phone varchar(11) DEFAULT NULL,
Mobile varchar(11) DEFAULT NULL,
Email varchar(50) DEFAULT NULL,
YPTReceiptNo int(11) DEFAULT NULL,
LSPReceiptNo int(11) DEFAULT NULL,
YPTFeePaid double DEFAULT NULL,
LSPFeePaid double DEFAULT NULL,
Involvement text,
PRIMARY KEY (ID)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ; ``

Structure for Student:

Code: Select all: `` –
– Table structure for table student
–
CREATE TABLE IF NOT EXISTS student (
ID int(11) NOT NULL AUTO_INCREMENT,
LName varchar(50) DEFAULT NULL,
FName varchar(50) DEFAULT NULL,
DOB date DEFAULT NULL,
HouseNo tinyint(4) DEFAULT NULL,
Street varchar(50) DEFAULT NULL,
Suburb varchar(50) DEFAULT NULL,
Postcode int(11) DEFAULT NULL,
Phone varchar(11) DEFAULT NULL,
Mobile varchar(11) DEFAULT NULL,
Email varchar(50) DEFAULT NULL,
EnrolmentStatus enum(‘WL’,’E’,’WD’) DEFAULT NULL,
SchoolYear year(4) DEFAULT NULL,
WaitingListPlace int(11) DEFAULT NULL,
WaitingListDate date DEFAULT NULL,
ClassPlacementDate date DEFAULT NULL,
ClassPlacementAcceptance tinyint(1) DEFAULT NULL,
ClassEnrolmentDate date DEFAULT NULL,
EnrolmentYear year(4) DEFAULT NULL,
Class int(11) DEFAULT NULL,
Status enum(‘S’,’A’) DEFAULT NULL,
FeesPaid double DEFAULT NULL,
ReceiptNo int(11) DEFAULT NULL,
PaymentMethod enum(‘dd’,’cc’,’c’,’cqe’) DEFAULT NULL,
Productions text,
Involvement text,
PRIMARY KEY (ID),
KEY FK_class (Class)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=13 ; ``

I’ve already posted the Family definitions.

shannah — Tue Jan 05, 2010 7:18 pm

One thing I notice is that you are referring to your table names capitalized in your relationship definitions, but they are actually all lower case in the database definition. MySQL may be forgiving for these things (case insensitive), but Xataface is case sensitive.

I.e. If you change your relationship definitions to have the correct table names it should work properly.

00Davo — Tue Jan 05, 2010 7:39 pm

Unfortunately, it still does not seem to work properly. It only fills in the “local” ID, and not the foreign one, so the relationship fails.

shannah — Tue Jan 05, 2010 8:35 pm

Currently Xataface works best if a relationship doesn’t have duplicate column names - and if they do have duplicate column names, that the two columns are meant to be bound so that their values can be used interchangeably.

The simple syntax for relationships unfortunately can’t decide which column to treat as the correct column when there are duplicates, so it results in somewhat undefined behavior. If you use SQL to define your relationship you can be much more specific and it will work correctly.

e.g.

Code: Select all: [Children] __sql__ = "select student.* from student inner join family on student.ID=family.StudentID and family.ParentID='$ID'"

Hopefully in future versions I’ll be able to refine Xataface’s intelligence further to be able to deal with duplicate column names.

00Davo — Tue Jan 05, 2010 8:40 pm

This is ridiculous. I’m STILL getting problems. I converted those relationships to raw SQL, and now receive an error like this whenever I try to declare the relationship:

Code: Select all: Fatal error: [pear_error: message="Could not add existing related record 'Tennant, David' because it can only belong to a single relationship and it already belongs to one." code=0 mode=return level=notice prefix="" info=""] in C:\xampp\xampp\htdocs\newypt\xataface\Dataface\Application.php on line 1607

The weirdest bit is that I didn’t actually select the record ‘Tennant, David’ as the related record, yet it’s complaining about it anyway.

shannah — Tue Jan 05, 2010 9:21 pm

I thought that might have been a workaround for the duplicate column name limitation but evidently it won’t work. The solution, then is to either:
a. Don’t use duplicate field names in a relationship. (e.g. Instead of calling your ID column just “ID”, call it “FamilyID, StudentID, and ParentID” respectively.
b. If you cannot change your table column names, create views for your tables that don’t have duplicate names and use those instead.

00Davo — Tue Jan 05, 2010 10:09 pm

This is very, very annoying. I’ve uniquifyed all the ID fields, fixed up the .inis to match, and I’m still getting the same error (although it’s at least citing the correct record now!)

Code: Select all: Fatal error: [pear_error: message="Could not add existing related record 'McLean, Robyn' because it can only belong to a single relationship and it already belongs to one." code=0 mode=return level=notice prefix="" info=""] in C:\xampp\xampp\htdocs\newypt\xataface\Dataface\Application.php on line 1607

shannah — Tue Jan 05, 2010 11:47 pm

I have set up your exact same table/relationship structure.. and I have set up a test environment on PHP 5.3.0 and I am not getting these errors. What does your relationship definition/table defs look like now?

-Steve

shannah — Tue Jan 05, 2010 8:35 pm

e.g.

Code: Select all: [Children] __sql__ = "select student.* from student inner join family on student.ID=family.StudentID and family.ParentID='$ID'"

Hopefully in future versions I’ll be able to refine Xataface’s intelligence further to be able to deal with duplicate column names.

00Davo — Tue Jan 05, 2010 8:40 pm

This is ridiculous. I’m STILL getting problems. I converted those relationships to raw SQL, and now receive an error like this whenever I try to declare the relationship:

Code: Select all: Fatal error: [pear_error: message="Could not add existing related record 'Tennant, David' because it can only belong to a single relationship and it already belongs to one." code=0 mode=return level=notice prefix="" info=""] in C:\xampp\xampp\htdocs\newypt\xataface\Dataface\Application.php on line 1607

The weirdest bit is that I didn’t actually select the record ‘Tennant, David’ as the related record, yet it’s complaining about it anyway.

shannah — Tue Jan 05, 2010 9:21 pm

00Davo — Tue Jan 05, 2010 10:09 pm

This is very, very annoying. I’ve uniquifyed all the ID fields, fixed up the .inis to match, and I’m still getting the same error (although it’s at least citing the correct record now!)

Code: Select all: Fatal error: [pear_error: message="Could not add existing related record 'McLean, Robyn' because it can only belong to a single relationship and it already belongs to one." code=0 mode=return level=notice prefix="" info=""] in C:\xampp\xampp\htdocs\newypt\xataface\Dataface\Application.php on line 1607

shannah — Tue Jan 05, 2010 11:47 pm

-Steve

00Davo — Wed Jan 06, 2010 12:36 am

/tables/Student/relationships.ini

Code: Select all: [Parents] __sql__ = "select parent.* from parent inner join family on parent.ParentID=family.ParentID and family.StudentID='$StudentID'" [Class] class.ClassID = "$Class"

/tables/Parent/relationships.ini

Code: Select all: [Children] __sql__ = "select student.* from student inner join family on student.StudentID=family.StudentID and family.ParentID='$ParentID'"

PHPMyAdmin table definitions:

Code: Select all

`` – phpMyAdmin SQL Dump
– version 3.2.4
– http://www.phpmyadmin.net
–
– Host: localhost
– Generation Time: Jan 06, 2010 at 06:46 PM
– Server version: 5.1.41
– PHP Version: 5.3.1

SET SQL_MODE=”NO_AUTO_VALUE_ON_ZERO”;

–
– Database: ypt
–

–
– Table structure for table student
–

CREATE TABLE IF NOT EXISTS student (
StudentID int(11) NOT NULL AUTO_INCREMENT,
LName varchar(50) DEFAULT NULL,
FName varchar(50) DEFAULT NULL,
DOB date DEFAULT NULL,
HouseNo tinyint(4) DEFAULT NULL,
Street varchar(50) DEFAULT NULL,
Suburb varchar(50) DEFAULT NULL,
Postcode int(11) DEFAULT NULL,
Phone varchar(20) DEFAULT NULL,
Mobile varchar(20) DEFAULT NULL,
Email varchar(50) DEFAULT NULL,
EnrolmentStatus enum(‘WL’,’E’,’WD’) DEFAULT NULL,
SchoolYear year(4) DEFAULT NULL,
WaitingListPlace int(11) DEFAULT NULL,
WaitingListDate date DEFAULT NULL,
ClassPlacementDate date DEFAULT NULL,
ClassPlacementAcceptance tinyint(1) DEFAULT NULL,
ClassEnrolmentDate date DEFAULT NULL,
EnrolmentYear year(4) DEFAULT NULL,
Class int(11) DEFAULT NULL,
Status enum(‘S’,’A’) DEFAULT NULL,
FeesPaid double DEFAULT NULL,
ReceiptNo int(11) DEFAULT NULL,
PaymentMethod enum(‘dd’,’cc’,’c’,’cqe’) DEFAULT NULL,
Productions text,
Involvement text,
PRIMARY KEY (StudentID)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=13 ;

–
– Table structure for table parent
–

CREATE TABLE IF NOT EXISTS parent (
ParentID int(11) NOT NULL AUTO_INCREMENT,
LName varchar(50) DEFAULT NULL,
FName varchar(50) DEFAULT NULL,
HouseNo int(11) DEFAULT NULL,
Street varchar(50) DEFAULT NULL,
Suburb varchar(50) DEFAULT NULL,
Postcode int(11) DEFAULT NULL,
Phone varchar(20) DEFAULT NULL,
Mobile varchar(20) DEFAULT NULL,
Email varchar(50) DEFAULT NULL,
YPTReceiptNo int(11) DEFAULT NULL,
LSPReceiptNo int(11) DEFAULT NULL,
YPTFeePaid double DEFAULT NULL,
LSPFeePaid double DEFAULT NULL,
Involvement text,
PRIMARY KEY (ParentID)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

–
– Table structure for table family
–

CREATE TABLE IF NOT EXISTS family (
FamilyID int(11) NOT NULL AUTO_INCREMENT,
ParentID int(11) DEFAULT NULL,
StudentID int(11) DEFAULT NULL,
PRIMARY KEY (FamilyID)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=16 ; ``

00Davo — Wed Jan 06, 2010 12:42 am

shannah wrote:I have set up a test environment on PHP 5.3.0 and I am not getting these errors.

Um - mine is PHP 3.5.1, since I upgraded to solve a previous problem getting this DB working.

shannah — Wed Jan 06, 2010 1:16 am

I have made some changes that should allow xataface to support duplicate columns in relationships. Preliminary testing looks promising… I’ll be uploading a dev version later today.

With respect to your relationships, now that you have changed your column names, you should change your relationship definitions back to the way they were originally (except with proper case in table names).

E.g. here are my relationships:

tables/student/relationships.ini

Code: Select all: [Parents] parent.ParentID = family.ParentID family.StudentID = "$StudentID"

tables/parent/relationships.ini

Code: Select all: [Children] family.ParentID = "$ParentID" student.StudentID = family.StudentID

00Davo — Wed Jan 06, 2010 1:20 am

Changing my relationships.inis as you just described, the student-parent relationship now seems to work perfectly. That, somehow, seems to have solved the issue.

Thanks for all the assistance.

shannah — Wed Jan 06, 2010 1:44 am

On a side note, I have uploaded version 1.2.3b2 which adds preliminary support for duplicate keys (i.e. with this version your relationships would have worked without having to change anything…
https://sourceforge.net/projects/datafa … z/download

I haven’t really run this version through the battery of tests, but initial tests looked good.

rugcutter — Fri Feb 19, 2010 1:27 pm

Incidentally we ran into this exact issue. We ended up downgrading PHP from 5.3.1 to 5.2.8.

rugcutter — Fri Feb 19, 2010 1:27 pm

Incidentally we ran into this exact issue. We ended up downgrading PHP from 5.3.1 to 5.2.8.