Re: [sqlite] Proof that a line has been modified

[Paxdo]

Thank you Clemens!


Clemens Ladisch 
7 septembre 2017 à 10:34

Something like .

See git or fossil, where the current state of the entire repository is
identified by a hash over all data and all previous changes; once a hash
value has been published, it is not possible to change anything without
changing the hash value. (You need to save the hash values of the
interesting revisions somewhere else to be able to check them.)

These tools work on files, not on database objects. But you could put
the entire database file in the repository.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Paxdo 
7 septembre 2017 à 10:16

Hi all!

For security reasons, a customer wants to be sure that a database line 
cannot be modified after its initial insertion (or unmodified without 
being visible, with proof that the line has been modified). Including 
by technicians who can open the database (SQLITE of course).


Is there a solution to that?

I thought of a hash calculated and recorded only when the line was 
inserted (calculated on all columns of the line, and stored in a 
column of that line).


Or a trigger that would prevent any modification.

But these solutions can be easily bypassed, right?

Do you have any advice?

Thank you!

Tom
(Sorry for my bad english)
___
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] Need help with SQL query

[R Smith]

Correction:


On 2017/09/11 6:43 AM, R Smith wrote:


SELECT I.ID, ISNULL(A.VALUE,'[No Value]')
  FROM ITEM AS I
  LEFT JOIN ATTRIBUTES AS A ON A.ITEM_ID = I.ID
 WHERE A.key='abc' OR A.key IS NULL
 ORDER BY A.VALUE;


There is of course no such thing as SORT BY in SQL, it's ORDER BY.
(Forgive me, it's 6am and I need to go to bed still...)



Note 1:  Left join will list all the values from the first table (the 
LEFT table) and add results where possible from the second (RIGHT) 
table, else the values will be NULL for it.
Note 2: ISNULL(x,a) Will output the value of x, unless it is NULL, in 
which case it will show a.
Note 3:  The strings in SQL has single quotes (like 'abc'), only 
identifiers get double quotes.
Note 4: LIMIT and OFFSET is a very bad way to do paging (in case 
that's what you are planning). It's good for limiting the size of a 
query, but offset has to reproduce the entire query every time and 
wait for the offset number of rows to pass before it can jump in and 
start adding rows to the output - it's not really "remembering" where 
it left off. You can do that better with temporary tables containing a 
result set and then stepping through those tables based on a key.


Cheers,
Ryan


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

Re: [sqlite] Need help with SQL query

[R Smith]

On 2017/09/11 5:35 AM, Vikas Aditya wrote:

Hi All,

I need some help in figuring our right query syntax for querying items from two 
tables. We have two tables. One of the table has list of items. And Second 
table has additional attributes.

CREATE TABLE ITEM (
ID INTEGER,
FIELD0 TEXT
FIELD1 TEXT,
FIELD3 TEXT,
FIELD4 TEXT
);

CREATE TABLE ATTRIBUTES (
ID INTEGER,
ITEM_ID INTEGER,
KEY TEXT
VALUE TEXT
);

For a single row in item table, we can have multiple rows in attributes table. 
It is a generic Key/Value attributes for an item. ITEM_ID in 2nd table is the 
ID of an item in 1st table.

We want to select Items sorted by VALUE in the Attribute table.

I have tried following query

SELECT ITEM.ID from ITEM, ATTRIBUTES where ITEM.ID == ATTRIBUTES.ITEM_ID and 
key=“abc” SORT BY VALUE;

This works and items are sorted by value but naturally it only selects items 
that have key/value specified for an item. We also have some items that have 
missing attributes, so key/value row for that item is missing and above query 
will not select those items. We can write a different query that can select all 
items where key is missing. For example:

SELECT ITEM.ID, FIELD0 from ITEM, ATTRIBUTES where ITEM.ID not in (SELECT 
ITEM.ID from ITEM, ATTRIBUTES where ITEM.ID == ATTRIBUTES.ITEM_ID and 
key==“abc”)


But is it possible to write a single query that will present results from both 
queries above?
Our use case is that we are trying to implement sorting functionality in our 
app and for items that don’t have a key/value attribute, we want to treat value 
as NULL or “”  but still return the item. Because results are sorted by VALUE, 
these items with missing key/value will be at beginning of result set. Because 
we also want to add LIMIT and OFFSET, we think a single query is better 
solution versus trying to run two queries and trying to merge two results.


You want LEFT JOIN.

Something like:

SELECT I.ID, ISNULL(A.VALUE,'[No Value]')
  FROM ITEM AS I
  LEFT JOIN ATTRIBUTES AS A ON A.ITEM_ID = I.ID
 WHERE A.key='abc' OR A.key IS NULL
 SORT BY VALUE;


Note 1:  Left join will list all the values from the first table (the 
LEFT table) and add results where possible from the second (RIGHT) 
table, else the values will be NULL for it.
Note 2: ISNULL(x,a) Will output the value of x, unless it is NULL, in 
which case it will show a.
Note 3:  The strings in SQL has single quotes (like 'abc'), only 
identifiers get double quotes.
Note 4: LIMIT and OFFSET is a very bad way to do paging (in case that's 
what you are planning). It's good for limiting the size of a query, but 
offset has to reproduce the entire query every time and wait for the 
offset number of rows to pass before it can jump in and start adding 
rows to the output - it's not really "remembering" where it left off. 
You can do that better with temporary tables containing a result set and 
then stepping through those tables based on a key.


Cheers,
Ryan



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

[sqlite] Need help with SQL query

[Vikas Aditya]

Hi All,

I need some help in figuring our right query syntax for querying items from two 
tables. We have two tables. One of the table has list of items. And Second 
table has additional attributes.

CREATE TABLE ITEM (
ID INTEGER,
FIELD0 TEXT
FIELD1 TEXT,
FIELD3 TEXT,
FIELD4 TEXT
);

CREATE TABLE ATTRIBUTES (
ID INTEGER,
ITEM_ID INTEGER,
KEY TEXT
VALUE TEXT
);

For a single row in item table, we can have multiple rows in attributes table. 
It is a generic Key/Value attributes for an item. ITEM_ID in 2nd table is the 
ID of an item in 1st table.

We want to select Items sorted by VALUE in the Attribute table. 

I have tried following query

SELECT ITEM.ID from ITEM, ATTRIBUTES where ITEM.ID == ATTRIBUTES.ITEM_ID and 
key=“abc” SORT BY VALUE;

This works and items are sorted by value but naturally it only selects items 
that have key/value specified for an item. We also have some items that have 
missing attributes, so key/value row for that item is missing and above query 
will not select those items. We can write a different query that can select all 
items where key is missing. For example:

SELECT ITEM.ID, FIELD0 from ITEM, ATTRIBUTES where ITEM.ID not in (SELECT 
ITEM.ID from ITEM, ATTRIBUTES where ITEM.ID == ATTRIBUTES.ITEM_ID and 
key==“abc”)


But is it possible to write a single query that will present results from both 
queries above? 
Our use case is that we are trying to implement sorting functionality in our 
app and for items that don’t have a key/value attribute, we want to treat value 
as NULL or “”  but still return the item. Because results are sorted by VALUE, 
these items with missing key/value will be at beginning of result set. Because 
we also want to add LIMIT and OFFSET, we think a single query is better 
solution versus trying to run two queries and trying to merge two results.

Thanks,
Vikas

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

Re: [sqlite] [EXTERNAL] Performance impact of UPDATEing multiple columns vs few columns

[Darko Volaric]

If you're preparing that statement more than once then you are wasting time, 
there's no reason whatsoever to do it. You're also wasting time if you make a 
bind call to set any column that hasn't changed since the last time you 
executed the statement.

The entire row is rewritten when updating so the most efficient way to do it is 
to call sqlite3_prepare_v2 once, then bind any columns that have changed, call 
sqlite3_step to execute the statement, then call sqlite3_reset to reuse the 
statement, then go back to binding any columns that have changed and repeat the 
other steps. sqlite3_reset does not clear any column bindings.



> On Sep 10, 2017, at 7:08 AM, ghalwasi  wrote:
> 
 Are you using a single prepared statement and binding values (in which
> case, how do you know what values to bind for the "non-updated" columns?) or
> are you creating query strings? 
> 
> I am not too sure, if i get it completely. My current code has a lot of
> update statements like.
> 
> "update records set name=:name, type=:type, class=:class, ttl=:ttl where
> rr_id=:rr_id;"
> every time we do prepare the statetment again (sqlite3_prepare_v2) and call
> sqlite_bind_* for each of these columns (name, type, class, ttl) and then
> execute.
> 
> Now here intent was/is to just update "ttl" column but i see that we are
> unnecessarily updating 4 fields. And my original question was in this
> context where i want to figure out whether it could make some performance
> improvement if we change the above statement to 
> "update records set ttl=:ttl where rr_id=:rr_id;"
> 
> 
> 
> 
> --
> 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] [EXTERNAL] Performance impact of UPDATEing multiple columns vs few columns

[ghalwasi]

>>> Are you using a single prepared statement and binding values (in which
case, how do you know what values to bind for the "non-updated" columns?) or
are you creating query strings? 

I am not too sure, if i get it completely. My current code has a lot of
update statements like.

"update records set name=:name, type=:type, class=:class, ttl=:ttl where
rr_id=:rr_id;"
every time we do prepare the statetment again (sqlite3_prepare_v2) and call
sqlite_bind_* for each of these columns (name, type, class, ttl) and then
execute.

Now here intent was/is to just update "ttl" column but i see that we are
unnecessarily updating 4 fields. And my original question was in this
context where i want to figure out whether it could make some performance
improvement if we change the above statement to 
"update records set ttl=:ttl where rr_id=:rr_id;"




--
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] JOIN vs. INTERSECT vs. WHERE IN (...) - speed and efficiency differences

[R Smith]

Well yes but the documentation suggests that one could expect a slight 
degradation. The words "works best with" does not seem to imbue an idea 
of "give WITHOUT ROWID tables a wide berth when your tables are more 
than few columns wide", and I don't think the Devs intended that either.


I can still roll with the idea that the WITHOUT ROWID tables with 151 
columns and lots of data actually performed FASTER than the same ROWID 
tables for the other queries but slower with the JOIN... but that much 
slower?


I mean we are not talking a "little" slower, we are talking 50ms vs. 
~70,000ms, that's a difference of a whopping 3 orders of magnitude and 
change. And we are not talking huge tables, another query that simply 
walks the tables can do so several million times in the same time the 
JOIN query does.


Put another way, I can create 100 new tables and populate them each with 
the rows from one test table, then delete the rows from each new table 
that doesn't satisfy an EXISTS() check in the other test table 
(essentially mimicking the JOIN query) and then output each full new 
table, 100 of them in turn, and then DROP them all. SQlite can do ALL of 
that in a fraction of the time that the normal JOIN query (between those 
same two test tables) takes to complete.


We are talking a formula 1 car suddenly going at max speed of 1 
mile-per-weekend, barely keeping up with a semi-athletic snail, and 
people suggest checking the fuel octane rating. I'm saying there is 
something wrong under the hood.


Cheers,
Ryan


On 2017/09/10 11:28 AM, Clemens Ladisch wrote:

R Smith wrote:

I am using 151 columns for both tests. The only thing that changes
between the two scripts are the words "WITHOUT ROWID" being added

 says:
| WITHOUT ROWID tables will work correctly ... for tables with a single
| INTEGER PRIMARY KEY. However, ordinary rowid tables will run faster in
| that case.
and:
| WITHOUT ROWID tables work best when individual rows are not too large.

So this is pretty much the documented worst case for WITHOUT ROWID tables.

If the query is executed by making a copy of all the table data into
a temporary B-tree (index), which is what INTERSECT and probably IN(...)
are doing, then the WITHOUT-ROWID-ness of the table does not matter.


Regards,
Clemens
___
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] JOIN vs. INTERSECT vs. WHERE IN (...) - speed and efficiency differences

[Clemens Ladisch]

R Smith wrote:
> I am using 151 columns for both tests. The only thing that changes
> between the two scripts are the words "WITHOUT ROWID" being added

 says:
| WITHOUT ROWID tables will work correctly ... for tables with a single
| INTEGER PRIMARY KEY. However, ordinary rowid tables will run faster in
| that case.
and:
| WITHOUT ROWID tables work best when individual rows are not too large.

So this is pretty much the documented worst case for WITHOUT ROWID tables.

If the query is executed by making a copy of all the table data into
a temporary B-tree (index), which is what INTERSECT and probably IN(...)
are doing, then the WITHOUT-ROWID-ness of the table does not matter.


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

Re: [sqlite] Fwd: Problem on Windows 10 machines

[Kevin Benson]

I wonder if the answer at this following inquiry is suggesting that your
last posted results are unremarkable:

https://stackoverflow.com/questions/11872539/windbg-crash-dump-analysis
and that suggested alternative approach may be more helpful than WinDbg ?

--
   --
  -- "
 --Ö¿Ö--
K e V i N

On Sat, Sep 9, 2017 at 10:23 AM, Bart Smissaert 
wrote:

> I did debugging with WinDbg and got the following with !analyse:
>
> GetUrlPageData2 (WinHttp) failed: 12002.
> DUMP_CLASS: 2
> DUMP_QUALIFIER: 0
> FAULTING_IP:
> ntdll!LdrpDoDebuggerBreak+30
> 7ffb`0a4871b8 cc  int 3
> EXCEPTION_RECORD:  (.exr -1)
> ExceptionAddress: 7ffb0a4871b8
> (ntdll!LdrpDoDebuggerBreak+0x0030)
>ExceptionCode: 8003 (Break instruction exception)
>   ExceptionFlags: 
> NumberParameters: 1
>Parameter[0]: 
> FAULTING_THREAD:  1b24
> BUGCHECK_STR:  BREAKPOINT
> DEFAULT_BUCKET_ID:  BREAKPOINT
> PROCESS_NAME:  COM32on64.exe
> ERROR_CODE: (NTSTATUS) 0x8003 - {EXCEPTION}  Breakpoint  A breakpoint
> has been reached.
> EXCEPTION_CODE: (HRESULT) 0x8003 (2147483651) - One or more arguments
> are invalid
> EXCEPTION_CODE_STR:  8003
> EXCEPTION_PARAMETER1:  
> WATSON_BKT_PROCSTAMP:  56efa71c
> WATSON_BKT_PROCVER:  1.0.0.0
> PROCESS_VER_PRODUCT:  COM32on64
> WATSON_BKT_MODULE:  ntdll.dll
> WATSON_BKT_MODSTAMP:  a329d3a8
> WATSON_BKT_MODOFFSET:  d71b8
> WATSON_BKT_MODVER:  10.0.15063.447
> MODULE_VER_PRODUCT:  Microsoft® Windows® Operating System
> BUILD_VERSION_STRING:  10.0.15063.447 (WinBuild.160101.0800)
> MODLIST_WITH_TSCHKSUM_HASH:  38cbe79f85e7dfdb265ff9afb82039794b9b5c6a
> MODLIST_SHA1_HASH:  6299f03ade45bd5452864c8b494e355b11bc3b25
> NTGLOBALFLAG:  70
> PROCESS_BAM_CURRENT_THROTTLED: 0
> PROCESS_BAM_PREVIOUS_THROTTLED: 0
> APPLICATION_VERIFIER_FLAGS:  0
> PRODUCT_TYPE:  1
> SUITE_MASK:  784
> DUMP_TYPE:  fe
> ANALYSIS_SESSION_HOST:  DESKTOP-9UDFVUA
> ANALYSIS_SESSION_TIME:  09-09-2017 14:37:25.0292
> ANALYSIS_VERSION: 10.0.15063.468 amd64fre
> THREAD_ATTRIBUTES:
> OS_LOCALE:  ENG
> PROBLEM_CLASSES:
> ID: [0n300]
> Type:   [@APPLICATION_FAULT_STRING]
> Class:  Primary
> Scope:  DEFAULT_BUCKET_ID (Failure Bucket ID prefix)
> BUCKET_ID
> Name:   Omit
> Data:   Add
> String: [BREAKPOINT]
> PID:[Unspecified]
> TID:[Unspecified]
> Frame:  [0]
> PRIMARY_PROBLEM_CLASS:  BREAKPOINT
> LAST_CONTROL_TRANSFER:  from 7ffb0a442b28 to 7ffb0a4871b8
> STACK_TEXT:
> `0009f2b0 7ffb`0a442b28 : `0040 `0003
> ` `0021d000 : ntdll!LdrpDoDebuggerBreak+0x30
> `0009f2f0 7ffb`0a47a1fc : ` `
> ` `0001 : ntdll!LdrpInitializeProcess+0xfa4
> `0009f720 7ffb`0a429b1b : 7ffb`0a3b `
> ` `0021d000 : ntdll!_LdrpInitialize+0x506cc
> `0009f7a0 7ffb`0a429ace : `0009f820 `
> ` ` : ntdll!LdrpInitialize+0x3b
> `0009f7d0 ` : ` `
> ` ` : ntdll!LdrInitializeThunk+0xe
> THREAD_SHA1_HASH_MOD_FUNC:  f6d506c4546bb5d137f475a99d6cb238658f395a
> THREAD_SHA1_HASH_MOD_FUNC_OFFSET:  6cbeafe1708871adca27efbe26e88c
> d700c132ee
> THREAD_SHA1_HASH_MOD:  421247e39d7ac2afa4b65a67c08f22894942361d
> FOLLOWUP_IP:
> ntdll!LdrpDoDebuggerBreak+30
> 7ffb`0a4871b8 cc  int 3
> FAULT_INSTR_CODE:  4800ebcc
> SYMBOL_STACK_INDEX:  0
> SYMBOL_NAME:  ntdll!LdrpDoDebuggerBreak+30
> FOLLOWUP_NAME:  MachineOwner
> MODULE_NAME: ntdll
> IMAGE_NAME:  ntdll.dll
> DEBUG_FLR_IMAGE_TIMESTAMP:  0
> STACK_COMMAND:  dt ntdll!LdrpLastDllInitializer BaseDllName ; dt
> ntdll!LdrpFailureData ; ~0s ; kb
> BUCKET_ID:  BREAKPOINT_ntdll!LdrpDoDebuggerBreak+30
> FAILURE_EXCEPTION_CODE:  8003
> FAILURE_IMAGE_NAME:  ntdll.dll
> BUCKET_ID_IMAGE_STR:  ntdll.dll
> FAILURE_MODULE_NAME:  ntdll
> BUCKET_ID_MODULE_STR:  ntdll
> FAILURE_FUNCTION_NAME:  LdrpDoDebuggerBreak
> BUCKET_ID_FUNCTION_STR:  LdrpDoDebuggerBreak
> BUCKET_ID_OFFSET:  30
> BUCKET_ID_MODTIMEDATESTAMP:  0
> BUCKET_ID_MODCHECKSUM:  1dd2f4
> BUCKET_ID_MODVER_STR:  10.0.15063.447
> BUCKET_ID_PREFIX_STR:  BREAKPOINT_
> FAILURE_PROBLEM_CLASS:  BREAKPOINT
> FAILURE_SYMBOL_NAME:  ntdll.dll!LdrpDoDebuggerBreak
> FAILURE_BUCKET_ID:  BREAKPOINT_8003_ntdll.dll!LdrpDoDebuggerBreak
> WATSON_STAGEONE_URL:
> http://watson.microsoft.com/StageOne/COM32on64.exe/1.0.0.
> 0/56efa71c/ntdll.dll/10.0.15063.447/a329d3a8/8003/
> 000d71b8.htm?Retriage=1
> TARGET_TIME:  2017-09-09T13:37:25.000Z
> OSBUILD:  15063
> OSSERVICEPACK:  447
> SERVICEPACK_NUMBER: 0
> OS_REVISION: 0
> OSPLATFORM_TYPE:  x64
> OSNAME:  Windows 10
> OSEDITION:  Windows 10 WinNt SingleUserTS Personal
> USER_LCID:  0
> OSBUILD_TIMESTAMP: