Klaus. The CTE manual with good examples is at
https://www.sqlite.org/lang_with.html
IgorT posted some good stuff about your problem using CTE.
FYI. TRIGGER is also recursive. Could be more efficient if you have to
store them anyway:
CREATE TABLE domain(d TEXT);
CREATE TRIGGER domain_after_ins AFTER INSERT ON domain WHEN instr(NEW.d,'.')
BEGIN INSERT INTO domain VALUES(substr(NEW.d,instr(NEW.d,'.')+1)); END;
PRAGMA recursive_triggers=1;
INSERT INTO domain VALUES ('a.b.c.d');
INSERT INTO domain VALUES('e.f.g.1.2');
SELECT * FROM domain;
d
----------
a.b.c.d
b.c.d
c.d
d
e.f.g.1.2
f.g.1.2
g.1.2
1.2
2
Peter
On Mon, Dec 11, 2017 at 9:22 AM, Klaus Maas <[email protected]> wrote:
> Thank you, Igor, for your solution.
> I expected that I would be pointed to a recursive approach.
> Your solution is quite elegant in my opinion.
> My problem, however, is that I have not yet understood recursive CTE's.
> Could you give me some pointers for good resources to learn about them?
>
> Well, your code example is helping a lot in understanding them, although I
> am still a long way off producing something similar myself without
> assistance.
>
> There is 1 item in the last where condition of which I do not understand
> the purpose:
> (instr(subdomain, '.') = 0 and subdomain = long)
> This means that anything in the form of 'ftp://test/' would output the
> string between the two delimiters (:// and /), in this case 'test'.
> But that is not a domain name in the format domain.tld.
> (I am working under the assumption that table links is cleaned up contains
> valid links only with the protocol and :// prepended.)
> Or am I missing something?.
>
> Klaus
>
> ------------------------------------------------------------------------
> On 2017-12-11 14:59, Igor Tandetnik wrote:
>
>> Something like this (could likely be simplified further, but this should
>> give the idea):
>>
>> with recursive subdomains as (
>> SELECT substr(link, instr(link, '://')+3, instr(substr(link,
>> instr(link, '://')+3), '/')-1) AS long,
>> substr(link, instr(link, '://')+3, instr(substr(link,
>> instr(link, '://')+3), '/')-1) as subdomain
>> FROM links
>> union all
>> select long, substr(subdomain, instr(subdomain, '.') + 1)
>> from subdomains
>> where instr(subdomain, '.') > 0)
>> select * from subdomains
>> where (instr(subdomain, '.') = 0 and subdomain = long) OR
>> (instr(subdomain, '.') > 0 and instr(substr(subdomain,
>> instr(subdomain, '.') + 1), '.')=0);
>>
>> The main point is to recursively build a table of all suffixes, then
>> select just the suffixes you want.
>>
>
> _______________________________________________
> sqlite-users mailing list
> [email protected]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
