Simple database implementation

General FreeBASIC programming questions.
badidea
Posts: 1461
Joined: May 24, 2007 22:10
Location: The Netherlands

Simple database implementation

Postby badidea » Aug 19, 2018 22:20

Edit: Ignore this post, have to re-think it...

After reading Gablea's trouble with csv-files as a database I started playing with a 'flexible' database implementation using random access files.

Ideally, for a database one would have the definition of the data variables (type, length) independent form the code that manages the data. To create this in freebasic seems rather complex to me and probably wiser to use an actual database tool (e.g. mysql or something lighter).

So to keep things simple, I define the data variables in code (in dbItem_type) and have a separate type that handles the storage (in memory and on disk) called dbManager_type. The dbManager does not know what data types are in dbItem_type.

There is however a problem. What if I want to handle multiple dbItem_types (lets say a dbItem1_type and a dbItem2_type). How to change the manager to work with multiple types?

Code so far (creates file dbItems.bin):

Code: Select all

'-------------------------------------------------------------------------------

const as integer MAX_DES_LEN = 63

type dbItem_type field = 1
   private:
   dim as longint id 'identifier
   dim as string * MAX_DES_LEN des 'description
   dim as long qty 'quantity
   public:
   declare constructor()
   declare constructor(id as longint, des as string, qty as long)
   declare function show() as integer
   declare function create(id as longint, des as string, qty as long) as integer
end type

constructor dbItem_type()
   this.des = ""
   this.id = -1
   this.qty = 0
end constructor

constructor dbItem_type(id as longint, des as string, qty as long)
   if len(des) > MAX_DES_LEN then
      this.des = left(des, MAX_DES_LEN)
   else
      this.des = des
   end if
   this.id = id
   this.qty = qty
end constructor

function dbItem_type.show() as integer
   print str(id), trim(des), str(qty)
   return 0
end function

'-------------------------------------------------------------------------------

'Todo: use linked list, dymanic number of records

const as integer MAX_DB_ITEMS = 10

type dbManager_type
   private:
   dim as integer numItems
   dim as dbItem_type dbItems(MAX_DB_ITEMS-1)
   public:
   declare function add(dbItem as dbItem_type) as integer
   declare function empty() as integer
   declare function show() as integer
   declare function diskSaveAppend(fileName as string) as integer
   declare function diskSaveNew(fileName as string) as integer
   declare function diskLoad(fileName as string) as integer
   'todo: Delete
   'todo: Update
   'todo: Find
   'todo: ...
end type

function dbManager_type.add(dbItem as dbItem_type) as integer
   if numItems >= MAX_DB_ITEMS then return -1
   dbItems(numItems) = dbItem
   numItems += 1
   return numItems
end function

function dbManager_type.empty() as integer
   numItems = 0
   return numItems
end function

function dbManager_type.show() as integer
   for i as integer = 0 to numItems - 1
      dbItems(i).show()
   next
   return 0
end function

'todo: check file does not exist?
function dbManager_type.diskSaveNew(fileName as string) as integer
   dim as integer fileNum = freefile
   if open(fileName, for random, access write, as #fileNum, len = sizeof(dbItem_type)) <> 0 then
      return -1
   else
      for i as integer = 0 to numItems - 1
         put #1, , dbItems(i)
      next
      close fileNum
   end if
   return 0
end function

function dbManager_type.diskSaveAppend(fileName as string) as integer
   dim as integer recordNum, fileNum = freefile
   if open(fileName, for random, access read write, as #fileNum, len = sizeof(dbItem_type)) <> 0 then
      return -1
   else
      recordNum = (LOF(fileNum) \ sizeof(dbItem_type)) + 1
      for i as integer = 0 to numItems - 1
         put #1, recordNum, dbItems(i)
         recordNum += 1
      next
      close fileNum
   end if
   return 0
end function

function dbManager_type.diskLoad(fileName as string) as integer
   dim as integer fileNum = freefile, ret = 0
   if open(fileName for random, access read, as #fileNum, len = sizeof(dbItem_type)) <> 0 then
      return -1
   else
      numItems = 0
      while not eof(fileNum)
         get #1, , dbItems(numItems)
         numItems += 1
         if numItems >= MAX_DB_ITEMS then
            ret = -2
            exit while
         end if
      wend
      close fileNum
   end if
   return ret
end function

'-------------------------------------------------------------------------------

dim as dbManager_type dbManager
dim as dbItem_type dbItem = dbItem_type(1234, "FreeBASIC", 1)

dbManager.add(dbItem)
dbManager.add(type(-1, "Deleted", 99)) 'add temperory item
dbManager.add(type(2345, "Test123 Test123 Test123 long text exceeding 63 bytes Test123 Test123", 10)) 'one more
'dbManager.diskSaveAppend("dbItems.bin")
dbManager.diskSaveNew("dbItems.bin")
dbManager.empty()
if dbManager.diskLoad("dbItems.bin") <> 0 then print "diskLoad: somthing went wrong"
dbManager.show()

'-------------------------------------------------------------------------------
badidea
Posts: 1461
Joined: May 24, 2007 22:10
Location: The Netherlands

Re: Simple database implementation

Postby badidea » Aug 20, 2018 22:08

Restart, start simple now:

Code: Select all

'-------------------------------------------------------------------------------

function diskSaveNew(fileName as string, pData as any ptr, dataSize as integer, numItems as integer) as integer
   dim as integer fileNum = freefile
   if open(fileName, for random, access write, as #fileNum, len = dataSize) <> 0 then
      return -1
   else
      for i as integer = 0 to numItems - 1
         put #1, , *cast(ubyte ptr, pData), dataSize
         pData += dataSize
      next
      close fileNum
   end if
   return 0
end function

function diskLoad(fileName as string, pData as any ptr, dataSize as integer, maxItems as integer) as integer
   dim as integer numItems, fileNum = freefile
   if open(fileName for random, access read, as #fileNum, len = dataSize) <> 0 then
      return -1
   else
      numItems = 0
      while not eof(fileNum)
         get #1, , *cast(ubyte ptr, pData), dataSize
         pData += dataSize
         numItems += 1
         if numItems >= maxItems then
            exit while
         end if
      wend
      close fileNum
   end if
   return numItems
end function

'-------------------------------------------------------------------------------

type dbItem1_type field = 1
   dim as longint id 'identifier
   dim as string * 63 des 'description
   dim as long qty 'quantity
end type

const as integer MAX_DB1_ITEMS = 10
dim as dbItem1_type DB1(MAX_DB1_ITEMS-1)

print "DB1:"
'some data
DB1(0).id = 1234 : DB1(0).des = "Freebasic" : DB1(0).qty = 10
DB1(1).id = -1 : DB1(1).des = "Test123" : DB1(0).qty = -1
'save this
diskSaveNew("DB1_items.bin", @DB1(0), sizeof(DB1(0)), 2)
'reset data
DB1(0).id = 0 : DB1(0).des = "-" : DB1(0).qty = 0
DB1(1).id = 0 : DB1(1).des = "-" : DB1(0).qty = 0
'load data
print diskLoad("DB1_items.bin", @DB1(0), sizeof(DB1(0)), 2)
'print data
print DB1(0).id, trim(DB1(0).des), DB1(0).qty
print DB1(1).id, trim(DB1(1).des), DB1(1).qty

'-------------------------------------------------------------------------------

type dbItem2_type field = 1
   dim as string * 63 person
   dim as string * 63 address
   dim as string * 31 phone
end type

const as integer MAX_DB2_ITEMS = 20
dim as dbItem2_type DB2(MAX_DB2_ITEMS-2)

print "DB2:"
'some data
DB2(0).person = "John Doe" : DB2(0).address = "Street xzy" : DB2(0).phone = "1234-56789"
DB2(1).person = "A" : DB2(1).address = "B" : DB2(1).phone = "C"
'save this
diskSaveNew("DB2_items.bin", @DB2(0), sizeof(DB2(0)), 1) ' save 1
'reset data
DB2(0).person = "-" : DB2(0).address = "-" : DB2(0).phone = "-"
DB2(1).person = "-" : DB2(1).address = "-" : DB2(1).phone = "-"
'load data
print diskLoad("DB2_items.bin", @DB2(0), sizeof(DB2(0)), 2) ' try to load 2 items
'print data
print trim(DB2(0).person), trim(DB2(0).address), trim(DB2(0).phone)
print trim(DB2(1).person), trim(DB2(1).address), trim(DB2(1).phone)
RockTheSchock
Posts: 220
Joined: Mar 12, 2006 16:25

Re: Simple database implementation

Postby RockTheSchock » Aug 22, 2018 9:01

As you stated in your first post:
badidea wrote:Ideally, for a database one would have the definition of the data variables (type, length) independent form the code that manages the data. To create this in freebasic seems rather complex to me and probably wiser to use an actual database tool (e.g. mysql or something lighter).
If you wanna continue making a dbms just for fun, here just my two cents...
data structure on disk
- distinguish between table defintion and data store
- files for dynamic sized datatypes (texts,blobs)
- index files for faster search

you could for example store each table in a directory and there for each column of the table you have one file plus one file for the tabledefinition itself. So you are able to rename,insert or drop table columns easily.

interface
- functions to create / read / modify / drop a table definition (schema)
- functions to read/search/insert/delete records

Your simple solution can only be used for simple cases where you wanna store data of which you know the structure before and which is not gonna change.
badidea
Posts: 1461
Joined: May 24, 2007 22:10
Location: The Netherlands

Re: Simple database implementation

Postby badidea » Aug 22, 2018 18:20

RockTheSchock wrote:Your simple solution can only be used for simple cases where you wanna store data of which you know the structure before and which is not gonna change.

Yes, I am still considering whether this is a useful exercise. Also the use of random access instead a just binary seems a bit pointless here.
paul doe
Posts: 919
Joined: Jul 25, 2017 17:22
Location: Argentina

Re: Simple database implementation

Postby paul doe » Aug 23, 2018 3:46

badidea wrote:Yes, I am still considering whether this is a useful exercise.

It is. General code to serialize arbitrary data is a very useful thing to have. I'll contribute something to this exercise soon enough ;)
badidea wrote:Also the use of random access instead a just binary seems a bit pointless here.

Indeed, you'll be better off just using binary files. Oh! I just remembered: have a look at this thread (maybe you remembered it too), it contains some useful info that I'd rather not forget =D
badidea
Posts: 1461
Joined: May 24, 2007 22:10
Location: The Netherlands

Re: Simple database implementation

Postby badidea » Aug 23, 2018 23:28

Trying more things... getting ugly. Casting fun at the end.

Code: Select all

#include "crt/string.bi"

'-------------------------------------------------------------------------------

type dbTable_type
   'private:
   dim as integer maxRecords, numRecords, recordSize
   dim as any ptr pData
   'public:
   declare function init(maxRec as integer, recSize as integer) as integer
   declare function add(pRecord as any ptr) as integer
   declare function diskSaveNew(fileName as string) as integer
   declare function diskLoad(fileName as string) as integer
   declare function getRec(recNum as integer) as any ptr
end type

function dbTable_type.init(maxRec as integer, recSize as integer) as integer
   maxRecords = maxRec
   recordSize = recSize
   pData = callocate(maxRecords, recordSize)
   numRecords = 0
   return numRecords
end function

function dbTable_type.add(pRecord as any ptr) as integer
   if numRecords >= maxRecords then return -1
   memcpy(pData + numRecords * recordSize, pRecord, recordSize)
   numRecords += 1
   return numRecords
end function

function dbTable_type.diskSaveNew(fileName as string) as integer
   dim as integer fileNum = freefile
   if open(fileName, for random, access write, as #fileNum, len = recordSize) <> 0 then
      return -1
   else
      for i as integer = 0 to numRecords - 1
         put #1, , *cast(ubyte ptr, pData), recordSize
         pData += recordSize
      next
      close fileNum
   end if
   return 0
end function

function dbTable_type.diskLoad(fileName as string) as integer
   dim as integer fileNum = freefile
   if open(fileName for random, access read, as #fileNum, len = recordSize) <> 0 then
      return -1
   else
      numRecords = 0
      while not eof(fileNum)
         get #1, , *cast(ubyte ptr, pData + numRecords * recordSize), recordSize
         numRecords += 1
         if numRecords >= maxRecords then
            exit while
         end if
      wend
      close fileNum
   end if
   return numRecords
end function

function dbTable_type.getRec(recNum as integer) as any ptr
   if recNum >= numRecords then return 0
   return pData + recNum * recordSize
end function

'-------------------------------------------------------------------------------

type dbItem1_type field = 1
   dim as longint id 'identifier
   dim as string * 63 des 'description
   dim as long qty 'quantity
   declare sub show()
end type

sub dbItem1_type.show()
   print id, trim(des), qty
end sub

type dbItem2_type field = 1
   dim as string * 63 person
   dim as string * 63 address
   dim as string * 31 phone
end type

'-------------------------------------------------------------------------------

dim as dbTable_type dbTable1

dbTable1.init(10, sizeof(dbItem1_type)) 'max 10 records
dbTable1.add(@type<dbItem1_type>(1234, "FreeBasic", -1))
dbTable1.add(@type<dbItem1_type>(777, "Test123", 100))

'dbTable1.diskLoad("dbTable1.bin")

'print 1st item
print cast(dbItem1_type ptr, dbTable1.getRec(0))->id,
print trim(cast(dbItem1_type ptr, dbTable1.getRec(0))->des),
print cast(dbItem1_type ptr, dbTable1.getRec(0))->qty
'or with a show method
cast(dbItem1_type ptr, dbTable1.getRec(0))->show()

'print 2nd item
print cast(dbItem1_type ptr, dbTable1.getRec(1))->id,
print trim(cast(dbItem1_type ptr, dbTable1.getRec(1))->des),
print cast(dbItem1_type ptr, dbTable1.getRec(1))->qty
'or with pointer
dim as dbItem1_type ptr pItem = dbTable1.getRec(1)
print pItem->id, trim(pItem->des), pItem->qty

dbTable1.diskSaveNew("dbTable1.bin")

'Forgot to free the data!

Edit: Code above updated

Return to “General”

Who is online

Users browsing this forum: No registered users and 9 guests