I recall that
http://blog.heapanalytics.com/postgresqls-powerful-new-join-type-lateral/ shows
how Pg 9.3's LATERAL join is useful in practice, as it lets you do in
declarational SQL what you may have needed procedural code for before, in which
case it is an improvement. -- Darren Duncan
own transaction and remember the value, then use that
remembered value in your main transaction that you explicitly do afterwards.
Do you understand what's going on now?
-- Darren Duncan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http
itself, as that practice
generally leads to huge security problems / SQL injection (although if your
language is strongly typed an int wouldn't do it, but a string would). -- Darren
Duncan
On 2014-11-04 1:47 PM, Drago, William @ CSG - NARDAEAST wrote:
All,
I've been pulling my hair out trying
-05')
where the content of the IN would have the first item and the last item of the
list, but that's it? Thanks.
You're talking about a range/interval.
In SQL it is spelled like this:
BETWEEN '2014-01-01' AND '2014-01-05'
-- Darren Duncan
changed by accident.
-- Darren Duncan
On 2014-09-06, 7:22 PM, Richard Warburton wrote:
Hi,
Brief:
Should transactions be used for ensuring consistency between multiple
queries? And if so, after I've finished is there a reason why I should not
call commit?
Background:
I'm using SQLite for a web
A common logical error that may be affecting you is, do your dates include a
time portion or are they just year-month-day? If they include a time portion,
then records from Dec 31 likely won't be counted as your 'between' arguments may
be equivalent to '2013-12-31 00:00:00'. -- Darren Duncan
other value, which is how it is supposed to be.
-- Darren Duncan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
), t FROM (
SELECT cust, ProjFund, Xtra8, coalesce(billdate,bdate) as t FROM
LSOpenProjects WHERE billdate BETWEEN '2013-01-01' AND '2013-12-31' OR bdate
BETWEEN '2013-01-01' AND '2013-12-31'
) x GROUP BY substr(t,1,7), cust;
-- Darren Duncan
___
sqlite
On 2014-08-31, 9:35 PM, Darren Duncan wrote:
On 2014-08-31, 9:10 PM, jose isaias cabrera wrote:
SELECT cust, sum(ProjFund), sum(Xtra8), coalesce(billdate,bdate) as t FROM
LSOpenProjects WHERE billdate BETWEEN '2013-01-01' AND '2013-12-31' OR bdate
BETWEEN '2013-01-01' AND '2013-12-31' GROUP
.
-- Darren Duncan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Sorry, the count thing was actually Mark Halegua's question. -- Darren Duncan
On 2014-08-27, 8:58 PM, Darren Duncan wrote:
On 2014-08-27, 8:41 PM, Keith Medcalf wrote:
this may seem like a small issue, but I'm not sure if the solutions I've
found on the web will do what I want in a low memory
in a temporary
table and then use that for the join in a subsequent select.
-- Darren Duncan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Is something wrong with the configuration of this sqlite-users list?
A message of subject Porting SQLite to plain C RTOS was allowed and
distributed through it this morning with attachments.
Not only attachments, but about 5MB of attachments.
-- Darren Duncan
. There's no
reason that table names can't be values in principle. -- Darren Duncan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
in which SQLite is more powerful than MySQL (but not PostgreSQL), the WITH
support being another, and subjecting data definition to transactions is another.
-- Darren Duncan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi
have seen several others that are on the realistic side.
But a counter-example is a show I saw where they had programming code but it
was actually HTML source, which really shows those ones didn't do their homework.
-- Darren Duncan
___
sqlite
for the name of a
concrete type, like with integer and ratio. (Well strictly speaking all of
these could be abstract types, but the latter set are more specific in meaning,
and in particular ratio and float imply a representation while the others don't.
-- Darren Duncan
condition saying which fields you
want to be used for matching in the join, and also replace the select * with a
specific list of fields you want to match up for the union.
-- Darren Duncan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http
require creation of separate schema objects, while
someone with read-only access to a db can use WITH, especially beneficial for
ad-hoc reports. -- Darren Duncan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin
WITH RECURSIVE without WITH. -- Darren Duncan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
that they want to go with WITH
RECURSIVE. So the CONNECT BY branch has been closed and we are starting to
work on a WITH RECURSIVE implementation.
That's great news! This will be a serious step up in feature set for SQLite,
both in ease of use and power, I look forward to it. -- Darren Duncan
be taking up with the makers or a users
group of the wrapper for help. You should be asking in some support forum
specific to Entity Framework about this problem. -- Darren Duncan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http
the whole database they may be useful with.
-- Darren Duncan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
today don't have the precondition of a transaction being
active to use them, so on their own savepoint is like a generalization of a
transaction. -- Darren Duncan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman
On 2013.10.15 10:34 PM, Petite Abeille wrote:
On Oct 16, 2013, at 7:20 AM, Darren Duncan dar...@darrenduncan.net wrote:
On 2013.10.14 11:58 PM, Sqlite Dog wrote:
seems like SQLite is not checking trigger SQL for invalid column names
until execution?
What you describe sounds like
to reverse it, at least for default semantics. -- Darren Duncan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
metadata as tablevars. -- Darren Duncan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
I don't think that being ACID and SQL compliant is the definition of a DBMS, far
from it. While it is true that typically anything which is ACID and SQL
compliant is a DBMS, lots of things can be a DBMS without being either ACID or
SQL compliant. See dBASE for example. -- Darren Duncan
of that is best to have in practice.
-- Darren Duncan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
like this:
execute format( 'SELECT %I FROM table that has that columnName',
(SELECT columnName FROM columnNameTable WHERE condition how to select limit
1) );
But I don't know if SQLite can do that in SQL; you might have to use the host
language.
-- Darren Duncan
support views, you could define views having the names that
the users want. In fact, that's what views are for, letting different users
have different interfaces to the same database.
-- Darren Duncan
___
sqlite-users mailing list
sqlite-users
subqueries before you talk about joins or grouping. On the other hand, I
suppose from an explanation point of view, a subquery in the SELECT list could
actually be a simpler thing to explain to a SQL newbie, so maybe that's why it
is first. -- Darren Duncan
produce a
fractional result while the latter guarantees a whole number result.) The
pragma is a bad idea. -- Darren Duncan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
.
-- Darren Duncan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
, and a very appropriate way to bring
in many kinds of extra functionality, such as regular expression support. And
I'm glad an extension for that is provided now, which gives much more power to
type constraint definitions. -- Darren Duncan
(' +
dataGridView1.Rows[i].Cells[Column1].Value + ', ' +
dataGridView1.Rows[i].Cells[Column2].Value + ', ' +
dataGridView1.Rows[i].Cells[Column3].Value + ', ' +
dataGridView1.Rows[i].Cells[Column4].Value + ', ' +
dataGridView1.Rows[i].Cells[Column5].Value + ');;
-- Darren Duncan
for example and end user of the DBD::SQLite
Perl module that wants to pull in the latest SQLite version to build it against,
without having to specify a version. We shouldn't expect such a user to have a
fossil client, they should just be able to pull the amalgamation tarball over
the web. -- Darren
databases per user, even if they may typically just use one. If users open your
program directly and not by double-clicking on a database file, you could
automatically bring up a prompt to make a new one, as if they used the new
database menu. -- Darren Duncan
Igor Korot wrote:
Darren,
On Tue, Oct 23, 2012 at 8:53 PM, Darren Duncan dar...@darrenduncan.net wrote:
You should not have an application installer, at all. Instead, you can ask
the question on where to store the database when the user opens your
program. Or better yet, your application
wanted an autonomous transaction, which
is different, depending on what you want.
-- Darren Duncan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
,
you'll have Windows 8 on your next trade-up, as it comes out later this month.
-- Darren Duncan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
automatically (though it could also be configured to happen
automatically).
-- Darren Duncan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Jay Kreibich wrote:
On Oct 8, 2012, at 3:36 PM, Darren Duncan dar...@darrenduncan.net wrote:
Of course, if that is done, then in order to have predictable performance we'd also want
to add some other statement one can invoke when they want to reclaim disk space later,
which actually goes
choice less
verbose; in practice, one would more likely do it how you did, however. --
Darren Duncan
Petite Abeille wrote:
On Sep 9, 2012, at 6:51 AM, Darren Duncan dar...@darrenduncan.net wrote:
You will need to use a subquery to do what you want, because you want to do a
join
, ChangeDate) in
(select RowID, max(ChangeDate) as ChangeDate from audtbl group by RowID);
-- Darren Duncan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
to actually download and play with it? -- Darren Duncan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
education, I'd like to know if there is an elegant way of achieving this in
a single query.
Thanks
Nige
You say something like this:
select myfield, count(*) as mycnt from mytbl group by myfield
... where myfield is the one containing the duplicates.
-- Darren Duncan
and Calibre -- Darren Duncan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
is to use Postgres 8.4+
(9.1 latest), which lets you do window functions directly in SQL. I know thats
a not-SQLite solution, but it may be the least work to accomplish what you want,
as its still terse/declarational SQL, and its also open source. -- Darren Duncan
, to also install DBI if you don't have it.
That's one of the reasons you use those CPAN clients, to effortlessly pull in
dependencies too, essentially like package managers.
Also, cpan and cpanplus are bundled with Perl 5.10.1, but cpanm you have to
install separately.
-- Darren Duncan
Steven Michalske wrote:
On Tue, Jan 10, 2012 at 3:19 PM, Darren Duncan dar...@darrenduncan.net wrote:
Steven Michalske wrote:
I would like to use the :nnn named parameters but have spaces in the
named parameters.
It seems that :nnn nnn, :nn nn, or :nn\ nn are all not recognized
Sreekumar TP wrote:
Is it possible to insert multiple rows using a single statement ?
Yes.
INSERT INTO foo (x, y)
VALUES (1,2), (3,4), (5,6),...;
INSERT INTO foo (x,y)
SELECT x, y FROM bar;
That's at least 2 ways.
-- Darren Duncan
___
sqlite
):
WITH (SELECT * FROM a) AS sfa,
(SELECT * FROM b) AS sfb :
SELECT * FROM m WHERE
c IN sfa OR
c IN sfb
AND (NOT c IN sfa
OR c IN sfb));
-- Darren Duncan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi
a trivial case of a semijoin where the table
you are filtering on has exactly 1 row whose field value is 3, and ostensibly
such WHERE clauses should also be optimizable.
-- Darren Duncan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http
those, same as a mailing list would, and people can
reply by email and then their replies end up on the forum as if someone had used
the web to do it.
It is important to have email copies of everything, which I can archive locally.
-- Darren Duncan
boolean operators would be for all other uses, where
the validity of one argument doesn't depend on the values of any of the other
arguments, and so the compiler can be free to reorder it.
-- Darren Duncan
___
sqlite-users mailing list
sqlite-users
to be more restrictive, it would be that one can't
reference a field directly in the select list that isn't in the group by unless
we are grouping by a key of the table that the fields in the select list are
from, so we have this uniqueness guarantee.
-- Darren Duncan
from using the SQLite source by itself
under the public domain, no matter how anyone gets their copy of SQLite,
whether
linked with readline or not. -- Darren Duncan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin
developed by and for libraries, and that has been used in production for a few
years now in many libraries.
You should adapt this or modify it to meet your needs rather than start a new
one, unless you can justify otherwise.
-- Darren Duncan
___
sqlite-users
an order number, such
as using the RANK() SQL window function would give you.
-- Darren Duncan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Darren Duncan wrote:
MySQL should be avoided like the plague.
I hereby retract my above-quoted statement as I realize that it is too severe a
statement to be making.
Instead I will say the following in its place:
MySQL should not be considered as the default choice of a non-lite SQL DBMS
Dagdamor wrote:
Darren Duncan dar...@darrenduncan.net писал(а) в своём письме Mon, 06 Jun
2011 05:08:45 +0600:
MySQL should not be considered as the default choice of a non-lite SQL
DBMS, for projects not currently using it, when you have a choice between
multiple SQL DBMSs; instead
Mr. Puneet Kishor wrote:
On Jun 3, 2011, at 6:16 PM, Darren Duncan wrote:
a. MySQL silently ignores all CHECK constraints in all engines, so for
example you can't even tell it you want a column to only hold values
between 1 and 10. Its in the MySQL docs: The CHECK clause is parsed
MySQL should be avoided like the plague. Use Postgres instead if you have to
switch to a larger SQL DBMS. But hopefully the help you've gotten so far will
extend your mileage with SQLite and you won't have to switch to anything yet.
--
Darren Duncan
Ian Hardingham wrote:
Guys, the server
Mr. Puneet Kishor wrote:
On Jun 3, 2011, at 1:19 PM, Darren Duncan wrote:
MySQL should be avoided like the plague.
why?
This is a long standing (un)conventional wisdom to which I too have hewed.
Now, it so happens, I will be starting work on a project that uses MySQL
exclusively, and has
? -- Darren Duncan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
you have both corresponding x and
y records. I'm sure there are various business rules that this would
effectively model. -- Darren Duncan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite
of and how does it differ from sum?
Also, it's better to call a function mean than avg if that's what is
intended, since there are other kinds of averages like median and mode.
-- Darren Duncan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http
. Maybe you're just wanting more operators so it is easier
to introspect or manipulate them? -- Darren Duncan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
the
radix point since that was kept track of along with the number. -- Darren Duncan
On Sat, Mar 26, 2011 at 8:15 PM, BareFeetWare list@barefeetware.com
wrote:
On 27/03/2011, at 12:39 PM, Patrick Earl wrote:
Base-10 numbers are frequently used in financial calculations because
Patrick Earl wrote:
On Sat, Mar 26, 2011 at 10:03 PM, Darren Duncan dar...@darrenduncan.net
wrote:
You could store your exact precision numbers as a pair of integers
representing
a numerator/denominator ratio and then have math operators that work on these
pairs like they were one number
in both performance and disk usage.
-- Darren Duncan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
:
column_info
foreign_key_info
primary_key_info
table_info
statistics_info
... and those are described elsewhere on that page.
I haven't used those myself, though, but I believe they are popular for others.
-- Darren Duncan
___
sqlite-users mailing
it
would be more on topic.
-- Darren Duncan
Wols Lists wrote:
On 15/12/10 02:47, Darren Duncan wrote:
Wols Lists wrote:
On 15/12/10 00:18, Darren Duncan wrote:
The point I'm making is that a list doesn't contain any ordering *data*
- it's inherent in the fact of a list. A list is an abstract
on my basic mathematics comment - in a list of rational
numbers, what is the ordinal position of the number 1?
Normally there isn't an answer to this.
The basic proofs of what is infinity rely on the fact that this
question has no answer ...
-- Darren Duncan
to disk when you fsync.)
-- Darren Duncan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Wols Lists wrote:
On 15/12/10 00:18, Darren Duncan wrote:
The point I'm making is that a list doesn't contain any ordering *data*
- it's inherent in the fact of a list. A list is an abstract concept. In
Pick, I can store a data structure that IS an abstract list. In an rdbms
I can't.
Put
. (That is also the canonical way to do it in Muldis D.)
It is perfectly valid to nest tuples and relations inside each other (these
*are* valid 1NF), and so likewise you can have record field values that are
sets
or arrays or tables or whatever.
-- Darren Duncan
Wols Lists wrote:
On 13/12/10 22:44, Darren Duncan wrote:
I am also very interested in these subjects.
I believe that the relational model can accurately model anything in
the real world, and that this can be implemented in efficient ways,
with physical structure taking hints from
Wols Lists wrote:
On 12/12/10 00:29, Darren Duncan wrote:
Nonsense. An information schema is a *good* thing, and is generally the
*best*
tool for introspecting a database. It lets you use all the power features
you
have when querying data, anything a SELECT can do, and you can query
Darren Duncan wrote:
Wols Lists wrote:
Dunno how well that approach translates into a relational engine,
because Pick has several very non-relational quirks (every row MUST
have a primary key, the dictionary DEscribes, not PREscribes the FILE,
etc etc).
Can you say more about this last
the power features you
have when querying data, anything a SELECT can do, and you can query the
database structure likewise. This is the way a relational database is supposed
to work. -- Darren Duncan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http
-amalgamation unzip to the folder name
sqlite-amalgamation-3070400 but -autoconf untars to the folder name
sqlite-3.7.4? Why the inconsistent use of version formats?
-- Darren Duncan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org
Roger Binns wrote:
On 12/07/2010 08:45 PM, Darren Duncan wrote:
I am also working with automated scripts, which now have to be updated to
use
either the new style or old style depending on the user-requested SQLite
version. (DBD::SQLite bundles a SQLite version, and includes a script
of apply the journal or WAL and then don't change anything further;
the
latter is also important to support but users should have a choice between the
two options. -- Darren Duncan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080
Cory Nelson wrote:
On Sun, Oct 10, 2010 at 8:51 PM, Darren Duncan dar...@darrenduncan.net
wrote:
I think that it should be possible to configure SQLite to be strictly
read-only
in every respect, such that if with such configuration SQLite is told to
open a
database that would need
. This was a completely backwards-compatible change, hence it came in a
0.0.1
version update. -- Darren Duncan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
updateable by the public; posting in the list can
cause an update there by a registered SQLite developer).
Please do not reply to me directly with your responses. Instead send them to
the forums or file with RT as is appropriate.
Thank you. -- Darren Duncan
Roger Binns wrote:
On 07/21/2010 08:01 PM, Darren Duncan wrote:
Simply substituting in 3.7.0 causes a few new test failures for me with the
Perl
binding, DBD::SQLite, citing disk I/O error.
I can't speak for the Perl binding, but some of the underlying error
handling (invalid filenames
work.
I'll report here once something's worked out.
-- Darren Duncan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
and running make
test. Building the same version pristine, without the perl util/getsqlite.pl
3.7.0, passes all tests.
-- Darren Duncan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
of the numeric, text, blob, etc types. This is how I see it, and put
in
those terms, SQLite is still strongly typed, but it is just more flexible than
some other DBMSs, those that don't support generic or union types.
-- Darren Duncan
___
sqlite-users mailing
work if the primary key was used.)
Separately, as was reported in another reply, this issue is something you
should
report as a bug to the OpenOffice people, since I think you said an alternate
connection method, MS Access worked fine?
-- Darren Duncan
and then you can make that a primary key. Not that
I
actually advise this since then you're just gaining a new problem or two in
place of the one you lost, such as ensuring there's not more than one row in
the
other table per row in the parent.
-- Darren Duncan
versions.
-- Darren Duncan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Richard Hipp wrote:
On Thu, Jun 3, 2010 at 11:15 PM, Darren Duncan dar...@darrenduncan.netwrote:
Richard Hipp wrote:
Partition means that one part of the database cannot communicate with
another part. In the usual use of the CAP theorem, this means that the
database is spread across multiple
Richard Hipp wrote:
On Sat, May 29, 2010 at 5:42 PM, Darren Duncan dar...@darrenduncan.netwrote:
3. Transactions that involve changes against multiple ATTACHed
databases are atomic for each individual database, but are not atomic across
all
databases as a set.
I greatly hope
to the database engine to decide how to do the searches most efficiently, the
programmer just says how much memory it can use to do so.
What are some examples of the proposed SQL replacements that do this?
-- Darren Duncan
___
sqlite-users mailing list
Simon Slavin wrote:
On 2 Jun 2010, at 1:14am, Darren Duncan wrote:
What are some examples of the proposed SQL replacements that do this?
You might be interested in NoSQL, or in databases which have no schema: every
piece of information is a property of an object. Please note: I am
Congratulations on this milestone.
I also just realized now that you're adding WAL to SQLite; I have more to say
on
this, but that will be in a new thread.
-- Darren Duncan
D. Richard Hipp wrote:
The first code check-in for SQLite occurred on 2000-05-29 14:26 UTC -
ten years ago today
1 - 100 of 410 matches
Mail list logo
