1 unit TestDBExport;
2 
3 {
4   Unit tests which are common to all datasets. Tests export to various formats.
5 }
6 
7 {$IFDEF FPC}
8   {$mode Delphi}{$H+}
9 {$ENDIF}
10 
11 interface
12 
13 uses
14   fpcunit, testregistry,
15   Classes, SysUtils, db, ToolsUnit, bufdataset,
16   fpDBExport,
17   fpXMLXSDExport,
18   fpdbfexport,
19   fpcsvexport,
20   fpfixedexport,
21   fpSimpleXMLExport,
22   fpsimplejsonexport,
23   fpSQLExport,
24   fptexexport,
25   fprtfexport;
26 
27 
28 type
29   TDetailedExportFormats = (efDBaseIII, efDBaseIV, efDBaseVII, efCSV, efFixedLengthText, efFoxpro,
30     efJSON, efRTF, efSQL, efTeX, efXML, efXMLXSDAccess, efXMLXSDADONet, efXMLXSDClientDataset,
31     efXMLXSDExcel, efVisualFoxpro);
32 const
33   TDetailedExportExtensions: array [TDetailedExportFormats] of string[5] =
34     ('.dbf','.dbf','.dbf','.csv','.txt','.dbf','.json','.rtf','.sql','.tex',
35     '.xml','.xml','.xml','.xml','.xml','.dbf'); //File extension for the corresponding TDetailedExportFormats
36 type
37   { TTestDBExport }
38   TTestDBExport = class(TTestCase)
39   private
40     FExportTempDir: string; //directory where test files are placed
41     FKeepFilesAfterTest: boolean; //remove files after testing?
FieldSupportednull42     function FieldSupported(const FieldType: TFieldType;
43       const ExportSubFormat: TDetailedExportFormats): boolean; //Checks if output dataset supports a certain field type
44     procedure GenericExportTest(Exporter: TCustomDatasetExporter; ExportFormat: TDetailedExportFormats);
GetABCDSnull45     function GetABCDS: TBufDataset;
GetBooleanDSnull46     function GetBooleanDS: TBufDataset;
GetFileSizenull47     function GetFileSize(const FileName: string): integer; //Gets a file's size
48     function GetWideStringDS: TBufDataset;
49   protected
50     procedure SetUp; override;
51     procedure TearDown; override;
52   published
53     procedure TestDBFExport_DBaseIV;
54     procedure TestDBFExport_DBaseVII;
55     procedure TestDBFExport_FoxPro;
56     procedure TestDBFExport_VisualFoxPro;
57     procedure TestCSVExport; //tests csv export with default values
58     procedure TestCSVExport_RFC4180WithHeader; //tests csv export with settings that match RFC4180
59     procedure TestCSVExport_TweakSettingsSemicolon; //tests semicolon delimited, custom country values
60     procedure TestFixedTextExport;
61     procedure TestFixedTextExportBoolean;
62     procedure TestFixedTextExportUTF8;
63     procedure TestFixedTextExportUTF16;
64     procedure TestFixedTextExportHeader;
65     procedure TestFixedTextExportSpaces;
66     procedure TestJSONExport;
67     procedure TestRTFExport;
68     procedure TestSQLExport;
69     procedure TestTeXExport;
70     procedure TestXMLExport; //tests simple xml export
71     procedure TestXMLExportSpecialChars;
72     procedure TestXSDExport_Access_NoXSD_DecimalOverride; //tests xmlxsd export
73     procedure TestXSDExport_Access_NoXSD_NoDecimalOverride; //tests xmlxsd export
74     procedure TestXSDExport_Access_XSD_DecimalOverride; //tests xmlxsd export
75     procedure TestXSDExport_Access_XSD_NoDecimalOverride; //tests xmlxsd export
76     procedure TestXSDExport_ADONET_NoXSD; //tests xmlxsd export
77     procedure TestXSDExport_ADONET_XSD; //tests xmlxsd export
78     procedure TestXSDExport_DelphiClientDataset; //tests xmlxsd export
79     procedure TestXSDExport_Excel; //tests xmlxsd export
80   end;
81 
82 implementation
83 
84 uses xmlread,dom;
85 
86 
FieldSupportednull87 function TTestDBExport.FieldSupported(const FieldType: TFieldType;
88   const ExportSubFormat: TDetailedExportFormats): boolean;
89 const
90   // Alphabetically sorted for quick review:
91   DBaseVIIUnsupported=[ftADT,ftArray,ftBCD,ftBytes,ftCurrency,ftCursor,ftDataSet,
92     ftFixedWideChar,
93     ftFMTBcd,ftFmtMemo,ftGraphic,ftGuid,ftIDispatch,ftInterface,ftOraBlob,
94     ftOraClob,ftParadoxOle,ftReference,ftTime,ftTimeStamp,ftTypedBinary,
95     ftUnknown,ftVarBytes,ftVariant,ftWidememo,ftWideString];
96   FoxProUnsupported=  [ftADT,ftArray,      ftBytes,           ftCursor,ftDataSet,
97     ftFixedWideChar,
98     ftFMTBcd,ftFmtMemo,ftGraphic,ftGuid,ftIDispatch,ftInterface,ftOraBlob,
99     ftOraClob,ftParadoxOle,ftReference,ftTime,ftTimeStamp,ftTypedBinary,
100     ftUnknown,ftVarBytes,ftVariant,ftWideMemo,ftWideString];
101 begin
102   result:=true;
103   case ExportSubFormat of
104     efDBaseIII: if FieldType in DBaseVIIUnsupported+[ftAutoInc] then result:=false;
105     efDBaseIV: if FieldType in DBaseVIIUnsupported+[ftAutoInc] then result:=false;
106     efDBaseVII: if FieldType in DBaseVIIUnsupported then result:=false;
107     efCSV: result:=true;
108     efFixedLengthText: result:=true; //todo: verify if all fields are really supported. Quick glance would indicate so
109     efFoxpro: if FieldType in FoxProUnsupported then result:=false;
110     efVisualFoxpro: if FieldType in FoxProUnsupported-[ftVarBytes] then result:=false;
111     efJSON: result:=true;
112     efRTF: result:=true;
113     efSQL: result:=true;
114     efTeX: result:=true;
115     efXML: result:=true;
116     efXMLXSDAccess, efXMLXSDADONet, efXMLXSDClientDataset, efXMLXSDExcel: result:=true;
117   else
118     result:=false;
119     Fail('Error in test code itself: FieldSupported unknown ExportSubFormat '+inttostr(ord(ExportSubFormat)));
120   end;
121 end;
122 
123 procedure TTestDBExport.GenericExportTest(Exporter: TCustomDatasetExporter; ExportFormat: TDetailedExportFormats);
124 var
125   FieldMapping: TExportFields;
126   NumberExported: integer;
127   i: integer;
128 begin
129   FieldMapping:=TExportFields.Create(Exporter.ExportFields.ItemClass);
130   try
131     Exporter.Dataset := DBConnector.GetFieldDataset;
132     Exporter.Dataset.Open;
133     Exporter.BuildDefaultFieldMap(FieldMapping);
134     // Remove unsupported data types in export from the mapping.
135     // Cannot use FieldMapping[i].Field.DataType as
136     // the field hasn't been set by BindFields yet... assume the
137     // order of original fields and their mapping match
138     for i:=Exporter.Dataset.Fields.Count-1 downto 0 do
139     begin
140       if not FieldSupported(
141         Exporter.Dataset.Fields[i].DataType,
142         ExportFormat) then
143           FieldMapping.Delete(i);
144     end;
145     for i:=0 to FieldMapping.Count-1 do
146       Exporter.ExportFields.Add.Assign(FieldMapping[i]);
147     NumberExported := Exporter.Execute;
148     Exporter.Dataset.Last;
149     Exporter.Dataset.First;
150     AssertEquals('Number of records exported matches recordcount', NumberExported,
151       Exporter.Dataset.RecordCount);
152     Exporter.Dataset.Close;
153   finally
154     FieldMapping.Free;
155   end;
156 end;
157 
TTestDBExport.GetFileSizenull158 function TTestDBExport.GetFileSize(const FileName: string): integer;
159 Var
160   F : file of byte;
161 begin
162   result:=0;
163   assign (F,FileName);
164   try
165     reset(F);
166     result:=filesize(F);
167   finally
168     close(F);
169   end;
170 end;
171 
172 procedure TTestDBExport.SetUp;
173 begin
174   inherited SetUp;
175   InitialiseDBConnector;
176   DBConnector.StartTest(TestName);
177   FExportTempDir:=IncludeTrailingPathDelimiter(ExpandFileName(''))+'exporttests'+PathDelim; //Store output in subdirectory
178   ForceDirectories(FExportTempDir);
179   // FKeepFilesAfterTest:=true; //keep test files; consistent with other units right now
180 end;
181 
182 procedure TTestDBExport.TearDown;
183 begin
184   inherited TearDown;
185   DBConnector.StopTest(TestName);
186   FreeDBConnector;
187 end;
188 
189 procedure TTestDBExport.TestDBFExport_DBaseVII;
190 var
191   Exporter: TFPDBFExport;
192   ExportFormat: TDetailedExportFormats;
193   ExportSettings:TDBFExportFormatSettings;
194 begin
195   Exporter := TFPDBFExport.Create(nil);
196   ExportSettings:=TDBFExportFormatSettings.Create(true);
197   try
198     ExportFormat:=efDBaseVII;
199     ExportSettings.TableFormat:=tfDBaseVII;
200     ExportSettings.AutoRenameFields:=true; //rename conflicting column names
201     // Use export subtype position to differentiate output filenames:
202     Exporter.FileName := FExportTempDir + inttostr(ord(ExportFormat)) +
203       lowercase(rightstr(TestName,5)) +
204       TDetailedExportExtensions[ExportFormat];
205     Exporter.FormatSettings:=ExportSettings;
206     GenericExportTest(Exporter, ExportFormat);
207     AssertTrue('Output file must be created', FileExists(Exporter.FileName));
208     AssertFalse('Output file must not be empty', (GetFileSize(Exporter.FileName) = 0));
209   finally
210     if (FKeepFilesAfterTest = False) then
211       DeleteFile(Exporter.FileName);
212     ExportSettings.Free;
213     Exporter.Free;
214   end;
215 end;
216 
217 procedure TTestDBExport.TestDBFExport_DBaseIV;
218 var
219   Exporter: TFPDBFExport;
220   ExportFormat: TDetailedExportFormats;
221   ExportSettings:TDBFExportFormatSettings;
222 begin
223   Exporter := TFPDBFExport.Create(nil);
224   ExportSettings:=TDBFExportFormatSettings.Create(true);
225   try
226     ExportFormat:=efDBaseIV;
227     ExportSettings.TableFormat:=tfDBaseIV;
228     ExportSettings.AutoRenameFields:=true; //rename conflicting column names
229     Exporter.FileName := FExportTempDir + inttostr(ord(ExportFormat)) +
230       lowercase(rightstr(TestName,5)) +
231       TDetailedExportExtensions[ExportFormat];
232     Exporter.FormatSettings:=ExportSettings;
233     GenericExportTest(Exporter, ExportFormat);
234     AssertTrue('Output file must be created', FileExists(Exporter.FileName));
235     AssertFalse('Output file must not be empty', (GetFileSize(Exporter.FileName) = 0));
236   finally
237     if (FKeepFilesAfterTest = False) then
238       DeleteFile(Exporter.FileName);
239     ExportSettings.Free;
240     Exporter.Free;
241   end;
242 end;
243 
244 procedure TTestDBExport.TestDBFExport_FoxPro;
245 var
246   Exporter: TFPDBFExport;
247   ExportFormat: TDetailedExportFormats;
248   ExportSettings:TDBFExportFormatSettings;
249 begin
250   Exporter := TFPDBFExport.Create(nil);
251   ExportSettings:=TDBFExportFormatSettings.Create(true);
252   try
253     ExportFormat:=efFoxpro;
254     ExportSettings.TableFormat:=tfFoxPro;
255     ExportSettings.AutoRenameFields:=true; //rename conflicting column names
256     Exporter.FileName := FExportTempDir + inttostr(ord(ExportFormat)) +
257       lowercase(rightstr(TestName,5)) +
258       TDetailedExportExtensions[ExportFormat];
259     Exporter.FormatSettings:=ExportSettings;
260     GenericExportTest(Exporter, ExportFormat);
261     AssertTrue('Output file must be created', FileExists(Exporter.FileName));
262     AssertFalse('Output file must not be empty', (GetFileSize(Exporter.FileName) = 0));
263   finally
264     if (FKeepFilesAfterTest = False) then
265       DeleteFile(Exporter.FileName);
266     ExportSettings.Free;
267     Exporter.Free;
268   end;
269 end;
270 
271 procedure TTestDBExport.TestDBFExport_VisualFoxPro;
272 var
273   Exporter: TFPDBFExport;
274   ExportFormat: TDetailedExportFormats;
275   ExportSettings:TDBFExportFormatSettings;
276 begin
277   Exporter := TFPDBFExport.Create(nil);
278   ExportSettings:=TDBFExportFormatSettings.Create(true);
279   try
280     ExportFormat:=efVisualFoxpro;
281     ExportSettings.TableFormat:=tfVisualFoxPro;
282     ExportSettings.AutoRenameFields:=true; //rename conflicting column names
283     Exporter.FileName := FExportTempDir + inttostr(ord(ExportFormat)) +
284       lowercase(rightstr(TestName,5)) +
285       TDetailedExportExtensions[ExportFormat];
286     Exporter.FormatSettings:=ExportSettings;
287     GenericExportTest(Exporter, ExportFormat);
288     AssertTrue('Output file must be created', FileExists(Exporter.FileName));
289     AssertFalse('Output file must not be empty', (GetFileSize(Exporter.FileName) = 0));
290   finally
291     if (FKeepFilesAfterTest = False) then
292       DeleteFile(Exporter.FileName);
293     ExportSettings.Free;
294     Exporter.Free;
295   end;
296 end;
297 
298 procedure TTestDBExport.TestXSDExport_Access_NoXSD_DecimalOverride;
299 var
300   Exporter: TXMLXSDExporter;
301   ExportFormat: TDetailedExportFormats;
302   ExportSettings:TXMLXSDFormatSettings;
303 begin
304   Exporter := TXMLXSDExporter.Create(nil);
305   ExportSettings:=TXMLXSDFormatSettings.Create(true);
306   try
307     ExportSettings.ExportFormat:=AccessCompatible;
308     ExportFormat:=efXMLXSDAccess;
309     ExportSettings.CreateXSD:=false;
310     ExportSettings.DecimalSeparator:='.'; //override
311     Exporter.FileName := FExportTempDir + inttostr(ord(ExportFormat)) +
312       lowercase(rightstr(TestName,5)) +
313       TDetailedExportExtensions[ExportFormat];
314     Exporter.FormatSettings:=ExportSettings;
315     GenericExportTest(Exporter, ExportFormat);
316     AssertTrue('Output file must be created', FileExists(Exporter.FileName));
317     AssertFalse('Output file must not be empty', (GetFileSize(Exporter.FileName) = 0));
318   finally
319     if (FKeepFilesAfterTest = False) then
320       DeleteFile(Exporter.FileName);
321     ExportSettings.Free;
322     Exporter.Free;
323   end;
324 end;
325 
326 procedure TTestDBExport.TestXSDExport_Access_NoXSD_NoDecimalOverride;
327 var
328   Exporter: TXMLXSDExporter;
329   ExportFormat: TDetailedExportFormats;
330   ExportSettings:TXMLXSDFormatSettings;
331 begin
332   Exporter := TXMLXSDExporter.Create(nil);
333   ExportSettings:=TXMLXSDFormatSettings.Create(true);
334   try
335     ExportSettings.ExportFormat:=AccessCompatible;
336     ExportFormat:=efXMLXSDAccess;
337     ExportSettings.CreateXSD:=false;
338     ExportSettings.DecimalSeparator:=char(''); //don't override
339     Exporter.FileName := FExportTempDir + inttostr(ord(ExportFormat)) +
340       lowercase(rightstr(TestName,5)) +
341       TDetailedExportExtensions[ExportFormat];
342     Exporter.FormatSettings:=ExportSettings;
343     GenericExportTest(Exporter, ExportFormat);
344     AssertTrue('Output file must be created', FileExists(Exporter.FileName));
345     AssertFalse('Output file must not be empty', (GetFileSize(Exporter.FileName) = 0));
346   finally
347     if (FKeepFilesAfterTest = False) then
348       DeleteFile(Exporter.FileName);
349     ExportSettings.Free;
350     Exporter.Free;
351   end;
352 end;
353 
354 procedure TTestDBExport.TestXSDExport_Access_XSD_DecimalOverride;
355 var
356   Exporter: TXMLXSDExporter;
357   ExportFormat: TDetailedExportFormats;
358   ExportSettings:TXMLXSDFormatSettings;
359 begin
360   Exporter := TXMLXSDExporter.Create(nil);
361   ExportSettings:=TXMLXSDFormatSettings.Create(true);
362   try
363     ExportSettings.ExportFormat:=AccessCompatible;
364     ExportFormat:=efXMLXSDAccess;
365     ExportSettings.CreateXSD:=true;
366     ExportSettings.DecimalSeparator:='.'; //override
367     Exporter.FileName := FExportTempDir + inttostr(ord(ExportFormat)) +
368       lowercase(rightstr(TestName,5)) +
369       TDetailedExportExtensions[ExportFormat];
370     Exporter.FormatSettings:=ExportSettings;
371     GenericExportTest(Exporter, ExportFormat);
372     AssertTrue('Output file must be created', FileExists(Exporter.FileName));
373     AssertFalse('Output file must not be empty', (GetFileSize(Exporter.FileName) = 0));
374   finally
375     if (FKeepFilesAfterTest = False) then
376       DeleteFile(Exporter.FileName);
377     ExportSettings.Free;
378     Exporter.Free;
379   end;
380 end;
381 
382 procedure TTestDBExport.TestXSDExport_Access_XSD_NoDecimalOverride;
383 var
384   Exporter: TXMLXSDExporter;
385   ExportFormat: TDetailedExportFormats;
386   ExportSettings:TXMLXSDFormatSettings;
387 begin
388   Exporter := TXMLXSDExporter.Create(nil);
389   ExportSettings:=TXMLXSDFormatSettings.Create(true);
390   try
391     ExportSettings.ExportFormat:=AccessCompatible;
392     ExportFormat:=efXMLXSDAccess;
393     ExportSettings.CreateXSD:=true;
394     ExportSettings.DecimalSeparator:=char(''); //don't override
395     Exporter.FileName := FExportTempDir + inttostr(ord(ExportFormat)) +
396       lowercase(rightstr(TestName,5)) +
397       TDetailedExportExtensions[ExportFormat];
398     Exporter.FormatSettings:=ExportSettings;
399     GenericExportTest(Exporter, ExportFormat);
400     AssertTrue('Output file must be created', FileExists(Exporter.FileName));
401     AssertFalse('Output file must not be empty', (GetFileSize(Exporter.FileName) = 0));
402   finally
403     if (FKeepFilesAfterTest = False) then
404       DeleteFile(Exporter.FileName);
405     ExportSettings.Free;
406     Exporter.Free;
407   end;
408 end;
409 
410 procedure TTestDBExport.TestXSDExport_ADONET_NoXSD;
411 var
412   Exporter: TXMLXSDExporter;
413   ExportFormat: TDetailedExportFormats;
414   ExportSettings:TXMLXSDFormatSettings;
415 begin
416   Exporter := TXMLXSDExporter.Create(nil);
417   ExportSettings:=TXMLXSDFormatSettings.Create(true);
418   try
419     ExportSettings.ExportFormat:=ADONETCompatible;
420     ExportFormat:=efXMLXSDADONet;
421     ExportSettings.CreateXSD:=false;
422     Exporter.FileName := FExportTempDir + inttostr(ord(ExportFormat)) +
423       lowercase(rightstr(TestName,5)) +
424       TDetailedExportExtensions[ExportFormat];
425     Exporter.FormatSettings:=ExportSettings;
426     GenericExportTest(Exporter, ExportFormat);
427     AssertTrue('Output file must be created', FileExists(Exporter.FileName));
428     AssertFalse('Output file must not be empty', (GetFileSize(Exporter.FileName) = 0));
429   finally
430     if (FKeepFilesAfterTest = False) then
431       DeleteFile(Exporter.FileName);
432     ExportSettings.Free;
433     Exporter.Free;
434   end;
435 end;
436 
437 procedure TTestDBExport.TestXSDExport_ADONET_XSD;
438 var
439   Exporter: TXMLXSDExporter;
440   ExportFormat: TDetailedExportFormats;
441   ExportSettings:TXMLXSDFormatSettings;
442 begin
443   Exporter := TXMLXSDExporter.Create(nil);
444   ExportSettings:=TXMLXSDFormatSettings.Create(true);
445   try
446     ExportSettings.ExportFormat:=ADONETCompatible;
447     ExportFormat:=efXMLXSDADONet;
448     ExportSettings.CreateXSD:=true;
449     Exporter.FileName := FExportTempDir + inttostr(ord(ExportFormat)) +
450       lowercase(rightstr(TestName,5)) +
451       TDetailedExportExtensions[ExportFormat];
452     Exporter.FormatSettings:=ExportSettings;
453     GenericExportTest(Exporter, ExportFormat);
454     AssertTrue('Output file must be created', FileExists(Exporter.FileName));
455     AssertFalse('Output file must not be empty', (GetFileSize(Exporter.FileName) = 0));
456   finally
457     if (FKeepFilesAfterTest = False) then
458       DeleteFile(Exporter.FileName);
459     ExportSettings.Free;
460     Exporter.Free;
461   end;
462 end;
463 
464 procedure TTestDBExport.TestCSVExport;
465 var
466   Exporter: TCSVExporter;
467   ExportFormat: TDetailedExportFormats;
468   ExportSettings: TCSVFormatSettings;
469 begin
470   Exporter := TCSVExporter.Create(nil);
471   ExportSettings:=TCSVFormatSettings.Create(true);
472   try
473     ExportFormat:=efCSV;
474     Exporter.FileName := FExportTempDir + inttostr(ord(ExportFormat)) +
475       lowercase(rightstr(TestName,5)) +
476       TDetailedExportExtensions[ExportFormat];
477     Exporter.FormatSettings:=ExportSettings;
478     GenericExportTest(Exporter, ExportFormat);
479     AssertTrue('Output file must be created', FileExists(Exporter.FileName));
480     AssertFalse('Output file must not be empty', (GetFileSize(Exporter.FileName) = 0));
481   finally
482     if (FKeepFilesAfterTest = False) then
483       DeleteFile(Exporter.FileName);
484     ExportSettings.Free;
485     Exporter.Free;
486   end;
487 end;
488 
489 procedure TTestDBExport.TestCSVExport_RFC4180WithHeader;
490 var
491   Exporter: TCSVExporter;
492   ExportFormat: TDetailedExportFormats;
493   ExportSettings: TCSVFormatSettings;
494 begin
495   Exporter := TCSVExporter.Create(nil);
496   ExportSettings:=TCSVFormatSettings.Create(true);
497   try
498     ExportSettings.FieldDelimiter:=','; //RFC 4180 specified commas as delimiter
499     ExportSettings.HeaderRow:=true; //...allows an optional header line
500     ExportSettings.QuoteChar:='"'; //...requires quoting with " (if quoting)
501     // Fields containing line breaks (CRLF), double quotes,
502     // and commas should be enclosed in double-quotes.
503     // => this probably won't get tested with this test set.
504     ExportFormat:=efCSV;
505     Exporter.FileName := FExportTempDir + inttostr(ord(ExportFormat)) +
506       lowercase(rightstr(TestName,5)) +
507       TDetailedExportExtensions[ExportFormat];
508     Exporter.FormatSettings:=ExportSettings;
509     GenericExportTest(Exporter, ExportFormat);
510     AssertTrue('Output file must be created', FileExists(Exporter.FileName));
511     AssertFalse('Output file must not be empty', (GetFileSize(Exporter.FileName) = 0));
512   finally
513     if (FKeepFilesAfterTest = False) then
514       DeleteFile(Exporter.FileName);
515     ExportSettings.Free;
516     Exporter.Free;
517   end;
518 end;
519 
520 procedure TTestDBExport.TestCSVExport_TweakSettingsSemicolon;
521 var
522   Exporter: TCSVExporter;
523   ExportFormat: TDetailedExportFormats;
524   ExportSettings: TCSVFormatSettings;
525 begin
526   Exporter := TCSVExporter.Create(nil);
527   ExportSettings:=TCSVFormatSettings.Create(true);
528   try
529     ExportSettings.FieldDelimiter:=';';
530     ExportSettings.QuoteChar:='"'; //try explicit assignment
531     ExportSettings.RowDelimiter:=#10; //Unix/Linux format
532     ExportSettings.BooleanFalse:='onwaar'; //why not a Dutch output format?
533     ExportSettings.BooleanTrue:='waar'; //why not a Dutch output format?
534     ExportSettings.CurrencyDigits:=3;
535     ExportSettings.CurrencySymbol:='€'; //euro sign
536     ExportSettings.DateFormat:='d-mm-yyyy'; //Dutch setting
537     ExportSettings.DateTimeFormat:='d-mm-yyyy hh:nn:ss'; //Dutch setting
538     ExportSettings.DecimalSeparator:=','; //another Dutch setting
539     ExportSettings.TimeFormat:='hh:nn:ss'; //Dutch setting
540     ExportSettings.IntegerFormat:='0000';//Strange but nice ;)
541     ExportFormat:=efCSV;
542     Exporter.FileName := FExportTempDir + inttostr(ord(ExportFormat)) +
543       lowercase(rightstr(TestName,5)) +
544       TDetailedExportExtensions[ExportFormat];
545     Exporter.FormatSettings:=ExportSettings;
546     GenericExportTest(Exporter, ExportFormat);
547     AssertTrue('Output file must be created', FileExists(Exporter.FileName));
548     AssertFalse('Output file must not be empty', (GetFileSize(Exporter.FileName) = 0));
549   finally
550     if (FKeepFilesAfterTest = False) then
551       DeleteFile(Exporter.FileName);
552     ExportSettings.Free;
553     Exporter.Free;
554   end;
555 end;
556 
557 procedure TTestDBExport.TestFixedTextExport;
558 var
559   Exporter: TFixedLengthExporter;
560   ExportFormat: TDetailedExportFormats;
561 begin
562   Exporter := TFixedLengthExporter.Create(nil);
563   try
564     ExportFormat:=efFixedLengthText;
565     Exporter.FileName := FExportTempDir + inttostr(ord(ExportFormat)) +
566       lowercase(rightstr(TestName,5)) +
567       TDetailedExportExtensions[ExportFormat];
568     GenericExportTest(Exporter, ExportFormat);
569     AssertTrue('Output file must be created', FileExists(Exporter.FileName));
570     AssertFalse('Output file must not be empty', (GetFileSize(Exporter.FileName) = 0));
571   finally
572     if (FKeepFilesAfterTest = False) then
573       DeleteFile(Exporter.FileName);
574     Exporter.Free;
575   end;
576 end;
577 
578 Function TTestDBExport.GetBooleanDS : TBufDataset;
579 
580 Var
581   DS : TBufDataset;
582 
583 begin
584   DS:=TBufDataset.Create(Nil);
585   try
586     DS.FieldDefs.Add('F',ftBoolean,0);
587     DS.CreateDataset;
588     DS.Append;
589     DS.Fields[0].AsBoolean:=true;
590     DS.Post;
591     DS.Append;
592     DS.Fields[0].AsBoolean:=False;
593     DS.Post;
594     DS.First;
595   except
596     DS.Free;
597     Raise;
598   end;
599   Result:=DS;
600 end;
601 
602 procedure TTestDBExport.TestFixedTextExportBoolean;
603 var
604   DS : TBufDataset;
605   Exporter: TFixedLengthExporter;
606   F : text;
607   S : UTF8String;
608   haveFile : Boolean;
609 
610 begin
611   haveFile:=False;
612   Exporter:=Nil;
613   DS:=GetBooleanDS;
614   try
615     Exporter := TFixedLengthExporter.Create(nil);
616     Exporter.FormatSettings.BooleanFalse:='false';
617     Exporter.FormatSettings.BooleanTrue:='True';
618     Exporter.Dataset:=DS;
619     Exporter.FileName := FExportTempDir + lowercase(TestName) + '.txt';
620     Exporter.BuildDefaultFieldMap(Exporter.ExportFields);
621     AssertEquals('Correct width',5, TFixedLengthExportFieldItem(Exporter.ExportFields[0]).Width);
622     AssertEquals('Output count',2,Exporter.Execute);
623     AssertTrue('Output file must be created', FileExists(Exporter.FileName));
624     AssertFalse('Output file must not be empty', (GetFileSize(Exporter.FileName) = 0));
625     AssignFile(F,Exporter.FileName);
626     Reset(F);
627     haveFile:=True;
628     Readln(F,S);
629     AssertEquals('Correct first line','True ',S); // 1 extra
630     Readln(F,S);
631     AssertEquals('Correct second line','false',S);
632   finally
633     if HaveFile then
634       closeFile(F);
635     if (FKeepFilesAfterTest = False) then
636       DeleteFile(Exporter.FileName);
637     Exporter.Free;
638   end;
639 end;
640 
641 Const
642   // UTF8 code page assumed !
643   WidestringLine1 = '这是一个测验';
644   WidestringLine2 = 'Это тест.';
645   WidestringLine3 = 'ça roule.';
646   WidestringResLine1 = '这是一';
647   WidestringResLine2 = 'Это';
648   WidestringResLine3 = 'ça ';
649 
650 Function TTestDBExport.GetWideStringDS : TBufDataset;
651 
652 Var
653   DS : TBufDataset;
654 
655 begin
656   DS:=TBufDataset.Create(Nil);
657   try
658     DS.FieldDefs.Add('F',ftWideString,10);
659     DS.CreateDataset;
660     DS.Append;
661     DS.Fields[0].AsWideString:=UTF8Decode(WideStringLine1);
662     DS.Post;
663     DS.Append;
664     DS.Fields[0].AsWideString:=UTF8Decode(WideStringLine2);
665     DS.Post;
666     DS.Append;
667     DS.Fields[0].AsWideString:=UTF8Decode(WideStringLine3);
668     DS.Post;
669     DS.First;
670   except
671     DS.Free;
672     Raise;
673   end;
674   Result:=DS;
675 end;
676 
677 
678 procedure TTestDBExport.TestFixedTextExportUTF8;
679 
680 var
681   DS : TBufDataset;
682   Exporter: TFixedLengthExporter;
683   F : text;
684   S : UTF8String;
685   haveFile : Boolean;
686 
687 begin
688   haveFile:=False;
689   Exporter:=Nil;
690   DS:=GetWideStringDS;
691   try
692     Exporter := TFixedLengthExporter.Create(nil);
693     Exporter.Dataset:=DS;
694     Exporter.FormatSettings.CharMode:=cmUTF8;
695     Exporter.FileName := FExportTempDir + lowercase(TestName) + '.txt';
696     Exporter.BuildDefaultFieldMap(Exporter.ExportFields);
697     TFixedLengthExportFieldItem(Exporter.ExportFields[0]).Width:=3;
698     AssertEquals('Output count',3,Exporter.Execute);
699     AssertTrue('Output file must be created', FileExists(Exporter.FileName));
700     AssertFalse('Output file must not be empty', (GetFileSize(Exporter.FileName) = 0));
701     AssignFile(F,Exporter.FileName);
702     Reset(F);
703     haveFile:=True;
704     Readln(F,S);
705     AssertEquals('Correct first line',UTF8Decode(WideStringResLine1),UTF8Decode(S));
706     Readln(F,S);
707     AssertEquals('Correct second line',UTF8Decode(WideStringResLine2),UTF8Decode(S));
708     Readln(F,S);
709     AssertEquals('Correct second line',UTF8Decode(WideStringResLine3),UTF8Decode(S));
710   finally
711     if HaveFile then
712       closeFile(F);
713     if (FKeepFilesAfterTest = False) then
714       DeleteFile(Exporter.FileName);
715     Exporter.Free;
716   end;
717 end;
718 
719 procedure TTestDBExport.TestFixedTextExportUTF16;
720 
721 var
722   DS : TBufDataset;
723   Exporter: TFixedLengthExporter;
724   F : text;
725   S : UnicodeString;
726   haveFile : Boolean;
727 
728 begin
729   haveFile:=False;
730   Exporter:=Nil;
731   DS:=GetWideStringDS;
732   try
733     Exporter := TFixedLengthExporter.Create(nil);
734     Exporter.Dataset:=DS;
735     Exporter.FormatSettings.CharMode:=cmUTF16;
736     Exporter.FileName := FExportTempDir + lowercase(TestName) + '.txt';
737     Exporter.BuildDefaultFieldMap(Exporter.ExportFields);
738     TFixedLengthExportFieldItem(Exporter.ExportFields[0]).Width:=3;
739     AssertEquals('Output count',3,Exporter.Execute);
740     AssertTrue('Output file must be created', FileExists(Exporter.FileName));
741     AssertFalse('Output file must not be empty', (GetFileSize(Exporter.FileName) = 0));
742     AssignFile(F,Exporter.FileName);
743     Reset(F);
744     haveFile:=True;
745     Readln(F,S);
746     AssertEquals('Correct first line',UTF8Decode(WideStringResLine1),S);
747     Readln(F,S);
748     AssertEquals('Correct second line',UTF8Decode(WideStringResLine2),S);
749     Readln(F,S);
750     AssertEquals('Correct second line',UTF8Decode(WideStringResLine3),S);
751   finally
752     if HaveFile then
753       closeFile(F);
754     if (FKeepFilesAfterTest = False) then
755       DeleteFile(Exporter.FileName);
756     Exporter.Free;
757   end;
758 end;
759 
760 Function TTestDBExport.GetABCDS : TBufDataset;
761 
762 Var
763   DS : TBufDataset;
764 
765 begin
766   DS:=TBufDataset.Create(Nil);
767   try
768     DS.FieldDefs.Add('A',ftString,2);
769     DS.FieldDefs.Add('B',ftString,2);
770     DS.FieldDefs.Add('C',ftString,2);
771     DS.CreateDataset;
772     DS.Append;
773     DS.Fields[0].AsString:='xx';
774     DS.Fields[1].AsString:='y';
775     DS.Fields[2].AsString:='zz';
776     DS.Post;
777     DS.Append;
778     DS.Fields[0].AsString:='x';
779     DS.Fields[1].AsString:='yy';
780     DS.Fields[2].AsString:='z';
781     DS.Post;
782     DS.First;
783   except
784     DS.Free;
785     Raise;
786   end;
787   Result:=DS;
788 end;
789 
790 
791 procedure TTestDBExport.TestFixedTextExportHeader;
792 
793 var
794   DS : TBufDataset;
795   Exporter: TFixedLengthExporter;
796   F : text;
797   S : UTF8String;
798   haveFile : Boolean;
799 
800 begin
801   haveFile:=False;
802   Exporter:=Nil;
803   DS:=GetBooleanDS;
804   try
805     Exporter := TFixedLengthExporter.Create(nil);
806     Exporter.FormatSettings.BooleanFalse:='false';
807     Exporter.FormatSettings.BooleanTrue:='True';
808     Exporter.FormatSettings.HeaderRow:=True;
809     Exporter.Dataset:=DS;
810     Exporter.FileName := FExportTempDir + lowercase(TestName) + '.txt';
811     Exporter.BuildDefaultFieldMap(Exporter.ExportFields);
812     AssertEquals('Correct width',5, TFixedLengthExportFieldItem(Exporter.ExportFields[0]).Width);
813     AssertEquals('Output count',2,Exporter.Execute);
814     AssertTrue('Output file must be created', FileExists(Exporter.FileName));
815     AssertFalse('Output file must not be empty', (GetFileSize(Exporter.FileName) = 0));
816     AssignFile(F,Exporter.FileName);
817     Reset(F);
818     haveFile:=True;
819     Readln(F,S);
820     AssertEquals('Correct header line','F    ',S); // 1 extra
821     Readln(F,S);
822     AssertEquals('Correct first line','True ',S); // 1 extra
823     Readln(F,S);
824     AssertEquals('Correct second line','false',S);
825   finally
826     if HaveFile then
827       closeFile(F);
828     if (FKeepFilesAfterTest = False) then
829       DeleteFile(Exporter.FileName);
830     Exporter.Free;
831   end;
832 end;
833 
834 procedure TTestDBExport.TestFixedTextExportSpaces;
835 var
836   DS : TBufDataset;
837   Exporter: TFixedLengthExporter;
838   F : text;
839   S : UTF8String;
840   haveFile : Boolean;
841 
842 begin
843   haveFile:=False;
844   Exporter:=Nil;
845   DS:=GetABCDS;
846   try
847     Exporter := TFixedLengthExporter.Create(nil);
848     Exporter.FormatSettings.BooleanFalse:='false';
849     Exporter.FormatSettings.BooleanTrue:='True';
850     Exporter.FormatSettings.HeaderRow:=True;
851     Exporter.FormatSettings.ColumnSeparatorSpaceCount:=2;
852     Exporter.Dataset:=DS;
853     Exporter.FileName := FExportTempDir + lowercase(TestName) + '.txt';
854     Exporter.BuildDefaultFieldMap(Exporter.ExportFields);
855     AssertEquals('Output count',2,Exporter.Execute);
856     AssertTrue('Output file must be created', FileExists(Exporter.FileName));
857     AssertFalse('Output file must not be empty', (GetFileSize(Exporter.FileName) = 0));
858     AssignFile(F,Exporter.FileName);
859     Reset(F);
860     haveFile:=True;
861     Readln(F,S);
862     AssertEquals('Correct header line','A   B   C ',S); // 1 extra
863     Readln(F,S);
864     AssertEquals('Correct first line','xx  y   zz',S); // 1 extra
865     Readln(F,S);
866     AssertEquals('Correct first line','x   yy  z ',S); // 1 extra
867   finally
868     if HaveFile then
869       closeFile(F);
870     if (FKeepFilesAfterTest = False) then
871       DeleteFile(Exporter.FileName);
872     Exporter.Free;
873   end;
874 end;
875 
876 procedure TTestDBExport.TestJSONExport;
877 var
878   Exporter: TSimpleJSONExporter;
879   ExportFormat: TDetailedExportFormats;
880   ExportSettings:TSimpleJSONFormatSettings;
881 begin
882   Exporter := TSimpleJSONExporter.Create(nil);
883   ExportSettings:=TSimpleJSONFormatSettings.Create(true);
884   try
885     ExportFormat:=efJSON;
886     Exporter.FileName := FExportTempDir + inttostr(ord(ExportFormat)) + lowercase(rightstr(TestName,5))+
887      inttostr(ord(ExportFormat))+
888      TDetailedExportExtensions[ExportFormat];    Exporter.FormatSettings:=ExportSettings;
889     GenericExportTest(Exporter, ExportFormat);
890     AssertTrue('Output file must be created', FileExists(Exporter.FileName));
891     AssertFalse('Output file must not be empty', (GetFileSize(Exporter.FileName) = 0));
892   finally
893     if (FKeepFilesAfterTest = False) then
894       DeleteFile(Exporter.FileName);
895     ExportSettings.Free;
896     Exporter.Free;
897   end;
898 end;
899 
900 procedure TTestDBExport.TestRTFExport;
901 var
902   Exporter: TRTFExporter;
903   ExportFormat: TDetailedExportFormats;
904   ExportSettings:TRTFExportFormatSettings;
905 begin
906   Exporter := TRTFExporter.Create(nil);
907   ExportSettings:=TRTFExportFormatSettings.Create(true);
908   try
909     ExportFormat:=efRTF;
910     Exporter.FileName := FExportTempDir + inttostr(ord(ExportFormat)) +
911       lowercase(rightstr(TestName,5))+
912       TDetailedExportExtensions[ExportFormat];
913     Exporter.FormatSettings:=ExportSettings;
914     GenericExportTest(Exporter, ExportFormat);
915     AssertTrue('Output file must be created', FileExists(Exporter.FileName));
916     AssertFalse('Output file must not be empty', (GetFileSize(Exporter.FileName) = 0));
917   finally
918     if (FKeepFilesAfterTest = False) then
919       DeleteFile(Exporter.FileName);
920     ExportSettings.Free;
921     Exporter.Free;
922   end;
923 end;
924 
925 procedure TTestDBExport.TestSQLExport;
926 var
927   Exporter: TSQLExporter;
928   ExportFormat: TDetailedExportFormats;
929   ExportSettings:TSQLFormatSettings;
930 begin
931   Exporter := TSQLExporter.Create(nil);
932   ExportSettings:=TSQLFormatSettings.Create(true);
933   try
934     ExportSettings.TableName:='ATABLE'; //required for export to succeed
935     ExportFormat:=efSQL;
936     Exporter.FileName := FExportTempDir + inttostr(ord(ExportFormat)) +
937       lowercase(rightstr(TestName,5))+
938       TDetailedExportExtensions[ExportFormat];
939     Exporter.FormatSettings:=ExportSettings;
940     GenericExportTest(Exporter, ExportFormat);
941     AssertTrue('Output file must be created', FileExists(Exporter.FileName));
942     AssertFalse('Output file must not be empty', (GetFileSize(Exporter.FileName) = 0));
943   finally
944     if (FKeepFilesAfterTest = False) then
945       DeleteFile(Exporter.FileName);
946     ExportSettings.Free;
947     Exporter.Free;
948   end;
949 end;
950 
951 procedure TTestDBExport.TestTeXExport;
952 var
953   Exporter: TTexExporter;
954   ExportFormat: TDetailedExportFormats;
955   ExportSettings:TTeXExportFormatSettings;
956 begin
957   Exporter := TTexExporter.Create(nil);
958   ExportSettings:=TTeXExportFormatSettings.Create(true);
959   try
960     ExportFormat:=efTeX;
961     Exporter.FileName := FExportTempDir +
962       inttostr(ord(ExportFormat)) + lowercase(rightstr(TestName,5))+
963       TDetailedExportExtensions[ExportFormat];
964     Exporter.FormatSettings:=ExportSettings;
965     GenericExportTest(Exporter, ExportFormat);
966     AssertTrue('Output file must be created', FileExists(Exporter.FileName));
967     AssertFalse('Output file must not be empty', (GetFileSize(Exporter.FileName) = 0));
968   finally
969     if (FKeepFilesAfterTest = False) then
970       DeleteFile(Exporter.FileName);
971     ExportSettings.Free;
972     Exporter.Free;
973   end;
974 end;
975 
976 procedure TTestDBExport.TestXMLExport;
977 var
978   Exporter: TSimpleXMLExporter;
979   ExportFormat: TDetailedExportFormats;
980   ExportSettings:TSimpleXMLFormatSettings;
981 begin
982   Exporter := TSimpleXMLExporter.Create(nil);
983   ExportSettings:=TSimpleXMLFormatSettings.Create(true);
984   try
985     ExportFormat:=efXML;
986     Exporter.FileName := FExportTempDir + inttostr(ord(ExportFormat)) +
987      lowercase(rightstr(TestName,5)) +
988      TDetailedExportExtensions[ExportFormat];
989     Exporter.FormatSettings:=ExportSettings;
990     GenericExportTest(Exporter, ExportFormat);
991     AssertTrue('Output file must be created', FileExists(Exporter.FileName));
992     AssertFalse('Output file must not be empty', (GetFileSize(Exporter.FileName) = 0));
993   finally
994     if (FKeepFilesAfterTest = False) then
995       DeleteFile(Exporter.FileName);
996     ExportSettings.Free;
997     Exporter.Free;
998   end;
999 end;
1000 
1001 procedure TTestDBExport.TestXMLExportSpecialChars;
1002 var
1003   Exporter: TSimpleXMLExporter;
1004   FieldMapping: TExportFields;
1005   NumberExported: integer;
1006   i: integer;
1007   XML : TXMLDocument;
1008 begin
1009   XML:=Nil;
1010   Exporter := TSimpleXMLExporter.Create(nil);
1011   FieldMapping:=TExportFields.Create(Exporter.ExportFields.ItemClass);
1012   try
1013     Exporter.Dataset := DBConnector.GetFieldDataset;
1014     Exporter.Dataset.Open;
1015     Exporter.Dataset.Edit;
1016     Exporter.Dataset.FieldByName('FString').AsString:='*&*<*>*';
1017     Exporter.Dataset.Post;
1018     Exporter.BuildDefaultFieldMap(FieldMapping);
1019     Exporter.FileName := FExportTempDir +  lowercase(rightstr(TestName,5)) +   TDetailedExportExtensions[efXML];
1020     for i:=Exporter.Dataset.Fields.Count-1 downto 0 do
1021     begin
1022       if not FieldSupported(
1023         Exporter.Dataset.Fields[i].DataType,
1024         efXML) then
1025           FieldMapping.Delete(i);
1026     end;
1027     for i:=0 to FieldMapping.Count-1 do
1028       Exporter.ExportFields.Add.Assign(FieldMapping[i]);
1029     NumberExported := Exporter.Execute;
1030     Exporter.Dataset.Last;
1031     Exporter.Dataset.First;
1032     AssertEquals('Number of records exported matches recordcount', NumberExported,
1033       Exporter.Dataset.RecordCount);
1034     Exporter.Dataset.Close;
1035     ReadXMLFile(XML,Exporter.FileName);
1036     AssertEquals('Correct written','*&*<*>*',XML.DocumentElement.FirstChild.FirstChild.NextSibling.FirstChild.NodeValue);
1037 
1038   finally
1039     XML.Free;
1040     FieldMapping.Free;
1041     Exporter.Free;
1042   end;
1043 end;
1044 
1045 procedure TTestDBExport.TestXSDExport_DelphiClientDataset;
1046 var
1047   Exporter: TXMLXSDExporter;
1048   ExportFormat: TDetailedExportFormats;
1049   ExportSettings:TXMLXSDFormatSettings;
1050 begin
1051   Exporter := TXMLXSDExporter.Create(nil);
1052   ExportSettings:=TXMLXSDFormatSettings.Create(true);
1053   try
1054     ExportSettings.ExportFormat:=DelphiClientDataset;
1055     ExportFormat:=efXMLXSDClientDataset;
1056     Exporter.FileName := FExportTempDir + inttostr(ord(ExportFormat)) +
1057       lowercase(rightstr(TestName,5)) +
1058       TDetailedExportExtensions[ExportFormat];
1059     Exporter.FormatSettings:=ExportSettings;
1060     GenericExportTest(Exporter, ExportFormat);
1061     AssertTrue('Output file must be created', FileExists(Exporter.FileName));
1062     AssertFalse('Output file must not be empty', (GetFileSize(Exporter.FileName) = 0));
1063   finally
1064     if (FKeepFilesAfterTest = False) then
1065       DeleteFile(Exporter.FileName);
1066     ExportSettings.Free;
1067     Exporter.Free;
1068   end;
1069 end;
1070 
1071 procedure TTestDBExport.TestXSDExport_Excel;
1072 var
1073   Exporter: TXMLXSDExporter;
1074   ExportFormat: TDetailedExportFormats;
1075   ExportSettings:TXMLXSDFormatSettings;
1076 begin
1077   Exporter := TXMLXSDExporter.Create(nil);
1078   ExportSettings:=TXMLXSDFormatSettings.Create(true);
1079   try
1080     ExportSettings.ExportFormat:=ExcelCompatible;
1081     ExportFormat:=efXMLXSDExcel;
1082     Exporter.FileName := FExportTempDir + inttostr(ord(ExportFormat)) +
1083       lowercase(rightstr(TestName,5)) +
1084       TDetailedExportExtensions[ExportFormat];
1085     Exporter.FormatSettings:=ExportSettings;
1086     GenericExportTest(Exporter, ExportFormat);
1087     AssertTrue('Output file must be created', FileExists(Exporter.FileName));
1088     AssertFalse('Output file must not be empty', (GetFileSize(Exporter.FileName) = 0));
1089   finally
1090     if (FKeepFilesAfterTest = False) then
1091       DeleteFile(Exporter.FileName);
1092     ExportSettings.Free;
1093     Exporter.Free;
1094   end;
1095 end;
1096 
1097 
1098 initialization
1099   RegisterTest(TTestDBExport);
1100 end.
1101