hybris wrote:
First thing I wondered was if
public function save() {
if ($this->id) {
/* update... */
} else {
/* insert... */
}
}
is better than to use ON DUPLICATE KEY... To me it seems like the same functionality except in your function you can add more functionality (like do a usercheck() in the update function and only allow it for admins...or something). You can't add much stuff like that when using ON DUPLICATE KEY so I understand that makes it harder to do both separately..
Right. Which is why it's better. INSERT...ON DUPLICATE KEY is only really suited for times when all the data being updated also establishes uniqueness. Like relationships between tables, where all the data (the two keys and maybe another field or two) form a unique key (changing either of them changes the meaning of the relationship); the relationship either exists or does not exist with no other states or forms. However if you started adding more data to the relationship, that supplements it without contributing to the exact nature of the relationship itself, then INSERT...ON DUPLICATE KEY becomes a problem again; the relationship does not simply exist or not exist anymore, but it can exist in multiple states/forms (according to the additional data).
hybris wrote:
The second thing I wondered was if I use that save() function with my api would it not be better to have 2 separate functions like create() and update() where the create function return "Error: Product already exist" if trying to add a product that already exist and then have a separate update function for updating a product.
It would be better.
An API allows for consumers (ie, the clients using the API) to perform actions on entities. You have two options for how the API works:
1. "Create" and "update" products
2. Somehow get product data and then "save" it
I'd rather not go into details about how the two work, but you can figure it out yourself too if you think about it. Suffice it to say, the first option is easier for you (better error options with less work involved), a bit more work for the consumer (they have to know which action to use), and better for the data (clear separation between what's new and what's existing).
Your API code should then reflect how the API works as a whole. Note I said "API code": your implementation may very well look like
if (/* create */) {
$product = Product::create($foo, $bar);
$product->baz = $request["baz"];
$product->save();
return $product->id;
} else {
$product = Product::get($request["id"]);
$product->baz = $request["baz"];
$product->save();
}
where you end up calling a "save" method, but that's on the product model - not the API. Different things.
hybris wrote:
To me that would make more sense.. say I have a product 1 where a user added tons of info like manufacturer info, nutrition and ingredients and then I have a second (lazy) user that tries to create product 1 but don't bother to fill out nutrition and manufacturer info. If it just updates the new update will erase lot of the old info.
If we say the save problem is resolved in favor of separate insert/update then this becomes a non-issue: the second user will not be able to create a product because it already exists.
hybris wrote:
I used your suggestion for ingredient and nutrition tables (id | ingredient)..
When I update those tables i first delete all info for the specific id and then add the new info since it can happen a producer drops an ingredient so i cant just find and change one of the existing ingredients (like Felix ketchup updates to heintz ketchup).
Adding and removing individual ingredients is possible, but it is easier to just work with the list as a whole. What you do in the database is up to you - DELETE all and then INSERT all, or SELECT and identify changes and DELETE/INSERT as needed (with the former being easier in code).
hybris wrote:
Quote:
Now I'm lost. Why are you setting fields to NULL? Don't do that.
Why not?
I have lots of fields and I'm not sure all users bother to fill out everything all the time.. perhaps one user adds like nutrition and ingredients and then someone else bother to fill out manufacturer info at a later time. Also a field like "updated by" or one of the netweight or netvolume fields will be empty..
I guess instead of null i can have some standard char inserted like "-" if the field is not filled out but still if the lazy user don't add say manufacturer info when he tries to create a new product when this product already exist the old values will be overwritten with "-" instead of null...
Or am I missing something?
No, I'm the one missing something. NULL for a lack of data is fine - definitely don't use a "-" or whatever.
hybris wrote:
I guess in my API on POST to create a new product I can do a Product::get() check to see if the product exists and if so throw an exception from there instead of calling $this->save()
When you break an action (like creating a new product) into more than one database operation (finding an existing product then saving the new one) you have to start worrying about concurrency. What if two users try to create the same product at the same time?
- Consumer A calls the API
- API A sees no product
- Consumer B calls the API
- API B sees no product
- API B creates the product
- API A creates the product
It may be unlikely but you should prepare for that.
There's two basic ways to deal with it:
1. An exclusive lock: lock the product table against all read/write operations performed by other connections, get existing data, then create the product and unlock the table. Anyone else attempting to access the product table will stall until the lock is released (which should just be milliseconds).
2. Don't break the action into parts. Kinda. Make the code attempt an INSERT IGNORE with all the product data, then check what happened: if successful then the product was indeed created and you can continue saving other data (ingredients, etc.), and if not then something happened and you can't create the new product. You can (and should) still try to get an existing product first, and that will cover 99% of cases, but you need the combined get/update which the INSERT does implicitly to know for sure.
#2 only works if there's a unique constraint on the product table that would interfere with creating a new product. The whatsit IDs you mentioned earlier sound appropriate, but a key on a name or something like that would not be.