public function getCreateTable(Parser $parser)
{
$tableName = strtolower(trim($parser->getHeader('tbl')));
$sql = "\n-- DROP TABLE IF EXISTS " . strtolower($tableName) . ";\n";
$sql .= "CREATE TABLE " . strtolower($tableName) . " (\n";
$attrDefs = $parser->getAttributeDefs();
$isFirst = true;
foreach ($attrDefs as $attrName => $attrDef) {
if ($isFirst) {
$isFirst = false;
} else {
$sql .= ",\n";
}
if ($attrDef['type'] == 'char') {
if ($attrDef['width'] <= 4) {
$dataType = "CHAR({$attrDef['width']})";
} else {
$dataType = "VARCHAR({$attrDef['width']})";
}
} else {
if ($attrDef['type'] == 'num') {
if ($attrDef['scale'] == 0) {
if ($attrDef['width'] <= 4) {
$dataType = 'SMALLINT';
} else {
if ($attrDef['width'] <= 9) {
$dataType = 'INT';
} else {
if ($attrDef['width'] <= 18) {
$dataType = 'BIGINT';
} else {
$dataType = "DECIMAL({$attrDef['width']})";
}
}
}
} else {
$dataType = "DECIMAL({$attrDef['width']}, {$attrDef['scale']})";
}
} else {
throw new \DomainException("Unknown type '{$attrDef['type']}'");
}
}
$sql .= " " . strtolower(trim($attrName)) . " {$dataType}";
}
$sql .= "\n);";
return $sql;
}
public function pumpToDb(Connection $db, Parser $parser)
{
$tableName = strtolower(trim($parser->getHeader('tbl')));
$attrDefs = $parser->getAttributeDefs();
$recordsPumped = 0;
$sql = "INSERT INTO " . strtolower($tableName) . " (\n";
$isFirst = true;
foreach ($attrDefs as $attrName => $attrDef) {
if ($isFirst) {
$isFirst = false;
} else {
$sql .= ",\n";
}
$sql .= " " . strtolower(trim($attrName));
}
$sql .= "\n) VALUES (\n%s\n);\n";
foreach ($parser as $record) {
$values = array();
foreach ($attrDefs as $attrName => $attrDef) {
if (trim($record[$attrName]) === '') {
$values[] = 'NULL';
} else {
if ($attrDef['type'] == 'char') {
$values[] = $db->quote(iconv($this->sourceCharset, $this->targetCharset, trim($record[$attrName])));
} else {
$values[] = trim($record[$attrName]);
}
}
}
$valuesStr = " " . implode(",\n ", $values);
$recordsPumped++;
$insertSql = sprintf($sql, $valuesStr);
// echo $insertSql;
$db->exec($insertSql);
}
return $recordsPumped;
}