Simon,
This is from the documentation of SQLite:
<https://www.sqlite.org/c3ref/column_name.html>
"The name of a result column is the value of the "AS" clause for that column, if
there is an AS clause. If there is no AS clause then the name of the column is unspecified and may
change from one release of SQLite to the next."
If you do not use AS, then you have no idea what the names of your columns are.
You cannot even rely on each column having a different name. You might
upgrade to a slightly higher version of SQLite because of a bug, and get
completely different names for the columns in your result.
If you want to have any idea about your column names at all, assign each one using an
"AS" clause.
If you want to pass the results of "SELECT * FROM ..." to something else and don't know
the names of your columns, don't do a "SELECT * FROM ..." in the first place. Use
<http://www.sqlite.org/pragma.html#pragma_table_info>
to find the names of the columns which exist, and create your SELECT from that.
If you develop bussiness software, where you know exactly the querys
because you are writing them, then there is no problem at all. But other
kind of software, like a database manager, need all available metadata.
And more metadata info is better, for sure. And this is my case now.
If metadata were not useful, why exist functions as
sqlite3_column_name(), sqlite3_column_origin_name() or
sqlite3_column_table_name(), among many others? I know there is a
compilation switch to enable metadata (SQLITE_ENABLE_COLUMN_METADATA),
so, what's the problem to add such useful information that I'm asking for?
Well, at last I've tried to do myself, and I belive I've got it ;-)
I attach a patch (aplied over sqlite-amalgamation-3080701.zip), which
adds the functions sqlite3_column_table_alias_name() and
sqlite3_column_table_alias_name16(). Those functions return the table
alias for the related column, or the original table name if there is no
alias for it. It's rather small patch, althougt I've not added those
functions to the load_extension system, in order to not grow it and make
it more readable. But if needed, I can make a full patch. The key change
is this:
@@ -105364,6 +105386,7 @@
if( j<pTabList->nSrc ){
pTab = pTabList->a[j].pTab;
pS = pTabList->a[j].pSelect;
+ zTableAlias =
pTabList->a[j].zAlias?pTabList->a[j].zAlias:pTabList->a[j].zName;
}else{
pNC = pNC->pNext;
}
The rest of changes are to accomodate that info into the actual code.
Please, I'll be very happy if it's evaluated and incorporated to sqlite
code (if approved, of course).
Best regards,
Jose F. Gimenez
<https://www.sqlite.org/compile.html#enable_column_metadata>
Index: sqlite3.c
===================================================================
--- sqlite3.c (revisión: 28)
+++ sqlite3.c (copia de trabajo)
@@ -3747,6 +3747,8 @@
SQLITE_API const void *sqlite3_column_table_name16(sqlite3_stmt*,int);
SQLITE_API const char *sqlite3_column_origin_name(sqlite3_stmt*,int);
SQLITE_API const void *sqlite3_column_origin_name16(sqlite3_stmt*,int);
+SQLITE_API const char *sqlite3_column_table_alias_name(sqlite3_stmt*,int);
+SQLITE_API const void *sqlite3_column_table_alias_name16(sqlite3_stmt*,int);
/*
** CAPI3REF: Declared Datatype Of A Query Result
@@ -9326,8 +9328,9 @@
#define COLNAME_DATABASE 2
#define COLNAME_TABLE 3
#define COLNAME_COLUMN 4
+#define COLNAME_TABALIAS 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 */
@@ -68230,9 +68233,10 @@
** 2 The name of the database that the column derives from
** 3 The name of the table that the column derives from
** 4 The name of the table column that the result column derives from
+** 5 The name of the table for the column as its refered in the
sentence (maybe an alias)
**
** If the result is not a simple column reference (if it is an expression
-** or a constant) then useTypes 2, 3, and 4 return NULL.
+** or a constant) then useTypes 2, 3, 4 and 5 return NULL.
*/
static const void *columnName(
sqlite3_stmt *pStmt,
@@ -68355,6 +68359,22 @@
#endif /* SQLITE_OMIT_UTF16 */
#endif /* SQLITE_ENABLE_COLUMN_METADATA */
+/*
+** Return the name of the table for the column (maybe an alias).
+** NULL is returned if the result column is an expression or constant or
+** anything else which is not an unambiguous reference to a database column.
+*/
+SQLITE_API const char *sqlite3_column_table_alias_name(sqlite3_stmt *pStmt,
int N){
+ return columnName(
+ pStmt, N, (const void*(*)(Mem*))sqlite3_value_text, COLNAME_TABALIAS);
+}
+#ifndef SQLITE_OMIT_UTF16
+SQLITE_API const void *sqlite3_column_table_alias_name16(sqlite3_stmt *pStmt,
int N){
+ return columnName(
+ pStmt, N, (const void*(*)(Mem*))sqlite3_value_text16, COLNAME_TABALIAS);
+}
+#endif /* SQLITE_OMIT_UTF16 */
+
/******************************* sqlite3_bind_ ***************************
**
@@ -105317,24 +105337,26 @@
**
** The declaration type for any expression other than a column is NULL.
**
-** This routine has either 3 or 6 parameters depending on whether or not
+** This routine has either 3 or 7 parameters depending on whether or not
** the SQLITE_ENABLE_COLUMN_METADATA compile-time option is used.
*/
#ifdef SQLITE_ENABLE_COLUMN_METADATA
-# define columnType(A,B,C,D,E,F) columnTypeImpl(A,B,C,D,E,F)
+# define columnType(A,B,C,D,E,F,G) columnTypeImpl(A,B,C,D,E,F,G)
static const char *columnTypeImpl(
NameContext *pNC,
Expr *pExpr,
const char **pzOrigDb,
const char **pzOrigTab,
const char **pzOrigCol,
+ const char **pzTableAlias,
u8 *pEstWidth
){
char const *zOrigDb = 0;
char const *zOrigTab = 0;
char const *zOrigCol = 0;
+ char const *zTableAlias = 0;
#else /* if !defined(SQLITE_ENABLE_COLUMN_METADATA) */
-# define columnType(A,B,C,D,E,F) columnTypeImpl(A,B,F)
+# define columnType(A,B,C,D,E,F,G) columnTypeImpl(A,B,G)
static const char *columnTypeImpl(
NameContext *pNC,
Expr *pExpr,
@@ -105364,6 +105386,7 @@
if( j<pTabList->nSrc ){
pTab = pTabList->a[j].pTab;
pS = pTabList->a[j].pSelect;
+ zTableAlias =
pTabList->a[j].zAlias?pTabList->a[j].zAlias:pTabList->a[j].zName;
}else{
pNC = pNC->pNext;
}
@@ -105406,7 +105429,7 @@
sNC.pSrcList = pS->pSrc;
sNC.pNext = pNC;
sNC.pParse = pNC->pParse;
- zType = columnType(&sNC, p,&zOrigDb,&zOrigTab,&zOrigCol, &estWidth);
+ zType = columnType(&sNC,
p,&zOrigDb,&zOrigTab,&zOrigCol,&zTableAlias, &estWidth);
}
}else if( pTab->pSchema ){
/* A real table */
@@ -105451,7 +105474,7 @@
sNC.pSrcList = pS->pSrc;
sNC.pNext = pNC;
sNC.pParse = pNC->pParse;
- zType = columnType(&sNC, p, &zOrigDb, &zOrigTab, &zOrigCol, &estWidth);
+ zType = columnType(&sNC, p, &zOrigDb, &zOrigTab, &zOrigCol,
&zTableAlias, &estWidth);
break;
}
#endif
@@ -105463,6 +105486,12 @@
*pzOrigDb = zOrigDb;
*pzOrigTab = zOrigTab;
*pzOrigCol = zOrigCol;
+ /* If pzTableAlias is already set from a higher level, do not set again,
+ ** because it could be the same original table with another alias, and
+ ** only the higher level is the correct result.
+ */
+ if(*pzTableAlias==0)
+ *pzTableAlias = zTableAlias;
}
#endif
if( pEstWidth ) *pEstWidth = estWidth;
@@ -105491,7 +105520,8 @@
const char *zOrigDb = 0;
const char *zOrigTab = 0;
const char *zOrigCol = 0;
- zType = columnType(&sNC, p, &zOrigDb, &zOrigTab, &zOrigCol, 0);
+ const char *zTableAlias = 0;
+ zType = columnType(&sNC, p, &zOrigDb, &zOrigTab, &zOrigCol, &zTableAlias,
0);
/* The vdbe must make its own copy of the column-type and other
** column specific strings, in case the schema is reset before this
@@ -105500,8 +105530,9 @@
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_TABALIAS, zTableAlias,
SQLITE_TRANSIENT);
#else
- zType = columnType(&sNC, p, 0, 0, 0, 0);
+ zType = columnType(&sNC, p, 0, 0, 0, 0, 0);
#endif
sqlite3VdbeSetColName(v, i, COLNAME_DECLTYPE, zType, SQLITE_TRANSIENT);
}
@@ -105716,7 +105747,7 @@
a = pSelect->pEList->a;
for(i=0, pCol=pTab->aCol; i<pTab->nCol; i++, pCol++){
p = a[i].pExpr;
- pCol->zType = sqlite3DbStrDup(db, columnType(&sNC, p,0,0,0, &pCol->szEst));
+ pCol->zType = sqlite3DbStrDup(db, columnType(&sNC, p,0,0,0,0,
&pCol->szEst));
szAll += pCol->szEst;
pCol->affinity = sqlite3ExprAffinity(p);
if( pCol->affinity==0 ) pCol->affinity = SQLITE_AFF_NONE;
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
