CGI: VBScript File DB Class

VBScript and JavaScript CGI

VBScript File DB Class

This page covers a working file database class consolidating material covered on pages VBScript dictionary - DB and VBScript dictionary file. These outlined a file database using the VBScript scripting dictionary. Although the proposed solution for hard disk use is viable it suffers from slow write times when used on USB memory devices . This class provides an easy to use user interface and resolves the slow write issue.

Issues

Slow USB write

Hard disks have around equal read and write times (low-end 60MB/s) while USB memory sticks are asymmetric with low-end read/write times (18MB/s and 8MB/s). These times are insignificant if the data to be stored is relatively small (<10MB) above this you are talking seconds to write a file to a USB memory stick.

In the solution previously covered a database that changes only a few bytes requires the entire database to be written to a file. This clearly is not an ideal solution. The changes (additions) to the database could be appended to the file. However if the changes were deletions again the entire database needs to be rewritten.

Solution

Instead of saving a clean database, save only the changes required for building that database. These changes are tagged with a marker indicating an addition or deletion. Hence all changes require only a file append operation. Data can be updated in either single units or blocks, a class must cater for both.

Top

Class specification

1) Database File Name:
After a component has been created first operation is to set a database file name. This caters for multi-databases and initiates a database load from file.

2) Dictionaries required:
Resolving the above issues three dictionaries are required a main database main DB and two buffers add buffer and delete buffer. Each buffer has an associated subroutine. The add subroutine writes content from add buffer to the main DB and appends add-instructions to the database file. The delete subroutine removes data defined in the delete buffer from the main database and appends delete-instructions to the database file.

Each buffer stores a data unit (key-item pair) default is a single unit or a record consisting of multi-data units. Default record size is one this can be changed using a property. Buffer subroutines are automatically run on reaching either the default or predefined record size.

3) Properties and Methods
Preferably all properties and methods defined for a VBScript Dictionary shall be replicated in the File DB Class. Additional properties and methods specific to the File DB Class class shall allow listing of the entire database dictionary (key and items) and saving that dictionary to a time stamped backup file.

Top

Class design

The following provides a detailed description for each class block design.

File structure

A database file contains all changes made to the database dictionary, this file is used for recreating a database. Each line in the file has the following format, a marker indicating a specific instruction followed by an encoded key and encoded item, each separated by a comma. The following markers are used:

a - Add key and item pair to dictionary using obj.Add key,item
d - Delete key and item pair from dictionary using obj.Remove key,item
i - Create new item or update existing item using obj.Item(key) = item
k - Rename a key using obj.Key(old_key) = new_key

Top

Internal variables

The following private variables are used.

Variables	Comments;
db_file_name	File name (including extension) for database. Specify either a name or full path including name.
buff_size_count	Increment counter, tracks buffer size. On reaching user buffer (or default) size initiates running of appropriate buffer subroutine.
user_buff_size	User settable record size (default one) defines buffer size.

Top

Constructor

The constructor sets initial values for all private variables.

Also creates three private dictionaries:

Main database
Add buffer
Delete buffer

 Private Sub Class_Initialize()                       'Set inital
   db_file_name    = "" 'No default
   buff_size_count = 0  'Buffer size counter
   user_buff_size  = 1  'Default record size

   Set main_db  = CreateObject("Scripting.Dictionary")'Main database
   Set add_buff = CreateObject("Scripting.Dictionary")'Add buffer
   Set del_buff = CreateObject("Scripting.Dictionary")'Delete buffer
 End Sub

Destructor

This subroutine cleans up any items before a component is destroyed. In particular it ensures the database file is closed.

 Private Sub Class_Terminate 'Standard method when class destroyed
   On Error Resume Next      'File may already be closed.       
   dbFile.Close              'This ensures file really is closed.
 End Sub

Top

Load database from file

Setting the file name property runs this subroutine.

The database file is opened for reading and read line-by-line using a while loop.

Note: The last line in a database file contains no data and cannot be split. An attempt to do so produces a run time error. A blank line on detection, results in the database file closing and the subroutine terminating (Exit Sub).

A valid line is split on the comma-separator into an array. First element of this array split_line(0) contains an instruction marker.

A select case statement uses split_line(0) to determine appropriate action to take.

Private Sub load_main_db()
  Dim fso,dbFile,line,split_line
  Dim decoded_key,decoded_item,decoded_keyn
  Set fso=CreateObject("Scripting.FileSystemObject") 'File obj
  Set dbFile=fso.OpenTextFile(db_file_name,1,true)   'Open for read

  While Not dbFile.AtEndOfStream              'Read file
    line = dbFile.ReadLine()                  'Read line
    If line="" Then
      dbFile.Close                            'Close file
      Exit Sub                                'Nothing else to do
    End If

    split_line = Split(line,",")              'Split at seperator
    Select Case split_line(0)                 'Check marker
    Case "a"                                  'Marker add to db
     decoded_key  = Unescape(split_line(1))   'Decode key
     decoded_item = Unescape(split_line(2))   'Decode Item
     main_db.Add decoded_key,decoded_item     'Add entry to db

    Case "d"                                  'Marker delete from db
     decoded_key  = Unescape(split_line(1))   'Decode key
     main_db.Remove decoded_key               'Delete entry

    Case "i"                                  'Marker edit item
     decoded_key  = Unescape(split_line(1))   'Decode key
     decoded_item = Unescape(split_line(2))   'Decode Item
     main_db.Item(decoded_key) = decoded_item 'Edit key item

    Case "k"                                  'Marker edit key
     decoded_key  = Unescape(split_line(1))   'Decode key
     decoded_keyn = Unescape(split_line(2))   'Decode new key
     main_db.Key(decoded_key) = decoded_keyn  'Change key
 
    End Select 
  Wend 'End read file
  dbFile.Close                                'Close file
 End Sub

Top

Add buffer to main DB and update database file

The add-buffer stores key-item pairs. When this subroutine is run these are written to the main database and database file updated.

First the database file is opened for appending data.

All add-buffer keys are read into an array. This array is iterated through using a for-each loop.

A key-item pair may already exist in the main database in this case only the item requires changing. To achieve this the key-item pair requires deleting and a new add entry made. This delete action is an operation on the main database and requires adding to the database file. The line is assembled starting with the delete marker d followed by a comma and then the encoded key.

A new entry is added to the main database using main_db.Add. Before writing to the database file, using the escape function both key and item are encoded . These are assembled into a line starting with the add marker a. After closing the database file the buffer is cleared using add_buff.RemoveAll

Sub add_buff_sub()                           'Add content to main DB
   Dim key_array,strKey,encode1,encode2
   Dim fso,dbFile

   Set fso=CreateObject("Scripting.FileSystemObject") 'File obj
   Set dbFile=fso.OpenTextFile(db_file_name,8,false)  'Open for append

   key_array = add_buff.Keys                        'Get all Keys
   For Each strKey in key_array                     'Scan array
    If main_db.Exists(strKey) Then                  'Check key exists
      main_db.Remove strKey                         'yes delete entry
      encode1 =  escape(strKey)                     'encode key  
      dbFile.WriteLine "d," & encode1               'Save to file
    End If
    main_db.Add strKey, add_buff.Item(strKey)       'Add data unit to main db
    encode1 =  escape(strKey)                       'encode key  
    encode2 =  escape(add_buff.Item(strKey))        'encode Item
    dbFile.WriteLine "a," & encode1 & "," & encode2 'Save to file
   Next                                             'Get next line

   dbFile.Close                                     'Close file
   add_buff.RemoveAll                               'Clear buffer
 End Sub

Top

Delete buffer remove from main DB and update database file

The delete-buffer stores key-(blank)item pairs. When this subroutine is run they are removed from the main database and database file updated.

First the database file is opened for appending data.

All delete-buffer keys are read into an array. This array is iterated through using a for-each loop.

Each key must exist before it can be deleted. With this confirmed the key-item pair is removed from the main database. This delete action is an operation on the main database and requires adding to the database file. The line is assembled starting with the delete marker “d” followed by a comma and then the encoded key. After closing the database file the buffer is cleared using del_buff.RemoveAll

Sub del_buff_sub()                           'Delete content from main DB
   Dim key_array,strKey,encode1
   Dim fso,dbFile

   Set fso=CreateObject("Scripting.FileSystemObject") 'File obj
   Set dbFile=fso.OpenTextFile(db_file_name,8,false)  'Open for append

   key_array = del_buff.Keys                        'Get all Keys
   For Each strKey in key_array                     'Scan array
    If main_db.Exists(strKey) Then                  'Check key exists
      main_db.Remove strKey                         'yes delete entry
      encode1 =  escape(strKey)                     'encode key  
      dbFile.WriteLine "d," & encode1               'Save to file
    End If
   Next                                             'Get next line

   dbFile.Close                                     'Close file
   del_buff.RemoveAll                               'Clear buffer
 End Sub

Top

Class Design - Specific properties

This section contains properties specific to the file database class.

Property - file_name

The file_name property sets a database file name to use.
For example obj.file_name = "test_db.txt"

 Public Property Let file_name(name)
   db_file_name = name                'Set file name variable
   load_main_db                       'Load file into database 
 End Property

This property is readable, allowing the file name set to be retrieved.
For example file = obj.file_name

Public Property Get file_name()
   file_name = db_file_name           'Return file name
 End Property

Top

Property - buffer_size

The buffer_size property sets the buffer size to use.
For example obj.buffer_size = "3"
Note: Buffer size is equivalent to a record size.

 Public Property Let buffer_size(size)
   user_buff_size = size
 End Property

This property is readable, allowing the buffer size set to be retrieved.
For example size = buffer_size

 Public Property Get buffer_size
   buffer_size = user_buff_size
 End Property

Top

Class Design - Specific methods

This section contains methods specific to the file database class.

Method - list

This subroutine allows all data (index-item pairs) contained in the main database dictionary to be listed. Each line is output in HTML format.

Note: It is included only for testing.

Public Sub list()
   Dim key_array, strKey
   key_array = main_db.Keys                           'Get all Keys
   For Each strKey in key_array                       'Scan array
     Wscript.Echo strKey & " = " & main_db.Item(strKey) & "<br />"
   Next
 End Sub

Top

Method - clean

This function writes the main database dictionary to a time stamped file.

Format as shown: 1315777171.txt

It provides a clean database file containing only added items. Use this file when the main file becomes large and cluttered with old transactions.

 Public Function clean()
  Dim key_array,strKey,encode1,encode2
  Dim fso,dbFile,clean_name  

   'Create file with time stamp
   clean_name = DateDiff("s", "01/01/1970 00:00:00", Now()) & ".txt"

   Set fso=CreateObject("Scripting.FileSystemObject") 'File obj
   Set dbFile=fso.OpenTextFile(clean_name,2,true)   'Open for append

   key_array = main_db.Keys                         'Get all Keys
   For Each strKey in key_array                     'Scan array
    encode1 =  escape(strKey)                       'encode key  
    encode2 =  escape(main_db.Item(strKey))         'encode Item
    dbFile.WriteLine "a," & encode1 & "," & encode2 'Save to file
   Next                                             'Get next line
   dbFile.Close                                     'Close file
  clean = True
 End Function

Top

Class Design - Replicated dictionary properties

This section contains replicated dictionary properties. These have either a one to one relationship or are tailored for the file database class.

Property - Count

Returns number of key-item pairs contained in the main database dictionary.

 Public Property Get Count() 
   Count = main_db.Count         'Get number of key/item pairs         
 End Property

Top

Property - Item(key)

Returns Item value using its key. Data is extracted directely from the main database dictionary.

 Public Property Get Item(key) 
   Item = main_db.Item(key)      'Get Item value                       
 End Property

Top

Property - Item(Key,Item_value)

Sets a item value using its key.

A new key-item pair is added if the key does not exist or updates an exiting item if the key exists.

The transaction is appended to the database file with a maker i both key and item are encoded.

 Public Property Let Item(Key,Item_value) 
   Dim encode1,encode2
   Dim fso,dbFile

   main_db.Item(key) = Item_value                     'Set Item value
   Set fso=CreateObject("Scripting.FileSystemObject") 'File obj
   Set dbFile=fso.OpenTextFile(db_file_name,8,false)  'Open for append

   encode1 =  escape(key)                             'encode key  
   encode2 =  escape(Item_value)                      'encode Item
   dbFile.WriteLine "i," & encode1 & "," & encode2    'Save to file
   dbFile.Close                                       'Close file
 End Property

Top

Property - Key(oldkey,newkey)

Renames an existing key.

The transaction is appended to the database file with a maker k both old and new keys are encoded.

 Public Property Let Key(oldkey,newkey) 
   Dim encode1,encode2
   Dim fso,dbFile

   main_db.Key(oldkey) = newkey                       'Set new key
   Set fso=CreateObject("Scripting.FileSystemObject") 'File obj
   Set dbFile=fso.OpenTextFile(db_file_name,8,false)  'Open for append

   encode1 =  escape(oldkey)                          'encode key  
   encode2 =  escape(newkey)                          'encode new key
   dbFile.WriteLine "k," & encode1 & "," & encode2    'Save to file

   dbFile.Close                                       'Close file
 End Property

Top

Class Design - Replicated dictionary methods

This section contains replicated dictionary methods. These have either a one to one relationship or are tailored for the file database class.

Method - Add(key,item)

This method adds a Key-Item pair to the add buffer. If the key already exists it is first deleted and the new pair added.

The buffer counter is tested against the record size (buffer size). If equal the subroutine add_buff_sub is run and the counter reset.

Public Sub Add(key,item)
    If add_buff.Exists(key) Then            'Check key exists in buffer
      add_buff.Remove key                   'yes delete entry from buffer
    End If
   add_buff.Add key,item                    'Add new item to buffer

   buff_size_count = buff_size_count + 1    'Increment buff counter

   If buff_size_count = user_buff_size Then 'Is max record value reached
     add_buff_sub                           'Add data to DB and DB file
     buff_size_count = 0                    'Reset counter
   End If
 End Sub

Top

Method - Exists(key)

This method tests for a keys existence. If a key exists returns true otherwise returns false.

 Public Function Exists(key)
    If main_db.Exists(key) Then                                        
      Exists = True
    Else
      Exists = False
    End If
 End Function

Top

Method - Items()

This method returns an array of Items from the main database dictionary.

 Public Function Items()
    Items = main_db.Items                                              
 End Function

Top

Method - Keys()

This method returns an array of Keys from the main database dictionary.

 Public Function Keys()
    Keys = main_db.Keys                                                
 End Function

Top

Method - Remove(key)

This method adds a Key-Item pair to be the delete buffer. If the key already exists it is first deleted and the new pair added.

The buffer counter is tested against the record size (buffer size). If equal the subroutine del_buff_sub is run and the counter reset.

 Public Sub Remove(key)
   If del_buff.Exists(key) Then            'Check key exists in delete buffer
     dell_buff.Remove key                  'yes delete entry from buffer
   End If
   del_buff.Add key,""                     'Add new item to buffer
   buff_size_count = buff_size_count + 1   'Increment buff counter

  If buff_size_count = user_buff_size Then 'Is max record value reached
    del_buff_sub                           'Remove data from DB and add to DB file
    buff_size_count = 0                    'Reset counter
  End If
 End Sub

Top

Summary

The above provided a detailed look at properties and methods for the file DB class. Generally you do not need to use all of these in a project they offer flexibility and are there should you need to use that particular functionality.

On the next page a complete listing is provided for the class.

Where to next

Next page summaries the file DB class properties and method and a code listing for this class.

Top