Re: [sqlite] SQLite 3.22.0 coming soon

[J Decker]

On Tue, Jan 9, 2018 at 12:30 PM, J Decker  wrote:

> swarmvtab3 test is still failing... (windows 10, msvc 2017)  10.0.16299.0
>
> swarmvtab3-1.2.3...
> Error: inconsistent ::dbcache and disk
> swarmvtab3-1.3.3...
> Error: inconsistent ::dbcache and disk
>
>
This works

>
> On Tue, Jan 9, 2018 at 11:46 AM, Simon Slavin 
> wrote:
>
>> On 9 Jan 2018, at 6:46pm, Richard Hipp  wrote:
>>
>> > The latest change summary can be seen at
>> > https://www.sqlite.org/draft/releaselog/3_22_0.html and the draft
>>
>> In  …
>>
>> For "clobbering" use "overwriting", if only for the sake of international
>> readers.
>>
>> "If this option is pass a non-zero argument" should probably be "passed".
>>
>> I think you can come up with a more appropriate name than "expert".  How
>> about "cleverindex" or "indexwizard" or something.
>>
>> If this is easy to do, then for the .expert command and, by extension,
>> the external function …
>>
>> Please allow an option "—ignoreexisting" for the .expert command.  This
>> option makes SQLite ignore any existing indexes except for the ones which
>> implement primary keys.
>>
>> For extra points, with this option selected, in the output section, it
>> includes DROP INDEX commands for the indexes it ignored before the CREATE
>> INDEX command it prefers.
>>
>> For an additional bonus point, with this option selected, the command
>> should recognise when the new index it recommended is the same as one of
>> the existing indexes it ignored, and do the "no new indexes" thing.
>>
>> Simon.
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Change the update hook from the update hook?

[Gwendal Roué]

Hello,

Is it valid to change the update hook from the update hook itself?

The reason for this question is the following: when a notified database change 
makes it useless to perform further observation, one may want to remove the 
update hook alltogether. Can it be done from the update hook itself?

Here is a quick and dirty sample code, for the context: it installs an update 
hook that tracks any modification to the "players" table, and attempts to 
remove the update hook as soon as the "players" table is modified:

typedef struct {
sqlite3 *conn;
int players_table_was_modified;
} info;

void update_hook(info *info, int change, char const *db, char const *table, 
sqlite3_int64 rowed) {
if (strcmp(table, "players") == 0) {
info->players_table_was_modified = 1;

// Is it valid?
sqlite3_update_hook(info->conn, NULL, NULL);
}
}

sqlite3 *conn = ...;
info info = { conn, 0 };
sqlite3_update_hook(conn, update_hook, &info);

Quoting https://sqlite.org/c3ref/update_hook.html:

> The update hook implementation must not do anything that will modify the 
> database connection that invoked the update hook. Any actions to modify the 
> database connection must be deferred until after the completion of the 
> sqlite3_step() call that triggered the update hook. Note that 
> sqlite3_prepare_v2() and sqlite3_step() both modify their database 
> connections for the meaning of "modify" in this paragraph.

According to this documentation, I'm note sure if sqlite3_update_hook itself 
modifies the database connection for the meaning of "modify" in the quoted 
documentation paragraph, and is thus forbidden, or not.

Can anyone lift this doubt?

Thanks in advance,
Gwendal Roué

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database type graph

[Clemens Ladisch]

merlinverde...@infomed.sld.cu wrote:
> How can I design in a database a database type graph?

As the first step, by telling us what a "database type graph" is.
The meaning was probably lost in translation.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Change the update hook from the update hook?

[Gwendal Roué]

For the record, removing the update hook from the update hook works as expected 
in both SQLite 3.19.3 and 3.21, with a connection which is opened with the 
SQLITE_OPEN_NOMUTEX flag, and the guarantee that no two threads use the same 
connection at the same time.

But I'm just not sure if this is a guaranteed behavior?

Gwendal

> Le 14 janv. 2018 à 18:05, Gwendal Roué  a écrit :
> 
> Hello,
> 
> Is it valid to change the update hook from the update hook itself?
> 
> The reason for this question is the following: when a notified database 
> change makes it useless to perform further observation, one may want to 
> remove the update hook alltogether. Can it be done from the update hook 
> itself?
> 
> Here is a quick and dirty sample code, for the context: it installs an update 
> hook that tracks any modification to the "players" table, and attempts to 
> remove the update hook as soon as the "players" table is modified:
> 
> typedef struct {
> sqlite3 *conn;
> int players_table_was_modified;
> } info;
> 
> void update_hook(info *info, int change, char const *db, char const 
> *table, sqlite3_int64 rowed) {
> if (strcmp(table, "players") == 0) {
> info->players_table_was_modified = 1;
> 
> // Is it valid?
> sqlite3_update_hook(info->conn, NULL, NULL);
> }
> }
> 
> sqlite3 *conn = ...;
> info info = { conn, 0 };
> sqlite3_update_hook(conn, update_hook, &info);
> 
> Quoting https://sqlite.org/c3ref/update_hook.html: 
> 
> 
>> The update hook implementation must not do anything that will modify the 
>> database connection that invoked the update hook. Any actions to modify the 
>> database connection must be deferred until after the completion of the 
>> sqlite3_step() call that triggered the update hook. Note that 
>> sqlite3_prepare_v2() and sqlite3_step() both modify their database 
>> connections for the meaning of "modify" in this paragraph.
> 
> According to this documentation, I'm note sure if sqlite3_update_hook itself 
> modifies the database connection for the meaning of "modify" in the quoted 
> documentation paragraph, and is thus forbidden, or not.
> 
> Can anyone lift this doubt?
> 
> Thanks in advance,
> Gwendal Roué
> 

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Defect: single row table cross join causes infinite loop

[petern]

Consider the hypothetical CTE concerned with displaying the n'th coin flip
of a random series as follows.  [This is a simplified proxy for any
computation where an earlier computed table supplies the parameters for a
later computed table.]

sqlite> .v
SQLite 3.22.0 2018-01-12 23:38:10
dec3ea4e4e6c4b1761ddc883a29eaa50dcd663ce6199667cc0ff82f7849d4f2a


--This way works as expected: note how the final select is not joining the
params table.
sqlite>  WITH params(n) AS (VALUES (5))
 ,coinflip(flip,side) AS (SELECT 1,random()>0 UNION ALL SELECT
flip+1,random()>0 FROM coinflip)
SELECT flip,side FROM coinflip WHERE flip%5=0 LIMIT 3;
flip,side
5,0
10,1
15,0

--However, there is an infinite loop when params table column "n" is used.
 WITH params(n) AS (VALUES (5))
 ,coinflip(flip,side) AS (SELECT 1,random()>0 UNION ALL SELECT
flip+1,random()>0 FROM coinflip)
 SELECT flip,side FROM coinflip,params WHERE flip%n=0 LIMIT 3;

--Also infinite loop.
--Simply cross join params table without using any columns from it.

 WITH params(n) AS (VALUES (5))
 ,coinflip(flip,side) AS (SELECT 1,random()>0 UNION ALL SELECT
flip+1,random()>0 FROM coinflip)
 SELECT flip,side FROM coinflip,params WHERE flip%5=0 LIMIT 3;

--So, what's going on here?  [params] is a constant one row table!  How can
it be adding any rows to the output set let alone an infinite number?


Peter
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can an SQL script be built from within sqlite?

[Shane Dev]

Hi Simon,

I have found a way achieve this purely in the SQLite shell. The trick is to
make all rows in tcout1 SQL statements and then execute them.

sqlite> CREATE TABLE tcout1(sql text);
sqlite> CREATE TABLE tcout2(sql text);
sqlite> insert into tcout1(sql) select "insert into tcout2(sql) select
'.headers off';";
sqlite> insert into tcout1(sql) select "insert into tcout2(sql) select
'.once tc'||strftime('%s','now');";
sqlite> insert into tcout1(sql) select "insert into tcout2(sql) select
'select * from tc;';";
sqlite> .once tcout1.sql
sqlite> select * from tcout1;
sqlite> .read tcout1.sql
sqlite> select * from tcout2;
.headers off
.once tc1515968593
select * from tc;


On 13 January 2018 at 19:57, Simon Slavin  wrote:

> On 13 Jan 2018, at 6:48pm, Shane Dev  wrote:
>
> > Is there a way to execute the contents of certain rows (the second row in
> > this example) and replace it with its own result to create second table /
> > view which could interpreted by the sqlite shell?
>
> Not inside the SQLite shell.
>
> Looks like you need to learn programming.  Or at least how to script your
> OS shell.  Which OS are you using ?  And if it’s Linux/Unix, which shell
> are you using ?
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can an SQL script be built from within sqlite?

[Tony Papadimitriou]

You can use .output file and .print for most cases (except those that need a 
calculated result) to simplify your script a bit.


-Original Message- 
From: Shane Dev


I have found a way achieve this purely in the SQLite shell. The trick is to
make all rows in tcout1 SQL statements and then execute them.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can an SQL script be built from within sqlite?

[Simon Slavin]

On 14 Jan 2018, at 10:33pm, Shane Dev  wrote:

> I have found a way achieve this purely in the SQLite shell. The trick is to
> make all rows in tcout1 SQL statements and then execute them.

That’s clever.  Nice one.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can an SQL script be built from within sqlite?

[petern]

Shane.  That's very interesting considering the effort to make the one
thing happen exactly once without external software dependency.
Does the capability to write specially named local files but not have a
periodic loop nor network capability somehow get your application off the
ground?
Based on your problem statement, the user would have to initiate your
script and know when/if it is required to be run...

I had in mind adding periodic and conditional dot commands to SQLite shell
- to simulate continuous operation of application code.  If you've figured
out a way around needing such things to make a useful standalone SQLite
application, I would be very interested to understand how that works.

Peter





On Sun, Jan 14, 2018 at 2:33 PM, Shane Dev  wrote:

> Hi Simon,
>
> I have found a way achieve this purely in the SQLite shell. The trick is to
> make all rows in tcout1 SQL statements and then execute them.
>
> sqlite> CREATE TABLE tcout1(sql text);
> sqlite> CREATE TABLE tcout2(sql text);
> sqlite> insert into tcout1(sql) select "insert into tcout2(sql) select
> '.headers off';";
> sqlite> insert into tcout1(sql) select "insert into tcout2(sql) select
> '.once tc'||strftime('%s','now');";
> sqlite> insert into tcout1(sql) select "insert into tcout2(sql) select
> 'select * from tc;';";
> sqlite> .once tcout1.sql
> sqlite> select * from tcout1;
> sqlite> .read tcout1.sql
> sqlite> select * from tcout2;
> .headers off
> .once tc1515968593
> select * from tc;
>
>
> On 13 January 2018 at 19:57, Simon Slavin  wrote:
>
> > On 13 Jan 2018, at 6:48pm, Shane Dev  wrote:
> >
> > > Is there a way to execute the contents of certain rows (the second row
> in
> > > this example) and replace it with its own result to create second
> table /
> > > view which could interpreted by the sqlite shell?
> >
> > Not inside the SQLite shell.
> >
> > Looks like you need to learn programming.  Or at least how to script your
> > OS shell.  Which OS are you using ?  And if it’s Linux/Unix, which shell
> > are you using ?
> >
> > Simon.
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can an SQL script be built from within sqlite?

[Simon Slavin]

On 15 Jan 2018, at 12:30am, petern  wrote:

> Shane.  That's very interesting considering the effort to make the one
> thing happen exactly once without external software dependency.

On 13 Jan 2018, at 7:33pm, Shane Dev  wrote:

> I use mainly Linux (bash) and Windows (powershell) but my target platforms
> also include Android, iOS, IoT (anything that can link against the sqlite
> library).

I’ve never seen anyone compile sqlite3 (the shell tool) for iOS.  Android I 
have even less idea about.  Can you run command-line tools on Android ?  Has 
anyone compiled sqlite3 for it ?

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can an SQL script be built from within sqlite?

[Brian Curley]

I use Termux on Android. Very useful build of sqlite available there.

Regards.

Brian P Curley


On Jan 14, 2018 7:42 PM, "Simon Slavin"  wrote:

>
>
> On 15 Jan 2018, at 12:30am, petern  wrote:
>
> > Shane.  That's very interesting considering the effort to make the one
> > thing happen exactly once without external software dependency.
>
> On 13 Jan 2018, at 7:33pm, Shane Dev  wrote:
>
> > I use mainly Linux (bash) and Windows (powershell) but my target
> platforms
> > also include Android, iOS, IoT (anything that can link against the sqlite
> > library).
>
> I’ve never seen anyone compile sqlite3 (the shell tool) for iOS.  Android
> I have even less idea about.  Can you run command-line tools on Android ?
> Has anyone compiled sqlite3 for it ?
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can an SQL script be built from within sqlite?

[Richard Hipp]

On 1/14/18, Simon Slavin  wrote:
>
> I’ve never seen anyone compile sqlite3 (the shell tool) for iOS.  Android I
> have even less idea about.  Can you run command-line tools on Android ?  Has
> anyone compiled sqlite3 for it ?
>

You can easily compile and run all of the SQLite command-line tools
for Android.  There are simple instructions at the bottom of the
https://www.sqlite.org/fasterthanfs.html document.

The engineers at Apple tell me this is not possible on iOS.
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] os_unix.c:36136: (2)

[Rowan Worth]

On 12 January 2018 at 07:21, wei1.z  wrote:

> What is the meaning of this line?
>
> 01-11 14:40:59.733 10011  2864  2877 E SQLiteLog: (14) os_unix.c:36136: (2)
> open() -
>
> db file cannot be found, or permission issue ?
>

To decipher this in future, the first number in parens is the sqlite error
code. (14) is SQLITE_CANTOPEN. The second number in parens is the unix
errno; (2) is ENOENT. If you check the man page for the open syscall,
you'll see that ENOENT is returned if the requested file doesn't exist.

For a permission issue you would see (14) os_unix.c:36136: (13) open() -  errno 13 is EACCES.

-Rowan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] possible bug: separator string and quote mode

[p dev]

I have been using SQLite for a small data-processing application.
I am hoping to get quote mode output with a field separator that is not a 
comma.Many thanks,
Pamela--
Problem

Quote mode output ignores separator string
Background
https://www.sqlite.org/cli.html5. Changing Output Formats
...
In "quote" mode, the output is formatted as SQL literals. Strings are enclosed 
in single-quotes and internal single-quotes are escaped by doubling. Blobs are 
displayed in hexadecimal blob literal notation (Ex: x'abcd'). Numbers are 
displayed as ASCII text and NULL values are shown as "NULL". All columns are 
separated from each other by a comma (or whatever alternative character is 
selected using ".separator").
...

Demonstration

SQLite version 3.21.0 2017-10-24 18:55:49
Enter ".help" for usage hints.
sqlite> create table tbl1(one varchar(10), two smallint);
sqlite> insert into tbl1 values('hello!',10);
sqlite> insert into tbl1 values('goodbye', 20);
sqlite> insert into tbl1 values(null, 30);
sqlite> select * from tbl1;
hello!|10
goodbye|20
|30

sqlite> .separator #
sqlite> select * from tbl1;
hello!#10
goodbye#20
#30

sqlite> .mode quote
sqlite> select * from tbl1;
'hello!',10
'goodbye',20
NULL,30


Expected Output

sqlite> select * from tbl1;'hello!'#10
'goodbye'#20
NULL#30

sqlite> .show
    echo: off
 eqp: off
 explain: auto
 headers: on
    mode: quote
   nullvalue: ""
  output: stdout
colseparator: "#"
rowseparator: "\n"
   stats: off
   width:
    filename: data.db3

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_column_decltype and max and min

[Craig H Maynard]

Igor,

Two comments:

- Why Homebrew? Because it's an invaluable tool for keeping up with the latest 
release of not just SQLite but every installed package of interest.

- I think you missed the point. Niall is referring to the SQLite3 executable, 
not just the C source file. The excutable is needed to use SQLite from the 
command line.

Craig

--
Craig H Maynard
Rhode Island, USA


> On Sat, 13 Jan 2018, Igor Korot  wrote:
> 
> Why? Just include sqlite3.c in your project and recompile.
> 
> 
> On Sat, Jan 13, 2018 at 6:32 AM, Niall O'Reilly  wrote:
> 
>> It may be more convenient to use the [Homebrew package 
>> manager](https://brew.sh/), which tracks SQLite pretty closely.  In order to 
>> avoid interfering with the Apple-supplied installation, Homebrew 
>> deliberately neglects to link the executable to _/usr/local/bin_, so some 
>> care is needed when invoking SQLite in order to run the intended version.



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite_column_table_name() and table alias name

[Jake Chen]

Hi,

My apologies if it's impolite to reply to such an ancient thread.

I've encountered the same problem while implementing an object-relational 
mapping library for Node.js. Is there any chance that OP's patch gets merged 
into master?

If this email weren't able to get related to the original post, here's the link 
http://mailinglists.sqlite.org/cgi-bin/mailman/private/sqlite-users/2014-November/056379.html
 


Jake Chen
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] possible bug: separator string and quote mode

[petern]

Pamela.  Shell .mode quote uses hardwired separator, presumably to rule out
errors when the mode is requested to generate SQL compatible strings.

It seems to me the enhancement you're requesting could be made backward
compatible by using  p->colSeparator instead of the hardwired ',' and then
setting the  p->colSeparator to the backward compatible comma in the .mode
quote meta-command and then allowing the user to subsequently override with
the .separator command.

Maybe there is a different reason for the hardwiring or reason why it can't
be changed.  That would be Richard's decision.

Peter
--
/*from shell.c*/
case MODE_Quote: {
  if( azArg==0 ) break;
  if( p->cnt==0 && p->showHeader ){
for(i=0; i0 ) raw_printf(p->out, ",");
  output_quoted_string(p->out, azCol[i]);
}
raw_printf(p->out,"\n");
  }
  p->cnt++;
  for(i=0; i0 ) raw_printf(p->out, ",");
if( (azArg[i]==0) || (aiType && aiType[i]==SQLITE_NULL) ){
  utf8_printf(p->out,"NULL");
}else if( aiType && aiType[i]==SQLITE_TEXT ){
  output_quoted_string(p->out, azArg[i]);
}else if( aiType && aiType[i]==SQLITE_INTEGER ){
  utf8_printf(p->out,"%s", azArg[i]);
}else if( aiType && aiType[i]==SQLITE_FLOAT ){
  char z[50];
  double r = sqlite3_column_double(p->pStmt, i);
  sqlite3_snprintf(50,z,"%!.20g", r);
  raw_printf(p->out, "%s", z);
}else if( aiType && aiType[i]==SQLITE_BLOB && p->pStmt ){
  const void *pBlob = sqlite3_column_blob(p->pStmt, i);
  int nBlob = sqlite3_column_bytes(p->pStmt, i);
  output_hex_blob(p->out, pBlob, nBlob);
}else if( isNumber(azArg[i], 0) ){
  utf8_printf(p->out,"%s", azArg[i]);
}else{
  output_quoted_string(p->out, azArg[i]);
}
  }
  raw_printf(p->out,"\n");
  break;
}


On Sun, Jan 14, 2018 at 9:29 AM, p dev  wrote:

> I have been using SQLite for a small data-processing application.
> I am hoping to get quote mode output with a field separator that is not a
> comma.Many thanks,
> Pamela--
> Problem
>
> Quote mode output ignores separator string
> Background
> https://www.sqlite.org/cli.html5. Changing Output Formats
> ...
> In "quote" mode, the output is formatted as SQL literals. Strings are
> enclosed in single-quotes and internal single-quotes are escaped by
> doubling. Blobs are displayed in hexadecimal blob literal notation (Ex:
> x'abcd'). Numbers are displayed as ASCII text and NULL values are shown as
> "NULL". All columns are separated from each other by a comma (or whatever
> alternative character is selected using ".separator").
> ...
>
> Demonstration
>
> SQLite version 3.21.0 2017-10-24 18:55:49
> Enter ".help" for usage hints.
> sqlite> create table tbl1(one varchar(10), two smallint);
> sqlite> insert into tbl1 values('hello!',10);
> sqlite> insert into tbl1 values('goodbye', 20);
> sqlite> insert into tbl1 values(null, 30);
> sqlite> select * from tbl1;
> hello!|10
> goodbye|20
> |30
>
> sqlite> .separator #
> sqlite> select * from tbl1;
> hello!#10
> goodbye#20
> #30
>
> sqlite> .mode quote
> sqlite> select * from tbl1;
> 'hello!',10
> 'goodbye',20
> NULL,30
>
>
> Expected Output
>
> sqlite> select * from tbl1;'hello!'#10
> 'goodbye'#20
> NULL#30
>
> sqlite> .show
> echo: off
>  eqp: off
>  explain: auto
>  headers: on
> mode: quote
>nullvalue: ""
>   output: stdout
> colseparator: "#"
> rowseparator: "\n"
>stats: off
>width:
> filename: data.db3
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite_column_table_name() and table alias name

[petern]

Jake.  Maybe somebody else can see the patch.  There's a login screen at
the linked page.
Others are using Node.js with the current version of SQLite.  Are you sure
that patch is needed?
If you can briefly explain what's not working I'm sure somebody else will
reply.
Peter


On Sun, Jan 14, 2018 at 9:17 PM, Jake Chen  wrote:

> Hi,
>
> My apologies if it's impolite to reply to such an ancient thread.
>
> I've encountered the same problem while implementing an object-relational
> mapping library for Node.js. Is there any chance that OP's patch gets
> merged into master?
>
> If this email weren't able to get related to the original post, here's the
> link http://mailinglists.sqlite.org/cgi-bin/mailman/private/
> sqlite-users/2014-November/056379.html  org/cgi-bin/mailman/private/sqlite-users/2014-November/056379.html>
>
> Jake Chen
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite_column_table_name() and table alias name

[Jake Chen]

Hi Peter,

Currently the query result returned by node-sqlite3 has the rows formatted into 
objects all ready. Given SQL `SELECT foo.*, bar.* FROM foo JOIN bar`, the 
columns of foo and bar collide with each other. I've already sent a PR 
https://github.com/mapbox/node-sqlite3/pull/932 
 to node-sqlite3 to allow a 
different result structure that keeps the rows untouched and returns fields 
along with rows. 

However, when the SQL contains table aliases, such as `SELECT foo.*, bar.* FROM 
egg AS foo JOIN egg AS bar ON foo.id  = bar.parent_id`, there's 
no way to return the fields correctly since `sqlite_column_table_name()` 
returns the actual table name rather than the alias. The link I attached in 
previous post is a discussion that took place 4 years ago. OP suggests a new 
function called `sqlite_column_table_alias_name()` to return the table alias 
instead of actual name. A patch is attached to implement this function. The 
major change is in `columnTypeImpl` of `src/select.c`:

@@ -105364,6 +105386,7 @@
  if( jnSrc ){
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;
  }

Source: 
http://mailinglists.sqlite.org/cgi-bin/mailman/private/sqlite-users/2014-November/056388.html
 

 

Jake
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users