MUSHclient : VBscript : Handling numerous speedwalks - and using a database

Gammon Software Solutions forum

See www.mushclient.com/spam for dealing with forum spam. Please read the MUSHclient FAQ!

Entire forum
MUSHclient
VBscript
Handling numerous speedwalks - and using a database

Home \| Users \| Search \| FAQ
Username:	Register forum user name
Password:	Forgotten password?

Handling numerous speedwalks - and using a database

Reply to this subject Start a new subject Refresh page

Pages: 1 2 3 4

Posted by Nick Gammon Australia (18,772 posts) bio Forum Administrator

Date

Reply #15 on Fri 30 Aug 2002 02:43 AM (UTC) [ quote ]

Amended on Thu 03 Oct 2002 10:11 PM (UTC) by Nick Gammon

Message

Also, see posts on previous page for how to set up the database from scratch, and create a table.

To demonstrate adding a record to the database ...


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

Note that these examples are rather simple, for instance I haven't defined a primary key. I am trying to show the basic syntax here, rather than provide a fabulous solution to data management problems.

The quotes might look strange, but you need to use two double-quotes inside a string to get a single double-quote in the finished result. An example of what we are trying to generate is:


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

You can try this from the command window like this:



/AddRecordTable "here", "is", "a", "test"

- Nick Gammon

www.gammon.com.au, www.mushclient.com

Posted by Nick Gammon Australia (18,772 posts) bio Forum Administrator

Date

Reply #16 on Fri 30 Aug 2002 02:58 AM (UTC) [ quote ]

Amended on Thu 03 Oct 2002 10:10 PM (UTC) by Nick Gammon

Message

Finally here is an example that reads data from the database and displays it ...


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

- Nick Gammon

www.gammon.com.au, www.mushclient.com

Posted by Nick Gammon Australia (18,772 posts) bio Forum Administrator

Date

Reply #17 on Fri 30 Aug 2002 03:15 AM (UTC) [ quote ]

Amended on Thu 03 Oct 2002 10:11 PM (UTC) by Nick Gammon

Message

If you prefer creating tables using SQL syntax (as I do) here is an example of creating the table in SQL, rather than using properties of the objects. The example uses one of my own tables (for recording events) that looks like this (in MySQL):


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) |      |     |            |                |
+----------+--------------+------+-----+------------+----------------+

In MySQL the "identity" column is called "auto_increment" but apart from that they are much the same.

You can see that the 'create table' sets up a table with a primary key, auto increment, which is an integer, a date, a time, and a comment.


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

- Nick Gammon

www.gammon.com.au, www.mushclient.com

Posted by

Shadowfyr USA (1,774 posts)

Date

Reply #18 on Tue 03 Sep 2002 01:35 AM (UTC) [ quote ]

Amended on Tue 03 Sep 2002 01:55 AM (UTC) by Shadowfyr

Message

Thanks again Nick as I said in the email. Figure I should post to this anyway, so anyone else that didn't notice the update will. ;)

One comment though.. You suggest using a type 5, instead of type 4 for the Access database. The only problem here is that the point in most cases is to more easilly deal with complex data in mushclient, not to load it into access. There is a pretty good chance that most people will have a version of the access drivers that support the earlier version, but otherwise they would have to update the ODBC drivers on their computer to support the database. It is sort of the same situation as with MySQL. I see no real solution to it though, since the driver version could also be wrong. :p This is of course why most programs that support DBs include the updated driver set as part of their install. I am not entirely sure how the drivers deal with outdated requests...

Oh yes.. As to why no one posted.. It is Labor Day weekend in the US, thus most of the normal posters where probably too drunk to type. lol Either that or doing other national holiday sort of stuff. ;)

main {
__if (Schrodinger_Cat is Alive or version >= "XP"){
____if version = "Vista" then Performance /= Number_of_Cores;
____call Functional_Code();}
__else
____call Crash_Windows();}

Posted by

Nick Gammon Australia (18,772 posts)

bio Forum Administrator

Date

Reply #19 on Tue 03 Sep 2002 02:48 AM (UTC) [ quote ]

Message

Quote:

You suggest using a type 5, instead of type 4 for the Access database. The only problem here is that the point in most cases is to more easilly deal with complex data in mushclient, not to load it into access.

I agree, if you are not planning to use Access, probably type 4 is better, and more likely to be installed.

However if you are going to use Access, then when you try to open the database it wants to convert it (creating a new file under a different name), which means you then have to close the original one, rename the new one as the original name, and then start using it again. This might not be totally intuitive.

The nice thing about Access, if you have it, is you can do reports, forms, queries etc. quite simply, so if your data lends itself to it, you can fiddle with it "offline".

- Nick Gammon

www.gammon.com.au, www.mushclient.com

Posted by Jeffrey F. Pia (49 posts) bio

Date Reply #20 on Tue 03 Sep 2002 01:44 PM (UTC) [ quote ]

Message



db.Open = "Provider=Microsoft.Jet.OLEDB.4.0;" & _

          "Data Source=c:\mushclient\mydb.mdb"

If I wanted to access a DB stored in a text file or Excel spreadsheet, would I just change the Data Source, or would I have to change the Provider as well?

Posted by

Shadowfyr USA (1,774 posts)

Date

Reply #21 on Tue 03 Sep 2002 07:08 PM (UTC) [ quote ]

Amended on Tue 03 Sep 2002 07:10 PM (UTC) by Shadowfyr

Message

I believe you just change the data source.. Jet is the ODBC library and it supports all of the following (at least in the install I have, though a few things like Oracle and SQL have been added by other programs on my comp.):

Access - .mdb

Ironically this was also the extension used by Visual Basic for DOS's ISAM databases, but MS didn't supply a way to convert them... Which bugs me, since I wrote a program a while back that used VB DOS... :p

dBase, FoxPro - .dbf (?? How do you tell it which one to use or are they actually the same...?)
Excel - .xls
Paradox - .db
Text - .txt,.csv

I believe that simply changing the file extension probably handles the correct creation, but the docs I have looked at so far a a little sketchy about things like the 'Type=' Nick mentioned earlier. :p In fact the docs I have read don't even mention that option, but then they also mostly use the DBEngine or the like instead of ADOX.

main {
__if (Schrodinger_Cat is Alive or version >= "XP"){
____if version = "Vista" then Performance /= Number_of_Cores;
____call Functional_Code();}
__else
____call Crash_Windows();}

Posted by

Avariel Portugal (55 posts)

Date

Reply #22 on Thu 05 Sep 2002 03:29 PM (UTC) [ quote ]

Message

why not just use a plugin an a couple of mushclient variables?

sw_a_x - name of the x area <space> path to the x area.

n_a - number of areas

sw_m_y - name of the y mob <space> name of mob area <space> path to mob from start of area.

n_m - number of mobs

with a few simple functions, a few IF and FOR you can get all the informations you want with that... and as the plugin stores the variables in a file you can handle a lot of variables with easy.

adding and deleting paths without repeat, its easy too... check how i do it in my WindScript Loan.

i will do a plugin like i said when i have a few time and maybe you can take a look and see if you like it.

The Avariel Wind Lord

Posted by

Shadowfyr USA (1,774 posts)

Date

Reply #23 on Thu 05 Sep 2002 08:10 PM (UTC) [ quote ]

Message

Because Avariel, as 'easy' as you imply it to be, it really isn't. I spent 3 days recoding a script I used to report potion types I could make to use mushclient variables to store some of the information and make it complient with my muds "don't just hand out information players should learn on their own" policies. I then recoded it again, then again, and again, etc. Till I am now at version 3.1 and I think it is finally stable. However, there are a few things that my hard coded custom version does that the plugin still can't and the reason is because the code needed to handle a mix of mushclient variables and complex interrelations is too much of a major in the backside to code.

In the case of the original poster to this thread, the problem was less complex, but required maintaining a list of multiple paths, not just one at a time. I dealt with the same problem by once again hard coding the paths into my script in a case statement, but doing so is inflexible and added a very large chunk up code that does nothing more than what a database would have done far more efficiently. Mushclient variable are very useful for simple things, but when you are trying to handle large numbers of individual paths or complex connection, the fact that you have to convert them every time into an array, then back, make sure each variable has the same number of records (or just the right number), etc., rapidly becomes confusing and overly complicated.

Your method works, but is far harder to maintain, get working right and most importantly modify if your decide to make heavy modifications to the it for some reason. My own plugin checks a variable that stores a version number for the current plugin when it installs and if it doesn't match the current plugin, deletes all of the variables and recreates a few of them and leaving the user data empty. It does this because the structure of the strings used to track the data has changed about 5 times since I started designing the plugin. This is not a reasonable way to do things, but transfering the old data into new variables would have required maybe fifty lines of code 'for each previous version'. By comparison, with a database you just move the stuff you want to keep to new tables, or if some information is redundant or unneeded, just treat those fields as empty.

Put simply for simple things mushclient variables are useful, but when you have to start designing a database in scripting using those variables, you may as well avoid the aggrivation and use a real one. ;) lol

main {
__if (Schrodinger_Cat is Alive or version >= "XP"){
____if version = "Vista" then Performance /= Number_of_Cores;
____call Functional_Code();}
__else
____call Crash_Windows();}

Posted by

Avariel Portugal (55 posts)

Date

Reply #24 on Fri 06 Sep 2002 03:51 PM (UTC) [ quote ]

Amended on Fri 06 Sep 2002 03:56 PM (UTC) by Avariel

Message

nod i agree with you, Shadowfyr. using mushclient variables to build complex databases isnt the most eficient way of doing it, but i dont consider this sw database that much complex... at least not as your potion script.

the script i use now... i havent put it in a plugin look yet, can handle a few sw. i dont have much ( 58 areas with 5 mobs each average ).

i have a diferent structure as the one i post up.. i was trying to change it but i guess that way make it a lot more rigid.

i have a variable for number of areas ( n_area ) and other for number of mobs ( n_mob ) that are update each time i add or remove an area or a mob.

each area have 2 variables - name and dir to it ( area_name_1 area_dir_1 area_name_2 area_dir_2 etc.. )

each mob have 3 variables - name, area and dir ( mob_name_1 mob_area_1 mob_dir_1 etc...)

update for areas...

each time i try to add an area i do a loop from 1 to n_area checking the area_name_ variables to see if the area is already there... if exists display the existing area_dir_ ... if not create a new area_name_ and area_dir_ and increase n_area by 1.

each time i try to delete an area i do a loop from 1 to n_area checking the area_name_ variables to see if the area is there, and do a loop from 1 to n_mob checking the mob_area_ variables to see if its associated to any mob... if exists and no mob associated delete area_name_ and area_dir_ for that area and decrease n_area by 1... if not display the existing area dont exists or display that are mobs in the area.

to get a list of all areas i do a loop from 1 to n_area checking the area_name_ and send a note for each.

update for mobs...

each time i try to add a mob i do a loop from 1 to n_mob checking the mob_name_ variables to see if the mob is already there, and do a loop from 1 to n_area checking the area_name_ variables to see if the area is there... if the mob exists display the existing mob_dir_ and mob_area_ ... if the area dont exist display the area dont exist... if not create mob_name_ , mob_area_ , mob_dir_ and increase n_mob by 1.

each time i try to delete a mob i do a loop from 1 to n_mob checking the mob_name_ variables to see if its there... if exists delete mob_name_ , mob_area_ and mob_dir_ for that mob and decrease n_mob by 1... if not display the existing mob dont exist.

to get a list of all mob i do a loop from 1 to n_mob checking the mob_name_ and send a note for each.

going for areas...

each time i try to go to an area i do a loop from 1 to n_area checking the area_name_ variables to see if the area is already there... if exists evaluate area_dir_ ... if not display area dont exist.

going for mobs...

i have 2 choices... going from base and going from area entrance.
in both cases i do a loop from 1 to n_mob checking the mob_name_ variables to see if its there... if not display mob dont exist.
going from area entrance... evaluate mob_dir_
going from base... do a loop from 1 to n_area checking the area_name_ variables to get area number, then evaluate area_dir_ , then evaluate mob_dir_ .

i say again... i agree this isnt the best way of doing it but this isnt a prob that hard to code.

if you want more information stored you dont need a big change in variables... just add a variable to correspondent mob or area... you think you have useless information stored? delete all variables of that kind. maybe thats to much variables for a mob or an area but... changing version wont be a prob, you just need to know what area_name_ area_dir mob_name_ etc. means.

im not saying he should do like this, i was just showing another option.

The Avariel Wind Lord

Posted by

Magnum Canada (580 posts)

Date

Reply #25 on Fri 06 Sep 2002 05:57 PM (UTC) [ quote ]

Message

Personally, I try and be as considerate as possible to overall "overhead" when I'm working on any particular project. I would presonally really try to avoid writing a plugin that required more than about 20-30 variables.

Some things to take into account:

How urgently are the values needed?
Will the values be needed in combat (Lag)?
Are there any other reasons lag should be of concern?
How many values are expected?

For example, my spell queue, should it ever be converted to a plugin, needs to be as quick as possible, so I would probably elect not to use an external file of any kind.

A database of item descriptions is a pretty low priority where speed is concerned, and given that the database would be quite large, this would definately go in an external file.

Speedwalks, in my opinion, have a low priority where speed is concerned. If the client lags for a moment while it pulls data from an external file, it's not so bad, since you aren't likely to execute a speedwalk while in battle. Also, a speedwalk database could concievable grow quite large, especially on a mud where new areas are developed frequently.

Anyhow, just thought I would throw in my two cents. :)

Get my plugins here: http://www.magnumsworld.com/muds/

Constantly proving I don't know what I am doing...
Magnum.

Posted by Shadowfyr USA (1,774 posts) bio

Date Reply #26 on Fri 06 Sep 2002 08:29 PM (UTC) [ quote ]

Message

Avariel, if you have 58 speedwalks.. have you considered a simple insertion sort for puting the stuff in to the list? Doing so requires some tricks like using 'redim preserve' to resize the array (something I had a problem getting to work right though). But even if you had to use two arrays, having the data sorted and using a binary search would speed things up of large amounts for data.

A binary search in case you don't know basically does this:


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

I tried using this once to also do the insertion, but gave up. Mostly because I forgot that last if/then test to keep in from infinite looping. lol For insertion the new record should always be at 'en'. ;)

In any case, this just about halves the time needed to find something in a large array.

main {
__if (Schrodinger_Cat is Alive or version >= "XP"){
____if version = "Vista" then Performance /= Number_of_Cores;
____call Functional_Code();}
__else
____call Crash_Windows();}

Posted by

Jeffrey F. Pia (49 posts)

Date

Reply #27 on Sat 07 Sep 2002 06:21 AM (UTC) [ quote ]

Message

Hi guys :) Thanks for all your tips. I am having tons of fun (only slight sarcasm) learning how to do this stuff. Oh, just fyi, the MUD I'm doing this for has over 200 areas, the largest having 400 or so rooms and at least as many mobs, the smallest having 25 rooms and about 50 mobs. I have a nice lofty spreadsheet with all the data I've collected, but I might break it out into several spreadsheets, as it's getting rather large and unwieldy. I am going all out and trying to make this as comprehensive as possible, including SWs from portals, clan exits, manor exits, passdoor on/off, and alternate entrances. I only have the main SWs done so far, so when I get everything else entered, it's pretty much going to triple or quadruple in size. Heh, variables are pretty much out of the picture at this point. I'm still having problems getting the code to recognize an Excel spreadsheet as a DB though... I tried the code Nick posted earlier, changing the .mdb to a .xls, but the client just lags for a minute and returns error -2147467259, Unrecognized database format. *sigh* I may have to turn to Access... yet another thing I know little about... heh, I think I'm going to put this on my resume when I get it done....

Posted by

Nick Gammon Australia (18,772 posts)

bio Forum Administrator

Date

Reply #28 on Sat 07 Sep 2002 07:03 AM (UTC) [ quote ]

Message

Yes, a spreadsheet is not exactly a database, although in some ways they are similar.

You can make an Access database and import a spreadsheet into it, so migrating the data shouldn't be too hard.

To do the job efficiently you really need good database design, which I think is called "third normal form" from memory.

What this means in practice, is that each table in the database should hold one "kind" of data, and there should not be repetition (ie. the name of a mob should be held once, and if you need to refer to it in two different places you move it into a separate table).

For instance, you might have a table of area names, table of room names, table of exits, table of speedwalks and so on.

- Nick Gammon

www.gammon.com.au, www.mushclient.com

Posted by

Jeffrey F. Pia (49 posts)

Date

Reply #29 on Sat 07 Sep 2002 09:49 PM (UTC) [ quote ]

Message

Yes, I totally agree... I had read somewhere that you could use ODBC with flat text files and Excel spreadsheets, but the more I think about it, the more I realize a full DB is the only real way to go. I need to do a bit of research on planning a relational DB, just so I make sure I get it right the first time. I played around with a small DB, and got it to work great! Thanks for all the help with the sample code you provided. A few more questions though:
Why would you create a table using SQL as opposed to within the DB itself? and what is the difference between using Type 4 and Type 5 as mentioned above?

The dates and times for posts above are shown in Universal Co-ordinated Time (UTC).

To show them in your local time you can join the forum, and then set the 'time correction' field in your profile to the number of hours difference between your location and UTC time.

13,116 views.

This is page 2, subject is 4 pages long: 1 2 3 4

Reply to this subject Start a new subject Refresh page

Go to topic: Search the forum

Comments to: Gammon Software support
Forum RSS feed ( http://www.gammon.com.au/rss/forum.xml )