LIKE to use an index it has to be a full index?
Regards,
Mark
--
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
e error: I
was keeping (Perl) statement handles around after the database handles
had expired. That doesn't mean that there isn't an issue with
how DBD::SQLite is using SQLite, but I no longer have the motivation to
track down that error when the easy answer to my problem is "don't do
that."
Search for "scheam=" to find it.
--
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
On Mon Aug 24, 2015 at 08:46:57AM +0200, Mark Lawrence wrote:
>
> You can achieve this using a partial index[1] on the Books.title
> column, which is used only when the author is null. A test script to
My apologies. It appears from the mailing list archive this was already
mention
--- --
-- History of Scotland A. Jones
-- History of Scotland T. Smith
-- Manual of DOSNULL
insert into Books values(4, 'Manual of DOS', NULL);
-- Error: near line 37: UNIQUE constraint failed: Books.title
[1] https://www.sqlite.org/partialindex.html
Mark
--
Mark Lawrence
0,0,0,"COMPOUND SUBQUERIES 1 AND 2 (UNION ALL)"
Is this expected? For my real query the x_all data is relatively
complicated with lots of joins, and I was wondering if it is absolutely
necessary for SQLite to be doing double the amount of work it needs to?
Mark.
--
Mark Lawrence
;
-- selectidorder fromdetail
-- -- -- --
-- 0 0 0 SCAN TABLE x
This would potentially allow me to shortcut some largish UNION
statements.
Mark.
--
Mark Lawrence
Home: +41 44 520 12 59
s.
I'm a little curious about why you say a CTE statement is slower than a
VIEW for large tables. I don't have large tables to test on but I get
the same query plan for both versions on small test tables. What
changes with size?
--
Mark Lawrence
___
sq
My apologies for the previous completely wrong mesage. I got mixed up
with operator meaning & precedence...
On Mon Oct 13, 2014 at 02:39:40PM +0100, Paul Sanderson wrote:
>
> My actual code is as folows
>
> (CASE visits.transition & 0xFF00 WHEN 0x0080 THEN 'Blocked'
> ELSE '' END
On Mon Oct 13, 2014 at 04:51:16PM +0200, Mark Lawrence wrote:
> On Mon Oct 13, 2014 at 02:39:40PM +0100, Paul Sanderson wrote:
>
> Perl equivalent:
>
> use feature 'say';
> my $a = 0x0080 | 0x0800;
>
> say $a & 0x0080;
> say $a & 0x
say $a & 0x0800;
say $a & 0x0800 & 0x08000000;
Result:
8388608
134217728
134217728
--
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
name, new_value)
;
Could the SQLite team perhaps comment on how difficult this would be to
implement?
--
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
y: for non-C languages there is less translation between
the language/SQLite boundary.
--
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
need to worry
about. If you keep a reference to the statement handle somewhere then
you can run execute() on it again, and when all the references are
gone Perl will do what it needs to do memory wise which *may* involve
calls to the underlying SQLite C API as needed.
--
Mark Lawrence
rl DBI was
intended to support. Although I haven't specifically measured the
memory use, I do the above quite a lot without a problem.
By the way, the last call to fetchrow_array() (that returns 'undef')
implicitly calls finish() internally.
--
Mark Lawrence
_
on yourself to find out how.
It is probbaly better that we stop this thread here as it is off-topic
for the mailing list. But feel free to reply to me privately if you
still have issues getting it to run.
--
Mark Lawrence
___
sqlite-users mailing li
/bin/db-browser
It can be installed as follows:
sudo cpan App::DBBrowser
After which the "db-browser" script should be in your path:
db-browser -s $DIRECTORY_CONTAINING_SQLITE_DATABASE
--
Mark Lawrence
___
sqlite-users mailing list
sql
e
code afterwards.
--
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
lies when an insert does
not provide a value, but I don't see the contradiction. The table
defines an *explicit* default that should (to my mind) override any
kind of magical-in-the-absence-of-a-default-default. Such an explicit
default should certainly not be accepted if it is going to be ignored.
val
--- --
4841191733402647298 a
I get the expected result if I create the table WITHOUT ROWID.
--
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin
re out that the columns are the same and
only do it once?
If SQLite is capable of determining that the same expression is used
twice, why not just accept a SELECT expression?
--
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqli
On Thu Sep 25, 2014 at 08:32:29PM +0200, Mark Lawrence wrote:
> GROUP BY on a result column fails with "ambiguous column name":
>
> SELECT
> COALESCE(x.id, y.id) AS id
> FROM
> y
> LEFT JOIN
> x
> ON
>
hich doesn't enlighten me
much.
--
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
On Mon Sep 15, 2014 at 10:51:04AM +0200, Mark Lawrence wrote:
>
> Normally one could use a CTE to do the work once:
>
> WITH
> cte
> AS (
> SELECT 1 AS x, 2 AS y
> )
> UPDATE
> t
> SET
> x = cte.x,
>
that works in PostgreSQL and
I could use it in SQLite for performance reasons.
UPDATE
t
SET
(x,y) = (SELECT 1,2)
;
Alternatively, is there any effort underway to make CTEs work inside
triggers?
--
Mark Lawrence
___
sqlite-users
ill find the combination of COUNT
without a GROUP BY to be unintuitive, but at least I know why now.
--
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
the tables are empty? Adding a "GROUP BY x.id" returned the
expected empty set.
--
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
insert each value as it appears. The PerlData virtual table would
instead let me keep them in an @array variable to be used in a
single, simple, SQL statement at the end. This would dramatically
reduce the number calls I make into SQLite.
--
Mark Lawrence
__
,
col2,
col3
)
SELECT
col1,
col2,
col3
FROM
main.messages
WHERE
absid = some_value
;
And then retrieve the last rowid:
SELECT last_insert_rowid();
Mark.
--
Mark Lawrence
_
t fails then the
transaction is invalid (thereby ignoring the COMMIT). SQLite treats
that situation differently.
--
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
subscribe.
For this list it shows:
List-Unsubscribe:
<http://sqlite.org:8080/cgi-bin/mailman/options/sqlite-users>,
<mailto:sqlite-users-requ...@sqlite.org?subject=unsubscribe>
In this case a mail to sqlite-users-requ...@sqlite.org with the
subject "unsubscribe&
On Thu Apr 24, 2014 at 09:42:20AM +0200, Mark Lawrence wrote:
> In triggers I often want to include information when raising an error.
> However, RAISE doesn't accept dynamic/derived arguments which would
> otherwise be valid in a SELECT.
>
> SELECT RAISE(ABORT, 'strin
of a work-around?
--
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
t those independently client-side. If you manage to determine some
kind of work-around for the condition occuring I'd appreciate hearing
about it, although potentially 7 co-routines hitting the same register
is 7 times harder to avoid...
--
Mark Lawrence
On Tue Feb 25, 2014 at 05:24:55PM +0100, Mark Lawrence wrote:
> On Tue Feb 25, 2014 at 09:37:41AM -0500, Richard Hipp wrote:
> > Can you please send the database schema, and possibly some test data?
>
> Attached is an SQL file containing enough to reproduce the issue on my
> sy
can reproduce the
issue with what is in the file.
Mark.
--
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
project_status 1
project d2e2b16d45d4a7e514da610cdc46cbcfec29431a
As far as I understand union all, it should never return less than the
sum of the individual queries. Any ideas?
Mark.
--
Mark Lawrence
___
behaviour?
Mark.
--
Mark Lawrence
CREATE TABLE t(
id integer
);
INSERT INTO t VALUES(1);
INSERT INTO t VALUES(2);
INSERT INTO t VALUES(3);
PRAGMA reverse_unordered_selects = ON;
SELECT
GROUP_CONCAT(ordered.id)
FROM
(SELECT
id
FROM
t
ORDER BY
id ASC
38 matches
Mail list logo
