NAME
JsonSQL::Query::Insert - JsonSQL::Query::Insert object. Stores a Perl representation of a set of INSERT statements created from a JSON string.
VERSION
version 0.4
SYNOPSIS
Use this to generate an SQL INSERT statement from a JSON string.
To use this:
use JsonSQL::Query::Insert;
my $jsonString = '{
"inserts": [
{
"table": {"table": "table1", "schema": "MySchema"},
"values": [
{"column": "column1", "value": "value1"},
{"column": "column2", "value": "value2"}
],
"returning": [{"column": "column1", "as": "bestcolumn"}, {"column": "column2"}]
},
{
"table": {"table": "table2"},
"values": [
{"column": "columnA", "value": "valueA"},
{"column": "columnB", "value": "valueB"}
]
}
]
}';
my $whitelisting_rules = [
{ schema => '#anySchema', 'table1' => [ 'column1', 'column2' ], 'table2' => [ 'columnA', 'columnB' ] }
];
my ( $insertObj, $err ) = JsonSQL::Query::Insert->new($whitelisting_rules, $jsonString);
if ( $insertObj ) {
my ( $sql, $binds ) = $insertObj->get_all_inserts;
<...>
} else {
die $err;
}Now you can go ahead and use $sql and $binds directly with the DBI module to do the query.
DESCRIPTION
This is a JsonSQL Query module that supports SQL generation for batched INSERT statements.
Examples of INSERT features supported by this module:
A single INSERT statement (minimum),
{
"inserts": [
{
"table": {"table": "MyTable"},
"values": [
{"column": "Animal", "value": "Giraffe"},
{"column": "Color", "value": "Yellow/Brown"}
]
}
]
}An INSERT statement with a RETURNING clause,
{
"inserts": [
{
"table": {"table": "MyTable"},
"values": [
{"column": "Animal", "value": "Giraffe"},
{"column": "Color", "value": "Yellow/Brown"}
],
"returning": [
{"column": "animal_id"}
]
}
]
}Multiple INSERT statements for batch processing,
{
"inserts": [
{
"table": {"table": "MyTable"},
"values": [
{"column": "Animal", "value": "Giraffe"},
{"column": "Color", "value": "Yellow/Brown"}
]
},
{
"table": {"table": "MyTable"},
"values": [
{"column": "Animal", "value": "Elephant"},
{"column": "Color", "value": "Grey"}
]
},
{
"table": {"table": "MyTable"},
"values": [
{"column": "Animal", "value": "Horse"},
{"column": "Color", "value": "Black"}
]
}
]
}Structure of INSERT JSON object:
The top-level property is the "inserts" property, which is an array of objects representing each INSERT. Each INSERT object has the following properties:
Required,
- table => { table => "table1" }
-
Generates: INSERT INTO 'table1' See L<JsonSQL::Param::Table> for more info. - values => [ { column => "scientist", value = "Einstein" }, { column => "theory", value = "Relativity" } ]
-
Generates ('scientist','theory') VALUES (?,?) Bind: ['Einstein','Relativity'] See L<JsonSQL::Param::InsertValues> for more info.
Optional,
- returning => { column => "column_id" }
-
Generates: RETURNING 'column_id'; See L<JsonSQL::Param::Insert> for more info.
Additional Properties,
- defaultschema => 'myschema'
-
If you are using DB schemas, this property can be used to generate the schema identifier for your queries. Particularly useful for per-user DB schemas.
See JsonSQL::Schemas::insert to view the restrictions enforced by the JSON schema.
Whitelisting Module
A set of whitelisting rules is required to successfully use this module to generate SQL. See JsonSQL::Validator to learn how this works.
METHODS
Constructor new($query_rulesets, $json_query)
Instantiates and returns a new JsonSQL::Query::Insert object.
$query_rulesets => The whitelisting rules to validate the query with.
$json_query => A stringified JSON object representing the query.Returns (0, <error message>) on failure.
ObjectMethod get_all_inserts -> ( $sql, $binds )
Generates the SQL statement represented by the object. Returns:
$sql => An arrayref of SQL INSERT strings.
$binds => An arrayref of arrays of parameterized values to pass with each INSERT query.AUTHOR
Chris Hoefler <bhoefler@draper.com>
COPYRIGHT AND LICENSE
This software is copyright (c) 2017 by Chris Hoefler.
This is free software; you can redistribute it and/or modify it under the same terms as the Perl 5 programming language system itself.