 |
JQuilici
Adept
Joined: 21 Sep 2005
Posts: 250
Location: Austin, TX
|
 Posted: Wed Sep 21, 2005 7:08 pm
Building DB queries on the fly
|
I have finally gotten a rather extensive equipment DB more or less working - capturing items automatically as they are identified, lookups by ID or name, etc. But the one thing I haven't managed to do with a script is a general query, where I can type any query I want and have the whole query expression captured in a variable, then run against the DB.
Let me be more specific. I have gotten queries working, when the query is defined directly in the script and just has some variable values substituted in. For example, here is the alias that looks up all records matching a certain name:
| Code: |
#AL eqname {
#var qname "*"%replace(%sort(%replace(%1,"-","|")),"|","*")"*" {} Ident
#var showfull {%2} {} Ident
#var count {%null} {} Ident
#say
#DBOFFLINE
#DBLOAD eq
#DBRESET
#QUERY (&Canon =~ @qname) "All" 1
#DBFIRST
#while (!%null(%rec)) {
#add count 1
#sayp &Num": "
#if (@showfull = "full") {eqshowlong %rec;#say} {eqshowshort %rec}
#DBNEXT
}
#DBONLINE
#if (!@count) {#say No matches for @qname}
#say
}
|
Note: The DB Field Canon contains a canonical name for the item. Item names on this Mud (Mozart) are a series of words separated by dashes. The canonical name contains the same words in alphabetical order to make searching work. Thus the canonical name of 'snake-skin-staff' is 'skin-snake-staff'. The processing on qname above just puts the argument into the same order and sticks in *s to allow for missing words.
This allows me to type 'eqname snake-skin' and get the following output (generated by the alias eqshowshort, not supplied):
| Code: |
32eq: snake-skin-staff: Light rare magic mana+35 Age+10
|
Now, though, I'd like to make another function (eqfind) that lets me type in a general query, and feed it in to the #QUERY command, and then show the results in the same way. Here's my latest attempt:
| Code: |
#AL eqfind {
#var qstr {%prompt("&Canon=~~rod","Query string:")} {} Ident
#var showfull {%2} {} Ident
#var count {%null} {} Ident
#say Query String: %expand(@qstr,1)
#DBOFFLINE
#DBLOAD eq
#DBRESET
#QUERY %expand(@qstr,1) "All" 1
#DBFIRST
#while (!%null(%rec)) {
#add count 1
#sayp &Num": "
#if (@showfull = "full") {eqshowlong %rec;#say} {eqshowshort %rec}
#DBNEXT
}
#DBONLINE
#if (!@count) {#say No matches for ~(%expand(@qstr,1)~)}
#say
}
|
I am using %prompt instead of taking the query string on the command line to avoid problems with variable expansion on the command line.
When I run it (and take the default answer to the prompt), I get the following output:
| Code: |
Query String: &Canon=~rod
3eq: amber-earrings: Jewelry rare mana+20 dex+6 wsl:cl wsc:100%
5eq: amber-silk-cape: Clothing rare AC-25 mana+50 mv+50
28eq: bell-ringer: Hammer !Cl/Ma/Th rare 6d3 HND+2/+2
23eq: belt-pockets: Container
20eq: black-sunglasses-glasses: Glasses r:fire
29eq: dart-inumbration-wicked: Dart !Cl/Wa rare magic 1d5 HND+3/+0 mana+10 ws:darkness wsl:1
27eq: evasion-stave: Staff rare magic HND+1/+1 AC-25 Dodge+10 Lose+25
22eq: necklace-truth: Wand !E/Th rare HND+1/+1 sa:true-sight
24eq: quest-token: Jewelry noscrap
19eq: rex-claw: Fist Weapon rare 3d6 HND+2/+2
30eq: rhea-club: Mace rare 3d8 HND+3/+3 ws:calm wsl:cl
18eq: rod-smiting: Mace !Th rare 7d3 HND+3/+3 str+8
35eq: silver-trident: Throwable Spear rare 4d4 HND+2/+2 sa:water-breath Backstab+3
36eq: skals-skal-skull-pain-hideous: Fist Weapon !Ma/Wa rare magic 3d6 HND+3/+3 ws:curse wsl:10 Necromancy+5
32eq: snake-skin-staff: Light rare magic mana+35 Age+10
31eq: staff-petrified-wood: Melee Staff rare 2-hand 5d5 HND+2/+1 hp-15 Saving-Paralyzation-2
33eq: sword-flame-tongue: Sword rare 2d8 HND+1/+1 ws:flame-tongue wsl:1
14eq: sword-healing: Sword 1d30 HND+3/+3 ws:cure-light wsl:1
17eq: troll-maul: Hammer !Th rare 3d8 HND+3/+3 dex-13
11eq: war-drums: Wand rare i:hold i:charm
8eq: war-hammer-warhammer-dwarven: Throwable Hammer 1d8
34eq: Wereboar-tusks: Dagger rare 2d10 HND-2/+0 Backstab-10
|
Note that this is the entire contents of the DB - the query is returning every record.
My problem seems to be related to the expansion of the variable @qstr in the #QUERY command. I have tried the following variations on that line, with identical results:
| Code: |
#QUERY (%expand(@qstr,1)) "All" 1
#QUERY (@qstr) "All" 1
|
So...any ideas? Is there any way to control the expansion of the @qstr variable properly? Am I missing something extremely basic? Is there an entirely better approach? I could use ADO to make this work (and create a SQL Select statement on the fly), but the database module doesn't use ADO yet, so I'd have to redo all of the database scripts and dispense with the built-in DB entirely. |
|
|
|
|
Vijilante
SubAdmin
Joined: 18 Nov 2001
Posts: 5187
|
Posted: Thu Sep 22, 2005 12:27 am
|
I have never seen such a problem with #QUERY, but then I always use %query because I want all output to be controlled by my script. I would suggest that change first, as it seem you are interested in a greater level of control over the outputs.
The next suggestion is more towards the complex scripting area. It is to use #EXECUTE to control the expansion. This used to be commonly used for alarms before they were changed to expand variables in the time/pattern parameter. You could use:
#EXEC {%concat("#QUERY {",@qstr,"} {All} 1")} |
|
_________________
The only good questions are the ones we have never answered before.
Search the Forums |
 |
|
|
JQuilici
Adept
Joined: 21 Sep 2005
Posts: 250
Location: Austin, TX
|
Posted: Thu Sep 22, 2005 3:57 am Even weirder %query / #query differences with pattern-match
|
Ok...took the advice above and tried using %query. No joy.
Tried using %query and controlling expansion. No better.
Tried using %query and %concat, as suggested. Still failed.
Tried using ALL of them. Finally got something that worked.
I extracted all my testing crud into a new class (Scratch). Here is an alias that shows the progression. Note that have to do the %replace to construct @qstr, because the interpreter is very aggressive about expanding &<name> constructs:
| Code: |
#AL testcmds {
#var pstr {} {} Scratch
#var qstr {} {} Scratch
pstr = "(.Hitroll>1)"
#say PSTR: @pstr
qstr = %replace(@pstr,".","&")
#say QSTR: @qstr
#say QSTR ~(controlled expansion~): %expand(@qstr,1)
#DBOFFLINE
#DBLOAD eq
#DBRESET
#sayp "Sanity Check: "
#show %query((&Hitroll>1),{All})
#DBRESET
#sayp "Test 1: "
#show %query(@qstr,{All})
#DBRESET
#sayp "Test 2: "
#show %query(%expand(@qstr,1),{All})
#DBRESET
#sayp "Test 3: "
#show %exec(%concat("%query(",@qstr,",{All})"))
#DBRESET
#sayp "Test 4: "
#show %exec(%concat("%query(",%expand(@qstr,1),",{All})"))
#DBONLINE
#say
}
|
The output shows the result. Note that Test 1 and Test 2 return everything in the test DB, Test 3 returns nothing, and Test 4 matches the Sanity Test:
| Code: |
PSTR: (.Hitroll>1)
QSTR: (0>1)
QSTR (controlled expansion): (&Hitroll>1)
Sanity Check: 28eq|29eq|19eq|30eq|18eq|35eq|36eq|31eq|14eq|17eq
Test 1: 3eq|5eq|28eq|23eq|20eq|29eq|27eq|22eq|24eq|19eq|30eq|18eq|35eq|36eq|32eq|31eq|33eq|14eq|17eq|11eq|8eq|34eq
Test 2: 3eq|5eq|28eq|23eq|20eq|29eq|27eq|22eq|24eq|19eq|30eq|18eq|35eq|36eq|32eq|31eq|33eq|14eq|17eq|11eq|8eq|34eq
Test 3:
Test 4: 28eq|29eq|19eq|30eq|18eq|35eq|36eq|31eq|14eq|17eq
|
This allowed me to build a (mostly) working alias that lets me do DB searches based on arbitrary criteria. Note that I substitute some chars on the command-line args, so that I don't have to quote the snot out of everthing I type (so "testinput .Name^rod" uses a query string of "(&Name=~rod)"):
| Code: |
#AL testinput {
#var pstr {%concat("(",%1 %2 %3 %4 %5 %6 %7 %8 %9 %10,")")} {} Scratch
#var qstr {%replace(%replace(@pstr,".","&"),"^",~=~~)} {} Scratch
#var qr {} {} Scratch
#DBOFFLINE
#DBLOAD eq
#DBRESET
#say qstr: %expand(@qstr,1)
qr = %exec(%concat("%query(",%expand(@qstr,1),",{All})"))
#if (@qr) {
#forall @qr {
#sayp %i": "
eqshowshort %dbget(%i,1)
}
} {#say No matches for %expand(@qstr,1)}
#DBONLINE
#say
}
|
This mostly works. For example, 'testinput .Hitroll>2' produces the following:
| Code: |
qstr: (&Hitroll>2)
29eq: dart-inumbration-wicked: Dart !Cl/Wa rare magic 1d5 HND+3/+0 mana+10 ws:darkness wsl:1
30eq: rhea-club: Mace rare 3d8 HND+3/+3 ws:calm wsl:cl
18eq: rod-smiting: Mace !Th rare 7d3 HND+3/+3 str+8
36eq: skals-skal-skull-pain-hideous: Fist Weapon !Ma/Wa rare magic 3d6 HND+3/+3 ws:curse wsl:10 Necromancy+5
14eq: sword-healing: Sword 1d30 HND+3/+3 ws:cure-light wsl:1
17eq: troll-maul: Hammer !Th rare 3d8 HND+3/+3 dex-13
|
So far so good. But it utterly fails on anything with a pattern-matching expression. In mucking about with this problem, I discovered that the %query function and the #query command would produce different results with the same input!
Again, I distilled this down to a simple test function:
| Code: |
#AL testmethods {
#var qr {} {} Scratch
#DBOFFLINE
#DBLOAD eq
#DBRESET
#say Using ~%query
qr = %query((&Canon=~amber),"All")
#show @qr
#DBRESET
#say Using ~#query
qr = {}
#query (&Canon=~amber) "All" 1
#while (!%null(%rec)) {#additem qr &Num;#DBNEXT}
#show @qr
#DBONLINE
}
|
Note that there are two items in my little test DB with the word 'amber' in their names, so you'd expect both methods in this function to return two-item lists. Instead:
| Code: |
Using %query
Using #query
3eq|5eq
|
This smells like a bug to me - unless someone can show me where the testmethods alias is in error? (And I'm using version 7.20a, in case it matters)
Anyway, I'm going to get some sleep. Tomorrow I re-write testinput to use #query and see if it works any better. |
|
|
|
|
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
