Re: [Maria-discuss] newbie question

12 Jun 2014

      hi guys! thaks for explanations and ideas, well the first problem is ok,
change to -1 and -2 and after 2 and 1, that was what i'm considering at the
first time i see primary key duplicated, and since mysql don't suport
defered insert/update/delete, ok let's workaround :)

my problem now is: change it 5 to position 2, and reposition the list,
position 2 will become position 4, and position 4 will become position 5,
position 3 will not change since it's locked='true'

i'm considering something like, change position 5 to -5, change position 4
to 5, change position 2 to 4, change -5 to 2, but how to do this only with
sql language? and the second problem is: change position 2 to position 5,
position 4 will become position 2, position 5 will become position 4

any other idea about how to do this? i think that's the easiest way,
considering that i will need a lock at positions 2,3,4,5 before start
updates, right? maybe i could do this with select * from test where id in
(2,3,4,5) for update, or lock table

ideas are wellcome

2014-06-12 8:32 GMT-03:00 Pantelis Theodosiou <ypercube@gmail.com>:
...
On Thu, Jun 12, 2014 at 9:57 AM, Federico Razzoli <federico_raz@yahoo.it>
wrote:
...
Duplicate data cannot be inserted in MariaDB, in no cases.
PostgreSQL has a different behaviour: transactions can optionally be
"deferred", which means that integrity checks will be done on commit. This
means that you can temporarly insert inconsistent data, and fix them before
commit.The same option is available for foreign keys and other features
that I don't remember.
Correct. Standard SQL behaviour is checking constraints at the end of a
statement. The optional ability to defer a constraint check means that the
check is done at the end of the transaction and not at the end of
statement. (Postgers and Oracle can do that, SQL-Server does not.)  No
duplicate data will be inserted either, in both cases, deferred or not
constraints. Any inconsistent data is only visible during the transaction,
by the transaction - with the MVCC model no other transaction can see them
before that one is committed.
What MySQL and MariaDB does is different than standard/common SQL
behaviour, it is checking the constraints after *every row update*. That
is causing the problem that Roberto faces.
...
But we are talking about MariaDB, so... what you can do, in you example,
is:
UPDATE ... SET id = NULL WHERE id = 1; -- does not exist, right?
UPDATE ... SET id = 1 WHERE id = 2; -- change other values
UPDATE ... SET id = 1 WHERE id IS NULL;
This is fine - as long as there are not any NULL values already on the
table in the id column. If there are, the 3rd UPDATE will convert all of
them to 1. You can use negative values - of course if and only if all your
ids are either positive or NULL:
UPDATE ... SET id = -2 WHERE id = 1;
UPDATE ... SET id = -1 WHERE id = 2;
UPDATE ... SET id = -id WHERE id < 0;
This can easily be extended for more complex updates and we only need two
UPDATE statements - and I suppose better inside a transaction, for InnoDB
engine:
UPDATE ...
SET id = CASE id
            WHEN 1 THEN -2
            WHEN 2 THEN -1
            WHEN ...
         END
WHERE id IN (1,2) ;
UPDATE ... SET id = -id WHERE id < 0;
Pantelis
...
Or, if your case is more complex than this:
1) lock the table
2) drop the pk
3) update
4) unlock
Ciao
Federico
_______________________________________________
Mailing list: https://launchpad.net/~maria-discuss
Post to     : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp
_______________________________________________
Mailing list: https://launchpad.net/~maria-discuss
Post to     : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp
-- 
Roberto Spadim
SPAEmpresarial
Eng. Automação e Controle