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.41

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, $quote_char)

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.
$quote_char          => Optional: the character to use for quoting identifiers. The SUPER defaults to ANSI double quotes.

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.

cpanm JsonSQL

perl -MCPAN -e shell
install JsonSQL