/*
 * Copyright 2014 Texas A&M Engineering Experiment Station
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 * http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package edu.tamu.tcat.db.postgresql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 * A set of utilities to help build database entities in PostgreSQL, such as databases and schemas.
 */
public class PostgreSqlEntityHelper
{
   private static final Logger debug = Logger.getLogger(PostgreSqlEntityHelper.class.getName());

   /**
    * @param connDefaultDb A connection to the db server on the database ""
    * @param targetDatabase The name of the database to create
    */
   public static void createDatabaseIfNotExists(Connection connDefaultDb, String targetDatabase)
   {
      String sqlCheck = "SELECT EXISTS (SELECT * FROM pg_catalog.pg_database WHERE datname = ?)";
      try (PreparedStatement existsCheck = connDefaultDb.prepareStatement(sqlCheck))
      {
         existsCheck.setString(1, targetDatabase);
         try (ResultSet results = existsCheck.executeQuery())
         {
            if (!results.next())
            {
               debug.log(Level.WARNING, "Failed testing for database existence ["+sqlCheck+"]");
               return;
            }

            boolean exists = results.getBoolean(1);
            if (exists)
               return;
         }
      }
      catch (Exception e)
      {
         debug.log(Level.SEVERE, "Failed checking for existence of database ["+targetDatabase+"] with sql ["+sqlCheck+"]", e);
         // don't try to create if exist check failed
         return;
      }

      debug.log(Level.INFO, "Database [" + targetDatabase + "] not found, creating it now");
      String sqlCreate = "CREATE DATABASE \"" + targetDatabase + "\"";
      try (PreparedStatement create = connDefaultDb.prepareStatement(sqlCreate))
      {
         create.executeUpdate();
      }
      catch (Exception e)
      {
         debug.log(Level.SEVERE, "Failed creating database ["+targetDatabase+"] with sql ["+sqlCreate+"]", e);
         return;
      }
   }

   /**
    * Create the "postgis" extension on the database for the given connection. The extension module
    * must already be installed on the database server, and this will ensure it is available in the database.
    *
    * @param conn
    */
   public static void createExtensionPostGis(Connection conn)
   {
      debug.log(Level.INFO, "Installing PostGIS extensions");

      try (PreparedStatement install = conn.prepareStatement("CREATE EXTENSION postgis"))
      {
         install.executeUpdate();
      }
      catch (Exception e)
      {
         debug.log(Level.WARNING, "Cannot create PostGIS extension, the package may need to be installed on the db server", e);
      }
   }

   //NOTE: if these SQL statements fail, things are likely still okay; if the db is in trouble, using the schema later will fail
   public static void createSchemaIfNotExists(Connection conn, String targetSchemaName)
   {
      String sqlCheck = "SELECT EXISTS(SELECT schema_name FROM information_schema.schemata WHERE schema_name = ?)";
      try (PreparedStatement existsCheck = conn.prepareStatement(sqlCheck))
      {
         existsCheck.setString(1, targetSchemaName);
         try (ResultSet results = existsCheck.executeQuery())
         {
            if (!results.next())
            {
               debug.log(Level.WARNING, "Failed testing for schema existence ["+sqlCheck+"]");
               return;
            }

            boolean exists = results.getBoolean(1);
            // schema already exists, exit early
            if (exists)
               return;
         }
      }
      catch (Exception e)
      {
         debug.log(Level.SEVERE, "Failed checking for existence of schema ["+targetSchemaName+"] with sql ["+sqlCheck+"]", e);
         // don't try to create if exist check failed
         return;
      }

      String sqlCreate = "CREATE SCHEMA \"" + targetSchemaName + "\"";
      try (PreparedStatement create = conn.prepareStatement(sqlCreate))
      {
         debug.log(Level.INFO, "Schema [" + targetSchemaName + "] not found, creating it now");
         create.executeUpdate();
      }
      catch (Exception e)
      {
         debug.log(Level.SEVERE, "Failed creating schema ["+targetSchemaName+"] with sql ["+sqlCreate+"]", e);
      }
   }
}

@@ -1217,28 +1217,31 @@
 blic static 
-void
+boolean
  createDatab
@@ -1243,27 +1243,16 @@
 Database
-IfNotExists
 (Connect
@@ -1770,32 +1770,38 @@
           return
+ false
 ;%0A            %7D%0A
@@ -1890,32 +1890,38 @@
           return
+ false
 ;%0A         %7D%0A   
@@ -2147,32 +2147,38 @@
 %0A         return
+ false
 ;%0A      %7D%0A%0A     
@@ -2450,32 +2450,54 @@
 xecuteUpdate();%0A
+         return true;%0A
       %7D%0A      ca
@@ -2640,32 +2640,38 @@
 %0A         return
+ false
 ;%0A      %7D%0A   %7D%0A%0A
@@ -2931,28 +2931,31 @@
 blic static 
-void
+boolean
  createExten
@@ -3353,32 +3353,51 @@
 r%22, e);%0A      %7D%0A
+      return true;%0A
    %7D%0A%0A   //NOTE:
@@ -3536,12 +3536,15 @@
 tic 
-void
+boolean
  cre
@@ -3556,19 +3556,8 @@
 hema
-IfNotExists
 (Con
@@ -4081,32 +4081,38 @@
           return
+ false
 ;%0A            %7D%0A
@@ -4250,32 +4250,38 @@
           return
+ false
 ;%0A         %7D%0A   
@@ -4515,16 +4515,22 @@
   return
+ false
 ;%0A      
@@ -4804,32 +4804,54 @@
 xecuteUpdate();%0A
+         return true;%0A
       %7D%0A      ca
@@ -4868,32 +4868,32 @@
 tion e)%0A      %7B%0A
-
          debug.l
@@ -4983,24 +4983,47 @@
 te+%22%5D%22, e);%0A
+         return false;%0A
       %7D%0A   %7D

Return success status