Package org.noise_planet.noisemodelling.webserver.database

Class DatabaseManagement

java.lang.Object
org.noise_planet.noisemodelling.webserver.database.DatabaseManagement
public class DatabaseManagement extends Object
Handle the creation of datasource according to application configuration The Model of the Web Server

  • Field Details

  • Constructor Details

    • DatabaseManagement

      public DatabaseManagement()

  • Method Details

    • createH2DataSource

      public static com.zaxxer.hikari.HikariDataSource createH2DataSource(String databaseDirectory, String databaseName, String userName, String userPassword, String secureBaseEncryptionSecret, boolean initializeSpatial) throws SQLException
      Create H2Database datasource
      Parameters:
      databaseDirectory - Where to store the database
      databaseName - Name of the database
      userName - Admin username
      userPassword - Admin password
      secureBaseEncryptionSecret - Encryption database password, optional (empty)
      initializeSpatial - If true initialize H2GIS
      Returns:
      DataSource instance
      Throws:
      SQLException - If something wrong happened

    • getConnectionUrl

      @NotNull public static StringBuilder getConnectionUrl(String databaseDirectory, String databaseName, boolean databaseEncryption)
      Build H2 connection URL
      Parameters:
      databaseDirectory - Database directory
      databaseName - Database name
      databaseEncryption - True to enable encryption
      Returns:
      Connection URL

    • initializeServerDatabaseStructure

      public static void initializeServerDatabaseStructure(DataSource dataSource, Configuration configuration) throws SQLException
      Throws:
      SQLException

    • createServerDataBaseStructure

      public static void createServerDataBaseStructure(Connection connection) throws SQLException
      Throws:
      SQLException

    • getJWTSigningKey

      public static String getJWTSigningKey(DataSource serverDataSource) throws SQLException
      Retrieve the generated signing key of the server That key is used to sign the JWT tokens provided to the users If a malicious user tries to change the payload (ex. user identifier), then the token will not be valid
      Parameters:
      serverDataSource - data source
      Returns:
      JWTSigningKey
      Throws:
      SQLException - Something went wrong

    • getUserRoles

      public static List<String> getUserRoles(Connection connection, int userIdentifier) throws SQLException
      Get user roles
      Parameters:
      connection - Data source to the database
      userIdentifier - User identifier in the database (e.g., primary key)
      Returns:
      List of roles associated with the user
      Throws:
      SQLException - If something wrong happened

    • getUser

      public static User getUser(DataSource serverDataSource, int userIdentifier) throws SQLException
      Get user from database
      Parameters:
      serverDataSource - Data source to the database
      userIdentifier - User identifier in the database (e.g., primary key)
      Returns:
      User object with associated roles and details
      Throws:
      SQLException - If something wrong happened

    • getUser

      public static User getUser(Connection connection, int userIdentifier) throws SQLException
      Get user from database
      Parameters:
      connection - Data source to the database
      userIdentifier - User identifier in the database (e.g., primary key)
      Returns:
      User object with associated roles and details
      Throws:
      SQLException - If something wrong happened

    • getUser

      @NotNull public static User getUser(Connection connection, ResultSet rsUser) throws SQLException
      Throws:
      SQLException

    • addUser

      public static int addUser(Connection connection, String email, Role... roles) throws SQLException
      This method adds a new user with the provided email.

      It generates an expected token that would be provided in the url when associating to a TOTP generator This token is provided to the user with the server url by mail or other communication method.

      Parameters:
      connection - The database Connection object used to execute the query.
      email - The email of the user to be added.
      Returns:
      The token generated for this new user.
      Throws:
      SQLException - If the operation fails to add a user (i.e., no rows are affected in the "USERS" table).

    • getUserByRegisterToken

      public static int getUserByRegisterToken(Connection connection, String registerToken) throws SQLException
      Get user by register token
      Parameters:
      connection - The database Connection object
      registerToken - The registration token from URL
      Returns:
      User identifier or -1 if not found
      Throws:
      SQLException - If database error occurs

    • updateUserTotpToken

      public static void updateUserTotpToken(Connection connection, int userIdentifier, String totpToken) throws SQLException
      Updates the TOTP token for a user and clears their registerToken field.
      Parameters:
      connection - The database connection object.
      userIdentifier - The unique identifier of the user in the database.
      totpToken - The new Time-Based One-Time Password (TOTP) token to be assigned to the user.
      Throws:
      SQLException - If there's an error executing the SQL update or if no rows were affected, indicating that no user with the given identifier was found in the database.

    • getTotpSecretByUserEmail

      public static String getTotpSecretByUserEmail(Connection connection, String email) throws SQLException
      Get user by register token
      Parameters:
      connection - The database Connection object
      email - User email
      Returns:
      User TOTP_TOKEN or empty if not found
      Throws:
      SQLException - If database error occurs

    • getUserIdByUserEmail

      public static int getUserIdByUserEmail(Connection connection, String email) throws SQLException
      Get user id by using email
      Parameters:
      connection - The database Connection object
      email - User email
      Returns:
      User identifier or -1 if not found
      Throws:
      SQLException - If database error occurs

    • createJob

      public static int createJob(Connection connection, int userIdentifier, String jobScript) throws SQLException
      Create a new job with the specified user and return the job identifier
      Parameters:
      connection - SQL Connection
      userIdentifier - User identifier
      Returns:
      Job identifier
      Throws:
      SQLException - Error

    • setJobState

      public static void setJobState(Connection connection, int jobId, String jobState) throws SQLException
      Update the state of an existing Job record.

      Persists the given state in table JOBS for the row identified by PK_JOB. The jobState is expected to be the name of a value from JobStates (e.g. QUEUED, RUNNING, etc.).

      Parameters:
      connection - The open JDBC Connection to use; must not be null.
      jobId - The identifier of the job to update (value of column PK_JOB).
      jobState - The new state to set for the job (typically JobStates.name()).
      Throws:
      SQLException - If a database access error occurs, or if no row was updated (job not found).

    • setJobProgression

      public static void setJobProgression(Connection connection, int jobId, double progression) throws SQLException
      Throws:
      SQLException

    • setJobEndTime

      public static void setJobEndTime(Connection connection, int jobId) throws SQLException
      Throws:
      SQLException

    • getJobs

      public static List<Map<String,Object>> getJobs(Connection connection, int filterByUserIdentifier) throws SQLException
      Fetch the content of the JOB table
      Parameters:
      connection -
      filterByUserIdentifier - if > 0, will filter the job for a specific user. Administrator see all jobs.
      Returns:
      Job list
      Throws:
      SQLException

    • dateToXMLGregorianCalendar

      public static XMLGregorianCalendar dateToXMLGregorianCalendar(String stringDate) throws SQLException, ParseException, DatatypeConfigurationException
      Parameters:
      stringDate -
      Throws:
      SQLException
      ParseException
      DatatypeConfigurationException

    • getJob

      public static Map<String,Object> getJob(Connection connection, int jobId) throws SQLException
      Fetch the content of the JOB table
      Parameters:
      connection -
      jobId - if > 0, will filter the job for a specific user. Administrator see all jobs.
      Returns:
      Job list
      Throws:
      SQLException

    • parseJob

      @NotNull public static Map<String,Object> parseJob(ResultSet rs, DateFormat mediumDateFormatEN, DecimalFormat f) throws SQLException
      Throws:
      SQLException

    • getUsers

      public static List<User> getUsers(Connection connection) throws SQLException
      Throws:
      SQLException

    • updateUserAttributes

      public static void updateUserAttributes(Connection connection, User user) throws SQLException
      Updates the user attributes (email, totp token) in the database.
      Parameters:
      connection - The database connection object.
      user - New attributes for the user.
      Throws:
      SQLException - If there's an error executing the SQL update or if no rows were affected, indicating that no user with the given identifier was found in the database.

    • deleteUser

      public static void deleteUser(Connection connection, int userIdentifier) throws SQLException
      Throws:
      SQLException

    • deleteJob

      public static void deleteJob(Connection connection, int jobId) throws SQLException
      Deletes a job record from the JOBS table based on the specified job ID.
      Parameters:
      connection - The database connection to be used for executing the query.
      jobId - The ID of the job to be deleted.
      Throws:
      SQLException - If a database access error occurs or the SQL statement fails to execute.

    • deleteAllFinalizedJobs

      public static void deleteAllFinalizedJobs(Connection connection, int identifier) throws SQLException
      Deletes all jobs from the JOBS table that are not in the 'QUEUED' or 'RUNNING' status.
      Parameters:
      connection - the active database connection to use for executing the deletion query
      identifier - User identifier
      Throws:
      SQLException - if a database access error occurs or the SQL statement fails