Handling numerous speedwalks - and using a database

ADOX, DBEngine or ODBC Control Panel
|
\____Create
        |
        V
      Driver _____  (Mydata.MDB is created here.)
                 _\|
      Driver <--> Data Source (Mydata.MDB)
        ^
        |
        V
 ____Connect to Data Source
/
ADODB and ODBC


And database actually looks like:

Data source (Mydata.MDB)
|
|-Table (MyTable) - This is what ADODB creates and contains the 'database'.
|     \
|      Records
|
|-Table (FredsTable)
|
Etc.

const adBigInt = 20 
const adBinary = 128 
const adBoolean = 11 
const adBSTR = 8 
const adChapter = 136 
const adChar = 129 
const adCurrency = 6 
const adDate = 7 
const adDBDate = 133 
const adDBTime = 134 
const adDBTimeStamp = 135 
const adDecimal = 14 
const adDouble = 5 
const adEmpty = 0 
const adError = 10 
const adFileTime = 64 
const adGUID = 72 
const adIDispatch = 9 
const adInteger = 3 
const adIUnknown = 13 
const adLongVarBinary = 205 
const adLongVarChar = 201 
const adLongVarWChar = 203 
const adNumeric = 131 
const adPropVariant = 138 
const adSingle = 4 
const adSmallInt = 2 
const adTinyInt = 16 
const adUnsignedBigInt = 21 
const adUnsignedInt = 19 
const adUnsignedSmallInt = 18 
const adUnsignedTinyInt = 17 
const adUserDefined = 132 
const adVarBinary = 204 
const adVarChar = 200 
const adVariant = 12 
const adVarNumeric = 139 
const adVarWChar = 202 
const adWChar = 130 

sub CreateDatabase
Dim catDB 
Set catDB = CreateObject ("ADOX.Catalog")
catDB.Create "Provider=Microsoft.Jet.OLEDB.4.0;" & _
              "Data Source=c:\mushclient\mydb.mdb;" & _
              "Jet OLEDB:Engine Type=5;"

Set catDB = Nothing
end sub

sub CreateTable
dim catDB
dim tblNew
Set catDB = CreateObject ("ADOX.Catalog")

 ' Open the catalog.
 catDB.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=c:\mushclient\mydb.mdb"

 Set tblNew = CreateObject ("ADOX.Table")
  With tblNew
   ' Create a new Table object.
   .Name = "Contacts"
    ' Create fields and append them to the 
    ' Columns collection of the new Table object.
     With .Columns
       .Append "FirstName", adVarWChar 
       .Append "LastName", adVarWChar 
       .Append "Phone", adVarWChar 
       .Append "Notes", adLongVarWChar 
     End With
  End With
  ' Add the new Table to the Tables collection of the database.
 catDB.Tables.Append tblNew
   
Set tblNew = nothing
Set catDB = Nothing
end sub

sub AddRecordTable (first, last, phone, notes)
dim db
Set db = CreateObject ("ADODB.Connection")

 ' Open the connection
db.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
          "Data Source=c:\mushclient\mydb.mdb"

db.Execute "INSERT INTO Contacts (FirstName, LastName," & _
      "Phone, Notes) VALUES (" & _
          """" & first & """, " & _
          """" & last & """, " & _
          """" & phone & """, " & _
          """" & notes & """ );"         

db.Close
Set db = Nothing
end sub

INSERT INTO Contacts (FirstName, LastName, Phone, Notes)
       VALUES ("John", "Smith", "7777 2222", "a person")

sub ShowRecords
dim db, rst
Set db = CreateObject ("ADODB.Connection")
Set rst = CreateObject ("ADODB.Recordset")

  ' Open the connection
  db.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
           "Data Source=c:\mushclient\mydb.mdb"

  ' Open the Recordset
  rst.Open "SELECT * FROM contacts", db

  ' display each record
  Do Until rst.EOF

    ' display each field      
    For Each fld In rst.Fields
       world.tell fld.Value & ";"
    Next

    world.note ""  ' newline

    rst.MoveNext
  
  Loop

db.Close

Set rst = Nothing
Set db = Nothing

end sub

mysql> explain event;
+----------+--------------+------+-----+------------+----------------+
| Field    | Type         | Null | Key | Default    | Extra          |
+----------+--------------+------+-----+------------+----------------+
| event_id | int(11)      |      | PRI | NULL       | auto_increment |
| edate    | date         |      |     | 0000-00-00 |                |
| etime    | time         | YES  |     | NULL       |                |
| comment  | varchar(255) |      |     |            |                |
+----------+--------------+------+-----+------------+----------------+

sub CreateTable2
dim db
Set db = CreateObject ("ADODB.Connection")

 ' Open the connection
db.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
          "Data Source=c:\mushclient\mydb.mdb"

db.Execute _
      "CREATE TABLE event (" & _
      "  event_id int NOT NULL IDENTITY," & _
      "  edate date NOT NULL," & _
      "  etime time default NULL," & _
      "  comment varchar(255) NOT NULL default ''," & _
      "  PRIMARY KEY  (event_id)" & _
      ")"

db.Close
Set db = Nothing
end sub

function find (name)
  dim names, walks 'Storing your location names and speed walks.
  names = split(world.getvariable("???"),",") 'What ever ones you use.
  walks = split(world.getvariable("???"),",")
  w
  fd = false
  st = 0
  en = ubound(record)
  do until fd or st = en
    cr = st + int((en-st)/2)
    if names(cr) > name then
      en = cr
    else if names(cr) < name then
      st = cr
    else
      fd = True
    end if
    if le = en and lst = st then 'Otherwise it goes forever if no match exists.
      exit do
    else
      lst = st
      le = en
    end if
  loop
  if fd then
    find = walks(cr)
  else
    find = "Unknown!" 'Check for this in the calling script (since sending it is pointless.) ;)
  end if
end function

1, Sword   <-- weapon table
2, Machete <-- weapon table

1, Cutting <-- damage type table
2, Pierce  <-- damage type table
3, Slice   <-- damage type table

1, 2  <-- weapon/damage table - sword pierces
1, 3  <-- weapon/damage table - sword slices
2, 1  <-- weapon/damage table - machete cuts

SELECT * from weapon, damage_type
       WHERE damage_type.damage_id = 3
       AND   damage_type.damage_id = weapon.damage_id
       AND   damage_type.weapon_id = weapon.weapon_id
ORDER BY weapon.name

Wpn table
Sword, Pierce
Sword, Slice
Machete, Cutting

Could be accessed with this SQL code:

SELECT * from Wpn
       WHERE Wpn.type = "Sword"
ORDER BY Wpn.type

Weapon                Damage Type(s)
------------          ------------------
Zeldan's Flameblade   Heat, Edge, Magic

The solutions to this is one of the following:

Bit-flag method - Stores type in an unsigned integer where the types are:
  1 - Edged
  2 - Blunt
  4 - Pierce
  8 - Heat
 16 - Cold
 32 - Poison
 64 - Mind
128 - Magic
256 - Asphyxiation
512 - Acid
So The above weapon would be stored as "Zelden's Flameblade,137" and the actually types would be returned from an array in the script, where the element in the array is the 'bit position' used to store that type.

Two Tables - Also uses an array in the script, but would use the following:

Table 1: Weapons
Weapon                Weapon Number
------------          ------------------
Zeldan's Flameblade   1

Table 2: Types
Index to the Weapon   Damage Type
-------------------   ------------------
1                     3
1                     0
1                     7