--- a/src/SQLHandler.h Wed Dec 07 00:41:11 2022 +0100
+++ b/src/SQLHandler.h Wed Dec 07 01:38:50 2022 +0100
@@ -44,10 +44,11 @@
private:
std::ostream& output;
Configuration& configuration;
- const char QUOTE = '"';
std::wstring_convert<std::codecvt_utf8<wchar_t>> convertor; // generate SQL always in UTF-8
- std::vector<AttributeMetadata> firstAttributes;
+ std::vector<AttributeMetadata> currentAttributes;
integer_t valueCount = 0;
+ integer_t recordCount = 0;
+ string_t currentTable;
/**
* @param a
@@ -59,26 +60,77 @@
for (int i = 0, limit = a.size(); i < limit; i++) if (a[i].getTypeId() != b[i].getTypeId()) return false;
return true;
}
+
+ static void writeIdentifier(std::ostream& output, std::string identifier) {
+ output << '"';
+ for (auto & ch : identifier) {
+ if (ch == '"') output << "\"\"";
+ else output << ch;
+ }
+ output << '"';
+ }
+
+ static void writeValue(std::ostream& output, std::string value) {
+ output << '\'';
+ for (auto & ch : value) {
+ if (ch == '\'') output << "''";
+ else output << ch;
+ }
+ output << '\'';
+ }
+
+ void writeRecordCount() {
+ // currently disabled due to relpipe-in-sql parser issues with last comment without any following expression
+ // output << "-- Record count: " << recordCount << std::endl;
+ }
+
public:
SQLHandler(std::ostream& output, Configuration& configuration) : output(output), configuration(configuration) {
}
void startRelation(string_t name, std::vector<AttributeMetadata> attributes) override {
- // TODO: CREATE TABLE
- // TODO: escape identifiers
// TODO: ALTER TABLE / add columns on duplicate relation name
+ // TODO: optionally omit CREATE/ALTER table (just INSERT)
+ // TODO: optional transformation to upper/lower case
// TODO: custom data type mapping
- // TODO: custom name transformation
+ // TODO: custom primary key or other column properties
+ // TODO: custom table properties
// TODO: custom SQL script before/after stream/relation/record
- // TODO: comments and/or custom comments
+ // TODO: comments and/or custom comments + record count of each table as a comment
// TODO: optional transactions: BEGIN/COMMIT/ROLLBACK for stream/relation/record
- output << "CREATE TABLE " << convertor.to_bytes(name) << " ( ... );" << std::endl;
-
- if (firstAttributes.empty()) {
- firstAttributes = attributes;
- if (configuration.writeHeader) for (auto attr : attributes) attribute(configuration.writeTypes ? attr.getAttributeName() + L"::" + attr.getTypeName() : attr.getAttributeName());
- } else if (matches(firstAttributes, attributes)) {
+ // TODO: optional wrapping at certain width (like 80 characters)?
+ // TODO: optional syntax highlighting?
+ // TODO: share code/behavior with relpipe-tr-sql (but it uses parametrized statements)
+
+ if (currentTable.size()) {
+ writeRecordCount();
+ output << std::endl;
+ }
+
+ currentTable = name;
+ currentAttributes = attributes;
+ recordCount = 0;
+ valueCount = 0;
+
+ output << "CREATE TABLE ";
+ writeIdentifier(output, convertor.to_bytes(currentTable));
+ output << " (" << std::endl;
+ for (size_t i = 0, limit = attributes.size(); i < limit; i++) {
+ auto attribute = attributes[i];
+ output << "\t";
+ writeIdentifier(output, convertor.to_bytes(attribute.getAttributeName()));
+ // TODO: support all data types + implement RelationalReaderValueHandler
+ output << " TEXT";
+ if (i < (limit - 1)) output << ",";
+ output << std::endl;
+
+ }
+ output << ");" << std::endl << std::endl;
+
+ if (currentAttributes.empty()) {
+ //if (configuration.writeHeader) for (auto attr : attributes) attribute(configuration.writeTypes ? attr.getAttributeName() + L"::" + attr.getTypeName() : attr.getAttributeName());
+ } else if (matches(currentAttributes, attributes)) {
// do UNION ALL – just append the records
} else {
// throw RelpipeSQLWriterException(L"To the SQL format we can convert only one relation or multiple relations that have same number of attributes of same types (relation and attribute names may differ – result is named after the first one).");
@@ -86,30 +138,40 @@
}
void attribute(const string_t& value) override {
- valueCount++;
-
- if (value.size() > 0) {
- output << QUOTE;
- for (auto ch : convertor.to_bytes(value)) {
- if (ch == QUOTE) output << QUOTE << QUOTE;
- else output << ch;
- }
- output << QUOTE;
+
+ if (valueCount % currentAttributes.size() == 0) {
+ // TODO: optional use of function/procedure instead of INSERT
+ // TODO: optional INSERT of multiple records
+ // TODO: custom line-ends + indentation
+ // TODO: optionally write also the column names
+ recordCount++;
+ output << "INSERT INTO ";
+ writeIdentifier(output, convertor.to_bytes(currentTable));
+ output << " VALUES (";
}
- if (valueCount % firstAttributes.size()) {
- output << ",";
+ valueCount++;
+
+ if (value.size() > 0) {
+ // TODO: support all data types + implement RelationalReaderValueHandler
+ writeValue(output, convertor.to_bytes(value));
} else {
- // TODO: INSERT INTO ...
- // TODO: escape identifiers and values
- // TODO: optional use of function/procedure instead of INSERT
- // TODO: optional INSERT of multiple records
- output << std::endl << "INSERT INTO ... VALUES ... ;" << std::endl;;
+ // TODO: support actual nulls when supported in the relpipe data format + just optional conversion from empty strings to NULLs
+ output << "NULL";
+ }
+
+ if (valueCount % currentAttributes.size()) {
+ output << ", ";
+ } else {
+ output << ");" << std::endl;
valueCount = 0;
}
}
void endOfPipe() {
+ if (currentTable.size()) {
+ writeRecordCount();
+ }
output.flush();
}