In windows shell Control-Z should be equivalent of Control-D on Unix
(it sends EOF to stdin).
Pavel
On Mon, May 4, 2009 at 2:58 PM, Kees Nuyt wrote:
> On Mon, 4 May 2009 14:00:45 -0400, "D. Richard Hipp"
> wrote:
>
>>
>>On May 4, 2009, at 1:44 PM, Sam Carleton wrote:
>>
>>> prefix with a period
Is it just me or somebody else is seeing too that the sql statement
"select blobid, fbid from sig group by peerid" is invalid and
shouldn't be executed or prepared at all?
Pavel
On Mon, May 4, 2009 at 2:51 PM, Joanne Pham wrote:
> Hi All,
> I ran the following sql statement:
> select blobid,
Hi, all!
Recently I've discovered that my application works faster if it uses
journal_mode = PERSIST instead of MEMORY. After running under
callgrind I've found that with journal_mode = MEMORY application
spends a lot of time inside malloc() which is called from
memjrnlWrite(). Is there a way to o
You should do
for i in $HANDLER
Notice: NO quotes. Though be careful - it will not work if row
contains text field with spaces.
Pavel
On Wed, May 6, 2009 at 5:24 PM, Daniel Wolny wrote:
> 2009/5/6 Kees Nuyt :
>> On Wed, 6 May 2009 23:01:24 +0200, Daniel Wolny
>> wrote:
>> It should be possibl
one statement, then this insert will be able to rollback
harmlessly even without journal?
Pavel
On Thu, May 7, 2009 at 12:27 AM, Dan wrote:
>
> On May 7, 2009, at 3:27 AM, Pavel Ivanov wrote:
>
>> Hi, all!
>>
>> Recently I've discovered that my application works
Maybe you want to do
sqlite3 test.db ".output o.txt; .read r.sql"
Pavel
On Thu, May 7, 2009 at 6:44 AM, Leo Freitag wrote:
> Hallo,
>
> I try to get run the following in a batch file
>
> Open database "test.db"
> Set output to "o.txt"
> Read sql-statement form "r.sql"
>
> === r.sql - Start ===
>
Hi!
It's great to hear about performance improvements and especially about
asynchronous I/O extension. Thank you very much for your work!
I have one question though: taking quick look at the sources of async
vfs I've noticed that even closing the file is just a task in the
async queue and thus af
operations complete to meet that needs.
>> I think asynchronous VFS should take care of waiting in
>> sqlite3_close()
>> call.
>>
>> -Original Message-
>> From: sqlite-users-boun...@sqlite.org
>> [mailto:sqlite-users-boun...@sqlite.org]
>> O
. I will think what can I do in
this situation.
Pavel
On Thu, May 7, 2009 at 12:54 PM, Dan wrote:
>
> On May 7, 2009, at 10:33 PM, Pavel Ivanov wrote:
>
>> Hi!
>>
>> It's great to hear about performance improvements and especially about
>> asynchronous I/O e
According to the syntax of CREATE TRIGGER statement you can use in the
trigger body only insert/update/delete/select statements. There's no
support of simple assignments or any other programming language
extensions.
Pavel
On Sat, May 9, 2009 at 11:11 AM, Sam Carleton
wrote:
> This is my first BE
Try something like this:
SELECT
tag_name.id,
SUM(CASE WHEN tag_link.app = 't' THEN 1 ELSE 0) AS cntTwt,
SUM(CASE WHEN tag_link.app = 'd' THEN 1 ELSE 0) AS cntDel
FROM
tag_name, tag_link
WHERE
tag_link.tag_id = tag_name.id
GROUP BY
tag_name.id
Pavel
On S
tag_id = tag_name.id
GROUP BY
tag_name.id
Pavel
On Sun, May 10, 2009 at 7:40 PM, Pavel Ivanov wrote:
> Try something like this:
>
> SELECT
> tag_name.id,
> SUM(CASE WHEN tag_link.app = 't' THEN 1 ELSE 0) AS cntTwt,
> SUM(CASE WHEN ta
I believe, these matters are very specific for each database server
(though I can't recall server for which it's true what you say). What
specific server is talked about in this book? What's the name of this
book?
As long as SQLite is a concern, I prepare statements outside of
transaction and then
009 at 12:48 PM, Joanne Pham wrote:
> Thanks for quick responde my email
> This is sqlite documentation. Below is the link and last paragraph in this
> document has stated that.
>
> http://www.sqlite.org/cvstrac/wiki?p=PerformanceTuning
>
>
> _________
Double call to sqlite3_finalize() ?
Pavel
On Tue, May 12, 2009 at 5:22 PM, Joanne Pham wrote:
> Hi all,
> I have the application and occasionally it is crashed on sqlite3_free when it
> callled sqlite3_finalized and I really didn't know what problem it was.
> Below is stack trace.
> Do you hav
Just a couple of questions that can clarify situation:
Do you work with pragma synchronous = FULL?
Does the behavior of the journal you have described remains if you
change pragma journal_mode = PERSIST?
Pavel
On Tue, May 12, 2009 at 4:15 PM, Ofir Neuman wrote:
> Hi,
>
> Using version 3.6.10.0.
SQLite doesn't support several simultaneous transactions on the same
connection to database. So in fact your select and insert statements
execute in the same transaction. And even more: your inserts are not
committed until your select is completely finished. So for your task
you should use differen
I didn't notice it earlier and now I'm a bit surprised. Can I ask a
more elaborate example which will include WHERE and/or GROUP BY but
not include FROM?
Pavel
On Mon, May 18, 2009 at 2:32 PM, D. Richard Hipp wrote:
>
> On May 18, 2009, at 2:18 PM, Mitchell L Model wrote:
>
>> Is it really possi
order by 1
>
> Noah
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Pavel Ivanov
> Sent: Monday, May 18, 2009 11:37 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] error in doc
Why do you expect performance improvement in your approach as opposed
to implemented now?
Pavel
On Thu, May 21, 2009 at 1:56 PM, rajyalakshmi bommaraju
wrote:
> I did not get any reply so adding bit more explanation for what I need.
>
> In a C program, I am trying to read data from database. I u
ue for that name for x times. instead I get
> values into tuple in one call.
>
> -Raji
>
> --- On Thu, 5/21/09, Pavel Ivanov wrote:
>
> From: Pavel Ivanov
> Subject: Re: [sqlite] Fw: Question about reading all database data into memory
> To: "General Discussion of
Instead of
exe_query(query.str());
try to do this:
size_t len = query.pcount();
exe_query(string(query.str(), len).c_str());
Stringstream never puts 0 byte at the end of the string.
Pavel
On Tue, May 26, 2009 at 12:12 PM, Enrico Piccinini
wrote:
> Hy to all, I'm very new to database and sqli
Hi, all!
Is there a way to set global restriction on cache size in SQLite?
I like the default cache_size value for each database. But if I open a
lot of different databases then total cache size for all of them could
be too big for me. So I'd want to set some global_cache_size to limit
it. Is it p
What create table statement did you use?
Pavel
2009/5/29 "Andrés G. Aragoneses" :
> I just tried to create a primary key with 2 columns and got this error:
>
> "sqlite error" "table X has more than one primary key"
>
>
> Doesn't SQLite support this?? :o
>
> Andres
>
> --
>
> __
> * The query that works on SQLite all versions fails on Oracle.
False conclusion. Did you try to make only one row in t?
> * Behaviour is inconsistent between MySQL and Oracle.
I believe this conclusion is also false. Did you try several rows in t
on MySQL? If it worked I wonder how it showed
> Do other SQL database engines not have this same limitation? Are
> MySQL and PostgreSQL and Firebird and MS-SQL and Oracle creating
> phantom indices on-the-fly to help them do joins faster, for example?
Sort of. There's 2 types of join methods in Oracle for this - Hash
joins and Sort merge joi
Your trigger basically does this:
UPDATE t SET b = DATETIME('now','localtime') WHERE 1 != 0;
So it updates all rows in the table. Try to change it to this:
UPDATE t SET b = DATETIME('now','localtime') WHERE rowid = new.rowid;
Pavel
On Sun, May 31, 2009 at 7:44 AM, Oliver Peters wrote:
> Afte
If you have unique index on stock_tab.prod_batch_code then you can
re-write your trigger as this:
INSERT OR REPLACE INTO stock_tab
(stock_id, prod_batch_code, stock_qty, stock_date)
SELECT new.purchase_id+2, new.prod_batch_code,
new.purchase_qty + ifnull(b.stock_qty
You can do during configuration:
../sqlite/configure -DSQLITE_THREADSAFE=2
Pavel
On Wed, Jun 3, 2009 at 2:27 PM, Joanne Pham wrote:
> Hi All,
> I would like to build the SQLite 3.6.14 to following the steps as mentioned
> in the document
> tar xzf sqlite.tar.gz ;# Unpack the source tree i
"Not an error" is SQLITE_OK. Are you sure that you don't do anything
between getting error code from SQLite and obtaining error message?
Maybe in another thread?
Pavel
On Fri, Jun 5, 2009 at 3:50 AM, Vladimir Kharitonov
wrote:
> I have a problem inserting data through a view.
>
> This is my DB:
I'd create another special file (maybe even database) that will keep
information about current file that your program should be working
with. So separate process will create new database and then update
this file. And program will just read this file and then work with
database mentioned in the fil
ite code to open databasses with
FILE_SHARE_DELETE flag. In this case you will be able to work the same
way as on Unix.
Pavel
On Mon, Jun 8, 2009 at 3:24 PM, Mohit Sindhwani wrote:
> Pavel Ivanov wrote:
>> I'd create another special file (maybe even database) that will keep
>>
It mentions "from" and "column_name" where column name in this
particular case is "selected". ;-)
@Igor: I thought that sql standard in this case doesn't guarantee that
outer select will return rows in the same order that were enforced in
inner select by "order by", does it?
Pavel
On Wed, Jun 10
I think you should try to rewrite condition to exclude OR like this:
WHERE C >= 1 AND C <= 2. You can even do like this: WHERE C >= 1 AND C
<= 3 AND (C = 1 OR C = 3). I think it will be faster than just ORed
conditions alone.
Pavel
On Thu, Jun 11, 2009 at 5:19 AM, Antti Nietosvaara wrote:
> On Th
I bet "synchronous"ness will not be your only bottleneck. Opening
connection, preparing statement and closing connection will take in
total much longer than executing statement itself. So that doing all
these operations 500 times per second will not be possible I think. If
you keep pool of connecti
According to this http://www.sqlite.org/lang_update.html you have
invalid syntax.
I believe you can achieve the same by this (assuming that id is unique
in fud28_thread):
UPDATE fud28_read
SET user_id=2, last_view=1244710953,
msg_id=(SELECT last_post_id FROM fud28_thread
How do you do step 5? Like "delete from table" or "delete from table
where ..."? Do you see any degrade in disk space used by database
along with slowness?
Pavel
On Tue, Jun 16, 2009 at 8:06 AM, Jens Páll
Hafsteinsson wrote:
> Hi
>
> I've been running some tests against sqlite and have found that
Try this:
select c.*
from players a, registrations b, players c
where a.device_id = b.device_id
and b.mesg_token='123456'
and a.table_group_id = c.table_group_id
and a.table_id = c.table_id
Pavel
On Tue, Jun 16, 2009 at 11:25 AM, Boris Ioffe wrote:
>
> Hello folks,
> I am having
You're trying identifier replacement, not literal replacement. It's
not allowed. You have to write table name without binding.
Pavel
On Fri, Jun 19, 2009 at 9:58 AM, Shaun Seckman
(Firaxis) wrote:
> I'm trying to add some literal replacements in my prepared SQL statement
> but I'm currently getti
ure I fully understand what you mean. Is it not possible to replace the
> table name in the prepared statement? What sort of things can I replace then?
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of P
Hi, Richard!
Could you explain why this scenario doesn't cause infinite call cycle
of the trigger by itself? Is there some protection in SQLite which
breaks such cycles?
Pavel
On Mon, Jun 22, 2009 at 4:10 PM, D. Richard Hipp wrote:
>
> On Jun 22, 2009, at 3:33 PM, Oliver Peters wrote:
>
>> Hello
You can do only
where movies.id = 'tt0426459'
or
where user.id = 'tt0426459'
What to choose depends on your needs. And you're wrong that these
variants are identical and movies.id is always equal to user.id
because you're making left join. They will be identical if you will
make inner join. But ev
>From http://www.sqlite.org/lockingv3.html:
--
Eventually, the writing process will want to update the database file,
either because its memory cache has filled up or because it is ready
to commit its changes. Before this happens, the writer must make sure
no ot
I believe you need this:
SELECT A.ID, A.Column1, A.Column2, B.Column1, C.Column1
FROM A INNER JOIN B ON A.Column3 = B.ID
INNER JOIN C ON B.Column2 = C.ID
Pavel
On Thu, Jul 2, 2009 at 2:53 PM, Ed
Hawke wrote:
> Hi all,
>
> I'm having problems getting nested inner joins to work with
> there is problem when getting data by sqlite3's c apis in signal-handling
> func.
What problem?
And as a hint: are you sure that your SQLite is in correct
thread-safety mode and you're not trying to dead-lock your
application?
Pavel
On Mon, Jul 6, 2009 at 9:58 PM, liubin liu<7101...@sina.com>
Did you try
sqlite3 accounts.db ".backup main a.db"
(note the quotes)?
Pavel
On Tue, Jul 7, 2009 at 2:28 PM, Eric
Peterson wrote:
>
>
>
> C:\InWork\rsm\weekly_status>sqlite3 accounts.db .database
> seq name file
> --- --- ---
>
Is this related to your problem?
http://www.sqlite.org/cvstrac/tktview?tn=3940
Pavel
On Wed, Jul 8, 2009 at 4:06 AM, Yan Bertrand wrote:
> Hello,
>
>
>
> I am compiling SQLite with options to reduce the size, and with hopes to
> reduce the duration of some operations on portable de
Why do you re-post your code as if it's another question not related
to previous one (along with the answer)?
> does it mean that the sqlite3's C-api isn't reentrant or thread-safety?
If SQLite is compiled with SQLITE_THREADSAFE = 1 or 2 then API is
thread-safe (with some limitations when it's 2)
> SQLite does the right thing
> for multi-threaded applications and many use it that way - you are not
> the first.
Roger, note that OP is talking about re-entrance, not thread-safety.
He wants to know which functions can be called from signal handlers
which can be called in the middle of some oth
Jay, you're pretty much mistaken:
> I'm pretty sure you don't want to do it this way. What this does is
> gather every row that meets the WHERE condition and then runs a max()
> or min() aggregation function across all of those rows. That means
> that even if the column "number" has an index
t;select *
... where ticks = (select max(ticks) ...)" approach.
Pavel
On Tue, Jul 14, 2009 at 12:35 AM, Jay A. Kreibich wrote:
> On Mon, Jul 13, 2009 at 10:33:00PM -0400, Pavel Ivanov scratched on the wall:
>> Jay, you're pretty much mistaken:
>>
>> > I
I believe your choice of query is not good enough. Try this one:
insert or replace into main.masterlist
select d.*
from delta.masterlist d left outer join main.masterlist M on d.sn = M.sn
where d.write_out_ok=0
and d.record_updatetime >= ifnull(M.record_updatetime, '')
Pavel
Does Tcl supports binding of parameters to prepared statement?
If yes then do just that and you will not need to do any "quotes" and
think of any "ifs".
Pavel
On Thu, Jul 16, 2009 at 3:49 AM, Fredrik Karlsson wrote:
> Dear list,
>
> Sorry for jumping onto the list mainly to ask a question, but it
> Suppose, if I don't have index, is this the desired behavior of Sqlite
> to take this much time to fetch just 10 records or am I missing
> something here.
You're missing that SQLite have to fetch all records satisfying your
condition into memory storage, sort all these records in memory and
then
Maybe this:
select childs.Path, count(*)
from Directory childs, Directory hierarchy
where childs.ParentDirID = ?
and hierarchy.Path like childs.Path || '%'
group by childs.Path
You should have indexes on ParentDirID and on Path to make this query
somewhat effective. And you shouldn't have '%' and
in the path (which is a problem I need
> to work around), but what is special about '_' ?
>
> Thanks
> Doug
>
>
>> -Original Message-
>> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>> boun...@sqlite.org] On Behalf Of Pavel I
Sounds like you're running several instances of your application in
several different processes simultaneously on the same database. Are
you sure you don't do that?
Pavel
On Mon, Jul 20, 2009 at 11:08 AM, Gary
Verhaegen wrote:
> Hi everybody,
>
> I just subscribed and this is my first post - I ho
Although your query doesn't make sense without any explanation of what
did you mean and how it is supposed too work I can provide you a
couple of observations:
1) Do you realize that select * doesn't make any sense in this query?
The only meaningful field will be ac.AcNum, all others will be
essen
Then I guess your initial query was almost correct. Try to change it like this:
select ac.AcNum, count(au.acNum) as auNum, count(int.acNum) as intNum
from academic ac
LEFT OUTER JOIN author au on ac.AcNum = au.AcNum
LEFT OUTER JOIN interest int on int.AcNum = ac.AcNum
group by ac.AcNum
having coun
Sorry, mis-looked: "as int.AcNumCount" should be "as AcNumCount".
Don't see other syntax errors.
Pavel
On Tue, Jul 21, 2009 at 10:21 AM, Hubboo wrote:
>
> Thanks. Returns an error
>
> Likely SQL syntax error: select ac.AcNum, count(au.acNum) as auNum,
> count(int.acNum) as intNum
> from academic
> If I remove or comment out the
> three lines with //* in the code above, I get no memory leaks.
So basically you're saying that if you don't insert any data into your
database and thus effectively don't do with your database anything and
thus SQLite don't have to cache anything from database the
Did you look into this: http://www.sqlite.org/c3ref/changes.html ?
Pavel
On Tue, Jul 21, 2009 at 7:57 PM, Steve Martin wrote:
> Hi List,
>
> I am new to sqlite and having trouble determining if an update has worked.
>
> I am using the C/C++ API.
>
> For an update, when sqlite3_step is executed it
ation works and whether it's SQLite uses memory or your
application does.
BTW, how do you measure memory usage and how do you see leakage? What
does sqlite3_memory_used() returns for you?
Pavel
On Wed, Jul 22, 2009 at 11:26 AM, Zachary Turner wrote:
> On Tue, Jul 21, 2009 at 6:45 PM, Pavel
se that would obviously be
> ideal.
Again, try to call sqlite3_memory_used() several times during work of
your application. What does it say to you?
Pavel
On Wed, Jul 22, 2009 at 2:30 PM, Zachary Turner wrote:
> On Wed, Jul 22, 2009 at 10:47 AM, Pavel Ivanov wrote:
>> SQLite synchr
> And how can I calculate the value for Action?
Just do the same approach with CASE you've already used:
Select ID,
CASE WHEN d1='X' AND d2 IS NULL THEN 'NEW'
WHEN d1 IS NULL AND d2='X' THEN 'DROP'
ELSE 'CHANGE' END AS Action,
Sum(CASE WHEN d1='X' THEN AMT ELSE 0 END) - Sum(CASE WHEN d2='
First of all in case you've mentioned it will be not nested
transaction but one transaction including all deletes and it will be
committed when select statement will be finished.
As a second note: it's pretty bad idea to change table which is used
in not yet finished select statement. In SQLite it
X' then amt else -amt end) as net
from Source
group by id
)
order by 2 desc;
Pavel
On Fri, Jul 24, 2009 at 3:44 PM, David Bicking wrote:
>
>
>> From: Pavel Ivanov
>>
>> Just do the same approach with CASE you've already used:
>>
>> Select ID,
Try to add to your SQLite test these steps:
3.5) Execute statement BEGIN (you can do it via prepared statement).
5.5) Say every 10 000 records execute COMMIT and then BEGIN again. Or
if you indeed want to measure maximum write speed then skip this step
at all.
And your words that pragma synchronou
You can take sqlite3.c from amalgamation package and include it into
your project. This way SQLite will be included into your application
and you won't need any .dll or .lib files.
Pavel
On Sat, Jul 25, 2009 at 10:41 AM, Paul Claessen wrote:
> Greetings,
>
>
>
> I'm trying to build and use the sh
For me it's pretty obvious that strftime() returns text data type
because it returns your date formatted as you like. And SQLite have
never compared text and integer as equal even if they have the same
notation when converted to text:
sqlite> select 1 where '1' = 1;
sqlite> select 1 where '1' = '1
Execute
pragma synchronous = off;
after opening connection to database. More info:
http://www.sqlite.org/pragma.html#pragma_synchronous.
Pavel
On Mon, Jul 27, 2009 at 2:31 PM, W.-H. Gu wrote:
> Hi,
>
> Is there a way to disable fsync() in my SQLite application on Linux? I
> saw a discussion
First of all you've mentioned schema which is not in agreement
slightly with sql you've given. But of course we can guess...
Second your sql seems to do slightly different thing from what you're
saying. In particular I'm concerned about this sentence:
> If the tag is the same as the 'current_tag',
ase I've decided to continue to write in your syntax
preference and put related conditions (pt_p.page_id = pt_np.page_id
AND pt_np.tag_id != 1) in one place because I think when they stand
nearby intention of the query is more understandable.
Pavel
On Tue, Jul 28, 2009 at 9:59 PM, P Kishor
Maybe this way:
select t.*
from table_name t
left outer join table_name t_del
on t_del.operation = 'Deleted'
and t_del.Filename = t.RenameTo
where t.operation = 'Renamed'
and t_del.ID is null
union all
select t.*
from table_name t
left outer join table_name t_ren
0x490 = 1168
Sounds like memory required to store one page of size 1024 in SQLite
database cache. Does your database has this size of pages? Will this
leak be gone if you close database connection?
Pavel
On Thu, Jul 30, 2009 at 7:00 AM, Maciej
Miszczak wrote:
> Hi,
>
> I have a simple function (u
> What I assume is if I share an in memory db connection handle across threads
> then it will not be sqlite's responsibility but the user's responsibility to
> protect multiple insert/update statements. On the other hand if the
> connection to physical db is shared amongst threads then sqlite ta
> Is lowering the PRAGMA cache_size a good way to fix this?
Yes, it's the only way to fix this. Though IIRC you cannot lower it
below 10, so 10 pages will be always stored in memory. OTOH you can
implement your own version of page cache that will not leave pages in
memory at all (for more informat
> How do you properly do a Date comparison in SELECT so that the only rows
> returned are those that do not exceed the date found in my sDateTemp
> variable?
As a simple string comparison. You made it perfectly right except that
your Date field should be stored in a format '-mm-dd' in database
> I've attached a view of the results from the working SQL statement that does
> not perform the WHERE.
Attachments do not come through to this list.
> Also, as stated in my previous post, I have sDateTemp formatted in the same
> format as that which is in the table.
Could you elaborate: your sD
int64 type is okay to store data of type unsigned int. And int64 is
exactly the type used for all integers in SQLite.
Pavel
On Fri, Aug 7, 2009 at 5:53 AM, liubin liu<7101...@sina.com> wrote:
>
> I want to use a integer to save the ip address of ipv4. Because they all are
> 4 bytes.
>
> but it is
I think that's ok to use the int type to store the four byte.
> Because I could get the value by using forced conversion.
>
> But Do SQLite3 support the unsigned type?
>
> And You know that int64 is too wasting here.
>
>
>
> Pavel Ivanov-2 wrote:
>>
>
> If I have 2 separate processes, 1 of which will attempt to Read and Write a
> Database (Process A)
> and the 2nd which will only Read the database (Process B), then if Process A
> is in the middle of a Write when Process B tries to read what will happen?
If Process A is in the middle of writing
Maybe you could benefit of using sqlite3_unlock_notify()? See more
info here: http://www.sqlite.org/c3ref/unlock_notify.html.
Pavel
On Fri, Aug 7, 2009 at 1:45 PM, Nikolaus Rath wrote:
> Hello,
>
> I have program that continuously runs several threads (about 4 to 10)
> which concurrently read an
Why not make it this way:
select 'database1', lists.* from database1.lists
union all
select 'database2', lists.* from database2.lists;
This way you will know exactly which lists persist in which database.
Pavel
On Sat, Aug 15, 2009 at 10:57 AM, Andrew Gatt wrote:
> Simon Slavin wrote:
>> On 15
> Is there any way the code can 'suggest' SQLite use a certain index?
Does INDEXED BY clause work for you?
http://www.sqlite.org/lang_select.html
Pavel
On Sat, Aug 15, 2009 at 2:08 AM, His Nerdship wrote:
>
> Good day,
> We have a puzzling problem with a large (1GB+) database.
> Most of our que
Just try these and you'll see why.
sqlite> select julianday('18-08-2009');
sqlite> select julianday('now') - julianday('18-08-2009');
Pavel
On Tue, Aug 18, 2009 at 12:58 PM, Gilles Ganault wrote:
> Hello
>
> I thought this query would work to read a date column that holds the
> DD-MM- date
You can always do
insert into table (id, count) values (?1, (select count(*) + 1 from
table where id = ?1))
Though I'd be cautious about race condition that seems like possible
here when after select returned some value and before insert was made
another process made another insert with the same
009 at 1:54 PM, Dan Kennedy wrote:
>
> On Aug 19, 2009, at 12:35 AM, Pavel Ivanov wrote:
>
>> You can always do
>>
>> insert into table (id, count) values (?1, (select count(*) + 1 from
>> table where id = ?1))
>>
>> Though I'd be cautious about ra
>> So executing the insert/update/delete statement places RESERVED lock
>> right from the start before executing nested selects?
>
> Yes.
Then I stand corrected. Thank you.
Pavel
On Tue, Aug 18, 2009 at 2:04 PM, Dan Kennedy wrote:
>
> On Aug 19, 2009, at 1:0
> insert or replace into mytable(id, count)
> values (:id, coalesce((select count from mytable where id=:id), 0) + 1);
I guess it doesn't work as OP intended:
sqlite> select * from t;
id|count
1|1
1|2
1|3
1|4
sqlite> insert or replace into t(id, count) values (1,
coalesce((select count from t whe
Personally for me the current documentation style is more
understandable at a glance. Looking at it it's easier for me to
understand the sequence of terms I should use, what can be omitted,
what terms cannot be used together and so on. Old style looks for me
more like list of requirements for progr
But how do you expect your application to deal with restarts and/or OS
crashes? Do you want to still be able to revert to "marks" set in
previous application run or not? And what about accessing to the data
stored between "marks" from other processes?
Pavel
On Wed, Aug 19, 2009 at 4:07 AM, Chris
> 1. When is a lock released during a SELECT? Is it after the first
> call to step()? The last call to step()? The call to finalize()?
> Yes, I'm using the C API.
Lock is released during the call to finalize() or reset().
> 2. If while in the step() process of a SELECT, there is a c
Without looking at your select statement it's very hard to help. But
general suggestion is insert your results into temporary table and
then issue a select on that table joined with itself with condition
like t.rowid = prev.rowid + 1.
Pavel
On Wed, Aug 19, 2009 at 10:10 AM, Mário Anselmo Scandela
eted as "unique constraint on id-count pair".
So let's wait for the answer. :)
Pavel
On Wed, Aug 19, 2009 at 12:05 PM, Igor Tandetnik wrote:
> Pavel Ivanov wrote:
>>> insert or replace into mytable(id, count)
>>> values (:id, coalesce((select count from m
If you have synonym for rowid (i.e. column INTEGER PRIMARY KEY) or
some other unique columns combination then you can do something like
this:
INSERT OR REPLACE INTO table_1 (rowid, field_a, field_b, field_c, field_d)
SELECT table_1.rowid, table_2.field_a, table_2.field_b,
table_2.field_c, ta
> If they are stored with wchar_t, then using the '16' APIs is
> probably easiest to use (ie sqlite3_open16, sqlite3_prepare16_v2, etc).
Just don't forget that wchar_t on some platforms (reportedly on Linux
for example) is 32-bit integer. So conversion between wchar_t and
UCS-2 encoding is not alw
> Is there really anyone using UCS-2 now or did you mean UTF-16?
No, I meant exactly UCS-2. Because UCS-2 guarantees that all symbols
are represented by 2 bytes when UTF-16 does not. And I had an
understanding that Doug said about this 16-bit guarantee. Also if
we're talking about encoding where a
f application 'state', allowing current state to be
> recalculated if an historic input is received 'late'. See
> http://www.finalcog.com/haskell-decoupling-time-from-physics for a
> similar idea (implemented in Haskell).
>
> Regards,
>
> Chris.
>
> On Aug 19, 2:
> Do I misunderstand something fundamental?
According to http://www.sqlite.org/lang_datefunc.html datetime('now')
returns date and time already as UTC. If you add 'utc' modifier then
it makes datetime() think that it's your local time and convert it to
'utc' thus adding 4 hours (apparently you're
1 - 100 of 1186 matches
Mail list logo