| View previous topic :: View next topic |
| Author |
Message |
chunkymonky
Joined: 20 Feb 2005
Posts: 31
|
 Posted: Thu Nov 23, 2006 4:00 Post subject: Getting started with SQLite on an existing PW |
 |
|
| I recently updated my mod to use NWNx, and I want to change over to the SQLite DB. Please, feel free to view me as a complete DB moron, as I am. I understand that the SQlite db was created on the very first startup of the mod, and I can see it in my parent directory. I want to start using that DB to store player info and the like. I have working scripts in place for player info saving taken from the PWHelper scripts of EPOlsen, and I use CNR on my mod. Also, DMFI and Shayans Subrace engine. I know I need to do some work in the scripts, but I am not sure how to direct them to write to this new DB. |
|
| Back to top |
|
 |
FunkySwerve
Joined: 02 Jun 2005
Posts: 377
|
| Posted: Thu Nov 23, 2006 6:36 Post subject: |
|
|
All the functions you need are in the aps_include script. You can just replace out Campaign variable calls with those Persistent variable calls, and you'll be using the new database, which is significantly faster and won't blow up on you down the road. If you want to be able to take advantage of SQL's more powerful features, however, you'd need to post more detailed explanations of what it is that you are trying to do.
Funky |
|
| Back to top |
|
|
chunkymonky
Joined: 20 Feb 2005
Posts: 31
|
| Posted: Thu Dec 07, 2006 23:56 Post subject: |
|
|
| Funky...thanks for the reply. I will start with the replacement of the variable calls and then get a handle on what I need to do from there. |
|
| Back to top |
|
|
chunkymonky
Joined: 20 Feb 2005
Posts: 31
|
| Posted: Sat Jan 13, 2007 14:42 Post subject: |
|
|
| OK, after searching around to the best of my ability, I cannot find the answer to a question I am having. I want to use multiple tables in my sqlite database, currently I only have pwdata which was set up by the SIMTools test mod. I want to create new tables for persistent location storage (pwloc) and questing variables (pwquest), for example, but I am hitting a "database locked" error when I try to create the tables. Do I need to shut the mod down to create them using an external db utility? |
|
| Back to top |
|
|
FunkySwerve
Joined: 02 Jun 2005
Posts: 377
|
| Posted: Sat Jan 13, 2007 19:03 Post subject: |
|
|
You should be able to add them in the fashion that the SIMTools mod does - look at the CREATE call in the script that adds the database in the SIMTools_Test mod. Here it is, reproduced:
| Code: |
#include "aps_include"
void main()
{
SQLExecDirect("DROP TABLE pwdata");
SendMessageToPC(GetLastUsedBy(), "Table 'pwdata' deleted.");
// For SQLite
SendMessageToPC(GetLastUsedBy(), "Creating Table 'pwdata' for SQLite...");
SQLExecDirect("CREATE TABLE pwdata (" +
"player varchar(64) NOT NULL default '~'," +
"tag varchar(64) NOT NULL default '~'," +
"name varchar(64) NOT NULL default '~'," +
"val text," +
"expire int(11) default NULL," +
"last timestamp NOT NULL default current_timestamp," +
"PRIMARY KEY (player,tag,name)" +
")");
// For MySQL
/*
SendMessageToPC(GetLastUsedBy(), "Creating Table 'pwdata' for MySQL...");
SQLExecDirect("CREATE TABLE pwdata (" +
"player varchar(64) NOT NULL default '~'," +
"tag varchar(64) NOT NULL default '~'," +
"name varchar(64) NOT NULL default '~'," +
"val text," +
"expire int(11) default NULL," +
"last timestamp NOT NULL default CURRENT_TIMESTAMP," +
"PRIMARY KEY (player,tag,name)" +
") ENGINE=MyISAM DEFAULT CHARSET=latin1;");
*/
// For Access
/*
SendMessageToPC(GetLastUsedBy(), "Creating Table 'pwdata' for Access...");
SQLExecDirect("CREATE TABLE pwdata (" +
"player text(64)," +
"tag text(64)," +
"name text(64)," +
"val memo," +
"expire text(4)," +
"last date)");
*/
SendMessageToPC(GetLastUsedBy(), "Table 'pwdata' created.");
}
|
The top, uncommented version is for SQLite.
Funky |
|
| Back to top |
|
|
chunkymonky
Joined: 20 Feb 2005
Posts: 31
|
| Posted: Sat Jan 13, 2007 23:47 Post subject: |
|
|
Thats what I was looking for. I assume that this can be adjusted for any table name I want? (replace pwdata with, say, pw_loc)
Or is that a terrible assumption? I want to separate the tables a little so not all my data is getting crammed into one table in the db.
*waits to get smacked for lack of knowledge*
Oh, by the way...thanks for all the help Funky. I am learning, albeit slowly, but I am learning. |
|
| Back to top |
|
|
FunkySwerve
Joined: 02 Jun 2005
Posts: 377
|
| Posted: Sun Jan 14, 2007 9:45 Post subject: |
|
|
You can rename it, and do pretty much whatever you want. Unless you are familar with database indexing and data types, however, I wouldn't tinker too much. Here's a same with the name changed only:
| Code: |
SQLExecDirect("CREATE TABLE pwloc (" +
"player varchar(64) NOT NULL default '~'," +
"tag varchar(64) NOT NULL default '~'," +
"name varchar(64) NOT NULL default '~'," +
"val text," +
"expire int(11) default NULL," +
"last timestamp NOT NULL default current_timestamp," +
"PRIMARY KEY (player,tag,name)" +
")");
|
You could use this table with the standard aps_include functions like GetPersistentString:
string GetPersistentString(object oObject, string sVarName, string sTable="pwdata");
simply by adding in the name of you database after the stardard object and value (if nothing is entered it defaults to pwdata).
Here's an example of a different table setup, one I use with our login tracking system, which uses custom SQL calls rather than the Get/SetPersistent aps_include functions:
| Code: |
void CreateTable(object oPC)
{
SQLExecDirect("CREATE TABLE loggedin (" +
"server VARCHAR(64) default NULL," +
"pcname VARCHAR(64) default NULL," +
"charname VARCHAR(64) default NULL," +
"level VARCHAR(64) default NULL," +
"KEY idx (server,pcname)" +
")" );
SendMessageToPC(oPC, "Table 'loggedin' created.");
}
|
When a character logs in, we check to see if they are logged in elsewhere (to prevent exploits like duping with doubled logins of the same character on different servers). This should give you an idea how you can take more full advantage of the power of SQL than aps_include allows, using custom calls:
| Code: |
int MarkCharacterEntry(object oPlayer)
{
int nBoot = FALSE;
//object oPlayer = GetEnteringObject();
string sServer;
sServer = GetLocalString(OBJECT_SELF, "ServerNumber");//returns either 111 or 112
string sPlayer;
string sUnencoded = GetPCPlayerName(oPlayer);
sPlayer = SQLEncodeSpecialChars(sUnencoded);
string sName = SQLEncodeSpecialChars(GetName(oPlayer));
int nLevel;
nLevel = GetHitDice(oPlayer);
int nLootable;
nLootable = GetLootable(oPlayer);
if (nLootable > 40) nLevel = nLootable;
string sLevel = IntToString(nLevel);
string sIP = GetPCIPAddress(oPlayer);
string sCD = GetPCPublicCDKey(oPlayer);
string sSQL = "SELECT pcname, charname, cdk, server FROM loggedin WHERE pcname='" + sPlayer + "'";
SQLExecDirect(sSQL);
if (SQLFetch() == SQL_SUCCESS)
{
// row exists
string sSQLPName = SQLGetData(1);
string sSQLCName = SQLGetData(2);
string sSQLCD = SQLGetData(3);
string sSQLServer = SQLGetData(4);
if ((sPlayer == sSQLPName) && (sName == sSQLCName) && (sCD != sSQLCD) && (sServer != sSQLServer)) //same p, c, diff cd, showing logged into diff server than this
{
nBoot = TRUE;//boot em, attempting double login
WriteTimestampedLogEntry("Double Login Attempt Detected! Player: " + sUnencoded + ".");
}
else //they aren't attempting double login, server prolly crashed
{
sSQL = "UPDATE loggedin SET server='" + sServer +
"',charname='" + sName + "',level='" + sLevel + "',cdk='" + sCD + "',cip='" + sIP + "' WHERE pcname='"+ sPlayer + "'";
SQLExecDirect(sSQL);
}
}
else //not foud on another server
{
// row doesn't exist
sSQL = "INSERT INTO loggedin (server,pcname,charname,level,cdk,cip) VALUES" +
"('" + sServer + "','" + sPlayer + "','" + sName + "','" + sLevel + "','" + sCD + "','" + sIP + "')";
SQLExecDirect(sSQL);
}
return nBoot;
}
|
The logged in table is wiped for that server when it restarts, because it wouldn't clear in the event of a crash or reset with characters still logged in (they are removed from the table only on clientexit, which doesnt fire if the server crashes, for example):
| Code: |
sSQL2 = "DELETE FROM loggedin WHERE server='" + sR3ServerIP + "'";
SQLExecDirect(sSQL2);
|
Here's the clientexit:
| Code: |
void MarkCharacterExit(string sPlayer)
{
string sPlayername;
sPlayername = SQLEncodeSpecialChars(sPlayer);
string sSQL = "DELETE FROM loggedin WHERE pcname='" + sPlayername + "'";
SQLExecDirect(sSQL);
}
|
Aside from exploit prevention, that lets us do stuff like cross-server player lists:
| Code: |
void main()
{
object oPC = GetPCSpeaker();
string sServer1List, sServer2List, sServer3List, sServer4List, sServer5List, sS1Header, sS2Header, sS3Header, sS4Header, sS5Header;
string sMessage, sPCName, sCharName, sLevel, sListServer;
string sServer = GetLocalString(GetModule(), "ServerNumber");
string SQL;
int nCount;
SQL = "SELECT pcname, charname, level, server FROM loggedin WHERE server<>'"+ sServer +"' ORDER BY level";
sMessage = "<c2>You are on server </c><c>216.144.214."+ sServer +"</c><c2>. Here are the players playing on the other servers:</c>\n<c2>Key: L = Level, P = Player, C = Character</c>\n";
SQLExecDirect(SQL);
while(SQLFetch() != SQL_ERROR) //lists 112 and 113
{
sPCName = SQLDecodeSpecialChars(SQLGetData(1));
sCharName = SQLDecodeSpecialChars(SQLGetData(2));
sLevel = SQLGetData(3);
sListServer = SQLGetData(4);
if (sListServer == "111") sServer1List += "<c>L: </c><c >" + sLevel + "</c><c>, P: </c><c>" + sPCName + "</c><c>, C: </c><c>" + sCharName + "</c>\n";
else if (sListServer == "112") sServer2List += "<c>L: </c><c >" + sLevel + "</c><c>, P: </c><c>" + sPCName + "</c><c>, C: </c><c>" + sCharName + "</c>\n";
else if (sListServer == "113") sServer3List += "<c>L: </c><c >" + sLevel + "</c><c>, P: </c><c>" + sPCName + "</c><c>, C: </c><c>" + sCharName + "</c>\n";
else if (sListServer == "114") sServer4List += "<c>L: </c><c >" + sLevel + "</c><c>, P: </c><c>" + sPCName + "</c><c>, C: </c><c>" + sCharName + "</c>\n";
else sServer5List += "<c>L: </c><c >" + sLevel + "</c><c>, P: </c><c>" + sPCName + "</c><c>, C: </c><c>" + sCharName + "</c>\n";
nCount++;
}
if (sServer1List != "") sS1Header = "<c2>=====Server 111=====</c>\n";
if (sServer2List != "") sS2Header = "<c2>=====Server 112=====</c>\n";
if (sServer3List != "") sS3Header = "<c2>=====Server 113=====</c>\n";
if (sServer4List != "") sS4Header = "<c2>=====Server 114=====</c>\n";
if (sServer5List != "") sS5Header = "<c2>=====Server 115=====</c>\n";
sMessage += sS1Header + sServer1List + sS2Header + sServer2List + sS3Header + sServer3List + sS4Header + sServer4List + sS5Header + sServer5List;
sMessage += "<c2>There are " + IntToString(nCount) + " players on other servers.</c>";
SendMessageToPC(oPC, sMessage);
}
|
(pardon the redundant color codes) 
Here's a third, used in our market, again using custom sql calls rather than the aps functions:
| Code: |
SQLExecDirect("CREATE TABLE market (" +
"server TINYINT UNSIGNED default NULL," +
"market VARCHAR(64) default NULL," +
"playername VARCHAR(64) default NULL," +
"cdkey VARCHAR(64) default NULL," +
"resref VARCHAR(64) default NULL," +
"name VARCHAR(64) default NULL," +
"price INT UNSIGNED default NULL," +
"charges TINYINT UNSIGNED default NULL," +
"bidder VARCHAR(64) default NULL," +
"bidding DATETIME default NULL," +
"expires DATETIME default NULL," +
"KEY (server,market,cdkey)" +
")" );
|
The choice of datatypes is dependant on speed, indexability, and what you need to store. I was fortunate enough to have an SQL expert to consult when working on my systems. You can also take a look at this reference manual:
http://dev.mysql.com/doc/refman/5.0/en/index.html
Hopefully this will show you some of the possibilities SQL provides once you start tinkering.
Funky[/code] |
|
| Back to top |
|
|
chunkymonky
Joined: 20 Feb 2005
Posts: 31
|
| Posted: Sun Jan 14, 2007 15:40 Post subject: |
|
|
| Wow, thanks Funky. I will have a project to tinker with now (aside from brewing beer in my basement) that uses my brain some. |
|
| Back to top |
|
|
|
|
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
|
Powered by phpBB © 2001, 2005 phpBB Group
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
