57 bool matches(const std::vector<AttributeMetadata>& a, const std::vector<AttributeMetadata>& b) { |
58 bool matches(const std::vector<AttributeMetadata>& a, const std::vector<AttributeMetadata>& b) { |
58 if (a.size() != b.size()) return false; |
59 if (a.size() != b.size()) return false; |
59 for (int i = 0, limit = a.size(); i < limit; i++) if (a[i].getTypeId() != b[i].getTypeId()) return false; |
60 for (int i = 0, limit = a.size(); i < limit; i++) if (a[i].getTypeId() != b[i].getTypeId()) return false; |
60 return true; |
61 return true; |
61 } |
62 } |
|
63 |
|
64 static void writeIdentifier(std::ostream& output, std::string identifier) { |
|
65 output << '"'; |
|
66 for (auto & ch : identifier) { |
|
67 if (ch == '"') output << "\"\""; |
|
68 else output << ch; |
|
69 } |
|
70 output << '"'; |
|
71 } |
|
72 |
|
73 static void writeValue(std::ostream& output, std::string value) { |
|
74 output << '\''; |
|
75 for (auto & ch : value) { |
|
76 if (ch == '\'') output << "''"; |
|
77 else output << ch; |
|
78 } |
|
79 output << '\''; |
|
80 } |
|
81 |
|
82 void writeRecordCount() { |
|
83 // currently disabled due to relpipe-in-sql parser issues with last comment without any following expression |
|
84 // output << "-- Record count: " << recordCount << std::endl; |
|
85 } |
|
86 |
62 public: |
87 public: |
63 |
88 |
64 SQLHandler(std::ostream& output, Configuration& configuration) : output(output), configuration(configuration) { |
89 SQLHandler(std::ostream& output, Configuration& configuration) : output(output), configuration(configuration) { |
65 } |
90 } |
66 |
91 |
67 void startRelation(string_t name, std::vector<AttributeMetadata> attributes) override { |
92 void startRelation(string_t name, std::vector<AttributeMetadata> attributes) override { |
68 // TODO: CREATE TABLE |
|
69 // TODO: escape identifiers |
|
70 // TODO: ALTER TABLE / add columns on duplicate relation name |
93 // TODO: ALTER TABLE / add columns on duplicate relation name |
|
94 // TODO: optionally omit CREATE/ALTER table (just INSERT) |
|
95 // TODO: optional transformation to upper/lower case |
71 // TODO: custom data type mapping |
96 // TODO: custom data type mapping |
72 // TODO: custom name transformation |
97 // TODO: custom primary key or other column properties |
|
98 // TODO: custom table properties |
73 // TODO: custom SQL script before/after stream/relation/record |
99 // TODO: custom SQL script before/after stream/relation/record |
74 // TODO: comments and/or custom comments |
100 // TODO: comments and/or custom comments + record count of each table as a comment |
75 // TODO: optional transactions: BEGIN/COMMIT/ROLLBACK for stream/relation/record |
101 // TODO: optional transactions: BEGIN/COMMIT/ROLLBACK for stream/relation/record |
76 output << "CREATE TABLE " << convertor.to_bytes(name) << " ( ... );" << std::endl; |
102 // TODO: optional wrapping at certain width (like 80 characters)? |
77 |
103 // TODO: optional syntax highlighting? |
78 if (firstAttributes.empty()) { |
104 // TODO: share code/behavior with relpipe-tr-sql (but it uses parametrized statements) |
79 firstAttributes = attributes; |
105 |
80 if (configuration.writeHeader) for (auto attr : attributes) attribute(configuration.writeTypes ? attr.getAttributeName() + L"::" + attr.getTypeName() : attr.getAttributeName()); |
106 if (currentTable.size()) { |
81 } else if (matches(firstAttributes, attributes)) { |
107 writeRecordCount(); |
|
108 output << std::endl; |
|
109 } |
|
110 |
|
111 currentTable = name; |
|
112 currentAttributes = attributes; |
|
113 recordCount = 0; |
|
114 valueCount = 0; |
|
115 |
|
116 output << "CREATE TABLE "; |
|
117 writeIdentifier(output, convertor.to_bytes(currentTable)); |
|
118 output << " (" << std::endl; |
|
119 for (size_t i = 0, limit = attributes.size(); i < limit; i++) { |
|
120 auto attribute = attributes[i]; |
|
121 output << "\t"; |
|
122 writeIdentifier(output, convertor.to_bytes(attribute.getAttributeName())); |
|
123 // TODO: support all data types + implement RelationalReaderValueHandler |
|
124 output << " TEXT"; |
|
125 if (i < (limit - 1)) output << ","; |
|
126 output << std::endl; |
|
127 |
|
128 } |
|
129 output << ");" << std::endl << std::endl; |
|
130 |
|
131 if (currentAttributes.empty()) { |
|
132 //if (configuration.writeHeader) for (auto attr : attributes) attribute(configuration.writeTypes ? attr.getAttributeName() + L"::" + attr.getTypeName() : attr.getAttributeName()); |
|
133 } else if (matches(currentAttributes, attributes)) { |
82 // do UNION ALL – just append the records |
134 // do UNION ALL – just append the records |
83 } else { |
135 } else { |
84 // 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)."); |
136 // 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)."); |
85 } |
137 } |
86 } |
138 } |
87 |
139 |
88 void attribute(const string_t& value) override { |
140 void attribute(const string_t& value) override { |
89 valueCount++; |
141 |
90 |
142 if (valueCount % currentAttributes.size() == 0) { |
91 if (value.size() > 0) { |
143 // TODO: optional use of function/procedure instead of INSERT |
92 output << QUOTE; |
144 // TODO: optional INSERT of multiple records |
93 for (auto ch : convertor.to_bytes(value)) { |
145 // TODO: custom line-ends + indentation |
94 if (ch == QUOTE) output << QUOTE << QUOTE; |
146 // TODO: optionally write also the column names |
95 else output << ch; |
147 recordCount++; |
96 } |
148 output << "INSERT INTO "; |
97 output << QUOTE; |
149 writeIdentifier(output, convertor.to_bytes(currentTable)); |
|
150 output << " VALUES ("; |
98 } |
151 } |
99 |
152 |
100 if (valueCount % firstAttributes.size()) { |
153 valueCount++; |
101 output << ","; |
154 |
|
155 if (value.size() > 0) { |
|
156 // TODO: support all data types + implement RelationalReaderValueHandler |
|
157 writeValue(output, convertor.to_bytes(value)); |
102 } else { |
158 } else { |
103 // TODO: INSERT INTO ... |
159 // TODO: support actual nulls when supported in the relpipe data format + just optional conversion from empty strings to NULLs |
104 // TODO: escape identifiers and values |
160 output << "NULL"; |
105 // TODO: optional use of function/procedure instead of INSERT |
161 } |
106 // TODO: optional INSERT of multiple records |
162 |
107 output << std::endl << "INSERT INTO ... VALUES ... ;" << std::endl;; |
163 if (valueCount % currentAttributes.size()) { |
|
164 output << ", "; |
|
165 } else { |
|
166 output << ");" << std::endl; |
108 valueCount = 0; |
167 valueCount = 0; |
109 } |
168 } |
110 } |
169 } |
111 |
170 |
112 void endOfPipe() { |
171 void endOfPipe() { |
|
172 if (currentTable.size()) { |
|
173 writeRecordCount(); |
|
174 } |
113 output.flush(); |
175 output.flush(); |
114 } |
176 } |
115 |
177 |
116 }; |
178 }; |
117 |
179 |