View previous topic :: View next topic |
Author |
Message |
Undertowe
Joined: 03 Jan 2005 Posts: 33
|
Posted: Mon Mar 05, 2007 6:03 Post subject: Generating a Unique Id for Items |
|
|
Here's the scenario. I want to create a "journal" item which allows a PC to "write" persistent "pages" in it. The content of the pages need to be stored in the database. The journal can be passed around from PC to PC and still contain the same pages. In other words, the pages follow the journal, not the PC nor the player. There can be multiple journals in the game, each with their own pages.
Here's what I'm thinking for implementation. When the journal is used, we do a GetLocalInt(oJournal, "nJournalId") to see if the journal already has an Id assigned. If nJournalId == 0 then we generate a unique Id for the journal and SetLocalInt() it on the journal. The implication is that the journal has never been used before and is empty. Since the journal will be saved in the PC's inventory (server vault) the journal Id should persist and be available thereafter. All the journal information in the database is keyed off this Id.
Problem is, I can't decide the best way to generate that unique journal Id. My first thought was to let the database generate it for me with an autoincrement field. I'd insert a new record for the journal and get the associated Id. There's a discussion of that approach here:
http://www.nwnx.org/phpBB2/viewtopic.php?t=168
But none of those techniques are absolutely guaranteed to return the last generated Id from the table, and that offends my delicate scripting sensibilities. Would I gain anything in reliability if I issued a two-part query from NWNX like the following:
Code: | INSERT Journal (field1, field2, etc.) VALUES (value1, value2, etc.);
SELECT last_insert_id() |
instead of splitting the INSERT and SELECT into separate calls to the database?
I also considered generating an Id myself based on a timestamp/random number/etc. This isn't guaranteed unique either, but the chances of duplication are pretty damn small.
How would you folks approach this?
Thanks! |
|
Back to top |
|
![](templates/subSilver/images/spacer.gif) |
Dazzle
Joined: 29 Dec 2004 Posts: 19
|
Posted: Mon Mar 05, 2007 17:39 Post subject: |
|
|
How about the public CDKey of the player with the character name? E.g. GetPCPublicCDKey() + GetName() |
|
Back to top |
|
![](templates/subSilver/images/spacer.gif) |
FunkySwerve
Joined: 02 Jun 2005 Posts: 377
|
Posted: Mon Mar 05, 2007 18:11 Post subject: |
|
|
Be aware if you follow Dazzle's suggestion you will need to guard against players makin second characters of the same name, somehow. You wouldn't think so, but they do this with aggravating frequency if you don't.
Funky |
|
Back to top |
|
![](templates/subSilver/images/spacer.gif) |
Undertowe
Joined: 03 Jan 2005 Posts: 33
|
Posted: Mon Mar 05, 2007 20:34 Post subject: |
|
|
Dazzle wrote: | How about the public CDKey of the player with the character name? E.g. GetPCPublicCDKey() + GetName() |
I considered that, but dismissed the idea because it would associate a journal with a particular PC. At first glance this might not seem like a problem. But, consider the case where a PC has multiple blank journals in their inventory (I forgot to mention this possibility). If I assign GetPCPublicCDKey(oPC) + GetName(oPC) to each one, that will result in them all having the same Id, and therefore the same content. They each need to have unique content.
I thought of another potential solution to this problem. Consider the following table:
Code: |
CREATE TABLE `journal`
(
`id` int(10) unsigned NOT NULL auto_increment,
`created_by` int(10) unsigned NOT NULL,
`last_used_by` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`)
)
|
The fields 'created_by' and 'last_used_by' are foreign keys to a 'PC' table where every PC in the world has a unique record.
There's also another table 'pages' to store the journal content, but its structure isn't relevant.
Now, when a journal is used for the first time (indicated by GetLocalInt(oJournal, "nJournalId") == 0) we run the following pseudo-code:
Code: |
object oJournal = GetItemActivated();
object oPC = GetItemActivator();
int nUniquePCId = GetUniquePCId(oPC);
INSERT journal (created_by, last_used_by) VALUES (nUniquePCId, nUniquePCId)
int nJournalId = SELECT max(id) FROM journal WHERE created_by = nUniquePCId
SetLocalInt(oJournal, "nJournalId", nJournalId);
|
If I understand correctly, the only way this would not return the correct journal Id is if the same PC activated a different blank journal in between the INSERT and SELECT statements triggered by the first journal. I don't see how that is possible.
Thoughts?
FunkySwerve wrote: | Be aware if you follow Dazzle's suggestion you will need to guard against players makin second characters of the same name, somehow. You wouldn't think so, but they do this with aggravating frequency if you don't. |
Good advice. We have a way to safeguard against this. |
|
Back to top |
|
![](templates/subSilver/images/spacer.gif) |
virusman
![](http://www.virusman.ru/images/asm80.gif)
Joined: 30 Jan 2005 Posts: 1020 Location: Russia
|
Posted: Mon Mar 05, 2007 23:16 Post subject: |
|
|
I'd just use last_insert_id(). |
|
Back to top |
|
![](templates/subSilver/images/spacer.gif) |
Gryphyn
Joined: 20 Jan 2005 Posts: 431
|
Posted: Mon Mar 05, 2007 23:20 Post subject: |
|
|
I gather these Journals are created dynamically using CreateObject().
Why not assign a unique TAG to the journal when it is created. This way you could have a separate 'sequencer' for each journal.
Journal,Entry,Content
001,001,...
001,002,...
002,001,...
002,002,...
iEntry = SELECT coalesce(max(Entry)+1,0) FROM <table> where Journal=# (from the Journals TAG)
if the iEntry = 0
iJournal = SELECT coalesce(max(Journal)+1,0) FROM <table>
then you will always be able to
INSERT <table> VALUES (iJournal,iEntry,content);
****
Of course this falls apart unless you track the movements of the journal.
OnAcquire - save the PC, on OnUnacquire - save location to respawn on a reset. <Journal,Entry,Owner,DropSite,Content>
??? Does anyone know which event fires first? OnAcquire or OnUnacquire in a trade situation?
Very much doable.
Cheers
Gryphyn |
|
Back to top |
|
![](templates/subSilver/images/spacer.gif) |
Undertowe
Joined: 03 Jan 2005 Posts: 33
|
Posted: Sat Mar 10, 2007 7:14 Post subject: |
|
|
Gryphyn wrote: | I gather these Journals are created dynamically using CreateObject(). |
I envisioned the journals being purchased from a merchant, or possibly part of treasure spawns. So, running some special bit of code when they get created won't be practical.
Gryphyn wrote: | Why not assign a unique TAG to the journal when it is created. |
Doesn't that beg the question of my original post? How would you suggest that I do this?
Your approach is technically clever, I must admit, but does it really offer any benefit over the approach I suggested above? Once I have a robust solution for generating a unique Id for a journal, I'm perfectly happy with using a "journal" and "page" table to store my data.
But, maybe I don't quite understand what your example is doing.
Here we'll get back either 0 (indicating that the journal has no entries, i.e. it's new) or the id of the next entry in the journal:
Gryphyn wrote: | iEntry = SELECT coalesce(max(Entry)+1,0) FROM <table> where Journal=# (from the Journals TAG) |
If the journal is new, the following will give us the next available id for a journal:
Gryphyn wrote: | iJournal = SELECT coalesce(max(Journal)+1,0) FROM <table> |
What I'm wondering is, if we already have a unique tag for the journal (as you mentioned above) why not just insert that into the "Journal" table as the primary key and dispense with all the coalesce() and max() functions? |
|
Back to top |
|
![](templates/subSilver/images/spacer.gif) |
Gryphyn
Joined: 20 Jan 2005 Posts: 431
|
Posted: Sat Mar 10, 2007 14:52 Post subject: |
|
|
Undertowe wrote: | Here we'll get back either 0 (indicating that the journal has no entries, i.e. it's new) or the id of the next entry in the journal:
Gryphyn wrote: | iEntry = SELECT coalesce(max(Entry)+1,0) FROM <table> where Journal=# (from the Journals TAG) |
If the journal is new, the following will give us the next available id for a journal:
Gryphyn wrote: | iJournal = SELECT coalesce(max(Journal)+1,0) FROM <table> |
What I'm wondering is, if we already have a unique tag for the journal (as you mentioned above) why not just insert that into the "Journal" table as the primary key and dispense with all the coalesce() and max() functions? |
A PrimaryKey does not have to be a single value, it can in-fact be made up of several elements, the criteria is that the combination provides an unique reference. In your case the PrimaryKey would be made up of both "Journal" and "Entry" values **Entry/Page
The coalesce() is used to ensure that the SELECT always returns a value, and avoids the need to handle nulls.
Undertowe wrote: | I envisioned the journals being purchased from a merchant, or possibly part of treasure spawns. So, running some special bit of code when they get created won't be practical. |
Maybe have an 'empty' journal object - for merchants and drops. - then when the 1st entry is written - destroy the 'empty' journal and replace it with an 'active' journal object (with appropriate TAG). This gives you a single place where the work needs to be done.
An advantage of this method is that you will only be tracking 'active' journals.
Cheers
Gryphyn |
|
Back to top |
|
![](templates/subSilver/images/spacer.gif) |
Grinning Fool
Joined: 12 Feb 2005 Posts: 264
|
Posted: Mon Mar 12, 2007 3:23 Post subject: |
|
|
I'd also recommend last_insert_id(); when called immediately after an insert, it will give you an accurate result - as long as no other queries have executed in the same connection thread. And since the nwn2 scrtip engine is single-threaded, you can be assure that only one script runs at a time -- your script will run to completion without being interrupted. That means that unless you're doing some odd background threading within nwnx itself and using the same mysql connection to do it (not possible without a custom nwnx plugin), the you're guaranteed a valid result from last_insert_id() _________________ Khalidine, a NWN2 persistent world
Looking for volunteers. |
|
Back to top |
|
![](templates/subSilver/images/spacer.gif) |
Baaleos
Joined: 02 Sep 2007 Posts: 830
|
Posted: Thu May 01, 2008 14:31 Post subject: Realise this is an old |
|
|
I know this is an old post, but shayans artifact system in the Rhun mod uses a method of getting a unique artifact tag number via selecting all objects that currently have a tag composed of a CONST value, and an int value, then adding 1 to the int.
Your database could have a column set up to store number id's for each journal.
You could just do a loop statemnt to find the highest value, and each time it loops, get the next row.
When it hits an error, this means its reached the last record, then your script can say, you would need to store the last good value in a seconday variable, to preserve it.
Code: | if(ReturnedData == Errormessagehere)
{
//get the last good variable, and add 1 to it.
preservednumber = perservednumber + 1;
//Insert code to add this new number to the database as a new row - This is the new id for your new journal
} |
This is all very rough in my head. |
|
Back to top |
|
![](templates/subSilver/images/spacer.gif) |
Gryphyn
Joined: 20 Jan 2005 Posts: 431
|
Posted: Thu May 01, 2008 23:25 Post subject: Re: Realise this is an old |
|
|
Baaleos wrote: | I know this is an old post, but shayans artifact system in the Rhun mod uses a method of getting a unique artifact tag number via selecting all objects that currently have a tag composed of a CONST value, and an int value, then adding 1 to the int.
Your database could have a column set up to store number id's for each journal.
You could just do a loop statemnt to find the highest value, and each time it loops, get the next row.
When it hits an error, this means its reached the last record, then your script can say, you would need to store the last good value in a seconday variable, to preserve it.
Code: | if(ReturnedData == Errormessagehere)
{
//get the last good variable, and add 1 to it.
preservednumber = perservednumber + 1;
//Insert code to add this new number to the database as a new row - This is the new id for your new journal
} |
This is all very rough in my head. |
This issue with this is that it MANY round trips to the database.
using max()+1 does it in a single trip.
Cheers
Gryphyn |
|
Back to top |
|
![](templates/subSilver/images/spacer.gif) |
|
|
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
|