source: trunk/base/portmgr/jobs/PortIndex2MySQL.tcl @ 59836

Last change on this file since 59836 was 59836, checked in by jmr@…, 11 years ago

new mportlistall proc, various slight efficiency improvements

  • Property svn:eol-style set to native
  • Property svn:keywords set to Id
File size: 14.8 KB
Line 
1#!/opt/local/bin/tclsh
2# -*- 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
3#
4# PortIndex2MySQL.tcl
5# Kevin Van Vechten | kevin@opendarwin.org
6# 3-Oct-2002
7# Juan Manuel Palacios | jmpp@macports.org
8# 22-Nov-2007
9# $Id: PortIndex2MySQL.tcl 59836 2009-10-23 20:58:55Z jmr@macports.org $
10#
11# Copyright (c) 2007 Juan Manuel Palacios, The MacPorts Project.
12# Copyright (c) 2003 Apple Computer, Inc.
13# Copyright (c) 2002 Kevin Van Vechten.
14# All rights reserved.
15#
16# Redistribution and use in source and binary forms, with or without
17# modification, are permitted provided that the following conditions
18# are met:
19# 1. Redistributions of source code must retain the above copyright
20#    notice, this list of conditions and the following disclaimer.
21# 2. Redistributions in binary form must reproduce the above copyright
22#    notice, this list of conditions and the following disclaimer in the
23#    documentation and/or other materials provided with the distribution.
24# 3. Neither the name of Apple Computer, Inc. nor the names of its contributors
25#    may be used to endorse or promote products derived from this software
26#    without specific prior written permission.
27#
28# THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
29# AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
30# IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
31# ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE
32# LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR
33# CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF
34# SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS
35# INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN
36# CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE)
37# ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
38# POSSIBILITY OF SUCH DAMAGE.
39
40
41#####
42# The PortIndex2MySQL script populates a database with key information extracted
43# from the Portfiles in the ports tree pointed to by the sources.conf file in a
44# MacPorts installation, found by loading its macports1.0 tcl package and initializing
45# it with 'mportinit' below. Main use of the resulting database is providing live
46# information to the ports.php page, a client tailored to poll it. For this very reason,
47# information fed to the database always has to be kept up to date in order to remain
48# meaningful, which is accomplished simply by calling the 'mportsync' proc in macports1.0
49# (which updates the ports tree in use) and by installing the script on cron/launchd to be
50# run on a timely schedule (not any more frequent than the run of the PortIndexRegen.sh
51# script on that creates a new PortIndex file).
52#
53# Remaining requirement to successfully run this script is performing the necessary
54# MySQL admin tasks on the host box to create the database in the first place and the
55# MySQL user that will be given enough privileges to alter it. Values in the database
56# related variables provided below have to be adapted accordingly to match the chosen
57# setup.
58#####
59
60
61
62# Runtime information log file and reciepient.
63set runlog "/tmp/portsdb.log"
64set runlog_fd [open $runlog w+]
65set lockfile "/tmp/portsdb.lock"
66set mailprog "/usr/sbin/sendmail"
67set DATE [clock format [clock seconds] -format "%A %Y-%m-%d at %T"]
68
69#set SPAM_LOVERS example@hostname.com
70
71set SUBJECT "PortIndex2MySQL run failure on $DATE"
72set FROM macports-mgr@lists.macosforge.org
73set HEADERS "To: $SPAM_LOVERS\r\nFrom: $FROM\r\nSubject: $SUBJECT\r\n\r\n"
74
75# handle command line arguments
76set create_tables true
77if {[llength $argv]} {
78    if {[lindex $argv 0] == "--create-tables"} {
79        set create_tables true
80    }
81}
82
83# House keeping on exit.
84proc cleanup {args} {
85    foreach file_to_clean $args {
86        upvar $file_to_clean up_file_to_clean
87        upvar ${file_to_clean}_fd up_file_to_clean_fd
88        close $up_file_to_clean_fd
89        file delete -force $up_file_to_clean
90    }
91}
92
93# What to do when terminating execution, depending on the $exit_status condition.
94proc terminate {exit_status} {
95    global runlog runlog_fd
96    if {$exit_status} {
97        global subject SPAM_LOVERS mailprog
98        seek $runlog_fd 0 start
99        exec -- $mailprog $SPAM_LOVERS <@ $runlog_fd
100    }
101    cleanup runlog
102    exit $exit_status
103}
104
105# macports1.0 UI instantiation to route information/error messages wherever we want.
106# This is a custom ui_channels proc because we want to get reported information on
107# channels other than the default stdout/stderr that the macports1.0 API provides,
108# namely a log file we can later mail to people in charge if need be.
109proc ui_channels {priority} {
110    global runlog_fd
111    switch $priority {
112        debug {
113            if {[macports::ui_isset ports_debug]} {
114                return $runlog_fd
115            } else {
116                return {}
117            }
118        }
119        info {
120            if {[macports::ui_isset ports_verbose]} {
121                return $runlog_fd
122            } else {
123                return {}
124            }
125        }
126        msg {
127            if {[macports::ui_isset ports_quiet]} {
128                return $runlog_fd
129            } else {
130                return {}
131            }
132        }
133        error {
134            return $runlog_fd
135        }
136        default {
137            return {}
138        }
139    }
140}
141
142# Procedure to catch the database password from a protected file.
143proc getpasswd {passwdfile} {
144    if {[catch {open $passwdfile r} passwdfile_fd]} {
145        global lockfile lockfile_fd
146        ui_error "${::errorCode}: $passwdfile_fd"
147        cleanup lockfile
148        terminate 1
149    }
150    if {[gets $passwdfile_fd passwd] <= 0} {
151        global lockfile lockfile_fd
152        close $passwdfile_fd
153        ui_error "No password found in password file $passwdfile!"
154        cleanup lockfile
155        terminate 1
156    }
157    close $passwdfile_fd
158    return $passwd
159}
160
161# SQL string escaping.
162proc sql_escape {str} {
163    regsub -all -- {'} $str {\\'} str
164    regsub -all -- {"} $str {\\"} str
165    regsub -all -- {\n} $str {\\n} str
166    return $str
167}
168
169# We first initialize the runlog with proper mail headers
170puts $runlog_fd $HEADERS
171
172# Check if there are any stray sibling jobs before moving on, bail in such case.
173if {[file exists $lockfile]} {
174    puts $runlog_fd "PortIndex2MySQL lock file found, is another job running?" 
175    terminate 1
176} else {
177    set lockfile_fd [open $lockfile a]
178}
179
180# Load macports1.0 so that we can use some of its procs and the portinfo array.
181if {[catch { source [file join "/Library/Tcl" macports1.0 macports_fastload.tcl] } errstr]} {
182    puts $runlog_fd "${::errorInfo}"
183    puts $runlog_fd "Failed to locate the macports1.0 Tcl package file: $errstr"
184    cleanup lockfile
185    terminate 1
186}
187if {[catch { package require macports } errstr]} {
188    puts $runlog_fd "${::errorInfo}"
189    puts $runlog_fd "Failed to load the macports1.0 Tcl package: $errstr"
190    cleanup lockfile
191    terminate 1
192}
193
194# Initialize macports1.0 and its UI, in order to find the sources.conf file
195# (which is what will point us to the PortIndex we're gonna use) and use
196# the runtime information.
197array set ui_options {ports_verbose yes}
198if {[catch {mportinit ui_options} errstr]} {
199    puts $runlog_fd "${::errorInfo}"
200    puts $runlog_fd "Failed to initialize MacPorts: $errstr"
201    cleanup lockfile
202    terminate 1
203}
204
205
206# Database abstraction variables:
207set sqlfile "/tmp/portsdb.sql"
208set portsdb_host localhost
209set portsdb_name macports
210set portsdb_user macports
211set passwdfile "/opt/local/share/macports/resources/portmgr/password_file"
212set portsdb_passwd [getpasswd $passwdfile]
213set portsdb_cmd [macports::findBinary mysql5]
214
215
216# Flat text file to which sql statements are written.
217if {[catch {open $sqlfile w+} sqlfile_fd]} {
218    ui_error "${::errorCode}: $sqlfile_fd"
219    cleanup lockfile
220    terminate 1
221}
222
223
224# Call the sync procedure to make sure we always have a fresh ports tree.
225if {[catch {mportsync} errstr]} {
226    ui_error "${::errorInfo}"
227    ui_error "Failed to update the ports tree, $errstr"
228    cleanup sqlfile lockfile
229    terminate 1
230}
231
232# Load every port in the index through a search that matches everything.
233if {[catch {set ports [mportlistall]} errstr]} {
234    ui_error "${::errorInfo}"
235    ui_error "port search failed: $errstr"
236    cleanup sqlfile lockfile
237    terminate 1
238}
239
240if {$create_tables} {
241    # Initial creation of database tables: log, portfiles, categories, maintainers, dependencies, variants and platforms.
242    # Do we need any other?
243    puts $sqlfile_fd "DROP TABLE IF EXISTS log;"
244    puts $sqlfile_fd "CREATE TABLE log (activity VARCHAR(255), activity_time TIMESTAMP(14)) DEFAULT CHARSET=utf8;"
245   
246    puts $sqlfile_fd "DROP TABLE IF EXISTS portfiles;"
247    puts $sqlfile_fd "CREATE TABLE portfiles (name VARCHAR(255) PRIMARY KEY NOT NULL, path VARCHAR(255), version VARCHAR(255),  description TEXT) DEFAULT CHARSET=utf8;"
248   
249    puts $sqlfile_fd "DROP TABLE IF EXISTS categories;"
250    puts $sqlfile_fd "CREATE TABLE categories (portfile VARCHAR(255), category VARCHAR(255), is_primary INTEGER) DEFAULT CHARSET=utf8;"
251   
252    puts $sqlfile_fd "DROP TABLE IF EXISTS maintainers;"
253    puts $sqlfile_fd "CREATE TABLE maintainers (portfile VARCHAR(255), maintainer VARCHAR(255), is_primary INTEGER) DEFAULT CHARSET=utf8;"
254   
255    puts $sqlfile_fd "DROP TABLE IF EXISTS dependencies;"
256    puts $sqlfile_fd "CREATE TABLE dependencies (portfile VARCHAR(255), library VARCHAR(255)) DEFAULT CHARSET=utf8;"
257   
258    puts $sqlfile_fd "DROP TABLE IF EXISTS variants;"
259    puts $sqlfile_fd "CREATE TABLE variants (portfile VARCHAR(255), variant VARCHAR(255)) DEFAULT CHARSET=utf8;"
260   
261    puts $sqlfile_fd "DROP TABLE IF EXISTS platforms;"
262    puts $sqlfile_fd "CREATE TABLE platforms (portfile VARCHAR(255), platform VARCHAR(255)) DEFAULT CHARSET=utf8;"
263
264    puts $sqlfile_fd "DROP TABLE IF EXISTS licenses;"
265    puts $sqlfile_fd "CREATE TABLE licenses (portfile VARCHAR(255), license VARCHAR(255)) DEFAULT CHARSET=utf8;"
266} else {
267    # if we are not creating tables from scratch, remove the old data
268    puts $sqlfile_fd "TRUNCATE log;"
269    puts $sqlfile_fd "TRUNCATE portfiles;"
270    puts $sqlfile_fd "TRUNCATE categories;"
271    puts $sqlfile_fd "TRUNCATE maintainers;"
272    puts $sqlfile_fd "TRUNCATE dependencies;"
273    puts $sqlfile_fd "TRUNCATE variants;"
274    puts $sqlfile_fd "TRUNCATE platforms;"
275    puts $sqlfile_fd "TRUNCATE licenses;"
276}
277 
278# Iterate over each matching port, extracting its information from the
279# portinfo array.
280foreach {name array} $ports {
281
282    array unset portinfo
283    array set portinfo $array
284
285    set portname [sql_escape $portinfo(name)]
286    if {[info exists portinfo(version)]} {
287        set portversion [sql_escape $portinfo(version)]
288    } else {
289        set portversion ""
290    }
291    set portdir [sql_escape $portinfo(portdir)]
292    if {[info exists portinfo(description)]} {
293        set description [sql_escape $portinfo(description)]
294    } else {
295        set description ""
296    }
297    if {[info exists portinfo(categories)]} {
298        set categories $portinfo(categories)
299    } else {
300        set categories ""
301    }
302    if {[info exists portinfo(maintainers)]} {
303        set maintainers $portinfo(maintainers)
304    } else {
305        set maintainers ""
306    }
307    if {[info exists portinfo(variants)]} {
308        set variants $portinfo(variants)
309    } else {
310        set variants ""
311    }
312    if {[info exists portinfo(depends_fetch)]} {
313        set depends_fetch $portinfo(depends_fetch)
314    } else {
315        set depends_fetch ""
316    }
317    if {[info exists portinfo(depends_extract)]} {
318        set depends_extract $portinfo(depends_extract)
319    } else {
320        set depends_extract ""
321    }
322    if {[info exists portinfo(depends_build)]} {
323        set depends_build $portinfo(depends_build)
324    } else {
325        set depends_build ""
326    }
327    if {[info exists portinfo(depends_lib)]} {
328        set depends_lib $portinfo(depends_lib)
329    } else {
330        set depends_lib ""
331    }
332    if {[info exists portinfo(depends_run)]} {
333        set depends_run $portinfo(depends_run)
334    } else {
335        set depends_run ""
336    }
337    if {[info exists portinfo(platforms)]} {
338        set platforms $portinfo(platforms)
339    } else {
340        set platforms ""
341    }
342    if {[info exists portinfo(license)]} {
343        set licenses $portinfo(license)
344    } else {
345        set licenses ""
346    }
347
348    puts $sqlfile_fd "INSERT INTO portfiles VALUES ('$portname', '$portdir', '$portversion', '$description');"
349
350    set primary 1
351    foreach category $categories {
352        set category [sql_escape $category]
353        puts $sqlfile_fd "INSERT INTO categories VALUES ('$portname', '$category', $primary);"
354        set primary 0
355    }
356   
357    set primary 1
358    foreach maintainer $maintainers {
359        set maintainer [sql_escape $maintainer]
360        puts $sqlfile_fd "INSERT INTO maintainers VALUES ('$portname', '$maintainer', $primary);"
361        set primary 0
362    }
363
364    foreach fetch_dep $depends_fetch {
365        set fetch_dep [sql_escape $fetch_dep]
366        puts $sqlfile_fd "INSERT INTO dependencies VALUES ('$portname', '$fetch_dep');"
367    }
368   
369    foreach extract_dep $depends_extract {
370        set extract_dep [sql_escape $extract_dep]
371        puts $sqlfile_fd "INSERT INTO dependencies VALUES ('$portname', '$extract_dep');"
372    }
373
374    foreach build_dep $depends_build {
375        set build_dep [sql_escape $build_dep]
376        puts $sqlfile_fd "INSERT INTO dependencies VALUES ('$portname', '$build_dep');"
377    }
378
379    foreach lib $depends_lib {
380        set lib [sql_escape $lib]
381        puts $sqlfile_fd "INSERT INTO dependencies VALUES ('$portname', '$lib');"
382    }
383
384    foreach run_dep $depends_run {
385        set run_dep [sql_escape $run_dep]
386        puts $sqlfile_fd "INSERT INTO dependencies VALUES ('$portname', '$run_dep');"
387    }
388
389    foreach variant $variants {
390        set variant [sql_escape $variant]
391        puts $sqlfile_fd "INSERT INTO variants VALUES ('$portname', '$variant');"
392    }
393
394    foreach platform $platforms {
395        set platform [sql_escape $platform]
396        puts $sqlfile_fd "INSERT INTO platforms VALUES ('$portname', '$platform');"
397    }
398
399    foreach license $licenses {
400        set license [sql_escape $license]
401        puts $sqlfile_fd "INSERT INTO licenses VALUES ('$portname', '$license');"
402    }
403
404}
405
406# Mark the db regen as done only once we're done processing all ports:
407puts $sqlfile_fd "INSERT INTO log VALUES ('update', NOW());"
408
409# Pipe the contents of the generated sql file to the database command,
410# reading from the file descriptor for the raw sql file to assure completeness.
411if {[catch {seek $sqlfile_fd 0 start} errstr]} {
412    ui_error "${::errorCode}: $errstr"
413    cleanup sqlfile lockfile
414    terminate 1
415}
416
417if {[catch {exec -- $portsdb_cmd --host=$portsdb_host --user=$portsdb_user --password=$portsdb_passwd --database=$portsdb_name <@ $sqlfile_fd} errstr]} {
418    ui_error "${::errorCode}: $errstr"
419    cleanup sqlfile lockfile
420    terminate 1
421}
422
423# done regenerating the database. Cleanup and exit successfully.
424cleanup sqlfile lockfile
425terminate 0
Note: See TracBrowser for help on using the repository browser.