Package edu.uiuc.ncsa.security.storage.sql

Class SQLStore<V extends Identifiable>

  • All Implemented Interfaces:
    Store<V>, Map<Identifier,​V>
    Direct Known Subclasses:
    MonitoredSQLStore, SASSQLStore
    public abstract class SQLStore<V extends Identifiable>
extends SQLDatabase
implements Store<V>
    Top-level SQL store object. A store is simply a logical analog of a hash table, where the key is the primary key. This in practice may front multiple tables. This implements several of the basic operations. You need to implement a couple of methods and supply a Table that models the storage and a MapConverter that allows you to turn a java object's properties into a map -- then you should be in business for using an SQL backend. All of these statements are SQL 2003 compliant and should work without change for all major vendors. This class also maintains a ConnectionPoolto a database.

    Created by Jeff Gaynor
    on Mar 12, 2010 at 12:58:14 PM

    • Constructor Detail

      • SQLStore

        public SQLStore()

    • Method Detail

      • create

        public V create()
        Description copied from interface: Store
        Create a new object of the given type. This is not in the store until it is registered. Attempts to update the object should throw an exception. Note that this allows for a separation of creation semantics. Some objects require specific initialization before saving
        Specified by:
        create in interface Store<V extends Identifiable>
        Returns:

      • getAll

        public List<V> getAll()
        This will get every entry in the database. For sparing use since this may be a huge load.
        Specified by:
        getAll in interface Store<V extends Identifiable>
        Returns:

      • update

        public void update​(V value)
        For an existing entry in the store. This will select it based on the primary key and change all other values.
        Specified by:
        update in interface Store<V extends Identifiable>
        Parameters:
        value -

      • update

        public void update​(V value,
                   boolean existenceChecked)

      • populate

        public void populate​(ColumnMap map,
                     V t)
        Take a *new* value and populate it from the given mapping of column names and values. All values must be accounted for since the result set generally will come from a SELECT * FROM...
        Parameters:
        map -
        t -

      • depopulate

        public ColumnMap depopulate​(V t)
                     throws SQLException
        Takes the object, V and returns a map of column name, value. This is used to construct various statements This is where the columns and object properties are put in correspondence. We could try to do this with some sort of introspection, but that is very, very slow and not always clear on how it should be done.
        Parameters:
        t -
        Returns:
        Throws:
        SQLException

      • save

        public void save​(V value)
        Description copied from interface: Store
        Saves an object. This bridges the gap between SQL stores update and insert commands. Implementations should check if the object already exists in the store and issue an appropriate call.
        Specified by:
        save in interface Store<V extends Identifiable>

      • doRegisterStatement

        public void doRegisterStatement​(PreparedStatement stmt,
                                V value)
                         throws SQLException
        Sets the values in the PreparedStatement to those in the value. This allows other utilities to create batch statements for this store.
        Parameters:
        stmt -
        value -
        Throws:
        SQLException

      • register

        public void register​(V value)
        Description copied from interface: Store
        Almost Identical to put(K,V) but since the object should have an identifier, passing along the key is redundant. This persists the object in the store. Note that this returns void since the contract assumes that this is not registered. If the object is registered an exception should be thrown. Generally use save(V).
        Specified by:
        register in interface Store<V extends Identifiable>

      • get

        public V get​(Object o)
        Retrieve a single row from a table then populate an instance.
        Note: If you need to jazz this up, it is probably better to override the SQLDatabase.rsToMap(java.sql.ResultSet) method in this class. For instance, if the select statement is a join and there are multiple rows to process. The basic version of this class presupposed one row per object, but there is no reason this cannot be extended.
        Specified by:
        get in interface Map<Identifier,​V extends Identifiable>
        Parameters:
        o -
        Returns:

      • search

        public List<V> search​(String key,
                      String condition,
                      boolean isRegEx,
                      List<String> attr)
        Description copied from interface: Store
        Return a subset of all the attributes. For non-SQL stores performance may be slow.
        Specified by:
        search in interface Store<V extends Identifiable>
        Returns:

      • search

        public List<V> search​(String key,
                      boolean isNull)
        Description copied from interface: Store
        Searches if a single column and returns all of the null or non-null elements
        Specified by:
        search in interface Store<V extends Identifiable>
        Returns:

      • search

        public List<V> search​(String key,
                      String condition,
                      boolean isRegEx)
        Description copied from interface: Store
        Allows for searching via a reg ex. Note that this may be very expensive for certain stores!
        Specified by:
        search in interface Store<V extends Identifiable>
        Returns:

      • getTable

        public Table getTable()

      • size

        public int size​(boolean includeVersions)
        Specified by:
        size in interface Store<V extends Identifiable>

      • size

        protected int size​(String tablename,
                   boolean includeVersions)
        Utility that gets the total number of rows in a given table, given the name.
        Parameters:
        tablename -
        Returns:

      • removeByID

        public boolean removeByID​(List<Identifier> ids)
        remove a collection of items by primary key. This uses SQL batching and should be used for large sets. remove(List) takes a list of objects and makes a very long list of things to remove.
        Specified by:
        removeByID in interface Store<V extends Identifiable>
        Parameters:
        ids -
        Returns:

      • putAll

        public void putAll​(Map<? extends Identifier,​? extends V> m)
        A terrifically inefficient way to add these since it loops. If you need this to work better, override and optimize.
        Specified by:
        putAll in interface Map<Identifier,​V extends Identifiable>
        Parameters:
        m -

      • values

        public Collection<V> values()
        Again, this is basic functionality for the map interface. Do you really need to get everything in the database? If the database is large, this might fail for various unrelated reasons. If you really need to use a call like this, then you should probably over-ride it and optimize, say with partial retrievals or some such.
        Specified by:
        values in interface Map<Identifier,​V extends Identifiable>
        Returns:

      • checkColumns

        public void checkColumns()
                  throws SQLException
        When invoked this will loop through the columns of the table and add columns as needed with the correct type. NOTE that this should only be run once as a utility at, say, servlet loading time before any data access can occur. Also, all the added columns are allowed to be null. You will have to change that (along with setting a defaul) if you do not want that.
        Throws:
        SQLException

      • checkTable

        public void checkTable()

      • getCreationTSField

        public abstract String getCreationTSField()

      • getMostRecentStatement

        protected String getMostRecentStatement​(String attributes,
                                        boolean desc)

      • getDerbyMostRecent

        protected String getDerbyMostRecent​(String attrbutes,
                                    boolean desc)

      • crappySQLParser

        public static List<String> crappySQLParser​(String fileName)
        This is to take an SQL script for e.g. creating a database and return a list of statements. It is not a clever parser at all. It strips out comments and anything that ends with a ; is a statement. Mostly the assumption is that this is a set of table create statements and ceate index statements. Anything else is at your own risk.
        Parameters:
        fileName - either the name of a resource or the fully qualified path to the file.
        Returns:
        Throws:
        Throwable