Swordfish Wiki | SwordfishUser / Database browse

Database

Note: If you have downloaded version 4.0.9 or above of the SDFileSystem library module, this module is now redundant as the functionality has been added to the SD library.

This is a simple module to help with the building and use of database files on SD or MMC cards. It implements reading and writing records as structures. It utilises the SDFileSystem library module.

Note: As overwriting of existing files is required, the updated Version 2.0 (or above) of the library module must first be downloaded (latest version at http://www.sfcompiler.co.uk/wiki/pmwiki.php?n=SwordfishUser.SDFileSystemVersion).

The module contains five subroutines/functions:

SetRecordLength - must be called before records are accessed, to declare the size of each record in the file. It should be equal in length to the size of the structure used to access each record. If bits or booleans are used, the number of these should be added and divided by 8, rounding up. 1 should be added for every byte, 2 for every word, 4 for every longword etc... If the structure contains another structure, then the size of this structures should be calculated first, rounding up to a whole number of bytes for each structue.
GetRecord - gets a record from the file. The function fills a structure variable with the record. Note that the record number is zero-based. Note also that the address of the variable, and not the variable itself, must be passed as a parameter.
SetRecord - sets a record in the file. The function writes the contents of a structure variable to the record. Note that the record number is zero-based. Note also that the address of the variable, and not the variable itself, must be passed as a parameter.
NewRecord - creates a new record in the file. The function writes the contents of a structure variable to the record. Note that the address of the variable, and not the variable itself, must be passed as a parameter.
NumberOfRecords - returns the number of records in the file.

The database file must first be opened for read/write, using the OpenFileRW command of the SDFileSystem library module. The file should also be closed at the end using the Close command. See the example code below for more details.

The example below also includes a routine for sorting the records in the database, in this case in ascending order of course name.

Example Code

{
********************************************************************************
*  Name    : DatabaseExample.BAS                                               *
*  Author  : S Wright                                                          *
*  Notice  : Copyright (c) 2007 S Wright                                       *
*          : All Rights Reserved                                               *
*  Date    : 21/10/2007                                                        *
*  Version : 1.0                                                               *
*  Notes   :                                                                   *
*          :                                                                   *
********************************************************************************
}
Device = 18F452
Clock =  40
Config OSC = HSPLL

Include "Convert.bas"
Include "USART.bas"
Include "SDFileSystem.bas"
Include "Database.bas"
Include "String.bas"

Structure THole
   Yards As Word
   Par As Byte
   StrokeIndex As Byte
End Structure

Structure TCourse
   IsDeleted As Boolean
   Index As Byte
   Name As String(20)
   Hole(18) As THole
End Structure

Dim USARTResponse As String(2)

{
********************************************************************************
* Name    : InputCourse                                                        *
********************************************************************************
}   
Sub InputCourse()
Dim Course As TCourse
Dim HoleNumber As Byte
Dim StringEntry As String(20)
   Course.IsDeleted = False
   Course.Index = DB.NumberOfRecords + 1
   USART.Write("Enter Course Name:", 13, 10)
   USART.Read(StringEntry)
   Course.Name = StringEntry
   For HoleNumber = 0 To 17
      USART.Write("Enter Yards for Hole ", DecToStr(HoleNumber + 1), ":", 13, 10)
      USART.Read(StringEntry)
      Course.Hole(HoleNumber).Yards = StrToDec(StringEntry)
   Next
   For HoleNumber = 0 To 17
      USART.Write("Enter Par for Hole ", DecToStr(HoleNumber + 1), ":", 13, 10)
      USART.Read(StringEntry)
      Course.Hole(HoleNumber).Par = StrToDec(StringEntry)
   Next
   For HoleNumber = 0 To 17
      USART.Write("Enter Stroke Index for Hole ", DecToStr(HoleNumber + 1), ":", 13, 10)
      USART.Read(StringEntry)
      Course.Hole(HoleNumber).StrokeIndex = StrToDec(StringEntry)
   Next
   DB.NewRecord(AddressOf(Course))
   USART.Write(13, 10)
End Sub
{
********************************************************************************
* Name    : OutputCourses                                                      *
********************************************************************************
}   
Sub OutputCourses()
Dim Course As TCourse
Dim CourseNumber As Byte
Dim HoleNumber As Byte
   For CourseNumber = 0 To DB.NumberOfRecords() - 1
      DB.GetRecord(CourseNumber, AddressOf(Course))
      If Not Course.IsDeleted Then
         USART.Write("Course Index: ", DecToStr(Course.Index), 13, 10)
         USART.Write("Name: ", Course.Name, 13, 10)
         For HoleNumber = 0 To 17
            USART.Write("Hole: ", DecToStr(HoleNumber + 1),
                        ", Yards: ", DecToStr(Course.Hole(HoleNumber).Yards),
                        ", Par: ", DecToStr(Course.Hole(HoleNumber).Par),
                        ", StrokeIndex: ", DecToStr(Course.Hole(HoleNumber).StrokeIndex), 13, 10)
         Next
         USART.Write(13, 10)
      EndIf
   Next
End Sub
{
********************************************************************************
* Name    : SortCourses                                                        *
********************************************************************************
}   
Sub SortCourses()
Dim Course1, Course2 As TCourse
Dim CourseNumber As Byte
Dim SortOccurred As Boolean
   If DB.NumberOfRecords() > 1 Then
      Repeat
         SortOccurred = False
         For CourseNumber = 0 To DB.NumberOfRecords() - 2
            DB.GetRecord(CourseNumber, AddressOf(Course1))
            DB.GetRecord(CourseNumber + 1, AddressOf(Course2))
            If Str.Compare(Course1.Name, Course2.Name) > 0 Then
               DB.SetRecord(CourseNumber, AddressOf(Course2))
               DB.SetRecord(CourseNumber + 1, AddressOf(Course1))
               SortOccurred = True
            EndIf
         Next      
      Until Not SortOccurred
   EndIf
   usart.write("Courses Sorted:", 13, 10, 13, 10)
End Sub


// Program start...
USART.SetBaudrate(br115200)
USART.ReadTerminator = #13
DelayMS(100)

USART.Write("Insert SD/MMC:", 13, 10)
Repeat
Until SD.Init(spiOscDiv4)

If Not SD.FileExists("COURSES.SDB") Then
   USART.Write("New DB:", 13, 10)
   SD.NewFile("COURSES.SDB")
   SD.CloseFile
EndIf

SD.OpenFileRW("COURSES.SDB")
DB.SetRecordLength(94)

Repeat
   USART.Write("Enter Menu Option: 1 - Add course, 2 - Display all, 3 - Sort, 4 - Save file & exit",
               13, 10, 13, 10)
   USART.Read(USARTResponse)
   Select USARTResponse
      Case "1" InputCourse()
      Case "2" OutputCourses()
      Case "3" SortCourses()
      Case "4" Break
   EndSelect
Until False

SD.CloseFile

USART.Write("Completed:", 13, 10)
DelayMS(1000)
End

Module Code

{
********************************************************************************
*  Name    : Database.BAS                                                      *
*  Author  : S Wright                                                          *
*  Notice  : Copyright (c) 2007 S Wright                                       *
*          : All Rights Reserved                                               *
*  Date    : 21/10/2007                                                        *
*  Version : 1.0                                                               *
*  Notes   :                                                                   *
*          :                                                                   *
*  Uses    : FSR1                                                              *
*          :                                                                   *
********************************************************************************
}
Module DB

Include "SDFileSystem.bas"

Dim RecordLength As Word

{
********************************************************************************
* Name    : SetRecordLength (PUBLIC)                                           *
* Purpose : Sets length of one record (in bytes) - must be called before       *
*         : accessing any records                                              *
********************************************************************************
}   
Public Sub SetRecordLength(pLength As Word)
   RecordLength = pLength
End Sub
{
********************************************************************************
* Name    : GetRecord (PUBLIC)                                                 *
* Purpose : Fills structure starting at pAddressOfStructure with record number *
*         : pRecordNumber (zero-based index).                                  *
*         : Return: errOK = Success, errRWError = Read/Write error,            *
*         : errFileNotOpen = File not open, errBeyondEOF = Trying to seek      *
*         : position beyond end of file                                        *
********************************************************************************
}   
Public Function GetRecord(pRecordNumber As Word, pAddressOfStructure As Word) As Byte
Dim Index As Word
Dim ByteRead As Byte
   Result = SD.FSeek(pRecordNumber * RecordLength)
   If Result = errOK Then
      Index = 0
      Repeat
         ByteRead = SD.ReadByte
         FSR1 = pAddressOfStructure + Index
         POSTINC1 = ByteRead
         Inc(Index)
      Until Index = RecordLength
   EndIf
End Function
{
********************************************************************************
* Name    : SetRecord (PUBLIC)                                                 *
* Purpose : Fills record number pRecordNumber (zero-based index) with          *
*         : structure starting at pAddressOfStructure.                         *
*         : Return: errOK = Success, errRWError = Read/Write error,            *
*         : errFileNotOpen = File not open, errBeyondEOF = Trying to seek      *
*         : position beyond end of file                                        *
********************************************************************************
}   
Public Function SetRecord(pRecordNumber As Word, pAddressOfStructure As Word) As Byte
Dim Index As Word
   Result = SD.FSeek(pRecordNumber * RecordLength)
   If Result = errOK Then
      Index = 0
      Repeat
         FSR1 = pAddressOfStructure + Index
         SD.Write(POSTINC1)
         Inc(Index)
      Until Index = RecordLength
   EndIf
End Function
{
********************************************************************************
* Name    : NewRecord (PUBLIC)                                                 *
* Purpose : Creates new record and fills with pRecord.                         *
*         : Return: errOK = Success, errRWError = Read/Write error,            *
*         : errFileNotOpen = File not open                                     *
********************************************************************************
}   
Public Function NewRecord(pAddressOfStructure As Word) As Byte
Dim Index As Word
   Result = SD.FSeek(SD.FileSize)
   If Result = errOK Then
      Index = 0
      Repeat
         FSR1 = pAddressOfStructure + Index
         SD.Write(POSTINC1)
         Inc(Index)
      Until Index = RecordLength
   EndIf
End Function
{
********************************************************************************
* Name    : NumberOfRecords (PUBLIC)                                           *
* Purpose : Returns the number of records in the database (ensure that         *
*         : SerRecordLength has been called first)                             *
********************************************************************************
}   
Public Function NumberOfRecords() As Word
   Result = SD.FileSize / RecordLength
End Function