general API question

[shiraz]

Hi again Shannah,

Thanks for the patch for the earlier issue -- I just saw your post now and will try it out and let you know if it works for me.

I have a very general question about the API and am seeking your advice. It's pretty easy and should only take you the length of time to read this to respond. I'm integrating a Dataface-run database with an Expression Engine site's membership database. I'm using triggers in Dataface to write changes to the EE MYSQL database directly. I had to be careful to inspect the EE code well to see exactly what it would do on different actions, and replicate that in my code in the triggers. That was tedious, but I've been successful.

Now I am coding it for the other direction, so that when a change is made in EE, it's reflected in the Dataface database. This is actually the easier job, in part because EE has some well-documented triggers already placed at all the vital points I require and in part because you have a great API with which to work with the Dataface database (score one for open source coding!). I just need to figure out how to insert/modify/delete the records in the Dataface database. Keeping in mind that I have relationships on the Dataface side for which I need to maintain referential integrity, three possibilities come to mind:


1. Write (add/modify/delete) directly to the database, ie. using PHP and mysql queries. For relationships, I would search first for relationships and modify/delete them accordingly. A bit long-winded, not the most elegant, but I know I can do it.

2. By giving a complete URI to Dataface to process, eg: http://www.thesite.net/database/index.php?MasterID=3262&-table=Master_Contact_List&-action=delete&-cursor=0&-limit=30&-delete-one=1

I'm not clear on how this would work but I believe it is possible, and it seems to me to be the simplest method, since it would take care of the relationships automatically, since that's in your code. Let me know if this is possible and if you recommend it.

3. Use the API. eg. the records object and some of the methods as you've documented them (and others that I see are not yet documented). The problem here is simply that I'm a bit early -- I'm having a hard time figuring out what to use considering the documentation on this is very young, AND this is my first exposure to OO in PHP and really to programming in OO at all, so it's a bit steep for me and I'm on a tight deadline. For example, I would want a way to delete a record, and any relationships of that record. I can't figure out if there is a method to do this automatically. If so, that'd be great (let me know!). If not, I realize I could use the method to obtain an array of relationships for a given record, and then delete each one using a separate delete method, but that does seem less preferable to option #2 above.


Ultimately I'd like know which of the above 3 methods you would recommend, or if there's something else I'm missing. In short, I just need to be able to delete records (and their relationships), modify records (and their relationships), and add records (no relationships).

Any advice you can offer me would be truly appreciated.


Happy Friday to you

Shiraz

[shannah]

Hi Shiraz,

This is the ever-present dilemma of database applications. I think that the best approach, if you are using the database with multiple systems, is to incorporate integrity constraints inside the database. Unfortunately you would require MySQL 5 to be able to do this, as far as I know.

Alternatively, you can use the Dataface API. Probably the best solution is to use the dataface API. That way you can capitalize on the triggers that you have already defined to maintain data integrity.

The Dataface_IO class gives you the ability to read and write records. And it will call your triggers.
Here is a sample:

Code: Select all

require_once 'dataface-public-api.php';
require_once 'Dataface/IO.php';


$io = new Dataface_IO('Profiles');  // Create new IO object to read/write on the Profiles table

$query = array('ProfileID' => 10);
$profile =& df_get_record('Profiles', $query); // load the Profile with ProfileID = 10
    // $profile is a Dataface_Record object

$res = $io->delete($profile);
    // Deletes the profile record from the database.
    // automatically fires the beforeDelete() and afterDelete() triggers
if ( PEAR::isError($res ) ){
    // The delete failed for some reason
    trigger_error($res->toString(), E_USER_ERROR);
} else {
    echo "Delete successful";
}



Hope this helps.

Best regards

Steve

[shiraz]

Hi Shannah,

Thanks for your response.


Actually the method you suggested I think will not work for maintaining data integrity, because:

a) the triggers in EE are fured after the transaction has taken place. So if I delete a record in EE, and then EE triggers some code I've written as you suggest above as you suggest, it will delete the record in Dataface, and then my afterDelete trigger in Dataface will try to delete the record in EE, but it's already been deleted in EE! It's redundant.

AND more signficantly

b) the relationships in Dataface will not be deleted.


So what I'm looking for are three methods in the Dataface API:
1. A method to create a new record. This is easy, and I already see what to use.
2. A method to delete a record and any relationships of that record. This is what happens when you delete a record in Dataface using the regular interface -- I just need to figure out what method to use, if there is any. ?
3. A method to modify a record and any relationships of that record. This also is what happens when you modify a record in Dataface using the regular interface -- again I just need to figure out what method to use, if there is any. ?



Also, just to let you know, I originally had my database using InnoDB tables, using mysql 4, not 5. InnoDB has built-in integrity constraints. That was great. But the problem is Dataface wouldn't play with InnoDB so I had to convert to the regular myISAM tables, which doesn't have integrity constraints. Sigh!


Cheers,

Shiraz

[shannah]

Hi Shiraz,

What kinds of problems were you having with Dataface and InnoDB?

>2. A method to delete a record and any relationships of that record.
>This is what happens when you delete a record in Dataface using the
>regular interface -- I just need to figure out what method to use,
>if there is any. ?

When you delete a record in Dataface using the regular interface, it does not delete the records in the relationships unless you write a trigger to do this. The regular dataface interface simply makes a call to the Dataface_IO::delete() method.

> 3. A method to modify a record and any relationships of that record.
> This also is what happens when you modify a record in Dataface using
> the regular interface -- again I just need to figure out what method
> to use, if there is any. ?

What do you mean by "modify a record and any relationships of that record?" Is this changing the value in a field of the record? How does this change the relationships?

Best regards

Steve

[shannah]

OK.. just tried out a few things with InnoDB tables. The only thing that I could find that didn't work was the full-text search. This is because InnoDB does not support full-text searches (i.e. the "MATCH" clause). I have filed a bug report on this at https://sourceforge.net/tracker/index.php?func=detail&aid=1482876&group_id=153729&atid=788932 and submitted a fix that corrects this issue.

Please let me know if there are any other issues with InnoDB tables.

Best regards

Steve

[shiraz]

Hi Steve,

1. Many thanks for the clarification re: deleting records. That explained everything, and I've got my system working nicely now. It also answered my question with respect to modifying relationship records.

2. Thanks also for the InnoDB fix. I have it on my to-do list to conver the tables to InnoDB and add primary key relationships down the road. That's a big plus for maintaining relationship integrity. I'll let you know if I run into any problems with the InnoDB tables.

Shiraz

[jitu]

hi Steve
again i m

steve i want to insert records in multiple table with form

let my table name is device_details and when i click on new record at that time some field of device details shouled be inserted into another table
let device details have 3 filed ab,bc,cd

i want to insert the value of bc field into another table say table name is type_details
and the value of ab and cd field should insert in to same table i.e device_details

second thing is i want cheak box like this dynamicaly

squaresign abc,bcd,kkk
squaresign abc1,bcd1,kkk1
squaresign abc2,bcd2,kkk2

i m getting

squaresign abc
squaresign abc1
squaresign abc2

dynamically from a table


waiting for ur reply
jitendra dinanath thakur
thakur_jitendra2005@yahoo.co.in

[shannah]

The current version of dataface doesn't play nice with editing multiple tables at a time. The next version will all for this.

I'm not sure I understand the checkbox question yet. Is it that you want a grid to show multiple records, with checkboxes for each field of each record to "enable" that field for that record. If so, you would probably be best to create a custom action and set up your own form for this.

-Steve