Data with dynamic web applications

Let's say you have a system that is designed to manage bands and their albums, and then the songs on each album.

The system's UI will show you what's already in there, and will allow you to edit existing names for things (band or album name, for example), add new things (maybe a new song).

The data is stored in a RDBMS, retrieved by a server-side language, and then sent to the browser via a JSON object.

That's all relatively easy to manage when you're just displaying data. But when you need to start keeping track of saves and updates, I start getting a little stuck about the right way.

This is how I have it now:


         RDBMS 
           ↓↑
   Scripting Language
      ↑|
    (json)   
      |↓               
   jsModel <---------(user input)
       :                |
   (dispatched events)  |
             :          |
        jsController----+
           ↑    ↓
   DOM events  jsUI

My confusion happens when I start getting into the updating and saving of data.

1. Do I need to be keeping track of what has changed in JS? Or do I just make changes to the whole JSON object, and shuttle that back and forth? If so, who (which layer) is responsible for tracking those changes?

2. When it comes back to the scripting language, how do I break the JSON object up into the SQL commands I need, particularly when UPDATEing and INSERTing? Do I just write one big query and bind every available, or should I be dynamically building the query based on the data the script receives¹? Or is there some other solution, involving the separation of files or writing different functions or something, such that no queries are built; rather, the program procedure gets routed differently depending on the data it gets? Also, this probably depends on the answer to question one.

3. Anything you'd like to add is appreciated.



<sub>_________________

1. I had previously been using an ORM solution, which I wrote myself, and I realized that I was either constantly changing it do more things or using the hooks I wrote in to code over the wall. It occurred to me that it wasn't really a solution so I dropped that in favor of just writing custom prepared statement queries myself when needed. But now that query building might be an option, a layer responsible for doing that might be a smart idea.

2. Just in case you're curious, the RBDMS is MySql, and the server-side scripting language is PHP.</sub>

Install Perl, problem solved.

>>2
Agreed.

Try not trying to be ENTERPRISE in using ECMASCRIPT and do what >>2 says. It's a lot easier, faster and more robust to not bother trying to send or retrieve data unless you're actually loading the page through a real hyperlink or by fisting F5.

>>4
I could go back now in theory, but I want to build this system to be as easy to use as possible. I believe that multiple page loads, even when cached, would be slower and more cumbersome for the user than making sensitive, dynamic forms managed by javascript. Plus I need the practice, I'm sure this won't be my first browser-based dynamic application.

_{Also it's too late, PHP is the server-side scripting language. I'd have to learn perl.}

>>2,3,4
recommending Perl
（　≖‿≖）
>>5
YHBT

>>6
You spend a lot of time in /g/ don't you?

>>1
#1: Easiest way is just send up the whole JSON object, and store the old objects somewhere else. You only really need to figure out what changed when you actually display the change history. It is usually easier to store each copy and calculate differences as needed than to come up with a system to store and index change information.

If you need something more complicated, there are plenty of things you need to consider, such as how in-depth does change information need to be, how often are you fetching it, is it typical to request differences across a wide range of revisions, etc. There are lots of different ways to do change tracking and there isn't just one correct way.

#2: Let an ORM do this.

Why on earth did you attempt to write your own ORM, only to scrap it later? Not using an ORM seems a little silly, because it would solve almost all of the issues you raised in your post. What scripting language are you using? You should be able to find a good ORM that will do everything you want.

>>8
For the first point, when I was thinking of changing, I didn't mean over time, just during that session, before the save event.

As for ORM, I decided to write my own because I wanted something incredibly lightweight and also MySQL specific, because all the ORM solutions I'd seen were 1)  actually ActiveRecord, which I didn't really care to use and/or 2) way too bloated with RDBMS abstraction. PHP is not the place for that kind of thing. A pretty good one I saw was phpDataMapper, but once I realized that PDO could get my data to me in essentially any format I needed it, and that I could take advantage of my own SQL knowledge, I figured I'd go the non-ORM route.

So my current plan is to have a standard structure for the data to be housed, for example:


data
  artists[]
    name
    ...
    albums[]
      name
      price[]
        qty
        base
        formula
      ...
      songs[]
        name
        ...

and then I'm going to keep two objects during my session, a dataRetrieved and dataNew. The dataNew will be made based on the users actions and sent to a PHP script that builds out a query based on what's there, using the standard structure as a guideline for how to build the query.

I figure this will be the fastest way to do it. Considering that this is JSON work, speed is pretty important. I wonder if using stored procedures in MySQL would be the way to go.

Anyway just writing this all out in question form really helped, thanks /pros/


   RDBMS
    ↓↑
Scripting Language
    ↑|
   (json)   
    |↓
Scripting Language (clientside)
    |↓
 (L)user

IT'S NOT THAT HARD, PEOPLE. TRY NOT TO GET LOST IN ENTERPRISE QUALITY OBJECT ORIENTED ARCHITECTURE MODEL PLATFORM FRAMEWORK SOLUTIONS.

>>7
In fact I don't. Why did you mention it?

>>9
As for ORM, I decided to write my own because I wanted something incredibly lightweight and also MySQL specific, because all the ORM solutions I'd seen were 1)  actually ActiveRecord, which I didn't really care to use and/or 2) way too bloated with RDBMS abstraction.
Ludicrous. If I were paying your salary I'd be pissed that you're wasting my money. This is a classic case of Not Invented Here syndrome; you think you can do it better by yourself, but you're wrong. Besides, safety is a much bigger concern than speed; an ORM gives you that much-needed layer of security against SQL injection which you will surely fuck up if you do it yourself.

>>12
You're absolutely incorrect. It was and is a case of Keeping It Simple. Using prepared statements with PDO is the main mechanism for prevention against SQL injection. The only other main thing to watch out for is Javascript/HTML injection which is easily sanitized using one or two PHP functions. Beyond that, ORM is just meant to be a convenience for the programmer for managing the gap between OOP and relational databases, and it's questionable even then if that job is done well. It's not a magic solution, even though many developers seem to treat it that way, and I'm surprised to hear someone at /prog/ advocate for such a solution in a scripting language that often gets shit for being slow and bloated to begin with.

>>10
Those javascript layers are just ways to organize complex code. Having everything done everywhere is not maintainable. Javascript objects are particularly mutable so those layers are light and mostly for the purpose of deciding what code goes where. The event dispatching model makes sense (and in fact, needs to be there) because that's how the DOM works. I'm all for simplification but those layers seem necessary. If you're willing to help me to understand why they aren't necessary then I'm more than happy to start deleting.

Use the sacred verbs and don't keep changes in the client. When you got a verbal request check if you have enough information to satisfy (server validation), then proceed to execute the action; insert, modify or delete.