Re: [sqlite] Is it possible to dump a sqlite db that has an associated -wal file?

[Stefan Brüns]

On Sonntag, 16. Februar 2020 21:50:18 CET Simon Slavin wrote:
> On 16 Feb 2020, at 8:44pm, Stefan Brüns  
wrote:
> > On Sonntag, 16. Februar 2020 21:26:00 CET Simon Slavin wrote:
> >>> One use case I am aware of (although this targets places.sqlite, not
> >>> cookies.sqlite) is reading the history, bookmarks and tags.>> 
> >> These things can be done using the bookmarks API, WebExtensions API, and
> >> other methods.  Reading the SQLite database is actually more difficult.> 
> > AFAIK this only works while FF is running ...
> 
> That is the problem that started this thread: that the database file could
> not be opened while FF was running.

The database being inaccessible while FF is running does not equate to FF is 
always running. Both cases (with and without FF running) have to be covered.

And having to write two different access methods (direct access and through 
bookmarks API) is obviously the worst of all variants.

Regards,

Stefan

-- 
Stefan Brüns  /  Bergstraße 21  /  52062 Aachen
home: +49 241 53809034 mobile: +49 151 50412019

signature.asc
Description: This is a digitally signed message part.
___
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 dump a sqlite db that has an associated -wal file?

[Simon Slavin]

On 16 Feb 2020, at 8:44pm, Stefan Brüns  wrote:

> On Sonntag, 16. Februar 2020 21:26:00 CET Simon Slavin wrote:
> 
>>> One use case I am aware of (although this targets places.sqlite, not 
>>> cookies.sqlite) is reading the history, bookmarks and tags.
>> 
>> These things can be done using the bookmarks API, WebExtensions API, and 
>> other methods.  Reading the SQLite database is actually more difficult.
> 
> AFAIK this only works while FF is running ...

That is the problem that started this thread: that the database file could not 
be opened while FF was running.
___
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 dump a sqlite db that has an associated -wal file?

[Stefan Brüns]

On Sonntag, 16. Februar 2020 21:26:00 CET Simon Slavin wrote:

> > One use case I am aware of (although this targets places.sqlite, not 
> > cookies.sqlite) is reading the history, bookmarks and tags.
> These things can be done using the bookmarks API, WebExtensions API, and
> other methods.  Reading the SQLite database is actually more difficult.

AFAIK this only works while FF is running ...

Regards,

Stefan

-- 
Stefan Brüns  /  Bergstraße 21  /  52062 Aachen
home: +49 241 53809034 mobile: +49 151 50412019

signature.asc
Description: This is a digitally signed message part.
___
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 dump a sqlite db that has an associated -wal file?

[Keith Medcalf]

On Sunday, 16 February, 2020 10:25, Richard Hipp  wrote:

>> Why the database can not be read by another sqlite3 session when the
>> corresponding -wal file exists? Thanks.

>Because Firefox uses "PRAGMA locking_mode=EXCLUSIVE;"

Perhaps on some platforms, but Firefox 73.0.0 on Windows 10 18636.657 does not 
-- or if it does, then it doesn't work properly as I can still read/write the 
various database files while Firefox is running.

However, when I open a database and set locking_mode=exclusive then connection 
to the same database cannot access the database.

-- 
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 dump a sqlite db that has an associated -wal file?

[Simon Slavin]

On 16 Feb 2020, at 8:02pm, Stefan Brüns  wrote:

> Will this stop anyone from just copying the DB without the -wal file? 
> Afterwards, the DB can be read, as there is no longer any associated log.

The purpose of the locking is to prevent changes being made to the database 
during a browser session.  The problem does not occur if changes are made 
between sessions.  Having a third-party app read the database could be 
considered a vulnerability (a question for the Mozilla developer team, not me), 
but it is not related to this specific problem.

> One use case I am aware of (although this targets places.sqlite, not  
> cookies.sqlite) is reading the history, bookmarks and tags.

These things can be done using the bookmarks API, WebExtensions API, and other 
methods.  Reading the SQLite database is actually more difficult.
___
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 dump a sqlite db that has an associated -wal file?

[Stefan Brüns]

On Sonntag, 16. Februar 2020 18:36:15 CET Simon Slavin wrote:
> On 16 Feb 2020, at 5:15pm, Peng Yu  wrote:
> > Why the database can not be read by another sqlite3 session when the
> > corresponding -wal file exists? Thanks.
> 
> This is done on purpose by the developers of Firefox to prevent a security
> vulnerability which I will not describe in public.

Will this stop anyone from just copying the DB without the -wal file? 
Afterwards, the DB can be read, as there is no longer any associated log.

> One of the Mozilla developers involved in the decision reads this list.  If
> you have a good reason why you want to open the SQLite database while
> Firefox is running, you could post it here.  You might be able to persuade
> them to reconsider the decision.

One use case I am aware of (although this targets places.sqlite, not 
cookies.sqlite) is reading the history, bookmarks and tags.

Kind regards,

Stefan

-- 
Stefan Brüns  /  Bergstraße 21  /  52062 Aachen
home: +49 241 53809034 mobile: +49 151 50412019

signature.asc
Description: This is a digitally signed message part.
___
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 dump a sqlite db that has an associated -wal file?

[Simon Slavin]

On 16 Feb 2020, at 5:15pm, Peng Yu  wrote:

> Why the database can not be read by another sqlite3 session when the
> corresponding -wal file exists? Thanks.

This is done on purpose by the developers of Firefox to prevent a security 
vulnerability which I will not describe in public.

One of the Mozilla developers involved in the decision reads this list.  If you 
have a good reason why you want to open the SQLite database while Firefox is 
running, you could post it here.  You might be able to persuade them to 
reconsider the decision.
___
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 dump a sqlite db that has an associated -wal file?

[Richard Hipp]

On 2/16/20, Peng Yu  wrote:
>> Does it work when you close Firefox?  If it works when Firefox is closed
>> but
>> not when Firefox is open, then the answer is probably no.
>
> I can check the content when Firefox is closed (the -wal file
> disappears after Firefox is closed).
>
> Why the database can not be read by another sqlite3 session when the
> corresponding -wal file exists? Thanks.

Because Firefox uses "PRAGMA locking_mode=EXCLUSIVE;"

-- 
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] Is it possible to dump a sqlite db that has an associated -wal file?

[Peng Yu]

> Does it work when you close Firefox?  If it works when Firefox is closed but
> not when Firefox is open, then the answer is probably no.

I can check the content when Firefox is closed (the -wal file
disappears after Firefox is closed).

Why the database can not be read by another sqlite3 session when the
corresponding -wal file exists? Thanks.

-- 
Regards,
Peng
___
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 dump a sqlite db that has an associated -wal file?

[Keith Medcalf]

On Saturday, 15 February, 2020 19:27, Peng Yu  wrote:

>I am trying to see what tables are available in sqlite_master from
>firefox cookies sqlite3 fire.

>~/Library/Application
>Support/Firefox/Profiles/jaseom4q.default-1480119569722/cookies.sqlite

>But the error message says "Error: database is locked".

>I see a cookies.sqlite-wal file in the same directory.

>Is there a way to inspect this db file without closing firefox?

Does it work when you close Firefox?  If it works when Firefox is closed but 
not when Firefox is open, then the answer is probably no.  If you still get an 
error message when Firefox is closed, then you probably have too old a version 
of whatever version of SQLite3 you are using to look at the database.

I can, but then I am on Windows and using the latest mostest up-to-date version 
of the SQLite3 command line tool.  

There are rumours on the various message boards that Firefox opens the 
databases with exclusive access to prevent fiddling.  Not having looked at the 
relevant Firefox source code, I have no clue whether or not this is accurate.  
However the rumoured exclusive access seems to "go away" when an up-to-date 
version of SQLite3 is used to look at the databases.  Plus, of course, if the 
access is supposed to be exclusive then it is broken on Windows.

-- 
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] Is it possible to dump a sqlite db that has an associated -wal file?

[Peng Yu]

Hi,

I am trying to see what tables are available in sqlite_master from
firefox cookies sqlite3 fire.

~/Library/Application
Support/Firefox/Profiles/jaseom4q.default-1480119569722/cookies.sqlite

But the error message says "Error: database is locked".

I see a cookies.sqlite-wal file in the same directory.

Is there a way to inspect this db file without closing firefox?

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

Re: [sqlite] Endless loop possible with simultaneous SELECT and UPDATE?

[Keith Medcalf]

On Tuesday, 3 September, 2019 15:01, Kees Nuyt  wrote:

>On Tue, 3 Sep 2019 18:26:01 +0100, you wrote:

>>> // do SELECT on db1
>>> // do UPDATE on db2

>> Do you expect the SELECT to see the results of the previous
>> UPDATE ?  It won't, until the transaction has ended
>> (unless you arrange this explicitly).

>That's the nice thing about this construct:
>isolation between the SELECT and the UPDATE,
>the pattern is indeed:

>* Iterate over unchanged rows,
>  without seeing DELETEs, UPDATEs and INSERTs
>  on the tables in the select,

>* Be free to act upon the original rows
>  without disturbance of the read.

>I think it only works for WAL journal mode.

It will work just fine in non-WAL mode provided that the number of changed 
pages made by the updates does not exceed the size of the cache (that is, the 
changes on db2 do not have to spill the cache and thus obtain an exclusive 
lock, that it will not be able to obtain, prior to commit time).  You of course 
have to start an ordinary transaction on db1 and an immediate transaction on 
db2 before starting the query/change loop, and commit db1 before committing db2 
after the loop runs out of rows (so that you do not deadlock yourself when the 
commit on db2 attempts to upgrade to an exclusive lock).

In WAL journal mode you do not have to worry any of that at all since an open 
read will not prevent a write on a different connection from spilling the cache 
or committing.

You can do it all on one connection using only a single immediate transaction 
(whether in WAL journal mode or not) if you are sure the query in the outer 
loop cannot be using an index modified by the inner update/delete/insert 
operations.  One way to do this is to require the use of a sorter by that query 
so that all the results must be gathered before the first row is returned (such 
as by using the ORDER BY +colname implementation detail).

-- 
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] Endless loop possible with simultaneous SELECT and UPDATE?

[Kees Nuyt]

On Tue, 3 Sep 2019 18:26:01 +0100, you wrote:

>> // do SELECT on db1
>> // do UPDATE on db2
>
> Do you expect the SELECT to see the results of the previous
> UPDATE ?  It won't, until the transaction has ended
> (unless you arrange this explicitly).

That's the nice thing about this construct: 
isolation between the SELECT and the UPDATE, 
the pattern is indeed:

* Iterate over unchanged rows,
  without seeing DELETEs, UPDATEs and INSERTs 
  on the tables in the select, 

* Be free to act upon the original rows
  without disturbance of the read.

I think it only works for WAL journal mode.

-- 
Regards,
Kees Nuyt

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

Re: [sqlite] Endless loop possible with simultaneous SELECT and UPDATE?

[Simon Slavin]

On 3 Sep 2019, at 5:34pm, Alexander Vega  wrote:

> sqlite3_open("database1", );
> sqlite3_open("database1", );

Bear in mind that SQLite is not a server/client DBMS.  The database is not kept 
in memory (unless you arrange this explicitly).  All operations have to wait 
for the storage that holds the database.

So yes, you can open two connections.  But it's not going to magically double 
the speed of your program.

> // do SELECT on db1
> // do UPDATE on db2

Do you expect the SELECT to see the results of the previous UPDATE ?  It won't, 
until the transaction has ended (unless you arrange this explicitly).
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Endless loop possible with simultaneous SELECT and UPDATE?

[Alexander Vega]

So in the original code if I added a NOT INDEXED it would be valid? Also,
would an ORDER BY Auth_id ASC fix the issue, since I an not adding any new
rows the auth_ids would remain constant?

Wow I did not know that you could call open multiple times on the same
database! So the following is valid and safe (considering POSIX locking
etc.)
sqlite3_open("database1", );
sqlite3_open("database1", );
// loop start
// BEGIN TRANSACTION BOTH dbs
// do SELECT on db1
// do UPDATE on db2
// END TRANSACTION
// loop end
// close db1
// use db2 for rest of the program...

I have not seen this pattern within any sqlite3 code before so I think it
is not well known that this is a design pattern for searching and updating
at the same time.

If I were determined to stay within defined behavior AND to only use one
connection, would the following updated code attain that.

#define SELECT_EXPIRED_IDS \
"SELECT Auth_id FROM AuthTable WHERE Auth_id > ? AND expiration < ? " \
"ORDER BY Auth_id ASC LIMIT 128;"

#define UPDATE_SESID_EXPIRED \
"UPDATE AuthTable SET sesCookie=?, expiration=? WHERE Auth_id=?;"

static void
expire_sesid(void)
{
int auth_ids[128] = {0};
int i=0, j;
/* get raw current time */
current_time = get_current_db_time(false);

/* prepare SQL queries */
sqlite3_prepare_v2(db,   SELECT_EXPIRED_IDS,
-1, _info, NULL);
sqlite3_prepare_v2(db,  UPDATE_SESID_EXPIRED,
-1, _ses_expired, NULL);

do {
  sqlite3_bind_int(expire_info, 1, auth_ids[i]);
  sqlite3_bind_int(expire_info, 2, current_time);
  /* while there is work to be done */
  i=0;
  while (sqlite3_step(expire_info) == SQLITE_ROW) {
auth_ids[i++] = sqlite3_column_int(expire_info, 0);   /* auth_id */
  }
  sqlite3_reset(expire_info);

  for (j=0; j < i; j++) {
/* generate new session id */
generate_ses_id(ses_id);
/* update ses_id and internal expiration to a year ahead, log in
will set it to a month for user log in */
sqlite3_bind_text(update_ses_expired, 1, ses_id,
   16, SQLITE_STATIC);
sqlite3_bind_int64(update_ses_expired, 2, current_time_plus_year);
sqlite3_bind_int(update_ses_expired, 3, auth_ids[j]);
sqlite3_step(update_ses_expired);
sqlite3_reset(update_ses_expired);
  }
} while ( i == 128 );
/* all work has completed */
sqlite3_finalize(expire_info);
sqlite3_finalize(update_ses_expired);
return;
}




On Sun, Sep 1, 2019 at 3:47 PM Keith Medcalf  wrote:

>
> On Sunday, 1 September, 2019 11:12, Alexander Vega 
> wrote:
>
> >Thank you Keith for your answer. It has led me to more questions.
>
> >"though you may or may not have visited all rows"
> >From the documentation I did not get the impression that you would
> >ever not visit ALL ROWS at least once. Is there a technical reason
> >for this? I would assume a full table scan is walking the un-ordered
> >leaf pages of the B*tree?
>
> How do you know that you are doing a table scan?  This certainly cannot be
> assumed.  Perhaps the AuthTable has 57 columns with a total length of
> several hundred bytes per row but there also happens to be an index on a
> subset of the columns that includes the two columns that you have asked
> for.  Perhaps you are "table scanning" that covering index instead (because
> it is cheaper than reading the actual table)?  There are ways to insist on
> a table scan (select ... from table NOT INDEXED ...) for example.  However,
> you left it up to the database engine to choose the most cost effective way
> to answer your select (which is how SQL works ... it is a declarative
> language ... you declare what you want and the database figures out the
> best way to go about giving you what you asked for).
>
> As a result of updating the first such row thus received the index has now
> changed such that the row you are operating on became the last row in the
> index being scanned.  Therefore there is no "next" row.  You will have
> visited only one row, even though there might have been millions of rows in
> the table.
>
> >"Your outer query should probably be "select auth_id, expiration from
> >AuthTable where expiration <= ? order by +auth_id, +expiration" and
> >binding current_time as the parameter since there is no point in
> >retrieving rows that you will not be updating is there?  "
>
> >You are correct that does make sense. I guess I was trying avoid any
> >ambiguities of a WHERE clause on the SELECT because I do not
> >understand its behavior in this circumstance.
>
> If you cannot understand the behaviour with a WHERE clause, then what
> would make you think that one without a WHERE clause would be any more
> transparent, especially given that all Relational Databases are designed to
> provide you the results you asked for as efficiently as possible?  Perhaps
> in a few days you will discover that you need to create another index for
> some other purpose, and that causes SQLite3 to obtain what you said you
> wanted in an entirely different manner.  When you make any change to the
> database do you re-evaluate the implementation 

Re: [sqlite] Endless loop possible with simultaneous SELECT and UPDATE?

[Keith Medcalf]

On Sunday, 1 September, 2019 11:12, Alexander Vega  wrote:

>Thank you Keith for your answer. It has led me to more questions.

>"though you may or may not have visited all rows"
>From the documentation I did not get the impression that you would
>ever not visit ALL ROWS at least once. Is there a technical reason 
>for this? I would assume a full table scan is walking the un-ordered 
>leaf pages of the B*tree?

How do you know that you are doing a table scan?  This certainly cannot be 
assumed.  Perhaps the AuthTable has 57 columns with a total length of several 
hundred bytes per row but there also happens to be an index on a subset of the 
columns that includes the two columns that you have asked for.  Perhaps you are 
"table scanning" that covering index instead (because it is cheaper than 
reading the actual table)?  There are ways to insist on a table scan (select 
... from table NOT INDEXED ...) for example.  However, you left it up to the 
database engine to choose the most cost effective way to answer your select 
(which is how SQL works ... it is a declarative language ... you declare what 
you want and the database figures out the best way to go about giving you what 
you asked for).

As a result of updating the first such row thus received the index has now 
changed such that the row you are operating on became the last row in the index 
being scanned.  Therefore there is no "next" row.  You will have visited only 
one row, even though there might have been millions of rows in the table.

>"Your outer query should probably be "select auth_id, expiration from
>AuthTable where expiration <= ? order by +auth_id, +expiration" and
>binding current_time as the parameter since there is no point in 
>retrieving rows that you will not be updating is there?  "

>You are correct that does make sense. I guess I was trying avoid any
>ambiguities of a WHERE clause on the SELECT because I do not
>understand its behavior in this circumstance.

If you cannot understand the behaviour with a WHERE clause, then what would 
make you think that one without a WHERE clause would be any more transparent, 
especially given that all Relational Databases are designed to provide you the 
results you asked for as efficiently as possible?  Perhaps in a few days you 
will discover that you need to create another index for some other purpose, and 
that causes SQLite3 to obtain what you said you wanted in an entirely different 
manner.  When you make any change to the database do you re-evaluate the 
implementation details of every previously written SQL statement to see if it 
still compatible with the details you depended on?  What about it you update 
the version of SQLite3?  You should not be dependent on the peculiarities of 
the implementation since they might change at any time.

>You mentioned two database connections to the same database. Is this
>going to work if I am using Threadsafe mode = 0? 

Yes.  Threadsafe mode only affects programs having multiple threads making 
calls into the sqlite3 library.  These are independent variables (that is you 
can have X threads and Y connections, and X is independent of Y) just because 
you have 47 connections does not mean that you have more than 1 thread, nor 
does having 47 threads mean that you have more than 1 connection.  Threads are 
commenced with _beginthread (or equivalent for the OS) calls and connections 
are commenced with sqlite3_open* calls.  The _beginthread operations result in 
the creation of a thread and the sqlite3_open* calls create a database 
connection -- they are not related to each other in any way.  Also consider 
that it is entirely possible for a program to have hundreds of threads yet 
still only be single-threaded as far as sqlite3 is concerned if only one of 
those threads makes use of the sqlite3 library, and that one thread may use 
hundreds of database connections either serially or in parallel or in some 
combination thereof.

>Would the second connection be done through an attach?

No.  The attach statement attaches a database to a connection.  You have to 
have opened the connection first.  Connections are created with the 
sqlite3_open* functions which return a pointer to a database connection.

>Does this conversation change if I wrap the whole select and updates
>in one transaction? e.g. BEGIN...END

No, because isolation is only BETWEEN connections, not WITHIN connections.  And 
the transaction state is per connection.

-- 
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] Endless loop possible with simultaneous SELECT and UPDATE?

[Alexander Vega]

Thank you Keith for your answer. It has led me to more questions.

"though you may or may not have visited all rows"
From the documentation I did not get the impression that you would ever not
visit ALL ROWS at least once. Is there a technical reason for this? I would
assume a full table scan is walking the un-ordered leaf pages of the B*
tree?

"Your outer query should probably be "select auth_id, expiration from
AuthTable where expiration <= ? order by +auth_id, +expiration" and binding
current_time as the parameter since there is no point in retrieving rows
that you will not be updating is there?  "
You are correct that does make sense. I guess I was trying avoid any
ambiguities of a WHERE clause on the SELECT because I do not understand its
behavior in this circumstance.

You mentioned two database connections to the same database. Is this going
to work if I am using Threadsafe mode = 0? Would the second connection be
done through an attach?

Does this conversation change if I wrap the whole select and updates in one
transaction? e.g. BEGIN...END

Thanks



On Sun, Sep 1, 2019 at 1:32 AM Keith Medcalf  wrote:

>
> > Having read :  https://www.sqlite.org/isolation.html
> > Specifically the line "And the application can UPDATE the current row
> > or any prior row, though doing so might cause that row to reappear in a
> > subsequent sqlite3_step()."
>
> > Is it possible to create and endless loop
>
> Eventually you will have no more rows to update and therefore the
> underlying structures become stable and the select loop will eventually run
> out of rows, though you may or may not have visited all rows, and may visit
> some rows two or more times (once before update and more than once after).
>
> If you change the outer query to "select auth_id, expiration from
> AuthTable order by +auth_id, +expiration" then you will PROBABLY never have
> a problem since the results will LIKELY be from a sorter and not from the
> underlying table, and therefore mutation of the underlying tables and
> indexes will not interfere with the result of the outer select, even if
> those mutations affect the AuthTable or the indexes on it.  Some SQL
> varients use a FOR UPDATE clause on a SELECT to tell the query planner that
> you intend to dally-about with the underlying datastore without having the
> proper isolation in place.  The SQLite way of doing this is by requesting a
> row sorter not dependent on indexes by using the +columnname syntax in an
> order by on the select.
>
> Your outer query should probably be "select auth_id, expiration from
> AuthTable where expiration <= ? order by +auth_id, +expiration" and binding
> current_time as the parameter since there is no point in retrieving rows
> that you will not be updating is there?
>
> The correct solution is, of course, to use separate connections so that
> you have isolation between the select and the updates.
>
> You SHOULD be executing the outer select on one connection and the updates
> on another connection.  This will work for journal mode delete unless the
> number of changed pages is too large to fit in sqlite's cache, in which
> case you may get an error from the update statement when it needs to spill
> the cache, and you will need to kaibosh the whole thing and do the updates
> in smaller chunks by putting a limit on the outer select and looping the
> whole thing until there are no more rows to process.  (or increase the
> cache_size to be sufficient).
>
> You can avoid that particular problem by having the database in
> journal_mode=WAL in which case you can even process each update in its own
> transaction if you wish (get rid of the db2.beginimmediate() and
> db2.commit(), though then you will have to handle the eventuality of
> getting errors on the UPDATE).
>
> db1 = Connection('database.db')
> db1.executescript('pragma journal_mode=WAL;')
> db2 = Connection('database.db')
> current_time = datetime.now()
> current_time_plus_one_year = current_time.add(years=1)
> sess_id = ... some constant ...
> db2.beginimmediate()
> for row in db1.execute('select auth_id, expiration from authtable where
> expiration <= ?;',
>(current_time,)):
> db2.execute('update authtable set sesCookie = ?, expiration = ? where
> auth_id = ?;',
> (generate_ses_id(sess_id), current_time_plus_one_year,
> row.auth_id,))
> db2.commit()
>
> --
> 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] Endless loop possible with simultaneous SELECT and UPDATE?

[Keith Medcalf]

> Having read :  https://www.sqlite.org/isolation.html
> Specifically the line "And the application can UPDATE the current row
> or any prior row, though doing so might cause that row to reappear in a
> subsequent sqlite3_step()."

> Is it possible to create and endless loop

Eventually you will have no more rows to update and therefore the underlying 
structures become stable and the select loop will eventually run out of rows, 
though you may or may not have visited all rows, and may visit some rows two or 
more times (once before update and more than once after).

If you change the outer query to "select auth_id, expiration from AuthTable 
order by +auth_id, +expiration" then you will PROBABLY never have a problem 
since the results will LIKELY be from a sorter and not from the underlying 
table, and therefore mutation of the underlying tables and indexes will not 
interfere with the result of the outer select, even if those mutations affect 
the AuthTable or the indexes on it.  Some SQL varients use a FOR UPDATE clause 
on a SELECT to tell the query planner that you intend to dally-about with the 
underlying datastore without having the proper isolation in place.  The SQLite 
way of doing this is by requesting a row sorter not dependent on indexes by 
using the +columnname syntax in an order by on the select.

Your outer query should probably be "select auth_id, expiration from AuthTable 
where expiration <= ? order by +auth_id, +expiration" and binding current_time 
as the parameter since there is no point in retrieving rows that you will not 
be updating is there?

The correct solution is, of course, to use separate connections so that you 
have isolation between the select and the updates.  

You SHOULD be executing the outer select on one connection and the updates on 
another connection.  This will work for journal mode delete unless the number 
of changed pages is too large to fit in sqlite's cache, in which case you may 
get an error from the update statement when it needs to spill the cache, and 
you will need to kaibosh the whole thing and do the updates in smaller chunks 
by putting a limit on the outer select and looping the whole thing until there 
are no more rows to process.  (or increase the cache_size to be sufficient).

You can avoid that particular problem by having the database in 
journal_mode=WAL in which case you can even process each update in its own 
transaction if you wish (get rid of the db2.beginimmediate() and db2.commit(), 
though then you will have to handle the eventuality of getting errors on the 
UPDATE).

db1 = Connection('database.db')
db1.executescript('pragma journal_mode=WAL;')
db2 = Connection('database.db')
current_time = datetime.now()
current_time_plus_one_year = current_time.add(years=1)
sess_id = ... some constant ...
db2.beginimmediate()
for row in db1.execute('select auth_id, expiration from authtable where 
expiration <= ?;', 
   (current_time,)):
db2.execute('update authtable set sesCookie = ?, expiration = ? where 
auth_id = ?;', 
(generate_ses_id(sess_id), current_time_plus_one_year, 
row.auth_id,))
db2.commit()

-- 
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] Endless loop possible with simultaneous SELECT and UPDATE?

[Alexander Vega]

 Having read :  https://www.sqlite.org/isolation.html
Specifically the line "And the application can UPDATE the current row or
any prior row, though doing so might cause that row to reappear in a
subsequent sqlite3_step()."

Is it possible to create and endless loop with the following (pseudo)code?

#define SELECT_EXPIRE_INFO \
"SELECT Auth_id, expiration FROM AuthTable;"

#define UPDATE_SESID_EXPIRED \
"UPDATE AuthTable SET sesCookie=?, expiration=? WHERE Auth_id=?;"

static void
expire_sesid(void)
{

/* get raw current time */
current_time = get_current_db_time(false);

/* prepare SQL queries */
sqlite3_prepare_v2(db,  SELECT_EXPIRE_INFO,
-1, _info, NULL);
sqlite3_prepare_v2(db,  UPDATE_SESID_EXPIRED,
-1, _ses_expired, NULL);

/* while there is work to be done */
while (sqlite3_step(expire_info) == SQLITE_ROW) {
auth_id = sqlite3_column_int(expire_info, 0);   /* auth_id */
expiration_time = sqlite3_column_int64(expire_info, 1);   /* expiration
*/

/* if the session is expired, today is greater than expiration date */
if ( current_time >  expiration_time ) {
  /* generate new invalid session id */
  generate_ses_id(ses_id);
  /* invalidate ses_id and set internal expiration to a year ahead, log
in
  will set it to a month for user log in */
  sqlite3_bind_text(update_ses_expired, 1, ses_id,
 16, SQLITE_STATIC);
  sqlite3_bind_int64(update_ses_expired, 2, current_time_plus_year);
  sqlite3_bind_int(update_ses_expired, 3, auth_id);
  sqlite3_step(update_ses_expired);
  sqlite3_reset(update_ses_expired);
  }
  }
/* all work has completed */
sqlite3_finalize(expire_info);
sqlite3_finalize(update_ses_expired);
return;
}

I appreciate everyone's time,
-Alex V
___
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?

[Jake Thaw]

Hi Shane,

You might be interested in trying out my pivot virtual table implementation.

https://github.com/jakethaw/pivot_vtab

This will cater for changing values, but like a VIEW implementation,
it does not satisfy your criteria of dynamically changing
rows/columns. Changes to rows/columns can only be propagated by
dropping and re-creating the virtual table.

I have provided an example below using your data structure, however it
works nicer with normalized data.

e.g.

.load ./pivot_vtab
.headers on
.mode column

CREATE TABLE t(
  "Product/Region",
  Belgium,
  France,
  USA
);
INSERT INTO t VALUES
('Oil_filter', 1, 2, 3),
('Spark_plug', 4, 5, 6),
('Coolent', 7, 8, 9);

CREATE VIRTUAL TABLE pivot USING pivot_vtab(
 (SELECT name "Product/Region" -- pivot table key
FROM pragma_table_info('t')
   WHERE name <> 'Product/Region'),

 (SELECT "Product/Region", -- pivot column key - can be referenced in
pivot query as ?2,
 "Product/Region"  -- pivot column name
FROM t),

 (SELECT CASE ?1
   WHEN 'Belgium' THEN Belgium
   WHEN 'France' THEN France
   WHEN 'USA' THEN USA
 END
FROM t
   WHERE "Product/Region" = ?2)
);

SELECT *
  FROM pivot;

Product/Region  Oil_filter  Spark_plug  Coolent
--  --  --  --
Belgium 1   4   7
France  2   5   8
USA 3   6   9

-Jake

On Mon, Apr 1, 2019 at 7:07 AM 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] Is it possible to transpose a table using SQL?

[Simon Slavin]

On 1 Apr 2019, at 10:18am, Dominique Devienne  wrote:

> I can't seem to find that one, but I found another here:
> https://metacpan.org/pod/SQLite::VirtualTable::Pivot

I looked at that one, but it doesn't do what OP wants, which is to swap rows 
and columns without the programmer having to specify anything.

It would be possible to implement the transform pivot as a virtual table in any 
language.  But you do have to do the work: the feature doesn't come with 
SQLite.  And if you're writing code you might as well do it in your program.

Simon.
___
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?

[Dominique Devienne]

On Mon, Apr 1, 2019 at 7:15 AM Shane Dev  wrote:

> [...]. 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
>

It's possible using a virtual table, which years ago a colleague used in
our app.
I can't seem to find that one, but I found another here:
https://metacpan.org/pod/SQLite::VirtualTable::Pivot

I don't see anything related to pivot/transpose in either usual locations
below:
https://www.sqlite.org/src/dir?ci=6cf8b18ec20f11c2=ext/misc
https://www.sqlite.org/contrib
___
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?

[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] Is it possible to transpose a table using SQL?

[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?

[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] Is it possible to transpose a table using SQL?

[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?

[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

Re: [sqlite] is this possible

[Simon Slavin]

On 29 Mar 2019, at 1:55pm, Dan Kennedy  wrote:

> The error doesn't occur unless you actually query the view though, correct?

My theory on SQLite was that the VIEW definition was only syntax-checked and 
not compiled in any way.  So you could do

CREATE VIEW v as select * from t ORDER BY a;
CREATE TABLE t (a INTEGER UNIQUE);
... use view v a few times ...
DROP TABLE t;
CREATE TABLE t (a TEXT, b REAL);
... use view v a few more times ...

changing what the view meant, whether a temporary index was needed, and how 
many columns would be returned, without having to recreate the view.

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

Re: [sqlite] is this possible

[Igor Tandetnik]

On 3/29/2019 9:55 AM, Dan Kennedy wrote:


On 29/3/62 03:00, Igor Tandetnik wrote:

On 3/28/2019 3:21 PM, Mark Wagner wrote:

Imagine I have these two tables and one view defining a join.

CREATE TABLE t (foo);
CREATE TABLE s (bar);
CREATE VIEW v as select * from t join s on (foo = q);


Surprisingly, this last statement succeeds. But if you then close the database and try to 
open it again, it'll fail with "no such column: q". So, don't do this - you are 
creating an unusable database file with corrupted schema.



The error doesn't occur unless you actually query the view though, correct?


Yes, I think the tool I was using tries to use the view right after opening the 
database, probably to figure out what columns it offers. This led me to 
erroneously conclude that merely opening the database triggers the error.
--
Igor Tandetnik


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

Re: [sqlite] is this possible

[Dan Kennedy]

On 29/3/62 03:00, Igor Tandetnik wrote:

On 3/28/2019 3:21 PM, Mark Wagner wrote:

Imagine I have these two tables and one view defining a join.

CREATE TABLE t (foo);
CREATE TABLE s (bar);
CREATE VIEW v as select * from t join s on (foo = q);


Surprisingly, this last statement succeeds. But if you then close the 
database and try to open it again, it'll fail with "no such column: 
q". So, don't do this - you are creating an unusable database file 
with corrupted schema.



The error doesn't occur unless you actually query the view though, correct?

Here, I get:

  $ ./sqlite3 x.db
  SQLite version 3.27.2 2019-02-25 16:06:06
  Enter ".help" for usage hints.
  sqlite> CREATE TABLE t (foo);
  sqlite> CREATE TABLE s (bar);
  sqlite> CREATE VIEW v as select * from t join s on (foo = q);
  sqlite>
  $ ./sqlite3 x.db
  SQLite version 3.27.2 2019-02-25 16:06:06
  Enter ".help" for usage hints.
  sqlite> SELECT * FROM t;
  sqlite> SELECT * FROM s;
  sqlite> SELECT * FROM v;
  Error: no such column: q


Dan.



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

Re: [sqlite] is this possible

[Keith Medcalf]

On Thursday, 28 March, 2019 13:21, Mark Wagner  wrote:

>Imagine I have these two tables and one view defining a join.

>CREATE TABLE t (foo);
>CREATE TABLE s (bar);
>CREATE VIEW v as select * from t join s on (foo = q);

>I appear to be able to do this query:

>select 20 as q, * from t join s on (foo=q);

Really, this is:

select 20 as q, *
  from t, s
 where foo == q;

q is an alias for the constant 20.  So what you are really saying is this:

select 20 as q, *
  from t, s
 where foo == 20;

which is valid.

>But apparently I cannot do this:

>sqlite> select 20 as q, * from v;
>Error: no such column: q

No, because q is not a column in either t or s.

>It's interesting because it allows me to define the view and at that
>point it knows nothing about q so I would have assumed it could be
>"supplied" later.

Defining a View is nothing more than storing the definition of the view in a 
table.  It is not parsed until you use it (though it is syntax checked so if 
you make a syntax error you will be told about then when you attempt to create 
the view).  You can define the view before defining the tables s and t, or 
after, or betwixt defining them.  You can even drop the tables (either or both) 
and recreate them (or not).  However, at the time you want to EXECUTE (use) the 
view v the tables s and t must exist (or you will get a no such table error), 
and the columns foo and q must be defined in one of those tables (or you get a 
no such column error).

>Is this just how it is or perhaps my syntax is off?  Or maybe I'm
>just confused.

---
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 this possible

[Igor Tandetnik]

On 3/28/2019 3:21 PM, Mark Wagner wrote:

Imagine I have these two tables and one view defining a join.

CREATE TABLE t (foo);
CREATE TABLE s (bar);
CREATE VIEW v as select * from t join s on (foo = q);


Surprisingly, this last statement succeeds. But if you then close the database and try to 
open it again, it'll fail with "no such column: q". So, don't do this - you are 
creating an unusable database file with corrupted schema.

I don't believe you can create a parameterized view.
--
Igor Tandetnik

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

Re: [sqlite] is this possible

[Amit Yaron]

From the page https://sqlite.org/lang_createview.html :
"The CREATE VIEW command assigns a name to a pre-packaged SELECT 
statement ..."


So, it seems that the command "CREATE VIEW"  just creates a name for a 
SELECT statement, and checks nothing more than syntax.


On 28.3.2019 21:21, Mark Wagner wrote:

CREATE VIEW v as select * from t join s on (foo = q);



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

[sqlite] is this possible

[Mark Wagner]

Imagine I have these two tables and one view defining a join.

CREATE TABLE t (foo);
CREATE TABLE s (bar);
CREATE VIEW v as select * from t join s on (foo = q);

I appear to be able to do this query:

select 20 as q, * from t join s on (foo=q);

But apparently I cannot do this:

sqlite> select 20 as q, * from v;
Error: no such column: q

It's interesting because it allows me to define the view and at that point
it knows nothing about q so I would have assumed it could be "supplied"
later.

Is this just how it is or perhaps my syntax is off?  Or maybe I'm just
confused.

Just curious.

Thanks!

-- Mark
___
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 use sqlite online-backup system in an ongoing manner?

[Barry Smith]

I think the sessions extension can be used for what you want to do. I haven't 
used it myself, but from it's documented behaviour it looks like you could 
record change sets for every three second interval then apply them back to your 
database on disk. If your app is multi-threaded it might be a pain (unless 
there's an atomic 'stop this changeset and immediately start another one'

> On 13 Mar 2019, at 8:40 am, Simon Slavin  wrote:
> 
>> On 13 Mar 2019, at 2:31pm, John Smith  wrote:
>> 
>> I am working with IN-MEMORY database.
>> When my program starts I load data from file-system DB into my IN-MEMORY DB.
>> All other SQL operations are performed directly on my IN-MEMORY database.
>> This is in order to keep performance high.
> 
> First, make sure you really need to do this.  SQLite performance is normally 
> very high, even without taking special measures.  You may be wasting 
> programming time and introducing complexity which will be difficult to debug. 
>  Run some time-trials.  Of course, you may have already run some time-trials.
> 
>>// Save only intermediate changes (?)
>>sqlite3_backup_step(p, -1); // Backup all modifications from last time
> 
> You cannot combine these two things.  The Online Backup API backs up an 
> entire database.  It does it page by page, without understanding individual 
> rows of data.  It cannot select only changes.  So you might want to use it, 
> but if you do you'll create a new copy of the entire database every time.
> 
> You might want to instead use the Resumable Bulk Update extension:
> 
> 
> 
> " An RBU Update is a bulk update of a database file that may include many 
> insert, update and delete operations on one or more tables. "
> 
> 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 use sqlite online-backup system in an ongoing manner?

[Simon Slavin]

On 13 Mar 2019, at 2:31pm, John Smith  wrote:

> I am working with IN-MEMORY database.
> When my program starts I load data from file-system DB into my IN-MEMORY DB.
> All other SQL operations are performed directly on my IN-MEMORY database.
> This is in order to keep performance high.

First, make sure you really need to do this.  SQLite performance is normally 
very high, even without taking special measures.  You may be wasting 
programming time and introducing complexity which will be difficult to debug.  
Run some time-trials.  Of course, you may have already run some time-trials.

> // Save only intermediate changes (?)
> sqlite3_backup_step(p, -1); // Backup all modifications from last time

You cannot combine these two things.  The Online Backup API backs up an entire 
database.  It does it page by page, without understanding individual rows of 
data.  It cannot select only changes.  So you might want to use it, but if you 
do you'll create a new copy of the entire database every time.

You might want to instead use the Resumable Bulk Update extension:



" An RBU Update is a bulk update of a database file that may include many 
insert, update and delete operations on one or more tables. "

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 use sqlite online-backup system in an ongoing manner?

[John Smith]

I am working with IN-MEMORY database.
When my program starts I load data from file-system DB into my IN-MEMORY DB.
All other SQL operations are performed directly on my IN-MEMORY database.
This is in order to keep performance high.

However, I have a requirement that my original file-system database will remain 
updated with the program modifications every few seconds.
My idea to implement this was to have a worker-thread that will work as follows:

void WorkerThread()
{
// Initialize SQLite online-backup ONCE:
p = sqlite3_backup_init(...);

loop{
Sleep(5 seconds);

 // Save only intermediate changes (?)
 sqlite3_backup_step(p, -1); // Backup all modifications from last time

}  while( program is running);

// No program is exiting...
// Release object resources
sqlite3_backup_finish(p);
}

The problem is that I see that first time around all data is saved, but all 
follwing calls to 'sqlite3_backup_step()' do not save anything.

My question:
Is there a way to use this online-backup system in an incremental way: that it 
will save only difference from last time BUT ALL the difference from last time?

Many thanks, John
___
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 concatenate an arbitrary number of columns into one?

[Michael Falconer]

As Simon points out there is no SQL solution to your issue. Some sort of
external utility processing with things like awk, sed or even cut may
assist or for a quick and dirty method you could set the sqlite3 command
line utility .separator value to a blank string which may (or may not)
provide a temporary method. Not in raw SQL though.

On Wed, 21 Nov 2018 at 11:32, Simon Slavin  wrote:

> On 20 Nov 2018, at 11:54pm, Shane Dev  wrote:
>
> > Is there an SQL statement to concatenate all columns into a single
> column without explicitly naming them?
>
> No.  And I can't think of any short-cut way to do what you want.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
Regards,
 Michael.j.Falconer.
___
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 concatenate an arbitrary number of columns into one?

[Simon Slavin]

On 20 Nov 2018, at 11:54pm, Shane Dev  wrote:

> Is there an SQL statement to concatenate all columns into a single column 
> without explicitly naming them?

No.  And I can't think of any short-cut way to do what you want.

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 concatenate an arbitrary number of columns into one?

[Shane Dev]

Hello,

An asterisk in the result-column represents all columns from the FROM
clause without explicitly naming them,
https://www.sqlite.org/syntax/result-column.html Is there an SQL statement
to concatenate all columns into a single column without explicitly naming
them? If it existed, I could imagine something like - select concat(*) from
table/view/query/etc
___
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?

[Csányi Pál]

2018-03-23 21:52 GMT+01:00 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.

Thank you very much.
I was already thinking of using static tables.

I modify your code a little: we call school years like this:
'2016/2017', '2017/2018' and so on.
Then I run it and then I realized there were breaks and holidays in a
school year.
Those days of holidays and breaks should be excluded from the result records.
Moreover, there are teaching Saturdays too, when on a Saturday we must
to take Lessons. Eg. on 2018-04-14 we must go to school and take
Lessons according to the Friday timetable. These teaching Saturdays
should be added to the result with UNION clause.

So I created more tables and now I have the Lessons.db with these clauses:
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
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/2018','2017-09-01','2018-06-14');
CREATE TABLE TeachingSaturdays
(
SaturdayDate text NOT NULL COLLATE nocase
CHECK (date(SaturdayDate) IS NOT NULL ),
TimeTableDay text NOT NULL COLLATE nocase
);
INSERT INTO TeachingSaturdays VALUES ('2018-04-14','F');
INSERT INTO TeachingSaturdays VALUES ('2018-05-05','M');
CREATE TABLE SchoolVacations
(
VacationName text NOT NULL 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 SchoolVacations VALUES ('Winter Break','2017-12-25','2018-01-09');
INSERT INTO SchoolVacations VALUES ('Spring Break','2018-03-30','2018-04-09');
INSERT INTO SchoolVacations VALUES ('Summer Break','2018-06-15','2018-08-31');
CREATE TABLE HoliDays
(
HoliDayName text NOT NULL 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 HoliDays VALUES ('Name 1','2017-10-21','2017-10-21');
INSERT INTO HoliDays VALUES ('Name 2','2017-11-11','2017-11-11');
INSERT INTO HoliDays VALUES ('Name 4','2018-02-15','2018-02-16');
INSERT INTO HoliDays VALUES ('Name 3','2018-02-27','2018-02-27');
INSERT INTO HoliDays VALUES ('Name 5','2018-04-22','2018-04-22');
INSERT INTO HoliDays VALUES ('Name 6','2018-05-01','2018-05-02');
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');
INSERT INTO DaysOfWeek VALUES(1,'M','Monday');
INSERT INTO DaysOfWeek VALUES(2,'Tu','Tuesday');
INSERT INTO DaysOfWeek VALUES(3,'W','Wednesday');
INSERT INTO DaysOfWeek VALUES(4,'Th','Thursday');
INSERT INTO DaysOfWeek VALUES(5,'F','Friday');
INSERT INTO DaysOfWeek VALUES(6,'Sa','Saturday');
CREATE TABLE LessonBlocks
(
WeekNumber integer NOT NULL PRIMARY KEY ,
LessonBlock text NOT NULL COLLATE nocase
);
INSERT INTO LessonBlocks VALUES(1,'1-2');
INSERT INTO LessonBlocks VALUES(2,'3-4');
INSERT INTO LessonBlocks VALUES(3,'5-6');
INSERT INTO LessonBlocks VALUES(4,'7-8');
INSERT INTO LessonBlocks VALUES(5,'9-10');
INSERT INTO LessonBlocks VALUES(6,'11-12');
INSERT INTO LessonBlocks VALUES(7,'13-14');
INSERT INTO LessonBlocks VALUES(8,'15-16');
INSERT INTO LessonBlocks VALUES(9,'17-18');
INSERT INTO LessonBlocks VALUES(10,'19-20');
INSERT INTO LessonBlocks VALUES(11,'21-22');
INSERT INTO LessonBlocks VALUES(12,'23-24');
INSERT INTO LessonBlocks VALUES(13,'25-26');
INSERT INTO LessonBlocks VALUES(14,'27-28');
INSERT INTO LessonBlocks VALUES(15,'29-30');
INSERT INTO LessonBlocks VALUES(16,'31-32');
INSERT INTO LessonBlocks VALUES(17,'33-34');
INSERT INTO LessonBlocks VALUES(18,'35-36');
INSERT INTO LessonBlocks VALUES(19,'37-38');
INSERT INTO LessonBlocks VALUES(20,'39-40');
INSERT INTO LessonBlocks VALUES(21,'41-42');
INSERT INTO LessonBlocks VALUES(22,'43-44');
INSERT INTO LessonBlocks VALUES(23,'45-46');
INSERT INTO LessonBlocks VALUES(24,'47-48');
INSERT INTO LessonBlocks VALUES(25,'49-50');
INSERT INTO LessonBlocks VALUES(26,'51-52');
INSERT 

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

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] Is it possible to CREATE TABLE from other tables in a complex way?

[csanyipal]

R Smith-2 wrote
> On 2018/03/21 9:58 PM, csanyipal wrote:
>>
>> I am really trying to understand how CTEs works and trying to achive my
>> goal
>> ( see bellow ) so I modified a little your code:
>> ...
>> As you can see I tried to add more CTEs into code out there but must
>> these
>> comment out because I get wrong Results.
>> So for now, with this code above I get followings ( for three school
>> weeks
>> ):
>>
>> 2017-09-01|F|1-2
>> 2017-09-01|F|3-4
>> 2017-09-01|F|5-6
>> 2017-09-04|M|1-2
>> 
>>
>> but I want followings ( for three school weeks ):
>>
>> 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
>> 
>>
>> So what SQL command should I use for this?
> 
> I'm not exactly sure how the classes work, it's hard to establish from 
> your explanation and SQL example output, for instance, in the output, 
> why does Friday 1 September 2017 only have lesson-block 1-2 for grades 
> 5b and 7c... why not lesson blocks 3-4 or 5-6? ARe the lesson blocks 
> only active on certain days? If so, we need a table to specify it so.
> 
> I like your table specifying the TimeTables. It needed fixing because 
> the VALUES mechanism works a bit differently than you think, but you can 
> compare between your and my version to see exactly how that works.
> Like I said, it's a bit unclear, but I'm going to assume some things and 
> suggest this:
> 
> WITH PAR(calStartDate, calEndDate) AS (
>      SELECT '2017-09-01', '2017-09-21'
> ),DoW(dayId,dayName) AS (
>      VALUES (0,'Su'), (1,'M'), (2,'Tu'), (3,'W'),(4,'Th'),
> (5,'F'),(6,'Sa')
> ),LBs(lessonBlock) 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'), ('37-38'), ('39-40'),
>     ('41-42'), ('43-44'), ('45-46'), ('47-48'), ('49-50'), 
> ('51-52'), ('53-54'), ('55-56'), ('57-58'), ('59-60'),
>     ('61-62'), ('63-64'), ('65-66'), ('67-68'), ('69-70'), 
> ('71-72')  */
> ), TimeTable(DoWeek,Grade,Class) AS (
>   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')
> ), CAL(dayDate,nextDay,dayId) AS (
>      SELECT date(calStartDate,'-1 day'), date(calStartDate), -1 FROM PAR
>   UNION ALL
>      SELECT nextDay, date(nextDay,'+1 day'), CAST(STRFTIME('%w',nextDay) 
> AS INT) FROM CAL,PAR WHERE nextDay <= calEndDate
> ), RES(dayDate, dayName, lessonBlock, Grade, Class) AS (
>      SELECT CAL.dayDate, DoW.dayName, LBs.lessonBlock, TimeTable.Grade, 
> TimeTable.Class
>    FROM CAL
>   CROSS JOIN LBs
>    JOIN DoW ON DoW.dayID = CAL.dayId
>    JOIN TimeTable ON TimeTable.DoWeek = DoW.dayName
>   WHERE CAL.dayId > 0 AND CAL.dayId < 6  -- No Sundays and No
> Saturdays
> )
> SELECT *
>      FROM RES;
> 
> 
> I kept the majority of lesson blocks commented out for brevity, but if 
> it works, you can uncomment them and everything should work.
> 
> I hope it works, but if not, feel free to ask again, perhaps with a more 
> exact explanation of what the output should contain and how the lesson 
> blocks work.
> 
> 
> Cheers!
> Ryan

I tried it out, but it not gives the expected result.
I modified the 'Class' column name to 'ClassName' everywhere where it
occures, eg. like this:
TimeTable(DoWeek,Grade,ClassName)
and
TimeTable.ClassName

in order to make it more understandable.
Then I run it but it gives not the expected results.
This is because I was not clear enough about how the lesson blocks works.
But now this is what I'm supposed to do. My first language is not English,
so sorry if I fail in my attempt to explain this.
So in a school year on grade/class name ( like 5/a, 5/b, 5/c or 6/b, etc.)
must take 36 lesson blocks where a lesson block is built up of two lessons -
school hours - and lasts twice for 45 minutes. So in a school year there is
72 lessons, so 72/2=36 lesson blocks out there.
The lesson blocks I call like this: 1-2, 3-4, etc.
But there are school weeks too. In a school year there are 36 school weeks
out there.
In a school year there are 36 lesson blocks too. We numbering lesson blocks
in a school week, like this:
in the 1. school week (SW) the lesson block is called 1-2,
in the 2. SW 3-4,
...
in the 35. SW 69-70, and finally
in the 36. SW 71-72.

Now about the TimeTable.
On Friday 2017-09-01 I had one lesson block ( the 1-2 ) with 5/b Class and
one lesson block ( the 1-2 ) with 7/c Class.
On the next Friday I had one lesson block ( the 3-4 ) with 5/b Class and one
lesson block ( the 3-4 ) with 7/c Class.
And so on.
Say on Friday 2018-06-08 I will have one lesson block ( the 71-72 ) with 5/b
Class and one lesson block ( the 71-72 ) with 7/c Class.

So the records from the
SELECT *
 FROM RES;

should gives the followings ( see the Fridays how changes the 

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

[R Smith]

On 2018/03/21 9:58 PM, csanyipal wrote:


I am really trying to understand how CTEs works and trying to achive my goal
( see bellow ) so I modified a little your code:
...
As you can see I tried to add more CTEs into code out there but must these
comment out because I get wrong Results.
So for now, with this code above I get followings ( for three school weeks
):

2017-09-01|F|1-2
2017-09-01|F|3-4
2017-09-01|F|5-6
2017-09-04|M|1-2


but I want followings ( for three school weeks ):

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


So what SQL command should I use for this?


I'm not exactly sure how the classes work, it's hard to establish from 
your explanation and SQL example output, for instance, in the output, 
why does Friday 1 September 2017 only have lesson-block 1-2 for grades 
5b and 7c... why not lesson blocks 3-4 or 5-6? ARe the lesson blocks 
only active on certain days? If so, we need a table to specify it so.


I like your table specifying the TimeTables. It needed fixing because 
the VALUES mechanism works a bit differently than you think, but you can 
compare between your and my version to see exactly how that works.
Like I said, it's a bit unclear, but I'm going to assume some things and 
suggest this:


WITH PAR(calStartDate, calEndDate) AS (
    SELECT '2017-09-01', '2017-09-21'
),DoW(dayId,dayName) AS (
    VALUES (0,'Su'), (1,'M'), (2,'Tu'), (3,'W'),(4,'Th'), (5,'F'),(6,'Sa')
),LBs(lessonBlock) 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'), ('37-38'), ('39-40'),
   ('41-42'), ('43-44'), ('45-46'), ('47-48'), ('49-50'), 
('51-52'), ('53-54'), ('55-56'), ('57-58'), ('59-60'),
   ('61-62'), ('63-64'), ('65-66'), ('67-68'), ('69-70'), 
('71-72')  */

), TimeTable(DoWeek,Grade,Class) AS (
 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')
), CAL(dayDate,nextDay,dayId) AS (
    SELECT date(calStartDate,'-1 day'), date(calStartDate), -1 FROM PAR
 UNION ALL
    SELECT nextDay, date(nextDay,'+1 day'), CAST(STRFTIME('%w',nextDay) 
AS INT) FROM CAL,PAR WHERE nextDay <= calEndDate

), RES(dayDate, dayName, lessonBlock, Grade, Class) AS (
    SELECT CAL.dayDate, DoW.dayName, LBs.lessonBlock, TimeTable.Grade, 
TimeTable.Class

  FROM CAL
 CROSS JOIN LBs
  JOIN DoW ON DoW.dayID = CAL.dayId
  JOIN TimeTable ON TimeTable.DoWeek = DoW.dayName
 WHERE CAL.dayId > 0 AND CAL.dayId < 6  -- No Sundays and No Saturdays
)
SELECT *
    FROM RES;


I kept the majority of lesson blocks commented out for brevity, but if 
it works, you can uncomment them and everything should work.


I hope it works, but if not, feel free to ask again, perhaps with a more 
exact explanation of what the output should contain and how the lesson 
blocks work.



Cheers!
Ryan

___
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]

David Raymond wrote
> In the commented out section:
> 
> TimeTable(DoWeek,Grade,Class_) AS
> (VALUES('M'),(7),('b'),('M'),(5),('a'),('Tu'),(8),('c')...
> 
> Shouldn't that be ...AS (VALUES ('M', 7, 'B'), ('M', 5, 'a'), ('Tu', 5,
> 'c')...?
> 
> WITH PAR(calStartDate, calEndDate) AS (SELECT '2017-09-01', '2017-09-21'),
>  DoW(dayId,dayName) AS (VALUES (0,'Su'), (1,'M'), (2,'Tu'), (3,'W'),
> (4,'Th'), (5,'F'),(6,'Sa')),
>  LBs(lessonBlock) 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'), ('37-38'), ('39-40'), ('41-42')
>   , ('43-44'), ('45-46'), ('47-48'), ('49-50'), ('51-52'), ('53-54'),
> ('55-56'), ('57-58'), ('59-60'), ('61-62'), ('63-64')
>   , ('65-66'), ('67-68'), ('69-70'), ('71-72')*/),
>  /*Grades(Grade) AS (VALUES (5), (6), (7), (8)),
>  Classes(Class) AS (VALUES ('a'), ('b'), ('c')),
>  TimeTable(DoWeek,Grade,Class_) AS (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')),*/
>  CAL(dayDate,nextDay,dayId) AS (SELECT date(calStartDate,'-1 day'),
>  date(calStartDate), -1
>  FROM PAR
>  UNION ALL
>   SELECT nextDay, date(nextDay,'+1 day'),
> CAST(STRFTIME('%w',nextDay) AS INT)
> FROM CAL,PAR
> WHERE nextDay <= calEndDate),
>  RES(dayDate, dayName, lessonBlock) AS
>(SELECT CAL.dayDate, DoW.dayName, LBs.lessonBlock
>   FROM CAL
>CROSS JOIN LBs
>JOIN DoW ON DoW.dayID = CAL.dayId
>WHERE CAL.dayId > 0 AND CAL.dayId < 6)  /* No Sundays and No
> Saturdays */
> SELECT *
> FROM RES;
> 

Indeed. I corrected that part and add the
JOIN TimeTable
like this:
 FROM PAR
 UNION ALL
  SELECT nextDay, date(nextDay,'+1 day'),
CAST(STRFTIME('%w',nextDay) AS INT)
FROM CAL,PAR
WHERE nextDay <= calEndDate),
 RES(dayDate, dayName, lessonBlock) AS
 (SELECT CAL.dayDate, DoW.dayName, LBs.lessonBlock
  FROM CAL
   CROSS JOIN LBs
   JOIN TimeTable
   JOIN DoW ON DoW.dayID = CAL.dayId
   WHERE CAL.dayId > 0 AND CAL.dayId < 6)  /* No Sundays and No
Saturdays */

but get wrong outputs:
2017-09-01|F|1-2
2017-09-01|F|1-2
2017-09-01|F|1-2
2017-09-01|F|1-2
2017-09-01|F|1-2
2017-09-01|F|1-2
2017-09-01|F|1-2
2017-09-01|F|1-2
2017-09-01|F|1-2
2017-09-01|F|1-2
2017-09-01|F|3-4
2017-09-01|F|3-4
2017-09-01|F|3-4
2017-09-01|F|3-4
2017-09-01|F|3-4
2017-09-01|F|3-4
2017-09-01|F|3-4
2017-09-01|F|3-4
2017-09-01|F|3-4
2017-09-01|F|3-4
2017-09-01|F|5-6
2017-09-01|F|5-6
2017-09-01|F|5-6
2017-09-01|F|5-6
2017-09-01|F|5-6
2017-09-01|F|5-6
2017-09-01|F|5-6
2017-09-01|F|5-6
2017-09-01|F|5-6
2017-09-01|F|5-6
2017-09-04|M|1-2
2017-09-04|M|1-2
2017-09-04|M|1-2
2017-09-04|M|1-2

etc.
What am I doing wrong?



-
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]

R Smith-2 wrote
> On 2018/03/17 12:40 PM, csanyipal wrote:
>> R Smith-2 wrote
>>> Here is a query that will produce all days of the year (without Sundays)
>>> plus their week days (and I've expanded for lesson blocks too, but you
>>> will probably need to add/edit as I don't know the exact values, but the
>>> method should be clear). You can JOIN this to the other tables
>>> containing courses and such to populate the hours table.
>>>
>>> WITH PAR(calStartDate, calEndDate) AS (
>>>       SELECT '2018-01-01 00:00:00', '2018-12-31 23:59:59'
>>> ), DoW(dayId,dayName) AS (
>>>       VALUES (0,'Su'), (1,'M'), (2,'Tu'), (3,'W'), (4,'Th'), (5,'F'),
>>> (6,'Sa')
>>> ), LBs(lessonBlock) AS (
>>>       VALUES ('1-2'), ('2-3'), ('3-4'), ('4-5')
>>> ), CAL(dayDate,nextDay,dayId) AS (
>>>       SELECT date(calStartDate,'-1 day'), date(calStartDate), -1  FROM
>>> PAR
>>>       UNION ALL
>>>       SELECT nextDay, date(nextDay,'+1 day'),
>>> CAST(strftime('%w',nextDay)
>>> AS INT) FROM CAL,PAR WHERE nextDay < calEndDate
>>> ), RES(dayDate, dayName, lessonBlock) AS (
>>>       SELECT CAL.dayDate, DoW.dayName, LBs.lessonBlock
>>>     FROM CAL
>>>     CROSS JOIN LBs
>>>     JOIN DoW ON DoW.dayID = CAL.dayId
>>>    WHERE CAL.dayId > 0  -- No Sundays
>>> )
>>> SELECT *
>>>     FROM RES
>> PAR and RES are table names; please tell me what is the meaning of the
>> PAR
>> and RES abbreviations?
>> This is what I need to better understand this query.
> 
> PAR and RES are simply names for the common table expression (CTE) views 
> I chose arbitrarily, I took PAR to mean "Parameters" since I only really 
> supply Start-Date and End-Date parameters in that first PAR view - it 
> has no other use.
> 
> I chose RES as short for "Results" and CAL as short for Calendar.
> 
> In the Calendar cte (CAL) I simply use recursive cte (the "UNION ALL" 
> followed by a "SELECT FROM itself" shows it is recursive) to make up all 
> the dates cross-joined by PAR so I can limit it to go no further than 
> calEndDate. You can achieve the same by simply hard-coding the dates in 
> CAL (in stead of joining the PAR view), but I tend to find it more 
> sensible to put "things that might change" right at the top of the query 
> mimicking the parameters of normal programming - That's all the PAR is 
> for, it's not in any way mandatory.
> 
> In the RES CTE view, I simply join all the dates from the recursive cte 
> calendar (CAL) with the Day-of-Week cte (DoW) and the LessonBlocks cte 
> (LB) to produce the resulting output we wanted.
> 
> One trick when using CTEs - The very bottom "SELECT FROM RES" you can 
> simply change to be "SELECT FROM CAL" or "SELECT FROM DoW" or indeed any 
> one of the CTE views used so that you can debug/inspect it to understand 
> what they do.
> 
> I hope that clears it up, but please feel free to ask more if you have 
> more questions - understanding CTEs well is a great advantage when using 
> sql.
> 
> 
> Cheers,
> Ryan

Thank you Ryan, for the explanations!

I am really trying to understand how CTEs works and trying to achive my goal
( see bellow ) so I modified a little your code:

WITH PAR(calStartDate, calEndDate) AS (SELECT '2017-09-01', '2017-09-21'),
 DoW(dayId,dayName) AS (VALUES (0,'Su'), (1,'M'), (2,'Tu'), (3,'W'),
(4,'Th'), (5,'F'),(6,'Sa')),
 LBs(lessonBlock) 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'), ('37-38'), ('39-40'), ('41-42')
  , ('43-44'), ('45-46'), ('47-48'), ('49-50'), ('51-52'), ('53-54'),
('55-56'), ('57-58'), ('59-60'), ('61-62'), ('63-64')
  , ('65-66'), ('67-68'), ('69-70'), ('71-72')*/),
 /*Grades(Grade) AS (VALUES (5), (6), (7), (8)),
 Classes(Class) AS (VALUES ('a'), ('b'), ('c')),
 TimeTable(DoWeek,Grade,Class_) AS (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')),*/
 CAL(dayDate,nextDay,dayId) AS (SELECT date(calStartDate,'-1 day'),
 date(calStartDate), -1
 FROM PAR
 UNION ALL
  SELECT nextDay, date(nextDay,'+1 day'),
CAST(STRFTIME('%w',nextDay) AS INT)
FROM CAL,PAR
WHERE nextDay <= calEndDate),
 RES(dayDate, dayName, lessonBlock) AS
 (SELECT CAL.dayDate, DoW.dayName, LBs.lessonBlock
  FROM CAL
   CROSS JOIN LBs
   JOIN DoW ON DoW.dayID = CAL.dayId
   WHERE CAL.dayId > 0 AND CAL.dayId < 6)  /* No Sundays and No
Saturdays */
SELECT *
FROM RES;

As you can see I tried to add more CTEs into code out there but must these
comment out because I get wrong Results.
So for now, with this code above I get followings ( for three school weeks
):

2017-09-01|F|1-2
2017-09-01|F|3-4
2017-09-01|F|5-6
2017-09-04|M|1-2
2017-09-04|M|3-4
2017-09-04|M|5-6
2017-09-05|Tu|1-2
2017-09-05|Tu|3-4

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

[David Raymond]

In the commented out section:

TimeTable(DoWeek,Grade,Class_) AS 
(VALUES('M'),(7),('b'),('M'),(5),('a'),('Tu'),(8),('c')...

Shouldn't that be ...AS (VALUES ('M', 7, 'B'), ('M', 5, 'a'), ('Tu', 5, 'c')...?




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

R Smith-2 wrote
> On 2018/03/17 12:40 PM, csanyipal wrote:
>> R Smith-2 wrote
>>> Here is a query that will produce all days of the year (without Sundays)
>>> plus their week days (and I've expanded for lesson blocks too, but you
>>> will probably need to add/edit as I don't know the exact values, but the
>>> method should be clear). You can JOIN this to the other tables
>>> containing courses and such to populate the hours table.
>>>
>>> WITH PAR(calStartDate, calEndDate) AS (
>>>       SELECT '2018-01-01 00:00:00', '2018-12-31 23:59:59'
>>> ), DoW(dayId,dayName) AS (
>>>       VALUES (0,'Su'), (1,'M'), (2,'Tu'), (3,'W'), (4,'Th'), (5,'F'),
>>> (6,'Sa')
>>> ), LBs(lessonBlock) AS (
>>>       VALUES ('1-2'), ('2-3'), ('3-4'), ('4-5')
>>> ), CAL(dayDate,nextDay,dayId) AS (
>>>       SELECT date(calStartDate,'-1 day'), date(calStartDate), -1  FROM
>>> PAR
>>>       UNION ALL
>>>       SELECT nextDay, date(nextDay,'+1 day'),
>>> CAST(strftime('%w',nextDay)
>>> AS INT) FROM CAL,PAR WHERE nextDay < calEndDate
>>> ), RES(dayDate, dayName, lessonBlock) AS (
>>>       SELECT CAL.dayDate, DoW.dayName, LBs.lessonBlock
>>>     FROM CAL
>>>     CROSS JOIN LBs
>>>     JOIN DoW ON DoW.dayID = CAL.dayId
>>>    WHERE CAL.dayId > 0  -- No Sundays
>>> )
>>> SELECT *
>>>     FROM RES
>> PAR and RES are table names; please tell me what is the meaning of the
>> PAR
>> and RES abbreviations?
>> This is what I need to better understand this query.
> 
> PAR and RES are simply names for the common table expression (CTE) views 
> I chose arbitrarily, I took PAR to mean "Parameters" since I only really 
> supply Start-Date and End-Date parameters in that first PAR view - it 
> has no other use.
> 
> I chose RES as short for "Results" and CAL as short for Calendar.
> 
> In the Calendar cte (CAL) I simply use recursive cte (the "UNION ALL" 
> followed by a "SELECT FROM itself" shows it is recursive) to make up all 
> the dates cross-joined by PAR so I can limit it to go no further than 
> calEndDate. You can achieve the same by simply hard-coding the dates in 
> CAL (in stead of joining the PAR view), but I tend to find it more 
> sensible to put "things that might change" right at the top of the query 
> mimicking the parameters of normal programming - That's all the PAR is 
> for, it's not in any way mandatory.
> 
> In the RES CTE view, I simply join all the dates from the recursive cte 
> calendar (CAL) with the Day-of-Week cte (DoW) and the LessonBlocks cte 
> (LB) to produce the resulting output we wanted.
> 
> One trick when using CTEs - The very bottom "SELECT FROM RES" you can 
> simply change to be "SELECT FROM CAL" or "SELECT FROM DoW" or indeed any 
> one of the CTE views used so that you can debug/inspect it to understand 
> what they do.
> 
> I hope that clears it up, but please feel free to ask more if you have 
> more questions - understanding CTEs well is a great advantage when using 
> sql.
> 
> 
> Cheers,
> Ryan

Thank you Ryan, for the explanations!

I am really trying to understand how CTEs works and trying to achive my goal
( see bellow ) so I modified a little your code:

WITH PAR(calStartDate, calEndDate) AS (SELECT '2017-09-01', '2017-09-21'),
 DoW(dayId,dayName) AS (VALUES (0,'Su'), (1,'M'), (2,'Tu'), (3,'W'),
(4,'Th'), (5,'F'),(6,'Sa')),
 LBs(lessonBlock) 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'), ('37-38'), ('39-40'), ('41-42')
  , ('43-44'), ('45-46'), ('47-48'), ('49-50'), ('51-52'), ('53-54'),
('55-56'), ('57-58'), ('59-60'), ('61-62'), ('63-64')
  , ('65-66'), ('67-68'), ('69-70'), ('71-72')*/),
 /*Grades(Grade) AS (VALUES (5), (6), (7), (8)),
 Classes(Class) AS (VALUES ('a'), ('b'), ('c')),
 TimeTable(DoWeek,Grade,Class_) AS (VALUES
('M'),(7),('b'),('M'),(5),('a'),('Tu'),(8),('c'),('Tu'),(8),('b'),('W'),(8),('a'),
 
('W'),(7)

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

[csanyipal]

R Smith-2 wrote
> On 2018/03/17 12:40 PM, csanyipal wrote:
>> R Smith-2 wrote
>>> Here is a query that will produce all days of the year (without Sundays)
>>> plus their week days (and I've expanded for lesson blocks too, but you
>>> will probably need to add/edit as I don't know the exact values, but the
>>> method should be clear). You can JOIN this to the other tables
>>> containing courses and such to populate the hours table.
>>>
>>> WITH PAR(calStartDate, calEndDate) AS (
>>>       SELECT '2018-01-01 00:00:00', '2018-12-31 23:59:59'
>>> ), DoW(dayId,dayName) AS (
>>>       VALUES (0,'Su'), (1,'M'), (2,'Tu'), (3,'W'), (4,'Th'), (5,'F'),
>>> (6,'Sa')
>>> ), LBs(lessonBlock) AS (
>>>       VALUES ('1-2'), ('2-3'), ('3-4'), ('4-5')
>>> ), CAL(dayDate,nextDay,dayId) AS (
>>>       SELECT date(calStartDate,'-1 day'), date(calStartDate), -1  FROM
>>> PAR
>>>       UNION ALL
>>>       SELECT nextDay, date(nextDay,'+1 day'),
>>> CAST(strftime('%w',nextDay)
>>> AS INT) FROM CAL,PAR WHERE nextDay < calEndDate
>>> ), RES(dayDate, dayName, lessonBlock) AS (
>>>       SELECT CAL.dayDate, DoW.dayName, LBs.lessonBlock
>>>     FROM CAL
>>>     CROSS JOIN LBs
>>>     JOIN DoW ON DoW.dayID = CAL.dayId
>>>    WHERE CAL.dayId > 0  -- No Sundays
>>> )
>>> SELECT *
>>>     FROM RES
>> PAR and RES are table names; please tell me what is the meaning of the
>> PAR
>> and RES abbreviations?
>> This is what I need to better understand this query.
> 
> PAR and RES are simply names for the common table expression (CTE) views 
> I chose arbitrarily, I took PAR to mean "Parameters" since I only really 
> supply Start-Date and End-Date parameters in that first PAR view - it 
> has no other use.
> 
> I chose RES as short for "Results" and CAL as short for Calendar.
> 
> In the Calendar cte (CAL) I simply use recursive cte (the "UNION ALL" 
> followed by a "SELECT FROM itself" shows it is recursive) to make up all 
> the dates cross-joined by PAR so I can limit it to go no further than 
> calEndDate. You can achieve the same by simply hard-coding the dates in 
> CAL (in stead of joining the PAR view), but I tend to find it more 
> sensible to put "things that might change" right at the top of the query 
> mimicking the parameters of normal programming - That's all the PAR is 
> for, it's not in any way mandatory.
> 
> In the RES CTE view, I simply join all the dates from the recursive cte 
> calendar (CAL) with the Day-of-Week cte (DoW) and the LessonBlocks cte 
> (LB) to produce the resulting output we wanted.
> 
> One trick when using CTEs - The very bottom "SELECT FROM RES" you can 
> simply change to be "SELECT FROM CAL" or "SELECT FROM DoW" or indeed any 
> one of the CTE views used so that you can debug/inspect it to understand 
> what they do.
> 
> I hope that clears it up, but please feel free to ask more if you have 
> more questions - understanding CTEs well is a great advantage when using 
> sql.
> 
> 
> Cheers,
> Ryan

Thank you Ryan, for the explanations!

I am really trying to understand how CTEs works and trying to achive my goal
( see bellow ) so I modified a little your code:

WITH PAR(calStartDate, calEndDate) AS (SELECT '2017-09-01', '2017-09-21'),
 DoW(dayId,dayName) AS (VALUES (0,'Su'), (1,'M'), (2,'Tu'), (3,'W'),
(4,'Th'), (5,'F'),(6,'Sa')),
 LBs(lessonBlock) 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'), ('37-38'), ('39-40'), ('41-42')
  , ('43-44'), ('45-46'), ('47-48'), ('49-50'), ('51-52'), ('53-54'),
('55-56'), ('57-58'), ('59-60'), ('61-62'), ('63-64')
  , ('65-66'), ('67-68'), ('69-70'), ('71-72')*/),
 /*Grades(Grade) AS (VALUES (5), (6), (7), (8)),
 Classes(Class) AS (VALUES ('a'), ('b'), ('c')),
 TimeTable(DoWeek,Grade,Class_) AS (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')),*/
 CAL(dayDate,nextDay,dayId) AS (SELECT date(calStartDate,'-1 day'),
 date(calStartDate), -1
 FROM PAR
 UNION ALL
  SELECT nextDay, date(nextDay,'+1 day'),
CAST(STRFTIME('%w',nextDay) AS INT)
FROM CAL,PAR
WHERE nextDay <= calEndDate),
 RES(dayDate, dayName, lessonBlock) AS
 (SELECT CAL.dayDate, DoW.dayName, LBs.lessonBlock
  FROM CAL
   CROSS JOIN LBs
   JOIN DoW ON DoW.dayID = CAL.dayId
   WHERE CAL.dayId > 0 AND CAL.dayId < 6)  /* No Sundays and No
Saturdays */
SELECT *
FROM RES;

As you can see I tried to add more CTEs into code out there but must these
comment out because I get wrong Results.
So for now, with this code above I get followings ( for three school weeks
):

2017-09-01|F|1-2
2017-09-01|F|3-4
2017-09-01|F|5-6
2017-09-04|M|1-2
2017-09-04|M|3-4
2017-09-04|M|5-6
2017-09-05|Tu|1-2
2017-09-05|Tu|3-4

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

[R Smith]

On 2018/03/17 12:40 PM, csanyipal wrote:

R Smith-2 wrote

Here is a query that will produce all days of the year (without Sundays)
plus their week days (and I've expanded for lesson blocks too, but you
will probably need to add/edit as I don't know the exact values, but the
method should be clear). You can JOIN this to the other tables
containing courses and such to populate the hours table.

WITH PAR(calStartDate, calEndDate) AS (
      SELECT '2018-01-01 00:00:00', '2018-12-31 23:59:59'
), DoW(dayId,dayName) AS (
      VALUES (0,'Su'), (1,'M'), (2,'Tu'), (3,'W'), (4,'Th'), (5,'F'),
(6,'Sa')
), LBs(lessonBlock) AS (
      VALUES ('1-2'), ('2-3'), ('3-4'), ('4-5')
), CAL(dayDate,nextDay,dayId) AS (
      SELECT date(calStartDate,'-1 day'), date(calStartDate), -1  FROM PAR
      UNION ALL
      SELECT nextDay, date(nextDay,'+1 day'), CAST(strftime('%w',nextDay)
AS INT) FROM CAL,PAR WHERE nextDay < calEndDate
), RES(dayDate, dayName, lessonBlock) AS (
      SELECT CAL.dayDate, DoW.dayName, LBs.lessonBlock
    FROM CAL
    CROSS JOIN LBs
    JOIN DoW ON DoW.dayID = CAL.dayId
   WHERE CAL.dayId > 0  -- No Sundays
)
SELECT *
    FROM RES

PAR and RES are table names; please tell me what is the meaning of the PAR
and RES abbreviations?
This is what I need to better understand this query.


PAR and RES are simply names for the common table expression (CTE) views 
I chose arbitrarily, I took PAR to mean "Parameters" since I only really 
supply Start-Date and End-Date parameters in that first PAR view - it 
has no other use.


I chose RES as short for "Results" and CAL as short for Calendar.

In the Calendar cte (CAL) I simply use recursive cte (the "UNION ALL" 
followed by a "SELECT FROM itself" shows it is recursive) to make up all 
the dates cross-joined by PAR so I can limit it to go no further than 
calEndDate. You can achieve the same by simply hard-coding the dates in 
CAL (in stead of joining the PAR view), but I tend to find it more 
sensible to put "things that might change" right at the top of the query 
mimicking the parameters of normal programming - That's all the PAR is 
for, it's not in any way mandatory.


In the RES CTE view, I simply join all the dates from the recursive cte 
calendar (CAL) with the Day-of-Week cte (DoW) and the LessonBlocks cte 
(LB) to produce the resulting output we wanted.


One trick when using CTEs - The very bottom "SELECT FROM RES" you can 
simply change to be "SELECT FROM CAL" or "SELECT FROM DoW" or indeed any 
one of the CTE views used so that you can debug/inspect it to understand 
what they do.


I hope that clears it up, but please feel free to ask more if you have 
more questions - understanding CTEs well is a great advantage when using 
sql.



Cheers,
Ryan


___
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?

[Keith Medcalf]

PAR is Parameters (calendar start and end)
RES is Results

from what I see anyway ...


---
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 csanyipal
>Sent: Saturday, 17 March, 2018 04:40
>To: sqlite-users@mailinglists.sqlite.org
>Subject: Re: [sqlite] Is it possible to CREATE TABLE from other
>tables in a complex way?
>
>R Smith-2 wrote
>> Here is a query that will produce all days of the year (without
>Sundays)
>> plus their week days (and I've expanded for lesson blocks too, but
>you
>> will probably need to add/edit as I don't know the exact values,
>but the
>> method should be clear). You can JOIN this to the other tables
>> containing courses and such to populate the hours table.
>>
>> WITH PAR(calStartDate, calEndDate) AS (
>>      SELECT '2018-01-01 00:00:00', '2018-12-31 23:59:59'
>> ), DoW(dayId,dayName) AS (
>>      VALUES (0,'Su'), (1,'M'), (2,'Tu'), (3,'W'), (4,'Th'),
>(5,'F'),
>> (6,'Sa')
>> ), LBs(lessonBlock) AS (
>>      VALUES ('1-2'), ('2-3'), ('3-4'), ('4-5')
>> ), CAL(dayDate,nextDay,dayId) AS (
>>      SELECT date(calStartDate,'-1 day'), date(calStartDate), -
>1  FROM PAR
>>      UNION ALL
>>      SELECT nextDay, date(nextDay,'+1 day'),
>CAST(strftime('%w',nextDay)
>> AS INT) FROM CAL,PAR WHERE nextDay < calEndDate
>> ), RES(dayDate, dayName, lessonBlock) AS (
>>      SELECT CAL.dayDate, DoW.dayName, LBs.lessonBlock
>>    FROM CAL
>>    CROSS JOIN LBs
>>    JOIN DoW ON DoW.dayID = CAL.dayId
>>   WHERE CAL.dayId > 0  -- No Sundays
>> )
>> SELECT *
>>    FROM RES
>
>PAR and RES are table names; please tell me what is the meaning of
>the PAR
>and RES abbreviations?
>This is what I need to better understand this 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



___
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]

R Smith-2 wrote
> Here is a query that will produce all days of the year (without Sundays) 
> plus their week days (and I've expanded for lesson blocks too, but you 
> will probably need to add/edit as I don't know the exact values, but the 
> method should be clear). You can JOIN this to the other tables 
> containing courses and such to populate the hours table.
> 
> WITH PAR(calStartDate, calEndDate) AS (
>      SELECT '2018-01-01 00:00:00', '2018-12-31 23:59:59'
> ), DoW(dayId,dayName) AS (
>      VALUES (0,'Su'), (1,'M'), (2,'Tu'), (3,'W'), (4,'Th'), (5,'F'), 
> (6,'Sa')
> ), LBs(lessonBlock) AS (
>      VALUES ('1-2'), ('2-3'), ('3-4'), ('4-5')
> ), CAL(dayDate,nextDay,dayId) AS (
>      SELECT date(calStartDate,'-1 day'), date(calStartDate), -1  FROM PAR
>      UNION ALL
>      SELECT nextDay, date(nextDay,'+1 day'), CAST(strftime('%w',nextDay) 
> AS INT) FROM CAL,PAR WHERE nextDay < calEndDate
> ), RES(dayDate, dayName, lessonBlock) AS (
>      SELECT CAL.dayDate, DoW.dayName, LBs.lessonBlock
>    FROM CAL
>    CROSS JOIN LBs
>    JOIN DoW ON DoW.dayID = CAL.dayId
>   WHERE CAL.dayId > 0  -- No Sundays
> )
> SELECT *
>    FROM RES

PAR and RES are table names; please tell me what is the meaning of the PAR
and RES abbreviations?
This is what I need to better understand this 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] Is it possible to CREATE TABLE from other tables in a complex way?

[csanyipal]

R Smith-2 wrote
> This seems like a whole assignment, and we are not in the habit to do 
> assignments for people,
> 
> BUT, we can get you started down the path.
> 
> You should know how to do all you are asking by simple RDBMS mechanics, 
> except maybe how to initialize a table with all dates and other things 
> pre-populated, so to assist with that
> 
> Here is a query that will produce all days of the year (without Sundays) 
> plus their week days (and I've expanded for lesson blocks too, but you 
> will probably need to add/edit as I don't know the exact values, but the 
> method should be clear). You can JOIN this to the other tables 
> containing courses and such to populate the hours table.

I did not even think that you would do the job for me.
However, thank you very much for the path you showed.
That's what I wanted to, to get me started down the path.
Thank you very much!



-
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?

[Simon Slavin]

On 11 Mar 2018, at 12:05pm, Csányi Pál  wrote:

> Columns are: id,date,D,lb,g,c,lp,ld,re
> 
> where D is a Day name in Week,
> lb is the number of the Lesson Block hour,
> g is the grade of a school class,
> c is the name of the school class,
> lp is LessonPlan,
> ld is LessonDiary,
> re is Reminder.

Don't do that.  If a column contains a lesson plan, call it "lessonPlan".  
Don't make up a short form you will forget.

Simon.
___
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?

[R Smith]

This seems like a whole assignment, and we are not in the habit to do 
assignments for people,


BUT, we can get you started down the path.

You should know how to do all you are asking by simple RDBMS mechanics, 
except maybe how to initialize a table with all dates and other things 
pre-populated, so to assist with that


Here is a query that will produce all days of the year (without Sundays) 
plus their week days (and I've expanded for lesson blocks too, but you 
will probably need to add/edit as I don't know the exact values, but the 
method should be clear). You can JOIN this to the other tables 
containing courses and such to populate the hours table.


WITH PAR(calStartDate, calEndDate) AS (
    SELECT '2018-01-01 00:00:00', '2018-12-31 23:59:59'
), DoW(dayId,dayName) AS (
    VALUES (0,'Su'), (1,'M'), (2,'Tu'), (3,'W'), (4,'Th'), (5,'F'), 
(6,'Sa')

), LBs(lessonBlock) AS (
    VALUES ('1-2'), ('2-3'), ('3-4'), ('4-5')
), CAL(dayDate,nextDay,dayId) AS (
    SELECT date(calStartDate,'-1 day'), date(calStartDate), -1  FROM PAR
    UNION ALL
    SELECT nextDay, date(nextDay,'+1 day'), CAST(strftime('%w',nextDay) 
AS INT) FROM CAL,PAR WHERE nextDay < calEndDate

), RES(dayDate, dayName, lessonBlock) AS (
    SELECT CAL.dayDate, DoW.dayName, LBs.lessonBlock
  FROM CAL
  CROSS JOIN LBs
  JOIN DoW ON DoW.dayID = CAL.dayId
 WHERE CAL.dayId > 0  -- No Sundays
)
SELECT *
  FROM RES


PS: There are efficiency improvements possible, but this is more to show 
the method than to be 100% processor friendly.



On 2018/03/11 2:05 PM, Csányi Pál wrote:

Hi,

is it possible to create a table from other tables with the following
conditions?

Note!
My goal is to create that table 'Hours' with a say 362 records and
after that to update those records with UPDATE sql commands. That is,
I don't want to INSERT in that table any more records, but just UPDATE
it's existing records only with new or modified informations.

The conditions are as follows.
The resulting table called Hours should look like this:

Columns are: id,date,D,lb,g,c,lp,ld,re

where D is a Day name in Week,
lb is the number of the Lesson Block hour,
g is the grade of a school class,
c is the name of the school class,
lp is LessonPlan,
ld is LessonDiary,
re is Reminder.

Example records would be like this:

1,2017-9-1,F,1-2,5,b,,,
2,2017-9-1,F,1-2,7,c,,,
3,2017-9-4,M,1-2,7,b,,,
4,2017-9-4,M,1-2,5,a,,,
5,2017-9-5,Tu,1-2,8,c,,,
6,2017-9-5,Tu,1-2,8,b,,,
7,2017-9-6,W,1-2,8,a,,,
8,2017-9-6,W,1-2,7,a,,,
9,2017-9-7,Th,1-2,6,a,,,
10,2017-9-7,Th,1-2,5,c,,,
11,2017-9-8,F,3-4,5,b,,,
12,2017-9-8,F,3-4,7,c,,,

where the last three columns have NULL values so far, because later I
want to fill those with informations by using UPDATE sql command.

So in Hours table one or more records could have the same date, Day
name in Week, Lesson Block number, but could have also same grade, and
same class name. Only id is UNIQUE in these records.

For start I will be happy to get the Hours table with proper date and
Day name in Week values out there.

Which date is proper in the Hours table should be determined by
another table in the database. Let this table be called the
'Semesters'.

However, a date value in Hours table should not be a Sunday date in the Year.

The Semesters table should have the following columns:
id,numberOfSemester, StartDay,EndDay

In a record of Hours table a date should have the Day name in the Week
which it has in the Calendar, but this behavior can be changed by
another two tables in the database; let these be called the
'TeachingSaturdays' and 'Timetable Exceptions'.

The TeachingSaturdays table should have the following columns:
id,date,D

where the value for date column  is definitely a Saturday day date,
and D could be one of these: M, Tu, W, Th, F.

The TimetableExceptions table should have the following columns:
id,date,D

where the value for D column could be one of these: M, Tu, W, Th, F.

Well, I hope I have clearly defined the problem.

Can this Hours table be created with these constraints?



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

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

[Csányi Pál]

Hi,

is it possible to create a table from other tables with the following
conditions?

Note!
My goal is to create that table 'Hours' with a say 362 records and
after that to update those records with UPDATE sql commands. That is,
I don't want to INSERT in that table any more records, but just UPDATE
it's existing records only with new or modified informations.

The conditions are as follows.
The resulting table called Hours should look like this:

Columns are: id,date,D,lb,g,c,lp,ld,re

where D is a Day name in Week,
lb is the number of the Lesson Block hour,
g is the grade of a school class,
c is the name of the school class,
lp is LessonPlan,
ld is LessonDiary,
re is Reminder.

Example records would be like this:

1,2017-9-1,F,1-2,5,b,,,
2,2017-9-1,F,1-2,7,c,,,
3,2017-9-4,M,1-2,7,b,,,
4,2017-9-4,M,1-2,5,a,,,
5,2017-9-5,Tu,1-2,8,c,,,
6,2017-9-5,Tu,1-2,8,b,,,
7,2017-9-6,W,1-2,8,a,,,
8,2017-9-6,W,1-2,7,a,,,
9,2017-9-7,Th,1-2,6,a,,,
10,2017-9-7,Th,1-2,5,c,,,
11,2017-9-8,F,3-4,5,b,,,
12,2017-9-8,F,3-4,7,c,,,

where the last three columns have NULL values so far, because later I
want to fill those with informations by using UPDATE sql command.

So in Hours table one or more records could have the same date, Day
name in Week, Lesson Block number, but could have also same grade, and
same class name. Only id is UNIQUE in these records.

For start I will be happy to get the Hours table with proper date and
Day name in Week values out there.

Which date is proper in the Hours table should be determined by
another table in the database. Let this table be called the
'Semesters'.

However, a date value in Hours table should not be a Sunday date in the Year.

The Semesters table should have the following columns:
id,numberOfSemester, StartDay,EndDay

In a record of Hours table a date should have the Day name in the Week
which it has in the Calendar, but this behavior can be changed by
another two tables in the database; let these be called the
'TeachingSaturdays' and 'Timetable Exceptions'.

The TeachingSaturdays table should have the following columns:
id,date,D

where the value for date column  is definitely a Saturday day date,
and D could be one of these: M, Tu, W, Th, F.

The TimetableExceptions table should have the following columns:
id,date,D

where the value for D column could be one of these: M, Tu, W, Th, F.

Well, I hope I have clearly defined the problem.

Can this Hours table be created with these constraints?

-- 
Best, Pali
___
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 conditionally insert a record?

[Dan Kennedy]

On 01/11/2018 12:48 PM, Shane Dev wrote:

Thanks, that works


Or, if you have a lot of data and an index on "value", this one might be 
faster:


  INSERT INTO max_value SELECT value FROM source_value ORDER BY value 
DESC LIMIT 1;







On 11 January 2018 at 06:40, Dan Kennedy  wrote:


On 01/11/2018 03:41 AM, Shane Dev wrote:


Hi Dan,

Your statement seems to insert a NULL into max_value


So it does. How about this then:

   INSERT INTO max_value SELECT max FROM (
 SELECT max(value) AS max FROM source_table
   ) WHERE EXISTS (SELECT 1 FROM source_table);

Dan.






sqlite> delete from source_table;
sqlite> delete from max_value;
sqlite> INSERT INTO max_value SELECT max(value) FROM source_table WHERE
EXISTS (SELECT 1 FROM source_table);
sqlite> select * from max_value;
max_value

sqlite>

Erik Nelson's solution works -

sqlite> delete from max_value;
sqlite> delete from source_table;
sqlite> insert into max_value select value from source_table order by
value
desc limit 1;
sqlite> select * from max_value;
sqlite> insert into source_table select 6;
sqlite> insert into max_value select value from source_table order by
value
desc limit 1;
sqlite> select * from max_value;
max_value
6
sqlite>

According to https://www.sqlite.org/lang_aggfunc.html -

max(X)

The max() aggregate function returns the maximum value of all values in
the
group. The maximum value is the value that would be returned last in an
ORDER BY on the same column. Aggregate max() returns NULL if and only if
there are no non-NULL values in the group.

despite that -

sqlite> delete from source_table;
sqlite> select * from source_table order by value;
sqlite> select max(value) from source_table;
max(value)

sqlite>

The behavior of SELECT max(X) from an empty table appears to contradict
the
documentation, or have I misunderstood something?




On 10 January 2018 at 19:38, Dan Kennedy  wrote:

On 01/10/2018 11:48 PM, Shane Dev wrote:

Hello,

sqlite> create table source_table(value);
sqlite> create table max_value(max_value);
sqlite> insert into max_value select max(value) from source_table;
sqlite> select * from table_max_value;
max_value

sqlite>


How can the maximum value of column source_table.value be inserted into
max_value only if there are records in source_table? (If source_table is
empty, nothing should be inserted into max_value, not even a NULL)

You could add a WHERE clause to your SELECT.

INSERT INTO max_value SELECT max(value) FROM src WHERE EXISTS (SELECT
1
FROM src);

Or similar.

___
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


___
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 conditionally insert a record?

[Shane Dev]

Thanks, that works

On 11 January 2018 at 06:40, Dan Kennedy  wrote:

> On 01/11/2018 03:41 AM, Shane Dev wrote:
>
>> Hi Dan,
>>
>> Your statement seems to insert a NULL into max_value
>>
>
> So it does. How about this then:
>
>   INSERT INTO max_value SELECT max FROM (
> SELECT max(value) AS max FROM source_table
>   ) WHERE EXISTS (SELECT 1 FROM source_table);
>
> Dan.
>
>
>
>
>
>> sqlite> delete from source_table;
>> sqlite> delete from max_value;
>> sqlite> INSERT INTO max_value SELECT max(value) FROM source_table WHERE
>> EXISTS (SELECT 1 FROM source_table);
>> sqlite> select * from max_value;
>> max_value
>>
>> sqlite>
>>
>> Erik Nelson's solution works -
>>
>> sqlite> delete from max_value;
>> sqlite> delete from source_table;
>> sqlite> insert into max_value select value from source_table order by
>> value
>> desc limit 1;
>> sqlite> select * from max_value;
>> sqlite> insert into source_table select 6;
>> sqlite> insert into max_value select value from source_table order by
>> value
>> desc limit 1;
>> sqlite> select * from max_value;
>> max_value
>> 6
>> sqlite>
>>
>> According to https://www.sqlite.org/lang_aggfunc.html -
>>
>> max(X)
>>
>> The max() aggregate function returns the maximum value of all values in
>> the
>> group. The maximum value is the value that would be returned last in an
>> ORDER BY on the same column. Aggregate max() returns NULL if and only if
>> there are no non-NULL values in the group.
>>
>> despite that -
>>
>> sqlite> delete from source_table;
>> sqlite> select * from source_table order by value;
>> sqlite> select max(value) from source_table;
>> max(value)
>>
>> sqlite>
>>
>> The behavior of SELECT max(X) from an empty table appears to contradict
>> the
>> documentation, or have I misunderstood something?
>>
>>
>>
>>
>> On 10 January 2018 at 19:38, Dan Kennedy  wrote:
>>
>> On 01/10/2018 11:48 PM, Shane Dev wrote:
>>>
>>> Hello,

 sqlite> create table source_table(value);
 sqlite> create table max_value(max_value);
 sqlite> insert into max_value select max(value) from source_table;
 sqlite> select * from table_max_value;
 max_value

 sqlite>


 How can the maximum value of column source_table.value be inserted into
 max_value only if there are records in source_table? (If source_table is
 empty, nothing should be inserted into max_value, not even a NULL)

 You could add a WHERE clause to your SELECT.
>>>
>>>INSERT INTO max_value SELECT max(value) FROM src WHERE EXISTS (SELECT
>>> 1
>>> FROM src);
>>>
>>> Or similar.
>>>
>>> ___
>>> 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
>
___
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 conditionally insert a record?

[Shane Dev]

Hi Peter,

Your solution quite simple and obvious in hindsight. Just to be clear - I
am using the sqlite3 shell exclusively at the moment and only I post
questions when I am stuck with a problem or observe behavior which appears
not to be documented. I appreciate the time spent by yourself and other
list members answering my questions.

On 11 January 2018 at 00:21, petern  wrote:

> Functions and aggregates have to return a scalar value or NULL.  Please
> recall functions as a basic concept from early high school:
> https://en.wikipedia.org/wiki/Function
>
> The query below will never return a NULL max(value) row from the
> empty_table table:
>
> WITH empty_table(value) AS (SELECT 1 WHERE 0) SELECT * FROM (SELECT
> max(value)max_value FROM empty_table) WHERE max_value NOT NULL;
>
> Also, INSERTing zero rows is conditioned by having no rows in the source
> SELECT like the following pattern:
>
> INSERT INTO ...  SELECT ... FROM ... WHERE   rows>
>
> Shane.  I encourage you experiment on your own in the shell of SQLite to
> improve your intuition about SQL.  Everything isn't a special case to be
> learned by rote.   There are are a few general patterns that, once
> mastered, do explain what to expect most of the time.
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> On Wed, Jan 10, 2018 at 1:20 PM, Shane Dev  wrote:
>
> > Hi Ryan,
> >
> > Nice! I have never used IGNORE before.
> >
> > Would you agree the documentation is wrong for the case of SELECT max(X)
> > FROM [an empty table or subquery]?
> >
> > max(X)
> >
> > The max() aggregate function returns the maximum value of all values in
> the
> > group. The maximum value is the value that would be returned last in an
> > ORDER BY on the same column. Aggregate max() returns NULL if and only if
> > there are no non-NULL values in the group.
> >
> > https://www.sqlite.org/lang_aggfunc.html
> >
> >
> > On 10 January 2018 at 21:44, R Smith  wrote:
> >
> > > Perhaps like this:
> > >
> > >   -- SQLite version 3.20.1  [ Release: 2017-08-24 ]  on SQLitespeed
> > > version 2.0.2.4.
> > >   -- Script Items: 4  Parameter Count: 0
> > >   -- 
> > > 
> > >
> > > create table source_table(value);
> > >
> > > create table max_value(max_value NOT NULL);
> > >
> > > insert OR IGNORE into max_value select max(value) from source_table;
> > >
> > > select * from max_value;
> > >
> > >
> > >   --   Script Stats: Total Script Execution Time: 0d 00h 00m and
> > > 00.031s
> > >
> > >
> > >
> > > On 2018/01/10 6:48 PM, Shane Dev wrote:
> > >
> > >> Hello,
> > >>
> > >> sqlite> create table source_table(value);
> > >> sqlite> create table max_value(max_value);
> > >> sqlite> insert into max_value select max(value) from source_table;
> > >> sqlite> select * from table_max_value;
> > >> max_value
> > >>
> > >> sqlite>
> > >>
> > >>
> > >> How can the maximum value of column source_table.value be inserted
> into
> > >> max_value only if there are records in source_table? (If source_table
> is
> > >> empty, nothing should be inserted into max_value, not even a NULL)
> > >> ___
> > >> 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
> >
> ___
> 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 conditionally insert a record?

[Dan Kennedy]

On 01/11/2018 03:41 AM, Shane Dev wrote:

Hi Dan,

Your statement seems to insert a NULL into max_value


So it does. How about this then:

  INSERT INTO max_value SELECT max FROM (
SELECT max(value) AS max FROM source_table
  ) WHERE EXISTS (SELECT 1 FROM source_table);

Dan.






sqlite> delete from source_table;
sqlite> delete from max_value;
sqlite> INSERT INTO max_value SELECT max(value) FROM source_table WHERE
EXISTS (SELECT 1 FROM source_table);
sqlite> select * from max_value;
max_value

sqlite>

Erik Nelson's solution works -

sqlite> delete from max_value;
sqlite> delete from source_table;
sqlite> insert into max_value select value from source_table order by value
desc limit 1;
sqlite> select * from max_value;
sqlite> insert into source_table select 6;
sqlite> insert into max_value select value from source_table order by value
desc limit 1;
sqlite> select * from max_value;
max_value
6
sqlite>

According to https://www.sqlite.org/lang_aggfunc.html -

max(X)

The max() aggregate function returns the maximum value of all values in the
group. The maximum value is the value that would be returned last in an
ORDER BY on the same column. Aggregate max() returns NULL if and only if
there are no non-NULL values in the group.

despite that -

sqlite> delete from source_table;
sqlite> select * from source_table order by value;
sqlite> select max(value) from source_table;
max(value)

sqlite>

The behavior of SELECT max(X) from an empty table appears to contradict the
documentation, or have I misunderstood something?




On 10 January 2018 at 19:38, Dan Kennedy  wrote:


On 01/10/2018 11:48 PM, Shane Dev wrote:


Hello,

sqlite> create table source_table(value);
sqlite> create table max_value(max_value);
sqlite> insert into max_value select max(value) from source_table;
sqlite> select * from table_max_value;
max_value

sqlite>


How can the maximum value of column source_table.value be inserted into
max_value only if there are records in source_table? (If source_table is
empty, nothing should be inserted into max_value, not even a NULL)


You could add a WHERE clause to your SELECT.

   INSERT INTO max_value SELECT max(value) FROM src WHERE EXISTS (SELECT 1
FROM src);

Or similar.

___
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] Is it possible to conditionally insert a record?

[petern]

Functions and aggregates have to return a scalar value or NULL.  Please
recall functions as a basic concept from early high school:
https://en.wikipedia.org/wiki/Function

The query below will never return a NULL max(value) row from the
empty_table table:

WITH empty_table(value) AS (SELECT 1 WHERE 0) SELECT * FROM (SELECT
max(value)max_value FROM empty_table) WHERE max_value NOT NULL;

Also, INSERTing zero rows is conditioned by having no rows in the source
SELECT like the following pattern:

INSERT INTO ...  SELECT ... FROM ... WHERE  

Shane.  I encourage you experiment on your own in the shell of SQLite to
improve your intuition about SQL.  Everything isn't a special case to be
learned by rote.   There are are a few general patterns that, once
mastered, do explain what to expect most of the time.
















On Wed, Jan 10, 2018 at 1:20 PM, Shane Dev  wrote:

> Hi Ryan,
>
> Nice! I have never used IGNORE before.
>
> Would you agree the documentation is wrong for the case of SELECT max(X)
> FROM [an empty table or subquery]?
>
> max(X)
>
> The max() aggregate function returns the maximum value of all values in the
> group. The maximum value is the value that would be returned last in an
> ORDER BY on the same column. Aggregate max() returns NULL if and only if
> there are no non-NULL values in the group.
>
> https://www.sqlite.org/lang_aggfunc.html
>
>
> On 10 January 2018 at 21:44, R Smith  wrote:
>
> > Perhaps like this:
> >
> >   -- SQLite version 3.20.1  [ Release: 2017-08-24 ]  on SQLitespeed
> > version 2.0.2.4.
> >   -- Script Items: 4  Parameter Count: 0
> >   -- 
> > 
> >
> > create table source_table(value);
> >
> > create table max_value(max_value NOT NULL);
> >
> > insert OR IGNORE into max_value select max(value) from source_table;
> >
> > select * from max_value;
> >
> >
> >   --   Script Stats: Total Script Execution Time: 0d 00h 00m and
> > 00.031s
> >
> >
> >
> > On 2018/01/10 6:48 PM, Shane Dev wrote:
> >
> >> Hello,
> >>
> >> sqlite> create table source_table(value);
> >> sqlite> create table max_value(max_value);
> >> sqlite> insert into max_value select max(value) from source_table;
> >> sqlite> select * from table_max_value;
> >> max_value
> >>
> >> sqlite>
> >>
> >>
> >> How can the maximum value of column source_table.value be inserted into
> >> max_value only if there are records in source_table? (If source_table is
> >> empty, nothing should be inserted into max_value, not even a NULL)
> >> ___
> >> 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
>
___
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 conditionally insert a record?

[Shane Dev]

Hi Ryan,

Nice! I have never used IGNORE before.

Would you agree the documentation is wrong for the case of SELECT max(X)
FROM [an empty table or subquery]?

max(X)

The max() aggregate function returns the maximum value of all values in the
group. The maximum value is the value that would be returned last in an
ORDER BY on the same column. Aggregate max() returns NULL if and only if
there are no non-NULL values in the group.

https://www.sqlite.org/lang_aggfunc.html


On 10 January 2018 at 21:44, R Smith  wrote:

> Perhaps like this:
>
>   -- SQLite version 3.20.1  [ Release: 2017-08-24 ]  on SQLitespeed
> version 2.0.2.4.
>   -- Script Items: 4  Parameter Count: 0
>   -- 
> 
>
> create table source_table(value);
>
> create table max_value(max_value NOT NULL);
>
> insert OR IGNORE into max_value select max(value) from source_table;
>
> select * from max_value;
>
>
>   --   Script Stats: Total Script Execution Time: 0d 00h 00m and
> 00.031s
>
>
>
> On 2018/01/10 6:48 PM, Shane Dev wrote:
>
>> Hello,
>>
>> sqlite> create table source_table(value);
>> sqlite> create table max_value(max_value);
>> sqlite> insert into max_value select max(value) from source_table;
>> sqlite> select * from table_max_value;
>> max_value
>>
>> sqlite>
>>
>>
>> How can the maximum value of column source_table.value be inserted into
>> max_value only if there are records in source_table? (If source_table is
>> empty, nothing should be inserted into max_value, not even a NULL)
>> ___
>> 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] Is it possible to conditionally insert a record?

[R Smith]

Perhaps like this:

  -- SQLite version 3.20.1  [ Release: 2017-08-24 ]  on SQLitespeed 
version 2.0.2.4.

  -- Script Items: 4  Parameter Count: 0
  -- 



create table source_table(value);

create table max_value(max_value NOT NULL);

insert OR IGNORE into max_value select max(value) from source_table;

select * from max_value;


  --   Script Stats: Total Script Execution Time: 0d 00h 00m and 
00.031s




On 2018/01/10 6:48 PM, Shane Dev wrote:

Hello,

sqlite> create table source_table(value);
sqlite> create table max_value(max_value);
sqlite> insert into max_value select max(value) from source_table;
sqlite> select * from table_max_value;
max_value

sqlite>


How can the maximum value of column source_table.value be inserted into
max_value only if there are records in source_table? (If source_table is
empty, nothing should be inserted into max_value, not even a NULL)
___
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 conditionally insert a record?

[Shane Dev]

Hi Dan,

Your statement seems to insert a NULL into max_value

sqlite> delete from source_table;
sqlite> delete from max_value;
sqlite> INSERT INTO max_value SELECT max(value) FROM source_table WHERE
EXISTS (SELECT 1 FROM source_table);
sqlite> select * from max_value;
max_value

sqlite>

Erik Nelson's solution works -

sqlite> delete from max_value;
sqlite> delete from source_table;
sqlite> insert into max_value select value from source_table order by value
desc limit 1;
sqlite> select * from max_value;
sqlite> insert into source_table select 6;
sqlite> insert into max_value select value from source_table order by value
desc limit 1;
sqlite> select * from max_value;
max_value
6
sqlite>

According to https://www.sqlite.org/lang_aggfunc.html -

max(X)

The max() aggregate function returns the maximum value of all values in the
group. The maximum value is the value that would be returned last in an
ORDER BY on the same column. Aggregate max() returns NULL if and only if
there are no non-NULL values in the group.

despite that -

sqlite> delete from source_table;
sqlite> select * from source_table order by value;
sqlite> select max(value) from source_table;
max(value)

sqlite>

The behavior of SELECT max(X) from an empty table appears to contradict the
documentation, or have I misunderstood something?




On 10 January 2018 at 19:38, Dan Kennedy  wrote:

> On 01/10/2018 11:48 PM, Shane Dev wrote:
>
>> Hello,
>>
>> sqlite> create table source_table(value);
>> sqlite> create table max_value(max_value);
>> sqlite> insert into max_value select max(value) from source_table;
>> sqlite> select * from table_max_value;
>> max_value
>>
>> sqlite>
>>
>>
>> How can the maximum value of column source_table.value be inserted into
>> max_value only if there are records in source_table? (If source_table is
>> empty, nothing should be inserted into max_value, not even a NULL)
>>
>
> You could add a WHERE clause to your SELECT.
>
>   INSERT INTO max_value SELECT max(value) FROM src WHERE EXISTS (SELECT 1
> FROM src);
>
> Or similar.
>
> ___
> 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 conditionally insert a record?

[Dan Kennedy]

On 01/10/2018 11:48 PM, Shane Dev wrote:

Hello,

sqlite> create table source_table(value);
sqlite> create table max_value(max_value);
sqlite> insert into max_value select max(value) from source_table;
sqlite> select * from table_max_value;
max_value

sqlite>


How can the maximum value of column source_table.value be inserted into
max_value only if there are records in source_table? (If source_table is
empty, nothing should be inserted into max_value, not even a NULL)


You could add a WHERE clause to your SELECT.

  INSERT INTO max_value SELECT max(value) FROM src WHERE EXISTS (SELECT 
1 FROM src);


Or similar.

___
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 conditionally insert a record?

[Nelson, Erik - 2]

Can you use something along the lines of this?

insert into max_value select value as "value" from source_table order by value 
desc limit 1;

Shane Dev Sent: Wednesday, January 10, 2018 11:49 AM
To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Subject: [sqlite] Is it possible to conditionally insert a record?

Hello,

sqlite> create table source_table(value);
sqlite> create table max_value(max_value);
sqlite> insert into max_value select max(value) from source_table;
sqlite> select * from table_max_value;
max_value

sqlite>


How can the maximum value of column source_table.value be inserted into
max_value only if there are records in source_table? (If source_table is
empty, nothing should be inserted into max_value, not even a NULL)

--
This message, and any attachments, is for the intended recipient(s) only, may 
contain information that is privileged, confidential and/or proprietary and 
subject to important terms and conditions available at 
http://www.bankofamerica.com/emaildisclaimer.   If you are not the intended 
recipient, please delete this message.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Is it possible to conditionally insert a record?

[Shane Dev]

Hello,

sqlite> create table source_table(value);
sqlite> create table max_value(max_value);
sqlite> insert into max_value select max(value) from source_table;
sqlite> select * from table_max_value;
max_value

sqlite>


How can the maximum value of column source_table.value be inserted into
max_value only if there are records in source_table? (If source_table is
empty, nothing should be inserted into max_value, not even a NULL)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Re: Possible User Defined Function (UDF) Bug?

[Richard Hipp]

On 12/5/17, no...@null.net  wrote:
> On Tue Nov 28, 2017 at 03:30:54PM +, David Raymond wrote:
>
> SQLite developers, do you recognise this thread as an issue?

Not a serious issue, no.  I might look into it when I have time, but
I'm neck-deep in other issues at the moment.

-- 
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] [EXTERNAL] Re: Possible User Defined Function (UDF) Bug?

[nomad]

On Tue Nov 28, 2017 at 03:30:54PM +, David Raymond wrote:
> With an integer primary key, not just any primary key. Probably
> something to do with the deterministic flag as well. Looks like in
> checking if it's gonna be a good integer for a rowid it calls it
> twice. Below you can see where random() gets called twice, so what
> the before trigger inserts into another table is different than what
> gets put into the original table. If you have a deterministic
> function like abs() it just calls it once, or if you have a
> non-integer-primary-key table it just calls it once.
> 
> 
> sqlite> create table d (id integer primary key);
> 
> sqlite> create table d2 (id int primary key);
> 
> sqlite> create table t (tableName text, id int);
> 
> sqlite> create trigger bi_d before insert on d begin insert into t values 
> ('d', new.id); end;
> 
> sqlite> create trigger bi_d2 before insert on d2 begin insert into t values 
> ('d2', new.id); end;
> 
> sqlite> insert into d values (random());
> 
> sqlite> insert into d2 values (random());
> 
> sqlite> select * from t;
> tableName|id
> d|-5810358455625904630
> d2|-3456845157187719103
> 
> sqlite> select * from d;
> id
> 6606271909038536929
> 
> sqlite> select * from d2;
> id
> -3456845157187719103

That looks like a good test case to me. This is certainly a regression
as earlier versions of SQLite don't show the same behaviour.

SQLite developers, do you recognise this thread as an issue? I've seen
other topics come and go and be fixed in the meantime, but no statement
on this one.

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

Re: [sqlite] [EXTERNAL] Re: Possible User Defined Function (UDF) Bug?

[David Raymond]

10  key=r[3]
17MakeRecord 2 1 10   00  r[10]=mkrec(r[2])
18Insert 0 101 d2 39  intkey=r[1] data=r[10]
19Halt   0 0 000
20Transaction0 1 390  01  usesStmtJournal=1
21Goto   0 1 000
0 Init   0 1 0 -- TRIGGER bi_d2  00  Start at 1; Start: 
bi_d2.default (BEFORE INSERT ON d2)
1 OpenWrite  0 2 0 2  00  root=2 iDb=0; t
2 NewRowid   0 1 000  r[1]=rowid
3 String80 2 0 d2 00  r[2]='d2'
4 Param  3 3 000  new.id -> $3
5 MakeRecord 2 2 4 BD 00  r[4]=mkrec(r[2..3])
6 Insert 0 4 1 t  39  intkey=r[1] data=r[4]
7 ResetCount 0 0 000
8 Halt   0 0 000  End: bi_d2.default

sqlite> explain insert into d values (abs(1));
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Init   0 20000  Start at 20
1 OpenWrite  0 3 0 1  00  root=3 iDb=0; d
2 SCopy  5 3 000  r[3]=r[5]
3 NotNull3 5 000  if r[3]!=NULL goto 5
4 Integer-13 000  r[3]=-1
5 MustBeInt  3 0 000
6 SCopy  5 4 000  r[4]=r[5]
7 Copy   4 6 000  r[6]=r[4]
8 Affinity   4 1 0 D  00  affinity(r[4])
9 Program1 197 program01  Call: bi_d.default
10Copy   6 1 000  r[1]=r[6]
11NotNull1 13000  if r[1]!=NULL goto 13
12NewRowid   0 1 000  r[1]=rowid
13MustBeInt  1 0 000
14SoftNull   2 0 000  r[2]=NULL
15NotExists  0 17100  intkey=r[1]
16Halt   1555  2 0 d.id   02
17MakeRecord 2 1 8 D  00  r[8]=mkrec(r[2])
18Insert 0 8 1 d  31  intkey=r[1] data=r[8]
19Halt   0 0 000
20Transaction0 1 390  01  usesStmtJournal=1
21Integer1 9 000  r[9]=1
22Function0  1 9 5 abs(1) 01  r[5]=func(r[9])
23Goto   0 1 000
0 Init   0 1 0 -- TRIGGER bi_d  00  Start at 1; Start: 
bi_d.default (BEFORE INSERT ON d)
1 OpenWrite  0 2 0 2  00  root=2 iDb=0; t
2 NewRowid   0 1 000  r[1]=rowid
3 String80 2 0 d  00  r[2]='d'
4 Param  2 3 000  new.rowid -> $3
5 MakeRecord 2 2 4 BD 00  r[4]=mkrec(r[2..3])
6 Insert 0 4 1 t  39  intkey=r[1] data=r[4]
7 ResetCount 0 0 000
8 Halt   0 0 000  End: bi_d.default

sqlite>


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Hick Gunter
Sent: Tuesday, November 28, 2017 9:35 AM
To: 'SQLite mailing list'
Subject: Re: [sqlite] [EXTERNAL] Re: Possible User Defined Function (UDF) Bug?

I suspect that the udf() function is called once to build the record and once 
again to build the parameter list for the trigger program.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von no...@null.net
Gesendet: Dienstag, 28. November 2017 15:04
An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Betreff: [EXTERNAL] Re: [sqlite] Possible User Defined Function (UDF) Bug?

Here is a trimmed-down test case for my issue:

CREATE TABLE d (
id INTEGER NOT NULL PRIMARY KEY
);


CREATE TRIGGER
bi_d
BEFORE INSERT ON
d
FOR EACH ROW
BEGIN
select 1;
END;


INSERT INTO
d
VALUES(
udf(1)
);

VDBE Program Listing:
   0 Init 0   200   00 Start at 20
   1 OpenWrite020 1 00 root=2 iDb=0; d
   2 Function0153 udf(1)01 r[3]=func(r[5])
   3 NotNull  350   

Re: [sqlite] [EXTERNAL] Re: Possible User Defined Function (UDF) Bug?

[Hick Gunter]

I suspect that the udf() function is called once to build the record and once 
again to build the parameter list for the trigger program.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von no...@null.net
Gesendet: Dienstag, 28. November 2017 15:04
An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Betreff: [EXTERNAL] Re: [sqlite] Possible User Defined Function (UDF) Bug?

Here is a trimmed-down test case for my issue:

CREATE TABLE d (
id INTEGER NOT NULL PRIMARY KEY
);


CREATE TRIGGER
bi_d
BEFORE INSERT ON
d
FOR EACH ROW
BEGIN
select 1;
END;


INSERT INTO
d
VALUES(
udf(1)
);

VDBE Program Listing:
   0 Init 0   200   00 Start at 20
   1 OpenWrite020 1 00 root=2 iDb=0; d
   2 Function0153 udf(1)01 r[3]=func(r[5])
   3 NotNull  350   00 if r[3]!=NULL goto 5
   4 Integer -130   00 r[3]=-1
   5 MustBeInt300   00
   6 Function0164 udf(1)01 r[4]=func(r[6])
   7 Copy 470   00 r[7]=r[4]
   8 Affinity 410 D 00 affinity(r[4])
   9 Program  1   198 program   00 Call: bi_d.default
  10 Copy 710   00 r[1]=r[7]
  11 NotNull  1   130   00 if r[1]!=NULL goto 13
  12 NewRowid 010   00 r[1]=rowid
  13 MustBeInt100   00
  14 SoftNull 200   00 r[2]=NULL
  15 NotExists0   171   00 intkey=r[1]
  16 Halt  155520 d.id  02
  17 MakeRecord   219 D 00 r[9]=mkrec(r[2])
  18 Insert   091 d 31 intkey=r[1] data=r[9]
  19 Halt 000   00
  20 Transaction  012 0 01 usesStmtJournal=1
  21 TableLock021 d 00 iDb=0 root=2 write=1
  22 Integer  150   00 r[5]=1
  23 Integer  160   00 r[6]=1
  24 Goto 010   00

Note the two calls to Function0 in the above. If you create a udf() function 
that prints a message you will see that it does in fact get called twice.

I only see this happening when all three of the following statements are true:

- The UDF is used in a VALUES() statement
- The destination table "d" has a PRIMARY KEY
- There is a BEFORE INSERT trigger on "d"

--
Mark Lawrence
___
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

Re: [sqlite] Is it possible to create the Stored Procedure (SP) in Sqlite?

[Hick Gunter]

Maybe Prakash Premkumar <prakash.p...@gmail.com> or Sairam Gaddam 
<gaddamsai...@gmail.com>, who seemed hell bent on implementing stored 
procedures (or at least storing generated bytecode) about two years ago, have 
made progress in the meantime?

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Clemens Ladisch
Gesendet: Donnerstag, 20. April 2017 09:38
An: sqlite-users@mailinglists.sqlite.org
Betreff: Re: [sqlite] Is it possible to create the Stored Procedure (SP) in 
Sqlite?

Olivier Mascia wrote:
> As far as I understood, SQLite will parse and compile the trigger text
> as part of each statement using them.  No bytecode compilation
> upfront, nor storage of it.

SQLite parses all triggers (and all other schema objects) when it reads the 
schema (see "struct Trigger" and "struct TriggerStep" in the source).
However, the bytecode for them is generated only when the actual query is 
prepared (see "struct TriggerPrg" and "struct SubProgram").


Regards,
Clemens
___
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
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


___
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 the Stored Procedure (SP) in Sqlite?

[Clemens Ladisch]

Olivier Mascia wrote:
> As far as I understood, SQLite will parse and compile the trigger text
> as part of each statement using them.  No bytecode compilation upfront,
> nor storage of it.

SQLite parses all triggers (and all other schema objects) when it reads
the schema (see "struct Trigger" and "struct TriggerStep" in the source).
However, the bytecode for them is generated only when the actual query
is prepared (see "struct TriggerPrg" and "struct SubProgram").


Regards,
Clemens
___
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 the Stored Procedure (SP) in Sqlite?

[Olivier Mascia]

> Le 20 avr. 2017 à 01:13, petern  a écrit :
> 
> 2. Here is a question.  It would be helpful to know if TRIGGERs are stored
> as prepared SQLite byte code or not.  What does the SQLite engine do
> exactly?  Anybody?

I'm answering to test my understanding, confronting it to more knowledgeable 
people here on this list.  As far as I understood, SQLite will parse and 
compile the trigger text as part of each statement using them.  No bytecode 
compilation upfront, nor storage of it.  And that is fine by me, well in line 
with the design goals of SQLite.

Please correct me as needed.

-- 
Best Regards, Meilleures salutations, Met vriendelijke groeten,
Olivier Mascia, http://integral.software


___
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 the Stored Procedure (SP) in Sqlite?

[petern]

1. Forgot to mention. In cases where the WHEN clause is not convenient for
trigger style stored procedure condition branching there is also "SELECT
raise(ignore) WHERE ":

https://sqlite.org/lang_createtrigger.html#raise

2. Here is a question.  It would be helpful to know if TRIGGERs are stored
as prepared SQLite byte code or not.  What does the SQLite engine do
exactly?  Anybody?

If CREATE TRIGGER produces prepared byte code, then TRIGGER programs are
not equivalent to making your own table of stored procedures in TEXT
columns that have to be loaded by external code which repeatedly issues the
prepare statement call.

3. For variables compare, "UPDATE mysproc_worktable SET name='John'" with
"LET @name='John'".   The difference amounts to a lack of imagination.

4. Those requiring loop constructs should consider that TRIGGERs are
re-entrant and can be called recursively.  Any loop can be written as a
recursive call.

5. Recursive CTE's are also available to directly generate/populate the
result columns of the worktable.

In short, SQLite has a fairly complete defacto stored procedure capability
that many could benefit from. But either for lack of a direct CREATE
PROCEDURE statement or a profound lack of imagination, many will never use
it.



On Wed, Apr 19, 2017 at 2:22 PM, R Smith  wrote:

>
>
> On 2017/04/19 6:58 PM, James K. Lowden wrote:
>
>> On Sun, 16 Apr 2017 12:01:01 +0200
>> Darko Volaric  wrote:
>>
>> There are good reasons to have stored procedures other than reducing
>>> connection latency - developers like to encapsulate logic that is
>>> associated entirely with the database in the database, use them to do
>>> extended checking, to populate denormalized or derived data, or to
>>> provide a level of abstraction, for instance.
>>>
>> Exactly so.  A stored procedure can serve the same purpose as a
>> function in C: to assign a name to a particular body of code.
>>
>> But the same effect can be had in SQLite without stored procedures per
>> se.  In a few projects I've used the build repository to accomplish
>> much the same thing.//
>>
>
> Indeed so, and I've had good success using a similar principle by simply
> storing those SQL "files" as simple TEXT column in a table named
> "StoredProcs" in any DB with a trivial step in the program to execute it
> when needed - thus truly having "Stored Procedures" by virtue of placement.
>
> However, I believe the main motivation of the requests do not intend the
> placement of the procedures so much as the character thereof - They do not
> care whether it is stored in a Trigger, File or Table, I believe the real
> request is for a system of assignable variables ( LET @Name = 'John'; )
> which could also be used as parameters in a query, or maybe assignable
> datasets ( #TmpResult = Query('...'); ) - perhaps even traversable datasets
> ( for each @Row in Query('...') do { ... DoSomethingWith( @Row.Name ); ...
> } ) and next will be flow control ( IF (thisIsTrue) BEGIN doThat(); END ).
>
> Once we start on this road, ALL those will become wanted - all of which
> are great, but probably outside the spirit of SQ"Lite".
>
> (Note: I'm not advocating against. I myself am on the fence - using SQLite
> so much and never in a size-sensitive anything, so it would be a boon to
> have proper procedural execution within, but a "general target audience" I
> don't make.)
>
> ___
> 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 the Stored Procedure (SP) in Sqlite?

[R Smith]

On 2017/04/19 6:58 PM, James K. Lowden wrote:

On Sun, 16 Apr 2017 12:01:01 +0200
Darko Volaric  wrote:


There are good reasons to have stored procedures other than reducing
connection latency - developers like to encapsulate logic that is
associated entirely with the database in the database, use them to do
extended checking, to populate denormalized or derived data, or to
provide a level of abstraction, for instance.

Exactly so.  A stored procedure can serve the same purpose as a
function in C: to assign a name to a particular body of code.

But the same effect can be had in SQLite without stored procedures per
se.  In a few projects I've used the build repository to accomplish
much the same thing.//


Indeed so, and I've had good success using a similar principle by simply 
storing those SQL "files" as simple TEXT column in a table named 
"StoredProcs" in any DB with a trivial step in the program to execute it 
when needed - thus truly having "Stored Procedures" by virtue of placement.


However, I believe the main motivation of the requests do not intend the 
placement of the procedures so much as the character thereof - They do 
not care whether it is stored in a Trigger, File or Table, I believe the 
real request is for a system of assignable variables ( LET @Name = 
'John'; ) which could also be used as parameters in a query, or maybe 
assignable datasets ( #TmpResult = Query('...'); ) - perhaps even 
traversable datasets ( for each @Row in Query('...') do { ... 
DoSomethingWith( @Row.Name ); ... } ) and next will be flow control ( IF 
(thisIsTrue) BEGIN doThat(); END ).


Once we start on this road, ALL those will become wanted - all of which 
are great, but probably outside the spirit of SQ"Lite".


(Note: I'm not advocating against. I myself am on the fence - using 
SQLite so much and never in a size-sensitive anything, so it would be a 
boon to have proper procedural execution within, but a "general target 
audience" I don't make.)


___
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 the Stored Procedure (SP) in Sqlite?

[Simon Slavin]

On 19 Apr 2017, at 8:59pm, Domingo Alvarez Duarte  wrote:
> 
> What I understood looking at the sqlite3 sources is that an update is always 
> 3 operations:
> 
> 1- Read old row
> 
> 2- Delete old row
> 
> 3- Insert updated row
> 
> So I seems that using "insert" would be less work.

I didn’t think of that.  Your way is probably faster.

Simon.
___
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 the Stored Procedure (SP) in Sqlite?

[Domingo Alvarez Duarte]

Hello !

What I understood looking at the sqlite3 sources is that an update is 
always 3 operations:


1- Read old row

2- Delete old row

3- Insert updated row

So I seems that using "insert" would be less work.

Cheers !


On 19/04/17 16:27, Simon Slavin wrote:

On 19 Apr 2017, at 7:47pm, no...@null.net wrote:


I use
triggers quite heavily as a kind of stored procedure.

Instead of basing them on views however I use real tables and AFTER
INSERT triggers whose final statement deletes the NEW row just
inserted.

I see two benefits to the use of AFTER INSERT triggers:

* Constraints are enforced so SQLite catches invalid
"procedure calls."
* Default values for columns (or "arguments") can be defined. This
is very useful if you want to use the incoming value in multiple
statements - you don't have to hardcode a bunch of
COALESCE(NEW.col, $DEFAULT) values everywhere.

Had you considered doing UPDATE instead of INSERT ?  Leave one row in the table 
and issue an UPDATE command when you want to trigger a trigger.  If the column 
you’re changing isn’t indexed it’s a little faster.  And just like INSERT you 
can use the value you set, using CASE … END, to set what you want to happen.

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 the Stored Procedure (SP) in Sqlite?

[Simon Slavin]

On 19 Apr 2017, at 7:47pm, no...@null.net wrote:

> I use
> triggers quite heavily as a kind of stored procedure.
> 
> Instead of basing them on views however I use real tables and AFTER
> INSERT triggers whose final statement deletes the NEW row just
> inserted.
> 
> I see two benefits to the use of AFTER INSERT triggers:
> 
>* Constraints are enforced so SQLite catches invalid
>"procedure calls."
>* Default values for columns (or "arguments") can be defined. This
>is very useful if you want to use the incoming value in multiple
>statements - you don't have to hardcode a bunch of
>COALESCE(NEW.col, $DEFAULT) values everywhere.

Had you considered doing UPDATE instead of INSERT ?  Leave one row in the table 
and issue an UPDATE command when you want to trigger a trigger.  If the column 
you’re changing isn’t indexed it’s a little faster.  And just like INSERT you 
can use the value you set, using CASE … END, to set what you want to happen.

Simon.
___
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 the Stored Procedure (SP) in Sqlite?

[nomad]

On Wed Apr 19, 2017 at 09:53:07AM -0700, petern wrote:
> My sense from these replies is that nobody bothers to try using
> triggers to store their SQLite procedural code within the DB.  I was
> skeptical when I first learned of the technique but the trigger
> syntax is very computationally permissive.  Frankly, I'm still
> surprised by what one is allowed to do in a trigger.

Just to provide at least one data point in the other direction, I use
triggers quite heavily as a kind of stored procedure.

Instead of basing them on views however I use real tables and AFTER
INSERT triggers whose final statement deletes the NEW row just
inserted.

I see two benefits to the use of AFTER INSERT triggers:

* Constraints are enforced so SQLite catches invalid
"procedure calls."
* Default values for columns (or "arguments") can be defined. This
is very useful if you want to use the incoming value in multiple
statements - you don't have to hardcode a bunch of
COALESCE(NEW.col, $DEFAULT) values everywhere.

Because the INSERT/TRIGGER/DELETE happens within a transaction I expect
the data never to hit the disk. I haven't measured it but I guess the
performance would not be too far off the INSTEAD-OF/VIEW trigger.

> CREATE VIEW my_sproc_caller_view as SELECT (33)a, ('some_param')b,
> (55)c, * from my_sproc_worktable;

My own naming convention uses tables like "func_action_name".

-- 
Mark Lawrence
___
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 the Stored Procedure (SP) in Sqlite?

[James K. Lowden]

On Sun, 16 Apr 2017 12:01:01 +0200
Darko Volaric  wrote:

> There are good reasons to have stored procedures other than reducing
> connection latency - developers like to encapsulate logic that is
> associated entirely with the database in the database, use them to do
> extended checking, to populate denormalized or derived data, or to
> provide a level of abstraction, for instance. 

Exactly so.  A stored procedure can serve the same purpose as a
function in C: to assign a name to a particular body of code.  

But the same effect can be had in SQLite without stored procedures per
se.  In a few projects I've used the build repository to accomplish
much the same thing.  

Choose a directory, say, "sql" for the queries that will be used in
the application.  Each file has a name and contains one query.  In that
way, every query has a name.  A bit of awk transforms that directory
into a C source code module with a contant array of strings.  The
filenames become an enumeration that serves to index the array by name.
(A C++ std::map also works.)  Calling the "stored procedure" is a simple
matter:

sqlite3_prepare(db, sql[name], ...)

One nice feature of this approach is that testing queries is simple.
It also confines all the SQL to one module, and avoids writing queries
"in line" as C strings.  And, not for nothing, a well chosen query
name renders the code clearer than embedded SQL does.  

--jkl


___
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 the Stored Procedure (SP) in Sqlite?

[petern]

My sense from these replies is that nobody bothers to try using triggers to
store their SQLite procedural code within the DB.  I was skeptical when I
first learned of the technique but the trigger syntax is very
computationally permissive.  Frankly, I'm still surprised by what one is
allowed to do in a trigger.

My hope is that more people will use this technique and eventually a good
proposal will emerge for syntactic sugar which condenses the syntax.

Here is a more concrete example without syntax error.  Just paste into a
SQLite shell and see for yourself.

CREATE VIEW my_sproc_caller_view as SELECT (33)a, ('some_param')b, (55)c, *
from my_sproc_worktable;

CREATE TRIGGER my_sproc INSTEAD OF INSERT ON my_sproc_caller_view
BEGIN
--THE STUFF WRITTEN HERE IS STORED IN THE DB.  AKA STORED PROCEDURE.
--What can be done:
--  Do something to insert/update/delete rows from the work table.
--  Using "SELECT fn(p1,p2,p3,...);" call some extension function written
in C etc.
--  Update/insert/delete other tables according to parameters a,b,c and/or
worktable rowset.
--  Call this sproc recursively up to SQLITE_MAX_TRIGGER_DEPTH
--  Have one sproc worktable per connection by using a temp worktable.
--What can't be done? Is there some operation missing here?
--One can introduce as many parameter and variable columns as needed to do
anything whatsoever.
select * from my_sproc_worktable;
END;

CREATE TABLE my_sproc_worktable(var1 TEXT,var2
TEXT,resultCol1,resultCol2,resultColN,etc);

INSERT INTO my_sproc_caller_view(a,b,c,var1) VALUES(1,2,3,4);
INSERT INTO my_sproc_caller_view(a,b,c,var2)
VALUES(7,8,9,"and_some_other_thing");

--SEE: https://sqlite.org/lang_createtrigger.html

On Wed, Apr 19, 2017 at 6:26 AM, Domingo Alvarez Duarte 
wrote:

> Hello Philip !
>
> There was this attempt https://www.sqliteconcepts.org/PL_index.html and I
> tried to adapt to sqlite3 but the change on the sqlite3 vm compared to
> sqlite2 made it a lot harder.
>
> The vm of sqlite3 is not well documented and is changing all the time.
>
> But I also agreed with you if we could have "@variables" at connection
> level, query level, trigger level and also be able to write triggers in "C"
> (or another glue language), simple stored procedures (queries with
> parameters at sql level) life would be a bit easier.
>
> Cheers !
>
> On 19/04/17 08:34, Philip Warner wrote:
>
>> There is another reason to have stored procedures: encapsulating logic
>> across apps/clients.
>>
>> A great deal can be done in triggers, but not much in terms of queries or
>> complex parameterized updates.
>>
>> It would be great, imo, if triggers could have durable local storage (ie.
>> variables) and if this were built upon to allow stored procedures, life
>> would be much more fun.
>>
>> Parameterized multi-query SQL statements returning event just a single
>> row set would be fine.
>>
>>
>>
>> On 16/04/2017 2:18 AM, Richard Hipp wrote:
>>
>>> On 4/15/17, Manoj Sengottuvel  wrote:
>>>
 Hi Richard,

 Is it possible to create the Stored Procedure (SP) in Sqlite?

 if not , is there any alternate way for SP?

>>> Short answer:  No.
>>>
>>> Longer answer:  With SQLite, your application is the stored procedure.
>>> In a traditional client/server database like PostgreSQL or Oracle or
>>> SQL Server, every SQL statement involves a round-trip to the server.
>>> So there is a lot of latency with each command.  The way applications
>>> overcome this latency is to put many queries into a stored procedure,
>>> so that only the stored procedure invocation needs to travel over the
>>> wire and latency is reduced to a single server round-trip.
>>>
>>> But with SQLite, each statement is just a procedure call.  There is no
>>> network traffic, not IPC, and hence very little latency. Applications
>>> that use SQLite can be very "chatty" with the database and that is not
>>> a problem.  For example, the SQLite website is backed by SQLite (duh!)
>>> and a typical page request involves 200 to 300 separate queries.  That
>>> would be a performance killer with a client/server database, but with
>>> SQLite it is not a problem and the pages render in about 5
>>> milliseconds.
>>>
>>
>> ___
>> 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] Is it possible to create the Stored Procedure (SP) in Sqlite?

[Domingo Alvarez Duarte]

Hello Philip !

There was this attempt https://www.sqliteconcepts.org/PL_index.html and 
I tried to adapt to sqlite3 but the change on the sqlite3 vm compared to 
sqlite2 made it a lot harder.


The vm of sqlite3 is not well documented and is changing all the time.

But I also agreed with you if we could have "@variables" at connection 
level, query level, trigger level and also be able to write triggers in 
"C" (or another glue language), simple stored procedures (queries with 
parameters at sql level) life would be a bit easier.


Cheers !

On 19/04/17 08:34, Philip Warner wrote:
There is another reason to have stored procedures: encapsulating logic 
across apps/clients.


A great deal can be done in triggers, but not much in terms of queries 
or complex parameterized updates.


It would be great, imo, if triggers could have durable local storage 
(ie. variables) and if this were built upon to allow stored 
procedures, life would be much more fun.


Parameterized multi-query SQL statements returning event just a single 
row set would be fine.




On 16/04/2017 2:18 AM, Richard Hipp wrote:

On 4/15/17, Manoj Sengottuvel  wrote:

Hi Richard,

Is it possible to create the Stored Procedure (SP) in Sqlite?

if not , is there any alternate way for SP?

Short answer:  No.

Longer answer:  With SQLite, your application is the stored procedure.
In a traditional client/server database like PostgreSQL or Oracle or
SQL Server, every SQL statement involves a round-trip to the server.
So there is a lot of latency with each command.  The way applications
overcome this latency is to put many queries into a stored procedure,
so that only the stored procedure invocation needs to travel over the
wire and latency is reduced to a single server round-trip.

But with SQLite, each statement is just a procedure call.  There is no
network traffic, not IPC, and hence very little latency. Applications
that use SQLite can be very "chatty" with the database and that is not
a problem.  For example, the SQLite website is backed by SQLite (duh!)
and a typical page request involves 200 to 300 separate queries.  That
would be a performance killer with a client/server database, but with
SQLite it is not a problem and the pages render in about 5
milliseconds.


___
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 the Stored Procedure (SP) in Sqlite?

[Philip Warner]

There is another reason to have stored procedures: encapsulating logic across 
apps/clients.


A great deal can be done in triggers, but not much in terms of queries or 
complex parameterized updates.


It would be great, imo, if triggers could have durable local storage (ie. 
variables) and if this were built upon to allow stored procedures, life would be 
much more fun.


Parameterized multi-query SQL statements returning event just a single row set 
would be fine.




On 16/04/2017 2:18 AM, Richard Hipp wrote:

On 4/15/17, Manoj Sengottuvel  wrote:

Hi Richard,

Is it possible to create the Stored Procedure (SP) in Sqlite?

if not , is there any alternate way for SP?

Short answer:  No.

Longer answer:  With SQLite, your application is the stored procedure.
In a traditional client/server database like PostgreSQL or Oracle or
SQL Server, every SQL statement involves a round-trip to the server.
So there is a lot of latency with each command.  The way applications
overcome this latency is to put many queries into a stored procedure,
so that only the stored procedure invocation needs to travel over the
wire and latency is reduced to a single server round-trip.

But with SQLite, each statement is just a procedure call.  There is no
network traffic, not IPC, and hence very little latency.  Applications
that use SQLite can be very "chatty" with the database and that is not
a problem.  For example, the SQLite website is backed by SQLite (duh!)
and a typical page request involves 200 to 300 separate queries.  That
would be a performance killer with a client/server database, but with
SQLite it is not a problem and the pages render in about 5
milliseconds.


___
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 the Stored Procedure (SP) in Sqlite?

[Simon Slavin]

On 16 Apr 2017, at 5:27pm, Jens Alfke  wrote:

> Is this list archived anywhere convenient?

I just google for posts I remember and google usually turns up an archive of 
this list.

googling "sqlite stored procedure latency" turns up



And it seems that that archive has a search field on its homepage:



Simon.
___
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 the Stored Procedure (SP) in Sqlite?

[Jens Alfke]

> On Apr 15, 2017, at 2:17 PM, Simon Slavin  wrote:
> 
> I do agree that DRH’s explanation of why it’s not as important in SQLite as 
> in client/server engines is well written.  We can point to it when we need it.

Is this list archived anywhere convenient? Last time I tried to look for an 
earlier post, I had to dig through the Mailman interface, which wanted a 
password, which I didn’t remember, which I had to ask for by email … not super 
conducive to knowledge sharing :(

—Jens
___
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 the Stored Procedure (SP) in Sqlite?

[Tim Streater]

On 15 Apr 2017 at 22:17, Simon Slavin  wrote: 

> On 15 Apr 2017, at 9:14pm, petern  wrote:
>
>> Yes, please include it in the FAQ
>
> It’s not a FAQ.  Not on this list, at least.  I would argue against it.

Well he meant on the sqlite website. And if it's not a FAQ then it's 
nonetheless useful info, so perhaps what the sqlite website might have is an 
LFAQ page (Less Frequently Asked Questions).

--
Cheers  --  Tim
___
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 the Stored Procedure (SP) in Sqlite?

[Darko Volaric]

If you really wanted to have stored procedures and did not mind calling them 
using a function syntax, you could write your own stored procedure extension. 
You'd store them in their own table, write a custom function that evaluates 
them and call them something like this: sp("name", param1, param2,...). A 
simple recursive-decent parser would likely do the trick to parse and evaluate 
the procedures. Depending on your needs, the "stored procedures" could be as 
simple as a series of SQL statements to execute, with parameter substitutions, 
which would be almost trivial to write. If you were so inclined.

There are good reasons to have stored procedures other than reducing connection 
latency - developers like to encapsulate logic that is associated entirely with 
the database in the database, use them to do extended checking, to populate 
denormalized or derived data, or to provide a level of abstraction, for 
instance. Although this code could be put in the client side you may want to be 
able to maintain the database independently of the application or you may have 
multiple client applications and want to avoid duplicating code in multiple 
code bases, for instance.



> On Apr 15, 2017, at 3:57 PM, Manoj Sengottuvel  wrote:
> 
> Hi Richard,
> 
> Is it possible to create the Stored Procedure (SP) in Sqlite?
> 
> if not , is there any alternate way for SP?
> 
> 
> regards
> Manoj
> ___
> 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 the Stored Procedure (SP) in Sqlite?

[Simon Slavin]

On 15 Apr 2017, at 9:14pm, petern  wrote:

> Yes, please include it in the FAQ

It’s not a FAQ.  Not on this list, at least.  I would argue against it.

I do agree that DRH’s explanation of why it’s not as important in SQLite as in 
client/server engines is well written.  We can point to it when we need it.

Simon.
___
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 the Stored Procedure (SP) in Sqlite?

[petern]

Yes, please include it in the FAQ along with a description of the SQLite
stored procedure pattern syntax which is never disclosed in these replies:

CREATE TRIGGER  my_sproc INSTEAD OF INSERT on my_sproc_caller_view
BEGIN
--My procedural code to be prepared and stored in the database.
END;
--Called by the following syntax with specific VALUES():
INSERT INTO my_sproc_caller_view VALUES();
--And results returned in the requisite my_sproc_work_table upon which
my_sproc_caller_view is created.

As for the chattiness of client/server DB's, there is a more frequent cause
of that inefficient pattern.  In particular, there is often a ham fisted
design which keeps vital model data in application code or in tables which
are not SQL joined to get the final result.  Often the vendor will
encourage the same incompetent programmers to recast their inefficient
queries within the bodies of stored procedures as proof that the firm needs
to buy much larger and more expensive hardware and requisite software
licenses.  [Filed under vendor contracts for $435 hammers and $600 toilet
seats.]






On Sat, Apr 15, 2017 at 11:33 AM, Christian Werner <
christian.wer...@t-online.de> wrote:

> On 04/15/2017 06:18 PM, Richard Hipp wrote:
>
>> On 4/15/17, Manoj Sengottuvel  wrote:
>>
>>> Hi Richard,
>>>
>>> Is it possible to create the Stored Procedure (SP) in Sqlite?
>>>
>>> if not , is there any alternate way for SP?
>>>
>>
>> Short answer:  No.
>>
>> Longer answer:  With SQLite, your application is the stored procedure.
>> In a traditional client/server database like PostgreSQL or Oracle or
>> SQL Server, every SQL statement involves a round-trip to the server.
>> So there is a lot of latency with each command.  The way applications
>> overcome this latency is to put many queries into a stored procedure,
>> so that only the stored procedure invocation needs to travel over the
>> wire and latency is reduced to a single server round-trip.
>>
>> But with SQLite, each statement is just a procedure call.  There is no
>> network traffic, not IPC, and hence very little latency.  Applications
>> that use SQLite can be very "chatty" with the database and that is not
>> a problem.  For example, the SQLite website is backed by SQLite (duh!)
>> and a typical page request involves 200 to 300 separate queries.  That
>> would be a performance killer with a client/server database, but with
>> SQLite it is not a problem and the pages render in about 5
>> milliseconds.
>>
>
> May I vote this conversation to be included in the SQLite FAQ.
>
> Best,
> Christian
>
> ___
> 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 the Stored Procedure (SP) in Sqlite?

[Christian Werner]

On 04/15/2017 06:18 PM, Richard Hipp wrote:

On 4/15/17, Manoj Sengottuvel  wrote:

Hi Richard,

Is it possible to create the Stored Procedure (SP) in Sqlite?

if not , is there any alternate way for SP?


Short answer:  No.

Longer answer:  With SQLite, your application is the stored procedure.
In a traditional client/server database like PostgreSQL or Oracle or
SQL Server, every SQL statement involves a round-trip to the server.
So there is a lot of latency with each command.  The way applications
overcome this latency is to put many queries into a stored procedure,
so that only the stored procedure invocation needs to travel over the
wire and latency is reduced to a single server round-trip.

But with SQLite, each statement is just a procedure call.  There is no
network traffic, not IPC, and hence very little latency.  Applications
that use SQLite can be very "chatty" with the database and that is not
a problem.  For example, the SQLite website is backed by SQLite (duh!)
and a typical page request involves 200 to 300 separate queries.  That
would be a performance killer with a client/server database, but with
SQLite it is not a problem and the pages render in about 5
milliseconds.


May I vote this conversation to be included in the SQLite FAQ.

Best,
Christian

___
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 the Stored Procedure (SP) in Sqlite?

[Richard Hipp]

On 4/15/17, Manoj Sengottuvel  wrote:
> Hi Richard,
>
> Is it possible to create the Stored Procedure (SP) in Sqlite?
>
> if not , is there any alternate way for SP?

Short answer:  No.

Longer answer:  With SQLite, your application is the stored procedure.
In a traditional client/server database like PostgreSQL or Oracle or
SQL Server, every SQL statement involves a round-trip to the server.
So there is a lot of latency with each command.  The way applications
overcome this latency is to put many queries into a stored procedure,
so that only the stored procedure invocation needs to travel over the
wire and latency is reduced to a single server round-trip.

But with SQLite, each statement is just a procedure call.  There is no
network traffic, not IPC, and hence very little latency.  Applications
that use SQLite can be very "chatty" with the database and that is not
a problem.  For example, the SQLite website is backed by SQLite (duh!)
and a typical page request involves 200 to 300 separate queries.  That
would be a performance killer with a client/server database, but with
SQLite it is not a problem and the pages render in about 5
milliseconds.
-- 
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] Is it possible to create the Stored Procedure (SP) in Sqlite?

[Manoj Sengottuvel]

Hi Richard,

Is it possible to create the Stored Procedure (SP) in Sqlite?

if not , is there any alternate way for SP?


regards
Manoj
___
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 change "synchronous" flag of ZIPVFS pager?

[Tomasz Maj]

Thank you very much. You're super quick guys :)


Cheers,

Tomasz M


From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> on behalf of 
Dan Kennedy <danielk1...@gmail.com>
Sent: Thursday, March 16, 2017 8:34:30 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] Is it possible to change "synchronous" flag of ZIPVFS 
pager?

On 03/16/2017 05:00 PM, Tomasz Maj wrote:
> I have an application which work with many zipped databases in WAL journal 
> mode and the databases are stored on a media of low write bandwidth. There is 
> a problem that that fsync operations invoked from one thread are blocking 
> other fsync operations invoked from another thread. I think that changing the 
> synchronous mode to NORMAL on some connections may make the blocking issue 
> less painful. May the possibility of changing ZIPVFS synchronous mode be 
> implemented in sqlite?

Latest commit in the zipvfs project fixes things so that "PRAGMA
synchronous" works with zipvfs databases.

Dan.



>
>
> Cheers,
>
> Tomasz M
>
> 
> From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> on behalf 
> of Tomek Maj <majek...@gmail.com>
> Sent: Thursday, March 16, 2017 10:50:37 AM
> To: SQLite mailing list
> Subject: Re: [sqlite] Is it possible to change "synchronous" flag of ZIPVFS 
> pager?
>
> 2017-03-16 9:51 GMT+01:00 Dan Kennedy <danielk1...@gmail.com>:
>
>> On 03/16/2017 01:40 PM, Tomasz Maj wrote:
>>
>>> Hi,
>>>
>>>
>>> According to my observations, "PRAGMA synchronous=...;" query affects
>>> only the standard sqlite pager. But for zipped databases sqlite uses
>>> additional ZIPVFS pager layer which actually decide whether and when to
>>> sync content of files associated with a database. It looks like "PRAGMA
>>> synchronous=...;" query doesn't have any effect on zipped databases. Are my
>>> observations right? If so, is it possible to manipulate "synchronous" flag
>>> of ZIPVFS pager?
>>>
>> It looks like there is not. Is it something you need to do?
>>
>> Dan.
>>
>>
>> ___
>> 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


___
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 change "synchronous" flag of ZIPVFS pager?

[Dan Kennedy]

On 03/16/2017 05:00 PM, Tomasz Maj wrote:

I have an application which work with many zipped databases in WAL journal mode 
and the databases are stored on a media of low write bandwidth. There is a 
problem that that fsync operations invoked from one thread are blocking other 
fsync operations invoked from another thread. I think that changing the 
synchronous mode to NORMAL on some connections may make the blocking issue less 
painful. May the possibility of changing ZIPVFS synchronous mode be implemented 
in sqlite?


Latest commit in the zipvfs project fixes things so that "PRAGMA 
synchronous" works with zipvfs databases.


Dan.






Cheers,

Tomasz M


From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> on behalf of Tomek 
Maj <majek...@gmail.com>
Sent: Thursday, March 16, 2017 10:50:37 AM
To: SQLite mailing list
Subject: Re: [sqlite] Is it possible to change "synchronous" flag of ZIPVFS 
pager?

2017-03-16 9:51 GMT+01:00 Dan Kennedy <danielk1...@gmail.com>:


On 03/16/2017 01:40 PM, Tomasz Maj wrote:


Hi,


According to my observations, "PRAGMA synchronous=...;" query affects
only the standard sqlite pager. But for zipped databases sqlite uses
additional ZIPVFS pager layer which actually decide whether and when to
sync content of files associated with a database. It looks like "PRAGMA
synchronous=...;" query doesn't have any effect on zipped databases. Are my
observations right? If so, is it possible to manipulate "synchronous" flag
of ZIPVFS pager?


It looks like there is not. Is it something you need to do?

Dan.


___
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



___
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 change "synchronous" flag of ZIPVFS pager?

[Tomasz Maj]

I have an application which work with many zipped databases in WAL journal mode 
and the databases are stored on a media of low write bandwidth. There is a 
problem that that fsync operations invoked from one thread are blocking other 
fsync operations invoked from another thread. I think that changing the 
synchronous mode to NORMAL on some connections may make the blocking issue less 
painful. May the possibility of changing ZIPVFS synchronous mode be implemented 
in sqlite?


Cheers,

Tomasz M


From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> on behalf of 
Tomek Maj <majek...@gmail.com>
Sent: Thursday, March 16, 2017 10:50:37 AM
To: SQLite mailing list
Subject: Re: [sqlite] Is it possible to change "synchronous" flag of ZIPVFS 
pager?

2017-03-16 9:51 GMT+01:00 Dan Kennedy <danielk1...@gmail.com>:

> On 03/16/2017 01:40 PM, Tomasz Maj wrote:
>
>> Hi,
>>
>>
>> According to my observations, "PRAGMA synchronous=...;" query affects
>> only the standard sqlite pager. But for zipped databases sqlite uses
>> additional ZIPVFS pager layer which actually decide whether and when to
>> sync content of files associated with a database. It looks like "PRAGMA
>> synchronous=...;" query doesn't have any effect on zipped databases. Are my
>> observations right? If so, is it possible to manipulate "synchronous" flag
>> of ZIPVFS pager?
>>
>
> It looks like there is not. Is it something you need to do?
>
> Dan.
>
>
> ___
> 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] Is it possible to change "synchronous" flag of ZIPVFS pager?

[Tomek Maj]

2017-03-16 9:51 GMT+01:00 Dan Kennedy :

> On 03/16/2017 01:40 PM, Tomasz Maj wrote:
>
>> Hi,
>>
>>
>> According to my observations, "PRAGMA synchronous=...;" query affects
>> only the standard sqlite pager. But for zipped databases sqlite uses
>> additional ZIPVFS pager layer which actually decide whether and when to
>> sync content of files associated with a database. It looks like "PRAGMA
>> synchronous=...;" query doesn't have any effect on zipped databases. Are my
>> observations right? If so, is it possible to manipulate "synchronous" flag
>> of ZIPVFS pager?
>>
>
> It looks like there is not. Is it something you need to do?
>
> Dan.
>
>
> ___
> 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 change "synchronous" flag of ZIPVFS pager?

[Dan Kennedy]

On 03/16/2017 01:40 PM, Tomasz Maj wrote:

Hi,


According to my observations, "PRAGMA synchronous=...;" query affects only the standard sqlite 
pager. But for zipped databases sqlite uses additional ZIPVFS pager layer which actually decide whether and 
when to sync content of files associated with a database. It looks like "PRAGMA synchronous=...;" 
query doesn't have any effect on zipped databases. Are my observations right? If so, is it possible to 
manipulate "synchronous" flag of ZIPVFS pager?


It looks like there is not. Is it something you need to do?

Dan.

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

[sqlite] Is it possible to change "synchronous" flag of ZIPVFS pager?

[Tomasz Maj]

Hi,


According to my observations, "PRAGMA synchronous=...;" query affects only the 
standard sqlite pager. But for zipped databases sqlite uses additional ZIPVFS 
pager layer which actually decide whether and when to sync content of files 
associated with a database. It looks like "PRAGMA synchronous=...;" query 
doesn't have any effect on zipped databases. Are my observations right? If so, 
is it possible to manipulate "synchronous" flag of ZIPVFS pager?


Cheers,

Tomasz M

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