Re: [sqlite] sqlite3rebaser_* function declaration error

[Keith Medcalf]

Thanks Richard, it is fixed.

---
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 Richard Hipp
>Sent: Friday, 23 March, 2018 18:20
>To: SQLite mailing list
>Subject: Re: [sqlite] sqlite3rebaser_* function declaration error
>
>Please try again with the latest and let me know whether or not the
>problem is fixed.  Thanks.
>
>On 3/23/18, Keith Medcalf  wrote:
>>
>> The forward references do not contain the SQLITE_API preface, yet
>the
>> function declarations do.  This means that if you define SQLITE_API
>as
>> static (ie, for inclusion in APSW) then the rebaser functions cause
>the
>> compiler to toss a bunch of errors:
>>
>> D:\Source\apsw\sqlite3.c:185554:16: error: static declaration of
>> 'sqlite3rebaser_create' follows non-static declaration
>>  SQLITE_API int sqlite3rebaser_create(sqlite3_rebaser **ppNew){
>> ^
>> In file included from src/apsw.c:47:0:
>> D:\Source\apsw\sqlite3.c:11369:5: note: previous declaration of
>> 'sqlite3rebaser_create' was here
>>  int sqlite3rebaser_create(sqlite3_rebaser **ppNew);
>>  ^
>> In file included from src/apsw.c:47:0:
>> D:\Source\apsw\sqlite3.c:185571:16: error: static declaration of
>> 'sqlite3rebaser_configure' follows non-static declaration
>>  SQLITE_API int sqlite3rebaser_configure(
>> ^~~~
>> In file included from src/apsw.c:47:0:
>> D:\Source\apsw\sqlite3.c:11380:5: note: previous declaration of
>> 'sqlite3rebaser_configure' was here
>>  int sqlite3rebaser_configure(
>>  ^~~~
>> In file included from src/apsw.c:47:0:
>> D:\Source\apsw\sqlite3.c:185588:16: error: static declaration of
>> 'sqlite3rebaser_rebase' follows non-static declaration
>>  SQLITE_API int sqlite3rebaser_rebase(
>> ^
>> In file included from src/apsw.c:47:0:
>> D:\Source\apsw\sqlite3.c:11399:5: note: previous declaration of
>> 'sqlite3rebaser_rebase' was here
>>  int sqlite3rebaser_rebase(
>>  ^
>> In file included from src/apsw.c:47:0:
>> D:\Source\apsw\sqlite3.c:185554:16: error: static declaration of
>> 'sqlite3rebaser_create' follows non-static declaration
>>  SQLITE_API int sqlite3rebaser_create(sqlite3_rebaser **ppNew){
>> ^
>> In file included from src/apsw.c:47:0:
>> D:\Source\apsw\sqlite3.c:11369:5: note: previous declaration of
>> 'sqlite3rebaser_create' was here
>>  int sqlite3rebaser_create(sqlite3_rebaser **ppNew);
>>  ^
>> In file included from src/apsw.c:47:0:
>> D:\Source\apsw\sqlite3.c:185607:16: error: static declaration of
>> 'sqlite3rebaser_rebase_strm' follows non-static declaration
>>  SQLITE_API int sqlite3rebaser_rebase_strm(
>> ^~
>> In file included from src/apsw.c:47:0:
>> D:\Source\apsw\sqlite3.c:11572:5: note: previous declaration of
>> 'sqlite3rebaser_rebase_strm' was here
>>  int sqlite3rebaser_rebase_strm(
>>  ^~
>> In file included from src/apsw.c:47:0:
>> D:\Source\apsw\sqlite3.c:185571:16: error: static declaration of
>> 'sqlite3rebaser_configure' follows non-static declaration
>>  SQLITE_API int sqlite3rebaser_configure(
>> ^~~~
>> In file included from src/apsw.c:47:0:
>> D:\Source\apsw\sqlite3.c:11380:5: note: previous declaration of
>> 'sqlite3rebaser_configure' was here
>>  int sqlite3rebaser_configure(
>>  ^~~~
>> In file included from src/apsw.c:47:0:
>> D:\Source\apsw\sqlite3.c:185628:17: error: static declaration of
>> 'sqlite3rebaser_delete' follows non-static declaration
>>  SQLITE_API void sqlite3rebaser_delete(sqlite3_rebaser *p){
>>  ^
>> In file included from src/apsw.c:47:0:
>> D:\Source\apsw\sqlite3.c:11413:6: note: previous declaration of
>> 'sqlite3rebaser_delete' was here
>>  void sqlite3rebaser_delete(sqlite3_rebaser *p);
>>   ^
>> In file included from src/apsw.c:47:0:
>> D:\Source\apsw\sqlite3.c:185588:16: error: static declaration of
>> 'sqlite3rebaser_rebase' follows non-static declaration
>>  SQLITE_API int sqlite3rebaser_rebase(
>> ^
>> In file included from src/apsw.c:47:0:
>> D:\Source\apsw\sqlite3.c:11399:5: note: previous declaration of
>> 'sqlite3rebaser_rebase' was here
>>  int sqlite3rebaser_rebase(
>>  ^
>> In file included from src/apsw.c:47:0:
>> D:\Source\apsw\sqlite3.c:185607:16: error: static declaration of
>> 'sqlite3rebaser_rebase_strm' follows non-static declaration
>>  SQLITE_API int sqlite3rebaser_rebase_strm(
>> ^~
>> In file included from src/apsw.c:47:0:
>> 

Re: [sqlite] sqlite3rebaser_* function declaration error

[Richard Hipp]

Please try again with the latest and let me know whether or not the
problem is fixed.  Thanks.

On 3/23/18, Keith Medcalf  wrote:
>
> The forward references do not contain the SQLITE_API preface, yet the
> function declarations do.  This means that if you define SQLITE_API as
> static (ie, for inclusion in APSW) then the rebaser functions cause the
> compiler to toss a bunch of errors:
>
> D:\Source\apsw\sqlite3.c:185554:16: error: static declaration of
> 'sqlite3rebaser_create' follows non-static declaration
>  SQLITE_API int sqlite3rebaser_create(sqlite3_rebaser **ppNew){
> ^
> In file included from src/apsw.c:47:0:
> D:\Source\apsw\sqlite3.c:11369:5: note: previous declaration of
> 'sqlite3rebaser_create' was here
>  int sqlite3rebaser_create(sqlite3_rebaser **ppNew);
>  ^
> In file included from src/apsw.c:47:0:
> D:\Source\apsw\sqlite3.c:185571:16: error: static declaration of
> 'sqlite3rebaser_configure' follows non-static declaration
>  SQLITE_API int sqlite3rebaser_configure(
> ^~~~
> In file included from src/apsw.c:47:0:
> D:\Source\apsw\sqlite3.c:11380:5: note: previous declaration of
> 'sqlite3rebaser_configure' was here
>  int sqlite3rebaser_configure(
>  ^~~~
> In file included from src/apsw.c:47:0:
> D:\Source\apsw\sqlite3.c:185588:16: error: static declaration of
> 'sqlite3rebaser_rebase' follows non-static declaration
>  SQLITE_API int sqlite3rebaser_rebase(
> ^
> In file included from src/apsw.c:47:0:
> D:\Source\apsw\sqlite3.c:11399:5: note: previous declaration of
> 'sqlite3rebaser_rebase' was here
>  int sqlite3rebaser_rebase(
>  ^
> In file included from src/apsw.c:47:0:
> D:\Source\apsw\sqlite3.c:185554:16: error: static declaration of
> 'sqlite3rebaser_create' follows non-static declaration
>  SQLITE_API int sqlite3rebaser_create(sqlite3_rebaser **ppNew){
> ^
> In file included from src/apsw.c:47:0:
> D:\Source\apsw\sqlite3.c:11369:5: note: previous declaration of
> 'sqlite3rebaser_create' was here
>  int sqlite3rebaser_create(sqlite3_rebaser **ppNew);
>  ^
> In file included from src/apsw.c:47:0:
> D:\Source\apsw\sqlite3.c:185607:16: error: static declaration of
> 'sqlite3rebaser_rebase_strm' follows non-static declaration
>  SQLITE_API int sqlite3rebaser_rebase_strm(
> ^~
> In file included from src/apsw.c:47:0:
> D:\Source\apsw\sqlite3.c:11572:5: note: previous declaration of
> 'sqlite3rebaser_rebase_strm' was here
>  int sqlite3rebaser_rebase_strm(
>  ^~
> In file included from src/apsw.c:47:0:
> D:\Source\apsw\sqlite3.c:185571:16: error: static declaration of
> 'sqlite3rebaser_configure' follows non-static declaration
>  SQLITE_API int sqlite3rebaser_configure(
> ^~~~
> In file included from src/apsw.c:47:0:
> D:\Source\apsw\sqlite3.c:11380:5: note: previous declaration of
> 'sqlite3rebaser_configure' was here
>  int sqlite3rebaser_configure(
>  ^~~~
> In file included from src/apsw.c:47:0:
> D:\Source\apsw\sqlite3.c:185628:17: error: static declaration of
> 'sqlite3rebaser_delete' follows non-static declaration
>  SQLITE_API void sqlite3rebaser_delete(sqlite3_rebaser *p){
>  ^
> In file included from src/apsw.c:47:0:
> D:\Source\apsw\sqlite3.c:11413:6: note: previous declaration of
> 'sqlite3rebaser_delete' was here
>  void sqlite3rebaser_delete(sqlite3_rebaser *p);
>   ^
> In file included from src/apsw.c:47:0:
> D:\Source\apsw\sqlite3.c:185588:16: error: static declaration of
> 'sqlite3rebaser_rebase' follows non-static declaration
>  SQLITE_API int sqlite3rebaser_rebase(
> ^
> In file included from src/apsw.c:47:0:
> D:\Source\apsw\sqlite3.c:11399:5: note: previous declaration of
> 'sqlite3rebaser_rebase' was here
>  int sqlite3rebaser_rebase(
>  ^
> In file included from src/apsw.c:47:0:
> D:\Source\apsw\sqlite3.c:185607:16: error: static declaration of
> 'sqlite3rebaser_rebase_strm' follows non-static declaration
>  SQLITE_API int sqlite3rebaser_rebase_strm(
> ^~
> In file included from src/apsw.c:47:0:
> D:\Source\apsw\sqlite3.c:11572:5: note: previous declaration of
> 'sqlite3rebaser_rebase_strm' was here
>  int sqlite3rebaser_rebase_strm(
>  ^~
> In file included from src/apsw.c:47:0:
> D:\Source\apsw\sqlite3.c:185628:17: error: static declaration of
> 'sqlite3rebaser_delete' follows non-static declaration
>  SQLITE_API void sqlite3rebaser_delete(sqlite3_rebaser *p){
>  ^
> In file included from src/apsw.c:47:0:
> D:\Source\apsw\sqlite3.c:11413:6: note: previous 

[sqlite] sqlite3rebaser_* function declaration error

[Keith Medcalf]

The forward references do not contain the SQLITE_API preface, yet the function 
declarations do.  This means that if you define SQLITE_API as static (ie, for 
inclusion in APSW) then the rebaser functions cause the compiler to toss a 
bunch of errors:

D:\Source\apsw\sqlite3.c:185554:16: error: static declaration of 
'sqlite3rebaser_create' follows non-static declaration
 SQLITE_API int sqlite3rebaser_create(sqlite3_rebaser **ppNew){
^
In file included from src/apsw.c:47:0:
D:\Source\apsw\sqlite3.c:11369:5: note: previous declaration of 
'sqlite3rebaser_create' was here
 int sqlite3rebaser_create(sqlite3_rebaser **ppNew);
 ^
In file included from src/apsw.c:47:0:
D:\Source\apsw\sqlite3.c:185571:16: error: static declaration of 
'sqlite3rebaser_configure' follows non-static declaration
 SQLITE_API int sqlite3rebaser_configure(
^~~~
In file included from src/apsw.c:47:0:
D:\Source\apsw\sqlite3.c:11380:5: note: previous declaration of 
'sqlite3rebaser_configure' was here
 int sqlite3rebaser_configure(
 ^~~~
In file included from src/apsw.c:47:0:
D:\Source\apsw\sqlite3.c:185588:16: error: static declaration of 
'sqlite3rebaser_rebase' follows non-static declaration
 SQLITE_API int sqlite3rebaser_rebase(
^
In file included from src/apsw.c:47:0:
D:\Source\apsw\sqlite3.c:11399:5: note: previous declaration of 
'sqlite3rebaser_rebase' was here
 int sqlite3rebaser_rebase(
 ^
In file included from src/apsw.c:47:0:
D:\Source\apsw\sqlite3.c:185554:16: error: static declaration of 
'sqlite3rebaser_create' follows non-static declaration
 SQLITE_API int sqlite3rebaser_create(sqlite3_rebaser **ppNew){
^
In file included from src/apsw.c:47:0:
D:\Source\apsw\sqlite3.c:11369:5: note: previous declaration of 
'sqlite3rebaser_create' was here
 int sqlite3rebaser_create(sqlite3_rebaser **ppNew);
 ^
In file included from src/apsw.c:47:0:
D:\Source\apsw\sqlite3.c:185607:16: error: static declaration of 
'sqlite3rebaser_rebase_strm' follows non-static declaration
 SQLITE_API int sqlite3rebaser_rebase_strm(
^~
In file included from src/apsw.c:47:0:
D:\Source\apsw\sqlite3.c:11572:5: note: previous declaration of 
'sqlite3rebaser_rebase_strm' was here
 int sqlite3rebaser_rebase_strm(
 ^~
In file included from src/apsw.c:47:0:
D:\Source\apsw\sqlite3.c:185571:16: error: static declaration of 
'sqlite3rebaser_configure' follows non-static declaration
 SQLITE_API int sqlite3rebaser_configure(
^~~~
In file included from src/apsw.c:47:0:
D:\Source\apsw\sqlite3.c:11380:5: note: previous declaration of 
'sqlite3rebaser_configure' was here
 int sqlite3rebaser_configure(
 ^~~~
In file included from src/apsw.c:47:0:
D:\Source\apsw\sqlite3.c:185628:17: error: static declaration of 
'sqlite3rebaser_delete' follows non-static declaration
 SQLITE_API void sqlite3rebaser_delete(sqlite3_rebaser *p){
 ^
In file included from src/apsw.c:47:0:
D:\Source\apsw\sqlite3.c:11413:6: note: previous declaration of 
'sqlite3rebaser_delete' was here
 void sqlite3rebaser_delete(sqlite3_rebaser *p);
  ^
In file included from src/apsw.c:47:0:
D:\Source\apsw\sqlite3.c:185588:16: error: static declaration of 
'sqlite3rebaser_rebase' follows non-static declaration
 SQLITE_API int sqlite3rebaser_rebase(
^
In file included from src/apsw.c:47:0:
D:\Source\apsw\sqlite3.c:11399:5: note: previous declaration of 
'sqlite3rebaser_rebase' was here
 int sqlite3rebaser_rebase(
 ^
In file included from src/apsw.c:47:0:
D:\Source\apsw\sqlite3.c:185607:16: error: static declaration of 
'sqlite3rebaser_rebase_strm' follows non-static declaration
 SQLITE_API int sqlite3rebaser_rebase_strm(
^~
In file included from src/apsw.c:47:0:
D:\Source\apsw\sqlite3.c:11572:5: note: previous declaration of 
'sqlite3rebaser_rebase_strm' was here
 int sqlite3rebaser_rebase_strm(
 ^~
In file included from src/apsw.c:47:0:
D:\Source\apsw\sqlite3.c:185628:17: error: static declaration of 
'sqlite3rebaser_delete' follows non-static declaration
 SQLITE_API void sqlite3rebaser_delete(sqlite3_rebaser *p){
 ^
In file included from src/apsw.c:47:0:
D:\Source\apsw\sqlite3.c:11413:6: note: previous declaration of 
'sqlite3rebaser_delete' was here
 void sqlite3rebaser_delete(sqlite3_rebaser *p);
  ^
In file included from src/apsw.c:47:0:
D:\Source\apsw\sqlite3.c:185554:16: error: static declaration of 
'sqlite3rebaser_create' follows non-static declaration
 SQLITE_API int 

Re: [sqlite] The upcoming 3.23.0 release

[Stephen Chrzanowski]

If only all tech manuals were written like that, I'd be doing much MUCH
more entertaining things with my life.  Mind you, I love my job as is

On Fri, Mar 23, 2018 at 1:14 PM, Simon Slavin  wrote:

>
> You'll get mixed up, as you know
> You're not sure what you know
> So be sure when you test, test with care and great tact
> And remember that coding's a balancing act
> Work hard so your SQL is dextrous and deft
> And make sure that your comments and docs are the best
>
> All future SQLite documentation will be written by Dr Suess.
>
> 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] Is it possible to CREATE TABLE from other tables in a complex way?

[David Raymond]

This gets a little ugly. Was stuck for a while wondering what the heck was 
going on until I found out that the modulo operator can return negatives. Which 
makes absolutely no sense coming from someone who was a math major, but hey, 
now I know. It's also fun that %w "day of week" goes Sunday to Sunday, but %W 
"week of year" goes Monday to Monday (And then your school weeks go Friday to 
Friday)

Tables and fields have been renamed slightly from the previous emails. The 4 
tables I'm putting here are all static, so you can put them at the front of 
your CTE with values clauses if you really, really want to make it one big 
select statement from no tables.

CREATE TABLE SchoolYearDates
(
SchoolYear text not null primary key collate nocase,
StartDate text not null collate nocase
check (date(StartDate) is not null),
EndDate text not null collate nocase
check (date(EndDate) is not null)
);
insert into SchoolYearDates values ('2017', '2017-09-01', '2018-06-08');

CREATE TABLE DaysOfWeek
(
DayID integer not null primary key,
DayAbbrev text not null collate nocase,
DayName text not null collate nocase
);
insert into DaysOfWeek values
(0, 'Su', 'Sunday'),
(1, 'M', 'Monday'),
(2, 'Tu', 'Tuesday'),
(3, 'W', 'Wednesday'),
(4, 'Th', 'Thursday'),
(5, 'F', 'Friday'),
(6, 'Sa', 'Saturday');

CREATE TABLE LessonBlocks
(
WeekNumber integer not null primary key,
LessonBlock text not null collate nocase
);
with recursive foo (WeekNumber, LessonBlock) as (
values (1, '1-2')
union all
select
WeekNumber + 1,
cast(WeekNumber * 2 + 1 as text) || '-' || cast(WeekNumber * 2 + 2 as text)
from foo
where WeekNumber < 36)
insert into LessonBlocks select * from foo;

CREATE TABLE TimeTable
(
DayOfWeek text not null collate nocase,
Grade int not null,
Class text not null collate nocase
);

insert into TimeTable values
('M', 7, 'b'), ('M', 5, 'a'),
('Tu', 8, 'c'), ('Tu', 8, 'b'),
('W', 8, 'a'), ('W', 7, 'a'),
('Th', 6, 'a'), ('Th', 5, 'c'),
('F', 5, 'b'), ('F', 7, 'c');

--Now the fun part (Trying to limit to 80 chars per line will be weird

with recursive
Days (DayDate, DayOfWeek, WeekNumber) as (
  select
  StartDate,
  (select DayAbbrev from DaysOfWeek
where DayID = cast(strftime('%w', StartDate) as int)),
  1
  from SchoolYearDates
  where SchoolYear = '2017'
  
  union all
  
  select
  date(DayDate, '+1 day'),
  (select DayAbbrev from DaysOfWeek
where DayID = cast(strftime('%w', DayDate, '+1 day') as int)),
  (
(
  cast(strftime('%W', DayDate, '+1 day') as int)
  - cast(strftime('%W', (select StartDate from SchoolYearDates
 where SchoolYear = '2017')) as int)
  + ((cast(strftime('%w', DayDate, '+1 day') as int) + 6) % 7 >= 4)
) + 52
  ) % 52
  from Days
  where DayDate < (select EndDate from SchoolYearDates
   where SchoolYear = '2017')
)
select
Days.DayDate,
Days.DayOfWeek,
LessonBlocks.LessonBlock,
TimeTable.Grade,
TimeTable.Class
from
Days inner join LessonBlocks
on Days.WeekNumber = LessonBlocks.WeekNumber
inner join TimeTable
on Days.DayOfWeek = TimeTable.DayOfWeek
order by
DayDate, Grade, Class;


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of csanyipal
Sent: Friday, March 23, 2018 4:19 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] Is it possible to CREATE TABLE from other tables in a 
complex way?

csanyipal wrote
> csanyipal wrote
> 2017-09-01|F|1-2|5|b
> 2017-09-01|F|1-2|7|c
> 2017-09-04|M|1-2|7|b
> 2017-09-04|M|1-2|5|a
> 2017-09-05|Tu|1-2|8|c
> 2017-09-05|Tu|1-2|8|b
> 2017-09-06|W|1-2|8|a
> 2017-09-06|W|1-2|7|a
> 2017-09-07|Th|1-2|6|a
> 2017-09-07|Th|1-2|5|c
> 2017-09-08|F|3-4|5|b
> 2017-09-08|F|3-4|7|c
> 2017-09-11|M|3-4|7|b
> 2017-09-11|M|3-4|5|a
> 2017-09-12|Tu|3-4|8|c
> 2017-09-12|Tu|3-4|8|b
> 2017-09-13|W|3-4|8|a
> 2017-09-13|W|3-4|7|a
> 2017-09-14|Th|3-4|6|a
> 2017-09-14|Th|3-4|5|c
> 2017-09-15|F|5-6|5|b
> 2017-09-15|F|5-6|7|c
> 2017-09-18|M|5-6|7|b
> 2017-09-18|M|5-6|5|a
> 2017-09-19|Tu|5-6|8|c
> 2017-09-19|Tu|5-6|8|b
> 2017-09-20|W|5-6|8|a
> 2017-09-20|W|5-6|7|a
> 2017-09-21|Th|5-6|6|a
> 2017-09-21|Th|5-6|5|c
> 
> Can this output be achieved with a  SELECT
> http://www.sqlite.org/lang_select.html;   query?

I think I must to create one more CTE called SchoolWeeks and must modify the
LBs table like this:
LBs(SchoolWeek,lessonBlock) AS (
 VALUES (1,'1-2'), (2,'3-4')/*, (3,'5-6'), (4,'7-8'),(5,'9-10'),
(6,'11-12'),
(7,'13-14'), (8,'15-16'), (9,'17-18'), (10,'19-20'),
(11,'21-22'), (12,'23-24'), (13,'25-26'), (14,'27-28'),
(15,'29-30'),
(16,'31-32'), (17,'33-34'), (18,'35-36'), (19,'37-38'), (20,'39-40'),
(21,'41-42'), (22,'43-44'), (23,'45-46'), (24,'47-48'),
(25,'49-50'),
(26,'51-52'), (27,'53-54'), (28,'55-56'), (29,'57-58'), (30,'59-60'),
(31,'61-62'), (32,'63-64'), (33,'65-66'), (34,'67-68'),
(35,'69-70'),
(36,'71-72')  */
SWs(SchoolWeek) AS VALUES

Re: [sqlite] Is it possible to CREATE TABLE from other tables in a complex way?

[csanyipal]

csanyipal wrote
> csanyipal wrote
> 2017-09-01|F|1-2|5|b
> 2017-09-01|F|1-2|7|c
> 2017-09-04|M|1-2|7|b
> 2017-09-04|M|1-2|5|a
> 2017-09-05|Tu|1-2|8|c
> 2017-09-05|Tu|1-2|8|b
> 2017-09-06|W|1-2|8|a
> 2017-09-06|W|1-2|7|a
> 2017-09-07|Th|1-2|6|a
> 2017-09-07|Th|1-2|5|c
> 2017-09-08|F|3-4|5|b
> 2017-09-08|F|3-4|7|c
> 2017-09-11|M|3-4|7|b
> 2017-09-11|M|3-4|5|a
> 2017-09-12|Tu|3-4|8|c
> 2017-09-12|Tu|3-4|8|b
> 2017-09-13|W|3-4|8|a
> 2017-09-13|W|3-4|7|a
> 2017-09-14|Th|3-4|6|a
> 2017-09-14|Th|3-4|5|c
> 2017-09-15|F|5-6|5|b
> 2017-09-15|F|5-6|7|c
> 2017-09-18|M|5-6|7|b
> 2017-09-18|M|5-6|5|a
> 2017-09-19|Tu|5-6|8|c
> 2017-09-19|Tu|5-6|8|b
> 2017-09-20|W|5-6|8|a
> 2017-09-20|W|5-6|7|a
> 2017-09-21|Th|5-6|6|a
> 2017-09-21|Th|5-6|5|c
> 
> Can this output be achieved with a  SELECT
> http://www.sqlite.org/lang_select.html;   query?

I think I must to create one more CTE called SchoolWeeks and must modify the
LBs table like this:
LBs(SchoolWeek,lessonBlock) AS (
 VALUES (1,'1-2'), (2,'3-4')/*, (3,'5-6'), (4,'7-8'),(5,'9-10'),
(6,'11-12'),
(7,'13-14'), (8,'15-16'), (9,'17-18'), (10,'19-20'),
(11,'21-22'), (12,'23-24'), (13,'25-26'), (14,'27-28'),
(15,'29-30'),
(16,'31-32'), (17,'33-34'), (18,'35-36'), (19,'37-38'), (20,'39-40'),
(21,'41-42'), (22,'43-44'), (23,'45-46'), (24,'47-48'),
(25,'49-50'),
(26,'51-52'), (27,'53-54'), (28,'55-56'), (29,'57-58'), (30,'59-60'),
(31,'61-62'), (32,'63-64'), (33,'65-66'), (34,'67-68'),
(35,'69-70'),
(36,'71-72')  */
SWs(SchoolWeek) AS VALUES
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36)
so I can JOIN these in a proper way. Right?



-
Best, Pál
--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
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 CREATE TABLE from other tables in a complex way?

[csanyipal]

csanyipal wrote
> 2017-09-01|F|1-2|5|b
> 2017-09-01|F|1-2|7|c
> 2017-09-04|M|1-2|7|b
> 2017-09-04|M|1-2|5|a
> 2017-09-05|Tu|1-2|8|c
> 2017-09-05|Tu|1-2|8|b
> 2017-09-06|W|1-2|8|a
> 2017-09-06|W|1-2|7|a
> 2017-09-07|Th|1-2|6|a
> 2017-09-07|Th|1-2|5|c
> 2017-09-08|F|3-4|5|b
> 2017-09-08|F|3-4|7|c
> 2017-09-11|M|3-4|7|b
> 2017-09-11|M|3-4|5|a
> 2017-09-12|Tu|3-4|8|c
> 2017-09-12|Tu|3-4|8|b
> 2017-09-13|W|3-4|8|a
> 2017-09-13|W|3-4|7|a
> 2017-09-14|Th|3-4|6|a
> 2017-09-14|Th|3-4|5|c
> 2017-09-15|F|5-6|5|b
> 2017-09-15|F|5-6|7|c
> 2017-09-18|M|5-6|7|b
> 2017-09-18|M|5-6|5|a
> 2017-09-19|Tu|5-6|8|c
> 2017-09-19|Tu|5-6|8|b
> 2017-09-20|W|5-6|8|a
> 2017-09-20|W|5-6|7|a
> 2017-09-21|Th|5-6|6|a
> 2017-09-21|Th|5-6|5|c

Can this output be achieved with a  SELECT
   query?



-
Best, Pál
--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] The upcoming 3.23.0 release

[Simon Slavin]

On 23 Mar 2018, at 3:09pm, R Smith  wrote:

> select blue, one from foo where one is blue or blue is true;
>   -- blue |  one
>   --  | 
>   --   1  |   1
> 
> Is the blue one true? or the true one blue? or is this the one true blue in 
> the foo?

You'll get mixed up, as you know
You're not sure what you know
So be sure when you test, test with care and great tact
And remember that coding's a balancing act
Work hard so your SQL is dextrous and deft
And make sure that your comments and docs are the best

All future SQLite documentation will be written by Dr Suess.

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

Re: [sqlite] The upcoming 3.23.0 release

[R Smith]

On 2018/03/23 3:59 PM, David Raymond wrote:


sqlite> create table foo (true boolean);

sqlite> insert into foo (true) values (false);

sqlite> select * from foo;
true
0

sqlite> select 1 is true;
1 is true
1

sqlite> select 1 is true from foo;
1 is true
0

sqlite> select 1, true, 1 is true from foo;
1|true|1 is true
1|0|0


Too much thinking, brain hurts now.


Well brain-hurtz, yes, but it's just an exercise in linguistic semantics 
juxtaposed with query semantics. Or perhaps it is "techno-poetry".  (If 
the genre is new, I call naming-rights...)

You could as well have posed the query:

create table foo(blue, one);
insert into foo VALUES (1,true);
select blue, one from foo where one is blue or blue is true;
  -- blue |  one
  --  | 
  --   1  |   1

Is the blue one true? or the true one blue? or is this the one true blue 
in the foo?



We all remove unneeded complexity if it is better (and sometimes if it's 
not better), but introducing willful obfuscation is not a thing I've 
seen from too many programmers. I think we're safe from breaking 
hysterical historical queries, but agree with needing it well-documented.


Cheers,
Ryan

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

Re: [sqlite] The upcoming 3.23.0 release

[David Raymond]

"Moral:  Don't use identifiers "true" or "false" as column names."

Oh, I'm in full agreement there. But as you said: "developers who use SQLite 
tend to be very creative and wind up using SQLite in ways that we never thought 
to test." So I tried to think up some psychotic/pathological cases to see what 
it would do.

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

Re: [sqlite] The upcoming 3.23.0 release

[Richard Hipp]

On 3/23/18, David Raymond  wrote:
>
> sqlite> create table foo (true boolean);
>
> sqlite> insert into foo (true) values (false);
>

Then you get a non-empty set from:  SELECT * FROM foo WHERE true IS false;

Moral:  Don't use identifiers "true" or "false" as column names.
-- 
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] The upcoming 3.23.0 release

[David Raymond]

2. Recognize TRUE and FALSE as constants. (For compatibility, if there exist 
columns named "true" or "false", then the identifiers refer to the columns 
rather than Boolean constants.)
3. Support operators IS TRUE, IS FALSE, IS NOT TRUE, and IS NOT FALSE. 

On the documentation side of things I think
https://sqlite.org/draft/lang_expr.html
is gonna need a number of updates or additions to go over those a bit more. 
Including a reminder that [is true] is a new operator of two words which is a 
unary postfix operator, and that it isn't [is](the existing operator) 
[true](the new alias for 1)

sqlite> select 2 is true;
2 is true
1

sqlite> select 2 is 1;
2 is 1
0

sqlite> select true is 2;
true is 2
0

Hmm, my brain is sort of working, but not completely. Since [is true] is a 
unary postfix operator but we're respecting existing column names, how does 
"something is true" get parsed? Let's check... Looks like it prefers x [is] 
[true](column) over x [is true](postfix)

sqlite> create table foo (true boolean);

sqlite> insert into foo (true) values (false);

sqlite> select * from foo;
true
0

sqlite> select 1 is true;
1 is true
1

sqlite> select 1 is true from foo;
1 is true
0

sqlite> select 1, true, 1 is true from foo;
1|true|1 is true
1|0|0


Too much thinking, brain hurts now.


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Richard Hipp
Sent: Thursday, March 22, 2018 3:09 PM
To: General Discussion of SQLite Database; sqlite-dev
Subject: [sqlite] The upcoming 3.23.0 release

SQLite version 3.23.0 will probably be released soon, in early April.
For a summary of changes see
https://sqlite.org/draft/releaselog/3_23_0.html

Please download the latest Pre-release Snapshot
(https://sqlite.org/download.html) and test out the latest SQLite in
your applications.  Report any issues, either to this mailing list, or
directly to me at d...@sqlite.org.

All of our regression tests are passing with 100% branch coverage.
However, passing over 100 million test cases do not guarantee that all
is well, since developers who use SQLite tend to be very creative and
wind up using SQLite in ways that we never thought to test.  Hence,
your independent verification is helpful to us and much appreciated.

The release checklist for 3.23.0 is found at

https://www.sqlite.org/checklists/323/index

We have not yet frozen the code nor started the release checklist.
But we will do that soon. You can follow our progress on the
checklist.  The release will occur when the checklist goes all-green.

-- 
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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Re: The upcoming 3.23.0 release

[Hick Gunter]

Replace LEFT with CROSS in all cases that you used LEFT as a hint. Not at all 
complex, apart from you having to discern between "hint" and "necessities".

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Eric Grange
Gesendet: Freitag, 23. März 2018 09:07
An: SQLite mailing list 
Betreff: [EXTERNAL] Re: [sqlite] The upcoming 3.23.0 release

> Add the LEFT JOIN strength reduction optimization that converts a LEFT
JOIN into an ordinary JOIN

A question on this one: I have been using LEFT JOIN for many years (and not 
juste in SQLIte) in cases where a JOIN could have been used as a way to "hint" 
query optimizers which tables to scan first, typically in cases where the WHERE 
is too complex or misleading for the optimizer (ie.
whenever index statistics paint a wrong picture, for whatever specific reasons).
I can see that change "breaking" optimized queries back to their original 
performance.

Is there a planned "QUERY PLAN" or lightweight hinting is in the works?
The current suggestions in the doc are to use cross joins or stat3/stat4 
tables, but both are quite more complex to maintain, while just introducing a 
"LEFT" before a "JOIN" in a particular query used to be enough :)

On Fri, Mar 23, 2018 at 1:06 AM, Olivier Mascia  wrote:



___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] The upcoming 3.23.0 release

[Clemens Ladisch]

Eric Grange wrote:
>> Add the LEFT JOIN strength reduction optimization that converts a LEFT
>> JOIN into an ordinary JOIN
>
> A question on this one: I have been using LEFT JOIN for many years (and not
> juste in SQLIte) in cases where a JOIN could have been used as a way to
> "hint" query optimizers which tables to scan first.
>
> Is there a planned "QUERY PLAN" or lightweight hinting is in the works?

No, because it's already there:
http://www.sqlite.org/lang_select.html#crossjoin


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

Re: [sqlite] The upcoming 3.23.0 release

[Eric Grange]

> Add the LEFT JOIN strength reduction optimization that converts a LEFT
JOIN into an ordinary JOIN

A question on this one: I have been using LEFT JOIN for many years (and not
juste in SQLIte) in cases where a JOIN could have been used as a way to
"hint" query optimizers which tables to scan first, typically in cases
where the WHERE is too complex or misleading for the optimizer (ie.
whenever index statistics paint a wrong picture, for whatever specific
reasons).
I can see that change "breaking" optimized queries back to their original
performance.

Is there a planned "QUERY PLAN" or lightweight hinting is in the works?
The current suggestions in the doc are to use cross joins or stat3/stat4
tables, but both are quite more complex to maintain, while just introducing
a "LEFT" before a "JOIN" in a particular query used to be enough :)

On Fri, Mar 23, 2018 at 1:06 AM, Olivier Mascia  wrote:

> > Le 22 mars 2018 à 20:09, Richard Hipp  a écrit :
> >
> > ...
> > Please download the latest Pre-release Snapshot
> > (https://sqlite.org/download.html) and test out the latest SQLite in
> > your applications.  Report any issues, either to this mailing list, or
> > directly to me at d...@sqlite.org.
>
> I am of course not really much concerned by this detail — yet I'm writing
> an email about it :).  Since 3.22 and now with 3.23 snapshot as of today,
> latest updates of Visual Studio 2017 report this warning:
>
> "shell.c(2377): warning C4996: 'chmod': The POSIX name for this item is
> deprecated. Instead, use the ISO C and C++ conformant name: _chmod."
>
> It occurs at two places in shell.c, and the same thing occurs once for
> unlink (_unlink).
>
> In their latest versions of the documentation about their UCRT they say:
>
> > The C++ standard reserves names that begin with an underscore in the
> global namespace to the implementation. Because the POSIX functions are in
> the global namespace, but are not part of the standard C runtime library,
> the Microsoft-specific implementations of these functions have a leading
> underscore. For portability, the UCRT also supports the default names, but
> the Visual C++ compiler issues a deprecation warning when code that uses
> them is compiled. Only the default POSIX names are deprecated, not the
> functions. To suppress the warning, define _CRT_NONSTDC_NO_WARNINGS before
> including any headers in code that uses the original POSIX names.
>
> See: https://docs.microsoft.com/en-us/cpp/c-runtime-library/compatibility
>
> Adding a #define _CRT_NONSTDC_NO_WARNINGS to shell.c might indeed be a
> nice way to go.
>
> --
> Best Regards, Meilleures salutations, Met vriendelijke groeten,
> Olivier Mascia
>
>
> ___
> 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] [EXTERNAL] R*Trees query data cached?

[Hick Gunter]

As you already noted, it looks like the initial read of the data is taking a 
lot of time. Maybe your embedded OS has some way of measuring IO rates, then I 
guess you would see saturation of the IO channel.

As you have already done qhat you can to make file IO work faster, what remains 
is reducing the amount of work to do, i.e. change the query to read fewer 
records, if possible.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von David Ashman - Zone 7 Engineering, LLC
Gesendet: Freitag, 23. März 2018 00:25
An: SQLite mailing list 
Betreff: Re: [sqlite] [EXTERNAL] R*Trees query data cached?

Thanks Gunter.  Below are the execution times:
pragma table_value(pragma table_info(adas_link_geometry) 2mspragma 
table_value(pragma table_info(idx_adas_link_geometry) 1msSELECT  1.1 secondsSELECT  9msSELECT  9msSELECT 
 9msSELECT  9ms
It appears that the bulk of the time is taken up in the initial query.  
Presumably, there is buffering that occurs both in SQLite and my file system to 
assist in future queries.  I've changed some buffer parameters in my file 
system initialization and was able to reduce the initial query time from 1.6 
seconds to 1.1 seconds.  I've also asked Segger file system tech support for 
some help understanding the initial query time as it applies to file 
reads/buffering.  Using sqlite3_config() I've changed some SQLite page cache 
and heap parameters, and disabled a few unwanted #defines which has decreased 
initial query time.  Do the above times give you any clues about the initial 
query time?  Any other ideas would be helpful.
Thanks again.


  From: Hick Gunter 
 To: 'SQLite mailing list' 
 Sent: Wednesday, March 21, 2018 1:29 AM
 Subject: Re: [sqlite] [EXTERNAL] R*Trees query data cached?

The .describe is just to make sure the SQLite has loaded the table definition. 
Try pragma table_info();

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von David Ashman - Zone 7 Engineering, LLC
Gesendet: Dienstag, 20. März 2018 22:45
An: SQLite mailing list 
Betreff: Re: [sqlite] [EXTERNAL] R*Trees query data cached?

Thank you for the quick reply Hick.  I've implemented your script file in C 
since I'm running this application in the embedded world with no OS.  I don't 
see a .describe in the SQLite documentation.  I've tried to use .schema but 
that returns an error.  Do you have another suggestion to obtain the table 
information you had in mind?





  From: Hick Gunter 
 To: 'SQLite mailing list' 
 Sent: Tuesday, March 20, 2018 9:04 AM
 Subject: Re: [sqlite] [EXTERNAL] R*Trees query data cached?

SQLite does not have "query caching". It does have a "page cache" that will 
keep heavily used pages iin memory. There is also the possibility of a 
file-system/os-level cache. To break down the 1.6 seconds required for the 
first query, try executing an sql script. In linux this would be along the 
lines of:

> date; sqlite3 file.db < script.sql;date

With script.sql containing:

Select strftime('%Y-%m-%d %H:%M:%S.%f'); Select strftime('%Y-%m-%d 
%H:%M:%S.%f'); .describe adas_link_geometry Select strftime('%Y-%m-%d 
%H:%M:%S.%f'); .describe idx_adas_link_geometry Select strftime('%Y-%m-%d 
%H:%M:%S.%f'); Select ; Select strftime('%Y-%m-%d 
%H:%M:%S.%f'); Select ; Select strftime('%Y-%m-%d 
%H:%M:%S.%f'); Select ; Select strftime('%Y-%m-%d %H:%M:%S.%f');

This should give you an idea of where the time is being spent.


-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von David Ashman - Zone 7 Engineering, LLC
Gesendet: Dienstag, 20. März 2018 16:34
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] R*Trees query data cached?

Hello -
I have a question on SQLite query data buffering.
I'm successfully using SQLite v3.22.0 on an embedded ARM processor from ST with 
SD card.  The database file size is about 750MB.  The file system is Segger 
emFile FAT32.  I've configured SQLite to use 6MB RAM for heap.  I've done some 
query time benchmarking and found that the very first R*Trees query takes about 
1.6 seconds to complete.  Each successive R*Trees query (same query string with 
slightly different search parameters) takes about 11ms to complete.  Being new 
to SQLite and spatial queries, I'm trying to understand the substantial query 
time differences... does SQLite cache data from each query for future queries? 
The initial query:SELECT LINK_ID, FROM_REF_ELEVATION, TO_REF_ELEVATION FROM 
adas_link_geometry, idx_adas_link_geometry WHERE adas_link_geometry.ROWID = 
idx_adas_link_geometry.id AND minLat > 454760320 AND maxLat < 454800320 AND 
minLong > -1226807072 AND maxLong < 

Re: [sqlite] [EXTERNAL] Re: Bug? INSERT OR REPLACE not equivalent to DELETE followed by INSERT

[Hick Gunter]

There are no stored procedures in SQLite. But you could try creating a trigger 
program. Not sure if this works...

CREATE TRIGGER BEFORE UPDATE ON  WHEN NOT EXISTS SELECT 1 FROM  
WHERE  BEGIN INSERT INTO  ( VALUES (NEW. ...); 
END;

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Peter Michaux
Gesendet: Donnerstag, 22. März 2018 20:18
An: SQLite mailing list 
Betreff: [EXTERNAL] Re: [sqlite] Bug? INSERT OR REPLACE not equivalent to 
DELETE followed by INSERT

I think there are a couple main offenders with

> BEGIN;
> INSERT OR IGNORE ... ;
> UPDATE  ;
> COMMIT;

The first is that it is bulky. If this is in the application code then it has 
to be repeated for each desired UPSERT and it has to be repeated in the code of 
each application that uses the database.

The second is that it seems so inefficient in the case of a new row being 
inserted. The row is inserted and then immediately updated. Why do both 
operations when only one is needed?

Is it possible to write a stored procedure that checks a result of the INSERT 
OR IGNORE and only attempts the UPDATE if the row already existed?
That would at least move the bulky code out of the application and into the 
database. Also it seems it would be more efficient.

Thanks.

Peter


Peter



On Wed, Mar 21, 2018 at 6:15 PM, Kees Nuyt  wrote:

> On Wed, 21 Mar 2018 14:05:07 -0700, Peter Michaux
>  wrote:
>
> > You are right that my purpose does seem kind of confusing.
> >
> > What I really want is UPSERT to avoid doing an UPDATE, checking if
> > the number of rows affected is zero, then doing an INSERT. The lack
> > of UPSERT leads to a lot more application code using the UPDATE/INSERT 
> > combination.
> > UPSERT doesn't exist in SQLite so I was trying to work around that
> > by
> using
> > INSERT OR REPLACE which is not the same thing. I can see from
> > another recent thread that some others also think that UPSERT would
> > be a valuable addition to SQLite.
>
> I fail to see the problem in
> BEGIN;
> INSERT OR IGNORE ... ;
> UPDATE  ;
> COMMIT;
> Simple code, no need to test number of affected rows, and pretty fast
> because the relevant pages will be in cache.
>
> Or use an updatable view with an INSTEAD OF INSERT trigger.
>
> I did notice that attempts to define a proper UPSERT syntax opened a
> can of worms by itself because it (also) has to provide two colum
> lists, one for a full INSERT if the row with that PK doesn't exist,
> and another one for the columns to be updated when the row already
> exists. So, I don't see a big advantage in UPSERT.
>
> My humble two cents,
>
> --
> Regards,
> Kees Nuyt
> ___
> 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


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users