1unit tcsdfdata;
2// Tests specific functionality of SdfDataSet (multiline etc)
3//                             and FixedFormatDataSet
4
5{$mode objfpc}{$H+}
6
7interface
8
9uses
10  Classes, SysUtils, Fpcunit, TestRegistry,
11  dateutils,sdfdata,ToolsUnit;
12
13type
14
15  { TTestSdfSpecific }
16
17  TTestSdfSpecific = class(TTestCase)
18  private
19    TestDataset: TSdfDataset;
20    function TestFileName(const FileName: string=''): string;
21  protected
22    procedure Setup; override;
23    procedure Teardown; override;
24  published
25    procedure TestEmptyFileHeader;
26    procedure TestEmptyFileNoHeader;
27    procedure TestSingleLineHeader;
28    procedure TestSingleLineNoHeader;
29    procedure TestOutput;
30    procedure TestDelimitedTextOutput;
31    procedure TestEmptyFieldHeader;
32    Procedure TestEmptyFieldNoHeader;
33    procedure TestEmptyFieldContents;
34    Procedure TestEmptyFieldHeaderStripTrailingDelimiters;
35    Procedure TestStripTrailingDelimiters;
36  end;
37
38  { TTestFixedFormatSpecific }
39
40  TTestFixedFormatSpecific = class(TTestCase)
41  private
42    TestDataset: TFixedFormatDataset;
43    function TestFileName(const FileName: string=''): string;
44    procedure CreateTestFile;
45  protected
46    procedure Setup; override;
47    procedure Teardown; override;
48  published
49    procedure TestTrimSpace;
50    procedure TestNoTrimSpace;
51  end;
52
53implementation
54
55function Ttestsdfspecific.TestFileName(const FileName: string): string;
56const
57  DefaultTestFileName = 'test.csv';
58begin
59  if FileName = '' then
60    Result := DefaultTestFileName
61  else
62    Result := FileName;
63
64  if dbname <> '' then
65    begin
66    ForceDirectories(dbname);
67    Result := IncludeTrailingPathDelimiter(dbname) + Result;
68    end;
69
70  if FileExists(Result) then DeleteFile(Result);
71end;
72
73procedure Ttestsdfspecific.TestEmptyFileHeader;
74// An empty file should return 0 records even if it has a header
75begin
76  // with Schema, with Header line
77  TestDataset.FirstLineAsSchema := True;
78  TestDataset.FileName := TestFileName('empty.csv');
79  TestDataset.Open;
80
81  TestDataset.Last;
82  TestDataset.First;
83  AssertEquals('Number of records in test dataset', 0, TestDataset.RecordCount);
84  TestDataset.Close;
85end;
86
87procedure Ttestsdfspecific.TestEmptyFileNoHeader;
88// An empty file should return 0 records even if it has a header
89begin
90  // with Schema, without Header line
91  TestDataset.FirstLineAsSchema := False;
92  TestDataset.FileName := TestFileName('empty.csv');
93  TestDataset.Open;
94
95  TestDataset.Last;
96  TestDataset.First;
97  AssertEquals('Number of records in test dataset', 0, TestDataset.RecordCount);
98  TestDataset.Close;
99end;
100
101procedure Ttestsdfspecific.TestSingleLineHeader;
102// A file with a single data line and header should return 1 records
103var
104  FileStrings: TStringList;
105begin
106  // with Schema, with Header line, which differs from Schema
107  TestDataset.FirstLineAsSchema := True;
108  TestDataset.FileName := TestFileName('singleh.csv');
109
110  FileStrings:=TStringList.Create;
111  try
112    FileStrings.Add('ID,NAME,BIRTHDAY,GENDER'); // 4 fields override 3 fields in Schema
113    FileStrings.Add('1,SimpleName,31-12-1976,M');
114    FileStrings.SaveToFile(TestDataset.FileName);
115  finally
116    FileStrings.Free;
117  end;
118
119  TestDataset.Open;
120  AssertEquals('FieldDefs.Count', 4, TestDataset.FieldDefs.Count);
121  AssertEquals('1', TestDataset.Fields[0].AsString); // just after Open
122
123  TestDataset.Last;
124  TestDataset.First;
125  AssertEquals('RecNo', 1, TestDataset.RecNo);
126  AssertEquals('RecordCount', 1, TestDataset.RecordCount);
127  TestDataset.Close;
128  AssertEquals('RecordCount after Close', 0, TestDataset.RecordCount);
129end;
130
131procedure Ttestsdfspecific.TestSingleLineNoHeader;
132// A file with a single data line, no header should return 1 records
133var
134  FileStrings: TStringList;
135begin
136  // with Schema, without Header line
137  TestDataset.FirstLineAsSchema := False;
138  TestDataset.FileName := TestFileName('singleh.csv');
139
140  FileStrings:=TStringList.Create;
141  try
142    FileStrings.Add('1,SimpleName,31-12-1976');
143    FileStrings.SaveToFile(TestDataset.FileName);
144  finally
145    FileStrings.Free;
146  end;
147
148  TestDataset.Open;
149  AssertEquals('FieldDefs.Count', 3, TestDataset.FieldDefs.Count);
150  AssertEquals('1', TestDataset.Fields[0].AsString);
151
152  TestDataset.Last;
153  TestDataset.First;
154  AssertEquals('RecNo', 1, TestDataset.RecNo);
155  AssertEquals('RecordCount', 1, TestDataset.RecordCount);
156  TestDataset.Close;
157  AssertEquals('RecordCount after Close', 0, TestDataset.RecordCount);
158end;
159
160procedure Ttestsdfspecific.TestOutput;
161// Basic assignment test: assign some difficult data to records and
162// see if the RecordCount is correct.
163const
164  NAME: array[1..4] of string = (
165    'J"T"',                             // Data with quotes
166    'Hello, goodbye',                   // Data with delimiter
167    '  Just a line with spaces     ',   // Regular data
168    'Delimiter,"and";quote'             // Data with delimiter and quote
169  );
170var
171  i: integer;
172begin
173  // with Schema, with Header line
174  TestDataset.Schema[1] := 'NAME=30';
175  TestDataset.FileName := TestFileName('output.csv');
176  TestDataset.Open;
177
178  // Fill test data
179  TestDataset.Append;
180  TestDataset.FieldByName('ID').AsInteger := 1;
181  TestDataset.FieldByName('NAME').AsString := NAME[1];
182  TestDataset.FieldByName('BIRTHDAY').AsDateTime := ScanDateTime('yyyymmdd', '19761231', 1);
183  TestDataset.Post;
184
185  TestDataset.Append;
186  TestDataset.FieldByName('ID').AsInteger := 2;
187  TestDataset.FieldByName('NAME').AsString := NAME[2];
188  TestDataset.FieldByName('BIRTHDAY').AsDateTime := ScanDateTime('yyyymmdd', '19761231', 1);
189  TestDataset.Post;
190
191  TestDataset.Append;
192  TestDataset.FieldByName('ID').AsInteger := 4;
193  TestDataset.FieldByName('NAME').AsString := NAME[4];
194  TestDataset.FieldByName('BIRTHDAY').AsDateTime := ScanDateTime('yyyymmdd', '19761231', 1);
195  TestDataset.Post;
196
197  TestDataset.Insert;
198  TestDataset.FieldByName('ID').AsInteger := 3;
199  TestDataset.FieldByName('NAME').AsString := NAME[3];
200  TestDataset.FieldByName('BIRTHDAY').AsDateTime := ScanDateTime('yyyymmdd', '19761231', 1);
201  TestDataset.Post;
202
203  // test sequential order of records
204  TestDataset.First;
205  for i:=1 to 4 do begin
206    AssertEquals('RecNo', i, TestDataset.RecNo);
207    AssertEquals(i, TestDataset.FieldByName('ID').AsInteger);
208    TestDataset.Next;
209  end;
210  // set/test RecNo
211  for i:=1 to 4 do begin
212    TestDataset.RecNo := i;
213    AssertEquals('RecNo', i, TestDataset.RecNo);
214    AssertEquals(i, TestDataset.FieldByName('ID').AsInteger);
215  end;
216  AssertEquals('RecordCount', 4, TestDataset.RecordCount);
217  TestDataset.Close;
218  AssertEquals('RecordCount after Close', 0, TestDataset.RecordCount);
219
220  // reopen, retest
221  TestDataset.Open;
222  for i:=1 to 4 do begin
223    AssertEquals(NAME[i], TestDataset.FieldByName('NAME').AsString);
224    TestDataset.Next;
225  end;
226  AssertTrue('Eof', TestDataset.Eof);
227end;
228
229procedure Ttestsdfspecific.TestDelimitedTextOutput;
230// Test if saving and loading data keeps the original values.
231// Mainly check if writing & reading embedded quotes and CRLF works.
232const
233  Value1='Delimiter,"and";quote';
234  Value2='J"T"';
235  Value3='Just a long line';
236  Value4='Just a quoted long line';
237  Value5='multi'+#13+#10+'line';
238  Value6='Delimiter,and;done';
239  Value7='Some "random" quotes';
240Var
241  F : Text;
242  i : integer;
243begin
244  // with Schema, with Header line
245  TestDataset.Close;
246  TestDataset.AllowMultiLine := True;
247  TestDataset.FirstLineAsSchema := True;
248  TestDataset.FileName := TestFileName('delim.csv');
249  Assign(F, TestDataset.FileName);
250  Rewrite(F);
251  Writeln(F,'Field1,Field2,Field3,Field4,Field5,Field6,Field7');
252  for i:=1 to 3 do
253  begin
254    Writeln(F,'"Delimiter,""and"";quote","J""T""",Just a long line,"Just a quoted long line","multi');
255    Writeln(F,'line","Delimiter,and;done","Some ""random"" quotes"');
256  end;
257  Close(F);
258  // Load our dataset
259  TestDataset.Open;
260  AssertEquals('FieldDefs.Count', 7, TestDataset.FieldDefs.Count);
261  AssertEquals('RecordCount', 3, TestDataset.RecordCount);
262  TestDataset.First;
263  for i:=1 to 3 do
264  begin
265    AssertEquals('Field1', Value1, TestDataSet.Fields[0].AsString);
266    AssertEquals('Field2', Value2, TestDataSet.Fields[1].AsString);
267    AssertEquals('Field3', Value3, TestDataSet.Fields[2].AsString);
268    AssertEquals('Field4', Value4, TestDataSet.Fields[3].AsString);
269    AssertEquals('Field5', Value5, TestDataSet.Fields[4].AsString);
270    AssertEquals('Field6', Value6, TestDataSet.Fields[5].AsString);
271    AssertEquals('Field7' ,Value7, TestDataSet.Fields[6].AsString);
272    TestDataSet.Next;
273  end;
274end;
275
276procedure Ttestsdfspecific.TestEmptyFieldContents;
277Var
278  F : Text;
279begin
280  // with empty Field name in Header line
281  TestDataset.FirstLineAsSchema := True;
282  TestDataset.Delimiter := ';';
283  TestDataset.FileName := TestFileName();
284
285  Assign(F, TestDataset.FileName);
286  Rewrite(F);
287  Writeln(F,'1;2;3;;5');
288  Writeln(F,'11;12;13;;15');
289  Close(F);
290
291  TestDataset.Open;
292  AssertEquals('FieldDefs.Count',5,TestDataset.FieldDefs.Count);
293  AssertEquals('RecordCount',1,TestDataset.RecordCount);
294end;
295
296procedure Ttestsdfspecific.TestEmptyFieldHeader;
297Var
298  F : Text;
299begin
300  // with empty Field name in Header line
301  TestDataset.Delimiter := ';';
302  TestDataset.FirstLineAsSchema := True;
303  TestDataset.FileName := TestFileName();
304
305  Assign(F, TestDataset.FileName);
306  Rewrite(F);
307  Writeln(F,'1;2;3;;5');
308  Close(F);
309
310  TestDataset.Open;
311  AssertEquals('FieldDefs.Count',5,TestDataset.FieldDefs.Count);
312  AssertEquals('RecordCount', 0, TestDataset.RecordCount);
313end;
314
315procedure Ttestsdfspecific.TestEmptyFieldNoHeader;
316
317Var
318  F : Text;
319  S1,S2 : String;
320
321begin
322  // without Schema, without Header line
323  TestDataset.Schema.Clear;
324  TestDataset.FirstLineAsSchema := False;
325  TestDataset.Delimiter := ';';
326  TestDataset.FileName := TestFileName();
327
328  Assign(F, TestDataset.FileName);
329  Rewrite(F);
330  Writeln(F,'value1;value2;;;');
331  Close(F);
332
333  with TestDataset do begin
334    Open;
335    AssertEquals('FieldDefs.Count', 5, FieldDefs.Count);
336    AssertEquals('RecordCount', 1, RecordCount);
337    // #1 record
338    Edit;
339    Fields[0].AsString := 'Value1';
340    Post;
341    AssertEquals('Fields[4]', '', Fields[4].AsString);
342    // #2 record
343    Append;
344    Fields[1].AsString := 'Value2';
345    Fields[2].AsString := 'Value"'; // embedded double quote
346    Post;
347    Close;
348  end;
349
350  Assign(F, TestDataset.FileName);
351  Reset(F);
352  ReadLn(F,S1);
353  ReadLn(F,S2);
354  Close(F);
355  AssertEquals('Value1;value2;;;',S1);
356  AssertEquals(';Value2;"Value""";;',S2);
357end;
358
359procedure Ttestsdfspecific.TestEmptyFieldHeaderStripTrailingDelimiters;
360Var
361  F : Text;
362  S : String;
363
364begin
365  // without Schema, without Header line
366  TestDataset.Schema.Clear;
367  TestDataset.FirstLineAsSchema := False;
368  TestDataset.Delimiter := ';';
369  TestDataset.StripTrailingDelimiters := True;
370  TestDataset.FileName := TestFileName();
371
372  Assign(F, TestDataset.FileName);
373  Rewrite(F);
374  Writeln(F,'value1;value2;;;');
375  Close(F);
376
377  TestDataset.Open;
378  AssertEquals('FieldDefs.Count',2,TestDataset.FieldDefs.Count);
379  TestDataset.Edit;
380  TestDataset.Fields[0].AsString:='Value1';
381  TestDataset.Post;
382  TestDataset.Close;
383
384  Assign(F, TestDataset.FileName);
385  Reset(F);
386  ReadLn(F,S);
387  Close(F);
388  AssertEquals('No data lost','Value1;value2',S);
389end;
390
391procedure Ttestsdfspecific.TestStripTrailingDelimiters;
392Var
393  F : Text;
394  S1,S2 : String;
395
396begin
397  // without Schema, with Header line
398  TestDataset.Schema.Clear;
399  TestDataset.FirstLineAsSchema := True;
400  TestDataset.Delimiter := ';';
401  TestDataset.StripTrailingDelimiters := True;
402  TestDataset.FileName := TestFileName();;
403
404  Assign(F, TestDataset.FileName);
405  Rewrite(F);
406  Writeln(F,'value1;value2;;;');
407  Writeln(F,'value1;value2;;;');
408  Close(F);
409
410  TestDataset.Open;
411  AssertEquals('FieldDefs.Count',2,TestDataset.FieldDefs.Count);
412  TestDataset.Edit;
413  TestDataset.Fields[0].AsString:='Value1';
414  TestDataset.Post;
415  TestDataset.Close;
416
417  Assign(F, TestDataset.FileName);
418  Reset(F);
419  ReadLn(F,S1);
420  ReadLn(F,S2);
421  Close(F);
422  AssertEquals('Headers lost','value1;value2;;;',S1); // should striping affect also header line ?
423  AssertEquals('Data lost','Value1;value2',S2);
424end;
425
426
427procedure Ttestsdfspecific.Setup;
428
429begin
430  TestDataset := TSDFDataset.Create(nil);
431  TestDataset.Delimiter := ',';
432  TestDataset.FileMustExist := False;
433  TestDataset.FirstLineAsSchema := True;
434  TestDataset.TrimSpace := False;
435  TestDataset.AllowMultiLine := False;
436  TestDataset.Schema.Add('ID');
437  TestDataset.Schema.Add('NAME');
438  TestDataset.Schema.Add('BIRTHDAY');
439end;
440
441procedure Ttestsdfspecific.Teardown;
442begin
443  try
444    TestDataset.Close;
445  except
446    //swallow
447  end;
448
449  TestDataset.Free;
450  try
451    //DeleteFile(FCSVFileName);
452  except
453    //swallow
454  end;
455end;
456
457
458{ TTestFixedFormatSpecific }
459
460procedure TTestFixedFormatSpecific.Setup;
461begin
462  TestDataset := TFixedFormatDataset.Create(nil);
463  TestDataset.FileMustExist := False;
464  TestDataset.Schema.Add('ID=1');
465  TestDataset.Schema.Add('NAME=10');
466  TestDataset.Schema.Add('BIRTHDAY=10');
467end;
468
469procedure TTestFixedFormatSpecific.Teardown;
470begin
471  TestDataSet.Close;
472  TestDataSet.Free;
473end;
474
475function TTestFixedFormatSpecific.TestFileName(const FileName: string): string;
476const
477  DefaultTestFileName = 'test.sdf';
478begin
479  if FileName = '' then
480    Result := DefaultTestFileName
481  else
482    Result := FileName;
483
484  if dbname <> '' then
485    begin
486    ForceDirectories(dbname);
487    Result := IncludeTrailingPathDelimiter(dbname) + Result;
488    end;
489
490  if FileExists(Result) then DeleteFile(Result);
491end;
492
493procedure TTestFixedFormatSpecific.CreateTestFile;
494var
495  FileStrings: TStringList;
496begin
497  FileStrings:=TStringList.Create;
498  try
499    FileStrings.Add('1John      2000-01-01');
500    FileStrings.Add('2Christiana2001-02-02');
501    FileStrings.SaveToFile(TestDataset.FileName);
502  finally
503    FileStrings.Free;
504  end;
505end;
506
507procedure TTestFixedFormatSpecific.TestTrimSpace;
508begin
509  TestDataset.FileName := TestFileName();
510  CreateTestFile;
511
512  with TestDataset do begin
513    Open;
514    AssertEquals('FieldDefs.Count', 3, FieldDefs.Count);
515    AssertEquals('1', Fields[0].AsString); // just after Open
516
517    Last;
518    First;
519    AssertEquals('RecNo', 1, RecNo);
520    AssertEquals('RecordCount', 2, RecordCount);
521    AssertEquals('1', Fields[0].AsString);
522    AssertEquals('John', Fields[1].AsString);
523    Next;
524    AssertEquals('2', Fields[0].AsString);
525    AssertEquals('Christiana', Fields[1].AsString);
526    Edit;
527    Fields[1].AsString := 'Chris';
528    Post;
529    AssertEquals('Chris', Fields[1].AsString);
530    Close; // save changes
531    AssertEquals('RecordCount after Close', 0, RecordCount);
532    Open;
533    Next;
534    AssertEquals('Chris', Fields[1].AsString);
535  end;
536end;
537
538procedure TTestFixedFormatSpecific.TestNoTrimSpace;
539begin
540  TestDataset.FileName := TestFileName();
541  CreateTestFile;
542
543  with TestDataset do begin
544    TrimSpace := False;
545    Open;
546    AssertEquals('1', Fields[0].AsString);
547    AssertEquals('John      ', Fields[1].AsString);
548    Next;
549    AssertEquals('2', Fields[0].AsString);
550    AssertEquals('Christiana', Fields[1].AsString);
551    Edit;
552    Fields[1].AsString := 'Chris';
553    Post;
554    AssertEquals('Chris     ', Fields[1].AsString);
555    Close; // save changes
556    Open;
557    Next;
558    AssertEquals('Chris     ', Fields[1].AsString);
559  end;
560end;
561
562initialization
563  // Only run these tests if we are running
564  // sdf tests. After all, running these when testing
565  // e.g. SQL RDBMS doesn't make sense.
566  if uppercase(dbconnectorname)='SDFDS' then
567    begin
568    RegisterTest(TTestSdfSpecific);
569    RegisterTest(TTestFixedFormatSpecific);
570    end;
571end.
572
573