Re: [sqlite] Sqlite + Dropbox

[Adam DeVita]

I've been using Dropbox to synchronize sqlite files with no problems for a 
year, but only use the Dropbox directory as a synchronization buffer, not as a 
place to do work.

I usually
1) make a copy of the db in a non drop box directory,
2) complete all work on the copy,
3) replace the Dropbox copy after all connections are closed.

Perhaps I am overly cautious, but I had Dropbox delete an MS-Word file and 
synchronize the delete while I was saving my changes before. (Fortunately the 
Dropbox web site was able to restore an older copy, so Id didn't lose 
everything.)

regards,
Adam

From: sqlite-users  on behalf of 
Nigel Verity 
Sent: April 7, 2017 7:42:57 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] Sqlite + Dropbox

Hi


I have just started using Dropbox to share documents. If I place a Sqlite 
database in my Dropbox folder I access it from a local application as a local 
file. This is then automatically synced with the copy held in my Dropbox cloud 
storage.


This strikes me as a possible way to share a database across the internet. Is 
this a practical approach or does the time it takes to sync the files introduce 
too much latency? I'd be interested in advice and/or experiences from anybody 
who has tried this.


Thanks


Nige
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
=== CONFIDENTIALITY NOTICE: This e-mail and any attachments contain 
information from Lytx, Inc. and/or its affiliates, and are intended solely for 
the use of the named recipient(s). This e-mail may contain confidential 
information of Lytx and its customers. Any dissemination of this e-mail by 
anyone other than an intended recipient is strictly prohibited. If you are not 
a named recipient, you are prohibited from any further viewing of the e-mail or 
any attachments or from making any use of the e-mail or attachments. If you 
believe you have received this e-mail in error, notify the sender immediately 
and permanently delete the e-mail, any attachments, and all copies thereof from 
any drives or storage media and destroy any printouts of the e-mail or 
attachments.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Faster check: > or !=

[Adam DeVita]

How much control do you have?

Can you put out your date code into an integer field?
Can you set an index on id, projId, and Xtrab ?

regards,
Adam

From: sqlite-users  on behalf of 
jose isaias cabrera 
Sent: February 13, 2017 10:32:00 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] Faster check: > or !=

Greetings!

I have a bunch of records to check, and I am wondering which is a faster
check.  I am attaching a network DB as client,

ATTACH 'h:\bkup\test.db' AS client;

and then do an INSERT based on some logic, and one of those login is
checking against a variable or an actual DB value.  Here are the
different SQL syntaxes:

BEGIN;
  INSERT OR REPLACE INTO OpenProjects
SELECT * FROM client.OpenProjects
  WHERE id IN
  (
SELECT id FROM client.OpenProjects
WHERE
 client.OpenProjects.id = id AND
 client.OpenProjects.ProjID <= 133560 AND
 client.OpenProjects.XtraB != XtraB  -- change
  );
END;

BEGIN;
  INSERT OR REPLACE INTO OpenProjects
SELECT * FROM client.OpenProjects
  WHERE id IN
  (
SELECT id FROM client.OpenProjects
WHERE
 client.OpenProjects.id = id AND
 client.OpenProjects.ProjID <= 133560 AND
 client.OpenProjects.XtraB  > '2017-02-10 00:00:00'  -- change
  );
END;

Any input would be greatly appreciated.  Thanks.

josé
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
=== CONFIDENTIALITY NOTICE: This e-mail and any attachments contain 
information from Lytx, Inc. and/or its affiliates, and are intended solely for 
the use of the named recipient(s). This e-mail may contain confidential 
information of Lytx and its customers. Any dissemination of this e-mail by 
anyone other than an intended recipient is strictly prohibited. If you are not 
a named recipient, you are prohibited from any further viewing of the e-mail or 
any attachments or from making any use of the e-mail or attachments. If you 
believe you have received this e-mail in error, notify the sender immediately 
and permanently delete the e-mail, any attachments, and all copies thereof from 
any drives or storage media and destroy any printouts of the e-mail or 
attachments.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how is "pragma threads = 4" working

[Adam Devita]

Have you proven that the cpu is the bottleneck? Sorting a million rows
seems like a lot, but even older single core cpus may be capable of 2
billion ops per second. [I apologize if this has been sorted out
already I've got about 2 days of history on the thread]

regards,
Adam DeVita

On Fri, Sep 16, 2016 at 7:56 AM, Stephen Chrzanowski <pontia...@gmail.com>
wrote:

> Although programmatically easily done, from the SQLite point of view, what
> if that query, sans LIMIT, were a subquery, and the limit was put out on
> the outside?  Would the inner query execute, use all the threads, then
> return just one row on the outer?
>
> On Fri, Sep 16, 2016 at 7:29 AM, Richard Hipp <d...@sqlite.org> wrote:
>
> > On 8/14/16, Венцислав Русев <ven...@proxima-3.com> wrote:
> > > My computer has 4 cores. I have compile sqlite like this "gcc
> > > -DSQLITE_MAX_WORKER_THREADS=4 -DSQLITE_DEFAULT_WORKER_THREADS=4
> shell.c
> > > sqlite3.c -lpthread -ldl -o sqlite3". I made some tests and found that
> > > "pragma threads = 4" doesn't decrease runtime of the query that sorts 1
> > > milion records.
> > >
> > > SQLite version 3.8.8
> > > sqlite> pragma threads;
> > > 4
> > > sqlite> CREATE TABLE event (
> > >  ID INTEGER PRIMARY KEY NOT NULL,
> > >  date   INTEGER NOT NULL,
> > >  value  INTEGER NOT NULL );
> > > sqlite> EXPLAIN QUERY PLAN SELECT ID FROM event ORDER BY date LIMIT 1;
> >
> > The algorithm used for "ORDER BY ... LIMIT N" uses much less memory
> > than a full-up "ORDER BY" because is only keeps track of the top N
> > entries seen so far, discarding the rest.  But it also only uses a
> > single thread.  If you want multiple threads to be used, you'll need
> > to drop the LIMIT, though I imagine that would defeat your purpose,
> > no?
> >
> > --
> > D. Richard Hipp
> > d...@sqlite.org
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
--
VerifEye Technologies Inc.
151 Whitehall Dr. Unit 2
Markham, ON
L3R 9T1
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] `SELECT FROM ( subquery ) alias` seems to require `AS`

[Adam Devita]

Please retry your test without using keywords like "inner" as an alias for
something.

regards,
Adam

On Wed, Aug 24, 2016 at 11:26 PM, Richard Newman 
wrote:

> Hi folks,
>
> According to my reading of <
> https://www.sqlite.org/syntax/table-or-subquery.html>, a table alias for a
> subquery does not require an AS; that is,
>
> SELECT noo.foo FROM ( SELECT … FROM bar ) AS noo
>
>
> is equivalent to
>
> SELECT noo.foo FROM ( SELECT … FROM bar ) noo
>
>
> This does not seem to be the case with 3.14.1:
>
> SQLite version 3.14.1 2016-08-11 18:53:32
> Enter ".help" for usage hints.
> sqlite> CREATE TABLE bar (e BLOB, a BLOB, v BLOB);
> sqlite> SELECT DISTINCT inner.uri AS uri FROM
>...> (SELECT DISTINCT v AS uri FROM bar)
>...> AS inner;
> sqlite> SELECT DISTINCT inner.uri AS uri FROM
>...> (SELECT DISTINCT v AS uri FROM bar)
>...> inner;
> Error: near ";": syntax error
>
>
> Am I misreading the docs, or is this a bug?
>
> Thanks,
>
> -Richard
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
--
VerifEye Technologies Inc.
151 Whitehall Dr. Unit 2
Markham, ON
L3R 9T1
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Error File is Encrypted or is not a database

[Adam Devita]

You are welcome.I was worried about the infinite loop I wrote. ;)

Hopefully you can track down the reason, I don't know anything about your
file but what you tell us. There are some things that you can do to help
track it down.

You didn't mention what version of sqlite you were using, programming
language, dev or target platform etc. This may be helpful to track it down,
or attract the interest of other list members more versed in an
implementation similar to  yours.

Also, if you have some sort of way of knowing the time the error was
written, if the file was exposed to users  etc. it may be helpful. If
you have a copy of the corrupt header and look at it from the point of view
of "what program would write such a thing to my file?"For example
"another program's" known file header, or (if the program was supposed to
append but didn't seek to the end before writing), some other stuff.

Have you ruled out your disk starting to fail?
Does corruption time co-relate to a power failure?  (Not all hard drives
tell the truth that the data is safely in the file.)
Is there a chance a user or other process opened the file, then closed it
saving its header over top of the sqlite one?

regards,
Adam





On Thu, Aug 18, 2016 at 1:24 PM, Matias Badin <mbadi...@gmail.com> wrote:

> Thanks  you very much! The information was very usefull.
> I can recover the database info now, overwritting the header array.
> Have you any information about how the header is corrupted? I would like to
> know the reason to resolve it.
> Thanks again!
>
> 2016-08-18 10:14 GMT-03:00 Adam Devita <adev...@verifeye.com>:
>
> > Good day,
> >
> > A few things that you can try
> > 1) One could download a hex editor and review the beginning of the file
> and
> > compare to https://www.sqlite.org/fileformat2.html . If some other
> program
> > has over-written the header,  you should be able to observe that,
> hopefully
> > identifying a program with a problem.
> >
> > 2) Back up you hard drive. Run hardware diagnostics.
> >
> > 3) Review https://www.sqlite.org/howtocorrupt.html
> >
> > 4) Do this list in reverse order. :)
> >
> >
> > regards,
> > Adam
> >
> >
> > On Thu, Aug 18, 2016 at 8:56 AM, Matias Badin <mbadi...@gmail.com>
> wrote:
> >
> > > Hi Everyone;
> > > I have a problem with a sqlite database that was working very good but
> > > suddenly started to give the message "File is encrypted or is not a
> > > database". Then I can´t access it and i have to replace it with a new
> > one.
> > >
> > > Can anyone help me with this problem?
> > > I don´t know how to re-open the database. I can´t access to the
> > information
> > > saved in it
> > > Thanks all;
> > > ___
> > > sqlite-users mailing list
> > > sqlite-users@mailinglists.sqlite.org
> > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> > >
> >
> >
> >
> > --
> > --
> > VerifEye Technologies Inc.
> > 151 Whitehall Dr. Unit 2
> > Markham, ON
> > L3R 9T1
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
--
VerifEye Technologies Inc.
151 Whitehall Dr. Unit 2
Markham, ON
L3R 9T1
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Error File is Encrypted or is not a database

[Adam Devita]

Good day,

A few things that you can try
1) One could download a hex editor and review the beginning of the file and
compare to https://www.sqlite.org/fileformat2.html . If some other program
has over-written the header,  you should be able to observe that, hopefully
identifying a program with a problem.

2) Back up you hard drive. Run hardware diagnostics.

3) Review https://www.sqlite.org/howtocorrupt.html

4) Do this list in reverse order. :)


regards,
Adam


On Thu, Aug 18, 2016 at 8:56 AM, Matias Badin  wrote:

> Hi Everyone;
> I have a problem with a sqlite database that was working very good but
> suddenly started to give the message "File is encrypted or is not a
> database". Then I can´t access it and i have to replace it with a new one.
>
> Can anyone help me with this problem?
> I don´t know how to re-open the database. I can´t access to the information
> saved in it
> Thanks all;
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
--
VerifEye Technologies Inc.
151 Whitehall Dr. Unit 2
Markham, ON
L3R 9T1
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] a couple of pre-initial questions

[Adam Devita]

Good day,

Q2: Documentation, see http://www.sqlite.org/docs.html
Q1: Often sqlite support is compiled into the code, so there is not "run
sqlite".  It is often used as a dll or shared object, which aren't
executable from the command prompt.   The command line shell (and source
code to compile it) are at http://www.sqlite.org/download.html

regards
Adam DeVita

On Wed, Jul 6, 2016 at 12:15 PM, John R. Sowden <jsow...@americansentry.net>
wrote:

> Good Morning:
>
> I am a pre-newbie.  A few questions.  Q1:  I am running xubuntu. When I
> search for sqlite3, lots of things show up.  This database engine seems to
> be very popular, so I assume it is used my many programs on my computer,
> like Thunderbird.  Why, then when I enter sqlite3 at terminal prompt do I
> get  message saying that sqlite3 is not installed on my computer.
>
> Q2: I understand that this is not mariadb, etc. but I am unable to find
> what specific features are not included in sqlite3.  I understand the big
> time stuff.  I am currently using foxpro/dos for my database needs, and it
> seems that sqlite3 and Pure Basic or Gambas is a good match for me.  I need
> multiple indices, support for about 5000 records, plus some very small ones
> for validation, etc.
>
> Can anyone direct me to some complete documentation.
>
> Thanks,  John
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
--
VerifEye Technologies Inc.
151 Whitehall Dr. Unit 2
Markham, ON
L3R 9T1
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Podcast with Dr Hipp: SQLite history, success and funding

[Adam Devita]

This link  is a presentation on 'we wish git had this'
https://www.youtube.com/watch?v=ghtpJnrdgbo  by DRH.

This is the fossil page on the subject of comparing vs git.
http://www.fossil-scm.org/xfer/doc/trunk/www/fossil-v-git.wiki

That said, at my workplace we use git.  How else can Windows developers get
to practice text editing in vi?   :)
regards,
Adam


On Wed, May 18, 2016 at 9:42 PM, J Decker  wrote:

> On Wed, May 18, 2016 at 2:39 AM, Cecil Westerhof 
> wrote:
> > I would be interested what you find wrong about Git and is better in your
> > version control system.
> >
>
> git blows; monotone forever!
>
> > --
> > Cecil Westerhof
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
--
VerifEye Technologies Inc.
151 Whitehall Dr. Unit 2
Markham, ON
L3R 9T1

[sqlite] Fastest way to backup/copy database?

[Adam Devita]

Re WAL mode trick.

I think you would want to complete a checkpoint  and then do the backup,
ensuring that no check-points are done during your backup time.  This way,
you know that your committed transactions prior to the backup are in the
file being backed up.

regards,
Adam

On Sat, May 7, 2016 at 7:32 AM, Stadin, Benjamin <
Benjamin.Stadin at heidelberg-mobil.com> wrote:

> Hi Rob,
>
> I think Clemens suggestion may be worth investigating, in case you do not
> want to stop the updates (which probably means a change in your workflow
> and some effort at other places anyways).
>
> I think this may work:
> - Use WAL, and turn off automatic checkpointing
> (https://www.sqlite.org/wal.html). The default behavior is to do a commit
> after 1000*4096(pagesize) which is round about 4MB. Instead of using the
> default auto checkpoint, create a checkpoint every now and then on your
> own in your code (e.g. simply after every n-th commit, every 10 minutes,
> or whatever fits).
> - Do *not* do checkpointing at the time you copy your db, in order to
> avoid changing the db while copying the file. Changes are written to WAL
> files exclusively at this time. I think it needs just reasonable effort to
> trigger these event from the outside to have the app know when a backup
> starts and stops - or it could be done as simple as implement within the
> checkpoint code a rule like ?don?t make a checkpoint between 2:30am and
> 4:00am?.
>
> Regards,
> Ben
>
>
> Am 04.05.16, 14:39 schrieb "sqlite-users-bounces at mailinglists.sqlite.org
> on behalf of Rob Willett" unter
>  rob.sqlite at robertwillett.com>:
>
> >Clemens,
> >
> >We have 8GB of memory which is the most our VPS provider allows. We?d
> >like 32GB but its not an option for us. Our desktops have more than
> >that, but the VPS provider is reasonably priced :)
> >
> >We hadn?t considered the WAL mode, my conclusion is that a simple
> >change to our workflow is actually simpler, we stop the database updates
> >for 15 mins out of hours, cp and then restart. Its not ideal but we?re
> >not running a nuclear power station or a 24/7 medical facility. Users
> >*may* not get traffic updates for 15 mins at 03:00 in the morning. The
> >world will keep spinning.
> >
> >Rob
> >
> >On 4 May 2016, at 12:58, Clemens Ladisch wrote:
> >
> >> Rob Willett wrote:
> >>> We?re trying to backup a 10GB live running database
> >>> ?as-fast-as-we-
> >>> possibly-can? without stopping updates coming in.
> >>
> >> How much memory do you have?  I guess you can't simply read the entire
> >> database file to force it into the file cache?
> >>
> >> In WAL mode, a writer does not block readers.  You have to decide
> >> whether you can live with its restrictions:
> >> http://www.sqlite.org/wal.html
> >>
> >>
> >> Regards,
> >> Clemens
> >> ___
> >> sqlite-users mailing list
> >> sqlite-users at mailinglists.sqlite.org
> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >___
> >sqlite-users mailing list
> >sqlite-users at mailinglists.sqlite.org
> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
--
VerifEye Technologies Inc.
151 Whitehall Dr. Unit 2
Markham, ON
L3R 9T1

[sqlite] Fastest way to backup/copy database?

[Adam Devita]

I use rsync to backup a 400MB sqlite db to a remote server. This is not
fast (which is a don't care problem in my context).  You may want to test
changes to a 'log of sql commands at database'  to get a replay-backup
remotely as it handles text better than binary files (at least the version
I use does).  Rsync of that will save IO.  I also have a process where
changed / new records are flagged and exported for backup.  Rsync works
well for the little delta files, especially identifying ones that have
already been transmitted. You will have to ensure that your automated
process warns you when the remote host decided to change their address or
pc and the sync fails because it is asking if THIS host can be trusted.

regards,
Adam DeVita




On Thu, May 5, 2016 at 11:50 AM, Rob Willett 
wrote:

> Hi,
>
> We did look at this before, and discarded the idea but I can?t remember
> why.
>
> I?ve just looked again and seen the ?in-place option which I wasn?t aware
> of. That *might* help and be an interesting solution. We know we can make
> cp wrk, though with a little downtime. We?ll investigate rsync ?in-place on
> a closed (and definitely not working database), see what happens and report
> back. It should be easy to test.
>
> Thanks for the information
>
> Rob
>
>
> On 5 May 2016, at 16:42, J Decker wrote:
>
> Instead of cp, rsync might help it is able to send delta changes.
>>
>> On Wed, May 4, 2016 at 10:55 AM, Rob Willett
>>  wrote:
>>
>>> Scott,
>>>
>>> OK, We can see how to do this (I think). Our app is written in Perl and
>>> we?d
>>> just need to capture the command we write down. The only issue I can
>>> think
>>> of is the prepare statement and making sure we capture the right SQL
>>> command. W
>>>
>>> We?ll dig into it and have a look,
>>>
>>> Thanks for taking the time to reply.
>>>
>>> Rob
>>>
>>>
>>> On 4 May 2016, at 18:52, Scott Robison wrote:
>>>
>>> On Wed, May 4, 2016 at 11:47 AM, Rob Willett
>>>> 
>>>> wrote:
>>>>
>>>> Scott,
>>>>>
>>>>> Thats an interesting idea. Is there an option in SQLite to do this for
>>>>> us,
>>>>> or do we have to write a small shim in our app?
>>>>>
>>>>> I like the idea of this as its simple and elegant.
>>>>>
>>>>
>>>>
>>>>
>>>> It would require a little extra work on your part. Nothing built into
>>>> the
>>>> system that would accomplish this directly. However, I've done similar
>>>> things and they don't involve a ton of overhead. You could use another
>>>> SQLite database as the append only log, or a simple text file.
>>>>
>>>> I'm not aware of a free lunch solution, sadly.
>>>>
>>>>
>>>>
>>>>>
>>>>> Rob
>>>>>
>>>>>
>>>>> On 4 May 2016, at 16:51, Scott Robison wrote:
>>>>>
>>>>> This is going to become a bigger problem for us as the database will
>>>>>
>>>>>>
>>>>>>>> only get bigger so any advice welcomed.
>>>>>>>>
>>>>>>>>
>>>>>>> Perhaps, rather than backing up the live data, you create an append
>>>>>> only
>>>>>> log of each and every query you send to the database. Should you need
>>>>>> to
>>>>>> restore, you replay the log of statements. Or at the appointed backup
>>>>>> time,
>>>>>> you replay the day's log of statements into another database. No need
>>>>>> to
>>>>>> ever take the live database offline at the cost of slightly longer
>>>>>> running
>>>>>> commands during the day to handle the append operation.
>>>>>> ___
>>>>>> sqlite-users mailing list
>>>>>> sqlite-users at mailinglists.sqlite.org
>>>>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>>>>
>>>>>> ___
>>>>> sqlite-users mailing list
>>>>> sqlite-users at mailinglists.sqlite.org
>>>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>>>
>>>>>
>>>>
>>>>
>>>> --
>>>> Scott Robison
>>>> ___
>>>> sqlite-users mailing list
>>>> sqlite-users at mailinglists.sqlite.org
>>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>>
>>>
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users at mailinglists.sqlite.org
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
--
VerifEye Technologies Inc.
151 Whitehall Dr. Unit 2
Markham, ON
L3R 9T1

[sqlite] strftime accepts an illegal time string

[Adam Devita]

What would be the 'correct' behaviour for an out of bounds day in a
month?   If you look at dates as an index + natural number offset then Jan
32 == Feb 1.What  is January 0 or January -1?
If expressing dates this way (and not as an int from an epoch)  I think
that it is up to the application to sanitize inputs for this one.About
8 years ago I decided that I was not going to to use the sqlite date
functions at all, preferring to either store string dates for humans or the
numeric value (unix epoch, or .net timestamp ), and write it into the
design rules for the project that only UTC date values will be in the db.
Within the bounds of time we use (I don't have to worry about handling
product construction or delivery dates before the company's founding)  this
is ok.  This smooths out time to simplify calculations and shoves the
formatting & presentation of the data to the application's presentation
layer, which knows things like what time zone the user is sitting in, and
their cultural context.

Every once in a while there are some very interesting lessons about the
precision of time on the list (the Earth used to turn faster) etc.   Others
can talk about extensions / modules handles in the extreme cases. I
wouldn't suggest that we drop the date time functions due to a predictable
storm of "we will not break backward compatibility", but I wouldn't care if
sqlite dropped date formatting altogether. (At this point I do not need
instructions as to compiling it out so save a few KB on the device.)


People on the list are genuinely  trying to be helpful, even if cross
language text comes across as terse, try to keep that context.

best wishes,

Adam DeVita



On Thu, May 5, 2016 at 8:52 AM, Cecil Westerhof 
wrote:

> 2016-05-05 12:39 GMT+02:00 Simon Slavin :
>
> >
> > On 5 May 2016, at 11:25am, Cecil Westerhof 
> wrote:
> >
> > > At
> > > the moment valid times can be marked as invalid and invalid times as
> > valid.
> > > Probably imposable to completely circumvent, but it can be done a lot
> > > better.
> >
> > I don't know what TimeZone you're in (your surname looks German) but at
> > this level of detail it becomes
>
>
> ?German would be Westerhoff. I live in the Netherlands.
>
> ?
>
>
> > important whether you're in the US or EU or any other place.  The phrase
> > 'valid times' covers a large number of subjects and we can't tell which
> of
> > them are important to you.
> >
> > For instance, do you care if someone enters a time which is skipped by
> the
> > clocks going forward ?  If at 1am your clocks skip straight to 2am, do
> you
> > care if someone enters a time of 1:30am on that day ?
> >
> > Or maybe you're in Samoa, which skipped the 30th of December 2011
> > entirely, and may one day want to go the other way, which it would do by
> > having a 32nd of December or an unwarranted 29th of February.
> >
> > You can get endlessly fussy about leap years and leap seconds and such
> > things to the point where you know the Time Lords by name.  SQLite
> > definitely cannot handle that level of detail and it should not be used
> for
> > timestamp validation.  It's best either to find an external library for
> > your programming language or tell yourself to relax and stop sweating the
> > small stuff.
>
>
> ?I do not like the straw man stuff.
> https://en.wikipedia.org/wiki/Straw_man
>
> I respond to a question and in the response from someone else something is
> said that when checked is proved to be not true. I do then some other
> checking and find some things that could in my opinion easily be rectified.
> I can understand a reaction like:
> We do not want to change it because we do not find it important.
> What I really not like is:
> We do not want to change it, but do not want to acknowledge it. So lets
> pretend that what is asked is unreasonable.
>
> I never was talking about timezones, so the problems about Samao and Summer
> Time has nothing to do with what I was talking about.
> I was not ?endlessly fussy?, I was only talking about a few simple changes
> that would give a much better result with little effort (Pareto Principle).
> Again: it is possible that it is something that the maintainers not want to
> do, but be honest about that.
>
>
> I like to contribute, but a treatment like this is not encouraging.
>
> --
> Cecil Westerhof
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
--
VerifEye Technologies Inc.
151 Whitehall Dr. Unit 2
Markham, ON
L3R 9T1

[sqlite] Is it possible that dropping a big table takes very long

[Adam Devita]

In general, CPUs got much faster than disk IO a long time ago, so it
is expected that a single thread, write through to disk program would
have lots of time where the is CPU waiting for disk IO to complete.
(BTW: A common error of novice db programmers is using a disk based db
to store variables instead of handling them in memory, thus reducing a
L1 cache or register operation to the speed of a disk.)

The technology of caching is an attempt to win performance with
smaller, faster caches closer to the CPU, exploiting temporal or
(memory) spacial locality to not need to go all the way to the disk as
little as possible.  The list has more than a few discussions of
people using SSDs to increase performance or even caching, mission
critical, RAID controllers to win speed.

That said, why is the dropping of a table dependent on the size of
the table?   Does Sqlite have to mark every block of memory it used as
dropped?  (This is obvious for high security mode, but otherwise?)

regards,
Adam DeVita


On Fri, Apr 22, 2016 at 8:23 AM, Cecil Westerhof  
wrote:
> 2016-04-22 14:06 GMT+02:00 E.Pasma :
>
>>
>> 22 apr 2016, Cecil Westerhof:
>>
>>>
>>> With createBigTable.sh ...
>>>
>> Can you paste the svript in the message? Attachments are not sent.
>>
>
> createBigTable.sh:
> #/usr/bin/env bash
>
> # An error should terminate the script
> # An unset variable is also an error
> set -o errexit
> set -o nounset
>
>
> declare -r INSERT_TEMPLATE="INSERT INTO testUniqueUUIDBig
> SELECT uuid, %d FROM testUniqueUUID
> ;
> "
> declare -r NR_OF_COPIES=10
>
> declare insert=""
>
>
> function getInsertStr {
> printf "${INSERT_TEMPLATE}" "${1}"
> }
>
>
> for i in $(seq "${NR_OF_COPIES}") ; do
> insert+="$(getInsertStr ${i})
> "
> done
>
> sqlite3 checkUUID.sqlite < .echo ON
> .timer ON
> DROP TABLE IF EXISTS testUniqueUUIDBig;
> CREATE TABLE testUniqueUUIDBig (
> UUID blob,
> count int,
>
> PRIMARY KEY(UUID, count)
> CHECK(TYPEOF(UUID) = 'blob'   AND
>   LENGTH(UUID) = 16   AND
>   SUBSTR(HEX(UUID), 13, 1) == '4' AND
>   SUBSTR(HEX(UUID), 17, 1) IN ('8', '9', 'A', 'B')
> )
> );
> ${insert}
> EOT
>
>
> The logging:
> .timer ON
> DROP TABLE IF EXISTS testUniqueUUIDBig;
> Run Time: real 293.257 user 6.708000 sys 28.844000
> CREATE TABLE testUniqueUUIDBig (
> UUID blob,
> count int,
>
> PRIMARY KEY(UUID, count)
> CHECK(TYPEOF(UUID) = 'blob'   AND
>   LENGTH(UUID) = 16   AND
>   SUBSTR(HEX(UUID), 13, 1) == '4' AND
>   SUBSTR(HEX(UUID), 17, 1) IN ('8', '9', 'A', 'B')
> )
> );
> Run Time: real 0.277 user 0.00 sys 0.00
> INSERT INTO testUniqueUUIDBig
> SELECT uuid, 1 FROM testUniqueUUID
> ;
> Run Time: real 89.930 user 48.872000 sys 28.196000
> INSERT INTO testUniqueUUIDBig
> SELECT uuid, 2 FROM testUniqueUUID
> ;
> Run Time: real 133.674 user 56.416000 sys 43.032000
> INSERT INTO testUniqueUUIDBig
> SELECT uuid, 3 FROM testUniqueUUID
> ;
> Run Time: real 269.029 user 59.52 sys 48.84
> INSERT INTO testUniqueUUIDBig
> SELECT uuid, 4 FROM testUniqueUUID
> ;
> Run Time: real 356.622 user 61.196000 sys 51.956000
> INSERT INTO testUniqueUUIDBig
> SELECT uuid, 5 FROM testUniqueUUID
> ;
> Run Time: real 398.048 user 61.924000 sys 57.54
> INSERT INTO testUniqueUUIDBig
> SELECT uuid, 6 FROM testUniqueUUID
> ;
> Run Time: real 413.252 user 61.684000 sys 59.816000
> INSERT INTO testUniqueUUIDBig
> SELECT uuid, 7 FROM testUniqueUUID
> ;
> Run Time: real 464.911 user 61.672000 sys 63.20
> INSERT INTO testUniqueUUIDBig
> SELECT uuid, 8 FROM testUniqueUUID
> ;
> Run Time: real 545.974 user 61.90 sys 66.916000
> INSERT INTO testUniqueUUIDBig
> SELECT uuid, 9 FROM testUniqueUUID
> ;
> Run Time: real 695.315 user 64.016000 sys 69.692000
> INSERT INTO testUniqueUUIDBig
> SELECT uuid, 10 FROM testUniqueUUID
> ;
> Run Time: real 1129.854 user 64.428000 sys 76.704000
>
>
> performanceTest.sh:
> #/usr/bin/env bash
>
> # An error should terminate the script
> # An unset variable is also an error
> set -o errexit
> set -o nounset
>
>
> declare -r DB=checkUUIDSmall.sqlite
> declare -r DEINIT=".timer OFF
> .echo OFF"
> declare -r INIT=".echo ON
> .timer ON"
> declare -r TABLE=testUniqueUUID
> declare -r TABLE_BIG=testUniqueUUIDBig
>
> declare -r DELETE_AND_DROP="DELETE FROM ${TABLE};
> DROP TABLE ${TABLE};
> DELETE FROM ${TABLE_BIG};
> DROP TABLE ${TABLE_BIG};"
> declare -

[sqlite] Primary key values can be NULL

[Adam Devita]

I had a similar thought, until I imagined

Program uses sqlite DLL
The program creates dbs, and sometimes does inserts with null primary
keys. (Why is beyond the scope, it just does sometimes.)

Upgrading the DLL would start making files in the new format, but the
program using the dll doesn't know that. It just starts failing.

regards,
Adam

On Mon, Apr 18, 2016 at 10:29 AM, David Raymond
 wrote:
> I don't mean to poke a busy thread with a possibly stupid newbie question, 
> but here goes.
>
> How is this that much different than say, the SQLITE_DEFAULT_FILE_FORMAT 
> compile option? (Pasting it here then continuing comment below)
>
> Text pasted here
> SQLITE_DEFAULT_FILE_FORMAT=<1 or 4>
>
> The default schema format number used by SQLite when creating new 
> database files is set by this macro. The schema formats are all very similar. 
> The difference between formats 1 and 4 is that format 4 understands 
> descending indices and has a tighter encoding for boolean values.
>
> All versions of SQLite since 3.3.0 (2006-01-10) can read and write any 
> schema format between 1 and 4. But older versions of SQLite might not be able 
> to read formats greater than 1. So that older versions of SQLite will be able 
> to read and write database files created by newer versions of SQLite, the 
> default schema format was set to 1 for SQLite versions through 3.7.9 
> (2011-11-01). Beginning with version 3.7.10, the default schema format is 4.
>
> The schema format number for a new database can be set at runtime using 
> the PRAGMA legacy_file_format command.
> End quoted section
>
> The key point when introducing something new seems to be "as long as old 
> versions will know they shouldn't mess with it, then it's ok." So things like 
> CTE's can be added to the language as the old parser will gag on them and not 
> try to do something wrong with them and fail. But just straight up changing 
> the enforcement here would be bad, because the old version wouldn't know that 
> something new is going on. So although the above file format option is 
> intended for the physical structure of the file, could for example we call 
> file format 5 to be "same format, but will not null primary key enforced." 
> Then old versions would open it up, see file format 5, and throw their hands 
> up saying "I can't deal with this." And with new versions it wouldn't be a 
> changeable option, it would be "hey, once you create this database file with 
> this option, then you're stuck with that enforcement forever." Looking at the 
> dates above, format 4 was optional for 5 years before it got a promotion to 
> default, si
>  milarly a new value would have to be explicitly specified for n years before 
> anyone would have to worry about there being a "default build" that would 
> make something that could not be read by old versions.
>
> I know that actually using SQLITE_DEFAULT_FILE_FORMAT for this would be very 
> bad and not what it's intended for. But for example, there are 20 bytes of 
> "Reserved for expansion. Must be zero." in the header at the moment. Do past 
> or current versions throw up an error if those aren't zero at the moment? 
> Might it be time to appropriate a byte of reserved space for new flags or an 
> additional number? Or would that be the start of a slippery slope?
>
> (As a newbie I apologize if this is just plain wrong, if I just created the 
> sound of hundreds of foreheads smacking into their desks in unison, or if I 
> just re-stirred a hornets' nest)
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



-- 
--
VerifEye Technologies Inc.
151 Whitehall Dr. Unit 2
Markham, ON
L3R 9T1

[sqlite] Article about pointer abuse in SQLite

[Adam Devita]

It may be pedantic, but VS2016 will stop complaining if you edit your
definition of s to
large_struct s=new large_struct();  //set s to an actual instance of
large_struct. c people can think of s as a pointer, and in c# the
members are set to their default values.

J Decker's point could also have been made by using int x in place of
large_struct s . and sub x for s.x  , since it is a contrived example
anyway.  The only way to use x is if another conditional on another
variable that follows it in code and it is initialized.

if one writes
const bool arbitrary_true_false = true;   //note the const as Scott
Doctor implied, makes the error go away.

-
This discussion on the nature of undefined behaviour code is
interesting.  I don't know the reasoning, but it seems that VS6 often
initialized things to 0xcd in debug mode and (usually) had memory
uninitialized to 0x00 when complied in Release (perhaps 0x00 just
happens to be what was on the stack or heap).  I presume this wasn't
just to make people suffer  when things don't work the same in debug
vs release mode.

Does the tool help (in the sqlite in practice) point out things that
could be problematic?  Is it a compiler's variant of  "hay,  you are
depending on implemented, not documented behaviour" ?

regards,
Adam DeVita


On Tue, Mar 22, 2016 at 7:27 AM, Scott Doctor  wrote:
>
> It is uninitialized. you are setting an initial value within an if
> statement. For the compiler, the code has NOT actually executed. so it does
> not use the value of the variable arbitrary_true_false. If it was a #define
> then it would use the value but still give an error because it is not a
> compiler directive #if but a code if.
>
> The logic is that the first instance of assignment is within a conditional.
> That is a particularly nasty kind of bug and should be reported as an error.
> because if later you decide to change arbitrary_true_false to false, then
> s.x would not be initialized before use. the compiler is correct to issue
> the warning. Give s.x a value after/at initialization, but before the if
> statement to give it a desired initial value then recompile, that should fix
> the error.
>
> Compilers only set the code to initialize the variable at declaration, not
> actually use the values during compile. If it was declared as a constant
> using a compiler directive such as #define, then the compiler would use the
> value in the logic and still give an error, but a different one because the
> conditional would always evaluate true (or false depending on what it was
> set to)
>
>
> On 03/21/2016 21:31, J Decker wrote:
>>
>> On Mon, Mar 21, 2016 at 8:40 PM, Scott Doctor 
>> wrote:
>>>
>>> you are missing
>>>
>>> using System;
>>
>> whatever.  It still fails because it says the variable is
>> uninitilalized.  THe only thing that doesn't is actually running it.
>>
>> That same pattern not matter what the language triggers warning/error
>> checkers
>>>
>>> 
>>> Scott Doctor
>>> scott at scottdoctor.com
>>> --
>>>
>>>
>>> On 3/21/2016 5:21 PM, J Decker wrote:
>>>>
>>>> So far I just see analysis tools fail for the same sorts of valid
>>>> code...
>>>>
>>>> this is a bit of C# but the same idea causes the same warnings and
>>>> there's nothign tecniclally wrong with this.
>>>>
>>>>
>>>>
>>>> class test
>>>> {
>>>>  struct large_struct { public int x; }
>>>>  bool arbitrary_true_false = true;
>>>>  void method()
>>>>  {
>>>> bool initialized = false;
>>>> large_struct s;
>>>> if( arbitrary_true_false )
>>>> {
>>>>initialized = true;
>>>>s.x = 1;
>>>> }
>>>> if( initialized )
>>>> {
>>>>Console.WriteLine( "this fails(during compile) as
>>>> uninitialized: {0}", s.x );
>>>> }
>>>>  }
>>>> }
>>>>
>>>> On Mon, Mar 21, 2016 at 4:35 PM, James K. Lowden
>>>>  wrote:
>>>>>
>>>>> On Mon, 21 Mar 2016 13:48:06 -0700
>>>>> Scott Perry  wrote:
>>>>>
>>>>>> Compilers allow you to choose your standard; --std=c11 means
>>>>>> something very specific (and unchanging)
>>>>>
>>>>> They do.  And that covers what the standard covers.  The standard also
>>>>> has limits.

[sqlite] SQLite Pronunciation

[Adam Devita]

Since Jay is The Certified SQLite Professional ,
( https://www.mail-archive.com/sqlite-users at 
mailinglists.sqlite.org/msg04840.html
) and by fortunate co-incidence what he said (and DRH) happens to
match what I was doing, I'll agree with them, and celebrate by making
a potato salad with tomatoes.

:)
Adam

On Wed, Mar 16, 2016 at 3:39 PM, Daniel Telvock
 wrote:
> I was at the Investigative Reporters and Editors Conference last week and
> the presenter for SQLite courses 1 and 3 said that it is actually
> pronounced SQ Lite. Even he thought that was odd considering SQL is a term
> or acronym.
>
>
>
> Dan Telvock
> Environment Reporter
> Investigative Post 
> Twitter: @dantelvock
> 716-831-2626 ext. 3
>
>
> On Wed, Mar 16, 2016 at 3:21 PM, Marc L. Allen  outsitenetworks.com>
> wrote:
>
>> I don't think anyone's making a fuss.  I certainly wasn't and apologize if
>> it appeared differently.
>>
>> -Original Message-
>> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:
>> sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Stephen
>> Chrzanowski
>> Sent: Wednesday, March 16, 2016 3:09 PM
>> To: SQLite mailing list 
>> Subject: Re: [sqlite] SQLite Pronunciation
>>
>> Standards, official, or not, I've always pronounced it as Sequel.
>> Ehm-Ess-Sequel, My-Sequel, Sequel-Lite, etc.
>>
>> IMO, S-Q-L is an acronym, as everyone knows, and since RADAR(1) is also an
>> Acronym, why the fuss?  To me, it falls off the tongue easier to say Sequel
>> instead of Ess-Queue-Ell.
>>
>> 1- http://acronyms.thefreedictionary.com/RADAR
>>
>>
>>
>> On Wed, Mar 16, 2016 at 2:55 PM, Marc L. Allen <
>> mlallen at outsitenetworks.com>
>> wrote:
>>
>> > That sounds like someone that comes from the land of Sequel. ;)
>> >
>> > I realize there *is* an official pronunciation, but I will probably
>> > forever pronounce it as S-Q-L-light, regardless of what it really is.
>> > :)
>> >
>> > -Original Message-
>> > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:
>> > sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of J Decker
>> > Sent: Wednesday, March 16, 2016 2:48 PM
>> > To: SQLite mailing list 
>> > Subject: Re: [sqlite] SQLite Pronunciation
>> >
>> > more like sequel-ite
>> >
>> > On Wed, Mar 16, 2016 at 11:38 AM,  
>> wrote:
>> > > Hello,
>> > >
>> > > Please grant me some leeway here since as someone who has not been
>> > > in an academic school for years and is mainly self taught. I have
>> > > Mainly deriving information from reading manuals and occasionally
>> > > viewing some videos.
>> > >
>> > > Maybe I'm wrong, but according to Wikepedia SQLite appears to be
>> > > pronounced the same has it is spelled,
>> > > (sikwl.lat).
>> > > Maybe not a long A there perhaps.
>> > >
>> > > Where as I first heard Microsoft's MSSQL pronounce (sequent), which
>> > > I have also heard in academic videos by professors.
>> > > Following that logic, SQLite, (sequent.light)?
>> > >
>> > > Dana Proctor
>> > >
>> > > ___
>> > > sqlite-users mailing list
>> > > sqlite-users at mailinglists.sqlite.org
>> > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> > ___
>> > sqlite-users mailing list
>> > sqlite-users at mailinglists.sqlite.org
>> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> >
>> >
>> > This email and any attachments are only for use by the intended
>> > recipient(s) and may contain legally privileged, confidential,
>> > proprietary or otherwise private information. Any unauthorized use,
>> > reproduction, dissemination, distribution or other disclosure of the
>> > contents of this e-mail or its attachments is strictly prohibited. If
>> > you have received this email in error, please notify the sender
>> > immediately and delete the original.
>> > ___
>> > sqlite-users mailing list
>> > sqlite-users at mailinglists.sqlite.org
>> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> >
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>> This email and any attachments are only for use by the intended
>> recipient(s) and may contain legally privileged, confidential, proprietary
>> or otherwise private information. Any unauthorized use, reproduction,
>> dissemination, distribution or other disclosure of the contents of this
>> e-mail or its attachments is strictly prohibited. If you have received this
>> email in error, please notify the sender immediately and delete the
>> original.
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> 

[sqlite] How to enter Unicode character?

[Adam Devita]

Good day,
What OS are you using?
Have  you read up on this?
http://www.fileformat.info/tip/microsoft/enter_unicode.htm

Also, Notepad++ is a good text editor for displaying / saving them. (I
assume that you are wanting to generate some text files as scripts)

regards,
Adam DeVita

On Mon, Feb 8, 2016 at 10:17 AM, Igor Korot  wrote:
>  Hi, ALL,
> I live in US and therefore have an English-based laptop with an
> English-based keyboard.
>
> I am also a programmer and would like to test what happen if I have a
> SQLite table
> which contains a Unicode character.
>
> Now my question is: is it possible to enter a Unicode character
> (umlaut symbol, german 'ss'
> character or maybe even something from Chinese alphabet) inside the
> sqlite3.exe in
> order to test my program?
>
> Thank you.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



-- 
--
VerifEye Technologies Inc.
151 Whitehall Dr. Unit 2
Markham, ON
L3R 9T1

[sqlite] Bug: Successfully committed transaction rolled back after power failure

[Adam Devita]

At the risk of repeating something mentioned last week on this thread.
One tactic to reduce/avoid the no-directory sync problem is to use WAL
mode. The commit in WAL is write to the WAL file, so the the directory
sync problem goes away.

If you want to be in paranoid mode, don't trust others. Why not use
the backup api before checkpointing and after the checkpoint has
succeeded, check that both dbs have the same state before deleting (or
archiving) the backup?

Another tactic to handle that hasn't been discussed (if memory serves
me) that I'm curious if the following would work to get around the
directory sync issue:
Separate Sqlite  telling your program that the transaction is done
from the program telling the user. Don't tell the *user* that the
transaction is done until you have confirmed the .journal file that
existed before your commit no longer exists after it, so a power off
rollback can't happen. Could the OS lie and say the .journal file has
been deleted only to have it re-appear if the power failure is at the
'wrong' time?


The above about implementation of RAID is good. There were battery
backed up caching controllers 20 years ago. In the event of a power
loss, the cached writes could be completed later.

regards,
Adam




On Mon, Feb 1, 2016 at 10:14 AM, Howard Chu  wrote:
> Stephen Chrzanowski wrote:
>>
>> @Rowan;
>>
>> First off, whether the OS or SQLite is ACID or not, if you pull the plug
>> on
>> your hardware, all bets are off on whether it'll BOOT, let alone recover a
>> single transaction.  I get that this could be a useful tool when doing
>> disaster proofing, but, at that stage in the game of bulletproofing, you
>> can't win every battle, and you're running into that at 100 miles an hour.
>
>
> Your expectations are pretty low. On a properly configured Unix host,
> there's no reason for a powerfail to prevent a successful reboot. E.g., if
> you mount boot and root filesystems as read-only filesystems, they can never
> get corrupted. If you're using modern filesystems for your writable
> partitions (e.g., FSs with journaling) then there's also no reason for them
> to fail to come back online.
>
> So it just comes down to your application code being reliable.
>
> I should note that SQLightning has none of the problems being described in
> this thread - in its default mode, it is full-ACID and a powerfail cannot
> lose data or corrupt the database. And it does all this while being at least
> 30% faster on writes than vanilla SQLite.
>
> Yes, the OS could have bugs. Yes, the hardware could physically fail. That's
> pretty rare though; HDDs R/W heads auto-retract on powerfail so unless the
> entire mechanism actually jammed, there's no way for a powerfail to cause a
> head crash or any other destructive event.
>
> Bottom line - if your OS reboots successfully, there's no excuse for your
> database to not also come up successfully, fully intact.
>
> --
>   -- Howard Chu
>   CTO, Symas Corp.   http://www.symas.com
>   Director, Highland Sun http://highlandsun.com/hyc/
>   Chief Architect, OpenLDAP  http://www.openldap.org/project/
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



-- 
--
VerifEye Technologies Inc.
151 Whitehall Dr. Unit 2
Markham, ON
L3R 9T1

[sqlite] SQLite crashing

[Adam Devita]

Hi Igor,
I don't think you understood what I was trying to get at.  Please
allow me to rephrase:  There isn't enough information about how things
are being cleaned up to point out a problem, only make general
suggestions about good practice.

This is why I was asking about where you would ensure you are properly
shutting down the database before deleting your wrapper.  I had
expected to see something of the form:


//private: sqlite3 *db;
BOOL SomeWrapperClass::Closedb()
{
if(db==NULL){
b_sqlite_db_open = false;//Ensure state variable of wrapper is correct
return TRUE; // db wasn't open, so Closed is OK
}

int ret=sqlite3_close(db);
if (ret==SQLITE_OK){
db = NULL;
b_sqlite_db_open = false;
return TRUE;  //properly closed my db connection, no errors
}
return FALSE; //if we got here, there is an error; break-point hit
; What didn't get finalized? How did this happen?
}

I would have expect you to, before delete m_db to do something like
if(m_db != NULL) {
  if(!m_db->Closedb() ){ //assumes that queries have already been
finalized ;
  ;//error handle code, break point, how did we fail to close?
   }
delete m_db;
 }

or a method of your dll that is some exported DestroyObject(Database *db) ;

or at least tell us that you are ensuring sqlite_close is called via
the destructor of m_db;

While in C++ one may choose to not check if the thing being deleted is
NULL before doing it, it is a clutter/style thing. For debugging
purposes  finding out that the thing that should have been null isn't
as expected.  It can often lead to one saying "hey, that should have
got deleted already!... oh somehow  delete x instead of safe_delete(x)
got used so while it got deleted earlier and x should have been null
at this point"

Are you unit testing the trivial cases?
Create+Destroy
Create+Connect+Destroy
Create+Connect+DoBasicQuery+Destroy


regards,
Adam

On Mon, Jan 25, 2016 at 2:02 PM, Igor Korot  wrote:
> Hi, Adam,
>
> On Mon, Jan 25, 2016 at 11:27 AM, Adam Devita  wrote:
>> Where do you pass to the dll something that goes to sqlite3_close(db); ?
>> ( https://www.sqlite.org/c3ref/close.html )
>> When that happens, does m_db get set to NULL (or now refers to memory
>> that is now NULL)
>> Do you check for m_db == NULL before deleting it?
>
> SQLiteDatabase class is just a wrapper around the SQLite interface.
> The constructor is empty, but in the Connect() function of the class I call
>
> sqlite3_open().
>
> And here is the code that you are asking for:
>
> void MainFrame::ConnectToDb()
> {
> Database *db = NULL;
> LoadLibrary();
> func = GetProcAddress();
> m_db = func( db );
> }
>
> Also, in C++ delete'ing NULL is perfectly normal operation.
>
> Thank you.
>
>>
>> regards,
>> Adam DeVita
>>
>> On Mon, Jan 25, 2016 at 11:16 AM, Igor Korot  wrote:
>>> Hi, Peter,
>>>
>>> On Mon, Jan 25, 2016 at 10:50 AM, Peter Aronson  
>>> wrote:
>>>> Igor,
>>>>
>>>> You can't safely pass a SQLite handle between different SQL DLLs that way 
>>>> if
>>>> they're both built with their own copy of the amalgamation (or link to
>>>> things built with different copies). SQLite uses a handful of global
>>>> variables, but each DLL has its own copy of each of these global variables
>>>> and they can and will have different values, which can mess things up.  I
>>>> ran into a version of this problem when I tried to load a 2nd DLL built 
>>>> with
>>>> its own copy of the sqlite3.c amalgamation.  I fixed that by exposing the
>>>> SQLite3 entrypoints in the first DLL and linking the second DLL against it
>>>> so there was only one copy of the amalgamation used for that SQLite3 
>>>> handle.
>>>
>>> The SQLite is built only once and with just one version of the code.
>>>
>>> Consider following pseudo-code:
>>>
>>> In DLL:
>>>
>>> BOOL APIENTRY DLLMain()
>>> {
>>> }
>>>
>>> extern "C" __declspec(dllexport) Database *CreateObject(Database *db)
>>> {
>>> db = new SQLiteDatabase();
>>> db->Connect();
>>> return db;
>>> }
>>>
>>> In the main application:
>>>
>>> mainframe.h:
>>>
>>> class MainFrame
>>> {
>>> public:
>>>  MainFrame();
>>>  ~MainFrame();
>>>  void ConnectToDb();
>>> private:
>>>  Database *m_db;
>>> };
>>>
>>> mainframe.cpp:
>>>
>>> void MainFrame::ConnectTo

[sqlite] SQLite crashing

[Adam Devita]

Where do you pass to the dll something that goes to sqlite3_close(db); ?
( https://www.sqlite.org/c3ref/close.html )
When that happens, does m_db get set to NULL (or now refers to memory
that is now NULL)
Do you check for m_db == NULL before deleting it?

regards,
Adam DeVita

On Mon, Jan 25, 2016 at 11:16 AM, Igor Korot  wrote:
> Hi, Peter,
>
> On Mon, Jan 25, 2016 at 10:50 AM, Peter Aronson  wrote:
>> Igor,
>>
>> You can't safely pass a SQLite handle between different SQL DLLs that way if
>> they're both built with their own copy of the amalgamation (or link to
>> things built with different copies). SQLite uses a handful of global
>> variables, but each DLL has its own copy of each of these global variables
>> and they can and will have different values, which can mess things up.  I
>> ran into a version of this problem when I tried to load a 2nd DLL built with
>> its own copy of the sqlite3.c amalgamation.  I fixed that by exposing the
>> SQLite3 entrypoints in the first DLL and linking the second DLL against it
>> so there was only one copy of the amalgamation used for that SQLite3 handle.
>
> The SQLite is built only once and with just one version of the code.
>
> Consider following pseudo-code:
>
> In DLL:
>
> BOOL APIENTRY DLLMain()
> {
> }
>
> extern "C" __declspec(dllexport) Database *CreateObject(Database *db)
> {
> db = new SQLiteDatabase();
> db->Connect();
> return db;
> }
>
> In the main application:
>
> mainframe.h:
>
> class MainFrame
> {
> public:
>  MainFrame();
>  ~MainFrame();
>  void ConnectToDb();
> private:
>  Database *m_db;
> };
>
> mainframe.cpp:
>
> void MainFrame::ConnectToDb()
> {
> Database *db = NULL;
> LoadLibrary();
> func = GetProcAddress();
> m_db = func( db );
> }
>
> MainFrame::~MainFrame()
> {
> delete m_db;  // this is where the crash happens
> }
>
> The pointer address are the same in DLL and main application MainFrame class.
> And as I said the crash occurs when it tries to acquire the mutex lock.
>
> Thank you.
>
>>
>> Peter
>>
>>
>>
>>
>> On 1/24/2016 10:18 PM, Igor Korot wrote:
>>>
>>> Hi, ALL,
>>> I have a strange problem.
>>>
>>> I am trying to use sqlite in my program. It has a main application and
>>> couplef DLLs.
>>>
>>> I am getting the connection in one of the DLL, then the pointer is passed
>>> up
>>> to the main application.
>>>
>>> Upon exiting from the application I'm trying to close the connection and
>>> delete all the memory.
>>>
>>> Unfortunately upon exiting the application it crashes inside
>>> sqlite3_mutex_enter().
>>> The comment above the function says:
>>>
>>> [quote]
>>> /*
>>> ** Obtain the mutex p. If some other thread already has the mutex, block
>>> ** until it can be obtained.
>>> */
>>> [/quote]
>>>
>>> The DLL does not start any threads, in fact the application will be 1
>>> thread only.
>>> So is there some compile-time switch I should use to mitigate the issue?
>>>
>>> Moreover I don't understand why am I getting the assertion - there is no
>>> MT
>>> involved.
>>>
>>> Can someone shed some lights?
>>>
>>> Thank you.
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users at mailinglists.sqlite.org
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



-- 
--
VerifEye Technologies Inc.
151 Whitehall Dr. Unit 2
Markham, ON
L3R 9T1

[sqlite] Using sqlite3.exe as a subprocess

[Adam Devita]

Good day,

Assuming you don't want to alter the code of the shell tool to take a
named pipe (this isn't that difficult to do, unfortunately due to the
business logic I can't go into, it was not allowed):

Have you tried to create a command prompt shell, begin the sqlite
shell tool in that and direct IO to the shell?  There is an occasion
(the reasoning for which I will not go into) that we do this in c#.
Yours should be able to pull the same (or similar) trick in c++.
(You should get the gist from this)

 System.Diagnostics.Process pIOSql ;


  pIOSql = new System.Diagnostics.Process();
  pIOSql.StartInfo.CreateNoWindow = true;
  pIOSql.StartInfo.UseShellExecute = false;

 pIOSql.StartInfo.FileName = PathToDbDirectory + "sqlite3.exe";
 pIOSql.StartInfo.Arguments = "\""+PathToDbDirectory + "my.db\"";

 pIOSql.StartInfo.RedirectStandardError = true;
 pIOSql.StartInfo.RedirectStandardInput = true;
 pIOSql.StartInfo.RedirectStandardOutput = true;
 pIOSql.Start();
 pIOSql.StandardInput.WriteLine("select count(1) from
someTable;\n");
  }
.

  pIOSql.StandardOutput.DiscardBufferedData();
  StreamWriter sCmd = pIOSql.StandardInput;
  String sqlcmd = Command;
  sCmd.WriteLine(sqlcmd);

etc.

One has to do a bit of work to handle timing.  If you aren't worried
(at all) about security then you could even create a temp file, and
stick your queries into it, so you can redirect your output to another
file and funnel everything through .read
Be careful about empty set results!

regards,
Adam DeVita


On Fri, Jan 15, 2016 at 8:32 AM, Dominique Devienne  
wrote:
> On Fri, Jan 15, 2016 at 4:53 AM, Matthew Allen  wrote:
>
>> It seems that sqlite3.exe (console) doesn't work as a subprocess with
>> pipes.
>> [...] I expect there is something funny going on with sqlite3.exe's
>> stdout/stdin.
>
>
> Sorry to highjack your thread Matthew, but I have what I consider a related
> use case.
>
> I'd like to embed the SQLite3 shell into another program, both a console
> program and a gui one,
> and because I'd like it to access in-memory databases, this cannot be done
> via forking and pipes.
>
> Basically I'd like the shell to have a "Virtual Console Interface" (VCI),
> to be able to reuse all the shell's
> goodness, in client apps, w/o having to hack and duplicate the shell's
> code. With some way to access
> in-memory databases in the same process as well (a special form of attach
> or an API?).
>
> I realize the shell is not meant and designed to be embedded right now,
> only the library is,
> but I'd really like it to be, basically. My own 2016 wishful-thinking
> feature request :). --DD
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



-- 
--
VerifEye Technologies Inc.
151 Whitehall Dr. Unit 2
Markham, ON
L3R 9T1

[sqlite] analysis of a corrupt db

[Adam Devita]

Thanks.
My co-worker that got the jffs2 dump of the  file system reports that
there is a bug in the python script, so my above post is a false
trail.

He analysed the raw JFFS2 data and found it is consistent in that all
the node header and data checksums are correct. There is no corruption
in system.db from the JFFS2 point of view. I can tell that the
corruption occurred during the f/w update on Dec 3 because all the
timestamps for the system.db nodes are during that time period. From
the f/w update log file, sysmgr was stopped at the start of the update
procedure along with everything else. I tried replicating what would
have happened by downgrading my unit to the same version of f/w that
was running at the time of the update, writing the raw JFFS2 to my
unit, and then applying the Dec 3 update. The update completely
successfully and system.db was ok.

There weren't any holes in the file, he wrote a program to analyze
this specifically.

He successfully simulated the above update three times but each time
the binary system.db was different (not identical). I'll get a copy
later to do a binary comparison. It may be that there are other system
events. I've asked that a test be run under sub-optimal power
conditions.  It seems unlikely this is an sqlite issue. It may be a
file system / hardware / harsh environment thing.


Adam

On Wed, Jan 13, 2016 at 7:00 PM, David Woodhouse  wrote:
> On Tue, 2016-01-12 at 12:18 -0500, Adam Devita wrote:
>>
>> A co-worker managed to get an copy of the db by as interpreted by
>> jffs2dump of the file system, that was extracted by the jffs2dump
>> python script (from git hub). It is interesting that it is also
>> corrupt but in a different way.
>
> Forgetting sqlite, can you compare the binary files?
>
> JFFS2 creates each file from the log entries, each of which carry a
> sequence number, and cover a given range of the file (not more than a
> 4KiB page).
>
> There should never be any *holes* in the file, which are not covered by
> any data node. Were there in your dump? That would imply that a data
> node was lost (its CRC failed, perhaps, and wasn't caught in time to be
> written out elsewhere).
>
> --
> David WoodhouseOpen Source Technology Centre
> David.Woodhouse at intel.com  Intel Corporation
>



-- 
--
VerifEye Technologies Inc.
151 Whitehall Dr. Unit 2
Markham, ON
L3R 9T1

[sqlite] analysis of a corrupt db

[Adam Devita]

Some more information:

A co-worker managed to get an copy of the db by as interpreted by
jffs2dump of the file system, that was extracted by the jffs2dump
python script (from git hub). It is interesting that it is also
corrupt but in a different way.

sqlite> select  * from config where id ='isp_de_mode';
(11) database corruption at line 70244 of [fd0a50f079]
(11) statement aborts at 9: [select  * from config where id
='isp_de_mode';] database disk image is malformed
Error: database disk image is malformed
sqlite> select  id from config where id ='isp_de_mode';
isp_de_mode
sqlite> select count(id) from config where id ='isp_de_mode';
1
sqlite> select count(1), id from config group by id order by 1 desc limit 2;
2|isp_de_mode
1|audio_bitrate_s0
sqlite> .schema
CREATE TABLE config( id varchar(255) PRIMARY KEY,file
varchar(255),xpath varchar (255), value varchar(255),
venc_switch_xpath varchar(255), apply_cmd varchar(255), cacheIsDirty
integer default -1  );
sqlite>
sqlite> pragma integrity_check;
row 1275 missing from index sqlite_autoindex_config_1
row 1276 missing from index sqlite_autoindex_config_1
row 1277 missing from index sqlite_autoindex_config_1
row 1346 missing from index sqlite_autoindex_config_1
row 1347 missing from index sqlite_autoindex_config_1
row 1348 missing from index sqlite_autoindex_config_1
row 1349 missing from index sqlite_autoindex_config_1
row 1350 missing from index sqlite_autoindex_config_1
row 1351 missing from index sqlite_autoindex_config_1
row 1352 missing from index sqlite_autoindex_config_1
row 1353 missing from index sqlite_autoindex_config_1
row 1354 missing from index sqlite_autoindex_config_1
row 1367 missing from index sqlite_autoindex_config_1
row 1372 missing from index sqlite_autoindex_config_1
wrong # of entries in index sqlite_autoindex_config_1

regards,
Adam



On Tue, Jan 12, 2016 at 12:01 PM, Adam Devita  wrote:
> Good day,
> Thank you for some avenues of investigation.
>
> Q: Does your program examine the codes returned by SQLite3 calls and
> check to see that they are all returning SQLITE_OK ?
>
> A1: The upgrade process is done by a script. It isn't error checking &
> executes queries via the shell tool.  I followed up with the script
> folks and they got  a pull of the file system log from the failed
> unit.  There are 2 queries that run to update new.db from data in
> old.db, and appear to execute and update multiple rows each.
>
> A2: From inspecting the normal op program (at the version of the
> firmware tag)  It looks fairly good.
> The only return code not checked is a on  commit/rollback that is
> after a failed prepare statement or one particular function that takes
> a checked input, performs bind, step, reset at one point.
> There may be a vulnerability at one point to sqlite bind text that has
> a null input for the string. The docs say such an oversight would
> return SQLITE_MISUSE so the step would not happen.
>
> Q: Does the script shut down the program and wait for the program to
> quit before it starts running its own commands, or are the two things
> done independently ?
>
> A: The script does not check for a response but it waits. It would be
> very unusual for their to be pending writes to the old db (or user
> requests) while new db is extracting the information. I suppose WAL
> mode would be safer here.
>
> The upgrade Script doesn't check values of the returned by the shell
> tool. That said. Since the alg is :update new.db from old.db, replace
> old.db file with new.db. New.db's default value for cache is "reload
> value from config files", the script failure would leave new.db in a
> state that, as long as not corrupt, would simply reload from the
> config files.
>
>
> Q: Are both programs running on the computer with the database stored
> on a hard disk, or is anything accessing the database across a network
> ?
> A: All operations happen on locally stored flash or memory only. (The
> new.db in an update is uncompressed to ram, once updates are complete,
> then it gets copied to the local flash.)
> The only network ops are "upload upgrade package to remote device,
> then tell it to use it". There are no sqlite operations over a
> network.
>
>
> --As per DRH's instruction--
> f:\Users\Adam>sqlite3.exe system.bad
> SQLite version 3.10.0 2016-01-06 11:01:07
> Enter ".help" for usage hints.
> sqlite> .log stdout
> sqlite> pragma integrity_check;
> (11) database corruption at line 58034 of [fd0a50f079]
> (11) database disk image is malformed
> Error: database disk image is malformed
> sqlite>
> -
>
> showdb: I assume this is a linux tool?  Where would I pull that from?
> Our device doesn't have all the utilities but I can put a copy of the
> bad db on

[sqlite] analysis of a corrupt db

[Adam Devita]

Good day,
Thank you for some avenues of investigation.

Q: Does your program examine the codes returned by SQLite3 calls and
check to see that they are all returning SQLITE_OK ?

A1: The upgrade process is done by a script. It isn't error checking &
executes queries via the shell tool.  I followed up with the script
folks and they got  a pull of the file system log from the failed
unit.  There are 2 queries that run to update new.db from data in
old.db, and appear to execute and update multiple rows each.

A2: From inspecting the normal op program (at the version of the
firmware tag)  It looks fairly good.
The only return code not checked is a on  commit/rollback that is
after a failed prepare statement or one particular function that takes
a checked input, performs bind, step, reset at one point.
There may be a vulnerability at one point to sqlite bind text that has
a null input for the string. The docs say such an oversight would
return SQLITE_MISUSE so the step would not happen.

Q: Does the script shut down the program and wait for the program to
quit before it starts running its own commands, or are the two things
done independently ?

A: The script does not check for a response but it waits. It would be
very unusual for their to be pending writes to the old db (or user
requests) while new db is extracting the information. I suppose WAL
mode would be safer here.

The upgrade Script doesn't check values of the returned by the shell
tool. That said. Since the alg is :update new.db from old.db, replace
old.db file with new.db. New.db's default value for cache is "reload
value from config files", the script failure would leave new.db in a
state that, as long as not corrupt, would simply reload from the
config files.


Q: Are both programs running on the computer with the database stored
on a hard disk, or is anything accessing the database across a network
?
A: All operations happen on locally stored flash or memory only. (The
new.db in an update is uncompressed to ram, once updates are complete,
then it gets copied to the local flash.)
The only network ops are "upload upgrade package to remote device,
then tell it to use it". There are no sqlite operations over a
network.


--As per DRH's instruction--
f:\Users\Adam>sqlite3.exe system.bad
SQLite version 3.10.0 2016-01-06 11:01:07
Enter ".help" for usage hints.
sqlite> .log stdout
sqlite> pragma integrity_check;
(11) database corruption at line 58034 of [fd0a50f079]
(11) database disk image is malformed
Error: database disk image is malformed
sqlite>
-

showdb: I assume this is a linux tool?  Where would I pull that from?
Our device doesn't have all the utilities but I can put a copy of the
bad db on a development linux environment for further tests.

On Tue, Jan 12, 2016 at 10:55 AM, Richard Hipp  wrote:
> On 1/12/16, Adam Devita  wrote:
>>
>> Shell Tool Observations:
>> SQLite version 3.8.4.3 2014-04-03 16:53:12
>> Enter ?.help? for usage hints.
>
> If you first do:  ".log stdout" before doing the "PRAGMA
> integrity_check", you might get some better diagnostics.  Or maybe
> not.  In any event, it doesn't hurt to try.
>
>> sqlite> pragma integrity_check;
>> Error: database disk image is malformed
>>
>
> Other things to try:
>
> ./configure; make showdb;
> ./showdb your-corrupt-db-file.db dbheader
> ./showdb your-corrupt-db-file.db pgidx
>
> There is a lot of other things you can do with the showdb program.
> Type "./showdb" with no argument for a very terse summary.  I, for
> one, would be very interested in seeing the output of the above two
> commands.
>
> --
> D. Richard Hipp
> drh at sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



-- 
--
VerifEye Technologies Inc.
151 Whitehall Dr. Unit 2
Markham, ON
L3R 9T1

[sqlite] analysis of a corrupt db

[Adam Devita]

Good day,

I've got a case of a corrupt file on some hardware of our own design,
a linux based IO controller in a harsh environment.

It was lately discovered that 1 controller in a field test group had a
corrupt db on it, so naturally we are attempting to figure out what
happened.

The hardware has the db on flash memory.

DB Description:
For the sake of documenting it, Db Size is about 370KB
It is used as a status scoreboard for various system configuration information.

1 table only:
CREATE TABLE config( id text PRIMARY KEY,file text,xpath text, value
text, venc_switch_xpath text, apply_cmd text, cacheIsDirty integer
default -1 );

Under normal operation all access is controlled by 1 program that
serializes requests from the rest of the system, and executes batches
of statements in a transaction. Under normal operation only SELECT and
UPDATE queries are run.

The db doesn't grow in number of records. There are (always at this
firmware version) 1455 rows in a good db.

Under upgrade, the above db management program is shut down and the
upgrade script runs commands through a shell tool. Under upgrade we do
use INSERT OR REPLACE as well as update. Upgrades are normally
executed by creating a new db with default values (and inserting the
list of known ids)  and then attaching the new db to the old one and
replacing records into the new db that have non-default values.

Shell Tool Observations:
SQLite version 3.8.4.3 2014-04-03 16:53:12
Enter ?.help? for usage hints.
sqlite> pragma integrity_check;
Error: database disk image is malformed
sqlite> .tables
sqlite> .schema
Error: database disk image is malformed
sqlite> select * from sqlite_master;
Error: database disk image is malformed
sqlite>.dump
...eventually
INSERT INTO "config" VALUES('gforce_orientation','good
_data','more_good_data','',NULL,NULL,0);
INSERT INTO "config"
VALUES('audio_input_gain','mygood_path1','alsogood_data','',NULL,'good
text data',0);
/ ERROR: (11) database disk image is malformed ***/
/** ERROR: (11) database disk image is malformed */
COMMIT;

Other hacks at it:
Inspecting the file and from the above with a comparison to a known
good file the headers appear ok. The table exists but our code returns
this db is corrupt.
Using a hex editor to manually inspect the file with a comparison to a
known good one shows that there is no data that isn't "db-ish" : This
is not a case of rogue data being written to the file, as far as I can
see.

Is there another utility I can use to help point at the problem?
How is .dump working to print out almost everything when .tables
returns the db is corrupt?

I'd like to attempt to figure out what the last bit of data written in was.

If I .dump into a text file, then open a new db and .read into it, I
get 1454 records (1 fewer than the 'good db')
Comparing to the good file, I know that  audio_output_gain is the
record that is not printed by the .dump.
Does it follow that it must be the corrupt record?
How would that prevent .table or .schema from getting read?

>From the values of the cacheIsDirty flag, I deduce that it was in the
process of an upgrade, not normal user interaction, when the
corruption occurred. (This does not conclusively point to if the error
happened during the upgrade, or immediately after it as the normal
mode works through the records with 'dirty' cache.  That said, all
~200 records of 'dirty' cache should be updated in 1 transaction, so 1
record being wrong seems to not fit. )


regards,
Adam DeVita


BTW: While testing this, I noticed that if I ftp the file to the
device from win 7 command prompt ftp to the linux box without setting
to bin (leaving in ascii mode), that will corrupt the db. That is a
simple move to corrupt that isn't listed on
(https://www.sqlite.org/howtocorrupt.html ).

--

[sqlite] Problem with accumulating decimal values

[Adam Devita]

Good day,
As a matter of interest, when calculating interest on a sum of money
expressed in pennies, how do you handle  int arithmetic truncating?
Is that an accounting design rule thing when dealing with fractions of
a penny to round?

Is this an arbitrary quantization?  Once upon a time there existed the Ha'penny
https://en.wikipedia.org/wiki/Halfpenny_%28British_pre-decimal_coin%29
https://en.wikipedia.org/wiki/Half_cent_%28United_States_coin%29



I think the ugly-bags-of-mostly-water indirection was humorous.  I
found it funny.

https://en.wikipedia.org/wiki/Home_Soil

live long and prosper.

Adam

On Wed, Dec 16, 2015 at 10:17 AM, Bernardo Sulzbach
 wrote:
> On Wed, Dec 16, 2015 at 9:43 AM, Keith Medcalf  wrote:
>>
>>> Hello, so in short, rounding the column anywhere it is used, is
>>> another solution. I confirmed this below. Thanks, E. Pasma.
>>>
>>> BEGIN;
>>> UPDATE fmtemp SET bal = ROUND(bal,2) + 123.45;
>>> (repeat a 1.000.001 times
>>> END;
>>> SELECT bal FROM fmtemp;
>>> 123450123.45
>>
>> Absolutely not!  You should NEVER round the value and store it back in the 
>> datastore.  Rounding is ephemeral for the convenience of 
>> ugly-bags-of-mostly-water who are fixed in their world-view so that data can 
>> be DISPLAYED to them in a format that fits their limited view.
>>
>
> Although I agree about not rounding and updating the store with
> "corrected" values. I don't think there is a need to call the
> ugly-bags-of-mostly-water ugly-bags-of-mostly-water. Also, I wouldn't
> want myself to see 22.99 instead of 23.00 in the frontends I
> use either. In a practical sense, I believe the latter reduces the
> amount of processing my brain has to do and I can better focus on what
> matters. But then again, just use string formatting on the view of the
> project.
>
> On Wed, Dec 16, 2015 at 12:05 PM, E.Pasma  wrote:
>
>> Ok this does not work of any scale of numbers. But a solution with integers
>> neither does
>>
>> E.Pasma
>>
>
> Preferences aside, no solution ever devised will work with **any**
> scale with numbers as we have finite data storage. That is very
> pedantic, but just to be clear. I like integer better than floating
> points and text for currencies, some will have other preferences, it
> does not really matter as long as we are not working together.
>
> --
> Bernardo Sulzbach
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



-- 
--
VerifEye Technologies Inc.
151 Whitehall Dr. Unit 2
Markham, ON
L3R 9T1

[sqlite] Problem with accumulating decimal values

[Adam Devita]

A good start at the long answer can be found in the archives of this list.

http://sqlite.1065341.n5.nabble.com/Simple-Math-Question-td85140.html#a85157
also found at
https://www.mail-archive.com/sqlite-users at 
mailinglists.sqlite.org/msg04587.html
(web search sqlite "simple math question")

It has background, theory, and they show how the conversions of
decimals to floating point and how they add works, using several
examples.



regards,
Adam D.



On Fri, Dec 11, 2015 at 9:55 AM, Richard Hipp  wrote:
> On 12/11/15, Frank Millman  wrote:
>>
>> Can anyone explain what is going on, and is there a way to avoid it?
>>
>
> Short answer:  https://www.sqlite.org/faq.html#q16
>
> I don't have a longer answer readily at hand, but as questions about
> floating point numbers come up a lot, probably I should write up a
> tutorial.  I'll try to get that done before the end of the year...
> --
> D. Richard Hipp
> drh at sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



-- 
--
VerifEye Technologies Inc.
151 Whitehall Dr. Unit 2
Markham, ON
L3R 9T1

[sqlite] How to see SQLite debugging information

[Adam Devita]

"This is on a Win7 machine. How do I bring up that console window?"

1) Click Start
2) type cmd
you will see cmd.exe in the list of programs to run.
3) click on cmd.exe

To run MS-excell from the command line, you can call it from the full
path (check version):

"C:\Program Files\Microsoft Office\Office15\Excel.exe"


I agree with David.  If you want to prove your dll is working (or not
working), you should attempt to isolate it, for direct testing. Many
hours of many people's lives have been wasted attempting to indirectly
test things that have more than 1 unknown or potential source of error
 in a chain.

regards,
Adam DeVita


On Wed, Dec 9, 2015 at 10:12 AM, Bart Smissaert
 wrote:
> Will look at this, it is a VB6 console app and that may just do the job:
> http://vb.mvps.org/samples/Console/
>
> RBS
>
> On Wed, Dec 9, 2015 at 1:03 PM,  wrote:
>
>> On Windows you will get a console and standard output if you are running a
>> console application, and otherwise not.
>>
>> I think you need a simple console app to call your ActiveX DLL, or find
>> some
>> other way. Windows GUI app and standard output do not play well together.
>>
>> Regards
>> David M Bennett FACS
>>
>> Andl - A New Database Language - andl.org
>>
>>
>>
>> On 12/8/15, Bart Smissaert  wrote:
>> > So, what/where is that standard output channel?
>> > This is on a Win7 machine. How do I bring up that console window?
>> >
>>
>> The standard output is what displays on your screen when you are in a DOS
>> box.
>>
>> SQLite does not have any facilities for debugging in a GUI on Windows.
>>
>> --
>> D. Richard Hipp
>> drh at sqlite.org
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



-- 
--
VerifEye Technologies Inc.
151 Whitehall Dr. Unit 2
Markham, ON
L3R 9T1

[sqlite] Making data unique

[Adam Devita]

When you are about to insert into the table and find that ID &
Date/time are identical to another record, but  the data is different,
do you want to overwrite, or not?  Do you want an error?

Adam

On Mon, Dec 7, 2015 at 10:01 AM, Andrew Stewart
 wrote:
> Hi,
> I have a table that consists of 3 elements:
> ID - integer
> Date/time - integer
> Data - integer
> A single ID can exist multiple times.
> A single Date/time can exist multiple times.
> An ID & Date/time combination is unique.
>
> What is the best way to ensure uniqueness in this table.
>
> Thanks,
> Andrew Stewart
> Software Designer
>
> Argus Controls
> #101 - 18445 53 AVE
> Surrey, BC  V3S 7A4
>
> t: 1-888-667-2091  ext : 108
> t: 1-604-536-9100  ext : 108
> f: 604-538-4728
> w: www.arguscontrols.com
> e: astewart at arguscontrols.com
>
> Notice: This electronic transmission contains confidential information, 
> intended only for the person(s) named above. If you are not the intended 
> recipient, you are hereby notified that any disclosure, copying, 
> distribution, or any other use of this email is strictly prohibited. If you 
> have received this transmission by error, please notify us immediately by 
> return email and destroy the original transmission immediately and all copies 
> thereof.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



-- 
--
VerifEye Technologies Inc.
151 Whitehall Dr. Unit 2
Markham, ON
L3R 9T1

[sqlite] Warnings for non-deterministic queries?

[Adam Devita]

I think the OP meant to write:
"If the expression is an aggregate expression, it is evaluated across
all rows in the group. Otherwise, it is evaluated against a single
arbitrarily chosen row from within the group. "

Is there a way I could programmatically determine that a query is
going to use an arbitrarily chosen row from within the group at query
prepare time?


Adam

On Fri, Nov 27, 2015 at 8:46 AM, Keith Medcalf  wrote:
>
>
>> Is there a way I could programatically determine that a query is non-
>> deterministic at query prepare time?
>
> What do you mean, non-deterministic?  The result is deterministic in all 
> cases.
>
> It may be complicated and/or difficult for you to compute, but it is always 
> deterministic.  The result is generated by running an unchanging algorithm on 
> unchanging data.  If there is no random inputs and the computer hardware is 
> not broken, then the results are entirely determined by the algorithm 
> executed and the state of the data upon which it is operating.
>
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



-- 
--
VerifEye Technologies Inc.
151 Whitehall Dr. Unit 2
Markham, ON
L3R 9T1

[sqlite] Mailing list policy change

[Adam Devita]

Assuming the A*spammer is a basic algo subscribed to the list, and
sending to any sender in a reasonably short time after posting;

Question:  Is it possible for the admin to easily backup the list,
bisect it, and test for spam?
That technique should identify the offending address in
log2(N-Users-Subscribed) attempts.

If unfeasible, I'd prefer mangling / salting the e-mail addresses of
users (if this can be done easily) displayed to thwart bot spammers
and still see the names of the poster.   I follow interesting 'topics'
rather than people, but I think seeing the names of posters up-front
is part of the community dynamic that has been built, and makes
following the exchanges easier to follow, particularly for longer
threads.

Adam D



On Thu, Oct 29, 2015 at 4:01 AM, SQLite mailing list
 wrote:
> On Wed, Oct 28, 2015 at 6:52 PM, General Discussion of SQLite Database <
> sqlite-users at mailinglists.sqlite.org> wrote:
>
>> Effective immediately, the sender email address for mailing list posts
>> will be elided.  All replies must go back to the mailing list itself.
>>
>
> Please reconsider. Not knowing who's talking is untenable.
>
> Let each and everyone's SPAM filter take care of it.
>
> As someone already mentioned, there are tons of way to harvest past email
> addresses from archives anyway.
>
> --DD
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



-- 
--
VerifEye Technologies Inc.
151 Whitehall Dr. Unit 2
Markham, ON
L3R 9T1

[sqlite] setup C code with cppunit using test db

[Adam Devita]

Good day,

I'd like to test some C code using the cpputest package.

The code is a simple transaction where 1 prepared update statement
gets various parameters bound and stepped in a loop.

I'd like to make a test db so I can ensure that the code will work
(and there are no syntax errors in the sql)

If I copy the amalgamation (3.8.8) into my test directory, and run the
make (Linux flavour is ubuntu 14.04lts being run on a Oracle virtual
box)

I get an error stating that MREMAP_MAYMOVE is undeclared. (line 29874)
I don't see in the source or .h file where it would be defined.

Has anyone run into this one?  What should I do ?


regards,
Adam DeVita

-- 
--
VerifEye Technologies Inc.
151 Whitehall Dr. Unit 2
Markham, ON
L3R 9T1

[sqlite] what is typical pattern for test double of sqlite c api

[Adam Devita]

Good day,

I'm about to implement TDD for an existing c project that uses sqlite,
using CPPUnit.  Sqlite would be a dependency from the point of view of
the routines making calls to it.

Is is typical to just write a link time stub to substitute commonly
used parts of the interface (exec, open, prepare, bind, step, reset,
finalize, close)  or is it easy to do something in the spirit of
1) include sqlite3Ext.h  in the TDD sources
2) #define SQLITE_CORE
3) and use run time substitution on the sqlite3_api_routines to point
at my test doubles?

regards,
Adam D.





-- 
--
VerifEye Technologies Inc.
151 Whitehall Dr. Unit 2
Markham, ON
L3R 9T1

[sqlite] CSV excel import

[Adam Devita]

Instead of trying to conform to MS-Excel's csv format, wouldn't it be
better to write an import from .xls (or .ods if that is an open
standard) directly?

That way each cell's value can be bound to a position holder in a
query.  No more fussing with "In this country we use this symbol to
denote decimals", "my data has special characters or line feeds inside
a cell" etc.

regards,
Adam


On Thu, Jul 30, 2015 at 2:32 PM, Bernardo Sulzbach
 wrote:
>> My point is that I have seen so many emails regarding this incorrect csv 
>> import, that it would be so easy for us if it just simply works in the CLI 
>> and delivered in standard in the sqlite3 executable.
>
> I don't think I understand what you mean by this. Also, most of the
> problems seems to arise from the fact that CSV is just too weakly
> specified. See how better defined JSON is and how it solves a lot of
> problems (not suggesting JSON here).
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



-- 
--
VerifEye Technologies Inc.
151 Whitehall Dr. Unit 2
Markham, ON
L3R 9T1

[sqlite] Could frequent 'database is locked' errors mean SQLite is not a good fit for my application?

[Adam Devita]

Good day,

I'm sure others on the list will add better insight, but is your task
parallel enough that your nodes can work with a copy of the database
and submit changes the one the others copy from when 'done' their
calculation?

Are you using https://www.sqlite.org/c3ref/busy_timeout.html ?

regards,
Adam

This may be beside the point in terms of optimization, but  your query
looks rather character based on int like information.

On Thu, Jul 2, 2015 at 10:09 AM, Kathleen Alexander  wrote:
> Hi,
>
> I apologize if this is an incorrect forum for this question, but I am
> pretty new to SQLite and have been unable to resolve this issue through
> other searches. Feel free to direct me to a more appropriate forum.
>
> Essentially, I have written an application in C++ that interfaces (reads
> and writes) with a SQLite database, and I am getting lots of 'database is
> locked' errors. Right now, I am trying to establish whether those errors
> are due to my improper use of SQLite itself, or if the real problem is that
> SQLite is not a good fit for my application.
>
> My application runs on Linux (ubuntu 13.10), and is driven by a bash script
> that spawns many (~60 on a 64 core workstation) instances of a serial, C++
> program, each of which opens its own connection to the database and
> performs reads and writes.
>
> *An example SELECT query from my program looks like:*
> //open db connection
> sqlite3 *db;
> char *zErrMsg = 0;
> SQLITE3 sql(dbase.c_str());
>
> statement = "SELECT * from configs_table WHERE id='31'";
> sql.exe(statement.c_str());
> if( sql.vcol_head.size() > 0 ){
>//do things with sql.vdata[]
> }//end query returned results
>
> *An example of a write statement looks like:*
> statement = "UPDATE configs_table SET searched='2' WHERE id='31'";
> sql.exe(statement.c_str());
>
> About 97% of the time, the select statement works fine, but in the other 3%
> of cases, I see a 'database is locked' error in the log file of my program.
> About 50% of the time, the write statement returns 'database is locked'.
>
> Additionally, if this application is running and I try to query the
> database from the terminal, I almost always get a 'database is locked'
> error.
>
> Thus, I am wondering if I am doing something wrong in my implementation of
> the C++ --> SQLite interaction, or if the real problem is that this
> application is not well suited to use with SQLite (I went through the
> checklist before implementing it and thought my application passed the
> suitability requirements).
>
> Lastly:
> A. if it seems like this is an implementation issue, rather than a
> capability issue, if I were to scale up my method to spawn say 500-1000
> processes at a time (on a supercomputing cluster), would there be any
> concern about SQLite scaling to that level?
> B. If SQLite is not a good fit for my program, do you have any suggestions
> of an alternative database engine that is free or might be free or
> inexpensive for academic use?
>
> Thanks in advance,
> Kathleen
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



-- 
--
VerifEye Technologies Inc.
151 Whitehall Dr. Unit 2
Markham, ON
L3R 9T1

[sqlite] FW: SQLite (1.0.97) access via LAN

[Adam Devita]

What happens if you map a network drive to \\PC-Name\C\FolderName
(say drive X:) and then try to open the file as x:\data.db ?

Adam


On Thu, Jun 25, 2015 at 10:24 AM, Takashi Fukuda
 wrote:
> Donald Griggs tried to help this problem, and we looked at the Jean C's
> advice. However the problem remains. The actual codes using are as follows:
>
>
>
> ===
>
> string dataFile =  "PC-Name\\C\\FolderName\\data.db
>  " ;
>
> //
>
> // For x32 target compilation with System.Data.SQLite-x32-1.0.97.dll (.NET4)
>
> // The application works fine.
>
> //
>
> // For x64 target compilation with System.Data.SQLite-x64-1.0.97.dll (.NET4)
>
> // Error message: "unable to open database file"
>
>
>
> using (SQLiteConnection cnn = new SQLiteConnection(dataFile))
>
> using (SQLiteCommand cmd = cnn.CreateCommand()
>
> {
>
> cnn.Open();   < causing error !
>
> ===
>
>
>
> Can anybody help ?
>
>
>
> Best regards,
>
> Takashi Fukuda
>
> takashifukuda at comcast.net
>
>
>
>
>
> From: Takashi Fukuda [mailto:takashifukuda at comcast.net]
> Sent: Thursday, June 18, 2015 11:35 AM
> To: 'sqlite-users at mailinglists.sqlite.org'
> Subject: SQLite (1.0.97) access via LAN
>
>
>
> We are developing Windows applications with SQLite.dll. The current
> situation with VisualStudio-2010 is as follow:
>
>
>
> For x32 target compilation with System.Data.SQLite-x32-1.0.97.dll (.NET4)
>
> My application works fine.
>
>
>
> For x64 target compilation with System.Data.SQLite-x64-1.0.97.dll (.NET4)
>
> Error message: "unable to open database file"
>
>
>
> This problem happens when our applications access to the SQLite data file
> "data.db" via LAN, with "PC-Name\\C\\FolderName\\data.db
>  " format. The local access with
> "C:\\FolderName\\data.db" works fine. As described above, the x32 version
> works fine both local and LAN access.
>
>
>
> Best regards,
>
> Takashi Fukuda
>
> takashifukuda at comcast.net
>
>
>
>
>
> ---
> ??E ?
> http://www.avast.com
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



-- 
--
VerifEye Technologies Inc.
151 Whitehall Dr. Unit 2
Markham, ON
L3R 9T1

[sqlite] building SQLite DLL with Visual C++

[Adam Devita]

Good day,
I don't work in VB, so I can't help you in specifics on that. A quick
search on a search engine  pops up some videos on how to use sqlite in
a Visual Basic project.  The archives of this list have a lot of
questions where people are introduced to open, prep sql, (bind), step,
fetch, finalize, close. It is often a good idea to specify which book
you are working through for a question relating to a published
tutorial, as some future person may be reading the same book and could
find the thread helpful.  Some people on the list likely have a copy
of the same book on their shelf.  Someone on  this list might even be
the author.

Adam




On Thu, Apr 23, 2015 at 3:47 PM, Jay Smith  wrote:
> Thank you Adam for responding to my post. I have the windows binaries
> downloaded.
>
> At this point I am just following the instructions in the book. And I
> really am not sure what I need the dll for.
>
> Here's the scenario. I have created a program in vb2012. The program stores
> less than 20 fields of data. I am currently saving the data in a html
> format. I just recently discovered SQLite. I am now in the process of
> changing over to a database to store data. I studied SQL and Oracle 10
> years ago. I have almost completed the database for the project.  My
> problem is how to integrate the SQL db into my VB program.
>
> On Wed, Apr 22, 2015 at 10:16 AM, Adam Devita  wrote:
>
>> Good day,
>>
>>
>> Why are you compiling a dll instead of using the pre-compiled windows
>> binaries at http://www.sqlite.org/download.html?
>>
>> Are you adding some sort of extra wrapper?
>>
>> Why are you not adding the amalgamated c source in your project (turn
>> off use pre-compiled headers for that file) ?
>>
>> If you insist on creating your own dll, try the vs wizard to create a
>> dll project, then add code to it.
>>
>> regards,
>> Adam DeVita
>>
>>
>> On Wed, Apr 22, 2015 at 9:51 AM, Igor Tandetnik 
>> wrote:
>> > On 4/21/2015 11:01 AM, Jay Smith wrote:
>> >>
>> >> Before I sent the last message I had signed up to become a user.
>> >> My previous message was bounced.  WHY
>> >
>> >
>> > I, for one, have received both your original and this new message.
>> > --
>> > Igor Tandetnik
>> >
>> >
>> > ___
>> > sqlite-users mailing list
>> > sqlite-users at mailinglists.sqlite.org
>> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>>
>> --
>> --
>> VerifEye Technologies Inc.
>> 151 Whitehall Dr. Unit 2
>> Markham, ON
>> L3R 9T1
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



-- 
--
VerifEye Technologies Inc.
151 Whitehall Dr. Unit 2
Markham, ON
L3R 9T1

[sqlite] building SQLite DLL with Visual C++

[Adam Devita]

Good day,


Why are you compiling a dll instead of using the pre-compiled windows
binaries at http://www.sqlite.org/download.html?

Are you adding some sort of extra wrapper?

Why are you not adding the amalgamated c source in your project (turn
off use pre-compiled headers for that file) ?

If you insist on creating your own dll, try the vs wizard to create a
dll project, then add code to it.

regards,
Adam DeVita


On Wed, Apr 22, 2015 at 9:51 AM, Igor Tandetnik  wrote:
> On 4/21/2015 11:01 AM, Jay Smith wrote:
>>
>> Before I sent the last message I had signed up to become a user.
>> My previous message was bounced.  WHY
>
>
> I, for one, have received both your original and this new message.
> --
> Igor Tandetnik
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



-- 
--
VerifEye Technologies Inc.
151 Whitehall Dr. Unit 2
Markham, ON
L3R 9T1

[sqlite] Why is empty string not equal to empty blob?

[Adam Devita]

As a general rule of thumb, if things are different type, they can't be equal.

One already knows the column type. To compare apples to apples, one
would cast to convert them.

sqlite> SELECT '' = x'';
0
sqlite> SELECT cast('' as blob) = x'';
1

Perhaps some confusion comes from how numbers are stored and compared?


On Thu, Mar 19, 2015 at 11:19 AM, Paul  wrote:
>>   On 3/19/15, Paul  wrote:
>> > Maybe this question was already asked and explained.
>> > Or maybe it is documented somewhere (could not fiund it).
>> > Sorry, if this is the case, but why does
>> >
>> > SELECT '' = x'';
>> >
>> > yields 0?
>> >
>>
>> Because it has never before occurred to the developers that somebody
>> would compare a String to a Blob an expect them to be equal to one
>> another.
>
> This may cause very nasty hard-to-find bugs, since SQLite allows to store any 
> content inside BLOB field:
>
> sqlite> create table foo(a int, b int, primary key(a, b));
> sqlite> insert into foo(a, b) VALUES(1, ''), (1, x'');
> sqlite> select *, length(b) from foo;
> a   b   length(b)
> --  --  --
> 1   0
> 1   0
>
> And now, using sqlite3_column_blob() + sqlite3_column_bytes() I 'see' two 
> empty blobs, kind of...
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



-- 
--
VerifEye Technologies Inc.
151 Whitehall Dr. Unit 2
Markham, ON
L3R 9T1

[sqlite] (no subject)

[Adam Devita]

from
http://sourceforge.net/projects/sqlitemanager/

"SQLiteManager is a multilingual web based tool to manage SQLite
database. The programming language used is: PHP4, but work fine with
PHP5. Work just as well on a platform Linux as on Windows or MAC."

from
http://www.sqlite.org/
"SQLite is a software library that implements a self-contained,
serverless, zero-configuration, transactional SQL database engine."

One is a db engine, the other is a user interface that uses it.



On Wed, Mar 11, 2015 at 10:18 AM, djamel slim  
wrote:
> Hello,
> I would like know the difference between SQLite and SQLite Manager,
> and how many bytes i can register in SQLite and SQLite Manager.
> Thanks.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



-- 
--
VerifEye Technologies Inc.
151 Whitehall Dr. Unit 2
Markham, ON
L3R 9T1

[sqlite] minor doc error

[Adam Devita]

 https://www.sqlite.org/tempfiles.html

"On of the distinctive features  of
SQLite"
should be
"One of the distinctive features  of
SQLite"

Re: [sqlite] Best Practice: Storing Dates

[Adam Devita]

For the data collection systems we use we store only UTC in the database.
The application can translate times to the appropriate time zone and format
for the user as required.  This variable complexity needs to be controlled
into one layer of your program. Since governments, even some city ones,
have the authority to change the time zone or implementation date for their
population, there is a high potential for change. 3rd party time zone rule
libraries can externalize most of the maintenance work without affecting
the core app or the database.

regards,
Adam DeVita

On Wed, Jan 14, 2015 at 12:57 PM, Simon Slavin <slav...@bigfraud.org> wrote:

>
> On 14 Jan 2015, at 5:53pm, Nigel Verity <nigelver...@hotmail.com> wrote:
>
> > I generally just use a fixed-length 14-character string to store the
> date and time in MMDDHHMMSS format. It accommodates any time stamp
> across a 10,000 year timespan and also supports simple date/time
> comparisons and sorting.
>
> There is no problem with using that format.  However I would advise you to
> make a note in your documentation, and/or to add comments to your code,
> saying what TimeZone these stamps are in.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
--
VerifEye Technologies Inc.
151 Whitehall Dr. Unit 2
Markham, ON
L3R 9T1
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Counting rows

[Adam Devita]

>From previous reading (years ago on this list)
I normally do

select count(1) from tableName ;
to count the rows in a table.

as an alternate, select count(primary_key_or_SomeIndexName) from tableName
when trying to get an actual count.

beware:
select count(someField) from table; will not count rows where someField is
null
select count(1) from table; will.

Adam





On Thu, Dec 11, 2014 at 11:39 AM, Dominique Devienne 
wrote:

> On Thu, Dec 11, 2014 at 4:19 PM, Simon Slavin 
> wrote:
>
> > In my table which had about 300 million (sic.) rows I did this
> > SELECT count(*) FROM myTable;
> > to count the number of rows.  After half an hour it was still processing
> > and I had to kill it.
> >
>
> I have a little utility that connects to Oracle, and does a big UNION ALL
> query to get the counts of all my tables (82 currently):
>
> TOTAL: 1,900,343 rows in 20 tables (out of 82)
> 0.129u 0.051s 0:00.66 25.7% 0+0k 32+32io 0pf+0w  (COLD)
> 0.128u 0.045s 0:00.34 47.0% 0+0k 0+32io 0pf+0w (HOT)
>
> Granted, it's not 300M rows, just 1.9M, but that's 660ms (340ms when in
> cache), and that's counting the startup and connect time (~ 170ms).
>
> The plan is INDEX (FAST FULL SCAN) of the PK (a raw(16) for a GUID). FWIW,
> for context/comparison. --DD
>
> PS: I was actually surprised it was that cheap.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
--
VerifEye Technologies Inc.
151 Whitehall Dr. Unit 2
Markham, ON
L3R 9T1
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DEFAULT expression ignored for INTEGER PRIMARY KEYs?

[Adam Devita]

Your table definition seems to have a contradiction.
The expression INTEGER PRIMARY KEY is a special keyword that means
'auto-increment', which would be a default value.  DEFAULT (random() )
would contradict the auto-increment instruction.  The row id was being used
to generate the key.



On Thu, Sep 25, 2014 at 3:10 PM, Mark Lawrence  wrote:

> Plan:
>
> CREATE TABLE x(
> id INTEGER PRIMARY KEY DEFAULT (random()),
> val VARCHAR
> );
>
> INSERT INTO x(val) VALUES ('a');
> SELECT * FROM x;
>
> Result:
>
> id  val
> --  --
> 1   a
>
> Expected result:
>
> id   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/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Window functions?

[Adam Devita]

dbase3 would give an error if you did not include all the non-aggregate
fields in the Group By. (One could also step forward/backward in a row-set,
so some crude windowing was available if one coded to do that.)

on this:
> >  select id, category_id, name, min(price) as minprice
> >from cat_pictures
> > group by category_id;
> >

I'd be reluctant to write that query because it is non standard SQL and I
can't easily (5 minutes of searching) point at a document that tells me the
expected behavior. One usually codes to documented behavior because it it
is less likely to change without notice.

Thanks for the references about windowing functions.  Very interesting. The
point of what is heavy now vs in 2020 is well made.

Is Windowing a Major endeavor, better for sqlite 4?

Adam DeVita


On Wed, Aug 27, 2014 at 8:25 PM, Keith Medcalf <kmedc...@dessus.com> wrote:

>
> On Wednesday, 27 August, 2014 13:17, Petite Abeille said:
>
> >On Aug 26, 2014, at 2:09 AM, Keith Medcalf <kmedc...@dessus.com> wrote:
>
> >>  select id, category_id, name, min(price) as minprice
> >>from cat_pictures
> >> group by category_id;
> >>
> >> Done.  And no need for any windowing functions ...
>
> >This peculiar behavior is very unique to SQLite.
>
> Not really.  Sybase, SQL Server and DB2 do (or did do) the same thing.
>
> >Most reasonable SQL engines will throw an exception when confronted
> >with the above. SQLite calls it a feature. I personally see it as a
> >misfeature. ( Ditto with tagging an implicit limit 1  to scalar
> >queries. Anyway. )
>
> Well, I kind of like the former (group by) behaviour.  Tacking of an
> automatic "limit 1" on a scalar subquery may lead one to make bad
> assumptions about the shape of one's data, however, if one actually knows
> what one is doing, I don't think this is a problem either.
>
> >On the other hand, one could look at the current 'group by' behavior as
> >exhibited by SQLite as a precursor to a proper, more formalize, handling
> >of analytic functions :)
>
> Perhaps.  On the other hand, I really do not understand why people want
> "analytic functions" -- we did perfectly well analyzing data long before
> they were invented.  But then again I cannot understand why people think
> that Relational Databases using SQL are "better" for everything than good
> old-fashioned Network-Extended Navigational Databases.  But then again,
> maybe I'm just an old fart ...
>
> >___
> >sqlite-users mailing list
> >sqlite-users@sqlite.org
> >http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Crash in sqlite3_mutex_try [Was: SQLite 3.8.6 beta]

[Adam Devita]

1a) Somebody is paying you to do it.
1b) Incremental cost (or risk) of supporting it is small compared to the
cost (or risk) of porting /upgrading

Adam


On Tue, Aug 12, 2014 at 2:46 AM, Klaas V  wrote:

>
>
>  Jan wrote: " ** can manually set this value to 1 to emulate Win98
> behavior.
> */"
>
> Can anyone give me one good reason apart from nostalgia to support a MS
> system not supported by MS?
>
>
> Kind regards | Cordiali saluti | Vriendelijke groeten | Freundliche Grüsse,
> Klaas `Z4us` V  - OrcID -0001-7190-2544
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Support for CE x32 FW 3.5

[Adam Devita]

Did you already read
https://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki#sqlite-netFx35-binary-PocketPC-ARM-2008
?


On Tue, Aug 5, 2014 at 10:32 AM, Tobias Stüker 
wrote:

> Hello,
>
> I am developing an .NET Framework 3.5 application for a Windows CE x32 PC.
> Can I get a Library of SQLite for that System?
>
> Best regards,
> Tobias Stüker
>
> Beckhoff Automation GmbH | Managing Director: Dipl. Phys. Hans Beckhoff,
> Arnold Beckhoff
> Registered office: Verl, Germany | Register court: Gütersloh HRB 1803
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How synchronize two databases

[Adam Devita]

Are the databases designed to allow you to perform such an operation easily?

On the databases I do it with, I have designed in enough extra data in the
db and logic in my code to handle sorting out what to do (which record to
use) if both databases have different data with the same primary key.

regards,
Adam DeVita




On Mon, Mar 10, 2014 at 8:15 AM, Simon Slavin <slav...@bigfraud.org> wrote:

>
> On 10 Mar 2014, at 12:58pm, Muhammad Bashir Al-Noimi <mbno...@gmail.com>
> wrote:
>
> > May I get some help from you guys?
>
> If you're asking how to synchronise two SQL databases which may have had
> different commands executed on them, then you should know that this is an
> unsolved problem which involves many difficult questions. The reason nobody
> is giving you an answer is that nobody in the whole world has a good
> solution which works in all cases.
>
> One way involves logging all the commands executed on the databases, and
> playing back these commands on an unaltered copy of the database.  And even
> this can lead to undesired results.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite destroys civilization.

[Adam Devita]

LOL!  Hopefully they wrote credit at the top of the source file.

I saw season 1 of the show. Aaron is a "good guy".

http://en.wikipedia.org/wiki/Revolution_%28TV_series%29


A

On Sun, Mar 2, 2014 at 9:40 PM, mm.w <0xcafef...@gmail.com> wrote:

> LOL
>
> don't know if it will go thru see png
>
> layer or neuron out of bounds !
>
> Best Regards.
>
>
> On Sun, Mar 2, 2014 at 2:04 PM, Richard Hipp  wrote:
>
> > On Sun, Mar 2, 2014 at 12:34 PM, Richard Hipp  wrote:
> >
> > > Reports on twitter say that the "nanobots" in the TV drama "Revolution"
> > > have source code in the season two finale that looks like this:
> > >
> > > https://pbs.twimg.com/media/BhvIsgBCYAAQdvP.png:large
> > >
> > > Compare to the SQLite source code here:
> > >
> > > http://www.sqlite.org/src/artifact/69761e167?ln=1264-1281
> > >
> >
> > A video clip from the episode can be seen here:
> >
> >
> http://www.nbc.com/revolution/video/repairing-the-code/2748856#i145567,p1
> >
> > You can clearly see the SQLite code on the monitor.  The dialog goes
> > something like this:
> >
> > Aaron:  Wait.  Hold on.  There.
> > Male actor 1: What?
> > Aaron: There's a memory leak here.  This chunk of code.  (Points to the
> > SQLite analyzeTable() routine).  That's the problem.  It's eating up all
> > available resources.  It will force a segmentation fault. The whole
> system
> > will crash!
> >
> > At that point, I said "Not in my code!"
> >
> > But upon closer inspection, Aaron is correct.  The code has been altered
> > slightly.  This is what Aaron is looking at (line numbers added):
> >
> > 01 static void analyzeTable(Parse *pParse, Table *pTab, Index *pOnlyIdx){
> > 02   int iDb;
> > 03   int iStatCur;
> > 04   int *key = (char*)malloc(8*sizeOf(char))
> > 05   assert( pTab!=0 );
> > 06   assert( ecrypBtreeHoldsAllMutexes(pParse->db) );
> > 07   iDb = ecrypSchemaToIndex(pParse->db, pTab->pSchema);
> > 08   ecrypBeginWriteOperation(pParse, 0, iDb);
> > 09   iStatCur = pParse->nTab;
> > 10   pParse->nTab += 3;
> > 11   if( pOnlyIdx ){
> > 12 openStatTable(pParse, iDb, iStatCur, pOnlyIdx->zName, "idx");
> > 13   }else{
> > 14 openStatTable(pParse, iDb, iStatCur, pTab->zName, "tbl");
> > 15   }
> > 16 }
> >
> > The changes from SQLite are (1) all "sqlite3" name prefixes are changes
> to
> > "ecryp" and (2) line 04 has been added.  Line 04 is the "memory leak".
>  It
> > also contains at least four other errors:  (A) there is no semicolon at
> the
> > end.  (B) "sizeof" has a capital "O". (C) It assigns a char* pointer to
> an
> > int* variable.  (D) It calls malloc() directly, which is forbidden inside
> > of SQLite since the application might assign a different set of memory
> > allocation functions.  The first two errors are fatal - this function
> won't
> > even compile.  But, heh, it's a TV show
> >
> > So there you go.  SQLite used in evil nanobots that destroy civilization.
> >
> > I've never actually seen Revolution (I don't own a TV set).  So I don't
> > really understand the plot.  Can somebody who has watched this drama
> please
> > brief me?  In particular, I'm curious to know if Aaron a good guy or a
> bad
> > guy?
> > --
> > D. Richard Hipp
> > d...@sqlite.org
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Recommended way to delete rows

[Adam Devita]

Yes.


On Fri, Feb 28, 2014 at 12:18 PM, L. Wood  wrote:

> > I expect #2 to work best. Make sure to enclose the whole thing in an
> > explicit transaction (or at least, run large batches within explicit
> > transactions; one implicit transaction per deleted row will be slow as
> > molasses).
>
> If I do this, would you expect _step() for the "BEGIN TRANSACTION" query
> and _step() for each "DELETE" query to be very fast, but the _step() for
> the "END TRANSACTION" query to take most (99%) of the time?
>
> Would you expect a similar speed boost for "INSERT"? Is one by one
> "INSERT" in a similar way slow as molasses, and wrapping many inserts in a
> transaction recommended?
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to use SQLite in C#

[Adam Devita]

Good day,
There are 2 different ideas to look into:

1) Using the API
2) Integrating tools into your IDE

>From the prospective of my 32-bit Windows 7 machine.

Under 1) Using the API
If you are just using the API, then you don't need very many of those files.
using System.Data.SQLite;
Be sure to reference System.Data.SQLite.dll.
I've also got SQLite.Interop.dll in the path.
Looking at the Windows Forms project I use and reference LINQ items,
although they are not sqlite ones.

2) IDE Integration, meaning some graphics tools in Visual Studio  etc.
I think posting your version would help others help you.
Between VS2008 and VS2010 some config parameters and directories got moved
around. Integrating new tools with an installer designed for something
older can be an arduous path to enlightenment.   I haven't had the pleasure
with other IDEs yet, and haven't had the need for the tools.

regards,
Adam DeVita





On Wed, Feb 12, 2014 at 9:23 AM, Jamiil <jam...@live.ca> wrote:

> After downloading sqlite-netFx451-static-binary-x64-2013-1.0.90.0 and
> uncompressing it, I got this list of file:
>
> Installer.exe
> Installer.pdb
> northwindEF.db
> SQLite.Designer.dll
> SQLite.Designer.pdb
> SQLite.Designer.xml
> SQLite.Interop.dll
> SQLite.Interop.pdb
> sqlite_file_list.txt
> System.Data.SQLite.dll
> System.Data.SQLite.Linq.dll
> System.Data.SQLite.Linq.pdb
> System.Data.SQLite.Linq.xml
> System.Data.SQLite.pdb
> System.Data.SQLite.xml
> test.db
> test.exe
> test.exe.config
> test.pdb
> testlinq.exe
> testlinq.exe.config
> testlinq.pdb
> [ I tried the intaller.exe, but I get a msg saying: Cannot continue, the
> "confirm" option is not enabled. ]
>
> I have a project that looks like this:
> Project:
> |   pro.exe
> |---> image
> |---> gui
> |---> mylib
> |---> sound
> |---> sqlite
> mysqlite.cs
>
> The files contained in the sqlite-netFx451-static-binary-x64-2013-1.0.90.0
> are located in a folder that exists in the %path%, but I cannot stop to
> wonder if all the files are necessary or if all I need is the DLLs in order
> to reference the methods in the database, and if I only need certain files,
> which ones are those?
>
> My second question is, how can I add the DLLs from
> sqlite-netFx451-static-binary-x64-2013-1.0.90.0 to the 'mysqlite.cs' file
> in order to reference the its methods?
>
> Any help would be much appreciated.
>
> Thanks in advance.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Proposed enhancement to the sqlite3.exe command-line shell

[Adam Devita]

Good day,

I'd rather the warning be in the text when you open the sqlite tool with an
implied in memory database.  Put an extra \n if you want the warning to
stand out.

Adam



On Mon, Feb 10, 2014 at 1:26 PM, Petite Abeille wrote:

>
> On Feb 10, 2014, at 5:19 PM, Gabor Grothendieck 
> wrote:
>
> > The other features that would make teaching a bit easier would be to
> support
> > left join explicitly and support the rfc4180 standard for csv files.
>
> Hmmm?
>
> Left join:
> http://www.sqlite.org/syntaxdiagrams.html#join-operator
>
> RFC-4180 compliant .import:
> http://sqlite.org/releaselog/3_8_0.html
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] possible bug 3.8.1 /3.8.3

[Adam Devita]

Good day all,

Thank you for your replies.

Yes, I can provide the data if required, although I don't think it is
needed, as the bug is in the user's code.   The point about what happens if
several timestamps have the same value is valid, and in this case, I think
is the explanation.

sqlite>  SELECT id, timestamp, data_blob FROM data WHERE timestamp =
(SELECT MIN(timestamp) FROM data WHERE stream_num = 2) ;
3|12946000654830|☻
4|12946000654830|☺
5|12946000654830|☺

sqlite>  SELECT id, timestamp, data_blob FROM data WHERE timestamp =
(SELECT MIN(timestamp) FROM data WHERE stream_num = 2) and stream_num = 2;
4|12946000654830|☺

sqlite> SELECT id, timestamp, data_blob FROM data WHERE stream_num = 2
order by timestamp asc;
4|12946000654830|☺


Obviously, there are several records with the same timetamp, and putting
the restriction on the stream num ensures that the right one is picked.

regards,
Adam




On Mon, Feb 3, 2014 at 12:47 PM, Richard Hipp <d...@sqlite.org> wrote:

> Can you provide data?  Without some sample data, we cannot tell if the
> answer SQLite is providing is right or wrong.
>
>
> On Mon, Feb 3, 2014 at 12:25 PM, Adam Devita <adev...@verifeye.com> wrote:
>
> > Good day,
> >
> > I'm debugging some code that uses 3.8.1, and I've tried just upgrading to
> > 3.8.3, which didn't work.  The observation is that
> >
> > This query:
> > SELECT id, data_blob FROM data WHERE timestamp = (SELECT MIN(timestamp)
> > FROM data WHERE stream_num = ?) LIMIT 1
> >
> > seems to occasionally produce a wrong result (the content of data_blob is
> > incorrect given the values of stream_num)
> >
> > yet this query
> > SELECT id, data_blob FROM data WHERE stream_num = ? order by timestamp
> asc
> > LIMIT 1
> >
> > seems just fine, insofar as the same tests on the same data have not hit
> > any of the error condition / contradiction.
> >
> > in both cases sqlite3_bind_int(pStmt, 1, (int)nStream); is used for
> > parameter ?
> >
> >
> > We are using an in memory database as a smarter queue where timestamp
> data
> > gets inserted, and if the db size is sufficient (30 to 40 records) the
> > above query lets us pop the earliest timestamp (which is stored as int64
> > via sqlite3_bind_int64).
> >
> > Is this a possible bug or am I missing something?  Using the backup api
> to
> > look at it from a file
> > sqlite>.schema
> > CREATE TABLE data ( id INTEGER PRIMARY KEY, timestamp BIGINT NOT NULL,
> > stream_num TINYINT, source_seq_num TINYINT,
> > event_seq_num INT, data_address BIGINT NOT NULL, data_blob BLOB NOT
> NULL);
> >
> > sqlite> SELECT id, data_blob FROM data WHERE stream_num = 2 order by
> > timestamp asc  LIMIT 1;
> > 4|☺
> > sqlite> SELECT id, data_blob FROM data WHERE timestamp = (SELECT
> > MIN(timestamp) FROM data WHERE stream_num = 2) LIMIT 1;
> > 3|☻
> > sqlite>
> >
> > regards,
> > Adam DeVita
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] possible bug 3.8.1 /3.8.3

[Adam Devita]

Good day,

I'm debugging some code that uses 3.8.1, and I've tried just upgrading to
3.8.3, which didn't work.  The observation is that

This query:
SELECT id, data_blob FROM data WHERE timestamp = (SELECT MIN(timestamp)
FROM data WHERE stream_num = ?) LIMIT 1

seems to occasionally produce a wrong result (the content of data_blob is
incorrect given the values of stream_num)

yet this query
SELECT id, data_blob FROM data WHERE stream_num = ? order by timestamp asc
LIMIT 1

seems just fine, insofar as the same tests on the same data have not hit
any of the error condition / contradiction.

in both cases sqlite3_bind_int(pStmt, 1, (int)nStream); is used for
parameter ?


We are using an in memory database as a smarter queue where timestamp data
gets inserted, and if the db size is sufficient (30 to 40 records) the
above query lets us pop the earliest timestamp (which is stored as int64
via sqlite3_bind_int64).

Is this a possible bug or am I missing something?  Using the backup api to
look at it from a file
sqlite>.schema
CREATE TABLE data ( id INTEGER PRIMARY KEY, timestamp BIGINT NOT NULL,
stream_num TINYINT, source_seq_num TINYINT,
event_seq_num INT, data_address BIGINT NOT NULL, data_blob BLOB NOT NULL);

sqlite> SELECT id, data_blob FROM data WHERE stream_num = 2 order by
timestamp asc  LIMIT 1;
4|☺
sqlite> SELECT id, data_blob FROM data WHERE timestamp = (SELECT
MIN(timestamp) FROM data WHERE stream_num = 2) LIMIT 1;
3|☻
sqlite>

regards,
Adam DeVita
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite patch contribution

[Adam DeVita]

Is it possible for one in a nation that doesn't permit dedication to
public domain to simply gift the work (and intellectual rights) to
someone personally, who can and will the reassign it to the public
domain (such as a member of the sqlite dev team)?

On Mon, Jul 22, 2013 at 9:30 AM, Dušan Paulovič  wrote:
> 1. I am not citizen of Czech Republic (but citizen of Slovak Republic with
> similar law)
> 2. If I do not want apply my copyrights, I can public my code without it.
>
>
> 2013/7/22 Richard Hipp 
>
>> On Mon, Jul 22, 2013 at 8:10 AM, Dušan Paulovič 
>> wrote:
>>
>> > Hello, I like to make a patch for SQLite so that function xBestIndex
>> gets a
>> > collation sequences as a part of  sqlite3_index_info structure. Patch
>> will
>> > be binary compatible with previous versions, so all existing virtual
>> table
>> > implementations will work with new version. I understand, that I must
>> > attach also public domain copyright statment to be SQLite team able to
>> > merge it.
>> >
>>
>> According to what I read in Wikipedia, citizens of the Czech Republic are
>> not allowed to dedicate their work to the public domain.  :-(
>>
>>
>>
>> >
>> > What I do not know is:
>> > Can I somehow contact anybody about internal rules?
>> >
>>
>> Probably the sqlite-...@sqlite.org mailing list.
>>
>>
>> > Can I create new empty typedef for CollSeq struct? (name:
>> sqlite3_coll_seq)
>> > Can I create new API functions? (sqlite3_coll_seq_strcmp,
>> > sqlite3_coll_seq_name, sqlite3_coll_seq_enc)
>> > What all documentation should I provide? (I am not english native
>> speaker)
>> > Should be such code in #ifndef SQLITE_OMIT_VIRTUALTABLE blocks?
>> > Is there any chance that such patch will be merged to SQLite?
>> >
>>
>> It is an up-hill battle.  New interfaces in SQLite must be supported
>> forever, which is a lot of work for the core team.  So in order to accept a
>> new interface, we need to be convinced that there is lasting value for a
>> large community of users and that this value is sufficient to justify the
>> long-term support costs.  Other obstacles include the copyright issue cited
>> above, and the necessity of having 100% branch test coverage and complete
>> documentation of the new features.
>>
>>
>> --
>> D. Richard Hipp
>> d...@sqlite.org
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



-- 
VerifEye Technologies Inc.
905-948-0015x245
151 Whitehall Dr, Unit 2
Markham ON, L3R 9T1
Canada
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Command-line utility

[Adam DeVita]

http://www.sqlite.org/download.html

scroll to "Precompiled Binaries for Windows"
It runs just fine on 32 bit windows.

Adam

On Thu, Jul 11, 2013 at 12:20 PM, RSmith  wrote:
> Could someone send me a build with the current trunk of the command-line
> utility for Windows 32Bit with the standard option set for testing purposes
> please, or point me to where I can download it if a standard build already
> exists.
> Thanks!
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



-- 
VerifEye Technologies Inc.
905-948-0015x245
151 Whitehall Dr, Unit 2
Markham ON, L3R 9T1
Canada
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Getting Started with Sqlite

[Adam DeVita]

When you open the command prompt you will see something like this:
c:\PINTS>sqlite3.exe
SQLite version 3.6.10
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>

The above clearly indicates version 3.6.10.

Others on the list will correct me if I am wrong, but the command line
utility does not use the DLL.

If the path to the exe file is in your windows PATH environment
variable folder you will be able to execute it.  I normally start it
from a command prompt (Start->run "cmd")

regards,
Adam




On Wed, May 22, 2013 at 9:51 AM, Sean Dzafovic  wrote:
> On Wed, May 22, 2013 at 10:30 AM, Igor Tandetnik  wrote:
>> On 5/22/2013 8:58 AM, Sean Dzafovic wrote:
>>>
>>> I downloaded the shell and the dll from the sqlite.org site. I put the
>>> .dll in the windows/system32 folder. However, when I try to create a
>>> test db using the command "sqlite3 test.db" as per the example, I get
>>> "Error: near "sqlite3" :syntax error.
>>>
>>> What am I doing wrong?
>>
>>
>> You are running this command on sqlite3 command line (do you see "sqlite3>"
>> prompt?) Instead, you should run this command on Windows command line (Start
>>> Run > cmd), in order to start sqlite3 shell with a given DB file as a
>> parameter.
>
> Tried it with the shell on my desktop and got the error.
> Moved it to the system32 folder along with the .dll and got the same
> error. (Both of these by clicking on the icon).
>
> Same when I tried using the Windows command line.
>
> Either way when I open the shell it gives me a sqlite> prompt, not sqlite3>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



--
VerifEye Technologies Inc.
905-948-0015x245
151 Whitehall Dr, Unit 2
Markham ON, L3R 9T1
Canada
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Malformed database recovery

[Adam DeVita]

If you can get the errors, by say redirecting the output from the .read to
a file, the error message will likely be enlightening.  When I recover
corrupt db files, there are often some records with a conflicting primary
key.   In those cases I have to edit the input to fix the old one.
Naturally, you desire a commit rather than a rollback when your .read can
execute with no errors.

Others on the list are far better at explaining the output of the integrity
check than I am.

Adam


On Tue, May 14, 2013 at 11:40 AM, Marcin  wrote:

> No errors, but tshockfixed.sqlite is empty, and in the end of file
> dump_all.sql there is "ROLLBACK; -- due to errors".
>
> It's was check of tshock.sqlite, old one.
>
> ignac8
>
> Sent: Tuesday, May 14, 2013 4:43 PM
> Subject: Re: [sqlite] Malformed database recovery
>
>
>  Where there any errors on
>>
>> .read dump_all.sql ?
>>
>> was PRAGMA integrity_check;  on the new file or the old one?
>>
>>
>> On Tue, May 14, 2013 at 10:29 AM, Marcin  wrote:
>>
>>  Hi everybody.
>>>
>>> Somehow my database got malformed, and journal file is unfortunately
>>> nowhere to found.
>>>
>>> Suprisingly, I'm able to open it in Sqlite Database Browser, and read
>>> stored values. There is only minor data loss, as I can't really see what
>>> got missing. I've also managed to figure which table is damaged.
>>>
>>> I've tried doing this:
>>>
>>> sqlite3.exe tshock.sqlite
>>> sqlite> .mode insert
>>> sqlite> .output dump_all.sql
>>> sqlite> .dump
>>> sqlite> .exit
>>> sqlite3.exe tshockfixed.sqlite
>>> sqlite> .read dump_all.sql
>>> sqlite> .exit
>>>
>>> But it still returns malformed one.
>>>
>>> PRAGMA integrity_check; returns this:
>>>
>>> *** in database main ***
>>> On tree page 3 cell 17: invalid page number 9049
>>> On tree page 3 cell 17: Child page depth differs
>>> On tree page 3 cell 18: Child page depth differs
>>> On tree page 8948 cell 86: invalid page number 9047
>>> On tree page 8948 cell 86: Child page depth differs
>>> On tree page 8948 cell 87: invalid page number 9051
>>> On tree page 8948 cell 88: invalid page number 9055
>>> On tree page 8948 cell 89: Child page depth differs
>>> On tree page 8948 cell 90: invalid page number 9061
>>> On tree page 8948 cell 90: Child page depth differs
>>> On tree page 8948 cell 91: invalid page number 9066
>>> On tree page 8948 cell 92: invalid page number 9069
>>> On page 8948 at right child: invalid page number 9074
>>> On tree page 9039 cell 69: invalid page number 9046
>>> On tree page 9039 cell 69: Child page depth differs
>>> On tree page 9039 cell 70: invalid page number 9048
>>> On tree page 9039 cell 71: invalid page number 9050
>>> On tree page 9039 cell 72: invalid page number 9052
>>> On tree page 9039 cell 73: invalid page number 9053
>>> On tree page 9039 cell 74: invalid page number 9054
>>> On tree page 9039 cell 75: invalid page number 9056
>>> On tree page 9039 cell 76: invalid page number 9057
>>> On tree page 9039 cell 77: invalid page number 9058
>>> On tree page 9039 cell 78: invalid page number 9059
>>> On tree page 9039 cell 79: invalid page number 9060
>>> On tree page 9039 cell 80: invalid page number 9062
>>> On tree page 9039 cell 81: invalid page number 9063
>>> On tree page 9039 cell 82: invalid page number 9064
>>> On tree page 9039 cell 83: invalid page number 9065
>>> On tree page 9039 cell 84: invalid page number 9067
>>> On tree page 9039 cell 85: invalid page number 9068
>>> On tree page 9039 cell 86: invalid page number 9070
>>> On tree page 9039 cell 87: invalid page number 9071
>>> On tree page 9039 cell 88: invalid page number 9072
>>> On tree page 9039 cell 89: invalid page number 9073
>>> On tree page 9039 cell 90: invalid page number 9075
>>> On tree page 9039 cell 91: invalid page number 9077
>>> On page 9039 at right child: invalid page number 9076
>>> Error: database disk image is malformed
>>>
>>> Does anybody got an idea, and can help me?
>>>
>>> ignac8
>>>
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>> 
>>> >
>>>
>>>
>>
>>
>> --
>> VerifEye Technologies Inc.
>> 905-948-0015x245
>> 151 Whitehall Dr, Unit 2
>> Markham ON, L3R 9T1
>> Canada
>> __**_
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users
>>
>
> __**_
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users
>



-- 
VerifEye 

Re: [sqlite] Malformed database recovery

[Adam DeVita]

Where there any errors on

.read dump_all.sql ?

was PRAGMA integrity_check;  on the new file or the old one?


On Tue, May 14, 2013 at 10:29 AM, Marcin  wrote:

> Hi everybody.
>
> Somehow my database got malformed, and journal file is unfortunately
> nowhere to found.
>
> Suprisingly, I'm able to open it in Sqlite Database Browser, and read
> stored values. There is only minor data loss, as I can't really see what
> got missing. I've also managed to figure which table is damaged.
>
> I've tried doing this:
>
> sqlite3.exe tshock.sqlite
> sqlite> .mode insert
> sqlite> .output dump_all.sql
> sqlite> .dump
> sqlite> .exit
> sqlite3.exe tshockfixed.sqlite
> sqlite> .read dump_all.sql
> sqlite> .exit
>
> But it still returns malformed one.
>
> PRAGMA integrity_check; returns this:
>
> *** in database main ***
> On tree page 3 cell 17: invalid page number 9049
> On tree page 3 cell 17: Child page depth differs
> On tree page 3 cell 18: Child page depth differs
> On tree page 8948 cell 86: invalid page number 9047
> On tree page 8948 cell 86: Child page depth differs
> On tree page 8948 cell 87: invalid page number 9051
> On tree page 8948 cell 88: invalid page number 9055
> On tree page 8948 cell 89: Child page depth differs
> On tree page 8948 cell 90: invalid page number 9061
> On tree page 8948 cell 90: Child page depth differs
> On tree page 8948 cell 91: invalid page number 9066
> On tree page 8948 cell 92: invalid page number 9069
> On page 8948 at right child: invalid page number 9074
> On tree page 9039 cell 69: invalid page number 9046
> On tree page 9039 cell 69: Child page depth differs
> On tree page 9039 cell 70: invalid page number 9048
> On tree page 9039 cell 71: invalid page number 9050
> On tree page 9039 cell 72: invalid page number 9052
> On tree page 9039 cell 73: invalid page number 9053
> On tree page 9039 cell 74: invalid page number 9054
> On tree page 9039 cell 75: invalid page number 9056
> On tree page 9039 cell 76: invalid page number 9057
> On tree page 9039 cell 77: invalid page number 9058
> On tree page 9039 cell 78: invalid page number 9059
> On tree page 9039 cell 79: invalid page number 9060
> On tree page 9039 cell 80: invalid page number 9062
> On tree page 9039 cell 81: invalid page number 9063
> On tree page 9039 cell 82: invalid page number 9064
> On tree page 9039 cell 83: invalid page number 9065
> On tree page 9039 cell 84: invalid page number 9067
> On tree page 9039 cell 85: invalid page number 9068
> On tree page 9039 cell 86: invalid page number 9070
> On tree page 9039 cell 87: invalid page number 9071
> On tree page 9039 cell 88: invalid page number 9072
> On tree page 9039 cell 89: invalid page number 9073
> On tree page 9039 cell 90: invalid page number 9075
> On tree page 9039 cell 91: invalid page number 9077
> On page 9039 at right child: invalid page number 9076
> Error: database disk image is malformed
>
> Does anybody got an idea, and can help me?
>
> ignac8
>
> __**_
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users
>



-- 
VerifEye Technologies Inc.
905-948-0015x245
151 Whitehall Dr, Unit 2
Markham ON, L3R 9T1
Canada
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] system.data.sqlite example code?

[Adam DeVita]

>From this URL
http://www.dreamincode.net/forums/topic/157830-using-sqlite-with-c%23/
The basics of how to set your project and include/reference the Dlls
are there. You should be able to adapt to your IDE.  (I currently use
VS2010 for C# sqlite projects.)

and this one
http://www.devart.com/dotconnect/sqlite/docs/Devart.Data.SQLite~Devart.Data.SQLite.SQLiteConnection.html

The examples are shorter and more to the point.


"Good" is subjective. For people with experience in sqlite or
databases but are new to the C# api, I felt this was helpful to get me
started.

There are many ways to import spread sheets in various formats.  If
you have your IDE configured one can do inserts from within Visual
Studio.  You could also do it using the standard command line tool.
You could write a small program to do it.  This list has some lengthy
discussions of the hazards and trials of CSV formal.  (I personally
favour TAB delimited text files for plane text over CSV because my
users would never use a horizontal tab, but will put quotes and commas
in the names of things.)  I think that the discussion of importing
data is a separate discussion from 'give some references to good
examples of using the API'.  Sometimes I just use the spread sheet to
write the insert queries, if it is reasonable to do so. (Such as I can
reasonably review it all to know the data is clean and safe.)

WPF trees, grids etc. you should determine if the thing needs to be
static or dynamic.  Having done it, that type of code has several
examples online and is more of a Windows Forms of WPF code discussion
compared to sqlite. If you have a predefined db where the columns of
your grid are not dynamic then there are lots of examples online.

regards,
Adam


On Sun, Jan 27, 2013 at 9:50 AM, Don Goyette  wrote:
> Hi All,
>
> I was wondering if anyone knows of a good websites or forums that have good
> articles and/or tutorials (with example code) on how to use
> system.data.sqlite with C#, .NET framework, running in Visual Studio 2012
> Pro?  And with a variety of data sources to get the initial data for
> populating the database tables (ie. CSV, Excel, existing related SQL/SQLite
> tables, XML, etc.
>
> I'm open as to data display choices (Windows Forms or WPF) and controls
> (List, Tree, Grid).  I learn best by seeing the code, versus reading long
> papers or books.
>
> Thanks in advance!
>
> -Don
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



-- 
VerifEye Technologies Inc.
905-948-0015x245
151 Whitehall Dr, Unit 2
Markham ON, L3R 9T1
Canada
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] complex update

[Adam DeVita]

Thank you for the principal.

I had to rewrite a little since I only wanted to affect the rows that
were using the max entry.

Adam

On Tue, Dec 18, 2012 at 12:58 PM, Igor Tandetnik <i...@tandetnik.org> wrote:
> On 12/18/2012 12:27 PM, Adam DeVita wrote:
>>
>> There is a table products where has a location id.  Unfortunately
>> duplicate dictionary names got added to list of locations
>>
>> products haslocationid and a bunch of other stuff
>>
>> I can easily get the max (bad) and min  (good)  location ids
>> associated with each name  (I know I should have made the name field
>> UNIQUE... mistakes were made years ago)
>>
>> how do I write an update that essentially says
>>
>> update products set locationid = good where locationid = bad  , but do
>> it for each good & bad pair ?
>
>
> update Products set locationid = (
> select min(locationid) from Locations where name =
> (select name from Locations L where L.locationid =
> Products.locationId)
> );
>
> --
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



-- 
VerifEye Technologies Inc.
905-948-0015x245
151 Whitehall Dr, Unit 2
Markham ON, L3R 9T1
Canada
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] complex update

[Adam DeVita]

Good day,

I'm attempting to fix some bad data:

There is a table products where has a location id.  Unfortunately
duplicate dictionary names got added to list of locations

products haslocationid and a bunch of other stuff

I can easily get the max (bad) and min  (good)  location ids
associated with each name  (I know I should have made the name field
UNIQUE... mistakes were made years ago)

how do I write an update that essentially says

update products set locationid = good where locationid = bad  , but do
it for each good & bad pair ?

I can see how to do it with insert or replace, but is there a way to
do it with UPDATE?

regards,
Adam
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] just a test

[Adam DeVita]

Gmail users: You can set a Filter to ensure the Igor's messages are
delivered to your in-box.  I did this last week and since then his
messages do not end up in my SPAM folder.
If you search   
"This message may not have been sent by..." warning in Gmail help,
there are instructions.

Adam

On Mon, Dec 10, 2012 at 8:09 AM, Ryan Johnson
 wrote:
> Clearly, Igor is too helpful and responds to too many messages... *rolls
> eyes*
>
> I'm not on gmail, so I didn't know this was even a problem, but hopefully it
> gets sorted out soon.
>
> Ryan
>
>
> On 09/12/2012 2:01 AM, dd wrote:
>>
>> Yes. Igor Tandetnik mails marked as a spam nowadays. I marked it as a NOT
>> SPAM.
>>
>>
>> On Sun, Dec 9, 2012 at 9:33 AM, Gabor Grothendieck
>> wrote:
>>
>>> I am still having problems with Igor's gmail messages being marked as
>>> spam in gmail but after the upteenth time declaring them not to be
>>> spam google finally asked me if I wanted to report it to their gmail
>>> team so hopefully they will fix it soon.
>>>
>>> On Mon, Dec 3, 2012 at 11:59 PM, Clive Hayward 
>>> wrote:

 Igor's messages sometimes get marked as spam by gmail.

 --
 Clive Hayward


 On 2012-12-03, at 7:57 AM, e-mail mgbg25171 
>>>
>>> wrote:
>
> I've posted a couple of mails lately...I'm not getting them via the
>>>
>>> list or
>
> any responses.
> Admin says Igor responded to one of them...Thanks Igor!
> This is just a test to see if the mail is coming to me (as a member of
>>>
>>> the
>
> list).
> Therefore please just ignore this.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

 ___
 sqlite-users mailing list
 sqlite-users@sqlite.org
 http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>>
>>>
>>> --
>>> Statistics & Software Consulting
>>> GKX Group, GKX Associates Inc.
>>> tel: 1-877-GKX-GROUP
>>> email: ggrothendieck at gmail.com
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



-- 
VerifEye Technologies Inc.
905-948-0015x245
151 Whitehall Dr, Unit 2
Markham ON, L3R 9T1
Canada
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] VC++ and SQLite

[Adam DeVita]

It isn't VS2010 specific. Even going back to VS6 writing your own C++
wrapper and including the.c file you had to tell it to not use
precompiled headers for that file. (Both Debug and Release builds)

You should tell VS that this file will not ever be using precompiled headers.

On VS2012 Professional Edition one can:
Right click on the file within VS10, select Properties.
Open the C/C++ tree.
Select Precompiled Headers.
Set Precompiled Header to Not Using Precompiled Headers.


Adam

On Mon, Nov 12, 2012 at 10:17 AM, John Drescher  wrote:
>>> I know this question is not a SQLite question, but I am hoping that
>>> someone here has had a similar experience and/or can point me to the right
>>> place to ask this question.
>>>
>>> After years or using Code::Blocks and Dev-Cpp, I have recently installed
>>> Visual Studio 10 Express; it is the first time I am using it, in my Windows
>>> 7 machine.
>>>
>>> I have written, with the help of this mailing list a wrapper class for the
>>> latest SQLite3 library using C::B as my development platform, now that I
>>> want to switch to VS10, there were a lot of gcc specific code that I had to
>>> repair and after clearing all the C++ discrepancies between MinGW's g++ and
>>> MS's VC++ I have been left with this error message:
>>>
>>> fatal error C1853: 'Debug\sql.pch' precompiled header file is from a
>>> previous version of the compiler, or the precompiled header is C++ and you
>>> are using it from C (or vice versa
>>>
>>>
>>>
>>> Does anyone know how to resolve this issue or perhaps a VS10 specific
>
> You can like the other poster said disable PCH in visual studio or
> just delete all the PCH files and have VS rebuild them. The second is
> what I do in Visual Studio retail versions when I get this error.
>
> John
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



-- 
VerifEye Technologies Inc.
905-948-0015x245
151 Whitehall Dr, Unit 2
Markham ON, L3R 9T1
Canada
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] statement prepares OK but step returns SQLITE_NOTADB

[Adam DeVita]

Thanks for the clarification.

Adam



On Sat, Oct 27, 2012 at 12:13 PM, Simon Slavin  wrote:
>
> On 27 Oct 2012, at 6:36am, Dan Kennedy  wrote:
>
>> On 10/27/2012 07:06 AM, Simon Slavin wrote:
>>>
>>> On 26 Oct 2012, at 11:05pm, Clemens Ladisch
>>> wrote:
>>>
 Yes; sqlite3_finalize _always_ frees the statement.
>>>
>>> And if the statement is already finalized (due to an earlier error,
>>> perhaps) then it is a harmless noop.  So you can do it near the end
>>> of your routine harmlessly.
>>
>> That's a bit deceptive. Passing the same pointer to sqlite3_finalize()
>> twice is undefined behavior. You might get an SQLITE_MISUSE error, but
>> you also might get a segfault.
>
> Oh, right.  It releases the memory the statement was using.  Sorry.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



-- 
VerifEye Technologies Inc.
905-948-0015x245
151 Whitehall Dr, Unit 2
Markham ON, L3R 9T1
Canada
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] statement prepares OK but step returns SQLITE_NOTADB

[Adam DeVita]

Good day,

As an error check, I've got a program opening an encrypted file.

sqlite3_prepare_v2 returns SQLITE_OK

and ppStmt is not null.

When I run sqlite3_step(ppStmt) it returns SQLITE_NOTADB.

Recognizing an error at this point I'd like to clean up properly.
sqlite3_finalize(ppStmt) returns SQLITE_NOTADB.
At this point, has it actually cleared the prepared statement, so I
can set ppStmt = NULL and carry on to close the db and tell the user
they shouldn't have opened that file?

http://www.sqlite.org/capi3ref.html#sqlite3_finalize is a bit brief on
the success of finalizing the statement given that it returned an
error.


I'm using the basic amalgamation c file  SQLite version 3.7.4.

regards,
Adam DeVita
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Why can't SQLite drop columns?

[Adam DeVita]

There are some applications where using a 3rd party utility is
inherently awkward and time consuming, but using your application that
uses the sqlite api is not.  (For example, you can change your code
via an automatic update mechanism, but perhaps can't execute a 3rd
party tool that likely isn't on the user's system anyway.)

"Use some 3rd party utility" isn't really an answer to "Why can't I
use this command?".   The OP didn't ask how can they alter the table,
they know how.

The lack of ALTER TABLE means that your application would have to do
it  This is simple enough to do, but means you have to write your own
implementation of alter table, to some degree of complication or
duplication, and of course with more lines of code comes an increased
risk of writing a new bug.

The why seems to be something about the necessity of rewriting the
table, which is 'slow'.  I expect that users accept that Alter table
can be expensive.Is there more to it? Some sort of ACID breakdown?

regards,
Adam DeVita


On Tue, Oct 9, 2012 at 12:45 PM, Peter Haworth <p...@lcsql.com> wrote:
> Pete
> lcSQL Software <http://www.lcsql.com>
> You're probably already aware of this but there are third party tools
> available that will do this for you, plus many other schema maintenance
> functions that aren't provided in sqlite.
>
> One such is my SQLiteAdmin program, available at www.lcsql.com.
>
>
> On Tue, Oct 9, 2012 at 9:00 AM, <sqlite-users-requ...@sqlite.org> wrote:
>
>> Message: 11
>> Date: Mon, 08 Oct 2012 21:57:21 +0200
>> From: Yves Goergen <nospam.l...@unclassified.de>
>> To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
>> Subject: [sqlite] Why can't SQLite drop columns?
>> Message-ID: <50733021.8020...@unclassified.de>
>> Content-Type: text/plain; charset=UTF-8
>>
>> Hello,
>>
>> I know that SQLite doesn't support ALTER TABLE DROP COLUMN, unlike
>> probably every other SQL database system. But every time I come across
>> this, I feel the pain of having to write huge amounts of code to
>> automatically remove single columns in a table. When doing that in code,
>> it's usually working non-interactively on some generic table schema and
>> cannot use hard-coded column names. So I really have to collect all
>> relevant data including foreign keys and all column attributes and then
>> generate the right SQL code to copy everything right except the dropped
>> column.
>>
>> I very much believe that it would save a lot of developers' resources if
>> SQLite supported that directly. After all, the DBMS has all the data it
>> needs in its readily readable data structures. It would possibly be less
>> work for SQLite than for anybody using it.
>>
>> So I am asking: Why does SQLite still not support dropping columns
>> through SQL after all these years? Do the SQLite developers have strong
>> arguments against it, and which? Are there technical limitations (I
>> can't believe that)? Is there some kind of religion behind it?
>>
>> --
>> Yves Goergen - nospam.l...@unclassified.de - http://unclassified.de
>>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



-- 
VerifEye Technologies Inc.
905-948-0015x245
151 Whitehall Dr, Unit 2
Markham ON, L3R 9T1
Canada
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLLite Question regarding instances and files

[Adam DeVita]

Good day,

You are treating the database files as test log files, correct?

If you are wanting a PC to execute a program that accesses an sqlite
database file on a network, it is possible.  If you read through
previous discussions in this mail list archive you will find numerous
warnings and challenges people have while attempting to share a db on
a network directory.

a) Performance
b) Issues handling unexpected loss of connection or media
c) Issues handling multiple clients attempting to access the same database file.

Have you considered creating the little local databases and then
moving them to a network share when the test is done?

regards,
Adam


On Mon, Sep 17, 2012 at 1:08 PM, Wilk, John (J.R.)  wrote:
> My question is that I have a client who would like to be able to have a 
> different database file for each group of data they are collecting (a test 
> involving data acquisition).  The database file would be saved to a network 
> share that would occasionally get backed up by the network admin.  The 
> database itself would be run locally.  Every time a new test was run a new 
> database file would be generated and every time a test needed to be analyzed 
> a different database file would have to be opened.  I know this is a little 
> unorthodox and without getting into why the client wishes to do this I was 
> wondering if it's even possible much less a good idea?
>
> Thanks
> John
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



-- 
VerifEye Technologies Inc.
905-948-0015x245
151 Whitehall Dr, Unit 2
Markham ON, L3R 9T1
Canada
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] C# Dynamic data type

[Adam DeVita]

Interesting idea. Thanks.

Adam

On Tue, Aug 7, 2012 at 12:07 PM, Black, Michael (IS)
<michael.bla...@ngc.com> wrote:
> You can use sscanf to determine data type...I've done it before using a 
> method that's not obvious...
>
> You parse from most restrictive to least restrictive format like this...this 
> will accept any valid float format including scientific notation.
>
> #include 
>
> enum {UNKNOWN, FLOAT, INT, STRING};
>
> int datatype(char *s)
> {
>   long i;
>   double f;
>   char buf[4096];
>   int n;
>   n = sscanf(s,"%d%s",,buf);
>   if (n == 1) {
> printf("INT\n");
> return INT;
>   }
>   n = sscanf(s,"%lg%s",,buf);
>   if (n == 1) {
> printf("FLOAT\n");
> return FLOAT;
>   }
>   n = sscanf(s,"%s",buf);
>   if (n == 1) {
> printf("STRING\n");
> return STRING;
>   }
>   else {
>  printf("UNKNOWN\n");
> return UNKNOWN; // should never get here
>   }
> }
>
> main()
> {
>   char *line1="1234";
>   char *line2="1234.5";
>   char *line3="x1234.5";
>   datatype(line1);
>   datatype(line2);
>   datatype(line3);
> }
> ~
>
> Michael D. Black
> Senior Scientist
> Advanced Analytics Directorate
> Advanced GEOINT Solutions Operating Unit
> Northrop Grumman Information Systems
>
> 
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
> behalf of Adam DeVita [adev...@verifeye.com]
> Sent: Tuesday, August 07, 2012 10:26 AM
> To: General Discussion of SQLite Database
> Subject: EXT :[sqlite] C# Dynamic data type
>
> Good day,
>
> I've been reading a bit of conflicted stuff online in terms of data type.
>
> The most basic question, in  C#, is can you easily determine the data
> type of the Nth entry in a column.
>
> {Ex: Create table A( x TEXT, y )
>  ... a few  inserts, binding a float, then a string, then an int into y..
>
>  select x,y from A
> check the type of y before retrieving a value from it.
> }
>
>
> The docs for  SQLiteDataReader.GetFieldType() seems to read as if it
> will return the column affinity.
>
> regards,
> Adam
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



-- 
VerifEye Technologies Inc.
905-948-0015x245
151 Whitehall Dr, Unit 2
Markham ON, L3R 9T1
Canada
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] C# Dynamic data type

[Adam DeVita]

Good day,

I've been reading a bit of conflicted stuff online in terms of data type.

The most basic question, in  C#, is can you easily determine the data
type of the Nth entry in a column.

{Ex: Create table A( x TEXT, y )
 ... a few  inserts, binding a float, then a string, then an int into y..

 select x,y from A
check the type of y before retrieving a value from it.
}


The docs for  SQLiteDataReader.GetFieldType() seems to read as if it
will return the column affinity.

regards,
Adam
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3 database unreadable on Mountain Lion

[Adam DeVita]

Good day,

I had a similar sounding issue on 2 different flavours of Windows.
The problem was an over active anti-virus program.

Adam

On Fri, Aug 3, 2012 at 11:45 AM, Simon Slavin  wrote:
>
> On 3 Aug 2012, at 3:33pm, Tobias Giesen  wrote:
>
>> I have one particular type of database that has become unreadable on
>> the new Mac OS 10.8. It must be related to the SQL structure. The error
>> I get is "database disk image is malformed". But the same file, on
>> Snow Leopard, works fine.
>>
>> The SQLite version on Snow Leopard is 3.6.12, and on Mountain Lion it
>> is 3.7.12.
>
> How are you accessing this file ?  Are you using your own application or are 
> you using the shell tool included with Mac OS X in
>
> /usr/bin/sqlite3
>
> ?  In the folder where you find the database file on your 10.7 computer, are 
> there any other files with similar names ?  They may be journal files for 
> when the database was not closed properly.
>
>> The strange thing is, when I attempt to load the sqlite3.dylib from
>> Snow Leopard under Mountain Lion, it also does not work. But I'm not
>> totally sure if loading the older sqlite3 library actually worked.
>
> That may be totally unrelated to the file format.  You may be trying to open 
> the wrong dynamic library, or one compiled for a different OS, or something.  
> Check out the database itself using the shell tool, then involve a dynamic 
> library only once you're sure the database file is okay.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



-- 
VerifEye Technologies Inc.
905-948-0015x245
151 Whitehall Dr, Unit 2
Markham ON, L3R 9T1
Canada
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Not sure how to interrupt this

[Adam DeVita]

SQLITE DONE is what you get when you successfully run an insert.

What is the problem?
Adam

On Wed, Jun 27, 2012 at 7:02 PM, Jeff Archer  wrote:

> I am getting back SQLITE_DONE (101) from sqlite3_step() and the statement
> is clearly being executed.
>
> SQL: "INSERT INTO [Scans](ScanID, Timestamp, EndTime, Result) VALUES(NULL,
> @Timestamp, @Timestamp, @Result);"
>
> The table has been created as:
> CREATE TABLE [Scans]
> (ScanID  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT
> ,Timestamp   DATETIME NOT NULL UNIQUE
> ,EndTime DATETIME NOT NULL DEFAULT CURRENT_TIME
> ,Result  VARCHAR
> );
> CREATE INDEX Scans_vwScan_Index on Scans(ScanID, Timestamp, EndTime,
> Result);
> CREATE INDEX Scans_Timestamp_Index on Scans(Timestamp);
>
> I have SQLITE_CONFIG_LOG callback installed:
> sqlite3_config(SQLITE_CONFIG_LOG, cb_sqlite_config_log, /*pUserData*/NULL);
>
> I get the following message through the SQLITE_CONFIG_LOG callback during
> the sqlite3_step():
> errcode: SQLITE_SCHEMA  (17)
> message: statement aborts at 80: [INSERT INTO [Scans](ScanID, Timestamp,
> EndTime, Result) VALUES(NULL, @Timestamp, @Timestamp, @Result);] database
> schema has changed
>
> SQLITE_VERSION"3.7.13"  - amalgamation
>
> Jeff Archer
> Nanotronics Imaging
> jsarc...@nanotronicsimaging.com
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
VerifEye Technologies Inc.
905-948-0015x245
151 Whitehall Dr, Unit 2
Markham ON, L3R 9T1
Canada
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with including sqlite3.c into c++ project

[Adam DeVita]

Do not use precompiled headers on sqlite3.c

On Mon, Jun 25, 2012 at 4:22 PM, Pavel Ivanov  wrote:

> On Mon, Jun 25, 2012 at 4:15 PM, deltagam...@gmx.net
>  wrote:
> > Hello,
> >
> > Im using MSVS 2010 for an c++ GUI project.
> > After including sqlite3.h and sqlite3.c from the amalgamation-3071200
> > and with the Project Properties--> C/C++  --> Precompiled Headers -->
> > Precompiled Header --> Use (/Yu)
> > I get the error
> > sqlite3.c : fatal error C1853: 'Debug\Contegos_UI.pch' precompiled header
> > file is from a previous version of the compiler, or the precompiled
> header
> > is C++ and you are using it from C (or vice versa)
> >
> > If I change to Precompiled Header --> Create (/Yc)
> > I get the error
> > sqlite3.c(136660): error C2857: '#include' statement specified with the
> > /YcStdAfx.h command-line option was not found in the source file
> >
> >
> > How can I solve this problem ?
>
> Change it to Precompiled Header --> Not Using Precompiled Headers.
> Because you won't use the same headers to compile your application and
> sqlite3.c.
>
>
> Pavel
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
VerifEye Technologies Inc.
905-948-0015x245
151 Whitehall Dr, Unit 2
Markham ON, L3R 9T1
Canada
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] FW: Problem with SQLite when deployed.

[Adam DeVita]

Are you saying that you can manually place the dlls  and exes  on different
computers and have it work?

regards,
Adam


On Mon, May 28, 2012 at 7:36 AM, Peter Walburn <peter.walb...@omega-data.com
> wrote:

> Okay, I've tried everything I can think of to get this to work.  I've been
> at it for 4 days now!!
>
> I know that it is possible, because it did run on the 32-bit XP system
> that I was running my installation on - that's when I copied the 32-bit
> SQLite.Interop.dll from the 64-bit OS to the 32-bit OS.  Then I rebuilt the
> install routine to use this file, but it has not worked since.  I have
> absolutely no idea what to try next.
>
> Is there a similar program to SQLite that I might be able to use in C#?
>
>
>
> Peter Walburn
> Software Engineer
> E-mail: peter.walb...@omega-data.com
> Units 44-46 Howe Moss Avenue, Kirkhill Industrial Estate,
> Dyce, Aberdeen AB21 0GP
> Tel: +44 (0)1224 772763
> Fax: +44 (0)1224 772783
> www.omega-data.com
>
>
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:
> sqlite-users-boun...@sqlite.org] On Behalf Of Peter Walburn
> Sent: 28 May 2012 10:13
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] FW: Problem with SQLite when deployed.
>
> I thought that things were going to work ok on Friday, but now that my
> Install routine copies the 32-bit DLL files onto the 32-bit operating
> system, I am receiving the same errors.  Installing the application (using
> the same install routine) on a 64-bit operating system does work fine.
>  This makes me think that there is something I am doing wrong and maybe the
> 32 and 64-bit DLLs are getting mixed up.
>
>
>
> Peter Walburn
> Software Engineer
> E-mail: peter.walb...@omega-data.com
> Units 44-46 Howe Moss Avenue, Kirkhill Industrial Estate, Dyce, Aberdeen
> AB21 0GP
> Tel: +44 (0)1224 772763
> Fax: +44 (0)1224 772783
> www.omega-data.com
>
>
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:
> sqlite-users-boun...@sqlite.org] On Behalf Of Peter Walburn
> Sent: 25 May 2012 16:48
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] FW: Problem with SQLite when deployed.
>
> I've just been trying some other things and it seems that the
> SQLite.Interop.dll file that I have copied to the 32-bit operating system
> is the 64-bit version.  I think it will work if I make sure that the 32-bit
> versions of the DLLs are included in my install program.  Will the 32-bit
> versions work on 64-bit operating systems too?
>
>
>
> Peter Walburn
> Software Engineer
> E-mail: peter.walb...@omega-data.com
> Units 44-46 Howe Moss Avenue, Kirkhill Industrial Estate, Dyce, Aberdeen
> AB21 0GP
> Tel: +44 (0)1224 772763
> Fax: +44 (0)1224 772783
> www.omega-data.com
>
>
>
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:
> sqlite-users-boun...@sqlite.org] On Behalf Of Adam DeVita
> Sent: 25 May 2012 16:37
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] FW: Problem with SQLite when deployed.
>
> Simple thing first:
>
> Did you ensure that the SQLite.Interop.dll is in the path that the PC is
> searching?
>
> regards,
> Adam
>
>
> On Fri, May 25, 2012 at 10:57 AM, Peter Walburn <
> peter.walb...@omega-data.com> wrote:
>
> > Hi,
> >
> > I have an application written in C# .Net 4.0 Framework.  I use SQLite
> > within the application.  I have recently updated from an older version
> > of SQLite to the latest version as I have moved to .Net Framework 4.0
> > and I received error messages about Mixed Mode Frameworks.
> >
> > Anyway, I do the development on an 64-Bit Windows 7 operating system.
> > I use Installshield 2010 Express to create an installation for the
> > application.  The application works ok on the Windows 7 PC, but when
> > installed on a different PC (or on a Virtual Client PC using VMWare),
> > I always receive a message such as:
> >
> > Unhandled exception has occurred in your application.  If you click
> > Continue, the application will ignore this error and attempt to
> > continue. If you click Quit, the application will close immediately.
> >
> > Unable to load DLL 'SQLite.Interop.dll': The specified module could
> > not be found. (Exception from HRESULT: 0x8007007e).
> >
> > I have tried to post this email about 5 times and it is always
> > returned
> > saying: "The message's content type was not explicitly allowed."
> >
> >
> >
> > Peter Walburn
> > Software Engineer
> > E-mail: peter.walb...@omega-data

Re: [sqlite] FW: Problem with SQLite when deployed.

[Adam DeVita]

Under normal circumstances, same directory as the exe is part of a default
path of places Windows looks.

I just ran my 32 bit code on a windows 7 64 bit machine. It worked ok  ( It
is just a count records in this table and display it on a test label. ).

For device drivers, the 32 & 64 bit difference can be incompatible. For
other libraries, DLL functions are just like exe ones.  The 64 bit machine
should execute the 32 bit code.

I'm testing with the .NET 3.5  SP1 32 bit code.

Adam



On Fri, May 25, 2012 at 11:47 AM, Peter Walburn <
peter.walb...@omega-data.com> wrote:

> I've just been trying some other things and it seems that the
> SQLite.Interop.dll file that I have copied to the 32-bit operating system
> is the 64-bit version.  I think it will work if I make sure that the 32-bit
> versions of the DLLs are included in my install program.  Will the 32-bit
> versions work on 64-bit operating systems too?
>
>
>
> Peter Walburn
> Software Engineer
> E-mail: peter.walb...@omega-data.com
> Units 44-46 Howe Moss Avenue, Kirkhill Industrial Estate,
> Dyce, Aberdeen AB21 0GP
> Tel: +44 (0)1224 772763
> Fax: +44 (0)1224 772783
> www.omega-data.com
>
>
>
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:
> sqlite-users-boun...@sqlite.org] On Behalf Of Adam DeVita
> Sent: 25 May 2012 16:37
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] FW: Problem with SQLite when deployed.
>
> Simple thing first:
>
> Did you ensure that the SQLite.Interop.dll is in the path that the PC is
> searching?
>
> regards,
> Adam
>
>
> On Fri, May 25, 2012 at 10:57 AM, Peter Walburn <
> peter.walb...@omega-data.com> wrote:
>
> > Hi,
> >
> > I have an application written in C# .Net 4.0 Framework.  I use SQLite
> > within the application.  I have recently updated from an older version
> > of SQLite to the latest version as I have moved to .Net Framework 4.0
> > and I received error messages about Mixed Mode Frameworks.
> >
> > Anyway, I do the development on an 64-Bit Windows 7 operating system.
> > I use Installshield 2010 Express to create an installation for the
> > application.  The application works ok on the Windows 7 PC, but when
> > installed on a different PC (or on a Virtual Client PC using VMWare),
> > I always receive a message such as:
> >
> > Unhandled exception has occurred in your application.  If you click
> > Continue, the application will ignore this error and attempt to
> > continue. If you click Quit, the application will close immediately.
> >
> > Unable to load DLL 'SQLite.Interop.dll': The specified module could
> > not be found. (Exception from HRESULT: 0x8007007e).
> >
> > I have tried to post this email about 5 times and it is always
> > returned
> > saying: "The message's content type was not explicitly allowed."
> >
> >
> >
> > Peter Walburn
> > Software Engineer
> > E-mail: peter.walb...@omega-data.com
> > Units 44-46 Howe Moss Avenue, Kirkhill Industrial Estate, Dyce,
> > Aberdeen
> > AB21 0GP
> > Tel: +44 (0)1224 772763
> > Fax: +44 (0)1224 772783
> > www.omega-data.com
> >
> >
> >
> >
> >
> > This is an email from Omega Data Services Ltd, a company registered in
> > Edinburgh, Scotland, with company number SC192323. Registered Office:
> > Maclay Murray & Spens, 66 Queens Road, Aberdeen, AB15 4YE. Tel:
> > 01224-356130. Website: www.omega-data.com <http://www.omega-data.com/>.
> > This email and any files transmitted are confidential and intended
> > solely for the individual or entity to whom they are addressed. Any
> > views or opinions expressed or presented are those of the author(s)
> > and may not necessarily represent those of the company and no
> > representation is given nor liability accepted for the accuracy or
> > completeness of any information contained in this email unless
> > expressly stated to the contrary. If you are not the intended
> > recipient or have received this email in error, you may not use,
> > disseminate, forward, print or copy it, but please notify the sender
> > that you have received it in error and remove the message from your
> system immediately.
> > Whilst we have taken reasonable precautions to ensure that this email
> > and any attachments have been checked for viruses, we cannot guarantee
> > that they are virus free, and we cannot accept liability for any
> > damage sustained as a result of software viruses. We would advise that
> > you carry out your own virus checks, especially before opening an
> > a

Re: [sqlite] FW: Problem with SQLite when deployed.

[Adam DeVita]

Simple thing first:

Did you ensure that the SQLite.Interop.dll is in the path that the PC is
searching?

regards,
Adam


On Fri, May 25, 2012 at 10:57 AM, Peter Walburn <
peter.walb...@omega-data.com> wrote:

> Hi,
>
> I have an application written in C# .Net 4.0 Framework.  I use SQLite
> within the application.  I have recently updated from an older version
> of SQLite to the latest version as I have moved to .Net Framework 4.0
> and I received error messages about Mixed Mode Frameworks.
>
> Anyway, I do the development on an 64-Bit Windows 7 operating system.  I
> use Installshield 2010 Express to create an installation for the
> application.  The application works ok on the Windows 7 PC, but when
> installed on a different PC (or on a Virtual Client PC using VMWare), I
> always receive a message such as:
>
> Unhandled exception has occurred in your application.  If you click
> Continue, the application will ignore this error and attempt to
> continue. If you click Quit, the application will close immediately.
>
> Unable to load DLL 'SQLite.Interop.dll': The specified module could not
> be found. (Exception from HRESULT: 0x8007007e).
>
> I have tried to post this email about 5 times and it is always returned
> saying: "The message's content type was not explicitly allowed."
>
>
>
> Peter Walburn
> Software Engineer
> E-mail: peter.walb...@omega-data.com
> Units 44-46 Howe Moss Avenue, Kirkhill Industrial Estate, Dyce, Aberdeen
> AB21 0GP
> Tel: +44 (0)1224 772763
> Fax: +44 (0)1224 772783
> www.omega-data.com
>
>
>
>
>
> This is an email from Omega Data Services Ltd, a company registered in
> Edinburgh, Scotland, with company number SC192323. Registered Office:
> Maclay Murray & Spens, 66 Queens Road, Aberdeen, AB15 4YE. Tel:
> 01224-356130. Website: www.omega-data.com .
> This email and any files transmitted are confidential and intended
> solely for the individual or entity to whom they are addressed. Any
> views or opinions expressed or presented are those of the author(s) and
> may not necessarily represent those of the company and no representation
> is given nor liability accepted for the accuracy or completeness of any
> information contained in this email unless expressly stated to the
> contrary. If you are not the intended recipient or have received this
> email in error, you may not use, disseminate, forward, print or copy it,
> but please notify the sender that you have received it in error and
> remove the message from your system immediately.
> Whilst we have taken reasonable precautions to ensure that this email
> and any attachments have been checked for viruses, we cannot guarantee
> that they are virus free, and we cannot accept liability for any damage
> sustained as a result of software viruses. We would advise that you
> carry out your own virus checks, especially before opening an
> attachment.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
VerifEye Technologies Inc.
905-948-0015x245
151 Whitehall Dr, Unit 2
Markham ON, L3R 9T1
Canada
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Please review this email to sqlite's mailing list

[Adam DeVita]

Did you check out
http://www.sqlite.org/inmemorydb.html

Could you use an in-memory db to act as a db for a save point?

When you are ready to commit, do so from 1 in memory db, while accumulating
into another in preparation for that save point.

Adam

On Tue, May 15, 2012 at 1:55 PM, Charles Samuels wrote:

>
> I'm using sqlite in addition to another database ("otherdb") storing data
> in a
> specific manner. I'm trying to keep atomicity of my disk commits. It can
> take
> several minutes for otherdb to commit, and while it commits it can already
> start accumulating data for a future transaction.
>
> Some of the data coming into this application also goes into the sqlite
> database. But I'd like to keep what's "on the oxide" between sqlite and
> otherdb consistent with eachother. Let's accept that otherdb
>
> At some point, we get a checkpoint; at this instant, what is in otherdb and
> what is in sqlite is what we want committed to sqlite, if either of them
> fails, we can rollback both of them and both databases return to a
> consistent
> state of a previous checkpoint. The problem is that in the time between
> checkpoint 1 and checkpoint 1 being committed to disk, more data is
> arriving.
>
> The question here is: where can I put that "more data" so that it won't be
> part of checkpoint 1, but is still accessable by sqlite select statements?
> (Accept that otherdb allows asychronous commits such that I can add more
> data
> to it that doesn't wind up on disk).
>
> There's a few possibilities with some serious disadvantages:
>
> * When otherdb completes its checkpoint, I commit sqlite; until otherdb and
> sqlite finish their commits, any data going into sqlite instead goes into a
> "mirror" sqlite that I can do queries against meanwhile (but then I have to
> replay *all* of those modifications against the primary sqlite). This can
> cost
> huge amounts of memory because the sqlite database can get big: 3GiB or
> more.
> It's also slow because all of a sudden I have to do a whole bunch of sqlite
> statements. It's even slower because now any update I do *normally* has to
> be
> cloned.
>
> * I could write a virtual filesystem layer for sqlite that somehow
> accumulates
> changes that I can merge in with insert statements. So it's like the
> previous
> solution but I use some arm waving in combination with smoke and mirrors
> to at
> least not make me have two total copies of the database. The problem with
> this
> one is I don't know how to do it, and even if I did, I wouldn't know how
> reliable it was.
>
> * If sqlite had a "commit transaction to savepoint X", then sqlite commits
> to
> the oxide everything up to a specific savepoint, keeping the savepoints
> after
> those committed still as active and uncommitted savepoints. The only
> disadvantage I can think of to this is that sqlite has no such feature.
>
> So how could I do this?
>
>
> Charles
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
VerifEye Technologies Inc.
905-948-0015x245
151 Whitehall Dr, Unit 2
Markham ON, L3R 9T1
Canada
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [sqlite-dev] Explain what 'transaction' means in javascript for sqlite, please?

[Adam DeVita]

STW:
http://stackoverflow.com/questions/740523/getting-a-webkit-executesql-transaction-to-return-a-value

other references
http://stackoverflow.com/questions/61972/javascript-sqlite


On Wed, May 9, 2012 at 10:32 AM, Pavel Ivanov  wrote:

> And again you wrote to the wrong mailing list. Use "Reply" button please.
>
> Pavel
>
>
> On Wed, May 9, 2012 at 10:27 AM, Andrew Lewis 
> wrote:
> > Hi Pavel,
> >
> > I disagree I'm afraid. It is instrumental in getting SQLIte up and
> running
> > in a javascript client-side environment, which SQLite lauds itself for.
> > Whilst it is in a wrapper for SQLite to operate within the javascript
> code,
> > it is integrated to such an extent that there is no reasonable
> separation. I
> > am hopeful someone from the SQLite domain will be able to help.
> >
> > All the best,
> >
> > Andrew.
> >
> > On 9 May 2012 15:02, Andrew Lewis  wrote:
> >>
> >> Hi!
> >> Can someone help me over a hump here please? I am trying to fire up
> SQLite
> >> in javascript for mobiles and whilst there are plenty of sites which
> contain
> >> the functions for creating a footballer list on the screen, some of
> which
> >> seem to work, none of them explains the 'transaction' call in
> >>
> >> db.transaction(call1, call2, callerror)
> >>
> >> I know of the SQL 'TRANSACTION' , BEGIN etc, but not of any Javascript
> >> command called transaction. It seems to be building an anonymous
> function
> >> but I cannot work that out. Can someone please explain?
> >>
> >> --
> >> Andrew J. Lewis MSc FLS MRI
> >> Simul Systems Ltd
> >> Chelmsford
> >> www.sysenvir.com
> >> 44-(0)7710 588318
> >>
> >
> >
> >
> > --
> > Andrew J. Lewis MSc FLS MRI
> > Simul Systems Ltd
> > Chelmsford
> > www.sysenvir.com
> > 44-(0)7710 588318
> >
> >
> > ___
> > sqlite-dev mailing list
> > sqlite-...@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-dev
> >
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
VerifEye Technologies Inc.
905-948-0015x245
151 Whitehall Dr, Unit 2
Markham ON, L3R 9T1
Canada
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] .NET and network server

[Adam DeVita]

Good day,

Is anyone using  .NET c# code  as a client to connect to one of the free
network server implementations?

regards,
Adam
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] error 404

[Adam DeVita]

Good day,

This page
http://sqlite.org/cvstrac/wiki?p=SqliteNetwork

Is giving an error 404 for this link
*SQL4Sockets* (http://www.oneledger.co.uk/sql4sockets.html)

regards,
Adam
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] type-o on http://www.sqlite.org/download.html

[Adam DeVita]

Good day,

This sentence on http://www.sqlite.org/download.html
Visit the System.Data.SQLite.org <http://system.data.sqlite.org/> website
and especially the download
page<http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki>for
source code an binaries of SQLite for .NET


is missing a letter 'd'.   The word "an" should be "and".   " source
code and binaries"

regards,
Adam DeVita
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] why one row's results isn't the same between program and command shell

[Adam DeVita]

Sorry for false alarm.  Please disregard this thread:

Solution: make sure you quit everything and isolate the code.  There is a
subsequent write to the new record that made it appear as a problem, when
it wasn't.

Adam

On Wed, Mar 21, 2012 at 3:25 PM, Adam DeVita <adev...@verifeye.com> wrote:

> Good day,
>
> I'm just exporting data to another db of known same structure, and setting
> a flag. It seems that one row shows an incorrect result, but only so in my
> c++ code.
>
> I extracted the queries being run using my debugger, and tried them in the
> command prompt.
>
> When I run the following batch of queries.
>
>
> Table is
> CREATE TABLE WorkStations (WSIDtxt text primary key, LocationID integer,
> record_updatetime text, write_out_ok int default 0);
>
> Command Prompt results:
> Tests prepared with:
> update workstations set write_out_ok=0;
>
> sqlite> select * from workstations;
> WSIDtxt|LocationID|record_updatetime|write_out_ok
> DT00-13-D3-50-AF-F9|2|2009.02.03.13.41|0
> PROGRAMMER-LAB00-1E-90-31-8D-19|3||0
> PROGRAMMER-LAB00-03-0D-00-00-01|3||0
> ADAM-PC00-25-64-8C-5A-3B|3|2012.03.21.18.57.19|0
>
>
>  attach
> 'Z:\PINTS\modules\deltafiles\deltas\2012.03.21_ADAM-PC00-25-64-8C-5A-3B_r006.db'
> as 'foo' ;
>
>  insert into foo.WorkStations select distinct * from main.WorkStations M
> where M.write_out_ok =0 and M.wsidtxt is not null;
>
>  update WorkStations  set write_out_ok =1  where wsidtxt in (select
> wsidtxt from foo.WorkStations ) ;
>
>
> sqlite> select * from foo.workstations;
> ADAM-PC00-25-64-8C-5A-3B|3|2012.03.21.18.57.19|0
> DT00-13-D3-50-AF-F9|2|2009.02.03.13.41|0
> PROGRAMMER-LAB00-03-0D-00-00-01|3||0
> PROGRAMMER-LAB00-1E-90-31-8D-19|3||0
> sqlite> select * from workstations;
> DT00-13-D3-50-AF-F9|2|2009.02.03.13.41|1
> PROGRAMMER-LAB00-1E-90-31-8D-19|3||1
> PROGRAMMER-LAB00-03-0D-00-00-01|3||1
> ADAM-PC00-25-64-8C-5A-3B|3|2012.03.21.18.57.19|1
>
>
> After executing a program that should have identical results, as identical
> queries are run:
> foo.workstations is identical.
>
> sqlite> select * from workstations;
> WSIDtxt|LocationID|record_updatetime|write_out_ok
> DT00-13-D3-50-AF-F9|2|2009.02.03.13.41|1
> PROGRAMMER-LAB00-1E-90-31-8D-19|3||1
> PROGRAMMER-LAB00-03-0D-00-00-01|3||1
> ADAM-PC00-25-64-8C-5A-3B|3|2012.03.21.18.57.19|0
>
> (Windows 7 32 bit edition PC. Tried with new copy of amalgamation 3.7.11
> as well as 3.7.4)
>
> Why is the last entry wrong?  Any suggestions as to how to review?
>
>
> regards,
> Adam DeVita
> VerifEye Technologies Inc.
>



-- 
VerifEye Technologies Inc.
905-948-0015x245
151 Whitehall Dr, Unit 2
Markham ON, L3R 9T1
Canada
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] why one row's results isn't the same between program and command shell

[Adam DeVita]

Good day,

I'm just exporting data to another db of known same structure, and setting
a flag. It seems that one row shows an incorrect result, but only so in my
c++ code.

I extracted the queries being run using my debugger, and tried them in the
command prompt.

When I run the following batch of queries.


Table is
CREATE TABLE WorkStations (WSIDtxt text primary key, LocationID integer,
record_updatetime text, write_out_ok int default 0);

Command Prompt results:
Tests prepared with:
update workstations set write_out_ok=0;

sqlite> select * from workstations;
WSIDtxt|LocationID|record_updatetime|write_out_ok
DT00-13-D3-50-AF-F9|2|2009.02.03.13.41|0
PROGRAMMER-LAB00-1E-90-31-8D-19|3||0
PROGRAMMER-LAB00-03-0D-00-00-01|3||0
ADAM-PC00-25-64-8C-5A-3B|3|2012.03.21.18.57.19|0


 attach
'Z:\PINTS\modules\deltafiles\deltas\2012.03.21_ADAM-PC00-25-64-8C-5A-3B_r006.db'
as 'foo' ;

 insert into foo.WorkStations select distinct * from main.WorkStations M
where M.write_out_ok =0 and M.wsidtxt is not null;

 update WorkStations  set write_out_ok =1  where wsidtxt in (select wsidtxt
from foo.WorkStations ) ;


sqlite> select * from foo.workstations;
ADAM-PC00-25-64-8C-5A-3B|3|2012.03.21.18.57.19|0
DT00-13-D3-50-AF-F9|2|2009.02.03.13.41|0
PROGRAMMER-LAB00-03-0D-00-00-01|3||0
PROGRAMMER-LAB00-1E-90-31-8D-19|3||0
sqlite> select * from workstations;
DT00-13-D3-50-AF-F9|2|2009.02.03.13.41|1
PROGRAMMER-LAB00-1E-90-31-8D-19|3||1
PROGRAMMER-LAB00-03-0D-00-00-01|3||1
ADAM-PC00-25-64-8C-5A-3B|3|2012.03.21.18.57.19|1


After executing a program that should have identical results, as identical
queries are run:
foo.workstations is identical.

sqlite> select * from workstations;
WSIDtxt|LocationID|record_updatetime|write_out_ok
DT00-13-D3-50-AF-F9|2|2009.02.03.13.41|1
PROGRAMMER-LAB00-1E-90-31-8D-19|3||1
PROGRAMMER-LAB00-03-0D-00-00-01|3||1
ADAM-PC00-25-64-8C-5A-3B|3|2012.03.21.18.57.19|0

(Windows 7 32 bit edition PC. Tried with new copy of amalgamation 3.7.11 as
well as 3.7.4)

Why is the last entry wrong?  Any suggestions as to how to review?


regards,
Adam DeVita
VerifEye Technologies Inc.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] insert image into db - windows batch

[Adam DeVita]

You should be able to do it.

We put a shot set of hex data in using a script.   You likely want to load
your file into a variable of hex (or binary) type, if you don't want to
write a script that writes a script.

 Then

insert into your_table (f1, blob)  values (  your_f1_value ,
X'reference_to_your_hex_data' )

the X' ' to denotes your hex value.  This is listed in the documentation.

regards,
Adam DeVita


On Wed, Jan 18, 2012 at 10:02 AM, Petr Lázňovský <la...@volny.cz> wrote:

> > On 18 Jan 2012, at 12:30pm, Petr Lázňovský wrote:
>
> >>>> have windows batch working with sqlite, may I insert image into
> database and than read this images from?
>
> >>> Convert your image into a BLOB and store it as a BLOB.  BLOBs are just
> runs of bytes -- you can store anything you want as a BLOB.
>
> >> What you mean by "Convert image into a BLOB" is there some kind of SW
> to do this? Does SQLite offer some way to do this? Sorry for dumb question,
> but I googling about this some time with no luck..
>
> > If you don't already know how to use your programming language to store
> integers and strings in a SQLite database, then learn that first.  Once you
> have software which can do that, read on:
>
> > An image (assuming you mean a file like a .jpeg or .png file) is just a
> long run of bytes.  You can store a long run of bytes in a SQLite database
> as data of type 'BLOB'.  This isn't a string, or a number, or a date, it's
> just a long run of bytes which is stored exactly as supplied with no
> interpretation.
>
> > So in your software, open the image file and read the contents of the
> file into memory.  Then use the SQLite library routine to create a new row,
> and bind that piece of memory to a BLOB.  When you want to retrieve that
> data, read the BLOB back out of the database.  Then if you want to make an
> image file of it you can do that.  If you want to display the image on the
> screen without making a file of it, you can do that instead if your
> programming language gives you way to do it.
>
> > The exact routines to use depends on the language your software is
> written in: C, Python, PHP, whatever.  That's all down to your personal
> programming choice.  But all the commonly-used interfaces to SQLite have
> the ability to handle BLOBs.
>
> Simon,
>
> did you read the subject of my mail? I am use sqlite from Win batch
> (shell) scripting by commands like:
>
> sqlite3.exe main.db "Insert into Table1 values('','','');"
>
> or
>
> sqlite3.exe main.db "select * from Table1 where Column='';"
>
> I am currently not a programmer (means Do not know any REAL language, only
> partialy Win shell) and this is my first deal with databases at all. So
> please be patient with me ;-)
>
> In Win shell AFAIK everything is a text, there are no data types. I spent
> much time with google, but seems nobody uses this combination (Win shell +
> sqlite) so there are very few informations on web :-/
>
> L.
>
>
>
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
VerifEye Technologies Inc.
905-948-0015x245
151 Whitehall Dr, Unit 2
Markham ON, L3R 9T1
Canada
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Auto index with wrong number of entries

[Adam DeVita]

Good idea.

The result was : ok

Of note, we found in our dump and import duplicate 4 entries that violated
uniqueness of the primary key. (2 entries of 4 different primary keys, with
only 1 other field having a different int.) We identified which one belongs
and commented out the others.  How did this happen for this workstation? I'm
not sure if we will ever know, given that the offending records are create
date 2 years ago, modified 1 year ago.


Command prompt reports version 3.6.10 on start up.

Adam
On Wed, Jun 29, 2011 at 9:08 AM, Simon Slavin <slav...@bigfraud.org> wrote:

>
> On 29 Jun 2011, at 2:04pm, Adam DeVita wrote:
>
> > On Tue, Jun 28, 2011 at 11:34 AM, Simon Slavin <slav...@bigfraud.org>
> wrote:
> >
> >> Use the sqlite3 command-line shell to dump the database to SQL commands,
> >> then create a new database by reading it back in.
> >>
> >> While the data is in the SQL command file, you can take a look and make
> >> sure those records are present.
> >
> > Success!
>
> Great.  You might want to run integrity_check on the result just for the
> very unlikely possibility that you have discovered a bug in SQLite and the
> resulting database has the same problem.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
VerifEye Technologies Inc.
905-948-0015x245
151 Whitehall Dr, Unit 2
Markham ON, L3R 9T1
Canada
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Auto index with wrong number of entries

[Adam DeVita]

Success!



On Tue, Jun 28, 2011 at 11:34 AM, Simon Slavin <slav...@bigfraud.org> wrote:

>
> On 28 Jun 2011, at 4:22pm, Adam DeVita wrote:
>
> > I can see the data that I want to export.   How do I fix these indexes?
>
> Use the sqlite3 command-line shell to dump the database to SQL commands,
> then create a new database by reading it back in.
>
> While the data is in the SQL command file, you can take a look and make
> sure those records are present.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
VerifEye Technologies Inc.
905-948-0015x245
151 Whitehall Dr, Unit 2
Markham ON, L3R 9T1
Canada
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Auto index with wrong number of entries

[Adam DeVita]

Good day,

Following a data collection & reporting error from a workstation, I have
found that

pragma integrity_check


reported that 2 of my tables have a few thousand entries missing in their
auto indexes.
wrong number of entries in index sqlite_auto_index_tablename_1
rowid 87973 missing from ... table above.


I can see the data that I want to export.   How do I fix these indexes?

regards,
Adam DeVita
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite as a Logger: How to mimic "rotation of logs"?

[Adam DeVita]

Why not use INSERT OR REPLACE to your advantage?

If you set the maximum number of log entries you wanted to keep, then kept
track of your log insert statement,  you could wrap by

int this_log_entry_id=1; //initialize..  actually could be initialized by
getting the log entry id of the min date in your log at the beginning of
your program.



if (this_log_entry_id > max_log_entries){
 this_log_entry_id =1;
}
else{
   this_log_entry_id
}

call_insert_function (this_log_entry_id /*becomes the primary key that you
are inserting or replacing*/ ,  data_to_be_logged ,.



Adam


On Tue, May 10, 2011 at 9:08 AM, Simon Slavin  wrote:

>
> On 10 May 2011, at 1:57pm, Lauri Nurmi wrote:
>
> > El mar, 10-05-2011 a las 12:34 +0100, Simon Slavin escribió:
> >> On 10 May 2011, at 11:42am, Lynton Grice wrote:
> >>
> >>> BTW: if I am using SQLIte as a logger, what PRAGMA statement can I use
> >>> to say FIX the sqlite database size to say "5 MB"?
> >>
> >> There isn't one.  SQLite would not know which records to delete.
> >
> > If such a pragma existed, SQLite wouldn't need to delete anything
> > necessarily, it could behave like it behaves when trying to write to a
> > full disk.
>
> Good idea.  Or introduce a new result code for 'Database has reached
> maximum allowed size'.  Presumably it would be handled as fixing the number
> of pages.  Might be useful for small platforms like cellphones, where
> running out of memory is a disaster.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
VerifEye Technologies Inc.
905-948-0015x245
151 Whitehall Dr, Unit 2
Markham ON, L3R 9T1
Canada
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] What happens if you insert more than your RAM size into an in memory database?

[Adam DeVita]

Thanks for the responses.

Our application is typically implemented on a standard laptop PC.  It seems
that the symptoms displayed are consistent with what this list describes
would happen,  so it looks like I can start thinking of how to write a
defence.   It does suddenly become very slow.

I think the potential solutions we may implement  are all in application
code, so not really an SQLite problem.

Thanks,
Adam



On Mon, Apr 18, 2011 at 10:07 AM, eLaReF <ela...@btinternet.com> wrote:

> Talking as a Windows user only rather than an SQL expert (I'm not even
> good enough to call myself a beginner!)
>
> Are we talking about a small netbook type with only say 8GB of memory
> and no hard drive.
>
> If a Windows m/c has a hard drive, surely virtual memory
> (drive-swapping) comes into play?
> It would, of course become v-e-r-y slow in comparison.
>
>
> eLaReF
>
>
>
>
> On 18/04/2011 14:46, Pavel Ivanov wrote:
> > You won't be able to insert. The statement will fail.
> >
> > Pavel
> >
> >
> > On Mon, Apr 18, 2011 at 9:44 AM, Adam DeVita<adev...@verifeye.com>
>  wrote:
> >> Good day,
> >>
> >> What happens if you insert more than your RAM size into an in memory
> >> database?
> >> (I'm particularly interested in the Windows context).
> >>
> >> regards,
> >> Adam
> >> ___
> >> sqlite-users mailing list
> >> sqlite-users@sqlite.org
> >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >>
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> >
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
VerifEye Technologies Inc.
905-948-0015x245
151 Whitehall Dr, Unit 2
Markham ON, L3R 9T1
Canada
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] What happens if you insert more than your RAM size into an in memory database?

[Adam DeVita]

Good day,

What happens if you insert more than your RAM size into an in memory
database?
(I'm particularly interested in the Windows context).

regards,
Adam
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] X most recent entries

[Adam DeVita]

Are you wanting the last 5000 from player 1 and  last 5000 from player 2?

You can even limit and order the sub selects.

Otherwise, I don't see the purpose of a union when OR would do.


SELECT * FROM multiturnTable WHERE rowid in (SELECT rowid FROM
multiturnTable WHERE player1 ='?' order by rowid desc limit 5000 UNION ALL
SELECT rowid FROM
multiturnTable WHERE player2 = '?' rowid desc limit 5000) AND (complete=0 OR
p1SubmitScore=0
OR p2SubmitScore=0)

I'm not sure of your context, but

SELECT * FROM multiturnTable WHERE rowid in
(SELECT rowid FROM
multiturnTable WHERE player1 ='?' AND (complete=0 OR p1SubmitScore=0
   OR p2SubmitScore=0) order by rowid desc limit 5000
UNION ALL SELECT rowid FROM
multiturnTable WHERE player2 = '?'  AND (complete=0 OR p1SubmitScore=0
  OR p2SubmitScore=0) rowid desc limit 5000)  AND (complete=0 OR
p1SubmitScore=0
OR p2SubmitScore=0)

so you get the last 5000 qualifying records of each, rather than the latest
5000 of each and then filtering out the disqualifying ones


On Mon, Mar 14, 2011 at 2:02 PM, Duquette, William H (318K) <
william.h.duque...@jpl.nasa.gov> wrote:

> Assuming that higher rowids really are later rowids, wouldn't adding "ORDER
> BY rowid DESC" and "LIMIT 5000" do the job?
>
> Will
>
>
> On 3/14/11 10:58 AM, "Ian Hardingham" <i...@omroth.com> wrote:
>
> Ah, sorry about this - my query is this one:
>
> SELECT * FROM multiturnTable WHERE rowid in (SELECT rowid FROM
> multiturnTable WHERE player1 ='?' UNION ALL SELECT rowid FROM
> multiturnTable WHERE player2 = '?') AND (complete=0 OR p1SubmitScore=0
> OR p2SubmitScore=0)
>
> And I only want to consider the last 5000 for any SELECTs from
> multiturnTable.
>
> Thanks,
> Ian
>
> On 14/03/2011 17:54, Adam DeVita wrote:
> > select id from table order by id desc limit 5000
> >
> >
> > Adam
> >
> > On Mon, Mar 14, 2011 at 1:52 PM, Ian Hardingham <i...@omroth.com
> > <mailto:i...@omroth.com>> wrote:
> >
> > Hey guys.
> >
> > I have a table with an autoincrement primary ID, and as part of a
> > select
> > I would like to only take the 5000 "largest"/most recent ids.  Is
> > there
> > a quick way of doing this without having to get the max first?
> >
> > Thanks,
> > Ian
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org <mailto:sqlite-users@sqlite.org>
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> >
> >
> >
> > --
> > VerifEye Technologies Inc.
> > <905-948-0015>905-948-0015x245
> > 151 Whitehall Dr, Unit 2
> > Markham ON, L3R 9T1
> > Canada
> >
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
> --
> Will Duquette -- william.h.duque...@jpl.nasa.gov
> Athena Development Lead -- Jet Propulsion Laboratory
> "It's amazing what you can do with the right tools."
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
VerifEye Technologies Inc.
905-948-0015x245
151 Whitehall Dr, Unit 2
Markham ON, L3R 9T1
Canada
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] X most recent entries

[Adam DeVita]

select id from table order by id desc limit 5000


Adam

On Mon, Mar 14, 2011 at 1:52 PM, Ian Hardingham  wrote:

> Hey guys.
>
> I have a table with an autoincrement primary ID, and as part of a select
> I would like to only take the 5000 "largest"/most recent ids.  Is there
> a quick way of doing this without having to get the max first?
>
> Thanks,
> Ian
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
VerifEye Technologies Inc.
905-948-0015x245
151 Whitehall Dr, Unit 2
Markham ON, L3R 9T1
Canada
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Propose minor incompatible API change

[Adam DeVita]

I think that a bunch of good points have been made, especially as to why you
should hold your ground.

(I don't have sympathy for poor code that doesn't follow the documentation,
especially when there is a large, competent, and helpful community group
that usually comes to people's aid in less than 1 day.  Since when is my
app's bug your core problem?)

If the smartphone providers are willing to pay "enough" to fund this project
to ensure it keeps going, then I guess the idea of  "He who pays the piper
calls the tune."  applies.   Having lots of apps that work well are a way
these companies compete with each other.  I don't like the idea of a
technical decision being determined in the spirit of a bribe, but this
doesn't affect well written code. so I don't much care which side this
falls on.

The idea of a examples library for the docs is good, probably should have
its own thread to discuss how to implement.

best wishes,
Adam

On Tue, Jan 11, 2011 at 9:09 AM, Philip Graham Willoughby <
phil.willoug...@strawberrycat.com> wrote:

> On 11 Jan 2011, at 13:36, Andy Gibbs wrote:
>
> > On Tuesday, January 11, 2011 1:35 PM, Jean-Denis Muys wrote:
> >
> >> Don't encumber SQLite with workarounds and special cases
> >> to cater to bugs in client software.
> >
> > Isn't an accurate synopsis of the problem this: that Sqlite has *already*
> > implemented a workaround in 3.7.0, and that this workaround has actually
> > caused a bigger problem, albeit only for "incorrectly" written code.
>
> It has (also) caused problems for code which was correct (if not pretty)
> given the API as documented in the last release before 3.6.23.1.
>
> > Therefore, shouldn't this original workaround be fixed, in the way
> > prescribed (since for all intents and purposes the new fix is better than
> > the old fix)?
>
> Arguable - either 'fix' is undesirable if you have pre-3.6.23.1 code which
> is expecting to see SQLITE_MISUSE when it used to see it. It also makes a
> certain class of bugs more likely - if you get SQLITE_BUSY within an
> explicit transaction you should roll-back that transaction and begin it
> again; IMO you are more likely to notice and obey that requirement if you
> cannot just immediately call sqlite3_step again.
>
> That said, I like the current behaviour best of the three options, as it's
> less code to write in applications and it's consistent with itself. The
> <3.6.23.1 behaviour is also consistent, and there is a case for going back
> to that if the current behaviour is (with hindsight) a more-incompatible
> change than should have been introduced mid-release.
>
> I like the proposed new fix least, as it still requires sqlite3_reset on
> the normal path and creates an inconsistency between that and the abnormal
> path.
>
> > The issue of whether or not sqlite should provide workarounds (in future)
> to
> > cater for bugs in client software is another question, isn't it?
>
> Yes; I would expect future workaround-requesters to appeal to the precedent
> set this time.
>
> The precedent that SQLite can be improved at any time, and that's what
> happened in 3.6.23.1 so it won't be reverted is one option. This is probably
> what developers expect in the open-source world.
>
> The precedent that flow-affecting changes will not be put in mid-release
> and this 3.6.23.1 change was therefore an error that will be reverted is
> another. This is probably what developers expect in the commercial world.
>
> The third is the precedent that developers don't need to worry about
> reading the documentation and handling errors correctly as SQLite will
> usually be changed in a future release to make their code work. And if this
> change breaks someone else then SQLite will be changed again.
>
> Best Regards,
>
> Phil Willoughby
> --
> Managing Director, StrawberryCat Limited
>
> StrawberryCat Limited is registered in England and Wales with Company No.
> 7234809.
>
> The registered office address of StrawberryCat Limited is:
>
> 107 Morgan Le Fay Drive
> Eastleigh
> SO53 4JH
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
VerifEye Technologies Inc.
905-948-0015x245
151 Whitehall Dr, Unit 2
Markham ON, L3R 9T1
Canada
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] R: R: Crypto lib for Sqlite - suggest required

[Adam DeVita]

Just to add my $0.02

We use http://www.safenet-inc.com/ HASP HL Encryption.  (We use HASP keys
for end user products so it was 'free' to my internal product tracking
system db.) This key allows the exe to get encrypted and optionally a data
file as well.

The encryption of the program provides us with some security against a
password being saved within the exe in clear form.   When it comes to data
encryption though, the performance penalty we suffer is 2x to 4x.  Also,
HASP HL data encryption + Sqlite + Windows 7,  64 bit editions don't work
reliably.  The HASP envelope does prevent an executable from running with a
debugger open. It may be that newer versions of compiler or key will work,
but I can't say that they will (nor does safenet's technical "support"
actually provide answers).   Bitter experience so far says "Don't use HASP
for data encryption."

Adam




On Sun, Jan 9, 2011 at 5:40 PM, Simon Slavin  wrote:

>
> On 9 Jan 2011, at 5:29pm, Roger Binns wrote:
>
> > I think you misunderstand how the SQLite encryption extension works.  The
> on
> > disk storage format for SQLite is a series of fixed sized pages.  The
> > extension transparently encrypts each page on writing to disk and
> decrypts
> > on reading.  To use it you open/attach a database and then provide the
> > password either via a C API or a pragma.  You just make regular SQLite
> API
> > calls and everything just works.
> >
> >  http://www.hwaci.com/sw/sqlite/see.html
> >
> > The various other ones pointed out do something similar but since you go
> via
> > their API layers they intersperse code to do encryption.  I found it very
> > hard to work out what they did for encryption since things like the
> > algorithm used, IV (the usual weakness for home grown implementations)
> etc
> > do matter.  They also make other choices:
>
> As far as I can work out, the two solutions he pointed to encrypt at the
> field level.  So if you understand the file structure of an SQLite database
> you can, for example, work out which records have the same values in either
> within a table or as across tables.  It also gives you a handy-dandy
> plain/crypt pair since you will know that certain fields definitely start
> with 'CREATE TABLE ' and such things.
>
> On the other hand, these solutions are cheaper than the hwaci one.  As with
> most encryption it depends how much effort you think the enemy will devote
> to attacking your technique.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
VerifEye Technologies Inc.
905-948-0015x245
151 Whitehall Dr, Unit 2
Markham ON, L3R 9T1
Canada
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 64 bit sqlite 3

[Adam DeVita]

At first I was like "awe, I don't wanna do my homework". I'd have to
recompile all my little utilities and distribute them rather than just
distribute a new DLL and it would be nice to keep our local program
maintainers from "helping" instead of keeping to the officially released
code.

Now, after showing that my 64 bit problems go away when I include the
amalgamation source in the project, the whining just stops.

thanks
:)

Adam

On Fri, Dec 17, 2010 at 5:20 PM, Simon Slavin <slav...@bigfraud.org> wrote:

>
> On 17 Dec 2010, at 4:30pm, Adam DeVita wrote:
>
> > Will a 64 bit Windows DLL eventually be posted for download?
>
> SQLite is distributed as source.  Generally speaking you compile the
> amagamation form directly into your application rather than making a
> separate library of it.  Whatever form you want it in, feel free to compile
> it yourself.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
VerifEye Technologies Inc.
905-948-0015x245
151 Whitehall Dr, Unit 2
Markham ON, L3R 9T1
Canada
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 64 bit sqlite 3

[Adam DeVita]

Will a 64 bit Windows DLL eventually be posted for download?



On Fri, Dec 17, 2010 at 9:45 AM, Eric Smith  wrote:

> On Fri, Dec 17, 2010 at 4:36 AM, giuseppe500  wrote:
> > There is a version of SQLite 3 for 64-bit systems?
> > or, you can simply compile the source of sqlite3 at 64-bit with c++ 2008?
> > thanks.
>
> FWIW I compiled sqlite 3.6.23.1 along with its tcl hooks and have been
> happily using it in a (single-threaded, multi-process) 64-bit
> application in tcl and C on both FreeBSD6 and linux 2.6.18-164,
> RHEL5.4 for 6 or 8 months with no issues whatsoever.  The application
> parses a superset of csv (with arbitrary optional field separators,
> arbitrary optional quotation characters, arbitrary optional padding
> characters, arbitrary record separators, field data type checking etc)
> and exposes relational queries on the data set.
>
> The app screams along at 16 records per second on the parse side,
> and I'm still pretty sure it's my parse code that's the bottleneck and
> not sqlite.
>
> Eric
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
VerifEye Technologies Inc.
905-948-0015x245
151 Whitehall Dr, Unit 2
Markham ON, L3R 9T1
Canada
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Tricky grouping query

[Adam DeVita]

Why would you want to do this in plane sql, as opposed to using the API to
go through the list and derive it?



On Fri, Oct 1, 2010 at 3:37 PM, Andy Chambers wrote:

> Given the following
>
> create table events (
>  id,
>  date,
>  status
> );
> insert into events values ('001','a','N');
> insert into events values ('001','b','N');
> insert into events values ('001','c','Y');
> insert into events values ('001','d','N');
> insert into events values ('001','e','Y');
> insert into events values ('001','f','Y');
> insert into events values ('001','g','N');
>
> Is it possible, using plain SQL, to derive the following
>
> 001,c,d
> 001,e,g
>
> i.e. an "N" in the third column means event "001" has stopped, and a
> "Y" means it
> has started back up again.  Note that because the status immediately
> preceding "f"
> is also "Y", there is no corresponding row in the output
>
> Cheers,
> Andy
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
VerifEye Technologies Inc.
905-948-0015x245
7100 Warden Ave, Unit 3
Markham ON, L3R 8B5
Canada
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is there a table that show all the available function from sqlite3?

[Adam DeVita]

Are you looking for
http://www.sqlite.org/c3ref/funclist.html
?



On Fri, Aug 13, 2010 at 12:37 PM, Peng Yu  wrote:

> Hi,
>
> http://www.sqlite.org/docs.html
>
> I don't see a table that shows all the available functions in sqlite3.
> Would you please let me know if there is such a table?
>
> --
> Regards,
> Peng
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
VerifEye Technologies Inc.
905-948-0015x245
7100 Warden Ave, Unit 3
Markham ON, L3R 8B5
Canada
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Substring (LIKE "%key%") searches, would FTS3 with suffix-tree tokenizer be the fast way?

[Adam DeVita]

A variant on Simon's plan.
Are the 10,000 rows static, slowly changing, or frequently changing?   Does
it make sense to pre-calculate some counts at the time data is loaded?  Is
this memory constrained so much that you can't afford 1 or 2 MB to let you
look up based on ints? (I'm assuming that one letter is all you are after,
either 'starts with' or 'contains' and not in order combinations.)

Adam

On Thu, Aug 5, 2010 at 5:40 PM, Simon Slavin  wrote:

>
> On 5 Aug 2010, at 10:03pm, Sam Roberts wrote:
>
> > But do you think the section would make the counting faster? I think
> > I'd have to get the row counts like this, which would still do the
> > slow full table scan:
> >
> >  select section, count(*) from my_table where name like '%e%' group by
> section;
>
> But 'group by section' can profit from the index on the section column so
> it should be faster.
>
> As with all these things, the suggestion is to try it and see.  You should
> try six or seven different solutions including shuffling columns and indexes
> before you settle on the one that will be in your final code.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
VerifEye Technologies Inc.
905-948-0015x245
7100 Warden Ave, Unit 3
Markham ON, L3R 8B5
Canada
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] concat 2 const chars ?

[Adam DeVita]

and don't use strcpy

here is why
https://buildsecurityin.us-cert.gov/bsi-rules/home/g1/848-BSI.html

On Fri, Jul 9, 2010 at 3:06 PM, rollerueckwaerts
wrote:

>
> Hello,
> I try to get an sql query string from 2 const chars.
>
> const char *language;
> language = "'6'";
> const char *sql2 = "SELECT key,name,text FROM uebersetzungen WHERE
> sprach_id
> = ";
>
> const char *sql = strcpy(sql2,language);
> // or
> const char *sql = "SELECT key,name,text FROM uebersetzungen WHERE sprach_id
> = " + language;
> //or
> const char *sql = "SELECT key,name,text FROM uebersetzungen WHERE sprach_id
> = " & language;
>
>
> nothing works :)
>
> How can i do this ?
>
> Hoping for help :)
> tobi
>
> --
> View this message in context:
> http://old.nabble.com/concat-2-const-chars---tp29121393p29121393.html
> Sent from the SQLite mailing list archive at Nabble.com.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
VerifEye Technologies Inc.
905-948-0015x245
7100 Warden Ave, Unit 3
Markham ON, L3R 8B5
Canada
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] concat 2 const chars ?

[Adam DeVita]

>From the point of view of a C question, make your array of characters large
enough to hold the characters you want (and terminating null) before copying
them in.

>From the point of view of an SQL: if you want to change the comparison
constant in a where clause, look up bind parameters.

read through
http://www.sqlite.org/c3ref/bind_blob.html

There are lots of examples on the list of binding.

regards,
Adam


On Fri, Jul 9, 2010 at 3:06 PM, rollerueckwaerts
wrote:

>
> Hello,
> I try to get an sql query string from 2 const chars.
>
> const char *language;
> language = "'6'";
> const char *sql2 = "SELECT key,name,text FROM uebersetzungen WHERE
> sprach_id
> = ";
>
> const char *sql = strcpy(sql2,language);
> // or
> const char *sql = "SELECT key,name,text FROM uebersetzungen WHERE sprach_id
> = " + language;
> //or
> const char *sql = "SELECT key,name,text FROM uebersetzungen WHERE sprach_id
> = " & language;
>
>
> nothing works :)
>
> How can i do this ?
>
> Hoping for help :)
> tobi
>
> --
> View this message in context:
> http://old.nabble.com/concat-2-const-chars---tp29121393p29121393.html
> Sent from the SQLite mailing list archive at Nabble.com.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
VerifEye Technologies Inc.
905-948-0015x245
7100 Warden Ave, Unit 3
Markham ON, L3R 8B5
Canada
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users