Re: [sqlite] After column add, what should be done to update the schema?

[Keith Medcalf]

Probably a bug.  SQLiteExpert does not even run on my computer.  It just farts 
in the wind and does not even bother to log or show an error message.  Maybe it 
tries to access some internal Microsoft Spying mechanism that I have disabled.

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-Original Message-
>From: sqlite-users  On
>Behalf Of Andy KU7T
>Sent: Thursday, 27 February, 2020 17:03
>To: SQLite mailing list 
>Subject: [sqlite] After column add, what should be done to update the
>schema?
>
>Hi,
>
>I use a simple ALTER TABLE ADD COLUMN statement. However, when I use the
>Sqlite Expert, the DDL is not reflected. Even though the column is
>created. I read somewhere that the DDL is really just a copy of the text
>when teh table was created. Is there a way to refresh this?
>
>Thanks,
>Andy
>
>___
>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] After column add, what should be done to update the schema?

[Jean-Christophe Deschamps]

Bonjour,

I use a simple ALTER TABLE ADD COLUMN statement. However, when I use 
the Sqlite Expert, the DDL is not reflected. Even though the column is 
created. I read somewhere that the DDL is really just a copy of the 
text when teh table was created. Is there a way to refresh this?


I can't reproduce the issue with neither Expert v3.5.96 nor v5.3.5.472.
Post a short reproducer.

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

[sqlite] After column add, what should be done to update the schema?

[Andy KU7T]

Hi,

I use a simple ALTER TABLE ADD COLUMN statement. However, when I use the Sqlite 
Expert, the DDL is not reflected. Even though the column is created. I read 
somewhere that the DDL is really just a copy of the text when teh table was 
created. Is there a way to refresh this?

Thanks,
Andy

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

Re: [sqlite] sqlite-users Digest, Vol 146, Issue 27

[Balaji Ramanathan]

On Thu, Feb 27, 2020 at 6:00 AM <
sqlite-users-requ...@mailinglists.sqlite.org> wrote:

>   18. Cannot export 'sqlite3' file to CSV (Rachael Courts)
> From: Rachael Courts 
> To: "sqlite-users@mailinglists.sqlite.org" <
> sqlite-users@mailinglists.sqlite.org>
> Cc:
> Bcc:
> Date: Thu, 27 Feb 2020 01:05:50 +
> Subject: [sqlite] Cannot export 'sqlite3' file to CSV
> Hi All,
>
> I'm a couple of months into my PhD, studying bioacoustics. I am using
> SQLiteStudio to open '.sqlite3' files, which I need to convert to CSV files
> to import into MATLAB for further analyses.
>
> Currently I have attempted the data export function, selecting which
> database and table I would like to export, however no option for 'CSV'
> exportation is available. The only options that appear are 'JSON, SQL,
> HTML, and PDF' which can't be read into excel.
>
> I have also attempted the queries tab in SQLiteStudio, in an attempt to
> write code for exportation. This did not work.
>
> I also attempted opening the command window on my computer, to change the
> '.sqlite3' file to 'CSV' but it seems that the '.sqlite3' file doesn't have
> a '.db' attached, and therefore will not open in the command window for
> editing.
>
> I'm seeking ANY advice on how to export these files into CSV, so they can
> be read in excel, and easily imported into MATLAB.
>
> I've attached an example file, if this helps.
>
> I hope to hear from someone soon,
> Rachael
>

I am not sure exactly what you are trying to do, but SQLiteStudio does
provide you the ability to export as CSV.  Are you trying to export just
the table or the table + table indexes + table triggers?  CSV export is
supported under Data ->Export only for table export (not for table +
indexes or table + triggers, etc.).

If you are trying to export the whole database, it is best to think of
a database with multiple tables as a spreadsheet with multiple tabs.  You
can export each tab as a CSV, but you can't export an entire multi-tab
spreadsheet as one CSV.  Same thing applies to databases.  You can either
export each table as a CSV, then import them into multiple tabs of a
spreadsheet and use vlookups to relate the data into one bigger dataset
with more columns - or better yet, write some SQL to "join" the tables and
export the results of that query as a CSV for import into a spreadsheet.

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

Re: [sqlite] Issues with sqlite3session_attach(conn, NULL) w/ duplicate table in temp

[Adam Levy]

Dan,

I tried the above patch and it resolves the issue! Also you were right
about use of "*" in attach. I misinterpreted the output of my test in
that case.

Thank you so much for taking the time to fix this! When can I expect
this patch to make it into a release?

Adam Levy

On Thu, Feb 27, 2020 at 8:23 AM Dan Kennedy  wrote:
>
>
> On 27/2/63 05:04, Adam Levy wrote:
> > Hi all
> >
> > When I have a database connection with a temp table of the same name
> > as a table in main, I am getting what I feel is unexpected behavior
> > from the session extension.
> >
> > Most succinctly, I start a session on a connection on the main db with
> > a table with an INTEGER PRIMARY KEY, attach it to all tables by
> > passing NULL, make a change such as a single INSERT in aforementioned
> > table, and then record a changeset. Then I invert this changeset, and
> > apply that inverse to the same connection, to reverse the changes just
> > made.
> >
> > Normally this works flawlessly. However, if a table by the same name
> > exists in the temp database on that connection, I run into odd
> > behavrior.
> >
> > If the temp table has different rows than the main table, a very odd
> > changeset conflict occurs. The conflict type is SQLITE_CHANGESET_DATA
> > but when I examine the changeset iter the values for the columns, they
> > match the conflicting values. The inverse of an INSERT is a DELETE so
> > this conflict type indicates that one of the non-primary key column
> > values doesn't match, but in every examination of the database and the
> > changeset I have performed they do match. The primary key exists and
> > matches as well.
> >
> > If the temp table has the same rows at the main table (what the
> > changeset expects), the changeset_apply does not raise a conflict, but
> > it takes no affect on the main table.
>
> Thanks for reporting this. As far as I can figure, it's a bug in
> sqlite3changeset_apply(), not sqlite3session_attach(). Now fixed here:
>
>https://sqlite.org/src/info/f71a13d072398c9f
>
> If you get the chance, please check if this fixes your test script and
> let us know if it does not.
>
> > This can all be avoided by being explicit about the database when
> > calling sqlite3session_attach.
> > For example passing "*" or "main.*" or "main." avoids the
> > issue.
>
> I think that's just because sqlite3session_attach() doesn't understand
> any of those shorthands. If you pass "*", it searches for a table named
> "*", not a table that matches the glob pattern "*".
>
> Thanks,
>
> Dan.
>
>
>
>
>
> > But it occurs if NULL is passed. This is not obvious and
> > arguably not expected given that the documentation about the Session
> > extension makes it seem like it should be already scoped to a single
> > database on a connection. After all, sqlite3session_create accepts the
> > database as an argument. Also, sqlite3_changeset_apply appears to be
> > specific to the "main" database. So it shouldn't care about whats in
> > the temp database at all.
> >
> >  From all of the docs I have read on the Session extension this seems
> > like misbehavior at the level of sqlite3session_attach being passed
> > NULL for the zTab argument. At the minimum perhaps a note in the
> > documentation of sqlite3session_attach would be appropriate.
> >
> > Below is a very simple example that can reproduce this behavior. It
> > has a number of lines commented out that describe how the bug can be
> > avoided to caused.
> >
> > Its written in Golang, but the library it uses is just a very thin
> > wrapper around the C interface. It is using the latest 3.31.1
> > amalgamation. I help maintain the SQLite library that it uses and can
> > confirm that everything being called here is a very thin wrapper
> > around the C interface. I could write a C example but it would take me
> > more work.
> >
> > The library it calls to print the SQL of the changeset is a utility I
> > wrote that is just using a thin wrapper around the changeset_iter to
> > parse the changeset into human readable SQL. It of course is not aware
> > of database names so it doesn't print "main" but this SQL is just for
> > displaying to the user. The values returned by
> > sqlite3changeset_conflict are formatted into the comments of that
> > printed SQL.
> >
> > The code can be more easily downloaded here:
> > https://github.com/AdamSLevy/sqlite-session-bug
> >
> > // main.go
> >
> > package main
> >
> > import (
> >  "bytes"
> >  "fmt"
> >  "io"
> >  "log"
> >
> >  "crawshaw.io/sqlite"
> >  "crawshaw.io/sqlite/sqlitex"
> >  "github.com/AdamSLevy/sqlitechangeset"
> > )
> >
> > func run() error {
> >  conn, err := sqlite.OpenConn(":memory:", 0)
> >  if err != nil {
> >  return fmt.Errorf("sqlite open: %w", err)
> >  }
> >  defer conn.Close()
> >
> >  fmt.Println("Creating tables...")
> >  err = sqlitex.ExecScript(conn, `
> > CREATE TABLE main.t(id INTEGER PRIMARY KEY, a,b,c);
> > CREATE TABLE temp.t(id INTEGER 

Re: [sqlite] Issues with sqlite3session_attach(conn, NULL) w/ duplicate table in temp

[Dan Kennedy]

On 27/2/63 05:04, Adam Levy wrote:

Hi all

When I have a database connection with a temp table of the same name
as a table in main, I am getting what I feel is unexpected behavior
from the session extension.

Most succinctly, I start a session on a connection on the main db with
a table with an INTEGER PRIMARY KEY, attach it to all tables by
passing NULL, make a change such as a single INSERT in aforementioned
table, and then record a changeset. Then I invert this changeset, and
apply that inverse to the same connection, to reverse the changes just
made.

Normally this works flawlessly. However, if a table by the same name
exists in the temp database on that connection, I run into odd
behavrior.

If the temp table has different rows than the main table, a very odd
changeset conflict occurs. The conflict type is SQLITE_CHANGESET_DATA
but when I examine the changeset iter the values for the columns, they
match the conflicting values. The inverse of an INSERT is a DELETE so
this conflict type indicates that one of the non-primary key column
values doesn't match, but in every examination of the database and the
changeset I have performed they do match. The primary key exists and
matches as well.

If the temp table has the same rows at the main table (what the
changeset expects), the changeset_apply does not raise a conflict, but
it takes no affect on the main table.


Thanks for reporting this. As far as I can figure, it's a bug in 
sqlite3changeset_apply(), not sqlite3session_attach(). Now fixed here:


  https://sqlite.org/src/info/f71a13d072398c9f

If you get the chance, please check if this fixes your test script and 
let us know if it does not.



This can all be avoided by being explicit about the database when
calling sqlite3session_attach.
For example passing "*" or "main.*" or "main." avoids the
issue.


I think that's just because sqlite3session_attach() doesn't understand 
any of those shorthands. If you pass "*", it searches for a table named 
"*", not a table that matches the glob pattern "*".


Thanks,

Dan.






But it occurs if NULL is passed. This is not obvious and
arguably not expected given that the documentation about the Session
extension makes it seem like it should be already scoped to a single
database on a connection. After all, sqlite3session_create accepts the
database as an argument. Also, sqlite3_changeset_apply appears to be
specific to the "main" database. So it shouldn't care about whats in
the temp database at all.

 From all of the docs I have read on the Session extension this seems
like misbehavior at the level of sqlite3session_attach being passed
NULL for the zTab argument. At the minimum perhaps a note in the
documentation of sqlite3session_attach would be appropriate.

Below is a very simple example that can reproduce this behavior. It
has a number of lines commented out that describe how the bug can be
avoided to caused.

Its written in Golang, but the library it uses is just a very thin
wrapper around the C interface. It is using the latest 3.31.1
amalgamation. I help maintain the SQLite library that it uses and can
confirm that everything being called here is a very thin wrapper
around the C interface. I could write a C example but it would take me
more work.

The library it calls to print the SQL of the changeset is a utility I
wrote that is just using a thin wrapper around the changeset_iter to
parse the changeset into human readable SQL. It of course is not aware
of database names so it doesn't print "main" but this SQL is just for
displaying to the user. The values returned by
sqlite3changeset_conflict are formatted into the comments of that
printed SQL.

The code can be more easily downloaded here:
https://github.com/AdamSLevy/sqlite-session-bug

// main.go

package main

import (
 "bytes"
 "fmt"
 "io"
 "log"

 "crawshaw.io/sqlite"
 "crawshaw.io/sqlite/sqlitex"
 "github.com/AdamSLevy/sqlitechangeset"
)

func run() error {
 conn, err := sqlite.OpenConn(":memory:", 0)
 if err != nil {
 return fmt.Errorf("sqlite open: %w", err)
 }
 defer conn.Close()

 fmt.Println("Creating tables...")
 err = sqlitex.ExecScript(conn, `
CREATE TABLE main.t(id INTEGER PRIMARY KEY, a,b,c);
CREATE TABLE temp.t(id INTEGER PRIMARY KEY, a,b,c) --- remove this
line or rename the table to avoid issue;
`)
 if err != nil {
 return err
 }
 fmt.Println("Starting session on main...")
 sess, err := conn.CreateSession("main")
 if err != nil {
 return err
 }
 defer sess.Delete()
 // An empty string will pass NULL to sqlite3session_attach and allow
 // the bug.
 var attach string
 // Any of these prevent the bug.
 //attach = "*"
 //attach = "main.*"
 //attach = "main.t"
 fmt.Printf("Attaching to %s ...\n", attach)
 if err := sess.Attach(attach); err != nil {
 return err
 }

 fmt.Println("Inserting into main.t ...")
 commit := 

Re: [sqlite] Cannot export 'sqlite3' file to CSV

[sky5walk]

Easier still: Install https://nightlies.sqlitebrowser.org/latest/
Then open your sqlite database(s) directly within the browser.
There you can run queries and/or export to csv.

On Thu, Feb 27, 2020, 4:54 AM Shawn Wagner  wrote:

> Use the standard sqlite3 shell program:
> https://www.sqlite.org/cli.html#csv_export
>
> On Thu, Feb 27, 2020, 1:48 AM Rachael Courts 
> wrote:
>
> > Hi All,
> >
> > I'm a couple of months into my PhD, studying bioacoustics. I am using
> > SQLiteStudio to open '.sqlite3' files, which I need to convert to CSV
> files
> > to import into MATLAB for further analyses.
> >
> > Currently I have attempted the data export function, selecting which
> > database and table I would like to export, however no option for 'CSV'
> > exportation is available. The only options that appear are 'JSON, SQL,
> > HTML, and PDF' which can't be read into excel.
> >
> > I have also attempted the queries tab in SQLiteStudio, in an attempt to
> > write code for exportation. This did not work.
> >
> > I also attempted opening the command window on my computer, to change the
> > '.sqlite3' file to 'CSV' but it seems that the '.sqlite3' file doesn't
> have
> > a '.db' attached, and therefore will not open in the command window for
> > editing.
> >
> > I'm seeking ANY advice on how to export these files into CSV, so they can
> > be read in excel, and easily imported into MATLAB.
> >
> > I've attached an example file, if this helps.
> >
> > I hope to hear from someone soon,
> > Rachael
> >
> >
> > Sent from Outlook
> > ___
> > 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] VFS xFullPathname cannot access URI parameters

[Richard Hipp]

On 2/26/20, Chris Warner  wrote:
> In 3.31.1, attempting to access URI Parameters via the sqlite3_uri_*
> functions inside a VFS xFullPathname function returns junk.  This was not an
> issue in 3.30.1 or 3.31.0.
>

SQLite does not promise that sqlite3_uri_parameter() will work for the
argument passed into xFullPathname.  But it has done so historically,
and (apparently) some software has come to depend on that accidental
behavior.  So I have implemented changes on trunk, and on branch-3.31
to make it work again.

-- 
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] Cannot export 'sqlite3' file to CSV

[Shawn Wagner]

Use the standard sqlite3 shell program:
https://www.sqlite.org/cli.html#csv_export

On Thu, Feb 27, 2020, 1:48 AM Rachael Courts 
wrote:

> Hi All,
>
> I'm a couple of months into my PhD, studying bioacoustics. I am using
> SQLiteStudio to open '.sqlite3' files, which I need to convert to CSV files
> to import into MATLAB for further analyses.
>
> Currently I have attempted the data export function, selecting which
> database and table I would like to export, however no option for 'CSV'
> exportation is available. The only options that appear are 'JSON, SQL,
> HTML, and PDF' which can't be read into excel.
>
> I have also attempted the queries tab in SQLiteStudio, in an attempt to
> write code for exportation. This did not work.
>
> I also attempted opening the command window on my computer, to change the
> '.sqlite3' file to 'CSV' but it seems that the '.sqlite3' file doesn't have
> a '.db' attached, and therefore will not open in the command window for
> editing.
>
> I'm seeking ANY advice on how to export these files into CSV, so they can
> be read in excel, and easily imported into MATLAB.
>
> I've attached an example file, if this helps.
>
> I hope to hear from someone soon,
> Rachael
>
>
> Sent from Outlook
> ___
> 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] Cannot export 'sqlite3' file to CSV

[Rachael Courts]

Hi All,

I'm a couple of months into my PhD, studying bioacoustics. I am using 
SQLiteStudio to open '.sqlite3' files, which I need to convert to CSV files to 
import into MATLAB for further analyses.

Currently I have attempted the data export function, selecting which database 
and table I would like to export, however no option for 'CSV' exportation is 
available. The only options that appear are 'JSON, SQL, HTML, and PDF' which 
can't be read into excel.

I have also attempted the queries tab in SQLiteStudio, in an attempt to write 
code for exportation. This did not work.

I also attempted opening the command window on my computer, to change the 
'.sqlite3' file to 'CSV' but it seems that the '.sqlite3' file doesn't have a 
'.db' attached, and therefore will not open in the command window for editing.

I'm seeking ANY advice on how to export these files into CSV, so they can be 
read in excel, and easily imported into MATLAB.

I've attached an example file, if this helps.

I hope to hear from someone soon,
Rachael


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