[CalendarServer-changes] [506]
CalendarServer/branches/users/wsanchez/provisioning/twistedcaldav
source_changes at macosforge.org
source_changes at macosforge.org
Thu Nov 16 21:13:41 PST 2006
Revision: 506
http://trac.macosforge.org/projects/calendarserver/changeset/506
Author: cdaboo at apple.com
Date: 2006-11-16 21:13:40 -0800 (Thu, 16 Nov 2006)
Log Message:
-----------
SQL directory service implementation. Note that a new top level sql module was created
by cloning code from index.py. This abstracts out common sql ops with a sqlite db file.
At some point we should refactor the abstract calendar index to inherit from this new one.
Added Paths:
-----------
CalendarServer/branches/users/wsanchez/provisioning/twistedcaldav/directory/sqldb.py
CalendarServer/branches/users/wsanchez/provisioning/twistedcaldav/directory/test/test_sqldb.py
CalendarServer/branches/users/wsanchez/provisioning/twistedcaldav/sql.py
Added: CalendarServer/branches/users/wsanchez/provisioning/twistedcaldav/directory/sqldb.py
===================================================================
--- CalendarServer/branches/users/wsanchez/provisioning/twistedcaldav/directory/sqldb.py (rev 0)
+++ CalendarServer/branches/users/wsanchez/provisioning/twistedcaldav/directory/sqldb.py 2006-11-17 05:13:40 UTC (rev 506)
@@ -0,0 +1,288 @@
+##
+# Copyright (c) 2006 Apple Computer, 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.
+#
+# DRI: Cyrus Daboo, cdaboo at apple.com
+##
+from twistedcaldav.sql import AbstractSQLDatabase
+
+
+"""
+SQL (sqlite) based user/group/resource directory service implementation.
+"""
+
+"""
+SCHEMA:
+
+User Database:
+
+ROW: TYPE, UID (unique), PSWD, NAME, CANPROXY
+
+Group Database:
+
+ROW: GRPUID, UID
+
+CUAddress database:
+
+ROW: CUADDR (unqiue), UID
+
+"""
+
+__all__ = [
+ "SQLDirectoryService",
+]
+
+from twisted.cred.credentials import UsernamePassword
+from twisted.python.filepath import FilePath
+
+from twistedcaldav.directory.directory import DirectoryService, DirectoryRecord
+from twistedcaldav.directory.xmlaccountsparser import XMLAccountsParser
+
+import os
+
+class SQLDirectoryManager(AbstractSQLDatabase):
+ """
+ House keeping operations on the SQL DB, including loading from XML file,
+ and record dumping. This can be used as a standalong DB management tool.
+ """
+
+ DBTYPE = "DIRECTORYSERVICE"
+ DBNAME = ".db.accounts"
+ DBVERSION = "1"
+ ACCOUNTDB = "ACCOUNTS"
+ GROUPSDB = "GROUPS"
+ CUADDRDB = "CUADDRS"
+
+ def __init__(self, path):
+ path = os.path.join(path, SQLDirectoryManager.DBNAME)
+ super(SQLDirectoryManager, self).__init__(path, SQLDirectoryManager.DBVERSION)
+
+ def loadFromXML(self, xmlFile):
+ xmlAccounts = XMLAccountsParser(xmlFile)
+
+ # Totally wipe existing DB and start from scratch
+ if os.path.exists(self.dbpath):
+ os.remove(self.dbpath)
+
+ # Now add records to db
+ for item in xmlAccounts.items.itervalues():
+ self._add_to_db(item)
+ self._db_commit()
+
+ def listRecords(self, recordType):
+ return self._db_values_for_sql("select UID from ACCOUNTS where TYPE = :1", recordType)
+
+ def getRecord(self, recordType, uid):
+ # Get individual account record
+ rowiter = self._db_execute("select UID, PSWD, NAME from ACCOUNTS where TYPE = :1 and UID = :2", recordType, uid)
+ result = None
+ for row in rowiter:
+ if result:
+ result = None
+ break
+ result = row
+
+ if result is None:
+ return None
+
+ uid = result[0]
+ pswd = result[1]
+ name = result[2]
+ members = []
+ groups = []
+
+ # See if we have a group
+ if recordType == "group":
+ rowiter = self._db_execute("select UID from GROUPS where GRPUID = :1", uid)
+ for row in rowiter:
+ members.append(row[0])
+
+ # See if we are a member of a group
+ rowiter = self._db_execute("select GRPUID from GROUPS where UID = :1", uid)
+ for row in rowiter:
+ groups.append(row[0])
+
+ return uid, pswd, name, members, groups
+
+ def _add_to_db(self, record):
+ # Do regular account entry
+ type = record.recordType
+ uid = record.uid
+ pswd = record.pswd
+ name = record.name
+ canproxy = ('F', 'T')[record.canproxy]
+ self._db_execute(
+ """
+ insert into ACCOUNTS (TYPE, UID, PSWD, NAME, CANPROXY)
+ values (:1, :2, :3, :4, :5)
+ """, type, uid, pswd, name, canproxy
+ )
+
+ # Check for group
+ if type == "group":
+ for member in record.members:
+ self._db_execute(
+ """
+ insert into GROUPS (GRPUID, UID)
+ values (:1, :2)
+ """, uid, member
+ )
+
+ # CUAddress
+ for cuaddr in record.cuaddrs:
+ self._db_execute(
+ """
+ insert into CUADDRS (CUADDR, UID)
+ values (:1, :2)
+ """, cuaddr, uid
+ )
+
+ def _delete_from_db(self, uid):
+ """
+ Deletes the specified entry from all dbs.
+ @param name: the name of the resource to delete.
+ @param uid: the uid of the resource to delete.
+ """
+ self._db_execute("delete from ACCOUNTS where UID = :1", uid)
+ self._db_execute("delete from GROUPS where GRPUID = :1", uid)
+ self._db_execute("delete from GROUPS where UID = :1", uid)
+ self._db_execute("delete from CUADDRS where UID = :1", uid)
+
+ def _db_type(self):
+ """
+ @return: the collection type assigned to this index.
+ """
+ return SQLDirectoryManager.DBTYPE
+
+ def _db_init_data_tables(self, q):
+ """
+ Initialise the underlying database tables.
+ @param q: a database cursor to use.
+ """
+ #
+ # ACCOUNTS table
+ #
+ q.execute(
+ """
+ create table ACCOUNTS (
+ TYPE text,
+ UID text unique,
+ PSWD text,
+ NAME text,
+ CANPROXY text(1)
+ )
+ """
+ )
+
+ #
+ # GROUPS table
+ #
+ q.execute(
+ """
+ create table GROUPS (
+ GRPUID text,
+ UID text
+ )
+ """
+ )
+
+ #
+ # CUADDRS table
+ #
+ q.execute(
+ """
+ create table CUADDRS (
+ CUADDR text unique,
+ UID text
+ )
+ """
+ )
+
+class SQLDirectoryService(DirectoryService):
+ """
+ XML based implementation of L{IDirectoryService}.
+ """
+ def __repr__(self):
+ return "<%s %r>" % (self.__class__.__name__, self.xmlFile)
+
+ def __init__(self, dbParentPath, xmlFile = None):
+ super(SQLDirectoryService, self).__init__()
+
+ if type(dbParentPath) is str:
+ dbParentPath = FilePath(dbParentPath)
+
+ self.manager = SQLDirectoryManager(dbParentPath.path)
+ if xmlFile:
+ self.manager.loadFromXML(xmlFile)
+
+ def recordTypes(self):
+ recordTypes = ("user", "group", "resource")
+ return recordTypes
+
+ def listRecords(self, recordType):
+ for name in self.manager.listRecords(recordType):
+ yield name
+
+ def recordWithShortName(self, recordType, shortName):
+ result = self.manager.getRecord(recordType, shortName)
+ if result:
+ return SQLDirectoryRecord(
+ service = self,
+ recordType = recordType,
+ shortName = result[0],
+ pswd = result[1],
+ name = result[2],
+ members = result[3],
+ groups = result[4],
+ )
+
+ return None
+
+ def recordWithGUID(self, guid):
+ raise NotImplementedError()
+
+class SQLDirectoryRecord(DirectoryRecord):
+ """
+ XML based implementation implementation of L{IDirectoryRecord}.
+ """
+ def __init__(self, service, recordType, shortName, pswd, name, members, groups):
+ super(SQLDirectoryRecord, self).__init__(
+ service = service,
+ recordType = recordType,
+ guid = None,
+ shortName = shortName,
+ fullName = name,
+ )
+
+ self.password = pswd
+ self._members = members
+ self._groups = groups
+
+ def members(self):
+ for shortName in self._members:
+ yield self.service.recordWithShortName("user", shortName)
+
+ def groups(self):
+ for shortName in self._groups:
+ yield self.service.recordWithShortName("group", shortName)
+
+ def verifyCredentials(self, credentials):
+ if isinstance(credentials, UsernamePassword):
+ return credentials.password == self.password
+
+ return super(SQLDirectoryRecord, self).verifyCredentials(credentials)
+
+if __name__ == '__main__':
+ mgr = SQLDirectoryManager("./")
+ mgr.loadFromXML("test/accounts.xml")
Added: CalendarServer/branches/users/wsanchez/provisioning/twistedcaldav/directory/test/test_sqldb.py
===================================================================
--- CalendarServer/branches/users/wsanchez/provisioning/twistedcaldav/directory/test/test_sqldb.py (rev 0)
+++ CalendarServer/branches/users/wsanchez/provisioning/twistedcaldav/directory/test/test_sqldb.py 2006-11-17 05:13:40 UTC (rev 506)
@@ -0,0 +1,67 @@
+##
+# Copyright (c) 2005-2006 Apple Computer, 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.
+#
+# DRI: Cyrus Daboo, cdaboo at apple.com
+##
+
+import os
+
+from twisted.python.filepath import FilePath
+
+import twistedcaldav.directory.test.util
+from twistedcaldav.directory.sqldb import SQLDirectoryService
+
+xmlFile = FilePath(os.path.join(os.path.dirname(__file__), "accounts.xml"))
+
+# FIXME: Add tests for GUID hooey, once we figure out what that means here
+
+class Basic (twistedcaldav.directory.test.util.BasicTestCase):
+ """
+ Test SQL directory implementation.
+ """
+ recordTypes = set(("user", "group", "resource"))
+
+ users = {
+ "admin" : "nimda",
+ "proxy" : "yxorp",
+ "wsanchez": "zehcnasw",
+ "cdaboo" : "oobadc",
+ "lecroy" : "yorcel",
+ "dreid" : "dierd",
+ "user01" : "01user",
+ "user02" : "02user",
+ }
+
+ groups = {
+ "managers" : ("lecroy",),
+ "grunts" : ("wsanchez", "cdaboo", "dreid"),
+ "right_coast": ("cdaboo",),
+ "left_coast" : ("wsanchez", "dreid", "lecroy"),
+ }
+
+ resources = set((
+ "mercury",
+ "gemini",
+ "apollo",
+ ))
+
+ def xmlFile(self):
+ if not hasattr(self, "_xmlFile"):
+ self._xmlFile = FilePath(self.mktemp())
+ xmlFile.copyTo(self._xmlFile)
+ return self._xmlFile
+
+ def service(self):
+ return SQLDirectoryService(os.getcwd(), self.xmlFile())
Added: CalendarServer/branches/users/wsanchez/provisioning/twistedcaldav/sql.py
===================================================================
--- CalendarServer/branches/users/wsanchez/provisioning/twistedcaldav/sql.py (rev 0)
+++ CalendarServer/branches/users/wsanchez/provisioning/twistedcaldav/sql.py 2006-11-17 05:13:40 UTC (rev 506)
@@ -0,0 +1,213 @@
+##
+# Copyright (c) 2005-2006 Apple Computer, 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.
+#
+# DRI: Cyrus Daboo, cdaboo at apple.com
+##
+
+"""
+Generic SQL database access object.
+"""
+
+__all__ = [
+ "AbstractSQLDatabase",
+]
+
+import os
+
+from pysqlite2 import dbapi2 as sqlite
+
+from twisted.python import log
+
+class AbstractSQLDatabase(object):
+ """
+ A generic SQL database.
+ """
+
+ def __init__(self, dbpath, version):
+ """
+ @param resource: the L{twistedcaldav.static.CalDAVFile} resource to
+ index. C{resource} must be a calendar collection (ie.
+ C{resource.isPseudoCalendarCollection()} returns C{True}.)
+ """
+ self.dbpath = dbpath
+ self.version = version
+
+ def _db_type(self):
+ """
+ @return: the collection type assigned to this index.
+ """
+ raise NotImplementedError
+
+ def _db(self):
+ """
+ Access the underlying database.
+ @return: a db2 connection object for this index's underlying data store.
+ """
+ if not hasattr(self, "_db_connection"):
+ db_filename = self.dbpath
+ self._db_connection = sqlite.connect(db_filename)
+
+ #
+ # Set up the schema
+ #
+ q = self._db_connection.cursor()
+ try:
+ # Create CALDAV table if needed
+ q.execute(
+ """
+ select (1) from SQLITE_MASTER
+ where TYPE = 'table' and NAME = 'CALDAV'
+ """)
+ caldav = q.fetchone()
+
+ if caldav:
+ q.execute(
+ """
+ select VALUE from CALDAV
+ where KEY = 'SCHEMA_VERSION'
+ """)
+ version = q.fetchone()
+
+ if version is not None: version = version[0]
+
+ q.execute(
+ """
+ select VALUE from CALDAV
+ where KEY = 'TYPE'
+ """)
+ type = q.fetchone()
+
+ if type is not None: type = type[0]
+
+ if (version != self.version) or (type != self._db_type()):
+ if version != self.version:
+ log.err("Database %s has different schema (v.%s vs. v.%s)"
+ % (db_filename, version, self.version))
+ if type != self._db_type():
+ log.err("Database %s has different type (%s vs. %s)"
+ % (db_filename, type, self._db_type()))
+
+ # Delete this index and start over
+ q.close()
+ q = None
+ self._db_connection.close()
+ del(self._db_connection)
+ os.remove(db_filename)
+ return self._db()
+
+ else:
+ self._db_init(db_filename, q)
+
+ self._db_connection.commit()
+ finally:
+ if q is not None: q.close()
+ return self._db_connection
+
+ def _db_init(self, db_filename, q):
+ """
+ Initialise the underlying database tables.
+ @param db_filename: the file name of the index database.
+ @param q: a database cursor to use.
+ """
+ log.msg("Initializing database %s" % (db_filename,))
+
+ self._db_init_schema_table(q)
+ self._db_init_data_tables(q)
+
+ def _db_init_schema_table(self, q):
+ """
+ Initialise the underlying database tables.
+ @param db_filename: the file name of the index database.
+ @param q: a database cursor to use.
+ """
+
+ #
+ # CALDAV table keeps track of our schema version and type
+ #
+ q.execute(
+ """
+ create table CALDAV (
+ KEY text unique, VALUE text unique
+ )
+ """
+ )
+ q.execute(
+ """
+ insert into CALDAV (KEY, VALUE)
+ values ('SCHEMA_VERSION', :1)
+ """, [self.version]
+ )
+ q.execute(
+ """
+ insert into CALDAV (KEY, VALUE)
+ values ('TYPE', :1)
+ """, [self._db_type()]
+ )
+
+ def _db_init_data_tables(self, q):
+ """
+ Initialise the underlying database tables.
+ @param db_filename: the file name of the index database.
+ @param q: a database cursor to use.
+ """
+ raise NotImplementedError
+
+ def _db_values_for_sql(self, sql, *query_params):
+ """
+ Execute an SQL query and obtain the resulting values.
+ @param sql: the SQL query to execute.
+ @param query_params: parameters to C{sql}.
+ @return: an interable of values in the first column of each row
+ resulting from executing C{sql} with C{query_params}.
+ @raise AssertionError: if the query yields multiple columns.
+ """
+ return (row[0] for row in self._db_execute(sql, *query_params))
+
+ def _db_value_for_sql(self, sql, *query_params):
+ """
+ Execute an SQL query and obtain a single value.
+ @param sql: the SQL query to execute.
+ @param query_params: parameters to C{sql}.
+ @return: the value resulting from the executing C{sql} with
+ C{query_params}.
+ @raise AssertionError: if the query yields multiple rows or columns.
+ """
+ value = None
+ for row in self._db_values_for_sql(sql, *query_params):
+ assert value is None, "Multiple values in DB for %s %s" % (sql, query_params)
+ value = row
+ return value
+
+ def _db_execute(self, sql, *query_params):
+ """
+ Execute an SQL query and obtain the resulting values.
+ @param sql: the SQL query to execute.
+ @param query_params: parameters to C{sql}.
+ @return: an interable of tuples for each row resulting from executing
+ C{sql} with C{query_params}.
+ """
+ q = self._db().cursor()
+ try:
+ try:
+ q.execute(sql, query_params)
+ except:
+ log.err("Exception while executing SQL: %r %r" % (sql, query_params))
+ raise
+ return q.fetchall()
+ finally:
+ q.close()
+
+ def _db_commit (self): self._db_connection.commit()
+ def _db_rollback(self): self._db_connection.rollback()
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.macosforge.org/pipermail/calendarserver-changes/attachments/20061116/f4a0fcf4/attachment.html
More information about the calendarserver-changes
mailing list