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; }