Showing posts with label sql. Show all posts
Showing posts with label sql. Show all posts

Monday, September 5, 2016

Hibernate 4 - publish changes / updates to the schema with entities in Spring JavaConfig

package com.mycompany.domain.test;

import java.io.File;
import java.io.IOException;
import java.net.URL;
import java.util.ArrayList;
import java.util.Enumeration;
import java.util.Iterator;
import java.util.List;
import java.util.Properties;

import org.apache.log4j.BasicConfigurator;
import org.apache.log4j.Logger;
import org.hibernate.MappingException;
import org.hibernate.boot.registry.StandardServiceRegistryBuilder;
import org.hibernate.boot.registry.internal.StandardServiceRegistryImpl;
import org.hibernate.cfg.Configuration;
import org.hibernate.metamodel.MetadataSources;
import org.hibernate.metamodel.source.MetadataImplementor;
import org.hibernate.service.ServiceRegistry;
import org.hibernate.tool.hbm2ddl.SchemaUpdate;

public class JpaSchemaExport {
   
   private static final Logger LOGGER = Logger.getLogger(JpaSchemaExport.class);
     
   public static void main(String[] args) throws MappingException, IOException, ClassNotFoundException {
      BasicConfigurator.configure();
      Configuration c = new Configuration();
      Class[] clzzs = getClasses("com.mycompany.domain.entity");
      for (int i = 0; i < clzzs.length; i++) {
         c.addAnnotatedClass(clzzs[i]);
      }
      Properties hibernateProperties = new Properties();
      hibernateProperties.put("hibernate.connection.driver_class","oracle.jdbc.OracleDriver");
      hibernateProperties.put("hibernate.connection.url","jdbc:oracle:thin:@");
      hibernateProperties.put("hibernate.connection.username","scott");
      hibernateProperties.put("hibernate.connection.password","secret99");
      hibernateProperties.put("hibernate.show_sql", true);
      hibernateProperties.put("hibernate.dialect","org.hibernate.dialect.Oracle10gDialect");
      hibernateProperties.put("hibernate.format_sql", true);
      hibernateProperties.put("connection.pool_size", 3);
      hibernateProperties.put("current_session_context_class", "thread");
      hibernateProperties.put("hibernate.hbm2ddl.auto", "update");
      c.addProperties(hibernateProperties);
      
      StandardServiceRegistryBuilder registry = new StandardServiceRegistryBuilder();
      registry.applySettings(c.getProperties());

      ServiceRegistry serviceRegistry = registry.build();
      MetadataImplementor metadata = (MetadataImplementor) new MetadataSources(serviceRegistry).buildMetadata();

      SchemaUpdate schemaExport = new SchemaUpdate(serviceRegistry, c);

      // use this line when exporting the whole DDL 
      //SchemaExport schemaExport = new SchemaExport(serviceRegistry,c);
      File f = new File("export.sql");  
      schemaExport.setOutputFile(f.getAbsolutePath());
      System.err.println(f.getAbsolutePath());
      schemaExport.execute(true, false);
      

      if (schemaExport.getExceptions().size() > 0) {
         Iterator iterator = schemaExport.getExceptions().iterator();
         int cnt = 1;
         LOGGER.warn(schemaExport.getExceptions().size() + " errors occurred while performing .");
         while (iterator.hasNext()) {
             LOGGER.error("Error #" + cnt + ": " + iterator.next().toString());
         }

     }
      
      ( (StandardServiceRegistryImpl) serviceRegistry ).destroy();
  }
  
  /**
   * Scans all classes accessible from the context class loader which belong to the given package and subpackages.
   *
   * @param packageName The base package
   * @return The classes
   * @throws ClassNotFoundException
   * @throws IOException
   */
  private static Class[] getClasses(String packageName)
          throws ClassNotFoundException, IOException {
      ClassLoader classLoader = Thread.currentThread().getContextClassLoader();
      assert classLoader != null;
      String path = packageName.replace('.', '/');
      Enumeration resources = classLoader.getResources(path);
      List dirs = new ArrayList();
      while (resources.hasMoreElements()) {
          URL resource = resources.nextElement();
          dirs.add(new File(resource.getFile()));
      }
      ArrayList classes = new ArrayList();
      for (File directory : dirs) {
          classes.addAll(findClasses(directory, packageName));
      }
      return classes.toArray(new Class[classes.size()]);
  }

  /**
   * Recursive method used to find all classes in a given directory and subdirs.
   *
   * @param directory   The base directory
   * @param packageName The package name for classes found inside the base directory
   * @return The classes
   * @throws ClassNotFoundException
   */
  private static List findClasses(File directory, String packageName) throws ClassNotFoundException {
      List classes = new ArrayList();
      if (!directory.exists()) {
          return classes;
      }
      File[] files = directory.listFiles();
      for (File file : files) {
          if (file.isDirectory()) {
              assert !file.getName().contains(".");
              classes.addAll(findClasses(file, packageName + "." + file.getName()));
          } else if (file.getName().endsWith(".class")) {
              classes.add(Class.forName(packageName + '.' + file.getName().substring(0, file.getName().length() - 6)));
          }
      }
      return classes;
  }  
}

Wednesday, August 24, 2016

Oracle DeadLock Detection

Normally deadlock generates dump file and automatically is released by oracle system process
1) check v$session
sqlplus> select sid, serial#, username, command, lockwait, osuser from v$session where lockwait is not null
2) To kill a locked session, first need to find sid, serial and use
sqlplus>alter system kill session 'sid, serial#';
*** you need have dba priviledge to kill sessions
3. To find which SQL has lock wait
sqlplus>select sql_text from v$sqltext where (address,hash_value) in (select sql_address,sql_hash_value from v$session where lockwait is not null) order by address, hash_value, piece

Sunday, February 1, 2015

SQL Right Join

SQL RIGHT JOIN Keyword

The RIGHT JOIN keyword returns all rows from the right table (table2), with the matching rows in the left table (table1). The result is NULL in the left side when there is no match.

SQL RIGHT JOIN Syntax

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name=table2.column_name;
or:
SELECT column_name(s)
FROM table1
RIGHT OUTER JOIN table2
ON table1.column_name=table2.column_name;
PS! In some databases RIGHT JOIN is called RIGHT OUTER JOIN.
SQL RIGHT JOIN

SQL Left Join

SQL LEFT JOIN Keyword

The LEFT JOIN keyword returns all rows from the left table (table1), with the matching rows in the right table (table2). The result is NULL in the right side when there is no match.

SQL LEFT JOIN Syntax

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;
or:
SELECT column_name(s)
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name=table2.column_name;
PS! In some databases LEFT JOIN is called LEFT OUTER JOIN.
SQL LEFT JOIN


Thursday, February 3, 2011

SQLite: inserts or updates on counts with triggers

If a record for the summary does not exist we need to insert a record with zero count and then up the count with 1. How to do this.... here the SQLite if then else version.


DROP TRIGGER IF EXISTS "trigger_year_action_summary" ;
CREATE TRIGGER "trigger_year_action_summary" AFTER INSERT ON "AUDIT_RECORD" FOR EACH ROW
BEGIN

INSERT INTO SUMMARIES ('VALUE','YEAR','MONTH','ENVIRONMENT','ACTION','USER','SUCCESS','HOUR_OF_DAY')
SELECT 0,new.YEAR,new.MONTH,new.ENVIRONMENT,new.ACTION,null,null,null
WHERE NOT EXISTS (
SELECT 1 FROM SUMMARIES WHERE
MONTH = new.MONTH
and ACTION = new.ACTION
and YEAR = new.YEAR
and environment = new.ENVIRONMENT
and success is null
and user is null
and hour_of_day is null
and day_of_week is null

);

UPDATE SUMMARIES
SET value = value + 1
WHERE
MONTH = new.MONTH
and ACTION = new.ACTION
and YEAR = new.YEAR
and environment = new.ENVIRONMENT
and success is null
and user is null
and hour_of_day is null
and day_of_week is null
;

END;

SQLite: pivioting summary with sub selects and triggers

Accounting and summaries can be a drag in SQL. This is just one reminder of such a problem...

One audit table and one summary table with counts counts counts....


DROP TABLE IF EXISTS SUMMARIES;
CREATE TABLE "SUMMARIES" (
"id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
"value" INT,
"YEAR" INTEGER DEFAULT NULL,
"MONTH" INTEGER DEFAULT NULL,
"ACTION" TEXT DEFAULT NULL,
"ENVIRONMENT" TEXT DEFAULT NULL,
"USER" TEXT DEFAULT NULL,
"SUCCESS" INT DEFAULT NULL,
"HOUR_OF_DAY" INTEGER DEFAULT NULL,
"DAY_OF_WEEK" TEXT DEFAULT NULL,
CONSTRAINT "ALL" UNIQUE ("YEAR", "MONTH", "ACTION", "ENVIRONMENT", "USER", "SUCCESS", "HOUR_OF_DAY", "DAY_OF_WEEK") ON CONFLICT ROLLBACK
);

-- ----------------------------
-- Table structure for "AUDIT_RECORD"
-- ----------------------------
DROP TABLE IF EXISTS "AUDIT_RECORD";
CREATE TABLE "AUDIT_RECORD" (
"ENVIRONMENT" TEXT(8) NOT NULL DEFAULT '-',
"USER" TEXT NOT NULL DEFAULT '-',
"TIMESTAMP" TEXT NOT NULL,
"CREATED_DT" REAL,
"SUCCESS" TEXT,
"PROJECT" TEXT NOT NULL,
"LABEL" TEXT,
"YEAR" INTEGER NOT NULL,
"MONTH" INTEGER NOT NULL,
"DAY" INTEGER NOT NULL,
"DAY_OF_MONTH" INTEGER NOT NULL,
"OWNER" TEXT,
"HOUR_OF_DAY" INTEGER,
"ACTION" TEXT NOT NULL,
PRIMARY KEY ("USER", "TIMESTAMP", "ENVIRONMENT")
);




Triggers for each of the counts as an after insert

CREATE TRIGGER "trigger_year_action_summary" AFTER INSERT ON "AUDIT_RECORD" FOR EACH ROW
BEGIN

INSERT INTO SUMMARIES ('VALUE','YEAR','MONTH','ENVIRONMENT','ACTION','USER','SUCCESS','HOUR_OF_DAY')
SELECT 0,new.YEAR,new.MONTH,new.ENVIRONMENT,new.ACTION,null,null,null
WHERE NOT EXISTS (
SELECT 1 FROM SUMMARIES WHERE
MONTH = new.MONTH
and ACTION = new.ACTION
and YEAR = new.YEAR
and environment = new.ENVIRONMENT
and success is null
and user is null
and hour_of_day is null
and day_of_week is null

);

UPDATE SUMMARIES
SET value = value + 1
WHERE
MONTH = new.MONTH
and ACTION = new.ACTION
and YEAR = new.YEAR
and environment = new.ENVIRONMENT
and success is null
and user is null
and hour_of_day is null
and day_of_week is null
;




select
c.id
,c.year as 'YEAR'
, c.environment as 'ENV'
, c.month as 'MONTH'
, c.value as 'total'
, x.value as 'builds'
, y.value as 'deploys'
, xs.value as 'bld_suc'
, xf.cvalue as 'bld_fail'
, ys.value as 'dly_suc'
, yf.cvalue as 'dly_fail'
from
summaries c
,(select year, month, value, environment from summaries where action = 'builds' and success is null ) x
,(select year, month, value, environment from summaries where action = 'deploy' and success is null ) y
,(select year, month, value, environment from summaries where action = 'builds' and success =0) xs
,(select year, month, value, environment, sum(value) as "cvalue" from summaries where action = 'builds' and success != 0 group by year, month, environment) xf
,(select year, month, value, environment from summaries where action = 'deploy' and success =0) ys
,(select year, month, value, environment, sum(value) as "cvalue" from summaries where action = 'deploy' and success != 0 group by year, month, environment) yf
where
c.year = 2010
and c.environment = 'PRD'
and c.action is null
and x.year = c.year
and x.environment = c.environment
and x.month = c.month
and y.year = c.year
and y.environment = c.environment
and y.month = c.month
and xs.year = c.year
and xs.environment = c.environment
and xs.month = c.month
and xf.year = c.year
and xf.environment = c.environment
and xf.month = c.month
and yf.year = c.year
and yf.environment = c.environment
and yf.month = c.month
and ys.year = c.year
and ys.environment = c.environment
and ys.month = c.month

Thursday, December 30, 2010

Oracle: what is the cursor count?

select *    
from v$sesstat s, v$statname n
where s.statistic# = n.statistic#     
   and  n.name = 'opened cursors current'
   and  s.sid  in (   
        select sid 
        from v$session 
        where machine = '[my machine]' 
        )

Wednesday, December 29, 2010

Oracle: Reading Table Locks And From Where

SELECT ALL 
   ALL_OBJECTS.OWNER, 
   ALL_OBJECTS.OBJECT_NAME,
   ALL_OBJECTS.OBJECT_TYPE, 
   V$LOCKED_OBJECT.ORACLE_USERNAME,
   V$LOCKED_OBJECT.OS_USER_NAME,
   DECODE(V$LOCKED_OBJECT.LOCKED_MODE,
            0, 'None', 
            1, 'Null', 
            2, 'Row-S (SS)', 
            3, 'Row-X (SX)', 
            4, 'Share', 
            5, 'S/Row-X (SSX)', 
            6, 'Exclusive', 'Unknown') LockMode,
   V$SESSION.MACHINE, 
   V$SESSION.PROGRAM, 
   V$SESSION.LOGON_TIME,
   V$SESSION.TERMINAL, 
   V$SESSION.SID, 
   V$SESSION.SERIAL#
FROM 
   V$LOCKED_OBJECT, ALL_OBJECTS, V$SESSION
WHERE 
  ((ALL_OBJECTS.OBJECT_ID=V$LOCKED_OBJECT.OBJECT_ID)
      AND (V$LOCKED_OBJECT.SESSION_ID=V$SESSION.SID));

Wednesday, October 7, 2009

Oracle: Listing All Invalid PLSQL Packages

SELECT count(*) 
FROM ALL_OBJECTS 
WHERE owner = 'MYAPP'
  and  OBJECT_TYPE like  'PACKAGE\%'
  and status = 'INVALID'; ";

Showing CLOB information for Oracle

SELECT  dbms_lob.substr(a.user_data.payload, 3000,1)
FROM vmsut.tms_queue_table a