[MySQL] ON DUPLICATE KEY

[Thor]

Database structures isn't code you can refactor later. You have to know what you plan to do.

IF (currency_name exists)
run update command
ELSE
run insert command


I dont know mysql yet but I know I am looking for something like this:

begin tran
if exists (select * from table with (updlock,serializable) where key = @key)
begin
   update table set ...
   where key = @key
end
else
begin
   insert into table (key, ...)
   values (@key, ...)
end
commit tran

Simplified it is this:

IF (currency_name exists)
  run update command
ELSE
  run insert command

 

[[)roi(]]

Again what's with all the complexity, why not simply?:
1. start a transaction
2. delete records
3. insert records
4a. if it succeeds, commit the changes
4b. if it fails, rollback the changes.

 

[Thor]

Again what's with all the complexity, why not simply?:
1. start a transaction
2. delete records
3. insert records
4a. if it succeeds, commit the changes
4b. if it fails, rollback the changes.

Exactly.

That is what I want to do, I do that with actual php. Which I assume is not correct so I am trying to figure out how to go about the MySQL way.

I literally went

IF ($currency_name = $check_currency_name_in_DB)
run update SQL query
else
run insert sql query

===
I like your way that is perfect.

DROP records and then insert new records.

Jip that is perfect! Thanks!!!! Again

 

[[)roi(]]

Exactly.

That is what I want to do, I do that with actual php. Which I assume is not correct so I am trying to figure out how to go about the MySQL way.

I literally went

IF ($currency_name = $check_currency_name_in_DB)
run update SQL query
else
run insert sql query

===
I like your way that is perfect.

DROP records and then insert new records.

Jip that is perfect! Thanks!!!! Again

Why even check if it's in the table; just delete everything.
Then construct your inserts from your list of currencies you want; linked to the JSON.

 

[Thor]

#Profit

<?php
/* Begin a transaction, turning off autocommit */
$dbh->beginTransaction();

/* Change the database schema and data */
$sth = $dbh->exec("DROP TABLE fruit");
$sth = $dbh->exec("UPDATE dessert
    SET name = 'hamburger'");

/* Recognize mistake and roll back changes */
$dbh->rollBack();

/* Database connection is now back in autocommit mode */
?>

Damn. That was easy, thank you @[)roi(]

 

[IdlePhaedrus]

..snip

IF (currency_name exists)
  run update command
ELSE
  run insert command

And this is why you need to go back and look at how relational database structure works. Your fundamental database design is not right, so if you want to scale this system, you need to know how that works before you proceed.

To answer your very first post:

The key, or more appropriately the primary key, is the column (or combination of columns, known as a composite key) that uniquely identifies a row of data in your table.

If I recall correctly the use of a composite key can be a bit dodgy with regard to the "ON DUPLICATE KEY" directive in MySQL, but that may have been fixed in more recent versions. Also, I am not sure the directive is ANSI standard, so if you wanted to move this database to Oracle or MS SQL Server later, you wouldn't be able to without rewriting much of the code, just saying.

In your case, the currency code, or the currency name can be the unique key. Traditionally the currency code would be used.

In this instance, when doing an insert using the "ON DUPLICATE KEY" directive, where the insert statement results in a duplicate key, an update will occur instead.

I haven't read your links regarding the reason for the frequency of updates, but I see no reason to until you get your database structure correct first given your objectives. You need to figure that out within the constraints of good practice, which seems sadly lacking here given the replies to your issue to date.

 

[[)roi(]]

#Profit

<?php
/* Begin a transaction, turning off autocommit */
$dbh->beginTransaction();

/* Change the database schema and data */
$sth = $dbh->exec("DROP TABLE fruit");
$sth = $dbh->exec("UPDATE dessert
    SET name = 'hamburger'");

/* Recognize mistake and roll back changes */
$dbh->rollBack();

/* Database connection is now back in autocommit mode */
?>

Damn. That was easy, thank you @[)roi(]

Careful; DROP is a Data Definition Statement (DDS); the problem is they are immediate commits, meaning your transaction is broken i.e. the action can't be undone (rolled back).

Better to use "delete * from ..."

Why this is important; if something goes wrong, for example: bloomberg API is unavailable, using the DROP statement could leave you in a state where the table has been deleted, but nothing was created because of the JSON step failed.

If you wrap (delete all + insert) in a transaction; then when something fails, it simply rolls back to to the previous state i.e. the last update.

 

[Thor]

Careful; DROP is a Data Definition Statement (DDS); the problem is they are immediate commits, meaning your transaction is broken i.e. the action can't be undone (rolled back).

Better to use "delete * from ..."

Why this is important; if something goes wrong, for example: bloomberg API is unavailable, using the DROP statement could leave you in a state where the table has been deleted, but nothing was created because of the JSON step failed.

If you wrap (delete all + insert) in a transaction; then when something fails, it simply rolls back to to the previous state i.e. the last update.

<?php
// begin transaction
$db->beginTransaction();

try {
  
    //Empty the table
    $sql = $db->exec("DELETE FROM currency_test");
    $statement->execute($sql);
  
    // this query inserts data into the `currency_test` table
    $sql = "INSERT INTO `currency_test` (`currency_name`, `currency_price`, `currency_percentage`, `currency_history`) (:currency_name, :currency_price, :currency_percentage, :currency_history)";
    $statement = $db->prepare($sql);
    $statement->execute(array(':currency_name' => $currency_name, ':currency_price' => $currency_price, ':currency_percentage' => $currency_percentage, ':currency_history' => $currency_history));

    // commit transaction
    $db->commit();

} // any errors from the above database queries will be catched
catch (PDOException $e) {
    // roll back transaction
    $db->rollback();
    // log any errors to file
    ExceptionErrorHandler($e);
    require_once($footer_inc);
    exit;
}
?>

EDIT: okey I see I must use delete from rather. That makes a scary amount of sense actually.

I read this now http://dev.mysql.com/doc/refman/5.7/en/truncate-table.html

So what must I use if both those options causes an explicit commit and cannot be rolled back?

 

[Thor]

And this is why you need to go back and look at how relational database structure works. Your fundamental database design is not right, so if you want to scale this system, you need to know how that works before you proceed.

To answer your very first post:

The key, or more appropriately the primary key, is the column (or combination of columns, known as a composite key) that uniquely identifies a row of data in your table.

If I recall correctly the use of a composite key can be a bit dodgy with regard to the "ON DUPLICATE KEY" directive in MySQL, but that may have been fixed in more recent versions. Also, I am not sure the directive is ANSI standard, so if you wanted to move this database to Oracle or MS SQL Server later, you wouldn't be able to without rewriting much of the code, just saying.

In your case, the currency code, or the currency name can be the unique key. Traditionally the currency code would be used.

In this instance, when doing an insert using the "ON DUPLICATE KEY" directive, where the insert statement results in a duplicate key, an update will occur instead.

I haven't read your links regarding the reason for the frequency of updates, but I see no reason to until you get your database structure correct first given your objectives. You need to figure that out within the constraints of good practice, which seems sadly lacking here given the replies to your issue to date.

That I fully understand and is a priority I am reading up now about relational DB's.

Exciting new world!

 

[IdlePhaedrus]

That I fully understand and is a priority I am reading up now about relational DB's.

Exciting new world!

Great. the database is the core of your system. If it isn't designed correctly it will not scale. Doesn't matter how much code you put on top, if the DB isn't right, the system will eventually fail or require major rewrites.

Love your DB from the start

 

[Thor]

Great. the database is the core of your system. If it isn't designed correctly it will not scale. Doesn't matter how much code you put on top, if the DB isn't right, the system will eventually fail or require major rewrites.

Love your DB from the start

That makes complete sense I am starting to see just how deep this rabbit hole goes I have worked with php some time, but this past few days is the first time I actually went and deliberately tried to incorporate a DB everywhere to learn and I can see why you hire a separate human who's sole task is Database Architecture this is a skill set in on itself.

 

[IdlePhaedrus]

That makes complete sense I am starting to see just how deep this rabbit hole goes I have worked with php some time, but this past few days is the first time I actually went and deliberately tried to incorporate a DB everywhere to learn and I can see why you hire a separate human who's sole task is Database Architecture this is a skill set in on itself.

In the old days those people were called DBA's (database administrators). I am not sure that that job description still exists (and they were paid a cr@p load of money those days and a lot of them weren't very good), but, to my mind, any programmer who wants to interact with a database should know how to design one as well.

Last post for today here, but you might like to read why this is important:
https://en.wikipedia.org/wiki/Declarative_Referential_Integrity
https://docs.oracle.com/cd/B14117_01/server.101/b10743/data_int.htm

 

[Thor]

Thanks @IdlePhaedrus although it doesn't look like it, I read all your links. I will step up my game in each new thread I make

I appreciate the guidance. Honestly.

 

[gkm]

Use TRUNCATE instead of DROP to clear the table contents.

 

[Thor]

Use TRUNCATE instead of DROP to clear the table contents.

to my understanding TRUNCATE TABLE as this statement will trigger a commit just like CREATE TABLE or DROP TABLE so I am lost.


http://dev.mysql.com/doc/refman/5.7/en/truncate-table.html

 

[Johnatan56]

to my understanding TRUNCATE TABLE as this statement will trigger a commit just like CREATE TABLE or DROP TABLE so I am lost.


http://dev.mysql.com/doc/refman/5.7/en/truncate-table.html

Deleting records from a table logs every deletion and executes delete triggers for the records deleted. Truncate is a more powerful command that empties a table without logging each row. SQL Server prevents you from truncating a table with foreign keys referencing it, because of the need to check the foreign keys on each row.

Truncate is normally ultra-fast, ideal for cleaning out data from a temporary table. It does preserve the structure of the table for future use.

If you actually want to remove the table definitions as well as the data, simply drop the tables.

See this MSDN article for more info

https://stackoverflow.com/questions/135653/difference-between-drop-table-and-truncate-table

 

[Thor]

https://stackoverflow.com/questions/135653/difference-between-drop-table-and-truncate-table

Ie I can't roll back the data. So I can't do that in the transaction.

Logically, TRUNCATE TABLE is similar to a DELETE statement that deletes all rows, or a sequence of DROP TABLE and CREATE TABLE statements. To achieve high performance, it bypasses the DML method of deleting data. Thus, it cannot be rolled back.

 

[Thor]

I was able to ROLLBACK a CREATE TABLE statement in SQLite. Its CREATE TABLE documentation does not mention any special transactional 'gotchas'.


However in mySQL which I am using for this it seems DDL causes an implicit commit regardless

So I assume the drop then insert in a transaction will not be the ideal way in MySQL so I need to fall back to the initial idea of Insert or Update based on the existence currency_name?


Disclaimer: Please don't get irritated with me, this is all new concepts to me and I don't think I fully comprehend what I am reading as I am reading these manuals and specs, but I will "click" it in time I can feel it.

 

[[)roi(]]

I was able to ROLLBACK a CREATE TABLE statement in SQLite. Its CREATE TABLE documentation does not mention any special transactional 'gotchas'.


However in mySQL which I am using for this it seems DDL causes an implicit commit regardless

So I assume the drop then insert in a transaction will not be the ideal way in MySQL so I need to fall back to the initial idea of Insert or Update based on the existence currency_name?


Disclaimer: Please don't get irritated with me, this is all new concepts to me and I don't think I fully comprehend what I am reading as I am reading these manuals and specs, but I will "click" it in time I can feel it.

Seems like you've got a handle on this...

 

[rward]

EDIT: okey I see I must use delete from rather. That makes a scary amount of sense actually.

I read this now http://dev.mysql.com/doc/refman/5.7/en/truncate-table.html

So what must I use if both those options causes an explicit commit and cannot be rolled back?

Thor, Seeing you linking to documentation you've read makes me smile.
Thanks.