<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"
"http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head><meta http-equiv="content-type" content="text/html; charset=utf-8" />
<title>[128996] trunk/base/portmgr/jobs/PortIndex2PGSQL.tcl</title>
</head>
<body>
<style type="text/css"><!--
#msg dl.meta { border: 1px #006 solid; background: #369; padding: 6px; color: #fff; }
#msg dl.meta dt { float: left; width: 6em; font-weight: bold; }
#msg dt:after { content:':';}
#msg dl, #msg dt, #msg ul, #msg li, #header, #footer, #logmsg { font-family: verdana,arial,helvetica,sans-serif; font-size: 10pt; }
#msg dl a { font-weight: bold}
#msg dl a:link { color:#fc3; }
#msg dl a:active { color:#ff0; }
#msg dl a:visited { color:#cc6; }
h3 { font-family: verdana,arial,helvetica,sans-serif; font-size: 10pt; font-weight: bold; }
#msg pre { overflow: auto; background: #ffc; border: 1px #fa0 solid; padding: 6px; }
#logmsg { background: #ffc; border: 1px #fa0 solid; padding: 1em 1em 0 1em; }
#logmsg p, #logmsg pre, #logmsg blockquote { margin: 0 0 1em 0; }
#logmsg p, #logmsg li, #logmsg dt, #logmsg dd { line-height: 14pt; }
#logmsg h1, #logmsg h2, #logmsg h3, #logmsg h4, #logmsg h5, #logmsg h6 { margin: .5em 0; }
#logmsg h1:first-child, #logmsg h2:first-child, #logmsg h3:first-child, #logmsg h4:first-child, #logmsg h5:first-child, #logmsg h6:first-child { margin-top: 0; }
#logmsg ul, #logmsg ol { padding: 0; list-style-position: inside; margin: 0 0 0 1em; }
#logmsg ul { text-indent: -1em; padding-left: 1em; }#logmsg ol { text-indent: -1.5em; padding-left: 1.5em; }
#logmsg > ul, #logmsg > ol { margin: 0 0 1em 0; }
#logmsg pre { background: #eee; padding: 1em; }
#logmsg blockquote { border: 1px solid #fa0; border-left-width: 10px; padding: 1em 1em 0 1em; background: white;}
#logmsg dl { margin: 0; }
#logmsg dt { font-weight: bold; }
#logmsg dd { margin: 0; padding: 0 0 0.5em 0; }
#logmsg dd:before { content:'\00bb';}
#logmsg table { border-spacing: 0px; border-collapse: collapse; border-top: 4px solid #fa0; border-bottom: 1px solid #fa0; background: #fff; }
#logmsg table th { text-align: left; font-weight: normal; padding: 0.2em 0.5em; border-top: 1px dotted #fa0; }
#logmsg table td { text-align: right; border-top: 1px dotted #fa0; padding: 0.2em 0.5em; }
#logmsg table thead th { text-align: center; border-bottom: 1px solid #fa0; }
#logmsg table th.Corner { text-align: left; }
#logmsg hr { border: none 0; border-top: 2px dashed #fa0; height: 1px; }
#header, #footer { color: #fff; background: #636; border: 1px #300 solid; padding: 6px; }
#patch { width: 100%; }
#patch h4 {font-family: verdana,arial,helvetica,sans-serif;font-size:10pt;padding:8px;background:#369;color:#fff;margin:0;}
#patch .propset h4, #patch .binary h4 {margin:0;}
#patch pre {padding:0;line-height:1.2em;margin:0;}
#patch .diff {width:100%;background:#eee;padding: 0 0 10px 0;overflow:auto;}
#patch .propset .diff, #patch .binary .diff {padding:10px 0;}
#patch span {display:block;padding:0 10px;}
#patch .modfile, #patch .addfile, #patch .delfile, #patch .propset, #patch .binary, #patch .copfile {border:1px solid #ccc;margin:10px 0;}
#patch ins {background:#dfd;text-decoration:none;display:block;padding:0 10px;}
#patch del {background:#fdd;text-decoration:none;display:block;padding:0 10px;}
#patch .lines, .info {color:#888;background:#fff;}
--></style>
<div id="msg">
<dl class="meta">
<dt>Revision</dt> <dd><a href="https://trac.macports.org/changeset/128996">128996</a></dd>
<dt>Author</dt> <dd>jmr@macports.org</dd>
<dt>Date</dt> <dd>2014-12-02 08:29:02 -0800 (Tue, 02 Dec 2014)</dd>
</dl>
<h3>Log Message</h3>
<pre>add PortIndex2PGSQL.tcl, #40579</pre>
<h3>Added Paths</h3>
<ul>
<li><a href="#trunkbaseportmgrjobsPortIndex2PGSQLtcl">trunk/base/portmgr/jobs/PortIndex2PGSQL.tcl</a></li>
</ul>
</div>
<div id="patch">
<h3>Diff</h3>
<a id="trunkbaseportmgrjobsPortIndex2PGSQLtclfromrev128993trunkbaseportmgrjobsPortIndex2MySQLtcl"></a>
<div class="copfile"><h4>Copied: trunk/base/portmgr/jobs/PortIndex2PGSQL.tcl (from rev 128993, trunk/base/portmgr/jobs/PortIndex2MySQL.tcl) (0 => 128996)</h4>
<pre class="diff"><span>
<span class="info">--- trunk/base/portmgr/jobs/PortIndex2PGSQL.tcl (rev 0)
+++ trunk/base/portmgr/jobs/PortIndex2PGSQL.tcl 2014-12-02 16:29:02 UTC (rev 128996)
</span><span class="lines">@@ -0,0 +1,436 @@
</span><ins>+#!/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
</ins></span></pre>
</div>
</div>
</body>
</html>