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: Mon Apr 28, 2008 2:36 am   
 
Awesome! well I just finished rewriting a ton of stuff for the way i searched for stuff through my script to search the db....

Im really having a ton of fun with this.
Reply with quote
Dharkael
Enchanter


Joined: 05 Mar 2003
Posts: 593
Location: Canada

PostPosted: Mon Apr 28, 2008 3:55 am   
 
Basically in SQL an index is like the index of a book it keeps things in a sorted order to make it easy to find.
In a book when you're looking for something under a particular section you can quickly scan the index find what you're looking for and then turn to that page,
rather than having to thumb through the whole book until you find what you want.
Sqlite basically keeps another table with your column data from each row of the column(s) in your index in sorted order with a reference to the rowid that the data is found in.
When you search using an indexed column sqlite can search the indexed table quickly using a binary search and quickly determine what rows match the criteria placed on the index column(s)
It can then do another binary search on the original table using the rowid (which is a unique column sqlite automatically creates for each row in your table)
now it's doing two searches but they're binary searches and it doesn't have to scan the entire unsorted table to get the rows that match your criteria
it doesn't have to be unique,the more distinct the items in the indexed column(s) are the more effective the index is when you search.
Now indexes do cause some slow down for inserts and updates that affect an indexed column since the data has to be updated.
So it's not a good idea to just index every column, but if you search often using a particular column and has enough distinctiveness the speed gains are well worth it.


EDIT:I didn't notice you edited your previous post, so now I'm answering a question you didn't even ask :p
_________________
-Dharkael-
"No matter how subtle the wizard, a knife between the shoulder blades will seriously cramp his style."

Last edited by Dharkael on Mon Apr 28, 2008 3:13 pm; edited 1 time in total
Reply with quote
Dharkael
Enchanter


Joined: 05 Mar 2003
Posts: 593
Location: Canada

PostPosted: Mon Apr 28, 2008 3:58 am   
 
Added a standard deviation extension function to Sqlite3 so you can do something like SELECT STDDEV(price) FROM Sales .......
Also if you updated after todays earlier update please do so again.
I made an error wherein if your Execute statement does not return data like in any non SELECT statement or a SELECT that returns nothing.
the LastError message was updated to give a message like 'NoneType is not iterable' everything still worked but still that might confuse some people, my bad.
_________________
-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: Wed Apr 30, 2008 6:16 am   
 
I am getting an "error is 'NoneType' object is not iterable" from LastError(). In fact, there are no errors, I only display this text when the string returned is non-empty.
_________________
Sic itur ad astra.
Reply with quote
Dharkael
Enchanter


Joined: 05 Mar 2003
Posts: 593
Location: Canada

PostPosted: Wed Apr 30, 2008 7:41 am   
 
Yeah just get the latest.
As I said in the post above, I had noted and corrected this last sunday.
When I was working out the unicode stuff, I had moved an if check and neglected to put it back in place in the code I released earlier on sunday.
_________________
-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: Thu May 01, 2008 6:45 pm   
 
I need a little help with what im working with now.

I have a very awesome script for searching through my db using all sorts of parameters.
I have been able to add items to the DB as well.

I am creating a new table that stores the gear I personally own, and what I would like to do is be able to modify record values in this table, or even delete records completely. So I figure every record having a unique identifier will accomplish that nicely.

How would I go about this, is Autonumber an available feature?

Also could somebody please post an example of modifying a record?

Thanks so much :)
Reply with quote
Dharkael
Enchanter


Joined: 05 Mar 2003
Posts: 593
Location: Canada

PostPosted: Thu May 01, 2008 7:36 pm   
 
xekon wrote:
Autonumber an available feature?

Yes in Sqlite it's called Autoincrement
xekon wrote:
Also could somebody please post an example of modifying a record?

I already have, your question about changing the types to Proper case.
Code:
#call @eqall.Execute("UPDATE alleq SET type=? WHERE type=?",@proper(%trim(%db(%i,type))),%db(%i,type))

This of course would update multiple records so a unique ID or a set of params that point to a unique row would be needed to ensure you update or delete only one record
UPDATE is the statement you're looking for to modify.
DELETE for well....

Quote:
In SQLite, every row of every table has an integer ROWID. The ROWID for each row is unique among all rows in the same table.

So if you don't specify a column with INTEGER PRIMARY KEY or INTEGER PRIMARY KEY AUTOINCREMENT
a rowid column is still created even if it's not visible, if you do create a column with one of those two declarations then rowid becomes an alias for that column
Code:
#call @eqall.Execute("DELETE FROM alleq WHERE rowid=?",@row_to_be_deleted)



EDIT: if you're creating a new table , I would create the first column with a INTEGER PRIMARY KEY AUTOINCREMENT that can be used to ID unique rows
It will of course alias to rowid, but it will be something that's visible if you do something like SELECT * FROM mygear WHERE ....
where if you dont create the INTEGER PRIMARY KEY explicitly you'll have to do SELECT rowid,* FROM mygear WHERE ....

Also without the AUTOINCREMENT the INTEGER PRIMARY KEY would require you to supply the unique integer for each row.
_________________
-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: Thu May 01, 2008 8:27 pm   
 
Dharkael, since this table will hold my personal gear i dont see the number of records in the table ever exceeding 400-500.

I did not realize there was already the ROWID that was created, with that I really dont see the need for a second row that does pretty much the same thing....
Could I not just make all of my Select statements for this table in this fashion:

Code:
SELECT rowid,* FROM mygear WHERE ....


I cannot think of any reason I couldnt but maybe I am over looking something.
Reply with quote
Dharkael
Enchanter


Joined: 05 Mar 2003
Posts: 593
Location: Canada

PostPosted: Thu May 01, 2008 8:36 pm   
 
Whichever way you go is fine, btw its not a second column, its aliased.
Two names same column, one name created implicitly one explicitly, but always the same column.
But yeah you don't need to create it explicitly if you don't want.
One case where you would want to create and thus name it explicitly is where you're using the relational aspect of databases.
Where a RowID in one table might be linked to another table, it wouldn't do to go around having the unique ID for each table named rowid.
If it's called something like GearID or something then you know you're refering to the unique ID in in the Gear Table or whatever.
But if you plan to use the table without ever relating it to another table then the implicit rowid will do the trick.


EDIT: This Blog Post gives a compelling argument as why its always a good idea to explicit create
the INTEGER PRIMARY KEY AUTOINCREMENT column
_________________
-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: Thu May 01, 2008 9:03 pm   
 
OH! I understand either way I go I will have the RowID, I should have read your statement closer. I will definately explicitly create it then. I thought the ROWID exists REGARDLESS of weather or not you create INTEGER PRIMARY KEY AUTOINCREMENT (as in the explicitly declared column being an addition to the ROWID), but if you do then RowID becomes an alias to the explicitly declared column
Reply with quote
Dharkael
Enchanter


Joined: 05 Mar 2003
Posts: 593
Location: Canada

PostPosted: Thu May 01, 2008 9:05 pm   
 
Now you have it "five by five"
_________________
-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 May 02, 2008 4:23 am   
 
Dharkael Ive emailed you an updated copy of my alleq.db3 file.

I have another issues and figured I would provide you with my db again because then you can see my problem from your end.

When I run the alias insert stuff it complains that there are 30 columns in the table but only 29 values provided but this is intentional because the one i did not proivde is the auto incrementing Primary key integer.

Error after INSERT : table myequip has 30 columns but 29 values were supplied


Last edited by xekon on Sun May 04, 2008 4:38 am; edited 3 times in total
Reply with quote
Dharkael
Enchanter


Joined: 05 Mar 2003
Posts: 593
Location: Canada

PostPosted: Fri May 02, 2008 4:26 am   
 
pass NULL as the value to your autoincrement column.

P.S.
You really should avoid using concat directly to pass values, it doesn't automatically escapes things that need to be escaped.
the qmark style will and you can avoid putting in all those single quotes
_________________
-Dharkael-
"No matter how subtle the wizard, a knife between the shoulder blades will seriously cramp his style."

Last edited by Dharkael on Fri May 02, 2008 4:42 am; edited 3 times in total
Reply with quote
xekon
Apprentice


Joined: 11 Oct 2007
Posts: 154

PostPosted: Fri May 02, 2008 4:30 am   
 
Code:
Error after INSERT : 'NoneType' object is not iterable
insert into myequip values('the Amulet of Aardwolf','1','','Hold','Portal','12','2','2','2','2','2','2','','','','','','','','','','','','','','','','amulet aardwolf (185403)','(185403)',NULL)


Last edited by xekon on Sun May 04, 2008 4:38 am; edited 1 time in total
Reply with quote
Dharkael
Enchanter


Joined: 05 Mar 2003
Posts: 593
Location: Canada

PostPosted: Fri May 02, 2008 4:33 am   
 
Okay I'm gonna have to ask when you last updated the component, because there have already been two posts about the NoneType since Sunday last. If you haven't updated since late late Sunday then please do so.
_________________
-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 May 02, 2008 4:33 am   
 
ahhh ok i think missing the last update or so let me give that a go first.

WOOO! you are the man as usual Dharkael, thanks again for the help, and very quick response.
Reply with quote
Dharkael
Enchanter


Joined: 05 Mar 2003
Posts: 593
Location: Canada

PostPosted: Fri May 02, 2008 4:44 am   
 
P.P.S Even if using concat when passing a number to a field you don't need to quote it
_________________
-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 May 02, 2008 4:13 pm   
 
Sometimes the value I pass is actually a Null value and by just encapsulating them all in quotes it works...
Reply with quote
xekon
Apprentice


Joined: 11 Oct 2007
Posts: 154

PostPosted: Sun May 04, 2008 3:42 am   
 
Im having trouble with a couple odd special characters in certain situations... going to try and make a list of all the ways it does it then report back.


Last edited by xekon on Sun May 04, 2008 5:11 am; edited 3 times in total
Reply with quote
xekon
Apprentice


Joined: 11 Oct 2007
Posts: 154

PostPosted: Sun May 04, 2008 3:59 am   
 
I know you said that not to use %concat to directly pass values, how about using %format and a bunch of those &s thingys instead of the bunch of ????? is that ok?
Reply with quote
xekon
Apprentice


Joined: 11 Oct 2007
Posts: 154

PostPosted: Sun May 04, 2008 4:18 am   
 
Ok this one works for me but its giving me a little weirdness when certain characters are present in fields.
Code:

#call @eqall.Execute(%format("insert into myequip values(&s,&s,&s,&s,&s,&s,&s,&s,&s,&s,&s,&s,&s,&s,&s,&s,&s,&s,&s,&s,&s,&s,&s,&s,&s,&s,&s,&s,&s,&s,&s)", %concat("'",@eqitem.name,"'"), %concat("'",@eqitem.level,"'"), %concat("'",@eqitem.worn,"'"), %concat("'",@eqitem.wear,"'"), %concat("'",@eqitem.type,"'"), %concat("'",@eqitem.totstats,"'"), %concat("'",@eqitem.str,"'"), %concat("'",@eqitem.int,"'"), %concat("'",@eqitem.wis,"'"), %concat("'",@eqitem.dex,"'"), %concat("'",@eqitem.con,"'"), %concat("'",@eqitem.luck,"'"), %concat("'",@eqitem.saves,"'"), %concat("'",@eqitem.damroll,"'"), %concat("'",@eqitem.hitroll,"'"), %concat("'",@eqitem.hp,"'"), %concat("'",@eqitem.mana,"'"), %concat("'",@eqitem.moves,"'"), %concat("'",@eqitem.wtype,"'"), %concat("'",@eqitem.dam_avg,"'"), %concat("'",@eqitem.wflags,"'"), %concat("'",@eqitem.weight,"'"), %concat("'",@eqitem.notes,"'"), %concat("'",@eqitem.keep,"'"), %concat("'",@eqitem.dam_type,"'"), %concat("'",@eqitem.resist_phy,"'"), %concat("'",@eqitem.resist_mag,"'"), %concat("'",@eqitem.keywords,"'"), %concat("'",@eqitem.aardnum,"'"),NULL, %concat("'",@eqitem.flags,"'")))


Last edited by xekon on Sun May 04, 2008 4:37 am; edited 1 time in total
Reply with quote
xekon
Apprentice


Joined: 11 Oct 2007
Posts: 154

PostPosted: Sun May 04, 2008 4:20 am   
 
OK I figured out the problem, these two items when i try to add them:


(K)(G)(H) _-/Troika's Revenge\-_ (200)
(K)(G)(H) Agrizar's Fanon of Strength (200)

it dont work because of the single quote in both of their names.

OK im not sure if this is the right way to go about this, but it seems to be working:

Code:
#addkey eqitem name %replace(@eqitem.name,"'","''")


Last edited by xekon on Sun May 04, 2008 6:36 am; edited 2 times in total
Reply with quote
xekon
Apprentice


Joined: 11 Oct 2007
Posts: 154

PostPosted: Sun May 04, 2008 4:47 am   
 
W00ts figured out how to make my table with the constraints i wanted.

Code:
#call @stuff.Execute("create table myequip (RowID integer NOT NULL Primary Key, name, level, worn, wear, type, totstats, str, int, wis, dex, con, luck, saves, damroll, hitroll, hp, mana, moves, wtype, dam_avg, wflags, flags, weight, notes, keep, dam_type, resist_phy, resist_mag, keywords, aardnum)")
Reply with quote
xekon
Apprentice


Joined: 11 Oct 2007
Posts: 154

PostPosted: Sun May 04, 2008 5:44 am   
 
Only Unresolved Problem (I kinda did a lot today, i know, i can ramble some times, hehe)

OK when I query my alleq DB and these items are part of the query results There is a carriage return inserted after the item name.... making the rest of the line that should have continued bumped down a line. I should note that I am using Cmud, so i have cmud output set to 1.

<*><|Sash of Jord|><*>
<*><|Fenris Fangs|><*>
<*><|Modi's Courage|><*>
<*><|Asgardian Greaves|><*>
<*><|Brisings' Necklace|><*>
<*><|Strength of Magni|><*>
\|/ Ogham Bark \|/
\|/ The Yellow Book of Lechan \|/
=|EarthSword>
||A Book of Troll Poetry||
[|Pencil|>
|white toga|
|=|=|Footsteps of Evil|=|=|
/-|Strength Of Perseverance|-\

"blade named ""Stormbringer"""
"black hilted sword called, ""Dwarf Slayer"""
"Holy Avenger ""Faith Restorer"""
"Holy Avenger ""Faith Restorer"""

Heres a picture of what im looking at:
Reply with quote
Dharkael
Enchanter


Joined: 05 Mar 2003
Posts: 593
Location: Canada

PostPosted: Sun May 04, 2008 1:53 pm   
 
Xekon wrote:
I know you said that not to use %concat to directly pass values, how about using %format and a bunch of those &s thingys instead of the bunch of ????? is that ok?

Xekon wrote:
it dont work because of the single quote in both of their names.

These two things are directly related by using the qmark to insert values it automatically takes care of quoting single quotes for you.
I'm not sure why you wouldn't want to use it its cleaner and easier to use...
Instead of your above code you can get the same thing done with
Code:
#call @eqall.Execute("INSERT INTO myequip values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,NULL,?)",
 @eqitem.name, @eqitem.level, @eqitem.worn, @eqitem.wear, @eqitem.type,
 @eqitem.totstats, @eqitem.str, @eqitem.int, @eqitem.wis, @eqitem.dex, @eqitem.con, @eqitem.luck, @eqitem.saves, @eqitem.damroll, @eqitem.hitroll,
 @eqitem.hp, @eqitem.mana, @eqitem.moves, @eqitem.wtype, @eqitem.dam_avg, @eqitem.wflags, @eqitem.weight,  @eqitem.notes, @eqitem.keep,
 @eqitem.dam_type, @eqitem.resist_phy, @eqitem.resist_mag, @eqitem.keywords, @eqitem.aardnum,@eqitem.flags))

I can't find the advantage to using %format or %concat to get this done.

I'm also currently working on a way to pass values using named parameters;
So you would be able to get the same thing done with something like
Code:

#call @eqall.Execute("INSERT INTO myequip values(:name,:level,:worn,:wear,:type,:totstats,:str,:int,:wis,:dex,:con,:luck,
:saves,:damroll,:hitroll,:hp,:mana,:moves,:wtype,:dam_avg,:wflags,:weight,:notes,:keep,:dam_type,:resist_phy,:resist_mag,:keywords,:aardnum,NULL,:flags",@eqitem)


It's not quite done yet though, so I'll let you know when I've posted it.

Okay as for your current problem with the terminal carriage returns, I don't add them anywhere in the code so they must be in your data.
Try using %trim to strip data before you add the data or when you retrieve it.
I'm not sure if %trim only works on spaces instead of any whitespace so experiment and find out.
If it's not removing carriage returns then this function will do the trick
Code:
#FUNCTION trimws($orig_str) {#RETURN %subregex($orig_str,"(\A\s+)|(\s+\Z)")}

It should remove any whitespace at the beginning and end of the string, but leave internal whitespaces intact.
_________________
-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 6 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