<!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>[12763] CalendarServer/trunk</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="http://trac.calendarserver.org//changeset/12763">12763</a></dd>
<dt>Author</dt> <dd>cdaboo@apple.com</dd>
<dt>Date</dt> <dd>2014-02-27 10:13:47 -0800 (Thu, 27 Feb 2014)</dd>
</dl>
<h3>Log Message</h3>
<pre>Add a cli tool to compare the schema in an existing postgres DB with the matching schema version that we define and print any mismatches.</pre>
<h3>Added Paths</h3>
<ul>
<li><a href="#CalendarServertrunkbincalendarserver_check_database_schema">CalendarServer/trunk/bin/calendarserver_check_database_schema</a></li>
<li><a href="#CalendarServertrunkcalendarservertoolscheckdatabaseschemapy">CalendarServer/trunk/calendarserver/tools/checkdatabaseschema.py</a></li>
</ul>
</div>
<div id="patch">
<h3>Diff</h3>
<a id="CalendarServertrunkbincalendarserver_check_database_schema"></a>
<div class="addfile"><h4>Added: CalendarServer/trunk/bin/calendarserver_check_database_schema (0 => 12763)</h4>
<pre class="diff"><span>
<span class="info">--- CalendarServer/trunk/bin/calendarserver_check_database_schema (rev 0)
+++ CalendarServer/trunk/bin/calendarserver_check_database_schema 2014-02-27 18:13:47 UTC (rev 12763)
</span><span class="lines">@@ -0,0 +1,33 @@
</span><ins>+#!/usr/bin/env python
+
+##
+# Copyright (c) 2014 Apple Inc. All rights reserved.
+#
+# Licensed under the Apache License, Version 2.0 (the "License");
+# you may not use this file except in compliance with the License.
+# You may obtain a copy of the License at
+#
+# http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing, software
+# distributed under the License is distributed on an "AS IS" BASIS,
+# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+# See the License for the specific language governing permissions and
+# limitations under the License.
+##
+
+import sys
+
+#PYTHONPATH
+
+if __name__ == "__main__":
+ if "PYTHONPATH" in globals():
+ sys.path.insert(0, PYTHONPATH)
+ else:
+ try:
+ import _calendarserver_preamble
+ except ImportError:
+ sys.exc_clear()
+
+ from calendarserver.tools.checkdatabaseschema import main
+ main()
</ins><span class="cx">Property changes on: CalendarServer/trunk/bin/calendarserver_check_database_schema
</span><span class="cx">___________________________________________________________________
</span></span></pre></div>
<a id="svnexecutable"></a>
<div class="addfile"><h4>Added: svn:executable</h4></div>
<a id="CalendarServertrunkcalendarservertoolscheckdatabaseschemapy"></a>
<div class="addfile"><h4>Added: CalendarServer/trunk/calendarserver/tools/checkdatabaseschema.py (0 => 12763)</h4>
<pre class="diff"><span>
<span class="info">--- CalendarServer/trunk/calendarserver/tools/checkdatabaseschema.py (rev 0)
+++ CalendarServer/trunk/calendarserver/tools/checkdatabaseschema.py 2014-02-27 18:13:47 UTC (rev 12763)
</span><span class="lines">@@ -0,0 +1,264 @@
</span><ins>+##
+# Copyright (c) 2014 Apple Inc. All rights reserved.
+#
+# Licensed under the Apache License, Version 2.0 (the "License");
+# you may not use this file except in compliance with the License.
+# You may obtain a copy of the License at
+#
+# http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing, software
+# distributed under the License is distributed on an "AS IS" BASIS,
+# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+# See the License for the specific language governing permissions and
+# limitations under the License.
+##
+from __future__ import print_function
+
+from getopt import getopt, GetoptError
+import os
+import re
+import subprocess
+import sys
+from twext.enterprise.dal.model import Schema, Table, Column, Sequence
+from twext.enterprise.dal.parseschema import addSQLToSchema, schemaFromPath
+from twisted.python.filepath import FilePath
+
+USERNAME = "caldav"
+DATABASENAME = "caldav"
+PGSOCKETDIR = "127.0.0.1"
+SCHEMADIR = "./txdav/common/datastore/sql_schema/"
+
+# Executables:
+PSQL = "../postgresql/_root/bin/psql"
+
+def usage(e=None):
+ name = os.path.basename(sys.argv[0])
+ print("usage: %s [options] username" % (name,))
+ print("")
+ print(" Check calendar server postgres database and schema")
+ print("")
+ print("options:")
+ print(" -d: path to server's sql_schema directory [./txdav/common/datastore/sql_schema/]")
+ print(" -k: postgres socket path (value for psql -h argument [127.0.0.1])")
+ print(" -p: location of psql tool if not on PATH already [psql]")
+ print(" -x: use default values for OS X server")
+ print(" -h --help: print this help and exit")
+ print(" -v --verbose: print additional information")
+ print("")
+
+ if e:
+ sys.stderr.write("%s\n" % (e,))
+ sys.exit(64)
+ else:
+ sys.exit(0)
+
+
+
+def execSQL(title, stmt, verbose=False):
+ """
+ Execute the provided SQL statement, return results as a list of rows.
+
+ @param stmt: the SQL to execute
+ @type stmt: L{str}
+ """
+
+ cmdArgs = [
+ PSQL,
+ "-h", PGSOCKETDIR,
+ "-d", DATABASENAME,
+ "-U", USERNAME,
+ "-t",
+ "-c", stmt,
+ ]
+ try:
+ if verbose:
+ print("\n{}".format(title))
+ print("Executing: {}".format(" ".join(cmdArgs)))
+ out = subprocess.check_output(cmdArgs, stderr=subprocess.STDOUT)
+ if verbose:
+ print(out)
+ except subprocess.CalledProcessError, e:
+ if verbose:
+ print(e.output)
+ raise CheckSchemaError(
+ "%s failed:\n%s (exit code = %d)" %
+ (PSQL, e.output, e.returncode)
+ )
+
+ return [s.strip() for s in out.splitlines()[:-1]]
+
+
+
+def getSchemaVersion(verbose=False):
+ """
+ Return the version number for the schema installed in the database.
+ Raise CheckSchemaError if there is an issue.
+ """
+
+ out = execSQL(
+ "Reading schema version...",
+ "select value from calendarserver where name='VERSION';",
+ verbose
+ )
+
+ try:
+ version = int(out[0])
+ except ValueError, e:
+ raise CheckSchemaError(
+ "Failed to parse schema version: %s" % (e,)
+ )
+ return version
+
+
+
+def dumpCurrentSchema(verbose=False):
+
+ schema = Schema("Dumped schema")
+ tables = {}
+
+ # Tables
+ rows = execSQL(
+ "Schema tables...",
+ "select table_name from information_schema.tables where table_schema = 'public';",
+ verbose
+ )
+ for row in rows:
+ name = row
+ table = Table(schema, name)
+ tables[name] = table
+
+ # Columns
+ rows = execSQL(
+ "Reading table '{}' columns...".format(name),
+ "select column_name from information_schema.columns where table_schema = 'public' and table_name = '{}';".format(name),
+ verbose
+ )
+ for row in rows:
+ name = row
+ # TODO: figure out the type
+ column = Column(table, name, None)
+ table.columns.append(column)
+
+ # Indexes
+ # TODO: handle implicit indexes created via primary key() and unique() statements within CREATE TABLE
+ rows = execSQL(
+ "Schema indexes...",
+ "select indexdef from pg_indexes where schemaname = 'public';",
+ verbose
+ )
+ for indexdef in rows:
+ addSQLToSchema(schema, indexdef.replace("public.", ""))
+
+ # Sequences
+ rows = execSQL(
+ "Schema sequences...",
+ "select sequence_name from information_schema.sequences where sequence_schema = 'public';",
+ verbose
+ )
+ for row in rows:
+ name = row
+ Sequence(schema, name)
+
+ return schema
+
+
+
+def checkSchema(dbversion, verbose=False):
+ """
+ Compare schema in the database with the expected schema file.
+ """
+
+ dbschema = dumpCurrentSchema(verbose)
+
+ # Find current schema
+ fp = FilePath(SCHEMADIR)
+ fpschema = fp.child("old").child("postgres-dialect").child("v{}.sql".format(dbversion))
+ if not fpschema.exists():
+ fpschema = fp.child("current.sql")
+ expectedSchema = schemaFromPath(fpschema)
+
+ mismatched = dbschema.compare(expectedSchema)
+ if mismatched:
+ print("\nCurrent schema in database is mismatched:\n\n" + "\n".join(mismatched))
+ else:
+ print("\nCurrent schema in database is a match to the expected server version")
+
+
+
+class CheckSchemaError(Exception):
+ pass
+
+
+
+def error(s):
+ sys.stderr.write("%s\n" % (s,))
+ sys.exit(1)
+
+
+
+def main():
+ try:
+ (optargs, _ignore_args) = getopt(
+ sys.argv[1:], "d:hk:vx", [
+ "help",
+ "verbose",
+ ],
+ )
+ except GetoptError, e:
+ usage(e)
+
+ verbose = False
+
+ global SCHEMADIR, PGSOCKETDIR, PSQL
+
+ for opt, arg in optargs:
+ if opt in ("-h", "--help"):
+ usage()
+ elif opt in ("-d",):
+ SCHEMADIR = arg
+ elif opt in ("-k",):
+ PGSOCKETDIR = arg
+ elif opt in ("-p",):
+ PSQL = arg
+ elif opt in ("-x",):
+ sktdir = FilePath("/var/run/caldavd")
+ for skt in sktdir.children():
+ if skt.basename().startswith("ccs_postgres_"):
+ PGSOCKETDIR = skt.path
+ PSQL = "/Applications/Server.app/Contents/ServerRoot/usr/bin/psql"
+ SCHEMADIR = "/Applications/Server.app/Contents/ServerRoot/usr/share/caldavd/lib/python/txdav/common/datastore/sql_schema/"
+ elif opt in ("-v", "--verbose"):
+ verbose = True
+ else:
+ raise NotImplementedError(opt)
+
+ # Retrieve the db_version number of the installed schema
+ try:
+ db_version = getSchemaVersion(verbose=verbose)
+ except CheckSchemaError, e:
+ db_version = 0
+
+ # Retrieve the version number from the schema file
+ currentschema = FilePath(SCHEMADIR).child("current.sql")
+
+ try:
+ data = currentschema.getContent()
+ except IOError:
+ print("Unable to open the current schema file: %s" % (currentschema.path,))
+ else:
+ found = re.search("insert into CALENDARSERVER values \('VERSION', '(\d+)'\);", data)
+ if found is None:
+ print("Schema is missing required schema VERSION insert statement: %s" % (currentschema.path,))
+ else:
+ current_version = int(found.group(1))
+ if db_version == current_version:
+ print("Schema version {} is current".format(db_version))
+
+ else: # upgrade needed
+ print("Schema needs to be upgraded from {} to {}".format(db_version, current_version))
+
+ checkSchema(db_version, verbose)
+
+if __name__ == "__main__":
+ main()
</ins></span></pre>
</div>
</div>
</body>
</html>