Squid url redirector

Check-in [fce4cc7368]
anonymous

Check-in [fce4cc7368]

Overview
Comment:minor function corrections
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | master | trunk
Files: files | file ages | folders
SHA3-256: fce4cc7368ce0858124572430193aaec611c45f6522cc3bb4f431f9b3145b507
User & Date: c.kworr@d4daf22a-8aaf-11de-a64d-234b64dd91b4 on 2009-10-02 06:09:18.000
Other Links: branch diff | manifest | tags
Context
2009-10-02
07:52
a lot of comments, new tripdomain function and function for reverse transformation check-in: ddb0e69dd7 user: c.kworr@d4daf22a-8aaf-11de-a64d-234b64dd91b4 tags: master, trunk
06:09
minor function corrections check-in: fce4cc7368 user: c.kworr@d4daf22a-8aaf-11de-a64d-234b64dd91b4 tags: master, trunk
2009-10-01
12:56
resolved stmt work, added sample sql database structure check-in: 09a01deb52 user: c.kworr@d4daf22a-8aaf-11de-a64d-234b64dd91b4 tags: master, trunk
Changes
Unified Diff Ignore Whitespace Patch
Modified database.sql from [8d08e9c8ac] to [dcb8126c9c]. 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

CREATE PROCEDURAL LANGUAGE plpgsql;

CREATE FUNCTION mark(domain text, new_tag text) RETURNS void
    LANGUAGE plpgsql STRICT
    AS $$
declare
 my_site varchar[];
 my_site_id smallint;
 my_tag text[];
 my_tag_id smallint;
begin
 my_site := tripdomain(domain);

 select id_site from site where my_site = site into my_site_id;
 if not found then
  insert into site (site) values (my_site);
  select id_site from site where my_site = site into my_site_id;
 end if;

 select tag from urls natural join tag where id_site = my_site_id into my_tag;
 if not found then
  my_tag := new_tag;
 else
  select array_agg(tag) from (select distinct unnest(my_tag || array[new_tag]) as tag order by tag asc) a into 
my_tag;
  delete from urls where id_site = my_site_id;
 end if;

 select id_tag from tag where my_tag = tag into my_tag_id;







|














|








1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

CREATE PROCEDURAL LANGUAGE plpgsql;

CREATE FUNCTION mark(domain text, new_tag text) RETURNS void
    LANGUAGE plpgsql STRICT
    AS $$
declare
 my_site text[];
 my_site_id smallint;
 my_tag text[];
 my_tag_id smallint;
begin
 my_site := tripdomain(domain);

 select id_site from site where my_site = site into my_site_id;
 if not found then
  insert into site (site) values (my_site);
  select id_site from site where my_site = site into my_site_id;
 end if;

 select tag from urls natural join tag where id_site = my_site_id into my_tag;
 if not found then
  my_tag := array[new_tag];
 else
  select array_agg(tag) from (select distinct unnest(my_tag || array[new_tag]) as tag order by tag asc) a into 
my_tag;
  delete from urls where id_site = my_site_id;
 end if;

 select id_tag from tag where my_tag = tag into my_tag_id;
︙︙
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71

declare
 my_tag text[];
 my_tag_id smallint;
begin

 select tag from urls natural join tag where id_site = my_site_id into my_tag;
 if not found then
  my_tag := new_tag;
 else
  select array_agg(tag) from (select distinct unnest(my_tag || array[new_tag]) as tag order by tag asc) a into 
my_tag;
  delete from urls where id_site = my_site_id;
 end if;

 select id_tag from tag where my_tag = tag into my_tag_id;
 if not found then
  insert into tag (tag) values(my_tag);
  select id_tag from tag where my_tag = tag into my_tag_id;
 end if;

 insert into urls (id_site, id_tag) values (my_site_id, my_tag_id);
end;$$;

CREATE FUNCTION tripdomain(url character varying) RETURNS character varying[]
    LANGUAGE plpgsql IMMUTABLE STRICT
    AS $_$
declare
  result varchar[];
  splitted varchar[];
  reversed varchar[];
  x integer;








|















|








41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71

declare
 my_tag text[];
 my_tag_id smallint;
begin

 select tag from urls natural join tag where id_site = my_site_id into my_tag;
 if not found then
  my_tag := array[new_tag];
 else
  select array_agg(tag) from (select distinct unnest(my_tag || array[new_tag]) as tag order by tag asc) a into 
my_tag;
  delete from urls where id_site = my_site_id;
 end if;

 select id_tag from tag where my_tag = tag into my_tag_id;
 if not found then
  insert into tag (tag) values(my_tag);
  select id_tag from tag where my_tag = tag into my_tag_id;
 end if;

 insert into urls (id_site, id_tag) values (my_site_id, my_tag_id);
end;$$;

CREATE FUNCTION tripdomain(url text) RETURNS text[]
    LANGUAGE plpgsql IMMUTABLE STRICT
    AS $_$
declare
  result varchar[];
  splitted varchar[];
  reversed varchar[];
  x integer;
︙︙