[gold-users] SQL Queries for gold commands

Scott Jackson scottmo at adaptivecomputing.com
Thu Jul 1 14:59:59 MDT 2010


Steve,

None of the clients query the database directly. All of the clients 
communicate with the gold server and the gold server invokes all the 
database queries via routines in Gold/Database.pm. The SQL is 
dynamically built up using the routines in Gold/Database.pm. My best 
recommendation is to grep SQL goldd.log to see the queries, inserts, 
updates, etc. in their raw form and attempt to replicate this.

For example, a gstatement generated the following for me:

grep SQL goldd.log
2010-07-01 14:57:49.213 DEBUG Gold::Database::select SQL Query: SELECT 
g_active,g_special FROM g_user WHERE ( g_name='scottmo' ) AND 
g_deleted!='True'
2010-07-01 14:57:49.215 DEBUG Gold::Database::select SQL Results: True|False
2010-07-01 14:57:49.217 DEBUG Gold::Database::select SQL Query: SELECT 
g_id,g_account,g_amount FROM g_allocation WHERE ( ( 
g_start_time<='1278017869' AND g_end_time>='1278017869' ) AND 
g_active='False' ) AND g_deleted!='True'
2010-07-01 14:57:49.218 DEBUG Gold::Database::select SQL Results:
2010-07-01 14:57:49.220 DEBUG Gold::Database::select SQL Query: SELECT 
g_id,g_account,g_amount FROM g_allocation WHERE ( ( 
g_start_time>='1278017869' OR g_end_time<='1278017869' ) AND 
g_active='True' ) AND g_deleted!='True'
2010-07-01 14:57:49.222 DEBUG Gold::Database::select SQL Results:
2010-07-01 14:57:49.225 DEBUG Gold::Database::select SQL Query: SELECT 
g_account.g_id AS g_id,g_account_project.g_name AS 
g_name,g_account_user.g_name AS g_name,g_account_machine.g_name AS 
g_name FROM g_account, g_account_project, g_account_user, 
g_account_machine WHERE ( g_account_project.g_account=g_account.g_id AND 
g_account_user.g_account=g_account.g_id AND 
g_account_machine.g_account=g_account.g_id AND ( g_account.g_id!='0' AND 
( g_account_project.g_name!='NONE' AND g_account_project.g_access='True' 
) AND ( ( g_account_user.g_name='ANY' AND g_account_user.g_access='True' 
) OR ( g_account_user.g_name='scottmo' AND 
g_account_user.g_access='True' ) OR ( g_account_user.g_name='MEMBERS' 
AND g_account_user.g_access='True' ) ) AND ( 
g_account_machine.g_name!='NONE' AND g_account_machine.g_access='True' ) 
AND g_account.g_id!='0' ) ) AND g_account.g_deleted!='True' AND 
g_account_project.g_deleted!='True' AND g_account_user.g_deleted!='True' 
AND g_account_machine.g_deleted!='True'
2010-07-01 14:57:49.252 DEBUG Gold::Database::select SQL Results: 
43|ABCDE-1234-F56|MEMBERS|ANY
2010-07-01 14:57:49.254 DEBUG Gold::Database::select SQL Query: SELECT 
g_account_project.g_account AS g_account FROM g_account_project, 
g_project_user WHERE ( g_project_user.g_active='True' AND 
g_project_user.g_name='scottmo' AND ( g_account_project.g_name='ANY' OR 
g_account_project.g_name=g_project_user.g_project ) AND ( 
g_account_project.g_account='33' OR g_account_project.g_account='32' OR 
g_account_project.g_account='50' OR g_account_project.g_account='39' OR 
g_account_project.g_account='40' OR g_account_project.g_account='51' OR 
g_account_project.g_account='41' OR g_account_project.g_account='38' OR 
g_account_project.g_account='8' OR g_account_project.g_account='34' OR 
g_account_project.g_account='37' OR g_account_project.g_account='30' OR 
g_account_project.g_account='43' OR g_account_project.g_account='29' OR 
g_account_project.g_account='31' ) ) AND 
g_account_project.g_deleted!='True' AND g_project_user.g_deleted!='True'
2010-07-01 14:57:49.255 DEBUG Gold::Database::select SQL Results: 51
2010-07-01 14:57:49.258 DEBUG Gold::Database::select SQL Query: SELECT 
DISTINCT g_account.g_id AS g_id FROM g_account, g_account_project, 
g_account_user, g_account_machine WHERE ( 
g_account_project.g_account=g_account.g_id AND 
g_account_user.g_account=g_account.g_id AND 
g_account_machine.g_account=g_account.g_id AND ( g_account.g_id='52' OR 
g_account.g_id='53' OR g_account.g_id='51' OR g_account.g_id='41' OR 
g_account.g_id='54' ) AND ( g_account.g_id='0' OR ( 
g_account_project.g_name='ANY' AND g_account_project.g_access='False' ) 
OR ( ( g_account_user.g_name='ANY' AND g_account_user.g_access='False' ) 
OR ( g_account_user.g_name='scottmo' AND g_account_user.g_access='False' 
) OR ( g_account_user.g_name='MEMBERS' AND 
g_account_user.g_access='False' ) ) OR ( g_account_machine.g_name='ANY' 
AND g_account_machine.g_access='False' ) OR g_account.g_id='0' ) ) AND 
g_account.g_deleted!='True' AND g_account_project.g_deleted!='True' AND 
g_account_user.g_deleted!='True' AND g_account_machine.g_deleted!='True'
2010-07-01 14:57:49.260 DEBUG Gold::Database::select SQL Results:
2010-07-01 14:57:49.272 DEBUG Gold::Database::select SQL Query: SELECT 
g_id,g_name FROM g_account WHERE ( ( g_id='52' OR g_id='53' OR g_id='51' 
OR g_id='41' OR g_id='54' ) AND ( g_deleted='True' OR g_deleted='False' 
) ) ORDER BY g_id
2010-07-01 14:57:49.274 DEBUG Gold::Database::select SQL Results: 
41|chemistry on colony
2010-07-01 14:57:49.351 DEBUG Gold::Database::select SQL Query: SELECT 
g_id,g_account,g_amount FROM g_allocation WHERE ( ( 
g_start_time<='1278017869' AND g_end_time>='1278017869' ) AND 
g_active='False' ) AND g_deleted!='True'
2010-07-01 14:57:49.352 DEBUG Gold::Database::select SQL Results:
2010-07-01 14:57:49.353 DEBUG Gold::Database::select SQL Query: SELECT 
g_id,g_account,g_amount FROM g_allocation WHERE ( ( 
g_start_time>='1278017869' OR g_end_time<='1278017869' ) AND 
g_active='True' ) AND g_deleted!='True'
2010-07-01 14:57:49.354 DEBUG Gold::Database::select SQL Results:
2010-07-01 14:57:49.367 DEBUG Gold::Database::select SQL Query: SELECT 
SUM(a.g_amount) AS g_amount FROM ( SELECT * FROM g_allocation UNION 
SELECT * FROM g_allocation_log) AS a, ( SELECT 
g_id,MAX(g_transaction_id) AS g_transaction_id FROM ( SELECT 
g_id,g_transaction_id FROM g_allocation WHERE g_modification_time<=0 
UNION SELECT g_id,g_transaction_id FROM g_allocation_log WHERE 
g_modification_time<=0) as c GROUP BY g_id) AS b WHERE ( ( 
a.g_account='52' OR a.g_account='53' OR a.g_account='51' OR 
a.g_account='41' OR a.g_account='54' ) AND a.g_active='True' ) AND 
g_deleted!='True' AND a.g_transaction_id=b.g_transaction_id AND 
a.g_id=b.g_id LIMIT 100000 OFFSET 0
2010-07-01 14:57:49.414 DEBUG Gold::Database::select SQL Results:
2010-07-01 14:57:49.527 DEBUG Gold::Database::select SQL Query: SELECT 
SUM(g_delta) AS g_delta FROM g_transaction WHERE ( ( g_account='52' OR 
g_account='53' OR g_account='51' OR g_account='41' OR g_account='54' ) 
AND g_delta>'0' AND g_creation_time>='0' AND 
g_creation_time<'1278017869' ) AND g_deleted!='True' LIMIT 100000 OFFSET 0
2010-07-01 14:57:49.529 DEBUG Gold::Database::select SQL Results: 50200100
2010-07-01 14:57:49.616 DEBUG Gold::Database::select SQL Query: SELECT 
SUM(g_delta) AS g_delta FROM g_transaction WHERE ( ( g_account='52' OR 
g_account='53' OR g_account='51' OR g_account='41' OR g_account='54' ) 
AND g_delta<='0' AND g_creation_time>='0' AND 
g_creation_time<'1278017869' ) AND g_deleted!='True' LIMIT 100000 OFFSET 0
2010-07-01 14:57:49.617 DEBUG Gold::Database::select SQL Results: 
-106424.180578
2010-07-01 14:57:49.698 DEBUG Gold::Database::select SQL Query: SELECT 
g_id,g_account,g_amount FROM g_allocation WHERE ( ( 
g_start_time<='1278017869' AND g_end_time>='1278017869' ) AND 
g_active='False' ) AND g_deleted!='True'
2010-07-01 14:57:49.699 DEBUG Gold::Database::select SQL Results:
2010-07-01 14:57:49.701 DEBUG Gold::Database::select SQL Query: SELECT 
g_id,g_account,g_amount FROM g_allocation WHERE ( ( 
g_start_time>='1278017869' OR g_end_time<='1278017869' ) AND 
g_active='True' ) AND g_deleted!='True'
2010-07-01 14:57:49.702 DEBUG Gold::Database::select SQL Results:
2010-07-01 14:57:49.724 DEBUG Gold::Database::select SQL Query: SELECT 
SUM(a.g_amount) AS g_amount FROM ( SELECT * FROM g_allocation UNION 
SELECT * FROM g_allocation_log) AS a, ( SELECT 
g_id,MAX(g_transaction_id) AS g_transaction_id FROM ( SELECT 
g_id,g_transaction_id FROM g_allocation WHERE 
g_modification_time<=1278017869 UNION SELECT g_id,g_transaction_id FROM 
g_allocation_log WHERE g_modification_time<=1278017869) as c GROUP BY 
g_id) AS b WHERE ( ( a.g_account='52' OR a.g_account='53' OR 
a.g_account='51' OR a.g_account='41' OR a.g_account='54' ) AND 
a.g_active='True' ) AND g_deleted!='True' AND 
a.g_transaction_id=b.g_transaction_id AND a.g_id=b.g_id LIMIT 100000 
OFFSET 0
2010-07-01 14:57:49.726 DEBUG Gold::Database::select SQL Results: 
50093658.919422
2010-07-01 14:57:49.828 DEBUG Gold::Database::select SQL Query: SELECT 
g_object,g_action,g_job_id,g_delta AS g_amount,g_creation_time AS g_time 
FROM g_transaction WHERE ( ( g_account='52' OR g_account='53' OR 
g_account='51' OR g_account='41' OR g_account='54' ) AND g_delta>'0' AND 
g_creation_time>='0' AND g_creation_time<'1278017869' ) AND 
g_deleted!='True' ORDER BY g_creation_time LIMIT 100000 OFFSET 0
2010-07-01 14:57:49.831 DEBUG Gold::Database::select SQL Results: 
Account|Deposit||50000000|1184178490
2010-07-01 14:57:49.960 DEBUG Gold::Database::select SQL Query: SELECT 
g_object,g_action,g_job_id,g_project,g_user,g_machine,g_delta AS 
g_amount,g_creation_time AS g_time FROM g_transaction WHERE ( ( 
g_account='52' OR g_account='53' OR g_account='51' OR g_account='41' OR 
g_account='54' ) AND g_delta<='0' AND g_creation_time>='0' AND 
g_creation_time<'1278017869' ) AND g_deleted!='True' ORDER BY 
g_creation_time LIMIT 100000 OFFSET 0
2010-07-01 14:57:49.963 DEBUG Gold::Database::select SQL Results: 
Account|Transfer|||||-100|1184633590

You may be getting into more than you bargained for!

Scott



Steve Crusan wrote:
> Greetings,
>
> Is there anywhere in the source that has a SQL styled listing of the 
> queries used for certain gold commands (such as gstatement, etc, etc)? 
> Looking through the source of some of the perl scripts, it looks like 
> most of those use the Database.pm in the lib/Gold/ libraries directory.
>
> We’re building a front end to gold, but we’d like to use the postgres 
> database itself as the datasource, and I like some of the gstatement 
> commands and such. So I was wondering if there is an internal listing 
> or set of documentation anywhere for this that lists some of the SQL 
> queries used to generate the reports. I know we can probably look over 
> the Database.pm heavily and devise our own system, but since Gold 
> already does this extremely well internally, I’d rather not recreate 
> the wheel.
>
> It is either doing the above, or putting the Gold client 
> commands/libraries on our web app and then hooking PHP with it to 
> generate what we need.
>
> Anyone else do something similar?
>
> Thanks!
>
>
> ----------------------
> Steve Crusan
> System Administrator
> Center for Research Computing
> University of Rochester
> (585) 276-5599
> https://www.crc.rochester.edu/
> ------------------------------------------------------------------------
>
> _______________________________________________
> gold-users mailing list
> gold-users at supercluster.org
> http://www.supercluster.org/mailman/listinfo/gold-users
>   


More information about the gold-users mailing list