testdbbasics.pas 46 KB

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889909192939495969798991001011021031041051061071081091101111121131141151161171181191201211221231241251261271281291301311321331341351361371381391401411421431441451461471481491501511521531541551561571581591601611621631641651661671681691701711721731741751761771781791801811821831841851861871881891901911921931941951961971981992002012022032042052062072082092102112122132142152162172182192202212222232242252262272282292302312322332342352362372382392402412422432442452462472482492502512522532542552562572582592602612622632642652662672682692702712722732742752762772782792802812822832842852862872882892902912922932942952962972982993003013023033043053063073083093103113123133143153163173183193203213223233243253263273283293303313323333343353363373383393403413423433443453463473483493503513523533543553563573583593603613623633643653663673683693703713723733743753763773783793803813823833843853863873883893903913923933943953963973983994004014024034044054064074084094104114124134144154164174184194204214224234244254264274284294304314324334344354364374384394404414424434444454464474484494504514524534544554564574584594604614624634644654664674684694704714724734744754764774784794804814824834844854864874884894904914924934944954964974984995005015025035045055065075085095105115125135145155165175185195205215225235245255265275285295305315325335345355365375385395405415425435445455465475485495505515525535545555565575585595605615625635645655665675685695705715725735745755765775785795805815825835845855865875885895905915925935945955965975985996006016026036046056066076086096106116126136146156166176186196206216226236246256266276286296306316326336346356366376386396406416426436446456466476486496506516526536546556566576586596606616626636646656666676686696706716726736746756766776786796806816826836846856866876886896906916926936946956966976986997007017027037047057067077087097107117127137147157167177187197207217227237247257267277287297307317327337347357367377387397407417427437447457467477487497507517527537547557567577587597607617627637647657667677687697707717727737747757767777787797807817827837847857867877887897907917927937947957967977987998008018028038048058068078088098108118128138148158168178188198208218228238248258268278288298308318328338348358368378388398408418428438448458468478488498508518528538548558568578588598608618628638648658668678688698708718728738748758768778788798808818828838848858868878888898908918928938948958968978988999009019029039049059069079089099109119129139149159169179189199209219229239249259269279289299309319329339349359369379389399409419429439449459469479489499509519529539549559569579589599609619629639649659669679689699709719729739749759769779789799809819829839849859869879889899909919929939949959969979989991000100110021003100410051006100710081009101010111012101310141015101610171018101910201021102210231024102510261027102810291030103110321033103410351036103710381039104010411042104310441045104610471048104910501051105210531054105510561057105810591060106110621063106410651066106710681069107010711072107310741075107610771078107910801081108210831084108510861087108810891090109110921093109410951096109710981099110011011102110311041105110611071108110911101111111211131114111511161117111811191120112111221123112411251126112711281129113011311132113311341135113611371138113911401141114211431144114511461147114811491150115111521153115411551156115711581159116011611162116311641165116611671168116911701171117211731174117511761177117811791180118111821183118411851186118711881189119011911192119311941195119611971198119912001201120212031204120512061207120812091210121112121213121412151216121712181219122012211222122312241225122612271228122912301231123212331234123512361237123812391240124112421243124412451246124712481249125012511252125312541255125612571258125912601261126212631264126512661267126812691270127112721273127412751276127712781279128012811282128312841285128612871288128912901291129212931294129512961297129812991300130113021303130413051306130713081309131013111312131313141315131613171318131913201321132213231324132513261327132813291330133113321333133413351336133713381339134013411342134313441345134613471348134913501351135213531354135513561357135813591360136113621363136413651366136713681369137013711372137313741375137613771378137913801381138213831384138513861387138813891390139113921393139413951396139713981399140014011402140314041405140614071408140914101411141214131414141514161417141814191420142114221423142414251426142714281429143014311432143314341435143614371438143914401441144214431444144514461447144814491450145114521453145414551456145714581459146014611462146314641465146614671468146914701471147214731474147514761477147814791480148114821483148414851486148714881489149014911492149314941495149614971498149915001501150215031504150515061507150815091510151115121513151415151516151715181519152015211522152315241525152615271528152915301531153215331534153515361537153815391540154115421543154415451546154715481549155015511552155315541555155615571558155915601561156215631564156515661567156815691570157115721573157415751576157715781579158015811582158315841585158615871588158915901591159215931594159515961597159815991600160116021603160416051606160716081609161016111612161316141615161616171618161916201621162216231624162516261627162816291630163116321633163416351636163716381639164016411642164316441645164616471648164916501651165216531654165516561657165816591660166116621663166416651666166716681669167016711672167316741675167616771678167916801681168216831684168516861687168816891690169116921693169416951696169716981699170017011702170317041705170617071708170917101711171217131714171517161717171817191720172117221723172417251726172717281729173017311732173317341735173617371738173917401741174217431744174517461747174817491750175117521753175417551756175717581759176017611762176317641765176617671768176917701771177217731774177517761777177817791780178117821783178417851786178717881789179017911792179317941795179617971798179918001801180218031804180518061807180818091810181118121813181418151816181718181819182018211822182318241825182618271828182918301831183218331834183518361837183818391840184118421843184418451846184718481849185018511852185318541855185618571858185918601861186218631864186518661867186818691870187118721873187418751876187718781879188018811882188318841885188618871888188918901891189218931894189518961897189818991900190119021903190419051906190719081909191019111912191319141915191619171918191919201921192219231924192519261927192819291930193119321933193419351936193719381939194019411942194319441945194619471948194919501951195219531954195519561957195819591960196119621963196419651966196719681969197019711972197319741975197619771978197919801981198219831984198519861987198819891990199119921993199419951996199719981999200020012002
  1. unit TestDBBasics;
  2. {$IFDEF FPC}
  3. {$mode Delphi}{$H+}
  4. {$ENDIF}
  5. interface
  6. uses
  7. fpcunit, testutils, testregistry, testdecorator,
  8. Classes, SysUtils, db;
  9. type
  10. { TTestDBBasics }
  11. TTestDBBasics = class(TTestCase)
  12. private
  13. procedure TestOnFilterProc(DataSet: TDataSet; var Accept: Boolean);
  14. procedure TestfieldDefinition(AFieldType : TFieldType;ADatasize : integer;var ADS : TDataset; var AFld: TField);
  15. procedure TestcalculatedField_OnCalcfields(DataSet: TDataSet);
  16. procedure FTestDelete1(TestCancelUpdate : boolean);
  17. procedure FTestDelete2(TestCancelUpdate : boolean);
  18. procedure FTestXMLDatasetDefinition(ADataset : TDataset);
  19. procedure TestAddIndexFieldType(AFieldType : TFieldType; ActiveDS : boolean);
  20. protected
  21. procedure SetUp; override;
  22. procedure TearDown; override;
  23. published
  24. procedure TestFileNameProperty;
  25. procedure TestClientDatasetAsMemDataset;
  26. procedure TestCancelUpdDelete1;
  27. procedure TestCancelUpdDelete2;
  28. procedure TestSafeAsXML;
  29. procedure TestBookmarks;
  30. procedure TestBookmarkValid;
  31. procedure TestLocate;
  32. procedure TestFirst;
  33. procedure TestDelete1;
  34. procedure TestDelete2;
  35. procedure TestIntFilter;
  36. procedure TestOnFilter;
  37. procedure TestStringFilter;
  38. procedure TestSetFieldValues;
  39. procedure TestGetFieldValues;
  40. procedure TestAddIndex;
  41. procedure TestAddDescIndex;
  42. procedure TestAddCaseInsIndex;
  43. procedure TestInactSwitchIndex;
  44. procedure TestAddIndexInteger;
  45. procedure TestAddIndexSmallInt;
  46. procedure TestAddIndexBoolean;
  47. procedure TestAddIndexFloat;
  48. procedure TestAddIndexLargeInt;
  49. procedure TestAddIndexDateTime;
  50. procedure TestAddIndexCurrency;
  51. procedure TestAddIndexBCD;
  52. procedure TestAddIndexActiveDS;
  53. procedure TestAddIndexEditDS;
  54. procedure TestIndexFieldNames;
  55. procedure TestIndexFieldNamesAct;
  56. procedure TestIndexCurRecord;
  57. procedure TestAddDblIndex;
  58. procedure TestIndexEditRecord;
  59. procedure TestNullAtOpen;
  60. procedure TestSupportIntegerFields;
  61. procedure TestSupportSmallIntFields;
  62. procedure TestSupportStringFields;
  63. procedure TestSupportBooleanFields;
  64. procedure TestSupportFloatFields;
  65. procedure TestSupportLargeIntFields;
  66. procedure TestSupportDateFields;
  67. procedure TestSupportCurrencyFields;
  68. procedure TestSupportBCDFields;
  69. procedure TestIsEmpty;
  70. procedure TestAppendOnEmptyDataset;
  71. procedure TestInsertOnEmptyDataset;
  72. procedure TestBufDatasetCancelUpd; //bug 6938
  73. procedure TestEofAfterFirst; //bug 7211
  74. procedure TestBufDatasetCancelUpd1;
  75. procedure TestDoubleClose;
  76. procedure TestCalculatedField;
  77. procedure TestAssignFieldftString;
  78. procedure TestAssignFieldftFixedChar;
  79. procedure TestSelectQueryBasics;
  80. procedure TestPostOnlyInEditState;
  81. procedure TestMove; // bug 5048
  82. procedure TestActiveBufferWhenClosed;
  83. procedure TestEOFBOFClosedDataset;
  84. procedure TestDataEventsResync;
  85. procedure TestBug7007;
  86. procedure TestBug6893;
  87. procedure TestRecordcountAfterReopen; // partly bug 8228
  88. procedure TestdeFieldListChange;
  89. procedure TestLastAppendCancel; // bug 5058
  90. procedure TestRecNo; // bug 5061
  91. procedure TestSetRecNo; // bug 6919
  92. procedure TestRequired;
  93. end;
  94. { TSQLTestSetup }
  95. TDBBasicsTestSetup = class(TTestSetup)
  96. protected
  97. procedure OneTimeSetup; override;
  98. procedure OneTimeTearDown; override;
  99. end;
  100. implementation
  101. uses toolsunit, bufdataset, variants;
  102. type THackDataLink=class(TdataLink);
  103. procedure TTestDBBasics.TestIsEmpty;
  104. begin
  105. if not (DBConnector.GetNDataset(5) is TBufDataset) then
  106. Ignore('This test only applies to TBufDataset and descendents.');
  107. with tbufdataset(DBConnector.GetNDataset(True,1)) do
  108. begin
  109. open;
  110. delete;
  111. refresh;
  112. applyupdates;
  113. AssertTrue(IsEmpty);
  114. end;
  115. end;
  116. procedure TTestDBBasics.TestAppendOnEmptyDataset;
  117. begin
  118. with DBConnector.GetNDataset(0) do
  119. begin
  120. open;
  121. AssertTrue(CanModify);
  122. AssertTrue(eof);
  123. AssertTrue(bof);
  124. append;
  125. AssertFalse(Bof);
  126. AssertTrue(Eof);
  127. post;
  128. AssertFalse(eof);
  129. AssertFalse(bof);
  130. end;
  131. end;
  132. procedure TTestDBBasics.TestInsertOnEmptyDataset;
  133. begin
  134. with DBConnector.GetNDataset(0) do
  135. begin
  136. open;
  137. AssertTrue(CanModify);
  138. AssertTrue(eof);
  139. AssertTrue(bof);
  140. AssertTrue(IsEmpty);
  141. insert;
  142. AssertTrue(Bof);
  143. AssertTrue(Eof);
  144. AssertFalse(IsEmpty);
  145. post;
  146. AssertFalse(IsEmpty);
  147. AssertFalse(eof);
  148. AssertFalse(bof);
  149. end;
  150. end;
  151. procedure TTestDBBasics.TestSelectQueryBasics;
  152. var b : TFieldType;
  153. begin
  154. with DBConnector.GetNDataset(1) do
  155. begin
  156. Open;
  157. AssertEquals(1,RecNo);
  158. AssertEquals(1,RecordCount);
  159. AssertEquals(2,FieldCount);
  160. AssertTrue(CompareText('ID',fields[0].FieldName)=0);
  161. AssertTrue(CompareText('ID',fields[0].DisplayName)=0);
  162. AssertTrue('The datatype of the field ''ID'' is incorrect, it should be ftInteger',ftInteger=fields[0].DataType);
  163. AssertTrue(CompareText('NAME',fields[1].FieldName)=0);
  164. AssertTrue(CompareText('NAME',fields[1].DisplayName)=0);
  165. AssertTrue(ftString=fields[1].DataType);
  166. AssertEquals(1,fields[0].Value);
  167. AssertEquals('TestName1',fields[1].Value);
  168. Close;
  169. end;
  170. end;
  171. procedure TTestDBBasics.TestPostOnlyInEditState;
  172. begin
  173. with DBConnector.GetNDataset(1) do
  174. begin
  175. open;
  176. AssertException('Post was called in a non-edit state',EDatabaseError,Post);
  177. end;
  178. end;
  179. procedure TTestDBBasics.TestMove;
  180. var i,count : integer;
  181. aDatasource : TDataSource;
  182. aDatalink : TDataLink;
  183. ABufferCount : Integer;
  184. begin
  185. aDatasource := TDataSource.Create(nil);
  186. aDatalink := TTestDataLink.Create;
  187. aDatalink.DataSource := aDatasource;
  188. ABufferCount := 11;
  189. aDatalink.BufferCount := ABufferCount;
  190. DataEvents := '';
  191. for count := 0 to 32 do
  192. begin
  193. aDatasource.DataSet := DBConnector.GetNDataset(count);
  194. with aDatasource.Dataset do
  195. begin
  196. i := 1;
  197. Open;
  198. AssertEquals('deUpdateState:0;',DataEvents);
  199. DataEvents := '';
  200. while not EOF do
  201. begin
  202. AssertEquals(i,fields[0].AsInteger);
  203. AssertEquals('TestName'+inttostr(i),fields[1].AsString);
  204. inc(i);
  205. Next;
  206. if (i > ABufferCount) and not EOF then
  207. AssertEquals('deCheckBrowseMode:0;deDataSetScroll:-1;DataSetScrolled:1;',DataEvents)
  208. else
  209. AssertEquals('deCheckBrowseMode:0;deDataSetScroll:0;DataSetScrolled:0;',DataEvents);
  210. DataEvents := '';
  211. end;
  212. AssertEquals(count,i-1);
  213. close;
  214. AssertEquals('deUpdateState:0;',DataEvents);
  215. DataEvents := '';
  216. end;
  217. end;
  218. end;
  219. procedure TTestDBBasics.TestdeFieldListChange;
  220. var i,count : integer;
  221. aDatasource : TDataSource;
  222. aDatalink : TDataLink;
  223. ds : TDataset;
  224. begin
  225. aDatasource := TDataSource.Create(nil);
  226. aDatalink := TTestDataLink.Create;
  227. aDatalink.DataSource := aDatasource;
  228. ds := DBConnector.GetNDataset(1);
  229. with ds do
  230. begin
  231. aDatasource.DataSet := ds;
  232. DataEvents := '';
  233. open;
  234. Fields.add(tfield.Create(DBConnector.GetNDataset(1)));
  235. AssertEquals('deUpdateState:0;deFieldListChange:0;',DataEvents);
  236. DataEvents := '';
  237. fields.Clear;
  238. AssertEquals('deFieldListChange:0;',DataEvents)
  239. end;
  240. aDatasource.Free;
  241. aDatalink.Free;
  242. end;
  243. procedure TTestDBBasics.TestActiveBufferWhenClosed;
  244. begin
  245. with DBConnector.GetNDataset(0) do
  246. begin
  247. AssertNull(ActiveBuffer);
  248. open;
  249. AssertFalse('Activebuffer of an empty dataset shouldn''t be nil',ActiveBuffer = nil);
  250. end;
  251. end;
  252. procedure TTestDBBasics.TestEOFBOFClosedDataset;
  253. begin
  254. with DBConnector.GetNDataset(1) do
  255. begin
  256. AssertTrue(EOF);
  257. AssertTrue(BOF);
  258. open;
  259. close;
  260. AssertTrue(EOF);
  261. AssertTrue(BOF);
  262. end;
  263. end;
  264. procedure TTestDBBasics.TestDataEventsResync;
  265. var i,count : integer;
  266. aDatasource : TDataSource;
  267. aDatalink : TDataLink;
  268. ds : tdataset;
  269. begin
  270. aDatasource := TDataSource.Create(nil);
  271. aDatalink := TTestDataLink.Create;
  272. aDatalink.DataSource := aDatasource;
  273. ds := DBConnector.GetNDataset(6);
  274. ds.BeforeScroll := DBConnector.DataEvent;
  275. with ds do
  276. begin
  277. aDatasource.DataSet := ds;
  278. open;
  279. DataEvents := '';
  280. Resync([rmExact]);
  281. AssertEquals('deDataSetChange:0;',DataEvents);
  282. DataEvents := '';
  283. next;
  284. AssertEquals('deCheckBrowseMode:0;DataEvent;deDataSetScroll:0;DataSetScrolled:1;',DataEvents);
  285. close;
  286. end;
  287. aDatasource.Free;
  288. aDatalink.Free;
  289. end;
  290. procedure TTestDBBasics.TestLastAppendCancel;
  291. var count : integer;
  292. begin
  293. for count := 0 to 32 do with DBConnector.GetNDataset(count) do
  294. begin
  295. open;
  296. Last;
  297. Append;
  298. Cancel;
  299. AssertEquals(count,fields[0].asinteger);
  300. AssertEquals(count,RecordCount);
  301. Close;
  302. end;
  303. end;
  304. procedure TTestDBBasics.TestRecNo;
  305. var i : longint;
  306. passed : boolean;
  307. begin
  308. with DBConnector.GetNDataset(0) do
  309. begin
  310. // Accessing RecNo on a closed dataset should raise an EDatabaseError or should
  311. // return 0
  312. passed := false;
  313. try
  314. i := recno;
  315. except on E: Exception do
  316. begin
  317. passed := E.classname = EDatabaseError.className
  318. end;
  319. end;
  320. if not passed then
  321. AssertEquals('Failed to get the RecNo from a closed dataset',0,RecNo);
  322. // Accessing Recordcount on a closed dataset should raise an EDatabaseError or should
  323. // return 0
  324. passed := false;
  325. try
  326. i := recordcount;
  327. except on E: Exception do
  328. begin
  329. passed := E.classname = EDatabaseError.className
  330. end;
  331. end;
  332. if not passed then
  333. AssertEquals('Failed to get the Recordcount from a closed dataset',0,RecNo);
  334. Open;
  335. AssertEquals(0,RecordCount);
  336. AssertEquals(0,RecNo);
  337. first;
  338. AssertEquals(0,RecordCount);
  339. AssertEquals(0,RecNo);
  340. last;
  341. AssertEquals(0,RecordCount);
  342. AssertEquals(0,RecNo);
  343. append;
  344. AssertEquals(0,RecNo);
  345. AssertEquals(0,RecordCount);
  346. first;
  347. AssertEquals(0,RecNo);
  348. AssertEquals(0,RecordCount);
  349. append;
  350. FieldByName('id').AsInteger := 1;
  351. AssertEquals(0,RecNo);
  352. AssertEquals(0,RecordCount);
  353. first;
  354. AssertEquals(1,RecNo);
  355. AssertEquals(1,RecordCount);
  356. last;
  357. AssertEquals(1,RecNo);
  358. AssertEquals(1,RecordCount);
  359. append;
  360. FieldByName('id').AsInteger := 1;
  361. AssertEquals(0,RecNo);
  362. AssertEquals(1,RecordCount);
  363. Close;
  364. end;
  365. end;
  366. procedure TTestDBBasics.TestSetRecNo;
  367. begin
  368. with DBConnector.GetNDataset(15) do
  369. begin
  370. Open;
  371. RecNo := 1;
  372. AssertEquals(1,fields[0].AsInteger);
  373. AssertEquals(1,RecNo);
  374. RecNo := 2;
  375. AssertEquals(2,fields[0].AsInteger);
  376. AssertEquals(2,RecNo);
  377. RecNo := 8;
  378. AssertEquals(8,fields[0].AsInteger);
  379. AssertEquals(8,RecNo);
  380. RecNo := 15;
  381. AssertEquals(15,fields[0].AsInteger);
  382. AssertEquals(15,RecNo);
  383. RecNo := 3;
  384. AssertEquals(3,fields[0].AsInteger);
  385. AssertEquals(3,RecNo);
  386. RecNo := 14;
  387. AssertEquals(14,fields[0].AsInteger);
  388. AssertEquals(14,RecNo);
  389. RecNo := 15;
  390. AssertEquals(15,fields[0].AsInteger);
  391. AssertEquals(15,RecNo);
  392. // test for exceptions...
  393. { RecNo := 16;
  394. AssertEquals(15,fields[0].AsInteger);
  395. AssertEquals(15,RecNo);}
  396. Close;
  397. end;
  398. end;
  399. procedure TTestDBBasics.TestRequired;
  400. begin
  401. with DBConnector.GetNDataset(2) do
  402. begin
  403. Open;
  404. FieldByName('ID').Required := True;
  405. Append;
  406. AssertException(EDatabaseError,Post);
  407. FieldByName('ID').AsInteger := 1000;
  408. Post;
  409. Close;
  410. end;
  411. end;
  412. procedure TTestDBBasics.SetUp;
  413. begin
  414. DBConnector.StartTest;
  415. end;
  416. procedure TTestDBBasics.TearDown;
  417. begin
  418. DBConnector.StopTest;
  419. end;
  420. procedure TTestDBBasics.TestSafeAsXML;
  421. var ds : TDataset;
  422. LoadDs: TBufDataset;
  423. begin
  424. ds := DBConnector.GetNDataset(true,5);
  425. if not (ds is TBufDataset) then
  426. Ignore('This test only applies to TBufDataset and descendents.');
  427. ds.open;
  428. TBufDataset(ds).SaveToFile('test.xml');
  429. ds.close;
  430. LoadDs := TBufDataset.Create(nil);
  431. LoadDs.LoadFromFile('test.xml');
  432. FTestXMLDatasetDefinition(LoadDS);
  433. end;
  434. procedure TTestDBBasics.TestFileNameProperty;
  435. var ds : TDataset;
  436. LoadDs: TBufDataset;
  437. begin
  438. ds := DBConnector.GetNDataset(true,5);
  439. if not (ds is TBufDataset) then
  440. Ignore('This test only applies to TBufDataset and descendents.');
  441. ds.open;
  442. TBufDataset(ds).FileName:='test.xml';
  443. ds.close;
  444. ds := DBConnector.GetNDataset(True,7);
  445. TBufDataset(ds).FileName:='test.xml';
  446. ds.Open;
  447. FTestXMLDatasetDefinition(Ds);
  448. end;
  449. procedure TTestDBBasics.TestClientDatasetAsMemDataset;
  450. var ds : TBufDataset;
  451. i : integer;
  452. begin
  453. ds := TBufDataset.Create(nil);
  454. DS.FieldDefs.Add('ID',ftInteger);
  455. DS.FieldDefs.Add('NAME',ftString,50);
  456. DS.CreateDataset;
  457. DS.Open;
  458. for i := 1 to 10 do
  459. begin
  460. ds.Append;
  461. ds.FieldByName('ID').AsInteger := i;
  462. ds.FieldByName('NAME').AsString := 'TestName' + inttostr(i);
  463. DS.Post;
  464. end;
  465. ds.first;
  466. for i := 1 to 10 do
  467. begin
  468. AssertEquals(i,ds.fieldbyname('ID').asinteger);
  469. AssertEquals('TestName' + inttostr(i),ds.fieldbyname('NAME').AsString);
  470. ds.next;
  471. end;
  472. AssertTrue(ds.EOF);
  473. DS.Close;
  474. end;
  475. procedure TTestDBBasics.TestBookmarks;
  476. var BM1,BM2,BM3,BM4,BM5 : TBookmark;
  477. begin
  478. with DBConnector.GetNDataset(true,14) do
  479. begin
  480. AssertNull(GetBookmark);
  481. open;
  482. BM1:=GetBookmark; // id=1, BOF
  483. next;next;
  484. BM2:=GetBookmark; // id=3
  485. next;next;next;
  486. BM3:=GetBookmark; // id=6
  487. next;next;next;next;next;next;next;next;
  488. BM4:=GetBookmark; // id=14
  489. next;
  490. BM5:=GetBookmark; // id=14, EOF
  491. GotoBookmark(BM2);
  492. AssertEquals(3,FieldByName('id').AsInteger);
  493. GotoBookmark(BM1);
  494. AssertEquals(1,FieldByName('id').AsInteger);
  495. GotoBookmark(BM3);
  496. AssertEquals(6,FieldByName('id').AsInteger);
  497. GotoBookmark(BM4);
  498. AssertEquals(14,FieldByName('id').AsInteger);
  499. GotoBookmark(BM3);
  500. AssertEquals(6,FieldByName('id').AsInteger);
  501. GotoBookmark(BM5);
  502. AssertEquals(14,FieldByName('id').AsInteger);
  503. GotoBookmark(BM1);
  504. AssertEquals(1,FieldByName('id').AsInteger);
  505. next;
  506. delete;
  507. GotoBookmark(BM2);
  508. AssertEquals(3,FieldByName('id').AsInteger);
  509. delete;delete;
  510. GotoBookmark(BM3);
  511. AssertEquals(6,FieldByName('id').AsInteger);
  512. GotoBookmark(BM1);
  513. AssertEquals(1,FieldByName('id').AsInteger);
  514. insert;
  515. fieldbyname('id').AsInteger:=20;
  516. insert;
  517. fieldbyname('id').AsInteger:=21;
  518. insert;
  519. fieldbyname('id').AsInteger:=22;
  520. insert;
  521. fieldbyname('id').AsInteger:=23;
  522. post;
  523. GotoBookmark(BM3);
  524. AssertEquals(6,FieldByName('id').AsInteger);
  525. GotoBookmark(BM1);
  526. AssertEquals(1,FieldByName('id').AsInteger);
  527. GotoBookmark(BM5);
  528. AssertEquals(14,FieldByName('id').AsInteger);
  529. end;
  530. end;
  531. procedure TTestDBBasics.TestBookmarkValid;
  532. var BM1,BM2,BM3,BM4,BM5 : TBookmark;
  533. begin
  534. with DBConnector.GetNDataset(true,14) do
  535. begin
  536. BM1 := Nil;
  537. AssertFalse(BookmarkValid(BM1));
  538. open;
  539. BM1:=GetBookmark; // id=1, BOF
  540. AssertTrue(BookmarkValid(BM1));
  541. next;next;
  542. BM2:=GetBookmark; // id=3
  543. AssertTrue(BookmarkValid(BM2));
  544. next;next;next;
  545. BM3:=GetBookmark; // id=6
  546. AssertTrue(BookmarkValid(BM3));
  547. next;next;next;next;next;next;next;next;
  548. BM4:=GetBookmark; // id=14
  549. AssertTrue(BookmarkValid(BM4));
  550. next;
  551. BM5:=GetBookmark; // id=14, EOF
  552. AssertTrue(BookmarkValid(BM5));
  553. AssertTrue(BookmarkValid(BM4));
  554. AssertTrue(BookmarkValid(BM3));
  555. AssertTrue(BookmarkValid(BM2));
  556. AssertTrue(BookmarkValid(BM1));
  557. GotoBookmark(BM2);
  558. AssertTrue(BookmarkValid(BM5));
  559. AssertTrue(BookmarkValid(BM4));
  560. AssertTrue(BookmarkValid(BM3));
  561. AssertTrue(BookmarkValid(BM2));
  562. AssertTrue(BookmarkValid(BM1));
  563. end;
  564. end;
  565. procedure TTestDBBasics.TestLocate;
  566. begin
  567. with DBConnector.GetNDataset(true,13) do
  568. begin
  569. open;
  570. asserttrue(Locate('id',vararrayof([5]),[]));
  571. AssertEquals(5,FieldByName('id').AsInteger);
  572. AssertFalse(Locate('id',vararrayof([15]),[]));
  573. asserttrue(Locate('id',vararrayof([12]),[]));
  574. AssertEquals(12,FieldByName('id').AsInteger);
  575. close;
  576. open;
  577. asserttrue(Locate('id',vararrayof([12]),[]));
  578. AssertEquals(12,FieldByName('id').AsInteger);
  579. asserttrue(Locate('id;name',vararrayof([4,'TestName4']),[]));
  580. AssertEquals(4,FieldByName('id').AsInteger);
  581. assertFalse(Locate('id;name',vararrayof([4,'TestName5']),[]));
  582. end;
  583. end;
  584. procedure TTestDBBasics.TestSetFieldValues;
  585. var PassException : boolean;
  586. begin
  587. with DBConnector.GetNDataset(true,11) do
  588. begin
  589. open;
  590. first;
  591. edit;
  592. FieldValues['id']:=5;
  593. post;
  594. AssertEquals('TestName1',FieldByName('name').AsString);
  595. AssertEquals(5,FieldByName('id').AsInteger);
  596. edit;
  597. FieldValues['name']:='FieldValuesTestName';
  598. post;
  599. AssertEquals('FieldValuesTestName',FieldByName('name').AsString);
  600. AssertEquals(5,FieldByName('id').AsInteger);
  601. edit;
  602. FieldValues['id;name']:= VarArrayOf([243,'ValuesTestName']);
  603. post;
  604. AssertEquals('ValuesTestName',FieldByName('name').AsString);
  605. AssertEquals(243,FieldByName('id').AsInteger);
  606. PassException:=false;
  607. try
  608. edit;
  609. FieldValues['id;name;fake']:= VarArrayOf([243,'ValuesTestName',4]);
  610. except
  611. on E: EDatabaseError do PassException := True;
  612. end;
  613. post;
  614. AssertTrue(PassException);
  615. end;
  616. end;
  617. procedure TTestDBBasics.TestGetFieldValues;
  618. var AVar : Variant;
  619. PassException : boolean;
  620. begin
  621. with DBConnector.GetNDataset(true,14) do
  622. begin
  623. open;
  624. AVar:=FieldValues['id'];
  625. AssertEquals(AVar,1);
  626. AVar:=FieldValues['name'];
  627. AssertEquals(AVar,'TestName1');
  628. AVar:=FieldValues['id;name'];
  629. AssertEquals(AVar[0],1);
  630. AssertEquals(AVar[1],'TestName1');
  631. AVar:=FieldValues['name;id;'];
  632. AssertEquals(AVar[1],1);
  633. AssertEquals(AVar[0],'TestName1');
  634. PassException:=false;
  635. try
  636. AVar:=FieldValues['name;id;fake'];
  637. except
  638. on E: EDatabaseError do PassException := True;
  639. end;
  640. AssertTrue(PassException);
  641. end;
  642. end;
  643. procedure TTestDBBasics.TestFirst;
  644. var i : integer;
  645. begin
  646. with DBConnector.GetNDataset(true,14) do
  647. begin
  648. open;
  649. AssertEquals(1,FieldByName('ID').AsInteger);
  650. First;
  651. AssertEquals(1,FieldByName('ID').AsInteger);
  652. next;
  653. AssertEquals(2,FieldByName('ID').AsInteger);
  654. First;
  655. AssertEquals(1,FieldByName('ID').AsInteger);
  656. for i := 0 to 12 do
  657. next;
  658. AssertEquals(14,FieldByName('ID').AsInteger);
  659. First;
  660. AssertEquals(1,FieldByName('ID').AsInteger);
  661. close;
  662. end;
  663. end;
  664. procedure TTestDBBasics.TestDelete1;
  665. begin
  666. FTestDelete1(false);
  667. end;
  668. procedure TTestDBBasics.TestDelete2;
  669. begin
  670. FTestDelete2(false);
  671. end;
  672. procedure TTestDBBasics.TestCancelUpdDelete1;
  673. begin
  674. FTestDelete1(true);
  675. end;
  676. procedure TTestDBBasics.TestCancelUpdDelete2;
  677. begin
  678. FTestDelete2(true);
  679. end;
  680. procedure TTestDBBasics.FTestDelete1(TestCancelUpdate : boolean);
  681. // Test the deletion of records, including the first and the last one
  682. var i : integer;
  683. ds : TDataset;
  684. begin
  685. ds := DBConnector.GetNDataset(true,17);
  686. with ds do
  687. begin
  688. Open;
  689. for i := 0 to 16 do if i mod 4=0 then
  690. delete
  691. else
  692. next;
  693. First;
  694. for i := 0 to 16 do
  695. begin
  696. if i mod 4<>0 then
  697. begin
  698. AssertEquals(i+1,FieldByName('ID').AsInteger);
  699. AssertEquals('TestName'+inttostr(i+1),FieldByName('NAME').AsString);
  700. next;
  701. end;
  702. end;
  703. end;
  704. if TestCancelUpdate then
  705. begin
  706. if not (ds is TBufDataset) then
  707. Ignore('This test only applies to TBufDataset and descendents.');
  708. with TBufDataset(ds) do
  709. begin
  710. CancelUpdates;
  711. First;
  712. for i := 0 to 16 do
  713. begin
  714. AssertEquals(i+1,FieldByName('ID').AsInteger);
  715. AssertEquals('TestName'+inttostr(i+1),FieldByName('NAME').AsString);
  716. next;
  717. end;
  718. close;
  719. end;
  720. end;
  721. end;
  722. procedure TTestDBBasics.FTestDelete2(TestCancelUpdate : boolean);
  723. // Test the deletion of edited and appended records
  724. var i : integer;
  725. ds : TDataset;
  726. begin
  727. ds := DBConnector.GetNDataset(true,17);
  728. with ds do
  729. begin
  730. Open;
  731. for i := 0 to 16 do
  732. begin
  733. if i mod 4=0 then
  734. begin
  735. edit;
  736. fieldbyname('name').AsString:='this record will be gone soon';
  737. post;
  738. end;
  739. next;
  740. end;
  741. for i := 17 to 20 do
  742. begin
  743. append;
  744. fieldbyname('id').AsInteger:=i+1;
  745. fieldbyname('name').AsString:='TestName'+inttostr(i+1);
  746. post;
  747. end;
  748. first;
  749. for i := 0 to 20 do if i mod 4=0 then
  750. delete
  751. else
  752. next;
  753. First;
  754. i := 0;
  755. for i := 0 to 20 do
  756. begin
  757. if i mod 4<>0 then
  758. begin
  759. AssertEquals(i+1,FieldByName('ID').AsInteger);
  760. AssertEquals('TestName'+inttostr(i+1),FieldByName('NAME').AsString);
  761. next;
  762. end;
  763. end;
  764. end;
  765. if TestCancelUpdate then
  766. begin
  767. if not (ds is TBufDataset) then
  768. Ignore('This test only applies to TBufDataset and descendents.');
  769. with TBufDataset(ds) do
  770. begin
  771. CancelUpdates;
  772. First;
  773. for i := 0 to 16 do
  774. begin
  775. AssertEquals(i+1,FieldByName('ID').AsInteger);
  776. AssertEquals('TestName'+inttostr(i+1),FieldByName('NAME').AsString);
  777. next;
  778. end;
  779. close;
  780. end;
  781. end;
  782. end;
  783. procedure TTestDBBasics.FTestXMLDatasetDefinition(ADataset: TDataset);
  784. begin
  785. AssertEquals(2,ADataset.FieldDefs.Count);
  786. AssertEquals(5,ADataset.RecordCount);
  787. AssertEquals(2,ADataset.Fields.Count);
  788. AssertEquals('ID',ADataset.Fields[0].FieldName);
  789. AssertEquals('NAME',ADataset.Fields[1].FieldName);
  790. AssertTrue('Incorrect fieldtype',ADataset.fields[1].DataType=ftString);
  791. AssertEquals('TestName1',ADataset.FieldByName('name').AsString);
  792. ADataset.Next;
  793. AssertEquals('TestName2',ADataset.FieldByName('name').AsString);
  794. end;
  795. procedure TTestDBBasics.TestOnFilterProc(DataSet: TDataSet; var Accept: Boolean);
  796. var a : TDataSetState;
  797. begin
  798. Accept := odd(Dataset.FieldByName('ID').AsInteger);
  799. end;
  800. procedure TTestDBBasics.TestOnFilter;
  801. var tel : byte;
  802. begin
  803. with DBConnector.GetNDataset(15) do
  804. begin
  805. OnFilterRecord := TestOnFilterProc;
  806. Filtered := True;
  807. Open;
  808. for tel := 1 to 8 do
  809. begin
  810. AssertTrue(odd(FieldByName('ID').asinteger));
  811. next;
  812. end;
  813. AssertTrue(EOF);
  814. end;
  815. end;
  816. procedure TTestDBBasics.TestIntFilter;
  817. var tel : byte;
  818. begin
  819. with DBConnector.GetNDataset(15) do
  820. begin
  821. Filtered := True;
  822. Filter := '(id>4) and (id<9)';
  823. Open;
  824. for tel := 5 to 8 do
  825. begin
  826. AssertEquals(tel,FieldByName('ID').asinteger);
  827. next;
  828. end;
  829. AssertTrue(EOF);
  830. Close;
  831. end;
  832. end;
  833. procedure TTestDBBasics.TestRecordcountAfterReopen;
  834. var
  835. datalink1: tdatalink;
  836. datasource1: tdatasource;
  837. query1: TDataSet;
  838. begin
  839. query1:= DBConnector.GetNDataset(11);
  840. datalink1:= TDataLink.create;
  841. datasource1:= tdatasource.create(nil);
  842. try
  843. datalink1.datasource:= datasource1;
  844. datasource1.dataset:= query1;
  845. query1.active := true;
  846. query1.active := False;
  847. AssertEquals(0, THackDataLink(datalink1).RecordCount);
  848. query1.active := true;
  849. AssertTrue(THackDataLink(datalink1).RecordCount>0);
  850. query1.active := false;
  851. finally
  852. datalink1.free;
  853. datasource1.free;
  854. end;
  855. end;
  856. procedure TTestDBBasics.TestStringFilter;
  857. var tel : byte;
  858. begin
  859. with DBConnector.GetNDataset(15) do
  860. begin
  861. Open;
  862. //FilterOptions := [foNoPartialCompare];
  863. //FilterOptions := [];
  864. Filter := '(name=''*Name3'')';
  865. Filtered := True;
  866. AssertFalse(EOF);
  867. AssertEquals(3,FieldByName('ID').asinteger);
  868. AssertEquals('TestName3',FieldByName('NAME').asstring);
  869. next;
  870. AssertTrue(EOF);
  871. Close;
  872. end;
  873. end;
  874. procedure TTestDBBasics.TestAddIndexFieldType(AFieldType: TFieldType; ActiveDS : boolean);
  875. var ds : TBufDataset;
  876. FList : TStringList;
  877. LastValue : Variant;
  878. StrValue : String;
  879. begin
  880. ds := DBConnector.GetFieldDataset as TBufDataset;
  881. with ds do
  882. begin
  883. if not ActiveDS then
  884. begin
  885. AddIndex('testindex','F'+FieldTypeNames[AfieldType],[]);
  886. IndexName:='testindex';
  887. end
  888. else
  889. MaxIndexesCount := 3;
  890. try
  891. open;
  892. except
  893. if not assigned(ds.FindField('F'+FieldTypeNames[AfieldType])) then
  894. Ignore('Fields of the type ' + FieldTypeNames[AfieldType] + ' are not supported by this type of dataset')
  895. else
  896. raise;
  897. end;
  898. if ActiveDS then
  899. begin
  900. if not assigned(ds.FindField('F'+FieldTypeNames[AfieldType])) then
  901. Ignore('Fields of the type ' + FieldTypeNames[AfieldType] + ' are not supported by this type of dataset');
  902. AddIndex('testindex','F'+FieldTypeNames[AfieldType],[]);
  903. IndexName:='testindex';
  904. First;
  905. end;
  906. LastValue:=null;
  907. while not eof do
  908. begin
  909. if AFieldType=ftString then
  910. AssertTrue(AnsiCompareStr(VarToStr(LastValue),VarToStr(FieldByName('F'+FieldTypeNames[AfieldType]).AsString))<=0)
  911. else
  912. AssertTrue(LastValue<=FieldByName('F'+FieldTypeNames[AfieldType]).AsVariant);
  913. LastValue:=FieldByName('F'+FieldTypeNames[AfieldType]).AsVariant;
  914. Next;
  915. end;
  916. while not bof do
  917. begin
  918. if AFieldType=ftString then
  919. AssertTrue(AnsiCompareStr(VarToStr(LastValue),VarToStr(FieldByName('F'+FieldTypeNames[AfieldType]).AsString))>=0)
  920. else
  921. AssertTrue(LastValue>=FieldByName('F'+FieldTypeNames[AfieldType]).AsVariant);
  922. LastValue:=FieldByName('F'+FieldTypeNames[AfieldType]).AsVariant;
  923. Prior;
  924. end;
  925. end;
  926. end;
  927. procedure TTestDBBasics.TestAddIndexSmallInt;
  928. begin
  929. TestAddIndexFieldType(ftSmallint,False);
  930. end;
  931. procedure TTestDBBasics.TestAddIndexBoolean;
  932. begin
  933. TestAddIndexFieldType(ftBoolean,False);
  934. end;
  935. procedure TTestDBBasics.TestAddIndexFloat;
  936. begin
  937. TestAddIndexFieldType(ftFloat,False);
  938. end;
  939. procedure TTestDBBasics.TestAddIndexInteger;
  940. begin
  941. TestAddIndexFieldType(ftInteger,False);
  942. end;
  943. procedure TTestDBBasics.TestAddIndexLargeInt;
  944. begin
  945. TestAddIndexFieldType(ftLargeint,False);
  946. end;
  947. procedure TTestDBBasics.TestAddIndexDateTime;
  948. begin
  949. TestAddIndexFieldType(ftDateTime,False);
  950. end;
  951. procedure TTestDBBasics.TestAddIndexCurrency;
  952. begin
  953. TestAddIndexFieldType(ftCurrency,False);
  954. end;
  955. procedure TTestDBBasics.TestAddIndexBCD;
  956. begin
  957. TestAddIndexFieldType(ftBCD,False);
  958. end;
  959. procedure TTestDBBasics.TestAddIndex;
  960. var ds : TBufDataset;
  961. AFieldType : TFieldType;
  962. FList : TStringList;
  963. i : integer;
  964. begin
  965. ds := DBConnector.GetFieldDataset as TBufDataset;
  966. with ds do
  967. begin
  968. AFieldType:=ftString;
  969. AddIndex('testindex','F'+FieldTypeNames[AfieldType],[]);
  970. FList := TStringList.Create;
  971. FList.Sorted:=true;
  972. FList.CaseSensitive:=True;
  973. FList.Duplicates:=dupAccept;
  974. open;
  975. while not eof do
  976. begin
  977. flist.Add(FieldByName('F'+FieldTypeNames[AfieldType]).AsString);
  978. Next;
  979. end;
  980. IndexName:='testindex';
  981. first;
  982. i:=0;
  983. while not eof do
  984. begin
  985. AssertEquals(flist[i],FieldByName('F'+FieldTypeNames[AfieldType]).AsString);
  986. inc(i);
  987. Next;
  988. end;
  989. while not bof do
  990. begin
  991. dec(i);
  992. AssertEquals(flist[i],FieldByName('F'+FieldTypeNames[AfieldType]).AsString);
  993. Prior;
  994. end;
  995. end;
  996. end;
  997. procedure TTestDBBasics.TestAddDescIndex;
  998. var ds : TBufDataset;
  999. AFieldType : TFieldType;
  1000. FList : TStringList;
  1001. i : integer;
  1002. begin
  1003. ds := DBConnector.GetFieldDataset as TBufDataset;
  1004. with ds do
  1005. begin
  1006. AFieldType:=ftString;
  1007. AddIndex('testindex','F'+FieldTypeNames[AfieldType],[],'F'+FieldTypeNames[AfieldType]);
  1008. FList := TStringList.Create;
  1009. FList.Sorted:=true;
  1010. FList.CaseSensitive:=True;
  1011. FList.Duplicates:=dupAccept;
  1012. open;
  1013. while not eof do
  1014. begin
  1015. flist.Add(FieldByName('F'+FieldTypeNames[AfieldType]).AsString);
  1016. Next;
  1017. end;
  1018. IndexName:='testindex';
  1019. first;
  1020. i:=FList.Count-1;
  1021. while not eof do
  1022. begin
  1023. AssertEquals(flist[i],FieldByName('F'+FieldTypeNames[AfieldType]).AsString);
  1024. dec(i);
  1025. Next;
  1026. end;
  1027. while not bof do
  1028. begin
  1029. inc(i);
  1030. AssertEquals(flist[i],FieldByName('F'+FieldTypeNames[AfieldType]).AsString);
  1031. Prior;
  1032. end;
  1033. end;
  1034. end;
  1035. procedure TTestDBBasics.TestAddCaseInsIndex;
  1036. var ds : TBufDataset;
  1037. AFieldType : TFieldType;
  1038. FList : TStringList;
  1039. i : integer;
  1040. begin
  1041. ds := DBConnector.GetFieldDataset as TBufDataset;
  1042. with ds do
  1043. begin
  1044. AFieldType:=ftString;
  1045. AddIndex('testindex','F'+FieldTypeNames[AfieldType],[],'','F'+FieldTypeNames[AfieldType]);
  1046. FList := TStringList.Create;
  1047. FList.Sorted:=true;
  1048. FList.Duplicates:=dupAccept;
  1049. open;
  1050. while not eof do
  1051. begin
  1052. flist.Add(FieldByName('F'+FieldTypeNames[AfieldType]).AsString);
  1053. Next;
  1054. end;
  1055. IndexName:='testindex';
  1056. first;
  1057. i:=0;
  1058. while not eof do
  1059. begin
  1060. AssertEquals(flist[i],FieldByName('F'+FieldTypeNames[AfieldType]).AsString);
  1061. inc(i);
  1062. Next;
  1063. end;
  1064. while not bof do
  1065. begin
  1066. dec(i);
  1067. AssertEquals(flist[i],FieldByName('F'+FieldTypeNames[AfieldType]).AsString);
  1068. Prior;
  1069. end;
  1070. end;
  1071. end;
  1072. procedure TTestDBBasics.TestInactSwitchIndex;
  1073. // Test if the default-index is properly build when the active index is not
  1074. // the default-index while opening then dataset
  1075. var ds : TBufDataset;
  1076. AFieldType : TFieldType;
  1077. i : integer;
  1078. begin
  1079. ds := DBConnector.GetFieldDataset as TBufDataset;
  1080. with ds do
  1081. begin
  1082. AFieldType:=ftString;
  1083. AddIndex('testindex','F'+FieldTypeNames[AfieldType],[]);
  1084. IndexName:='testindex';
  1085. open;
  1086. IndexName:=''; // This should set the default index (default_order)
  1087. first;
  1088. i := 0;
  1089. while not eof do
  1090. begin
  1091. AssertEquals(testStringValues[i],FieldByName('F'+FieldTypeNames[AfieldType]).AsString);
  1092. inc(i);
  1093. Next;
  1094. end;
  1095. end;
  1096. end;
  1097. procedure TTestDBBasics.TestAddIndexActiveDS;
  1098. var ds : TBufDataset;
  1099. I : integer;
  1100. begin
  1101. TestAddIndexFieldType(ftString,true);
  1102. end;
  1103. procedure TTestDBBasics.TestAddIndexEditDS;
  1104. var ds : TBufDataset;
  1105. I : integer;
  1106. LastValue : String;
  1107. begin
  1108. ds := DBConnector.GetNDataset(True,5) as TBufDataset;
  1109. with ds do
  1110. begin
  1111. MaxIndexesCount:=3;
  1112. open;
  1113. edit;
  1114. FieldByName('name').asstring := 'Zz';
  1115. post;
  1116. next;
  1117. next;
  1118. edit;
  1119. FieldByName('name').asstring := 'aA';
  1120. post;
  1121. AddIndex('test','name',[]);
  1122. first;
  1123. ds.IndexName:='test';
  1124. first;
  1125. LastValue:=FieldByName('name').AsString;
  1126. while not eof do
  1127. begin
  1128. AssertTrue(AnsiCompareStr(LastValue,FieldByName('name').AsString)<=0);
  1129. Next;
  1130. end;
  1131. end;
  1132. end;
  1133. procedure TTestDBBasics.TestIndexFieldNamesAct;
  1134. var ds : TBufDataset;
  1135. AFieldType : TFieldType;
  1136. FList : TStringList;
  1137. i : integer;
  1138. begin
  1139. ds := DBConnector.GetFieldDataset as TBufDataset;
  1140. with ds do
  1141. begin
  1142. AFieldType:=ftString;
  1143. FList := TStringList.Create;
  1144. FList.Sorted:=true;
  1145. FList.CaseSensitive:=True;
  1146. FList.Duplicates:=dupAccept;
  1147. open;
  1148. while not eof do
  1149. begin
  1150. flist.Add(FieldByName('F'+FieldTypeNames[AfieldType]).AsString);
  1151. Next;
  1152. end;
  1153. IndexFieldNames:='F'+FieldTypeNames[AfieldType];
  1154. first;
  1155. i:=0;
  1156. while not eof do
  1157. begin
  1158. AssertEquals(flist[i],FieldByName('F'+FieldTypeNames[AfieldType]).AsString);
  1159. inc(i);
  1160. Next;
  1161. end;
  1162. while not bof do
  1163. begin
  1164. dec(i);
  1165. AssertEquals(flist[i],FieldByName('F'+FieldTypeNames[AfieldType]).AsString);
  1166. Prior;
  1167. end;
  1168. AssertEquals('F'+FieldTypeNames[AfieldType],IndexFieldNames);
  1169. IndexFieldNames:='ID';
  1170. first;
  1171. i:=0;
  1172. while not eof do
  1173. begin
  1174. AssertEquals(testStringValues[i],FieldByName('F'+FieldTypeNames[AfieldType]).AsString);
  1175. inc(i);
  1176. Next;
  1177. end;
  1178. AssertEquals('ID',IndexFieldNames);
  1179. IndexFieldNames:='';
  1180. first;
  1181. i:=0;
  1182. while not eof do
  1183. begin
  1184. AssertEquals(testStringValues[i],FieldByName('F'+FieldTypeNames[AfieldType]).AsString);
  1185. inc(i);
  1186. Next;
  1187. end;
  1188. AssertEquals('',IndexFieldNames);
  1189. end;
  1190. end;
  1191. procedure TTestDBBasics.TestIndexCurRecord;
  1192. // Test if the currentrecord stays the same after an index change
  1193. var ds : TBufDataset;
  1194. AFieldType : TFieldType;
  1195. i : integer;
  1196. OldID : Integer;
  1197. OldStringValue : string;
  1198. begin
  1199. ds := DBConnector.GetFieldDataset as TBufDataset;
  1200. with ds do
  1201. begin
  1202. AFieldType:=ftString;
  1203. AddIndex('testindex','F'+FieldTypeNames[AfieldType],[]);
  1204. open;
  1205. for i := 0 to (testValuesCount div 3) do
  1206. Next;
  1207. OldID:=FieldByName('id').AsInteger;
  1208. OldStringValue:=FieldByName('F'+FieldTypeNames[AfieldType]).AsString;
  1209. IndexName:='testindex';
  1210. AssertEquals(OldID,FieldByName('id').AsInteger);
  1211. AssertEquals(OldStringValue,FieldByName('F'+FieldTypeNames[AfieldType]).AsString);
  1212. next;
  1213. AssertTrue(OldStringValue<=FieldByName('F'+FieldTypeNames[AfieldType]).AsString);
  1214. prior;
  1215. prior;
  1216. AssertTrue(OldStringValue>=FieldByName('F'+FieldTypeNames[AfieldType]).AsString);
  1217. OldID:=FieldByName('id').AsInteger;
  1218. OldStringValue:=FieldByName('F'+FieldTypeNames[AfieldType]).AsString;
  1219. IndexName:='';
  1220. AssertEquals(OldID,FieldByName('id').AsInteger);
  1221. AssertEquals(OldStringValue,FieldByName('F'+FieldTypeNames[AfieldType]).AsString);
  1222. next;
  1223. AssertEquals(OldID+1,FieldByName('ID').AsInteger);
  1224. prior;
  1225. prior;
  1226. AssertEquals(OldID-1,FieldByName('ID').AsInteger);
  1227. end;
  1228. end;
  1229. procedure TTestDBBasics.TestAddDblIndex;
  1230. var ds : TBufDataset;
  1231. LastInteger : Integer;
  1232. LastString : string;
  1233. begin
  1234. ds := DBConnector.GetFieldDataset as TBufDataset;
  1235. with ds do
  1236. begin
  1237. AddIndex('testindex','F'+FieldTypeNames[ftString]+';F'+FieldTypeNames[ftInteger],[]);
  1238. open;
  1239. IndexName:='testindex';
  1240. first;
  1241. LastString:='';
  1242. while not eof do
  1243. begin
  1244. AssertTrue(AnsiCompareStr(FieldByName('F'+FieldTypeNames[ftString]).AsString,LastString)>=0);
  1245. LastString:= FieldByName('F'+FieldTypeNames[ftString]).AsString;
  1246. LastInteger:=-MaxInt;
  1247. while (FieldByName('F'+FieldTypeNames[ftString]).AsString=LastString) and not eof do
  1248. begin
  1249. AssertTrue(FieldByName('F'+FieldTypeNames[ftInteger]).AsInteger>=LastInteger);
  1250. LastInteger:=FieldByName('F'+FieldTypeNames[ftInteger]).AsInteger;
  1251. next;
  1252. end;
  1253. end;
  1254. while not bof do
  1255. begin
  1256. AssertTrue(AnsiCompareStr(FieldByName('F'+FieldTypeNames[ftString]).AsString,LastString)<=0);
  1257. LastString:= FieldByName('F'+FieldTypeNames[ftString]).AsString;
  1258. LastInteger:=+MaxInt;
  1259. while (FieldByName('F'+FieldTypeNames[ftString]).AsString=LastString) and not bof do
  1260. begin
  1261. AssertTrue(FieldByName('F'+FieldTypeNames[ftInteger]).AsInteger<=LastInteger);
  1262. LastInteger:=FieldByName('F'+FieldTypeNames[ftInteger]).AsInteger;
  1263. prior;
  1264. end;
  1265. end;
  1266. end;
  1267. end;
  1268. procedure TTestDBBasics.TestIndexEditRecord;
  1269. var ds : TBufDataset;
  1270. AFieldType : TFieldType;
  1271. i : integer;
  1272. OldID : Integer;
  1273. OldStringValue : string;
  1274. begin
  1275. ds := DBConnector.GetFieldDataset as TBufDataset;
  1276. with ds do
  1277. begin
  1278. AFieldType:=ftString;
  1279. AddIndex('testindex','F'+FieldTypeNames[AfieldType],[]);
  1280. IndexName:='testindex';
  1281. open;
  1282. OldStringValue:=FieldByName('F'+FieldTypeNames[AfieldType]).AsString;
  1283. next;
  1284. AssertTrue(OldStringValue<=FieldByName('F'+FieldTypeNames[AfieldType]).AsString);
  1285. OldStringValue:=FieldByName('F'+FieldTypeNames[AfieldType]).AsString;
  1286. next;
  1287. AssertTrue(AnsiCompareStr(OldStringValue,FieldByName('F'+FieldTypeNames[AfieldType]).AsString)<=0);
  1288. prior;
  1289. edit;
  1290. FieldByName('F'+FieldTypeNames[AfieldType]).AsString := 'ZZZ';
  1291. post;
  1292. prior;
  1293. AssertTrue(AnsiCompareStr('ZZZ',FieldByName('F'+FieldTypeNames[AfieldType]).AsString)>=0);
  1294. next;
  1295. next;
  1296. AssertTrue(AnsiCompareStr('ZZZ',FieldByName('F'+FieldTypeNames[AfieldType]).AsString)<=0);
  1297. close;
  1298. end;
  1299. end;
  1300. procedure TTestDBBasics.TestIndexFieldNames;
  1301. var ds : TBufDataset;
  1302. AFieldType : TFieldType;
  1303. PrevValue : String;
  1304. begin
  1305. ds := DBConnector.GetFieldDataset as TBufDataset;
  1306. with ds do
  1307. begin
  1308. AFieldType:=ftString;
  1309. IndexFieldNames:='F'+FieldTypeNames[AfieldType];
  1310. open;
  1311. PrevValue:='';
  1312. while not eof do
  1313. begin
  1314. AssertTrue(AnsiCompareStr(FieldByName('F'+FieldTypeNames[AfieldType]).AsString,PrevValue)>=0);
  1315. PrevValue:=FieldByName('F'+FieldTypeNames[AfieldType]).AsString;
  1316. Next;
  1317. end;
  1318. AssertEquals('F'+FieldTypeNames[AfieldType],IndexFieldNames);
  1319. end;
  1320. end;
  1321. procedure TTestDBBasics.TestcalculatedField_OnCalcfields(DataSet: TDataSet);
  1322. begin
  1323. case dataset.fieldbyname('ID').asinteger of
  1324. 1 : dataset.fieldbyname('CALCFLD').AsInteger := 5;
  1325. 2 : dataset.fieldbyname('CALCFLD').AsInteger := 70000;
  1326. 3 : dataset.fieldbyname('CALCFLD').Clear;
  1327. 4 : dataset.fieldbyname('CALCFLD').AsInteger := 1234;
  1328. 10 : dataset.fieldbyname('CALCFLD').Clear;
  1329. else
  1330. dataset.fieldbyname('CALCFLD').AsInteger := 1;
  1331. end;
  1332. end;
  1333. procedure TTestDBBasics.TestCalculatedField;
  1334. var ds : TDataset;
  1335. AFld1, AFld2, AFld3 : Tfield;
  1336. begin
  1337. ds := DBConnector.GetNDataset(5);
  1338. with ds do
  1339. begin
  1340. AFld1 := TIntegerField.Create(ds);
  1341. AFld1.FieldName := 'ID';
  1342. AFld1.DataSet := ds;
  1343. AFld2 := TStringField.Create(ds);
  1344. AFld2.FieldName := 'NAME';
  1345. AFld2.DataSet := ds;
  1346. AFld3 := TIntegerField.Create(ds);
  1347. AFld3.FieldName := 'CALCFLD';
  1348. AFld3.DataSet := ds;
  1349. Afld3.FieldKind := fkCalculated;
  1350. AssertEquals(3,FieldCount);
  1351. ds.OnCalcFields := TestcalculatedField_OnCalcfields;
  1352. open;
  1353. AssertEquals(1,FieldByName('ID').asinteger);
  1354. AssertEquals(5,FieldByName('CALCFLD').asinteger);
  1355. next;
  1356. AssertEquals(70000,FieldByName('CALCFLD').asinteger);
  1357. next;
  1358. AssertEquals(true,FieldByName('CALCFLD').isnull);
  1359. next;
  1360. AssertEquals(1234,FieldByName('CALCFLD').AsInteger);
  1361. edit;
  1362. FieldByName('ID').AsInteger := 10;
  1363. post;
  1364. AssertEquals(true,FieldByName('CALCFLD').isnull);
  1365. close;
  1366. AFld1.Free;
  1367. AFld2.Free;
  1368. AFld3.Free;
  1369. end;
  1370. end;
  1371. procedure TTestDBBasics.TestEofAfterFirst;
  1372. begin
  1373. with DBConnector.GetNDataset(0) do
  1374. begin
  1375. open;
  1376. AssertTrue(eof);
  1377. AssertTrue(BOF);
  1378. first;
  1379. AssertTrue(eof);
  1380. AssertTrue(BOF);
  1381. end;
  1382. end;
  1383. procedure TTestDBBasics.TestfieldDefinition(AFieldType : TFieldType;ADatasize : integer;var ADS : TDataset; var AFld: TField);
  1384. var i : byte;
  1385. begin
  1386. ADS := DBConnector.GetFieldDataset;
  1387. ADS.Open;
  1388. AFld := ADS.FindField('F'+FieldTypeNames[AfieldType]);
  1389. if not assigned (AFld) then
  1390. Ignore('Fields of the type ' + FieldTypeNames[AfieldType] + ' are not supported by this type of dataset');
  1391. AssertTrue(Afld.DataType = AFieldType);
  1392. AssertEquals(ADatasize,Afld.DataSize );
  1393. end;
  1394. procedure TTestDBBasics.TestSupportIntegerFields;
  1395. var i : byte;
  1396. ds : TDataset;
  1397. Fld : TField;
  1398. begin
  1399. TestfieldDefinition(ftInteger,4,ds,Fld);
  1400. for i := 0 to testValuesCount-1 do
  1401. begin
  1402. AssertEquals(testIntValues[i],Fld.AsInteger);
  1403. ds.Next;
  1404. end;
  1405. ds.close;
  1406. end;
  1407. procedure TTestDBBasics.TestSupportSmallIntFields;
  1408. var i : byte;
  1409. ds : TDataset;
  1410. Fld : TField;
  1411. begin
  1412. TestfieldDefinition(ftSmallint,2,ds,Fld);
  1413. for i := 0 to testValuesCount-1 do
  1414. begin
  1415. AssertEquals(testSmallIntValues[i],Fld.AsInteger);
  1416. ds.Next;
  1417. end;
  1418. ds.close;
  1419. end;
  1420. procedure TTestDBBasics.TestSupportStringFields;
  1421. var i : byte;
  1422. ds : TDataset;
  1423. Fld : TField;
  1424. begin
  1425. TestfieldDefinition(ftString,11,ds,Fld);
  1426. for i := 0 to testValuesCount-1 do
  1427. begin
  1428. AssertEquals(testStringValues[i],Fld.AsString);
  1429. ds.Next;
  1430. end;
  1431. ds.close;
  1432. end;
  1433. procedure TTestDBBasics.TestSupportBooleanFields;
  1434. var i : byte;
  1435. ds : TDataset;
  1436. Fld : TField;
  1437. begin
  1438. TestfieldDefinition(ftBoolean,2,ds,Fld);
  1439. for i := 0 to testValuesCount-1 do
  1440. begin
  1441. AssertEquals(testBooleanValues[i],Fld.AsBoolean);
  1442. ds.Next;
  1443. end;
  1444. ds.close;
  1445. end;
  1446. procedure TTestDBBasics.TestSupportFloatFields;
  1447. var i : byte;
  1448. ds : TDataset;
  1449. Fld : TField;
  1450. begin
  1451. TestfieldDefinition(ftFloat,8,ds,Fld);
  1452. for i := 0 to testValuesCount-1 do
  1453. begin
  1454. AssertEquals(testFloatValues[i],Fld.AsFloat);
  1455. ds.Next;
  1456. end;
  1457. ds.close;
  1458. end;
  1459. procedure TTestDBBasics.TestSupportLargeIntFields;
  1460. var i : byte;
  1461. ds : TDataset;
  1462. Fld : TField;
  1463. begin
  1464. TestfieldDefinition(ftLargeint,8,ds,Fld);
  1465. for i := 0 to testValuesCount-1 do
  1466. begin
  1467. AssertEquals(testLargeIntValues[i],Fld.AsLargeInt);
  1468. ds.Next;
  1469. end;
  1470. ds.close;
  1471. end;
  1472. procedure TTestDBBasics.TestSupportDateFields;
  1473. var i : byte;
  1474. ds : TDataset;
  1475. Fld : TField;
  1476. begin
  1477. TestfieldDefinition(ftDate,8,ds,Fld);
  1478. for i := 0 to testValuesCount-1 do
  1479. begin
  1480. AssertEquals(testDateValues[i],FormatDateTime('yyyy/mm/dd',Fld.AsDateTime));
  1481. ds.Next;
  1482. end;
  1483. ds.close;
  1484. end;
  1485. procedure TTestDBBasics.TestSupportCurrencyFields;
  1486. var i : byte;
  1487. ds : TDataset;
  1488. Fld : TField;
  1489. begin
  1490. TestfieldDefinition(ftCurrency,8,ds,Fld);
  1491. for i := 0 to testValuesCount-1 do
  1492. begin
  1493. AssertEquals(testCurrencyValues[i],Fld.AsCurrency);
  1494. AssertEquals(testCurrencyValues[i],Fld.AsFloat);
  1495. ds.Next;
  1496. end;
  1497. ds.close;
  1498. end;
  1499. procedure TTestDBBasics.TestSupportBCDFields;
  1500. var i : byte;
  1501. ds : TDataset;
  1502. Fld : TField;
  1503. begin
  1504. TestfieldDefinition(ftBCD,8,ds,Fld);
  1505. for i := 0 to testValuesCount-1 do
  1506. begin
  1507. AssertEquals(testCurrencyValues[i],Fld.AsCurrency);
  1508. AssertEquals(testCurrencyValues[i],Fld.AsFloat);
  1509. ds.Next;
  1510. end;
  1511. ds.close;
  1512. end;
  1513. procedure TTestDBBasics.TestDoubleClose;
  1514. begin
  1515. with DBConnector.GetNDataset(1) do
  1516. begin
  1517. close;
  1518. close;
  1519. open;
  1520. close;
  1521. close;
  1522. end;
  1523. end;
  1524. procedure TTestDBBasics.TestAssignFieldftString;
  1525. var AParam : TParam;
  1526. AField : TField;
  1527. begin
  1528. AParam := TParam.Create(nil);
  1529. with DBConnector.GetNDataset(1) do
  1530. begin
  1531. open;
  1532. AField := fieldbyname('name');
  1533. AParam.AssignField(AField);
  1534. AssertTrue(ftString=AParam.DataType);
  1535. close;
  1536. end;
  1537. AParam.Free;
  1538. end;
  1539. procedure TTestDBBasics.TestAssignFieldftFixedChar;
  1540. var AParam : TParam;
  1541. AField : TField;
  1542. begin
  1543. AParam := TParam.Create(nil);
  1544. with DBConnector.GetNDataset(1) do
  1545. begin
  1546. open;
  1547. AField := fieldbyname('name');
  1548. (AField as tstringfield).FixedChar := true;
  1549. AParam.AssignField(AField);
  1550. AssertTrue(ftFixedChar=AParam.DataType);
  1551. close;
  1552. end;
  1553. AParam.Free;
  1554. end;
  1555. procedure TTestDBBasics.TestBufDatasetCancelUpd;
  1556. var i : byte;
  1557. begin
  1558. if not (DBConnector.GetNDataset(5) is TBufDataset) then
  1559. Ignore('This test only applies to TBufDataset and descendents.');
  1560. with DBConnector.GetNDataset(5) as TBufDataset do
  1561. begin
  1562. open;
  1563. next;
  1564. next;
  1565. edit;
  1566. FieldByName('name').AsString := 'changed';
  1567. post;
  1568. next;
  1569. delete;
  1570. CancelUpdates;
  1571. First;
  1572. for i := 1 to 5 do
  1573. begin
  1574. AssertEquals(i,fields[0].AsInteger);
  1575. AssertEquals('TestName'+inttostr(i),fields[1].AsString);
  1576. Next;
  1577. end;
  1578. end;
  1579. end;
  1580. procedure TTestDBBasics.Testbug7007;
  1581. var
  1582. datalink1: tdatalink;
  1583. datasource1: tdatasource;
  1584. query1: TDataSet;
  1585. begin
  1586. query1:= DBConnector.GetNDataset(6);
  1587. datalink1:= TTestDataLink.create;
  1588. datasource1:= tdatasource.create(nil);
  1589. try
  1590. datalink1.datasource:= datasource1;
  1591. datasource1.dataset:= query1;
  1592. datalink1.datasource:= datasource1;
  1593. DataEvents := '';
  1594. query1.open;
  1595. datalink1.buffercount:= query1.recordcount;
  1596. AssertEquals('deUpdateState:0;',DataEvents);
  1597. AssertEquals(0, datalink1.ActiveRecord);
  1598. AssertEquals(6, datalink1.RecordCount);
  1599. AssertEquals(6, query1.RecordCount);
  1600. AssertEquals(1, query1.RecNo);
  1601. DataEvents := '';
  1602. query1.append;
  1603. AssertEquals('deCheckBrowseMode:0;deUpdateState:0;deDataSetChange:0;',DataEvents);
  1604. AssertEquals(5, datalink1.ActiveRecord);
  1605. AssertEquals(6, datalink1.RecordCount);
  1606. AssertEquals(6, query1.RecordCount);
  1607. AssertTrue(query1.RecNo in [0,7]);
  1608. DataEvents := '';
  1609. query1.cancel;
  1610. AssertEquals('deCheckBrowseMode:0;deUpdateState:0;deDataSetChange:0;',DataEvents);
  1611. AssertEquals(5, datalink1.ActiveRecord);
  1612. AssertEquals(6, datalink1.RecordCount);
  1613. AssertEquals(6, query1.RecordCount);
  1614. AssertEquals(6, query1.RecNo);
  1615. finally
  1616. datalink1.free;
  1617. datasource1.free;
  1618. end;
  1619. end;
  1620. procedure TTestDBBasics.TestBug6893;
  1621. var
  1622. datalink1: tdatalink;
  1623. datasource1: tdatasource;
  1624. query1: TDataSet;
  1625. begin
  1626. query1:= DBConnector.GetNDataset(25);
  1627. datalink1:= TDataLink.create;
  1628. datasource1:= tdatasource.create(nil);
  1629. try
  1630. datalink1.datasource:= datasource1;
  1631. datasource1.dataset:= query1;
  1632. datalink1.buffercount:= 5;
  1633. query1.active := true;
  1634. query1.MoveBy(20);
  1635. AssertEquals(5, THackDataLink(datalink1).Firstrecord);
  1636. AssertEquals(4, datalink1.ActiveRecord);
  1637. AssertEquals(21, query1.RecNo);
  1638. query1.active := False;
  1639. AssertEquals(0, THackDataLink(datalink1).Firstrecord);
  1640. AssertEquals(0, datalink1.ActiveRecord);
  1641. query1.active := true;
  1642. AssertEquals(0, THackDataLink(datalink1).Firstrecord);
  1643. AssertEquals(0, datalink1.ActiveRecord);
  1644. AssertEquals(1, query1.RecNo);
  1645. finally
  1646. datalink1.free;
  1647. datasource1.free;
  1648. end;
  1649. end;
  1650. procedure TTestDBBasics.TestBufDatasetCancelUpd1;
  1651. var i : byte;
  1652. begin
  1653. if not (DBConnector.GetNDataset(5) is TBufDataset) then
  1654. Ignore('This test only applies to TBufDataset and descendents.');
  1655. with DBConnector.GetNDataset(5) as TBufDataset do
  1656. begin
  1657. open;
  1658. next;
  1659. next;
  1660. delete;
  1661. insert;
  1662. FieldByName('id').AsInteger := 100;
  1663. post;
  1664. CancelUpdates;
  1665. last;
  1666. for i := 5 downto 1 do
  1667. begin
  1668. AssertEquals(i,fields[0].AsInteger);
  1669. AssertEquals('TestName'+inttostr(i),fields[1].AsString);
  1670. Prior;
  1671. end;
  1672. end;
  1673. end;
  1674. procedure TTestDBBasics.TestNullAtOpen;
  1675. begin
  1676. with dbconnector.getndataset(0) do
  1677. begin
  1678. active:= true;
  1679. AssertTrue('Field isn''t NULL on a just-opened empty dataset',fieldbyname('id').IsNull);
  1680. append;
  1681. AssertTrue('Field isn''t NULL after append on an empty dataset',fieldbyname('id').IsNull);
  1682. fieldbyname('id').asinteger:= 123;
  1683. cancel;
  1684. AssertTrue('Field isn''t NULL after cancel',fieldbyname('id').IsNull);
  1685. end;
  1686. end;
  1687. { TSQLTestSetup }
  1688. procedure TDBBasicsTestSetup.OneTimeSetup;
  1689. begin
  1690. InitialiseDBConnector;
  1691. end;
  1692. procedure TDBBasicsTestSetup.OneTimeTearDown;
  1693. begin
  1694. FreeAndNil(DBConnector);
  1695. end;
  1696. initialization
  1697. RegisterTestDecorator(TDBBasicsTestSetup, TTestDBBasics);
  1698. end.