Feature: allow for duplicate key inserts?

[andig]

Currently, RB requires to my understanding high overhead or manual SQL for performing "upserts" against tables with duplicate keys. I'd appreciate a solution that is able to use "REPLACE INTO" or "INSERT IGNORE" or similar mechanisms.

[gabordemooij]

I have never experienced any problems with this (yes sometimes you need to code SQL for an edge case but I assume you have run into a more frequently occurring issue), can you give me an example case ?

[andig]

Don't think this is an edge case but maybe I'm overlooking things. My example is an app that has an setup page. All setup options are stored as opt => value pairs in a single table. What to do when the setup is ok'ed by the user and you have say 50 options:

• findOne for each value and then store the updated bean -> 100 SQL queries
• or- what I'd do in mysql would be REPLACE INTO setup (opt, value) VALUES (x, y) and the unique key on the table would take are of half the queries otherwise required.

I was wondering if this might be a separate ::replace function for the facade?

[andig]

In sqlite REPLACE exists as well and should be similiar to INSERT ON CONFLICT REPLACE.

Kind regards, Andreas

[gabordemooij]

What about:

R::begin(); R::wipe('setup'); foreach($setup as $k=>$v) { $option = R::dispense('setup'); $option->key = $k; $option->value = $v; R::store($option); } R::commit();

My problem with a replace() function is that a user has to 'know' something about the database schema. RedBeanPHP tries to combine NoSQL with SQL so it does not try to hide SQL from you but the balance is subtle. RedBeanPHP has been designed to be a 'SQL queryable NoSQL database' ;) I dont want to have leaky abstractions.

Another problem with replace() is that it does not notify models (just like wipe), however wipe is a one-of-a-kind command, R::replace() would have to me merged with R::store() and R::store consults models if needed, I am not sure how to integrate something like replace() (do we need to call the delete event on the model as well?).

Still thinking about this, but these are my concerns.

[andig]

Hi Gabor,

does of course not work if you have a unique key that is not your primary key- just try to run this twice:

// DB setup R::setup('sqlite:test.db3');

$option = R::dispense(SETUP); $option->setMeta("buildcommand.unique", array(array('key'))); $option->key = 'key'; $option->value = 'value'; R::store($option);

PHP Fatal error: Uncaught [23000] - SQLSTATE[23000]: Integrity constraint viola tion: 19 column key is not unique thrown in C:\data\htdocs\redbean\rb.php on line 695

[andig]

Hi Gabor,

thinking about it I don't agree that replace needs to know about the DB schema. Replace is nothing more than INSERT..ON DUPLICATE UPDATE (mysql) so they are actually the same. What kind of unique key exists is- from replace's perspective- beside the point as the DB itself will take care of this?

Kind regards, Andreas

On Thu, May 24, 2012 at 8:26 PM, Gabor de Mooij < reply@reply.github.com

wrote:

What about:

R::begin(); R::wipe('setup'); foreach($setup as $k=>$v) { $option = R::dispense('setup'); $option->key = $k; $option->value = $v; R::store($option); } R::commit();

My problem with a replace() function is that a user has to 'know' something about the database schema. RedBeanPHP tries to combine NoSQL with SQL so it does not try to hide SQL from you but the balance is subtle. RedBeanPHP has been designed to be a 'SQL queryable NoSQL database' ;) I dont want to have leaky abstractions.

Another problem with replace() is that it does not notify models (just like wipe), however wipe is a one-of-a-kind command, R::replace() would have to me merged with R::store() and R::store consults models if needed, I am not sure how to integrate something like replace() (do we need to call the delete event on the model as well?).

Still thinking about this, but these are my concerns.

---

Reply to this email directly or view it on GitHub: https://github.com/gabordemooij/redbean/issues/160#issuecomment-5913866

[gabordemooij]

I will look into this

[gabordemooij]

Then we'll end up with R::store() and R::replace() and the user has to decide which one to use based on how the DB would handle the insert? That does not look right. Why not simply delete all settings and re-insert them like I proposed?

[andig]

Hi Gabor, you are right- your option is valid. I believe the performance overhead is lower that I initially thought as long as the DB is running in-process and doesnt need network calls (sqlite). It will for sure be slower for mysql. I don't like the fact that it will need higher amount of client-side coding (finding the row, deleting it, then adding it) instead of using the db features and just replacing it. Your approach is probably what it takes with DB abstraction frameworks like RB. I still think that a ::replace method would not do harm as it is straightforward for the developer to decide. If he needs to replace he makes the conscious choice of getting rid of data what was there previously- no surprised and not a complicated decision at all. I've looked at the github sources and beleive the changes for replace aren't too complicated- if I feel like doing it I still could go that way.

Kind regards, Andreas

[gabordemooij]

Okay let's summarize pros and cons:

Pro:

• improved performance
• less code for developer (= good)

Cons:

• might confuse API (people will ask: when do I use replace instead of store?)
• very expensive change (all drivers need to support this feature, future drivers as well)
• more confusion: what FUSE Model method will relace() invoke? delete? update? or both?

[katanacrimson]

Mark it an advanced feature, don't document it alongside store (that WOULD be confusing); those who know what an upsert is for will know when to use it. In fact, in the documentation, don't label its section as "replace" - label it "upsert" specifically.

You're right about the FUSE Model method though, that's a bit messy. It should not call delete() though, as the bean isn't ever being deleted; it is instead being inserted or updated. The problem is detecting what happened; determining whether the bean's row was inserted or updated will be a pain in the ass.

[sorbing]

I am also need a capability INSERT IGNORE... My example: table with proxies, where field "host" - unique key. Before inserting new records, I need to verify their existence in the table. I have insert new items (proxy) as plain SQL INSERT IGNORE. I can not take advantage benefits a RedBean. What do you recommend in this case? Thanks.

[katanacrimson]

@Sorbing for the time being you can query to see if the bean exists with a SELECT COUNT using R::$f.

[sorbing]

Yes, thanks, I did so. But I do not understand - whether implemented INSERT IGNORE using R::store()?

[gabordemooij]

I think these are custom queries. A plugin would be handy for such functionality but I wont put it in the core of RB. The core needs to be compact and clean.
Also 'insert ignore' fails silently which is quite dangerous in the hands of inexperienced developers, rather not do that by default. I would prefer an app to raise an exception. Using R::$f queries also highlight the fact that you are doing something special which might improve maintainability.

[sorbing]

Thank you for your extensive response. I understand your position.

[gabordemooij]

Always wanna help you building a custom plugin, unfortunately as much as would like to help you out I have to be kind of rigid concerning the core. In the past I added features and had to remove them afterwards causing a lot of damage and hurt feelings.

[andig]

For sake of completeness: the issue of INSERT OR REPLACE is much more complicated than expected according to this discussion: http://stackoverflow.com/questions/418898/sqlite-upsert-not-insert-or-replace As this is the case the request would- for SQLite- lead to high complexity in the core library.

[gabordemooij]

Hm, interesting. However a plugin can be tied to specfic databases. I would not like to have this kind of logic in the core, no.

[sorbing]

And you also write a plugin does not want? What do you think about the add hooks to the resulting query for support IGNORE, ON DUPLICATE UPDATE, etc?

..->hook_after('ON DUPLICATE UPDATE ...');