SQLite Forum

Timeline
Login

20 forum posts by user d3x0r

2021-11-16
20:52 Reply: Feature Request : Get table name alias from prepared statement (artifact: fe6f40c78d user: d3x0r)

really kinda silly - someone spent some time making this super easy to extend, and noone actually wants to use that ability :)

This is just for reference, I understand there's apparently a difference between open source and open contribution; I don't care if you don't use my exact bytes to provide the functionality; be contrarian.

https://github.com/d3x0r/sqlite/tree/sqlite3_column_table_alias (which has a git PR representing the diff between master and it; not this is relevant information)

xx.diff

 src/loadext.c    |   6 ++
 src/select.c     |  25 +++++---
 src/sqlite.h.in  |   7 ++-
 src/sqlite3ext.h |   6 ++
 src/test1.c      |   2 +
 src/vdbe.h       |  13 ++--
 src/vdbeapi.c    |  17 +++++
 test/capi3f.test | 188 +++++++++++++++++++++++++++++++++++++++++++++++++++++++
 8 files changed, 249 insertions(+), 15 deletions(-)

diff --git a/src/loadext.c b/src/loadext.c
index 4edefec0c..0fb2c41e5 100644
--- a/src/loadext.c
+++ b/src/loadext.c
@@ -30,6 +30,8 @@
 # define sqlite3_column_database_name16 0
 # define sqlite3_column_table_name      0
 # define sqlite3_column_table_name16    0
+# define sqlite3_column_table_alias     0
+# define sqlite3_column_table_alias16   0
 # define sqlite3_column_origin_name     0
 # define sqlite3_column_origin_name16   0
 #endif
@@ -61,6 +63,7 @@
 # define sqlite3_value_text16le         0
 # define sqlite3_column_database_name16 0
 # define sqlite3_column_table_name16    0
+# define sqlite3_column_table_alias16   0
 # define sqlite3_column_origin_name16   0
 #endif
 
@@ -485,6 +488,9 @@ static const sqlite3_api_routines sqlite3Apis = {
   sqlite3_total_changes64,
   /* Version 3.37.0 and later */
   sqlite3_autovacuum_pages,
+  /* Version 3.38.0 and later */
+  sqlite3_column_table_alias,
+  sqlite3_column_table_alias16,
 };
 
 /* True if x is the directory separator character
diff --git a/src/select.c b/src/select.c
index ebb746467..e50b42c49 100644
--- a/src/select.c
+++ b/src/select.c
@@ -1751,9 +1751,9 @@ static void generateSortTail(
 ** the SQLITE_ENABLE_COLUMN_METADATA compile-time option is used.
 */
 #ifdef SQLITE_ENABLE_COLUMN_METADATA
-# define columnType(A,B,C,D,E) columnTypeImpl(A,B,C,D,E)
+# define columnType(A,B,C,D,E,F) columnTypeImpl(A,B,C,D,E,F)
 #else /* if !defined(SQLITE_ENABLE_COLUMN_METADATA) */
-# define columnType(A,B,C,D,E) columnTypeImpl(A,B)
+# define columnType(A,B,C,D,E,F) columnTypeImpl(A,B)
 #endif
 static const char *columnTypeImpl(
   NameContext *pNC, 
@@ -1763,7 +1763,8 @@ static const char *columnTypeImpl(
   Expr *pExpr,
   const char **pzOrigDb,
   const char **pzOrigTab,
-  const char **pzOrigCol
+  const char **pzOrigCol,
+  const char **pzOrigTabAlias
 #endif
 ){
   char const *zType = 0;
@@ -1772,6 +1773,7 @@ static const char *columnTypeImpl(
   char const *zOrigDb = 0;
   char const *zOrigTab = 0;
   char const *zOrigCol = 0;
+  char const *zOrigTabAlias = 0;
 #endif
 
   assert( pExpr!=0 );
@@ -1791,6 +1793,10 @@ static const char *columnTypeImpl(
         if( j<pTabList->nSrc ){
           pTab = pTabList->a[j].pTab;
           pS = pTabList->a[j].pSelect;
+#ifdef SQLITE_ENABLE_COLUMN_METADATA
+          zOrigTabAlias =
+              pTabList->a[j].zAlias?pTabList->a[j].zAlias:pTabList->a[j].zName;
+#endif
         }else{
           pNC = pNC->pNext;
         }
@@ -1839,7 +1845,7 @@ static const char *columnTypeImpl(
           sNC.pSrcList = pS->pSrc;
           sNC.pNext = pNC;
           sNC.pParse = pNC->pParse;
-          zType = columnType(&sNC, p,&zOrigDb,&zOrigTab,&zOrigCol); 
+          zType = columnType(&sNC, p,&zOrigDb,&zOrigTab,&zOrigCol,&zOrigTabAlias);
         }
       }else{
         /* A real table or a CTE table */
@@ -1885,7 +1891,7 @@ static const char *columnTypeImpl(
       sNC.pSrcList = pS->pSrc;
       sNC.pNext = pNC;
       sNC.pParse = pNC->pParse;
-      zType = columnType(&sNC, p, &zOrigDb, &zOrigTab, &zOrigCol); 
+      zType = columnType(&sNC, p, &zOrigDb, &zOrigTab, &zOrigCol, &zOrigTabAlias);
       break;
     }
 #endif
@@ -1897,6 +1903,7 @@ static const char *columnTypeImpl(
     *pzOrigDb = zOrigDb;
     *pzOrigTab = zOrigTab;
     *pzOrigCol = zOrigCol;
+    *pzOrigTabAlias = zOrigTabAlias;
   }
 #endif
   return zType;
@@ -1925,7 +1932,8 @@ static void generateColumnTypes(
     const char *zOrigDb = 0;
     const char *zOrigTab = 0;
     const char *zOrigCol = 0;
-    zType = columnType(&sNC, p, &zOrigDb, &zOrigTab, &zOrigCol);
+    const char *zOrigTabAlias = 0;
+    zType = columnType(&sNC, p, &zOrigDb, &zOrigTab, &zOrigCol, &zOrigTabAlias);
 
     /* The vdbe must make its own copy of the column-type and other 
     ** column specific strings, in case the schema is reset before this
@@ -1934,8 +1942,9 @@ static void generateColumnTypes(
     sqlite3VdbeSetColName(v, i, COLNAME_DATABASE, zOrigDb, SQLITE_TRANSIENT);
     sqlite3VdbeSetColName(v, i, COLNAME_TABLE, zOrigTab, SQLITE_TRANSIENT);
     sqlite3VdbeSetColName(v, i, COLNAME_COLUMN, zOrigCol, SQLITE_TRANSIENT);
+    sqlite3VdbeSetColName(v, i, COLNAME_TABLE_ALIAS, zOrigTabAlias, SQLITE_TRANSIENT);
 #else
-    zType = columnType(&sNC, p, 0, 0, 0);
+    zType = columnType(&sNC, p, 0, 0, 0, 0);
 #endif
     sqlite3VdbeSetColName(v, i, COLNAME_DECLTYPE, zType, SQLITE_TRANSIENT);
   }
@@ -2199,7 +2208,7 @@ void sqlite3SelectAddColumnTypeAndCollation(
     int n, m;
     pTab->tabFlags |= (pCol->colFlags & COLFLAG_NOINSERT);
     p = a[i].pExpr;
-    zType = columnType(&sNC, p, 0, 0, 0);
+    zType = columnType(&sNC, p, 0, 0, 0, 0);
     /* pCol->szEst = ... // Column size est for SELECT tables never used */
     pCol->affinity = sqlite3ExprAffinity(p);
     if( zType ){
diff --git a/src/sqlite.h.in b/src/sqlite.h.in
index 98a028b0b..d86391bce 100644
--- a/src/sqlite.h.in
+++ b/src/sqlite.h.in
@@ -4656,7 +4656,10 @@ const void *sqlite3_column_name16(sqlite3_stmt*, int N);
 ** ^The name of the database or table or column can be returned as
 ** either a UTF-8 or UTF-16 string.  ^The _database_ routines return
 ** the database name, the _table_ routines return the table name, and
-** the origin_ routines return the column name.
+** the origin_ routines return the column name.  _table_alias_ results
+** with the alias(if any) of the table associated with the column;
+** _table_ always returns the source table name, even if it has been
+** aliased, this returns the original table name if there is no alias.
 ** ^The returned string is valid until the [prepared statement] is destroyed
 ** using [sqlite3_finalize()] or until the statement is automatically
 ** reprepared by the first call to [sqlite3_step()] for a particular run
@@ -4694,6 +4697,8 @@ const char *sqlite3_column_table_name(sqlite3_stmt*,int);
 const void *sqlite3_column_table_name16(sqlite3_stmt*,int);
 const char *sqlite3_column_origin_name(sqlite3_stmt*,int);
 const void *sqlite3_column_origin_name16(sqlite3_stmt*,int);
+const char *sqlite3_column_table_alias(sqlite3_stmt*,int);
+const void *sqlite3_column_table_alias16(sqlite3_stmt*,int);
 
 /*
 ** CAPI3REF: Declared Datatype Of A Query Result
diff --git a/src/sqlite3ext.h b/src/sqlite3ext.h
index 9767daa01..cecb80691 100644
--- a/src/sqlite3ext.h
+++ b/src/sqlite3ext.h
@@ -344,6 +344,9 @@ struct sqlite3_api_routines {
   int (*autovacuum_pages)(sqlite3*,
      unsigned int(*)(void*,const char*,unsigned int,unsigned int,unsigned int),
      void*, void(*)(void*));
+  /* Version 3.38.0 and later */
+  const char * (*column_table_alias)(sqlite3_stmt*,int);
+  const void * (*column_table_alias16)(sqlite3_stmt*,int);
 };
 
 /*
@@ -655,6 +658,9 @@ typedef int (*sqlite3_loadext_entry)(
 #define sqlite3_total_changes64        sqlite3_api->total_changes64
 /* Version 3.37.0 and later */
 #define sqlite3_autovacuum_pages       sqlite3_api->autovacuum_pages
+/* Version 3.38.0 and later */
+#define sqlite3_column_table_alias     sqlite3_api->column_table_alias
+#define sqlite3_column_table_alias16   sqlite3_api->column_table_alias16
 #endif /* !defined(SQLITE_CORE) && !defined(SQLITE_OMIT_LOAD_EXTENSION) */
 
 #if !defined(SQLITE_CORE) && !defined(SQLITE_OMIT_LOAD_EXTENSION)
diff --git a/src/test1.c b/src/test1.c
index 6060290b9..c0d8a4682 100644
--- a/src/test1.c
+++ b/src/test1.c
@@ -8506,6 +8506,7 @@ int Sqlitetest1_Init(Tcl_Interp *interp){
 #ifdef SQLITE_ENABLE_COLUMN_METADATA
 { "sqlite3_column_database_name",test_stmt_utf8,(void*)sqlite3_column_database_name},
 { "sqlite3_column_table_name",test_stmt_utf8,(void*)sqlite3_column_table_name},
+{ "sqlite3_column_table_alias",test_stmt_utf8,(void*)sqlite3_column_table_alias},
 { "sqlite3_column_origin_name",test_stmt_utf8,(void*)sqlite3_column_origin_name},
 #endif
 
@@ -8521,6 +8522,7 @@ int Sqlitetest1_Init(Tcl_Interp *interp){
 {"sqlite3_column_database_name16",
   test_stmt_utf16, (void*)sqlite3_column_database_name16},
 {"sqlite3_column_table_name16", test_stmt_utf16, (void*)sqlite3_column_table_name16},
+{"sqlite3_column_table_alias16", test_stmt_utf16, (void*)sqlite3_column_table_alias16},
 {"sqlite3_column_origin_name16", test_stmt_utf16, (void*)sqlite3_column_origin_name16},
 #endif
 #endif
diff --git a/src/vdbe.h b/src/vdbe.h
index 3257ff68a..1ee2b66e3 100644
--- a/src/vdbe.h
+++ b/src/vdbe.h
@@ -140,13 +140,14 @@ typedef struct VdbeOpList VdbeOpList;
 ** The Vdbe.aColName array contains 5n Mem structures, where n is the 
 ** number of columns of data returned by the statement.
 */
-#define COLNAME_NAME     0
-#define COLNAME_DECLTYPE 1
-#define COLNAME_DATABASE 2
-#define COLNAME_TABLE    3
-#define COLNAME_COLUMN   4
+#define COLNAME_NAME        0
+#define COLNAME_DECLTYPE    1
+#define COLNAME_DATABASE    2
+#define COLNAME_TABLE       3
+#define COLNAME_COLUMN      4
+#define COLNAME_TABLE_ALIAS 5
 #ifdef SQLITE_ENABLE_COLUMN_METADATA
-# define COLNAME_N        5      /* Number of COLNAME_xxx symbols */
+# define COLNAME_N          6      /* Number of COLNAME_xxx symbols */
 #else
 # ifdef SQLITE_OMIT_DECLTYPE
 #   define COLNAME_N      1      /* Store only the name */
diff --git a/src/vdbeapi.c b/src/vdbeapi.c
index 5eeb5d1c0..6548b6a85 100644
--- a/src/vdbeapi.c
+++ b/src/vdbeapi.c
@@ -1321,6 +1321,23 @@ const void *sqlite3_column_origin_name16(sqlite3_stmt *pStmt, int N){
   return columnName(pStmt, N, 1, COLNAME_COLUMN);
 }
 #endif /* SQLITE_OMIT_UTF16 */
+
+/*
+** Return the alias or, if no alias specified, the name of the table from
+** which a result column derives. NULL is returned if the result name is
+** an expression or constant or anything else which is not an unambiguous
+** reference to a database table.
+*/
+const char *sqlite3_column_table_alias(sqlite3_stmt *pStmt, int N){
+  return columnName(
+      pStmt, N, 0, COLNAME_TABLE_ALIAS);
+}
+#ifndef SQLITE_OMIT_UTF16
+const void *sqlite3_column_table_alias16(sqlite3_stmt *pStmt, int N){
+  return columnName(
+      pStmt, N, 1, COLNAME_TABLE_ALIAS);
+}
+#endif /* SQLITE_OMIT_UTF16 */
 #endif /* SQLITE_ENABLE_COLUMN_METADATA */
 
 
diff --git a/test/capi3f.test b/test/capi3f.test
new file mode 100644
index 000000000..ca4efab4c
--- /dev/null
+++ b/test/capi3f.test
@@ -0,0 +1,188 @@
+# 2021 January 22
+#
+# The author disclaims copyright to this source code.  In place of
+# a legal notice, here is a blessing:
+#
+#    May you do good and not evil.
+#    May you find forgiveness for yourself and forgive others.
+#    May you share freely, never taking more than you give.
+#
+#***********************************************************************
+# This file implements regression tests for SQLite library.  The
+# focus of this script testing the callback-free C/C++ API.
+#
+# $Id: capi3f.test,v 1.70 2009/01/09 02:49:32 d3x0r Exp $
+#
+
+set testdir [file dirname $argv0]
+source $testdir/tester.tcl
+set ::testprefix capi3f
+
+# Do not use a codec for tests in this file, as the database file is
+# manipulated directly using tcl scripts (using the [hexio_write] command).
+#
+do_not_use_codec
+
+# Return the UTF-16 representation of the supplied UTF-8 string $str.
+# If $nt is true, append two 0x00 bytes as a nul terminator.
+proc utf16 {str {nt 1}} {
+  set r [encoding convertto unicode $str]
+  if {$nt} {
+    append r "\x00\x00"
+  }
+  return $r
+}
+
+# Return the UTF-8 representation of the supplied UTF-16 string $str. 
+proc utf8 {str} {
+  # If $str ends in two 0x00 0x00 bytes, knock these off before
+  # converting to UTF-8 using TCL.
+  binary scan $str \c* vals
+  if {[lindex $vals end]==0 && [lindex $vals end-1]==0} {
+    set str [binary format \c* [lrange $vals 0 end-2]]
+  }
+
+  set r [encoding convertfrom unicode $str]
+  return $r
+}
+
+
+# This proc is used to test the following API calls:
+#
+# sqlite3_column_origin_name
+# sqlite3_column_origin_name16
+# sqlite3_column_table_name
+# sqlite3_column_table_name16
+# sqlite3_column_database_name
+# sqlite3_column_database_name16
+# sqlite3_column_table_alias
+# sqlite3_column_table_alias16
+
+#
+# $STMT is a compiled SQL statement. $test is a prefix
+# to use for test names within this proc. $names is a list
+# of the column names that should be returned by $STMT.
+# $decltypes is a list of column declaration types for $STMT.
+#
+# Example:
+#
+# set STMT [sqlite3_prepare "SELECT 1, 2, 2;" -1 DUMMY]
+# check_header test1.1 {1 2 3} {"" "" ""}
+#
+proc check_origin_header2 {STMT test dbs tables als cols} {
+  # If sqlite3_column_origin_name() and friends are not compiled into
+  # this build, this proc is a no-op.
+  ifcapable columnmetadata {
+    # Use the return value of sqlite3_column_count() to build
+    # a list of column indexes. i.e. If sqlite3_column_count
+    # is 3, build the list {0 1 2}.
+    set ::idxlist [list]
+    set ::numcols [sqlite3_column_count $STMT]
+    for {set i 0} {$i < $::numcols} {incr i} {lappend ::idxlist $i}
+  
+    # Database names in UTF-8
+    do_test $test.1 {
+      set cnamelist [list]
+      foreach i $idxlist {
+        lappend cnamelist [sqlite3_column_database_name $STMT $i]
+      } 
+      set cnamelist
+    } $dbs
+  
+    # Database names in UTF-16
+    ifcapable {utf16} {
+      do_test $test.2 {
+        set cnamelist [list]
+        foreach i $idxlist {
+          lappend cnamelist [utf8 [sqlite3_column_database_name16 $STMT $i]]
+        }
+        set cnamelist
+      } $dbs
+    }
+  
+    # Table alias in UTF-8
+    do_test $test.3 {
+      set caliaslist [list]
+      foreach i $idxlist {
+        lappend caliaslist [sqlite3_column_table_alias $STMT $i]
+      } 
+      set caliaslist
+    } $als
+  
+    # Table alias in UTF-16
+    ifcapable {utf16} {
+      do_test $test.4 {
+        set caliaslist [list]
+        foreach i $idxlist {
+          lappend caliaslist [utf8 [sqlite3_column_table_alias16 $STMT $i]]
+        }
+        set caliaslist
+      } $als
+    }
+
+    # Table names in UTF-8
+    do_test $test.5 {
+      set cnamelist [list]
+      foreach i $idxlist {
+        lappend cnamelist [sqlite3_column_table_name $STMT $i]
+      } 
+      set cnamelist
+    } $tables
+  
+    # Table names in UTF-16
+    ifcapable {utf16} {
+      do_test $test.6 {
+        set cnamelist [list]
+        foreach i $idxlist {
+          lappend cnamelist [utf8 [sqlite3_column_table_name16 $STMT $i]]
+        }
+        set cnamelist
+      } $tables
+    }
+  
+    # Origin names in UTF-8
+    do_test $test.7 {
+      set cnamelist [list]
+      foreach i $idxlist {
+        lappend cnamelist [sqlite3_column_origin_name $STMT $i]
+      } 
+      set cnamelist
+    } $cols
+  
+    # Origin declaration types in UTF-16
+    ifcapable {utf16} {
+      do_test $test.8 {
+        set cnamelist [list]
+        foreach i $idxlist {
+          lappend cnamelist [utf8 [sqlite3_column_origin_name16 $STMT $i]]
+        }
+        set cnamelist
+      } $cols
+    }
+  }
+}
+
+do_test capi3f-1 {
+  execsql {
+    CREATE TABLE t1(a VARINT, b BLOB, c VARCHAR(16));
+    INSERT INTO t1 VALUES(1, 2, 3);
+    INSERT INTO t1 VALUES('one', 'two', NULL);
+    INSERT INTO t1 VALUES(1.2, 1.3, 1.4);
+  }
+  set sql "SELECT * FROM t1 tz"
+  set STMT [sqlite3_prepare_v2 $DB $sql -1 TAIL]
+  sqlite3_column_count $STMT
+} 3
+
+check_origin_header2 $STMT capi3f-1 {main main main} {t1 t1 t1} {tz tz tz} {a b c}
+
+do_test capi3f-2 {
+  set sql2 "SELECT * FROM t1"
+  set STMT [sqlite3_prepare_v2 $DB $sql2 -1 TAIL]
+  sqlite3_column_count $STMT
+} 3
+
+check_origin_header2 $STMT capi3f-2 {main main main} {t1 t1 t1} {t1 t1 t1} {a b c}
+
+
+finish_test

2021-07-29
09:31 Reply: Virtual RDBMS over SQLite (artifact: 5935c273d1 user: d3x0r)

You didn't say what you were developing it in/for.

I have a slim ODBC/Sqlite interface for Javascript

https://www.npmjs.com/package/sack.vfs

https://github.com/d3x0r/sack.vfs/blob/master/tutorial/tutorial-sqlite.md

Handles all the native types date, number, string, blob (typedArray), etc... has a small create table parser which can take a create table statement, create it if it doesn't exist, and update/add columns/indexes that are new...

basically 1 open command, and 1 do SQL command command.

2021-06-09
10:03 Reply: Feature request: Stored Procedures (artifact: bfcc8e3026 user: d3x0r)

Easy enough to do with a sqlite library..

import {sack} from "sack.vfs"
const db = sack.Sqlite( "database.db" );

db.function( "add_person", addPerson );
const success = db.do( "select add_person(?,?,?,?) f", 1,'first last','home','555-1212' ) ;
if( !success[0].f ) {
   // failed to insert somehow...
}


function addPerson(pid,name,address,phone)=>{
   try {
      db.do( "insert into people(pid,name,address) values(?,?,?)", pid,name,address));
      db.do( "INSERT INTO phones(pid,phone) values(?,?)", pid,phone ) ;
      return 1;
   } catch(err) {
      return 0;
   }
}

though - that just ends up as part of the library of the app...

2021-06-06
14:24 Reply: Feature request: Stored Procedures (artifact: e0ac7d2426 user: d3x0r)

Stored procedures aren't really needed for Sqlite, since the program you write around Sqlite can certainly implement any 'stored' procedures itself.

But beyond that you can certainly implement user functions which provide stored procedure results; but that's not a thing other databases can do.

Not only that, but Stored procedures would require an additional language implementation to handle variable declaration, assignment and expressions... there's no standard when it comes to writing scripts to be executed by a SQL engine; other than the SQL commends themselves.

2021-04-01
04:55 Reply: Trouble writing/retrieving a small UTF-8 GIF image. (artifact: 8b51b89861 user: d3x0r)

Converting binary to utf8 is just making sure it's legal text, which can later be reversed by looking up the appropriate code points, so it doesn't get rejected by strict transport layers. 0xFF is a byte that can never appear in UTF-8 encoding (actually something like 0xF8-FF). So if you take each byte of the image, and convert it to an equivalent codepoint representation you can later reverse it.

Sometimes, this can be more condensed then a base64 conversion; a base64 expands by 33% or 3:4; that for every 3 bytes input you get 4. UTF-8 is 1:1 or 1:2 .. and ideally it also is 200% more 50% of the time or 2:3, which is actually +50%, so encoding in utf-8 is often worse than just using a base64 conversion.

04:45 Reply: Trouble writing/retrieving a small UTF-8 GIF image. (artifact: 911a9b4c76 user: d3x0r)

This only applies for the C runtime, binding column values lets you specify the length, which makes any checks for NUL get bypassed. SQLite itself has no issues with storing '0' or retrieving (except the console will truncate the displayed value retrieved)

2021-03-14
23:58 Reply: "SQL Features That SQLite Does Not Implement" needs updating (artifact: 3d089b54c4 user: d3x0r)

Getting the alias specified for a table in a query from the result set should be added.

2021-03-12
16:40 Reply: Unable to load dll 'SQLite.interop.dll' after installing my app. The dll is present in the application folder (artifact: 0e59ec324c user: d3x0r)

I suspect the C/C++ VS runtime isn't installed on the target.

2021-02-26
00:38 Reply: Convert SQLite to JSON (artifact: 2f1dd2dacb user: d3x0r)

Depends on what you're familiar with; Node.JS with sack.vfs can do it pretty simply... just open a database const db = sack.Sqlite('db.db') and select something... JSON.stringify( db.do( 'select something from a_table'))

https://www.npmjs.com/package/sack.vfs

https://github.com/d3x0r/sack.vfs/blob/master/tutorial/tutorial-sqlite.md

2021-02-23
04:59 Reply: Feature Request : Get table name alias from prepared statement (artifact: 2b55e0c8d3 user: d3x0r)

I was working on some tutorial type information for my Sqlite(ODBC)->JS interface node.js addon... So... this is an example application that can benefit from this additional information.

https://github.com/d3x0r/sack.vfs/blob/master/tutorial/tutorial-sqlite.md#sqlite

in the case of name conflicts, the table name results in the query object, rather than just the column name (so one can distinguish one name from another name)

Section on overlapped names...

console.table( db.do( "select fruit.name,color.name from fruit as f \
       join fruit_color fc on fruit_color.fruit_id=fruit.fruit_id \
       join color c on fruit_color.color_id=color.color_id" ) );

┌─────────┬────────────────────┬────────────────────┬──────────────────────────────────────────────────┐
│ (index) │         f          │         c          │                       name                       │
├─────────┼────────────────────┼────────────────────┼──────────────────────────────────────────────────┤
│    0    │ { name: 'apple' }  │  { name: 'red' }   │     [ 'apple', 'red', f: 'apple', c: 'red' ]     │
│    1    │ { name: 'orange' } │ { name: 'orange' } │ [ 'orange', 'orange', f: 'orange', c: 'orange' ] │
│    2    │ { name: 'banana' } │ { name: 'yellow' } │ [ 'banana', 'yellow', f: 'banana', c: 'yellow' ] │
└─────────┴────────────────────┴────────────────────┴──────────────────────────────────────────────────┘

And it is handy, if such a query is composed, to be able to rename some other query which uses a different table, but provides an alias for the table name to match what other code expects.

No Parsing required... I know there's some pragma I can issue, but I'm not really in charge of what the application wants to issue for pragmas so that's not really up to me. They could indeed get back long names and work that way also... but this is just a SQLite ->JS interface, not an ORM.

and mind you this already works as expected through ODBC to MySQL/Postgres/mssql,.... probably mostly everything other than sqlite (as it is)

2021-01-23
22:19 Edit reply: Feature Request : Get table name alias from prepared statement (artifact: 85a6e002ba user: d3x0r)

Fixed test for additional sqlite3_column_table_alias and sqlite3_column_table_alias16

Single change diff

Where the information comes from

which after fetched from the query is set in the meta data...

sqlite3VdbeSetColName( COLNAME_TABLE_ALIAS,... )

COLNAME_TABLE_ALIAS is an additional symbol here.

22:15 Edit reply: Feature Request : Get table name alias from prepared statement (artifact: fb69cc40cd user: d3x0r)

Fixed test for additional sqlite3_column_table_alias and sqlite3_column_table_alias16

Single change diff

Where the information comes from

which after fetched from the query is set in the meta data...

sqlite3VdbeSetColName( COLNAME_TABLE_ALIAS,... )

COLNAME_TABLE_ALIAS is an additional symbol here.

Ahh darn This is a badly formatted merge and bad version number, the next change is right... but that changes the version ID for all the above links I'd bet.

09:28 Reply: Feature Request : Get table name alias from prepared statement (artifact: ba16af8b81 user: d3x0r)

Table name/alias, not column name/alias... or the alias/name of the table the column is from...

08:39 Reply: Feature Request : Get table name alias from prepared statement (artifact: e7a8bf5fe8 user: d3x0r)

Was trying to add a test for the patch too... but the check header code is using the wrong statement... it's using the original statement only...

Capi3f.test

I know the code works, and that the data is duplicated internally as required... I added logging to sqlite3.c where it was getting the table alias (and setting) and what statement it's using. The aliases are right for the correct statement handle.... but I don't get why the check isn't just using the STMT passed to it.

Also found that I had dropped the patch to test1.c to register the new functions.

2021-01-12
17:39 Reply: Feature Request : Get table name alias from prepared statement (artifact: 0b465a4bc2 user: d3x0r)

updated github sqlite3_column_table_alias branch patch against official sqlite/sqlite github mirror.

noticed it is currently lacking additional test scripts.

This request goes back much further, which even included patch code... Nov 01, 2014; sqlite-column-table-name-and-table-alias-name (nabble)


So 3 yeas ago(nearly) there was this first message I saw...

http://sqlite.1065341.n5.nabble.com/Re-sqlite-column-table-name-and-table-alias-name-td100016.html (Jan 14, 2018)

Original message with patched code

http://sqlite.1065341.n5.nabble.com/Feat-Req-Get-Aliased-Table-Name-td101023.html#a105525

Mar 18, 2018 suggested a patch Jan 24, 2019 a year later... suggested a patch... 2020-03-12 another year I started this thread when the forum started

2021-01-12 maybe this is the year this feature can get implemented?

It's not like this would make sqlite be the only one in the world doing a thing, in fact it makes it so sqlite ISN'T the only one NOT able to return specified table alias.

			SQLColAttribute( pCollect->hstmt, col+1, SQL_DESC_BASE_TABLE_NAME, colname, 256, &len, NULL );

vs

			SQLColAttribute( pCollect->hstmt, col+1, SQL_DESC_TABLE_NAME, colname, 256, &len, NULL );

The first code is the only mode Sqlite is able to return... the second mode is the more basic table name, which would be the alias, if specified in the query.

This functionality has nothing to do with automatic names in queries from calculations or other nested select information...but returning the information as specified from the query. (as http://sqlite.1065341.n5.nabble.com/Re-sqlite-column-table-name-and-table-alias-name-tp100016p100033.html this part of the thread got off into)

2020-06-10
15:11 Reply: Request: stored functions/procedures (artifact: eb9cdce49d user: d3x0r)

Besides all of the other things already said, what flavor of script language would you propose; every RDBMS out there has it own dialect, and it's not like you can just take your procedures and put them anywhere else. This would become yet another cog that we can't get updates and support for.

2020-05-26
01:50 Reply: DESPERATE: Need help getting SQLite working with Electron on Windows 10 (artifact: d9a6513e1b user: d3x0r)

npmjs.org/ https://www.npmjs.com/package/sack.vfs#sqlite-interface I have this sqlite wrapper for node as an alternative.

2020-03-29
07:41 Edit reply: Colons in column names? (artifact: 62525ab9d6 user: d3x0r)

Hmm that's an interesting note I thought it might be a property of the shell...

but this is what I got... (shared keys are split by table alias, if different, so [0].t1.id or [0].id.t1 or [0].id[0] are really all the same value.

I tried to test with MySQL(mariadb) and the second query is an error, without an alias...

select * from (select t1.id, t2.id from t1 join t2 on t1.id = t2.t1Id) t;
-- not final 't' added... and then it complains duplicate column id.

mind you these are deprecated https://www.sqlite.org/pragma.html#pragma_full_column_names

(no pragma )
[ { t1: { id: 0 }, t2: { id: 1 }, id: [ 0, 1, t1: 0, t2: 1 ] } ]
[ { id: 0, 'id:1': 1 } ]

#pragma short_column_names=on
[ { t1: { id: 0 }, t2: { id: 1 }, id: [ 0, 1, t1: 0, t2: 1 ] } ]
[ { id: 0, 'id:1': 1 } ]

--pragma short_column_names=off
#pragma long_column_names=on
[ { 't1.id': 0, 't2.id': 1 } ]
[ { 'subquery_1.id': 0, 'subquery_1.id:1': 1 } ]

#pragma short_column_names=on
#pragma long_column_names=on
[ { 't1.id': 0, 't2.id': 1 } ]
[ { id: 0, 'id:1': 1 } ]
07:39 Reply: Colons in column names? (artifact: 4303456ce9 user: d3x0r)

Hmm that's an interesting note I thought it might be a property of the shell...

but this is what I got... (shared keys are split by table alias, if different, so [0].t1.id or [0].id.t1 or [0].id[0] are really all the same value.

mind you these are deprecated https://www.sqlite.org/pragma.html#pragma_full_column_names

(no pragma )
[ { t1: { id: 0 }, t2: { id: 1 }, id: [ 0, 1, t1: 0, t2: 1 ] } ]
[ { id: 0, 'id:1': 1 } ]

#pragma short_column_names=on
[ { t1: { id: 0 }, t2: { id: 1 }, id: [ 0, 1, t1: 0, t2: 1 ] } ]
[ { id: 0, 'id:1': 1 } ]

--pragma short_column_names=off
#pragma long_column_names=on
[ { 't1.id': 0, 't2.id': 1 } ]
[ { 'subquery_1.id': 0, 'subquery_1.id:1': 1 } ]

#pragma short_column_names=on
#pragma long_column_names=on
[ { 't1.id': 0, 't2.id': 1 } ]
[ { id: 0, 'id:1': 1 } ]
2020-03-12
21:46 Post: Feature Request : Get table name alias from prepared statement (artifact: fdb0bb7ad0 user: d3x0r)

I was going to track down sqlite mailing list messages that relate; so I wouldn't have to repeat myself, but found that the mailing list archives aren't public, so, that's not really viable.

Please do consider extending the family of functions to get statement names to include a method to get the alias of a table name from a query. The only thing one can get now is the original table name.

const char *sqlite3_column_database_name(sqlite3_stmt*,int);
const void *sqlite3_column_database_name16(sqlite3_stmt*,int);
const char *sqlite3_column_table_name(sqlite3_stmt*,int);
const void *sqlite3_column_table_name16(sqlite3_stmt*,int);
const char *sqlite3_column_origin_name(sqlite3_stmt*,int);
const void *sqlite3_column_origin_name16(sqlite3_stmt*,int);

really needs to include

const char *sqlite3_column_table_alias(sqlite3_stmt*,int);
const void *sqlite3_column_table_alias16(sqlite3_stmt*,int);