· 8 years ago · Apr 03, 2018, 12:40 PM
1// Name : nwnx_sql_persist
2// Purpose : Create a table for persistent data
3// Authors : Ingmar Stieger, Lamancha
4// Modified : April 28th, 2012 - for PostgresSQL now generic
5
6// This file is licensed under the terms of the
7// GNU GENERAL PUBLIC LICENSE (GPL) Version 2
8
9//******//******//******//******//******//******//******//******//******//******//
10//******WILL NEED TO BE CHANGED WHEN WE HAVE THE NEW INCLUDE FOR NWNX:EE******//
11#include "nwnx_sql"
12//******WILL NEED TO BE CHANGED WHEN WE HAVE THE NEW INCLUDE FOR NWNX:EE******//
13//******//******//******//******//******//******//******//******//******//******//
14
15// Creates a table in the Database for Persistance.
16// It only creates the table if it does not already
17// exist.
18void CreatePWTable(string sTableName);
19
20// Creates a table in the Database for Persistance.
21// It only creates the table if it does not already
22// exist.
23void CreatePWObjectTable(string sTableName);
24
25// Creates a table in the Database for Garden Persistance.
26// It only creates the table if it does not already
27// exist.
28void CreatePWGardeningTable(string sTableName);
29
30//Creates table to handle items stored from Players.
31void CreatePWPitItemTable(string sTableName);
32
33//Creates bounty ranking for players.
34void CreatePWBountyTable(string sTableName);
35
36// Return a string value when given a location
37string APSLocationToString(location lLocation);
38
39// Return a location value when given the string form of the location
40location APSStringToLocation(string sLocation);
41
42// Return a string value when given a vector
43string APSVectorToString(vector vVector);
44
45// Return a vector value when given the string form of the vector
46vector APSStringToVector(string sVector);
47
48// Set oObject's persistent string variable sVarName to sValue
49// Optional parameters:
50// iExpiration: Number of days the persistent variable should be kept in database (default: 0=forever)
51// sTable: Name of the table where variable should be stored (default: pwdata)
52void SetPersistentSQLString(object oObject, string sVarName, string sValue, int iExpiration =0 , string sTable = "pwdata");
53
54// Set oObject's persistent integer variable sVarName to iValue
55// Optional parameters:
56// iExpiration: Number of days the persistent variable should be kept in database (default: 0=forever)
57// sTable: Name of the table where variable should be stored (default: pwdata)
58void SetPersistentSQLInt(object oObject, string sVarName, int iValue, int iExpiration = 0, string sTable = "pwdata");
59
60// Set oObject's persistent float variable sVarName to fValue
61// Optional parameters:
62// iExpiration: Number of days the persistent variable should be kept in database (default: 0=forever)
63// sTable: Name of the table where variable should be stored (default: pwdata)
64void SetPersistentSQLFloat(object oObject, string sVarName, float fValue, int iExpiration =
65 0, string sTable = "pwdata");
66
67// Set oObject's persistent location variable sVarName to lLocation
68// Optional parameters:
69// iExpiration: Number of days the persistent variable should be kept in database (default: 0=forever)
70// sTable: Name of the table where variable should be stored (default: pwdata)
71// This function converts location to a string for storage in the database.
72void SetPersistentSQLLocation(object oObject, string sVarName, location lLocation, int iExpiration =
73 0, string sTable = "pwdata");
74
75// Set oObject's persistent vector variable sVarName to vVector
76// Optional parameters:
77// iExpiration: Number of days the persistent variable should be kept in database (default: 0=forever)
78// sTable: Name of the table where variable should be stored (default: pwdata)
79// This function converts vector to a string for storage in the database.
80void SetPersistentSQLVector(object oObject, string sVarName, vector vVector, int iExpiration =
81 0, string sTable = "pwdata");
82
83// Set oObject's persistent object with sVarName to sValue
84// Optional parameters:
85// iExpiration: Number of days the persistent variable should be kept in database (default: 0=forever)
86// sTable: Name of the table where variable should be stored (default: pwobjdata)
87void SetPersistentSQLObject(object oObject, string sVarName, object oObject2, int iExpiration =
88 0, string sTable = "pwobjdata");
89
90// Get oObject's persistent string variable sVarName
91// Optional parameters:
92// sTable: Name of the table where variable is stored (default: pwdata)
93// * Return value on error: ""
94string GetPersistentSQLString(object oObject, string sVarName, string sTable = "pwdata");
95
96// Get oObject's persistent integer variable sVarName
97// Optional parameters:
98// sTable: Name of the table where variable is stored (default: pwdata)
99// * Return value on error: 0
100int GetPersistentSQLInt(object oObject, string sVarName, string sTable = "pwdata");
101
102// Get oObject's persistent float variable sVarName
103// Optional parameters:
104// sTable: Name of the table where variable is stored (default: pwdata)
105// * Return value on error: 0
106float GetPersistentSQLFloat(object oObject, string sVarName, string sTable = "pwdata");
107
108// Get oObject's persistent location variable sVarName
109// Optional parameters:
110// sTable: Name of the table where variable is stored (default: pwdata)
111// * Return value on error: 0
112location GetPersistentSQLLocation(object oObject, string sVarname, string sTable = "pwdata");
113
114// Get oObject's persistent vector variable sVarName
115// Optional parameters:
116// sTable: Name of the table where variable is stored (default: pwdata)
117// * Return value on error: 0
118vector GetPersistentSQLVector(object oObject, string sVarName, string sTable = "pwdata");
119
120// Get oObject's persistent object sVarName
121// Optional parameters:
122// sTable: Name of the table where object is stored (default: pwobjdata)
123// * Return value on error: 0
124object GetPersistentSQLObject(object oObject, string sVarName, object oOwner = OBJECT_INVALID, string sTable = "pwobjdata");
125
126// Delete persistent variable sVarName stored on oObject
127// Optional parameters:
128// sTable: Name of the table where variable is stored (default: pwdata)
129void DeletePersistentSQLVariable(object oObject, string sVarName, string sTable = "pwdata");
130
131// (private function) Replace special character ' with ~
132string SQLEncodeSpecialChars(string sString);
133
134// (private function)Replace special character ' with ~
135string SQLDecodeSpecialChars(string sString);
136
137
138// ##################
139// # IMPLEMENTATION #
140// ##################
141
142
143void CreatePWTable(string sTableName)
144{
145
146 WriteTimestampedLogEntry("Creating Table '" + sTableName + "' for Postgres...");
147
148 string sql = "CREATE TABLE IF NOT EXISTS " + sTableName + " (" +
149 "player varchar(64) NOT NULL default '~'," +
150 "tag varchar(64) NOT NULL default '~'," +
151 "name varchar(64) NOT NULL default '~'," +
152 "val text," +
153 "expire int default NULL," +
154 "last timestamp NOT NULL default CURRENT_TIMESTAMP," +
155 "PRIMARY KEY (player,tag,name)" +
156 ") ;";
157
158 int sqlSuccess = NWNX_SQL_ExecuteQuery(sql);
159
160 if(sqlSuccess == 0)
161 {
162 WriteTimestampedLogEntry("Failed in Creating Table OR Table already exists '" + sTableName + "' for Postgres...");
163 }
164
165 if(sqlSuccess == 1)
166 {
167 WriteTimestampedLogEntry("SUCCESS in Creating Table '" + sTableName + "' for Postgres...");
168 }
169
170 /* OLD SQL exerc direct method
171 SQLExecDirect("CREATE TABLE IF NOT EXISTS " + sTableName + " (" +
172 "player varchar(64) NOT NULL default '~'," +
173 "tag varchar(64) NOT NULL default '~'," +
174 "name varchar(64) NOT NULL default '~'," +
175 "val text," +
176 "expire int default NULL," +
177 "last timestamp NOT NULL default CURRENT_TIMESTAMP," +
178 "PRIMARY KEY (player,tag,name)" +
179 ") ;");
180 */
181}
182
183void CreatePlayerDataTable()
184{
185
186 WriteTimestampedLogEntry("Creating Table 'PlayerData' for Postgres...");
187
188 string sql = "CREATE TABLE IF NOT EXISTS PlayerData (" +
189 "player varchar(64) NOT NULL default '~'," +
190 "name varchar(64) NOT NULL default '~'," +
191 "cdkey varchar(16) NOT NULL default '~'," +
192 "last timestamp NOT NULL," +
193 "PRIMARY KEY (player,cdkey,name)" +
194 ") ;";
195
196 int sqlSuccess = NWNX_SQL_ExecuteQuery(sql);
197
198 if(sqlSuccess == 0)
199 {
200 WriteTimestampedLogEntry("Failed in Creating Table OR Table already exists 'PlayerData' for Postgres...");
201 }
202
203 if(sqlSuccess == 1)
204 {
205 WriteTimestampedLogEntry("SUCCESS in Creating Table 'PlayerData' for Postgres...");
206 }
207
208 /* old sql ExecuDirect method
209
210 SQLExecDirect("CREATE TABLE IF NOT EXISTS PlayerData (" +
211 "player varchar(64) NOT NULL default '~'," +
212 "name varchar(64) NOT NULL default '~'," +
213 "cdkey varchar(16) NOT NULL default '~'," +
214 "last timestamp NOT NULL," +
215 "PRIMARY KEY (player,cdkey,name)" +
216 ") ;");
217 */
218}
219
220void CreatePWObjectTable(string sTableName)
221{
222 WriteTimestampedLogEntry("Creating Table '" + sTableName + "' for Postgres...");
223
224 string sql = "CREATE TABLE IF NOT EXISTS " + sTableName + " (" +
225 "player character varying(64) NOT NULL DEFAULT '~'::character varying," +
226 "tag character varying(64) NOT NULL DEFAULT '~'::character varying," +
227 "name character varying(64) NOT NULL DEFAULT '~'::character varying," +
228 "val bytea," +
229 "expire int default NULL," +
230 "last timestamp without time zone NOT NULL DEFAULT now()," +
231 "CONSTRAINT pwobjdata_pkey PRIMARY KEY (player , tag , name )" +
232 ") WITH (OIDS=FALSE) ; ALTER TABLE pwobjdata OWNER TO arabel;";
233
234 int sqlSuccess = NWNX_SQL_ExecuteQuery(sql);
235
236 if(sqlSuccess == 0)
237 {
238 WriteTimestampedLogEntry("Failed in Creating Table '" + sTableName + "' OR Table already exists for Postgres...");
239 }
240
241 if(sqlSuccess == 1)
242 {
243 WriteTimestampedLogEntry("SUCCESS in Creating Table '" + sTableName + "' for Postgres...");
244 }
245
246 /* old sqlexec direct method
247
248 SQLExecDirect("CREATE TABLE IF NOT EXISTS " + sTableName + " (" +
249 "player character varying(64) NOT NULL DEFAULT '~'::character varying," +
250 "tag character varying(64) NOT NULL DEFAULT '~'::character varying," +
251 "name character varying(64) NOT NULL DEFAULT '~'::character varying," +
252 "val bytea," +
253 "expire int default NULL," +
254 "last timestamp without time zone NOT NULL DEFAULT now()," +
255 "CONSTRAINT pwobjdata_pkey PRIMARY KEY (player , tag , name )" +
256 ") WITH (OIDS=FALSE) ; ALTER TABLE pwobjdata OWNER TO arabel;");
257 */
258}
259
260void CreatePWGardeningTable(string sTableName)
261{
262 WriteTimestampedLogEntry("Creating Gardening Table '" + sTableName + "' for Postgres...");
263
264 string sql = "CREATE TABLE IF NOT EXISTS " + sTableName + " (" +
265 "planttag varchar(64) NOT NULL default '~'," +
266 "areatag varchar(64) NOT NULL default '~'," +
267 "location varchar(256) NOT NULL default '~'," +
268 "year int NOT NULL default '0'," +
269 "month int NOT NULL default '0'," +
270 "day int NOT NULL default '0'," +
271 "expire int default NULL," +
272 "PRIMARY KEY (areatag,location)" +
273 ") ;";
274
275 int sqlSuccess = NWNX_SQL_ExecuteQuery(sql);
276
277 if(sqlSuccess == 0)
278 {
279 WriteTimestampedLogEntry("Failed in Creating Table '" + sTableName + "' OR Table already exists for Postgres...");
280 }
281
282 if(sqlSuccess == 1)
283 {
284 WriteTimestampedLogEntry("SUCCESS in Creating Table '" + sTableName + "' for Postgres...");
285 }
286
287 /* old sqlexec direct method
288
289 SQLExecDirect("CREATE TABLE IF NOT EXISTS " + sTableName + " (" +
290 "planttag varchar(64) NOT NULL default '~'," +
291 "areatag varchar(64) NOT NULL default '~'," +
292 "location varchar(256) NOT NULL default '~'," +
293 "year int NOT NULL default '0'," +
294 "month int NOT NULL default '0'," +
295 "day int NOT NULL default '0'," +
296 "expire int default NULL," +
297 "PRIMARY KEY (areatag,location)" +
298 ") ;");
299
300 */
301}
302
303void CreatePWPitItemTable(string sTableName)
304{
305 WriteTimestampedLogEntry("Creating Table '" + sTableName + "' for Postgres...");
306
307 string sql = "CREATE TABLE IF NOT EXISTS " + sTableName + " (" +
308 "player varchar(64) NOT NULL default '~'," +
309 "name varchar(64) NOT NULL default '~'," +
310 "itemid int NOT NULL default 0," +
311 "val bytea," +
312 "expire int default NULL," +
313 "PRIMARY KEY (player,name,itemid)" +
314 ") ;";
315
316 int sqlSuccess = NWNX_SQL_ExecuteQuery(sql);
317
318 if(sqlSuccess == 0)
319 {
320 WriteTimestampedLogEntry("Failed in Creating Table '" + sTableName + "' OR Table already exists for Postgres...");
321 }
322
323 if(sqlSuccess == 1)
324 {
325 WriteTimestampedLogEntry("SUCCESS in Creating Table '" + sTableName + "' for Postgres...");
326 }
327 /* old sqlexec direct method
328
329 SQLExecDirect("CREATE TABLE IF NOT EXISTS " + sTableName + " (" +
330 "player varchar(64) NOT NULL default '~'," +
331 "name varchar(64) NOT NULL default '~'," +
332 "itemid int NOT NULL default 0," +
333 "val bytea," +
334 "expire int default NULL," +
335 "PRIMARY KEY (player,name,itemid)" +
336 ") ;");
337 */
338}
339
340void CreatePWBountyTable(string sTableName)
341{
342 WriteTimestampedLogEntry("Creating Table '" + sTableName + "' for Postgres...");
343
344 string sql = "CREATE TABLE IF NOT EXISTS " + sTableName + " (" +
345 "player varchar(64) NOT NULL default '~'," +
346 "name varchar(64) NOT NULL default '~'," +
347 "points int NOT NULL default 0," +
348 "expires int default NULL," +
349 "PRIMARY KEY (player,name)" +
350 ") ;";
351
352 int sqlSuccess = NWNX_SQL_ExecuteQuery(sql);
353
354 if(sqlSuccess == 0)
355 {
356 WriteTimestampedLogEntry("Failed in Creating Table '" + sTableName + "' OR Table already exists for Postgres...");
357 }
358
359 if(sqlSuccess == 1)
360 {
361 WriteTimestampedLogEntry("SUCCESS in Creating Table '" + sTableName + "' for Postgres...");
362 }
363
364 /* old sqlexec direct method
365
366 SQLExecDirect("CREATE TABLE IF NOT EXISTS " + sTableName + " (" +
367 "player varchar(64) NOT NULL default '~'," +
368 "name varchar(64) NOT NULL default '~'," +
369 "points int NOT NULL default 0," +
370 "expires int default NULL," +
371 "PRIMARY KEY (player,name)" +
372 ") ;");
373 */
374}
375
376// These functions deal with various data types. Ultimately, all information
377// must be stored in the database as strings, and converted back to the proper
378// form when retrieved.
379
380string APSVectorToString(vector vVector)
381{
382 return "#POSITION_X#" + FloatToString(vVector.x) + "#POSITION_Y#" + FloatToString(vVector.y) +
383 "#POSITION_Z#" + FloatToString(vVector.z) + "#END#";
384}
385
386vector APSStringToVector(string sVector)
387{
388 float fX, fY, fZ;
389 int iPos, iCount;
390 int iLen = GetStringLength(sVector);
391
392 if (iLen > 0)
393 {
394 iPos = FindSubString(sVector, "#POSITION_X#") + 12;
395 iCount = FindSubString(GetSubString(sVector, iPos, iLen - iPos), "#");
396 fX = StringToFloat(GetSubString(sVector, iPos, iCount));
397
398 iPos = FindSubString(sVector, "#POSITION_Y#") + 12;
399 iCount = FindSubString(GetSubString(sVector, iPos, iLen - iPos), "#");
400 fY = StringToFloat(GetSubString(sVector, iPos, iCount));
401
402 iPos = FindSubString(sVector, "#POSITION_Z#") + 12;
403 iCount = FindSubString(GetSubString(sVector, iPos, iLen - iPos), "#");
404 fZ = StringToFloat(GetSubString(sVector, iPos, iCount));
405 }
406
407 return Vector(fX, fY, fZ);
408}
409
410string APSLocationToString(location lLocation)
411{
412 object oArea = GetAreaFromLocation(lLocation);
413 vector vPosition = GetPositionFromLocation(lLocation);
414 float fOrientation = GetFacingFromLocation(lLocation);
415 string sReturnValue;
416
417 if (GetIsObjectValid(oArea))
418 sReturnValue =
419 "#AREA#" + GetTag(oArea) + "#POSITION_X#" + FloatToString(vPosition.x) +
420 "#POSITION_Y#" + FloatToString(vPosition.y) + "#POSITION_Z#" +
421 FloatToString(vPosition.z) + "#ORIENTATION#" + FloatToString(fOrientation) + "#END#";
422
423 return sReturnValue;
424}
425
426location APSStringToLocation(string sLocation)
427{
428 location lReturnValue;
429 object oArea;
430 vector vPosition;
431 float fOrientation, fX, fY, fZ;
432
433 int iPos, iCount;
434 int iLen = GetStringLength(sLocation);
435
436 if (iLen > 0)
437 {
438 iPos = FindSubString(sLocation, "#AREA#") + 6;
439 iCount = FindSubString(GetSubString(sLocation, iPos, iLen - iPos), "#");
440 oArea = GetObjectByTag(GetSubString(sLocation, iPos, iCount));
441
442 iPos = FindSubString(sLocation, "#POSITION_X#") + 12;
443 iCount = FindSubString(GetSubString(sLocation, iPos, iLen - iPos), "#");
444 fX = StringToFloat(GetSubString(sLocation, iPos, iCount));
445
446 iPos = FindSubString(sLocation, "#POSITION_Y#") + 12;
447 iCount = FindSubString(GetSubString(sLocation, iPos, iLen - iPos), "#");
448 fY = StringToFloat(GetSubString(sLocation, iPos, iCount));
449
450 iPos = FindSubString(sLocation, "#POSITION_Z#") + 12;
451 iCount = FindSubString(GetSubString(sLocation, iPos, iLen - iPos), "#");
452 fZ = StringToFloat(GetSubString(sLocation, iPos, iCount));
453
454 vPosition = Vector(fX, fY, fZ);
455
456 iPos = FindSubString(sLocation, "#ORIENTATION#") + 13;
457 iCount = FindSubString(GetSubString(sLocation, iPos, iLen - iPos), "#");
458 fOrientation = StringToFloat(GetSubString(sLocation, iPos, iCount));
459
460 lReturnValue = Location(oArea, vPosition, fOrientation);
461 }
462
463 return lReturnValue;
464}
465
466// These functions are responsible for transporting the various data types back
467// and forth to the database.
468
469
470void SetPersistentSQLString(object oObject, string sVarName, string sValue, int iExpiration =0, string sTable = "pwdata")
471{
472 string sPlayer;
473 string sTag;
474
475 if (GetIsPC(oObject))
476 {
477 sPlayer = SQLEncodeSpecialChars(GetPCPlayerName(oObject));
478 sTag = SQLEncodeSpecialChars(GetName(oObject));
479 }
480 else
481 {
482 sPlayer = "~";
483 sTag = GetTag(oObject);
484 }
485
486 sVarName = SQLEncodeSpecialChars(sVarName);
487 sValue = SQLEncodeSpecialChars(sValue);
488
489 string sSQL = "SELECT player FROM " + sTable + " WHERE player='" + sPlayer +
490 "' AND tag='" + sTag + "' AND name='" + sVarName + "'";
491
492
493 int ret = NWNX_SQL_ExecuteQuery(sSQL);
494
495 if (ret == TRUE)
496 {
497 // row exists
498 sSQL = "UPDATE " + sTable + " SET val='" + sValue +
499 "',expire=" + IntToString(iExpiration) + " WHERE player='" + sPlayer +
500 "' AND tag='" + sTag + "' AND name='" + sVarName + "'";
501 NWNX_SQL_ExecuteQuery(sSQL);
502 }
503 else
504 {
505 // row doesn't exist
506 sSQL = "INSERT INTO " + sTable + " (player,tag,name,val,expire) VALUES" +
507 "('" + sPlayer + "','" + sTag + "','" + sVarName + "','" +
508 sValue + "'," + IntToString(iExpiration) + ")";
509 NWNX_SQL_ExecuteQuery(sSQL);
510 }
511
512/* old SQL exec direct method
513
514 SQLExecDirect(sSQL);
515
516 if (SQLFetch() == SQL_SUCCESS)
517 {
518 // row exists
519 sSQL = "UPDATE " + sTable + " SET val='" + sValue +
520 "',expire=" + IntToString(iExpiration) + " WHERE player='" + sPlayer +
521 "' AND tag='" + sTag + "' AND name='" + sVarName + "'";
522 SQLExecDirect(sSQL);
523 }
524 else
525 {
526 // row doesn't exist
527 sSQL = "INSERT INTO " + sTable + " (player,tag,name,val,expire) VALUES" +
528 "('" + sPlayer + "','" + sTag + "','" + sVarName + "','" +
529 sValue + "'," + IntToString(iExpiration) + ")";
530 SQLExecDirect(sSQL);
531 */
532}
533
534string GetPersistentSQLString(object oObject, string sVarName, string sTable = "pwdata")
535{
536 string sPlayer;
537 string sTag;
538
539 if (GetIsPC(oObject))
540 {
541 sPlayer = SQLEncodeSpecialChars(GetPCPlayerName(oObject));
542 sTag = SQLEncodeSpecialChars(GetName(oObject));
543 }
544 else
545 {
546 sPlayer = "~";
547 sTag = GetTag(oObject);
548 }
549
550 sVarName = SQLEncodeSpecialChars(sVarName);
551
552 string sSQL = "SELECT val FROM " + sTable + " WHERE player='" + sPlayer +
553 "' AND tag='" + sTag + "' AND name='" + sVarName + "'";
554
555 int ret = NWNX_SQL_ExecuteQuery(sSQL);
556
557 if(ret == TRUE)
558 {
559 while (NWNX_SQL_ReadyToReadNextRow())
560 {
561 NWNX_SQL_ReadNextRow();
562 // Note NWNX_SQL_ReadDataInActiveRow is zero based..
563 // 0 is the first column, 1 is the second, etc.
564 // Also, it returns a string representation by default. Use StringToInt/Float as necessary.
565 return NWNX_SQL_ReadDataInActiveRow(0);
566 }
567 }
568
569 else
570 {
571 return "";
572 }
573
574 /* OLD sql exec direct method
575 SQLExecDirect(sSQL);
576
577 if (SQLFetch() == SQL_SUCCESS)
578 return SQLDecodeSpecialChars(SQLGetData(1));
579 else
580 {
581 return "";
582 // If you want to convert your existing persistent data to APS, this
583 // would be the place to do it. The requested variable was not found
584 // in the database, you should
585 // 1) query it's value using your existing persistence functions
586 // 2) save the value to the database using SetPersistentString()
587 // 3) return the string value here.
588 }
589 */
590}
591
592void SetPersistentSQLInt(object oObject, string sVarName, int iValue, int iExpiration =
593 0, string sTable = "pwdata")
594{
595 SetPersistentSQLString(oObject, sVarName, IntToString(iValue), iExpiration, sTable);
596}
597
598int GetPersistentSQLInt(object oObject, string sVarName, string sTable = "pwdata")
599{
600
601 string returnedVal = GetPersistentSQLString(oObject, sVarName, sTable);
602
603 return StringToInt(returnedVal);
604
605/* old method - I have no idea why it did it this way honestly. Seems bizzarre to use direct ODBC nwnx functions
606//instead of using SQL Fetch so if what I do here doesnt work, well... fuck?
607
608 string sPlayer;
609 string sTag;
610 object oModule;
611
612 if (GetIsPC(oObject))
613 {
614 sPlayer = SQLEncodeSpecialChars(GetPCPlayerName(oObject));
615 sTag = SQLEncodeSpecialChars(GetName(oObject));
616 }
617 else
618 {
619 sPlayer = "~";
620 sTag = GetTag(oObject);
621 }
622
623 sVarName = SQLEncodeSpecialChars(sVarName);
624
625 string sSQL = "SELECT val FROM " + sTable + " WHERE player='" + sPlayer +
626 "' AND tag='" + sTag + "' AND name='" + sVarName + "'";
627 SQLExecDirect(sSQL);
628
629 oModule = GetModule();
630 SetLocalString(oModule, "NWNX!ODBC!FETCH", "-2147483647");
631 return StringToInt(GetLocalString(oModule, "NWNX!ODBC!FETCH"));
632 */
633}
634
635void SetPersistentSQLFloat(object oObject, string sVarName, float fValue, int iExpiration =
636 0, string sTable = "pwdata")
637{
638 SetPersistentSQLString(oObject, sVarName, FloatToString(fValue), iExpiration, sTable);
639}
640
641float GetPersistentSQLFloat(object oObject, string sVarName, string sTable = "pwdata")
642{
643
644 string returnedVal = GetPersistentSQLString(oObject, sVarName, sTable);
645
646 return StringToFloat(returnedVal);
647
648/* old method - I have no idea why it did it this way honestly. Seems bizzarre to use direct ODBC nwnx functions
649//instead of using SQL Fetch so if what I do here doesnt work, well... fuck?
650
651 string sPlayer;
652 string sTag;
653 object oModule;
654
655 if (GetIsPC(oObject))
656 {
657 sPlayer = SQLEncodeSpecialChars(GetPCPlayerName(oObject));
658 sTag = SQLEncodeSpecialChars(GetName(oObject));
659 }
660 else
661 {
662 sPlayer = "~";
663 sTag = GetTag(oObject);
664 }
665
666 sVarName = SQLEncodeSpecialChars(sVarName);
667
668 string sSQL = "SELECT val FROM " + sTable + " WHERE player='" + sPlayer +
669 "' AND tag='" + sTag + "' AND name='" + sVarName + "'";
670 SQLExecDirect(sSQL);
671
672 oModule = GetModule();
673 SetLocalString(oModule, "NWNX!ODBC!FETCH", "-340282306073709650000000000000000000000.000000000");
674 return StringToFloat(GetLocalString(oModule, "NWNX!ODBC!FETCH"));
675
676 */
677}
678
679void SetPersistentSQLLocation(object oObject, string sVarName, location lLocation, int iExpiration =
680 0, string sTable = "pwdata")
681{
682 SetPersistentSQLString(oObject, sVarName, APSLocationToString(lLocation), iExpiration, sTable);
683}
684
685location GetPersistentSQLLocation(object oObject, string sVarName, string sTable = "pwdata")
686{
687 return APSStringToLocation(GetPersistentSQLString(oObject, sVarName, sTable));
688}
689
690void SetPersistentSQLVector(object oObject, string sVarName, vector vVector, int iExpiration =
691 0, string sTable = "pwdata")
692{
693 SetPersistentSQLString(oObject, sVarName, APSVectorToString(vVector), iExpiration, sTable);
694}
695
696vector GetPersistentSQLVector(object oObject, string sVarName, string sTable = "pwdata")
697{
698 return APSStringToVector(GetPersistentSQLString(oObject, sVarName, sTable));
699}
700
701/* this needs to be replaced with a new system to use it
702void SetPersistentSQLObject(object oOwner, string sVarName, object oObject, int iExpiration =
703 0, string sTable = "pwobjdata")
704{
705 string sPlayer;
706 string sTag;
707
708 if (GetIsPC(oOwner))
709 {
710 sPlayer = SQLEncodeSpecialChars(GetPCPlayerName(oOwner));
711 sTag = SQLEncodeSpecialChars(GetName(oOwner));
712 }
713 else
714 {
715 sPlayer = "~";
716 sTag = GetTag(oOwner);
717 }
718 sVarName = SQLEncodeSpecialChars(sVarName);
719
720 string sSQL = "SELECT player FROM " + sTable + " WHERE player='" + sPlayer +
721 "' AND tag='" + sTag + "' AND name='" + sVarName + "'";
722 SQLExecDirect(sSQL);
723
724 if (SQLFetch() == SQL_SUCCESS)
725 {
726 // row exists
727 sSQL = "UPDATE " + sTable + " SET val=%s,expire=" + IntToString(iExpiration) +
728 " WHERE player='" + sPlayer + "' AND tag='" + sTag + "' AND name='" + sVarName + "'";
729 SetLocalString(GetModule(), "NWNX!ODBC!SETSCORCOSQL", sSQL);
730 StoreCampaignObject ("NWNX", "-", oObject);
731 }
732 else
733 {
734 // row doesn't exist
735 sSQL = "INSERT INTO " + sTable + " (player,tag,name,val,expire) VALUES" +
736 "('" + sPlayer + "','" + sTag + "','" + sVarName + "',%s," + IntToString(iExpiration) + ")";
737 SetLocalString(GetModule(), "NWNX!ODBC!SETSCORCOSQL", sSQL);
738 StoreCampaignObject ("NWNX", "-", oObject);
739 }
740}
741*/
742/* this needs to be replaced with a new system to use it
743object GetPersistentSQLObject(object oObject, string sVarName, object oOwner = OBJECT_INVALID, string sTable = "pwobjdata")
744{
745 string sPlayer;
746 string sTag;
747 object oModule;
748
749 if (GetIsPC(oObject))
750 {
751 sPlayer = SQLEncodeSpecialChars(GetPCPlayerName(oObject));
752 sTag = SQLEncodeSpecialChars(GetName(oObject));
753 }
754 else
755 {
756 sPlayer = "~";
757 sTag = GetTag(oObject);
758 }
759 sVarName = SQLEncodeSpecialChars(sVarName);
760
761 string sSQL = "SELECT val FROM " + sTable + " WHERE player='" + sPlayer +
762 "' AND tag='" + sTag + "' AND name='" + sVarName + "'";
763 SetLocalString(GetModule(), "NWNX!ODBC!SETSCORCOSQL", sSQL);
764
765 if (!GetIsObjectValid(oOwner))
766 oOwner = oObject;
767 return RetrieveCampaignObject ("NWNX", "-", GetLocation(oOwner), oOwner);
768}
769*/
770
771
772/* NEVER get's used! so screw it
773void DeletePersistentSQLVariable(object oObject, string sVarName, string sTable = "pwdata")
774{
775 string sPlayer;
776 string sTag;
777
778 if (GetIsPC(oObject))
779 {
780 sPlayer = SQLEncodeSpecialChars(GetPCPlayerName(oObject));
781 sTag = SQLEncodeSpecialChars(GetName(oObject));
782 }
783 else
784 {
785 sPlayer = "~";
786 sTag = GetTag(oObject);
787 }
788
789 sVarName = SQLEncodeSpecialChars(sVarName);
790 string sSQL = "DELETE FROM " + sTable + " WHERE player='" + sPlayer +
791 "' AND tag='" + sTag + "' AND name='" + sVarName + "'";
792 SQLExecDirect(sSQL);
793}
794
795*/
796
797// Problems can arise with SQL commands if variables or values have single quotes
798// in their names. These functions are a replace these quote with the tilde character
799
800string SQLEncodeSpecialChars(string sString)
801{
802 if (FindSubString(sString, "'") == -1) // not found
803 return sString;
804
805 int i;
806 string sReturn = "";
807 string sChar;
808
809 // Loop over every character and replace special characters
810 for (i = 0; i < GetStringLength(sString); i++)
811 {
812 sChar = GetSubString(sString, i, 1);
813 if (sChar == "'")
814 sReturn += "~";
815 else
816 sReturn += sChar;
817 }
818 return sReturn;
819}
820
821
822
823string SQLDecodeSpecialChars(string sString)
824{
825 if (FindSubString(sString, "~") == -1) // not found
826 return sString;
827
828 int i;
829 string sReturn = "";
830 string sChar;
831
832 // Loop over every character and replace special characters
833 for (i = 0; i < GetStringLength(sString); i++)
834 {
835 sChar = GetSubString(sString, i, 1);
836 if (sChar == "~")
837 sReturn += "'";
838 else
839 sReturn += sChar;
840 }
841 return sReturn;
842}
843/*Main function, uncomment to see compiler error messages*/
844//void main(){}