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] 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

[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] 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] 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] 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] 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] 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] 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] 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] 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] 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] 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] 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"

[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] 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] 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] 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

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] 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] 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] 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] 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 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] 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] 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] 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] 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] 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

[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] 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

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

[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] 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

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

[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] 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] 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] 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] 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] 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] 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]

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]

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] 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] 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] 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 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] 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] 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] 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] 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] 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

Re: [sqlite] copy data from one db to another

[Adam DeVita]

start by doing an open db1 (as main)
then attach path to db2 as 'db2'

insert into main.table_one_name select * from db2.table_one_name ;

This selects all records from db2 and puts them into db1 in one statement.

Adam
On Tue, Jun 8, 2010 at 3:02 PM, Scott Frankel  wrote:

>
> Hi all,
>
> What's the best way to copy data from one db to another?
>
> Given 2 databases with identical schemas, one full of data and the
> other empty, the brute force way would be to perform selects on the
> source db, then for each row, perform an insert into the destination
> db.  Is there a more efficient way?
>
> Thanks in advance!
> Scott
>
>
> ___
> 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] copy data from one db to another

[Adam DeVita]

The db that you open your initial connection to is called main by default. I
haven't had the occasion to use a temp or memory db so I can't comment.

The attach statement works as normal SQL.
attach 'path to your db' as 'some_alias_name'

like
attach 'c:\temp dir\db2.db' as 'db2'

Suppose both files have a table named 'some_table'.

select * from db2.some_table ; /*refers to the attached db*/
select * from main.some_table ; /*refers to the db you first made a
connection with.*/

I find the command line tool wonderful for testing out syntax.

Happy Computing

Adam


On Tue, Jun 8, 2010 at 4:09 PM, Scott Frankel <fran...@circlesfx.com> wrote:

>
> On Jun 8, 2010, at 12:07 PM, Adam DeVita wrote:
>
> > start by doing an open db1 (as main)
> > then attach path to db2 as 'db2'
> >
> > insert into main.table_one_name select * from db2.table_one_name ;
> >
> > This selects all records from db2 and puts them into db1 in one
> > statement.
>
> I've been reading about the ATTACH DATABASE cmd, but was confused by
> the documentation's warnings about main and temp dbs, namings, and
> transactions using :memory:.  I'll take a closer look.
>
> Thanks!
> Scott
>
>
>
>
> >
> > Adam
> > On Tue, Jun 8, 2010 at 3:02 PM, Scott Frankel
> > <fran...@circlesfx.com> wrote:
> >
> >>
> >> Hi all,
> >>
> >> What's the best way to copy data from one db to another?
> >>
> >> Given 2 databases with identical schemas, one full of data and the
> >> other empty, the brute force way would be to perform selects on the
> >> source db, then for each row, perform an insert into the destination
> >> db.  Is there a more efficient way?
> >>
> >> Thanks in advance!
> >> Scott
> >>
> >>
> >> ___
> >> 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
> >
>
> ___
> 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] database development - correct way?

[Adam DeVita]

I wouldn't advise using an SQL keyword as a table name: "Order"

I presume that your order collection table example is shorter than the real
one for the sake of the example?  One often sees a date or time of some sort
associated with an order so that one can create reports based on dates.
(How many sales did we make this month?)

regards,
Adam




On Wed, Jun 9, 2010 at 9:09 AM, Oliver Peters  wrote:

> Hello,
>
> despite it's just a question about construction I hope somebody is willing
> to
> push me into the right direction if necessary.
>
> my simplified case
> --
> I've the 3 tables customer, article and order
>
> my thoughts about the table customer:
> the customernumber can be from 3 different sources with possible
> overlappings
> (i.e. I can get 3 from source A and 3 from source B) so I adopt the
> field customerorigin to make a difference
> For simplicity I created a field id that is taking the part of the Primary
> Key
> and just declared "UNIQUE(customernumber,customerorigin)"
>
>
> the SQL-Code
> 
> CREATE TABLE customer(
>   idINTEGER PRIMARY KEY AUTOINCREMENT,
>   customernumberINTEGER,
>   customeroriginINTEGER,
>   name  TEXT,
>   UNIQUE(customernumber,customerorigin)
>   );
>
> CREATE TABLE article(
>   idINTEGER PRIMARY KEY AUTOINCREMENT,
>   name  TEXT
>   );
>
> CREATE TABLE order(
>   idINTEGER PRIMARY KEY AUTOINCREMENT,
>   id_customer   INTEGER,
>   id_articleINTEGER,
>   UNIQUE(id_customer,id_article),
>   FOREIGN KEY(id_customer) REFERENCES customer(id),
>   FOREIGN KEY(id_article)  REFERENCES article(id)
>   );
>
>
> simple question
> ---
> Is this a correct way or do I make a mistake?
>
> greetings
> Oliver
>
> ___
> 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] Mail loop?

[Adam DeVita]

Is it possible for the list admin to seed a message to the list that, if
auto-replied, would automatically remove one from the list?

This thread, although off the sqlite topic, is still interesting.

regards,
Adam

On Mon, Jun 14, 2010 at 10:06 AM, Black, Michael (IS) <
michael.bla...@ngc.com> wrote:

> And if Peter isn't the problem you can do a binary search.
>
> With 2500 members just keep splitting the list in half and set a test email
> to each half.
> Then do it again for the half that shows the loop.
>
> 2500->1250->625->318->159->80->40->20->10->5->3->2->1
>
> 26 emails will ID the culprit (two at each level -- one should show the
> loop and one should not).
>
> Just put a different subject in each email so you know what you're looking
> at.
>
> Or...2500 emails to each person on the list..one of which should show up on
> this list.
>
>
> Michael D. Black
> Senior Scientist
> Northrop Grumman Mission Systems
>
>
> 
>
> From: sqlite-users-boun...@sqlite.org on behalf of Richard Hipp
> Sent: Mon 6/14/2010 8:30 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Mail loop?
>
>
>
> On Mon, Jun 14, 2010 at 9:09 AM, Black, Michael (IS) <
> michael.bla...@ngc.com
> > wrote:
>
> > The problem is that somebody has a .forward or such which loops back to
> the
> > list.  It's probably in the alias expansion of sqlite-users which expands
> to
> > a listfor which a member then expands back to sqlite-users.
> >
> > The mail logs may show whether it happens locally or if its a remote
> user.
> >  If it's a remote user you should see a log entry complaining about too
> many
> > loops and it may ID the user name involved.
> >
> >
> > I'd check sqlite-users for another sqlite-users@ entry...
> >
>
> There is no "sqlite-users" entry in the (2500+) membership list for
> sqlite-users.  Nor am I able to find any clues in the header of the bounce
> reply.  Another ideas?
>
>
> >
> > Michael D. Black
> > Senior Scientist
> > Northrop Grumman Mission Systems
> >
> >
> > 
> >
> > From: sqlite-users-boun...@sqlite.org on behalf of Richard Hipp
> > Sent: Mon 6/14/2010 7:46 AM
> > To: General Discussion of SQLite Database
> > Subject: Re: [sqlite] Mail loop?
> >
> >
> >
> > It is not just you.  But I have no idea what the problem is or how to fix
> > it.
> >
> > On Mon, Jun 14, 2010 at 8:43 AM, Black, Michael (IS) <
> > michael.bla...@ngc.com
> > > wrote:
> >
> > > I've been seing this the last 2-3 weeks whenever I email the list...is
> it
> > > just me???
> > >
> > >
> > > This is the mail system at host sqlite.org.
> > >
> > > I'm sorry to have to inform you that your message could not
> > > be delivered to one or more recipients. It's attached below.
> > >
> > > For further assistance, please send mail to postmaster.
> > >
> > > If you do so, please include this problem report. You can
> > > delete your own text from the attached returned message.
> > >
> > >   The mail system
> > >
> > > : mail forwarding loop for
> > > sqlite-users@sqlite.org
> > >
> > >
> > >
> > > Michael D. Black
> > > Senior Scientist
> > > Northrop Grumman Mission Systems
> > >
> > >
> > >
> > > ___
> > > 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
> >
> >
>
>
> --
> -
> 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
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

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] 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] 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] 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] 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] 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] 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] 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

[sqlite] Foreign key support

[Adam DeVita]

Good day,

If I write into the sqlite3 using the analyzer:

create table mysns (SN int primary key, desc text);
insert into mysns (SN, desc) values (1,2);
create table t2 (mynum int, desc2 text, SN references mysns(SN) );

insert into t2 (mynum, desc2, SN) values (1,"two", 3);

This insert didn't produce an error, despite the fact 3 isn't in table
mysns.  Why?

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

Re: [sqlite] Trying to INSERT rows into a table

[Adam DeVita]

Why are you calling reset after step?

regards,
Adam

On Fri, Feb 6, 2009 at 3:51 PM, Stephen Abbamonte
 wrote:
> Hi all I am using SQLite3 and running into a bit of a snag.  I have a
> Database with an empty table and I am calling a series of INSERT commands
> during the execution of my program but it seems that each call is
> overwriting the first row in the table. I have tried the table with and
> without a PRIMARY KEY and I get the same result.  The call to sqlite3_step
> with my INSERT command returns SQLITE_DONE so I call  sqlite3_reset first
> then I call sqlite3_finalize. Then I prepare a new statement and
> do these steps again. If anyone can help I would greatly appreciate it.
> Thanks.
>
>
>
> Stephen Abbamonte
>
>  Software Engineer
>
>  Tornado Studios
>
>
>
>
>
> ___
> 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] Trying to INSERT rows into a table

[Adam DeVita]

Good day,

I'm new to SQL lite too, but  I don't see the point of reset for a
one-off insert query or one that gets finalized right away.

Insert has no row set, so you aren't about to reset and step through
the rowset again.

As always, I'm willing to be enlightened  My code runs successive
inserts without an apparent problem so far.

Adam

On Fri, Feb 6, 2009 at 3:57 PM, Stephen Abbamonte
<st...@tornadostudios.com> wrote:
> Because the SDK docs tell me to.
> http://www.sqlite.org/c3ref/step.html
>
>
> " SQLITE_DONE means that the statement has finished executing successfully.
> sqlite3_step() should not be called again on this virtual machine without
> first calling sqlite3_reset() to reset the virtual machine back to its
> initial state."
>
>
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Adam DeVita
> Sent: Friday, February 06, 2009 2:57 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Trying to INSERT rows into a table
>
> Why are you calling reset after step?
>
> regards,
> Adam
>
> On Fri, Feb 6, 2009 at 3:51 PM, Stephen Abbamonte
> <st...@tornadostudios.com> wrote:
>> Hi all I am using SQLite3 and running into a bit of a snag.  I have a
>> Database with an empty table and I am calling a series of INSERT commands
>> during the execution of my program but it seems that each call is
>> overwriting the first row in the table. I have tried the table with and
>> without a PRIMARY KEY and I get the same result.  The call to sqlite3_step
>> with my INSERT command returns SQLITE_DONE so I call  sqlite3_reset first
>> then I call sqlite3_finalize. Then I prepare a new statement
> and
>> do these steps again. If anyone can help I would greatly appreciate it.
>> Thanks.
>>
>>
>>
>> Stephen Abbamonte
>>
>>  Software Engineer
>>
>>  Tornado Studios
>>
>>
>>
>>
>>
>> ___
>> 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
>
>
>
> ___
> 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] Merging two SQLites + leaving out duplicates?

[Adam DeVita]

Good day,
I'd like to tack on an additional question, since it is a common case
applied to the goal of this thread.

Suppose you have a field that time stamps the date each company's data
was entered.

Unfortunately some records in old2.db have newer contact information,
while others are older than in old1.db.

Since companies sometimes change their address the one with the latest
'date_Entered' should be the one left in the merged database.

One way to do this is similar to ...

1) Insert into new.db from old1.db (as previously posted)
2) delete from new.db newdb where newdb.company in( select company
from old1.db) and newdb.date_entered < old1.db.date_entered (/*syntax
errors likely present here*/)
3) insert from old3.db where not in new.db (as previously posted)

Can 2) be done with an UPDATE instead?   Is there a better way than even this?

I presume this would be easily adapted back to the  case of just
updating  db1 with new or newer records in db2.

regards,
Adam DeVita


On Fri, Mar 13, 2009 at 11:24 AM, Igor Tandetnik <itandet...@mvps.org> wrote:
> "Gilles Ganault" <gilles.gana...@free.fr> wrote in
> message news:bcqkr45c944gv4g1h9ovpjq1ood1i1v...@4ax.com
>> On Fri, 13 Mar 2009 09:52:25 -0400, "Igor Tandetnik"
>> <itandet...@mvps.org> wrote:
>> Thank you Igor for the help. Before I give it a shot, I need to speciy
>> those requirements:
>> 1. The tables live in two SQLite database files, so I must open both
>> in the same client session
>
> http://sqlite.org/lang_attach.html
>
>> 2. Each table may contain one or more records of the same company
>>
>> The goal is to create a third, new database file where companies are
>> unique, ie. a single record for each company.
>
> .open new.db
> ATTACH 'old1.db' as old1;
> ATTACH 'old2.db' as old2;
>
> create table companies(...);
>
> insert into companies
> select * from old1.companies where rowid in
> (select min(rowid) from old1.companies
>  group by company);
>
> insert into companies
> select * from old2.companies where rowid in
> (select min(rowid) from old2.companies
>  where company not in (select company from companies)
>  group by company);
>
> 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
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] Database inserts gradually slowing down

[Adam DeVita]

Remember the implications of Moore's law and how much time has passed.

CPU speed is much faster than memory speed.
Memory speed is much faster than disk access.

This is why hardware folks play all sorts of tricks with pipelines, caches,
interleaving, and parallelism.

For a single process that interacts with the HDD, the HDD will be the bottle
neck and the CPU will spend lots of time waiting for the rest of the machine
to catch up.  Even if you have a RAID system, the CPU is still much faster
than the bus the hard drives are on.


On Tue, Jun 16, 2009 at 9:52 AM, Jens Páll Hafsteinsson
wrote:

> In step 5 I execute "delete from t1" without any where clause.
>
> I haven't monitored the disk space used (does sqlite use temporary files
> beside the database file?) but the database file itself has been fixed in
> size at around 12MB (12.461.056 bytes) the whole time.
>
> The load on the disk is minimal (sustained 1MB/s) and the CPU load is about
> 6%, which is a bit surprising since I thought I would be putting a huge load
> on the computer running a loop like this. I'm not at all happy to see these
> low load numbers given how the test is programmed (it should practically own
> the machine). The database should utilize the computer much better than
> this.
>
> I've been running the test now for about 10 minutes using 3.6.15 and it
> 'seems' to be behaving as before, slowly increasing in execution time. I
> want to run this a bit longer to be absolutely sure and will let you know.
>
> JP
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:
> sqlite-users-boun...@sqlite.org] On Behalf Of Pavel Ivanov
> Sent: 16. júní 2009 12:15
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Database inserts gradually slowing down
>
> How do you do step 5? Like "delete from table" or "delete from table
> where ..."? Do you see any degrade in disk space used by database
> along with slowness?
>
> Pavel
>
> On Tue, Jun 16, 2009 at 8:06 AM, Jens Páll
> Hafsteinsson wrote:
> > Hi
> >
> > I've been running some tests against sqlite and have found that inserts
> are gradually slowing down. Since I'm new to sqlite I might be doing
> something wrong and thought I'd ask here if anyone has seen this before or
> know what might be causing this.
> >
> > The test I'm running creates a database containing a single table (int,
> int, varchar(100)) along with an index (not unique) on the first field. I
> then perform the following operations (all records have unique data in the
> first field):
> >
> >
> > 1.   start a transaction
> >
> > 2.   insert 1000 records
> >
> > 3.   commit
> >
> > 4.   repeat steps 1-3 100 times
> >
> > 5.   delete everything from the table
> >
> > 6.   Start again at step 1
> >
> > The time taken to execute steps 1-4 increases gradually from taking
> around 16 seconds the first time to just over 28 seconds after about 2400
> iterations. To me, this is alarming since this time increase seems not to be
> asymptotic (not sure though, there is a slight curve on the graph and I
> haven't done any curve fitting) and I fear that I will end up with an
> unusable slow database after some time of use. Even if I shut down the test
> application and start again (on the same database and without re-creating
> the table), it just continues running as before it was stopped, that is,
> taking 28 seconds (and increasing) if I stop it after 2400 iterations.
> >
> > At first I ran this for shorter periods without an index and think I saw
> the same behavior, but I have to verify that to be sure.
> >
> > I'm using sqlite version 3.6.14 on Windows XP, compiled using Visual
> Studio 2008.
> >
> > If anyone is interested I can e-mail the collected data and the test
> program I'm using.
> >
> > Any pointers appreciated.
> >
> > Thanks,
> > JP
> >
> > -
> > "What you take away is more important than what you add." Chuck Moore
> >
> > ___
> > 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
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] listing tables in a database

[Adam DeVita]

SELECT name, sql FROM sqlite_master WHERE type='table'

On Thu, Jun 18, 2009 at 12:47 PM, Gary Johnson wrote:

> I'm looking for a way to programmatically get a list of all tables in
> an SQLite database -- effectively emulating the .tables command but
> using the C API calls.  I've been through the documentation but
> couldn't find anything.  I'm hoping someone on his list might be able
> to point me in the right direction.
>
> Thanks!
> Gary
> ___
> 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] search in archive

[Adam DeVita]

There is a search of archives at
http://www.mail-archive.com/sqlite-users%40sqlite.org/



On Fri, Jun 19, 2009 at 2:43 PM, Kees Nuyt  wrote:

> On Fri, 19 Jun 2009 13:56:52 -0400, "Rizzuto, Raymond"
>  wrote:
>
> > Is it possible to have a search feature for the
> > archive?
>
> Which archive?
>
> I'll assume you have 18 different databases and you want to
> search them in parallel.
>
> > I.e. rather than having to do a linear
> > search through 18 archives for an answer
> > to a question, have a google-like search
> > across all of the archives?
>
> Yes, make your application multithreaded, one thread for the
> user interface and 18 for databases. Every dbthread would
> open a different database.
>
> It will only really help if your system has multiple
> processor cores, and if the databases are each on a
> different disk.
> --
>  (  Kees Nuyt
>  )
> c[_]
> ___
> 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] search in archive

[Adam DeVita]

The link I posted (http://www.mail-archive.com/sqlite-users%40sqlite.org/)
only goes back to March 10, 2009  It searches well but is currently
incomplete. Will it be expanded to include older posts?

http://sqlite.org:8080/cgi-bin/mailman/private/sqlite-users/

goes back much further, but searching is as Raymond described.  Painful,
compared to the nice enter search text here at the mail-archive.com.

http://marc.info/?l=sqlite-users=1=2 also has nice search
capabilities.   Perhaps the http://www.sqlite.org/support.html should be
updated to advertise that the off site archives have search features? A lot
of questions that have already been answered and keep coming back could be
avoided.






On Fri, Jun 19, 2009 at 3:16 PM, Swithun Crowe <
swit...@swithun.servebeer.com> wrote:

> Hello
>
> KN On Fri, 19 Jun 2009 13:56:52 -0400, "Rizzuto, Raymond"
> KN  wrote:
> KN
> KN > Is it possible to have a search feature for the
> KN > archive?
> KN
> KN Which archive?
>
> I think Raymond means the sqlite-users archive.
>
> You could download all the txt.gz files, cat them together and then grep
> for what you want to find. You wouldn't have the threads, but it might be
> easier than checking all the threads via the web site.
>
> Perhaps it is possible to import the files into an email client which can
> recreate the messages and threads.
>
> Swithun.
> ___
> 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] Nested Inner Join Help

[Adam DeVita]

why not use:

SELECT A.ID , A.Column1, A.Column2, B.Column1, C.Column1
FROM A
INNER JOIN B ON A.Column3 = B.ID 
INNER JOIN C ON B.Column2 = C.ID 

?

On Thu, Jul 2, 2009 at 2:53 PM, Ed Hawke <
edward.ha...@hawkeyeinnovations.co.uk> wrote:

> Hi all,
>
> I'm having problems getting nested inner joins to work with SQLite. As
> far as I can tell from various resources the correct way of joining
> multiple tables is this:
>
> SELECT A.ID, A.Column1, A.Column2, B.Column1, C.Column1 FROM A INNER
> JOIN B (INNER JOIN C ON B.Column2 = C.ID) ON A.Column3 = B.ID
>
> However depending upon where I put the parentheses I get various
> different errors from the viewer I use (SQLite Manager for Firefox). A
> normal Inner Join without the nesting works fine.
>
> Can anyone tell me what I'm doing wrong?
>
> Regards,
>
> Ed
> ___
> 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

[sqlite] Optimizing insert or replace speed

[Adam DeVita]

Good day,

Could someone explain where I'm going wrong with this?

I've identified the following query as a bottle neck in a utility I've
written.

insert or replace into main.masterlist select * from delta.masterlist d
where d.write_out_ok=0 and
d.sn not in(select M.sn from main.masterlist M where M.record_updatetime >
d.record_updatetime)

The purpose is to import a data from a  remotely created change file, with
only new/newer records.  (Due to the fact that the subject of the data is
shipping / receiving product serial numbers and that data moves faster than
product there is no way independent nodes can create a change to a record at
the same time.  Also, deleting is not allowed.)

The change file is attached as 'delta'

The structure of masterlist in the main database is:
sqlite> .schema masterlist
CREATE TABLE MasterList (SN int primary key not null, TypeID int default 0
references Product_type_dictionary(TypeID)  , ConstructionDate text, MFGID
int default 0 references MFG_dictionary (MFGID), Init_cust_ship_date text,
record_updatetime text default "2000.00.00.00", write_out_ok int default 0);

CREATE INDEX IDX_MasterList on MasterList (write_out_ok, MFGID, TypeID, SN);

main.masterlist has 36,000 records
deltas.masterlist has 9,000 records

Notes about fields:
write_out_ok is a flag indicating that the record has been imported. States
are 1 or 0.
MFGID is a manufacturer, about 4 different ints can be used.
TypeID is a product Type, about 7 different types,

The index is ordered by cardinality, and all int.
record_updatetime  is the modified date & time GMT (UTC),
.mm.dd.hh.MM.ss



Experimenting with indexes on the delta file with
No indexes:
7 min 22s

CREATE INDEX IDX_MasterList on MasterList ( SN);
14min 52s

CREATE INDEX IDX_MasterList on MasterList (write_out_ok, MFGID, TypeID, SN);
20 min, 07s

---
Dropped indexes on both main and delta.
~20 min.
-

Is the real problem a poor choice of index in main?


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

Re: [sqlite] Optimizing insert or replace speed

[Adam DeVita]

Awesome, brilliant, and decisive!

New times:

No index on Delta File:
3 seconds.

Index on SN:
4 seconds.

Index on MasterList (write_out_ok, MFGID, TypeID, SN);
4 seconds.

The speedup of the one query is greater than this because the above time
figures include
1) A query to see if there are any records in deltas with write_out_ok=0 (if
so, don't execute other queries)
2) A query to update write_out_ok =1 in delta where the record in main
exists and is newer.
3) A query to update write_out_ok =1 in main where the record came from
delta;

1,2, & 3 were negligible compared to the un-optimized
insert or replace into TargetD select * from sourceD sa where
sa.write_out_ok=0 and sa.sn not in (select ta.sn from TargetD ta where
ta.record_updatetime > sa.record_updatetime)   ;

Now, it appears that the time is comparable, so the actual time is in the
order of 2 seconds faster than listed above.  Dropping the sequence time
from 7 min 22s down to 0 minutes 4 seconds is tremendous.

thank you.

Adam


On Tue, Jul 14, 2009 at 2:04 PM, Pavel Ivanov <paiva...@gmail.com> wrote:

> I believe your choice of query is not good enough. Try this one:
>
> insert or replace into main.masterlist
>   select d.*
> from delta.masterlist d left outer join main.masterlist M on d.sn =
> M.sn
>where d.write_out_ok=0
>   and d.record_updatetime >= ifnull(M.record_updatetime, '')
>
>
> Pavel
>
> On Tue, Jul 14, 2009 at 12:54 PM, Adam DeVita<adev...@verifeye.com> wrote:
> > Good day,
> >
> > Could someone explain where I'm going wrong with this?
> >
> > I've identified the following query as a bottle neck in a utility I've
> > written.
> >
> > insert or replace into main.masterlist select * from delta.masterlist d
> > where d.write_out_ok=0 and
> > d.sn not in(select M.sn from main.masterlist M where M.record_updatetime
> >
> > d.record_updatetime)
> >
> > The purpose is to import a data from a  remotely created change file,
> with
> > only new/newer records.  (Due to the fact that the subject of the data is
> > shipping / receiving product serial numbers and that data moves faster
> than
> > product there is no way independent nodes can create a change to a record
> at
> > the same time.  Also, deleting is not allowed.)
> >
> > The change file is attached as 'delta'
> >
> > The structure of masterlist in the main database is:
> > sqlite> .schema masterlist
> > CREATE TABLE MasterList (SN int primary key not null, TypeID int default
> 0
> > references Product_type_dictionary(TypeID)  , ConstructionDate text,
> MFGID
> > int default 0 references MFG_dictionary (MFGID), Init_cust_ship_date
> text,
> > record_updatetime text default "2000.00.00.00", write_out_ok int default
> 0);
> >
> > CREATE INDEX IDX_MasterList on MasterList (write_out_ok, MFGID, TypeID,
> SN);
> >
> > main.masterlist has 36,000 records
> > deltas.masterlist has 9,000 records
> >
> > Notes about fields:
> > write_out_ok is a flag indicating that the record has been imported.
> States
> > are 1 or 0.
> > MFGID is a manufacturer, about 4 different ints can be used.
> > TypeID is a product Type, about 7 different types,
> >
> > The index is ordered by cardinality, and all int.
> > record_updatetime  is the modified date & time GMT (UTC),
> > .mm.dd.hh.MM.ss
> >
> >
> > 
> > Experimenting with indexes on the delta file with
> > No indexes:
> > 7 min 22s
> >
> > CREATE INDEX IDX_MasterList on MasterList ( SN);
> > 14min 52s
> >
> > CREATE INDEX IDX_MasterList on MasterList (write_out_ok, MFGID, TypeID,
> SN);
> > 20 min, 07s
> >
> > ---
> > Dropped indexes on both main and delta.
> > ~20 min.
> > -
> >
> > Is the real problem a poor choice of index in main?
> >
> >
> > 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
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] Optimizing insert or replace speed

[Adam DeVita]

Also, very good.


No index on Delta File:
3 seconds.

Index on SN:
4 seconds.

Index on MasterList (write_out_ok, MFGID, TypeID, SN);
4 seconds.

Time is to the nearest second in my test program, so I can't distinguish
between the two.

In summary:

/*FAST */
insert or replace into main.masterlist select d.*  from delta.masterlist d
left outer join main.masterlist M on d.sn = M.sn
   where d.write_out_ok=0 and d.record_updatetime >=
ifnull(M.record_updatetime, '')

/* just as FAST*/
insert or replace into main.masterlist select * from delta.masterlist d
where d.write_out_ok=0 and
not exists (select 1 from main.masterlist M  where M.sn = d.sn and
M.record_updatetime > d.record_updatetime);

/* very SLOW*/
insert or replace into main.masterlist select * from delta.masterlist d
where d.write_out_ok=0 and
d.sn not in(select M.sn from main.masterlist M where M.record_updatetime >
d.record_updatetime)



On Wed, Jul 15, 2009 at 7:33 AM, Igor Tandetnik <itandet...@mvps.org> wrote:

> Adam DeVita wrote:
> > I've identified the following query as a bottle neck in a utility I've
> > written.
> >
> > insert or replace into main.masterlist select * from delta.masterlist
> > d where d.write_out_ok=0 and
> > d.sn not in(select M.sn from main.masterlist M where
> > M.record_updatetime > d.record_updatetime)
>
> Try this:
>
> insert or replace into main.masterlist
> select * from delta.masterlist d
> where d.write_out_ok=0 and
> not exists (select 1 from main.masterlist M
>  where M.sn = d.sn and M.record_updatetime > d.record_updatetime);
>
> It appears that your query doesn't use an index on M(sn), while mine
> does.
>
> 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
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] Protect against SQL injection inside of the database?

[Adam DeVita]

http://unixwiz.net/techtips/sql-injection.html  is a nice introduction to
sql injection attacks.  (Learning by example) It also explains why binding
is far superior to trying to invent a set of rules and cleaning the input.

.

On Thu, Jul 16, 2009 at 9:01 AM, Michael Schlenker  wrote:

> Fredrik Karlsson schrieb:
> > On Thu, Jul 16, 2009 at 1:20 PM, Michael Schlenker
> wrote:
> >> Your working far too hard. The sqlite Tcl binding already does all thats
> needed.
> >>
> >> This is perfectly safe:
> >> set result [db1 eval {select * from X where label = $myStringValue and
> id >
> >> $compId}]
> >>
> >> But you MUST use {} to quote your query and not "", so sqlite gets to do
> the
> >> substitution (or better said convert things to prepared statements and
> bind
> >> values correctly) and not Tcl.
> >>
> >> Michael
> >
> > Hi Michael,
> >
> > Ok, I can see how this would be the easiest solution, but what I am
> > doing is basically a query builder (maping of comands in a specialized
> > language to pattern subselects in SQL queries). Since the statements
> > can be nested in many different ways, I cannot expect to be able to
> > construct the query and keeping track of variable names to be used in
> > the final substitution, so that I can make use of the built in binding
> > feature of sqlite It is much to much hard work.
> >
>
> I don't think so.
>
> Just use an array to store your values and prefix the names with the
> identifier of your subpattern. Now when you emit your subpattern via
> [format] or some other method just add the appropriate prefixed bind
> variables. Should not be too hard.
>
> > Instead, I think I need to make each part of the query return a
> > complete (not to be evaluated further outside of sqlite) SQL query
> > subselect statement, which is why I think I need to make sure that the
> > values I insert is safe inside an SQL statement myself.
> > Or, do you know of a Tcl command to make strings "SQL safe"? (Sorry
> > for making this into a Tcl question now..)
>
> Its the wrong way. See the mess you get with mysql_real_escape() in PHP and
> you know its wrong.
>
> Michael
>
> --
> Michael Schlenker
> Software Engineer
>
> CONTACT Software GmbH   Tel.:   +49 (421) 20153-80
> Wiener Straße 1-3   Fax:+49 (421) 20153-41
> 28359 Bremen
> http://www.contact.de/  E-Mail: m...@contact.de
>
> Sitz der Gesellschaft: Bremen
> Geschäftsführer: Karl Heinz Zachries, Ralf Holtgrefe
> Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215
> ___
> 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] translating CSV file into sqlite3 database for iPhone?

[Adam DeVita]

This is why I generally advocate TAB delimited files over CSV

Restaurant , Menu Item, Price
Tom, Dick "The MAN", and Harry's Bar & Grill  , Specials /new stuff! Mikey's
Burger "Delishiousness ' ,  $5

If you only have to upload your data once, you should be able to use a
spreadsheet program to convert to TAB delimited rather than going through
the work of writing your own parser.



On Fri, Aug 7, 2009 at 10:43 AM, Wilson, Ron P <
ronald.wil...@tycoelectronics.com> wrote:

> > I'm trying to take a CSV file and create a sqlite3 database for the
> > iPhone.
> > The CSV file has 33K entries and is 2 MB.  The problem I am having is
> that
> > only about 1/10 of the database file gets written into the sqlite3
> > database.
>
> The .import csv method is imperfect; if you have quoted strings in your csv
> that have commas or newlines in them, the import will do surprising things.
>  I had to write my own code to do imports with quoted strings.
>
> RW
>
> Ron Wilson, Engineering Project Lead
> (o) 434.455.6453, (m) 434.851.1612, www.harris.com
>
> HARRIS CORPORATION   |   RF Communications Division
> assuredcommunications(tm)
>
> ___
> 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] Comparing two tables?

[Adam DeVita]

Good day,


Are you looking to simply identify records that are different (not missing
from the tables) or identify records with ANY field different and get the
result?

Is there a primary key? Posting the structure would be helpful.  This should
not be hard.

C:\Documents and Settings\HP_Administrator>sqlite3 adam.db
SQLite version 3.6.10
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .tables
sqlite> create table one (a int, b text);
sqlite> create table two (a int, b text);
sqlite> insert into one (a,b) values (1,'23);
   ...> ');
sqlite> insert into one (a,b) values (2,'23');
sqlite> insert into one (a,b) values (3,'423');
sqlite> insert into one (a,b) values (5,'4423');
sqlite> insert into one (a,b) values (6,'4423');
sqlite> insert into two select * from one;
sqlite> insert into one (a,b) values (4,'3423');
sqlite> insert into one (a,b) values (123,'3423');
sqlite> insert into two (a,b) values (123,'3423');
sqlite> insert into two (a,b) values (1233,'3423');
sqlite> select a,b from one where a not in(select a from two)
   ...> union all
   ...> select a,b from two where a not in(select a from one) ;
4|3423
1233|3423
sqlite>



On Tue, Sep 29, 2009 at 12:38 PM, Petite Abeille
wrote:

>
> On Sep 29, 2009, at 6:32 PM, Joe Bennett wrote:
>
> > Have two tables structured exactly the same. Want to compare both of
> > them and get the delta. Been Googling for about an hour now and I see
> > tools that do this (maybe a freeware one I haven't found?) and was
> > looking for a solution that more meets the budget I was given for this
> > project, zero... Any words of wisdom from the group at large on where
> > to find how to do what I'm looking for or any examples?
>
> Have you consider union/minus/intersect? Very handy. And free.
> ___
> 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] Database Diff libs or applications

[Adam DeVita]

There has been a lot of discussion of this and several of us are doing it.

Are you talking about

A)
DB1 which has modify data
and
DB2 which only receives modifications from DB1 only,

or
B)
DB1 and DB2 both get updates independently and need to be synchronized?

or
C)
  something else Not (A or B)

Your context implies what automated or custom solution you would use.

On Thu, Oct 1, 2009 at 9:44 AM, Shaun Seckman (Firaxis) <
shaun.seck...@firaxis.com> wrote:

> Hello,
>
>I'm looking to externally track the actions made to a
> database so that I can apply those same actions to another database
> (assuming the other database has a similar schema).   I've searched the
> documentation and there doesn't seem to be an easy way to extract this
> data so my only option seems to be utilizing some sort of library or
> application to diff the two databases and generate a SQL script based on
> the changes.
>
>
>
> Has anyone done this before?  Does such a library or application exist?
> I'm sure I could write my own if needed.
>
>
>
> -Shaun
>
>
>
>
>
> ___
> 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] Synchronising multiple copies of a database

[Adam DeVita]

regarding this
" The fault is that
almost nobody does it right: they neglect to keep an 'unaltered
central copy' and think they can cross-apply journals each time two
databases talk to one-another.  That does not work for various reasons."

Would a central repository of journals that can be applied to local
repositories be sufficient?  I suppose I assume that running the same
program on N workstations with the same set of journals should produce N
identical results.



On Wed, Oct 7, 2009 at 12:16 PM, Simon Slavin
wrote:

>
> On 7 Oct 2009, at 1:47pm, Jean-Denis Muys wrote:
>
> > On 10/7/09 11:50 , "Simon Slavin"  wrote:
> >
> >> Try really really hard just to have all sites access your MySQL
> >> database remotely.
> >
> > Unfortunately this approach is not possible in the short term. The
> > client
> > applications are legacy applications, porting them to that scheme is
> > a major
> > undertaking. [snip]
>
> I completely understand.  The recommendation is valuable in the
> general case, but useless in yours.  Still, that's why they pay you
> the big bucks: to write the complicated program.
>
> >> Keep a journal.  Keep an unaltered central copy of the data.  As each
> >> site contacts the central site, play that sites journal back against
> >> the unaltered central copy.  The post-journal central copy of the
> >> database becomes the new copy for distribution.
> >
> > Interesting idea, that makes a lot of sense in the "offline" scenario.
>
> Standard solution to the synchronisation problem.  The fault is that
> almost nobody does it right: they neglect to keep an 'unaltered
> central copy' and think they can cross-apply journals each time two
> databases talk to one-another.  That does not work for various reasons.
>
> The synchronisation service built into Mac OS X (e.g. synchronising
> with online services or an iPhone/iPod) implements it in the correct
> manner.  It takes extra data space and fussy programming but it does
> at least work right !
>
> > [snip] In any case, any book reference on this topic?
>
> Since I joined this list and noticed repeated questions on the subject
> I have been trying hard to find any book with anything significant to
> say on the issue.  I failed: everything I found was lacking in some
> way.  Some were flat-out wrong.  I work at a university and I think
> I'm going to ask the Computing people to find me someone who knows
> this stuff.  I'm just paid to do it in real life, not read or write
> books about it.  If I find something good I'll read it and post here
> about it.
>
> 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] Insert speed greatly decreasing over time

[Adam DeVita]

Would dropping and re-creating an index help?

On Wed, Oct 7, 2009 at 2:19 PM, Alexey Pechnikov wrote:

> Hello!
>
> Try this:
> pragma cache_size=20;
>
> Best regards, Alexey Pechnikov.
> http://pechnikov.tel/
> ___
> 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] Synchronising multiple copies of a database

[Adam DeVita]

"You have to be really careful"
Absolutely.  Even if  you know the order of updates (which I do). If site A
updates an off line record in a cached copy after site B deletes it other
sites can receive the change records in order and have the record re-appear
(via insert or replace).

One can  also get a mess if Mr. Red and Mr Black both get new customers, and
enter them and they both get the same ID because the auto-generated int
happens to be the same. Both copies get updated with the other guy's data,
they then get annoyed and enter the stuff again and it happens over again,
but now there are N entries of the other guy's customer  in the database
depending on how many times they do it.





On Wed, Oct 7, 2009 at 3:18 PM, Simon Slavin <slav...@hearsay.demon.co.uk>wrote:

>
> On 7 Oct 2009, at 7:20pm, Adam DeVita wrote:
>
> > regarding this
> > " The fault is that
> > almost nobody does it right: they neglect to keep an 'unaltered
> > central copy' and think they can cross-apply journals each time two
> > databases talk to one-another.  That does not work for various
> > reasons."
> >
> > Would a central repository of journals that can be applied to local
> > repositories be sufficient?  I suppose I assume that running the same
> > program on N workstations with the same set of journals should
> > produce N
> > identical results.
>
> You need a copy of the database which is not changed by any site.  All
> the sites send in their journals.  The journals are merged into a
> superjournal in time order.  The superjournal is then applied to the
> central copy of the database.  Then the updated database is sent back
> out to all sites.
>
> The problem comes when you apply multiple journals in a different
> order. Start with each site with identical copies of a TABLE with
> three clients: one managed by Mr. Green, one by Mr. Red, and one by
> Mr. Black.  'G R B'.  Then, in this order ...
>
> Mr. Green goes on holiday ...
> Site A says that all Mr. Green's customers will be handled by Mr. Red.
> UPDATE clients SET contact = 'red' WHERE contact = 'green'
>
> Mr. Red goes on holiday ...
> Site B says that all Mr. Red's customers will be handled by Mr. Black.
>
> Then Mr. Green comes back from holiday, and Mr. Black goes on holiday
> so ...
> Site C says that all Mr. Black's customers will be handled by Mr. Green.
>
> Then they all synchronise databases.  See if you can make them all end
> up with the same data if they synch against each-other rather than a
> central unaltered copy of the databases.  Doesn't work: one site might
> have 'B B B', another 'R B R'.  You can do it only by luck ... by
> happening to know in which order people went on holiday.  However, if
> you always synch against a central unaltered copy of the database you
> can synch in any order.  Once everyone has synchronised you distribute
> a copy of the central database to everyone and they all have identical
> data once more.  That's the simplest setup.  You can get more
> complicated by having each site remember which journals they've played
> back.
>
> The problem does not occur if any record can only ever be modified by
> one site.  But if you have the normal 'anyone can do anything' setup,
> you have to be really really careful.
>
> 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] SQLite encription

[Adam DeVita]

One can use a 3rd party tool such as a Alladdin HASP key.  This encrypts the
application, and optionally the database file too.  The drivers for the
program won't execute a program if it detects a debugger.  This solution is
of course limited to operating systems with the available drivers.  Once
nice thing about it is that you can encrypt a copy of the command line tool
for yourself so that you can access the encrypted database.

On Sun, Oct 18, 2009 at 11:25 PM, Roger Binns  wrote:

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> Simon Slavin wrote:
> > It happened again.  DRH explained to me last time I asked.  If someone
> > posts from an address that isn't on this list, there's a delay before
> > the post shows up because it waits for moderator approval.  In the
> > meantime the original poster often posts the same question again.
>
> It didn't happen again - yes I checked before responding.  The original
> post
> made it to the list several days ago.  This post was a brand new one - look
> at the headers and you can see.
>
> Roger
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.9 (GNU/Linux)
> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
>
> iEYEARECAAYFAkrb3BkACgkQmOOfHg372QTwZgCgoUnLkoaB0jEgCiGd0kAvi+pH
> oQIAoLc/iTrQ3oP6HIbMo/7frlOS5RTo
> =WQYU
> -END PGP SIGNATURE-
> ___
> 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] .import on a .csv file

[Adam DeVita]

Do you have any new lines, returns, or tabs in any of the real data?  Can
you prove it?

Is this a 1 off thing or are you going to do this routinely?

There has been a lot of discussion on this list about importing csv data and
the hardships of writing a good csv importer.

If this is a one off, some possible tricks:

1) If there are no newlines or tabs inside the data, perhaps you can run
away from your embedded delimiter by changing the delimiter to a tab?
2) Have you considered using Access, XL, or open office  to see if you can
get a clean import into there?  This may allow you to save into another
format or...
2b) Use the spreadsheet to create the sql you want to import.

The merits of various solutions can be found by searching the archive.

Go to
http://www.mail-archive.com/sqlite-users@sqlite.org/info.html

and search on:
Tedious CSV import question This was a good discussion.



On Thu, Oct 22, 2009 at 12:16 PM, Scott Baker  wrote:

> I'm trying to .import a CSV file and I can't quite figure out the syntax.
>
> I created a table, and then did:
>
> .separator ,
> .import /tmp/foo.csv mytable
>
> This works sort of, unless my data has , in it. Something like "last,
> first". Because it tries to split at that , and then the number of rows
> doesn't match my table.
>
> Then I tried setting the separator to
>
> .separator \",\"
>
> Which works correctly (i.e. it splits the data properly). However, now my
> first and last columns have " on the beginning/end of them. Is there a way
> to import a well formed CSV.
>
> My CSV data looks like this:
>
> "38665","101977","Deadly Sparrows Inc.","1435 S. Doolis
> Ln","Donkville","OR","90210","Doolis, Jason","5032349422","Active"
>
> Help!
>
> --
> Scott Baker - Canby Telcom
> System Administrator - RHCE - 503.266.8253
> ___
> 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

[sqlite] Windows 7?

[Adam DeVita]

Good day,

Will a new DLL be required for Windows 7 ?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Ideal way to check whether a table has a specific column

[Adam DeVita]

Another way

SELECT name, sql FROM sqlite_master WHERE type='table' and name ='yourtable
name'

the field sql will give you the full table structure as a string.  Parse for
your desired table name.

No statements fail.




On Tue, Nov 3, 2009 at 4:37 PM, Igor Tandetnik  wrote:

> Shaun Seckman (Firaxis)
>  wrote:
> >I just wanted to find out whether or not this is the
> > most ideal method for determining if a column exists in a table.  My
> > current technique is to do execute "pragma table_info(tableName)" then
> > step through the results and perform a string comparison against the
> > "name" column until I hit a match or I've finished stepping through
> > the record set.
> >
> > Is there a better way?
>
> I guess you could just prepare a statement "select mycolumn from mytable;"
> (you don't need to actually run it). If the column doesn't exist, prepare
> will fail.
>
> 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
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] Location of Sqlite Files

[Adam DeVita]

1) save the db wherever is appropriate for your application. (network drives
are generally considered to be a bad idea)
2) An sqllite db is a file. There are lots of ways to delete files

Adam


On Fri, Nov 27, 2009 at 2:54 PM, mr_orange  wrote:

>
> Hey, I am kind of new to SQLite. I was wondering if anyone could help me
> with
> a couple things:
> 1) Where are the SQLite databases stored? I was told in the C:\WINDOWS\temp
> folder, but I can't seem to find any SQLite-related files there.
> 2) How do you delete a SQLite database?
>
> Any help is much appreciated, thank you.
> --
> View this message in context:
> http://old.nabble.com/Location-of-Sqlite-Files-tp26545375p26545375.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] Archive Search Engine

[Adam DeVita]

But there is a search engine on:

http://www.mail-archive.com/sqlite-users%40sqlite.org/info.html

It is right at the top.

Adam

On Tue, Dec 29, 2009 at 12:02 PM, Bill Marvin wrote:

>
> It would be very helpful if there was a search engine for the sqlite-user
> mailing list archive.  My question might have already been answered, but
> currently using the archive I have to manually look through the threads
> month by month.  It is like finding a needle in a haystack!
>
> Bill
>
>
> _
> Your E-mail and More On-the-Go. Get Windows Live Hotmail Free.
> http://clk.atdmt.com/GBL/go/171222985/direct/01/
> ___
> 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] Confusing FAQ(26) wording

[Adam DeVita]

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

seems to clarify things for me on this topic.

Adam

On Wed, Dec 30, 2009 at 9:13 AM, Tobias Hoffmann <
lsqlite-l...@thax.hardliners.org> wrote:

> Hi,
>
> I had a hard time to understand the FAQ entry on UNIQUE constraint -- in
> the end I had to try out sqlite's behavior myself because the FAQ  -- so
> maybe the wording can be improved and/or an example added.
>
> Here a some comments:
> > *(26) The SQL standard requires that a UNIQUE constraint be enforced
> > even of one or more of the columns in the constraint are NULL, but
> > SQLite does not do this. Isn't that a bug?*
> So this seems to imply that two NULL values will not violate the
> UNIQUEness of two rows in SQlite. [Btw. shouldn't it be "... enforced
> even IF one or ..."?]
> >
> > Perhaps you are referring to the following statement from SQL92:
> >
> > A unique constraint is satisfied if and only if no two rows in
> > a table have the same non-null values in the unique columns.
> >
> IMHO: as they did't just write  ".. have the same values in the unique
> columns", the database should only compare those columns that are
> non-null when enforcing uniqueness. (just as above -- and as SQlite does
> it).
> >
> > That statement is ambiguous, having at least two possible
> > interpretations:
> >
> Now the confusion begins.
> >
> >1. A unique constraint is satisfied if and only if no two rows
> >   in a table have the same values and have non-null values in
> >   the unique columns.
> >
> Shall this mean something like (parenthesis to show parsing precendence)
>  (no two rows in the table have the same values) and ([they] have
> non-null values) ...[after some time I realized: this does not make
> much sense. But how else was it meant?]
> or
>  no two rows in a table have (the same values and have non-null values)
> in the unique columns.[maybe removing the second "have" would help]
> >
> >2. A unique constraint is satisfied if and only if no two rows
> >   in a table have the same values in the subset of unique
> >   columns that are not null.
> >
> So you compare only those columns that are not NULL, right?
> Where is the difference to (1)? [this made understanding (1) even more
> difficult to me].
> And why does the following paragraph state that that SQLite does not
> follow this interpretation, although it seems that this is the
> unexpected behavior in the original question?
> >
> > SQLite follows interpretation (1), as does PostgreSQL, MySQL,
> > Oracle, and Firebird. It is true that Informix and Microsoft SQL
> > Server use interpretation (2), however we the SQLite developers
> > hold that interpretation (1) is the most natural reading of the
> > requirement and we also want to maximize compatibility with other
> > SQL database engines, and most other database engines also go with
> > (1), so that is what SQLite does.
> >
> After all I tried with SQLite and found out that you can have two rows
> with NULL in the same (unique-constraint) column.
> But I'm not sure if this is really the point of the question, as I still
> haven't understood (2) [and don't have MSSQL to test] - or whether its
> [wild guess:] about certain behavior with multi-column indices.
>
> If this is clear to everybody except me, I would appreciate a hint...
> otherwise please consider clarifying this FAQ.
>
>  Tobias
>
> ___
> 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] Limitation on Column count

[Adam DeVita]

Good day,

In general I try to work within the limits of any database engine that I am
using.  Often, the limits are there for good reasons (such as speed
problems).  I would suggest seeing if there is a way to normalize the big
tables such that infrequently used columns are split into tables that aren't
joined in often.  (The principal I'm using is borrowed from hardware
architecture "Make the common case fast, and ensure the uncommon case is
correct.")

It may or may not be sensible given your data, but there may be an
opportunity to reduce the number of columns  by making an encoded column to
aggregate, such as lots of mutually exclusive binary flag fields.

regards,
Adam

On Mon, Jan 11, 2010 at 3:46 PM, Stefan_E  wrote:

>
> Hello all,
>
> I'm considering using SQLite for a new application. The schema will contain
> a bunch of small tables with few columns (~10) plus one large table with
> many columns and 1000...1 rows.
>
> 'Many columns' typically fits into the default 2000 column limit, but can
> exceed it at times (that is, on some of the foreseen databases). It will
> never exceed the theoretical / compile time selectable limit of 32k
> columns.
> Queries on this big table will be rather straight-forward: either on the
> table alone (SELECT * FROM table_large) or one join on one field to one of
> the smaller tables.
>
> The  http://www.sqlite.org/limits.html Limits page  warns: "There are
> places
> in the SQLite code generator that use algorithms that are O(N²) where N is
> the number of columns." which is kind of discouraging to increase max.
> column count at compile time, but is not very specific about when this
> happens...
>
> I now have two design options:
> - increase max. column count at compile time (possibly setting
> SQLITE_LIMIT_COLUMN on databases where  I don't need more than (say) 1000
> columns) and accept the quoted performance degradation.
> - alternatively, in the client handle cases with more than 2000 columns,
> splitting the storage up into two (or more) tables
>
> Any advise, experience - or more specifics on the "O(N²)" remark are highly
> welcome!
>
> Thanks for your help - Stefan
> --
> View this message in context:
> http://old.nabble.com/Limitation-on-Column-count-tp27117364p27117364.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] Limitation on Column count

[Adam DeVita]

One may be able to make "measurement type" a column, thus eliminating the
need for a column for each type.

Some speed may be recoverable with indexing.

regards,
Adam

On Tue, Jan 12, 2010 at 1:21 PM, Pavel Ivanov <paiva...@gmail.com> wrote:

> > so normalization would lead to a doubling
> > of the storage space (add a measurement_id to each measurement).
>
> My strong belief is that when you try this normalization you'll see
> that such doubling of storage is a good enough trade-off for the speed
> you'll achieve. I don't think that speed of queries on the table with
> 100+ columns would be any useful (of course unless you *always* select
> all columns and *never* try to select only a few ones).
>
> > Second, the most common use case is to view the table in the currently
> > foreseen format - so, I'd pay both in space and time...
>
> Most probably you view your table from your application which can
> denormalize the table very quickly. Even if you view your table from
> sqlite3 command line tool you still can write denormalizer even using
> bash scripts and I believe it will still work fast enough and it will
> be better than creating such huge table.
>
>
> Pavel
>
> On Tue, Jan 12, 2010 at 1:09 PM, Stefan_E <se_m...@hotmail.com> wrote:
> >
> > Hi Adam,
> >
> > thanks for your suggestion. Unfortunately, it doesn't help in my case.
> > Essentially, we are talking about a time series (rows) of n different
> > measurements (columns) - so normalization would lead to a doubling
> > of the storage space (add a measurement_id to each measurement).
> >
> > Second, the most common use case is to view the table in the currently
> > foreseen format - so, I'd pay both in space and time...
> >
> > Anyway, thanks for the suggestion!
> >
> > Regards, Stefan
> >
> >
> > Adam DeVita wrote:
> >>
> >> Good day,
> >>
> >> In general I try to work within the limits of any database engine that I
> >> am
> >> using.  Often, the limits are there for good reasons (such as speed
> >> problems).  I would suggest seeing if there is a way to normalize the
> big
> >> tables such that infrequently used columns are split into tables that
> >> aren't
> >> joined in often.  (The principal I'm using is borrowed from hardware
> >> architecture "Make the common case fast, and ensure the uncommon case is
> >> correct.")
> >>
> >> It may or may not be sensible given your data, but there may be an
> >> opportunity to reduce the number of columns  by making an encoded column
> >> to
> >> aggregate, such as lots of mutually exclusive binary flag fields.
> >>
> >> regards,
> >> Adam
> >>
> >> On Mon, Jan 11, 2010 at 3:46 PM, Stefan_E <se_m...@hotmail.com> wrote:
> >>
> >>>
> >>> Hello all,
> >>>
> >>> I'm considering using SQLite for a new application. The schema will
> >>> contain
> >>> a bunch of small tables with few columns (~10) plus one large table
> with
> >>> many columns and 1000...1 rows.
> >>>
> >>> 'Many columns' typically fits into the default 2000 column limit, but
> can
> >>> exceed it at times (that is, on some of the foreseen databases). It
> will
> >>> never exceed the theoretical / compile time selectable limit of 32k
> >>> columns.
> >>> Queries on this big table will be rather straight-forward: either on
> the
> >>> table alone (SELECT * FROM table_large) or one join on one field to one
> >>> of
> >>> the smaller tables.
> >>>
> >>> The  http://www.sqlite.org/limits.html Limits page  warns: "There are
> >>> places
> >>> in the SQLite code generator that use algorithms that are O(N²) where N
> >>> is
> >>> the number of columns." which is kind of discouraging to increase max.
> >>> column count at compile time, but is not very specific about when this
> >>> happens...
> >>>
> >>> I now have two design options:
> >>> - increase max. column count at compile time (possibly setting
> >>> SQLITE_LIMIT_COLUMN on databases where  I don't need more than (say)
> 1000
> >>> columns) and accept the quoted performance degradation.
> >>> - alternatively, in the client handle cases with more than 2000
> columns,
> >>> splitting the storage up into two (or more) tables
> >>>
> >&g

Re: [sqlite] Newbie problem using special column name

[Adam DeVita]

Create the table using single quotes around the strange name.


sqlite> create table x (boomer int, 'squid-nick' text);
sqlite> insert into x values (1,'asdlh');
sqlite> select * from x;
1|asdlh
sqlite> select squid-nick from x;
SQL error: no such column: nick
sqlite> select 'squid-nick' from x;
squid-nick
/*oops, I selected the string not a column name. */
sqlite> select x.'squid-nick' from x;  /*reference the table name before the
column*/
asdlh


As an aside: If you realize that the database name for a column doesn't have
to be the same as the user friendly name in your user interface, you don't
have to use keywords or special characters as column names.


On Mon, Jan 25, 2010 at 9:18 AM, Patrick Ben Koetter 
wrote:

> Can I add a column name containing a dash "-" and if yes, how would I do
> that?
>
> I am asking because I fail to add a column name that contains a dash "-"
> and I
> don't know if I cause the problem (easy solution) or if its something else
> causing this to fail.
>
> Here's what I try:
>
>  sqlite> create table test(column-1 varchar(255));
>  SQL error: near "-": syntax error
>
> So far I have had a look at the SQLite documentation, but couldn't find
> anything that would tell me about 'reserved' characters or how I would
> escape
> a dash.
>
> Thanks,
>
> p...@rick
>
> --
> state of mind
> Digitale Kommunikation
>
> http://www.state-of-mind.de
>
> Franziskanerstraße 15  Telefon +49 89 3090 4664
> 81669 München  Telefax +49 89 3090 4666
>
> Amtsgericht MünchenPartnerschaftsregister PR 563
> ___
> 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] The character "'" not liked by sqlite?

[Adam DeVita]

Good day,
If you look in http://www.sqlite.org/capi3ref.html#sqlite3_bind_blob
for the function

int sqlite3_bind_text(sqlite3_stmt*, int, const char*, int n, void(*)(void*));

This will allow you to bind any character into an SQL statement.
There are other benefits to using this technique.

regards,
Adam


On Tue, Mar 2, 2010 at 2:37 PM, Kavita Raghunathan <
kavita.raghunat...@skyfiber.com> wrote:

> Simon and Gabriel,
>
> I'm using the C API, I'm inserting strings. One of the strings happens to
> have an "'" in it. I have to write extra code to parse the character and
> escape it, I'll do that if I have to. I have not tried the command line
> tool. I'll try it and get back to you.
>
> Kavita
>
> On 3/2/10 12:56 PM, "Simon Slavin"  wrote:
>
> >
> > On 2 Mar 2010, at 6:51pm, Kavita Raghunathan wrote:
> >
> >> I notice that when I try to insert the character ³¹² as part of a string
> into
> >> the sqlite database, my updates don¹t work. Any ideas why? The same
> string
> >> without the ³¹² character works. I have not debugged to see where
> exactly in
> >> sqlite it fails.
> >>
> >> I¹m inserting a text like this: ³Rootuser¹s desktop² does not work.
> ³Rootuser
> >> desktop² works, the update to database suceeds and I¹m able to view it
> using
> >> select.
> >
> > What API or toolkit are you using ?  Have you tried executing the same
> command
> > with the command-line tool ?
> >
> > 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
>



-- 
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] Question about binding

[Adam DeVita]

Good day,

For the sake of fun, I have to share this - especially with tall this talk
of binding all the parameters.

void poem(CString pth)
{

sqlite3_stmt *ppStmt; //statement pointer
sqlite3 *db; //database
const char *pzTail;
char *pzerr;

if( sqlite3_open(pth, ) ){
printf("Can't open database!" );
sqlite3_close(db);
return ;
}

CString csql;
csql.Format("Create table if not exists poem (verseno integer primary
key, rings int, location text)");   //sets the string.
int status = sqlite3_prepare_v2(db,csql, /* SQL statement, UTF-16
encoded */   csql.GetLength(),  /* Maximum length of zSql in bytes. */
,  /* OUT: Statement handle */
  /*  OUT: Pointer to unused portion of zSql  which
I'm not going to use because I want to bind things different ways.*/ );

if (status != SQLITE_OK){
printf("something is wrong, shame, shame, shame. ");
sqlite3_close(db);
return ;
}
sqlite3_step(ppStmt);
sqlite3_finalize(ppStmt);


int rings[5] ={3 , 7 ,9,1 ,1};
CString verse1 =_T(" for elvin kings, under the sky");
CString verse2 =_T(" for dwarf lords, in their halls of stone");
CString verse3 =_T(" for mortal men, doomed to die");
CString verse4 =_T(" for the dark lord, on his dark throne ");

csql.Format(" insert into poem (rings, location) values (?, ?) ");
status = sqlite3_prepare_v2(db,csql, /* SQL statement, UTF-16 encoded
*/   csql.GetLength(),  /* Maximum length of zSql in bytes.
*/,  /* OUT: Statement handle */
  /*  OUT: Pointer to unused portion of zSql */ );

if (status != SQLITE_OK){
printf("something is wrong, like %d",status);
sqlite3_close(db);
return ;
}
int ring_verse =0;
sqlite3_bind_int (ppStmt, 1, rings[ring_verse]);
sqlite3_bind_text(ppStmt, 2, verse1, verse1.GetLength(), SQLITE_STATIC);
sqlite3_step(ppStmt);
ring_verse++;



csql.Format(" insert into poem (rings, location) values (?002, ?001)
");
status = sqlite3_prepare_v2(db,csql, /* SQL statement, UTF-16 encoded
*/csql.GetLength(),  /* Maximum length of zSql in bytes. */
,  /* OUT: Statement handle */
  /*  OUT: Pointer to unused portion of zSql */ );

if (status != SQLITE_OK){
printf("something is wrong %d",status);
sqlite3_close(db);
return ;
}
sqlite3_bind_text(ppStmt, 1, verse2, verse2.GetLength(), SQLITE_STATIC);
sqlite3_bind_int (ppStmt, 2, rings[ring_verse]);
sqlite3_step(ppStmt);
ring_verse++;

csql.Format(" insert into poem (rings, location) values ($ringy, :versy)
");
status = sqlite3_prepare_v2(db,csql, /* SQL statement, UTF-16 encoded */
csql.GetLength(),  /* Maximum length of zSql in bytes. */
,  /* OUT: Statement handle */
  /*  OUT: Pointer to unused portion of zSql */ );

if (status != SQLITE_OK){
printf("something is wrong %d",status);
sqlite3_close(db);
return ;
}
sqlite3_bind_text(ppStmt, sqlite3_bind_parameter_index(ppStmt,
":versy"), verse3, verse3.GetLength(), SQLITE_STATIC);
sqlite3_bind_int (ppStmt, sqlite3_bind_parameter_index(ppStmt,
"$ringy"), rings[ring_verse]);
sqlite3_step(ppStmt);
ring_verse++;



CString csrepeated =_T("\nin the land of Mordor, where the shadows
lie.");
csql.Format(" insert into poem (rings, location) values (@ringy,  :versy
|| :repeats) ");
status = sqlite3_prepare_v2(db,csql, /* SQL statement, UTF-16 encoded */
csql.GetLength(),  /* Maximum length of zSql in bytes. */
,  /* OUT: Statement handle */
  /*  OUT: Pointer to unused portion of zSql */ );

if (status != SQLITE_OK){
printf("something is wrong %d",status);
sqlite3_close(db);
return ;
}
sqlite3_bind_text(ppStmt, sqlite3_bind_parameter_index(ppStmt,
":repeats"), csrepeated, csrepeated.GetLength(), SQLITE_STATIC);
sqlite3_bind_text(ppStmt, sqlite3_bind_parameter_index(ppStmt,
":versy"), verse4, verse4.GetLength(), SQLITE_STATIC);
sqlite3_bind_int (ppStmt, sqlite3_bind_parameter_index(ppStmt,
"@ringy"), rings[ring_verse]);
sqlite3_step(ppStmt);
ring_verse++;

//and finally


verse3.Format(" to bring them all and in the darkness BIND them ");
csql.Format(" insert into poem (rings, location) values (@ringy, ' ring
to rule them all '|| @ringy ||' ring to find them, '|| @ringy ||:versy ||
:repeats) ");
status = sqlite3_prepare_v2(db,csql,csql.GetLength(), ,
   );

if (status != SQLITE_OK){
printf("something is wrong %d",status);
sqlite3_close(db);
return ;
}
sqlite3_bind_text(ppStmt, sqlite3_bind_parameter_index(ppStmt,
":repeats"), csrepeated, csrepeated.GetLength(), SQLITE_STATIC);
sqlite3_bind_text(ppStmt, 

Re: [sqlite] Performance problem with count(*) calculation

[Adam DeVita]

How does
$ time sqlite3 test32k.db "select count(1) from role_exist"

perform?

On Thu, Apr 1, 2010 at 5:52 AM, Alexey Pechnikov wrote:

> Hello!
>
> $ time sqlite3 test32k.db "select count(*) from role_exist"
> 1250
>
> real0m58.908s
> user0m0.056s
> sys 0m0.864s
>
> $ sqlite3 test32k.db
> SQLite version 3.6.23
> sqlite> .schema role_exist
> CREATE TABLE role_exist (
>  id INTEGER PRIMARY KEY,
>  uid BLOB NOT NULL DEFAULT (randomblob(16)) UNIQUE ON CONFLICT REPLACE
> );
>
> So 58s for count of all records! The count(*) for all records may use
> the counter from primary key b-tree, is't it?
> ==
> HARDWARE:
>
> $ grep CPU /proc/cpuinfo
> model name  : Intel(R) Core(TM)2 Quad CPUQ6700  @ 2.66GHz
> model name  : Intel(R) Core(TM)2 Quad CPUQ6700  @ 2.66GHz
> model name  : Intel(R) Core(TM)2 Quad CPUQ6700  @ 2.66GHz
> model name  : Intel(R) Core(TM)2 Quad CPUQ6700  @ 2.66GHz
>
> $ free
> total   used   free sharedbuffers cached
> Mem:   83108927552880 758012  0  294966667708
> -/+ buffers/cache: 8556767455216
> Swap:  3903784 3012403602544
>
> Best regards, Alexey Pechnikov.
> http://pechnikov.tel/
> ___
> 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] SQLite parsing of a .sql file

[Adam DeVita]

Is this a 1 off import?  If so, perhaps the command line tool can .read it.

On Fri, Apr 9, 2010 at 2:13 PM, Shaun Seckman (Firaxis) <
shaun.seck...@firaxis.com> wrote:

> Greetings,
>
> I've got a .SQL file that contains multiple SQL insert statements for
> various tables as well as comments embedded throughout.  When I
> attempted to pass the file into sqlite3_exec, I found that only the
> first SQL command was getting executed.  What is the best way to ensure
> that all commands are executed?   Parsing the file line-by-line is
> inaccurate as multiple statements may be on the same line and looking
> for the next ';' character has parsing problems as well.
>
>
>
> The documents state that sqlite3_complete() only returns a 1 if the
> statement is complete.  Were this method to return the index into the
> character array to denote WHERE the SQL statement is complete, I could
> use that to parse multiple statements.
>
>
>
> Does anyone have any suggestions?
>
>
>
> -Shaun
>
>
>
>
>
> ___
> 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