Re: [sqlite] sqlite3_limit equivalent in System.Data.SQLite.dll
I’ll raise the default limit for the next release of System.Data.SQLite. Sent from my iPhone > On Jan 16, 2020, at 2:01 PM, Keith Bertram wrote: > > Yes I recognize that this would be a problem. I plan on having no more than > 20-25 attachments. > > If I understand correctly, the only way to set the value above 10, is to > recompile the source and set the SQLITE_MAX_ATTACHED variable to a number > higher than my 20-25 and also below 125. I was hoping to just use the .dll > straight out of nuget. > > Keith > > -Original Message- > From: sqlite-users On Behalf > Of Simon Slavin > Sent: Thursday, January 16, 2020 10:19 AM > To: SQLite mailing list > Subject: Re: [sqlite] sqlite3_limit equivalent in System.Data.SQLite.dll > >> On 16 Jan 2020, at 3:21pm, Keith Bertram wrote: >> >> Ok. I was hoping I could set the value to a value higher than 10 without >> compiling. I'm curious why the limit is set by default to 10. > > The actual limit is 125. You can set SQLITE_MAX_ATTACHED to more than 125. > > It's worth explaining why you wouldn't want to attach 200 databases to the > same connection. Each time you refer to an attached database, SQLite has to > search for that database in a list, meaning it has to match the schema name, > then iterate through, on average, n/2 entries to find the attachment number. > The longer that list gets, the slower every operation not on 'main' or 'temp' > gets. (It's possible that SQLite hashes schema names, but I don't remember > anyone ever saying that.) > > I have seen posts to this list that say things like "We make one database > file per day, and I want to search them all at once so I want to attach 500 > files to my connection.". It's a terrible idea and would result in slow > performance. > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > https://urldefense.proofpoint.com/v2/url?u=http-3A__mailinglists.sqlite.org_cgi-2Dbin_mailman_listinfo_sqlite-2Dusers=DwIGaQ=hmGTLOph1qd_VnCqj81HzEWkDaxmYdIWRBdoFggzhj8=l90GvhOo_5uKc2d8JIuoVtb0fH4diec3z4TsA-hoNTc=UbVOkNIBY7TVyWQB9v2LK-xBIWk_rpXp5sXbzOFNwr0=U-mtya649Yx5nGJacNeBxjNHS1gBxc2IssQUKmPwgbA= > > ___ > 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] sqlite3_limit equivalent in System.Data.SQLite.dll
Yes I recognize that this would be a problem. I plan on having no more than 20-25 attachments. If I understand correctly, the only way to set the value above 10, is to recompile the source and set the SQLITE_MAX_ATTACHED variable to a number higher than my 20-25 and also below 125. I was hoping to just use the .dll straight out of nuget. Keith -Original Message- From: sqlite-users On Behalf Of Simon Slavin Sent: Thursday, January 16, 2020 10:19 AM To: SQLite mailing list Subject: Re: [sqlite] sqlite3_limit equivalent in System.Data.SQLite.dll On 16 Jan 2020, at 3:21pm, Keith Bertram wrote: > Ok. I was hoping I could set the value to a value higher than 10 without > compiling. I'm curious why the limit is set by default to 10. The actual limit is 125. You can set SQLITE_MAX_ATTACHED to more than 125. It's worth explaining why you wouldn't want to attach 200 databases to the same connection. Each time you refer to an attached database, SQLite has to search for that database in a list, meaning it has to match the schema name, then iterate through, on average, n/2 entries to find the attachment number. The longer that list gets, the slower every operation not on 'main' or 'temp' gets. (It's possible that SQLite hashes schema names, but I don't remember anyone ever saying that.) I have seen posts to this list that say things like "We make one database file per day, and I want to search them all at once so I want to attach 500 files to my connection.". It's a terrible idea and would result in slow performance. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org https://urldefense.proofpoint.com/v2/url?u=http-3A__mailinglists.sqlite.org_cgi-2Dbin_mailman_listinfo_sqlite-2Dusers=DwIGaQ=hmGTLOph1qd_VnCqj81HzEWkDaxmYdIWRBdoFggzhj8=l90GvhOo_5uKc2d8JIuoVtb0fH4diec3z4TsA-hoNTc=UbVOkNIBY7TVyWQB9v2LK-xBIWk_rpXp5sXbzOFNwr0=U-mtya649Yx5nGJacNeBxjNHS1gBxc2IssQUKmPwgbA= ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Request: Allow virtual tables to make use of partial indexes
I believe I've found another limitation for efficient querying of virtual tables. The xBestIndex call communicates column constraints, but it doesn't specify whether a constraint's value is known at compile time, nor pass such a compile-time value to xBestIndex. This means that the virtual-table implementation can't make use of partial indexes in its underlying storage, because it doesn't know whether the index's predicate is satisfied. For example, say my data store is FooCabinet, and it has a FooCabinet index* on the "cost" column where the "type" column is equal to "expense". Consider these two queries: SELECT cost FROM myvtable WHERE cost > ? AND type = 'expense'; SELECT cost FROM myvtable WHERE cost > ? AND type =?; The first query can make use of the index; the second can't. But in either case, my xBestIndex function is called with SQLITE_INDEX_CONSTRAINT_GT on "cost" and SQLITE_INDEX_CONSTRAINT_EQ on "type", and that's all I know. I can't tell whether the index is useable, so I can't use it. Consider this an enhancement request to extend the sqlite3_index_info struct somehow to convey compile-time column constraint values to the extension, perhaps by adding a field const sqlite3_value** const constraintValues; Thanks, —Jens * Obviously we are not talking about SQLite indexes here! My underlying data store has its own partial- index functionality. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_limit equivalent in System.Data.SQLite.dll
On 16 Jan 2020, at 4:19pm, Simon Slavin wrote: > The actual limit is 125. You can set SQLITE_MAX_ATTACHED to more than 125. 'can' should be 'can't'. Sorry. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_limit equivalent in System.Data.SQLite.dll
On 16 Jan 2020, at 3:21pm, Keith Bertram wrote: > Ok. I was hoping I could set the value to a value higher than 10 without > compiling. I'm curious why the limit is set by default to 10. The actual limit is 125. You can set SQLITE_MAX_ATTACHED to more than 125. It's worth explaining why you wouldn't want to attach 200 databases to the same connection. Each time you refer to an attached database, SQLite has to search for that database in a list, meaning it has to match the schema name, then iterate through, on average, n/2 entries to find the attachment number. The longer that list gets, the slower every operation not on 'main' or 'temp' gets. (It's possible that SQLite hashes schema names, but I don't remember anyone ever saying that.) I have seen posts to this list that say things like "We make one database file per day, and I want to search them all at once so I want to attach 500 files to my connection.". It's a terrible idea and would result in slow performance. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_limit equivalent in System.Data.SQLite.dll
Ok. I was hoping I could set the value to a value higher than 10 without
compiling. I'm curious why the limit is set by default to 10.
Keith
-Original Message-
From: sqlite-users On Behalf Of
Keith Medcalf
Sent: Wednesday, January 15, 2020 2:54 PM
To: SQLite mailing list
Subject: Re: [sqlite] sqlite3_limit equivalent in System.Data.SQLite.dll
What did you define SQLITE_MAX_ATTACHED as when you compiled the DLL? The
default limit is 10. You can dynamically decrease the limit to be less than
the compile time limit, but you cannot increase it beyond the maximum set when
you compiled the library.
https://urldefense.proofpoint.com/v2/url?u=https-3A__sqlite.org_limits.html=DwIGaQ=hmGTLOph1qd_VnCqj81HzEWkDaxmYdIWRBdoFggzhj8=l90GvhOo_5uKc2d8JIuoVtb0fH4diec3z4TsA-hoNTc=msKB5Cm5_n99wUiklx4_tVTut5zWsQjh7CN1Vh4i65A=ibWsJ1kdTb3PI75vFiL0vv8xOusBf31qmOx3ovzgFR8=
11. Maximum Number Of Attached Databases
The ATTACH statement is an SQLite extension that allows two or more databases
to be associated to the same database connection and to operate as if they were
a single database. The number of simultaneously attached databases is limited
to SQLITE_MAX_ATTACHED which is set to 10 by default. The maximum number of
attached databases cannot be increased above 125.
The maximum number of attached databases can be lowered at run-time using the
sqlite3_limit(db,SQLITE_LIMIT_ATTACHED,size) interface.
--
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 On
>Behalf Of Keith Bertram
>Sent: Wednesday, 15 January, 2020 12:50
>To: SQLite mailing list
>Subject: Re: [sqlite] sqlite3_limit equivalent in
>System.Data.SQLite.dll
>
>Hello,
>
>I'm attempting to use the new SetLimitOption added to the 1.0.112
>version of System.Data.SQLite.dll. Either I'm not using this function
>correctly or there is a problem within the dll. Any help you can
>provide is greatly appreciated.
>
>private void Button_Click (object sender, RoutedEventArgs e)
>{
>int iRequestValue = 20;
>String fName = @"d:\vancouver\OpenCommsCPV.db";
>String connStr = String.Format("Data Source={0},
>Version=3", fName);
>SQLiteConnection conn = new SQLiteConnection(connStr);
>conn.Open();
>
>int iOldValue =
>conn.SetLimitOption(SQLiteLimitOpsEnum.SQLITE_LIMIT_ATTACHED,
>iRequestValue);
>int iNewValue =
>conn.SetLimitOption(SQLiteLimitOpsEnum.SQLITE_LIMIT_ATTACHED, -1); //
>Pass -1 to get the current value.
>if ( iNewValue != iRequestValue )
>{
>MessageBox.Show("Setting Num Attachments limit failed.");
>}
>else
>{
>MessageBox.Show("Setting Num Attachments limit
>succeeded.");
>}
>}
>
>Keith
>
>-Original Message-
>From: sqlite-users On
>Behalf Of Joe Mistachkin
>Sent: Thursday, October 24, 2019 8:36 AM
>To: SQLite mailing list
>Subject: Re: [sqlite] sqlite3_limit equivalent in
>System.Data.SQLite.dll
>
>
>Yes, the NuGet packages are included in the release.
>
>Sent from my iPhone
>
>> On Oct 24, 2019, at 9:13 AM, Keith Bertram
>>
>wrote:
>>
>> Ok. Thanks. Does it get immediately placed under nuget?
>>
>> Keith
>>
>> -Original Message-
>> From: sqlite-users On
>> Behalf Of Joe Mistachkin
>> Sent: Thursday, October 24, 2019 8:12 AM
>> To: SQLite mailing list
>> Subject: Re: [sqlite] sqlite3_limit equivalent in
>> System.Data.SQLite.dll
>>
>>
>> Within the next week or so.
>>
>> Sent from my iPhone
>>
>>> On Oct 24, 2019, at 8:37 AM, Keith Bertram
>>>
>wrote:
>>>
>>> Ok. Thanks. Do you have an estimated release date?
>>>
>>> Keith
>>>
>>> -Original Message-
>>> From: sqlite-users On
>>> Behalf Of Joe Mistachkin
>>> Sent: Wednesday, October 23, 2019 9:08 PM
>>> To: 'SQLite mailing list'
>>> Subject: Re: [sqlite] sqlite3_limit equivalent in
>>> System.Data.SQLite.dll
>>>
>>>
>>> Keith Bertram wrote:
The default limit for the number of attached databases appears to
be
10
>>> with the
1.0.106 version of System.Data.SQLite.dll and I would like to
increase
>>> this value
to 25. I've used the sqlite3_limit function in the past to increase
this
>
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>https://urldefense.proofpoint.com/v2/url?u=http-
>3A__mailinglists.sqlite.org_cgi-2Dbin_mailman_listinfo_sqlite-
>2Dusers=DwIGaQ=hmGTLOph1qd_VnCqj81HzEWkDaxmYdIWRBdoFggzhj8=l90Gvh
>Oo
>_5uKc2d8JIuoVtb0fH4diec3z4TsA-hoNTc=e4b8CwDAzK1-
>TKWOZZJxjXuHFSnDxxfXP6h5QVKBpaI=dOVM934ewvnS0gL9iui_PzN0keYUOjW48CiQW
>0X
>ea6A=
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
[sqlite] Obtaining rowid of an updated row in UPSERT
Dear SQLiters,
If an INSERT ... ON CONFLICT DO UPDATE statement detects that a row
already exists and needs to be updated, it doesn't seem to set lastRowid
to the rowid of that row. Observe (sqlite 3.30.1):
> create table users (id integer primary key, firstname text, lastname text,
phonenumber text);
> create unique index idx_users_firstname_lastname on users (firstname,
lastname);
> insert into users (firstname, lastname, phonenumber) values ('John', 'Doe',
'1'),
('Donald', 'Covfefe', '2');
> insert into users (firstname, lastname, phonenumber) values ('John', 'Doe',
'3')
on conflict (firstname, lastname) do update set phonenumber =
excluded.phonenumber;
> select last_insert_rowid();
2
I'd like to have obtained 1 instead, the rowid for John Doe.
I imagine the reasoning behind this is that if there was no inserted row,
then last_*insert*_rowid should remain as it was... but is there any way
to obtain this information, other than making a subsequent SELECT?
Thanks,
– Daniel
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Next Release? Visual release timeline?
On Wed, Jan 15, 2020 at 4:54 PM R Smith wrote:
> On 2020/01/15 1:24 PM, Richard Hipp wrote:
> >> (2) Assume the data is a JSON array of pairs. The first element of
> >> each pair is the release name (ex: "3.30.0") and the second element is
> >> the time as a fractional year (ex: "2019.7775").
Note that Richard replied to me private with a JSON array of this form:
chronology = [{"hash":"xx","vers":"3.31.0","date":2020.0398},
{"hash":"18db032d05","vers":"3.30.1","date":2019.7748},
{"hash":"c20a353364","vers":"3.30.0","date":2019.7557},
{"hash":"fc82b73eaa","vers":"3.29.0","date":2019.5202},
{"hash":"884b4b7e50","vers":"3.28.0","date":2019.2875},
{"hash":"bd49a8271d","vers":"3.27.2","date":2019.1506},
...]
So with a little gymnastic to recover the date, and given the hashes,
all the currently "hardcoded" elements can also be generated
from this JSON array.
Of course, some people disable JavaScript, so "server-side" rendering
might be preferred.
> We'd like to submit this layout as an option:
> https://sqlitespeed.com/sqlite_releases.html
>
> Shown alongside the current list in simple form. Tried a few layouts,
> not all work as well (SQLite releases are much more dense than Lua),
> finally settled on the above, but left some options open.
Interesting, thanks for the submission. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
