[sqlite] about The open-source Fossil version control system

[YAN HONG YE]

Date: Tue, 03 Apr 2012 20:56:15 +0200
Kees Nuyt  wrote:

>The open-source Fossil  version control system
>used by SQLite contains an implementation of system() that correctly deals
>with UTF8 even on windows systems.  Perhaps the OP can copy some of the
>code from that.  LInk:
>
>http://www.fossil-scm.org/fossil/artifact/70e4b10c0208b3?ln=685-707
>
> But I don't know how to write.

Well, perhaps today you don't know how to write code that uses the
example, but if you take some time to learn it, you may be able to do it
in a few weeks or months.
Or you may want to hire someone who can do it for you.

-- 
When I open the source on the page, but I don't found 
  #include "config.h"
  #include "main.h"
file, where it is?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Variation on INSERT with SELECT issue

[Igor Tandetnik]

On 4/4/2012 8:48 PM, Pete wrote:

The test this time was:

sqlite>  create table t1 (Col1,Col2);
sqlite>  insert into t1 (Col1) values ('xxx');
sqlite>  select * from t1;
xxx|

sqlite>  create table t2 (Col1, col2 not null default 'abc');
sqlite>  insert into t2 SELECT * from t1;
SQL error: t2.col2 may not be NULL


Default clause applies when you omit a column from the list in INSERT 
(like you did when inserting into t1). It doesn't apply when you attempt 
to insert NULL explicitly.

--
Igor Tandetnik

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

[sqlite] Variation on INSERT with SELECT issue

[Pete]

The test this time was:

sqlite> create table t1 (Col1,Col2);
sqlite> insert into t1 (Col1) values ('xxx');
sqlite> select * from t1;
xxx|

sqlite> create table t2 (Col1, col2 not null default 'abc');
sqlite> insert into t2 SELECT * from t1;
SQL error: t2.col2 may not be NULL

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

Re: [sqlite] INSERT INTO with SELECT

[Simon Slavin]

On 5 Apr 2012, at 1:36am, Pete  wrote:

> Here's my test:
> 
> CREATE TABLE t2 (Col1 text,Col2 text);
> insert into t2 (Col1) values('xxx');
> select * from t2
> xxx|
> 
> CREATE TABLE t3 (Col1 text,Col2 text default 'abc');
> insert into t3 SELECT * FROM t2;
> select * from t3;
> xxx|
> 
> Why does t3.Col2 not have it's default value of 'abc'?

Because you fed it a value for the second column: NULL.  If you want the second 
column to have a default value you might find that

insert into t3 SELECT Col1 FROM t2;

works.

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

[sqlite] INSERT INTO with SELECT

[Pete]

I'm seeing something that doesn't look right when using the SELECT form of
the INSERT statement.

Here's my test:

CREATE TABLE t2 (Col1 text,Col2 text);
insert into t2 (Col1) values('xxx');
select * from t2
xxx|

CREATE TABLE t3 (Col1 text,Col2 text default 'abc');
insert into t3 SELECT * FROM t2;
select * from t3;
xxx|

Why does t3.Col2 not have it's default value of 'abc'?

Pete



-- 
Pete
Molly's Revenge 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite & JDBC & generated key

[Kees Nuyt]

On Wed, 4 Apr 2012 21:08:24 +0200, gwenn  wrote:

>  2) Do you know if there are other bindings that implement/support
> this kind of feature ?

I almost forgot to mention:

  SELECT last_insert_rowid();

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

-- 
Regards,

Kees Nuyt

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

Re: [sqlite] SQLite & JDBC & generated key

[Kees Nuyt]

On Wed, 4 Apr 2012 21:08:24 +0200, gwenn  wrote:

> * In JDBC API, there is a method to retreive the generated key during an 
> insert:
>http://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#getGeneratedKeys()
> * With SQLite API, there is: sqlite3_last_insert_rowid.
> Let suppose that:
> - the primary key is correctly declared to make it an alias for the rowid,
> - and the connection is not shared.
>
> 1) Do you know how to retreive the column name of the primary key (the
> table name is not known) ?

The table name is know, it is in sqlite_master. Besides, the program
should know against which table it just performed an INSERT statement.

With that table name, you can introspect the database to retrieve the
column name of the primary key:
PRAGMA table_info(TABLENAME);


In a shell script:

for tbl in $( \
printf "SELECT name FROM sqlite_master WHERE type='table';\n" \
| sqlite3 dbfile \
)
do
printf "PRAGMA table_info(%s);" "$tbl" \
| sqlite3 dbfile | gawk -v t="$tbl" '{printf "%s|%s\n",t,$0}'
done

I leave it as an exercise to the reader to convert that to java.

Actually, you may not need the name, as that given name for the INTEGER
PRIMARY KEY is just an alias for ROWID, and the name ROWID would still
work.

> 2) Do you know if there are other bindings that implement/support this
> kind of feature ?

As far as I can tell all required primitives are available, it's just a
matter of programming.


-- 
Regards,

Kees Nuyt

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

[sqlite] ETL anomalies

[Udi Karni]

First - Dr. Hipp - thanks for the .import fix from 1/16 baked into 3.7.11 -
it now recognizes commas embedded within character strings in a csv +
strips the double-quotes. This makes the loading much smoother.

 ==

Now for 3 strange events I've been running into. Sorry - the data volumes
are large - so it's very time consuming to run many tests to pinpoint these
further. Just raising the flag in case any of this rings a bell with anyone.

==

(1) I import data into a table in one DB
(2) I then create an identical table in a 2nd DB
(3) I then copy the table from DB1 to DB2 - with some SQL ETL cleanups.
Surprisingly - the data gets appended to the initial table on DB1 instead
of the new table on DB2.

I tried to replicate it with a couple of rows - but it works fine. However
- with the real 100 million row table it happens.

My workaround for the moment is to give the 2 tables on the 2 DBs different
names and ALTER RENAME when all is said and done. That works fine.

sqlite3 orig.db3
create table xyz (.);
import  xyz.csv  xyz
.exit

sqlite3 final.db3
create table xyz (.);
attach database 'orig.db3' as indb;
insert into xyz select * from indb.xyz
.exit

Look at the size of orig.db3 - it's now double in size with twice as many
rows
Look at the size of final.db3 - 0K

 ==

The reason I use the 2 DB approach in leu of just UPDATEing the original DB
- is that on UPDATE sqlite seems to try and read the table first. At least
it seems to do that on large tables.

Apparently - if the table is large enough - memory usage slowly rises - and
then I get an "out of memory" message.

So UPDATE works on smaller tables (up to 10-20GB) but for larger ones I
need to create a whole new copy of the table for every ETL iteration.

Since I would like each large table to end up in its own DB, and I would
like to avoid putting multiple intermediate tables in the same DB, then
delete the unnecessary ones, then finally VACUUM - I use multiple physical
DBs - hence - the abovementioned attach issue.

 ==

If I run an .import and mistype something - like the path of the .csv file
- or the wrong .separator - the commands obviously fails. I correct the
syntax and run again - I sometimes get a "expected x columns but found y
columns stopping at row nn" type of message.

Initiall I very carefully looked at the data and tried to load "nn+1" rows.
Everything worked. I reran the .import and all was fine.

Then I learned to just close / reopen the DB - run the .import again -
without changing anything - and all works fine.

It's as if when .import runs against a problem - it doesn't clean something
up - which leads to other messages. Starting from scratch - making sure the
syntax is correct the first time - works fine.

==

Again - sorry these sounds so fuzzy. I'll try to provide more detail as I
run across it - but just in case these are simple to figure out.

Thanks !
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Selecting every nth row efficiently

[Kees Nuyt]

On Wed, 4 Apr 2012 10:59:20 -0500, "Jay A. Kreibich" 
wrote:

>  Not to mention avg().
>
>  You might want to have a look at how RRDtool deals with condensing
>  data.  It is common pratice to plot average, min, and max to preserve 
>  outliers, while still showing trends.
>
>  http://oss.oetiker.ch/rrdtool/
>
>
>   -j

I second this, just dump all your data into a well configured rrdtool
round robin database.



## CONSTANTS

# sample interval in seconds, 5 minutes
INTERVAL=300
# max interval to miss (heartbeat)
HEARTBEAT=900

## Aggregate parameters
# aggregate1 is   1 samples =   1 * 300 =   300 sec =  5 min @ 2016 rows
=   168 hrs   =   7 days =   1 week
# aggregate2 is  12 samples =  12 * 300 =  3600 sec =  1 hr  @ 1008 rows
=  1008 hrs   =  42 days =   6 weeks (5 + 1 ^^ )
# aggregate3 is  72 samples =  72 * 300 = 21600 sec =  6 hr  @  896 rows
=  5376 hrs   = 224 days =  32 weeks (26+5+1 ^^ ^^ )
# aggregate3 is 288 samples = 288 * 300 = 86400 sec = 24 hr  @  616 rows
= 14784 hrs   = 616 days =  88 weeks (56+26+5+1 round up^^ ^^ ^^)
STEPS1=1
STEPS2=12
STEPS3=72
STEPS4=288
# nr of aggregared generations 42 days
ROWS1=2016
ROWS2=1008
ROWS3=896
ROWS4=616


### create and populate the round robin database "once"

rrcreate () {
test -f "${RRFN}" && rm "${RRFN}"

rrdtool create "${RRFN}" \
--start $(printf ".separator :\nSELECT epstamp - 300 FROM rrdfrom;\n" |
sqlite3 ${dbfn}) \
--step ${INTERVAL} \
DS:UpstreamBitRate:GAUGE:${HEARTBEAT}:0:4096000 \
DS:DnstreamBitRate:GAUGE:${HEARTBEAT}:0:32768000 \
DS:BytesSent:COUNTER:${HEARTBEAT}:0:5000 \
DS:BytesRecv:COUNTER:${HEARTBEAT}:0:1 \
DS:Connected:GAUGE:${HEARTBEAT}:0:3 \
RRA:AVERAGE:0.1:${STEPS1}:${ROWS1} \
RRA:MIN:0.1:${STEPS1}:${ROWS1} \
RRA:MAX:0.1:${STEPS1}:${ROWS1} \
RRA:AVERAGE:0.1:${STEPS2}:${ROWS2} \
RRA:MIN:0.1:${STEPS2}:${ROWS2} \
RRA:MAX:0.1:${STEPS2}:${ROWS2} \
RRA:AVERAGE:0.1:${STEPS3}:${ROWS3} \
RRA:MIN:0.1:${STEPS3}:${ROWS3} \
RRA:MAX:0.1:${STEPS3}:${ROWS3} \
RRA:AVERAGE:0.1:${STEPS4}:${ROWS4} \
RRA:MIN:0.1:${STEPS4}:${ROWS4} \
RRA:MAX:0.1:${STEPS4}:${ROWS4}

### POPULATE RRD, 

printf ".separator :\nSELECT * FROM rrdupdate;\n" \
| sqlite3 "${dbfn}" \
| gawk -v rr="${RRFN}" 'BEGIN{ 
# group 24 measurements into one update line for speed
buf=""
}
function prbuf(){
printf "update %s%s\n",rr,buf
buf=""
}
( 0 == (NR % 24)){
prbuf()
}
{
buf = buf " " $0
}
END{
if (buf != "") prbuf()
}' | rrdtool -

}


where rrdfrom is a view to define the start timestamp of the RRD:

CREATE VIEW rrdfrom AS
SELECT strftime('%s',MIN(dtstamp)) AS epstamp
FROM T_Statistics AS ST
;


rrdupdate is a view providing the data:

CREATE VIEW rrdupdate AS
SELECT strftime('%s',dtstamp) AS epstamp
,   Layer1UpstreamMaxBitRate
,   Layer1DownstreamMaxBitRate
,   TotalBytesSent
,   TotalBytesReceived
,   CASE CS.cs_val
WHEN 'Connected' THEN 1
ELSE 0
END AS ConnectionStatus
FROM T_Statistics AS ST
INNER JOIN ConnectionStatussAS CS
ON ST.cs_id == CS.cs_id
 ORDER BY dtstamp;


T_Statistics is a table: 

CREATE TABLE T_Statistics   (   -- ST
st_id   INTEGER 
PRIMARY KEY NOT NULL
,   dtstamp REALNOT 
NULL-- julianday(), UTC
,   rc  
INTEGER NOT NULL
,   cs_id   INTEGER 
NOT NULL
 REFERENCES ConnectionStatuss   (cs_id)
 ON UPDATE CASCADE ON DELETE CASCADE
,   le_id   INTEGER 
NOT NULL
 REFERENCES LastConnectionErrors(le_id)
 ON UPDATE CASCADE ON DELETE CASCADE
,   uptime  INTEGER NOT 
NULL-- seconds
,   wa_id   INTEGER 
NOT NULL
 REFERENCES WANAccessTypes  (wa_id)
 ON UPDATE CASCADE ON DELETE CASCADE
,   Layer1UpstreamMaxBitRateINTEGER NOT NULL
,   Layer1DownstreamMaxBitRate  INTEGER NOT NULL
,   pl_id   INTEGER 
NOT NULL
 REFERENCES PhysicalLinkStatuss (pl_id)
 ON UPDATE CASCADE ON DELETE CASCADE
,   ByteSendRateINTEGER NOT NULL
,   ByteReceiveRate INTEGER NOT NULL
,   PacketSendRate  INTEGER NOT NULL
,   PacketReceiveRate   INTEGER NOT NULL
,   TotalBytesSent  INTEGER NOT NULL

Re: [sqlite] SQLite & JDBC & generated key

[Simon Slavin]

On 4 Apr 2012, at 8:08pm, gwenn  wrote:

> * In JDBC API, there is a method to retreive the generated key during an 
> insert:
> http://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#getGeneratedKeys()
> * With SQLite API, there is: sqlite3_last_insert_rowid.
> Let suppose that:
> - the primary key is correctly declared to make it an alias for the rowid,
> - and the connection is not shared.
> 1) Do you know how to retreive the column name of the primary key (the
> table name is not known) ?

You can pluck the primary key from the CREATE TABLE command you can find in 
sqlite_master, though you'll have to parse it out from there.

However, note that the primary key may be more than one column:

CREATE TABLE something (column1, column2, column3, PRIMARY KEY (column1, 
column2))

though this shouldn't happen under the constraints you gave above.

> 2) Do you know if there are other bindings that implement/support this
> kind of feature ?

Can't think of any advantage to it.  Unless the programmer defined a column 
called _rowid_ I can always use that, and there's too much likelihood of 
someone else using a compound key as above.

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

[sqlite] SQLite & JDBC & generated key

[gwenn]

* In JDBC API, there is a method to retreive the generated key during an insert:
http://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#getGeneratedKeys()
* With SQLite API, there is: sqlite3_last_insert_rowid.
Let suppose that:
 - the primary key is correctly declared to make it an alias for the rowid,
 - and the connection is not shared.
1) Do you know how to retreive the column name of the primary key (the
table name is not known) ?
2) Do you know if there are other bindings that implement/support this
kind of feature ?
Thanks.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] About INSERT into "External Content FTS4 Tables"

[Alexey Pechnikov]

Why INSERT command can't copy all needed fields from external content table
same as "rebuild" command? The definition of all values does not has any
reasons and can produce inconsistent FTS table.

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

Re: [sqlite] sqlite3VdbeSetSql should report out of memory condition

[Rafael Ávila de Espíndola]

On 12-04-04 11:20 AM, Richard Hipp wrote:

2012/4/4 Rafael Ávila de Espíndola


Currently if the call

  p->zSql = sqlite3DbStrNDup(p->db, z, n);

fails, zSql will stay null without the caller being notified that it
failed. sqlite3VdbeSetSql should probably return a bool. With that,
sqlite3Prepare could check it and return SQLITE_NOMEM.



When sqlite3DbStrNDup() fails, it sets the mallocFailed flag in p->db.
This later gets translated into an SQLITE_NOMEM error which is returned by
sqlite3_prepare().

We have test cases for this very occurrence.


Awesome!


In fact, during out-of-memory (OOM)
testing,
our test harnesses verify that every simulated OOM error does in fact
result in an SQLITE_NOMEM error (or at least some kind of error).  There
are a few places in the code where a memory allocation is merely an
optimization and is not required for correctness.  (For example
here.)
In those places, a  failed allocation does not result in an error.  But
those cases are surrounded by calls to sqlite3BeginBenignMalloc() and
sqlite3EndBenignMalloc(), which disable the logic in the test harnesses
that verify that all OOM errors are detected.  The allocation of the zSql
string is not one of those places, however.  I have just now double-checked
that it is not by running one of our test programs in a debugger.


Thanks for checking this. Sorry I missed the check in sqlite3ApiExit.

I thought this was the root problem in 
https://bugzilla.mozilla.org/show_bug.cgi?id=741821, where we are 
accessing a null returned by sqlite3_sql, but I guess it is mozilla's 
code that is eating the error somewhere.



Thanks,
Rafael

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

Re: [sqlite] Selecting every nth row efficiently

[Jay A. Kreibich]

On Wed, Apr 04, 2012 at 12:05:44PM +0200, Steinar Midtskogen scratched on the 
wall:
> [Jean-Christophe Deschamps]
> 
> > You're going to have at most one random sample in every slice of 320
> > s.  The GROUP BY clause will select only one for you and the query can
> > be as simple as:
> >
> > select sample from from mytable group by timestamp / 320 order by
> > timestamp;
> 
> Ah.  I didn't think of that.  It's even better than getting every nth
> row, since I get one sample for a fixed period, which is what I really
> want.  And yet better, I suppose I could do something like SELECT
> min(sample), max(sample) FROM mytable GROUP BY timestamp / 3600 and
> use financebars or similar in gnuplot to avoid missing the extremes in
> the plot, making it appear more or less identical as if I had plotted
> every value.


  Not to mention avg().

  You might want to have a look at how RRDtool deals with condensing
  data.  It is common pratice to plot average, min, and max to preserve 
  outliers, while still showing trends.

  http://oss.oetiker.ch/rrdtool/


   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Hashing 2 SQLite db files with the same data

[Jay A. Kreibich]

On Wed, Apr 04, 2012 at 12:30:48PM +, Black, Michael (IS) scratched on the 
wall:
> Howeverthe DB file is portable across big/little endian and 32/64 bit.

  Yes.  As I said, SQLite will automatically convert values when the
  file and platform endian don't match.

> I guess SQLite uses the endianess of the database file over the architecture?

  Yes.  The endianess of the file is set on creation, and cannot be
  changed (without a full VACUUM).  By default, the encoding defaults
  to the host platform's native endian.  You can use the "encoding"
  PRAGMA to over-ride this, however.

   -j




> 
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
> behalf of Jay A. Kreibich [j...@kreibi.ch]
> Sent: Wednesday, April 04, 2012 7:26 AM
> To: General Discussion of SQLite Database
> Subject: EXT :Re: [sqlite] Hashing 2 SQLite db files with the same data
> 
> On Wed, Apr 04, 2012 at 11:15:10AM +1000, Webdude scratched on the wall:
> 
> > But the same SQLite version, using the same schema, setup with the
> > same PRAGMA's, creating a db with the same data and in the same
> > order, and despite hardware / HDD / OS, should still produce the
> > same file byte-for byte ?
> 
>   No.  (And you thought this was going to be easy, didn't you?)
> 
>   By default, the database file uses the same endian as the platform
>   used to create the database file.  On platforms where the endian is
>   different from the file, there is an automatic conversion.  This
>   means the *information* in the database can be the exact same, but
>   the *file* can still be different.
> 
>   IIRC, the endian only applies to UTF-16 encoded strings, however.
> 
> 
>   And yes... there is a PRAGMA to set this ("encoding").  So, if "setup
>   with the same PRAGMAs" is absolutely true across dozens of PRAGMAs, the
>   files should be the same.
> 
>   If you want to do this, however, you're going to need to set a whole
>   boat-load of PRAGMAs on both platforms, and make sure you get them
>   just right... and re-examine the list every time you upgrade SQLite.
> 
> -j
> 
> --
> Jay A. Kreibich < J A Y  @  K R E I B I.C H >
> 
> "Intelligence is like underwear: it is important that you have it,
>  but showing it to the wrong people has the tendency to make them
>  feel uncomfortable." -- Angela Johnson
> ___
> 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

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3VdbeSetSql should report out of memory condition

[Richard Hipp]

2012/4/4 Rafael Ávila de Espíndola 

> Currently if the call
>
>  p->zSql = sqlite3DbStrNDup(p->db, z, n);
>
> fails, zSql will stay null without the caller being notified that it
> failed. sqlite3VdbeSetSql should probably return a bool. With that,
> sqlite3Prepare could check it and return SQLITE_NOMEM.
>

When sqlite3DbStrNDup() fails, it sets the mallocFailed flag in p->db.
This later gets translated into an SQLITE_NOMEM error which is returned by
sqlite3_prepare().

We have test cases for this very occurrence.

In fact, during out-of-memory (OOM)
testing,
our test harnesses verify that every simulated OOM error does in fact
result in an SQLITE_NOMEM error (or at least some kind of error).  There
are a few places in the code where a memory allocation is merely an
optimization and is not required for correctness.  (For example
here.)
In those places, a  failed allocation does not result in an error.  But
those cases are surrounded by calls to sqlite3BeginBenignMalloc() and
sqlite3EndBenignMalloc(), which disable the logic in the test harnesses
that verify that all OOM errors are detected.  The allocation of the zSql
string is not one of those places, however.  I have just now double-checked
that it is not by running one of our test programs in a debugger.



>
> Cheers,
> Rafael
> __**_
> 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] sqlite3VdbeSetSql should report out of memory condition

[Rafael Ávila de Espíndola]

Currently if the call

  p->zSql = sqlite3DbStrNDup(p->db, z, n);

fails, zSql will stay null without the caller being notified that it 
failed. sqlite3VdbeSetSql should probably return a bool. With that, 
sqlite3Prepare could check it and return SQLITE_NOMEM.


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

Re: [sqlite] Hashing 2 SQLite db files with the same data

[Simon Slavin]

On 4 Apr 2012, at 1:30pm, "Black, Michael (IS)"  wrote:

> Howeverthe DB file is portable across big/little endian and 32/64 bit.
> 
> So do your hash on the DB file and distribute that.  Any reason you can't do 
> that?

My understanding is that he's having two different computers put lots of data 
into SQLite files, and wanting to compare MD5 hashes of the two files and use 
that to prove that both computers put the same data in.  What we've done is 
tell him that he's going to get very many false negatives, where both computers 
put the same information into the database but he ended up with different MD5 
hashes because the files on disk have different contents.

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

Re: [sqlite] Hashing 2 SQLite db files with the same data

[Black, Michael (IS)]

Howeverthe DB file is portable across big/little endian and 32/64 bit.

So do your hash on the DB file and distribute that.  Any reason you can't do 
that?



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



I guess SQLite uses the endianess of the database file over the architecture?





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 Jay A. Kreibich [j...@kreibi.ch]
Sent: Wednesday, April 04, 2012 7:26 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Hashing 2 SQLite db files with the same data

On Wed, Apr 04, 2012 at 11:15:10AM +1000, Webdude scratched on the wall:

> But the same SQLite version, using the same schema, setup with the
> same PRAGMA's, creating a db with the same data and in the same
> order, and despite hardware / HDD / OS, should still produce the
> same file byte-for byte ?

  No.  (And you thought this was going to be easy, didn't you?)

  By default, the database file uses the same endian as the platform
  used to create the database file.  On platforms where the endian is
  different from the file, there is an automatic conversion.  This
  means the *information* in the database can be the exact same, but
  the *file* can still be different.

  IIRC, the endian only applies to UTF-16 encoded strings, however.


  And yes... there is a PRAGMA to set this ("encoding").  So, if "setup
  with the same PRAGMAs" is absolutely true across dozens of PRAGMAs, the
  files should be the same.

  If you want to do this, however, you're going to need to set a whole
  boat-load of PRAGMAs on both platforms, and make sure you get them
  just right... and re-examine the list every time you upgrade SQLite.

-j

--
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
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] Hashing 2 SQLite db files with the same data

[Jay A. Kreibich]

On Wed, Apr 04, 2012 at 11:27:41AM +1000, Webdude scratched on the wall:

> But if data was added exactly in the same way/order shouldn't the 
> counters all count to the same end result if the process was
> repeated at a later time on a another machine?

  In theory, yes, but that's a very thin line to walk.  For example,
  inserting records individually vs inserting the exact same records,
  in the exact same order, but doing it in batches, will result in
  different counters.  All the data inserts will have to be the EXACT
  same way.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Hashing 2 SQLite db files with the same data

[Jay A. Kreibich]

On Wed, Apr 04, 2012 at 11:15:10AM +1000, Webdude scratched on the wall:

> But the same SQLite version, using the same schema, setup with the
> same PRAGMA's, creating a db with the same data and in the same
> order, and despite hardware / HDD / OS, should still produce the
> same file byte-for byte ?

  No.  (And you thought this was going to be easy, didn't you?)

  By default, the database file uses the same endian as the platform
  used to create the database file.  On platforms where the endian is
  different from the file, there is an automatic conversion.  This
  means the *information* in the database can be the exact same, but
  the *file* can still be different.
  
  IIRC, the endian only applies to UTF-16 encoded strings, however.


  And yes... there is a PRAGMA to set this ("encoding").  So, if "setup
  with the same PRAGMAs" is absolutely true across dozens of PRAGMAs, the
  files should be the same.

  If you want to do this, however, you're going to need to set a whole
  boat-load of PRAGMAs on both platforms, and make sure you get them
  just right... and re-examine the list every time you upgrade SQLite.

-j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Selecting every nth row efficiently

[Steinar Midtskogen]

Let's say that I want to plot the entire column and the plot is 1000
pixels wide.  Then I only need 1000 samples, so I could do this:

SELECT timestamp, sample FROM mytable GROUP BY timestamp * 1000 / ((SELECT 
max(timestamp) FROM mytable) - (SELECT min(timestamp) FROM mytable));

(timestamp is the primary key)

-Steinar

Steinar Midtskogen  writes:

> [Jean-Christophe Deschamps]
>
>> You're going to have at most one random sample in every slice of 320
>> s.  The GROUP BY clause will select only one for you and the query can
>> be as simple as:
>>
>> select sample from from mytable group by timestamp / 320 order by
>> timestamp;
>
> Ah.  I didn't think of that.  It's even better than getting every nth
> row, since I get one sample for a fixed period, which is what I really
> want.  And yet better, I suppose I could do something like SELECT
> min(sample), max(sample) FROM mytable GROUP BY timestamp / 3600 and
> use financebars or similar in gnuplot to avoid missing the extremes in
> the plot, making it appear more or less identical as if I had plotted
> every value.
>
> Thanks!
> -- 
> Steinar
> ___
> 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] Selecting every nth row efficiently

[Steinar Midtskogen]

[Jean-Christophe Deschamps]

> You're going to have at most one random sample in every slice of 320
> s.  The GROUP BY clause will select only one for you and the query can
> be as simple as:
>
> select sample from from mytable group by timestamp / 320 order by
> timestamp;

Ah.  I didn't think of that.  It's even better than getting every nth
row, since I get one sample for a fixed period, which is what I really
want.  And yet better, I suppose I could do something like SELECT
min(sample), max(sample) FROM mytable GROUP BY timestamp / 3600 and
use financebars or similar in gnuplot to avoid missing the extremes in
the plot, making it appear more or less identical as if I had plotted
every value.

Thanks!
-- 
Steinar
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Selecting every nth row efficiently

[Jean-Christophe Deschamps]

This is a good suggestion.  A drawback is that the interval can't be
too small otherwise there is a risk that a sample would be missed.  So
I will get more samples than I need.  In you example, if there is a
sample every second more or less, I would usually get 3-4 samples
every hour instead of just one which would suffice.


No, because of group by clause.


In reality I have around 5 samples every 300 seconds, and almost
certainly at least one sample within 320 seconds.  Then, most of the 5
samples will typically be within a few seconds, and rarely spread
evenly across the 300 second interval.  So the sampling is only
semi-regular.


You're going to have at most one random sample in every slice of 320 
s.  The GROUP BY clause will select only one for you and the query can 
be as simple as:


select sample from from mytable group by timestamp / 320 order by 
timestamp;


Test on a temporary table taken as a small subset of your main table 
and see what gives.  Increase 320 to whatever value is needed to grab 
at least one sample in every time slice.


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

Re: [sqlite] Selecting every nth row efficiently

[Steinar Midtskogen]

[Jean-Christophe Deschamps]

>>If your sampling is essentially regular, why not make it
>>
>>select ... where timestamp % N between min_interval and max_interval
>>
>> N being the typical time delta of your n rows above and interval
>> bounds reducing the possiblity of gross under- and over-sampling.
>> May need adjustment but the query should run faster than full table
>> load.
>
> Let's assume your timestamps are unix epoch and your device samples on
> an average 1-second basis (delays in processing could cause two
> samples with the same timestamp or no sample with a given
> epoch). You're certain that there is always at least one sample
> between seconds (say) 8 and 11 of every hour.  You want only one
> sample every hour, taken randomly(*) between seconds 8 and 11.
>
> select sample from from mytable where timestamp % 86400 between 8 and
> 11 group by timestamp / 86400;
>
> (*) SQL[ite] doesn't give you a chance to specify which row will
> represent each group, hence the randomly in 8..11
>
> Would that do what you want?

This is a good suggestion.  A drawback is that the interval can't be
too small otherwise there is a risk that a sample would be missed.  So
I will get more samples than I need.  In you example, if there is a
sample every second more or less, I would usually get 3-4 samples
every hour instead of just one which would suffice.

In reality I have around 5 samples every 300 seconds, and almost
certainly at least one sample within 320 seconds.  Then, most of the 5
samples will typically be within a few seconds, and rarely spread
evenly across the 300 second interval.  So the sampling is only
semi-regular.

I will mostly be selecting from a virtual table, so another option for
me could be to add a column which simply returns an incrementing
number, which I can use % on, or perhaps better a column containing a
flag which is set every time the timestamp has increased more than a
certain limit since the last time the flag was set.  But something
less ad hoc is better.

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

Re: [sqlite] Selecting every nth row efficiently

[Jean-Christophe Deschamps]

Hit Send inadvertandly, sorry.


My rowid isn't increasing with something predictable, so I can't do
something like WHERE rowid % n = 0.  I can use WHERE random() % n = 0
giving me sort of what I want (better than row % n, but I still need
something better).


If your sampling is essentially regular, why not make it

select ... where timestamp % N between min_interval and max_interval

N being the typical time delta of your n rows above and interval 
bounds reducing the possiblity of gross under- and over-sampling.  May 
need adjustment but the query should run faster than full table load.


Let's assume your timestamps are unix epoch and your device samples on 
an average 1-second basis (delays in processing could cause two samples 
with the same timestamp or no sample with a given epoch). You're 
certain that there is always at least one sample between seconds (say) 
8 and 11 of every hour.  You want only one sample every hour, taken 
randomly(*) between seconds 8 and 11.


select sample from from mytable where timestamp % 86400 between 8 and 
11 group by timestamp / 86400;


(*) SQL[ite] doesn't give you a chance to specify which row will 
represent each group, hence the randomly in 8..11


Would that do what you want?

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

Re: [sqlite] Selecting every nth row efficiently

[Jean-Christophe Deschamps]

I have a big database with timestamps and sensor readings, which I
access with SELECT to have gnuplot draw graphs.  However, sometimes I
have readings every minute and want to plot several years of data, and
feeding everything to gnuplot is overkill.  In these cases it would be
sufficient to select only every nth row.  Is there a way to do this
efficiently (i.e. more efficient than just having gnuplot, which is
pretty fast, to plot everything)?

My rowid isn't increasing with something predictable, so I can't do
something like WHERE rowid % n = 0.  I can use WHERE random() % n = 0
giving me sort of what I want (better than row % n, but I still need
something better).


If your sampling is essentially regular, why not make it

select ... where timestamp % N between min_interval and max_interval

N being the typical time delta of your n rows above and interval bounds 
reducing the possiblity of gross under- and over-sampling.  May need 
adjustment but the query should run faster than full table load. 


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

Re: [sqlite] Parser bug on sub joins when on right side of the join operator

[TAUZIN Mathieu]

Hi,

I expected someone would reply to this one

Mathieu


-Message d'origine-
De : sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
De la part de Mathieu TAUZIN
Envoyé : lundi 26 mars 2012 15:53
À : sqlite-users@sqlite.org
Objet : [sqlite] Parser bug on sub joins when on right side of the join operator

Hi,
 
According to their definition 
(http://sqlite.org/syntaxdiagrams.html#single-source) , Join sources (named 
single-source) are either : 
* a table or view with an optional alias and/or with an 
optional index
* a sub query with an optional alias
* a sub join (with no alias)

In SQLite parser.y source code we can find on line 496 the grammar rule 
handling those three cases (in the same order)
 

...
seltablist(A) ::= stl_prefix(X) nm(Y) dbnm(D) as(Z) indexed_opt(I) on_opt(N) 
using_opt(U). {
  A = sqlite3SrcListAppendFromTerm(pParse,X,&Y,&D,&Z,0,N,U);
  sqlite3SrcListIndexedBy(pParse, A, &I); }
 
seltablist(A) ::= stl_prefix(X) LP select(S) RP
as(Z) on_opt(N) using_opt(U). {
A = sqlite3SrcListAppendFromTerm(pParse,X,0,0,&Z,S,N,U);
  }
 
seltablist(A) ::= stl_prefix(X) LP seltablist(F) RP
as(Z) on_opt(N) using_opt(U). {
if( X==0 && Z.n==0 && N==0 && U==0 ){
  A = F;
}else{
  Select *pSubquery;
  sqlite3SrcListShiftJoinType(F);
  pSubquery = sqlite3SelectNew(pParse,0,F,0,0,0,0,0,0,0);
  A = sqlite3SrcListAppendFromTerm(pParse,X,0,0,&Z,pSubquery,N,U);
}
  }
...

 
Case 1 and 2 are handled properly but as you can see the third definition (wich 
should deal with sub joins) contains mistakes :
#1 : It allows an as clause after the parenthesis
#2 : on the right of a join operator (else { ... }) it 
generates a subquery instead of merging F (which is a seltabList, not a sub
query) with X into A.
 
I wish I could propose a fix but I have no skills in C/yacc.
 
Hope this will help anyway.
 
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