Re: [sqlite] Is it possible to create the Stored Procedure (SP) in Sqlite?
> 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?
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?
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] Filter results based on contents of another single field table
I recently had a case where I needed to stack the strings that I did want,
minus those that I didn't. The in() and like() options weren't all that
flexible given their assumption of known strings and I needed to
accommodate near-hits. I found that group_concat() and regexp() work pretty
well together, so that you can have offsetting populations:
First, arrange two views, one with the strings you're seeking, then another
with those you're skipping. The subquery here uses a reference table
(tbl_misc) and so it's optional, but allows for an external maintenance
point...in case this is a persistent situation. One caveat though: regexp()
is intentionally limited in the general release, and a little flaky when it
comes to collations. It does handle ^string vs string$, but only if they're
placed accordingly at the head or back of the line...
SELECT DISTINCT
-- Select only column_name values that match [SEEK|SKIP] list in
tbl_misc...
column_name
FROM
vw_OD_distinct_columnnames
WHERE
column_name
-- Works as hard-coded list...
-- REGEXP ('_BEG_|addr|init|loan|merch|name|score|ssn|_END_')
-- Initially failed to match as a flattened resultset...
-- Resolved by appending a dummy string...and matching suffix (_BEG_ ..
_END_)
REGEXP (
SELECT
"'_BEG_|" || group_concat(code_val,"|") || "|_END_'" regex_arr
FROM (
SELECT
code_val
FROM
tbl_misc
WHERE
code_key
== 'SEEK' -- alternatively 'SKIP'
AND attrib
IS NULL
GROUP BY
1
)
)
AND ( -- Only select columns that are well-formed, ie "_" or alphabetical
start...
unicode(lower(column_name))
== 95
OR
unicode(lower(column_name))
between 97
and 122
)
ORDER BY
-- Eliminate case factors in sorting...
CASE WHEN unicode(lower(column_name)) < 95
THEN unicode(lower(column_name)) + 32
ELSE unicode(lower(column_name))
END
,1
Then a subsequent view joins the two together, using EXCEPT:
SELECT DISTINCT
-- Streamlined list of column_names, based on _seek and _skip...
column_name
FROM ( -- Outer query required to offset quirk that prevented ORDER BY
below...
SELECT
*
FROM
vw_sub_strings_seek
EXCEPT
SELECT
*
FROM
vw_sub_strings_skip
)
ORDER BY
CASE WHEN unicode(lower(column_name)) < 95
THEN unicode(lower(column_name)) + 300
ELSE unicode(lower(column_name))
END
Regards.
Brian P Curley
On Wed, Apr 19, 2017 at 4:47 PM, R Smith wrote:
>
> On 2017/04/19 9:12 PM, Stephen Chrzanowski wrote:
>
>> I'm attempting to get a report given by TrustWave trimmed down to results
>> that can be more easily managed. I've taken the results of a report,
>> cleaned it up with Excel, then used SQLite Expert to import that result
>> into a database.
>>
>> Here are the two table DDLs:
>>
>> CREATE TABLE [SkipRemed] (
>>[Skip] CHAR);
>>
>>
>> CREATE TABLE [TWScan] (
>>[ExtIP] CHAR, [IntIP] CHAR, [Service] CHAR, [VulnName] CHAR,
>> [Desc] CHAR,
>>[Remediation] CHAR, [Port] CHAR, [Severity] CHAR, [CVE] CHAR,
>> [Ticket] INTEGER);
>>
>> There's only 2049 results in TWScan, so I'm not concerned about speed, and
>> there is no direct relationship between the two tables. That is the
>> entire
>> DDL for the entire database.
>>
>> What I want to do is use SkipRemed to "Filter Out" or "Filter For" results
>> in TWScan based on the Remediation table. The difference between Out and
>> For would be just in the calling query. Right now, I want to filter OUT
>> results in SkipRemed so I can more easily see what reports are commonly
>> themed and result in one action being required to correct. (IE: Upgrade
>> PHP or Apache).
>>
>> The query I've been messing with is this:
>>
>> *select distinct ExtIP, IntIP, Service,Remediation from PMEScan where
>> Remediation not like (select distinct Skip from SkipRemed) order by
>> upper(Desc),upper(Service)*
>>
>> I don't get the results I want, unless I use the actual full text of the
>> Remediation text. I've changed Skip to '%'||Skip||'%' in the subquery but
>> that doesn't get me the results I want either.
>>
>> Somethings up with my logic, not sure where. Ideas?
>>
>
> SELECT DISTINCT ExtIP, IntIP, Service, Remediation
> FROM PMEScan
> WHERE NOT EXISTS(SELECT 1 FROM SkipRemed WHERE Remediation LIKE Skip)
>AND ...
> ORDER BY ...
>
>
> Should do it.
> Cheers,
> Ryan
>
>
> ___
> 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] Filter results based on contents of another single field table
On 2017/04/19 9:12 PM, Stephen Chrzanowski wrote: I'm attempting to get a report given by TrustWave trimmed down to results that can be more easily managed. I've taken the results of a report, cleaned it up with Excel, then used SQLite Expert to import that result into a database. Here are the two table DDLs: CREATE TABLE [SkipRemed] ( [Skip] CHAR); CREATE TABLE [TWScan] ( [ExtIP] CHAR, [IntIP] CHAR, [Service] CHAR, [VulnName] CHAR, [Desc] CHAR, [Remediation] CHAR, [Port] CHAR, [Severity] CHAR, [CVE] CHAR, [Ticket] INTEGER); There's only 2049 results in TWScan, so I'm not concerned about speed, and there is no direct relationship between the two tables. That is the entire DDL for the entire database. What I want to do is use SkipRemed to "Filter Out" or "Filter For" results in TWScan based on the Remediation table. The difference between Out and For would be just in the calling query. Right now, I want to filter OUT results in SkipRemed so I can more easily see what reports are commonly themed and result in one action being required to correct. (IE: Upgrade PHP or Apache). The query I've been messing with is this: *select distinct ExtIP, IntIP, Service,Remediation from PMEScan where Remediation not like (select distinct Skip from SkipRemed) order by upper(Desc),upper(Service)* I don't get the results I want, unless I use the actual full text of the Remediation text. I've changed Skip to '%'||Skip||'%' in the subquery but that doesn't get me the results I want either. Somethings up with my logic, not sure where. Ideas? SELECT DISTINCT ExtIP, IntIP, Service, Remediation FROM PMEScan WHERE NOT EXISTS(SELECT 1 FROM SkipRemed WHERE Remediation LIKE Skip) AND ... ORDER BY ... Should do it. Cheers, Ryan ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Filter results based on contents of another single field table
I don't think like works with a subquery as its righthand operand. Or at least not the way you're expecting it to. It's probably only using the very first result of the subquery for all the comparisons. If you're looking for an exact match then what Simon suggested is the way to go. If you're going to have actual match patterns in SkipRemed then you're going to need a join in there. On 19 Apr 2017, at 8:12pm, Stephen Chrzanowski wrote: > The query I've been messing with is this: > > *select distinct ExtIP, IntIP, Service,Remediation from PMEScan where > Remediation not like (select distinct Skip from SkipRemed) order by > upper(Desc),upper(Service)* I would have expected select distinct ExtIP, IntIP, Service,Remediation from PMEScan where Remediation NOT IN (SELECT Skip from SkipRemed) order by upper(Desc),upper(Service) > I don't get the results I want, unless I use the actual full text of the > Remediation text. I've changed Skip to '%'||Skip||'%' in the subquery but > that doesn't get me the results I want either. What are the affinities of the Remediation and Skip columns ? What is it doing ? Skipping ones you want to include, or including ones you want to skip ? Both ? Can you give examples of the Remediation and Skip values ? 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] Filter results based on contents of another single field table
As I understand it, "IN" presents an exact match, case sensitive
comparison. Using LIKE was thought to get a list of substrings that could
be anywhere in any string found within the Skip field. So if a row in the
Skip field contained "%Apache HTTP%" then I'd like to see, or not see,
depending on the NOT being present in the outer query, the appropriate
results. For example, if I did not want to see any Remediation with the
text "Apache HTTP", I'd enter as a row in Skip "Apache HTTP" or "%Apache
HTTP%" and the query I provided would give me all results that do NOT
contain "Apache HTTP" in PMEScan. The "Not seeing" portion was to get rid
of visual garbage so I can further refine the result set, and make sure I'm
adding criteria I need to tag into the ticket.
Everything is plain text, defined in the table def as just CHAR, probably
stored as Latin1 or UTF8, except the Ticket which is defined as an
integer... but.. knowing SQLite and it not really caring about field
types
An example row in TWScan (Renamed from PMEScan) would be:
ExtIPIntIPServiceVulnNameDescRemediationPort
SeverityCVETicket
x.x.x.x y.y.y.ySomeWebService_Public"Apache HTTP Server
mod_log_config Denial of Service Vulnerability""Apache HTTP Server
contains a vulnerability in the mod_log_config component that could allow a
remote attacker to crash the web server.""This vulnerability was fixed
with the release of versions 2.4.9 and 2.2.27 of Apache HTTP Server.
However, it is recommended that you upgrade to the latest available
release."443MCVE-2014-00980
An example row in SkipRemed would be
Skip
Apache HTTP
%Apache HTTP%
The query I'm using to get the list of services & existing Remediations:
select distinct VulnName,Remediation from TWScan where Remediation not in
(select distinct Skip from SkipRemed) and Severity in ('M','H') and Ticket
is null order by Remediation like '%Apache%' desc, upper(Remediation);
(This sorts anything to do with Apache near the top of the result list,
then sorts based on text)
On Wed, Apr 19, 2017 at 3:30 PM, Simon Slavin wrote:
>
> On 19 Apr 2017, at 8:12pm, Stephen Chrzanowski
> wrote:
>
> > The query I've been messing with is this:
> >
> > *select distinct ExtIP, IntIP, Service,Remediation from PMEScan where
> > Remediation not like (select distinct Skip from SkipRemed) order by
> > upper(Desc),upper(Service)*
>
> I would have expected
>
> select distinct ExtIP, IntIP, Service,Remediation from PMEScan where
> Remediation NOT IN (SELECT Skip from SkipRemed) order by
> upper(Desc),upper(Service)
>
> > I don't get the results I want, unless I use the actual full text of the
> > Remediation text. I've changed Skip to '%'||Skip||'%' in the subquery
> but
> > that doesn't get me the results I want either.
>
> What are the affinities of the Remediation and Skip columns ?
>
> What is it doing ? Skipping ones you want to include, or including ones
> you want to skip ? Both ? Can you give examples of the Remediation and
> Skip values ?
>
> 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?
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?
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] Filter results based on contents of another single field table
On 19 Apr 2017, at 8:12pm, Stephen Chrzanowski wrote: > The query I've been messing with is this: > > *select distinct ExtIP, IntIP, Service,Remediation from PMEScan where > Remediation not like (select distinct Skip from SkipRemed) order by > upper(Desc),upper(Service)* I would have expected select distinct ExtIP, IntIP, Service,Remediation from PMEScan where Remediation NOT IN (SELECT Skip from SkipRemed) order by upper(Desc),upper(Service) > I don't get the results I want, unless I use the actual full text of the > Remediation text. I've changed Skip to '%'||Skip||'%' in the subquery but > that doesn't get me the results I want either. What are the affinities of the Remediation and Skip columns ? What is it doing ? Skipping ones you want to include, or including ones you want to skip ? Both ? Can you give examples of the Remediation and Skip values ? 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?
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] Filter results based on contents of another single field table
I'm attempting to get a report given by TrustWave trimmed down to results that can be more easily managed. I've taken the results of a report, cleaned it up with Excel, then used SQLite Expert to import that result into a database. Here are the two table DDLs: CREATE TABLE [SkipRemed] ( [Skip] CHAR); CREATE TABLE [TWScan] ( [ExtIP] CHAR, [IntIP] CHAR, [Service] CHAR, [VulnName] CHAR, [Desc] CHAR, [Remediation] CHAR, [Port] CHAR, [Severity] CHAR, [CVE] CHAR, [Ticket] INTEGER); There's only 2049 results in TWScan, so I'm not concerned about speed, and there is no direct relationship between the two tables. That is the entire DDL for the entire database. What I want to do is use SkipRemed to "Filter Out" or "Filter For" results in TWScan based on the Remediation table. The difference between Out and For would be just in the calling query. Right now, I want to filter OUT results in SkipRemed so I can more easily see what reports are commonly themed and result in one action being required to correct. (IE: Upgrade PHP or Apache). The query I've been messing with is this: *select distinct ExtIP, IntIP, Service,Remediation from PMEScan where Remediation not like (select distinct Skip from SkipRemed) order by upper(Desc),upper(Service)* I don't get the results I want, unless I use the actual full text of the Remediation text. I've changed Skip to '%'||Skip||'%' in the subquery but that doesn't get me the results I want either. Somethings up with my logic, not sure where. Ideas? ___ 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?
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] How difficult for bindings to support API 14 or below?
On 04/19/2017 06:28 PM, Philip Warner wrote: FWIW, it compiles and builds with API set to 14...I'm just worried I'll get some esoteric breakage. Any insights from someone who understands NDK/API level interactions? Looking at old chat logs, I don't think we have tried to use that code with API level 14. It might work. Or it might not. Google rewrote the code that the sqlite.org/android code is based on for the release associated with API level 15. So the official API level 14 stuff is quite different. I think that's the only reason we thought it would only work on level 15 or greater. Dan. On 18/04/2017 11:49 PM, Philip Warner wrote: I notice that the Android bindings support API 16 by default, with a custom branch for API 15. Naively, it looks like API 15 support has a small number of changes relating to a missing/changed(?) exception. Can anyone offer insight into how difficult it would be to support API 14 or below? It does not need to be source code compatible with the old android salute API from my point off view it just needs to work/run. ___ 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] gnu gcc address sanitizer finds stack-use-after-scope in sqlite3VdbeExec
On 04/18/2017 07:12 PM, Vitti wrote:
Hi there, I just downloaded version 3.18 and after compiling it with
the following command
gcc shell.c sqlite3.c -g -fsanitize=address -DSQLITE_DEBUG -o sqlite
-ldl -pthread
Hi,
Thanks for reporting this. I haven't been able to reproduce it with any
of the released versions of gcc so far. Does it not like us using a
switch() to jump into the middle of a block that contains variable
declarations?
If you compile the code below with gcc 7.0.1:
gcc code.c -fsantitize=address -o tst
and then run:
./tst 2
Does the sanitizer make the same complaint?
Thanks,
Dan.
/**/
#include
#include
int main(int argc, char **argv){
int a, b, c;
if( argc!=2 ) return -1;
a = atoi(argv[1]);
b = 2;
c = 0;
switch( a ){
case 1: {
int res;
b = 1;
case 2:
res = b;
c = res;
break;
}
}
printf("c=%d\n", c);
return 0;
}
/**/
running sqlite command
.selftest --init
I get a sanitizer error message.
In my opinion this is probably due to erroneous usage of variable res
in the branches
of the huge switch in sqlite3VdbeExec
I have Linux Fedora 25 on an x86-64 hardware.
gnu gcc 7.0.1
This one occurred also in 3.16 from Qt 5.9 where I found it initially.
Best regards
Vitti
=
==3045==ERROR: AddressSanitizer: stack-use-after-scope on address
0x7ffca9d69510 at pc 0x00529307 bp 0x7ffca9d68bf0 sp
0x7ffca9d68be8
WRITE of size 4 at 0x7ffca9d69510 thread T0
#0 0x529306 in sqlite3VdbeExec /home/vitti/sqlite/sqlite3.c:82355
#1 0x5087c2 in sqlite3Step /home/vitti/sqlite/sqlite3.c:76607
#2 0x508e38 in sqlite3_step /home/vitti/sqlite/sqlite3.c:76668
#3 0x5bbe71 in sqlite3_exec /home/vitti/sqlite/sqlite3.c:110900
#4 0x40dc26 in createSelftestTable /home/vitti/sqlite/shell.c:2102
#5 0x424347 in do_meta_command /home/vitti/sqlite/shell.c:5867
#6 0x429f00 in process_input /home/vitti/sqlite/shell.c:6730
#7 0x42c8a2 in main /home/vitti/sqlite/shell.c:7365
#8 0x2ae9ef525400 in __libc_start_main (/usr/lib64/libc.so.6+0x20400)
#9 0x402ef9 in _start
(/home/vitti/sqlite-amalgamation-318/sqlite+0x402ef9)
Address 0x7ffca9d69510 is located in stack of thread T0 at offset 672 in frame
#0 0x510037 in sqlite3VdbeExec /home/vitti/sqlite/sqlite3.c:78743
This frame has 47 object(s):
[32, 36) 'p2'
[96, 100) 'offset'
[160, 164) 'avail'
[224, 228) 't'
[288, 292) 'len'
[352, 356) 'iMeta'
[416, 420) 'iMeta'
[480, 484) 'pgno'
[544, 548) 'res'
[608, 612) 'res'
[672, 676) 'res' <== Memory access at offset 672 is inside this variable
[736, 740) 'res'
[800, 804) 'res'
[864, 868) 'res'
[928, 932) 'res'
[992, 996) 'res'
[1056, 1060) 'res'
[1120, 1124) 'res'
[1184, 1188) 'res'
[1248, 1252) 'iMoved'
[1312, 1316) 'nChange'
[1376, 1380) 'pgno'
[1440, 1444) 'nErr'
[1504, 1512) 'iB'
[1568, 1576) 'iA'
[1632, 1640) 'uA'
[1696, 1704) 'pC'
[1760, 1768) 'nEntry'
[1824, 1832) 'v'
[1888, 1896) 'v'
[1952, 1960) 'rowid'
[2016, 2024) 'val'
[2080, 2088) 'x'
[2144, 2152) 'pVCur'
[2208, 2216) 'rowid'
[2272, 2284) 'aRes'
[2336, 2360) 'r'
[2400, 2424) 'r'
[2464, 2488) 'r'
[2528, 2552) 'r'
[2592, 2616) 'initData'
[2656, 2704) 'x'
[2752, 2800) 'x'
[2848, 2904) 'sContext'
[2944, 3016) 'sMem'
[3072, 3144) 't'
[3200, 3272) 'sMem'
HINT: this may be a false positive if your program uses some custom
stack unwind mechanism or swapcontext
(longjmp and C++ exceptions *are* supported)
SUMMARY: AddressSanitizer: stack-use-after-scope
/home/vitti/sqlite/sqlite3.c:82355 in sqlite3VdbeExec
Shadow bytes around the buggy address:
0x1000153a5250: f1 f1 f8 f2 f2 f2 f2 f2 f2 f2 f8 f2 f2 f2 f2 f2
0x1000153a5260: f2 f2 f8 f2 f2 f2 f2 f2 f2 f2 f8 f2 f2 f2 f2 f2
0x1000153a5270: f2 f2 f8 f2 f2 f2 f2 f2 f2 f2 f8 f2 f2 f2 f2 f2
0x1000153a5280: f2 f2 04 f2 f2 f2 f2 f2 f2 f2 04 f2 f2 f2 f2 f2
0x1000153a5290: f2 f2 04 f2 f2 f2 f2 f2 f2 f2 04 f2 f2 f2 f2 f2
=>0x1000153a52a0: f2 f2[f8]f2 f2 f2 f2 f2 f2 f2 04 f2 f2 f2 f2 f2
0x1000153a52b0: f2 f2 04 f2 f2 f2 f2 f2 f2 f2 04 f2 f2 f2 f2 f2
0x1000153a52c0: f2 f2 04 f2 f2 f2 f2 f2 f2 f2 f8 f2 f2 f2 f2 f2
0x1000153a52d0: f2 f2 f8 f2 f2 f2 f2 f2 f2 f2 04 f2 f2 f2 f2 f2
0x1000153a52e0: f2 f2 04 f2 f2 f2 f2 f2 f2 f2 04 f2 f2 f2 f2 f2
0x1000153a52f0: f2 f2 04 f2 f2 f2 f2 f2 f2 f2 04 f2 f2 f2 f2 f2
Shadow byte legend (one shadow byte represents 8 application bytes):
Addressable: 00
Partially addressable: 01 02 03 04 05 06 07
Heap left redzone: fa
Freed heap region: fd
Stack left redzone: f1
Stack mid redzone: f2
Stack right
Re: [sqlite] Is it possible to create the Stored Procedure (SP) in Sqlite?
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?
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] Database corruption, and PRAGMA fullfsync on macOS
I'm using SQLCipher in my main app and it's using SQLite 3.15.2. However, my little sample app where I could also easily demonstrate the problem, is using whatever the built-in SQLite version is in macOS Sierra 10.12.4. I couldn't find SQLITE_MMAP_READWRITE in the Couchbase Lite source anywhere, so I'm sure it's not setting it. All I know now is with mmap I/O on, I get the corruption when the power goes out. With it off, I don't. On Wed, Apr 19, 2017 at 12:21 AM, Dan Kennedy wrote: > On 04/19/2017 02:42 AM, Jens Alfke wrote: > >> On Apr 18, 2017, at 2:20 AM, Deon Brewis wrote: >>> >>> It's not like it was subtle - it's a dead on repro. I was able to repro >>> this by doing a power cycle 2 hours after shutting the app down. OSX didn't >>> seem to have any interest in flushing mmap files until you soft reboot the >>> machine. >>> >> OK, hang on — I just reread the docs on memory-mapped I/O in SQLite. >> Memory-mapping is *only used for reads*, never for writes: >> > > It was used for writes in versions before 3.10.0 (January 2016). And still > is if SQLITE_MMAP_READWRITE is defined (not the default). > > Dan. > > > > > > > >> When updating the database file, SQLite always makes a copy of the page content into heap memory before modifying the page. This is necessary for two reasons. First, changes to the database are not supposed to be visible to other processes until after the transaction commits and so the changes must occur in private memory. Second, SQLite uses a read-only memory map to prevent stray pointers in the application from overwriting and corrupting the database file. >>> — https://www.sqlite.org/mmap.html >> >> Therefore I can’t imagine how using it could trigger database corruption. >> It doesn’t affect the way data is written at all! >> >> I accept that both of you have experimentally seen that memory-mapping >> leads to corruption, so I can only assume that either the above >> documentation is wrong, or that there’s some subtle bug in SQLite that >> alters the way data is written when memory-mapping is enabled. >> >> —Jens >> ___ >> 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?
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] performing an UPDATE on a table-valued function
On 4/19/17, Timothy Stack wrote: > > Richard, if I made the necessary changes to the parser, docs, and updated > the carray extension as an example, would you accept the patch? > Probably not. But an actual demonstration showing that your proposed enhancement is useful and does not impact performance for the (literally) billions of people who do not use your enhancement would be one step toward getting the enhancement in the core. (1) SQLite is public domain. In order to keep it that way, we are unable to accept contributions from unknown persons on the internet. (2) Most of the work involved in an enhancement is supporting it for the next 37 years. (3) One of the most important jobs of a project leader is learning when to say "no" and preventing feature-creep and loss of focus. I have not done a great job of that in the past. I'm trying to do better. New features need to be a clear win before they are accepted. -- 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] performing an UPDATE on a table-valued function
On Sun, Apr 16, 2017 at 3:40 PM, Richard Hipp wrote:
> > On 16 Apr 2017, at 10:57pm, Timothy Stack
> > wrote:
> >
> >> UPDATE foo SET col0 = 'bar' WHERE hidden_field = 'baz'
> >>
> >> Having the real syntax, like the following, would be nice though:
> >>
> >> UPDATE foo('baz') SET col0 = 'bar'
>
> Whether or not the suggest is a good idea is a different matter. I'm
> skeptical. A function is typically not an l-value and is hence not
> typically something that can be modified.
Richard, if I made the necessary changes to the parser, docs, and updated
the carray extension as an example, would you accept the patch?
thanks,
tim
> --
> 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-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?
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] How difficult for bindings to support API 14 or below?
FWIW, it compiles and builds with API set to 14...I'm just worried I'll get some esoteric breakage. Any insights from someone who understands NDK/API level interactions? On 18/04/2017 11:49 PM, Philip Warner wrote: I notice that the Android bindings support API 16 by default, with a custom branch for API 15. Naively, it looks like API 15 support has a small number of changes relating to a missing/changed(?) exception. Can anyone offer insight into how difficult it would be to support API 14 or below? It does not need to be source code compatible with the old android salute API from my point off view it just needs to work/run. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE_OMIT_* vs amalgamation
On 4/19/17, Kim Gräsman wrote: > I'm building for both Windows and Linux and it's a reproducible build, > so I'd rather not depend on anything extra. The only thing extra you need is tclsh installed as it is used during the build process. Besides TCL, just a C compiler and the ordinary tools. On unix, just run "./configure; make". On Windows with MSVC the command is "nmake /f makefile.msc" -- 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] Issue on Windows 10 app
Hello, I have an issue related to an app published on the Windows Store. The app which I'm talking about is Weather 14 days. What occurs is the following. We published some days ago an update of our app in the Windows Store, version 3.2.8.0, on 6th April. Since that, the problem we have is that, inside Developer Dashboard, on Weather 14 days -> Analytics -> Health, we're receiving a lot of weird errors related to SQLite, with an average of 300.000-400.000 errors/day. The SQLite which we're using is "SQLite for Universal Windows Platform" version 3.17.0. The error is the following: STOWED_EXCEPTION_SQLite.Net.SQLiteException_80131500_tiempocom.dll!$26_SQLite::Net::Platform::WinRT::SQLiteApiWinRT.Prepare2 Since the update, the error appears 3.800.864 times in the moment that i'm writing this email. We have no idea about the error, also we couldn't find any information about it, so please if you could tell us what it's happening, we'll very grateful. Also I attach on the email some pictures where the error can be seen. Alejandro ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Tad - An Open Source Pivot Table Viewer for CSVs and sqlite
On Wed, Apr 19, 2017 at 8:02 AM, Antony Courtney wrote: > Tad uses LIMIT and OFFSET to only pull as many rows as needed to display > the current viewport, even in the presence of pivoting, so it's possible to > use Tad on large tables. > I'm no expert at all in this domain, but I believe it was discussed several times on this list that using LIMIT and OFFSET for a "scrolling cursor" is not the most efficient way to achieve that goal. Hopefully experts like DRH, James, and co. will provide more information and pointers (which I can't find right now). FWIW. --DD ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
