ports/158941: [PATCH] ports-mgmt/portscout: add MySQL backend and fix restrict maintainers bug
Martin Matuska
mm at FreeBSD.org
Fri Jul 15 10:40:12 UTC 2011
>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 at 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 at 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 at inerd.com>
++# Copyright (C) 2011, Martin Matuska <mm at 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 at inerd.com>
++ * Copyright (C) 2011, Martin Matuska <mm at 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:
More information about the freebsd-ports-bugs
mailing list