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&r=1&w=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  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 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  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 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

[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
 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
>  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  wrote:
> "Gilles Ganault"  wrote in
> message news:bcqkr45c944gv4g1h9ovpjq1ood1i1v...@4ax.com
>> On Fri, 13 Mar 2009 09:52:25 -0400, "Igor Tandetnik"
>>  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] 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  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  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  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 mo

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, &db) ){
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. */
&ppStmt,  /* OUT: Statement handle */
&pzTail  /*  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.
*/&ppStmt,  /* OUT: Statement handle */
&pzTail  /*  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. */
&ppStmt,  /* OUT: Statement handle */
&pzTail  /*  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. */
&ppStmt,  /* OUT: Statement handle */
&pzTail  /*  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. */
&ppStmt,  /* OUT: Statement handle */
&pzTail  /*  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(), &ppStmt,
&pzTail   );

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.GetLengt

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

[sqlite] Date Dimension

[Adam DeVita]

Good day,

Given the context I'm in, sqlite is going to be used for our data
warehousing.  (We generate about 2MB of raw data in a month, so we don't
think we need a heavy DB engine.)

Since most warehouses have one, which are very similar from application to
application, I'm wondering if  there is somewhere to download a pre-defined
Date Dimension?  I could write my own script, but re-invent and debug the
wheel?

regards,
Adam

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

[Adam DeVita]

Yes.

A Date dimension  is a table that has all possible dates for your
data, thus making reporting on properties of the date easy.  Something
like this

CREATE TABLE Date_dimension (
 DateID int NOT NULL , /*an int key to match up to date fields in fact
storage tables*/
 [Date] datetime NOT NULL,
 [Year] int NOT NULL,
 [Month] int NOT NULL,
 [Day] int NOT NULL,
 [QuarterNumber] int NOT NULL,
 [DayofWeek_name] text,
 [Month_name] text,

)



On Wed, Apr 21, 2010 at 3:25 PM, Pavel Ivanov  wrote:

> > What is a "Date Dimension"?
>
> Probably OP meant this:
> http://en.wikipedia.org/wiki/Dimension_(data_warehouse)<http://en.wikipedia.org/wiki/Dimension_%28data_warehouse%29>
> .
> But I don't have any answer to the question asked.
>
>
> Pavel
>
> On Wed, Apr 21, 2010 at 3:21 PM, P Kishor  wrote:
> > On Wed, Apr 21, 2010 at 1:50 PM, Adam DeVita 
> wrote:
> >> Good day,
> >>
> >> Given the context I'm in, sqlite is going to be used for our data
> >> warehousing.  (We generate about 2MB of raw data in a month, so we don't
> >> think we need a heavy DB engine.)
> >>
> >> Since most warehouses have one, which are very similar from application
> to
> >> application, I'm wondering if  there is somewhere to download a
> pre-defined
> >> Date Dimension?
> >
> > What is a "Date Dimension"? For SQLite's date time functions, see
> > http://www.sqlite.org/lang_datefunc.html
> >
> >>  I could write my own script, but re-invent and debug the
> >> wheel?
> >>
> >> regards,
> >> Adam
> >>
> >> --
> >> 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
> >>
> >
> >
> >
> > --
> > Puneet Kishor http://www.punkish.org
> > Carbon Model http://carbonmodel.org
> > Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
> > Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
> > Nelson Institute, UW-Madison http://www.nelson.wisc.edu
> > ---
> > Assertions are politics; backing up assertions with evidence is science
> > ===
> > ___
> > 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] Date Dimension

[Adam DeVita]

Good day,

I've been looking at doing that, but am having problems converting
backwards.

The idea of a date dimension is to have one row for every possible date in
the time span of interest

For example, I'm tracking product histories, so I know that there will be no
activity before January 1, 1990 (a date well before manufacture of the first
product)

and I won't care about what happens well into the future retire (say around
year 2030   )

This yeilds (20+20)*365.25 =14,610 maximum potential rows.  If all the
possibilities are pre-calculated, then if a user wants to express a roll up
of the facts by any grouping

select count(somthing_interesting) , dd.day_of_week
from fact_table ft
inner join date_dimension dd on ft.dateid = dd.dateid
group by dd,day_of_week

Then
a) the user doesn't have to worry about converting dates by some group
because it is all done for them.
b) we store the date of the event in our fact_table as an int
c) we don't have to run much of calculation of dates, just a join.

(I've been reading "The Data Warehoust Toolkit, Second Edition  by Kimball
and Ross)


If I have to generate the date dimension on my own, I'm hoping to use
something like
create table date_dimension (
 [Dateid] integer primary key,
 [Real_Year] int ,
 [Month_name] text,
 [Day] int ,
 [QuarterNumber] int,
 [DayofWeek_name] text,
 [dayofYear] int,
 [epoch_day] int,
 [julian_day] int
 );

/*populate some an auto increment so that all days are covered even if I
have to write a loop doing*/
 insert into date_dimension( epoch_day) select count(epoch_day) from
date_dimension;

/*then */
 update date_dimension set julian_day = julianday('now') -
julianday('1990-01-01') + epoch_day;

/* then  uh some query that  updates the table containing the julian
date of every day from Jan 1, 1990 through 2030, and  fill in the month
name, year, quarter in nice user friendly strings.*/

Is this approach better than generating a list of date strings for all
possible dates, throwing away the Feb 29s from non leap years, and then
parsing the string to get ye year, month, day, day of year, age from epoch,
month name, etc?


regards,
Adam




On Wed, Apr 21, 2010 at 3:59 PM, P Kishor  wrote:

> On Wed, Apr 21, 2010 at 2:37 PM, Adam DeVita  wrote:
> > Yes.
> >
> > A Date dimension  is a table that has all possible dates for your
> > data, thus making reporting on properties of the date easy.  Something
> > like this
> >
> > CREATE TABLE Date_dimension (
> >  DateID int NOT NULL , /*an int key to match up to date fields in fact
> > storage tables*/
> >  [Date] datetime NOT NULL,
> >  [Year] int NOT NULL,
> >  [Month] int NOT NULL,
> >  [Day] int NOT NULL,
> >  [QuarterNumber] int NOT NULL,
> >  [DayofWeek_name] text,
> >  [Month_name] text,
> >
> > )
> >
>
>
> methinks you can calculate all of the above storing your dates as
> strings in a single column, and using the date time functions on that
> column. Check out the functions in the link I sent you.
>
> >
> >
> > On Wed, Apr 21, 2010 at 3:25 PM, Pavel Ivanov 
> wrote:
> >
> >> > What is a "Date Dimension"?
> >>
> >> Probably OP meant this:
> >> http://en.wikipedia.org/wiki/Dimension_(data_warehouse)<http://en.wikipedia.org/wiki/Dimension_%28data_warehouse%29>
> <http://en.wikipedia.org/wiki/Dimension_%28data_warehouse%29>
> >> .
> >> But I don't have any answer to the question asked.
> >>
> >>
> >> Pavel
> >>
> >> On Wed, Apr 21, 2010 at 3:21 PM, P Kishor  wrote:
> >> > On Wed, Apr 21, 2010 at 1:50 PM, Adam DeVita 
> >> wrote:
> >> >> Good day,
> >> >>
> >> >> Given the context I'm in, sqlite is going to be used for our data
> >> >> warehousing.  (We generate about 2MB of raw data in a month, so we
> don't
> >> >> think we need a heavy DB engine.)
> >> >>
> >> >> Since most warehouses have one, which are very similar from
> application
> >> to
> >> >> application, I'm wondering if  there is somewhere to download a
> >> pre-defined
> >> >> Date Dimension?
> >> >
> >> > What is a "Date Dimension"? For SQLite's date time functions, see
> >> > http://www.sqlite.org/lang_datefunc.html
> >> >
> >> >>  I could write my own script, but re-invent and debug the
> >> >> wheel?
> >> >>
> >> >> regards,
> >> >> Adam
> >> >>
> >> >> --
> >> >> VerifEye Technologies Inc.
> &g

Re: [sqlite] Date Dimension

[Adam DeVita]

good ideas.

The spread sheet trick hadn't occurred to me.  I think I'll go that route
since it keeps things user readable

thank you for your thoughts, all.

regards,
Adam

On Thu, Apr 22, 2010 at 2:51 AM, Oliver Peters  wrote:

>
> Adam DeVita  writes:
>
> >
> [...]
> >
> > If I have to generate the date dimension on my own, I'm hoping to use
> > something like
> > create table date_dimension (
> >  [Dateid] integer primary key,
> >  [Real_Year] int ,
> >  [Month_name] text,
> >  [Day] int ,
> >  [QuarterNumber] int,
> >  [DayofWeek_name] text,
> >  [dayofYear] int,
> >  [epoch_day] int,
> >  [julian_day] int
> >  );
> >
>
> [...]
>
> Why don't you simply use a spreadsheet program like OpenOfice Calc or Excel
> to
> prepare the table data for your fixed timespan (2010 - 2030) and import the
> whole thing?
>
> Would be a work of a few minutes ;-)
>
> 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] column output truncating

[Adam DeVita]

Could you include a bit more information about your post? (Version number,
operating system etc.)

I'm unsure if you have compiled something or are using the command line
tool.

 There are lots of very knowledgeable and helpful people on the list.

On Thu, Apr 22, 2010 at 6:31 PM, Derek Martin  wrote:

> Hi,
>
> I have a query that produces about 10 columns, some of which are very
> wide.  When I run sqlite3 with -column -header, it truncates all
> fields to 10 characters.  This makes the query absolutely useless.
> How can I turn off this truncation?
>
> I already tried explicitly setting the width to 0, that did not help.
>
> Thanks.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
VerifEye Technologies Inc.
905-948-0015x245
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] WHERE = does not work

[Adam DeVita]

Is it possible there is a null, tab, newline or other invisible character?
Try

select timeStamp, '' || resourceType || 'xx'  From MyTable where
resourceType like 'PSM' LIMIT 10;

On Fri, Apr 30, 2010 at 9:53 AM, ecforu  wrote:

> I don't think it is a case issue.  See below from sqlite3 command line.
> Also one thing to note - I build the database from c API.  I don't know if
> that makes a difference.
>
> sqlite>
> sqlite> select timeStamp, resourceType From MyTable where resourceType like
> 'PSM' LIMIT 10;
> timeStamp|resourceType
> 2010-04-28 17:46:45.316|PSM
> 2010-04-28 17:46:49.854|PSM
> 2010-04-28 17:46:52.830|PSM
> 2010-04-28 17:47:04.939|PSM
> 2010-04-28 17:47:06.776|PSM
> 2010-04-28 17:47:08.846|PSM
> 2010-04-28 17:47:12.001|PSM
> 2010-04-28 17:47:13.845|PSM
> 2010-04-28 17:47:16.837|PSM
> 2010-04-28 17:47:18.846|PSM
> sqlite>
> sqlite>
> sqlite> select timeStamp, resourceType From MyTable where resourceType =
> 'PSM' LIMIT 10;
> sqlite>
> sqlite>
> sqlite>
>
> THANKS
>
>
> On Fri, Apr 30, 2010 at 9:35 AM, Black, Michael (IS) <
> michael.bla...@ngc.com
> > wrote:
>
> > You are likely getting the case insensitive result with "like".
> >
> > sqlite> create table t(resourceType varchar);
> > sqlite> insert into t values('PSM');
> > sqlite> insert into t values('psm');
> > sqlite> select * from t where resourceType = 'PSM';
> > PSM
> > sqlite> select * from t where resourceType like 'PSM';
> > PSM
> > psm
> > sqlite> select * from t where upper(resourceType) = 'PSM';
> > PSM
> > psm
> >
> > Michael D. Black
> > Senior Scientist
> > Northrop Grumman Mission Systems
> >
> >
> > 
> >
> > From: sqlite-users-boun...@sqlite.org on behalf of ecforu
> > Sent: Fri 4/30/2010 8:31 AM
> > To: General Discussion of SQLite Database
> > Subject: Re: [sqlite] WHERE = does not work
> >
> >
> >
> > But the like WHERE clause works the way it is.  Its the = that isn't
> > working.  I would rather use = than like.  I'm just using like for now
> > because it works.
> >
> > Thanks
> >
> > On Fri, Apr 30, 2010 at 9:29 AM, Timothy A. Sawyer <
> > tsaw...@mybowlingdiary.com> wrote:
> >
> > > With the like clause you have to use the % sign as a wildcard. So
> > > resourceType LIKE %'PSM' returns anything ending in PSM. The SQLite
> > website
> > > has excellent docs on standard SQL.
> > >
> > > -Original Message-
> > > From: ecforu 
> > > Sent: Friday, April 30, 2010 09:22
> > > To: sqlite-users@sqlite.org
> > > Subject: [sqlite] WHERE = does not work
> > >
> > > I have an sqlite3 database which I can't query with WHERE =.  I have to
> > use
> > > WHERE like.
> > >
> > > Any ideas why this is?
> > >
> > > For example I have a resourceType column that has as some of its
> entries
> > > (over 50) 'PSM'.
> > >
> > > SELECT * FROM MyTable WHERE resourceType = 'PSM'  --> returns nothing.
> > >
> > > SELECT * FROM MyTable WHERE resourceType like 'PSM' --> returns all PSM
> > > entries.
> > >
> > > What's the diff?
> > >
> > > Thanks
> > > ___
> > > 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
> >
> >
> >
> > ___
> > 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] Returning column to default

[Adam DeVita]

Is there a primary key on the table?

Is it possible to use insert or replace instead of update, and then not
reference the column you want to set as a default?

On Thu, May 6, 2010 at 10:41 AM, Andy Gibbs  wrote:

> > You could write a trigger that sets default value if NULL is inserted
> > or set via UPDATE.
>
> That's a great idea - thanks!  It won't work in all the places since in
> some
> places 'NULL' is a valid value, but I'm sure I can think of a work-around.
>
> Thank you!!
>
> ___
> 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] Update: set multiple values

[Adam DeVita]

Simon, can you expand your syntax, or are you just saying, "get x,y,z  store
them in a set of variables, then run update with appropriate bindings"?

Hopefully this related question isn't called hijacking a thread. I feel this
belongs together under set multiple values using the update query.

I'm toying with something similar, and don't want to get the run multiple
updates so that the C code can stay simple.

create table x (sn int primary key , comboid, property1 int , property2 int
, property3 int ...)
create table dictionary  (comboid int primary key, property1 int , property2
int, property3 int

(original insert into x was sn, comboid=-1 /*a flag to indicate this needs
an update*/ with property 1, 2, and 3 being correct. The original insert was
honking, very slow thing that I've given up hope of salvaging  since the
below beats it 10 to 100:1 in speed)

want to update each sn with the comboid from the dictionary where property1,
2, and 3 match.

currently I'm leaning on
insert or replace into x (sn, comboid, property1, property2, property3)
values select x.sn, d.comboid, x.property1, x.property2, x.property3 from x
inner join dictionary d on
x.property1 =d.property1 and x.property=d.property2 and  x.property3
=d.roperty3
where x.comboid=-1;

This somehow feels like cheating, though it seems to produce an acceptable
result quickly enough (on my relatively small db)

Adam



On Sun, May 9, 2010 at 5:23 PM, Simon Slavin  wrote:

>
> On 9 May 2010, at 8:41pm, Simon Hax wrote:
>
> > I think in sqlite the following is not possible:
> >
> > update T
> >  set (a,b,c) = ( select x,y,z from ...)
> >
> > Does anyone know how to do in an easy way ?
>
> Do your SELECT first, then set the multiple variables to the values
> retrieved from that:
>
> UPDATE T SET a=x,b=y,c=z WHERE ...
>
> 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] Update: set multiple values

[Adam DeVita]

but...

...but I LOVE my hammer!   How dare every problem not be a nail?

;)



Good point.   Likely all the updates can fit nicely into a transaction.


On Mon, May 10, 2010 at 5:11 PM, Simon Slavin  wrote:

>
> On 10 May 2010, at 9:25pm, Adam DeVita wrote:
>
> > Simon, can you expand your syntax, or are you just saying, "get x,y,z
>  store
> > them in a set of variables, then run update with appropriate bindings"?
>
> Just that.  You have a programming language with variables, so use it.
>  That's what your programming language is for.
>
> You might be able to get extremely clever and work out some contorted
> SQLite syntax which will do the whole thing in one SQL command, but why
> bother ?  It'll be hell to work out what's wrong if you get an error
> message.  And it'll be difficult to document because you have to explain
> your perverse syntax.  Better to use two extremely simple SQL commands and
> say "We get three values here ... then we use them in this UPDATE.".  Faster
> and simpler to write, debug and document.
>
> 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 connection?

[Adam DeVita]

Yes.

One can also
attach 'somedatabase path' as anothername  ;
and you can run a query accessing both at the same time.

regards,

Adam

On Tue, May 11, 2010 at 1:45 PM, john cummings wrote:

> hi all,
>
> i'm new to this forum and sqlite.
>
> is it possible to have an executable (i.e. .exe) with connections to 2
> sqlite databases?
>
> i've read doc and it doesn't speak to this one way or the other.
>
> thanks,
>
> john
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
VerifEye Technologies Inc.
905-948-0015x245
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]

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  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
> >  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] Faster check: > or !=

[Adam DeVita]

How much control do you have?

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

regards,
Adam

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

Greetings!

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

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

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

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

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

Any input would be greatly appreciated.  Thanks.

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

Re: [sqlite] Sqlite + Dropbox

[Adam DeVita]

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

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

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

regards,
Adam

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

Hi


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


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


Thanks


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

Re: [sqlite] 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] 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  wrote:

>
> On 14 Jan 2015, at 5:53pm, Nigel Verity  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] Why bind indexes start from 1 and column indexes start from 0?

[Adam Devita]

In C API one can avoid the problem using sqlite3_bind_parameter_index

https://www.sqlite.org/c3ref/bind_parameter_index.html

select  something from table where wsidint = @wsid_int;

int wsid_intVal=5; //for the sake of argument. (no pun intended)

sqlite3_bind_int(ppStmt, sqlite3_bind_parameter_index(ppStmt,
"@wsid_int"), wsid_intVal)

Adam DeVita

On Mon, Mar 2, 2015 at 11:01 AM, Simon Slavin  wrote:

>
> On 2 Mar 2015, at 12:23am, Jay Kreibich  wrote:
>
> > Every database I?ve every used starts SQL parameter indexes from 1.  I?m
> not sure it is part of the SQL standard, but it is more or less the defacto
> standard of SQL APIs, and might be considered part of the SQL language.
>
> I hope the SQLite library does something appropriate if you try to bind to
> parameter 0.  It would seem to be an excellent avenue for a security bug if
> nothing tests for it.
>
> Simon.
> ___
> 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] 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 FR

[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 

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

[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,
>> > > (ˈsiːkwəl.laɪt).
>> > > 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

[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] a couple of pre-initial questions

[Adam Devita]

Good day,

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

regards
Adam DeVita

On Wed, Jul 6, 2016 at 12:15 PM, John R. Sowden 
wrote:

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



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

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

[Adam Devita]

Good day,

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

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

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

4) Do this list in reverse order. :)


regards,
Adam


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

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



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

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

[Adam Devita]

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

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

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

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

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

regards,
Adam





On Thu, Aug 18, 2016 at 1:24 PM, Matias Badin  wrote:

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



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

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

[Adam Devita]

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

regards,
Adam

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

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



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

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

[Adam Devita]

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

regards,
Adam DeVita

On Fri, Sep 16, 2016 at 7:56 AM, Stephen Chrzanowski 
wrote:

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



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

Re: [sqlite] sqlite3 database unreadable on Mountain Lion

[Adam DeVita]

Good day,

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

Adam

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



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

[sqlite] C# Dynamic data type

[Adam DeVita]

Good day,

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

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

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

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


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

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

Re: [sqlite] C# Dynamic data type

[Adam DeVita]

Interesting idea. Thanks.

Adam

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



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

Re: [sqlite] SQLLite Question regarding instances and files

[Adam DeVita]

Good day,

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

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

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

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

regards,
Adam


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



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