Revision: 128996 https://trac.macports.org/changeset/128996 Author: jmr@macports.org Date: 2014-12-02 08:29:02 -0800 (Tue, 02 Dec 2014) Log Message: ----------- add PortIndex2PGSQL.tcl, #40579 Added Paths: ----------- trunk/base/portmgr/jobs/PortIndex2PGSQL.tcl Copied: trunk/base/portmgr/jobs/PortIndex2PGSQL.tcl (from rev 128993, trunk/base/portmgr/jobs/PortIndex2MySQL.tcl) =================================================================== --- trunk/base/portmgr/jobs/PortIndex2PGSQL.tcl (rev 0) +++ trunk/base/portmgr/jobs/PortIndex2PGSQL.tcl 2014-12-02 16:29:02 UTC (rev 128996) @@ -0,0 +1,436 @@ +#!/opt/local/bin/port-tclsh +# -*- coding: utf-8; mode: tcl; tab-width: 4; indent-tabs-mode: nil; c-basic-offset: 4 -*- vim:fenc=utf-8:filetype=tcl:et:sw=4:ts=4:sts=4 +# +# PortIndex2PGSQL.tcl +# Kevin Van Vechten | kevin@opendarwin.org +# 3-Oct-2002 +# Juan Manuel Palacios | jmpp@macports.org +# 22-Nov-2007 +# Eric Le Lay | elelay@macports.org +# 24-sept-2013 +# $Id$ +# +# Copyright (c) 2013 Eric Le Lay, The Macports Project +# Copyright (c) 2007 Juan Manuel Palacios, The MacPorts Project. +# Copyright (c) 2003 Apple Inc. +# Copyright (c) 2002 Kevin Van Vechten. +# 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. +# 3. Neither the name of Apple Inc. nor the names of its contributors +# may be used to endorse or promote products derived from this software +# without specific prior written permission. +# +# THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS 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 COPYRIGHT OWNER 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. + + +##### +# The PortIndex2PGSQL script populates a database with key information extracted +# from the Portfiles in the ports tree pointed to by the sources.conf file in a +# MacPorts installation, found by loading its macports1.0 tcl package and initializing +# it with 'mportinit' below. Main use of the resulting database is providing live +# information to the ports.php page, a client tailored to poll it. For this very reason, +# information fed to the database always has to be kept up to date in order to remain +# meaningful, which is accomplished simply by calling the 'mportsync' proc in macports1.0 +# (which updates the ports tree in use) and by installing the script on cron/launchd to be +# run on a timely schedule (not any more frequent than the run of the PortIndexRegen.sh +# script on that creates a new PortIndex file). +# +# Remaining requirement to successfully run this script is performing the necessary +# PGSQL admin tasks on the host box to create the database in the first place and the +# PGSQL user that will be given enough privileges to alter it. Values in the database +# related variables provided below have to be adapted accordingly to match the chosen +# setup. +##### + + +# Configuration variables. Fill in the blank strings. +# +# Email addresses that get failure notifications - e.g. "admin@macosforge.org" +set SPAM_LOVERS "" +# File to read the db password from - e.g. "/var/macports/script_data" +set passwdfile "" +# Database abstraction variables: +# name of the psql executable +set psql_exe "psql" +# path where the psql executable is located (only needed if not in the default PATH) +set psql_exe_path "" +set sqlfile "/tmp/portsdb.sql" +set portsdb_host localhost +set portsdb_name macports +set portsdb_user macports + +# Runtime information log file and recipient. +set runlog "/tmp/portsdb.log" +set runlog_fd [open $runlog w+] +set lockfile "/tmp/portsdb.lock" +set mailprog "/usr/sbin/sendmail" +set DATE [clock format [clock seconds] -format "%A %Y-%m-%d at %T"] + +set SUBJECT "PortIndex2PGSQL run failure on $DATE" +set FROM noreply@macports.org +set HEADERS "To: $SPAM_LOVERS\r\nFrom: $FROM\r\nSubject: $SUBJECT\r\n\r\n" + +# handle command line arguments +set create_tables true +if {[llength $argv]} { + if {[lindex $argv 0] eq "--create-tables"} { + set create_tables true + } +} + +# House keeping on exit. +proc cleanup {args} { + foreach file_to_clean $args { + upvar $file_to_clean up_file_to_clean + upvar ${file_to_clean}_fd up_file_to_clean_fd + close $up_file_to_clean_fd + file delete -force $up_file_to_clean + } +} + +# What to do when terminating execution, depending on the $exit_status condition. +proc terminate {exit_status} { + global runlog runlog_fd + if {$exit_status} { + global subject SPAM_LOVERS mailprog + seek $runlog_fd 0 start + exec -- $mailprog $SPAM_LOVERS <@ $runlog_fd + } + cleanup runlog + exit $exit_status +} + +# macports1.0 UI instantiation to route information/error messages wherever we want. +# This is a custom ui_channels proc because we want to get reported information on +# channels other than the default stdout/stderr that the macports1.0 API provides, +# namely a log file we can later mail to people in charge if need be. +proc ui_channels {priority} { + global runlog_fd + switch $priority { + debug { + if {[macports::ui_isset ports_debug]} { + return $runlog_fd + } else { + return {} + } + } + info { + if {[macports::ui_isset ports_verbose]} { + return $runlog_fd + } else { + return {} + } + } + msg { + if {[macports::ui_isset ports_quiet]} { + return $runlog_fd + } else { + return {} + } + } + error { + return $runlog_fd + } + default { + return {} + } + } +} + +# Procedure to catch the database password from a protected file. +proc getpasswd {passwdfile} { + if {$passwdfile eq ""} { + global lockfile lockfile_fd + ui_error "passwdfile is empty, did you forget to set it?" + cleanup lockfile + terminate 1 + } + if {[catch {open $passwdfile r} passwdfile_fd]} { + global lockfile lockfile_fd + ui_error "${::errorCode}: $passwdfile_fd" + cleanup lockfile + terminate 1 + } + if {[gets $passwdfile_fd passwd] <= 0} { + global lockfile lockfile_fd + close $passwdfile_fd + ui_error "No password found in password file $passwdfile!" + cleanup lockfile + terminate 1 + } + close $passwdfile_fd + return $passwd +} + +# SQL string escaping. +proc sql_escape {str} { + regsub -all -- {'} $str {''} str + return $str +} + +# We first initialize the runlog with proper mail headers +puts $runlog_fd $HEADERS + +# Check if there are any stray sibling jobs before moving on, bail in such case. +if {[file exists $lockfile]} { + puts $runlog_fd "PortIndex2PGSQL lock file found, is another job running?" + terminate 1 +} else { + set lockfile_fd [open $lockfile a] +} + +# Load macports1.0 so that we can use some of its procs and the portinfo array. +if {[catch { package require macports } errstr]} { + puts $runlog_fd "${::errorInfo}" + puts $runlog_fd "Failed to load the macports1.0 Tcl package: $errstr" + cleanup lockfile + terminate 1 +} + +# Initialize macports1.0 and its UI, in order to find the sources.conf file +# (which is what will point us to the PortIndex we're gonna use) and use +# the runtime information. +array set ui_options {ports_verbose yes} +if {[catch {mportinit ui_options} errstr]} { + puts $runlog_fd "${::errorInfo}" + puts $runlog_fd "Failed to initialize MacPorts: $errstr" + cleanup lockfile + terminate 1 +} + + +set portsdb_passwd [getpasswd $passwdfile] +set portsdb_cmd [macports::findBinary $psql_exe $psql_exe_path] + + +# Flat text file to which sql statements are written. +if {[catch {open $sqlfile w+} sqlfile_fd]} { + ui_error "${::errorCode}: $sqlfile_fd" + cleanup lockfile + terminate 1 +} + + +# Call the sync procedure to make sure we always have a fresh ports tree. +if {[catch {mportsync} errstr]} { + ui_error "${::errorInfo}" + ui_error "Failed to update the ports tree, $errstr" + cleanup sqlfile lockfile + terminate 1 +} + +# Load every port in the index through a search that matches everything. +if {[catch {set ports [mportlistall]} errstr]} { + ui_error "${::errorInfo}" + ui_error "port search failed: $errstr" + cleanup sqlfile lockfile + terminate 1 +} + +if {$create_tables} { + # Initial creation of database tables: log, portfiles, categories, maintainers, dependencies, variants and platforms. + # Do we need any other? + puts $sqlfile_fd "DROP TABLE IF EXISTS log;" + puts $sqlfile_fd "CREATE TABLE log (activity VARCHAR(255), activity_time TIMESTAMP);" + + puts $sqlfile_fd "DROP TABLE IF EXISTS portfiles;" + puts $sqlfile_fd "CREATE TABLE portfiles (name VARCHAR(255) PRIMARY KEY NOT NULL, path VARCHAR(255), version VARCHAR(255), description TEXT);" + + puts $sqlfile_fd "DROP TABLE IF EXISTS categories;" + puts $sqlfile_fd "CREATE TABLE categories (portfile VARCHAR(255), category VARCHAR(255), is_primary INTEGER);" + + puts $sqlfile_fd "DROP TABLE IF EXISTS maintainers;" + puts $sqlfile_fd "CREATE TABLE maintainers (portfile VARCHAR(255), maintainer VARCHAR(255), is_primary INTEGER);" + + puts $sqlfile_fd "DROP TABLE IF EXISTS dependencies;" + puts $sqlfile_fd "CREATE TABLE dependencies (portfile VARCHAR(255), library VARCHAR(255));" + + puts $sqlfile_fd "DROP TABLE IF EXISTS variants;" + puts $sqlfile_fd "CREATE TABLE variants (portfile VARCHAR(255), variant VARCHAR(255));" + + puts $sqlfile_fd "DROP TABLE IF EXISTS platforms;" + puts $sqlfile_fd "CREATE TABLE platforms (portfile VARCHAR(255), platform VARCHAR(255));" + + puts $sqlfile_fd "DROP TABLE IF EXISTS licenses;" + puts $sqlfile_fd "CREATE TABLE licenses (portfile VARCHAR(255), license VARCHAR(255));" +} else { + # if we are not creating tables from scratch, remove the old data + puts $sqlfile_fd "TRUNCATE log;" + puts $sqlfile_fd "TRUNCATE portfiles;" + puts $sqlfile_fd "TRUNCATE categories;" + puts $sqlfile_fd "TRUNCATE maintainers;" + puts $sqlfile_fd "TRUNCATE dependencies;" + puts $sqlfile_fd "TRUNCATE variants;" + puts $sqlfile_fd "TRUNCATE platforms;" + puts $sqlfile_fd "TRUNCATE licenses;" +} + +# Iterate over each matching port, extracting its information from the +# portinfo array. +foreach {name array} $ports { + + array unset portinfo + array set portinfo $array + + set portname [sql_escape $portinfo(name)] + if {[info exists portinfo(version)]} { + set portversion [sql_escape $portinfo(version)] + } else { + set portversion "" + } + set portdir [sql_escape $portinfo(portdir)] + if {[info exists portinfo(description)]} { + set description [sql_escape $portinfo(description)] + } else { + set description "" + } + if {[info exists portinfo(categories)]} { + set categories $portinfo(categories) + } else { + set categories "" + } + if {[info exists portinfo(maintainers)]} { + set maintainers $portinfo(maintainers) + } else { + set maintainers "" + } + if {[info exists portinfo(variants)]} { + set variants $portinfo(variants) + } else { + set variants "" + } + if {[info exists portinfo(depends_fetch)]} { + set depends_fetch $portinfo(depends_fetch) + } else { + set depends_fetch "" + } + if {[info exists portinfo(depends_extract)]} { + set depends_extract $portinfo(depends_extract) + } else { + set depends_extract "" + } + if {[info exists portinfo(depends_build)]} { + set depends_build $portinfo(depends_build) + } else { + set depends_build "" + } + if {[info exists portinfo(depends_lib)]} { + set depends_lib $portinfo(depends_lib) + } else { + set depends_lib "" + } + if {[info exists portinfo(depends_run)]} { + set depends_run $portinfo(depends_run) + } else { + set depends_run "" + } + if {[info exists portinfo(platforms)]} { + set platforms $portinfo(platforms) + } else { + set platforms "" + } + if {[info exists portinfo(license)]} { + set licenses $portinfo(license) + } else { + set licenses "" + } + + puts $sqlfile_fd "INSERT INTO portfiles VALUES ('$portname', '$portdir', '$portversion', '$description');" + + set primary 1 + foreach category $categories { + set category [sql_escape $category] + puts $sqlfile_fd "INSERT INTO categories VALUES ('$portname', '$category', $primary);" + set primary 0 + } + + set primary 1 + foreach maintainer $maintainers { + set maintainer [sql_escape $maintainer] + puts $sqlfile_fd "INSERT INTO maintainers VALUES ('$portname', '$maintainer', $primary);" + set primary 0 + } + + foreach fetch_dep $depends_fetch { + set fetch_dep [sql_escape $fetch_dep] + puts $sqlfile_fd "INSERT INTO dependencies VALUES ('$portname', '$fetch_dep');" + } + + foreach extract_dep $depends_extract { + set extract_dep [sql_escape $extract_dep] + puts $sqlfile_fd "INSERT INTO dependencies VALUES ('$portname', '$extract_dep');" + } + + foreach build_dep $depends_build { + set build_dep [sql_escape $build_dep] + puts $sqlfile_fd "INSERT INTO dependencies VALUES ('$portname', '$build_dep');" + } + + foreach lib $depends_lib { + set lib [sql_escape $lib] + puts $sqlfile_fd "INSERT INTO dependencies VALUES ('$portname', '$lib');" + } + + foreach run_dep $depends_run { + set run_dep [sql_escape $run_dep] + puts $sqlfile_fd "INSERT INTO dependencies VALUES ('$portname', '$run_dep');" + } + + foreach variant $variants { + set variant [sql_escape $variant] + puts $sqlfile_fd "INSERT INTO variants VALUES ('$portname', '$variant');" + } + + foreach platform $platforms { + set platform [sql_escape $platform] + puts $sqlfile_fd "INSERT INTO platforms VALUES ('$portname', '$platform');" + } + + foreach license $licenses { + set license [sql_escape $license] + puts $sqlfile_fd "INSERT INTO licenses VALUES ('$portname', '$license');" + } + +} + +# Mark the db regen as done only once we're done processing all ports: +puts $sqlfile_fd "INSERT INTO log VALUES ('update', NOW());" + +# Pipe the contents of the generated sql file to the database command, +# reading from the file descriptor for the raw sql file to assure completeness. +if {[catch {seek $sqlfile_fd 0 start} errstr]} { + ui_error "${::errorCode}: $errstr" + cleanup sqlfile lockfile + terminate 1 +} + +# psql will read the password from the file all by itself... +global env +set env(PGPASSFILE) $passwdfile +if {[catch {exec -- $portsdb_cmd -q --host=$portsdb_host $portsdb_name $portsdb_user <@ $sqlfile_fd} errstr]} { + ui_error "${::errorCode}: $errstr" + cleanup sqlfile lockfile + terminate 1 +} + +# done regenerating the database. Cleanup and exit successfully. +cleanup sqlfile lockfile +terminate 0
participants (1)
-
jmr@macports.org