Search in sources :

Example 16 with Table

use of org.obeonetwork.dsl.database.Table in project InformationSystem by ObeoNetwork.

the class PostGresDataBaseBuilder method buildColumnConstraint.

@Override
protected void buildColumnConstraint(DatabaseMetaData metaData, TableContainer owner, Column column) {
    Table table = column.getOwner();
    String key = table.getName().toUpperCase();
    // we add all constraints for a table at the same time
    if (cacheConstraints == null) {
        cacheConstraints = new HashMap<String, Collection<PostgreSQLConstraint>>();
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        try {
            PreparedStatement psmt = metaData.getConnection().prepareStatement("SELECT tc.constraint_name, pgc.consrc	, tc.table_name " + "FROM information_schema.table_constraints tc " + "LEFT JOIN pg_catalog.pg_constraint pgc " + "ON pgc.conname = tc.constraint_name " + "WHERE tc.table_schema = ? and tc.constraint_type = 'CHECK' and tc.constraint_name not like '%_not_null'");
            psmt.setString(1, schemaName);
            rs = psmt.executeQuery();
            while (rs.next()) {
                String name = rs.getString(1);
                String expression = rs.getString(2);
                String tableName = rs.getString(3);
                String key2 = tableName.toUpperCase();
                Collection<PostgreSQLConstraint> constraints = cacheConstraints.get(key2);
                if (constraints == null) {
                    constraints = new ArrayList<PostgreSQLConstraint>();
                }
                constraints.add(new PostgreSQLConstraint(tableName, name, expression));
                cacheConstraints.put(key2, constraints);
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            JdbcUtils.closeStatement(pstmt);
            JdbcUtils.closeResultSet(rs);
        }
    }
    if (cacheConstraints.containsKey(key)) {
        for (PostgreSQLConstraint pgConstraint : cacheConstraints.get(key)) {
            Constraint constraint = CreationUtils.createConstraint(table, pgConstraint.name);
            constraint.setExpression(pgConstraint.expression);
        }
        // We remove from the cache or the constraints would be added for every column in the table
        cacheConstraints.remove(key);
    }
}
Also used : Table(org.obeonetwork.dsl.database.Table) AbstractTable(org.obeonetwork.dsl.database.AbstractTable) Constraint(org.obeonetwork.dsl.database.Constraint) ResultSet(java.sql.ResultSet) Collection(java.util.Collection) PreparedStatement(java.sql.PreparedStatement) SQLException(java.sql.SQLException)

Example 17 with Table

use of org.obeonetwork.dsl.database.Table in project InformationSystem by ObeoNetwork.

the class PostGresDataBaseBuilder method buildSequences.

private void buildSequences(TableContainer owner) {
    ResultSet rs = null;
    PreparedStatement pstmt = null;
    try {
        PreparedStatement psmt = metaData.getConnection().prepareStatement("SELECT s.SEQUENCE_NAME, s.INCREMENT, s.MINIMUM_VALUE, s.MAXIMUM_VALUE, s.START_VALUE, s.CYCLE_OPTION , pg_catalog.obj_description(c.oid) " + "FROM INFORMATION_SCHEMA.SEQUENCES s " + "LEFT JOIN PG_CATALOG.pg_class c " + "ON c.relname = s.SEQUENCE_NAME " + "AND c.relkind = 'S' " + "WHERE s.SEQUENCE_SCHEMA = '" + schemaName + "'");
        rs = psmt.executeQuery();
        while (rs.next()) {
            String name = rs.getString(1);
            BigInteger increment = getBigIntValueForColumn(rs, 2);
            BigInteger minValue = getBigIntValueForColumn(rs, 3);
            BigInteger maxValue = getBigIntValueForColumn(rs, 4);
            BigInteger start = getBigIntValueForColumn(rs, 5);
            String cycleAsString = rs.getString(6);
            boolean cycle = "YES".equals(cycleAsString);
            String comment = rs.getString(7);
            // Retrieve CACHE value
            BigInteger cacheValue = null;
            PreparedStatement psmtCache = metaData.getConnection().prepareStatement("SELECT CACHE_VALUE FROM " + schemaName + "." + name);
            ResultSet rsCache = psmtCache.executeQuery();
            if (rsCache.next()) {
                cacheValue = getBigIntValueForColumn(rsCache, 1);
            }
            Sequence sequence = CreationUtils.createSequence(owner, name, increment, minValue, maxValue, start, cycle, cacheValue);
            sequence.setComments(comment);
            // Look for a table that could correspond to the sequence
            if (name.endsWith("_seq")) {
                String tableName = name.substring(0, name.length() - "_seq".length());
                AbstractTable abstractTable = queries.getTable(tableName);
                if (abstractTable != null && abstractTable instanceof Table) {
                    Table table = (Table) abstractTable;
                    if (table.getPrimaryKey() != null && table.getPrimaryKey().getColumns().size() == 1) {
                        Column column = table.getPrimaryKey().getColumns().get(0);
                        column.setSequence(sequence);
                    }
                }
            }
        }
    } catch (Exception ex) {
        ex.printStackTrace();
    } finally {
        JdbcUtils.closeStatement(pstmt);
        JdbcUtils.closeResultSet(rs);
    }
}
Also used : AbstractTable(org.obeonetwork.dsl.database.AbstractTable) Table(org.obeonetwork.dsl.database.Table) AbstractTable(org.obeonetwork.dsl.database.AbstractTable) Column(org.obeonetwork.dsl.database.Column) ResultSet(java.sql.ResultSet) BigInteger(java.math.BigInteger) PreparedStatement(java.sql.PreparedStatement) Sequence(org.obeonetwork.dsl.database.Sequence) SQLException(java.sql.SQLException)

Example 18 with Table

use of org.obeonetwork.dsl.database.Table in project InformationSystem by ObeoNetwork.

the class SQLServerDataBaseBuilder method buildSequences.

private void buildSequences(DatabaseMetaData metaData, TableContainer owner) {
    ResultSet rs = null;
    PreparedStatement pstmt = null;
    try {
        String query = "SELECT			CAST(seq.name AS NVARCHAR(128)), " + "				CAST(seq.increment AS NVARCHAR(128)), " + "				CAST(seq.minimum_value AS NVARCHAR(128)), " + "				CAST(seq.maximum_value AS NVARCHAR(128)), " + "				CAST(seq.start_value AS NVARCHAR(128)), " + "				CAST(seq.is_cycling AS NVARCHAR(128)), " + "				CAST(seq.cache_size AS NVARCHAR(128)) " + "FROM			sys.sequences AS seq " + "INNER JOIN		sys.schemas AS sch " + "ON				seq.schema_id = sch.schema_id " + "WHERE			sch.name = ?";
        pstmt = metaData.getConnection().prepareStatement(query);
        pstmt.setString(1, schemaName);
        rs = pstmt.executeQuery();
        while (rs.next()) {
            String name = rs.getString(1);
            BigInteger increment = getBigIntValueForColumn(rs, 2);
            BigInteger minValue = getBigIntValueForColumn(rs, 3);
            BigInteger maxValue = getBigIntValueForColumn(rs, 4);
            BigInteger start = getBigIntValueForColumn(rs, 5);
            boolean cycle = rs.getBoolean(6);
            BigInteger cacheSize = getBigIntValueForColumn(rs, 7);
            Sequence sequence = CreationUtils.createSequence(owner, name, increment, minValue, maxValue, start, cycle, cacheSize);
            // Look for a table that could correspond to the sequence
            if (name.endsWith("_SEQ")) {
                String tableName = name.substring(0, name.length() - "_SEQ".length());
                AbstractTable abstractTable = queries.getTable(tableName);
                if (abstractTable != null && abstractTable instanceof Table) {
                    Table table = (Table) abstractTable;
                    if (table.getPrimaryKey() != null && table.getPrimaryKey().getColumns().size() == 1) {
                        Column column = table.getPrimaryKey().getColumns().get(0);
                        column.setSequence(sequence);
                    }
                }
            }
        }
    } catch (Exception ex) {
        ex.printStackTrace();
    } finally {
        JdbcUtils.closeStatement(pstmt);
        JdbcUtils.closeResultSet(rs);
    }
}
Also used : AbstractTable(org.obeonetwork.dsl.database.AbstractTable) Table(org.obeonetwork.dsl.database.Table) AbstractTable(org.obeonetwork.dsl.database.AbstractTable) Column(org.obeonetwork.dsl.database.Column) ResultSet(java.sql.ResultSet) BigInteger(java.math.BigInteger) PreparedStatement(java.sql.PreparedStatement) Sequence(org.obeonetwork.dsl.database.Sequence) SQLException(java.sql.SQLException)

Example 19 with Table

use of org.obeonetwork.dsl.database.Table in project InformationSystem by ObeoNetwork.

the class ColumnServicesTest method isNullableNullTest.

@Test
public void isNullableNullTest() {
    Table table = getGSSerieTable();
    Column col = getColumn(table, "No table");
    assertEquals("No", new ColumnServices().isNullable(col));
}
Also used : Table(org.obeonetwork.dsl.database.Table) Column(org.obeonetwork.dsl.database.Column) Test(org.junit.Test) AbstractTest(org.obeonetwork.database.m2doc.services.common.AbstractTest)

Example 20 with Table

use of org.obeonetwork.dsl.database.Table in project InformationSystem by ObeoNetwork.

the class ColumnServicesTest method isAutoIncrementCheckNotTest.

// @Test
// public void isAutoIncrementCheckTest() {
// Table table = getGSSerieTable();
// Column col = getColumn(table, "RF_GENRE_ID");
// assertEquals("X", new ColumnServices().checkForeignKey(col));
// }
@Test
public void isAutoIncrementCheckNotTest() {
    Table table = getGSSerieTable();
    Column col = getColumn(table, "GS_SERIE_NOM");
    assertEquals("", new ColumnServices().checkInForeignKey(col));
}
Also used : Table(org.obeonetwork.dsl.database.Table) Column(org.obeonetwork.dsl.database.Column) Test(org.junit.Test) AbstractTest(org.obeonetwork.database.m2doc.services.common.AbstractTest)

Aggregations

Table (org.obeonetwork.dsl.database.Table)86 Column (org.obeonetwork.dsl.database.Column)41 Test (org.junit.Test)31 AbstractTest (org.obeonetwork.database.m2doc.services.common.AbstractTest)31 AbstractTable (org.obeonetwork.dsl.database.AbstractTable)22 EObject (org.eclipse.emf.ecore.EObject)17 ForeignKey (org.obeonetwork.dsl.database.ForeignKey)16 ArrayList (java.util.ArrayList)11 ForeignKeyElement (org.obeonetwork.dsl.database.ForeignKeyElement)7 Index (org.obeonetwork.dsl.database.Index)7 ResultSet (java.sql.ResultSet)6 SQLException (java.sql.SQLException)6 ReferencesTableSettings (org.eclipse.emf.eef.runtime.ui.widgets.referencestable.ReferencesTableSettings)6 Viewer (org.eclipse.jface.viewers.Viewer)6 ViewerFilter (org.eclipse.jface.viewers.ViewerFilter)6 Constraint (org.obeonetwork.dsl.database.Constraint)6 Sequence (org.obeonetwork.dsl.database.Sequence)6 BigInteger (java.math.BigInteger)5 PreparedStatement (java.sql.PreparedStatement)5 EObjectPropertiesEditionContext (org.eclipse.emf.eef.runtime.context.impl.EObjectPropertiesEditionContext)5