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.

cpanm JsonSQL

perl -MCPAN -e shell
install JsonSQL