Re: recursive search
From: Jerry Stuckle <jstucklex_at_attglobal.net>
Date: Sun, 11 Sep 2016 08:49:57 -0400
Message-ID: <nr3jtd$e5m$1_at_jstuckle.eternal-september.org>
>> On 9/10/2016 12:57 PM, J.O. Aho wrote:
>>> On 09/10/2016 06:27 PM, mireero wrote:
>>>> Thanks for the answers.
>>>>
>>>> What I get is:
>>>> - No recursive support in mysql (and might be handled by other database
>>>> systems).
>>>> - I can manually search with n joins if not going until the end is an
>>>> option (not my case here).
>>>>
>>>> What I have is:
>>>> - My app is not written around this recursive search, it's only one
>>>> query among maybe 30.
>>>> - I'd prefer the search to be fully recursive.
>>>>
>>>> Conclusion:
>>>> I'll stick with mysql.
>>>> I'll go with PHP.
>>>
>>> I would recommend you to take the step over to MariaDB and it's oqgraph,
>>> the transition is quite simple, just uninstall the mySQL and install the
>>> MariaDB, run a database update as you would normally do when you upgrade
>>> your database. No need to change any code you already done and most
>>> distributions do provide MariaDB as a replacement for mySQL.
>>>
>>>
>>>> I'll have a look at cursors (because I'm still learning and I'm
>>>> interested).
>>>> I do know basics of stored procedures but if you has hints/links about
>>>> how to create a recursive search, I would appreciate (I know, I can also
>>>> google it!).
>>>
>>> Making a recursive stored procedure is kind of the same as making a
>>> recursive function, where you start with a starting point and then call
>>> the same sp, but now with the next generation.
>>> The cursor will work like a for-loop.
>>>
>>> Just keep in mind, the more generations you have, the longer time it
>>> will take with mysql (or other traditional RDBMS), so if you will have
>>> many generations, you need other alternatives and even running logic in
>>> another layer will not be as fast as a graph-database.
>>>
>>> I'm not talking you to replace the whole system with a graph-database,
>>> but take help of it where the mySQL ain't good enough.
>>>
>>
>> J.O., a graph-database is not the same as recursive SQL. Recursive SQL
>> has been part of the standard since sometime in the 90's, supported by
>> most of the commercial databases, but not MySQL (or MariaDB, AFAIK).
>>
>> I believe PostGreSQL supports recursive SQL, and the free versions of
>> DB2 and Oracle do, also.
>>
Date: Sun, 11 Sep 2016 08:49:57 -0400
Message-ID: <nr3jtd$e5m$1_at_jstuckle.eternal-september.org>
On 9/11/2016 3:18 AM, Ikke wrote:
> Jerry Stuckle wrote: >
>> On 9/10/2016 12:57 PM, J.O. Aho wrote:
>>> On 09/10/2016 06:27 PM, mireero wrote:
>>>> Thanks for the answers.
>>>>
>>>> What I get is:
>>>> - No recursive support in mysql (and might be handled by other database
>>>> systems).
>>>> - I can manually search with n joins if not going until the end is an
>>>> option (not my case here).
>>>>
>>>> What I have is:
>>>> - My app is not written around this recursive search, it's only one
>>>> query among maybe 30.
>>>> - I'd prefer the search to be fully recursive.
>>>>
>>>> Conclusion:
>>>> I'll stick with mysql.
>>>> I'll go with PHP.
>>>
>>> I would recommend you to take the step over to MariaDB and it's oqgraph,
>>> the transition is quite simple, just uninstall the mySQL and install the
>>> MariaDB, run a database update as you would normally do when you upgrade
>>> your database. No need to change any code you already done and most
>>> distributions do provide MariaDB as a replacement for mySQL.
>>>
>>>
>>>> I'll have a look at cursors (because I'm still learning and I'm
>>>> interested).
>>>> I do know basics of stored procedures but if you has hints/links about
>>>> how to create a recursive search, I would appreciate (I know, I can also
>>>> google it!).
>>>
>>> Making a recursive stored procedure is kind of the same as making a
>>> recursive function, where you start with a starting point and then call
>>> the same sp, but now with the next generation.
>>> The cursor will work like a for-loop.
>>>
>>> Just keep in mind, the more generations you have, the longer time it
>>> will take with mysql (or other traditional RDBMS), so if you will have
>>> many generations, you need other alternatives and even running logic in
>>> another layer will not be as fast as a graph-database.
>>>
>>> I'm not talking you to replace the whole system with a graph-database,
>>> but take help of it where the mySQL ain't good enough.
>>>
>>
>> J.O., a graph-database is not the same as recursive SQL. Recursive SQL
>> has been part of the standard since sometime in the 90's, supported by
>> most of the commercial databases, but not MySQL (or MariaDB, AFAIK).
>>
>> I believe PostGreSQL supports recursive SQL, and the free versions of
>> DB2 and Oracle do, also.
>>
> Oh yes, I used the "SELECT ..... START WITH ...... CONNECT BY ....." in > Oracle for a multilevel kit bill of materials, worked like a charm. > The problem with the OP's setup is that in his/her case, the statement would > never end. > > Herman Viaene >
You can limit the recursion depth so that does not happen.
-- ================== Remove the "x" from my email address Jerry Stuckle jstucklex_at_attglobal.net ==================Received on Sun Sep 11 2016 - 14:49:57 CEST