# HG changeset patch
# User František Kučera <franta-hg@frantovo.cz>
# Date 1670373530 -3600
# Node ID 4c0366e1b4dfb29690124b60bd0af003b56bd66e
# Parent  7727b4d5560dab7a8ee2232a14e55e4109a1567e
first version

diff -r 7727b4d5560d -r 4c0366e1b4df src/SQLHandler.h
--- 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();
 	}