Scodigo, Inc.

Seems like a good approach. Managing the serial numbers is a bit of a pain but if your script is the only way records get added, then it seems solid. You'll have an issue if another user sneaks in and creates a record while you're doing your restore (they'll grab a serial number).

In some cases we use a serial number table to give us better control. When a new record is created, we go to this table, create a new record, grab the serial number, then delete the record. It's more work but we get to decide when serial numbers are generated.

Regards,
Micah

I'm inserting data back into fm using PHP, rather than using the fm import mechanism. With a few dozen tables to dump and restore, it seems to make more sense to completely automate the process from PHP. My date/time conversion gymnastics might be avoidable if there is a way to make filemaker produce alternatively formatted data for a select, but I haven't found that switch yet.

There are a few moving parts to the process.

First, for each table I need to dump, I create a layout called <tablename>_backup, with only those fields I want to back up, ie eliminate calcs and globals, as inserting data back into those fields either won't work (calcs) or doesn't make sense (globals.)

Then, in PHP, I create an array of the field names and data types from the _backup layout:

$file_name = fm_evaluate('Get(FileName)');
$table_name = fm_evaluate('$$DUMP_TABLE');
$layout_name = $table_name . '_backup';

// get list of fields in layout

$eval_string = 'FieldNames("' . $file_name . '"; "' . $layout_name . '")';
$field_list = fm_evaluate($eval_string);

$a_field_names = explode("\r", $field_list);
sort($a_field_names); // should already be sorted alphabetically, but just in case...
$current_table_field_name_list = implode("\002", $a_field_names); // create a string of the field names to compare to the file we ingest

// create an array of field types for the fields in the layout keyed by field name

$a_field_types = array();

foreach ($a_field_names as $field_name) {

// get type string for field from fm
$eval_string = 'FieldType("' . $file_name . '"; "' . $table_name . '::' . $field_name . '")';
$type_string = fm_evaluate($eval_string);
// string has four value separated by spaces: <fm field type> <datatype> <index status> <repetition status>
preg_match('/^(.*?) (.*?) (.*?) (.*)/', $type_string, $a_fields);
// stuff datatype into an array
$a_field_types[$field_name] = $a_fields[2];

}

The field names in a list for a sql insert should be delimited with double quotes, as some field names are magic reserved words, ie "date" causes problems in an insert unless it's escaped, so we loop through the field name list for the insert and delimit them:

$a_delimited_field_names = $a_field_names;

// quote field names to avoid extra interpretation
// 'date' is the culprit bringing this to our attention
// extending it to all field names doesn't hurt
$double_quote = '"';
array_walk($a_delimited_field_names,
create_function (
'&$value, $key, $delimiter',
'$value = $delimiter . $value . $delimiter;'
),
$double_quote
);

Next, using my associative array of field types, <field_name> => <field_type>, I can correctly quote and format the insert data list for the fields, and create a comma-separated list of the field values for an insert. (I'm walking through two arrays with the same number of values, one a list of fields and the other the value data.)

// Store the values for the insert
$a_values = explode("\002", $record);

$a_delimited_values = array();
for ($i = 0; $i < count($a_values); $i++) {

// choose appropriate delimiters for INSERT values and fix data as needed

if (
($a_field_types[$a_field_names[$i]] == 'Date') ||
($a_field_types[$a_field_names[$i]] == 'Time') ||
($a_field_types[$a_field_names[$i]] == 'Timestamp')
)
{
if ($a_field_types[$a_field_names[$i]] == 'Date') {

// convert 2008-06-01 dates to 6/1/2008 format

preg_match('/(....)-(..)-(..)/', $a_values[$i], $a_date_parts);
$timestamp = mktime(0, 0, 0, $a_date_parts[2], $a_date_parts[3], $a_date_parts[1]);
$a_values[$i] = date('n/j/Y', $timestamp);

} elseif ($a_field_types[$a_field_names[$i]] == 'Time') {

// convert 16:24:07 times to 4:24:07 PM format

preg_match('/(.{1,2}):(..):(..)/', $a_values[$i], $a_date_parts);
$timestamp = mktime($a_date_parts[1], $a_date_parts[2], $a_date_parts[3]);
$a_values[$i] = date('g:i:s A', $timestamp);

} elseif ($a_field_types[$a_field_names[$i]] == 'Timestamp') {

// convert 2008-06-01 16:23:50 timestamp to 6/1/2008 4:23:50 PM format

preg_match('/(....)-(..)-(..) (.{1,2}):(..):(..)/', $a_values[$i], $a_date_parts);
$timestamp = mktime($a_date_parts[4], $a_date_parts[5], $a_date_parts[6], $a_date_parts[2], $a_date_parts[3], $a_date_parts[1] );
$a_values[$i] = date('n/j/Y g:i:s A', $timestamp);

}

$start_delimiter = '{';
$end_delimiter = '}';

} elseif ($a_field_types[$a_field_names[$i]] == 'Text') {
$a_values[$i] = str_replace("'", "''", $a_values[$i]); // escape single quotes
$start_delimiter = "'";
$end_delimiter = "'";
} else {
$start_delimiter = "";
$end_delimiter = "";
}

array_push($a_delimited_values, $start_delimiter . $a_values[$i] . $end_delimiter);

}

$values = implode(', ', $a_delimited_values);

Note that date/time fields need {} delimiters, text is quoted and numbers are naked.

I've left out some details, like including a field name list at the head of a dump file and checking it on import to see that it matches the field list to be inserted, but hopefully these snippets are useful.

rick