[TYPO3-dev] Raising Mysql requirements to 4.1?

[Steffen Kamper]

"ries van Twisk" <typo3 at rvt.dds.nl> schrieb im Newsbeitrag 
news:mailman.124.1191249717.20721.typo3-dev at lists.netfielders.de...
>
> On Oct 1, 2007, at 9:08 AM, Steffen Kamper wrote:
>
>>
>> "Martin Kutschker" <Martin.Kutschker at n0spam-blackbox.net> schrieb im
>> Newsbeitrag news:mailman.1.1191246885.2835.typo3- 
>> dev at lists.netfielders.de...
>>> ries van Twisk schrieb:
>>>> Hey Dmitry,
>>>>
>>>> and others..
>>>>
>>>> what if we extension t3lib_db with tree additional functions
>>>>
>>>> 1) $GLOBAL['TYPO3_DB'] -> beginTransactionBlock();
>>>> 2) $GLOBAL['TYPO3_DB'] -> endTransactionBlock();
>>>> 3) $GLOBAL['TYPO3_DB'] -> abortTransactionBlock();
>>>>
>>>> And your below function could look like:
>>>>
>>>>> if (get_resource_type($GLOBALS['TYPO3_DB']->link) == 'mysql  link') {
>>>>>     $query =
>>>>> $GLOBALS['TYPO3_DB']->INSERTquery('tx_realurl_urlencodecache',
>>>>> $insertFields);
>>>>>     $query .= ' ON DUPLICATE KEY UPDATE tstamp=' .
>>>>> $insertFields['tstamp'];
>>>>>     $GLOBALS['TYPO3_DB']->sql_query($query);
>>>>> }
>>>>> else {
>>>>         $GLOBAL['TYPO3_DB'] -> beginTransactionBlock();
>>>>>     $GLOBALS['TYPO3_DB']->exec_DELETEquery 
>>>>> ('tx_realurl_urlencodecache',
>>>>> 'url_hash='.intval($hash));
>>>>>     $GLOBALS['TYPO3_DB']->exec_INSERTquery 
>>>>> ('tx_realurl_urlencodecache',
>>>>> $insertFields);
>>>>            $GLOBAL['TYPO3_DB'] -> endTransactionBlock();
>>>>> }
>>>>
>>>>
>>>> By doing it like that we can start supporting other RDBM's much 
>>>> better.
>>>> For the native MySQL ISAM driver the tree functions above simply  don't 
>>>> do
>>>> anything, with InnoDB we can also use transaction blocks. (Actually
>>>> MyISAM ignores transaction blocks anyways).
>>>
>>> If you care to check for the table type on Mysql you can replace  it (in
>>> this case!) with a TABLE LOCK. But the problem is that you cannot  abort
>>> it, so it's not a general solution.
>>>
>>> Intersting is also that the Mysql version does an UPDATE but we have
>>> DELETE/UPDATE pair on other DBs. Shouldn't that be a SELECT/UPDATE/ 
>>> INSERT
>>> triple?
>>>
>>> Masi
>>
>> in oracle there is a "upset"-syntax
>>
>> IF FOUND
>>
>>    THEN UPDATE
>>
>> ELSE
>>
>>    INSERT;
>>
>>
>>
>> in PG there is a "MERGE" syntax
>>
>>
>>
>> vg  Steffen
>>
>>
> Hey Steffen
>
> I don't think that PostgreSQL has a merge function, unless it's an 
> external PG module.
>
> It is possible with a SP, but I want to stay away from that for now  and 
> stick to standard SQL where possible.
>
> this is an SP example how to do it :
> http://www.postgresql.org/docs/current/static/plpgsql-control- 
> structures.html#PLPGSQL-UPSERT-EXAMPLE
>
> Ries
>

you're right ries, as you know i havn't expierience with PG. I found that 
MERGE is one of the latest SQL-Syntax and people want to have that 
integrated in PG.
May be it could be done by a trigger or the way you described. i only looked 
arround for a simular syntax to INSERT ... ON DUPLICATE UPDATE ...

vg  Steffen