A direct access database system
- sarossell
- Posts: 195
- Joined: Sat Nov 05, 2016 6:31 pm
- My devices: iPad Mini 2, iPhone 5, MacBook Air, MacBook Pro
- Flag:
- Contact:
Re: A direct access database system
Ah, of course, mea culpa. It was such a natural fit, I gazed right past it. Sorry. Datamine; informative, clever..and apparently, for my thick skull, subtly appropriate.
smart BASIC Rocks!
- Scott : San Diego, California
- Scott : San Diego, California
-
- Posts: 814
- Joined: Tue Apr 09, 2013 12:23 pm
- My devices: iPhone,iPad
Windows - Location: Groningen, Netherlands
- Flag:
Re: A direct access database system
A good suggestion there: i will assign slotnumber 1 to store the fieldheaders in one string, making a DB file more "selfcontaining". The string will be defined and written in the db_create() function, together with the other meta-data. It will be read and decomposed in the db() function. Thereafter the headers will be available to the user as db.header$(i) with i from 0 through db.N_fields-1.rbytes wrote:Thanks, Henko. I will take a look at the new code tonight. I am booked for Christmas shopping at IKEA this afternoon.
Some quick responses. I agree that it is best if you handle the low-level functions. I changed the data statements to put the surname first, and then made field 0 the key, since it was now the surname. Those changes corrected the labeling. Perhaps you have already done this correction some other way.
Regarding more or fewer fields. This one is a bit tougher. If there are only a small number of fields contained in the database, I don't think it would be good esthetically to have a bunch of blank slots on screen. Also some slots, such as those containing notes, will need to be quite a bit larger, or at least taller, such as the image window. Whether all of the tools need to be on screen for manipulating the layout is a good question. There could be pop-up menu that allows for setting the field layout and linking them to data. The other alternative would be some statements near the front of the code that could be changed to allow for more or fewer on-screen fields. If this program is to have maximum flexibility in being able to maintain multiple databases, then there will need to be a data header added to each database that describes its layout - how many fields, where positioned, what data links to them, etc. but I see that as an evolution that will take some time to reach
Re Next and Previous. These buttons make sense if the records can be called up alphabetically by surname, which your sorted list does well too. A possible combination could be that Next and Previous buttons step through the names in the list sequentially by alphabet, and the pop-up list gives random access to any name without needing to type it into the search field.
The Load button I envisioned to load distinctly different databases. I didn't include a save button since saves are performed continuously. But there may need to be at least a Confirm button for layout changes.
Regarding a name for the program, I did pick a name that I thought might work well, and I used it in the screen shots I posted last night. I like something short with a bit of flair, which ideally includes a pun or dual meaning. Naming it after one or both of us as the main collaborators doesn't appeal to me as much
The hashing algorithm shall be modified to exclude slot 1.
Please no names of us in the program name. Hashing was not invented here. Datamine or datavault or the like is ok for me. I don't care.
You mentioned large fields like notes. I think they should have a different approach than ordinary record fields, as the method is based on fixed sized slots. If a note of 2K must be possible ,all slots must at least have that size plus the rest of the records. The same goes for pictures. Maybe one or more fields must be references to filenames. Such fields must then be recognizable as textfiles or picturefiles (or music files for that matter ). Think about it.
A layout change in an exixting DB requires a dedicated conversion program. Not too difficult, but dedicated.
I will add a check on the length of new records in the add_record() function to ensure that the length fits in the slot size.
-
- Posts: 814
- Joined: Tue Apr 09, 2013 12:23 pm
- My devices: iPhone,iPad
Windows - Location: Groningen, Netherlands
- Flag:
Re: A direct access database system
Mofifications done.
The db() (open database) is now also recordlayout independent and has been moved to the include file.
Preparations have been made in the create() and db() functions to define the composition of the keys from the record contents. For instance, "2 0 1" means that the record key must consist of fields 2, 0, and 1 from the record. In the actual testDB, that would be simply "1", the key being the surname, field 1. Using this method (not yet fully implemented) would imply that adding a new record does no longer require the key as input. The function itself will generate it.
(It's time to start making a little user manual).
And the include file.
The db() (open database) is now also recordlayout independent and has been moved to the include file.
Preparations have been made in the create() and db() functions to define the composition of the keys from the record contents. For instance, "2 0 1" means that the record key must consist of fields 2, 0, and 1 from the record. In the actual testDB, that would be simply "1", the key being the surname, field 1. Using this method (not yet fully implemented) would imply that adding a new record does no longer require the key as input. The function itself will generate it.
(It's time to start making a little user manual).
Code: Select all
' version 13-12-2016
'
graphics ! graphics clear .8,.8,.8
fill color .8,.8,.8 ! draw color 0,0,0
first_time=1
f$="testdb"
if first_time then init_db(f$) else db(f$)
edit_rec("Brady")
' disp_rec()
' del_rec("Carter")
add_rec("Flintstone", "Fred Flintstone delivery 42 4400")
key_list()
page "keys" show
rec_window(340,50,300,400,.8,.8,.8,1)
page "dbrec" show
do ! slowdown
if bp("keys_close") then page "keys" hide
if bp("dbrec_close") then page "dbrec" hide
until forever
end
def init_db(f$)
'y'
h$="Name Surname Department Age Salary"
create_db(f$,"Test_database","1",h$,60,10)
restore to staff
for i=0 to 8
read rec$
split rec$ to field$,n with " " ' to extract the key
key$=field$(1)
add_rec(key$,rec$)
next i
staff:
data "John Friedman sales 32 4500 "
data " Betty Longa ledger 28 3700"
data " Ann Strady sales 43 5300 "
data "John-John Brady production 25 3250 "
data " Lydia Carter sales 36 3300 "
data " Peter Pan R&D 48 4100"
data " Reginald Nobody production 23 3100 "
data " Mary Poppins sales 29 3650 "
data " Elly Sunshine production 32 3560 "
''
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 "add / edit records" at(ww-190)/2,3 size 200,27
tx$="Window for viewing and editing records. Who is doing this piece of art? Can use the combined prompt+input field idea by Ton the Dutchman and some nice coloring."
field "temp" text tx$ at 10,140 size ww-20,180 ML RO
field "temp" back color .8,.8,.8
page name$ hide
page name$ frame xs,ys,ww,hh
end def
def user_edit$(rec$)
' this user function is called by edit_rec(key$)
'y'
' user code to modify the contents of rec$
''
return rec$
end def
def disp_rec(key$) ' temporary function for test purpose
dim field$(db.N_fields)
do
input "Surname? ": key$
if key$="stop" or key$="Stop" or key$="" then break
record$=get_record$(key$)
if record$="not found" then
print ! print record$ ! continue
end if
split record$ to field$,n with " "
print
for i=0 to n-1 ! print db.header$(i) & " - " & field$(i) ! next i
until forever
end def
{db_util}
' def create_db(fn$,db_name$,S_size,N_rec)
' def db(fn$)
' def add_rec(key$,rec$)
' def edit_rec(key$)
' def del_rec(key$)
' def get_record$(key$)
' def get_adr(key$)
' def hash(tt$)
' def key_list()
' def nslots()
' def get_1st_free()
' def put_1st_free(adr)
' def db_dump()
' def list_window(name$,titel$,cont$(),size,xs,ys,ww,hh,R,G,B,alpha)
' def pos(adr)
' def f_grey()
' def f_yellow()
' def d_black()
' def d_blue()
' def bp(a$)
Code: Select all
' Create a direct access database with fixed length slots
' fn$ - filename on "disc"
' db_name$ - database name for readability
' key$ - future use (composition of the key)
' header$ - fieldnames in one string, separated by spaces
' S_size - slotsize for key + record + link fields
' N_rec - estimated max. # of records
' version 13-12-2016
'
' create_db(f$,"Test_database","1",h$,50,10)
def create_db(fn$,db_name$,key$,header$,S_size,N_rec)
N_prim=N_rec ' # of slots in primary area
S_free=N_rec+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 key$,header$
'
' init the slots in the primary area
for i=2 to N_rec
file fn$ setpos S_size*i
file fn$ writeline "","","0"
next i
db(fn$)
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 k$,h$
empty_slot$="" ! for i=1 to S_size ! empty_slot$ &= " " ! next i
split h$ to header$,N_fields with " "
split k$ to key_index$,N_keys with " "
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
'
def add_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
def edit_rec(key$)
adr=get_adr(key$)
if adr>0 then
pos(adr) ! file db.fn$ readline key$,rec$,link$
rec$=user_edit$(rec$) ' user function to edit fields in rec$
pos(adr) ! file db.fn$ writeline key$,rec$,link$
end if
end def
def del_rec(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 get_record$(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
' 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
' 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
def key_list()
f$=db.fn$ ! slot=db.S_size ! nkeys=-1
N_slot=nslots()
dim keys$(N_slot)
for i=2 to N_slot
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
' returns the actual number of slots in the DB (inclusive overflow)
'
def nslots()
file db.fn$ setpos 999999
return floor(file_pos(db.fn$)/db.S_size)
end def
' 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 db_dump()
print
f$=db.fn$ ! slot=db.S_size
file f$ setpos 999999 ! nslot=floor(file_pos(f$)/slot)
for i=1 to nslot
pos(i) ! file f$ readline key$,rec$,link$
if key$<>"" then print i;" ";key$;" ";rec$;" ";link$
next i
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 pos(adr)
file db.fn$ setpos adr*db.S_size
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$)
- rbytes
- Posts: 1338
- Joined: Sun May 31, 2015 12:11 am
- My devices: iPhone 11 Pro Max
iPad Pro 11
MacBook
Dell Inspiron laptop
CHUWI Plus 10 convertible Windows/Android tablet - Location: Calgary, Canada
- Flag:
- Contact:
Re: A direct access database system
Dgh
- Attachments
-
- IMG_8737.PNG (3.96 MiB) Viewed 5852 times
Last edited by rbytes on Mon Feb 06, 2017 5:37 am, edited 7 times in total.
The only thing that gets me down is gravity...
- sarossell
- Posts: 195
- Joined: Sat Nov 05, 2016 6:31 pm
- My devices: iPad Mini 2, iPhone 5, MacBook Air, MacBook Pro
- Flag:
- Contact:
Re: A direct access database system
Exciting to see this coming about. I had to comment out the background jpg load since I don't have the image of course, but very cool.
smart BASIC Rocks!
- Scott : San Diego, California
- Scott : San Diego, California
- sarossell
- Posts: 195
- Joined: Sat Nov 05, 2016 6:31 pm
- My devices: iPad Mini 2, iPhone 5, MacBook Air, MacBook Pro
- Flag:
- Contact:
Re: A direct access database system
Exciting to see this coming about. I had to comment out the background jpg load since I don't have the image of course, but very cool.
smart BASIC Rocks!
- Scott : San Diego, California
- Scott : San Diego, California
-
- Posts: 814
- Joined: Tue Apr 09, 2013 12:23 pm
- My devices: iPhone,iPad
Windows - Location: Groningen, Netherlands
- Flag:
Re: A direct access database system
Ahh, this is definitly not going to work. This afternoon i completed the the flexible key mechanism, and we are left with two versions of the program now.
I will post my last version and indicate where i made modifications in yellow. You can then implement the key mechanism in your version and go on with it. The backbone is then completed, and most of the work remaining is the user interface. You're better in that than me. I refert to the wave&sound project (operator is still waiting.. ), but if you encounter any problem, i'll try to be of assistence.
I will post the stuff tomorrow, because marking what i have modified takes some time and it is my bridge evening
I will post my last version and indicate where i made modifications in yellow. You can then implement the key mechanism in your version and go on with it. The backbone is then completed, and most of the work remaining is the user interface. You're better in that than me. I refert to the wave&sound project (operator is still waiting.. ), but if you encounter any problem, i'll try to be of assistence.
I will post the stuff tomorrow, because marking what i have modified takes some time and it is my bridge evening
- sarossell
- Posts: 195
- Joined: Sat Nov 05, 2016 6:31 pm
- My devices: iPad Mini 2, iPhone 5, MacBook Air, MacBook Pro
- Flag:
- Contact:
Re: A direct access database system
I am very grateful for all of your hard work. Just a few short days ago smart BASIC didn't have a working database solution. Thank you so much for such a vital contribution. I've learned quite a bit from your work.
smart BASIC Rocks!
- Scott : San Diego, California
- Scott : San Diego, California
-
- Posts: 814
- Joined: Tue Apr 09, 2013 12:23 pm
- My devices: iPhone,iPad
Windows - Location: Groningen, Netherlands
- Flag:
Re: A direct access database system
Here's an example where the combination Salary + Surname is chosen as key (in that order).
Just by providing an addition parameter "4 1" to the db_create() function.
Just by providing an addition parameter "4 1" to the db_create() function.
-
- Posts: 814
- Joined: Tue Apr 09, 2013 12:23 pm
- My devices: iPhone,iPad
Windows - Location: Groningen, Netherlands
- Flag:
Re: A direct access database system
It has been fun. Next time when we do some project together, better rules must be agreed upon. And i'm not leaving the forumsarossell wrote:I am very grateful for all of your hard work. Just a few short days ago smart BASIC didn't have a working database solution. Thank you so much for such a vital contribution. I've learned quite a bit from your work.