User:Trifonnt/DB

From ADempiere ERP Wiki

< User:Trifonnt(Difference between revisions)
Jump to: navigation, search
(Created page with '* [http://schemaspy.sourceforge.net/ Schema Spy] Good tool which can create graphical representation of DB Schema ** [http://www.joachim-uhl.de/projekte/schemaspygui/ Schema Spy …')
m (Interesting SQL Queries)
 
Line 111: Line 111:
* [http://www.paragoncorporation.com/ArticleDetail.aspx?ArticleID=25 What is a Cross Tab Query?]
* [http://www.paragoncorporation.com/ArticleDetail.aspx?ArticleID=25 What is a Cross Tab Query?]
* [http://www.postgresonline.com/journal/index.php?/archives/14-CrossTab-Queries-in-PostgreSQL-using-tablefunc-contrib.html CrossTab Queries in PostgreSQL using tablefunc]
* [http://www.postgresonline.com/journal/index.php?/archives/14-CrossTab-Queries-in-PostgreSQL-using-tablefunc-contrib.html CrossTab Queries in PostgreSQL using tablefunc]
 +
* [http://dev.mysql.com/tech-resources/articles/wizard/print_version.html MySQL Wizardry]
== SQL Formatter ==
== SQL Formatter ==

Latest revision as of 17:23, 14 September 2010

  • Schema Spy Good tool which can create graphical representation of DB Schema
    • Schema Spy GUI
    • Install Debian package "gsfonts" in order to solve the issue with 'dot'.
  • Query Builder
    • Dynamic Query Builder
    • jDBIjDBI is designed to provide convenient tabular data access in Java(tm). It uses the Java collections framework for query results, provides a convenient means of externalizing sql statements, and provides named parameter support for any database being used.


  • DBsql2XMLJava tool (class) for transformation (export, convert) relational database into hierarchical XML.
  • Useful scripts related to Tables/Indexes in Oracle.
  • How to alter size of column in Postgre?
insert into t_alter_column values('ad_message','Value','VARCHAR(255)',null,null)


  • Query to look Sessions of users in Postgre:
select pg_class.relname, pg_locks.transaction, pg_locks.mode, 
pg_locks.granted as "g",
       pg_stat_activity.query_start,
   age(now(),pg_stat_activity.query_start) as "age", 
pg_stat_activity.procpid,
   pg_database.datname , pg_stat_activity.client_addr, 
   pg_locks.page, pg_locks.tuple, pg_locks.relation, 
rpad(pg_stat_activity.current_query, 200)
   from pg_stat_activity,pg_database , pg_locks 
   left outer join pg_class on
   (pg_locks.relation = pg_class.oid) 
   where pg_locks.pid=pg_stat_activity.procpid 
     and pg_locks.database = pg_database.oid
     and pg_locks.mode       like '%ExclusiveLock'
   order by query_start;

usie  pg_cancel_backend(pid) function to cancel

Following query lists all open sessions and the relevant PIDs.

SELECT Activity.datname, Activity.procpid, Shadow.usename,
Activity.current_query
FROM pg_stat_activity Activity 
INNER JOIN pg_shadow Shadow 
ON (Activity.usesysid=Shadow.usesysid);


  • Show active transactions in Postrges
psql -U postgresql -p -c select * from pg_stat_activity where current_query <> '<IDLE>'; > result.txt


  • Alter Existing Column in Oracle.
--Add temporary column
ALTER TABLE AD_PROCESS_PARA ADD ( X_COLUMNNAME VARCHAR2(40) );
--Copy values in the temp column
UPDATE AD_PROCESS_PARA SET X_COLUMNNAME=COLUMNNAME;
--Allow null values ( to be able to set to null and modify the data type)
ALTER TABLE AD_PROCESS_PARA MODIFY "COLUMNNAME" NVARCHAR2(40)  NULL;
UPDATE AD_PROCESS_PARA SET COLUMNNAME=null;
ALTER TABLE AD_PROCESS_PARA MODIFY (COLUMNNAME VARCHAR2(40));
--copy values from temp column and drop it
UPDATE AD_PROCESS_PARA SET COLUMNNAME=X_COLUMNNAME;
ALTER TABLE AD_PROCESS_PARA DROP COLUMN X_COLUMNNAME;



Contents

Interesting SQL Queries

SQL Formatter

SQL Search Engines

  • SphinxFull-text search engine, distributed under GPL version 2


SQL dialects

SQL Group By

Oracle

  • DBNEWID UtilityThe DBNEWID utility allows the DBID to be altered for the first time and makes changing the DBNAME simpler.


MySQL

HsqlDB

PostgreSQL

  • Users
    • Yahoo! (2 petabyte web user behavioral analysis database)
    • Skype
    • hi5.com
    • Sony online which uses it to power its massively multiplayer online games.
SELECT relname, reltuples, relpages * 8 / 1024 AS "MB" FROM pg_class ORDER BY relpages DESC;


Postgre Hints

  • Open Connections
SELECT * FROM pg_stat_activity ORDER BY procpid;


  • More ifno regarding open connections
SELECT granted, database, relation, page, tuple, transactionid,
 virtualtransaction, mode,
 relname, relkind,
 datname, usename,
CASE
 WHEN length(current_query) > 40
 THEN substring(current_query for 20) || '...' || substring(current_query from (length(current_query) - 19)	for 20)
 ELSE current_query
END as query, waiting, query_start, client_addr, client_port
FROM pg_locks
LEFT OUTER JOIN pg_class on oid = relation
LEFT OUTER JOIN pg_stat_activity on pid = procpid

Virtual Private DBs; Row Level Security

DB Replication

DB Backup

LDAP

LDAP Servers

LDAP Clients

DB Structure and Data comparison

Security, SQL Injection


Oracle to/from Postgre porting/conversion

ER Diagrams

Druid


Changes which i made to Druid

Show class name in modules dialog

Menu "Config" -> "Modules..." now shows class name of loaded module. This allows developer to find responsible class and modify it if necessary.

Loaded modules window


package druid.dialogs.modules;

import java.awt.Color;

import javax.swing.JTextField;

import org.dlib.gui.FlexLayout;
import org.dlib.gui.TLabel;
import org.dlib.gui.TPanel;
import org.dlib.gui.TTextArea;
import org.dlib.gui.TTextField;

import druid.interfaces.BasicModule;

public class GeneralPanel extends TPanel
{
	private JTextField txtId      = new TTextField();
	private JTextField txtVersion = new TTextField();
	private JTextField txtAuthor  = new TTextField();
	private TTextArea  txaDescr   = new TTextArea(6, 40);
	private JTextField txtClass	  = new TTextField();  // @Trifon

	public GeneralPanel()
	{
		super("General");

		FlexLayout flexL = new FlexLayout(2,5,4,4);  // @Trifon
		flexL.setColProp(1, FlexLayout.EXPAND);
		setLayout(flexL);

		//--- setup textfields

		txtId.setEditable(false);
		txtVersion.setEditable(false);
		txtAuthor.setEditable(false);
		txaDescr.setEditable(false);
		txtClass.setEditable(false);  // @Trifon

		txtId.setBackground(Color.white);
		txtVersion.setBackground(Color.white);
		txtAuthor.setBackground(Color.white);
		txtClass.setBackground(Color.white);  // @Trifon

		//--- setup panel

		add("0,0",     new TLabel("Id"));
		add("0,1",     new TLabel("Version"));
		add("0,2",     new TLabel("Author"));
		add("0,3,l,t", new TLabel("Descr"));
		add("0,4",     new TLabel("Class"));  // @Trifon

		add("1,0,x", txtId);
		add("1,1,x", txtVersion);
		add("1,2,x", txtAuthor);
		add("1,3,x", txaDescr);
		add("1,4,x", txtClass);  // @Trifon
	}

	public void setCurrentModule(BasicModule mod)
	{
		txtId.setText(mod.getId());
		txtVersion.setText(mod.getVersion());
		txtAuthor.setText(mod.getAuthor());
		txaDescr.setText(mod.getDescription());
		txtClass.setText(mod.getClass().getCanonicalName());  // @Trifon
	}
}


TODO SQL constant remapping

class: factory.sql.AbstractSqlGenModule

	protected String genFieldAttrib(FieldNode node, AttribSet asAttrib, Object obj)
	{
		StringBuffer sb = new StringBuffer();

		String faSqlName = asAttrib.getString("sqlName");

		if (obj instanceof Boolean)
		{
			boolean b = ((Boolean)obj).booleanValue();

			if (b)
				sb.append(faSqlName);
		}

		else if (obj instanceof String || obj instanceof Integer)
		{
			String value = obj.toString().trim();

			if (!value.equals("")) {
				// TODO - Trifon
				// Here we can make SQL constant remapping!
				// SYSDATE -> NOW()
				BasicDatabaseSettings sett = new BasicDatabaseSettings(node.getDatabase().modsConfig, this);

				AttribList alSqlMapping = sett.getSqlMapping();
				if (alSqlMapping.size() > 0) {
					sb.append(faSqlName + " " + remapSqlType(value, alSqlMapping));
				} else {
					sb.append(faSqlName + " " + value);
				}
			}
				
		}

		else
			throw new DruidException(DruidException.INC_STR, "Unknown type of object", obj);

		return sb.toString();
	}

Druid Hints

Hint.1 Remapping of DB Types

Druid fully supports remapping of database types. This feature allows developer to build his initial DB model against specific DB(Oracle for example) and generate SQL scripts for any DB (Postgre for example). Or developer can define abstract DB types and create appropriate mapping for each DB!

Field "Sql Type" can contain any Regular expression.

Java Pattern class : java.util.regex.Pattern


Example remapping from Oracle to Postgre.

Sql Type:

NUMBER\(10,0\)

Mappend type:

NUMERIC(10)
Personal tools