ndeed make it simpler. One good
example I would suggest is Trac which was originally written
to use SQLite.
I'll look into this and get back to you some time. Though I have
other usage scenarios that I would be addressing first.
-- Darren Duncan
e declared data type(s) you choose to use, and you can
count on its being consistent.
-- Darren Duncan
ler, and perform faster, since there are fewer possibilities to
check at logical decision points. And it should be easier to
optimize queries.
So even if no incompatible changes are made, I would hope that it is
possible to optimize for the simplest case.
-- Darren Duncan
result set in this case?
Cheers!
-Boris
Try removing the "t1." from both inner select statements, so it just
says "select id" in both places. -- Darren Duncan
At 11:32 AM +0600 2/15/06, Kirill wrote:
Whether will be SqlLite in the future and under Mac OS if there will
be that as soon?
SQLite runs under Mac OS X right now, and has for a long time. A
version is even bundled with X.4 Tiger. -- Darren Duncan
the
same named table, you need to say foo.your_table and bar.your_table;
leaving the prefix off one is ambiguous, so it could choose the one
in 'disk'. Try fixing your SQL and see if it works then. -- Darren
Duncan
pplication may have to iterate
through the rows of the source tables and insert conditionally into
the destination table.
-- Darren Duncan
ROM people WHERE name LIKE '%' || ? || '%'
-- Darren Duncan
nsert only (non-destructive drop-box)
3: Select/Insert only (fully non-destructive read and write)
4: Select/Insert/Update/Delete only (full read-write data, no ddl)
5: Power User
-- Darren Duncan
vector) AS match_res
FROM vectors AS match_tbl
ORDER BY match_res DESC
LIMIT 20;
-- Darren Duncan
At 10:01 PM -0700 11/12/05, Nathan Kurz wrote:
Hello --
I'm trying to figure out how to optimize a query a bit, and think I've
hit a case that could easily be optimized by sqlite but i
You don´t undertand me, maybe my poor english.
I will try to show with examples in the sqlite command line.
create table test("Full Name" varchar(30), "Login" varchar(15), Age integer);
insert into test ("Full Name", "Login", Age) values ("Enrique Esquivel",
"the_kique", 24);
.headers on
select
ng 100,000 memory allocations that you
shouldn't be doing, for the statement handle itself, and have already
hit yourself. Use prepared statements and both hits are brought down
to something negligible. -- Darren Duncan
.
-- Darren Duncan
.
-- Darren Duncan
At 6:30 PM -0500 11/2/05, [EMAIL PROTECTED] wrote:
In a CHECK constraint, if the expression is NULL (neither true
nor false) does the constraint fail?
Example:
CREATE TABLE ex1(
x INTEGER,
y REAL,
CHECK( x
some SQL code may need changing.
-- Darren Duncan
At 10:10 AM +0100 11/2/05, Zibetti Paolo wrote:
Most of the discussion so far was about proposed change number 2, on the
contrary I'm concerned about proposed change number 1.
Does this mean that a number that can be stored as an integer
g children actually get it both ways depending on their ages.
-- Darren Duncan
, and brands transcend any meaningfulness.
It should stay the same.
-- Darren Duncan
type, but
SQLite already isn't statically typed, so this change won't make
things any worse.
Under the circumstances, this change will actually be an improvement
to useability as I see it.
We have round() or truncate() or CAST when we need integer division.
-- Darren Duncan
ished by a connection or
statement specific pragma, or a wrapper.
-- Darren Duncan
ends up with a large number of records in it, one
per increment, which take up space but don't serve a useful purpose.
Whereas, an updating approach will not take up any more space than
necessary. -- Darren Duncan
, if you store '2.5' in one, then either '2.4' or
'2.50001' is actually stored.
SQLite should recognize the above 3 numerical types as being
distinct, and do the correct actions with math involving any of them.
-- Darren Duncan
a count(), but many people
don't; for them, putting that in the core slows things down; for
people that do want it sped up, the trigger option is perfectly valid.
I support leaving things the way they are, with no extra meta-info maintained.
-- Darren Duncan
ven though it is less common than an INNER or OUTER
join. See SQL:2003, 7.7 "". -- Darren Duncan
those are used by the
standard for array indices.
I suggest for simplicity that SQLite simply support single-quotes for
string delimiters and double-quotes for identifiers; clearly distinct
and simple.
-- Darren Duncan
.
Hopefully someone else took that picture. -- Darren Duncan
ch subquery in order to
prevent losing the first/last row.
-- Darren Duncan
be able to use that.
-- Darren Duncan
At 1:35 AM -0400 6/22/05, Al Danial wrote:
This table keeps track of how far two people ran in
a given week:
create table t(name, day, distance);
insert into t values("al", "monday" , 4.0);
insert into t values("al&quo
the
Makefile.PL, I didn't see any changes in them besides version number
updates. I looked in SQLite.xs and SQLite.pm.
-- Darren Duncan
email me privately.
-- Darren Duncan
on SQLITE 2.8.6... forgive the aging..
I believe that this is out of date and not applicable.
The SQLite 3.x line is not typeless and has distinct numerical and
text and binary data types.
Therefore it should be able to take columns declared as numbers and
sort them as such.
-- Darren Duncan
so that it only ever stores ints in int
columns. Like most databases do.
Matt, I would also appreciate it if a new DBD::SQLite was released
asap that embeds and is known to work well with the 3.2 series,
specifically 3.2.2.
Thank you in advance.
-- Darren Duncan
ite nor the Perl core is at fault, but the
intermediary between them, and hence the best solution is to fix that
so it at least always string-quotes (or ask Matt to do it). I ruled
out SQLite because you were using version 3 and explicitly defined
the field as a character string.
Meanwhile, you could follow the the workaround that DRH mentioned.
-- Darren Duncan
legal
concern out of thin air; there was a significant background to it,
even if it is an issue over which experts are divided.
Not FUD at all, in the malicious sense of the word anyway.
-- Darren Duncan
D;
You use 'declare' to declare a variable. Also,
you only use 'set' when assigning the value of
another variable or expression. You do not use
'set' to retrive the value of a query, but 'into'
instead.
I don't know if SQLite supports this feature, though.
-- Darren Duncan
At 9:50 AM +0200 5/10/0
statement is parsed once and then the actual data insert is
relatively little work and performs quickly. I believe this sort of
activity is what happens to implement the you wanted anyway. --
Darren Duncan
. This module does not come with Perl by default, but it is easy
to download from CPAN and install yourself.
-- Darren Duncan
ple.com/macosx/tiger/coredata.html
-- Darren Duncan
it seems
wrong to pick the same kind as that which always means string literal.
-- Darren Duncan
hopefully this will change
within 1-3 months.
-- Darren Duncan
I believe that exactly the right circumstances to allow bound
parameters is all of the same places where literal values are
allowed, namely strings, numbers, nulls, etc. It does not make sense
to have bound parameters in any other situation. -- Darren Duncan
ess "INTEGER PRIMARY KEY" and "NOT NULL" aren't separated by
commas?
Having NOT NULL with a PRIMARY KEY is redundant, if not an outright
error. Defining something as a primary key is implicitly defining it
to be both not null and distinct. -- Darren Duncan
at for ideas, or for customizing. --
Darren Duncan
At 4:22 PM -0500 3/7/05, Eli Burke wrote:
I've been working on a project using sqlite3 since last fall. At the time,
I knew that it would need a web-based front-end eventually. I have a very
small bit of experience with PHP, and I assumed that PHP
dle common
tasks, as some other posters here mentioned. In that respect, the
interpreted language is alike, since you are implicitly using
libraries rather than explicitly.
-- Darren Duncan
plied to what
would be bottlenecks. -- Darren Duncan
sult
column, which is determined either by the AS clause or other default
rules.
Calling this sqlite3_column_heading() is inappropriate when you
consider that most of the time this value is used as a primary
identifier for a query result column, for example being used as a
hash key.
Anyone agree or
.
-- Darren Duncan
to always use
'as' to explicitly define the column names you want. You always know
how these names map to original table columns because you explicitly
said so.
Insisting on using default names all the time is for uber-lazy users.
-- Darren Duncan
won't work correctly. What a pain.
Moreover, Oracle themselves have seen the light and support the SQL
standard syntax in versions 9 and 10+.
Please leave things the way they are now, like standard SQL.
-- Darren Duncan
3.1.2 tarball soon?
Or is this a "so minor it will wait until 3.1.3" thing?
-- Darren Duncan
ny case. I don't know if the current mechanism
for writing your own functions will let you
execute them at the necessary time. (The example
I gave may be wrong, if the SELECT line executes
prior to ORDER BY.)
-- Darren Duncan
sure there's a simple way to do it but I'm not sure how.
Richard, try the UNION ALL operator, something like this:
SELECT a, b FROM t0
UNION ALL
SELECT a, b FROM t1
-- Darren Duncan
rts of a schema to change over the long term except perhaps
for the smallest amount of control information, and said control
table could just have 2 columns that store key/value pairs; eg, one
key/value for version or other special clues that tell a program how
to deal with the rest of the schema. -- Darren Duncan
SQlite may have introduced, that were later
detected. Otherwise, I assume that SQLite itself is smart enough to
recognize when SQLite files are or are not compatible. You don't
necessarily have to worry about this. -- Darren Duncan
At 11:44 AM +1300 1/30/05, Murray Moffatt wrote:
I'm creating
.
What you want is '||', the string concatenation operator.
Also, all result fields that are calculations should use AS so that
their names are reasonable.
Eg: SELECT firstname || ' ' || lastname AS name FROM username
-- Darren Duncan
our suggestion would then produce the
desired result, a duplicates list:
SELECT * FROM table EXCEPT ALL SELECT DISTINCT * FROM table;
-- Darren Duncan
regardless of how many items are in the linked list.
Note that the SQL standard defines triggers as being more or less the
same as stored procedures as to what they can contain.
-- Darren Duncan
position, then I don't think those are supported.
-- Darren Duncan
be sure to find it useful. -- Darren Duncan
install OS is linux though???
thanks,
jim
No. SQLite runs on any operating system. The SQL::Routine library
also runs on any operating system, including the many flavors of Unix
or Linux, and Mac OS, and Windows.
-- Darren Duncan
the nodes. Then the create can be
generated from them when done.
-- Darren Duncan
.
On a separate matter, what is a 'QLString'? I've never heard of that
SQL data type before.
-- Darren Duncan
or diffs? Thanks!
SQLite 2 and 3 work for many people on Mac OS X, including me, which
is a PowerPC platform. Most people use the GCC line of compilers,
such as GCC 3.3. Try using GCC to compile yours and see if it works.
-- Darren Duncan
ing ported to Parrot asap, so that all the
languages which have compilers targeting Parrot (I think someone's
even doing PHP) can use it.
-- Darren Duncan
. -- Darren Duncan
At 9:09 PM -0400 10/11/04, D. Richard Hipp wrote:
Version 3.0.8 is now available on the website. http://www.sqlite.org/.
The primary change is a series of code size optimizations.
There are also some obscure bug fixes and a few minor enhancments.
If you are not having problems with version 3.0.7
all new development on SQLite 3
(unless you have a pile of legacy v2 data files) since that's where
it will benefit the community (and yourself) best.
-- Darren Duncan
n't pooling and reusing the
connections behind your back. This is analagous to C programs having
a separate sqlite open() in each thread, which is necessary. --
Darren Duncan
ity reasons), then giving an unqualified filename won't work.
Your current working directory when running your script is probably
different than the one your database is in, hence your problem.
Using full paths gets around this because then it doesn't matter what
your cwd is.
-- Darren Duncan
le doesn't exist yet,
prior to doing any inserts or updates.
-- Darren Duncan
At 1:02 PM -0500 10/8/04, Freeman, Michael wrote:
I am still having problems with a script trying to use SQLite2. My 4
line test script works fine, but my other code keeps giving me DBI
errors saying it can't find the t
, but rather the usual distro on
sqlite.org.
-- Darren Duncan
(the default).
Possibly the thing about checking the magic number could be a fourth
and fifth option (to complement 2 and 3), unless that is simply asked
for separately.
Any thoughts on this idea?
-- Darren Duncan
FYI, I added the following ticket today. A copy is also here on the
list in case any discussion is necessary. (No replies may be taken
as consensus.) -- Darren Duncan
-
Type: new
Version: 3.0.7
Status: active
Created: 2004-Oct-07 20:28
Severity: 3
Last Change
. But the '?' and ':A' are the only ones
that are SQL standard; I recommend you use ':A' myself, it's what I
would use. -- Darren Duncan
P.S. ":A" means "colon followed by an alpha character identifier string".
2
being mentioned as sidebars. Or have a completely different and
completely self-contained set for each of SQLite 2 and 3.
-- Darren Duncan
,
some of these might be good to include in the SQL standard.
Meanwhile, yes, sqlite3_create_function() is the way to go for now.
-- Darren Duncan
At 6:02 PM -0400 9/18/04, D. Richard Hipp wrote:
SQLite version 3.0.7 is now available on the website.
With this release, SQLite version 3.0 leaves beta and
becomes "stable".
And I thank-a-you :)
-- Darren Duncan
At 3:41 PM -0400 9/18/04, D. Richard Hipp wrote:
Check the website ;-)
Well now! Aren't *we* happy as a clam!
A new chapter in SQLite history begins today ...
-- Darren Duncan
a 3.0.7 today as another beta? That would make it easier for
people to test the current code prior to the production release. --
Darren Duncan
if your TCL is too old. -- Darren Duncan
believe they should be used when an efficient group by or
join can be used instead. Best tool for the job and all that.
-- Darren Duncan
, cutting the disk/ram footprint down.
What say you, Richard?
-- Darren Duncan
(And yes, I commit it to the public domain.)
Hopefully this will make it into SQLite 3.0.7, and a corresponding
DBD::SQLite 1.06.
-- Darren Duncan
[S0106000393c33758:/Volumes/Programming/DBD-SQLite-1.05]
darrenduncan% diff encode.c encode-DARREN.c
107c107
< ** or UPDATE statement. U
me before a CPAN install.
BTW, if you *really* can't access your own 10.2.8 machine, then I
could probably give you temporary shell access on mine for remote
testing.
I await the next reply.
-- Darren Duncan
for them. -- Darren Duncan
achine?
-- Darren Duncan
P.S. The above code is part of a larger routine that auto-detects
what data sources are available via all DBI drivers. It calls
DBI->data_sources() for each driver that passes the load test.
correspond to those, since they obviously were transformed.
Since I don't know whether the problem is in the core or in the Perl
bindings, should I file a ticket on SQLite.org for this?
Thank you. -- Darren Duncan
--
[S0106000393c33758:Documents/Perl Distributions/devworld]
dar
be updating all my dependencies to the newest
versions, so I can include yours.
With this round, I will start using the new stuff like named host parameters.
-- Darren Duncan
is so much faster because the slower action
actually did something but the faster action did nothing during the
time. The main risk is that your app is thinking the data is saved
at a certain point in time, but it actually isn't. -- Darren Duncan
for sorting. Only
after ORDER BY is run, then LIMIT is applied, because LIMIT doesn't
know which records to return until after they are sorted.
So to make this faster you either have to make WHERE return fewer
rows (better), or let it return more but remove the ORDER BY.
-- Darren Duncan
At 10:49
the other differences in what is being
compared. Please try that and resubmit the results here. Keep in
mind to try both without and with indexes on each field. -- Darren
Duncan
At 8:22 AM + 9/4/04, George Ionescu wrote:
Hello sqlite users,
Hello Dr. Hipp,
while using sqlite v3.0.6, I've
that the API is not frozen yet. -- Darren Duncan
n function that auto-detects SQLite database instances, so
in case I just want to present the user a menu of local databases
available for opening. In Perl, this can be used to help implement
DBI's data_sources() function.
So shall I start a ticket for this?
-- Darren Duncan
P.S. Richard, than
unique to the Perl community. --
Darren Duncan
etween the API you use and the encoding used on disk, so
you don't have to.
Also, unless I'm incorrect, all text in a SQLite database uses the
same encoding; you can't choose different ones on a by-table basis.
-- Darren Duncan
, also, with few-to-none updates or
deletes. While the whole DB is locked, the locking period should be
milliseconds short, so for typical usage no one should notice
slowdowns. Of course, try it and see. -- Darren Duncan
his should be done
during the beta phase, of course. -- Darren Duncan
t;SELECT * FROM bar ".
"WHERE baz = :yours OR foo = :mine OR zee = :yours" );
$sth->execute( { 'yours' => 3, 'mine' => 'hello' } );
...
$sth->execute( { 'yours' => 6, 'mine' => 'goodbye' } );
...
-- Darren Duncan
host parameters and '?';
please give a reference; I only saw the named host parameters
mentioned in the standard. -- Darren Duncan
ons or
:identifier, and does mention the old NNN formats.
Thank you very much to everyone for their hard work.
Good day. -- Darren Duncan
, as an integer or
identifier.
For "backwards compatability", any plain '?' could still be allowed,
and be mixed with both other usages, and each '?' occurance would
implicitly be the same as ?1, ?2, etc.
-- Darren Duncan
401 - 500 of 571 matches
Mail list logo
