/*
 * frmPrincipal.java
 *
 * Created on 27 de enero de 2005, 21:54
 */

package mssql2pgsql;

import java.sql.*;
import javax.swing.*;
import java.io.*;
import java.lang.*;
import java.util.HashMap;
import java.util.Properties;
import javax.swing.table.DefaultTableModel;
import java.util.concurrent.*;


/**
 *
 * @author  tarradelo
 */


public class frmPrincipal extends javax.swing.JFrame {
    private Connection conexSqlServer;
    private Connection conexPgSql;
    Semaphore Semaforos;
    
    private class clsPasaDatos extends Thread{
        private String nombreTabla;
        private JTable grid;
        private int filaGrid;
        private int colGrid;
        private Connection conexSqlSrv;
        private Connection conexPgSQL;
        // Inicializamos los datos...
        public void Init (Connection SqlSrv, Connection PgSQL, String tabla, JTable grd, int fila,int col){
            nombreTabla = tabla;
            grid = grd;
            filaGrid = fila;
            colGrid = col;
            conexSqlSrv = SqlSrv;
            conexPgSQL = PgSQL;
        }
        public synchronized void run (){
            // Y ejecutamos el paso de esta tabla en una hebra independiente...
            Statement sentencia,sentenciaM;
            String nombreTablaDestino;
            ResultSet rs, rsPg;
            boolean estado;
            Object obj;
            int numCols,x, numFilas, numRegs;
            DefaultTableModel Modelo = null;

            estado = false;
            try {
                numFilas = 0;
                numRegs = 0;
                numCols = 0;
                x = 0;
                obj = null;
                Modelo = (DefaultTableModel)grid.getModel();
                grid.setValueAt("Esperando...", filaGrid,colGrid);
                sentenciaM = conexSqlServer.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
                sentencia = conexPgSql.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
                nombreTablaDestino = nombreTabla.trim().toLowerCase().replace(" ", "_");
                rs = sentenciaM.executeQuery("select * from [" + nombreTabla+ "]");
                // Obtenemos el nº de registros
                rs.last();
                numRegs = rs.getRow();
                rs.beforeFirst();
                // -----------
                rsPg = sentencia.executeQuery("select * from " + nombreTablaDestino);
                numCols = rs.getMetaData().getColumnCount();
                while (rs.next()){
                    try {
                        rsPg.moveToInsertRow();
                        for (x = 1; x <= numCols; x++){
                            obj = rs.getObject(x);
                            switch (rsPg.getMetaData().getColumnType(x)){
                                case Types.BINARY:
                                    if (!rs.wasNull()){
                                        rsPg.updateBinaryStream(x, rs.getBinaryStream(x),  rs.getBinaryStream(x).available());
                                    }else{
                                        rsPg.updateBinaryStream(x, null,0);
                                    }
                                    break;
                                case Types.ARRAY:
                                    if (!rs.wasNull()){
                                        rsPg.updateArray(x, rs.getArray(x));
                                    }else{
                                        rsPg.updateArray(x, null);
                                    }
                                    break;
                                case Types.BLOB:
                                    if (!rs.wasNull()){
                                        rsPg.updateBlob(x,rs.getBlob(x));
                                    }else{
                                        rsPg.updateBlob(x,null);
                                    }

                                    break;                        
                                case Types.BOOLEAN:
                                    if (!rs.wasNull()){
                                        rsPg.updateBoolean(x, rs.getBoolean(x));
                                    }else{
                                        rsPg.updateBoolean (x,false);
                                    }
                                    break;
                                case Types.CHAR:
                                    if (!rs.wasNull()){
                                        rsPg.updateString(x, rs.getString(x));
                                    }else{
                                        rsPg.updateString(x,"");
                                    }
                                    break;
                                case Types.CLOB:
                                    if (!rs.wasNull()){
                                        rsPg.updateClob(x, rs.getClob(x));
                                    }else{
                                        rsPg.updateClob(x,null);
                                    }
                                    break;
                                case Types.DATE:
                                    if (!rs.wasNull()){
                                        rsPg.updateDate(x, rs.getDate(x));
                                    }else{
                                        rsPg.updateDate(x,null);
                                    }
                                    break;
                                case Types.DECIMAL:
                                    if (!rs.wasNull()){
                                        rsPg.updateFloat(x, rs.getFloat(x));
                                    }else{
                                        rsPg.updateFloat(x,0);
                                    }
                                    break;
                                case Types.DOUBLE:
                                    if (!rs.wasNull()){
                                        rsPg.updateDouble(x,rs.getDouble(x));
                                    }else{
                                        rsPg.updateDouble(x,0);
                                    }
                                    break;
                                case Types.FLOAT:
                                    if (!rs.wasNull()){
                                        rsPg.updateFloat(x, rs.getFloat(x));
                                    }else{
                                        rsPg.updateFloat(x,0);
                                    }
                                    break;
                                case Types.INTEGER:
                                    if (!rs.wasNull()){
                                        rsPg.updateInt(x, rs.getInt(x));
                                    }else{
                                        rsPg.updateInt(x,0);
                                    }
                                    break;
                                case Types.LONGVARCHAR:
                                    if (!rs.wasNull()){
                                        rsPg.updateString(x,rs.getString(x));
                                    }else{
                                        rsPg.updateString (x,"");
                                    }
                                    break;
                                case Types.NUMERIC:
                                    if (!rs.wasNull()){
                                        rsPg.updateInt(x, rs.getInt(x));
                                    }else{
                                        rsPg.updateInt(x,0);
                                    }
                                    break;
                                case Types.REAL:
                                    if (!rs.wasNull()){
                                        rsPg.updateFloat(x, rs.getFloat(x));
                                    }else{
                                        rsPg.updateFloat(x,0);
                                    }
                                    break;
                                case Types.SMALLINT:
                                    if (!rs.wasNull()){
                                        rsPg.updateInt(x, rs.getInt(x));
                                    }else{
                                        rsPg.updateInt(x,0);
                                    }
                                    break;
                                case Types.TIMESTAMP:
                                    if (!rs.wasNull()){
                                        rsPg.updateTimestamp(x,rs.getTimestamp(x));
                                    }else{
                                        rsPg.updateTimestamp(x,null);
                                    }
                                    break;
                                case Types.TINYINT:
                                    if (!rs.wasNull()){
                                        rsPg.updateInt(x, rs.getInt(x));
                                    }else{
                                        rsPg.updateInt(x,0);
                                    }
                                    break;
                                case Types.VARCHAR:
                                    if (!rs.wasNull()){
                                        rsPg.updateString(x, rs.getString(x));
                                    }else{
                                        rsPg.updateString(x,"");
                                    }
                                    break;
                                default:
                                    rsPg.updateObject(x,rs.getObject(x));
                            }                    
                        }
                        rsPg.insertRow();
                        numFilas++;
                        grid.setValueAt("Copiando...("+ String.valueOf(numFilas)  +" de "+ String.valueOf(numRegs) + ")", filaGrid,colGrid);
                        estado = true;
                    }catch (Exception ex){
                        estado = false;
                    }
                }
            }catch (Exception e){
            }
            if (estado){
                grid.setValueAt("Realizado", filaGrid,colGrid);
            }else{
                // Aparte, pondremos la fila de otro color...
                //coloresTabla.setRowColor(filaGrid, java.awt.Color.RED);
                grid.setValueAt("No Realizado", filaGrid,colGrid);
            }
            Semaforos.release();
        }
        
    }
    
    private Connection conectaSqlServer (String servidor, int puerto,String usuario, String password, String bd)
    {
        Connection retorno;
        String cadCon;
        
        
        //cadCon = "jdbc:jtds:sqlserver://" + servidor + ":" + String.valueOf(puerto) + "/"+ bd + ";user="+ usuario + ";password=" + password;
        if (puerto != 0){
            cadCon = "jdbc:jtds:sqlserver://" + servidor + ":" + String.valueOf(puerto);
        }else{
            cadCon = "jdbc:jtds:sqlserver://" + servidor + ":1443"; 
        }
        if (bd.trim() != ""){
            cadCon += "/" + bd.trim();
        }
        // probar cadena igual a la de postgres, con un ? como separador de BD a parametros
        cadCon += ";user="+ usuario + ";password=" + password;
        try{
            Class.forName("net.sourceforge.jtds.jdbc.Driver");
            retorno = DriverManager.getConnection(cadCon);
            
        }catch (Exception e){
            retorno = null;
        }
        
        return retorno;
    }
    
    private String[] obtieneTablasSqlServer (){
        String []retorno;
        String sql;
        ResultSet rs;
        Statement sentencia;
        String []tipos = new String[1];
        int numFilas;
        tipos[0] = "TABLE";
        try{
            // Sacaremos solo las tablas de usuario, que son las que nos interesan realmente
            sql = "select distinct name from sysobjects where (xtype = 'U') AND (status > 0)";
            sentencia = conexSqlServer.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            rs = sentencia.executeQuery(sql);
            rs.last();
            numFilas = rs.getRow();
            rs.beforeFirst();

            retorno = new String [numFilas];
            // Cargamos al array...
            while (rs.next()){
                retorno [rs.getRow() - 1] = rs.getString("name").trim();
            }
        }catch (Exception e){
            retorno = null;
        }
        
        return retorno;
    }
    
    private Connection conectaPgSql (String servidor, int puerto,String usuario, String password, String bd)
    {
        Connection retorno;
        String cadCon;
        
        if (puerto != 0){
            cadCon = "jdbc:postgresql://" + servidor + ":" + String.valueOf(puerto);
        }else{
            cadCon = "jdbc:postgresql://" + servidor + ":5432"; 
        }
        if (bd.trim() != ""){
            cadCon += "/" + bd.trim();
        }
        // probar cadena igual a la de postgres, con un ? como separador de BD a parametros
        cadCon += "?user="+ usuario + "&password=" + password;
        try{
            Class.forName ("org.postgresql.Driver");
            retorno = DriverManager.getConnection(cadCon);
            
        }catch (Exception e){
            retorno = null;
        }
        
        return retorno;
    }
    /** Creates new form frmPrincipal */
    public frmPrincipal() {
        initComponents();
        // Definimos los tamaños de las columnas...
        //jTable1.getColumn(jTable1.getColumnName(columna)).setPreferredWidth(longitud);
        this.tblEstado.getColumn(this.tblEstado.getColumnName(0)).setPreferredWidth(300);
        this.tblEstado.getColumn(this.tblEstado.getColumnName(1)).setPreferredWidth(100);
        this.jTabbedPane1.setEnabledAt(1,false);
        this.jTabbedPane1.setEnabledAt(2, false);
        
        try {
            // Cargamos el driver para SQL Server
            Class.forName("net.sourceforge.jtds.jdbc.Driver");
        }catch (Exception e){
            
        }
        
    }
    
    /** This method is called from within the constructor to
     * initialize the form.
     * WARNING: Do NOT modify this code. The content of this method is
     * always regenerated by the Form Editor.
     */
    private void initComponents() {//GEN-BEGIN:initComponents
        buttonGroup1 = new javax.swing.ButtonGroup();
        jTabbedPane1 = new javax.swing.JTabbedPane();
        pnlCfgSqlSrv = new javax.swing.JPanel();
        jLabel1 = new javax.swing.JLabel();
        jLabel2 = new javax.swing.JLabel();
        jLabel3 = new javax.swing.JLabel();
        jLabel4 = new javax.swing.JLabel();
        jLabel5 = new javax.swing.JLabel();
        txtSrvSqlServer = new javax.swing.JTextField();
        txtUserSqlServer = new javax.swing.JTextField();
        txtPassSqlServer = new javax.swing.JTextField();
        txtBDSqlServer = new javax.swing.JTextField();
        txtPuertoSqlServer = new javax.swing.JSpinner();
        btnSigSQLServer = new javax.swing.JButton();
        pnlCfgPgSql = new javax.swing.JPanel();
        jLabel6 = new javax.swing.JLabel();
        txtSrvPgSql = new javax.swing.JTextField();
        txtPuertoPgSql = new javax.swing.JSpinner();
        jLabel7 = new javax.swing.JLabel();
        jLabel8 = new javax.swing.JLabel();
        jLabel9 = new javax.swing.JLabel();
        jLabel10 = new javax.swing.JLabel();
        txtUserPgSql = new javax.swing.JTextField();
        txtPassPgSql = new javax.swing.JTextField();
        txtBDPgSql = new javax.swing.JTextField();
        btnSigSQLServer1 = new javax.swing.JButton();
        btnSigSQLServer2 = new javax.swing.JButton();
        pnlAccion = new javax.swing.JPanel();
        btnEjecutar = new javax.swing.JButton();
        chkPasaTablas = new javax.swing.JCheckBox();
        jScrollPane1 = new javax.swing.JScrollPane();
        tblEstado = new javax.swing.JTable();
        chkPasaPK = new javax.swing.JCheckBox();
        chkPasaDatos = new javax.swing.JRadioButton();
        chkPasaFG = new javax.swing.JRadioButton();
        chkPasaIndices = new javax.swing.JCheckBox();
        jPanel1 = new javax.swing.JPanel();
        jLabel11 = new javax.swing.JLabel();
        jLabel12 = new javax.swing.JLabel();
        jLabel13 = new javax.swing.JLabel();
        jScrollPane2 = new javax.swing.JScrollPane();
        jTable1 = new javax.swing.JTable();
        jLabel14 = new javax.swing.JLabel();

        setDefaultCloseOperation(javax.swing.WindowConstants.EXIT_ON_CLOSE);
        setTitle("SqlServer2PostgreSQL");
        jTabbedPane1.setMaximumSize(new java.awt.Dimension(640, 260));
        jTabbedPane1.setMinimumSize(new java.awt.Dimension(640, 260));
        jTabbedPane1.setPreferredSize(new java.awt.Dimension(640, 260));
        pnlCfgSqlSrv.setLayout(null);

        pnlCfgSqlSrv.addComponentListener(new java.awt.event.ComponentAdapter() {
            public void componentShown(java.awt.event.ComponentEvent evt) {
                pnlCfgSqlSrvComponentShown(evt);
            }
        });
        pnlCfgSqlSrv.addFocusListener(new java.awt.event.FocusAdapter() {
            public void focusGained(java.awt.event.FocusEvent evt) {
                pnlCfgSqlSrvFocusGained(evt);
            }
        });

        jLabel1.setText("Servidor (Host o IP)");
        pnlCfgSqlSrv.add(jLabel1);
        jLabel1.setBounds(20, 20, 130, 15);

        jLabel2.setText("Puerto (Por defecto 1433)");
        pnlCfgSqlSrv.add(jLabel2);
        jLabel2.setBounds(20, 50, 165, 15);

        jLabel3.setText("Usuario (Por defecto sa)");
        pnlCfgSqlSrv.add(jLabel3);
        jLabel3.setBounds(20, 80, 165, 15);

        jLabel4.setText("Password");
        pnlCfgSqlSrv.add(jLabel4);
        jLabel4.setBounds(20, 110, 75, 15);

        jLabel5.setText("Base de Datos");
        pnlCfgSqlSrv.add(jLabel5);
        jLabel5.setBounds(20, 140, 130, 15);

        pnlCfgSqlSrv.add(txtSrvSqlServer);
        txtSrvSqlServer.setBounds(240, 20, 200, 19);

        txtUserSqlServer.setText("sa");
        pnlCfgSqlSrv.add(txtUserSqlServer);
        txtUserSqlServer.setBounds(240, 80, 120, 19);

        pnlCfgSqlSrv.add(txtPassSqlServer);
        txtPassSqlServer.setBounds(240, 110, 120, 19);

        pnlCfgSqlSrv.add(txtBDSqlServer);
        txtBDSqlServer.setBounds(240, 140, 195, 19);

        txtPuertoSqlServer.setValue(1433);
        pnlCfgSqlSrv.add(txtPuertoSqlServer);
        txtPuertoSqlServer.setBounds(240, 50, 70, 20);

        btnSigSQLServer.setText("Siguiente >");
        btnSigSQLServer.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                btnSigSQLServerActionPerformed(evt);
            }
        });

        pnlCfgSqlSrv.add(btnSigSQLServer);
        btnSigSQLServer.setBounds(320, 170, 115, 30);

        jTabbedPane1.addTab("Config. Servidor SQL Server", pnlCfgSqlSrv);

        pnlCfgPgSql.setLayout(null);

        pnlCfgPgSql.addComponentListener(new java.awt.event.ComponentAdapter() {
            public void componentShown(java.awt.event.ComponentEvent evt) {
                pnlCfgPgSqlComponentShown(evt);
            }
        });

        jLabel6.setText("Servidor (Host o IP)");
        pnlCfgPgSql.add(jLabel6);
        jLabel6.setBounds(15, 15, 130, 15);

        pnlCfgPgSql.add(txtSrvPgSql);
        txtSrvPgSql.setBounds(240, 10, 200, 19);

        txtPuertoPgSql.setValue(5432);
        pnlCfgPgSql.add(txtPuertoPgSql);
        txtPuertoPgSql.setBounds(240, 35, 70, 20);

        jLabel7.setText("Puerto (Por defecto 5432)");
        pnlCfgPgSql.add(jLabel7);
        jLabel7.setBounds(15, 40, 165, 15);

        jLabel8.setText("Base de Datos");
        pnlCfgPgSql.add(jLabel8);
        jLabel8.setBounds(15, 115, 130, 15);

        jLabel9.setText("Password");
        pnlCfgPgSql.add(jLabel9);
        jLabel9.setBounds(15, 90, 75, 15);

        jLabel10.setText("Usuario (Por defecto postgres)");
        pnlCfgPgSql.add(jLabel10);
        jLabel10.setBounds(15, 65, 200, 15);

        txtUserPgSql.setText("postgres");
        pnlCfgPgSql.add(txtUserPgSql);
        txtUserPgSql.setBounds(240, 65, 120, 19);

        pnlCfgPgSql.add(txtPassPgSql);
        txtPassPgSql.setBounds(240, 90, 120, 19);

        pnlCfgPgSql.add(txtBDPgSql);
        txtBDPgSql.setBounds(240, 115, 215, 19);

        btnSigSQLServer1.setText("Siguiente >");
        btnSigSQLServer1.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                btnSigSQLServer1ActionPerformed(evt);
            }
        });

        pnlCfgPgSql.add(btnSigSQLServer1);
        btnSigSQLServer1.setBounds(335, 145, 115, 30);

        btnSigSQLServer2.setText("< Anterior");
        btnSigSQLServer2.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                btnSigSQLServer2ActionPerformed(evt);
            }
        });

        pnlCfgPgSql.add(btnSigSQLServer2);
        btnSigSQLServer2.setBounds(210, 145, 115, 30);

        jTabbedPane1.addTab("Config. Servidor PostgreSQL", pnlCfgPgSql);

        pnlAccion.setLayout(null);

        btnEjecutar.setText("Ejecutar");
        btnEjecutar.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                btnEjecutarActionPerformed(evt);
            }
        });

        pnlAccion.add(btnEjecutar);
        btnEjecutar.setBounds(460, 10, 120, 50);

        chkPasaTablas.setText("Pasar Tablas");
        pnlAccion.add(chkPasaTablas);
        chkPasaTablas.setBounds(20, 10, 180, 23);

        tblEstado.setModel(new javax.swing.table.DefaultTableModel(
            new Object [][] {

            },
            new String [] {
                "Acción", "Estado"
            }
        ) {
            boolean[] canEdit = new boolean [] {
                false, false
            };

            public boolean isCellEditable(int rowIndex, int columnIndex) {
                return canEdit [columnIndex];
            }
        });
        jScrollPane1.setViewportView(tblEstado);

        pnlAccion.add(jScrollPane1);
        jScrollPane1.setBounds(10, 100, 610, 130);

        chkPasaPK.setText("Pasar Claves Primarias");
        pnlAccion.add(chkPasaPK);
        chkPasaPK.setBounds(20, 40, 180, 23);

        buttonGroup1.add(chkPasaDatos);
        chkPasaDatos.setText("Pasar Datos de Tablas");
        pnlAccion.add(chkPasaDatos);
        chkPasaDatos.setBounds(210, 10, 210, 23);

        buttonGroup1.add(chkPasaFG);
        chkPasaFG.setText("Pasar Claves Externas");
        pnlAccion.add(chkPasaFG);
        chkPasaFG.setBounds(210, 40, 230, 23);

        chkPasaIndices.setText("Pasar Indices");
        pnlAccion.add(chkPasaIndices);
        chkPasaIndices.setBounds(20, 65, 180, 23);

        jTabbedPane1.addTab("Acciones", pnlAccion);

        jPanel1.setLayout(null);

        jLabel11.setText("Software realizado por...");
        jPanel1.add(jLabel11);
        jLabel11.setBounds(25, 15, 200, 15);

        jLabel12.setText("TaRRaDeLo");
        jPanel1.add(jLabel12);
        jLabel12.setBounds(50, 35, 200, 15);

        jLabel13.setText("email de contacto: tarradelo@ya.com");
        jPanel1.add(jLabel13);
        jLabel13.setBounds(140, 35, 235, 15);

        jTable1.setModel(new javax.swing.table.DefaultTableModel(
            new Object [][] {
                {"bigint", "int8"},
                {"binary", "bytea"},
                {"bit", "bool"},
                {"char", "char"},
                {"datetime", "date"},
                {"decimal", "float4"},
                {"float", "float8"},
                {"image", "bytea"},
                {"int", "int4"},
                {"money", "float4"},
                {"nchar", "char"},
                {"ntext", "text"},
                {"numeric", "numeric"},
                {"nvarchar", "varchar"},
                {"real", "float8"},
                {"smalldatetime", "date"},
                {"smallint", "int2"},
                {"smallmoney", "float4"},
                {"sql_variant", "varchar"},
                {"sysname", "varchar"},
                {"text", "text"},
                {"timestamp", "timestamp"},
                {"tinyint", "int2"},
                {"uniqueidentifier", "int4"},
                {"varbinary", "bytea"},
                {"varchar", "varchar"}
            },
            new String [] {
                "Tipo SqlServer", "Tipo PostgreSQL"
            }
        ) {
            boolean[] canEdit = new boolean [] {
                false, false
            };

            public boolean isCellEditable(int rowIndex, int columnIndex) {
                return canEdit [columnIndex];
            }
        });
        jScrollPane2.setViewportView(jTable1);

        jPanel1.add(jScrollPane2);
        jScrollPane2.setBounds(10, 90, 265, 120);

        jLabel14.setText("Equivalencias de Tipos de Datos");
        jPanel1.add(jLabel14);
        jLabel14.setBounds(10, 65, 230, 15);

        jTabbedPane1.addTab("Acerca de...", jPanel1);

        getContentPane().add(jTabbedPane1, java.awt.BorderLayout.CENTER);

        pack();
    }//GEN-END:initComponents

    private void pnlCfgPgSqlComponentShown(java.awt.event.ComponentEvent evt) {//GEN-FIRST:event_pnlCfgPgSqlComponentShown
        // TODO add your handling code here:
        if (this.jTabbedPane1.getSelectedIndex() == 1){
            this.jTabbedPane1.setEnabledAt(2, false);
        }
    }//GEN-LAST:event_pnlCfgPgSqlComponentShown

    private void pnlCfgSqlSrvComponentShown(java.awt.event.ComponentEvent evt) {//GEN-FIRST:event_pnlCfgSqlSrvComponentShown
        // TODO add your handling code here:
        if (this.jTabbedPane1.getSelectedIndex() == 0){
            this.jTabbedPane1.setEnabledAt(1,false);
            this.jTabbedPane1.setEnabledAt(2, false);
        }
    }//GEN-LAST:event_pnlCfgSqlSrvComponentShown

    private void pnlCfgSqlSrvFocusGained(java.awt.event.FocusEvent evt) {//GEN-FIRST:event_pnlCfgSqlSrvFocusGained
        // TODO add your handling code here:
        
    }//GEN-LAST:event_pnlCfgSqlSrvFocusGained

    private void btnEjecutarActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_btnEjecutarActionPerformed
        // TODO add your handling code here:
        String []tablas;
        String []vistas;
        DefaultTableModel Modelo;
        Statement sentencia;
        String sqlCrea;
        boolean estado;
        int linea;
        int x;
        clsPasaDatos []pasos = null;
        
        // Lo primero es obtener las tablas del sistema...
        
        try {
            estado = false;
            vistas = null;
            tablas = this.obtieneTablasSqlServer();
            sentencia = conexPgSql.createStatement();
            sqlCrea = "";
            x = 0;
            // Lo primero, siempre vaciar el grid
            Modelo = (DefaultTableModel) this.tblEstado.getModel();
            Modelo.setNumRows(0);
            linea = 0;
            if (tablas != null){
                pasos = new clsPasaDatos[tablas.length];
                if (chkPasaTablas.isSelected()) {
                    
                    for (x = 0; x < tablas.length; x++, linea++){
                        //hacemos el proceso completo...
                        Modelo.addRow(new Object [] {null});
                        tblEstado.setValueAt("Creación de Tabla " + tablas[x],linea,0);
                        //antes de crear la tabla, se hara un drop table _____ cascade, por si existiese, el cascade,
                        // por si tiene claves externas...
                        try {
                            sentencia.execute("drop table " + tablas[x].toLowerCase().trim().replace (" ","_") + " cascade");
                        }catch (Exception ex){
                            sqlCrea = "";
                        }
                        // Y a continuación ejecutamos
                        
                        try {
                            sqlCrea = this.creaTablaPgSQL(tablas[x]);
                            sentencia.execute(sqlCrea);
                            tblEstado.setValueAt("Realizado", linea,1);
                        }catch (Exception ex2){
                            //coloresTabla.setRowColor(linea, java.awt.Color.RED);
                            tblEstado.setValueAt("No Realizado", linea,1);
                        }
                        
                    }
                }
                // Despues de las tablas, lo siguiente, las claves primarias
                if (chkPasaPK.isSelected()) {
                    for (x = 0; x < tablas.length; x++,linea++){
                        //hacemos el proceso completo...
                        Modelo.addRow(new Object [] {null});
                        tblEstado.setValueAt("Creación de Clave Primaria a Tabla " + tablas[x], linea,0);
                        
                        try {
                            sqlCrea = this.creaClavePrimaria(tablas[x]);
                            sentencia.execute(sqlCrea);
                            tblEstado.setValueAt("Realizado", linea,1);
                        }catch (Exception ex2){
                            //coloresTabla.setRowColor(linea, java.awt.Color.RED);
                            tblEstado.setValueAt("No Realizado", linea,1);
                        }
                        
                    }
                }
                // Despues de las claves primarias, lo siguiente, los indices
                if (chkPasaIndices.isSelected()) {
                    for (x = 0; x < tablas.length; x++,linea++){
                        //hacemos el proceso completo...
                        Modelo.addRow(new Object [] {null});
                        tblEstado.setValueAt("Creación de Indice a Tabla " + tablas[x], linea,0);
                        
                        try {
                            sqlCrea = this.creaIndices(tablas[x]);
                            sentencia.execute(sqlCrea);
                            tblEstado.setValueAt("Realizado", linea,1);
                        }catch (Exception ex2){
                            //coloresTabla.setRowColor(linea, java.awt.Color.RED);
                            tblEstado.setValueAt("No Realizado", linea,1);
                        }
                        
                    }
                }
                // Luego pasaremos los datos...
                if (chkPasaDatos.isSelected()) {
                    Semaforos = new Semaphore ((-1)*x + 1);
                    for (x = 0; x < tablas.length; x++,linea++){
                        //hacemos el proceso completo...
                        Modelo.addRow(new Object [] {null});
                        tblEstado.setValueAt("Paso de Datos de Tabla " + tablas[x], linea,0);
                        try {
                            pasos[x] = new clsPasaDatos();
                            pasos[x].Init(conexSqlServer,conexPgSql,tablas[x], this.tblEstado, linea, 1);
                            pasos[x].start();
                        }catch (Exception ex2){
                            tblEstado.setValueAt("No Realizado", linea,1);
                        }
                        
                    }
                    // Debemos sincronizar las hebras, para que no pase de aquí hasta que
                    // terminen todas, ya que podría entrar en la zona de creación de claves
                    // externas y liarla...
                    // Bloqueamos usando semaforos (Por ahora deshabilitado)...
                    //Semaforos.acquire();
                    
                }
                // Luego las claves externas
                if (chkPasaFG.isSelected()) {
                    for (x = 0; x < tablas.length; x++,linea++){
                        //hacemos el proceso completo...
                        Modelo.addRow(new Object [] {null});
                        tblEstado.setValueAt("Creación de Clave externa de Tabla " + tablas[x], linea,0);
                        try {
                            sqlCrea = this.creaClavesExternas(tablas[x]);
                            sentencia.execute(sqlCrea);
                            tblEstado.setValueAt("Realizado", linea,1);
                        }catch (Exception ex2){
                            //coloresTabla.setRowColor(linea, java.awt.Color.RED);
                            tblEstado.setValueAt("No Realizado", linea,1);
                        }
                    }
                }
                
            }
        }catch (Exception e){
        }
    }//GEN-LAST:event_btnEjecutarActionPerformed

    private void btnSigSQLServer1ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_btnSigSQLServer1ActionPerformed
        // TODO add your handling code here:
        String consultaCreate;
        String consultaPK, consultaFG;
        String [] tablas;
        if ((conexPgSql = conectaPgSql (this.txtSrvPgSql.getText(),Integer.parseInt(String.valueOf(this.txtPuertoPgSql.getValue())),this.txtUserPgSql.getText(),this.txtPassPgSql.getText(),this.txtBDPgSql.getText())) != null) {
            this.jTabbedPane1.setEnabledAt(2,true);
            this.jTabbedPane1.setSelectedIndex(2);
        }
        
    }//GEN-LAST:event_btnSigSQLServer1ActionPerformed

    private void btnSigSQLServer2ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_btnSigSQLServer2ActionPerformed
        // TODO add your handling code here:
        this.jTabbedPane1.setSelectedIndex(0);
    }//GEN-LAST:event_btnSigSQLServer2ActionPerformed

    private void btnSigSQLServerActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_btnSigSQLServerActionPerformed
        // TODO add your handling code here:
        if ((conexSqlServer = conectaSqlServer (this.txtSrvSqlServer.getText(),Integer.parseInt(String.valueOf(this.txtPuertoSqlServer.getValue())),this.txtUserSqlServer.getText(),this.txtPassSqlServer.getText(),this.txtBDSqlServer.getText())) != null) {
            this.jTabbedPane1.setEnabledAt(1,true);
            this.jTabbedPane1.setSelectedIndex(1);
        }
    }//GEN-LAST:event_btnSigSQLServerActionPerformed
    
    /**
     * @param args the command line arguments
     */
    public static void main(String args[]) {
        java.awt.EventQueue.invokeLater(new Runnable() {
            public void run() {
                new frmPrincipal().setVisible(true);
            }
        });
    }
    
    // Variables declaration - do not modify//GEN-BEGIN:variables
    private javax.swing.JButton btnEjecutar;
    private javax.swing.JButton btnSigSQLServer;
    private javax.swing.JButton btnSigSQLServer1;
    private javax.swing.JButton btnSigSQLServer2;
    private javax.swing.ButtonGroup buttonGroup1;
    private javax.swing.JRadioButton chkPasaDatos;
    private javax.swing.JRadioButton chkPasaFG;
    private javax.swing.JCheckBox chkPasaIndices;
    private javax.swing.JCheckBox chkPasaPK;
    private javax.swing.JCheckBox chkPasaTablas;
    private javax.swing.JLabel jLabel1;
    private javax.swing.JLabel jLabel10;
    private javax.swing.JLabel jLabel11;
    private javax.swing.JLabel jLabel12;
    private javax.swing.JLabel jLabel13;
    private javax.swing.JLabel jLabel14;
    private javax.swing.JLabel jLabel2;
    private javax.swing.JLabel jLabel3;
    private javax.swing.JLabel jLabel4;
    private javax.swing.JLabel jLabel5;
    private javax.swing.JLabel jLabel6;
    private javax.swing.JLabel jLabel7;
    private javax.swing.JLabel jLabel8;
    private javax.swing.JLabel jLabel9;
    private javax.swing.JPanel jPanel1;
    private javax.swing.JScrollPane jScrollPane1;
    private javax.swing.JScrollPane jScrollPane2;
    private javax.swing.JTabbedPane jTabbedPane1;
    private javax.swing.JTable jTable1;
    private javax.swing.JPanel pnlAccion;
    private javax.swing.JPanel pnlCfgPgSql;
    private javax.swing.JPanel pnlCfgSqlSrv;
    private javax.swing.JTable tblEstado;
    private javax.swing.JTextField txtBDPgSql;
    private javax.swing.JTextField txtBDSqlServer;
    private javax.swing.JTextField txtPassPgSql;
    private javax.swing.JTextField txtPassSqlServer;
    private javax.swing.JSpinner txtPuertoPgSql;
    private javax.swing.JSpinner txtPuertoSqlServer;
    private javax.swing.JTextField txtSrvPgSql;
    private javax.swing.JTextField txtSrvSqlServer;
    private javax.swing.JTextField txtUserPgSql;
    private javax.swing.JTextField txtUserSqlServer;
    // End of variables declaration//GEN-END:variables
    
    private String tipoPostGreSql (int tipoSqlServer){
        String tipoPgSql;
        
        switch (tipoSqlServer){
            case 127: // bigint
                tipoPgSql="int8";
                break;
            case 173: // binary
                tipoPgSql="bytea";
                break;
            case 104: // bit
                tipoPgSql="bool";
                break;
            case 175: //char
                tipoPgSql="char";
                break;
            case 61: // datetime
                tipoPgSql="date";
                break;
            case 106: //decimal
                tipoPgSql="float4";
                break;
            case 62: //float
                tipoPgSql="float8";
                break;
            case 34: //image
                tipoPgSql="bytea";
                break;
            case 56: //int
                tipoPgSql="int4";
                break;
            case 60: // money
                tipoPgSql="float4";
                break;
            case 239: //nchar
                tipoPgSql="char";
                break;
            case 99: // ntext
                tipoPgSql="text";
                break;
            case 108: //numeric
                tipoPgSql="numeric";
                break;
            case 231: //nvarchar y sysname
                tipoPgSql="varchar";
                break;
            case 59: //real
                tipoPgSql="float8";
                break;
            case 58: // smalldatetime
                tipoPgSql="date";
                break;
            case 52: //smallint
                tipoPgSql="int2";
                break;
            case 122: // smallmoney
                tipoPgSql="money";
                break;
            case 98: //sql_variant
                tipoPgSql="varchar";
                break;
            case 35: // text
                tipoPgSql="text";
                break;
            case 189: // timestamp
                tipoPgSql="timestamp";
                break;
            case 48: // tinyint
                tipoPgSql="int2";
                break;
            case 36: //uniqueidentifier
                tipoPgSql="serial";
                break;
            case 165: //varbinary
                tipoPgSql="bytea";
                break;
            case 167: //varchar
                tipoPgSql="varchar";
                break;    
            default:
                tipoPgSql="varchar";
        }
        return tipoPgSql;
    }
    
    // Crea Indices que NO son clave primaria...
    private String creaIndices (String nombreTabla){
        String Sql, consSql;
        Statement sentencia;
        ResultSet rs;
        boolean primeraLinea;
        String indiceAnt;
        
        Sql = "";
        indiceAnt = "";
        // Cogemos aquellos indices que NO son PK, o sea, ke no son clave primaria...
        consSql  = " SELECT DISTINCT sysindexkeys.colid, syscolumns.name AS nombreCol, sysindexes.name as nombreIndice";
        consSql += " FROM sysindexes INNER JOIN";
        consSql += "      sysindexkeys ON sysindexes.id = sysindexkeys.id AND sysindexes.indid=sysindexkeys.indid INNER JOIN";
        consSql += "      syscolumns ON syscolumns.id = sysindexkeys.id AND sysindexkeys.colid = syscolumns.colid ";
        consSql += " where  sysindexes.name not in (select name from sysobjects where xtype='PK') and sysindexes.id in (select distinct id from sysobjects where name='"+ nombreTabla.trim() + "') order by sysindexes.name, sysindexkeys.colid";
        try {
            sentencia = conexSqlServer.createStatement();
            rs = sentencia.executeQuery(consSql);

            // Ahora añadimos las columnas al indice
            primeraLinea = false;
            while (rs.next()){
                if (primeraLinea && indiceAnt.compareTo(rs.getString("nombreIndice").trim()) != 0){
                    primeraLinea = false;
                    Sql += ");\n";
                }
                if (!primeraLinea){
                    primeraLinea = true;
                    
                    Sql += "create index \"" + rs.getString("nombreIndice").trim() + "\" on " + nombreTabla.trim().toLowerCase();
                    Sql += "(" + rs.getString ("nombreCol");
                }else{
                    Sql += ", " + rs.getString ("nombreCol");
                }
                indiceAnt =rs.getString("nombreIndice").trim();
            }
            Sql += ");\n";
        }catch (Exception e){
            Sql = "";
        }
        return Sql;
        
    }
    private String creaClavePrimaria (String nombreTabla){
        String Sql, consSql;
        Statement sentencia;
        ResultSet rs;
        boolean primeraLinea;
      
        Sql = "ALTER TABLE " + nombreTabla.toLowerCase().trim().replace (" ","_") + " ADD ";
        // Una vez definida la estructura de la tabla, vamos a por las claves primarias y luego las claves foraneas
        consSql  = " SELECT DISTINCT sysindexkeys.colid, syscolumns.name AS nombreCol, sysindexes.name as nombreIndice";
        consSql += " FROM sysindexes INNER JOIN";
        consSql += "      sysindexkeys ON sysindexes.id = sysindexkeys.id AND sysindexes.indid=sysindexkeys.indid INNER JOIN";
        consSql += "      syscolumns ON syscolumns.id = sysindexkeys.id AND sysindexkeys.colid = syscolumns.colid ";
        consSql += " where  sysindexes.name in (select name from sysobjects where xtype='PK') and sysindexes.id in (select distinct id from sysobjects where name='"+ nombreTabla.trim() + "') order by sysindexkeys.colid";

        try {
            sentencia = conexSqlServer.createStatement();
            rs = sentencia.executeQuery(consSql);

            // Ahora añadimos las columnas al indice
            primeraLinea = false;
            while (rs.next()){
                if (!primeraLinea){
                    primeraLinea = true;
                    Sql += " CONSTRAINT \"" + rs.getString("nombreIndice").trim() + "\" PRIMARY KEY (";
                    Sql += rs.getString("nombreCol").toLowerCase();
                }else{
                    Sql += ", " + rs.getString("nombreCol").toLowerCase();
                }
            }
            Sql += ");";
        } catch (Exception e){
            Sql = "";
        }
        
        return Sql;
    }
    
    private String obtieneNombreCol (int cdTabla, int cdCol){
        String sql;
        String retorno;
        Statement sentencia;
        ResultSet rs;
        sql = "select name from syscolumns where id="+ String.valueOf(cdTabla) + " and colid=" + String.valueOf (cdCol);
        retorno = "";
        try {
            sentencia = conexSqlServer.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            rs = sentencia.executeQuery(sql);
            rs.next();
            retorno = rs.getString("name");
        }catch (Exception e){
            retorno = "";
        }
        return retorno;
    }
    private String creaClavesExternas (String nombreTabla){
        String Sql, consSql;
        Statement sentencia;
        ResultSet rs;
        boolean primeraLinea, primeraLinea2;
        String nomTablaRef, cadRef, cadFG, claveAnt;
        int x;
        
        Sql = "ALTER TABLE " + nombreTabla.toLowerCase().trim().replace (" ","_") + " ADD ";
        
        // Y ya tenemos las claves primarias, solo nos quedan las foraneas, cuyo proceso es similar al de las primarias...
        consSql = "SELECT     sysobjects.name AS nombreRel, sysobjects_1.name AS nomTablaF, sysobjects_2.name AS nomTablaR, sysreferences.*";
        consSql += " FROM         sysobjects INNER JOIN ";
        consSql += " sysreferences ON sysobjects.id = sysreferences.constid INNER JOIN";
        consSql += " sysobjects sysobjects_1 ON sysreferences.fkeyid = sysobjects_1.id INNER JOIN";
        consSql += " sysobjects sysobjects_2 ON sysreferences.rkeyid = sysobjects_2.id";
        consSql += " WHERE     (sysobjects.xtype = 'F') and (sysobjects_1.name='"+ nombreTabla.trim() + "')";
        
        try {
            rs = null;
            sentencia = conexSqlServer.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            rs = sentencia.executeQuery(consSql);
            primeraLinea = false;
            primeraLinea2 = false;
            nomTablaRef = "";
            cadRef = "";
            cadFG = "";
            claveAnt = "";
            rs.next();
            x = 1;
            do{
                if (claveAnt.compareToIgnoreCase(rs.getString("nombreRel")) == 0){
                    primeraLinea = true;
                }
                if (!primeraLinea2){
                    primeraLinea2 = true;
                    Sql += " CONSTRAINT \"" + rs.getString("nombreRel").trim() + "\" FOREIGN KEY (";     
                }else{
                    Sql += ") REFERENCES " + nomTablaRef + "(" + cadRef +")";
                    cadRef = "";
                    Sql += ",\nADD CONSTRAINT \"" + rs.getString("nombreRel").trim() + "\" FOREIGN KEY (";     
                }
                for (x=1;x <=16;x++){
                    if (rs.getInt("fKey" + String.valueOf(x)) != 0){
                        if (x>1){
                            Sql += ", ";
                            cadRef += ", ";
                        }
                        Sql    += this.obtieneNombreCol(rs.getInt("fKeyId"),rs.getInt("fKey" + String.valueOf(x))).trim().toLowerCase();
                        cadRef += this.obtieneNombreCol(rs.getInt("rKeyId"),rs.getInt("rKey" + String.valueOf(x))).trim().toLowerCase();
                    }else{
                        break;
                    }
                }
                nomTablaRef = rs.getString("nomTablaR").toLowerCase().trim().replace (" ","_");
                claveAnt = rs.getString("nombreRel").trim();
            }while (rs.next());
            if (Sql.trim().compareTo("") != 0){
                Sql += ") REFERENCES " + nomTablaRef + "(" + cadRef +");\n";
            }
            rs.close();
        }catch (Exception ex2) {
            Sql = "";
        }
        return Sql;
    }
    
    private String creaTablaPgSQL (String nombreTabla)
    {
        String sql;
        String consSql;
        Statement sentencia; 
        ResultSet rs;
        boolean primeraLinea;
        boolean primeraLinea2;
        String cadFG, cadRef, nomTablaRef;
        String nombreCampo;
        String tipoCampo;
        int longTipoCampo;
        int conv;
        String valorDefecto;
        
        // Preparamos la consulta que obtendrá los datos de las columnas de la tabla...
        consSql =  "select distinct syscolumns.colid, syscolumns.id as codTabla,syscolumns.name as nombreCampo,systypes.xtype as codTipo,syscolumns.length as longTipo, isnullable as siNulo,syscomments.text as valorDefecto from syscolumns ";
        consSql += " inner join systypes on systypes.xtype=syscolumns.xtype ";
        consSql += " left join syscomments on syscomments.id=syscolumns.cdefault";
        consSql += " where syscolumns.id in (select distinct id from sysobjects where name='"+ nombreTabla.trim() + "') order by syscolumns.colid";
        sql = "";
        conv = 0;
        nombreCampo = "";
        tipoCampo = "";
        valorDefecto = "";
        longTipoCampo = 0;
        try {
            sentencia = conexSqlServer.createStatement();
            rs = sentencia.executeQuery(consSql);
            sql = "create table " + nombreTabla.trim().toLowerCase().replace(" ","_") + "(\n";
            primeraLinea = false;
            while (rs.next())
            {
                nombreCampo = rs.getString("nombreCampo").toLowerCase();
                tipoCampo = tipoPostGreSql(rs.getInt("codTipo")).toLowerCase();
                longTipoCampo = rs.getInt("longTipo");
                // Probablemente falte algun tipo de dato contemplado como numerico                
                if (!(tipoCampo.contains("int"))  && !(tipoCampo.contains("float")) &&  !(tipoCampo.contains("decimal")) && !(tipoCampo.contains("bool"))){
                    valorDefecto = "('')";
                }else if (!(tipoCampo.contains("bool"))){
                    valorDefecto = "(0)";
                }else{
                    valorDefecto = "(false)";
                }
                if (rs.getObject("valorDefecto") != null){
                    if (!(tipoCampo.contains("bool"))){
                        valorDefecto = rs.getString("valorDefecto");
                    }else{
                        try {
                            conv = Integer.parseInt(rs.getString("valorDefecto").substring(1,rs.getString("valorDefecto").length() - 1));
                            valorDefecto = "(true)";
                        }catch (Exception ex3){
                            valorDefecto = "(false)";
                        }
                        
                        
                    }
                }
                // Mientras haya datos que sacar...
                if (!primeraLinea){
                    primeraLinea = true;
                    
                    sql += nombreCampo + " " + tipoCampo;
                    
                    if (tipoCampo.contains("char")){
                        sql += "(" + longTipoCampo + ")";
                    }
                    if (rs.getInt("siNulo") == 0){
                        sql += " NOT NULL DEFAULT " + valorDefecto.substring(1,valorDefecto.length() - 1);
                    }
                }else{
                    sql += ",\n" + nombreCampo + " " + tipoCampo;
                    if (tipoCampo.contains("char")){
                        sql += "(" + longTipoCampo + ")";
                    }
                    if (rs.getInt("siNulo") == 0){
                        sql += " NOT NULL DEFAULT " + valorDefecto.substring(1,valorDefecto.length() - 1);
                    }
                }
            }
            
            
            
            // Y por fin, cerramos la consulta sql...
            sql += "\n) WITH OIDS;";
        }catch (Exception e){
            sql = "";
        }
        
        return sql;
    }
    // Crearemos las vistas desde Sql Server hacia postgresql
    private void CreaVistas (int linea){
        String sql;
        String consSql;
        Statement sentencia; 
        Statement sentenciaPg;
        ResultSet rs;
        int line;
    
        line = linea;
        consSql  = "SELECT     sysobjects.id AS id, sysobjects.name AS nombre, syscomments.text AS Definicion";
        consSql += " FROM         sysobjects INNER JOIN";
        consSql += " syscomments ON sysobjects.id = syscomments.id";
        consSql += " WHERE     (sysobjects.xtype = 'V') AND (sysobjects.category = 0)";
//        try {
//            sentencia = conexSqlServer.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
//            sentenciaPg = conexPgSql.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
//            rs = sentencia.executeQuery(consSql);
//            while (!rs.next()){
//                sql = "create view \""+ rs.getString("nombre").toLowerCase().trim().replace(" ","_") +"\" as \n";
//                
//            }
//        }catch (Exception e){
//            
//        }
    }
}
