Register to post in forums, or Log in to your existing account
 

Play RetroMUD
Post new topic  Reply to topic     Home » Forums » Finished MUD Scripts Goto page Previous  1, 2, 3, 4, 5, 6, 7, 8  Next
Dharkael Posted: Thu Apr 03, 2008 10:09 am
Python Sqlite COM Component for ZMUD/CMUD
xekon
Apprentice


Joined: 11 Oct 2007
Posts: 154
PostPosted: Thu Apr 24, 2008 6:07 pm   
 
Ill write up an example later using a test file DB, so that you can see the bug im talkin about, im off to work for now though so i wont be back for several hours.
Reply with quote
Anaristos
Sorcerer


Joined: 17 Jul 2007
Posts: 821
Location: California
PostPosted: Fri Apr 25, 2008 1:12 am   
 
Thanks for the reply, Dharkael. Please forgive me if it sounded as if I were criticizing your changes. I can't deny that I am frustrated, though.
Here is what is happening: I followed your suggestion and I strong-typed the variable receiving the output from the query. The parentheses did get stripped, however, the row data (list item) then ceased to be a database record and became an expanded string. So selecting the first field returned the entire row. However, when I do manually strip the parentheses, it works. I tried seeing what happens if I retrieve multiple rows (since it happens that the first row has the data I want, there is no harm in getting all the rows) and the result was somewhat fascinating. When fetching the first key/value pair, it returned the entire row, for the subsequent key/value pairs, it returned the correct values, with the exception of the last one, where a right parenthesis was affixed to the data. Further testing showed that when multiple rows are returned, a standard string list is returned with each entry (representing a row) encased in parentheses and containing unprintable characters which I assume are separators. My conclusion from this is that the problem is not how string lists are presented but how rows (database records) are presented within the string lists. They are the ones that need the parentheses stripped. You are correct when you say that if the rows are extracted in a #FORALL loop there are no problems. The stripping which I have done to make this work has not been necessary for those scripts.

EDIT: As far a view creation is concerned, this is another part of the learning curve. One cannot bind parameters in the view creation statement so one must create the SQL statement with all the pertinent data inside the statement using %concat e.g:
Code:

$exec  = %concat( "CREATE VIEW [Ready Portals] AS SELECT Description, PortalLevel, EntryPoint, PortalAlias, KeyWord, Enabled, ZoneID FROM Portals, CharacterData WHERE Portals.Enabled = 0 AND CharacterData.Name = '", @Username, "' AND Portals.PortalLevel <= CharacterData.CurLevel")
;;
#CALL @comsql.Execute($exec)

This works. I may add, though, that this circuitous method is due to the fact that SQLite Join is somewhat restricted. So one must do joined queries manually. A view is, of course, nothing more than a dynamic query which is updated by SQLite each time any of the invoked fields change.

NOTE: The single quotes must surround parameters that are typed as TEXT otherwise SQLite will think that they are column names.
_________________
Sic itur ad astra.		 Reply with quote
Dharkael
Enchanter


Joined: 05 Mar 2003
Posts: 593
Location: Canada
PostPosted: Fri Apr 25, 2008 3:59 am   
 
@Anaristos
I can't reproduce the problem you're having with the list items ceasing to be db variables.
This code below demonstrates how I am able to retrieve the first item and act on it as a db record.
You may be doing something completely different,so put together a test case that demos what's happening for you.
Code:
#var stuff %comcreate("Sqlite.ZMUD.WSC")
#call  @stuff.Open(":memory:")
#call @stuff.Execute("create table herblist (herb,method,effect,cures)")
#call @stuff.Execute("insert into herblist values(?,?,?,?)","Siriena","Eat","Cure for the Black Death. ","Black death")
#call @stuff.Execute("insert into herblist values(?,?,?,?)","Aldaka","Apply","Restores sight. ","Blind")
#call @stuff.Execute("insert into herblist values(?,?,?,?)","Echianta","Smoke","Heals physical afflictions such as clumsiness.","Clumsiness")
#call @stuff.Execute("insert into herblist values(?,?,?,?)","Febfendu","Drink","Restores hearing.","Deaf")
#call @stuff.Execute("insert into herblist values(?,?,?,?)","Courgia","Eat","Cure for a few diseases including haemophilia.","Haemophilia")
#call @stuff.Execute("insert into herblist values(?,?,?,?)","Madabril","Eat","Often known to cure the plague.","Plague")
#call @stuff.Execute("insert into herblist values(?,?,?,?)","Witan","Eat","Relieves stun and holding spells.","Stun")
result = @stuff.Execute("SELECT * FROM herblist WHERE method=? AND effect REGEXP ?","Eat","(C|c)ure")
#call %vartype(result,4)
#var res %item(@result,1)
#echo Herb Name:?@res.herb
#echo *********
#showdb %item(@result,1)


Results wrote:
Herb Name:?Siriena
*********
herb: Siriena
effect: Cure for the Black Death.
method: Eat
cures: Black death

Okay so I can access a row record using the @dbvar.key syntax and #showdb also has no problems.
So show me where you're hitting the wall.
The representation of rows as db records has not changed,the unprintable characters are separators, that is the ZMUD db record format, which CMUD also understands.
As evinced in the code and results above.
BTW if you're only interested in the first record returned, then you can only return one record using the LIMIT keyword A-la:
Code:
SELECT * FROM sometable WHERE sometuff=someother LIMIT 1

No matter how many records the select statement would have returned only one will be returned.
This means less work for the DB Python and CMUD/ZMUD.
LIMIT can also be used in conjuction with OFFSET to pick any range of records from the full set the SELECT would have returned.

I'm not sure what you mean by SQLite Join being restrictive I've found them expressive enough, The view you've shown above is pretty simple so I'm not sure what problems
it could be solving(however circuitous) that is beyond the power of Joins
If you can show how your tables are setup and what particular problem you're trying to solve maybe I can help.


BTW I'm contemplating having Execute return ZMUD or CMUD style data depending on true-false property, I really didn't want to do this but, it maybe the best solution.
It'll be at least several days before I make those changes however.
_________________
-Dharkael-
"No matter how subtle the wizard, a knife between the shoulder blades will seriously cramp his style."		 Reply with quote
Anaristos
Sorcerer


Joined: 17 Jul 2007
Posts: 821
Location: California
PostPosted: Fri Apr 25, 2008 4:23 am   
 
You know, I am really lost here. Not to say that I am not making things work, it's just that I have a problem with the consistency of things and I am sure that it has something to do with my understanding of how things work. Though these problems weren't there at the beginning and only started when you updated the wsc file to the (data) scheme. Now, some routines require that I strip the parentheses and some don't. I could very well be that I was misusing the interface before and getting away with it and now that's no longer the case. Before your modification, I could just do things like:
Code:

$rec = @comsql.Execute("SELECT * FROM table)

and then do things like $rec.keyname to extract the values from the record. Now that or %db($rec,keyname) won't work unless I strip the parentheses, but not always, only when a single record is returned. If I use %item, or #FORALL then everything seems to work OK.
As for the views, I am past that. I have built complex views based on many different data across multiple tables. What I was saying about the Join was that when I tried it, I got nothing back. My problem, I am sure. So I've resorted to doing the joins manually. At least, that is giving me a good handle on how to build queries. Now that I've discovered that one can use %concat to build the query statements, I am having an easier time getting to my data. The truth is that I went production too soon and that has added some pressure.
_________________
Sic itur ad astra.		 Reply with quote
Dharkael
Enchanter


Joined: 05 Mar 2003
Posts: 593
Location: Canada
PostPosted: Fri Apr 25, 2008 4:51 am   
 
Well the problem with the orginal implementation is what happens when your data contains a |.
Without that consideration defining a list was easy: Return rows encoded as zmud db var, for every row beyond the first prepend with a | to delineate list items.
Simple and easy :p
If you only had one record returned it was really an unadorned db var so
$rec = @comsql.Execute("SELECT * FROM table")
$rec.keyname
would work.
But really the value returned from SELECT is not a record, its a list of records.
By encasing each record in ()s,if said record contains a | it's not a problem and thats the way ZMUD does it.
Now I am considering making changes so that its possible to return a more CMUD specific encoding.
Regardless, the data being returned is always a list and although often the internal representation of a list of 1 item is same as if it was not a list,
if you always operate on it as a list even a list of one, you'll get better results because that's the contract I'm aiming to keep.
$rec = @comsql.Execute("SELECT * FROM table")
$rec = %item($rec,1)
$rec.keyname
_________________
-Dharkael-
"No matter how subtle the wizard, a knife between the shoulder blades will seriously cramp his style."		 Reply with quote
Anaristos
Sorcerer


Joined: 17 Jul 2007
Posts: 821
Location: California
PostPosted: Fri Apr 25, 2008 5:44 am   
 
Yes, I understand now, that is why this worked:
Code:

#IF (!%null( $rec)) {#RETURN %roomvnum( %db( %item( $rec, 1), EntryPoint))} {#RETURN -1}

However, using your scheme hasn't work for this case:
Code:

$entry    = @comsql.Execute("SELECT * FROM CharacterData WHERE Name = ?", @Username)
$entry    = %item($entry,1)

When I fetch %db( $entry, Name), it get the entire record, as will $entry.Name. The scheme $entry = %leftback( %right( $entry, 1), 1) still is what makes the data correspond. Which means, that for some reason, %item is not recognizing $entry as being a string list. Not even when strong type $entry.
_________________
Sic itur ad astra.		 Reply with quote
Dharkael
Enchanter


Joined: 05 Mar 2003
Posts: 593
Location: Canada
PostPosted: Fri Apr 25, 2008 5:57 am   
 
Code:
#var stuff %comcreate("Sqlite.ZMUD.WSC")
#call  @stuff.Open(":memory:")
#call @stuff.Execute("create table herblist (herb,method,effect,cures)")
#call @stuff.Execute("insert into herblist values(?,?,?,?)","Siriena","Eat","Cure for the Black Death. ","Black death")
#call @stuff.Execute("insert into herblist values(?,?,?,?)","Aldaka","Apply","Restores sight. ","Blind")
#call @stuff.Execute("insert into herblist values(?,?,?,?)","Echianta","Smoke","Heals physical afflictions such as clumsiness.","Clumsiness")
#call @stuff.Execute("insert into herblist values(?,?,?,?)","Febfendu","Drink","Restores hearing.","Deaf")
#call @stuff.Execute("insert into herblist values(?,?,?,?)","Courgia","Eat","Cure for a few diseases including haemophilia.","Haemophilia")
#call @stuff.Execute("insert into herblist values(?,?,?,?)","Madabril","Eat","Often known to cure the plague.","Plague")
#call @stuff.Execute("insert into herblist values(?,?,?,?)","Witan","Eat","Relieves stun and holding spells.","Stun")
$result = @stuff.Execute("SELECT * FROM herblist WHERE method=? AND effect REGEXP ?","Eat","(C|c)ure")
$result = %item($result,1)
#echo herbname?:$result.herb
#echo herbname2?:%db($result,herb)


Results wrote:
herbname?:Siriena
herbname2?:Siriena


So if it's not working for you.. can you give me a full demo where its not working for you?
obviously there must be something different about the data you're using.

And it doesn't look like you can use %vartype on local variables (pity that, also be nice to be able to use %arrays com objects in temp variables)



EDIT: I only get the entire variable result you're speaking of IF i skip the $entry = %item($entry,1) step, which is crucial
_________________
-Dharkael-
"No matter how subtle the wizard, a knife between the shoulder blades will seriously cramp his style."

Last edited by Dharkael on Fri Apr 25, 2008 6:09 am; edited 1 time in total		 Reply with quote
xekon
Apprentice


Joined: 11 Oct 2007
Posts: 154
PostPosted: Fri Apr 25, 2008 6:09 am   
 
Dharkael, do you have an email address that I can send you my database file...

I just cant seem to reproduce the UTF-8 bug except for when i querry this db...

I can of coarse provide the test alias here:

Code:
#var eqall %comcreate("Sqlite.ZMUD.WSC")
#call @eqall.Open("C:\alleq.db3")
#show "working example"
#for @eqall.Execute("SELECT * FROM alleq WHERE clan>0 AND level BETWEEN 73 AND 79") {
  #show %format( "&-30s", %db(%i,"name"))
}
#echo "Error SELECT?:"@eqall.LastError
#show ""
#show ""
#show "UTF-8 bug example"
#for @eqall.Execute("SELECT * FROM alleq WHERE clan>0 AND level BETWEEN 91 AND 91") {
  #show %format( "&-30s", %db(%i,"name"))
}
#echo "Error SELECT?:"@eqall.LastError
#show ""
Reply with quote
Dharkael
Enchanter


Joined: 05 Mar 2003
Posts: 593
Location: Canada
PostPosted: Fri Apr 25, 2008 6:30 am   
 
@Xekon
I'll have a look and see what I can come up with.
_________________
-Dharkael-
"No matter how subtle the wizard, a knife between the shoulder blades will seriously cramp his style."		 Reply with quote
xekon
Apprentice


Joined: 11 Oct 2007
Posts: 154
PostPosted: Fri Apr 25, 2008 7:23 am   Better TESTing Alias
 
Here is a better testing alias, it shows a working example, the UTF-8 bug, AND the portal string bug.

Code:
#var eqall %comcreate("Sqlite.ZMUD.WSC")
#call @eqall.Open("C:\alleq.db3")
#show "working example"
#for @eqall.Execute("SELECT * FROM alleq WHERE clan>0 AND level BETWEEN 72 AND 80") {
  #show %format( "&-30s", %db(%i,"name"))
}
#echo "Error SELECT?:"@eqall.LastError
#show ""
#show ""
#show "UTF-8 bug example"
#for @eqall.Execute("SELECT * FROM alleq WHERE clan>0 AND level BETWEEN 91 AND 91") {
  #show %format( "&-30s", %db(%i,"name"))
}
#echo "Error SELECT?:"@eqall.LastError
#show ""
#show ""
#show "Portal String bug example 3rd item is supposed to be -*)Fu-leng's Legacy(*- "
itemtype=portal
#for @eqall.Execute("SELECT * FROM alleq WHERE type=? AND level BETWEEN 40 AND 60",@itemtype) {
  #show %format( "&-30s", %db(%i,"name"))
}
#echo "Error SELECT?:"@eqall.LastError
#show ""
Reply with quote
Anaristos
Sorcerer


Joined: 17 Jul 2007
Posts: 821
Location: California
PostPosted: Fri Apr 25, 2008 7:24 am   
 
This is the gist of the code that requires the parentheses stripping:
Code:

$username = %if( %null( %1), @Username, %proper( %1))
$entry    = @comsql.Execute("SELECT * FROM CharacterData WHERE Name = ?", $username)
#IF (!%null( $entry)) {
  $entry    = %leftback( %right( $entry, 1), 1)
   ...
   ...
}

The missing part is just formatting of the data that is returned from the database. Since each entry in the table corresponds to one user, it only returns on record, if the user exists.
This table is crucial because this is the way I differentiate between alts.
_________________
Sic itur ad astra.		 Reply with quote
xekon
Apprentice


Joined: 11 Oct 2007
Posts: 154
PostPosted: Fri Apr 25, 2008 7:36 am   
 
Anaristos where does the %1 value come from... i would like to test your code and see if i can figure it out.

give me a workable alias or instructions on how to use and test for the problem your seeing.
Reply with quote
Dharkael
Enchanter


Joined: 05 Mar 2003
Posts: 593
Location: Canada
PostPosted: Fri Apr 25, 2008 7:41 am   
 
Anaristos What I meant was could you create a working self contained example, akin to my herb examples?
The actual data that is being returned is important, so just create something in memory that will duplicate the problems you're having with your perm database.
Something that I can run as is without having to guess or falsify data.
_________________
-Dharkael-
"No matter how subtle the wizard, a knife between the shoulder blades will seriously cramp his style."		 Reply with quote
Anaristos
Sorcerer


Joined: 17 Jul 2007
Posts: 821
Location: California
PostPosted: Fri Apr 25, 2008 10:36 am   
 
OK, Dharkael, here is the actual combination of the table creating plus the data access code changed to run on a ::memory:: database. If you notice there is a line of code commented out, if you re-activate that line and comment the parentheses-removing line just above, you will see (I hope) the problem I am having.
Code:

$username  = "TestWarrior"
$currace   = "Elf"
$curlevel  = 100
$curtier   = 0
$cursex    = 2
$deity     = "Buddha"
$classlist = "Ranger|Mage|Cleric|Thief|Warrior|Psionicist|Paladin"
$curpclass = %item( $classlist, 1)
$cursubcl  = "Shaman"
;;
#call @comsql.Open(":memory:")
#call @comsql.Execute("CREATE TABLE IF NOT EXISTS CharacterData (Name Text, CurSex Integer, CurRace Text, CurLevel Integer, CurTier Integer, PrimaryClass Text, SubClass Text, Deity Text, ClassList Text)")
;;
#CALL @comsql.Execute("INSERT INTO CharacterData values(?,?,?,?,?,?,?,?,?)", $username, $cursex, $currace, $curlevel, $curtier, $curpclass, $cursubcl, $deity, %replace($classlist,"|"," "))
;;
$entry    = @comsql.Execute("SELECT * FROM CharacterData WHERE Name = ?", $username)
#IF (!%null( $entry)) {
  $entry    = %leftback( %right( $entry, 1), 1)
;;  $entry = %item($entry,1)
  $classes  = %replace( %db( $entry, Classlist), " ", "|")
  #ECHO %cr
  #MXP <color white>Character Name: </color><color yellow>%db( $entry, Name)</color>
  #MXP <color white>Character Race: </color><color yellow>%db( $ENTRY, CurRace)</color>
  #MXP <color white>Character Sex: </color><color yellow>%item( "None|Male|Female", %db( $entry, CurSex))</color>
  #MXP <color white>Character Tier: </color><color yellow>%concat( "T", %db( $entry, CurTier))</color><color white>/</color><color yellow>%numitems( $classes)</color>
  #MXP <color white>Character Level: </color><color yellow>%db( $entry, CurLevel)</color>
  #MXP <color white>Character Primary Class: </color><color yellow>%db( $entry, PrimaryClass)~(</color><color red>%proper( %db( $entry, SubClass))</color><color yellow>~)</color>
  ;#MXP <color white>Character SubClass: </color><color yellow>%proper( @cursubcl)</color>
  #MXP <color white>Class List: </color><color yellow>%trim( %replace( %db( $entry, ClassList), " ", " / "))</color>
  #MXP <color white>Character Deity: </color><color yellow>%db( $entry, Deity)</color>
  #ECHO %cr
  }
{
  #ECHO %cr
  #MXP <color white>No data found for user </color><color yellow>$username</color>
  }

If I use the $entry = %item($entry,1) scheme, I get the following (which is obviously wrong):
Code:

Character Name: TestWarriorCurSex2CurRaceElfCurLevel100CurTier0PrimaryClassRangerSubClassShamanDeityBuddhaClassListRanger Mage Cleric Thief Warrior Psionicist Paladin
Character Race:
Character Sex:
Character Tier: T/0
Character Level:
Character Primary Class: ()
Class List:
Character Deity:

Notice how the entire db record was extracted when I asked for %db($entry,Name).
However with my "fix", it presents the data correctly:
Code:

Character Name: TestWarrior
Character Race: Elf
Character Sex: Male
Character Tier: T0/7
Character Level: 100
Character Primary Class: Ranger(Shaman)
Class List: Ranger / Mage / Cleric / Thief / Warrior / Psionicist / Paladin
Character Deity: Buddha
_________________
Sic itur ad astra.		 Reply with quote
xekon
Apprentice


Joined: 11 Oct 2007
Posts: 154
PostPosted: Sat Apr 26, 2008 4:51 am   
 
Let me know if theres anything else I can do to help Dharkael. I'm sure you have plenty of things going on. I've just been working on other parts of my script for the time being. All of my scripts that make use of this com object are mostly working except for when they come across the UTF-8 problems...

the other output problem just makes the output come out funny but other than that does not affect the rest of the functionality.
Reply with quote
Dharkael
Enchanter


Joined: 05 Mar 2003
Posts: 593
Location: Canada
PostPosted: Sun Apr 27, 2008 4:15 pm   
 
Okay sorry my update took so long, the CMUDFormat bit was quick, but the Codec problem was a little trickier to solve, of course after the fact it seems almost basic and obvious.

CMUDFormat is a boolean property which determines how the lists being returned work.
Set CMUDFormat to true value and it will send the SELECT results back in in CMUD's list format.
If UseMap is true ( and really it always should be:p) the underlying DB var is still a ZMUD dbvar but CMUD has no problems understanding that
and you can always manually make a CMUD style DB var using expanddb.

Codec is a string property which determines which codec is used to decode and return the data from your database.
the default value is now latin1( or iso8859-1) which will handle English and other Western European languages.


@Anaristos this means that you can avoid using %vartype and %leftback and all that. If CMUDFormat is set to true you can remove both the %leftback( %right( $entry, 1), 1)
and the $entry = %item($entry,1) and your test script will run fine, as a bonus it also means that you dont have to replace | in $classlist to store them, nested lists arent a problem with this format.

@Xekon with CMUDFormat enabled the problems demonstrated by your above test scripts are resolved.
since I've changed the default codec to latin1 you won't even have to worry about the ":::Manicúla Sutta:::" issue
but please try switching the Codec to utf-8 or ascii or some other valid codec, you'll see that the ú is replaced by ? so it's still important to know what codec your data is written in.
As for the "Portal String" bug well ZMUD list's have a difficult time with nested ( and ) the CMUDFormat will will remove that particular issue.
_________________
-Dharkael-
"No matter how subtle the wizard, a knife between the shoulder blades will seriously cramp his style."		 Reply with quote
Anaristos
Sorcerer


Joined: 17 Jul 2007
Posts: 821
Location: California
PostPosted: Sun Apr 27, 2008 6:46 pm   
 
Thanks for all the work, Dharkael.
There is one problem, though. Python thinks that CMUDFormat is a get only property. I looked at the code and saw nothing obviously wrong. The put routine is there, so somehow the property is getting flagged as read-only. Too bad it defaults to false. :D
_________________
Sic itur ad astra.		 Reply with quote
Dharkael
Enchanter


Joined: 05 Mar 2003
Posts: 593
Location: Canada
PostPosted: Sun Apr 27, 2008 6:52 pm   
 
Don't know what to tell ya Anaristos I've checked the code that's up has both read and write access , what happens if you try to get the value? if it gives an error it's because you're not using the updated version
As usual you have to unregister then reregister to get access to a Changed API.
Probably best to restart CMUD after that.
_________________
-Dharkael-
"No matter how subtle the wizard, a knife between the shoulder blades will seriously cramp his style."		 Reply with quote
Anaristos
Sorcerer


Joined: 17 Jul 2007
Posts: 821
Location: California
PostPosted: Sun Apr 27, 2008 7:24 pm   
 
Well, I've registered and unregistered the com object several times with the same result. It should work, I agree. For the time being I've defaulted the property to true (since I don't plan to return to zMUD), so that should resolve the problem for the time being.
An attempt to set the property returns with the Python message "get_CMUDFormat() takes no arguments (1 given)".
I am thinking that maybe I am doing it wrong...
Is this how?
Code:

#CALL @comsql.CMUDFormat(true)
_________________
Sic itur ad astra.		 Reply with quote
Dharkael
Enchanter


Joined: 05 Mar 2003
Posts: 593
Location: Canada
PostPosted: Sun Apr 27, 2008 7:37 pm   
 
Code:
comsql.CMUDFormat=True
comsql.CMUDFormat=1
#call %comset(comsql,CMUDFormat,"True")

etc.
It's a property not a method so the syntax is a little different
_________________
-Dharkael-
"No matter how subtle the wizard, a knife between the shoulder blades will seriously cramp his style."		 Reply with quote
Anaristos
Sorcerer


Joined: 17 Jul 2007
Posts: 821
Location: California
PostPosted: Sun Apr 27, 2008 7:51 pm   
 
Thanks, Dharkael. Learn something new everyday. I should have looked at the book instead of assuming that what works in one language will work in another.
_________________
Sic itur ad astra.		 Reply with quote
Dharkael
Enchanter


Joined: 05 Mar 2003
Posts: 593
Location: Canada
PostPosted: Sun Apr 27, 2008 8:05 pm   
 
No Probs! Between you guys and myself this thing is shaping up quite nicely I think.
_________________
-Dharkael-
"No matter how subtle the wizard, a knife between the shoulder blades will seriously cramp his style."		 Reply with quote
Anaristos
Sorcerer


Joined: 17 Jul 2007
Posts: 821
Location: California
PostPosted: Sun Apr 27, 2008 8:12 pm   
 
It's awesome, I think. I am full production now with it and I love the speed and the possibilities. Thanks for all the work. I am actively looking at Python now for my plugin applications. I tried writing a COM object for CMUD before and while Vista agreed that it was quite functional, CMUD didn't like it at all. With Python things are a lot smoother.
_________________
Sic itur ad astra.		 Reply with quote
xekon
Apprentice


Joined: 11 Oct 2007
Posts: 154
PostPosted: Mon Apr 28, 2008 12:41 am   
 
OK I need another helping hand, im not really sure what im doing here lol, ive gotten good at fetching data but not the other way around

I did some searching for a query I could just run from my sqlite3 ide, to acomplish this but i couldnt find one, so i figured I would just do it through cmud... sooo heres what im basically trying to do, im trying to capitalize the first letter of whatever is in the type field of my alleq table. (the logic is there however im sure the syntax is all sorts of wrong):


Code:
#for @eqall.Execute(%format("UPDATE alleq SET type = &s",%proper(%db(%i,"type")))) {}

or
Code:
#for @eqall.Execute("SELECT * FROM alleq") {
UPDATE alleq SET type=%proper(%db(%i,"wear"))
}
Reply with quote
Dharkael
Enchanter


Joined: 05 Mar 2003
Posts: 593
Location: Canada
PostPosted: Mon Apr 28, 2008 2:11 am   
 
Okay because Sqlite does not have a proper function you would have to use a function outside of your UPDATE statement to update.
Which means you have to get the values out first
proper doesn't work correctly in CMUD atm if your first character is not a letter
so use this function
Code:
#FUNC proper($string) {#RETURN %subregex($string,"^(\w)(.*)","%upper(%pat(1))%pat(2)")}


And here's your code
Code:
$t1=%secs
#for @eqall.Execute("SELECT DISTINCT type FROM alleq") {#call @eqall.Execute("UPDATE alleq SET type=? WHERE type=?",@proper(%trim(%db(%i,type))),%db(%i,type))}
$t2=%secs
#echo ($t2-$t1)


It takes several seconds, using the DB you sent me.
Btw you can speed up lookup times on your database with a few well chosen indexes.
_________________
-Dharkael-
"No matter how subtle the wizard, a knife between the shoulder blades will seriously cramp his style."		 Reply with quote
Display posts from previous:   
Post new topic   Reply to topic     Home » Forums » Finished MUD Scripts All times are GMT
Goto page Previous  1, 2, 3, 4, 5, 6, 7, 8  Next
Page 5 of 8

 
Jump to:  
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

© 2009 Zugg Software. Hosted by Wolfpaw.net