Mixing Keyed and Relative File Access

General development discussion.

Moderators: Susan Smith, admin, Gabriel

Post Reply
mikemiller
Posts: 7
Joined: Wed Mar 17, 2021 6:01 am

Mixing Keyed and Relative File Access

Post by mikemiller »

Since keyed file access seems like the handiest of the options, most of my reads use this method (utilizing fileio).
It makes it really easy to implement a read loop to where only the needed records are read and nothing more which obviously benefits performance.

Another method I've been leaning towards is to use record numbers as the final source of truth. So whenever I want to modify one or more records, I do so by record number since by definition, those will always be unique and never change out from underneath my feet.

My question is this:
Is there any scenario where modifying a file with

Code: Select all

rec=RecordNumber
will not properly update the associated keys? (Given that all keys are opened for outin during the update)
This includes

Code: Select all

delete #FileHandle, rec=RecordNumber:
Or is it always best practice to have a proper Primary Key associated with the file and operate off of that?
gordon
Posts: 350
Joined: Fri Apr 24, 2009 6:02 pm

Re: Mixing Keyed and Relative File Access

Post by gordon »

I seem to recall that some years ago we added the updating of keys when REC= was used to update a file properly opened with keys. However, it would be very useful to write a test program for checking this issue, including secondary keys.

Any volunteers?

As for efficiency, the address out sort is very fast for custom record sets that don't match keys.
GomezL
Posts: 244
Joined: Wed Apr 29, 2009 5:51 am
Contact:

Re: Mixing Keyed and Relative File Access

Post by GomezL »

Code: Select all

00005   DIM Key$*8,Data$*100
00010   OPEN #1: "Name=[Temp]Datafile.int,kfname=[temp]datafile.idx,Replace,RECL=128,KPS=1,KLN=8",INTERNAL,OUTIN,KEYED 
00015 ! Write Blank Records & Keys
00020   FOR _Write_Loop=1 TO 10
00030     WRITE #1,USING 40: "",""
00040     FORM Pos 1,C 8,C 80
00050   NEXT _Write_Loop
00095 ! LOOP & REWRITE - REC=
00100   FOR _Rewrite_Loop=1 TO 10
00110     REWRITE #1,USING 40,REC=_Rewrite_Loop: Cnvrt$('n 3',11-_Rewrite_Loop),"Record #:"&Str$(11-_Rewrite_Loop)
00120   NEXT _Rewrite_Loop
00200   RESTORE #1: 
00210   FOR _Read_Loop=1 TO 10
00220     READ #1,USING 40,KEY=Rpad$(Cnvrt$('n 3',11-_Read_Loop),8): Key$,Data$
00230     PRINT Rec(1);" ";Key$;" ";Data$
00240   NEXT _Read_Loop
I think this proves your theory!
  • First Loop writes blanks to the file
    The Second loop rewrites rec= using 11- record # as the key.
    Final Loop reads "Key=11- record #" as the key.
It looks like it updates the index, even though rewrite rec=
Gabriel
Posts: 407
Joined: Sun Aug 10, 2008 7:37 am
Location: Arlington, TX
Contact:

Re: Mixing Keyed and Relative File Access

Post by Gabriel »

If you're reading a lot of records, like to build a listview, its faster to open the file Relative and read it Sequentially. Thats why ScreenIO and FileIO both sometimes don't use the keys.

The other consideration, the reason that I use a unique key field in my software, is because the record numbers change if you ever do "copy -d" to shrink the file by getting rid of deleted records.

ScreenIO Supports using record numbers or keys. You don't have to use a unique key. You can call FNFM and pass in a record number instead of a key$.
gordon
Posts: 350
Joined: Fri Apr 24, 2009 6:02 pm

Re: Mixing Keyed and Relative File Access

Post by gordon »

Thanks Luis !!

Here is a revised version of Luis' program that shows that multiple keys will be updated as well.
01000 dim KEY$*8,DATA$*100
01010 open #1: "Name=[Temp]Datafile.int,kfname=[temp]datafile.idx,Replace,RECL=128,KPS=1,KLN=8",internal,outin,keyed
01020 open #2: "Name=[Temp]Datafile.int,kfname=[temp]datafile.idx2,Replace,RECL=128,KPS=17,KLN=8",internal,outin,keyed
01030 ! Write Blank Records & Keys
01040 for _WRITE_LOOP=1 to 10
01050 write #1,using 1060: "","","",""
01060 form 3*C 8,C 80
01070 next _WRITE_LOOP
01080 ! LOOP & REWRITE - REC=
01090 for _REWRITE_LOOP=1 to 10
01100 rewrite #1,using 1060,rec=_REWRITE_LOOP: CNVRT$('n 3',11-_REWRITE_LOOP),"",CNVRT$('n 3',16-_REWRITE_LOOP),"Record #:"&STR$(_REWRITE_LOOP)
01110 next _REWRITE_LOOP
01120 !
01130 ! read in ascending record order - via key 1
01140 restore #1:
01150 for _READ_LOOP=1 to 10 !read in ascending order
01160 read #1,using 1060,key=RPAD$(CNVRT$('n 3',11-_READ_LOOP),8): KEY$,X$,KEY2$,DATA$
01170 print REC(1);" ";KEY$;" ";KEY2$;" ";DATA$
01180 next _READ_LOOP
01190 !
01200 linput Z$
01210 print
01220 ! read in descending record order - via key 2
01230 restore #2:
01240 for _READ_LOOP=1 to 10
01250 read #2,using 1060: KEY$,X$,KEY2$,DATA$
01260 print REC(2);" ";KEY$;" ";KEY2$;" ";DATA$
01270 next _READ_LOOP
mikemiller
Posts: 7
Joined: Wed Mar 17, 2021 6:01 am

Re: Mixing Keyed and Relative File Access

Post by mikemiller »

Thanks a lot, everyone!

I'm sure I tested it in the past. I also believe I have unit tests somewhere that test this specific case.
I just had an issue pop up that I couldn't logically draw a conclusion from.

I have a file that has 2 keys, none of them unique.
The only way it can delete entries is something like this:

Code: Select all

def library FnRemoveQueuedIncreases(RawObject$*64; ___, OldRec, QueueFile)
    let FnEstablishLinkage
    let QueueFile = FnOpen(`queue`, mat Queue$, mat Queue, mat Forms$)
    do until ~(OldRec:=FnNextQueuedIncrease(RawObject$))
        delete #QueueFile, rec=OldRec, release : 
    loop
    let FnCloseFile(QueueFile, `queue`)
fnend


! returns the record number of the next queued increase for the current raw material
def library FnNextQueuedIncrease(Self$*64; ___, QueueFile, Key$*64)
    let FnEstablishLinkage
    let QueueFile = FnOpen(`queue`, mat Queue$, mat Queue, mat Forms$, 1, 2)
    let Key$ = `M{{rpad$(FnGetAttr$(Self$, 'Raw Code'), 32)}}0`
    read #QueueFile, using Forms$(QueueFile), key=Key$, release : mat Queue$, mat Queue nokey ignore
    if ~file(QueueFile) then
        let FnNextQueuedIncrease = rec(QueueFile)
    else
        let FnNextQueuedIncrease = 0
    end if
    close #QueueFile, release :
fnend
This all works fine.
And although this may look a little complex on the surface, all it's really doing is finding the next record number that matches a key(that may contain duplicates) and deleting that record number.

When I view it in the fileio datacrawler or just spit out record numbers manually, it all looks fine.
But when I start modifying the keys in this step:

Code: Select all

def library FnFinalizeDueRawIncreases(; ___, Now, rc, QueueFile)
    let FnEstablishLinkage
    let Now = days(date$)
    mat UpdatedEntries(0)
    
    let QueueFile = FnOpen(`queue`, mat Queue$, mat Queue, mat Forms$)
    restore #QueueFile, search=>`M0` : nokey ignore
    do until file(QueueFile)
        read #QueueFile, using Forms$(QueueFile) : mat Queue$, mat Queue eof ignore
        if ~file(QueueFile) then
            if Queue$(qu_type) == `M` and ~Queue(qu_posted) and Queue(qu_date) <= Now then
                ! an unrelated function call where the raw price is updated
            
                mat UpdatedEntries(udim(UpdatedEntries) + 1)
                let UpdatedEntries(udim(UpdatedEntries)) = rec(QueueFile)
            end if
        end if
    loop while Queue$(qu_type) == `M`
    
    for rc = 1 to udim(UpdatedEntries)
        read #QueueFile, using Forms$(QueueFile), rec=UpdatedEntries(rc) : mat Queue$, mat Queue
        let Queue(qu_posted) = 1
        rewrite #QueueFile, using Forms$(QueueFile) : mat Queue$, mat Queue
    next rc
    let FnCloseFile(QueueFile, `queue`)
fnend
Duplicate record numbers start popping up.
This is obviously a case of an invalid key file since duplicate record numbers just don't exist.

All that's getting modified on the file is qu_posted which is part of both keys.
Reindexing removes the duplicate entries so it's definitely a key issue.

I just thought I'd throw it out there in case I missed something obvious.
GomezL
Posts: 244
Joined: Wed Apr 29, 2009 5:51 am
Contact:

Re: Mixing Keyed and Relative File Access

Post by GomezL »

I would try the program Gordon uploaded to see if it works in your specific BR Version.

Here is a function we use to use "Keys" instead of "Delete Rec="

The program figures out the proper index based on the file handle passed, and then uses the key to read comparing record #s.



Please keep in mind:
  • It uses other functions, so it won't just run as is.
  • There are a few "Helper Functions" I included
The concept is get the keys from the file handle, and then parse the key from the "Raw Data" using the key, it builds a key and then loops through matching records looking for the deleted record.

PS: Delete has worked with indexes for a long time, but it was "Messy" leaving the deleted record in the index. With the changes Gordon mentioned, it might be cleaner.
23660 DEF Fndelrec(Dr_File_Handle,Dr_Record_Number)
23665 ! DR_File_Handle - file must be opened as keyed !:
! if not then just delte rec= !:
! this function will return a 1 if successful or a -Error Number if not, this function never returns a 0
23670 DIM Dr_Record$*32767
23675 DIM Dr_Key_Original$*512 ! Key of the original record (the record to be deleted)
23680 DIM Dr_Key_Nomatch ! 0=No, 1=Yes, Could find the Record to be deleted, but the Keys are no longer matching
23685 GOSUB SETUP_MESSAGEBOX
23690 LET Form_Generic_C_Len=Min(Rln(Dr_File_Handle),32767) ! necessary for FORM_GENERIC_C
23695 LET Set_Fndelrec=1
23700 ! read,release rec= and get the key
23705 LET Dr_Key_Original$=Fnkeybuildby_Recno$(Dr_File_Handle,Dr_Record_Number)
23710 LET Dr_Key_Nomatch=Dr_Found=0
23715 RESTORE #Dr_File_Handle,KEY=Dr_Key_Original$,RELEASE: NOKEY DR_RESTORE_NOKEY IOERR DR_RESTORE_ERR
23720 DO
23725 READ #Dr_File_Handle,USING FORM_GENERIC_C,RELEASE: Dr_Record$
23730 IF Dr_Record_Number=Rec(Dr_File_Handle) THEN
23735 LET Dr_Found=1
23740 ELSE IF Dr_Key_Original$<>Fnkeybuildby_Buff$(Dr_File_Handle,Dr_Record$) THEN
23745 LET Dr_Key_Nomatch=1
23750 END IF
23755 LOOP Until Dr_Found Or Dr_Key_Nomatch
23760 GOTO DR_FINIS
23765 DR_RESTORE_NOKEY: !
23770 LET Dr_Key_Nomatch=1
23775 GOTO DR_FINIS
23780 DR_RESTORE_ERR: !
23785 IF Err=702 THEN LET Dr_Found=0 : LET Dr_Key_Nomatch=1 : GOTO DR_FINIS ELSE GOTO DR_ERR_GENERIC
23790 DR_ERR_DELETE: !
23795 IF Err=721 And Lwrc$(Env$("Developer"))='yes' THEN !:
LET Fnmessagebox_("An Error 721 occured because the calling program passed a file handle that points to a File which has not been opened OutIn.",Mb_Exclamation+Mb_Okonly,"Collection-Master Developer") !:
LET Set_Fndelrec=-Err : GOTO DR_XIT ELSE GOTO DR_ERR_GENERIC
23800 DR_ERR_GENERIC: !
23805 LET Set_Fndelrec=-Err ! LET FNMESSAGEBOX_("An Error "&STR$(ERR)&" on line "&STR$(LINE)&" during a fnDelRec("&STR$(DR_FILE_HANDLE)&",("&STR$(DR_RECORD_NUMBER)&").",MB_EXCLAMATION+MB_OKONLY,"Collection-Master") !:
GOTO DR_XIT
23810 DR_FINIS: !
23815 IF Dr_Found And Rec(Dr_File_Handle)=Dr_Record_Number THEN
23820 READ #Dr_File_Handle,Same:
23825 DELETE #Dr_File_Handle: IOERR DR_ERR_DELETE
23830 ELSE IF Dr_Key_Nomatch THEN
23835 DELETE #Dr_File_Handle,REC=Dr_Record_Number: IOERR DR_ERR_DELETE
23840 IF Lwrc$(Env$("Developer"))='yes' THEN LET Fn_Log("Dev-[Session].log","fnDel_Rec tried to delete from "&File$(Dr_File_Handle)&" Record Number "&Str$(Dr_Record_Number)&" and was forced to do it via ""Delete #X:Rec=""")
23845 END IF
23850 DR_XIT: !
23855 LET Fndelrec=Set_Fndelrec
23860 FNEND
59130 DEF Fnkeybuildby_Recno$*512(Kbbr_File_Number_1,Kbbr_Dr_Record_Number)
59135 DIM Kbbr_Record$*4096
59140 LET Form_Generic_C_Len=Min(Rln(Kbbr_File_Number_1),32767) ! necessary for FORM_GENERIC_C
59145 ! OPEN #KBBR_FILE_HANDLE_2:=FNGETHANDLE_: "Name="&FILE$(KBBR_FILE_NUMBER_1)&",Shr",INTERNAL,INPUT,RELATIVE
59150 READ #Kbbr_File_Number_1,USING FORM_GENERIC_C,REC=Kbbr_Dr_Record_Number,RELEASE: Kbbr_Record$ NOREC NOREC_FNKEYBUILDBY_RECNO
59155 LET Fnkeybuildby_Recno$=Fnkeybuildby_Buff$(Kbbr_File_Number_1,Kbbr_Record$)
59160 GOTO XIT_FNKEYBUILDBY_RECNO
59165 NOREC_FNKEYBUILDBY_RECNO: !
59170 LET Fnkeybuildby_Recno$=""
59175 GOTO XIT_FNKEYBUILDBY_RECNO
59180 XIT_FNKEYBUILDBY_RECNO: !
59185 ! CLOSE #KBBR_FILE_HANDLE_2:
59190 FNEND
59045 DEF Fnkeybuildby_Buff$*512(Kbbb_File_Number,&Kbbb_Record$)
59050 ! This function returns a Key bulit from KBBB_Record$, determined by KLn and KPs of KBBB_File_Number !:
! KBBB_File_Number - should be a file that is opened Keyed
59055 DIM Kbbb_Key$*256
59060 MAT Kbbb_Kps(6)=(0) : MAT Kbbb_Kln(6)=(0) : LET Kbbb_Key$="" : LET Key_Part_Count=0
59065 DO
59070 LET Key_Part_Count+=1
59075 LET Kbbb_Kps(Key_Part_Count)=Kps(Kbbb_File_Number,Key_Part_Count)
59080 LET Kbbb_Kln(Key_Part_Count)=Kln(Kbbb_File_Number,Key_Part_Count)
59085 LOOP Until Kps(Kbbb_File_Number,Key_Part_Count+1)=-1
59086 MAT Kbbb_Kps(Key_Part_Count) : MAT Kbbb_Kln(Key_Part_Count)
59090 FOR Kbbb_Key_Portion=1 TO Udim(Kbbb_Kps)
59095 LET Kbbb_Key$=Kbbb_Key$&Kbbb_Record$(Kbbb_Kps(Kbbb_Key_Portion):Kbbb_Kps(Kbbb_Key_Portion)+Kbbb_Kln(Kbbb_Key_Portion)-1)
59100 NEXT Kbbb_Key_Portion
59105 LET Fnkeybuildby_Buff$=Kbbb_Key$
59110 FNEND
gordon
Posts: 350
Joined: Fri Apr 24, 2009 6:02 pm

Re: Mixing Keyed and Relative File Access

Post by gordon »

When you are working intensively with keys and record numbers I would encourage you to consider using ISAM indexes. I don't know if you can mix index types, but it's worth checking. You could use the program I submitted above to check that.

ISAM indexes can be normalized very fast with Index Reorg. After that you can read the index file itself as an internal file. Very plain.

I also have attached a binary search function that works with a string key and can be used with or without a sort address out file. That means you could use sort to identify a record number subset and search within that subset pretty quickly. The preamble of the function describes the parameters and the values returned under various conditions. I just pulled this from one of my programs that has been in use for a couple of years.
Attachments
binary.txt
(3.07 KiB) Downloaded 814 times
mikemiller
Posts: 7
Joined: Wed Mar 17, 2021 6:01 am

Re: Mixing Keyed and Relative File Access

Post by mikemiller »

Yes, the program's above seem to work just fine.
It's probably something simple I'm missing.

I also customized it to fit more what I was doing(triple split key between c / zd and bh formats).
As well as a few 100 duplicate keys. Everything seems to function fine so I'm leaning more towards a bug elsewhere.
I more just wanted to make sure I didn't have a complete misunderstanding of the way keys are updated internally.

I do use binary search on a few specialized files but nothing as generalized as the function you provided. Thanks!
mikemiller
Posts: 7
Joined: Wed Mar 17, 2021 6:01 am

Re: Mixing Keyed and Relative File Access

Post by mikemiller »

So, after a lot of attempts, I cannot reproduce.
However, I can reproduce with the old index and and a generated data file.

Here is the program to reproduce

Code: Select all

00001 def FnTestDuplicates
00002     dim RecsToUpdate(0), DulpicateRecords(0)
00003     dim Type$*1, ID$*32, Day, Posted, Size, rc, HasDuplicates
00004 !
00005     open #25: 'name=test.int.txt, kfname=test.idx.txt, shr', internal, outin, keyed
00006     let HasDuplicates = FnHasDuplicateRecords(25, mat DulpicateRecords)
00007     print 'Before: HasDuplicates == '; HasDuplicates
00008     mat RecsToUpdate(0)
00009     let Size = 0
00010     restore #25, search=>'M0' : nokey ignore
00011     do until file(25)
00012         read #25, using 'form pos 1, c 1, c 32, bh 3, x 8, zd 1' : Type$, ID$, Day, Posted eof ignore
00013         if ~file(25) then
00014             if Type$ == 'M' and ~Posted and Day <= days(date$('mdcy'), 'mdcy') then
00015                 let Size += 1
00016                 mat RecsToUpdate(Size)
00017                 let RecsToUpdate(Size) = rec(25)
00018             end if
00019         end if
00020     loop while Type$ == 'M'
00021 !
00022     for rc = 1 to udim(RecsToUpdate)
00023         rewrite #25, using 'form pos 45, zd 1', rec=RecsToUpdate(rc) : 1
00024     next rc
00025 !
00026     let HasDuplicates = FnHasDuplicateRecords(25, mat DulpicateRecords)
00027     print 'After: HasDuplicates == '; HasDuplicates
00028     for rc = 1 to udim(DulpicateRecords)
00029         print DulpicateRecords(rc)
00030         sleep(.1)
00031     next rc
00032     close #25, release :
00033 fnend
00034 !
00035 !
00036 def FnHasDuplicateRecords(FileHandle, mat DulpicateRecords; ___, Size, AllSize, _rec)
00037     dim AllRecs(0)
00038     mat DulpicateRecords(0)
00039     mat AllRecs(0)
00040     let Size = 0
00041     let AllSize = 0
00042     restore #FileHandle :
00043     do until file(FileHandle)
00044         read #FileHandle : eof ignore
00045         if ~file(FileHandle) then
00046             let _rec = rec(FileHandle)
00047             if max(srch(mat AllRecs, _rec), 0) then
00048                 let Size += 1
00049                 mat DulpicateRecords(Size)
00050                 let DulpicateRecords(Size) = _rec
00051             else
00052                 let AllSize += 1
00053                 mat AllRecs(AllSize)
00054                 let AllRecs(AllSize) = _rec
00055             end if
00056         end if
00057     loop
00058     let FnHasDuplicateRecords = udim(DulpicateRecords)
00059 fnend
00060 !
00061 let FnTestDuplicates
00062 stop
00063 !
I've also included it as a compiled program attachment.
While this may not be the most helpful, perhaps something can be gleaned from the index file.
Attachments
test.br
(4.72 KiB) Downloaded 833 times
test.int.txt
(473.34 KiB) Downloaded 850 times
test.idx.txt
(204.75 KiB) Downloaded 843 times
GomezL
Posts: 244
Joined: Wed Apr 29, 2009 5:51 am
Contact:

Re: Mixing Keyed and Relative File Access

Post by GomezL »

Your example reminded me why I don't use REC= for updating keyed files.

BR is being helpful and is adding a NEW INDEX ENTRY, but it's also keeping the "OLD INDEX ENTRY" as well. I think that's a "Bug" and that BR should be deleting the original key information.

If you run your program twice, it won't add even more records, because the NEW INDEX ENTRY is found.

I added Attachments with the actual data before & after including the Position in the index "COUNT" as well as the actual "Record". I also included the program that creates the exports.

You can see for example that Record 5087 "M 1029 44408 1" is in position 24 (Original) as well as 5460 (Updated)

The answer is to open another Keyed handle and to use that keyed handle to loop through the matching keys looking for a matching record #. Then you can rewrite that record. (My previous examples show generic routines that do all the work)

Or add a unique key to the record. In my applications, I make a new field called "Recno" and I store the Record # in that field. It can be a little confusing because if you for example pack the file, it no longer matches the true record, and it's possible to get duplicates, but when combined with the other keys it's probably unique (Perhaps not). I see you already have DAY but add time as well to get a more unique record.
Attachments
EXPORT_TEST.wb
(2.48 KiB) Downloaded 838 times
TEST.EXPORT_Before.TXT
(328.47 KiB) Downloaded 945 times
TEST.EXPORT_After.TXT
(341.16 KiB) Downloaded 802 times
mikemiller
Posts: 7
Joined: Wed Mar 17, 2021 6:01 am

Re: Mixing Keyed and Relative File Access

Post by mikemiller »

That makes sense.
I think the route I'll take is to just add a unique primary key and operate off of that.

Thanks again for taking the time to actually look at it! :D
Post Reply