Re: recursive search

From: Ikke <hviaene_at_invalid.org>
Date: Sun, 11 Sep 2016 09:18:59 +0200
Message-ID: <CaidnScaE_b5mEjKnZ2dnUU7-LPNnZ2d_at_edpnet.net>


Jerry Stuckle wrote:

> On 9/10/2016 12:57 PM, J.O. Aho wrote:

[Quoted] >> On 09/10/2016 06:27 PM, mireero wrote:
[Quoted] >>> 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 Received on Sun Sep 11 2016 - 09:18:59 CEST

Original text of this message