Re: [sqlite] Is it possible to transpose a table using SQL?

2019-03-31 Thread Shane Dev
Hi Keith, Jean-Luc

I should have mentioned my shell is configured to display column headers
Product/Region|Belgium|France|USA for table t1 and
Product/Region|Oil_filter|Spark_plug|Coolent for view v1. By "dynamically
changing table", I meant the number of columns and rows could could change
after the dependant view was created. it appears this is impossible using
only SQL

On Mon, 1 Apr 2019 at 02:38, Keith Medcalf  wrote:

>
> On Sunday, 31 March, 2019 14:07, Shane Dev  wrote:
>
> >Is it possible to create a view which switches rows and columns of a
> >dynamically changing table?
>
> >For example, imagine we have table t1 where both columns and rows
> >could change after the view has been created
>
> >sqlite> select * from t1;
> >Product/Region|Belgium|France|USA
> >Oil_filter|1|2|3
> >Spark_plug|4|5|6
> >Coolent|7|8|9
>
> >Could view v1 be created such that
>
> >sqlite> select * from v1;
> >Product/Region|Oil_filter|Spark_plug|Coolent
> >Belgium|1|4|7
> >France|2|5|8
> >USA|3|6|9
>
> You example is ambiguous.
>
> For example, is the table T1 thus:
>
> create table T1("Product/Region" text not null, Belgium integer not null,
> France integer not null, USA integer not null);
> insert into T1 values ('Oil_filter', 1, 2, 3);
> insert into T1 values ('Spark_plug', 4, 5, 6);
> insert into T2 values ('Coolent', 7, 8, 9);
>
> or so:
>
> create table T1(c0, c1, c2, c3);
> insert into T1 values ('Product/Region', 'Belgium', 'France', 'USA');
> insert into T1 values ('Oil_filter', 1, 2, 3);
> insert into T1 values ('Spark_plug', 4, 5, 6);
> insert into T2 values ('Coolent', 7, 8, 9);
>
> If "so", how do you know which column/row is the proposed row/column
> names?  Or do you just want to transpose the matrix?
>
> Please explain what you mean by "dynamically changing table" ... what
> exactly is dynamically changing?  The number of columns?  The number of
> rows?
>
> Note this is probably relatively simple for kiddie sized data but would be
> far more efficient if you did it at the application level.  It would be
> even simpler if the data were properly normalized.
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
>
>
>
> ___
> 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] FTS5 Transaction Leads to NULL Pointer

2019-03-31 Thread Philip Newton
On Tue, 19 Mar 2019 at 07:35, Dan Kennedy  wrote:
>
> Now fixed here:
>
>https://sqlite.org/src/info/45c73deb440496e8

From that diff, it seems that you changed the documentation of the
function's parameters where the function was defined (see fts5_hash.c
line 489 at the right) but not where the prototype was declared (see
fts5Int.h line 588 at the right, which still talks about a "pointer to
doclist").
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Bug in sqlite3 (CLI, linux/Ubuntu)

2019-03-31 Thread Keith Medcalf

Works fine for me on Centos ... using the default (ancient) version of SQLite3

# sqlite3 /tmp/test.db "CREATE TABLE test (col1); INSERT INTO test VALUES(1);"
# touch /tmp/test.init
# sqlite3 /tmp/test.db "SELECT * FROM test;" 2>/dev/null
1
# sqlite3 -init /tmp/test.init /tmp/test.db "SELECT * FROM test;" 2>/dev/null
1
[root@mail ~]# sqlite3 -init /tmp/test.init /tmp/test.db "SELECT * FROM test;"
-- Loading resources from /tmp/test.init
1
# sqlite3 --version
3.6.20
#

and with the current tip of trunk

# ./sqlite3 -init /tmp/test.init /tmp/test.db "SELECT * FROM test;"
-- Loading resources from /tmp/test.init
1
# ./sqlite3 -init /tmp/test.init /tmp/test.db "SELECT * FROM test;" 2>/dev/null
1
# ./sqlite3 --version
3.28.0 2019-03-31 18:17:00 
d03b611302f68483770d49b113b4ed685ba03526d2007647c306f8ec7ae697d2

---
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 [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of
>softw...@quantentunnel.de
>Sent: Saturday, 30 March, 2019 05:28
>To: sqlite-users@mailinglists.sqlite.org
>Subject: [sqlite] Bug in sqlite3 (CLI, linux/Ubuntu)
>
>Dear colleagues
>
>I detected an unexpected behaviour in sqlite3 (CLI) that I consider a
>bug as it seems not documented.
>
>When using an init file (even if an empty file), sqlite3 outputs an
>extra empty line to stdout. This messes up parsing of the sqlite3
>output, as this line is not present in the absence of an init file:
>
>buero:~$ sqlite3 /tmp/test.db "CREATE TABLE test (col1); INSERT INTO
>test VALUES(1);"
>buero:~$ touch /tmp/test.init
>buero:~$ ls -l /tmp/test.*
>-rw-r- 1 abc abc 2048 Mär 30 12:17 /tmp/test.db
>-rw-r- 1 abc abc0 Mär 30 12:17 /tmp/test.init
>buero:~$ sqlite3 /tmp/test.db "SELECT * FROM test;" 2>/dev/null
>1
>buero:~$ sqlite3 -init /tmp/test.init /tmp/test.db "SELECT * FROM
>test;" 2>/dev/null
>
>1
>buero:~$
>
>
>My configuration
>buero:~$ sqlite3 --version
>3.11.0 2016-02-15 17:29:24 3d862f207e3adc00f78066799ac5a8c282430a5f
>buero:~$ uname -a
>Linux buero 4.15.0-46-generic #49~16.04.1-Ubuntu SMP Tue Feb 12
>17:45:24 UTC 2019 x86_64 x86_64 x86_64 GNU/Linux
>___
>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] Is it possible to transpose a table using SQL?

2019-03-31 Thread Keith Medcalf

On Sunday, 31 March, 2019 14:07, Shane Dev  wrote:

>Is it possible to create a view which switches rows and columns of a
>dynamically changing table?

>For example, imagine we have table t1 where both columns and rows
>could change after the view has been created

>sqlite> select * from t1;
>Product/Region|Belgium|France|USA
>Oil_filter|1|2|3
>Spark_plug|4|5|6
>Coolent|7|8|9

>Could view v1 be created such that

>sqlite> select * from v1;
>Product/Region|Oil_filter|Spark_plug|Coolent
>Belgium|1|4|7
>France|2|5|8
>USA|3|6|9

You example is ambiguous.

For example, is the table T1 thus:

create table T1("Product/Region" text not null, Belgium integer not null, 
France integer not null, USA integer not null);
insert into T1 values ('Oil_filter', 1, 2, 3);
insert into T1 values ('Spark_plug', 4, 5, 6);
insert into T2 values ('Coolent', 7, 8, 9);

or so:

create table T1(c0, c1, c2, c3);
insert into T1 values ('Product/Region', 'Belgium', 'France', 'USA');
insert into T1 values ('Oil_filter', 1, 2, 3);
insert into T1 values ('Spark_plug', 4, 5, 6);
insert into T2 values ('Coolent', 7, 8, 9);

If "so", how do you know which column/row is the proposed row/column names?  Or 
do you just want to transpose the matrix?

Please explain what you mean by "dynamically changing table" ... what exactly 
is dynamically changing?  The number of columns?  The number of rows?

Note this is probably relatively simple for kiddie sized data but would be far 
more efficient if you did it at the application level.  It would be even 
simpler if the data were properly normalized.

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




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


Re: [sqlite] Is it possible to transpose a table using SQL?

2019-03-31 Thread Jean-Luc Hainaut
If by "a dynamically changing table " you mean that any data change (not 
schema change) in t1 will propagate to v1, and if the set of Product 
values doesn't change, then you can try this:


create table t1(Product,Belgium,France,USA);
insert into t1 values 
('OilFilter',1,2,3),('SparkPlug',4,5,6),('Coolent',7,8,9);

select * from t1;

create view v1(Region,OilFilter,SparkPlug,Coolent)
as select 'Belgium' as Region,
  sum(case Product when 'OilFilter' then Belgium else null end) 
as OilFilter,
  sum(case Product when 'SparkPlug' then Belgium else null end) 
as SparkPlug,
  sum(case Product when 'Coolent'   then Belgium else null end) 
as Coolent

   from t1
  union
   select 'France' as Region,
  sum(case Product when 'OilFilter' then France else null end) 
as OilFilter,
  sum(case Product when 'SparkPlug' then France else null end) 
as SparkPlug,
  sum(case Product when 'Coolent'   then France else null end) 
as Coolent

   from t1
  union
   select 'USA' as Region,
  sum(case Product when 'OilFilter' then USA else null end) as 
OilFilter,
  sum(case Product when 'SparkPlug' then USA else null end) as 
SparkPlug,
  sum(case Product when 'Coolent'   then USA else null end) as 
Coolent

   from t1;
select * from v1;

t1:
+---+-++-+
| Product   | Belgium | France | USA |
+---+-++-+
| OilFilter | 1   | 2  | 3   |
| SparkPlug | 4   | 5  | 6   |
| Coolent   | 7   | 8  | 9   |
+---+-++-+
v1:
+-+---+---+-+
| Region  | OilFilter | SparkPlug | Coolent |
+-+---+---+-+
| Belgium | 1 | 4 | 7   |
| France  | 2 | 5 | 8   |
| USA | 3 | 6 | 9   |
+-+---+---+-+

J-L Hainaut


On 31/03/2019 22:07, Shane Dev wrote:

Hello,

Is it possible to create a view which switches rows and columns of a
dynamically changing table?

For example, imagine we have table t1 where both columns and rows could
change after the view has been created

sqlite> select * from t1;
Product/Region|Belgium|France|USA
Oil_filter|1|2|3
Spark_plug|4|5|6
Coolent|7|8|9

Could view v1 be created such that

sqlite> select * from v1;
Product/Region|Oil_filter|Spark_plug|Coolent
Belgium|1|4|7
France|2|5|8
USA|3|6|9
___
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] Bug in sqlite3 (CLI, linux/Ubuntu)

2019-03-31 Thread Software

> Dear colleagues 
> 
> I detected an unexpected behaviour in sqlite3 (CLI) that I consider a bug as 
> it seems not documented. 
> 
> When using an init file (even if an empty file), sqlite3 outputs an extra 
> empty line to stdout. This messes up parsing of the sqlite3 output, as this 
> line is not present in the absence of an init file: 
> 
> buero:~$ sqlite3 /tmp/test.db "CREATE TABLE test (col1); INSERT INTO test 
> VALUES(1);" 
> buero:~$ touch /tmp/test.init 
> buero:~$ ls -l /tmp/test.* 
> -rw-r- 1 abc abc 2048 Mär 30 12:17 /tmp/test.db 
> -rw-r- 1 abc abc0 Mär 30 12:17 /tmp/test.init 
> buero:~$ sqlite3 /tmp/test.db "SELECT * FROM test;" 2>/dev/null 
> 1 
> buero:~$ sqlite3 -init /tmp/test.init /tmp/test.db "SELECT * FROM test;" 
> 2>/dev/null 
> 
> 1 
> buero:~$ 
> 
> 
> My configuration 
> buero:~$ sqlite3 --version 
> 3.11.0 2016-02-15 17:29:24 3d862f207e3adc00f78066799ac5a8c282430a5f 
> buero:~$ uname -a 
> Linux buero 4.15.0-46-generic #49~16.04.1-Ubuntu SMP Tue Feb 12 17:45:24 UTC 
> 2019 x86_64 x86_64 x86_64 GNU/Linux 
> ___
... [show rest of quote]

upgrade ? 

~$ sqlite3 /tmp/test.db "CREATE TABLE test (col1); INSERT INTO test 
VALUES(1);" 
~$ 
~$ touch /tmp/test.init 
~$ sqlite3 /tmp/test.db "SELECT * FROM test;" 2>/dev/null 
1 
~$ 
~$ 
~$ sqlite3 -int /tmp/test.init /tmp/test.db "SELECT * FROM test;" 
2>/dev/null 
~$ ls -l /tmp 
total 16 
-rw-r--r-- 1 luuk luuk 8192 Mar 30 17:18 test.db 
-rw-rw-rw- 1 luuk luuk0 Mar 30 17:18 test.init 
~$ sqlite3 -version 
3.22.0 2018-01-22 18:45:57 
0c55d179733b46d8d0ba4d88e01a25e10677046ee3da1d5b1581e86726f2alt1 
~$ 


Hi Luuk

Thanks for looking into it. There is a typo in your example (-int instead of 
-init); the error message ("sqlite3: Error: unknown option: -int") went to 
/dev/null. Thus, there is no output to stdout; also the expected query result 
'1' is lacking.

Before I manually upgrade (I use currently the latest automatically uogradable 
version in Ubuntu xenial): would it be possible that you re-run the example 
(with -init) to check whether the leading empty line is indeed no longer 
present in version 3.22?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Is it possible to transpose a table using SQL?

2019-03-31 Thread Simon Slavin
On 31 Mar 2019, at 9:07pm, Shane Dev  wrote:

> Is it possible to create a view which switches rows and columns of a 
> dynamically changing table?

Sorry, but no.  A VIEW is just a saved SELECT statement.  If you can't do it in 
a SELECT statement, you can't do it in a view.  And you can't do that.

What you're asking for – switching rows and columns – is not the way SQL looks 
at data.  Some SQL engines provide a custom function which does it (e.g. SQL 
Server) because of how difficult it is to do it without a special function.

For SQLite it's going to be easier to do it in your favourite programming 
language using SQL just to retrieve the data.

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


[sqlite] Is it possible to transpose a table using SQL?

2019-03-31 Thread Shane Dev
Hello,

Is it possible to create a view which switches rows and columns of a
dynamically changing table?

For example, imagine we have table t1 where both columns and rows could
change after the view has been created

sqlite> select * from t1;
Product/Region|Belgium|France|USA
Oil_filter|1|2|3
Spark_plug|4|5|6
Coolent|7|8|9

Could view v1 be created such that

sqlite> select * from v1;
Product/Region|Oil_filter|Spark_plug|Coolent
Belgium|1|4|7
France|2|5|8
USA|3|6|9
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users