Package io.levysworks.beans

Class DatabaseManager

java.lang.Object
io.levysworks.beans.DatabaseManager
@ApplicationScoped public class DatabaseManager extends Object
Database manager bean, used for connecting, and interacting with the database.

This class initializes a connection after all dependencies are injected

  • Constructor Details

    • DatabaseManager

      public DatabaseManager()

  • Method Details

    • getUserCount

      public int getUserCount() throws SQLException
      Queries the number of users present in the users table.
      Returns:
      the number of users in the database
      Throws:
      SQLException - if the users table doesn't exist or the connection is closed

    • getKeyCount

      public int getKeyCount() throws SQLException
      Queries the number of ssh keys present in the ssh_keys table.
      Returns:
      the number of ssh keys in the database
      Throws:
      SQLException - if the ssh_keys table doesn't exist or the connection is closed

    • getPendingRequestCount

      public int getPendingRequestCount() throws SQLException
      Queries the number of requests present in the requests table.
      Returns:
      the number of requests in the database
      Throws:
      SQLException - if the requests table doesn't exist or the connection is closed

    • getPendingRequestById

      public Request getPendingRequestById(int requestId) throws SQLException
      Queries a request by its id from the requests table
      Parameters:
      requestId - The id of the request in the requests table
      Returns:
      a Request containing the queried data
      Throws:
      SQLException - if the requests table doesn't exist or the connection is closed

    • getRequestsForTemplate

      public List<CompositeUserData> getRequestsForTemplate() throws SQLException
      Queries a set of user and request information by joining the requests and users tables.

      The result contains combined user-request data including name, email, request ID, server, key type, and timestamp.

      Returns:
      a List of CompositeUserData objects representing the joined data.
      Throws:
      SQLException - if a database access error occurs or the query fails.

    • addPendingRequest

      public void addPendingRequest(String user_uuid, String server, String public_key) throws SQLException
      Inserts a new row into the requests table with the provided parameters
      Parameters:
      user_uuid - The UUID of the user request
      server - The server the user requested a key to
      public_key - The public key generated by the server
      Throws:
      SQLException - if a database access error occurs or the query fails.

    • addActiveKey

      public void addActiveKey(String user_uuid, String server, String public_key) throws SQLException, NoSuchAlgorithmException
      Inserts a new row into the ssh_keys table with the provided parameters

      Computes a fingerprint for the public key for the sql statement

      Parameters:
      user_uuid - UUID of the owner of the key
      server - The server, the key will be on
      public_key - The public key to add
      Throws:
      SQLException - if a database access error occurs or the query fails.
      NoSuchAlgorithmException - if the KeyGenerator.KeyHasher.generateFingerprint(java.lang.String) fails to compute the fingerprint of the public key

    • getKeysForTemplate

      public List<CompositeUserData> getKeysForTemplate() throws SQLException
      Queries a set of user and SSH key information by joining the ssh_keys and users tables.

      The result contains combined user–SSH key data including name, email, fingerprint, key type, issued date, valid until, UID, and server.

      Returns:
      a List of CompositeUserData objects representing the joined user and key data.
      Throws:
      SQLException - if a database access error occurs or the query fails.

    • getUsersForTemplate

      public List<CompositeUserData> getUsersForTemplate() throws SQLException
      Retrieves user data along with SSH key metadata by joining users and ssh_keys.

      Includes name, email, UUID, department, key count, and associated servers.

      Returns:
      a List of CompositeUserData with user and key summary.
      Throws:
      SQLException - if a database access error occurs.

    • getUserByUUID

      public CompositeUserData getUserByUUID(String uuid) throws SQLException
      Queries the users table for the user with the specified UUID.
      Parameters:
      uuid - The UUID of the queried user
      Returns:
      a CompositeUserData object containing the first name, last name, email, department and admin notes about the user.
      Throws:
      SQLException - if a database access error occurs.

    • getUserKeysByUUID

      public List<CompositeUserData> getUserKeysByUUID(String uuid) throws SQLException
      Queries the ssh_keys table for all SSH keys associated with the specified user UUID.
      Parameters:
      uuid - The UUID of the user
      Returns:
      a List of CompositeUserData objects, each containing a separate SSH key's data
      Throws:
      SQLException - if a database access error occurs.

    • removeKeyByUID

      public void removeKeyByUID(String server, String uid) throws SQLException
      Removes the key entry from the ssh_keys table where server and uid both match
      Parameters:
      server - The name of the server
      uid - The UID of the SSH Key
      Throws:
      SQLException - if a database access error occurs.

    • updateUser

      public void updateUser(String uuid, String first_name, String last_name, String email, String department, String notes) throws SQLException
      Updates the user with matching uuid in the users table
      Parameters:
      uuid - The updatable user's UUID
      first_name - The firs name to update to
      last_name - The last name to update to
      email - The email to update to
      department - The department to update to
      notes - The notes to update to
      Throws:
      SQLException - if a database access error occurs.

    • addNewLog

      public void addNewLog(String title, String message, Timestamp timestamp) throws SQLException
      Inserts a new row into the audit_log table with the provided parameters
      Parameters:
      title - Title of the log
      message - Message of the log
      timestamp - Timestamp of the log
      Throws:
      SQLException - if a database access error occurs.

    • getLogs

      public List<CompositeUserData> getLogs(int limit) throws SQLException
      Queries the audit_log table for the latest logs ordered descending by the timestamp column, limited to the specified amount.
      Parameters:
      limit - The amount of logs to return
      Returns:
      a List of CompositeUserData objects representing the log entries
      Throws:
      SQLException - if a database access error occurs

    • checkKeyExists

      public boolean checkKeyExists(String uid) throws SQLException
      Checks whether an SSH Key exists in the ssh_keys table with the specified UID
      Parameters:
      uid - The UID to check
      Returns:
      a boolean representing whether the key exists or not
      Throws:
      SQLException - if a database access error occurs

    • removePendingRequest

      public void removePendingRequest(int id) throws SQLException
      Removes a request from the requests table by its ID
      Parameters:
      id - The ID of the removable request
      Throws:
      SQLException - if a database access error occurs

    • addUser

      public void addUser(String first_name, String last_name, String email, String department, String notes) throws SQLException
      Inserts a new user into the users table with the specified parameters
      Parameters:
      first_name - The first name of the user
      last_name - The last name of the user
      email - The email of the user
      department - The department the user is in
      notes - The admin notes about this user
      Throws:
      SQLException - if a database access error occurs

    • removeUser

      public void removeUser(String uuid) throws SQLException
      Removes a user from the users table by their UUID
      Parameters:
      uuid - The UUID of the user
      Throws:
      SQLException - if a database access error occurs