[128996] trunk/base/portmgr/jobs/PortIndex2PGSQL.tcl

2 Dec 2014

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

    

jmr＠macports.org

tags

participants (1)