Generating a Unique Id for Items

[Undertowe]

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:

[Dazzle]

How about the public CDKey of the player with the character name? E.g. GetPCPublicCDKey() + GetName()

[FunkySwerve]

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

[Undertowe]

[virusman]

I'd just use last_insert_id().

[Gryphyn]

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

[Undertowe]

[Gryphyn]

[Grinning Fool]

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.

[Baaleos]

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.

[Gryphyn]