User:Trifonnt/DB
From ADempiere
This Wiki is read-only for reference purposes to avoid broken links.
- 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'.
- JDBC Logger show you the SQL statements
- 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.
- Database Refactoring With LiquiBase; DBMS-independent library for tracking, managing and applying database changes; Similar to DDLUtils
- The Problem With Database Diffs Interesting post regarding automatic diff of DBs; I would say that the same problem applies for ERP system. You can't understand what is semantic of change only from Change Log. For example: Changes regarding Documents (Invoice, Orders, ... ) must be sent only when document status is changed (from Prepared to Completed for example), becaese lines are added after main record is created, that's why upon Document creation his children(Order Lines or Invoice Lines) are empty...
- Oracle (PL/SQL) Equivalents for MS SQL Server (T-SQL) Constructs List of the top 10 things Developer should known about Oracle - PL/SQL coming from an MS SQL Server - T-SQL background.
- Executing Common SQL Coding Tasks Using Function Calls Function implementations in Microsoft SQL Server, MySQL, Oracle, and PostgreSQL.
- QuantumPlug-in for Eclipse
- DBsql2XMLJava tool (class) for transformation (export, convert) relational database into hierarchical XML.
- Using MAX data types in SQL ServerMAX allows up to 2 GB text or image to be stored.
- 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;
- Table oriented frameworks
- Butler framework Table-oriented java object model on top of JDBC that makes database programming easier. It also has a set of database aware swing components and a JSP tag library.
- The power of table-oriented programming An introduction to Butler
Contents
- 1 Interesting SQL Queries
- 2 SQL Formatter
- 3 SQL Search Engines
- 4 SQL dialects
- 5 SQL Group By
- 6 Oracle
- 7 MySQL
- 8 HsqlDB
- 9 PostgreSQL
- 10 Virtual Private DBs; Row Level Security
- 11 DB Replication
- 12 DB Backup
- 13 LDAP
- 14 DB Structure and Data comparison
- 15 Security, SQL Injection
- 16 Oracle to/from Postgre porting/conversion
- 17 ER Diagrams
- 18 Druid
Interesting SQL Queries
SQL Formatter
SQL Search Engines
- SphinxFull-text search engine, distributed under GPL version 2
SQL dialects
SQL Group By
- SQL GROUP BY techniquesVery good article.
Oracle
- DBNEWID UtilityThe DBNEWID utility allows the DBID to be altered for the first time and makes changing the DBNAME simpler.
MySQL
- MySQL Commands
- How Do I Enable Remote Access To MySQL Database Server?
- What to tune in MySQL Server after installation
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.
- PostgreSQL in OpenSolaris and Ubuntu
- Install PostgreSQL on Ubuntu 7.10
- An Almost Idiot's Guide to PostgreSQL YUM
- Best Practices with PostgreSQL 8.3 on Solaris
- MySQL vs PostgreSQL
- Why PostgreSQL Instead of MySQL 2009
- Postgresql table sizes
SELECT relname, reltuples, relpages * 8 / 1024 AS "MB" FROM pg_class ORDER BY relpages DESC;
- Postgres Support and Training
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
- Oracle
- Postgre
- Row-based security (virtual private database)
- VeilDatabase security add-on for Postgres.
- Pentaho
DB Replication
- SymmetricDS
- Slony
- GolcondeQueue based replication solution for PostgreSQL written in Python 2.6
- Database Replication in MySQL
DB Backup
LDAP
- OpenLDAP-POSTGRESQL HOWTOWith real example.
- LDAP Schema Design
- LDAP Implementation HOWTO
- OpenLDAP-POSTGRESQL HOWTO
LDAP Servers
LDAP Clients
DB Structure and Data comparison
- pg_diff - compare two PostgreSQL database schemasRuby
- Pg Diff
- pg51g Data diff toolkit for PostgreSQL
- MySQL Diff
- EMS Data Comparer for PostgreSQLGood price.
Security, SQL Injection
Oracle to/from Postgre porting/conversion
- Porting from Oracle to PostgreSQL good document
- Postgre 8.0 data types
- Oracle to Posgre conversion
- Oracle to PostgreSQL Import/Export/Convert Software
- EMS Data Pump for PostgreSQLVery good price.
ER Diagrams
- Clay Eclipse based
- Mogwai ER-DesignerSupports import from torgue xml files.
- Power*Architect Data Modeling & Profiling Tool; Looks very interesting. Written in Java.
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.
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)