How to compare JSON and generate SQL statement

[Thai Heng]

I develop a web application, it's database heavy and Single Page Applicaiton. So, thansfer between browser and server is JSON.

I want to know, is there a moduler to compare json and auto generate sql statement.

For example, insert, delete, and update.

a scenario of person dic edit

client browser get person dictionary from server as json

in browser insert a person. update to local DB? or should send back server at once

send changed person dictionary to server as json

server get json. Qestion: the new person should store to database

Thanks! May be my question is not what I said.

[Your Mother]

I’ve done this with DBIx::Class, Catalyst, and JSON::XS + Catalyst::View::JSON/Catalyst::Action::REST. It’s totally out of the scope of a short answer here though and probably overkill for what you’re doing.

If I were starting a small personal project today I would first try Mojolicious + Mojolicious::Plugin::REST and see if that was all it took. You can also search (meta|search\.)cpan.org for “CRUD” to find all sorts of other options and approaches.

I apologize I don’t have some prefab or sample code for you today; no time. :|

[CountZero]

If the dictionary is large and the edits are small, then it is probably better to just send to the server the changes to be made, rather than the whole modified dictionary.

[sundialsvc4]

Also, it will not be ... must not be ... a matter of “auto-generate an SQL statement.”   That strongly implies that you are trusting the content of the JSON packet that is being sent to you. (Remember Bobby Tables ...)

Treat the JSON packet as the incoming parameter-list to a sub which you will now write to do anything that needs to be done.   The JSON packet will arrive as a hashref.   The content of the hashref must not be trusted.   You never rely upon the remote client to tell you what to do.   Instead, you need to map-out what each possible incoming packet might legitimately be, and then what the server’s response should be to each one.   You also identify how errors will be reported, whether it be a 500 Internal Server Error response-packet or otherwise.   (Much of which has already been standardized ... see the end of this post.)

The client-side web page “is a [JavaScript?] computer program,” and the server is a corresponding computer program written in Perl.   JSON is the readily-available protocol that the two parties can use to send hashrefs to each other.   (Neither party “automatically trusting” what the other party has just sent.)

Think of the exchanges as “remote procedure-calls (RPC)”, what actually is what they are.   Such as:

1. (Client Request) get_person(person_id) ==returns==> [{error_packet} {person_record}].
2. (Client Request) update_person(person_record ==returns==> [{error_packet} {person_record}] .

And so on.   And there are many existing Perl packages that can help, such as RPC::Any, which is an entire suite of related goodness.

It’s a little less common for the server to make RPC requests to the client (e.g. to “push” more data to it asynchronously), but it does happen ... and, once again, well-established techniques have been standardized.   CPAN contains a lot of pre-written, pre-tested support for all of it, so don’t approach this thing as though you were obliged to start from scratch.   Tasks like this are “as common as the flowers in May.”