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.
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.
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
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. |
ConstructorThe constructor sets initial values for all private variables. Also creates three private dictionaries:
|
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 |
DestructorThis 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 |
Load database from fileSetting the file name property runs this subroutine.
|
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 |
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.
|
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 |
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.
|
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 |
Class Design - Specific properties
This section contains properties specific to the file database class.
Property - file_nameThe file_name property sets a database file name to use. |
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. |
Public Property Get file_name() file_name = db_file_name 'Return file name End Property |
Property - buffer_sizeThe buffer_size property sets the buffer size to use. |
Public Property Let buffer_size(size) user_buff_size = size End Property |
This property is readable, allowing the buffer size set to be retrieved. |
Public Property Get buffer_size buffer_size = user_buff_size End Property |
Class Design - Specific methods
This section contains methods specific to the file database class.
Method - listThis 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 |
Method - cleanThis 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 |
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 - CountReturns 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 |
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 |
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 |
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 |
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.
|
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 |
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 |
Method - Items()This method returns an array of Items from the main database dictionary. |
Public Function Items() Items = main_db.Items End Function |
Method - Keys()This method returns an array of Keys from the main database dictionary. |
Public Function Keys() Keys = main_db.Keys End Function |
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.
|
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 |
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.