A direct access database system

[Henko]

Most direct access file systems do not do a hard delete of a record. Instead the record is removed from the key.

On the mainframe with VSAM, you have the ability to recover lost records this way. That is until a new record overwrites that slot.

VSAM works by creating record buffers, and your record is placed logically in the file. The physical file has no gaps, per se. But the key file does. This allows you to insert records and keep them in key order. The VSAM system does a best guess as to which buffer or group of keys to insert the record.

However, when running a program that does a mass load of records, you wind up with buffer splits, and this can either slow down your direct access or even corrupt the keyfile. Then you run a utility to rebuild the key from the records in the file.

Here we do not have to write that complicated a direct access file system, but if you decide to create and use a keyfile that holds the address of the record (in SmartBASIC, that would be the number of bytes to read to get to the record), you would need a way to insert keys so that file stays in order. Otherwise access to the file will be slow.

Just some information if you decide that a keyfile, if there is a possibility of working with large files, is a better and faster way to do a direct access to your record.

I am working on a program that reads in a file that holds voter registration data for 380,000 records, and 1.2 million voting history data, so in this case, I would need to create a key index file to help me access my records much faster. However, when I implement this App, the Florida State database is North of 10 million voter records, and more than 20 million voting history records.

When I do write this, I will share it here with the group, as key access is faster and better when accessing a file directly.

George.

I uncommented various functions and tried them. Got expected results for most. A surprise when I deleted Carter. When I looked at testdb afterward, most of Carter's record was still there. The key had been shortened to "er", but the fields were not deleted:

er
Lydia Carter sales 36 3300

Is this your intent?

I tried add_rec, but got an error message. I assumed I could format it as add_rec("Flintstone", "Fred Flintstone delivery 42 4400") but got an error. Could you please include an example for add_rec in the next posting? Thanks.

I will start to build an interface to make full use of all of the functions.

Congratulations - this will be a very useful program.

When deleting a record, the slot where it resides is merely marked as empty, is is not necessary to entirely erase it. After deletion, use the function db-dump() to display the actual content of the DB.
I copy/pasted your add_rec statement from here into the testcode (main), but got no error. Fred is nicely added into slot 4. See the dump result.

Using this hashing method, the average access speed is independent from the amount of records. It depends solely on the ratio between the number of slots in the primary area, and the number of records. If those numbers are equal, then about 63% of the records reside in the primary area, needing only one (timeless) access. The average number of accesses is 1.6 per record retrieval. If the primary area is chosen twice the size of the number of records, then those figures are about 80% and 1.3 accesses respectively. You can have the formulas if you want.
Those figures are based on a perfect hashing algorithm, which spreads the keys evenly over the primary adress space. For very large DB's, that property should ne investigated by a large enough sample of the key space.
I coded the method such, that the size of the secondary, or "overflow" area for the synonime records starts at zero and is automatically enlarged when needed.

[Henko]

A flat, random accessible file organization.

This is version 2 of this access method.
It is called "flat", because it is based upon one file only. Usually a database is a collection of files, which are related to each other in some sense, mostly because they share the same key or parts of each other's key.
In this case there is one file, with one record layout and one key for each record. Using this key, the corresponding record can be retrieved from the file with a very limited number of search accesses, mostly one or two accesses. And this is independent of the size of the file. If it containes 2 million records, the average number of acesses to retrieve a record is still about 1.5.

Version 2 differs from version 1 in that the user can specify the record layout in a simple way: the number of fields in a record and the field names (headers). Secondly the key to be used to access the records can be specified: one field out of the record, or an ordered combination of fields. Finally, the separation character used to separate the fields within a record can be specified.

Based upon this code, SB programmers may now design and code their own specific database system. The user interface (UI) is not coded by me, but Rbytes is coding a nice one, called "Datamine".
In this code package contains the "DB engine" version 2, which is in fact an include file, another includefile, named "auxilliary", which contains some functions that i use in a lot of apps, and a "main" program, just for testing and showing one of the basic functions of a DB system, namely retrieving and viewing record contents. For test purposes, a number of countries is downloaded from the site "get countries()". I prepared the dowload with a little program to make it fit the import function in the db-engine. The prepared importfile is in the following package as well. It should be named "importfile" and reside in the same directory where the DB program is started.

The access mechanism is based upon a technique called "hashing". The position of a record in the file is calculated on basis of its key. This requires that the file be split in a number of "slots" of equal size. The size must be able to accomodate the largest "logical" record and a little overhead. The hashing algorithm takes a key as input and produces a slotnumber where to put or to retrieve the record.
It will happen that two different keys result in the same slotadress. Such keys are called "synonimes". If it happens, the synonime record is placed in an "overflow" area, and will be connected to the first record in the primary area via a "linked list".
A search key will be "hashed" into a slotnumber in the primary area. If the key in that slot is equal to the search key, then the record is found. If not, and there is a linked list starting at the primary area location, the linked list is "followed", comparing the keys until equal or until the end of the list (in which case the record is "not found").

If the number of slots in the primary area is taken equal to the expected maximum number of records, about 2/3 of the records will be placed in the primary area. The other third of the records reside in the overflow area, connected with linked lists. It means that in that case about 1.5 accesses on the average are needed to retrieve a record.
It is interesting to note that the size of the primary area is not limited to any minimum or maximum. It must however contain at least one slot. In that case, all records get the same slotnumber assigned, and all records but one will be in one linked list in the overflow area. The average number of accesses to find a record will then be half the number of records on the average. On the other side, if you want to eliminate the probability of more than only one access, the number of slots must be infinite. A number of slots equal to the number of records is a fair compromize. But in this tool the choise is left to the programmer.

Each record in the DB consist of one string, containing the fields of the record, separated by an separation token. The application programmer is responsible for packing and unpacking of the record string.

The basic coding for this file and access organization is called "DB-engine" and contains 2 parts. There is a part that consists of a set of functions which are meant to be used by application programmers (can be seen as an "API"). This part consists of the following functions, that will be described in detail:
- create a database
- open an existing database
- add a record
- delete a record
- retrieve a record (for viewing, reporting, or editing)
- (re)write an edited record
- importing and exporting records
- enlarging the number of slots in the primary area (full reorganization)
- enlarging the record layout (full reorganization)
The second part consist of a number of "low level" functions, used by the API-functions herefore mentioned. The application programmer does not need to use these functions directly.

I would advice to not modify the API functions and the low-level functions.

Description of the "high-level" API functions.

Create a DB (database)
db-create(fn$,db_name$,header$,key$,separate$,S_size,N_slots)
When initially creating the DB, you have to specify:
fn$ - the filename for the DB
db_name$ - a descriptive name for the DB, just for readability purposes
header$ - the record layout, that is, the number and the names of the fields in a record. These are specified in a string, containing the field names (headers), separated by a separation token.
key$ - specification of the key to be used in this DB. Example: key$="0 3 1" means that the key is a concatenation of the 1st field, the 4th field, and the 2nd field, in that order, and separated by the separation character.
separate$ - A separation character to be used for this DB
S_size - the fixed slot size in bytes: must accommodate the largest record, the chosen key, and a linkfield of some 10 characters.
N_slots - The number of slots in the primary area. The expected maximum number of records in the DB is a reasonable amount.

Open an existing DB for processing
db_open(fn$)
fn$ - the filename of the DB to open.
After calling this function, the content of the DB can be accessed and processed.
Headers are available in the string array db.header$(i) with 0 <= i < db.N_fields
The number of slots in the primary area: db.N_prim
The slotsize: db.S_size.

Retrieving a record for viewing or editing
record$ = rec_get$(key$)
key$ - the search key of the record
If the key is found, the record is returned, if it does not exist, "not found" is returned.
If the content of the record is to be modified, then the original record must be kept for the edit function (see there), so do a org_rec$ = record$ (or likewise).

Adding a record to the DB
integer = rec_ add(rec$)
rec$ - a string with all fields of the record, separated by separate$.
The key of the record is constructed from the record field(s) as specified by the create function. The function returns an integer:
=>1 - the slotnumber where the record is added (succesfully)
=-1 - the key is already in use, record is not added
=-2 - the total length of the ley, the record and the link larger than the slotsize,
record not added
=-3 - the number of record fields not equal to db.N_fields

Editing the contents of a record in the DB
integer = rec_edit(old_rec$,new_rec$)
old_rec$ - the original record as read from the DB
new_rec$ - the modified record to replace the original
The function returns 1 on success, and 0 if the record is not replaced for any reason.
The function contructs the key for both records. If the key is not changed, the modified record is rewitten in the same slot. If the key is changed, the original record is deleted and the modified record is added to the DB, most probably getting another slot adress.

Deleting a record from the DB
integer = rec_del(keyS)
Key$ - the key of the record to be removed from the DB.
Returns 1 if the deletion is succesfull, 0 if the key is not found.
The slot where the record resides is not erased, but is merely marked for deletion by writing an "empty" record into is ("" , "" , "0" ). If you inspect the DB with the SmartBasic editor, you will still see a large part of the "deleted" record.

Importing records into the DB
Integer = db_import(imf$,sep$)
imf$ - the file from which records are imported
sep$ - the separation character used in the import file
The function returns 0 if some of the records were not accepted due to an incorrect number of fields, and returns 1 otherwise. The importfile must contain one string per record. Each string is unpacked (SPLIT statement) checked, (re)packed with the separation character of the DB, and added to the DB.

Exporting records from the DB
Integer = db_export(exf$,sep$)
exf$ - the file to which the DB records must be written
sep$ - the separation character to be used in the export file. This may be different from the DB separation character.
The function returns the number of written records. The export file contains one string for each record. In a next version, the functionallity might be augmented by adding a selection filter mechanism.

Enlarging the number of slots in the primary area
Not yet coded
The number of slots in the primary area is not subject to limitations (other than there must at least be one slot). Nevertheless, if the number of records grows well beyond the size of the primary area, the average number of accesses to retrieve a record grows steadily. If this effect becomes a nuisance, this utility may be used to fully reorganize the DB.

Enlarging the slotsize
Not yet coded
If the slotsize needs modification, the DB needs a full reorganization. If the modification is required because additional fields need to be added to the record format, the road to go is exporting the DB, add the records to the exportfile using a self coded program, create a new DB with the new record layout, and then import the modified export file.



Follows : the test program with an indication of the 2 include files

Code: Select all

' version 2, 23-12-2016
' test program

graphics ! graphics clear .8,.8,.8
fill color .8,.8,.8 ! draw color 0,0,0

'      specification for the new DB

f$="DB_countries"       ' filename for the DB to be created/opened
read rec_layout$        ' layout specification via the header names
data "countryCode,countryName,currencyCode,population,capital,areaInSqKm"
key$="1"    ' specification of the key, i.e. only field 1 (countryName)
sep$=","    ' separation character is comma
'      creation of the DB

if file_exists(f$) then file f$ delete
db_create(f$,"Countries of the world",rec_layout$,key$,sep$,200,800)

'  db_open(f$)  for existing DB this open statement instaed of create

'  import the records in the countries file

db_import("countries",",")   '  takes about 5 seconds for iPad air 2

'  retrieving and viewing records via a sorted key list
'  basic method would be by just entering one key at the time

'  produce a list with all keys, sorted (see include file auxilliary)
'  and make a viewing panel, fit for one record
'  both using the page mechanism to preserve a background

key_list()
rec_window(300,100,300,50+40*db.N_fields,.8,.8,.8,.3)

page "keys" show ! list "keys" select 0    ' open the selection list
page "dbrec" show                          ' open the viewing panel

do
  pt=list_selected("keys")
  if pt>=0 then               '  if a key in the list is tapped...
    key$=list_window.temp$(pt)  ' this is the key
    rec$=rec_get$(key$)         ' get the record
    split rec$ to fld$,nf with db.separate$  ' separate the fields
    for i=0 to nf-1
      n$="dbrecf"&i      '  fill the fields of the viewing panel
      field n$ text db.header$(i)&" : " & fld$(i)
      next i
    end if
  until forever      ' view other records
end

{db_engine}
'         API FUNCTIONS (do not modify for compatibility reasons)

' def db_create(fn$,db_name$,header$,key$,separate$,S_size,N_rec)
' def db_open(fn$)
' def rec_add(rec$)
' def rec_get$(key$)
' def rec_edit(old_rec$,new_rec$)
' def rec_del(key$)
' def db_import(imf$,sep$)
' def db_export(exf$,sep$)
'
'         LOW-LEVEL FUNCTIONS  (used by API, do not modify)

' def rec_pack$(field$(),sep$)
' def db(fn$)
' def get_adr(key$)
' def hash(tt$)
' def N_slots()
' def get_1st_free()
' def put_1st_free(adr)
' def pos(adr)

{auxilliary}
'         OTHER FUNCTIONS   (not critical)
' def rec_disp()
' def db_dump()
' def key_list()
' def f_grey()
' def f_yellow()
' def d_black()
' def d_blue()
' def bp(a$)
' def rec_window(xs,ys,ww,hh,R,G,B,alpha)
' def list_window(name$,titel$,cont$(),size,xs,ys,ww,hh,R,G,B,alpha)

Follows: the "db_engine" include file (the actual "product")

Code: Select all

' direct access database, version 2, 23-12-2016
'
'          API FUNCTIONS
'
' Create a direct access database with fixed length slots
'
' fn$      - filename on "disc"
' db_name$ - database name for readability
' header$  - fieldnames in one string, separated by separate$
' key$     - composition of the key (fieldnumber, separated)
' separate$- separate token, used in this database
' S_size   - slotsize for key + record + link fields
' N_slot   - # of slots
'
def db_create(fn$,db_name$,header$,key$,separate$,S_size,N_slot)
'
N_prim=N_slot         ' # of slots in primary area
S_free=N_slot+1        ' initial first free slot
dim slot(S_size)
if file_exists(fn$) then file fn$ delete
        ' create space for the database
for i=0 to N_prim ! file fn$ writedim slot ! next i
        ' write slot number 0 with meta data
file fn$ setpos 0
file fn$ writeline db_name$,str$(S_size),str$(N_prim),str$(S_free)
        ' write field info in slot 1
file fn$ setpos S_size
file fn$ writeline header$,key$,separate$
        ' init the slots in the primary area
for i=2 to N_slot
  file fn$ setpos S_size*i
  file fn$ writeline "","","0"
  next i
db_open(fn$)
end def

def db_open(f$)   ' API alias for db()
db(f$)
end def

' open database fn$
'
def db(fn$)
file fn$ setpos 0 ! file fn$ readline db_name$,size$,prim$,free$
S_size=size$ ! N_prim=prim$ ! S_free=free$
pos(1) ! file fn$ readline h$,k$,separate$
split h$ to header$,N_fields with separate$
split k$ to key_index$,N_keys with separate$
empty_slot$="" ! for i=1 to S_size ! empty_slot$ &= " " ! next i
end def

' add record to database
' returns slotnumber if succesfully added
' returns -1 if key is already in use
' returns -2 if key+record+link is too long
' returns -3 if number of fields is not correct
'
def rec_add(rec$)
split rec$ to a$,na with db.separate$
if na<>db.N_fields then return -3
key$=rec_key$(rec$)
ls=len(key$)+len(rec$)+len(db.free$)
if ls>db.S_size then return -2  ' length larger than slotsize
adr=get_adr(key$)
if adr>0 then return -1    ' record with same key already present
if adr<0 then              ' free slot in primary area
  adr=-adr
  pos(adr)  ! file db.fn$ writeline key$,rec$,"0"
  return adr
  end if
new=hash(key$)
pos(new)    ! file db.fn$ readline aux$,trec$,link$
rec_to=get_1st_free()
pos(rec_to) ! file db.fn$ writeline db.empty_slot$
pos(rec_to) ! file db.fn$ writeline key$,rec$,link$
pos(new)    ! file db.fn$ writeline aux$,trec$,str$(rec_to)
put_1st_free(rec_to+1)
end def

' rewrite an edited record into the database
' returns 1 on succes, 0 on failure
'
def rec_edit(old_rec$,new_rec$)
old_key$=rec_key$(old_rec$) ! new_key$=rec_key$(new_rec$)
if old_key$="error" or new_key$="error" then return 0
if old_key$=new_key$ then
  slot=get_adr(old_key$) ! if slot<=0 then return 0
  pos(slot) ! file db.fn$ readline t$,t$,link$
  pos(slot) ! file db.fn$ writeline new_key$,new_rec$,link$
 else
  rec_del(old_key$) ! rec_add(new_rec$)
  end if
return 1
end def

def rec_del(key$)
prd_adr=hash(key$)
pos(prd_adr) ! file db.fn$ readline prd_key$,prd_rec$,prd_link$
if prd_key$=key$ then
  suc_adr=val(prd_link$)
  if suc_adr=0 then
    pos(prd_adr) ! file db.fn$ writeline "","","0"
    return 0
    end if
  pos(suc_adr) ! file db.fn$ readline suc_key$,suc_rec$,suc_link$
  pos(prd_adr) ! file db.fn$ writeline suc_key$,suc_rec$,suc_link$
  pos(suc_adr) ! file db.fn$ writeline "","","0"
  return 1
  end if
do
  suc_adr=val(prd_link$)
  pos(suc_adr) ! file db.fn$ readline suc_key$,suc_rec$,suc_link$
  if suc_key$=key$ then
    prd_link$=suc_link$
    pos(prd_adr) ! file db.fn$ writeline prd_key$,prd_rec$,prd_link$
    pos(suc_adr) ! file db.fn$ writeline "","","0"
    return 1
    end if
  prd_adr=suc_adr
  pos(prd_adr) ! file db.fn$ readline prd_key$,prd_rec$,prd_link$
  until suc_link$="0"
return 0
end def

' retrieve record with key <key$> from the DB
' returns <not found> if not found
'
def rec_get$(key$)
slot=get_adr(key$)
if slot>0 then 
  pos(slot) ! file db.fn$ readline t$,rec$,t$
  return rec$
  else
  return "not found"
  end if
end def

' import records from import file into the opened databse
' imf$ - importfile with one string per record
' sep$ - string with one or more separators for SPLIT function
' function checks on correct number of fields
' function returns 1 if success
' function returns 0 if one or more records are not accepted
' can check db_import.N_read and db_import.N_added
'
def db_import(imf$,sep$)
file imf$ setpos 0 ! N_read=0 ! N_added=0
do
  file imf$ readline rec$ ! N_read+=1
  split rec$ to field$,nf with sep$
  if nf<>db.N_fields then continue
  rec$=rec_pack$(field$,db.separate$)
  if rec_add(rec$)>0 then N_added+=1
  until file_end(imf$)
  if N_read=N_added then return 1 else return 0
end def

' export content of database to file fex$
' deleted records are skipped
' exf$ - the filename to which the DB records must be exported
' sep$ - the separation character to use for the exported record
' returns the number of records written
'
def db_export(exf$,sep$)
if file_exists(exf$) then file exf$ delete
nslot=N_slots() ! nrec=0
for i=2 to nslot
pos(i) ! file db.fn$ readline key$, rec$, t$
  if key$="" then continue
  split rec$ to field$,n with db.separate$
  rec$=rec_pack$(field$,sep$)
  file exf$ writeline rec$ ! nrec+=1
  next i
return nrec
end def

'       LOW-LEVEL FUNCTIONS (DO NOT MODIFY)
'
' pack record fields into one string for adding or exporting
' fields$() - fields to be packed (# = db.N_fields)
' sep$      - separation character (may be different from db.separate$
'             in case of exporting)
' returns one record string
'
def rec_pack$(field$(),sep$)
for i=0 to db.N_fields-1
  if i=0 then rec$=field$(0) else rec$ &= sep$ & field$(i)
  next i
return rec$
end def

' find adress of record with given key
' key$ - the key on which to search
' function returns one of 3 values:
' adr > 0 : record found in slot <adr>    (update or delete record)
' adr = 0 : no record found with that key  (error key or add new record)
' adr < 0 : not found, but slot <abs(adr)> is free (add new record)
'
def get_adr(key$)
adr=hash(key$)
while adr>0
  pos(adr) ! file db.fn$ readline aux$,rec$,link$
  if aux$=key$ then return adr
  if aux$="" then return -adr
  adr=val(link$)
  end while
return 0
end def

' returns key$ of the passed record
' returns "error" if record is not valid
'
def rec_key$(rec$)
split rec$ to a$,na with db.separate$
if na<>db.N_fields then return "error"
key$=""
for i=0 to db.N_keys-1
  if i=0 then ! key$ = a$(val(db.key_index$(0)))
    else ! key$ &= db.separate$ & a$(val(db.key_index$(i)))
    end if
  next i
return key$
end def

' transform key tt$ into a DB adress (slotnumber)
'
def hash(tt$)
nt=len(tt$) ! adr=1
for i=0 to nt-1 ! adr+=asc(mid$(tt$,i,1))-32 ! adr*=7 ! next i
return 2+adr%(db.N_prim-1)
end def

' returns the actual number of slots in the DB (inclusive overflow)
'
def N_slots() = floor(file_size(db.fn$)/db.S_size)

' returns the adress of the first free slot
'
def get_1st_free()
pos(0) ! file db.fn$ readline t$,t$,t$,t$
return val(t$)
end def

' edits the adress of the first free slot
'
def put_1st_free(adr)
pos(0) ! file db.fn$ readline  t1$,t2$,t3$,t4$
pos(0) ! file db.fn$ writeline t1$,t2$,t3$,str$(adr)
return
end def

def pos(adr)
file db.fn$ setpos adr*db.S_size
end def

Follows: the "auxilliary" include file (needed for the test program)

Code: Select all

'       OTHER UTILITY FUNCTIONS (FEEL FREE TO USE OR MODIFY)

' retrieve and display individual records in text mode
' quick & dirty for test purposes
'
def rec_disp()
text
do
  input "Searchkey? ": key$
  if key$="stop" or key$="Stop" or key$="" then break
  record$=rec_get$(key$)
  if record$="not found" then
    print ! print record$ ! continue
    end if
  split record$ to field$,n with db.separate$
  print
  for i=0 to n-1 ! print db.header$(i) & " - " & field$(i) ! next i
  until forever
end def

' dump the content of the currently opened database in text mode
' quick & dirty for test purposes
'
def db_dump()
text ! print
f$=db.fn$ ! slot=db.S_size
nslot=N_slots()
for i=2 to nslot
pos(i) ! file f$ readline key$,rec$,link$
if key$<>"" then print i;"  ";key$;"  ";rec$;"  ";link$
next i
end def

' prepare a selection list with sorted keys
'
def key_list()
f$=db.fn$ ! slot=db.S_size ! nkeys=-1
nslot=N_slots()
dim keys$(nslot)
for i=2 to nslot
  pos(i) ! file f$ readline key$,t$,t$
  if key$<>"" then ! nkeys+=1 ! keys$(nkeys)=key$ ! end if
  next i
list_window("keys","Record keys",keys$,nkeys,50,50,180,500,.8,.8,.8,1)
end def

def list_window(name$,titel$,cont$(),size,xs,ys,ww,hh,R,G,B,alpha)
dim temp$(size+1)
for i=0 to size ! temp$(i)=cont$(i) ! next i
sort temp$
page name$ set 
page name$ frame xs,ys,0,0
page name$ color R,G,B,alpha
set buttons custom ! d_black() ! f_grey()
button name$&"_close" title "❎" at ww-30,5 size 22,22
button name$&"bottom" title "" at -6,hh-3 size ww+12,3
button name$&"left" title "" at 0,-6 size 3,hh+12
button name$&"right" title "" at ww-3,-6 size 3,hh+12
button name$&"upper1" title "" at -6,0 size ww+12,3
button name$&"upper2" title "" at -6,30 size ww+12,3
button name$&"title" title titel$ at 20,3 size ww-60,27
set lists custom
list name$ text temp$ at 2,32 size ww-4,hh-34
page name$ hide
page name$ frame xs,ys,ww,hh
end def

def rec_window(xs,ys,ww,hh,R,G,B,alpha)
name$="dbrec"
page name$ set 
page name$ frame xs,ys,0,0
page name$ color R,G,B,alpha
button name$&"_close" title "❎" at ww-30,5 size 22,22
set buttons custom ! draw color 0,0,0
button name$&"bottom" title "" at -6,hh-3 size ww+12,3
button name$&"left" title "" at 0,-6 size 3,hh+12
button name$&"right" title "" at ww-3,-6 size 3,hh+12
button name$&"upper1" title "" at -6,0 size ww+12,3
button name$&"upper2" title "" at -6,30 size ww+12,3
d_blue() ! f_grey()
button "d_title" title db.db_name$ at(ww-190)/2,3 size 200,27
for i=0 to db.N_fields-1
  n$=name$&"f"&i
  field n$ text "" at 10,40+40*i size ww-20,30 RO
  field n$ back color .8,.8,.8
  next i
page name$ hide
page name$ frame xs,ys,ww,hh
end def

def f_grey() ! fill color .8,.8,.8 ! end def
def f_yellow() ! fill color 1,1,.7 ! end def
def d_black() ! draw color 0,0,0 ! end def
def d_blue() ! draw color 0,0,1 ! end def
def bp(a$) = button_pressed(a$)

Follows: the datafile "countries"

Code: Select all

AD,Andorra,EUR,84000,Andorra la Vella,468.0
AE,United Arab Emirates,AED,4975593,Abu Dhabi,82880.0
AF,Afghanistan,AFN,29121286,Kabul,647500.0
AG,Antigua and Barbuda,XCD,86754,St. John's,443.0
AI,Anguilla,XCD,13254,The Valley,102.0
AL,Albania,ALL,2986952,Tirana,28748.0
AM,Armenia,AMD,2968000,Yerevan,29800.0
AO,Angola,AOA,13068161,Luanda,1246700.0
AQ,Antarctica,,0,,1.4E7
AR,Argentina,ARS,41343201,Buenos Aires,2766890.0
AS,American Samoa,USD,57881,Pago Pago,199.0
AT,Austria,EUR,8205000,Vienna,83858.0
AU,Australia,AUD,21515754,Canberra,7686850.0
AW,Aruba,AWG,71566,Oranjestad,193.0
AX,Åland,EUR,26711,Mariehamn,1580.0
AZ,Azerbaijan,AZN,8303512,Baku,86600.0
BA,Bosnia and Herzegovina,BAM,4590000,Sarajevo,51129.0
BB,Barbados,BBD,285653,Bridgetown,431.0
BD,Bangladesh,BDT,156118464,Dhaka,144000.0
BE,Belgium,EUR,10403000,Brussels,30510.0
BF,Burkina Faso,XOF,16241811,Ouagadougou,274200.0
BG,Bulgaria,BGN,7148785,Sofia,110910.0
BH,Bahrain,BHD,738004,Manama,665.0
BI,Burundi,BIF,9863117,Bujumbura,27830.0
BJ,Benin,XOF,9056010,Porto-Novo,112620.0
BL,Saint Barthélemy,EUR,8450,Gustavia,21.0
BM,Bermuda,BMD,65365,Hamilton,53.0
BN,Brunei,BND,395027,Bandar Seri Begawan,5770.0
BO,Bolivia,BOB,9947418,Sucre,1098580.0
BQ,Bonaire,USD,18012,Kralendijk,328.0
BR,Brazil,BRL,201103330,Brasília,8511965.0
BS,Bahamas,BSD,301790,Nassau,13940.0
BT,Bhutan,BTN,699847,Thimphu,47000.0
BV,Bouvet Island,NOK,0,,49.0
BW,Botswana,BWP,2029307,Gaborone,600370.0
BY,Belarus,BYR,9685000,Minsk,207600.0
BZ,Belize,BZD,314522,Belmopan,22966.0
CA,Canada,CAD,33679000,Ottawa,9984670.0
CC,Cocos [Keeling] Islands,AUD,628,West Island,14.0
CD,Democratic Republic of the Congo,CDF,70916439,Kinshasa,2345410.0
CF,Central African Republic,XAF,4844927,Bangui,622984.0
CG,Republic of the Congo,XAF,3039126,Brazzaville,342000.0
CH,Switzerland,CHF,7581000,Bern,41290.0
CI,Ivory Coast,XOF,21058798,Yamoussoukro,322460.0
CK,Cook Islands,NZD,21388,Avarua,240.0
CL,Chile,CLP,16746491,Santiago,756950.0
CM,Cameroon,XAF,19294149,Yaoundé,475440.0
CN,China,CNY,1330044000,Beijing,9596960.0
CO,Colombia,COP,47790000,Bogotá,1138910.0
CR,Costa Rica,CRC,4516220,San José,51100.0
CU,Cuba,CUP,11423000,Havana,110860.0
CV,Cape Verde,CVE,508659,Praia,4033.0
CW,Curacao,ANG,141766,Willemstad,444.0
CX,Christmas Island,AUD,1500,Flying Fish Cove,135.0
CY,Cyprus,EUR,1102677,Nicosia,9250.0
CZ,Czechia,CZK,10476000,Prague,78866.0
DE,Germany,EUR,81802257,Berlin,357021.0
DJ,Djibouti,DJF,740528,Djibouti,23000.0
DK,Denmark,DKK,5484000,Copenhagen,43094.0
DM,Dominica,XCD,72813,Roseau,754.0
DO,Dominican Republic,DOP,9823821,Santo Domingo,48730.0
DZ,Algeria,DZD,34586184,Algiers,2381740.0
EC,Ecuador,USD,14790608,Quito,283560.0
EE,Estonia,EUR,1291170,Tallinn,45226.0
EG,Egypt,EGP,80471869,Cairo,1001450.0
EH,Western Sahara,MAD,273008,Laâyoune / El Aaiún,266000.0
ER,Eritrea,ERN,5792984,Asmara,121320.0
ES,Spain,EUR,46505963,Madrid,504782.0
ET,Ethiopia,ETB,88013491,Addis Ababa,1127127.0
FI,Finland,EUR,5244000,Helsinki,337030.0
FJ,Fiji,FJD,875983,Suva,18270.0
FK,Falkland Islands,FKP,2638,Stanley,12173.0
FM,Micronesia,USD,107708,Palikir,702.0
FO,Faroe Islands,DKK,48228,Tórshavn,1399.0
FR,France,EUR,64768389,Paris,547030.0
GA,Gabon,XAF,1545255,Libreville,267667.0
GB,United Kingdom,GBP,62348447,London,244820.0
GD,Grenada,XCD,107818,St. George's,344.0
GE,Georgia,GEL,4630000,Tbilisi,69700.0
GF,French Guiana,EUR,195506,Cayenne,91000.0
GG,Guernsey,GBP,65228,St Peter Port,78.0
GH,Ghana,GHS,24339838,Accra,239460.0
GI,Gibraltar,GIP,27884,Gibraltar,6.5
GL,Greenland,DKK,56375,Nuuk,2166086.0
GM,Gambia,GMD,1593256,Bathurst,11300.0
GN,Guinea,GNF,10324025,Conakry,245857.0
GP,Guadeloupe,EUR,443000,Basse-Terre,1780.0
GQ,Equatorial Guinea,XAF,1014999,Malabo,28051.0
GR,Greece,EUR,11000000,Athens,131940.0
GS,South Georgia and the South Sandwich Islands,GBP,30,Grytviken,3903.0
GT,Guatemala,GTQ,13550440,Guatemala City,108890.0
GU,Guam,USD,159358,Hagåtña,549.0
GW,Guinea-Bissau,XOF,1565126,Bissau,36120.0
GY,Guyana,GYD,748486,Georgetown,214970.0
HK,Hong Kong,HKD,6898686,Hong Kong,1092.0
HM,Heard Island and McDonald Islands,AUD,0,,412.0
HN,Honduras,HNL,7989415,Tegucigalpa,112090.0
HR,Croatia,HRK,4284889,Zagreb,56542.0
HT,Haiti,HTG,9648924,Port-au-Prince,27750.0
HU,Hungary,HUF,9982000,Budapest,93030.0
ID,Indonesia,IDR,242968342,Jakarta,1919440.0
IE,Ireland,EUR,4622917,Dublin,70280.0
IL,Israel,ILS,7353985,,20770.0
IM,Isle of Man,GBP,75049,Douglas,572.0
IN,India,INR,1173108018,New Delhi,3287590.0
IO,British Indian Ocean Territory,USD,4000,,60.0
IQ,Iraq,IQD,29671605,Baghdad,437072.0
IR,Iran,IRR,76923300,Tehran,1648000.0
IS,Iceland,ISK,308910,Reykjavik,103000.0
IT,Italy,EUR,60340328,Rome,301230.0
JE,Jersey,GBP,90812,Saint Helier,116.0
JM,Jamaica,JMD,2847232,Kingston,10991.0
JO,Jordan,JOD,6407085,Amman,92300.0
JP,Japan,JPY,127288000,Tokyo,377835.0
KE,Kenya,KES,40046566,Nairobi,582650.0
KG,Kyrgyzstan,KGS,5776500,Bishkek,198500.0
KH,Cambodia,KHR,14453680,Phnom Penh,181040.0
KI,Kiribati,AUD,92533,Tarawa,811.0
KM,Comoros,KMF,773407,Moroni,2170.0
KN,Saint Kitts and Nevis,XCD,51134,Basseterre,261.0
KP,North Korea,KPW,22912177,Pyongyang,120540.0
KR,South Korea,KRW,48422644,Seoul,98480.0
KW,Kuwait,KWD,2789132,Kuwait City,17820.0
KY,Cayman Islands,KYD,44270,George Town,262.0
KZ,Kazakhstan,KZT,15340000,Astana,2717300.0
LA,Laos,LAK,6368162,Vientiane,236800.0
LB,Lebanon,LBP,4125247,Beirut,10400.0
LC,Saint Lucia,XCD,160922,Castries,616.0
LI,Liechtenstein,CHF,35000,Vaduz,160.0
LK,Sri Lanka,LKR,21513990,Colombo,65610.0
LR,Liberia,LRD,3685076,Monrovia,111370.0
LS,Lesotho,LSL,1919552,Maseru,30355.0
LT,Lithuania,EUR,2944459,Vilnius,65200.0
LU,Luxembourg,EUR,497538,Luxembourg,2586.0
LV,Latvia,EUR,2217969,Riga,64589.0
LY,Libya,LYD,6461454,Tripoli,1759540.0
MA,Morocco,MAD,31627428,Rabat,446550.0
MC,Monaco,EUR,32965,Monaco,1.95
MD,Moldova,MDL,4324000,Chişinău,33843.0
ME,Montenegro,EUR,666730,Podgorica,14026.0
MF,Saint Martin,EUR,35925,Marigot,53.0
MG,Madagascar,MGA,21281844,Antananarivo,587040.0
MH,Marshall Islands,USD,65859,Majuro,181.3
MK,Macedonia,MKD,2062294,Skopje,25333.0
ML,Mali,XOF,13796354,Bamako,1240000.0
MM,Myanmar [Burma],MMK,53414374,Naypyitaw,678500.0
MN,Mongolia,MNT,3086918,Ulan Bator,1565000.0
MO,Macao,MOP,449198,Macao,254.0
MP,Northern Mariana Islands,USD,53883,Saipan,477.0
MQ,Martinique,EUR,432900,Fort-de-France,1100.0
MR,Mauritania,MRO,3205060,Nouakchott,1030700.0
MS,Montserrat,XCD,9341,Plymouth,102.0
MT,Malta,EUR,403000,Valletta,316.0
MU,Mauritius,MUR,1294104,Port Louis,2040.0
MV,Maldives,MVR,395650,Malé,300.0
MW,Malawi,MWK,15447500,Lilongwe,118480.0
MX,Mexico,MXN,112468855,Mexico City,1972550.0
MY,Malaysia,MYR,28274729,Kuala Lumpur,329750.0
MZ,Mozambique,MZN,22061451,Maputo,801590.0
NA,Namibia,NAD,2128471,Windhoek,825418.0
NC,New Caledonia,XPF,216494,Noumea,19060.0
NE,Niger,XOF,15878271,Niamey,1267000.0
NF,Norfolk Island,AUD,1828,Kingston,34.6
NG,Nigeria,NGN,154000000,Abuja,923768.0
NI,Nicaragua,NIO,5995928,Managua,129494.0
NL,Netherlands,EUR,16645000,Amsterdam,41526.0
NO,Norway,NOK,5009150,Oslo,324220.0
NP,Nepal,NPR,28951852,Kathmandu,140800.0
NR,Nauru,AUD,10065,Yaren,21.0
NU,Niue,NZD,2166,Alofi,260.0
NZ,New Zealand,NZD,4252277,Wellington,268680.0
OM,Oman,OMR,2967717,Muscat,212460.0
PA,Panama,PAB,3410676,Panama City,78200.0
PE,Peru,PEN,29907003,Lima,1285220.0
PF,French Polynesia,XPF,270485,Papeete,4167.0
PG,Papua New Guinea,PGK,6064515,Port Moresby,462840.0
PH,Philippines,PHP,99900177,Manila,300000.0
PK,Pakistan,PKR,184404791,Islamabad,803940.0
PL,Poland,PLN,38500000,Warsaw,312685.0
PM,Saint Pierre and Miquelon,EUR,7012,Saint-Pierre,242.0
PN,Pitcairn Islands,NZD,46,Adamstown,47.0
PR,Puerto Rico,USD,3916632,San Juan,9104.0
PS,Palestine,ILS,3800000,,5970.0
PT,Portugal,EUR,10676000,Lisbon,92391.0
PW,Palau,USD,19907,Melekeok,458.0
PY,Paraguay,PYG,6375830,Asunción,406750.0
QA,Qatar,QAR,840926,Doha,11437.0
RE,Réunion,EUR,776948,Saint-Denis,2517.0
RO,Romania,RON,21959278,Bucharest,237500.0
RS,Serbia,RSD,7344847,Belgrade,88361.0
RU,Russia,RUB,140702000,Moscow,1.71E7
RW,Rwanda,RWF,11055976,Kigali,26338.0
SA,Saudi Arabia,SAR,25731776,Riyadh,1960582.0
SB,Solomon Islands,SBD,559198,Honiara,28450.0
SC,Seychelles,SCR,88340,Victoria,455.0
SD,Sudan,SDG,35000000,Khartoum,1861484.0
SE,Sweden,SEK,9828655,Stockholm,449964.0
SG,Singapore,SGD,4701069,Singapore,692.7
SH,Saint Helena,SHP,7460,Jamestown,410.0
SI,Slovenia,EUR,2007000,Ljubljana,20273.0
SJ,Svalbard and Jan Mayen,NOK,2550,Longyearbyen,62049.0
SK,Slovakia,EUR,5455000,Bratislava,48845.0
SL,Sierra Leone,SLL,5245695,Freetown,71740.0
SM,San Marino,EUR,31477,San Marino,61.2
SN,Senegal,XOF,12323252,Dakar,196190.0
SO,Somalia,SOS,10112453,Mogadishu,637657.0
SR,Suriname,SRD,492829,Paramaribo,163270.0
SS,South Sudan,SSP,8260490,Juba,644329.0
ST,São Tomé and Príncipe,STD,175808,São Tomé,1001.0
SV,El Salvador,USD,6052064,San Salvador,21040.0
SX,Sint Maarten,ANG,37429,Philipsburg,21.0
SY,Syria,SYP,22198110,Damascus,185180.0
SZ,Swaziland,SZL,1354051,Mbabane,17363.0
TC,Turks and Caicos Islands,USD,20556,Cockburn Town,430.0
TD,Chad,XAF,10543464,N'Djamena,1284000.0
TF,French Southern Territories,EUR,140,Port-aux-Français,7829.0
TG,Togo,XOF,6587239,Lomé,56785.0
TH,Thailand,THB,67089500,Bangkok,514000.0
TJ,Tajikistan,TJS,7487489,Dushanbe,143100.0
TK,Tokelau,NZD,1466,,10.0
TL,East Timor,USD,1154625,Dili,15007.0
TM,Turkmenistan,TMT,4940916,Ashgabat,488100.0
TN,Tunisia,TND,10589025,Tunis,163610.0
TO,Tonga,TOP,122580,Nuku'alofa,748.0
TR,Turkey,TRY,77804122,Ankara,780580.0
TT,Trinidad and Tobago,TTD,1228691,Port of Spain,5128.0
TV,Tuvalu,AUD,10472,Funafuti,26.0
TW,Taiwan,TWD,22894384,Taipei,35980.0
TZ,Tanzania,TZS,41892895,Dodoma,945087.0
UA,Ukraine,UAH,45415596,Kiev,603700.0
UG,Uganda,UGX,33398682,Kampala,236040.0
UM,U.S. Minor Outlying Islands,USD,0,,0.0
US,United States,USD,310232863,Washington,9629091.0
UY,Uruguay,UYU,3477000,Montevideo,176220.0
UZ,Uzbekistan,UZS,27865738,Tashkent,447400.0
VA,Vatican City,EUR,921,Vatican City,0.44
VC,Saint Vincent and the Grenadines,XCD,104217,Kingstown,389.0
VE,Venezuela,VEF,27223228,Caracas,912050.0
VG,British Virgin Islands,USD,21730,Road Town,153.0
VI,U.S. Virgin Islands,USD,108708,Charlotte Amalie,352.0
VN,Vietnam,VND,89571130,Hanoi,329560.0
VU,Vanuatu,VUV,221552,Port Vila,12200.0
WF,Wallis and Futuna,XPF,16025,Mata-Utu,274.0
WS,Samoa,WST,192001,Apia,2944.0
XK,Kosovo,EUR,1800000,Pristina,10908.0
YE,Yemen,YER,23495361,Sanaa,527970.0
YT,Mayotte,EUR,159042,Mamoudzou,374.0
ZA,South Africa,ZAR,49000000,Pretoria,1219912.0
ZM,Zambia,ZMW,13460305,Lusaka,752614.0
ZW,Zimbabwe,ZWL,13061000,Harare,390580.0

IMG_1336.PNG (361.3 KiB) Viewed 6083 times

[Tango33]

I tried to run this code for the simple ISAM database you posted earlier. I am trying to run it on an Ipad. It didn't work as it could not find the file which the program should create.

Ideally I would prefer a random access flat file along the line of version 2. But maybe I am wasting my time trying to get an Ipad to run this kind of code.

To save me a lot of time I have a simple question:

Using the Basic app for IOS, will an Ipad allow the code to create a file in the first place? Perhaps this code will only work on a laptop running IOS?

I also noticed this code: split rec$ to field$, n......

But I cannot find "split" in the documentation supplied with the IOS app.

Thanks for any help

[Dutchman]

…
I also noticed this code: split rec$ to field$, n......
But I cannot find "split" in the documentation supplied with the IOS app.
…

Searched and found in PDF-mqnual:
SPLIT A$ TO M$,N WITH S$
splits string [a$] to components using separator - string [s$] and stores result to one-dimensional string array [m$]. Array size is stored to numeric variable [n], if it is used. Parameter [n] is optional. Separator string [s$] contains characters, which are used to split string [a$]. Array [m$] does not contain empty strings.
Example:
SPLIT "1,2,3&4" TO pieces$,n WITH ",&"

[Tango33]

Hi Thanks for the info on Split. Perhaps my iPad code failed as split not supported.

I will need to figure out some code using other functions that replicates it. Seems it just chops up the contents of a string at a chosen interval with a specified seperator if I understand correctly.