Developer Thread: Ongame SQL DB and it's flaws - Gambling and Poker Software Forum

Two Plus Two Forums Online Poker Sites & Marketplaces Poker Software General Software Discussion

Developer Thread: Ongame SQL DB and it's flaws

Post Reply Subscribe

...

01-22-2009 , 01:03 PM

p3rc4

banned

Join Date: Jul 2006 Posts: 10,986

Hello haxx0rs.

I'm looking into writing a tracker for ongame tournaments, SnGs and MTTs, using C and sqlite. I've succesfully written a semi-working program to convert hands from the ongame DB into PokerStars type text HHs, but unfortunately there are issues I can't seem to fix. My program keeps getting bad/missing data from the DB file, which a) generates storage errors if error checking is low, or b) creates information gaps if error checking is high. I'm looking for other programmers with experience working with the ongame SQL DB

I have made a complete chart describing the DB, but I'm not gonna post it here, due to copyright issues and legal issues with the ongame software copyright. However, I feel safe posting the sqlite_master:

Code:

sqlite> select * from sqlite_master
   ...> ;
table|DBInfo|DBInfo|2|CREATE TABLE DBInfo
(
  version VARCHAR(20),
  alteredAtYear INTEGER,
  alteredAtMonth INTEGER,
  alteredAtDate INTEGER,
  alteredAtHour INTEGER,
  alteredAtMinute INTEGER,
  alteredAtSecond INTEGER
)
table|Session|Session|3|CREATE TABLE Session
(
  id VARCHAR(30) NOT NULL,
  startedAtYear INTEGER NOT NULL,
  startedAtMonth INTEGER NOT NULL,
  startedAtDay INTEGER NOT NULL,
  startedAtHour INTEGER NOT NULL,
  startedAtMinute INTEGER NOT NULL,
  startedAtSecond INTEGER NOT NULL,
  endedAtYear INTEGER NOT NULL,
  endedAtMonth INTEGER NOT NULL,
  endedAtDay INTEGER NOT NULL,
  endedAtHour INTEGER NOT NULL,
  endedAtMinute INTEGER NOT NULL,
  endedAtSecond INTEGER NOT NULL,
  duration INTEGER NOT NULL
)
index|sessionIndex|Session|4|CREATE INDEX sessionIndex ON Session(id)
table|SessionPlayer|SessionPlayer|5|CREATE TABLE SessionPlayer
(
  sessionID VARCHAR(30) NOT NULL,
  activePlayerName VARCHAR(30) NOT NULL
)
table|PokerTable|PokerTable|7|CREATE TABLE PokerTable
(
  id VARCHAR(40) NOT NULL,
  name VARCHAR(30) NOT NULL,
  gameType INTEGER NOT NULL,
  gameDescription VARCHAR(30),
  moneyType INTEGER NOT NULL,
  structureType INTEGER NOT NULL,
  smallestChipUnit INTEGER NOT NULL,
  isRaiseFoldTable NUMERIC(1) NOT NULL,
  isHiLoTable NUMERIC(1) NOT NULL,
  maxPlayers INTEGER NOT NULL,
  ante INTEGER,
  forcedBringIn INTEGER,
  maxRaises INTEGER,
  rakeDescription VARCHAR(100) NOT NULL,
  minBuyIn INTEGER NOT NULL,
  maxBuyIn INTEGER NOT NULL
)
index|pokerTableIndex|PokerTable|9|CREATE INDEX pokerTableIndex ON PokerTable(id
)
table|LimitInfo|LimitInfo|10|CREATE TABLE LimitInfo
(
  round INTEGER NOT NULL,
  lowLimit INTEGER NOT NULL,
  highLimit INTEGER NOT NULL,
  isPotLimit NUMERIC(1) NOT NULL,
  isNoLimit NUMERIC(1) NOT NULL,

  tableID INTEGER NOT NULL
)
index|limitInfoTableIndex|LimitInfo|11|CREATE INDEX limitInfoTableIndex ON Limit
Info(tableID)
table|BlindInfo|BlindInfo|12|CREATE TABLE BlindInfo
(
  counter INTEGER NOT NULL,
  amount INTEGER NOT NULL,

  tableID INTEGER NOT NULL
)
index|blindInfoTableIndex|BlindInfo|14|CREATE INDEX blindInfoTableIndex ON Blind
Info(tableID)
table|Hand|Hand|15|CREATE TABLE Hand
(
  id INTEGER NOT NULL,
  startedAtYear INTEGER NOT NULL,
  startedAtMonth INTEGER NOT NULL,
  startedAtDay INTEGER NOT NULL,
  startedAtHour INTEGER NOT NULL,
  startedAtMinute INTEGER NOT NULL,
  startedAtSecond INTEGER NOT NULL,
  startedAtWeekday INTEGER NOT NULL,
  endedAtYear INTEGER NOT NULL,
  endedAtMonth INTEGER NOT NULL,
  endedAtDay INTEGER NOT NULL,
  endedAtHour INTEGER NOT NULL,
  endedAtMinute INTEGER NOT NULL,
  endedAtSecond INTEGER NOT NULL,
  duration INTEGER NOT NULL,
  lastPlayedPhase INTEGER NOT NULL,
  isDealerChoice NUMERIC(1) DEFAULT 0,
  dealerPosition INTEGER NOT NULL,
  rake INTEGER NOT NULL,
  pot INTEGER NOT NULL,
  bestShowedHandDescription VARCHAR(30),
  bestShowedHandRank INTEGER,

  sessionID INTEGER NOT NULL,
  tableID VARCHAR(40) NOT NULL
)
index|handIndex|Hand|17|CREATE INDEX handIndex ON Hand(id)
index|handSessionIndex|Hand|18|CREATE INDEX handSessionIndex ON Hand(sessionID)
index|handTableIndex|Hand|19|CREATE INDEX handTableIndex ON Hand(tableID)
table|Player|Player|21|CREATE TABLE Player
(
  name VARCHAR(30) NOT NULL,
  position INTEGER NOT NULL,
  relativePosition INTEGER NOT NULL,
  cash INTEGER NOT NULL,
  betAmount INTEGER NOT NULL,
  potShare INTEGER NOT NULL,
  netResult INTEGER NOT NULL,
  isActiveUser NUMERIC(1) NOT NULL,
  didShowHand NUMERIC(1) NOT NULL,
  shownHandDescription VARCHAR(30),
  shownHandRank INTEGER,

  handID INTEGER NOT NULL
)
index|playerHandIndex|Player|22|CREATE INDEX playerHandIndex ON Player(handID)
index|playerNameIndex|Player|23|CREATE INDEX playerNameIndex ON Player(name)
table|CommunityCard|CommunityCard|24|CREATE TABLE CommunityCard
(
  cardColor INTEGER NOT NULL,
  cardValue INTEGER NOT NULL,
  counter INTEGER NOT NULL,
  cardUsedInWinningHand NUMERIC(1),

  handID INTEGER NOT NULL
)
index|communityCardHandIndex|CommunityCard|25|CREATE INDEX communityCardHandInde
x ON CommunityCard(handID)
table|PlayerCard|PlayerCard|27|CREATE TABLE PlayerCard
(
  cardColor INTEGER NOT NULL,
  cardValue INTEGER NOT NULL,
  counter INTEGER NOT NULL,
  round INTEGER NOT NULL,
  cardIsShown NUMERIC(1),
  cardUsedInWinningHand NUMERIC(1),

  handID INTEGER NOT NULL,
  playerName VARCHAR(30) NOT NULL
)
index|playerCardHandIndex|PlayerCard|28|CREATE INDEX playerCardHandIndex ON Play
erCard(handID)
index|playerCardHandPlayerIndex|PlayerCard|29|CREATE INDEX playerCardHandPlayerIndex ON PlayerCard(handID, playerName)
table|DiscardedPlayerCard|DiscardedPlayerCard|30|CREATE TABLE DiscardedPlayerCard
(
  cardColor INTEGER NOT NULL,
  cardValue INTEGER NOT NULL,
  counter INTEGER NOT NULL,
  round INTEGER NOT NULL,
  discardedRound INTEGER NOT NULL,

  handID INTEGER NOT NULL,
  playerName VARCHAR(30) NOT NULL
)
index|playerDiscardedCardHandIndex|DiscardedPlayerCard|31|CREATE INDEX playerDis
cardedCardHandIndex ON DiscardedPlayerCard(handID)
index|playerDiscardedCardHandPlayerIndex|DiscardedPlayerCard|33|CREATE INDEX pla
yerDiscardedCardHandPlayerIndex ON DiscardedPlayerCard(handID, playerName)
table|Action|Action|34|CREATE TABLE Action
(
  counter INTEGER NOT NULL,
  action INTEGER NOT NULL,
  time INTEGER NOT NULL,
  amount INTEGER NOT NULL,
  phase INTEGER NOT NULL,
  playerRelativeSeat INTEGER NOT NULL,
  isActiveUser NUMERIC(1) NOT NULL,

  playerName VARCHAR(30),
  handID INTEGER NOT NULL
)
index|actionHandIndex|Action|35|CREATE INDEX actionHandIndex ON Action(handID)
index|actionHandPlayerIndex|Action|36|CREATE INDEX actionHandPlayerIndex ON Acti
on(handID, playerName)
table|Message|Message|37|CREATE TABLE Message
(
  type INTEGER NOT NULL,
  message VARCHAR(100) NOT NULL,
  playerName VARCHAR(30),
  counter INTEGER NOT NULL,
  time INTEGER NOT NULL,
  isActiveUser NUMERIC(1) NOT NULL,

  handID INTEGER NOT NULL
)
index|messageHandIndex|Message|39|CREATE INDEX messageHandIndex ON Message(handI
D)
table|Tournament|Tournament|40|CREATE TABLE Tournament
(
  id INTEGER NOT NULL UNIQUE PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  startedAtYear INTEGER NOT NULL,
  startedAtMonth INTEGER NOT NULL,
  startedAtDay INTEGER NOT NULL,
  startedAtHour INTEGER NOT NULL,
  startedAtMinute INTEGER NOT NULL,
  startedAtSecond INTEGER NOT NULL,
  duration INTEGER NOT NULL,
  entryFeePot INTEGER NOT NULL,
  entryFeeRake INTEGER NOT NULL,
  nrOfContestants INTEGER NOT NULL,
  tournamentType INTEGER NOT NULL,
  gameType INTEGER NOT NULL,
  moneyType INTEGER NOT NULL,
  limitType INTEGER NOT NULL,
  description VARCHAR(255),
  totalPricePool INTEGER,
  entryLevel INTEGER NOT NULL,
  nrOfMoneyWinners INTEGER NOT NULL,
  startingChips INTEGER NOT NULL,
  playerHasBeenKnockedOut NUMERIC(1) NOT NULL,
  finalLevel INTEGER,
  finalPosition INTEGER,
  winAmount INTEGER,
  winDescription VARCHAR(255),

  sessionID VARCHAR(30) NOT NULL
)
index|sqlite_autoindex_Tournament_1|Tournament|41|
table|TournamentPlayer|TournamentPlayer|44|CREATE TABLE TournamentPlayer
(
  status INTEGER,
  playerName VARCHAR(30),
  ranking INTEGER,
  handID INTEGER
)
index|tournamentPlayerHandIndex|TournamentPlayer|45|CREATE INDEX tournamentPlaye
rHandIndex ON TournamentPlayer(handID)
table|TournamentResult|TournamentResult|46|CREATE TABLE TournamentResult
(
  name VARCHAR(20) NOT NULL,
  isProgramUser NUMERIC(1) NOT NULL,
  finalPosition INTEGER,
  finalLevel INTEGER,
  winAmount INTEGER,
  winDescription VARCHAR(40),

  tournamentID INTEGER
)
index|resultTournamentIndex|TournamentResult|47|CREATE INDEX resultTournamentInd
ex ON TournamentResult(tournamentID)
table|Rebuy|Rebuy|48|CREATE TABLE Rebuy
(
  cost INTEGER NOT NULL,
  nrOfChips INTEGER NOT NULL,

  tournamentID INTEGER NOT NULL,
  handID INTEGER NOT NULL
)
index|rebuyTournamentIndex|Rebuy|49|CREATE INDEX rebuyTournamentIndex ON Rebuy(t
ournamentID)
table|TournamentHand|TournamentHand|51|CREATE TABLE TournamentHand
(
  tournamentLevel INTEGER NOT NULL,

  handID INTEGER NOT NULL,
  tournamentID INTEGER NOT NULL
)
index|tournamentHandIndex|TournamentHand|52|CREATE INDEX tournamentHandIndex ON
TournamentHand(tournamentID)
table|Comment|Comment|53|CREATE TABLE Comment
(
  sessionID VARCHAR(30) NOT NULL,
  tableID VARCHAR(40),
  handID INTEGER,
  phase INTEGER,
  actionCount INTEGER,
  text VARCHAR(100) NOT NULL
)

The tables I consistently miss information from is (ordered by importance): TournamentHand, Hand, Player, TournamentPlayer, PokerTable, PlayerCard (for hero), and a couple of others which i think derives from the mentioned tables.

I'm 99% sure that my C sqlite code is correct, so that the error isn't in the information gathering code. My question for you is: Is the Ongame SQL DB flawed? Does it skip hands/tables/players due to buggy implementation? Or am I not gathering the correct data/doing the correct data handling?

Any replies are thankfully appriciated!

Quote

150% up to $2,000 Welcome Bonus on CoinPoker

Join the action now

Daily Rewards • Splash Pots • CoinRaces

01-22-2009 , 04:15 PM

Hood

Carpal \'Tunnel

Join Date: Apr 2004 Posts: 6,895

First, I can't see any legal implications with posting your schema for the sql database. I have my own knocking around somewhere, i'll try to dig it out.

to answer your questions - yes, the issue is with ongame saving the data to the SQL database, and it does miss out a lot of information. Their code does not seem to handle transactions correctly, and so when multitabling, information can and does get missed frequently.

I found a solution to this issue is storing the DB on a ramdisk. If you search the HEM thread, you'll see my post detailing the setup. The effect is it makes file I/O lightning fast, and the result is ongame itself doesn't have issues writing and storing the information due to file locking. This, accompanied with regular "archiving" (resetting the database to blank) has resulted in 100% import rate in to HEM. I would assume your tool will have the same success.

Unfortunately without this step, i can't see how any program can itself fix this issue.

Quote

01-23-2009 , 01:10 AM

p3rc4

banned

Join Date: Jul 2006 Posts: 10,986

Quote:

Originally Posted by Hood

A ramdisk sounds like a decent solution, accompanied by frequent DB resetting. Will write that when I get time/stop being lazy, then post results. Thanks a lot buddy!

(Don't think there's any reason to post my notes on the DB really)

Quote

01-23-2009 , 02:32 AM

p3rc4

banned

Join Date: Jul 2006 Posts: 10,986

BTW, do u think the DB inconsistencies are related to ongame SW not properly retrying DB writes when a read lock occurs from my program? Or does SQL locks only occur when writing to a DB? I'm confused about this as I come from a unix environment where reads don't generate locks, but I'm uncertain how that works in win.

Quote

01-23-2009 , 11:02 AM

Eratosthenes

old hand

Join Date: Oct 2004 Posts: 1,535

Quote:

Originally Posted by p3rc4

Ongame uses SQLite, right? Older versions of SQLite had problems with multiple clients writing to the same db. I just looked at the SQLite FAQ and the newest versions still have the problem with multiple clients writing to the db. Ongame can fix this by changing how they use SQLite, most easily by just checking the return code and retrying when they get a busy signal. You guys should bother Ongame to fix this on their side.

Quote

01-23-2009 , 01:28 PM

p3rc4

banned

Join Date: Jul 2006 Posts: 10,986

Quote:

Originally Posted by Eratosthenes

If that is indeed the issue, I agree that we should, even though the ramdisk solution sounds like a working short cut.

Quote

150% up to $2,000 Welcome Bonus on CoinPoker

Join the action now

Daily Rewards • Splash Pots • CoinRaces

Post Reply Subscribe

...