Wondering if there is a way to get the generation transaction id from the solution logged to SQL where the upstream result is true. That is, we find a block but I then need a handle to the transaction so I can monitor whether it gets the 120 confirmations.
If you're using a PHP frontend, you can do what I've done with my pool:
Changes to pushpool:
1) On startup of pushpoold, run a query on a new table [found_blocks for example], which contains an ID# for each block that has been found, auto increment. Get the highest ID#, increase it by one, store it as a global variable in pushpool.
2) Change the sharelog query to include an ID# in the shares table.
3) When it checks a share and finds upstream_result='Y', increment the block ID counter in pushpool.
PHP Frontend:
1) Cron script executing your PHP script every minute (or 5 mintues, 30, your preference), which polls bitcoind for a list of block generations (listtransactions, search for category: "generate".
2) Get the transaction ID from the most recent block generations, and check if those transaction IDs are in the found_blocks table.
3) If they aren't, create a new entry in the found_blocks table, and include the transaction ID from bitcoind for that block. You can now associate all the shares from that block with the transaction ID because they will use the same block_id as your found_blocks table does.
4) Have a confirmations field on your found_blocks table, that gets updated when the script executes.
Obviously this is just the quick and dirty implementation to get you started, you'll need to add security checks for invalids (category: "orphan"), and make sure it doesn't have a hiccup if two blocks are found between the checks.