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;
}
}
Rants and ravings of a semi-autistic developer who has a hard time remembering idiotic nonsense details. Why remember it, when you know where to find it.
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
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;
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;
FROM table1
RIGHT OUTER JOIN table2
ON table1.column_name=table2.column_name;
PS! In some databases RIGHT JOIN is called RIGHT OUTER JOIN.
Labels:
sql
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;
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;
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name=table2.column_name;
PS! In some databases LEFT JOIN is called LEFT OUTER JOIN.
Labels:
sql
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....
Triggers for each of the counts as an after insert
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
Subscribe to:
Posts (Atom)