[mirrormanager] master: initial.py: move armhfp to primary arch list (ff49fad)
by Matt Domsch
Repository : http://git.fedorahosted.org/cgit/
On branch : master
>---------------------------------------------------------------
commit ff49fadc48826fd9ea23c0a1993d636cb49b1ffb
Author: Matt Domsch <matt(a)domsch.com>
Date: Tue Jul 30 22:57:28 2013 -0500
initial.py: move armhfp to primary arch list
As of Fedora 20, armhfp is a primary arch.
>---------------------------------------------------------------
server/mirrormanager/initial.py | 4 ++--
1 files changed, 2 insertions(+), 2 deletions(-)
diff --git a/server/mirrormanager/initial.py b/server/mirrormanager/initial.py
index 2c0fbca..ceebb03 100644
--- a/server/mirrormanager/initial.py
+++ b/server/mirrormanager/initial.py
@@ -35,8 +35,8 @@ def create_arches():
except DuplicateEntryError:
pass
- primary_arches = (u'i386', u'x86_64', u'source')
- secondary_arches = (u'ppc', u'ppc64', u'sparc', u'sparc64', u'arm', u'armhfp', u's390', u's390x', u'ia64')
+ primary_arches = (u'i386', u'x86_64', u'source', u'armhfp')
+ secondary_arches = (u'ppc', u'ppc64', u'sparc', u'sparc64', u'arm', u's390', u's390x', u'ia64')
for arch in primary_arches:
_do_create(arch, True)
for arch in secondary_arches:
10 years, 9 months
[mirrormanager] master: fix traceback in crawler_perhost, warn about keepalives for each category (a479ba6)
by Matt Domsch
Repository : http://git.fedorahosted.org/cgit/
On branch : master
>---------------------------------------------------------------
commit a479ba61211e8871920e27ba91169c24ae47b7c6
Author: Matt Domsch <matt(a)domsch.com>
Date: Tue Jul 30 22:52:21 2013 -0500
fix traceback in crawler_perhost, warn about keepalives for each category
categoryUrl could be unset if there were not host categories. This was
then incorrectly derefenced after the loop over the categories,
causing a traceback. So ensure categoryUrl is at least set empty.
Furthermore, bail early and log a message if there are no host
categories.
Move the warning log message re no keepalives inside the host category
loop - each category may have different protocols to access it.
>---------------------------------------------------------------
server/crawler_perhost | 15 +++++++++------
1 files changed, 9 insertions(+), 6 deletions(-)
diff --git a/server/crawler_perhost b/server/crawler_perhost
index dfc9292..cf3c936 100755
--- a/server/crawler_perhost
+++ b/server/crawler_perhost
@@ -702,7 +702,13 @@ def per_host(host, options):
hoststate = hostState(http_debuglevel=http_debuglevel, ftp_debuglevel=ftp_debuglevel)
- for hc in select_host_categories_to_scan(options):
+ categoryUrl = ''
+ host_categories_to_scan = select_host_categories_to_scan(options)
+ if len(host_categories_to_scan) == 0:
+ mark_not_up2date(None, host, "No host category directories found. Check that your Host Category URLs are correct.")
+ return 1
+
+ for hc in host_categories_to_scan:
if must_dienow:
raise TimeoutException
@@ -781,8 +787,8 @@ def per_host(host, options):
mark_not_up2date(sys.exc_info(), host, "Unknown exception raised. This is a bug in the MM crawler.")
rc = 1
break
- if categoryUrl.startswith('http') and not hoststate.keepalives_available:
- logger.warning("Host %s (id=%d) does not have HTTP Keep-Alives enabled." % (host.name, host.id))
+ if categoryUrl.startswith('http') and not hoststate.keepalives_available:
+ logger.warning("Host %s (id=%d) does not have HTTP Keep-Alives enabled." % (host.name, host.id))
hoststate.close()
if options.timeout_minutes > 0:
@@ -790,9 +796,6 @@ def per_host(host, options):
if rc == 0:
if len(host_category_dirs) > 0:
sync_hcds(host, host_category_dirs)
- else:
- mark_not_up2date(None, host, "No host category directories found. Check that your Host Category URLs are correct.")
- return 1
return rc
def main():
10 years, 9 months
[mirrormanager] master: crawler_perhost: limit try_later_delay to 4 seconds (9becec2)
by Matt Domsch
Repository : http://git.fedorahosted.org/cgit/
On branch : master
>---------------------------------------------------------------
commit 9becec2ffa53bafb1f791803c9527378af95dcd6
Author: Matt Domsch <matt(a)domsch.com>
Date: Sat Jul 27 12:22:04 2013 -0500
crawler_perhost: limit try_later_delay to 4 seconds
>---------------------------------------------------------------
server/crawler_perhost | 2 +-
1 files changed, 1 insertions(+), 1 deletions(-)
diff --git a/server/crawler_perhost b/server/crawler_perhost
index 2f72a60..dfc9292 100755
--- a/server/crawler_perhost
+++ b/server/crawler_perhost
@@ -774,7 +774,7 @@ def per_host(host, options):
logger.warning("Host %s (id=%d) does not have HTTP Keep-Alives enabled." % (host.name, host.id))
time.sleep(try_later_delay)
- if try_later_delay < 64:
+ if try_later_delay < 8:
try_later_delay = try_later_delay << 1
except:
10 years, 9 months
[mirrormanager] master: crawler_perhost: report use of HTTP keepalives (fa75018)
by Matt Domsch
Repository : http://git.fedorahosted.org/cgit/
On branch : master
>---------------------------------------------------------------
commit fa750183c725d952cfa19756ea78b10c4ed5cb65
Author: Matt Domsch <matt(a)domsch.com>
Date: Sat Jul 27 12:19:50 2013 -0500
crawler_perhost: report use of HTTP keepalives
>---------------------------------------------------------------
server/crawler_perhost | 13 ++++++++++---
1 files changed, 10 insertions(+), 3 deletions(-)
diff --git a/server/crawler_perhost b/server/crawler_perhost
index 3b2c9e6..2f72a60 100755
--- a/server/crawler_perhost
+++ b/server/crawler_perhost
@@ -93,6 +93,7 @@ class hostState:
self.http_debuglevel = http_debuglevel
self.ftp_debuglevel = ftp_debuglevel
self.ftp_dir_results = None
+ self.keepalives_available = False
def get_connection(self, url):
@@ -277,7 +278,10 @@ def check_head(hoststate, url, filedata, recursion, readable, retry=0):
raise HTTPUnknown()
conn.end_request()
- if not r.keepalive_ok():
+ keepalive_ok = r.keepalive_ok()
+ if keepalive_ok:
+ hoststate.keepalives_available = True
+ if not keepalive_ok:
hoststate.close_http(url)
content_length = r.getheader('Content-Length')
@@ -751,7 +755,6 @@ def per_host(host, options):
if has_all_files is None:
has_all_files = try_perfile(d, hoststate, url)
-
if has_all_files == False:
logger.warning("Not up2date: %s" % (d.name))
host_category_dirs[(hc, d)] = False
@@ -767,6 +770,9 @@ def per_host(host, options):
except TryLater:
msg = "Server load exceeded - try later (%s seconds)" % try_later_delay
logger.warning(msg)
+ if categoryUrl.startswith('http') and not hoststate.keepalives_available:
+ logger.warning("Host %s (id=%d) does not have HTTP Keep-Alives enabled." % (host.name, host.id))
+
time.sleep(try_later_delay)
if try_later_delay < 64:
try_later_delay = try_later_delay << 1
@@ -775,7 +781,8 @@ def per_host(host, options):
mark_not_up2date(sys.exc_info(), host, "Unknown exception raised. This is a bug in the MM crawler.")
rc = 1
break
-
+ if categoryUrl.startswith('http') and not hoststate.keepalives_available:
+ logger.warning("Host %s (id=%d) does not have HTTP Keep-Alives enabled." % (host.name, host.id))
hoststate.close()
if options.timeout_minutes > 0:
10 years, 9 months
[mirrormanager] master: remove EXISTS from TODO (8c051b5)
by Matt Domsch
Repository : http://git.fedorahosted.org/cgit/
On branch : master
>---------------------------------------------------------------
commit 8c051b5afffd29f8b56bc15423930e690cf88822
Author: Matt Domsch <matt(a)domsch.com>
Date: Thu Jul 25 23:47:58 2013 -0500
remove EXISTS from TODO
>---------------------------------------------------------------
TODO | 5 -----
1 files changed, 0 insertions(+), 5 deletions(-)
diff --git a/TODO b/TODO
index cbe8f69..201e14c 100644
--- a/TODO
+++ b/TODO
@@ -14,11 +14,6 @@ Short Term:
* move Fedora-specific apps into a server/fedora directory, add /usr/share/mirrormanager/server to sys.path.
* crawlers failing after a 90min timeout - why?
* consider making http redirects only redirect to http servers, not FTP (fixes bfo/ipxe)
-* publiclist_hosts() query should use an EXISTS clause to reduce query
- time considerably. The long list of directories and
- HostCategoryURLs currently being generated by the joins is crazy, an
- EXISTS clause should be able to reduce that to a single row.
-
Long Term:
10 years, 9 months
[mirrormanager] master: generate-publiclist: handle loop invariants better (c2f2554)
by Matt Domsch
Repository : http://git.fedorahosted.org/cgit/
On branch : master
>---------------------------------------------------------------
commit c2f2554c9483cd565e4f4cdc396ecb33d06056f6
Author: Matt Domsch <matt(a)domsch.com>
Date: Thu Jul 25 23:21:52 2013 -0500
generate-publiclist: handle loop invariants better
We were making queries for otherwise constant values inside nested
loops. Not a huge time killer, but no sense in doing so.
>---------------------------------------------------------------
server/generate-publiclist | 80 ++++++++++++++++++++++++--------------------
1 files changed, 44 insertions(+), 36 deletions(-)
diff --git a/server/generate-publiclist b/server/generate-publiclist
index ead4f05..23c5a8c 100755
--- a/server/generate-publiclist
+++ b/server/generate-publiclist
@@ -115,12 +115,13 @@ class ProductVersionArchMatrix:
return result
def fill(self):
+ arches = publiclist_arches()
self.data = {}
- for p in Product.select():
- for v in p.versions:
+ for p in list(Product.select()):
+ for v in list(p.versions):
if not v.display:
continue
- for a in publiclist_arches():
+ for a in arches:
hosts = publiclist_hosts(productname=p.name, vername=v.name, archname=a.name)
self.set_pva(p.name, v.name, a.name, hosts)
msg = "filled %s/%s/%s with %s hosts" % (p.name, v.name, a.name, len(hosts))
@@ -142,27 +143,45 @@ def generate_all():
engine = engines.get('kid', None)
pvaMatrix = ProductVersionArchMatrix()
pvaMatrix.fill()
-
- generate_one_html(engine, pvaMatrix)
- for p in Product.select():
- generate_one_html(engine, pvaMatrix, productname=p.name)
+ products = list(Product.select().orderBy('name'))
+ arches = publiclist_arches()
+ email = config.get('mirrormanager.report_problems_to_email', 'webmaster')
+ utcnow = dt.datetime.utcnow()
+ projectname = config.get('mirrormanager.projectname','Fedora')
+ template = engine.load_template('mirrormanager.templates.publiclist')
+ generate_one_html(engine, template, pvaMatrix, projectname, products,
+ arches, email, utcnow)
+ for p in products:
+ generate_one_html(engine, template, pvaMatrix, projectname, products,
+ arches, email, utcnow, productname=p.name)
for v in p.versions:
if not v.display:
continue
- generate_one_html(engine, pvaMatrix, productname=p.name, vername=v.name)
+ generate_one_html(engine, template, pvaMatrix, projectname, products,
+ arches, email, utcnow, productname=p.name, vername=v.name)
+
+
+ for a in arches:
+ generate_one_html(engine, template, pvaMatrix, projectname, products,
+ arches, email, utcnow,
+ productname=p.name, vername=v.name, archname=a.name)
- for a in publiclist_arches():
- generate_one_html(engine, pvaMatrix, productname=p.name, vername=v.name, archname=a.name)
+def generate_one_html(engine, template, pvaMatrix, projectname, products, arches,
+ email, utcnow, productname=None, vername=None, archname=None):
+ try:
+ hosts = pvaMatrix.get_pva(productname, vername, archname)
+ except KeyError:
+ hosts = publiclist_hosts(productname=productname, vername=vername, archname=archname)
-def generate_one_html(engine, pvaMatrix, productname=None, vername=None, archname=None):
+ if len(hosts) == 0:
+ return
pva = make_pva(productname, vername, archname)
- msg = "making %s" % pva
+ title = "%s %s Public Active Mirrors" % (projectname, pva)
+ msg = "making %s, %d hosts" % (pva, len(hosts))
logger.info(msg)
- title = "%s %s Public Active Mirrors" % (config.get('mirrormanager.projectname','Fedora'),
- pva)
output_dir = os.path.join(options.output, pva)
try:
@@ -171,40 +190,29 @@ def generate_one_html(engine, pvaMatrix, productname=None, vername=None, archnam
if e.errno != errno.EEXIST:
raise
- try:
- hosts = pvaMatrix.get_pva(productname, vername, archname)
- logger.debug('pvaMatrix(%s, %s, %s) had %d hosts' % (productname, vername, archname, len(hosts)))
- except KeyError:
- hosts = publiclist_hosts(productname=productname, vername=vername, archname=archname)
- logger.debug('pvaMatrix(%s, %s, %s) did not have hosts, query required.' % (productname, vername, archname))
-
- if len(hosts) == 0:
- return
-
bandwidth = 0
for h in hosts:
bandwidth += h.bandwidth_int
bandwidth = bandwidth / 1024 # convert to Gb
- template_data = dict(page_title=title, hosts=hosts, numhosts=len(hosts), bandwidth=bandwidth,
- pvaMatrix=pvaMatrix,
- pva=pva,
- products=list(Product.select().orderBy('name')),
- arches=publiclist_arches(),
- report_problems_to_email = config.get('mirrormanager.report_problems_to_email', 'webmaster'),
- utcnow = dt.datetime.utcnow())
+ template_data = dict(page_title = title,
+ hosts = hosts, numhosts = len(hosts),
+ bandwidth = bandwidth,
+ pvaMatrix = pvaMatrix,
+ pva = pva,
+ products = products,
+ arches = arches,
+ report_problems_to_email = email,
+ utcnow = utcnow)
- t=engine.load_template('mirrormanager.templates.publiclist')
- out_html = _process_output(template_data, template=t, format='html', engine=engine)
+ out_html = _process_output(template_data, template = template,
+ format = 'html', engine = engine)
fname = "index.html"
f = open(os.path.join(output_dir, fname), "w")
f.write(out_html)
f.close()
-
-
-
options=None
def main():
10 years, 9 months
[mirrormanager] master: model: more publiclist_hosts SQL enhancements (738376c)
by Matt Domsch
Repository : http://git.fedorahosted.org/cgit/
On branch : master
>---------------------------------------------------------------
commit 738376c370b7956a5b4fbac13d7d0a6d30b1f63e
Author: Matt Domsch <matt(a)domsch.com>
Date: Thu Jul 25 22:54:38 2013 -0500
model: more publiclist_hosts SQL enhancements
More speedups from subquery optimization. This brings the total run
to just over 10 (down from 18+) minutes, with results remaining
constant.
>---------------------------------------------------------------
server/mirrormanager/model.py | 31 ++++++++++++-------------------
1 files changed, 12 insertions(+), 19 deletions(-)
diff --git a/server/mirrormanager/model.py b/server/mirrormanager/model.py
index ff86a12..2fcda5e 100644
--- a/server/mirrormanager/model.py
+++ b/server/mirrormanager/model.py
@@ -520,55 +520,48 @@ def _publiclist_hosts(product=None, re=None):
sql1_filter = ''
sql2_filter = ''
- sql_common_select = "SELECT DISTINCT host.id, host.country, host.name AS host_name, host.bandwidth_int, host.comment, host.internet2, "
- sql_common_select += "site.org_url, site.name AS site_name, category.name AS category_name, host_category_url.url "
- sql_common_from = "FROM category, host_category, host, site, host_category_url "
+ sql_common = "SELECT host.id, host.country, host.name AS host_name, host.bandwidth_int, host.comment, host.internet2, "
+ sql_common += "site.org_url, site.name AS site_name, category.name AS category_name, host_category_url.url "
+ sql_common += "FROM category, host_category, host, site, host_category_url "
# join conditions
- sql_common = "WHERE "
+ sql_common += "WHERE "
sql_common += "host_category.category_id = category.id AND "
sql_common += "host_category.host_id = host.id AND "
sql_common += "host_category_url.host_category_id = host_category.id AND "
if product is not None:
sql_common += "category.product_id = %s AND " % product.id
sql_common += "host.site_id = site.id "
- # select conditions # up2date, active, not private
+ # select conditions
+ # up2date, active, not private
sql_common += 'AND host.user_active AND site.user_active '
sql_common += 'AND host.admin_active AND site.admin_active '
sql_common += 'AND NOT host.private '
sql_common += 'AND NOT site.private '
sql_common += 'AND NOT host_category_url.private '
sql_common += 'AND category.publiclist '
- sql_common += 'AND EXISTS '
if re is not None:
sql1_filter = "AND " + _rlike(re, 'host_category_dir.path')
sql2_filter = "AND " + _rlike(re, 'directory.name')
sql1_subselect = '('
- sql1_subselect += 'SELECT 1 from host_category, host_category_dir '
- sql1_subselect += 'WHERE host_category.category_id = category.id '
- sql1_subselect += 'AND host_category.host_id = host.id '
- sql1_subselect += 'AND host_category_dir.host_category_id = host_category.id '
+ sql1_subselect += 'SELECT 1 from host_category_dir '
+ sql1_subselect += 'WHERE host_category_dir.host_category_id = host_category.id '
sql1_subselect += 'AND host_category_dir.up2date '
sql1_subselect += sql1_filter
sql1_subselect += 'LIMIT 1 '
sql1_subselect += ') '
sql2_subselect = '('
- sql2_subselect += 'SELECT 1 from host_category, category_directory, directory '
- sql2_subselect += 'WHERE host_category.category_id = category.id '
- sql2_subselect += 'AND host_category.host_id = host.id '
- sql2_subselect += 'AND category_directory.directory_id = directory.id '
- sql2_subselect += 'AND category_directory.category_id = category.id '
+ sql2_subselect += 'SELECT 1 from category_directory, directory '
+ sql2_subselect += 'WHERE category_directory.directory_id = directory.id '
+ sql2_subselect += 'AND category_directory.category_id = category.id '
sql2_subselect += 'AND host_category.always_up2date '
sql2_subselect += sql2_filter
sql2_subselect += 'LIMIT 1 '
sql2_subselect += ') '
- sql1 = sql_common_select + sql_common_from + sql_common + sql1_subselect
- sql2 = sql_common_select + sql_common_from + sql_common + sql2_subselect
-
- sql = "SELECT * FROM ( ( %s ) UNION ( %s ) ) AS subquery" % (sql1, sql2)
+ sql = sql_common + 'AND ( EXISTS ' + sql1_subselect + 'OR EXISTS ' + sql2_subselect + ') '
result = Directory._connection.queryAll(sql)
return result
10 years, 9 months
[mirrormanager] master: don't generate publiclist pages for architectures with no hosts (daa3a16)
by Matt Domsch
Repository : http://git.fedorahosted.org/cgit/
On branch : master
>---------------------------------------------------------------
commit daa3a16286fba1c9566032ad14aaf094ad7185ed
Author: Matt Domsch <matt(a)domsch.com>
Date: Thu Jul 25 16:49:46 2013 -0500
don't generate publiclist pages for architectures with no hosts
>---------------------------------------------------------------
server/generate-publiclist | 3 +++
1 files changed, 3 insertions(+), 0 deletions(-)
diff --git a/server/generate-publiclist b/server/generate-publiclist
index 6ca4203..ead4f05 100755
--- a/server/generate-publiclist
+++ b/server/generate-publiclist
@@ -178,6 +178,9 @@ def generate_one_html(engine, pvaMatrix, productname=None, vername=None, archnam
hosts = publiclist_hosts(productname=productname, vername=vername, archname=archname)
logger.debug('pvaMatrix(%s, %s, %s) did not have hosts, query required.' % (productname, vername, archname))
+ if len(hosts) == 0:
+ return
+
bandwidth = 0
for h in hosts:
bandwidth += h.bandwidth_int
10 years, 9 months
[mirrormanager] master: remove test for bandwidth_int not being an int, it must be now. (b980ed5)
by Matt Domsch
Repository : http://git.fedorahosted.org/cgit/
On branch : master
>---------------------------------------------------------------
commit b980ed5f9370b8e38b5d377e08415b4228db9816
Author: Matt Domsch <matt(a)domsch.com>
Date: Thu Jul 25 16:46:27 2013 -0500
remove test for bandwidth_int not being an int, it must be now.
>---------------------------------------------------------------
server/generate-publiclist | 7 +------
1 files changed, 1 insertions(+), 6 deletions(-)
diff --git a/server/generate-publiclist b/server/generate-publiclist
index d9454b9..6ca4203 100755
--- a/server/generate-publiclist
+++ b/server/generate-publiclist
@@ -178,14 +178,9 @@ def generate_one_html(engine, pvaMatrix, productname=None, vername=None, archnam
hosts = publiclist_hosts(productname=productname, vername=vername, archname=archname)
logger.debug('pvaMatrix(%s, %s, %s) did not have hosts, query required.' % (productname, vername, archname))
-
bandwidth = 0
for h in hosts:
- if not isinstance(h.bandwidth_int, int):
- msg = '%s bandwidth_int was not an int: %s' % (h, h.bandwidth_int)
- logger.warning(msg)
- h.bandwidth_int = 1
- bandwidth = bandwidth + h.bandwidth_int
+ bandwidth += h.bandwidth_int
bandwidth = bandwidth / 1024 # convert to Gb
template_data = dict(page_title=title, hosts=hosts, numhosts=len(hosts), bandwidth=bandwidth,
10 years, 9 months
[mirrormanager] master: model: speed up publiclist_hosts() greatly (cce3a73)
by Matt Domsch
Repository : http://git.fedorahosted.org/cgit/
On branch : master
>---------------------------------------------------------------
commit cce3a7345719b0b0f719936a8d055898f230983f
Author: Matt Domsch <matt(a)domsch.com>
Date: Thu Jul 25 16:28:08 2013 -0500
model: speed up publiclist_hosts() greatly
This query could take minutes to execute, as it was essentially
joining the entire database together across all the critical
relations. It was doing a naked join on both host_category_dir (so
the results had all host_category_dirs tested, only to throw away all
that data), as well as a naked join on host_category +
category_directory + directory.
I realized we could use a subquery, LIMIT 1, with EXISTS to handle
those naked joins cleanly. That's what this does.
Queries that used to take minutes now take ~10 seconds, and queries
thereafter on the same or similar queries now take 0.1s, much
improved.
This will speed up generate-publiclist a bit, and reduce load on the
database.
>---------------------------------------------------------------
server/mirrormanager/model.py | 67 ++++++++++++++++++++++++----------------
1 files changed, 40 insertions(+), 27 deletions(-)
diff --git a/server/mirrormanager/model.py b/server/mirrormanager/model.py
index 54bd2f9..ff86a12 100644
--- a/server/mirrormanager/model.py
+++ b/server/mirrormanager/model.py
@@ -507,12 +507,22 @@ def _publiclist_sql_to_list(sqlresult, valid_categories):
return l
def _publiclist_hosts(product=None, re=None):
+ class MY_RLIKE(RLIKE):
+ def __sqlrepr__(self, db):
+ return "(%s %s (%s))" % (
+ self.string, self._get_op(db), sqlrepr(self.expr, db))
+
+ def _rlike(pattern, string):
+ # there's probably a beter way to get this, but this works...
+ _dburi = config.get('sqlobject.dburi', 'sqlite://')
+ dbtype = _dburi.strip('notrans_').split(':')[0]
+ return MY_RLIKE(pattern, string).__sqlrepr__(dbtype) + " "
sql1_filter = ''
sql2_filter = ''
- sql_common_select = "SELECT DISTINCT host.id, host.country, host.name AS host_name, host.bandwidth_int, host.comment, host.internet2, site.org_url, site.name AS site_name, category.name AS category_name, host_category_url.url "
- sql1_from = "FROM category, host_category, host, site, host_category_url, host_category_dir "
- sql2_from = "FROM category, host_category, host, site, host_category_url, directory, category_directory "
+ sql_common_select = "SELECT DISTINCT host.id, host.country, host.name AS host_name, host.bandwidth_int, host.comment, host.internet2, "
+ sql_common_select += "site.org_url, site.name AS site_name, category.name AS category_name, host_category_url.url "
+ sql_common_from = "FROM category, host_category, host, site, host_category_url "
# join conditions
sql_common = "WHERE "
sql_common += "host_category.category_id = category.id AND "
@@ -521,41 +531,44 @@ def _publiclist_hosts(product=None, re=None):
if product is not None:
sql_common += "category.product_id = %s AND " % product.id
sql_common += "host.site_id = site.id "
- # select conditions
- # up2date, active, not private
+ # select conditions # up2date, active, not private
sql_common += 'AND host.user_active AND site.user_active '
sql_common += 'AND host.admin_active AND site.admin_active '
sql_common += 'AND NOT host.private '
sql_common += 'AND NOT site.private '
sql_common += 'AND NOT host_category_url.private '
sql_common += 'AND category.publiclist '
-
- sql1_join = 'AND host_category_dir.host_category_id = host_category.id '
- sql2_join = 'AND category_directory.directory_id = directory.id '
- sql2_join += 'AND category_directory.category_id = category.id '
-
- class MY_RLIKE(RLIKE):
- def __sqlrepr__(self, db):
- return "(%s %s (%s))" % (
- self.string, self._get_op(db), sqlrepr(self.expr, db))
-
- def _rlike(pattern, string):
- # there's probably a beter way to get this, but this works...
- _dburi = config.get('sqlobject.dburi', 'sqlite://')
- dbtype = _dburi.strip('notrans_').split(':')[0]
- return MY_RLIKE(pattern, string).__sqlrepr__(dbtype) + " "
+ sql_common += 'AND EXISTS '
if re is not None:
sql1_filter = "AND " + _rlike(re, 'host_category_dir.path')
sql2_filter = "AND " + _rlike(re, 'directory.name')
- sql1_up2date = 'AND host_category_dir.up2date '
- sql2_up2date = 'AND host_category.always_up2date '
-
- sql1 = sql_common_select + sql1_from + sql_common + sql1_filter + sql1_join + sql1_up2date
- sql2 = sql_common_select + sql2_from + sql_common + sql2_filter + sql2_join + sql2_up2date
-
- sql = "SELECT * FROM ( %s UNION %s ) AS subquery" % (sql1, sql2)
+ sql1_subselect = '('
+ sql1_subselect += 'SELECT 1 from host_category, host_category_dir '
+ sql1_subselect += 'WHERE host_category.category_id = category.id '
+ sql1_subselect += 'AND host_category.host_id = host.id '
+ sql1_subselect += 'AND host_category_dir.host_category_id = host_category.id '
+ sql1_subselect += 'AND host_category_dir.up2date '
+ sql1_subselect += sql1_filter
+ sql1_subselect += 'LIMIT 1 '
+ sql1_subselect += ') '
+
+ sql2_subselect = '('
+ sql2_subselect += 'SELECT 1 from host_category, category_directory, directory '
+ sql2_subselect += 'WHERE host_category.category_id = category.id '
+ sql2_subselect += 'AND host_category.host_id = host.id '
+ sql2_subselect += 'AND category_directory.directory_id = directory.id '
+ sql2_subselect += 'AND category_directory.category_id = category.id '
+ sql2_subselect += 'AND host_category.always_up2date '
+ sql2_subselect += sql2_filter
+ sql2_subselect += 'LIMIT 1 '
+ sql2_subselect += ') '
+
+ sql1 = sql_common_select + sql_common_from + sql_common + sql1_subselect
+ sql2 = sql_common_select + sql_common_from + sql_common + sql2_subselect
+
+ sql = "SELECT * FROM ( ( %s ) UNION ( %s ) ) AS subquery" % (sql1, sql2)
result = Directory._connection.queryAll(sql)
return result
10 years, 9 months