maria-discuss team mailing list archive

[Roberto Spadim <roberto@xxxxxxxxxxxxx>]

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 :)

now the full problem....
think about a list with 'locked' and 'unlocked' positions, and important
fields that make that entry information unique...
every position (id column) must be >0, all positions <=0 i'm considering as
'inserted but not completed transition'
|id|locked|other important data|
|1|false|blabla|
|2|false|blabla2|
|3|true|blabla3|
|4|false|blabla4|
|5|false|blabla5|
|6|true|blabla6|


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@xxxxxxxxx>:

>
>
>
> On Thu, Jun 12, 2014 at 9:57 AM, Federico Razzoli <federico_raz@xxxxxxxx>
> 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@xxxxxxxxxxxxxxxxxxx
>> Unsubscribe : https://launchpad.net/~maria-discuss
>> More help   : https://help.launchpad.net/ListHelp
>>
>
>
> _______________________________________________
> Mailing list: https://launchpad.net/~maria-discuss
> Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help   : https://help.launchpad.net/ListHelp
>
>


-- 
Roberto Spadim
SPAEmpresarial
Eng. Automação e Controle