Return a new SQLarray from a SELECT selection.

        This method is useful to build complicated selections and
        essentially new tables from existing data. The result of the
        SQL query is stored as a new table in the database. By
        default, a unique name is created but this can be overridden
        with the *name* keyword.

        :Arguments:
           *SQL*
               SQL ``SELECT`` query string. A leading ``SELECT * FROM __self__ WHERE``
               can be omitted (see examples below). The SQL is scrubbed and only data
               up to the first semicolon is used (note that this means that there
               cannot even be a semicolon in quotes; if this is a problem, file a bug
               report and it might be changed).
        :Keywords:
           *name*
               name of the table, ``None`` autogenerates a name unique
               to this query. *name* may not refer to the parent table itself.
               [``None``]
           *parameters*
               tuple of values that are safely  interpolated into subsequent ``?``
               characters in the SQL string
           *force*
               If ``True`` then an existing table of the same *name* is ``DROP``ped
               first. If ``False`` and the table already exists then *SQL* is ignored
               and a :class:`SQLarray` of the existing table *name* is returned.
               [``False``]

         :Returns: a :class:`SQLarray` referring to the table *name*
                   in the database; it also inherits the :attr:`SQLarray.dbfile`

        Examples::

                s = SQLarray.selection('a > 3')
                s = SQLarray.selection('a > ?', (3,))
                s = SQLarray.selection('SELECT * FROM __self__ WHERE a > ? AND b < ?', (3, 10))

def selection(self, SQL, parameters=None, **kwargs):
        """Return a new SQLarray from a SELECT selection.

        This method is useful to build complicated selections and
        essentially new tables from existing data. The result of the
        SQL query is stored as a new table in the database. By
        default, a unique name is created but this can be overridden
        with the *name* keyword.

        :Arguments:
           *SQL*
               SQL ``SELECT`` query string. A leading ``SELECT * FROM __self__ WHERE``
               can be omitted (see examples below). The SQL is scrubbed and only data
               up to the first semicolon is used (note that this means that there
               cannot even be a semicolon in quotes; if this is a problem, file a bug
               report and it might be changed).
        :Keywords:
           *name*
               name of the table, ``None`` autogenerates a name unique
               to this query. *name* may not refer to the parent table itself.
               [``None``]
           *parameters*
               tuple of values that are safely  interpolated into subsequent ``?``
               characters in the SQL string
           *force*
               If ``True`` then an existing table of the same *name* is ``DROP``ped
               first. If ``False`` and the table already exists then *SQL* is ignored
               and a :class:`SQLarray` of the existing table *name* is returned.
               [``False``]

         :Returns: a :class:`SQLarray` referring to the table *name*
                   in the database; it also inherits the :attr:`SQLarray.dbfile`

        Examples::

                s = SQLarray.selection('a > 3')
                s = SQLarray.selection('a > ?', (3,))
                s = SQLarray.selection('SELECT * FROM __self__ WHERE a > ? AND b < ?', (3, 10))

        """
        # TODO: under development
        # - could use VIEW

        force = kwargs.pop('force', False)

        # pretty unsafe... I hope the user knows what they are doing
        # - only read data to first semicolon
        # - here should be input scrubbing...
        safe_sql = re.match(r'(?P<SQL>[^;]*)',SQL).group('SQL')

        if re.match(r'\s*SELECT.*FROM',safe_sql,flags=re.IGNORECASE):
            _sql = safe_sql
        else:
            # WHERE clause only
            _sql = """SELECT * FROM __self__ WHERE """+str(safe_sql)
        # (note: MUST replace __self__  before md5!)
        _sql = _sql.replace('__self__', self.name)
        # unique name for table (unless user supplied... which could be 'x;DROP TABLE...')
        newname = kwargs.pop('name', 'selection_'+md5(_sql).hexdigest())

        if newname in ("__self__", self.name):
            raise ValueError("Table name %(newname)r cannot refer to the parent table itself." % vars())
        has_newname = self.has_table(newname)

        c = self.cursor

        if has_newname and force:
            c.execute("DROP TABLE %(newname)s" % vars())
            has_newname = False

        if not has_newname:
            # create table directly
            # SECURITY: unsafe tablename !!!! (but cannot interpolate?)
            _sql = "CREATE TABLE %(newname)s AS " % vars() + _sql
            if parameters is None:
                c.execute(_sql)              # no sanity checks!
            else:
                c.execute(_sql, parameters)  # no sanity checks; params should be tuple

        # associate with new table in db
        return SQLarray(newname, None, dbfile=self.dbfile, connection=self.connection)