SqlAction (OMERO (OME Remote Objects) Server)

All Known Implementing Classes:

PostgresSqlAction, SqlAction.Impl
```
public interface SqlAction
```
Single wrapper for all JDBC activities. This interface is meant to replace all uses of both SimpleJdbcTemplate and Session.createSQLQuery(String) for the entire OMERO code base.

Since:

4.2.1

Author:

Josh Moore, josh at glencoesoftware.com

See Also:
#73, #2684

Nested Class Summary

Nested Classes
Modifier and Type	Interface and Description
`static class`	`SqlAction.DeleteLog` Record-class which matches _fs_deletelog.
`static class`	`SqlAction.IdRowMapper`
`static class`	`SqlAction.Impl` Base implementation which can be used
`static class`	`SqlAction.LoggingSqlAction`
`static class`	`SqlAction.StringRowMapper`

Method Summary

Methods
Modifier and Type	Method and Description
`boolean`	`activeSession(String sessionUUID)` Returns true if the given string is the UUID of a session that is currently active.
`void`	`addMessageWithinDbPatchEnd(String version, int patch, String message)` Add a unique message to the DB patch table within the current patch.
`void`	`addMessageWithinDbPatchStart(String version, int patch, String message)` Add a unique message to the DB patch table within the current patch.
`int`	`changeGroupPermissions(Long id, Long internal)`
`int`	`changeTablePermissionsForGroup(String table, Long id, Long internal)`
`boolean`	`clearPermissionsBit(String table, long id, int bit)` Sets the given permissions bit to `0`.
`int`	`closeNode(String uuid)`
`int`	`closeNodeSessions(String uuid)`
`int`	`closeSessions(String uuid)`
`String`	`configValue(String name)`
`long`	`countFormat(String name)`
`String`	`createIdsTempTable(Collection<Long> ids)` Creates a temporary table filled with the given ids and returns its name.
`void`	`createInsertTrigger(String name, String table, String procedure)` Creates an insert trigger of the given name, for the given table, with the given procedure.
`void`	`createSavepoint(String savepoint)`
`Set<String>`	`currentUserNames()`
`long`	`currValue(String segmentName)`
`String`	`dbUuid()`
`String`	`dbVersion()`
`void`	`deferConstraints()`
`int`	`delConfigValue(String name)`
`void`	`delCurrentEventLog(String key)`
`int`	`deleteRepoDeleteLogs(SqlAction.DeleteLog template)` Delete all `SqlAction.DeleteLog` entries which match all of the non-null fields provided in the template.
`List<Long>`	`fileIdsInDb(String uuid, Set<String> mimetypes)` Return all IDs matching the given mimetypes, or all IDs if mimetypes is null.
`String`	`fileRepo(long fileId)`
`List<Long>`	`filterFileIdsByRepo(String uuid, List<Long> fileIds)` Find the original file IDs among those given that are in the given repository.
`List<SqlAction.DeleteLog>`	`findRepoDeleteLogs(SqlAction.DeleteLog template)` Find all `SqlAction.DeleteLog` entries which match all of the non-null fields provided in the template.
`Long`	`findRepoFile(String uuid, String dirname, String basename)` Calls `#findRepoFile(String, String, String, Set)` passing null.
`Long`	`findRepoFile(String uuid, String dirname, String basename, Set<String> mimetypes)` Lookup the id of an `OriginalFile` in a given repository or return null if none is found.
`Long`	`findRepoFile(String uuid, String dirname, String basename, String mimetype)` Calls `#findRepoFile(String, String, String, Set)`
`String`	`findRepoFilePath(String uuid, long id)`
`List<Long>`	`findRepoFiles(String repoUuid, String dirname)` Return a list of original file ids that all have a path value matching the passed dirname in the given repository.
`Map<String,Long>`	`findRepoFiles(String uuid, String dirname, List<String> basenames, Set<String> mimetypes)` Like `findRepoFile(String, String, String, Set)`, but queries in bulk and returns a map for the found IDs.
`Long`	`findRepoImageFromPixels(long id)`
`List<Long>`	`findRepoPixels(String uuid, String dirname, String basename)`
`String`	`findRepoRootPath(String uuid)` Find the path of the repository root.
`List<Long>`	`getDeletedIds(String entityType)`
`List<Object[]>`	`getEventLogPartitions(Collection<String> types, Collection<String> actions, long offset, long limit)` Loads up to "limit" event logs using partioning so that only the last event log of a particular (type, id) pair is returned.
`float`	`getEventLogPercent(String key)` Returns the percent (e.g. 0-100%) as calculated by the number of rows represented as completed by the configuration table row of this key divided by the total number of rows in the event log.
`Map<String,Long>`	`getGroupIds(Collection<String> names)`
`long`	`getGroupPermissions(long id)` Returns the permissions for the given group id.
`List<Long>`	`getLdapExperimenters()` Gets the experimenters who have the `ldap` attribute enabled.
`String`	`getPasswordHash(Long experimenterID)`
`List<String>`	`getPixelsNamePathRepo(long id)` Retrieves the name, path and repo for the given pixels set.
`List<Long>`	`getShapeIds(long roiId)`
`Map<Long,byte[]>`	`getShareData(List<Long> ids)` Returns a map of Share ID to Share data blob.
`Collection<String>`	`getUserEmailsByGroup(long groupId)` Load all the non-empty email addresses for users in a given group.
`List<String>`	`getUserGroups(String userName)`
`Long`	`getUserId(String userName)` Get the user's ID
`String`	`getUsername(long userId)` Get the user's name
`ExperimenterGroup`	`groupInfoFor(String table, long id)` Return a mostly unloaded `ExperimenterGroup` object containing only the id, name, and permissions.
`boolean`	`hasUnicodeUnits()`
`int`	`insertFormat(String name)`
`void`	`insertLogs(List<Object[]> batchData)`
`int`	`insertSession(Map<String,Object> params)`
`int`	`isFileInRepo(String uuid, long id, Set<String> mimetypes)`
`boolean`	`isLdapExperimenter(Long id)` Checks whether the specified experimenter ID has the `ldap` flag set.
`List<long[]>`	`nextPixelsDataLogForRepo(String repo, long lastEventId, int howmany)` Returns arrays of longs for the following SQL return values: `experimenter, eventlog, entityid as pixels, rownumber` The oldest N eventlogs with action = "PIXELDATA" and entitytype = "ome.model.core.Pixels" is found per user and returned.
`Long`	`nextSessionId()`
`long`	`nextValue(String segmentName, int incrementSize)` The implementation of this method guarantees that even if the current transaction fails that the value found will not be used by another transaction.
`long`	`nodeId(String internal_uuid)`
`Date`	`now()`
`void`	`prepareSession(long eventId, long userId, long groupId)` Stores the current event context information in a temporary table so that triggers can make use of them.
`void`	`refreshEventLogFromUpdatedAnnotations()` Convert the _updated_annotations table to REINDEX entries in the event log.
`void`	`releaseSavepoint(String savepoint)`
`int`	`removePassword(Long id)`
`Map<String,Object>`	`repoFile(long value)`
`int`	`repoScriptCount(String uuid, Set<String> mimetypes)`
`String`	`rewriteHql(String query, String key, Object value)` Allows the specific database implementations a chance to modify queries.
`List<Map<String,Object>>`	`roiByImageAndNs(long imageId, String ns)`
`void`	`rollbackSavepoint(String savepoint)`
`String`	`scriptRepo(long fileId, Set<String> mimetypes)` Similar to `fileRepo(long)`, but only returns values for files which are also scripts.
`long`	`selectCurrentEventLog(String key)`
`Long`	`sessionId(String uuid)`
`void`	`setCurrentEventLog(long id, String key)`
`void`	`setFileRepo(Collection<Long> ids, String repoId)`
`boolean`	`setPermissionsBit(String table, long id, int bit)` Sets the given permissions bit to `1`.
`void`	`setPixelsNamePathRepo(long pixId, String name, String path, String repoId)`
`long`	`setStatsInfo(Channel ch, StatsInfo si)`
`boolean`	`setUserPassword(Long experimenterID, String password)`
`int`	`synchronizeJobs(List<Long> ids)`
`int`	`updateConfiguration(String key, String value)`
`int`	`updateOrInsertConfigValue(String name, String value)`

- Method Detail
  - prepareSession
```
void prepareSession(long eventId,
                  long userId,
                  long groupId)
```
    Stores the current event context information in a temporary table so that triggers can make use of them.
    
    Parameters:
    eventId -
    userId -
    groupId -
  - rewriteHql
```
String rewriteHql(String query,
                String key,
                Object value)
```
    Allows the specific database implementations a chance to modify queries.
    
    Parameters:
    query - String query (non-null) which is in effect.
    key - Key of the argument e.g. (:ids)
    value - value which has been passed in for that parameter.
    
    Returns:
    Returns a query replacement.
    See Also:
    ticket:3961
  - createIdsTempTable
```
String createIdsTempTable(Collection<Long> ids)
```
    Creates a temporary table filled with the given ids and returns its name. The table is only available for the period if the transaction.
  - createInsertTrigger
```
void createInsertTrigger(String name,
                       String table,
                       String procedure)
```
    Creates an insert trigger of the given name, for the given table, with the given procedure. No error handling is performed.
  - activeSession
```
boolean activeSession(String sessionUUID)
```
    Returns true if the given string is the UUID of a session that is currently active.
    
    Parameters:
    sessionUUID - NOT NULL.
    
    Returns:
  - getGroupPermissions
```
long getGroupPermissions(long id)
```
    Returns the permissions for the given group id.
  - groupInfoFor
```
ExperimenterGroup groupInfoFor(String table,
                             long id)
```
    Return a mostly unloaded ExperimenterGroup object containing only the id, name, and permissions.
  - fileRepo
```
String fileRepo(long fileId)
```
  - scriptRepo
```
String scriptRepo(long fileId,
                Set<String> mimetypes)
```
    Similar to fileRepo(long), but only returns values for files which are also scripts. Null may be returned
    
    Parameters:
    fileId -
    mimetypes - null implies all files are checked
    
    Returns:
  - synchronizeJobs
```
int synchronizeJobs(List<Long> ids)
```
  - findRepoFile
```
Long findRepoFile(String uuid,
                String dirname,
                String basename)
```
    Calls #findRepoFile(String, String, String, Set) passing null.
  - findRepoFile
```
Long findRepoFile(String uuid,
                String dirname,
                String basename,
                String mimetype)
```
    Calls #findRepoFile(String, String, String, Set)
  - findRepoFile
```
Long findRepoFile(String uuid,
                String dirname,
                String basename,
                Set<String> mimetypes)
```
    Lookup the id of an OriginalFile in a given repository or return null if none is found.
    
    Parameters:
    uuid - The UUID of the repository (originalfile.sha1)
    dirname - the full directory path minus the file name.
    basename - i.e. the filename without any directory path
    mimetype - if null, then no mimetype query fragement will be added.
    
    Returns:
    null if no OriginalFile is found, otherwise the id.
  - findRepoFiles
```
Map<String,Long> findRepoFiles(String uuid,
                             String dirname,
                             List<String> basenames,
                             Set<String> mimetypes)
```
    Like findRepoFile(String, String, String, Set), but queries in bulk and returns a map for the found IDs.
  - findRepoFiles
```
List<Long> findRepoFiles(String repoUuid,
                       String dirname)
```
    Return a list of original file ids that all have a path value matching the passed dirname in the given repository.
    
    Parameters:
    uuid -
    dirname -
    
    Returns:
    possibly empty list of ids.
  - findRepoDeleteLogs
```
List<SqlAction.DeleteLog> findRepoDeleteLogs(SqlAction.DeleteLog template)
```
    Find all SqlAction.DeleteLog entries which match all of the non-null fields provided in the template.
    
    Parameters:
    template - non-null.
    
    Returns:
  - deleteRepoDeleteLogs
```
int deleteRepoDeleteLogs(SqlAction.DeleteLog template)
```
    Delete all SqlAction.DeleteLog entries which match all of the non-null fields provided in the template.
    
    Parameters:
    template - not-null
    
    Returns:
    the number of rows deleted.
  - findRepoRootPath
```
String findRepoRootPath(String uuid)
```
    Find the path of the repository root.
    
    Parameters:
    uuid - a repository UUID
    
    Returns:
    the repository root
  - findRepoFilePath
```
String findRepoFilePath(String uuid,
                      long id)
```
  - findRepoPixels
```
List<Long> findRepoPixels(String uuid,
                        String dirname,
                        String basename)
```
  - findRepoImageFromPixels
```
Long findRepoImageFromPixels(long id)
```
  - repoScriptCount
```
int repoScriptCount(String uuid,
                  Set<String> mimetypes)
```
    Parameters:
    uuid - repository identifier
    mimetypes - file mimetypes to check; if null, all files;
  - nextSessionId
```
Long nextSessionId()
```
  - fileIdsInDb
```
List<Long> fileIdsInDb(String uuid,
                     Set<String> mimetypes)
```
    Return all IDs matching the given mimetypes, or all IDs if mimetypes is null.
  - filterFileIdsByRepo
```
List<Long> filterFileIdsByRepo(String uuid,
                             List<Long> fileIds)
```
    Find the original file IDs among those given that are in the given repository.
    
    Parameters:
    uuid - a repository UUID
    fileIds - IDs of original files
    
    Returns:
    those IDs among those given whose original files are in the given repository
  - repoFile
```
Map<String,Object> repoFile(long value)
```
  - nextPixelsDataLogForRepo
```
List<long[]> nextPixelsDataLogForRepo(String repo,
                                    long lastEventId,
                                    int howmany)
```
    Returns arrays of longs for the following SQL return values: experimenter, eventlog, entityid as pixels, rownumber The oldest N eventlogs with action = "PIXELDATA" and entitytype = "ome.model.core.Pixels" is found per user and returned. Multiple eventlogs are returned per user in order to support multi-threading. Duplicate pixel ids are stripped.
  - countFormat
```
long countFormat(String name)
```
  - insertFormat
```
int insertFormat(String name)
```
  - closeSessions
```
int closeSessions(String uuid)
```
  - closeNodeSessions
```
int closeNodeSessions(String uuid)
```
  - closeNode
```
int closeNode(String uuid)
```
  - nodeId
```
long nodeId(String internal_uuid)
```
  - insertSession
```
int insertSession(Map<String,Object> params)
```
  - sessionId
```
Long sessionId(String uuid)
```
  - isFileInRepo
```
int isFileInRepo(String uuid,
               long id,
               Set<String> mimetypes)
```
    Parameters:
    uuid - Repository identifier
    id - file identifier
    mimetypes - Set of mimetypes to check; if null, all files.
  - removePassword
```
int removePassword(Long id)
```
  - now
```
Date now()
```
  - updateConfiguration
```
int updateConfiguration(String key,
                      String value)
```
  - dbVersion
```
String dbVersion()
```
  - configValue
```
String configValue(String name)
```
  - delConfigValue
```
int delConfigValue(String name)
```
  - updateOrInsertConfigValue
```
int updateOrInsertConfigValue(String name,
                            String value)
```
  - dbUuid
```
String dbUuid()
```
  - selectCurrentEventLog
```
long selectCurrentEventLog(String key)
```
  - getEventLogPercent
```
float getEventLogPercent(String key)
```
    Returns the percent (e.g. 0-100%) as calculated by the number of rows represented as completed by the configuration table row of this key divided by the total number of rows in the event log. Since this method executes 2 counts over the event log table, it can take a significant amount of time.
    
    Parameters:
    key - PersistentEventLogLoader key for lookup in the configuration table
    
    Returns:
    float value between 0 and 100 of the percent completed
  - getEventLogPartitions
```
List<Object[]> getEventLogPartitions(Collection<String> types,
                                   Collection<String> actions,
                                   long offset,
                                   long limit)
```
    Loads up to "limit" event logs using partioning so that only the last event log of a particular (type, id) pair is returned. The contents of the object array are:
    1. the id of the event log (Long)
    2. the entity type of the event log (String)
    3. the entity id of the event log (Long)
    4. the action of the event log (String)
    5. the number of skipped event logs (Integer)
    Parameters:
    types - Collection of entityType strings which should be queried
    actions - Collection of ACTION strings which should be queried
    offset - Offset to the row which should be queried first
    limit - Maximum number of rows (after partionting) which should be returned.
    
    Returns:
  - setCurrentEventLog
```
void setCurrentEventLog(long id,
                      String key)
```
  - delCurrentEventLog
```
void delCurrentEventLog(String key)
```
  - refreshEventLogFromUpdatedAnnotations
```
void refreshEventLogFromUpdatedAnnotations()
```
    Convert the _updated_annotations table to REINDEX entries in the event log.
  - nextValue
```
long nextValue(String segmentName,
             int incrementSize)
```
    The implementation of this method guarantees that even if the current transaction fails that the value found will not be used by another transaction. Database implementations can choose whether to do this at the procedure level or by using transaction PROPAGATION settings in Java.
    
    Parameters:
    segmentName -
    incrementSize -
    
    Returns:
    See Also:
    ticket:3697, ticket:3253
  - currValue
```
long currValue(String segmentName)
```
  - insertLogs
```
void insertLogs(List<Object[]> batchData)
```
  - roiByImageAndNs
```
List<Map<String,Object>> roiByImageAndNs(long imageId,
                                       String ns)
```
  - getShapeIds
```
List<Long> getShapeIds(long roiId)
```
  - setUserPassword
```
boolean setUserPassword(Long experimenterID,
                      String password)
```
  - getPasswordHash
```
String getPasswordHash(Long experimenterID)
```
  - getUserId
```
Long getUserId(String userName)
```
    Get the user's ID
    
    Parameters:
    userName - the user's name
    
    Returns:
    their ID, or null if they cannot be found
  - getUsername
```
String getUsername(long userId)
```
    Get the user's name
    
    Parameters:
    userId - the user's ID
    
    Returns:
    their name, or null if they cannot be found
  - getUserEmailsByGroup
```
Collection<String> getUserEmailsByGroup(long groupId)
```
    Load all the non-empty email addresses for users in a given group.
    
    Parameters:
    group - id
    
    Returns:
    a non-null Collection of non-empty user email addresses.
  - getLdapExperimenters
```
List<Long> getLdapExperimenters()
```
    Gets the experimenters who have the ldap attribute enabled.
    
    Returns:
    a list of user IDs.
  - isLdapExperimenter
```
boolean isLdapExperimenter(Long id)
```
    Checks whether the specified experimenter ID has the ldap flag set.
    
    Parameters:
    id - The experimenter ID.
    
    Returns:
    true if the experimenter is an LDAP user; false otherwise.
  - getGroupIds
```
Map<String,Long> getGroupIds(Collection<String> names)
```
  - getUserGroups
```
List<String> getUserGroups(String userName)
```
  - setFileRepo
```
void setFileRepo(Collection<Long> ids,
               String repoId)
```
  - setPixelsNamePathRepo
```
void setPixelsNamePathRepo(long pixId,
                         String name,
                         String path,
                         String repoId)
```
  - setStatsInfo
```
long setStatsInfo(Channel ch,
                StatsInfo si)
```
  - getDeletedIds
```
List<Long> getDeletedIds(String entityType)
```
  - createSavepoint
```
void createSavepoint(String savepoint)
```
  - releaseSavepoint
```
void releaseSavepoint(String savepoint)
```
  - rollbackSavepoint
```
void rollbackSavepoint(String savepoint)
```
  - deferConstraints
```
void deferConstraints()
```
  - getShareData
```
Map<Long,byte[]> getShareData(List<Long> ids)
```
    Returns a map of Share ID to Share data blob.
    
    Parameters:
    ids - IDs of Shares for which data blobs are to be returned.
    
    Returns:
    map of ID to data blob.
  - getPixelsNamePathRepo
```
List<String> getPixelsNamePathRepo(long id)
                                   throws InternalException
```
    Retrieves the name, path and repo for the given pixels set. If the id is not found, null is returned.
    
    Throws:
    
    InternalException
  - currentUserNames
```
Set<String> currentUserNames()
```
  - changeGroupPermissions
```
int changeGroupPermissions(Long id,
                         Long internal)
```
  - changeTablePermissionsForGroup
```
int changeTablePermissionsForGroup(String table,
                                 Long id,
                                 Long internal)
```
  - hasUnicodeUnits
```
boolean hasUnicodeUnits()
```
    Returns:
    if the database's type system contains correctly encoded units of measure
  - addMessageWithinDbPatchStart
```
void addMessageWithinDbPatchStart(String version,
                                int patch,
                                String message)
```
    Add a unique message to the DB patch table within the current patch. This method marks the start of the corresponding DB adjustment process.
    
    Parameters:
    version - the version of the current DB
    patch - the patch of the current DB
    message - the new message to note
  - addMessageWithinDbPatchEnd
```
void addMessageWithinDbPatchEnd(String version,
                              int patch,
                              String message)
```
    Add a unique message to the DB patch table within the current patch. This method marks the end of the corresponding DB adjustment process.
    
    Parameters:
    version - the version of the current DB
    patch - the patch of the current DB
    message - the new message to note
  - setPermissionsBit
```
boolean setPermissionsBit(String table,
                        long id,
                        int bit)
```
    Sets the given permissions bit to 1. Note: Actually sets the bit to 1 in the value stored in the database, does not adopt the inverse convention associated with permissions flags.
    
    Parameters:
    table - the table in which to find the row
    id - the value of the table's id column that identifies the row to update
    bit - the bit number to set to 1, counting from 0 as the least significant bit
    
    Returns:
    if the row was found in the table, regardless of the given bit's previous value
  - clearPermissionsBit
```
boolean clearPermissionsBit(String table,
                          long id,
                          int bit)
```
    Sets the given permissions bit to 0. Note: Actually sets the bit to 0 in the value stored in the database, does not adopt the inverse convention associated with permissions flags.
    
    Parameters:
    table - the table in which to find the row
    id - the value of the table's id column that identifies the row to update
    bit - the bit number to set to 0, counting from 0 as the least significant bit
    
    Returns:
    if the row was found in the table, regardless of the given bit's previous value

Interface SqlAction

Nested Class Summary

Method Summary

Method Detail

prepareSession

rewriteHql

createIdsTempTable

createInsertTrigger

activeSession

getGroupPermissions

groupInfoFor

fileRepo

scriptRepo

synchronizeJobs

findRepoFile

findRepoFile

findRepoFile

findRepoFiles

findRepoFiles

findRepoDeleteLogs

deleteRepoDeleteLogs

findRepoRootPath

findRepoFilePath

findRepoPixels

findRepoImageFromPixels

repoScriptCount

nextSessionId

fileIdsInDb

filterFileIdsByRepo

repoFile

nextPixelsDataLogForRepo

countFormat

insertFormat

closeSessions

closeNodeSessions

closeNode

nodeId

insertSession

sessionId

isFileInRepo

removePassword

now

updateConfiguration

dbVersion

configValue

delConfigValue

updateOrInsertConfigValue

dbUuid

selectCurrentEventLog

getEventLogPercent

getEventLogPartitions

setCurrentEventLog

delCurrentEventLog

refreshEventLogFromUpdatedAnnotations

nextValue

currValue

insertLogs

roiByImageAndNs

getShapeIds

setUserPassword

getPasswordHash

getUserId

getUsername

getUserEmailsByGroup

getLdapExperimenters

isLdapExperimenter

getGroupIds

getUserGroups

setFileRepo

setPixelsNamePathRepo

setStatsInfo

getDeletedIds

createSavepoint

releaseSavepoint

rollbackSavepoint

deferConstraints

getShareData

getPixelsNamePathRepo

currentUserNames

changeGroupPermissions