Re: [sqlite] [EXTERNAL] How to Save NULL data to DB

[Hick Gunter]

If you store NULL then SQLite will return NULL. Maybe your presentation layer 
is converting NULL to text.

The literal NULL is used in the query text, or you can call sqlite3_bind_null() 
for a parameter or sqlite3_result_null() to set the result of a user written 
function.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Prajeesh Prakash
Gesendet: Mittwoch, 05. Dezember 2018 09:35
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] How to Save NULL data to DB

Hi Team,

How to save NULL parameter into the DB. I saw something like (null) on table 
but when i am reading from the table it popup as a string not the NULL value.
___
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] Suitability for Macintosh OS 9.2?

[Charles Hudson]

Thank you for your reply.  I have SQLite running on a dual-boot
Win7/Fedora28 machine, and I have already investigated FMPro v.2,3 and
5.  I have not tried installing any version of OS X, however; that will
be my next avenue of exploration.

Thanks once again for your assistance.

-CH-

On Tue, 2018-12-04 at 17:40 -0600, Ryan Schmidt wrote:
> On Nov 19, 2018, at 07:58, Charles Hudson wrote:
> 
> > I come from an ANSI SQL client / server background (Oracle, MS SQL)
> > but am
> > interested in finding a SQL database to install on an old Macintosh
> > G3 Power
> > PC that is running OS 9.2.  I don't need network connectivity as
> > this would
> > be limited to a single machine.
> > 
> > I am pursuing this mostly out of curiosity; a learning experience
> > for
> > investigating the capabilities of the Mac.
> > 
> > Rather than sign up for your mailing list I thought I might ask
> > this one
> > question:  Which, if any, versions of SQLite might be suitable for
> > this
> > task?
> 
> You would probably have an easier time getting SQLite working on that
> hardware by installing a UNIX-like operating system. Classic Mac OS
> (Mac OS 9 and earlier) are not related to UNIX at all and SQLite is
> not designed for it, but Mac OS X (which was subsequently renamed to
> OS X and now macOS) is a BSD-derived UNIX operating system, and an
> old version of Mac OS X could be installed on your G3. Which version
> depends on which model of G3 you have.
> 
> Blue & white Power Macintosh G3s can run up to Mac OS X 10.4 Tiger.
> Beige Power Macintosh G3s may need a RAM upgrade but can run up to
> Mac OS X 10.2 Jaguar officially, and 10.4 can be installed with
> XPostFacto. Those versions of Mac OS X already come with an old
> version of SQLite; you may be able to compile a newer version if
> needed. If you're running at least Mac OS X 10.4, you can use
> MacPorts to install a newer SQLite for you. (Mac OS X 10.5 and later
> do not run on PowerPC G3 processors.)
> 
> You can keep your Mac OS 9 installation if you want, either on the
> same partition or on a separate partition or separate disk. You can
> run your Mac OS 9 programs within Mac OS X by using the "Classic"
> application, or you can reboot into Mac OS 9.
> 
> Alternately, you might be able to install another UNIX-like operating
> system, such as a Linux distribution or one of the other BSD
> variants.
> 
> Installing Mac OS X or any other UNIX-like system would also make it
> more likely that you could install other common SQL databases like
> MySQL/MariaDB and PostgreSQL.
> 
> Or if you just need a database and don't need it to be SQL, FileMaker
> Pro is a database system that was available for Mac OS 9 and is still
> available for Mac OS X. Even HyperCard for Mac OS 9 and earlier can
> be used as a simple database and was pretty fun.
> 
-- 
-CH-

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

[sqlite] How to Save NULL data to DB

[Prajeesh Prakash]

Hi Team,

How to save NULL parameter into the DB. I saw something like (null) on table 
but when i am reading from the table it popup as a string not the NULL value.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Number of changes

[Hick Gunter]

RTM.

Sqlite3_total_changes() counts the *total* number of changes done since the 
connection was opened. To get the number of changes made by the *last 
statement*, use sqlite3_changes(). This applies only to connections that are 
not shared between threads.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Prajeesh Prakash
Gesendet: Mittwoch, 05. Dezember 2018 13:10
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] Number of changes

Hi team,

How we can find the number of changes done on a particular table in DB using 
sqlite3 library function (C program). I tried with sqlite3_total_changes() but 
its doesn't helps me. I am writing only one record but its giving me 22 
records. At present my db contains 23 table and i tried to INSERT record into 1 
table out of 23 after INSERT (i used sqlite3_exec()) i kept 
sqlite3_total_changes() but it giving result as 22.



Thank you
___
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] [EXTERNAL] Number of changes

[Prajeesh Prakash]

Thank you  i tried sqlite3_changes() but in that case i INSERT 2 rows but it 
gave 1.

> 
> On December 5, 2018 at 5:51 PM Hick Gunter  wrote:
> 
> RTM.
> 
> Sqlite3_total_changes() counts the *total* number of changes done since 
> the connection was opened. To get the number of changes made by the *last 
> statement*, use sqlite3_changes(). This applies only to connections that are 
> not shared between threads.
> 
> -Ursprüngliche Nachricht-
> Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] 
> Im Auftrag von Prajeesh Prakash
> Gesendet: Mittwoch, 05. Dezember 2018 13:10
> An: sqlite-users@mailinglists.sqlite.org
> Betreff: [EXTERNAL] [sqlite] Number of changes
> 
> Hi team,
> 
> How we can find the number of changes done on a particular table in DB 
> using sqlite3 library function (C program). I tried with 
> sqlite3_total_changes() but its doesn't helps me. I am writing only one 
> record but its giving me 22 records. At present my db contains 23 table and i 
> tried to INSERT record into 1 table out of 23 after INSERT (i used 
> sqlite3_exec()) i kept sqlite3_total_changes() but it giving result as 22.
> 
> Thank you
> 
> ___
> 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
> 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] WG: [EXTERNAL] Number of changes

[Hick Gunter]

Von: Prajeesh Prakash [mailto:prajeeshprakash@elear.solutions]
Gesendet: Mittwoch, 05. Dezember 2018 13:47
An: SQLite mailing list ; Hick Gunter 

Betreff: Re: [sqlite] [EXTERNAL] Number of changes


Thank you  i tried sqlite3_changes() but in that case i INSERT 2 rows but it 
gave 1.

On December 5, 2018 at 5:51 PM Hick Gunter 
mailto:h...@scigames.at>> wrote:

RTM.

Sqlite3_total_changes() counts the *total* number of changes done since the 
connection was opened. To get the number of changes made by the *last 
statement*, use sqlite3_changes(). This applies only to connections that are 
not shared between threads.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Prajeesh Prakash
Gesendet: Mittwoch, 05. Dezember 2018 13:10
An: 
sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] Number of changes

Hi team,

How we can find the number of changes done on a particular table in DB using 
sqlite3 library function (C program). I tried with sqlite3_total_changes() but 
its doesn't helps me. I am writing only one record but its giving me 22 
records. At present my db contains 23 table and i tried to INSERT record into 1 
table out of 23 after INSERT (i used sqlite3_exec()) i kept 
sqlite3_total_changes() but it giving result as 22.

Thank you

___
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



___
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

[sqlite] WG: [EXTERNAL] Number of changes

[Hick Gunter]

Show what you executed. Make sure that there is no interference from other 
threads on the same connection. If you execute more than 1 statement in one 
call to sqlite3_exec(), only the last INSERT/UPDATE/DELETE will determine the 
return value of sqlite3_changes(). If you want the aggregate count of changes 
from 1 call to sqlite3_exec(), take the difference of the 
sqlite3_total_changes() values before and after.

Von: Prajeesh Prakash [mailto:prajeeshprakash@elear.solutions]
Gesendet: Mittwoch, 05. Dezember 2018 13:47
An: SQLite mailing list 
mailto:sqlite-users@mailinglists.sqlite.org>>;
 Hick Gunter mailto:h...@scigames.at>>
Betreff: Re: [sqlite] [EXTERNAL] Number of changes


Thank you  i tried sqlite3_changes() but in that case i INSERT 2 rows but it 
gave 1.

On December 5, 2018 at 5:51 PM Hick Gunter 
mailto:h...@scigames.at>> wrote:

RTM.

Sqlite3_total_changes() counts the *total* number of changes done since the 
connection was opened. To get the number of changes made by the *last 
statement*, use sqlite3_changes(). This applies only to connections that are 
not shared between threads.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Prajeesh Prakash
Gesendet: Mittwoch, 05. Dezember 2018 13:10
An: 
sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] Number of changes

Hi team,

How we can find the number of changes done on a particular table in DB using 
sqlite3 library function (C program). I tried with sqlite3_total_changes() but 
its doesn't helps me. I am writing only one record but its giving me 22 
records. At present my db contains 23 table and i tried to INSERT record into 1 
table out of 23 after INSERT (i used sqlite3_exec()) i kept 
sqlite3_total_changes() but it giving result as 22.

Thank you

___
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



___
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

[sqlite] Number of changes

[Prajeesh Prakash]

Hi team,

How we can find the number of changes done on a particular table in DB using 
sqlite3 library function (C program). I tried with sqlite3_total_changes() but 
its doesn't helps me. I am writing only one record but its giving me 22 
records. At present my db contains 23 table and i tried to INSERT record into 1 
table out of 23 after INSERT (i used sqlite3_exec()) i kept 
sqlite3_total_changes() but it giving result as 22.



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

Re: [sqlite] Corrupted database: On tree page 76852 cell 303: Rowid 18741471 out of order

[Ryan Schmidt]

On Dec 4, 2018, at 22:42, Simon Slavin wrote:

> On 5 Dec 2018, at 3:20am, Ryan Schmidt wrote:
> 
>> $ sqlite3 /opt/local/var/macports/registry/registry.db
>> SQLite version 3.25.2 2018-09-25 19:08:10
>> Enter ".help" for usage hints.
>> sqlite> .load /tmp/macports.sqlext
>> sqlite> pragma integrity_check;
>> *** in database main ***
>> On tree page 76852 cell 303: Rowid 18741471 out of order
>> On tree page 76852 cell 301: Rowid 18741430 out of order
>> On tree page 76852 cell 299: Rowid 18741387 out of order
>> On tree page 76852 cell 296: Rowid 18741324 out of order
> 
> It is possible that the rows reported as missing are actually deleted rows, 
> and that tree page 76852 just hasn't been moved from "part of TABLE ports" to 
> "freed pages".
> 
> Try using ".dump" to dump that database to a text file.  Can you read it and 
> tell whether anything is missing ?  You can either read the SQL commands by 
> eye, or use command-line-tool to ".read" the .sql file to create another 
> database, and use database tools to interrogate that one.
> 
> Does your database have any relations, either explicitly declared using 
> FOREIGN KEY or implicit in how your software handles the data ?  Presumably 
> every row in TABLE file should be part of a row in TABLE port, or something 
> like that.  Can you use your understanding of the relation to prove that 
> certain rows are missing from certain tables ?  Does it give you any idea how 
> much data is missing ?

I was able to .dump the data from the corrupt database and .read it into a new 
database (1.4GB, slightly smaller than the original 1.5GB database, which could 
be plausible if the original had not been vacuumed?). pragma integrity_check 
then found no problems in the new database.

The tables do have some very rudimentary FOREIGN KEY relations. The "ports" 
table has an "id" INTEGER primary key, and the "files" table and a couple 
others have an "id" column that references it.

Verifying the correctness of the data just by looking at it seemed infeasible, 
so, feeling adventurous, I put the new database in place and let MacPorts build 
a few things, but problems soon became apparent. The "files" table which 
records the association of files with a port has over 31,000 files associated 
with a port two or three times each -- duplicate entries. MacPorts didn't 
expect this condition to exist and didn't react well to it. This table does not 
have any unique constraints. We probably should have had a unique constraint 
over the combination of id and path to prevent this condition from existing. 
After the corruption first occurred, several automated builds went by, 
activating and deactivating various ports, which would have affected the 
registry, before I noticed the problem and stopped the automated builds. It's 
possible that because MacPorts was not able to access the registry properly, it 
got the wrong idea about what to do, and created those duplicate entries.

Since I don't know what else may have gone wrong with the contents of the 
registry by this point, it seems safer to erase the MacPorts installation and 
start fresh. This will take a bit longer as every port has to be re-fetched and 
re-installed but at least I'll have confidence in the integrity of the registry.

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

Re: [sqlite] [EXTERNAL] Number of changes

[Hick Gunter]

Just as I said. You are executing *TWO* SEPARATE *INSERTS* OF *1 CHANGE* EACH. 
So sqlite3_changes() reports 1 change (from the second INSERT), just as it is 
supposed to.

If you call sqlite3_total_changes() before executing the inserts and again 
afterwards, the *difference* of the returned values will be 2.

Von: Prajeesh Prakash [mailto:prajeeshprakash@elear.solutions]
Gesendet: Mittwoch, 05. Dezember 2018 14:04
An: Hick Gunter 
Betreff: Re: AW: [sqlite] [EXTERNAL] Number of changes


I ma trying to two insert on a single sqlite3_exec()

eg:   char *url = ""BEGIN TRANSACTION;"INSERT INTO Cars(NAME) 
VALUES('ABC');"INSERT INTO Cars(NAME) VALUES('ABC');"COMMIT";
then i am calling sqlite3_exec() then sqlite3_change()
On December 5, 2018 at 6:23 PM Hick Gunter 
mailto:h...@scigames.at>> wrote:



Show what you executed. Make sure that there is no interference from other 
threads on the same connection. If you execute more than 1 statement in one 
call to sqlite3_exec(), only the last INSERT/UPDATE/DELETE will determine the 
return value of sqlite3_changes(). If you want the aggregate count of changes 
from 1 call to sqlite3_exec(), take the difference of the 
sqlite3_total_changes() values before and after.



Von: Prajeesh Prakash [mailto:prajeeshprakash@elear.solutions]
Gesendet: Mittwoch, 05. Dezember 2018 13:47
An: SQLite mailing list 
mailto:sqlite-users@mailinglists.sqlite.org>>;
 Hick Gunter mailto:h...@scigames.at>>
Betreff: Re: [sqlite] [EXTERNAL] Number of changes



Thank you  i tried sqlite3_changes() but in that case i INSERT 2 rows but it 
gave 1.

On December 5, 2018 at 5:51 PM Hick Gunter 
mailto:h...@scigames.at>> wrote:

RTM.

Sqlite3_total_changes() counts the *total* number of changes done since the 
connection was opened. To get the number of changes made by the *last 
statement*, use sqlite3_changes(). This applies only to connections that are 
not shared between threads.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Prajeesh Prakash
Gesendet: Mittwoch, 05. Dezember 2018 13:10
An: 
sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] Number of changes

Hi team,

How we can find the number of changes done on a particular table in DB using 
sqlite3 library function (C program). I tried with sqlite3_total_changes() but 
its doesn't helps me. I am writing only one record but its giving me 22 
records. At present my db contains 23 table and i tried to INSERT record into 1 
table out of 23 after INSERT (i used sqlite3_exec()) i kept 
sqlite3_total_changes() but it giving result as 22.

Thank you

___
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




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



___
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] Corrupted database: On tree page 76852 cell 303: Rowid 18741471 out of order

[Simon Slavin]

On 5 Dec 2018, at 12:22pm, Ryan Schmidt  wrote:

> Since I don't know what else may have gone wrong with the contents of the 
> registry by this point, it seems safer to erase the MacPorts installation and 
> start fresh. This will take a bit longer as every port has to be re-fetched 
> and re-installed but at least I'll have confidence in the integrity of the 
> registry.

I agree with your diagnosis.  Your software has used the corrupted database and 
made incorrect changes to it because it trusted corrupt information.  
Unfortunate.  Since you do have the ability to build a new dataset from 
scratch, I think that's the best way.

I still have no better idea of the cause of the corruption.  I'd imagine you're 
still considering what you found in "howtocorrupt".

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

Re: [sqlite] Failure to rename table in 3.25 and 3.26

[Dan Kennedy]

On 12/03/2018 10:37 AM, Philip Warner wrote:

Tables with complex triggers (possibly limited to "Insert...With",
though that is not clear), fail with "no such table".


Thanks for reporting this. The bug was that table and column names 
within WITH clauses in the schema were not being updated correctly. Now 
fixed here:


  https://www.sqlite.org/src/info/f44bc7a8b3fac82a

Dan.





The following produces the error in 3.26; a much simpler trigger does
not produce the error.

|Create Table LOG_ENTRY(||
||LOG_ENTRY_ID int primary key,||
||TIME_START Long,||
||TIME_FINISH Long||
||);||
||
||Create Table SEGMENTS(START Long, FINISH Long);||
||
||Create View SEGMENTS_REBUILD_PV(START, FINISH) as Select Null, Null;||
||
||Drop Trigger if Exists SEGMENTS_REBUILD_PV_TG;||
||Create Trigger SEGMENTS_REBUILD_PV_TG ||
||Instead of Insert on SEGMENTS_REBUILD_PV||
||-- Recreate the SEGMENTS entries in the specified range||
||Begin||
||Delete from SEGMENTS Where ||
||START <= New.FINISH ||
||And FINISH >= New.START;||
||
||Insert or Replace into SEGMENTS(START, FINISH) ||
||With GAP(START, FINISH) as ||
||(Select||
||-- Nearest break before New.START (or New.START,||
||-- if nothing before).||
||Max( Coalesce((Select Max(TIME_FINISH)||
||From LOG_ENTRY E||
||Where E.TIME_FINISH < New.START), New.START),||
||Coalesce((Select Max(TIME_START)||
||From LOG_ENTRY E||
||Where E.TIME_START < New.START), New.START)||
||),||
||-- Nearest break after New.FINISH (or New.FINISH ,||
||-- if nothing after).||
||Min(Coalesce((Select Min(TIME_START)||
||From LOG_ENTRY E||
||Where E.TIME_START > New.FINISH), New.FINISH),||
||Coalesce((Select Min(TIME_FINISH)||
||From LOG_ENTRY E||
||Where E.TIME_START > New.FINISH), New.FINISH))||
||),||
||LOGS as||
||(Select * from LOG_ENTRY E, GAP||
||Where E.TIME_START <= GAP.FINISH||
||And E.TIME_FINISH >= GAP.START||
||)||
||Select Distinct B.START, B.FINISH From||
||GAP,||
||(Select||
||BREAK as START, ||
||Lead(BREAK) Over (Order by BREAK) as FINISH||
||From ||
||( ||
||Select Distinct TIME_START as BREAK from LOGS||
||UNION||
||Select Distinct TIME_FINISH as BREAK from LOGS||
||)||
||) B ||
||Where B.FINISH is Not NULL ||
||and B.START < GAP.FINISH||
||and B.FINISH >= GAP.START||
||;||
||End;||
|

|Alter table LOG_ENTRY Rename To ZZZ;|

|Error: error in trigger SEGMENTS_REBUILD_PV_TG after rename: no such
table: main.LOG_ENTRY|


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


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

Re: [sqlite] [EXTERNAL] Re: sqlite3_bind_text() and WHERE x IN (?)

[Hick Gunter]

... which is what SQLite does internally if you provide a list of literal 
values inside the parentheses. In some cases, SQLite 3.24 has been observed to 
use such an ephemeral table as the outer table of a join; with detrimental 
effects on query performance and no CROSS JOIN syntax available to force a 
different query plan

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von James K. Lowden
Gesendet: Mittwoch, 05. Dezember 2018 19:04
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] Re: [sqlite] sqlite3_bind_text() and WHERE x IN (?)

On Fri, 30 Nov 2018 23:25:48 +0900
Simon Walter  wrote:

> > SELECT id, data FROM val WHERE id IN ("1, 893, 121212");
...
> I have no idea yet if MySQL and/or PostgreSQL can handle this scenario
> and how they do it.

The important thing to understand about parameterized queries is that they are 
not a generalized macro system. Only data -- not metadata, not arbitrary 
strings -- can be parameterized.  That's why your IN list can't be 
parameterized (except as individual elements) and why can't say

SELECT id, data FROM ?
or
SELECT id, ? FROM val

as would occasionally be convenient.

Other than string-slinging, the only generalized standard solution for your 
parameterized IN list, where the number of elements is variable, is to first 
insert the list into a table, then use IN or EXISTS against it.

--jkl
___
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] sqlite3_bind_text() and WHERE x IN (?)

[James K. Lowden]

On Fri, 30 Nov 2018 23:25:48 +0900
Simon Walter  wrote:

> > SELECT id, data FROM val WHERE id IN ("1, 893, 121212");
...
> I have no idea yet if MySQL and/or PostgreSQL can handle this
> scenario and how they do it. 

The important thing to understand about parameterized queries is that
they are not a generalized macro system. Only data -- not metadata, not
arbitrary strings -- can be parameterized.  That's why your IN list
can't be parameterized (except as individual elements) and why can't say

SELECT id, data FROM ?
or
SELECT id, ? FROM val

as would occasionally be convenient.  

Other than string-slinging, the only generalized standard solution for
your parameterized IN list, where the number of elements is variable,
is to first insert the list into a table, then use IN or EXISTS against
it.  

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

Re: [sqlite] Corrupted database: On tree page 76852 cell 303: Rowid 18741471 out of order

[James K. Lowden]

On Wed, 5 Dec 2018 05:21:30 +
Simon Slavin  wrote:

> On 5 Dec 2018, at 5:16am, Ryan Schmidt  wrote:
> 
> > https://kb.vmware.com/s/article/1008542
> > 
> > "VMware ESX acknowledges a write or read to a guest operating
> > system only after that write or read is acknowledged by the
> > hardware controller to ESX. Applications running inside virtual
> > machines on ESX are afforded the same crash consistency guarantees
> > as applications running on physical machines or physical disk
> > controllers."
> 
> Interesting.  That paragraph is a well-written piece of text
> explaining the opposite of what I thought.  Maybe things have changed
> in the past decade.

VMware may well be doing the best it can on unreliable hardware.  I
believe it's common knowledge that consumer-grade hard drives lie when
acknowledging writes: the acknowlegement is sent when the data are
received into the device's write buffer, not after being written to
disk.  It's good for benchmarks.  No one benchmarks data corruptions.  

'Twas ever thus: If you want a reliable database, use a reliable disk.  

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