[arrow-adbc] branch main updated: fix(c/driver/sqlite): add table types by default from arrow types (#955)

[li...@apache.org]

This is an automated email from the ASF dual-hosted git repository.

lidavidm pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/arrow-adbc.git


The following commit(s) were added to refs/heads/main by this push:
     new 737af65e fix(c/driver/sqlite): add table types by default from arrow types (#955)
737af65e is described below

commit 737af65ef1eec3167444189e4fb9fe1177ec1f33
Author: Solomon Choe <12...@users.noreply.github.com>
AuthorDate: Wed Aug 2 14:00:48 2023 -0700

    fix(c/driver/sqlite): add table types by default from arrow types (#955)
    
    Fixes #834.
    
    ## Testing
    
    - `SqliteStatementTest.SqlIngestDate32` fails, but it looks like this is
    failing from a clean pull as well (`a0d3de`).
    - `test_create_types` is to validate that everything's working on the
    Python side.
---
 c/driver/sqlite/sqlite.c                      | 38 +++++++++++++++++++++++++++
 python/adbc_driver_sqlite/tests/test_dbapi.py | 28 ++++++++++++++++++++
 2 files changed, 66 insertions(+)

diff --git a/c/driver/sqlite/sqlite.c b/c/driver/sqlite/sqlite.c
index 5521f99a..87e20c99 100644
--- a/c/driver/sqlite/sqlite.c
+++ b/c/driver/sqlite/sqlite.c
@@ -1008,6 +1008,8 @@ AdbcStatusCode SqliteStatementInitIngest(struct SqliteStatement* stmt,
     goto cleanup;
   }
 
+  struct ArrowError arrow_error = {0};
+  struct ArrowSchemaView view = {0};
   for (int i = 0; i < stmt->binder.schema.n_children; i++) {
     if (i > 0) {
       sqlite3_str_appendf(create_query, "%s", ", ");
@@ -1025,6 +1027,42 @@ AdbcStatusCode SqliteStatementInitIngest(struct SqliteStatement* stmt,
       goto cleanup;
     }
 
+    int status =
+        ArrowSchemaViewInit(&view, stmt->binder.schema.children[i], &arrow_error);
+    if (status != 0) {
+      SetError(error, "Failed to parse schema for column %d: %s (%d): %s", i,
+               strerror(status), status, arrow_error.message);
+      code = ADBC_STATUS_INTERNAL;
+      goto cleanup;
+    }
+
+    switch (view.type) {
+      case NANOARROW_TYPE_UINT8:
+      case NANOARROW_TYPE_UINT16:
+      case NANOARROW_TYPE_UINT32:
+      case NANOARROW_TYPE_UINT64:
+      case NANOARROW_TYPE_INT8:
+      case NANOARROW_TYPE_INT16:
+      case NANOARROW_TYPE_INT32:
+      case NANOARROW_TYPE_INT64:
+        sqlite3_str_appendf(create_query, " INTEGER");
+        break;
+      case NANOARROW_TYPE_FLOAT:
+      case NANOARROW_TYPE_DOUBLE:
+        sqlite3_str_appendf(create_query, " REAL");
+        break;
+      case NANOARROW_TYPE_STRING:
+      case NANOARROW_TYPE_LARGE_STRING:
+      case NANOARROW_TYPE_DATE32:
+        sqlite3_str_appendf(create_query, " TEXT");
+        break;
+      case NANOARROW_TYPE_BINARY:
+        sqlite3_str_appendf(create_query, " BLOB");
+        break;
+      default:
+        break;
+    }
+
     if (i > 0) {
       if (StringBuilderAppend(&insert_query, "%s", ", ") != 0) {
         SetError(error, "[SQLite] Call to StringBuilderAppend failed");
diff --git a/python/adbc_driver_sqlite/tests/test_dbapi.py b/python/adbc_driver_sqlite/tests/test_dbapi.py
index 9598485b..44648ff9 100644
--- a/python/adbc_driver_sqlite/tests/test_dbapi.py
+++ b/python/adbc_driver_sqlite/tests/test_dbapi.py
@@ -17,6 +17,7 @@
 
 from pathlib import Path
 
+import pyarrow as pa
 import pytest
 
 from adbc_driver_sqlite import dbapi
@@ -57,3 +58,30 @@ def test_autocommit(tmp_path: Path) -> None:
         assert not conn._autocommit
         with conn.cursor() as cur:
             cur.executescript("PRAGMA journal_mode = WAL")
+
+
+def test_create_types(tmp_path: Path) -> None:
+    db = tmp_path / "foo.sqlite"
+    with dbapi.connect(f"file:{db}") as conn:
+        tbl = pa.Table.from_pydict({"numbers": [1, 2], "letters": ["a", "b"]})
+
+        with conn.cursor() as cur:
+            cur.adbc_ingest("foo", tbl, "create")
+            cur.execute("PRAGMA table_info('foo')")
+            table_info = cur.fetchall()
+
+            assert len(table_info) == len(tbl.columns)
+            """
+            A tuple from table_info should return 6 values:
+            - cid
+            - name
+            - type
+            - notnull
+            - dflt_value
+            - pk
+            For details: https://www.sqlite.org/pragma.html
+            """
+            for col in table_info:
+                assert len(col) == 6
+            actual_types = [col[2] for col in table_info]
+            assert actual_types == ["INTEGER", "TEXT"]