""" dbinfo.py - Experimental cross-database knowledge base support. """#" __copyright__ = """ Copyright (c) 2000, Marc-Andre Lemburg (mailto:mal@lemburg.com) All Rights Reserved. Permission to use, copy, modify, and distribute this software and its documentation for any purpose and without fee or royalty is hereby granted, provided that the above copyright notice appear in all copies and that both that copyright notice and this permission notice appear in supporting documentation or portions thereof, including modifications, that you make. THE AUTHOR MARC-ANDRE LEMBURG DISCLAIMS ALL WARRANTIES WITH REGARD TO THIS SOFTWARE, INCLUDING ALL IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS, IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR ANY SPECIAL, INDIRECT OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR PROFITS, WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS ACTION, ARISING OUT OF OR IN CONNECTION WITH THE USE OR PERFORMANCE OF THIS SOFTWARE ! """#" __version__ = "0.2" import exceptions,string,types class DatabaseTypeError(exceptions.TypeError): pass class DatabaseInfo: def __init__(self): pass def sqlquote(data, replace=string.replace,type=type,StringType=types.StringType, FloatType=types.FloatType,LongType=types.LongType, IntType=types.IntType,str=str): """ Convert data to a string and apply proper SQL quoting to it. """ if type(data) is StringType: pass elif type(data) is FloatType: return '%.30e' % data elif type(data) is IntType: return str(data) elif type(data) is LongType: data = str(data) if data[-1] == 'L': data = data[:-1] return data else: data = str(data) data = replace(data, "\\", "\\\\") data = replace(data, "\0", "\\0") data = replace(data, "\'", "\\\'") data = replace(data, "\"", "\\\"") return "'%s'" % data def stringcolumn(self, name, maxlength=254, padding=0, nullable=0, default=None, casesensitive=0): """ Returns a column definition for a string like column type having the given properties. maxlength gives the maximum length of the column in bytes. padding means that values written to the database are right padded with spaces. When reading these values, the database may or may not remove the trailing spaces. When padding is true, the column will have a fixed size in the database. nullable defines whether the column should be allowed to have NULL values or not. Default is not to allows NULL values. Some databases also allow providing default values which are used in case a not nullable column would have to store a NULL value. casesensitive defines how sorting and searching should be done on the column. Binary data should always ba stored in casesensitive columns. """ if padding: raise DatabaseTypeError,\ 'padding of strings not supported' options = '' if maxlength < 256: if padding: coltype = 'char(%i)' % maxlength else: coltype = 'varchar(%i)' % maxlength if casesensitive: coltype = coltype + ' binary' elif maxlength < 65536: if casesensitive: coltype = 'blob' else: coltype = 'text' elif maxlength < 167772152: if casesensitive: coltype = 'mediumblob' else: coltype = 'mediumtext' elif maxlength <= 4294967296L: if casesensitive: coltype = 'mediumblob' else: coltype = 'mediumtext' else: raise DatabaseTypeError,\ 'string size not supported' s = '%s %s' % (name, coltype) if not nullable: s = s + ' not null' if default is not None: s = s + ' default %s' % self.sqlquote(default) return s def datetimecolumn(self, name, date=1, time=1, nullable=0, default=None): """ Returns a column definition for a date/time like column type having the given properties. If date is true, the date part of a date/time value is stored in the databases. If time is true, the date part of a date/time value is stored in the databases. Default is to store both date and time part in the column. nullable defines whether the column should be allowed to have NULL values or not. Default is not to allows NULL values. Some databases also allow providing default values which are used in case a not nullable column would have to store a NULL value. """ if date and time: return '%s datetime' % name elif date: return '%s date' % name elif time: return '%s time' % name if not nullable: s = s + ' not null' if default is not None: s = s + ' default %s' % self.sqlquote(default) return s def integercolumn(self, name, maxvalue=2147483647, signed=1, nullable=0, default=None): """ Returns a column definition for an integer like column type having the given properties. maxvalue defines the maximum number which should be representable by the column. signed states whether the column should be able to accept negative values or not (default is support signed values). nullable defines whether the column should be allowed to have NULL values or not. Default is not to allows NULL values. Some databases also allow providing default values which are used in case a not nullable column would have to store a NULL value. """ if maxvalue < 0: maxvalue = -maxvalue - 1 if signed: if maxvalue < 128: return 'tinyint' elif maxvalue < 32768: return 'smallint' elif maxvalue < 8388608: return 'mediumint' elif maxvalue <= 2147483647: return 'int' elif maxvalue < 9223372036854775808L: return 'bigint' else: raise DatabaseTypeError,\ 'integer range not supported' else: if maxvalue < 256: return 'tinyint unsigned' elif maxvalue < 65536: return 'smallint unsigned' elif maxvalue < 16777216: return 'mediumint unsigned' elif maxvalue < 4294967296L: return 'int unsigned' elif maxvalue < 18446744073709551616L: return 'bigint unsigned' else: raise DatabaseTypeError,\ 'integer range not supported' if not nullable: s = s + ' not null' if default is not None: s = s + ' default ' + self.sqlquote(default) return s def floatcolumn(self, name, maxexp=38, precision=None, scale=None, packed=1, nullable=0, default=None): """ Returns a column definition for a float like column type having the given properties. maxexp defines the maximum exponent of the numbers which should be representable by the column, i.e. maxexp=38 gives you a float range of at least -1e38 -> -1e-38, 0, 1e-38 -> 1e38. The range may be further constrained by providing precision (the total number of digits in the float) and scale (the number of digits to the right of the decimal point). scale may only be given together with a precision value. packed defines whether the column should store the floats using an unpacked character representation (false) or a binary format (true). Default is to use a packed version. Precision and scale default to 5 and 0 resp. when the unpacked format is used. nullable defines whether the column should be allowed to have NULL values or not. Default is not to allows NULL values. Some databases also allow providing default values which are used in case a not nullable column would have to store a NULL value. """ if maxexp < 0: maxexp = -maxexp if precision is not None: try: precision = int(precision) except (ValueError, TypeError): raise DatabaseTypeError,\ 'precision must be an integer' if precision < 0: raise DatabaseTypeError,\ 'precision negative' if precision > 31: raise DatabaseTypeError,\ 'precision too large' if scale is not None: try: scale = int(scale) except (ValueError, TypeError): raise DatabaseTypeError,\ 'scale must be an integer' if scale < 0: raise DatabaseTypeError,\ 'scale negative' if scale > 31: raise DatabaseTypeError,\ 'scale too large' if not packed: if maxexp > 308: raise DatabaseTypeError,\ 'float range not supported' if precision is None: precision = 5 if scale is None: scale = 0 return 'decimal(%i,%i)' % (precision, scale) if maxexp <= 38 : return 'float' elif maxexp <= 308: return 'double' else: raise DatabaseTypeError,\ 'float range not supported' if not nullable: s = s + ' not null' if default is not None: s = s + ' default ' + self.sqlquote(default) return s # The base class provides information valid for MySQL MySQLInfo = DatabaseInfo ### Testing if __name__ == '__main__': info = MySQLInfo()