Date: Fri, 15 Jul 2011 12:38:12 +0200 (CEST) From: Martin Matuska <mm@FreeBSD.org> To: FreeBSD-gnats-submit@FreeBSD.org Cc: shaun@FreeBSD.org Subject: ports/158941: [PATCH] ports-mgmt/portscout: add MySQL backend and fix restrict maintainers bug Message-ID: <201107151038.p6FAcCa2080447@neo.vx.sk> Resent-Message-ID: <201107151040.p6FAeBLL098863@freefall.freebsd.org>
next in thread | raw e-mail | index | archive | help
>Number: 158941 >Category: ports >Synopsis: [PATCH] ports-mgmt/portscout: add MySQL backend and fix restrict maintainers bug >Confidential: no >Severity: non-critical >Priority: low >Responsible: freebsd-ports-bugs >State: open >Quarter: >Keywords: >Date-Required: >Class: change-request >Submitter-Id: current-users >Arrival-Date: Fri Jul 15 10:40:11 UTC 2011 >Closed-Date: >Last-Modified: >Originator: Martin Matuska >Release: FreeBSD 8.2-RELEASE-p2 amd64 >Organization: >Environment: System: FreeBSD neo.vx.sk 8.2-RELEASE-p2 FreeBSD 8.2-RELEASE-p2 #1 r223692M: Thu Jun 30 14:50:37 CEST >Description: - add support for MySQL backend - restrict maintainers are compared lowercase vs original, we should compare lowercase vs lowercase - the percentage in MySQL backend is limited to 2 decimals (can be changed - DECIMAL(10,3) = 3 decimals with precision 10, etc.) If the fields `key` and `ignore` could be renamed, mysql.pm would be much smaller (we only need to change the two big functions already changed in + random() -> rand()) Maybe putting this into portscout 0.8.2? Added file(s): - files/patch-Portscout-DataSrc-Ports.pm - files/patch-mysql Port maintainer (shaun@FreeBSD.org) is cc'd. Generated with FreeBSD Port Tools 0.99 >How-To-Repeat: >Fix: --- portscout-0.8.1_1.patch begins here --- Index: Makefile =================================================================== RCS file: /home/pcvs/ports/ports-mgmt/portscout/Makefile,v retrieving revision 1.14 diff -u -r1.14 Makefile --- Makefile 15 May 2011 18:10:52 -0000 1.14 +++ Makefile 15 Jul 2011 10:32:46 -0000 @@ -7,6 +7,7 @@ PORTNAME= portscout PORTVERSION= 0.8.1 +PORTREVISION= 1 CATEGORIES= ports-mgmt MASTER_SITES= http://mirror.inerd.com/FreeBSD/distfiles/${PORTNAME}/ \ http://www.atarininja.org/~wxs/distfiles/ \ @@ -15,7 +16,8 @@ MAINTAINER= shaun@FreeBSD.org COMMENT= A tool to scan for new versions of FreeBSD ports -OPTIONS= SQLITE3 "Use SQLite backend instead of PostgreSQL" off +OPTIONS= SQLITE3 "Use SQLite backend instead of PostgreSQL" off \ + MYSQL "Use MySQL backend instead of PostgreSQL" off NO_BUILD= yes USE_PERL5= yes @@ -37,11 +39,27 @@ .if defined(WITH_SQLITE3) USE_SQLITE= 3 RUN_DEPENDS+= ${SITE_PERL}/${PERL_ARCH}/DBD/SQLite.pm:${PORTSDIR}/databases/p5-DBD-SQLite -.elif !defined(WITHOUT_PGSQL) +.endif + +.if defined(WITH_MYSQL) +USE_MYSQL= yes +.endif + +.if !defined(WITH_MYSQL) && !defined(WITH_SQLITE3) USE_PGSQL= yes RUN_DEPENDS+= ${SITE_PERL}/${PERL_ARCH}/DBD/Pg.pm:${PORTSDIR}/databases/p5-DBD-Pg .endif +.if defined(WITH_MYSQL) && defined(WITH_SQLITE3) +IGNORE= options WITH_MYSQL and WITH_SQLITE3 are exclusive +.endif + +.include <bsd.port.pre.mk> + +.if defined(WITH_MYSQL) +RUN_DEPENDS+= ${SITE_PERL}/${PERL_ARCH}/DBD/mysql.pm:${PORTSDIR}/databases/p5-DBD-mysql${MYSQL_VER:S/323//} +.endif + pre-everything:: .if defined(WITH_SQLITE3) @${ECHO_MSG} "+-------------------------------------------------------------+" @@ -52,10 +70,15 @@ .endif post-patch: -.if defined(WITH_SQLITE3) +.if defined(WITH_SQLITE3) || defined(WITH_MYSQL) @${REINPLACE_CMD} 's/^\([^#]*DBI:Pg.*\)$$/#\1/g' ${WRKSRC}/portscout.conf +.endif +.if defined(WITH_SQLITE3) @${REINPLACE_CMD} 's/^#\(.*DBI:SQLite.*\)$$/\1/g' ${WRKSRC}/portscout.conf .endif +.if defined(WITH_MYSQL) + @${REINPLACE_CMD} 's/^#\(.*DBI:mysql.*\)$$/\1/g' ${WRKSRC}/portscout.conf +.endif @${REINPLACE_CMD} -e "s#^\(templates .*\)/etc#\1/share#" \ -e "s#^prefix\( *= *\).*#prefix\1${PREFIX}#" \ ${WRKSRC}/portscout.conf @@ -109,4 +132,4 @@ # ${PERL} ${WRKSRC}/10-postgresql.t .endif -.include <bsd.port.mk> +.include <bsd.port.post.mk> Index: pkg-plist =================================================================== RCS file: /home/pcvs/ports/ports-mgmt/portscout/pkg-plist,v retrieving revision 1.6 diff -u -r1.6 pkg-plist --- pkg-plist 15 May 2011 18:10:52 -0000 1.6 +++ pkg-plist 15 Jul 2011 10:32:46 -0000 @@ -7,6 +7,7 @@ %%DATADIR%%/templates/reminder.mail %%DATADIR%%/templates/restricted-ports.html %%DATADIR%%/sql/pgsql_init.sql +%%DATADIR%%/sql/mysql_init.sql %%DATADIR%%/sql/pgsql_destroy.sql %%DATADIR%%/sql/pgsql_upgrade_0.7.1_to_0.7.2.sql %%DATADIR%%/sql/pgsql_upgrade_0.7.3_to_0.7.4.sql @@ -15,6 +16,7 @@ %%DATADIR%%/sql/sqlite_init.sql %%DATADIR%%/sql/sqlite_destroy.sql %%DATADIR%%/sql/sqlite_upgrade_0.8_to_0.8.1.sql +%%SITE_PERL%%/Portscout/SQL/mysql.pm %%SITE_PERL%%/Portscout/SQL/SQLite.pm %%SITE_PERL%%/Portscout/SQL/Pg.pm %%SITE_PERL%%/Portscout/SiteHandler/SourceForge.pm Index: files/patch-Portscout-DataSrc-Ports.pm =================================================================== RCS file: files/patch-Portscout-DataSrc-Ports.pm diff -N files/patch-Portscout-DataSrc-Ports.pm --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ files/patch-Portscout-DataSrc-Ports.pm 15 Jul 2011 10:32:46 -0000 @@ -0,0 +1,12 @@ +diff -Naur Portscout/DataSrc/Ports.pm portscout-0.8.1/Portscout/DataSrc/Ports.pm +--- Portscout/DataSrc/Ports.pm 2011-07-15 11:38:44.886566509 +0200 ++++ Portscout/DataSrc/Ports.pm 2011-07-15 11:42:15.688856465 +0200 +@@ -243,7 +243,7 @@ + my (@fields, $maintainer, $port); + + @fields = split /\|/; +- $maintainer = $fields[5]; ++ $maintainer = lc($fields[5]); + $port = $fields[1]; + $port =~ s/^(?:.*\/)?([^\/]+)\/([^\/]+)$/$1\/$2/; + Index: files/patch-mysql =================================================================== RCS file: files/patch-mysql diff -N files/patch-mysql --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ files/patch-mysql 15 Jul 2011 10:32:46 -0000 @@ -0,0 +1,353 @@ +--- portscout.conf.orig 2011-07-15 11:44:55.469468526 +0200 ++++ portscout.conf 2011-07-15 11:45:17.284071220 +0200 +@@ -170,6 +170,7 @@ + + db connstr = DBI:Pg:dbname=%(db_name) + #db connstr = DBI:Pg:dbname=%(db_name);host=%(db_host);port=%(db_port) ++#db connstr = DBI:mysql:dbname=%(db_name) + #db connstr = DBI:SQLite:dbname=/var/db/portscout.db + + +--- Portscout/SQL/mysql.pm.orig 2011-07-15 12:23:50.038255621 +0200 ++++ Portscout/SQL/mysql.pm 2011-07-15 12:24:20.849694505 +0200 +@@ -0,0 +1,199 @@ ++#------------------------------------------------------------------------------ ++# Copyright (C) 2010, Shaun Amott <shaun@inerd.com> ++# Copyright (C) 2011, Martin Matuska <mm@FreeBSD.org> ++# All rights reserved. ++# ++# Redistribution and use in source and binary forms, with or without ++# modification, are permitted provided that the following conditions ++# are met: ++# 1. Redistributions of source code must retain the above copyright ++# notice, this list of conditions and the following disclaimer. ++# 2. Redistributions in binary form must reproduce the above copyright ++# notice, this list of conditions and the following disclaimer in the ++# documentation and/or other materials provided with the distribution. ++# ++# THIS SOFTWARE IS PROVIDED BY THE AUTHOR AND CONTRIBUTORS ``AS IS'' AND ++# ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE ++# IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ++# ARE DISCLAIMED. IN NO EVENT SHALL THE AUTHOR OR CONTRIBUTORS BE LIABLE ++# FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL ++# DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS ++# OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) ++# HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT ++# LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY ++# OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF ++# SUCH DAMAGE. ++# ++# $Id$ ++#------------------------------------------------------------------------------ ++ ++package Portscout::SQL::mysql; ++ ++require Exporter; ++ ++use strict; ++ ++require 5.006; ++ ++our @ISA = qw(Exporter); ++our @EXPORT_OK = qw(RegisterHacks); ++ ++ ++#------------------------------------------------------------------------------ ++# Globals ++#------------------------------------------------------------------------------ ++ ++my $sql = \%Portscout::SQL::sql; ++ ++ ++#------------------------------------------------------------------------------ ++# SQL that is different for this database engine. ++#------------------------------------------------------------------------------ ++ ++# CheckPortsDB ++ ++$$sql{sitedata_setrobots} = ++ q(UPDATE sitedata ++ SET robots = ?, ++ robots_paths = ?, ++ robots_nextcheck = TIMESTAMPADD(WEEK,2,CURRENT_TIMESTAMP) ++ WHERE host = ?); ++ ++# GenerateHTML ++ ++$$sql{portdata_genresults_init} = ++ q(DELETE FROM results); ++ ++$$sql{portdata_genresults} = ++ q(INSERT ++ INTO results ++ ++ SELECT maintainer, ++ total, ++ COALESCE(withnewdistfile,0) AS withnewdistfile, ++ CAST(100*(COALESCE(withnewdistfile,0)*1.0/total*1.0) AS DECIMAL(10,2)) ++ AS percentage ++ FROM ( ++ SELECT lower(maintainer) AS maintainer, ++ COUNT(maintainer) AS total, ++ COUNT(newver != ver) AS withnewdistfile ++ FROM portdata ++ WHERE moved != true ++ GROUP BY lower(maintainer) ++ ) ++ AS pd1 ++ ); ++ ++$$sql{portscout_getstat} = ++ q(SELECT val ++ FROM stats ++ WHERE `key` = ? ++ LIMIT 1); ++ ++$$sql{portscout_setstat} = ++ q(UPDATE stats ++ SET val = ? ++ WHERE `key` = ?); ++ ++$$sql{portconfig_update} = ++ q(UPDATE portdata ++ SET indexsite = ?, limitver = ?, limiteven = ?, ++ skipbeta = ?, skipversions = ?, limitwhich = ?, ++ `ignore` = ? ++ WHERE name = ? ++ AND cat = ? ++ AND moved != true); ++ ++$$sql{portdata_select} = ++ q(SELECT * ++ FROM portdata ++ WHERE ( (masterport_id = 0 OR masterport_id is NULL) OR (enslaved != true) ) ++ AND ( systemid = (SELECT id ++ FROM systemdata ++ WHERE host = ? ++ LIMIT 1) ++ OR systemid is NULL ) ++ AND moved != true ++ AND `ignore` != true ++ ORDER BY rand()); ++ ++$$sql{sitedata_select} = ++ q(SELECT host, robots, robots_paths, liecount, ++ (CURRENT_TIMESTAMP >= robots_nextcheck) AS robots_outofdate, ++ abs(successes + (5*failures)) AS _w ++ FROM sitedata ++ WHERE position(host in ?) > 0 ++ AND `ignore` is not true ++ ORDER BY _w ASC); ++ ++$$sql{portdata_findnewnew} = ++ q(SELECT name,cat,ver,newver ++ FROM portdata ++ WHERE lower(maintainer) = lower(?) ++ AND newver != ver ++ AND newver is not NULL ++ AND moved != true ++ AND `ignore` != true ++ AND (( mailed != ver AND mailed != newver ) ++ OR mailed is NULL ) ++ ORDER BY cat,name ASC); ++ ++_transformsql(); ++ ++ ++#------------------------------------------------------------------------------ ++# Func: new() ++# Desc: Constructor. ++# ++# Args: n/a ++# ++# Retn: $self ++#------------------------------------------------------------------------------ ++ ++sub new ++{ ++ my $self = {}; ++ my $class = shift; ++ ++ bless ($self, $class); ++ return $self; ++} ++ ++ ++#------------------------------------------------------------------------------ ++# Func: RegisterHacks() ++# Desc: Implement any missing database functions. This minimises the number of ++# different versions of queries we have to maintain. Needs to be called ++# after each new database connection. ++# ++# Args: \$dbh - Database handle, already connected. ++# ++# Retn: n/a ++#------------------------------------------------------------------------------ ++ ++sub RegisterHacks ++{ ++ my ($self) = shift; ++ ++ return; ++} ++ ++ ++#------------------------------------------------------------------------------ ++# Func: _transformsql() ++# Desc: Transform the SQL queries into a form that works with this database. ++# This is so we can share as many of the SQL queries as possible, rather ++# than duplicating them for minor changes. ++# ++# Args: n/a ++# ++# Retn: n/a ++#------------------------------------------------------------------------------ ++ ++sub _transformsql ++{ ++ return; ++} ++ ++ ++1; +--- sql/mysql_init.sql.orig 2011-07-15 12:23:50.039259659 +0200 ++++ sql/mysql_init.sql 2011-07-15 12:24:44.209236290 +0200 +@@ -0,0 +1,138 @@ ++/* ++ * Create initial portscout SQL tables ++ * ++ * Copyright (C) 2006-2011, Shaun Amott <shaun@inerd.com> ++ * Copyright (C) 2011, Martin Matuska <mm@FreeBSD.org> ++ * All rights reserved. ++ * ++ * $Id$ ++ */ ++ ++DROP TABLE IF EXISTS portdata; ++CREATE TABLE portdata ( ++ id serial UNIQUE, ++ name text, ++ distname text, ++ ver text, ++ newver text, ++ comment text, ++ cat text, ++ distfiles text, ++ sufx text, ++ mastersites text, ++ updated timestamp DEFAULT CURRENT_TIMESTAMP, ++ checked timestamp, ++ discovered timestamp, ++ maintainer text, ++ status text, ++ method integer, ++ newurl text, ++ `ignore` boolean DEFAULT 0, ++ limitver text, ++ masterport text, ++ masterport_id integer DEFAULT 0, ++ enslaved boolean DEFAULT 0, ++ skipbeta boolean DEFAULT 0, ++ limiteven boolean, ++ limitwhich smallint, ++ moved boolean DEFAULT 0, ++ indexsite text, ++ skipversions text, ++ pcfg_static boolean DEFAULT 0, ++ mailed text DEFAULT '', ++ systemid integer ++); ++ ++DROP TABLE IF EXISTS sitedata; ++CREATE TABLE sitedata ( ++ id serial UNIQUE, ++ failures integer DEFAULT 0, ++ successes integer DEFAULT 0, ++ liecount integer DEFAULT 0, ++ robots integer DEFAULT 1, ++ robots_paths text DEFAULT '', ++ robots_nextcheck timestamp, ++ type text, ++ host text, ++ `ignore` boolean DEFAULT 0 ++); ++ ++DROP TABLE IF EXISTS moveddata; ++CREATE TABLE moveddata ( ++ id serial UNIQUE, ++ fromport text, ++ toport text, ++ date text, ++ reason text ++); ++ ++DROP TABLE IF EXISTS maildata; ++CREATE TABLE maildata ( ++ id serial UNIQUE, ++ address text ++); ++ ++DROP TABLE IF EXISTS systemdata; ++CREATE TABLE systemdata ( ++ id serial UNIQUE, ++ host text ++); ++ ++DROP TABLE IF EXISTS allocators; ++CREATE TABLE allocators ( ++ id serial UNIQUE, ++ seq integer NOT NULL, ++ systemid integer REFERENCES systemdata (id), ++ allocator text ++); ++ ++DROP TABLE IF EXISTS portscout; ++CREATE TABLE portscout ( ++ dbver integer ++); ++ ++DROP TABLE IF EXISTS stats; ++CREATE TABLE stats ( ++ `key` text, ++ val text DEFAULT '' ++); ++ ++DROP TABLE IF EXISTS results; ++CREATE TABLE results ( ++ maintainer text, ++ total integer, ++ withnewdistfile integer, ++ percentage float ++); ++ ++INSERT ++ INTO portscout (dbver) ++VALUES (2011040901); ++ ++INSERT ++ INTO stats (`key`) ++VALUES ('buildtime'); ++ ++CREATE ++ INDEX portdata_index_name ++ ON portdata (name(255)); ++ ++CREATE ++ INDEX portdata_index_maintainer ++ ON portdata (maintainer(255)); ++ ++CREATE ++ INDEX portdata_index_masterport_id ++ ON portdata (masterport_id); ++ ++CREATE ++ INDEX portdata_index_discovered ++ ON portdata (discovered); ++ ++CREATE ++ INDEX sitedata_index_host ++ ON sitedata (host(255)); ++ ++CREATE ++ INDEX moveddata_index_fromport ++ ON moveddata (fromport(255)); --- portscout-0.8.1_1.patch ends here --- >Release-Note: >Audit-Trail: >Unformatted:
Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?201107151038.p6FAcCa2080447>