 |
Dharkael
Enchanter
Joined: 05 Mar 2003
Posts: 593
Location: Canada
|
 Posted: Thu Apr 03, 2008 10:09 am
Python Sqlite COM Component for ZMUD/CMUD
|
This script requires that you have ActivePython installed at least version 2.5
It is a Windows Script Component so you copy the code into a text file and save it with a WSC extension
for example Sqlite.ZMUD.WSC
Right click on the file and chose register
You would then see a message indicating if it was successfully registered
And can now be created like any COM component
You can then create this in ZMUD or CMUD for that matter with %comcreate("Sqlite.ZMUD.WSC")
Keep in mind "Sqlite.ZMUD.WSC" is the ProgID and is independent of the file name Although it makes sense
To save this file with the name Sqlite.ZMUD.WSC its less confusing that way
This Script will allow you to Create and use Sqlite3 databases from ZMUD or CMUD
| Code: |
<?xml version="1.0" ?>
<package>
<comment>
Dharkael's attempt at a SQlite for zMUD
</comment>
<component >
<?component error="true" debug="true" ?>
<registration progid="Sqlite.ZMUD.WSC" classid="{233E657B-9F36-49BB-A43E-F8AB44DD59B3}" description="Sqlite Access from ZMUD" version="1.0">
</registration>
<public>
<property name="Path">
<get internalName="get_Path"/>
</property>
<property name="LastError">
<get internalName="LastError"/>
</property>
<property name="AbsolutePath">
<get/>
</property>
<property name="UseMap">
<get internalName="get_UseMap"/>
<put internalName="put_UseMap"/>
</property>
<property name="ResultSep">
<get internalName="get_ResultSep"/>
<put internalName="put_ResultSep"/>
</property>
<property name="KeyValueSep">
<get internalName="get_KeyValueSep"/>
<put internalName="put_KeyValueSep"/>
</property>
<property name="HashEntrySep">
<get internalName="get_HashEntrySep"/>
<put internalName="put_HashEntrySep"/>
</property>
<property name="AutoCommit">
<get internalName="get_AutoCommit"/>
<put internalName="put_AutoCommit"/>
</property>
<property name="IsOpen">
<get internalName="get_IsOpen"/>
</property>
<property name="NullString">
<get internalName="get_NullString"/>
<put internalName="put_NullString"/>
</property>
<property name="CMUDFormat">
<get internalName="get_CMUDFormat"/>
<put internalName="put_CMUDFormat"/>
</property>
<property name="Codec">
<get internalName="get_Codec"/>
<put internalName="put_Codec"/>
</property>
<method name="Open">
<parameter name="path"/>
</method>
<method name="Close"/>
<method name="Commit"/>
<method name="Rollback"/>
<method name="PathExists">
<parameter name="path"/>
</method>
<method name="Execute">
<parameter name="SQLString"/>
</method>
<method name="Exec" internalName="Execute">
<parameter name="SQLString"/>
</method>
<method name="ExecuteScript">
<parameter name="SQLString"/>
</method>
</public>
<script language="Python">
<![CDATA[
# -*- coding: latin1 -*-
import sqlite3
import re
import os
import math
_conn = None
_path = u""
_usemap = True
_resultsep =unicode("|")
_keyvalsep =unicode("\x1E")
_hashentrysep =unicode("\x1D")
_autocommit = True
_lastError =u""
_nullAs = u""
_CMUDFormat = False
_codec = u"latin1"
def ourcode(x):
return unicode(x,_codec,errors='replace')
def quote_esc(mo):
return u'""'.encode(_codec)
def CMUD_LIE(item):
item = re.sub("""\"""",quote_esc,item)
if re.search("""\"|\|""",item):
item = u'"'.encode(_codec)+item + u'"'.encode(_codec)
return item
def regexp(pat,str):
return re.search(pat,str) != None
def match(pat,str):
return re.match(pat,str) != None
def get_AbsolutePath():
if _path == ":memory:" or _path == "":
return _path
return os.path.abspath(_path)
def _null_(arg):
if arg==None:
return _nullAs.encode(_codec)
if isinstance(arg,basestring):
return arg
return ourcode(str(arg))
def GetData(cursor):
cdata = cursor.fetchall()
if len(cdata) ==0:
return u""
desc = [x[0] for x in cursor.description]
if _CMUDFormat:
if _usemap:
return _resultsep.join([
_hashentrysep.join(
map( lambda k,v: CMUD_LIE(k )+ CMUD_LIE(_keyvalsep) + CMUD_LIE( _null_(v) ) ,desc,data)
) for data in cdata
])
return _resultsep.join([
(lambda k:
CMUD_LIE(u"|".join( [CMUD_LIE(_null_(l)) for l in k] ) )
)(x)
for x in cdata
])
else:
if _usemap:
return _resultsep.join([
u"("+
_hashentrysep.join(
map(lambda k,v: k+_keyvalsep+_null_(v) ,desc,data)
)
+u")"
for data in cdata
])
return _resultsep.join([
(lambda k:
u"("+u"|".join([_null_(l) for l in k])+u")"
)(x)
for x in cdata
])
def PathExists(path):
return os.path.exists(path)
def get_IsOpen():
return _conn <> None
def get_Path():
return _path
def get_UseMap():
return _usemap
def put_UseMap(bVal):
global _usemap
_usemap = bool(bVal)
def get_ResultSep():
return _resultsep
def put_ResultSep(sep):
global _resultsep
_resultsep = str(sep)
def get_KeyValueSep():
return _keyvalsep
def put_KeyValueSep(sep):
global _keyvalsep
_keyvalsep = str(sep)
def get_AutoCommit():
return _autocommit
def put_AutoCommit(bVal):
global _autocommit
_autocommit = bool(bVal)
def get_NullString():
return _nullAs
def put_NullString(nullStr):
global _nullAs
_nullAs = nullStr
def get_CMUDFormat():
return _CMUDFormat
def put_CMUDFormat(bVal):
global _CMUDFormat
_CMUDFormat = bool(bVal)
def get_Codec():
return _codec
def put_Codec(codec):
global _codec
_codec = unicode(codec)
def get_HashEntrySep():
return _hashentrysep
def put_HashEntrySep(sep):
global _hashentrysep
_hashentrysep = str(sep)
def LastError():
return _lastError
def Open(path=u""):
global _conn,_path,_lastError
_lastError =u""
if path == u"" or path == u":memory:":
_path = u":memory:"
else:
_path = str(path)
try:
_conn = sqlite3.connect(_path)
_conn.text_factory=ourcode
_conn.create_function(u"regexp", 2, regexp)
_conn.create_function(u"match", 2, match)
_conn.create_aggregate(u"STDDEV",1,StandardDev)
return get_AbsolutePath()
except Exception,e:
_lastError = e.message
_conn = None
_path = u""
raise
def Close():
global _path,_conn
try:
_conn.close()
except:
pass
_path = u""
_conn = None
def Commit():
if _conn:
_conn.commit()
def Rollback():
if _conn:
_conn.rollback()
def Execute(sql,*args):
global _lastError
if _conn:
parms = None
_lastError = ""
if (len(args) == 1) and not isinstance(args[0],(float,int,complex,long,str,unicode)):
try:
iter = args[0].__iter__()
except AttributeError:
params = tuple(args)
else:
params = tuple(args[0])
else:
params = tuple(args)
try:
ret = _conn.execute(sql,params)
if _autocommit:
_conn.commit()
return GetData(ret)
except Exception,e:
_lastError = e.message
return None
else:
_lastError = u"Cannot operate on a closed database."
raise sqlite3.ProgrammingError(u"Cannot operate on a closed database.")
def ExecuteScript(sql):
global _lastError
if _conn:
_lastError =u""
try:
ret = _conn.executescript(sql)
if _autocommit:
_conn.commit()
return GetData(ret)
except Exception,e:
_lastError = e.message
return None
else:
_lastError = u"Cannot operate on a closed database."
raise sqlite3.ProgrammingError(u"Cannot operate on a closed database.")
#I'm going to put the extension functions and helpers below here
def mean(numbers):
"Returns the arithmetic mean of a numeric list."
return sum(numbers) / len(numbers)
class StandardDev:
global math
global mean
def __init__(self):
self.data = []
def step(self,value):
self.data.append(float(value))
def finalize(self):
if len(self.data) == 0:
return 0
mavg = mean(self.data)
for x in range(0,len(self.data)):
value = self.data[x] - mavg
self.data[x] = value * value
sval = sum(self.data)/ (len(self.data))
self.data =[]
return math.sqrt(sval)
]]>
</script>
</component>
</package> |
Here is a little documentation 
| Quote: |
[Method]
Execute(SQLString,[OptionalArgs]);
Executes an SQL Statement;
If the statement is parametized (statement contains '?' characters that are substituted from the OptionalArgs),
there must be an additional argument for each '?' character for each parameter, if not an Exception is raised.
Raises Exception if the Database is not open or if there are errors in your SQL Statement.
Returns a ZMUD Stringlist with an item for each result when your statement is executed.
If the UseMap property of this component evaluates to the boolean the item is a ZMUD database variable whose
key/value pairs represents the Results columns/values
if UseMap evualuates false then the item contains a stringlist sorted by the results columns indexes
(Note if can also pass in a variant array created using the %array command, the array should contain the values necessary
for the bind parameters if you use a variant array it must be the 2nd argument and there must not be any further arguments passed
[Method]
Exec(SQLString,[OptionalArgs]);
An alias for the Execute method.
[Method]
Close();
Closes the database if open, does nothing otherwise.
Subsequence calls to Execute will raise an Exception until another database is opened
[Method]
Open(path);
Opens a database using the path argument, if the database cannot be opened an Exception is raised.
If open is successfully it returns the absolute path of the database opened
if path is an empty string "" or NULL or the case sensitive string ":memory:" the database is opened in memory and no file is created.
[Property]
IsOpen();
returns boolean value Indicating if there is currently an open database (ie it is safe to call Execute)
[Method]
PathExists(path);
Checks if there is an existing file(or directory) at the path given.
Returns boolean value indicating exists of file at path given.
Path may be absolute or relative.
[Property]
HashEntrySep();
HashEntrySep(newsep)
The HashEntrySep property is a get/set property
When UseMap is true and the results of Execute is being returned as a ZMUD DB variable
This property allows you to change the string thats used to seperate one entry from another.
The default value is the character that has an ascii decimal value of 29
[Property]
KeyValueSep();
KeyValueSep(newsep);
The KeyValueSep property is a get/set property
Serves a purpose similar to the HashEntrySep however it is used to seperate
a key from a value
The default value is the characeter that has an ascii decimal value of 30
[Property]
ResultSep();
ResultSep(newsep);
Also a get/set property
When multiple results are return from an Execute the ResultSep
is used to seperate each result.
the default character is a pipe "|" which in zMUD is used to seperate stringlist values
[Property]
NullString();
NullString(NullStr);
A get/set property
Determines how a NULL in a ResultSet is represented.
The default value is an empty string ''
[Property]
UseMap();
UseMap(bool);
a get/set property
when UseMap is true each result from Execute is encoded in the form of a ZMUD DB variable
[Property]
CMUDFormat();
CMUDFormat(bool);
a get/set property
When CMUDFormat is true a CMUD style list as opposed to ZMUD style list is returned.
The default is false
[Property]
Codec();
Codec(codecStr);
A get/set property
Assign a string to Codec set which codec should be used to interpret and return data from your database.
The string assigned should be one of Python's standard codecs or codec aliases.
A comprehensive list can be found at http://docs.python.org/lib/standard-encodings.html
Characters that do not map to the specified codec will be silently replaced generally with a "?"
The default codec set is latin1
[Property]
AbsolutePath();
a get only property
returns the Absolute path to the currently open database or an empty string if there are no databases open
[Property]
Path()
A get only propety
like AbsolutePath but it can return a relative path if for example open is called with a value of "test.sq3"
Path returns "test.sq3"
Where AbsolutePath may return "C:\ZMUD\test.sq3"
[Method]
ExecuteScript(SQLString,[OptionalArgs]);
Useful for executing multiple SQL statements at once, issues a COMMIT before executing script.
The SQL statements may not be parametrized, in other words the script is executed verbatim, no substitutions.
Returns a ZMUD Stringlist with an item for each result when your statement is executed.
If the UseMap property of this component evaluates to the boolean the item is a ZMUD database variable whose
key/value pairs represents the Results columns/values
if UseMap evualuates false then the item contains a stringlist sorted by the results columns indexes
[Method]
Commit();
Explicitly commits any open transactions,a must use if AutoCommit is set to false.
Added Rollback method, erases any changes made since last transaction opened.
[Method]
Rollback();
Erases any changes made since last transaction opened.
[Property]
AutoCommit();
AutoCommit(bVal);
The AutoCommit is get/set property.
Defaults to True
AutoCommit is a Boolean value that when set to True will automatically call Commit(write changes to the filesystem) after every call to execute or executescript.
The Python helps state "By default, the sqlite3 module opens transactions implicitly before a Data Modification
Language (DML) statement (i.e. INSERT/UPDATE/DELETE/REPLACE), and commits transactions implicitly before a non-DML,
non-query statement (i. e. anything other than SELECT/INSERT/UPDATE/DELETE/REPLACE)."
For us that means if you have AutoCommit set to false and issue a series of INSERT/UPDATE/REPLACE statements,
then forget to call Commit() before closing the database, all changes since transaction opened are lost.
[Property]
LastError();
LastError is readonly property.
After a call to Execute,ExecuteScript, or Open, if LastError is empty then no errors encountered, otherwise
contains a string describing the last error.
|
Properties should be called without parens.
Reading a property:
| Code: |
| #echo @stuff.IsOpen |
Writing to a property:
Example:
| 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")
#for @stuff.Execute("SELECT * FROM herblist WHERE method=? AND effect REGEXP ?","Eat","(C|c)ure") {#echo ******;#showdb {%i}}
|
Result:
| Quote: |
******
herb: Siriena
method: Eat
effect: Cure for the Black Death.
cures: Black death
******
herb: Courgia
method: Eat
effect: Cure for a few diseases including haemophilia.
cures: Haemophilia
******
herb: Madabril
method: Eat
effect: Often known to cure the plague.
cures: Plague
|
EDIT:
After updating scripts from please Unregister and then Register the file again, so that COM is aware of any changes to API.
Active COM objects will not reflect any new changes. In some cases changes may not be reflected until zMUD/CMUD has restarted.
APR-14-2008/00:50 - Added AutoCommit, Commit, Rollback, ExecuteScript,LastError.
APR-19-2008/20:30 - Added PathExists, and Exec.
APR-22-2008/18:40 - Non UTF-8 strings should no longer cause problems
APR-22-2008/22:40 - Added NullString property, determines how null values in result are represented.
APR-27-2008/11:50 - Added CMUDFormat and Codec property.
APR-27-2008/23:25 - Added STDDEV extension function. |
|
_________________
-Dharkael-
"No matter how subtle the wizard, a knife between the shoulder blades will seriously cramp his style."
Last edited by Dharkael on Thu Jun 19, 2008 2:04 pm; edited 13 times in total |
 |
|
|
MattLofton
GURU
Joined: 23 Dec 2000
Posts: 4834
Location: USA
|
Posted: Thu Apr 03, 2008 9:26 pm
|
Oh, neato. Statement retracted.
|
|
_________________
EDIT: I didn't like my old signature
Last edited by MattLofton on Fri Apr 04, 2008 3:26 am; edited 1 time in total |
|
|
|
Dharkael
Enchanter
Joined: 05 Mar 2003
Posts: 593
Location: Canada
|
Posted: Thu Apr 03, 2008 11:25 pm
|
It most certainly does work with ZMUD.
I did all my testing with ZMUD.
I think you confused the Windows Script Component XML which (I have given instructions in my post how to use) with the XML exported by CMUD
This scripts gives instructions on how to build a COM component using Python and WSC interface. |
|
_________________
-Dharkael-
"No matter how subtle the wizard, a knife between the shoulder blades will seriously cramp his style." |
|
|
|
Dharkael
Enchanter
Joined: 05 Mar 2003
Posts: 593
Location: Canada
|
Posted: Mon Apr 14, 2008 7:59 pm
|
With info garnered from this site,specifically this file
I created databases in zMUD and in SQLite using the same information.
The databases have over 3200 records in them.
Using 3 databases: 1 in zMUD, 1 unindexed in SQLite3 and 1 indexed in SQLite3.
I used the alias below to compare speeds for looking up objects in a decent sized database
These aliases suppose that in zMUD the database window is open with the needed database loaded,
also that the variable conn holds a SQLite.ZMUD.WSC COM object pointing to either the indexed SQlite3 database or the unindexed one.
| Code: |
#CLASS {SEARCH}
#ALIAS searchZDB {a="";last=%secs;#for @searchitems {#additem a %query(&Name="%i")};#show %eval(%secs-@last);#show @a}
#ALIAS searchSDB {a="";last=%secs;#for @searchitems {#additem a @conn.Execute("SELECT rowid FROM equipment WHERE Name =?",%i)};#show %eval(%secs-@last);#show @a}
#VAR a {}
#VAR conn {<OLE object: Sqlite.ZMUD.WSC>}
#VAR last {}
#VAR searchitems {iron chains of bondage|heavy iron warhammer|steel tipped longsword|two-handed sword|War Mattock of the Harbinger|two-handed broadsword|thin two-handed sword|talons of the roc|flamberge|garrote}
#CLASS 0
|
For each subject I ran the appropiate alias 10 times and averaged the elapsed time for each subject.
[SearchZDB] 907,921,906,907,922,922,922,922,906,906 [Avg 914ms]
====
[SearchSDB](unindexed) 62,62,63,63,63,62,62,62,63,62 [Avg 62ms]
====
[SearchSDB](indexed) 16,15,16,16,31,16,16,16,15,16 [Avg 17ms]
Wow what a difference even with the ZMUD->COM->Python->Sqlite3 route
No doubt be orders faster with just a ZMUD->Sqlite3 route.
(Maybe I should make a plugin someday and find out,although plugins still use COM)
And thats not even considering the expressive power of SQL.
I thought it pretty cool how much faster the indexed database was to query. |
|
_________________
-Dharkael-
"No matter how subtle the wizard, a knife between the shoulder blades will seriously cramp his style." |
|
|
|
Fang Xianfu
GURU
Joined: 26 Jan 2004
Posts: 5155
Location: United Kingdom
|
Posted: Mon Apr 14, 2008 11:04 pm
|
blimey. This bodes extremely well for the cmud mapper and database rewrites. Should be able to do more complex mapper queries more often, which is nice.
|
|
|
|
|
Anaristos
Sorcerer
Joined: 17 Jul 2007
Posts: 821
Location: California
|
Posted: Wed Apr 16, 2008 11:31 pm
|
OK, Dharkael, I know I am being dense, so please be patient. Firstly, if I want to use a disk file, do I create it first? If so, what type of extention do I use? That is, will a .txt file suffice? My point is, do I have to setup a SQL database first?
Secondly, I tried your potions example and it worked like a champ, but when I substitue my own stuff, it has no errors, but it doesn't do anything other than open the memory file. I don't know whether data is being stored or not, though I suspect it isn't because the SELECT command returns nothing. I am mimicking your code, just substituting the data, so it should work.
Of course, maybe my data substitution is not good so tell me if this code snippet makes sense:
| Code: |
....
#DBFIRST
#WHILE (!%null(%rec)) {#CALL @comsql.Execute("insert into moblist values(?,?)", %rec.Name, %rec.Room)
#DBNEXT
}
....
|
As you can tell, it is tyring to copy my cMUD database file over to the (:memory:) SQL file. Maybe the problem lies here. |
|
_________________
Sic itur ad astra. |
|
|
|
Anaristos
Sorcerer
Joined: 17 Jul 2007
Posts: 821
Location: California
|
Posted: Thu Apr 17, 2008 1:17 am
|
That I've done and that works, note that I said that his potions example worked just fine. It is when I modify the example to suit my purposes that it stops working. I am trying to insert my own data into the the :memory: file. It is not working.
|
|
_________________
Sic itur ad astra. |
|
|
|
Seb
Wizard
Joined: 14 Aug 2004
Posts: 1269
|
Posted: Thu Apr 17, 2008 1:49 am
|
This sounds awesome! Bookmarked! I haven't tried it, but well done and thanks for sharing, Dharkael.
|
|
|
|
|
Dharkael
Enchanter
Joined: 05 Mar 2003
Posts: 593
Location: Canada
|
Posted: Thu Apr 17, 2008 4:32 am
|
@Seb
No problem 
@Anaristos
If you want to use a disk file, you can either create it in CMUD/ZMUD using the appropriate create statememts or using an external SQlite IDE there are several free ones like
Sqlite Administrator, Sqlite Expert Personal or the SQLite Manager plugin for Firefox (check them all out find the one you like, they each have virtues and vices).
Then when you call Open just point it at your database file.
SQLite Database component doesn't care about file extensions so you can use any that makes sense.
If you name your databse Somedb.txt and that file is actually a sqlite3 database then no problems, if however it just a text file, the Open wont give an error but when you try to use it you'll get errors.
I general use .sl3 .db3 .sq3 or .sqlite3
If the path that you point it to doesn't Exist then Sqlite will create a file there.
Keep in mind that the :memory: database is an in memory database so there's no disk access nothing gets saved.
If you close and reopen the :memory: db then you have to start all over.
After using Execute ExecuteScript etc, you can always check to see what if anything the error was using the LastError property of the component. it gets reset with every use of Execute....
If its an empty string then there was no problem and we'll have to consider what else is going on.
Keep in mind that when you do an anything but a SELECT ,Execute shouldn't return any values.
The code you posted above looks fine, but obviously its not the whole picture.
Did you create a moblist table? notice I created the herblist table us
Try Echoing %rec.Name and %rec.Room to make sure there's actual data being inserted.
If all that fails try posting the code.
You can always start a new Thread and we can get to the bottom of it.
Anyways If you're not that familiar with SQL and Databases maybe read up a bit on the w3schools site and the sqlite site for the specific differences required for SQL
Use one of the IDE's I suggested about and Muck about praticing creating databases and querying them.
Once you have a handle on that then actually scripting this component in CMUD/ZMUD will be quite easy.
And you'll no doubt be storing querying and analysing Realms of data in no time:) |
|
_________________
-Dharkael-
"No matter how subtle the wizard, a knife between the shoulder blades will seriously cramp his style." |
|
|
|
Anaristos
Sorcerer
Joined: 17 Jul 2007
Posts: 821
Location: California
|
Posted: Thu Apr 17, 2008 9:41 am
|
Thanks for the reply, Dharkael.
Here is the complete test code. It basically mimics your example.
| Code: |
comsql = %comcreate( "Sqlite.ZMUD.WSC")
;;
#CALL @comsql.Open(":memory:")
#CALL @comsql.Execute("create table moblist (name,room)")
;;
#DBLOAD mo
#DBFIRST
#WHILE (!%null(%rec)) {#CALL @comsql.Execute("insert into moblist values(?,?)", %rec.Name, %rec.Room);#DBNEXT}
;;
$value = @comsql.Execute("SELECT * FROM moblist order by name")
#ECHO number of values returned is %numitems($value)
|
Now I know that the db was read because I echoed the records to the screen the first time I ran this and got no output.
Basically I am copying my mob database into the sqlite3 table. (I wanted to do some timing estimates...)
I do have some familiarity with SQL databases and I use it extensively in my scripts in %query and %mapquery, though those are not the best examples of SQL around, you can get creative with the format of the query. However, I am no expert so I will read what you recommend. |
|
_________________
Sic itur ad astra. |
|
|
|
Dharkael
Enchanter
Joined: 05 Mar 2003
Posts: 593
Location: Canada
|
Posted: Thu Apr 17, 2008 12:19 pm
|
Your code is perfect, it should do as you expected.
I tried it nearly verbatim changing only the name of the CMUD database and the fields.
Maybe try running it for a small portion of your data and checking what should have went in against errors returned if any, ala:
| Code: |
comsql = %comcreate( "Sqlite.ZMUD.WSC")
;;
#CALL @comsql.Open(":memory:")
#CALL @comsql.Execute("create table moblist (name,room)")
;;
$count=0
#DBLOAD mo
#DBFIRST
#WHILE ((!%null(%rec)) AND ($count < 10)) {
#CALL @comsql.Execute("insert into moblist values(?,?)", %rec.Name, %rec.Room)
#echo Record $count : %rec.Name : %rec.Room
#echo LastError:@comsql.LastError
#echo *******************
$count = $count +1
#DBNEXT
}
;;
$value = @comsql.Execute("SELECT * FROM moblist order by name")
#ECHO number of values returned is %numitems($value) |
And see what if anything pops up. |
|
_________________
-Dharkael-
"No matter how subtle the wizard, a knife between the shoulder blades will seriously cramp his style." |
|
|
|
Anaristos
Sorcerer
Joined: 17 Jul 2007
Posts: 821
Location: California
|
Posted: Thu Apr 17, 2008 11:55 pm
|
OK, I copy/pasted your test code and this is what it returned:
| Code: |
Record 0 : A baby : 23119
LastError:
*******************
Record 1 : A big bat : 23025
LastError:
*******************
Record 2 : A Blood Ring elite warrior : 15312 15322 15323 15329
LastError:
*******************
Record 3 : a Blood Ring noble : 15323 15325
LastError:
*******************
Record 4 : A Blood Ring noble : 15320
LastError:
*******************
Record 5 : A Blood Ring recruit : 15326
LastError:
*******************
Record 6 : A Blood Ring terrorist : 15356
LastError:
*******************
Record 7 : a Blood Ring warrior : 15318 15381
LastError:
*******************
Record 8 : A bodyguard : 15319
LastError:
*******************
Record 9 : a bored noble : 14234 14235 14236 14239
LastError:
*******************
number of values returned is 0
|
Apparently there are no errors creating the table, however nothing is returned. Is there a way to do a memory dump of the file? That is, to see what the memory buffer actually looks like?
I am hoping this can work, I just installed SQLite Expert Professional and I am very excited about it. I can see a lot of potential if all this works. |
|
_________________
Sic itur ad astra. |
|
|
|
Dharkael
Enchanter
Joined: 05 Mar 2003
Posts: 593
Location: Canada
|
Posted: Fri Apr 18, 2008 1:26 am
|
Damn this is annoying.
Okay here's what we're gonna do we're going to leave the in memory file behind and write to disk.
Try this code below changing only the path of file to suite yourself.
| Code: |
comsql = %comcreate( "Sqlite.ZMUD.WSC")
;;
#CALL @comsql.Open("E:\Test.sq3")
#CALL @comsql.Execute("create table if not exists moblist (name,room)")
;;
$count=0
#DBLOAD mo
#DBFIRST
#WHILE ((!%null(%rec)) AND ($count < 10)) {
#CALL @comsql.Execute("insert into moblist values(?,?)", %rec.Name, %rec.Room)
#echo Record $count : %rec.Name : %rec.Room
#echo LastError:@comsql.LastError
#echo *******************
$count = $count +1
#DBNEXT
}
;;
#echo Status @comsql.IsOpen : @comsql.AbsolutePath
$value = @comsql.Execute("SELECT * FROM moblist order by name")
#ECHO number of values returned is %numitems($value) |
Notice I also added another line 3rd from last.
Run that then using whichever SQLite IDE you're using (Sqlite Expert or whatever), Open up that same file and make sure first that it's a valid Sqlite file
and look to see if A the table was created and B if it has any data in it.
Please don't create the file first in your IDE
Let our CMUD component create it.
Let me know what you find out and please once again past the output from the test code. |
|
_________________
-Dharkael-
"No matter how subtle the wizard, a knife between the shoulder blades will seriously cramp his style." |
|
|
|
Anaristos
Sorcerer
Joined: 17 Jul 2007
Posts: 821
Location: California
|
Posted: Fri Apr 18, 2008 3:48 am
|
OK, I did as you requested. The output from the above code is
| Code: |
Record 0 : A baby : 23119
LastError:no such table: moblist
*******************
Record 1 : A big bat : 23025
LastError:no such table: moblist
*******************
Record 2 : A Blood Ring elite warrior : 15312 15322 15323 15329
LastError:no such table: moblist
*******************
Record 3 : a Blood Ring noble : 15323 15325
LastError:no such table: moblist
*******************
Record 4 : A Blood Ring noble : 15320
LastError:no such table: moblist
*******************
Record 5 : A Blood Ring recruit : 15326
LastError:no such table: moblist
*******************
Record 6 : A Blood Ring terrorist : 15356
LastError:no such table: moblist
*******************
Record 7 : a Blood Ring warrior : 15318 15381
LastError:no such table: moblist
*******************
Record 8 : A bodyguard : 15319
LastError:no such table: moblist
*******************
Record 9 : a bored noble : 14234 14235 14236 14239
LastError:no such table: moblist
*******************
Status True : C:\Anaristos\SQLite Expert\databases\Test.db3
number of values returned is 0
|
Sqlite Expert Profesional accepts the file as valid. An integrity check shows that the file is OK. However, the file is empty.
EDIT: It didn't like comments in the table create so I changed it to be exactly as in your previous post:
| Code: |
#CALL @comsql.Execute("create table moblist (name,room)")
|
The result was more to my liking!!!
| Code: |
Record 0 : A baby : 23119
LastError:
*******************
Record 1 : A big bat : 23025
LastError:
*******************
Record 2 : A Blood Ring elite warrior : 15312 15322 15323 15329
LastError:
*******************
Record 3 : a Blood Ring noble : 15323 15325
LastError:
*******************
Record 4 : A Blood Ring noble : 15320
LastError:
*******************
Record 5 : A Blood Ring recruit : 15326
LastError:
*******************
Record 6 : A Blood Ring terrorist : 15356
LastError:
*******************
Record 7 : a Blood Ring warrior : 15318 15381
LastError:
*******************
Record 8 : A bodyguard : 15319
LastError:
*******************
Record 9 : a bored noble : 14234 14235 14236 14239
LastError:
*******************
Status True : C:\Anaristos\SQLite Expert\databases\Test.db3
number of values returned is 10
|
However, and very strange. SEP shows the table as being empty. Let me do one more thing....
AHA! Hit REFRESH and yes, the database has the records! This is very exciting!!!! |
|
_________________
Sic itur ad astra.
Last edited by Anaristos on Fri Apr 18, 2008 7:39 am; edited 2 times in total |
|
|
|
Dharkael
Enchanter
Joined: 05 Mar 2003
Posts: 593
Location: Canada
|
Posted: Fri Apr 18, 2008 4:24 am
|
COOL I'm pleased its working out, I'm still curious about what the problem is with the :memory: database
I mean you stated that my demo using the in memory database worked for you fine.
Strange.
Also the revised table creation statement should work it merely checks to see if the table exists already before creation.
Did you just download and install ActivePython? do you know what the exact version number is ?
I'm using 2.5.1
Anyways it works SQlite for ALL!  |
|
_________________
-Dharkael-
"No matter how subtle the wizard, a knife between the shoulder blades will seriously cramp his style." |
|
|
|
Anaristos
Sorcerer
Joined: 17 Jul 2007
Posts: 821
Location: California
|
Posted: Fri Apr 18, 2008 4:38 am
|
Yes, your demo worked just fine. It is when I tried the same thing with my version of the test that the memory scheme failed. However, writing to files works quite well. I have copied my entire mob database over to SQlite and I have done some test queries and they have returned the expected values both using NAME = and LIKE %value%, both which are essential.
I just installed ActivePython to try this out and it is version 2.5.2.2 |
|
_________________
Sic itur ad astra. |
|
|
|
Dharkael
Enchanter
Joined: 05 Mar 2003
Posts: 593
Location: Canada
|
Posted: Fri Apr 18, 2008 5:21 am
|
I just found a page on the SQLite site about the SQLite Query Optimizer, it can give hints on how to write your queries so they perform even faster.
I also discovered that you can turn on and off case sensitive LIKE searches.
It also gives some hints on using %value versus %value% for speed.
Also I just want to point out that I also did implement the REGEXP and MATCH methods which can be used for regex queries against the database,
It uses Python Regex engine so there are some few differences between that and the PCRE flavour used in ZMUD/CMUD although in most cases its the same.
SQLite tends to be pretty lean so there might be some "standardish" functions that it doesn't implement if there are any functions that people think should be added and I agree I'll add it to original post.
If I don't agree I'll still help ya write if needed and help you add it to your distro.
This applies to both functions callable within SQLite or just auxilliary functions to use with the component, exposed tthrough the COM interface. |
|
_________________
-Dharkael-
"No matter how subtle the wizard, a knife between the shoulder blades will seriously cramp his style." |
|
|
|
Anaristos
Sorcerer
Joined: 17 Jul 2007
Posts: 821
Location: California
|
Posted: Sat Apr 19, 2008 12:32 am
|
Here is an interesting heads-up. It seems that we were lucky last time that all this stuff worked because there was a fundamental coding error. I re-ran all the tests that had previously worked and they all failed. Finally in desperation I read the help on %comcreate because the problem was that the database was neither being created (if non-existent) nor being accessed. I then realized what the problem was and how simple the solution would be.
Instead of of
| Code: |
comsql = %comcreate("Sqlite.ZMUD.WSC"")
|
One must declare it this way:
| Code: |
#VAR comsql %comcreate("Sqlite.ZMUD.WSC"")
|
EDIT: Typo removed.
According to the help file, this is the only way that cMUD (or zMUD, for that matter) knows how to create a COM object.
Of course, zMUD users never have this problem because they have no choice as to how to declare their variables.
Why did it worked when the object was declared the wrong way? I can only guess that cMUD uses two different routines depending on how the variable is declared and that depending on some unknown (to us) state, there is a chance that doing it the wrong way will work.
Now that I've switched to the correct format my scripts are back to working like champs. Also, the :memory: scheme works when the COM object is declared properly. |
|
_________________
Sic itur ad astra.
Last edited by Anaristos on Sat Apr 19, 2008 1:48 am; edited 2 times in total |
|
|
|
Dharkael
Enchanter
Joined: 05 Mar 2003
Posts: 593
Location: Canada
|
Posted: Sat Apr 19, 2008 1:12 am
|
Strange brew...
I've never had any problems in CMUD
using the format comsql = %comcreate("Sqlite.ZMUD.WSC") using any other classstring.
All the code you were using to test is exactly the same code I was using and I was never able to reproduce your error.
As far as I can tell both the #var variable %comcreate
and the variable = %comcreate both function
equally well for me, although I normally prefer the #var syntax
I should note that in the post directly above this one, your code
| Code: |
| comsql = %comcreate(" "Sqlite.ZMUD.WSC"") |
and
| Code: |
| #VAR comsql %comcreate(" "Sqlite.ZMUD.WSC"") |
Fail with an invalid class string, due to the strangely double quoted classstring.
I guess maybe thats a typo?
BTW I'm using CMUD 2.18 maybe it's a version thing. |
|
_________________
-Dharkael-
"No matter how subtle the wizard, a knife between the shoulder blades will seriously cramp his style." |
|
|
|
Anaristos
Sorcerer
Joined: 17 Jul 2007
Posts: 821
Location: California
|
Posted: Sat Apr 19, 2008 1:44 am
|
Yes, it was a typo and I am using 2.18 also. I can't tell you anything else other than using the #VAR version is making everything work including the ":memory:" tests that were failing.
|
|
_________________
Sic itur ad astra. |
|
|
|
Anaristos
Sorcerer
Joined: 17 Jul 2007
Posts: 821
Location: California
|
Posted: Sat Apr 19, 2008 5:41 pm
|
Here is a problem:
| Code: |
....
$rec = @comsql.Execute("SELECT * FROM Areas WHERE Area LIKE ?", $room)
....
|
returns no such table: Areas
Now, the table Areas exists because not only am I looking at it, but also I tested the SQL statement in SQLite Expert before I inserted it into my code. In the test the query returned the correct record. |
|
_________________
Sic itur ad astra. |
|
|
|
Dharkael
Enchanter
Joined: 05 Mar 2003
Posts: 593
Location: Canada
|
Posted: Sat Apr 19, 2008 6:31 pm
|
Don't know what to tell ya man, if it says it isn't there then, it isnt there :P
Maybe you have a typo somewhere? So the table name is Areas and it has a column named Area?
On a more helpful note this piece of code can help you verify if the table exists.
| Code: |
| #for @comsql.Execute("SELECT sql FROM sqlite_master WHERE type='table' AND name ='Areas'") {#show ****;#showdb %i} |
The sqlite_master table is a special table that exists in all sqlite databases, it contains the schema for the database.
If that SELECT doesn't return anything then the Areas table doesn't exist.
Are you sure that the file your pointing to is the same one you're using in SQLite Expert?
Verify using @comsql.AbsolutePath |
|
_________________
-Dharkael-
"No matter how subtle the wizard, a knife between the shoulder blades will seriously cramp his style." |
|
|
|
Anaristos
Sorcerer
Joined: 17 Jul 2007
Posts: 821
Location: California
|
Posted: Sat Apr 19, 2008 6:46 pm
|
yes, it is. It is the only file I have. It has all my tables. I have the table open and I am looking at it. It has 242 entries.
EDIT: Mystery solved. Corrupted file path. |
|
_________________
Sic itur ad astra.
Last edited by Anaristos on Sat Apr 19, 2008 7:55 pm; edited 1 time in total |
|
|
|
charneus
Wizard
Joined: 19 Jun 2005
Posts: 1876
Location: California
|
Posted: Sat Apr 19, 2008 6:47 pm
|
You know, for some reason (and maybe I'm just rather dumb about it all, since I have never really used COM objects but love the way it works in this case), I cannot get this to work in CMUD. It works wonders in zMUD, but when I run the demo in CMUD, It does absolutely nothing but display:
******
******
******
No tables, nothing. What am I missing here? I'm using 2.22 right now. :\
Charneus |
|
|
|
|
Anaristos
Sorcerer
Joined: 17 Jul 2007
Posts: 821
Location: California
|
Posted: Sat Apr 19, 2008 6:52 pm
|
Let's see your code.
|
|
_________________
Sic itur ad astra. |
|
|
|
|
|
|
|
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
|
|
