Repository: xerial/sqlite-jdbc (java)

Issue:
Connection.getMetaData().getColumns returns wrong/fixed values for column data type details
Using the `ResultSet` returned by `Connection.getMetaData().getColumns()` returns wrong values for the size of the column.

Especially these values are wrong:
- `COLUMN_SIZE` (fixed `10`)
- `DECIMAL_DIGITS` (fixed `2000000000`)

The `DATA_TYPE` is correct, but the client is unable to detect the size of the column or the precision of floating point values.

_Example:_
I have a table with this column: `ORIENTATION REAL(6,3)`

This is what I get:

- `COLUMN_NAME`=`"ORIENTATION"` (correct)
- `TYPE_NAME`=`"REAL(6,3)"` (correct)
- `DATA_TYPE`=`6` (correct, is `java.sql.Types.FLOAT`)
- `COLUMN_SIZE`=`2000000000` (wrong, should be `9`?, `6+3`)
- `DECIMAL_DIGITS`=`10` (wrong, should be `3`)

The cause for this can be found [here](https://github.com/xerial/sqlite-jdbc/blob/master/src/main/java/org/sqlite/jdbc3/JDBC3DatabaseMetaData.java#L920) and [here](https://github.com/xerial/sqlite-jdbc/blob/master/src/main/java/org/sqlite/jdbc3/JDBC3DatabaseMetaData.java#L922).

I just did not found a simple fix, so I add this issue instead of a pull request 😉 

Pull request description:
fix: fix Connection.getMetaData().getColumns returns wrong/fixed values for column data type details (#733)
* provide valid COLUMN_SIZE and DECIMAL_DIGITS values for columns queried using
 Connection.getMetaData().getColumns(java.lang.String, java.lang.String, java.lang.String, java.lang.String)
* add/adjust unit tests to test this change

Fixes #733 

Patch:
diff --git a/src/main/java/org/sqlite/jdbc3/JDBC3DatabaseMetaData.java b/src/main/java/org/sqlite/jdbc3/JDBC3DatabaseMetaData.java
index 882976fd..0ffa8f80 100644
--- a/src/main/java/org/sqlite/jdbc3/JDBC3DatabaseMetaData.java
+++ b/src/main/java/org/sqlite/jdbc3/JDBC3DatabaseMetaData.java
@@ -917,9 +917,9 @@ public abstract class JDBC3DatabaseMetaData extends org.sqlite.core.CoreDatabase
         StringBuilder sql = new StringBuilder(700);
         sql.append("select null as TABLE_CAT, null as TABLE_SCHEM, tblname as TABLE_NAME, ")
                 .append(
-                        "cn as COLUMN_NAME, ct as DATA_TYPE, tn as TYPE_NAME, 2000000000 as COLUMN_SIZE, ")
+                        "cn as COLUMN_NAME, ct as DATA_TYPE, tn as TYPE_NAME, colSize as COLUMN_SIZE, ")
                 .append(
-                        "2000000000 as BUFFER_LENGTH, 10   as DECIMAL_DIGITS, 10   as NUM_PREC_RADIX, ")
+                        "2000000000 as BUFFER_LENGTH, colDecimalDigits as DECIMAL_DIGITS, 10   as NUM_PREC_RADIX, ")
                 .append("colnullable as NULLABLE, null as REMARKS, colDefault as COLUMN_DEF, ")
                 .append(
                         "0    as SQL_DATA_TYPE, 0    as SQL_DATETIME_SUB, 2000000000 as CHAR_OCTET_LENGTH, ")
@@ -996,6 +996,10 @@ public abstract class JDBC3DatabaseMetaData extends org.sqlite.core.CoreDatabase
                         }
                         colFound = true;
 
+                        // default values
+                        int iColumnSize = 2000000000;
+                        int iDecimalDigits = 10;
+
                         /*
                          * improved column types
                          * ref http://www.sqlite.org/datatype3.html - 2.1 Determination Of Column Affinity
@@ -1011,14 +1015,62 @@ public abstract class JDBC3DatabaseMetaData extends org.sqlite.core.CoreDatabase
                         // rule #1 + boolean
                         if (TYPE_INTEGER.matcher(colType).find()) {
                             colJavaType = Types.INTEGER;
+                            // there are no decimal digits
+                            iDecimalDigits = 0;
                         } else if (TYPE_VARCHAR.matcher(colType).find()) {
                             colJavaType = Types.VARCHAR;
+                            // there are no decimal digits
+                            iDecimalDigits = 0;
                         } else if (TYPE_FLOAT.matcher(colType).find()) {
                             colJavaType = Types.FLOAT;
                         } else {
                             // catch-all
                             colJavaType = Types.VARCHAR;
                         }
+                        // try to find an (optional) length/dimension of the column
+                        int iStartOfDimension = colType.indexOf('(');
+                        if (iStartOfDimension > 0) {
+                            // find end of dimension
+                            int iEndOfDimension = colType.indexOf(')', iStartOfDimension);
+                            if (iEndOfDimension > 0) {
+                                String sInteger, sDecimal;
+                                // check for two values (integer part, fraction) divided by comma
+                                int iDimensionSeparator = colType.indexOf(',', iStartOfDimension);
+                                if (iDimensionSeparator > 0) {
+                                    sInteger =
+                                            colType.substring(
+                                                    iStartOfDimension + 1, iDimensionSeparator);
+                                    sDecimal =
+                                            colType.substring(
+                                                    iDimensionSeparator + 1, iEndOfDimension);
+                                }
+                                // only a single dimension
+                                else {
+                                    sInteger =
+                                            colType.substring(
+                                                    iStartOfDimension + 1, iEndOfDimension);
+                                    sDecimal = null;
+                                }
+                                // try to parse the values
+                                try {
+                                    int iInteger = Integer.parseUnsignedInt(sInteger);
+                                    // parse decimals?
+                                    if (sDecimal != null) {
+                                        iDecimalDigits = Integer.parseUnsignedInt(sDecimal);
+                                        // columns size equals sum of integer and decimal part of
+                                        // dimension
+                                        iColumnSize = iInteger + iDecimalDigits;
+                                    } else {
+                                        // no decimals
+                                        iDecimalDigits = 0;
+                                        // columns size equals dimension
+                                        iColumnSize = iInteger;
+                                    }
+                                } catch (NumberFormatException ex) {
+                                    // just ignore invalid dimension formats here
+                                }
+                            }
+                        }
 
                         sql.append("select ")
                                 .append(i + 1)
@@ -1028,6 +1080,10 @@ public abstract class JDBC3DatabaseMetaData extends org.sqlite.core.CoreDatabase
                                 .append("'")
                                 .append(colJavaType)
                                 .append("' as ct, ")
+                                .append(iColumnSize)
+                                .append(" as colSize, ")
+                                .append(iDecimalDigits)
+                                .append(" as colDecimalDigits, ")
                                 .append("'")
                                 .append(tableName)
                                 .append("' as tblname, ")
@@ -1077,7 +1133,7 @@ public abstract class JDBC3DatabaseMetaData extends org.sqlite.core.CoreDatabase
             sql.append(") order by TABLE_SCHEM, TABLE_NAME, ORDINAL_POSITION;");
         } else {
             sql.append(
-                    "select null as ordpos, null as colnullable, null as ct, null as tblname, null as cn, null as tn, null as colDefault, null as colautoincrement) limit 0;");
+                    "select null as ordpos, null as colnullable, null as ct, null as colsize, null as colDecimalDigits, null as tblname, null as cn, null as tn, null as colDefault, null as colautoincrement) limit 0;");
         }
 
         Statement stat = conn.createStatement();


Test patch:
diff --git a/src/test/java/org/sqlite/DBMetaDataTest.java b/src/test/java/org/sqlite/DBMetaDataTest.java
index 2a8ee6a1..3ca1590b 100644
--- a/src/test/java/org/sqlite/DBMetaDataTest.java
+++ b/src/test/java/org/sqlite/DBMetaDataTest.java
@@ -35,7 +35,7 @@ public class DBMetaDataTest {
         conn = DriverManager.getConnection("jdbc:sqlite:");
         stat = conn.createStatement();
         stat.executeUpdate(
-                "create table test (id integer primary key, fn float default 0.0, sn not null);");
+                "create table test (id integer primary key, fn float default 0.0, sn not null, intvalue integer(5), realvalue real(8,3));");
         stat.executeUpdate("create view testView as select * from test;");
         meta = conn.getMetaData();
     }
@@ -129,6 +129,8 @@ public class DBMetaDataTest {
         assertEquals(rs.getString("IS_NULLABLE"), "YES");
         assertNull(rs.getString("COLUMN_DEF"));
         assertEquals(rs.getInt("DATA_TYPE"), Types.INTEGER);
+        assertEquals(rs.getInt("COLUMN_SIZE"), 2000000000);
+        assertEquals(rs.getInt("DECIMAL_DIGITS"), 0);
         assertEquals(rs.getString("IS_AUTOINCREMENT"), "NO");
         assertFalse(rs.next());
 
@@ -138,6 +140,8 @@ public class DBMetaDataTest {
         assertEquals(rs.getInt("DATA_TYPE"), Types.FLOAT);
         assertEquals(rs.getString("IS_NULLABLE"), "YES");
         assertEquals(rs.getString("COLUMN_DEF"), "0.0");
+        assertEquals(rs.getInt("COLUMN_SIZE"), 2000000000);
+        assertEquals(rs.getInt("DECIMAL_DIGITS"), 10);
         assertEquals(rs.getString("IS_AUTOINCREMENT"), "NO");
         assertFalse(rs.next());
 
@@ -145,6 +149,28 @@ public class DBMetaDataTest {
         assertTrue(rs.next());
         assertEquals(rs.getString("COLUMN_NAME"), "sn");
         assertEquals(rs.getString("IS_NULLABLE"), "NO");
+        assertEquals(rs.getInt("COLUMN_SIZE"), 2000000000);
+        assertEquals(rs.getInt("DECIMAL_DIGITS"), 10);
+        assertNull(rs.getString("COLUMN_DEF"));
+        assertFalse(rs.next());
+
+        rs = meta.getColumns(null, null, "test", "intvalue");
+        assertTrue(rs.next());
+        assertEquals(rs.getString("COLUMN_NAME"), "intvalue");
+        assertEquals(rs.getInt("DATA_TYPE"), Types.INTEGER);
+        assertEquals(rs.getString("IS_NULLABLE"), "YES");
+        assertEquals(rs.getInt("COLUMN_SIZE"), 5);
+        assertEquals(rs.getInt("DECIMAL_DIGITS"), 0);
+        assertNull(rs.getString("COLUMN_DEF"));
+        assertFalse(rs.next());
+
+        rs = meta.getColumns(null, null, "test", "realvalue");
+        assertTrue(rs.next());
+        assertEquals(rs.getString("COLUMN_NAME"), "realvalue");
+        assertEquals(rs.getInt("DATA_TYPE"), Types.FLOAT);
+        assertEquals(rs.getString("IS_NULLABLE"), "YES");
+        assertEquals(rs.getInt("COLUMN_SIZE"), 11);
+        assertEquals(rs.getInt("DECIMAL_DIGITS"), 3);
         assertNull(rs.getString("COLUMN_DEF"));
         assertFalse(rs.next());
 
@@ -155,6 +181,10 @@ public class DBMetaDataTest {
         assertEquals(rs.getString("COLUMN_NAME"), "fn");
         assertTrue(rs.next());
         assertEquals(rs.getString("COLUMN_NAME"), "sn");
+        assertTrue(rs.next());
+        assertEquals(rs.getString("COLUMN_NAME"), "intvalue");
+        assertTrue(rs.next());
+        assertEquals(rs.getString("COLUMN_NAME"), "realvalue");
         assertFalse(rs.next());
 
         rs = meta.getColumns(null, null, "test", "%n");
@@ -175,6 +205,12 @@ public class DBMetaDataTest {
         assertTrue(rs.next());
         assertEquals(rs.getString("TABLE_NAME"), "test");
         assertEquals(rs.getString("COLUMN_NAME"), "sn");
+        assertTrue(rs.next());
+        assertEquals(rs.getString("TABLE_NAME"), "test");
+        assertEquals(rs.getString("COLUMN_NAME"), "intvalue");
+        assertTrue(rs.next());
+        assertEquals(rs.getString("TABLE_NAME"), "test");
+        assertEquals(rs.getString("COLUMN_NAME"), "realvalue");
         // VIEW "testView"
         assertTrue(rs.next());
         assertEquals(rs.getString("TABLE_NAME"), "testView");
@@ -185,6 +221,12 @@ public class DBMetaDataTest {
         assertTrue(rs.next());
         assertEquals(rs.getString("TABLE_NAME"), "testView");
         assertEquals(rs.getString("COLUMN_NAME"), "sn");
+        assertTrue(rs.next());
+        assertEquals(rs.getString("TABLE_NAME"), "testView");
+        assertEquals(rs.getString("COLUMN_NAME"), "intvalue");
+        assertTrue(rs.next());
+        assertEquals(rs.getString("TABLE_NAME"), "testView");
+        assertEquals(rs.getString("COLUMN_NAME"), "realvalue");
         assertFalse(rs.next());
 
         rs = meta.getColumns(null, null, "%", "%");
@@ -196,6 +238,10 @@ public class DBMetaDataTest {
         assertEquals(rs.getString("COLUMN_NAME"), "fn");
         assertTrue(rs.next());
         assertEquals(rs.getString("COLUMN_NAME"), "sn");
+        assertTrue(rs.next());
+        assertEquals(rs.getString("COLUMN_NAME"), "intvalue");
+        assertTrue(rs.next());
+        assertEquals(rs.getString("COLUMN_NAME"), "realvalue");
         // VIEW "testView"
         assertTrue(rs.next());
         assertEquals(rs.getString("TABLE_NAME"), "testView");
@@ -204,6 +250,10 @@ public class DBMetaDataTest {
         assertEquals(rs.getString("COLUMN_NAME"), "fn");
         assertTrue(rs.next());
         assertEquals(rs.getString("COLUMN_NAME"), "sn");
+        assertTrue(rs.next());
+        assertEquals(rs.getString("COLUMN_NAME"), "intvalue");
+        assertTrue(rs.next());
+        assertEquals(rs.getString("COLUMN_NAME"), "realvalue");
         assertFalse(rs.next());
 
         rs = meta.getColumns(null, null, "doesnotexist", "%");