litenoob wrote:
--
#/bin/sh
ROW_ID=`sqlite3 test.db <
FYI, the last part of the select is superfluous. You can simply do this:
insert into t values(1,2);
select last_insert_rowid();
HTH
Dennis C
a .read command on the command line instead of the -init option
like this:
sqlite3 mydatabasefile ".read BulkinsertItems.sql"
with the BulkinsertItems.sql file containing:
.separator \t
.import BulkItems.txt items
.quit
HTH
D
r framework is freely licensed open source
so others could use it as well?
It sounds interesting, and I would like to take a look at it if that is
possible. Is there a link to the source?
Dennis Cote
-
To uns
006-10-14')))
end
end
HTH
Dennis Cote
RB Smissaert wrote:
Thanks to Dennis Cote I got a nice way to get the age from the date in the
form '-nmm-dd'. It works fine when I run it on a field, but when I run
it on a literal date it gives me 100 too much:
select
case when
date('2002-01-01', '+' || (strftime('%Y', 'now') -
strftime
.
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
in the first byte and
the code only checks for zero vs nonzero values on that byte (then again
that may not be safe if other combined bitfield are set nonzero before
the isInited field is set). If its safe, you could save another byte per
structure.
Dennis Cote
platform applications. I suspect that is the
reason they aren't used.
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
.
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
for each row in table 1. Each evaluation
returns the value of column3 from a different row in table2, the row
where the column2 and column2 values match the row being updated.
HTH
Dennis Cote
-
To unsubscribe, send email
elect * from t;
order from detail
-- -- --
0 0 TABLE t
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
out of the services table.
select id as cust_id, service as service_id
from customers
where service not null;
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
data to an SQL statement without worrying about special quoting.
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
)
This will find points in a circumscribed square around the center of the
circle. The distance calculation would eliminate those points outside
the circle (i.e. the points in the corners of the squares).
Dennis Cote
-
To unsubscribe
= 5
intersect
select rowid from ex4 where y = 7
);
The intersect operation allows each of the sub-selects to be executed
using an independent index, and the outer select uses the implicit index
on the rowi
a query
that references the view.
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
makes many peoples lives at least a little easier and hence
better. I hope the rewards have been worth the effort.
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
or modify the
required rows in your view cache table whenever a record is inserted or
modified in the base tables. A trigger on the view will only fire when
you access the view.
HTH
Dennis Cote
-
To unsubscribe, send
Id" = '06d15df5-4253-4a65-b91f-cca52da960fe'
The extra join gives sqlite an opportunity to use the source id index
for the first join and the target id index for the second join.
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
ements but will build two
records (one in the table and one in the index) for each record.
I seem to be rambling so I will stop now.
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
S ne on l."Source.Id" = ne.Id
Where l."Target.Id" = '06d15df5-4253-4a65-b91f-cca52da960fe'
UNION
Select ne.*
From Link AS l
Join Entity AS ne on l."Target.Id" = ne.Id
Where l."Source.Id&quo
urce.Id" = '06d15df5-4253-4a65-b91f-cca52da960fe'
This should run in a reasonable time given that you have indexes on
Link("Target.Id") and Link("Source.Id")
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Allan, Mark wrote:
Excellent, thanks for your help.
Mark,
For future reference, your posts could use a little more trimming. There
is no need to quote the entire string of messages from your original
post on each reply. :-)
Dennis Cote
RelaxedSpiroTable as r on r.TestID=t.ID
where f.EVC > 2.0 and r.FVC > 2.0;
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
oTable as r on r.TestID=t.ID
where f.EVC > 2.0 and r.FVC > 2.0;
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
sqlite3.h header into a C++ source file.
It might help to post to the newsgroups saying you are also having
problems, or to vote on the bug in the QC system.
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL
LITE_NO_SYNC=1 $(TEMP_STORE) \
-o testfixture $(TESTSRC) $(TOP)/src/tclsqlite.c \
libsqlite3.la $(LIBTCL)
Are you suggesting that -DSQLITE_NO_SYNC=1should be added to TCC so it
affects all compiles?
Dennis Cote
system?
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
for the new test.
Anyway, misc7 works now.
Have you tried to run the latest laststmtchanges.test on Windows? I am
still getting a failure that shows the same double counting that was
originally reported on the mailing list.
laststmtchanges-1.2.1...
Expected: [5]
Got: [10]
Dennis Cote
s a string 'NULL' not as a null value. There is no way to
get SQLite to import null values. All you can do is run a few update
statements after you do the import that change the empty strings into
real nulls.
update table t set coln = null where coln = '';
HTH
D
to do any changes to sqlite, but i am more concerned
about
the licensing issues of the modules which are accesing sqlite.
Sqlite is public domain. You can apply any license you want to any
program that access sqlite.
HTH
Dennis Cote
There is nothing to be gained
by adding it to the index again.
Your first query will be satisfied by a binary search in the title table
looking for the id. It won't use the index.
Your second query will be satisfied by a binary search in the TitleIdx index
looking for a matching title. It won
thought someone would have let you know
about that long ago.
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
call from within TCL, or does this command have to be written in C?
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
I think it should be
skipped much like the tests that depend upon the SQLITE_MEMDEBUG being
defined.
Does anyone with better TCL knowledge have any recommendations for
either making these tests work under Windows, or detecting the OS so the
tests can be skipped when not running under that OS?
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
is a subtle bug here or not. I
suspect that perhaps the file isn't really being closed until the script
exits.Does this seem possible?
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
ts using MinGW/MSYS. I haven't
had any problems before, but I haven't built sqlite from source since
around version 3.3.12.
Do any of you TCL experts have any idea what could be wrong?
TIA
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
,
You have it correct.
Open the DB1
sqlite3 db1.db
> attach db2.db as db2;
> insert into table1 select * from db2.table1;
> .quit
You do basically the same thing using the C API. Open one database, then
issue an attach command for the other.
HTH
De
portion of the entire database to pay for their overhead.
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
and then looking up 150,200
records in the main table is simply more work than scanning the entire
table of 300,000 records once.
This case does not benefit from indexing, and in fact it is slowed down
on both lookups and inserts.
Dennis Cote
. Can you retry the test after running an ANALYZE command? Are the a
and b values in your sample code uniformly distributed?
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
multiple SQL statements, so you might be
able to use a trigger to do what you want if you execute a trigger in a
transaction.
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
the Contributed Code section here http://www.sqlite.org/copyright.html
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
for details.
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
definitions.
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
a different value for each row in the
parameters table.
for each row in table parameters
set value = same as the current row of parameters>
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
the comparisons of the fields from both tables
should affect the join.
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
is non standard. If
you want your SQL code to be portable to other database engines you
should use the standard syntax that Paul suggested.
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Stef Mientki wrote:
I don't know which version I'm running, how can I detect that ?
Stef,
You can call sqlite3_libversion (see
http://www.sqlite.org/capi3ref.html#sqlite3_libversion for details).
HTH
Dennis Cote
file
system such as http://sourceware.org/jffs2/ that uses "wear leveling"
algorithms to spread the writes over all the flash devices blocks if you
are writing often.
HTH
Dennis Cote
-
To unsubscribe,
for that missing
nugget.
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
I read it) and backwards
compatibility seems to be the most important thing here..
This behavior is prohibited by the standard.
Dennis Cote
a FROM t1 UNION SELECT b, a from t1 ORDER by a, b
SELECT a as c, b as d FROM t1 UNION SELECT b as c, a as d from t1 ORDER
by c, d
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
p2004/page-001.html
The discussion of indexes starts about slide 40 and joins are on slide 57.
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
download a free trial.
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Darren Duncan wrote:
At 3:33 PM -0600 4/11/07, Dennis Cote wrote:
You have lost me here. If this transaction is considered successful
without executing the middle step (which is the same as executing it
and then undoing that execution) then that step does not belong in
this transaction
Can you explain the difference?
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
in the discussion.
Again, I agree fully.
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
h as swapping
files on open and save rather than using transactions, so that real
transactions can be used to update the active file atomically.
Dennis Cote
.
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
und a
real bug.
You should open a bug report at http://www.sqlite.org/cvstrac/tktnew
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
complicated.
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[EMAIL PROTECTED] wrote:
I'm not sure if I can make intelligent decisions about choosing what I commit
to the database.
Things don't look too bright for you or your users then. ;-)
I couldn't resist. :-)
Dennis Cote
transaction
support would help *that* problem.
Likewise.
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
nformation its nearly impossible decide if it must
be done this way or not.
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
savepoints.
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
ed or fail on its own, because obviously it didn't
matter if it succeeded or failed in the first place.
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
don't want to execute the first
and last statement but skip the middle two. It seems to me any
application that can tolerate some statements in a transaction not
executing could factor those statements out into a separate transaction.
D
sqlite.org/cvstrac/wiki?p=VirtualTables for documentation
on the virtual table interface.
There is a lot of documentation in the wiki, especially for newer features.
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
/gmane.comp.db.sqlite.general/16175/match=fifo
for details.
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
. ;-)
See http://www.sqlite.org/cvstrac/fileview?f=sqlite/src/shell.c=1.160
and search for "import".
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
ameters only once (because the values
remain the same) and execute all the queries at once. I think this is
not possible, but I could be wrong.
You will need to bind the parameters to each prepared statement.
HTH
t;);
else
sqlite3_exec("commit");
}
void rollback_nested_transaction()
{
transaction_failed = true;
commit_transaction();
}
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
ards, I am glad to see that ANSI
got it right. ;-)
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
d indexing on them. I wouldn't
mess with trying to store invalid utf-8 bytes in a text field.
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Martin Pelletier wrote:
Hearing "sequel" for SQL always makes me cringe.
Me too! That is what prompted my original message. I just wanted to see
if I was perhaps the only one who was bothered by that pronunciation.
Thanks for the confirmation.
D
t; or something else like "sequel
light"?
I prefer "ess cue el" and "ess cue light" myself.
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
on of true as anything except
zero.
I think its reasonable for an application to assume that a database
field has a suitable value if it's the application that puts those
values (i.e. 0 or 1 only for a boolean column) into the database.
D
.
It will eliminate the comparisons in your application code and return
the allComplete value directly from the query. It's really very slick.
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
plete
from tech_modules
where tech_id = ? and coll_id = ?;
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
WHEN clauses on an INSTEAD OF trigger but since SQLite already accepts
it I think you are fine with your current implementation.
HTH
Dennis Cote
/||/
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
get this to compile I need to remove the first comma:
static const Mem nullMem = {{0}, 0.0, "", 0, MEM_Null, MEM_Null };
This seems like a real aggregate initializer error. Or possibly, you are
using an extension or some newer C variant that accepts this incomplete
in
handling code (in sqlite2 they were handled with
special in memory tree code). This may be the cause of part of your
speed difference.
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
t.
Even if I'm not being malicious, consider what happens if I innocently
put text containing quotes into the description field.
This really is a better idea, but I'm not sure how you do it from the
Python wrapper.
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
) values (''test'', ''its a string with
"quotes" in it.'');'
which sqlite will treat as a single literal string which can be inserted
into your log like this
logSQL = "insert into log values (" & SQuote(strSQL) & ")";
execute(logSQL);
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
which they seldom
are), collation, and view names.
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
"ID"
strSQL = "create " & Quote(strTable) & "(" & Quote(strColumn) & "
INTEGER PRIMARY KEY)"
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
econd code path to sqlite.
It might make sense to create a separate standalone utility program
(like sqlite3_analyzer) that reuses some the sqlite source to do bulk
inserts into a table in a database file as fast a possible with out
having to worry about locking or journaling etc.
D
print "Test ""quoted"" strings."
will output
Test "quoted" strings.
You can do the same with the strings you are building to send to SQLite.
Using the following VB statement
strSQL = "create """ & strTable & "
his makes it take 5 times as long.
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
n to standard SQL. You should quote identifiers
such as table and column names with double quotes.
Create table 'table1'([ID] INTEGER PRIMARY KEY)
should be:
Create table "table1"("ID" INTEGER PRIMARY KEY)
HTH
Dennis Cote
-
timings in the seconds range rather than
minutes (This assumes that you are not running into some cache size
problems that slow down the larger data set disproportionately).
Dennis Cote
-
To unsubscribe, send email
) to follow the execution of an SQL statement
by the virtual machine (at least for simple statements). Following the
logic of a complex statements can be challenging because there are no
human friendly text labels for branches, table and index names, or
runtime variables.
HTH
Dennis Cote
a single index per table per query. The way sqlite
uses indexes is explained in the slide show at
http://www.sqlite.org/php2004/page-001.html This may help you pick the
best columns to index for your application.
HTH
Dennis Cote
izing your SQL.
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Joel Cochran wrote:
I do think more and more that the solution for me is to trim the trailing
blanks before INSERTing them into SQLite.
That will be your best solution, and it will make your database files
smaller too since sqlite won't be storing the trailing spaces.
Dennis Cote
n use the typeof() function to get the type (storage class) of a
field.
select typeof(round(1.234));
You can use the cast(x as type) syntax to change the type of the round
result to integer.
select cast(round(1.234) as integer);
.
Is it usually better to re-index after deletes?
Indexes are updated automatically as records are added and deleted from
a table, that's why they add overhead if they are not serving some
purpose. Your index will be correct after you delete the records from
tableB.
HTH
Dennis Cote
the gift for formulating short clear direct
answers that some other (like Igor and Richard) do.
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
exists (select
id from t
ableA where tableA.id = tableB.id);
0|0|TABLE tableB
0|0|TABLE tableA USING PRIMARY KEY
Note that your index on tableB.id is not used and could be eliminated
unless it serves another p
501 - 600 of 1177 matches
Mail list logo
