pvarney pvarney - 7 days ago 7
jQuery Question

Perl: How to include scalar reference for DBIx::Class query information passed with ajax/json

I have a query I'm trying to pass from jquery/ajax/json to a perl script that will execute using DBIx::Class and return the results. I can get a basic query to work just fine, but am running into trouble when I have to include a scalar reference. My grasp on all the concepts around what I'm doing here is a little loose, so please forgive any inaccuracies.

Here is where I define the query parameters to pass to the perl script:

var jsonData = $.ajax({
url: 'cgi-bin/getdata.cgi',
data: JSON.stringify({
table: 'Processes',
search: {
query_server:'SERVER1',
query_time: {
'>=':'now() - INTERVAL 1 DAY'
}
},
attrib: {
select: [
'query_time',
{avg:'bytes_processed'}
],
order_by: 'query_time',
group_by: 'query_time'
}
}),
contentType: "application/json",
dataType: 'json',
method: 'POST',
async: false
}).responseText;


Here is the part of the perl script that reads standard in, converts from json and runs the query:

while ( <STDIN> )
{
$json .= $_;
}

my $query = from_json($json);

my $table = $query->{'table'};
my $search = $query->{'search'};
my $attrib= $query->{'attrib'};

print "Table:\n" . Dumper($table) . "\n";
print "Search:\n" . Dumper($search) . "\n";
print "Attrib:\n" . Dumper($attrib) . "\n";

my $retSet = $schema->resultset($table)->search( $search, $attrib );
my $retRow = $retSet->next;


And here is the output I get (with DBIC_TRACE enabled):

Table:
$VAR1 = 'Processes';

Search:
$VAR1 = {
'query_time' => {
'>=' => 'now() - INTERVAL 1 DAY'
},
'query_server' => 'SERVER1'
};

Attrib:
$VAR1 = {
'order_by' => 'query_time',
'group_by' => 'query_time',
'select' => [
'query_time',
{
'avg' => 'bytes_processed'
}
]
};

SELECT me.query_time, AVG( bytes_processed ) FROM processes me WHERE ( ( query_server = ? AND query_time >= ? ) ) GROUP BY query_time ORDER BY query_time: 'TSMCORP6', 'now() - INTERVAL 1 DAY'


The problem is that the
'now() - INTERVAL 1 DAY'
value needs to be a scalar reference in order to execute the query properly. If I build the query within the perl script (instead of passing from from the html file) like so:

my $search = {
query_server => 'SERVER1',
query_time => {
'>=' => \'now() - INTERVAL 1 DAY'
}
};


then search data looks like this and executes the way I want it to:

Search:
$VAR1 = {
'query_time' => {
'>=' => \'now() - INTERVAL 1 DAY'
},
'query_server' => 'SERVER1'
};
...
SELECT me.query_time, AVG( bytes_processed ) FROM processes me WHERE ( ( query_server = ? AND query_time >= now() - INTERVAL 1 DAY ) ) GROUP BY query_time ORDER BY query_time: 'TSMCORP6'


Based on my understanding, I don't think passing the value as a reference makes any sense, so I'm trying to understand how to "convert" it once it's in the perl script. I've tried throwing in the backslash before it gets processed by
from_json
, but that did not work. Much thanks for any help!

Update: I was able to turn the value into a reference with the following code. However, this worked because I knew what value needed to be converted to a reference. Under normal circumstances, the value could be anywhere in the data structure. Could I flag the value with something like
!ref!
then search the entire data structure and convert each one I find? How would that look?

my $cond = $query->{'search'}->{'query_time'}->{'>='};
$query->{'search'}->{'query_time'}->{'>='}=\$cond;

Answer

The problem is that JSON doesn't have anything that maps cleanly to a reference type because it's just data. However, I think you're on the right track with pre-processing the incoming data and converting literal SQL to the references that DBIC expects. On the sending side, you could add a literal_sql flag to the objects that need special handling, and then you could use filter_json_object to find those keys, delete them, and create scalar string references.

use strict;
use warnings;

use Data::Dump;
use JSON;

my $json = <<'EOF';
{
    "table": "Processes",
    "search": {
        "query_server": "SERVER1",
        "query_time": {
            ">=": "now() - INTERVAL 1 DAY",
            "literal_sql": true
        }
    },
    "attrib": {
        "select": [
            "query_time", {
                "avg": "bytes_processed"
            }
        ],
        "order_by": "query_time",
        "group_by": "query_time"
    }
}
EOF

my $query = JSON
    ->new
    ->filter_json_object (sub {
        my $obj = shift;
        return unless $obj->{literal_sql};
        delete($obj->{literal_sql});

        my ($key, $val) = each(%$obj);
        $obj->{$key} = \"$val";
        return $obj;
    })
    ->decode($json);

dd($query);

Output:

{
  attrib => {
              group_by => "query_time",
              order_by => "query_time",
              select   => ["query_time", { avg => "bytes_processed" }],
            },
  search => {
              query_server => "SERVER1",
              query_time   => { ">=" => \"now() - INTERVAL 1 DAY" },
            },
  table  => "Processes",
}