JSON export

I often make changes to HeidiSQL whenever I need it, but there's no clear way described to submit patches or anything.

Right now I've added JSON export to the list, based upon the latest version from SVN.

Can it be merged into the trunk if I send an updated exportgrid.pas?

Related to this, I also have older code around, that generates XML to a memory stream, including field definitions and connection parameters.

That XML is then combined with an xslt definition from a file, that describes how the output should look. That way, users can add their own export formats, simply by adding or modifying xslt files.

You can create any type of output like that. I've converted most of the existing export formats to xslt definitions, and added some new ones.

I've got xslt scripts for Delphi/C# arrays of records/structs, including generated type definitions. Because the connection parameters are even copied, you can even generate entirely working PHP code to connect to the database, query the data, and generate HTML output.

This system of using xslt to transform data is very flexible, and it allows users who cannot compile Delphi to add custom export formats.

No scripting language or special engines or external libraries are required to do this. The actual transformation can be as simple as this:


uses XMLDoc, XMLIntf;

function Transform(XMLContent,XSLContent: string): String;
var XML, XSL : IXMLDocument;
begin
XML := LoadXMLData(XMLContent);
XSL := LoadXMLData(XSLContent);
XML.DocumentElement.TransformNode(XSL.DocumentElement, Result)
end;

It's a bit like a plugin system. I think the added flexibility and customizable exports could provide HeidiSQL with one of the most advanced export options of all SQL clients.

I've used it myself for a while, and it was awesome, but I've stopped merging all changes into newer versions because it's just too time-consuming, and it gets increasingly complicated. It would be cool if I can merge some changes into the trunk and have them around with new updates automatically.

You can send me a svn patch.

I have quite a few programming rules for code in HeidiSQL, and I'm very picky. So please do not expect me to accept your code immediately.

Picky? Hehe, no way!

IMHO, the code that does the export should be refactored. I assume that on some level you'll agree with me.
So my hands were itching, but I've managed to contain myself, and injected my code entirely in your very personal style instead.

I can't add attachments, right? I'll just paste the patch here:

Index: exportgrid.dfm
===================================================================
--- exportgrid.dfm	(revision 4745)
+++ exportgrid.dfm	(working copy)
@@ -62,7 +62,8 @@
'SQL REPLACEs'
'LaTeX'
'Wiki markup'
-      'PHP Array')
+      'PHP Array'
+      'JSON')
TabOrder = 2
OnClick = grpFormatClick
end
Index: exportgrid.pas
===================================================================
--- exportgrid.pas	(revision 4745)
+++ exportgrid.pas	(working copy)
@@ -7,7 +7,7 @@
Dialogs, StdCtrls, ExtCtrls, Menus, ComCtrls, VirtualTrees, SynExportHTML, gnugettext;
type
-  TGridExportFormat = (efExcel, efCSV, efHTML, efXML, efSQLInsert, efSQLReplace, efLaTeX, efWiki, efPHPArray);
+  TGridExportFormat = (efExcel, efCSV, efHTML, efXML, efSQLInsert, efSQLReplace, efLaTeX, efWiki, efPHPArray, efJSON);
TfrmExportGrid = class(TForm)
btnOK: TButton;
@@ -69,9 +69,9 @@
FGrid: TVirtualStringTree;
FRecentFiles: TStringList;
const FFormatToFileExtension: Array[TGridExportFormat] of String =
-      (('csv'), ('csv'), ('html'), ('xml'), ('sql'), ('sql'), ('LaTeX'), ('wiki'), ('php'));
+      ('csv', 'csv', 'html', 'xml', 'sql', 'sql', 'LaTeX', 'wiki', 'php','json');
const FFormatToDescription: Array[TGridExportFormat] of String =
-      (('Excel CSV'), ('Delimited text'), ('HTML table'), ('XML'), ('SQL INSERTs'), ('SQL REPLACEs'), ('LaTeX'), ('Wiki markup'), ('PHP Array'));
+      ('Excel CSV', 'Delimited text', 'HTML table', 'XML', 'SQL INSERTs', 'SQL REPLACEs', 'LaTeX', 'Wiki markup', 'PHP Array', 'JSON');
procedure SaveDialogTypeChange(Sender: TObject);
function GetExportFormat: TGridExportFormat;
procedure SetExportFormat(Value: TGridExportFormat);
@@ -192,7 +192,7 @@
end;
end;
-  chkIncludeQuery.Enabled := ExportFormat in [efHTML, efXML];
+  chkIncludeQuery.Enabled := ExportFormat in [efHTML, efXML, efJSON];
Enable := ExportFormat = efCSV;
lblSeparator.Enabled := Enable;
editSeparator.Enabled := Enable;
@@ -582,6 +582,18 @@
end;
end;
+    efJSON: begin
+      // JavaScript Object Notation
+      Header := '{' + CRLF;
+      if chkIncludeQuery.Checked then
+        Header := Header + '  "query":"'+GridData.SQL+'",' + CRLF
+      else
+        Header := Header + '  "table":"'+TableName+'",' + CRLF ;
+
+      Header := Header + '  "rows":[';
+
+    end;
+
efXML: begin
// Imitate mysqldump's XML style
Header := '<?xml version="1.0" encoding="'+MainForm.GetCharsetByEncoding(Encoding)+'"?>' + CRLF + CRLF;
@@ -662,6 +674,8 @@
case ExportFormat of
efHTML: tmp := '        <tr>' + CRLF;
+      efJSON: tmp := #9'{' + CRLF;
+
efXML: tmp := #9'<row>' + CRLF;
efSQLInsert, efSQLReplace: begin
@@ -720,6 +734,25 @@
tmp := tmp + Data + Separator;
end;
+          efJSON: begin
+            if chkIncludeColumnNames.Checked then
+              tmp := tmp + #9#9+'"'+HTMLSpecialChars(Grid.Header.Columns[Col].Text) + '"';
+            if GridData.IsNull(Col) then
+              tmp := tmp + ': null, ' +CRLF
+            else begin
+              tmp := tmp + ': ';
+              case GridData.DataType(Col).Category of
+                dtcInteger,
+                dtcReal : tmp := tmp + data;
+                dtcBinary,
+                dtcSpatial : tmp := tmp + '"' + data + '"';
+              else
+                tmp := tmp + '"' + HTMLSpecialChars(data) + '"'
+              end;
+              tmp := tmp + ',' + CRLF
+            end;
+          end;
+
efXML: begin
// Print cell start tag.
tmp := tmp + #9#9'<field';
@@ -779,6 +812,8 @@
Delete(tmp, Length(tmp)-Length(Separator)+1, Length(Separator));
tmp := tmp + Terminator;
end;
+      efJSON:
+        tmp := tmp + #9'},' + CRLF;
efXML:
tmp := tmp + #9'</row>' + CRLF;
efSQLInsert, efSQLReplace: begin
@@ -806,6 +841,9 @@
'  </body>' + CRLF +
'</html>' + CRLF;
end;
+    efJSON: begin
+      tmp := '  ]' + CRLF + '}'
+    end;
efXML: begin
if chkIncludeQuery.Checked then
tmp := '</resultset>' + CRLF

Hang on, I realize that this works fine for my Python needs, where the last item in a list can be a comma, but that's probably not what other user want.

So better don't use the patch above yet; I'll provide a more universal JSON format...

So, here's the universal JSON version:

Index: exportgrid.dfm
===================================================================
--- exportgrid.dfm	(revision 4745)
+++ exportgrid.dfm	(working copy)
@@ -62,7 +62,8 @@
'SQL REPLACEs'
'LaTeX'
'Wiki markup'
-      'PHP Array')
+      'PHP Array'
+      'JSON')
TabOrder = 2
OnClick = grpFormatClick
end
Index: exportgrid.pas
===================================================================
--- exportgrid.pas	(revision 4745)
+++ exportgrid.pas	(working copy)
@@ -7,7 +7,7 @@
Dialogs, StdCtrls, ExtCtrls, Menus, ComCtrls, VirtualTrees, SynExportHTML, gnugettext;
type
-  TGridExportFormat = (efExcel, efCSV, efHTML, efXML, efSQLInsert, efSQLReplace, efLaTeX, efWiki, efPHPArray);
+  TGridExportFormat = (efExcel, efCSV, efHTML, efXML, efSQLInsert, efSQLReplace, efLaTeX, efWiki, efPHPArray, efJSON);
TfrmExportGrid = class(TForm)
btnOK: TButton;
@@ -69,9 +69,9 @@
FGrid: TVirtualStringTree;
FRecentFiles: TStringList;
const FFormatToFileExtension: Array[TGridExportFormat] of String =
-      (('csv'), ('csv'), ('html'), ('xml'), ('sql'), ('sql'), ('LaTeX'), ('wiki'), ('php'));
+      ('csv', 'csv', 'html', 'xml', 'sql', 'sql', 'LaTeX', 'wiki', 'php','json');
const FFormatToDescription: Array[TGridExportFormat] of String =
-      (('Excel CSV'), ('Delimited text'), ('HTML table'), ('XML'), ('SQL INSERTs'), ('SQL REPLACEs'), ('LaTeX'), ('Wiki markup'), ('PHP Array'));
+      ('Excel CSV', 'Delimited text', 'HTML table', 'XML', 'SQL INSERTs', 'SQL REPLACEs', 'LaTeX', 'Wiki markup', 'PHP Array', 'JSON');
procedure SaveDialogTypeChange(Sender: TObject);
function GetExportFormat: TGridExportFormat;
procedure SetExportFormat(Value: TGridExportFormat);
@@ -192,7 +192,7 @@
end;
end;
-  chkIncludeQuery.Enabled := ExportFormat in [efHTML, efXML];
+  chkIncludeQuery.Enabled := ExportFormat in [efHTML, efXML, efJSON];
Enable := ExportFormat = efCSV;
lblSeparator.Enabled := Enable;
editSeparator.Enabled := Enable;
@@ -582,6 +582,18 @@
end;
end;
+    efJSON: begin
+      // JavaScript Object Notation
+      Header := '{' + CRLF;
+      if chkIncludeQuery.Checked then
+        Header := Header + '  "query":"'+HTMLSpecialChars(GridData.SQL)+'",' + CRLF
+      else
+        Header := Header + '  "table":"'+HTMLSpecialChars(TableName)+'",' + CRLF ;
+
+      Header := Header + '  "rows":[';
+
+    end;
+
efXML: begin
// Imitate mysqldump's XML style
Header := '<?xml version="1.0" encoding="'+MainForm.GetCharsetByEncoding(Encoding)+'"?>' + CRLF + CRLF;
@@ -662,6 +674,8 @@
case ExportFormat of
efHTML: tmp := '        <tr>' + CRLF;
+      efJSON: tmp := #9'{' + CRLF;
+
efXML: tmp := #9'<row>' + CRLF;
efSQLInsert, efSQLReplace: begin
@@ -720,6 +734,25 @@
tmp := tmp + Data + Separator;
end;
+          efJSON: begin
+            if chkIncludeColumnNames.Checked then
+              tmp := tmp + #9#9+'"'+HTMLSpecialChars(Grid.Header.Columns[Col].Text) + '"';
+            if GridData.IsNull(Col) then
+              tmp := tmp + ': null, ' +CRLF
+            else begin
+              tmp := tmp + ': ';
+              case GridData.DataType(Col).Category of
+                dtcInteger,
+                dtcReal : tmp := tmp + data;
+                dtcBinary,
+                dtcSpatial : tmp := tmp + '"' + data + '"';
+              else
+                tmp := tmp + '"' + HTMLSpecialChars(data) + '"'
+              end;
+              tmp := tmp + ',' + CRLF
+            end;
+          end;
+
efXML: begin
// Print cell start tag.
tmp := tmp + #9#9'<field';
@@ -779,6 +812,11 @@
Delete(tmp, Length(tmp)-Length(Separator)+1, Length(Separator));
tmp := tmp + Terminator;
end;
+      efJSON:
+        begin
+          Delete(tmp, length(tmp)-2,2);
+          tmp := tmp + #9'},' + CRLF;
+        end;
efXML:
tmp := tmp + #9'</row>' + CRLF;
efSQLInsert, efSQLReplace: begin
@@ -806,6 +844,10 @@
'  </body>' + CRLF +
'</html>' + CRLF;
end;
+    efJSON: begin
+      S.Size := S.Size - 3;
+      tmp := CRLF+ '  ]' + CRLF + '}'
+    end;
efXML: begin
if chkIncludeQuery.Checked then
tmp := '</resultset>' + CRLF

IMHO, the code that does the export should be refactored. I assume that on some level you'll agree with me.

No, I don't.

Give me some time to check your patch.

Ok. fair enough :)

Is there any news about this?

No.

But I'll try to not to forget again about this issue :)

Done in r4945. Thanks to wouter_van_nifterick!

Works perfectly! Thank you so much.