PasteRack.org
Paste #
63431
2018-11-23 11:58:31
Fork
as a new paste.
Paste viewed 1233 times.
Tweet
Embed:
<link type="text/css" rel="stylesheet" href="http://pasterack.org/scribble.css"/><link type="text/css" rel="stylesheet" href="http://pasterack.org/racket.css"/><link type="text/css" rel="stylesheet" href="http://fonts.googleapis.com/css?family=Droid+Sans+Mono"/><div style="font-family:'Droid Sans Mono',monospace;background-color:transparent"><ol start="0" style="font-size:70%;color:#A0A0A0"><li><span style="font-family:'Droid Sans Mono',monospace;font-size:125%"><a class="RktModLink" data-pltdoc="x" href="http://docs.racket-lang.org/guide/Module_Syntax.html#%28part._hash-lang%29"><span class="RktMod">#lang</span></a><span class="RktMeta"></span><span class="hspace"> </span><span class="RktMeta"></span><a class="RktModLink" data-pltdoc="x" href="http://docs.racket-lang.org/reference/index.html"><span class="RktSym">racket</span></a><span class="RktMeta"></span></span></li><li><span style="font-family:'Droid Sans Mono',monospace;font-size:125%"><span class="RktMeta"></span><span class="RktPn">(</span><span class="RktSym"><a class="RktStxLink" data-pltdoc="x" href="http://docs.racket-lang.org/reference/define.html#%28form._%28%28lib._racket%2Fprivate%2Fbase..rkt%29._define%29%29">define</a></span><span class="RktMeta"></span><span class="hspace"> </span><span class="RktMeta"></span><span class="RktPn">(</span><span class="RktSym">intersect-tags</span><span class="RktMeta"></span><span class="hspace"> </span><span class="RktMeta"></span><span class="RktMeta">.</span><span class="RktMeta"></span><span class="hspace"> </span><span class="RktMeta"></span><span class="RktSym">k</span><span class="RktPn">)</span><span class="RktMeta"></span></span></li><li><span style="font-family:'Droid Sans Mono',monospace;font-size:125%"><span class="RktMeta"></span><span class="hspace"> </span><span class="RktMeta"></span><span class="RktPn">(</span><span class="RktSym"><a class="RktStxLink" data-pltdoc="x" href="http://docs.racket-lang.org/reference/define.html#%28form._%28%28quote._~23~25kernel%29._define-values%29%29">define-values</a></span><span class="RktMeta"></span><span class="hspace"> </span><span class="RktMeta"></span><span class="RktPn">(</span><span class="RktSym">exclude-tags</span><span class="RktMeta"></span><span class="hspace"> </span><span class="RktMeta"></span><span class="RktSym">include-tags</span><span class="RktPn">)</span><span class="RktMeta"></span><span class="hspace"> </span><span class="RktMeta"></span><span class="RktPn">(</span><span class="RktSym">split-list</span><span class="RktMeta"></span><span class="hspace"> </span><span class="RktMeta"></span><span class="RktPn">(</span><span class="RktSym"><a class="RktStxLink" data-pltdoc="x" href="http://docs.racket-lang.org/reference/lambda.html#%28form._%28%28lib._racket%2Fprivate%2Fbase..rkt%29._~ce~bb%29%29">λ</a></span><span class="RktMeta"></span><span class="hspace"> </span><span class="RktMeta"></span><span class="RktPn">(</span><span class="RktSym">w</span><span class="RktPn">)</span><span class="RktMeta"></span><span class="hspace"> </span><span class="RktMeta"></span><span class="RktPn">(</span><span class="RktSym"><a class="RktValLink" data-pltdoc="x" href="http://docs.racket-lang.org/reference/regexp.html#%28def._%28%28quote._~23~25kernel%29._regexp-match~3f%29%29">regexp-match?</a></span><span class="RktMeta"></span><span class="hspace"> </span><span class="RktMeta"></span><span class="RktVal">"^-"</span><span class="RktMeta"></span><span class="hspace"> </span><span class="RktMeta"></span><span class="RktSym">w</span><span class="RktPn">)</span><span class="RktPn">)</span><span class="RktMeta"></span><span class="hspace"> </span><span class="RktMeta"></span><span class="RktSym">k</span><span class="RktPn">)</span><span class="RktPn">)</span><span class="RktMeta"></span></span></li><li><span style="font-family:'Droid Sans Mono',monospace;font-size:125%"><span class="RktMeta"> </span></span></li><li><span style="font-family:'Droid Sans Mono',monospace;font-size:125%"><span class="RktMeta"></span><span class="hspace"> </span><span class="RktMeta"></span><span class="RktPn">(</span><span class="RktSym">ltime</span><span class="RktMeta"></span><span class="hspace"> </span><span class="RktMeta"></span><span class="RktVal">"intersect-tags"</span><span class="RktMeta"></span></span></li><li><span style="font-family:'Droid Sans Mono',monospace;font-size:125%"><span class="RktMeta"></span><span class="hspace"> </span><span class="RktMeta"></span><span class="RktPn">(</span><span class="RktSym"><a class="RktValLink" data-pltdoc="x" href="http://docs.racket-lang.org/reference/pairs.html#%28def._%28%28lib._racket%2Fprivate%2Fmap..rkt%29._map%29%29">map</a></span><span class="RktMeta"></span><span class="hspace"> </span><span class="RktMeta"></span><span class="RktSym">row->struct</span><span class="RktMeta"></span></span></li><li><span style="font-family:'Droid Sans Mono',monospace;font-size:125%"><span class="RktMeta"></span><span class="hspace"> </span><span class="RktMeta"></span><span class="RktPn">(</span><span class="RktSym"><a class="RktValLink" data-pltdoc="x" href="http://docs.racket-lang.org/reference/procedures.html#%28def._%28%28lib._racket%2Fprivate%2Fbase..rkt%29._apply%29%29">apply</a></span><span class="RktMeta"></span><span class="hspace"> </span><span class="RktMeta"></span><span class="RktSym">query-rows</span><span class="RktMeta"></span><span class="hspace"> </span><span class="RktMeta"></span><span class="RktPn">(</span><span class="RktSym">sqldb-param</span><span class="RktPn">)</span><span class="RktMeta"></span></span></li><li><span style="font-family:'Droid Sans Mono',monospace;font-size:125%"><span class="RktMeta"></span><span class="hspace"> </span><span class="RktMeta"></span><span class="RktPn">(</span><span class="RktSym"><a class="RktValLink" data-pltdoc="x" href="http://docs.racket-lang.org/reference/Writing.html#%28def._%28%28quote._~23~25kernel%29._format%29%29">format</a></span><span class="RktMeta"></span><span class="hspace"> </span><span class="RktMeta"></span><span class="RktPn">(</span><span class="RktSym"><a class="RktValLink" data-pltdoc="x" href="http://docs.racket-lang.org/reference/strings.html#%28def._%28%28quote._~23~25kernel%29._string-append%29%29">string-append</a></span><span class="RktMeta"></span><span class="hspace"> </span><span class="RktMeta"></span><span class="RktVal">"SELECT</span><span class="hspace"> </span><span class="RktVal">Files.filename,</span><span class="hspace"> </span><span class="RktVal">Files.file_id,</span><span class="hspace"> </span><span class="RktVal">Files.size,</span><span class="hspace"> </span><span class="RktVal">Files.num_tags</span><span class="hspace"> </span><span class="RktVal">FROM</span><span class="hspace"> </span><span class="RktVal">Files</span><span class="hspace"> </span><span class="RktVal">"</span><span class="RktMeta"></span></span></li><li><span style="font-family:'Droid Sans Mono',monospace;font-size:125%"><span class="RktMeta"></span><span class="hspace"> </span><span class="RktMeta"></span><span class="RktVal">"</span><span class="hspace"> </span><span class="RktVal">INNER</span><span class="hspace"> </span><span class="RktVal">JOIN</span><span class="hspace"> </span><span class="RktVal">"</span><span class="RktMeta"></span></span></li><li><span style="font-family:'Droid Sans Mono',monospace;font-size:125%"><span class="RktMeta"></span><span class="hspace"> </span><span class="RktMeta"></span><span class="RktVal">"</span><span class="hspace"> </span><span class="RktVal">(SELECT</span><span class="hspace"> </span><span class="RktVal">file_id</span><span class="hspace"> </span><span class="RktVal">FROM</span><span class="hspace"> </span><span class="RktVal">FileTags</span><span class="hspace"> </span><span class="RktVal">WHERE</span><span class="hspace"> </span><span class="RktVal">tag_id</span><span class="hspace"> </span><span class="RktVal">IN</span><span class="hspace"> </span><span class="RktVal">(~a)</span><span class="hspace"> </span><span class="RktVal">GROUP</span><span class="hspace"> </span><span class="RktVal">BY</span><span class="hspace"> </span><span class="RktVal">file_id</span><span class="hspace"> </span><span class="RktVal">HAVING</span><span class="hspace"> </span><span class="RktVal">COUNT(*)=~a"</span><span class="RktMeta"></span></span></li><li><span style="font-family:'Droid Sans Mono',monospace;font-size:125%"><span class="RktMeta"></span><span class="hspace"> </span><span class="RktMeta"></span><span class="RktVal">"</span><span class="hspace"> </span><span class="RktVal">EXCEPT"</span><span class="RktMeta"></span></span></li><li><span style="font-family:'Droid Sans Mono',monospace;font-size:125%"><span class="RktMeta"></span><span class="hspace"> </span><span class="RktMeta"></span><span class="RktVal">"</span><span class="hspace"> </span><span class="RktVal">SELECT</span><span class="hspace"> </span><span class="RktVal">file_id</span><span class="hspace"> </span><span class="RktVal">FROM</span><span class="hspace"> </span><span class="RktVal">FileTags</span><span class="hspace"> </span><span class="RktVal">WHERE</span><span class="hspace"> </span><span class="RktVal">tag_id</span><span class="hspace"> </span><span class="RktVal">IN</span><span class="hspace"> </span><span class="RktVal">(~a))"</span><span class="RktMeta"></span></span></li><li><span style="font-family:'Droid Sans Mono',monospace;font-size:125%"><span class="RktMeta"></span><span class="hspace"> </span><span class="RktMeta"></span><span class="RktVal">"</span><span class="hspace"> </span><span class="RktVal">AS</span><span class="hspace"> </span><span class="RktVal">sub</span><span class="hspace"> </span><span class="RktVal">"</span><span class="RktMeta"></span></span></li><li><span style="font-family:'Droid Sans Mono',monospace;font-size:125%"><span class="RktMeta"></span><span class="hspace"> </span><span class="RktMeta"></span><span class="RktVal">"</span><span class="hspace"> </span><span class="RktVal">ON</span><span class="hspace"> </span><span class="RktVal">Files.file_id=sub.file_id</span><span class="hspace"> </span><span class="RktVal">"</span><span class="RktPn">)</span><span class="RktMeta"></span></span></li><li><span style="font-family:'Droid Sans Mono',monospace;font-size:125%"><span class="RktMeta"></span><span class="hspace"> </span><span class="RktMeta"></span><span class="RktPn">(</span><span class="RktSym"><a class="RktValLink" data-pltdoc="x" href="http://docs.racket-lang.org/reference/strings.html#%28def._%28%28lib._racket%2Fstring..rkt%29._string-join%29%29">string-join</a></span><span class="RktMeta"></span><span class="hspace"> </span><span class="RktMeta"></span><span class="RktPn">(</span><span class="RktSym"><a class="RktValLink" data-pltdoc="x" href="http://docs.racket-lang.org/reference/pairs.html#%28def._%28%28lib._racket%2Fprivate%2Fmap..rkt%29._map%29%29">map</a></span><span class="RktMeta"></span><span class="hspace"> </span><span class="RktMeta"></span><span class="RktPn">(</span><span class="RktSym"><a class="RktStxLink" data-pltdoc="x" href="http://docs.racket-lang.org/reference/lambda.html#%28form._%28%28lib._racket%2Fprivate%2Fbase..rkt%29._~ce~bb%29%29">λ</a></span><span class="RktMeta"></span><span class="hspace"> </span><span class="RktMeta"></span><span class="RktPn">(</span><span class="RktSym">x</span><span class="RktPn">)</span><span class="RktMeta"></span><span class="hspace"> </span><span class="RktMeta"></span><span class="RktVal">"?"</span><span class="RktPn">)</span><span class="RktMeta"></span><span class="hspace"> </span><span class="RktMeta"></span><span class="RktSym">include-tags</span><span class="RktPn">)</span><span class="RktMeta"></span><span class="hspace"> </span><span class="RktMeta"></span><span class="RktVal">","</span><span class="RktPn">)</span><span class="RktMeta"></span></span></li><li><span style="font-family:'Droid Sans Mono',monospace;font-size:125%"><span class="RktMeta"></span><span class="hspace"> </span><span class="RktMeta"></span><span class="RktPn">(</span><span class="RktSym"><a class="RktValLink" data-pltdoc="x" href="http://docs.racket-lang.org/reference/pairs.html#%28def._%28%28quote._~23~25kernel%29._length%29%29">length</a></span><span class="RktMeta"></span><span class="hspace"> </span><span class="RktMeta"></span><span class="RktSym">include-tags</span><span class="RktPn">)</span><span class="RktMeta"></span></span></li><li><span style="font-family:'Droid Sans Mono',monospace;font-size:125%"><span class="RktMeta"></span><span class="hspace"> </span><span class="RktMeta"></span><span class="RktPn">(</span><span class="RktSym"><a class="RktValLink" data-pltdoc="x" href="http://docs.racket-lang.org/reference/strings.html#%28def._%28%28lib._racket%2Fstring..rkt%29._string-join%29%29">string-join</a></span><span class="RktMeta"></span><span class="hspace"> </span><span class="RktMeta"></span><span class="RktPn">(</span><span class="RktSym"><a class="RktValLink" data-pltdoc="x" href="http://docs.racket-lang.org/reference/pairs.html#%28def._%28%28lib._racket%2Fprivate%2Fmap..rkt%29._map%29%29">map</a></span><span class="RktMeta"></span><span class="hspace"> </span><span class="RktMeta"></span><span class="RktPn">(</span><span class="RktSym"><a class="RktStxLink" data-pltdoc="x" href="http://docs.racket-lang.org/reference/lambda.html#%28form._%28%28lib._racket%2Fprivate%2Fbase..rkt%29._~ce~bb%29%29">λ</a></span><span class="RktMeta"></span><span class="hspace"> </span><span class="RktMeta"></span><span class="RktPn">(</span><span class="RktSym">x</span><span class="RktPn">)</span><span class="RktMeta"></span><span class="hspace"> </span><span class="RktMeta"></span><span class="RktVal">"?"</span><span class="RktPn">)</span><span class="RktMeta"></span><span class="hspace"> </span><span class="RktMeta"></span><span class="RktSym">exclude-tags</span><span class="RktPn">)</span><span class="RktMeta"></span><span class="hspace"> </span><span class="RktMeta"></span><span class="RktVal">","</span><span class="RktPn">)</span><span class="RktPn">)</span><span class="RktMeta"></span></span></li><li><span style="font-family:'Droid Sans Mono',monospace;font-size:125%"><span class="RktMeta"></span><span class="hspace"> </span><span class="RktMeta"></span><span class="RktPn">(</span><span class="RktSym"><a class="RktValLink" data-pltdoc="x" href="http://docs.racket-lang.org/reference/pairs.html#%28def._%28%28quote._~23~25kernel%29._append%29%29">append</a></span><span class="RktMeta"></span><span class="hspace"> </span><span class="RktMeta"></span><span class="RktPn">(</span><span class="RktSym"><a class="RktValLink" data-pltdoc="x" href="http://docs.racket-lang.org/reference/pairs.html#%28def._%28%28lib._racket%2Fprivate%2Fmap..rkt%29._map%29%29">map</a></span><span class="RktMeta"></span><span class="hspace"> </span><span class="RktMeta"></span><span class="RktPn">(</span><span class="RktSym"><a class="RktStxLink" data-pltdoc="x" href="http://docs.racket-lang.org/reference/lambda.html#%28form._%28%28lib._racket%2Fprivate%2Fbase..rkt%29._~ce~bb%29%29">λ</a></span><span class="RktMeta"></span><span class="hspace"> </span><span class="RktMeta"></span><span class="RktPn">(</span><span class="RktSym">t</span><span class="RktPn">)</span><span class="RktMeta"></span><span class="hspace"> </span><span class="RktMeta"></span><span class="RktPn">(</span><span class="RktSym">tag-id-lookup</span><span class="RktMeta"></span><span class="hspace"> </span><span class="RktMeta"></span><span class="RktSym">t</span><span class="RktPn">)</span><span class="RktPn">)</span><span class="RktMeta"></span><span class="hspace"> </span><span class="RktMeta"></span><span class="RktSym">include-tags</span><span class="RktPn">)</span><span class="RktMeta"></span></span></li><li><span style="font-family:'Droid Sans Mono',monospace;font-size:125%"><span class="RktMeta"></span><span class="hspace"> </span><span class="RktMeta"></span><span class="RktPn">(</span><span class="RktSym"><a class="RktValLink" data-pltdoc="x" href="http://docs.racket-lang.org/reference/pairs.html#%28def._%28%28lib._racket%2Fprivate%2Fmap..rkt%29._map%29%29">map</a></span><span class="RktMeta"></span><span class="hspace"> </span><span class="RktMeta"></span><span class="RktPn">(</span><span class="RktSym"><a class="RktStxLink" data-pltdoc="x" href="http://docs.racket-lang.org/reference/lambda.html#%28form._%28%28lib._racket%2Fprivate%2Fbase..rkt%29._~ce~bb%29%29">λ</a></span><span class="RktMeta"></span><span class="hspace"> </span><span class="RktMeta"></span><span class="RktPn">(</span><span class="RktSym">t</span><span class="RktPn">)</span><span class="RktMeta"></span><span class="hspace"> </span><span class="RktMeta"></span><span class="RktPn">(</span><span class="RktSym">tag-id-lookup</span><span class="RktMeta"></span><span class="hspace"> </span><span class="RktMeta"></span><span class="RktPn">(</span><span class="RktSym"><a class="RktValLink" data-pltdoc="x" href="http://docs.racket-lang.org/reference/strings.html#%28def._%28%28quote._~23~25kernel%29._substring%29%29">substring</a></span><span class="RktMeta"></span><span class="hspace"> </span><span class="RktMeta"></span><span class="RktSym">t</span><span class="RktMeta"></span><span class="hspace"> </span><span class="RktMeta"></span><span class="RktVal">1</span><span class="RktPn">)</span><span class="RktPn">)</span><span class="RktPn">)</span><span class="RktMeta"></span><span class="hspace"> </span><span class="RktMeta"></span><span class="RktSym">exclude-tags</span><span class="RktPn">)</span><span class="RktPn">)</span><span class="RktPn">)</span><span class="RktPn">)</span><span class="RktPn">)</span><span class="RktPn">)</span><span class="RktMeta"></span></span></li></ol><p>=></p><blockquote><table style="font-size:90%;table-layout:fixed;width:100%;word-wrap:break-word"></table></blockquote></div>
#lang
racket
(
define
(
intersect-tags
.
k
)
(
define-values
(
exclude-tags
include-tags
)
(
split-list
(
λ
(
w
)
(
regexp-match?
"^-"
w
)
)
k
)
)
(
ltime
"intersect-tags"
(
map
row->struct
(
apply
query-rows
(
sqldb-param
)
(
format
(
string-append
"SELECT
Files.filename,
Files.file_id,
Files.size,
Files.num_tags
FROM
Files
"
"
INNER
JOIN
"
"
(SELECT
file_id
FROM
FileTags
WHERE
tag_id
IN
(~a)
GROUP
BY
file_id
HAVING
COUNT(*)=~a"
"
EXCEPT"
"
SELECT
file_id
FROM
FileTags
WHERE
tag_id
IN
(~a))"
"
AS
sub
"
"
ON
Files.file_id=sub.file_id
"
)
(
string-join
(
map
(
λ
(
x
)
"?"
)
include-tags
)
","
)
(
length
include-tags
)
(
string-join
(
map
(
λ
(
x
)
"?"
)
exclude-tags
)
","
)
)
(
append
(
map
(
λ
(
t
)
(
tag-id-lookup
t
)
)
include-tags
)
(
map
(
λ
(
t
)
(
tag-id-lookup
(
substring
t
1
)
)
)
exclude-tags
)
)
)
)
)
)
=>