I mentioned this a week or two ago, but I implemented the code changes
(shown below) and wanted to make the request again since they were extremely
trivial.
PRAGMA real_column_names=0|1
Usage:
For a given SELECT clause, enabling this pragma causes the column names
generated in the resultset (provided they are of TK_COLUMN type) to be
returned in dotted form as either TableName.ColumnName or
Database.TableName.ColumnName in the case of an attached database. Any
alias names are ignored unless they are for an unbound or calculated column
in the resultset.
Reason:
Enabling this pragma will give database driver implementers (like me) the
ability to extract better and more robust schema information gleaned from an
arbitrary SELECT clause.
It differs in important ways from PRAGMA full_column_names:
1. Aliases are ignored for the sake of naming the columns in the resultset.
2. When referencing an attached database, the attached database name is
included in the column's name.
3. Setting real_column_names=1 automatically implies full_column_names=1,
though it doesn't specifically set that flag.
The behavior of the existing full_column_names pragma is unchanged and
unaffected by this additional pragma.
For example:
CREATE TABLE Foo (ID Integer PRIMARY KEY AUTOINCREMENT, Bar VarChar(50));
SELECT 'Literal' AS A, [ID] As B, [Bar] as C FROM [Foo] AS D;
The returned resultset under normal circumstances contains the columns:
A, B, C
Using PRAGMA full_column_names=1, the returned resultset is still:
A, B, C
Using real_column_names=1, the returned columns are named:
A, Foo.ID, Foo.Bar
If the database were attached and called "Test" the results would be named:
A, Test.Foo.ID, Test.Foo.Bar
By parsing the returned full dotted names it is now possible to query for
detailed schema, indexes, keys, base table and base column information about
each returned column of the resultset. Yes, certain scenarios that
reference views or views of other views do come into play when parsing
schema, but that's not important for the sake of the pragma request.
Code Changes Required (as of version 3.12):
sqliteint.h, beginning at line 478:
#define SQLITE_WriteSchema 0x00000800 /* OK to update SQLITE_MASTER
*/
+ #define SQLITE_RealColNames 0x00002000 /* Show real column names on
SELECT */
pragma.c, beginning at line 150
{ "writable_schema", SQLITE_WriteSchema },
{ "omit_readlock", SQLITE_NoReadlock },
+ { "real_column_names", SQLITE_RealColNames },
};
select.c, beginning at line 743
/*
** Generate code that will tell the VDBE the names of columns
** in the result set. This information is used to provide the
** azCol[] values in the callback.
*/
static void generateColumnNames(
Parse *pParse, /* Parser context */
SrcList *pTabList, /* List of tables */
ExprList *pEList /* Expressions defining the result set */
){
Vdbe *v = pParse->pVdbe;
int i, j;
sqlite3 *db = pParse->db;
- int fullNames, shortNames;
+ int fullNames, shortNames, realNames;
#ifndef SQLITE_OMIT_EXPLAIN
/* If this is an EXPLAIN, skip this step */
if( pParse->explain ){
return;
}
#endif
assert( v!=0 );
if( pParse->colNamesSet || v==0 || sqlite3_malloc_failed ) return;
pParse->colNamesSet = 1;
- fullNames = (db->flags & SQLITE_FullColNames)!=0;
+ realNames = (db->flags & SQLITE_RealColNames)!=0;
+ fullNames = (db->flags & (SQLITE_FullColNames|SQLITE_RealColNames))!=0;
shortNames = (db->flags & SQLITE_ShortColNames)!=0;
sqlite3VdbeSetNumCols(v, pEList->nExpr);
for(i=0; i<pEList->nExpr; i++){
Expr *p;
p = pEList->a[i].pExpr;
if( p==0 ) continue;
- if( pEList->a[i].zName ){
+ if( pEList->a[i].zName && (realNames == 0 || p->op != TK_COLUMN)){
char *zName = pEList->a[i].zName;
sqlite3VdbeSetColName(v, i, zName, strlen(zName));
continue;
}
if( p->op==TK_COLUMN && pTabList ){
Table *pTab;
char *zCol;
int iCol = p->iColumn;
for(j=0; j<pTabList->nSrc && pTabList->a[j].iCursor!=p->iTable;
j++){}
assert( j<pTabList->nSrc );
pTab = pTabList->a[j].pTab;
if( iCol<0 ) iCol = pTab->iPKey;
assert( iCol==-1 || (iCol>=0 && iCol<pTab->nCol) );
if( iCol<0 ){
zCol = "rowid";
}else{
zCol = pTab->aCol[iCol].zName;
}
if( !shortNames && !fullNames && p->span.z && p->span.z[0] ){
sqlite3VdbeSetColName(v, i, p->span.z, p->span.n);
}else if( fullNames || (!shortNames && pTabList->nSrc>1) ){
char *zName = 0;
char *zTab;
+ char *zDb = 0;
zTab = pTabList->a[j].zAlias;
- if( fullNames || zTab==0 ) zTab = pTab->zName;
+ if( fullNames || zTab==0 ){
- sqlite3SetString(&zName, zTab, ".", zCol, 0);
+ if (pTab->iDb > 1) zDb = db->aDb[pTab->iDb].zName;
+ zTab = pTab->zName;
+ }
+ if (!zDb || !realNames) sqlite3SetString(&zName, zTab, ".", zCol,
0);
+ else sqlite3SetString(&zName, zDb, ".", zTab, ".", zCol, 0);
Robert Simpson
Programmer at Large
