Re: [sqlite] Multi threaded readers on memory sqlite cannot scale
Apologies if I muddled the waters here. I read the "SQLightning" response below as SQLitening. I didn't know there was a similarly named project out there. I also can't see the beginning of this discussion to have context on what was originally asked, so I don't know which project was actually intended. SQLitening is a (multi-threaded) client-server wrapper for SQLite. > Now I'm even more confused, there's "sqlightning" and "sqlitening". Are these > different names for the same project? It doesn't seem so. Which was intended? > https://github.com/LMDB/sqlightning > ?On 5/14/18, 8:47 AM, "sqlite-users on behalf of Bernard Ertl" > bern...@interplansystems.com> wrote: > This is the latest: > http://www.sqlitening.planetsquires.com/index.php?topic=9427.0 > I contributed to the last SQLitening update. No one has reported any > issues that need fixing or updating since that update. It seems to be > working quite well/stable. > > On 5/13/18, 6:48 AM, "sqlite-users on behalf of Howard Chu" > > wrote: > > SQLightning > > Do you have a link to the currently updated version of this? Google > gives me projects that haven't been updated since 2015. > > ___ > 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] Multi threaded readers on memory sqlite cannot scale
This is the latest: http://www.sqlitening.planetsquires.com/index.php?topic=9427.0 I contributed to the last SQLitening update. No one has reported any issues that need fixing or updating since that update. It seems to be working quite well/stable. > On 5/13/18, 6:48 AM, "sqlite-users on behalf of Howard Chu" > > wrote: > SQLightning > Do you have a link to the currently updated version of this? Google gives me > projects that haven't been updated since 2015. > ___ 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] How to use "cursors" in c#
> ... If your application requires concurrent network access, you should be > using either a network client/server DBMS or programming your own > Client/Server pair with the server process calling the SQLite API to modify > the db file held on the servers' local filesystem. ... There is an open source client/server wrapper for SQLite available here: http://www.sqlitening.com/support/index.php ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Referencing a column alias (subquery) as function parameter
Is it possible to do something like this: SELECT c1, c2, CurrentDate, (CASE ActiveCol WHEN 0 THEN c3 ELSE c4 END) AS ac, MyFn(c1,ac,CurrentDate) AS xc1, MyFn(c2,ac,CurrentDate) AS xc2 FROM ... where c1, c2, CurrentDate, ActiveCol, c3, c4 are all columns in the table(s) referenced in the FROM clause? When I try to run the query, I get errors that ac isn't a valid column. Is it not possible to reference aliased columns as a parameter to a (custom) function? I could just put the CASE statement in the (MyFn) function's parameter field, but I'm calling the function 4 times in a single SELECT statement. Wouldn't that cause SQLite to evaluate the CASE statement each time? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using foreign key reference on RowID
Wow. Thanks. I see now that this is mentioned in the docs on the page for the VACUUM statement. It really should be mentioned on the CREATE TABLE page also where the rowid is explained. This is important information for people who are learning SQLite and trying to figure out how to design their database! Jay A. Kreibich-2 wrote: > > On Wed, Nov 17, 2010 at 04:36:12PM -0600, Bernard Ertl scratched on the > wall: > >> Is it not possible to reference the SQLite >> internal/default column for the RowID in a foreign key definition? > > Even if you could, you don't want to do this. > > Unless you define an ROWID alias (i.e. an INTEGER PRIMARY KEY column) > ROWID values are not preserved across vacuums or dumps. > >-j > > -- > Jay A. Kreibich < J A Y @ K R E I B I.C H > > > "Intelligence is like underwear: it is important that you have it, > but showing it to the wrong people has the tendency to make them > feel uncomfortable." -- Angela Johnson > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://old.nabble.com/Using-foreign-key-reference-on-RowID-tp30246958p30248863.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using foreign key reference on RowID
As someone who just started using SQLite without any previous background in
SQL, it was confusing to me. I did a search on nabble through this mailing
list and see now that I'm not the first person to ask about this issue.
IMO, it would be helpful to people new to SQLite to mention this in the docs
on the foreign key support page:
http://www.sqlite.org/foreignkeys.html
It's not clear from the CREATE TABLE page in the docs:
http://www.sqlite.org/lang_createtable.html
if there are any performance issues or other considerations in defining an
alias to the rowid. The text there doesn't really offer any reason to
someone new to the system to use an alias. Seems like a duplication of work
for no benefit (because the benefits aren't clearly explained). Maybe this
text should be updated so people are encouraged to use an alias instead of
the 'hidden' column.
Kees Nuyt wrote:
>
> On Wed, 17 Nov 2010 16:36:12 -0600, "Bernard Ertl"
> wrote:
>
>>I'm getting a "foreign key mismatch" error with the following code:
>>
>>~~~
>>
>>PRAGMA foreign_keys = ON;
>>
>>CREATE TABLE IF NOT EXISTS JobPlans (Name UNIQUE);
>>
>>CREATE TABLE IF NOT EXISTS Tasks (JobPlan_ID INTEGER NOT NULL REFERENCES
JobPlans(RowID) ON DELETE CASCADE, UID UNIQUE NOT NULL);
>>
>>INSERT INTO JobPlans(Name) VALUES ('234234');
>>
>># Following line generates the error:
>>INSERT INTO Tasks(JobPlan_ID,UID) VALUES (1,'ZZZ');
>>~~~
>>
>> If I explicitly declare an alias for the RowID:
>>
>>CREATE TABLE IF NOT EXISTS JobPlans (RowID INTEGER PRIMARY KEY, Name
UNIQUE);
>>
>> I don't get the error. Is it not possible
>> to reference the SQLite internal/default column
>> for the RowID in a foreign key definition?
>
> No it isn't. In general you can't refer to anything that is not part
> of your schema.
>
>> The online docs should be updated to reflect this.
>
> Disputable, as this is not specific for SQLite, it's part of SQL.
> The fact that ROWID is something hidden is documented well enough.
>
>
> The definition:
> CREATE TABLE JobPlans (
> id INTEGER PRIMARY KEY NOT NULL,
> Name UNIQUE
> );
> is physically the same as
> CREATE TABLE JobPlans (
> Name UNIQUE
> );
> anyway, so what's the problem defining the alias?
> Using the alias is much more portable.
>
> Note that the rowid alias doesn't have to be called RowID at all.
>
> CREATE TABLE IF NOT EXISTS Tasks (
> JobPlan_ID INTEGER NOT NULL
> REFERENCES JobPlans(id)
> ON DELETE CASCADE,
> UID UNIQUE NOT NULL
> );
> --
> ( Kees Nuyt
> )
> c[_]
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
--
View this message in context:
http://old.nabble.com/Using-foreign-key-reference-on-RowID-tp30246958p30248826.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Using foreign key reference on RowID
I'm getting a "foreign key mismatch" error with the following code:
~~~
PRAGMA foreign_keys = ON;
CREATE TABLE IF NOT EXISTS JobPlans (Name UNIQUE);
CREATE TABLE IF NOT EXISTS Tasks (JobPlan_ID INTEGER NOT NULL REFERENCES
JobPlans(RowID) ON DELETE CASCADE, UID UNIQUE NOT NULL);
INSERT INTO JobPlans(Name) VALUES ('234234');
# Following line generates the error:
INSERT INTO Tasks(JobPlan_ID,UID) VALUES (1,'ZZZ');
~~~
If I explicitly declare an alias for the RowID:
CREATE TABLE IF NOT EXISTS JobPlans (RowID INTEGER PRIMARY KEY, Name UNIQUE);
I don't get the error. Is it not possible to reference the SQLite
internal/default column for the RowID in a foreign key definition? The online
docs should be updated to reflect this.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Trouble with TRIGGERS
Kees Nuyt wrote: > >> Nicolas Williams-2 wrote: >> > Do you have recursive triggers enabled? >> >> I'm not sure. How do I check? > > http://www.sqlite.org/pragma.html#pragma_recursive_triggers > I'm not using any pragma commands, so no, I'm not using recursive triggers. -- View this message in context: http://old.nabble.com/Trouble-with-TRIGGERS-tp30228089p30233595.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Trouble with TRIGGERS
Nicolas Williams-2 wrote: > Do you have recursive triggers enabled? I'm not sure. How do I check? -- View this message in context: http://old.nabble.com/Trouble-with-TRIGGERS-tp30228089p30233496.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Trouble with TRIGGERS
Dan Kennedy-4 wrote: > > Sounds like it. > > Calling sqlite3_prepare_v2() generates the VM code for all > triggers that could possibly be invoked by your statement. > All it considers when determining which triggers might be > needed is the type of statement (UPDATE, DELETE, INSERT) and > for UPDATES, the columns updated. > Thanks Dan. However, I think it's more correct to say that it generates the VM code for all triggers that could possibly be invoked by the statement *and any related triggers*. It appears to expand the pool of possible triggers on the fly based upon the content of each trigger that it's queuing up. For example, this trigger: CREATE TRIGGER fki_Tasks_PerComp_Range AFTER INSERT ON Tasks FOR EACH ROW WHEN NEW.PerComp IS NULL BEGIN UPDATE Tasks SET PerComp = 0 WHERE RowID = NEW.RowID; END; Is causing all my triggers related to an update on the Tasks table to be queued up when I execute an INSERT on Tasks *with* PerComp = 0 (not null). I guess I was expecting a short circuit evaluation on the FOR EACH ROW WHEN conditions. -- View this message in context: http://old.nabble.com/Trouble-with-TRIGGERS-tp30228089p30232856.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Trouble with TRIGGERS
Hi, I'm experiencing some performance issues with triggers at the moment and hoping someone can help shed some light on what is happening. I have a database with ~20 tables and >100 triggers. I noticed a severe performance degradation after adding the last few triggers and it puzzled me because the triggers I added should not be executed by the statements I was testing. In a nutshell, I am inserting a record into a table. The last few triggers causing the severe performance degradation should only be triggered if there is an update to the table. I tried capturing the output from the EXPLAIN statement and, as best as I can tell, SQLite is queing up triggers if there is a possibility that they will be needed (but before an evaluation confirms it). For example, I have a trigger that, upon an insert, tests a condition and possibly performs an update pending the results of the condition. As a result, a whole slew of triggers conditioned to an update on the table are showing up in the EXPLAIN output (immediately after the insert trigger) even though the result of the initial condition is false and the update is not executed. From what I'm seeing there is a huge cascade of triggers listed in the EXPLAIN output that should never be executed because the conditions for executing them are never met. Can anyone confirm how SQLite processes triggers? Am I interpreting the EXPLAIN results correctly? Cordially, Bernard ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
