Making sure I add unique records to text file

[karl Jonson 0]

Hi,
I am reading a database & adding to a file the following info:
   

user_name, surname, date of birth, phone number

One customer can have multiple records in db.
Which is the best way to keep track of data I am adding to text file & make sure the data I am copying in it is unique

Loop through records in db
  if combination user_name, surname, date of birth, phone number doesn't exist in text file then
    add
  else
    read next record

TIA
 

[Stano 143]

Have the right DataSet. Use Query and there you go

• Distinct
• Where
• Order By - Check it "manually"

Edited December 16, 2022 by Stano

[PeterBelow 238]

8 hours ago, karl Jonson said:

Hi,
I am reading a database & adding to a file the following info:
   

user_name, surname, date of birth, phone number

One customer can have multiple records in db.
Which is the best way to keep track of data I am adding to text file & make sure the data I am copying in it is unique

Loop through records in db
  if combination user_name, surname, date of birth, phone number doesn't exist in text file then
    add
  else
    read next record

TIA
 

The best way is to not have duplicate records in your query result in the first place. If you are using a SQL-supporting database just do not access the table directly (via TDBTable or analog), instead use a query component and give it a SELECT DISTINCT <columnlist> FROM <tablename> statement, optionally with more restrictions on the records to return (WHERE clause) and ordering. This way the result set will not contain duplicates in the first place.

 

If the database you use does not support this kind of statement your best bet is to keep track of what you have already written to the file in an in-memory structure that supports quick lookup. You could concatenate all field values of a record into a string (basically the line you write to the text file) and store them into a TDictionary<string,byte>, using the byte part to keep a count of the duplicates you find. Depending on the number of records you have to process this may use too much memory, though. You could cut down on that by calculating a hash value from the string and storing the hash instead of the string. That is not completely failsafe, though, since different strings can result in the same hash value. You can deal with that by writing any such duplicate detected to separate file, which will end up with only few such records. When done with the task load that file into a stringlist, sort the list, and then do a final run over the record file and compare the lines read with the content of the stringlist, marking each match found there. If you end up with any unmarked items in the stringlist those are false duplicates and need to be added to the record file.

 

[aehimself 396]

This level of nesting is just awful. Please do not use it, instead refactor your DB structure or modify your query to return the data exactly as you expect it.

I'm leaving this here only for you to see what spaghetti you need because of bad DB design.

Type
  TDateOfBirthPhoneNumber = TDictionary<TDateTime, String>;
  TSurnameAndRest = TObjectDictionary<String, TDateOfBirthPhoneNumber>;
  TUsernameAndRest = TObjectDictionary<String, TSurnameAndRest>;

  TUserCache = Class
  strict private
    _cache: TUsernameAndRest;
  public
    Constructor Create; ReIntroduce;
    Destructor Destroy; Override;
    Procedure AddRecord(Const inUserName, inSurName: String; Const inDateOfBirth: TDateTime; Const inPhoneNumber: String);
    Function ContainsRecord(Const inUserName, inSurName: String; Const inDateOfBirth: TDateTime; Const inPhoneNumber: String): Boolean;
  End;

implementation

Procedure TUserCache.AddRecord(Const inUserName, inSurName: String; Const inDateOfBirth: TDateTime; Const inPhoneNumber: String);
Begin
  If Not _cache.ContainsKey(inUserName) Then _cache.Add(inUserName, TSurnameAndRest.Create([doOwnsValues]));
  If Not _cache[inUserName].ContainsKey(inSurname) Then _cache[inUserName].Add(inSurname, TDateOfBirthPhoneNumber.Create);

  _cache[inUserName][inSurname].AddOrSetValue(inDateOfBirth, inPhoneNumber);
End;

Function TUserCache.ContainsRecord(Const inUserName, inSurName: String; Const inDateOfBirth: TDateTime; Const inPhoneNumber: String): Boolean;
Begin
  Result := _cache.ContainsKey(inUserName) And _cache[inUserName].ContainsKey(inSurname) And
            _cache[inUserName][inSurName].ContainsKey(inDateOfBirth) And
            (_cache[inUserName][inSurName][inDateOfBirth] = inPhoneNumber);
End;

Constructor TUserCache.Create;
Begin
  inherited;

  _cache := TUsernameAndRest.Create([doOwnsValues]);
End;

Destructor TUserCache.Destroy;
Begin
  FreeAndNil(_cache);

  inherited;
End;

For your information, I did not try this. High possibility of AVs!

[programmerdelphi2k 237]

it's not necessary so much code!

• 1 FDMemTable = i'm using to represent your dataset with full data... in your system!
• 1 FDBatchMove
  • 1 FDBatchMoveTextWriter
  • 1 FDBatchMoveDataSetReader1
• 1 FDLocalSQL1
• 1 FDQuery1 = "select DISTINCT name from FDMemTable1 order by Name "
• 1 FDConnection1 = SQLite to LocalSQL usage!
  • 1 FDGUIxWaitCursor1

 

just this code if not using on desig-time!

implementation

{$R *.dfm}

procedure TForm1.BtnExportClick(Sender: TObject);
begin
  FDBatchMove1.Execute
end;

procedure TForm1.BtnOpenDataClick(Sender: TObject);
begin
  FDConnection1.Open;
  FDLocalSQL1.Active := true;
  FDMemTable1.Open;
  FDQuery1.Open;
end;

 

Edited December 16, 2022 by programmerdelphi2k