[gold-users] SQL Queries for gold commands

Steve Crusan scrusan at ur.rochester.edu
Thu Jul 1 21:05:24 MDT 2010


Yep, I also looked at the postgres query logs, and just the gstatement is
magnificently complex...

We probably are going to gold client software on the app server, and just
use PHP hooks for it.


On 7/1/10 4:59 PM, "Scott Jackson" <scottmo at adaptivecomputing.com> wrote:

> 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
>>   
> _______________________________________________
> gold-users mailing list
> gold-users at supercluster.org
> http://www.supercluster.org/mailman/listinfo/gold-users



----------------------
Steve Crusan
System Administrator
Center for Research Computing
University of Rochester
(585) 276-5599
https://www.crc.rochester.edu/



More information about the gold-users mailing list