These forums have been archived and are now read-only.

The new forums are live and can be found at https://forums.eveonline.com/

EVE Technology Lab

 
  • Topic is locked indefinitely.
 

Q: How to avoid duplicate when you pull /WalletTransactions ?
Author
Previous Topic Next Topic
Guardian Stella
Doomheim
#1 - 2014-12-11 19:43:33 UTC
Hello, I am fairly new to EvE online development and am doing this as a way to train myself in PHP and MySQL. I am building a database to hold information I get from requesting /char/WalletTransactions.xml.aspx (in PHP and MySQL) I use this website as a reference guide ( http://wiki.eve-id.net/APIv2_Char_MarketTransactions_XML )

on this page (and some other) it is said "Transaction ID. Guaranteed to be unique with this page call; subject to renumbering periodically. Use the last listed transactionID with the beforeTransID argument to walk the list. See Journal Walking.". So I decided to build my table with an ID column of itself that generate a new unique ID for each transaction.

My question is : how do I assure there is no duplicate in the database if the ID provided is not unique, do I have to query the database each time I want to save a new transaction ?

Thank you very much
Khanadien Karlov
Wu Xi Holdings
#2 - 2014-12-11 20:24:47 UTC
Guardian Stella wrote:

My question is : how do I assure there is no duplicate in the database if the ID provided is not unique, do I have to query the database each time I want to save a new transaction ?

Thank you very much


That's probably old information. transactionid references journalid and both no longer get renumbered, ever since these columns switched from 32 to 64-bit ints. I just use transactionid and journalid as uniques and hasn't been an issue.
Guardian Stella
Doomheim
#3 - 2014-12-11 20:46:57 UTC
Khanadien Karlov wrote:
Guardian Stella wrote:

My question is : how do I assure there is no duplicate in the database if the ID provided is not unique, do I have to query the database each time I want to save a new transaction ?

Thank you very much


That's probably old information. transactionid references journalid and both no longer get renumbered, ever since these columns switched from 32 to 64-bit ints. I just use transactionid and journalid as uniques and hasn't been an issue.


Thank you Big smile that will be much easier !

Also, do you know if there is a place with more up-to-date documentation on the API ?
Dragonaire
Here there be Dragons
#4 - 2014-12-11 20:47:24 UTC  |  Edited by: Dragonaire
Yeah they don't renumber any more so you can just assume they are unique. Also might want to use the new site which is kept more up to date: https://neweden-dev.com/API

Finds camping stations from the inside much easier. Designer of Yapeal for the Eve API. Check out the Yapeal PHP API Library thread.
Hel O'Ween
Men On A Mission
#5 - 2014-12-13 14:00:34 UTC
"Unique" can be quite an ubiquitous word. Blink

It's unique per transactionFor and transactionType.

In other words: buyer and seller share the same transactionID. Depending on your database layout, if you store all transactions for multiple entities (characters, corporations) in one table and you rely solely on transactionID, you might miss transactions.

Simple test: buy from/sell to yourself/your corp.

Same's true for wallet journal data, BTW.

EVEWalletAware - an offline wallet manager.
Khanadien Karlov
Wu Xi Holdings
#6 - 2014-12-15 17:20:22 UTC
Hel O'Ween wrote:
"Unique" can be quite an ubiquitous word. Blink

It's unique per transactionFor and transactionType.

In other words: buyer and seller share the same transactionID. Depending on your database layout, if you store all transactions for multiple entities (characters, corporations) in one table and you rely solely on transactionID, you might miss transactions.

Simple test: buy from/sell to yourself/your corp.

Same's true for wallet journal data, BTW.


That's a good catch! Since transactiontype is (buy/sell) so primary key is therefore (transactionid, transactiontype).

TransactionFor is not important because you can buy from corp to corp, or from personal to personal, but only 2 legs of an eve transaction. I am assuming that transactionID is globally incremented and not per wallet entity. But if it wasn't, and required transactionFor to be part of primary key, then two legs of the transaction would have 2 different transaction id if they are not on the same entity. That would be a little weird. I think transaction reports are generated by a view on the global transaction table.
Hel O'Ween
Men On A Mission
#7 - 2014-12-16 13:43:00 UTC
As I said: it depends on your table(s) layout.

To be honest, I don't excaxtly remember why (it's been a while since I've implemented that), but my WalletTransactionExists function checks for both transactionFor and transactionType. IIRC, that's because I use a single table to store the data AND basically treat everything as belonging to the character rather than seperating personal/corp transactions. That way, when a char changes corps, the data of the old corp "moves" with him.

EVEWalletAware - an offline wallet manager.
Tonto Auri
Vhero' Multipurpose Corp
#8 - 2014-12-16 17:31:05 UTC
To give you a fair warning: Noone said the time of pruning transaction IDs are a thing of distant past.
Since EVE migration to 64-bit IDs it only takes longer to fill the field, but this is still a possibility. So, plan accordingly.

Two most common elements in the universe are hydrogen and stupidity. -- Harlan Ellison
qu1ckkkk
The Warp Core Stabilizers
#9 - 2014-12-18 04:14:19 UTC
Tonto Auri wrote:
To give you a fair warning: Noone said the time of pruning transaction IDs are a thing of distant past.
Since EVE migration to 64-bit IDs it only takes longer to fill the field, but this is still a possibility. So, plan accordingly.


Yeah I also don't agree with the statement saying it will never get re-numbered. Yeah it may take quite some time loner since the 64bit INT move, but its definitely not impossible, so I would also suggest you keep this in mind and maybe generate your own primary key depending on your schema.

Proud developer of SeAT! A Simple Eve API & Corporation Management Tool.

Project Page: https://github.com/eveseat/seat