Import Filter

[Paul]

Hi,

I am trying to import a very simple csv and I am getting errors.

When I click import records when in a child relationship table I get the following error and the import record form page does not display.

Fatal error: Call to undefined method Dataface_ShortRelatedRecordForm::_buildWidget() in C:\Program Files\xampp\htdocs\dataface\Dataface\ImportForm.php on line 217

then if I try to import directly to this table (not via a relationship) the import record form appears correctly and recognizes the import csv function in my tables delegated class.

I browse my csv file and choose the import file format "csv" and click submit and I get the following error.

Fatal error: Cannot redeclare class tables_coreplugs in C:\Program Files\xampp\htdocs\database\tables\CorePlugs\CorePlugs.php on line 2

It is attempting to redeclare my delegate class.

Can anyone help. I really want to import under a relationship for obvious reasons.

Paul

[shannah]

Hi Paul,

Not sure why this is happening. This doesn't seem to be happening in my tests.

A couple of questions:

1. Are you using a constructor in your CorePlugs delegate class? If so, what does it do? (if it tries to load the base table it could cause a loop that would result in your "redeclare" error.

2. Can you provide a little bit of relevant data that I can look at. E.g. relevant portions of the fields.ini, relationships.ini and a sort of description of the table structure?

-STeve

[Paul]

Hi Steve,

Table structure is fairly simple.

A well has core plugs.

Here is the SQL Create Code:

Code: Select all

CREATE TABLE CorePlugs (
  idCorePlugs INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  Sample_idSample INTEGER UNSIGNED NOT NULL,
  Well_idWell INTEGER UNSIGNED NOT NULL,
  CorePlugCode VARCHAR(45) NULL,
  PlugOrientation VARCHAR(20) NULL,
  Depth FLOAT NULL,
  UOM_idDepthUnits INTEGER UNSIGNED NOT NULL,
  DepthDatums_idDepthDatums INTEGER UNSIGNED NOT NULL,
  datecreated TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  lastmodified DATETIME NULL,
  PRIMARY KEY(idCorePlugs, Sample_idSample, Well_idWell),
  INDEX CorePlug_FKIndex3(Sample_idSample),
  INDEX CorePlugs_FKIndex2(Well_idWell)
)
TYPE=InnoDB;

This is my coreplugs.php

Code: Select all

class tables_coreplugs{
   
   function __import__csv(&$data, $defaultValues=array()){
    // build an array of Dataface_Record objects that are to be inserted based
    // on the CSV file data.
    $records = array();
   
    // first split the CSV file into an array of rows.
    $rows = explode("\n", $data);
    foreach ( $rows as $row ){
        // We iterate through the rows and parse the name, phone number, and email
        // addresses to that they can be stored in a Dataface_Record object.
        list($coreplugcode, $plugorientation, $depth) = explode(',', $row);
        $record = new Dataface_Record('CorePlugs', array());
       
        // We insert the default values for the record.
        $record->setValues($defaultValues);
       
        // Now we add the values from the CSV file.
        $record->setValues(
            array(
                'CorePlugCode'=>$coreplugcode,
                'PlugOrientation'=>$plugorientation,
                'Depth'=>$depth
                 )
            );
       
        // Now add the record to the output array.
        $records[] = $record;
    }
   
    // Now we return the array of records to be imported.
    return $records;
}
}
?>

Fields.ini

Code: Select all

[idCorePlug]
widget:type = hidden
visibility:list = hidden
visibility:browse = hidden

[Sample_idSample]
widget:type = hidden
visibility:list = hidden
visibility:browse = hidden

[Well_idWell]
widget:label = "Well"
widget:type = select
vocabulary = Wells

[CorePlugCode]
widget:label = "Core Plug Code"

[UOM_idDepthUnits]
widget:label = "Elevation Unit"
widget:description = "Metres or Feet"
widget:type = select
vocabulary = UOM

[DepthDatums_idDepthDatums]
widget:label = "Depth Datum"
widget:type = select
vocabulary = DepthDatums

[datecreated]
widget:type = hidden
visibility:list = hidden
visibility:browse = hidden
widget:label = "Date Created"

[lastmodified]
widget:label = "Last Modified"
widget:type = hidden
timestamp = insert
visibility:browse = hidden


Relationship.ini

Code: Select all

[Well]
Well.idWell = "$Well_idWell"
action:condition="false"

[Sample]
Sample.idSample = "$Sample_idSample"
action:condition="false"

[DocumentLink]
DocumentLink.Sample_idSample = "$Sample_idSample"

[shannah]

One thing that occurs to me is that Xataface is case sensitive - and mysql on windows tends to alter the cases of the field and table names. I.e. verify that after you have created the table, of whether capital letters are used and where in the database schema.

On the Xataface end of things, if the table name is CorePlugs then the delegate class should be located at
tables/CorePlugs/CorePlugs.php
and not
tables/coreplugs/coreplugs.php

For class names I don't recall whether PHP handles them in a case insensitive manner, but it is good practice to name the delegate classes with case sensitivity in mind. e.g. if the table name is CorePlugs, the delegate class name should be
tables_CorePlugs and not tables_coreplugs .

In fact, I'm almost certain that this is where your "redefinition of the delegate class" errors are coming from. For example there is at least one place where you are referring to the name of your table as CorePlugs

Code: Select all

$record = new Dataface_Record('CorePlugs', array());


yet your delegate class name is tables_coreplugs.

At least these should be consistent.

As far as importing via a relationship goes, I'm assuming you were importing from another table and its relationship. This is because the import filter that you have defined here will only apply either directly to the CorePlugs table or to relationships whose destination records are from the CorePlugs table. None of the relationships that you have laid out here would apply.

Best regards

Steve

[Paul]

Hi Steve,

Correct the cases across all files and still the same problem.

I also tried using the same code with 2 other tables, 1 with no relationships and 1 with only a single parent child relationship and it worked fine.

So I believe there must be a problem due to multiple relationships.

The relationship.ini file for Wells is the following:

Code: Select all

[Core_Plugs]
CorePlugs.Well_idWell = "$idWell"
CorePlugs.Sample_idSample = Sample.idSample

Let me explain the reason for the CorePlugs.Sample_idSample = Sample.idSample relationship.

To expand on my table structure I have.

//This is a listing of Wells

Well
idWell PK
....

//This is to have a unique ID or record across multiple tables for every sample in the database be that a coreplug, sidewallcore etc.

Sample
idSample PK
.....

Then I have 5 table with the same relationships to Wells and Sample but different entities. CorePlugs is one of these tables.

CorePlugs
idCorePlugs PK
Well_idWell FK
Sample_idSample FK
.....


Relationships >>

Wells - 1 to many - CorePlugs
Wells - 1 to many - SidewallCores
etc... etc...

Then

Sample - 1 to 1 - CorePlugs
Sample - 1 to 1 - Sidewallcores


Currently the Sample table add a new item when I add a record to CorePlugs because of the relationship.

[Sample]
Sample.idSample = "$Sample_idSample"
action:condition="false"

Perhaps I am stretching what can be done or have a bad methology in regards to Sample. But I can't figure a better way to design it.

Paul

[Paul]

To Add.

Even with the single parent child relationship of a different set of tables when I click the import %childtablename% records button. I get the error

Fatal error: Call to undefined method Dataface_ShortRelatedRecordForm::_buildWidget() in C:\Program Files\xampp\htdocs\dataface\Dataface\ImportForm.php on line 217

See following image. I get this error when clicking on import field_station records

[shannah]

I have added a patch to fix this problem in version 0.7.1 at http://bugs.weblite.ca/view.php?id=99

Thanks for reporting this. I had actually fixed this one quite a while ago in SVN, but haven't come out with a new release in a while. Apologies for not recording the fix.

-STeve

[Paul]

Thanks Steve,

That patch worked fine for the Call to undefined method Error.

What are your thoughts on the Redeclare Error?

Paul

[shannah]

Hi Paul,

I have run across the "cannot redeclare" error a few times before. In each case the problem was case sensitivity typo with a table name or a delegate class.

Things to check:
1. The name of the tables in MySQL (case sensitive).
2. If the table is listed in the conf.ini file (the [_tables] section) make sure it matches case sensitivity.
3. In your __import__xxx() method make sure that any calls calls to the Dataface_Record constructor, or any other function or method calls are case sensitive correct.

If it is not a case sensitivity issue, then I'm stumped.

-Steve

[Paul]

Thanks Steve,

So it turns out it was the case in the conf.ini file. I have now gone through all my current .ini files to make all the case the same.

Now I am getting some weird results with the input from CSV on the same table CorePlugs.

It is saying that the records are imported successfully but it is not populating the Foreign Keys of the relationship like when just inserting a new record. So instead of importing into the table it is generating new tables in my database named for example: coreplugs__import_1205213181_260914

Should the Import CSV automatically complete the FK's?
and
Could you double check over my relationships and see if there is any problem?

Also in a related issue. Sometime I want to be able to just add a record directly into the child table CorePlugs but i cant get it to obtain the next PK from Sample.idSample to fill in the FK CorePlugs.Sample_idSample. This automatically happens when adding via a Well because of the WELL relationship.ini as below.

Code: Select all

[Sample]
Sample.idSample = "$Sample_idSample"
action:condition="false"

How do I define this same relationship when going straight into the CorePlugs table?

I tried using the following in CorePlugs.ini and it works great but if I add a CorePlugs via a Well then it runs this script twice creating 2 new Samples: This also seems like a dodgy way to do something xataface already does via a relationship.

Code: Select all

   function beforeInsert(&$record){
         
            $sql = "INSERT INTO Sample values (null)"; //Creates a new idSample in table Sample to populate in the CorePlugs Tables
            $res = mysql_query($sql);
            $lastID = mysql_insert_id();
            $record->setValue('Sample_idSample',$lastID);
      }

I apprieciate any help you can give. At the moment I am stuck as this is a core part of my application as most data will be from .csv files.

Paul

[shannah]

It is saying that the records are imported successfully but it is not populating the Foreign Keys of the relationship like when just inserting a new record. So instead of importing into the table it is generating new tables in my database named for example: coreplugs__import_1205213181_260914

The coreplugs__import__xxxxx table is just a temporary table to store the import data until you have previewed it and approved it. I have since changed the way the import works so that it no longer creates these tables (it just stores them in temporary text fields that are automatically deleted).

Should the Import CSV automatically complete the FK's?

If you import the records via the relationship (i.e. click on the relationship tab then click "import") then it should automatically populate foreign keys.

Could you double check over my relationships and see if there is any problem?

Well, upon further inspection of your relationship definition:

[Core_Plugs]
CorePlugs.Well_idWell = "$idWell"
CorePlugs.Sample_idSample = Sample.idSample

I do have some questions. So this relationship is defined in the "Well" table. So when you are importing the Sample records through this relationship, the Well record already exists. So it is the CorePlugs record that is not getting populated in the import. Correct?

Are there any required fields in the CorePlugs table? These could be preventing records from being inserted. -- actually in reviewing the schema for CorePlugs that you posted, is the idCorePlugs field auto_increment? If it is not, then that would be the problem.

Also in a related issue. Sometime I want to be able to just add a record directly into the child table CorePlugs but i cant get it to obtain the next PK from Sample.idSample to fill in the FK CorePlugs.Sample_idSample. This automatically happens when adding via a Well because of the WELL relationship.ini as below.

Isn't the CorePlugs <-> Sample a one-to-one relationship? If it is one-to-one, then all bets are off when it comes to importing records into it. (How can you import more than one record into a one-to-one relationship?)

I tried using the following in CorePlugs.ini and it works great but if I add a CorePlugs via a Well then it runs this script twice creating 2 new Samples: This also seems like a dodgy way to do something xataface already does via a relationship.

Well... one-to-one relationships aren't really covered by Xataface relationships, so you'll need to do something special for this anyways. I generally try to steer clear of one-to-one relationships in my database design because they are icky (I usually just merge them into a single table, because its easier).

One way to de-ickify things is to figure out which record is a child of the other. Is the Sample record a child of the CorePlugs record or vice versa. It makes things easier if the FK field is part of the child record. That way you can pass the parent's ID to the child when the record is being created. (This is assuming that you create the records lazily - i.e. don't create the child until you need it).

Identifying which is the parent and which is the child also allows us to avoid loopy logic in our triggers. I.e. if the parent creates the child record - the child should not also create the parent.

I don't have the whole picture here but it seems that the difficult lies in the one-to-one nature of the relationship that lies outside of the scope of Xataface relationships - so we don't really know which is the parent and which is the child (and hopefully they're not peers).

My guess is that CorePlugs is the parent? If this is the case, I change teh structure so that the Sample table has a FK to coreplugs and remove the FK from the Coreplugs table. If you're not at liberty to change the structure, then... there is a way to make it work... You just need to closely analyze the processes to make avoid loopy logic.

Hope this helps a little.

-Steve

[Paul]

The coreplugs__import__xxxxx table is just a temporary table to store the import data until you have previewed it and approved it. I have since changed the way the import works so that it no longer creates these tables (it just stores them in temporary text fields that are automatically deleted).

Ok... is this change in an upcoming release. When will the next release be out?

If you import the records via the relationship (i.e. click on the relationship tab then click "import") then it should automatically populate foreign keys

This is how I am attempting to import.

I do have some questions. So this relationship is defined in the "Well" table. So when you are importing the Sample records through this relationship, the Well record already exists. So it is the CorePlugs record that is not getting populated in the import. Correct?

Correct... This relationship creates a record in CorePlug as well as Sample which works fine when creating a single new CorePlug from Well.

I have put together a snippet of the ERD for this section. Note there are more subtype relationships like CorePlug and OutcropsSample.



Sample is a SuperType and OutcropSample and CorePlugs is a SubType.

All PK's are Autoincrement

Isn't the CorePlugs <-> Sample a one-to-one relationship? If it is one-to-one, then all bets are off when it comes to importing records into it. (How can you import more than one record into a one-to-one relationship?)

I see it adding a new Sample for each Record imported in a one-to-one relationship. I guess this is some fuzzy logic which xataface is not designed to handed.

Well... one-to-one relationships aren't really covered by Xataface relationships, so you'll need to do something special for this anyways. I generally try to steer clear of one-to-one relationships in my database design because they are icky (I usually just merge them into a single table, because its easier).

I have scratch my head over this a few times trying to work out the best way for this to work and maintain relationship integrity. The only thing CorePlugs and OutcropSample have in common is that they are samples which have the same tests conducted on them. They have completely different entities and different parents in Wells and Stations.

Joining them all together would create a lot of nulls in columns not used but perhaps this is the only way to make it work. But then I would have problems with using the import because the imported data would be very different for a coreplug or outcropsample.


I have only been able to find one ERD on the web with the same design and that was for Vehicles (supertype) my sample and Car, Tractor, Truck(subtype) my core plugs etc. and all vehicles have parts (my tests)

I found an explaination of what I have:

Subtype/supertype model - one base table linked to many 1:1 tables. Base table keeps common data (supertype), 1:1 tables hold subtype-related data. In general, two different subtypes shouldn't point to same row from base table. Kind of vertical partitioning with many auxiliary tables. Maps roughly to OOP inherited classes (ancestor is supertype, descendants are subtypes). Example: supertype = vehicle, subtype=motorcycle, car, truck...

Thanks Again for all your help.

Paul

[Paul]

Hi Steve,

Im working on another solution for the above using MySQL Triggers and stored procedures.

I just want to make sure the import via csv was working so i created a filter for a simple table which has no relationships etc.

But for some reason when I import via CSV I get the following.



The temp table name is imported into the prospect table and the temp table remains in my database.
Im sure this was working previouslly.

I have totally wiped xataface and started from scratch. And no luck.

Any idea.

here is the csv code.

Code: Select all

class tables_Prospect{
   
   function __import__csvformat(&$data, $defaultValues=array()){
    // build an array of Dataface_Record objects that are to be inserted based
    // on the CSV file data.
    $records = array();
   
    // first split the CSV file into an array of rows.
    $rows = explode("\n", $data);
    foreach ( $rows as $row ){
        // We iterate through the rows and parse the name, phone number, and email
        // addresses to that they can be stored in a Dataface_Record object.
        list($prospectname) = explode(',', $row);
        $record = new Dataface_Record('Prospect', array());
       
        // We insert the default values for the record.
        $record->setValues($defaultValues);
       
        // Now we add the values from the CSV file.
        $record->setValues(
            array(
                'ProspectName'=>$prospectname
                 )
            );
       
        // Now add the record to the output array.
        $records[] = $record;
    }
   
    // Now we return the array of records to be imported.
    return $records;
}
}
?>

Paul

[shannah]

Hi Paul,

Sorry for the delay. I've been pretty swamped.

The temp table name is imported into the prospect table and the temp table remains in my database.

This is bizarre. I'm not sure what might be causing this. Your import code looks fine. You may want to put some debugging in to see what the default values are that you are getting.

e.g.

Code: Select all

print_r($defaultValues);exit;


Hmm...

[Paul]

Hi Steve,

Good news I changed over to version 1.0 beta and the weird temp table problem has gone.

Also I am now able to grab the next auto increment from a secondary table (Sample) and add it to the table (OutcropSample) being added before each row using a MySQL Trigger and a Stored Procedure.

I have list them below for anyone else wanting to use the same idea.

The trigger is activated and calls the stored produced which adds a new row to the secondary table(sample), it then gets the Last Insert ID and stores it in a global variable.

On returning to the trigger the global variable @idSample replaces the value of Sample_idSample before it is inserted into the Outcropsample table.

This works perfect with the CSV import aswell. As each row executes the trigger.

Trigger:

Code: Select all

DELIMITER $$

DROP TRIGGER `OutcropSample_bi` $$

CREATE TRIGGER `OutcropSample_bi` BEFORE INSERT ON OutcropSample
FOR EACH ROW
BEGIN
CALL GET_idSample();
SET NEW.Sample_idSample = @idSample;
END $$

DELIMITER ;

Stored Procedure:

Code: Select all

DELIMITER $$

DROP PROCEDURE IF EXISTS `databasename`.`GET_idSample` $$
CREATE PROCEDURE `databasename`.`GET_idSample`()

BEGIN

INSERT INTO Sample VALUES(Null);

SET @idSample := LAST_INSERT_ID();

END $$

DELIMITER ;

Cheers

Paul