date:20161021

Not possible in my case, but something to keep in mind for my next project.  
Thanks all for helping out a newbie. 

> On Oct 21, 2016, at 7:30 PM, Keith Medcalf  wrote:
> 
> 
> And what about NULL values?
> 
>> All fields would match in an existing record compared to that of a
>> proposed new record. That help?
>> 
>>> On 10/21/2016 4:49 PM, Simon Slavin wrote:
 On 21 Oct 2016, at 10:46pm, Rick Kohrs  wrote:
 
 I want to make sure that I do not insert a new record if ALL of the
>> variables match. I can potentially have 3 systems writing to the same
>> database and I don't want duplicate records
 
   sqlCommand =  """
CREATE TABLE himawari_db (
dateTime   TEXT,
filename   TEXT,
satID  TEXT,
year   INT,
month  INT,
dayINT,
hour   INT,
minute INT,
band   INT,
coverage   TEXT,
region INT,
segmentINT,
server TEXT);"""
try:
satDB.execute(sqlCommand)
try:
dbConnect.commit()
>>> How do you define "duplicate" ?  What field or fields of a new record
>> have to be the same as an existing one for you to want "reject that one,
>> it's a duplicate of one already there" ?
>>> 
>>> Simon.
>>> ___
>>> 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
> 
> 
> 
> ___
> 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

Re: [sqlite] Using IGNORE for complete record match


Of course, allows "duplicate" rows to be inserted if one (or more) of the 
fields are NULL:

sqlite> create table x(x int, y int, unique (x,y));
sqlite> insert into x values (1,1);
sqlite> insert into x values (NULL,1);
sqlite> insert into x values (1,NULL);
sqlite> insert into x values (1,1);
Error: UNIQUE constraint failed: x.x, x.y
sqlite> insert into x values (NULL,1);
sqlite> insert into x values (1,NULL);

If NULLs need to be considered equal, then you have to use a trigger.  If there 
is no intention to use NULLs then the table definition should say that.

> On 21 Oct 2016, at 10:53pm, Rick Kohrs  wrote:
> 
> > All fields would match in an existing record compared to that of a
> proposed new record. That help?
> 
> So if two rows had all fields identical except for segment, they are not
> identical ?  Okay, in that case
> 
> CREATE UNIQUE INDEX him_all
> ON himawari_db (dateTime, filename, satID, year, month, day, hour,
> minute, band   , coverage, region, segment, server);
> 
> This will mean it's impossible to insert the second row.  The command
> 
> INSERT OR IGNORE ...
> 
> will then not insert the second row, without causing an error.
> 
> Simon.
> ___
> 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

Re: [sqlite] Using IGNORE for complete record match


And what about NULL values?

> All fields would match in an existing record compared to that of a
> proposed new record. That help?
> 
> On 10/21/2016 4:49 PM, Simon Slavin wrote:
> > On 21 Oct 2016, at 10:46pm, Rick Kohrs  wrote:
> >
> >> I want to make sure that I do not insert a new record if ALL of the
> variables match. I can potentially have 3 systems writing to the same
> database and I don't want duplicate records
> >>
> >>sqlCommand =  """
> >> CREATE TABLE himawari_db (
> >> dateTime   TEXT,
> >> filename   TEXT,
> >> satID  TEXT,
> >> year   INT,
> >> month  INT,
> >> dayINT,
> >> hour   INT,
> >> minute INT,
> >> band   INT,
> >> coverage   TEXT,
> >> region INT,
> >> segmentINT,
> >> server TEXT);"""
> >> try:
> >> satDB.execute(sqlCommand)
> >> try:
> >> dbConnect.commit()
> > How do you define "duplicate" ?  What field or fields of a new record
> have to be the same as an existing one for you to want "reject that one,
> it's a duplicate of one already there" ?
> >
> > Simon.
> > ___
> > 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



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

Re: [sqlite] Using IGNORE for complete record match

On 21 Oct 2016, at 10:53pm, Rick Kohrs  wrote:

> All fields would match in an existing record compared to that of a proposed 
> new record. That help?

So if two rows had all fields identical except for segment, they are not 
identical ?  Okay, in that case

CREATE UNIQUE INDEX him_all
ON himawari_db (dateTime, filename, satID, year, month, day, hour, minute, 
band   , coverage, region, segment, server);

This will mean it's impossible to insert the second row.  The command

INSERT OR IGNORE ...

will then not insert the second row, without causing an error.

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

Re: [sqlite] Using IGNORE for complete record match

All fields would match in an existing record compared to that of a 
proposed new record. That help?


On 10/21/2016 4:49 PM, Simon Slavin wrote:

On 21 Oct 2016, at 10:46pm, Rick Kohrs  wrote:


I want to make sure that I do not insert a new record if ALL of the variables 
match. I can potentially have 3 systems writing to the same database and I 
don't want duplicate records

   sqlCommand =  """
CREATE TABLE himawari_db (
dateTime   TEXT,
filename   TEXT,
satID  TEXT,
year   INT,
month  INT,
dayINT,
hour   INT,
minute INT,
band   INT,
coverage   TEXT,
region INT,
segmentINT,
server TEXT);"""
try:
satDB.execute(sqlCommand)
try:
dbConnect.commit()

How do you define "duplicate" ?  What field or fields of a new record have to be the same 
as an existing one for you to want "reject that one, it's a duplicate of one already 
there" ?

Simon.
___
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

Re: [sqlite] Using IGNORE for complete record match

2016-10-21 Thread Jens Alfke

> On Oct 21, 2016, at 2:46 PM, Rick Kohrs  wrote:
> 
> INSERT or IGNORE does not seem to be working as expected.

The “or IGNORE” part describes what happens if there’s a conflict that would 
otherwise cause the INSERT to fail. Your schema doesn’t declare any column or 
columns as being UNIQUE, so SQLite sees no conflict in adding identical rows.

If you want to prevent identical rows it looks like you’d need to add a clause 
to your table like “UNIQUE (dateTime, filename, … server)”, i.e. listing all of 
the columns. (For performance’s sake it would help if you could identify a 
smaller number of columns that need to be unique.)

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

Re: [sqlite] Using IGNORE for complete record match

On 21 Oct 2016, at 10:46pm, Rick Kohrs  wrote:

> I want to make sure that I do not insert a new record if ALL of the variables 
> match. I can potentially have 3 systems writing to the same database and I 
> don't want duplicate records
> 
>   sqlCommand =  """
>CREATE TABLE himawari_db (
>dateTime   TEXT,
>filename   TEXT,
>satID  TEXT,
>year   INT,
>month  INT,
>dayINT,
>hour   INT,
>minute INT,
>band   INT,
>coverage   TEXT,
>region INT,
>segmentINT,
>server TEXT);"""
>try:
>satDB.execute(sqlCommand)
>try:
>dbConnect.commit()

How do you define "duplicate" ?  What field or fields of a new record have to 
be the same as an existing one for you to want "reject that one, it's a 
duplicate of one already there" ?

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

[sqlite] Using IGNORE for complete record match

I want to make sure that I do not insert a new record if ALL of the 
variables match. I can potentially have 3 systems writing to the same 
database and I don't want duplicate records


   sqlCommand =  """
CREATE TABLE himawari_db (
dateTime   TEXT,
filename   TEXT,
satID  TEXT,
year   INT,
month  INT,
dayINT,
hour   INT,
minute INT,
band   INT,
coverage   TEXT,
region INT,
segmentINT,
server TEXT);"""
try:
satDB.execute(sqlCommand)
try:
dbConnect.commit()

INSERT or IGNORE does not seem to be working as expected.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Troubles matching variable as type TIMESTAMP


thanks


On 10/21/2016 3:04 PM, Keith Medcalf wrote:

SQLite does not have a type of "TIMESTAMP".  Only TEXT, INTEGER, REAL, and 
BLOB.  They type of the data in the field depends on what you are storing in it.  What 
are you storing in it (you cannot store Python objects, such as a datetime object, in an 
SQLite database, only TEXT, INTEGER, REAL or BLOBs).


...
  sqlCommand =  """
  CREATE TABLE himawari_db (
  date_time  TIMESTAMP,


Once you know what you are storing, you will know how to compose a where clause.





___
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

Re: [sqlite] Troubles matching variable as type TIMESTAMP


And do not forget to always convert your datetime to UTC before storing, 
especially if you live somewhere where the timezone offset from UTC has ever 
changed (which includes the entire planet earth).  Wallclock timestamps in any 
localtime format cannot be compared unless your platform time functions are 
timezone aware -- in which you have to store any datetime value that is in 
string format with a UTC offset and then "convert" the datetime to utc datetime 
or utc epoch offset before comparing it with anything.  Strings with embeded 
UTC offsets are cannot be compared unless all the offsets are the same.)

> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of David Raymond
> Sent: Friday, 21 October, 2016 13:43
> To: SQLite mailing list
> Subject: Re: [sqlite] Troubles matching variable as type TIMESTAMP
> 
> "Python 3.19"?
> 
> SQLite doesn't have a set datetime record format. It's up to you to
> standardize the input. There're some built-in functions to help out, but
> you have to format it yourself. If you're doing them as standardized
> strings, ('2016-10-21 15:40:14')  then when you're retrieving them from
> the database you can run the resulting string through strptime to get a
> Python datetime. And when you're inserting or comparing from a Python
> datetime, you should use strftime on the Python datetime to turn it into
> the appropriate string before passing it to SQLite, as it will do a
> textual comparison against the other text entries in there.
> 
> 
> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Rick Kohrs
> Sent: Friday, October 21, 2016 3:05 PM
> To: sqlite-users@mailinglists.sqlite.org
> Subject: [sqlite] Troubles matching variable as type TIMESTAMP
> 
> Using Python 3.19.
> I am reading in a lines from an ever growing log file. Values from each
> line of the log file are parsed and placed into a database. Each record
> has a variable of type TIMESTAMP. Multiple records have the same the
> same value for time stamp.
> After a line is processed, I need to check if there are multiple records
> with the same time stamp and count the total number of records returned.
> I'm struggling trying to create a select clause to match a variable of
> type TIMESTAMP.
> 
> Example Record
> (datetime.datetime(2016, 10, 13, 8, 10),
> u'HS_H08_20161013_0810_B09_JP03_R20_S0101.DAT', u'Himawari8', 2016, 10,
> 13, 8, 10, 9, u'Japan', -1, 3, u'test')
> 
> Code snipits:
> 
> #HS_H08_20161013__B01_R304_R10_S0101.DAT
> 
>  imageInfo['year'] = int(filenameVals[2][0:4])
>  imageInfo['month'] = int(filenameVals[2][4:6])
>  imageInfo['day'] = int(filenameVals[2][6:8])
> 
>  imageInfo['hour'] = int(filenameVals[3][0:2])
>  imageInfo['minute'] = int(filenameVals[3][2:4])
>  imageInfo['band'] = int(filenameVals[4][2:4])
> 
>  string_date = (str(imageInfo['year']) + '-' +
> str(imageInfo['month']) + '-' +
> str(imageInfo['day']) + ' ' +
> str(imageInfo['hour']) + ':' +
> str(imageInfo['minute']) + ':00.0')
>  imageInfo['dateTime'] = datetime.datetime.strptime(string_date,
> "%Y-%m-%d %H:%M:%S.%f")
> 
> ...
>  sqlCommand =  """
>  CREATE TABLE himawari_db (
>  date_time  TIMESTAMP,
> 
>  checkTime = imageInfo['dateTime']
> 
>  print(checkTime)
>  try:
>  satDB.execute("SELECT * FROM himawari_db WHERE date_time=?
> ",(checkTime,))
> 
> 
> 
> 
> ___
> 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



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

Re: [sqlite] Troubles matching variable as type TIMESTAMP

SQLite does not have a type of "TIMESTAMP".  Only TEXT, INTEGER, REAL, and 
BLOB.  They type of the data in the field depends on what you are storing in 
it.  What are you storing in it (you cannot store Python objects, such as a 
datetime object, in an SQLite database, only TEXT, INTEGER, REAL or BLOBs).

> ...
>  sqlCommand =  """
>  CREATE TABLE himawari_db (
>  date_time  TIMESTAMP,
> 

Once you know what you are storing, you will know how to compose a where clause.





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

Re: [sqlite] Troubles matching variable as type TIMESTAMP

2016-10-21 Thread David Raymond

"Python 3.19"?

SQLite doesn't have a set datetime record format. It's up to you to standardize 
the input. There're some built-in functions to help out, but you have to format 
it yourself. If you're doing them as standardized strings, ('2016-10-21 
15:40:14')  then when you're retrieving them from the database you can run the 
resulting string through strptime to get a Python datetime. And when you're 
inserting or comparing from a Python datetime, you should use strftime on the 
Python datetime to turn it into the appropriate string before passing it to 
SQLite, as it will do a textual comparison against the other text entries in 
there.


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Rick Kohrs
Sent: Friday, October 21, 2016 3:05 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] Troubles matching variable as type TIMESTAMP

Using Python 3.19.
I am reading in a lines from an ever growing log file. Values from each 
line of the log file are parsed and placed into a database. Each record 
has a variable of type TIMESTAMP. Multiple records have the same the 
same value for time stamp.
After a line is processed, I need to check if there are multiple records 
with the same time stamp and count the total number of records returned. 
I'm struggling trying to create a select clause to match a variable of 
type TIMESTAMP.

Example Record
(datetime.datetime(2016, 10, 13, 8, 10), 
u'HS_H08_20161013_0810_B09_JP03_R20_S0101.DAT', u'Himawari8', 2016, 10, 
13, 8, 10, 9, u'Japan', -1, 3, u'test')

Code snipits:

#HS_H08_20161013__B01_R304_R10_S0101.DAT

 imageInfo['year'] = int(filenameVals[2][0:4])
 imageInfo['month'] = int(filenameVals[2][4:6])
 imageInfo['day'] = int(filenameVals[2][6:8])

 imageInfo['hour'] = int(filenameVals[3][0:2])
 imageInfo['minute'] = int(filenameVals[3][2:4])
 imageInfo['band'] = int(filenameVals[4][2:4])

 string_date = (str(imageInfo['year']) + '-' +
str(imageInfo['month']) + '-' +
str(imageInfo['day']) + ' ' +
str(imageInfo['hour']) + ':' +
str(imageInfo['minute']) + ':00.0')
 imageInfo['dateTime'] = datetime.datetime.strptime(string_date, 
"%Y-%m-%d %H:%M:%S.%f")

...
 sqlCommand =  """
 CREATE TABLE himawari_db (
 date_time  TIMESTAMP,

 checkTime = imageInfo['dateTime']

 print(checkTime)
 try:
 satDB.execute("SELECT * FROM himawari_db WHERE date_time=? 
",(checkTime,))




___
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

Re: [sqlite] 2 consecutive rises in value

2016-10-21 Thread Bart Smissaert

> so that one can find more than one row for the same values of (Id,Date)?
Yes and that messes up your idea. Solution is to tidy that table up first
before running any select SQL.

> Average, last one, largest one?
For now I have taken the largest one as in R Smith's SQL. Probably it is
better to keep the last value, so the one with the highest ROWID.

RBS

On Fri, Oct 21, 2016 at 7:59 PM, Jean-Luc Hainaut <
jean-luc.hain...@unamur.be> wrote:

> > I had a look at this and tried it without using the extra auto-increment
> field, using the table ROWID
> > instead to check for consecutiveness. It would work great and a lot
> faster indeed if it wasn't for
> > the multiple values on the same date. Problem with that is that if you
> group by date it may
> > skip a ROWID, so it won't pick up that triple. The answer is just to
> clean that table up and
> > clear these extra values on the same day. This will always need to be
> done in any case, so
> > it might as well be done as a one off rather than in every select SQL.
> Thanks for that idea,
> > it looks to me the fastest. RBS
>
> With a complexity of O(N.logN) this algorithm is the second best to a pure
> sequential scan that compares the successive triples, which is in O(N).
> Practically, considering the locality of accesses for the join (the 3 rows
> to join most often are in the same page) and the small size of the primary
> index, the performance should be close to that of a sequential scan.
>
> When you write "if it wasn't for the multiple values on the same date", do
> you mean that the values of (Id,Date) are not unique among the rows of
> TABLE1, so that one can find more than one row for the same values of
> (Id,Date)? In this case, how do you compute the unique Value of this couple
> (Id,Date)? Average, last one, largest one?
>
> JLH
>
>
> ___
> 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

[sqlite] Troubles matching variable as type TIMESTAMP


Using Python 3.19.
I am reading in a lines from an ever growing log file. Values from each 
line of the log file are parsed and placed into a database. Each record 
has a variable of type TIMESTAMP. Multiple records have the same the 
same value for time stamp.
After a line is processed, I need to check if there are multiple records 
with the same time stamp and count the total number of records returned. 
I'm struggling trying to create a select clause to match a variable of 
type TIMESTAMP.


Example Record
(datetime.datetime(2016, 10, 13, 8, 10), 
u'HS_H08_20161013_0810_B09_JP03_R20_S0101.DAT', u'Himawari8', 2016, 10, 
13, 8, 10, 9, u'Japan', -1, 3, u'test')


Code snipits:

#HS_H08_20161013__B01_R304_R10_S0101.DAT

imageInfo['year'] = int(filenameVals[2][0:4])
imageInfo['month'] = int(filenameVals[2][4:6])
imageInfo['day'] = int(filenameVals[2][6:8])

imageInfo['hour'] = int(filenameVals[3][0:2])
imageInfo['minute'] = int(filenameVals[3][2:4])
imageInfo['band'] = int(filenameVals[4][2:4])

string_date = (str(imageInfo['year']) + '-' +
   str(imageInfo['month']) + '-' +
   str(imageInfo['day']) + ' ' +
   str(imageInfo['hour']) + ':' +
   str(imageInfo['minute']) + ':00.0')
imageInfo['dateTime'] = datetime.datetime.strptime(string_date, 
"%Y-%m-%d %H:%M:%S.%f")


...
sqlCommand =  """
CREATE TABLE himawari_db (
date_time  TIMESTAMP,

checkTime = imageInfo['dateTime']

print(checkTime)
try:
satDB.execute("SELECT * FROM himawari_db WHERE date_time=? 
",(checkTime,))





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

Re: [sqlite] 2 consecutive rises in value

2016-10-21 Thread Jean-Luc Hainaut

> I had a look at this and tried it without using the extra 
auto-increment field, using the table ROWID
> instead to check for consecutiveness. It would work great and a lot 
faster indeed if it wasn't for
> the multiple values on the same date. Problem with that is that if 
you group by date it may
> skip a ROWID, so it won't pick up that triple. The answer is just to 
clean that table up and
> clear these extra values on the same day. This will always need to be 
done in any case, so
> it might as well be done as a one off rather than in every select 
SQL. Thanks for that idea,

> it looks to me the fastest. RBS

With a complexity of O(N.logN) this algorithm is the second best to a 
pure sequential scan that compares the successive triples, which is in 
O(N). Practically, considering the locality of accesses for the join 
(the 3 rows to join most often are in the same page) and the small size 
of the primary index, the performance should be close to that of a 
sequential scan.


When you write "if it wasn't for the multiple values on the same date", 
do you mean that the values of (Id,Date) are not unique among the rows 
of TABLE1, so that one can find more than one row for the same values of 
(Id,Date)? In this case, how do you compute the unique Value of this 
couple (Id,Date)? Average, last one, largest one?


JLH

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

Re: [sqlite] Drop Existing Table Results in 'no such table'

2016-10-21 Thread David Raymond

Using the writable_schema pragma "only" makes sqlite_master treated like any 
other table for  queries, it doesn't introduce extra logic to check for 
needed re-parsing or other  controlled stuff. So running an UPDATE 
query on sqlite_master becomes just like any  UPDATE on any normal table. 
It's seen as changing some text or numbers etc in some random table, and that's 
it. And since normal everyday  updates are not seen as changing the 
format of the schema, the  doesn't waste resources in re-parsing the 
schema, or in updating the file header with a new "schema cookie" value. Only 
queries that would have the  read or alter sqlite_master  
are the things that would trigger the  to re-parse and update the file 
header. So CREATE, DROP, ALTER etc, or analyze sqlite_master.

(Appologies if I used too many <>'s for  in plain text)


I think that's all correct anyway. Though I have been known to be wrong.

(You know, I used to be indecisive, but now I'm not so sure...)


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of sanhua.zh
Sent: Friday, October 21, 2016 3:24 AM
To: SQLite mailing list
Subject: Re: [sqlite] Drop Existing Table Results in 'no such table'

Hello Simon,
I try it again and I find that I can update the memory of connection itself by 
changing any other connections' schema_version.
This kind of behavior make me confused. Since it can tell other connections’ 
that the schema is changed, why can’t it tell to itself ?




原始邮件
发件人:sanhua.zhsanhua...@foxmail.com
收件人:SQLite mailing listsqlite-us...@mailinglists.sqlite.org
发送时间:2016年10月21日(周五) 14:48
主题:Re: [sqlite] Drop Existing Table Results in 'no such table'


You are changing the details saved on disk but not the copy in memory. Since 
reopen a new connection is a ugly way, is there any other way to update the 
memory ? I know that sqlite3InitCallback may update it, but I can’t find a way 
to invoke it. 原始邮件 发件人:Simon slavinslav...@bigfraud.org 收件人:SQLite mailing 
listsqlite-us...@mailinglists.sqlite.org 发送时间:2016年10月21日(周五) 14:39 主题:Re: 
[sqlite] Drop Existing Table Results in 'no such table' On 21 Oct 2016, at 
4:42am, sanhua.zh sanhua...@foxmail.com wrote: I am trying to rename a table by 
editing the sqlite_master directly. I do know there are a ‘ALTER TABLE … RENAME 
TO …’ syntax to do that. I just want to try it in this way. But I find that the 
‘no such table’ error occurs while I try to drop the renamed table. SQLite does 
not look at the sqlite_master table before executing each command. It has a 
copy of the database schema in the memory allocated to the connection. You are 
changing the details saved on disk but not the copy in memory. Only I close the 
connection or start a new connection, the renamed table can be dropped. That 
makes sense. Simon. ___ 
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
___
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

Re: [sqlite] 2 consecutive rises in value

2016-10-21 Thread Bart Smissaert

> select distinct T1.Id-- only one per Id
> from   TABLE1 T1, TABLE1 T2, TABLE1 T3   -- very fast join on
indexed rowid
> where  T2.Seq = T1.Seq+1 and T3.Seq = T1.Seq+2   -- consecutive
triples
> andT1.Id = T2.Id and T3.Id + T1.Id   -- same Id
> andT1.Value < T2.Value and T2.Value < T3.Value;  -- ascending Values

I had a look at this and tried it without using the extra auto-increment
field, using the table ROWID instead to check for consecutiveness.
It would work great and a lot faster indeed if it wasn't for the multiple
values on the same date. Problem with that is that if you group by date
it may skip a ROWID, so it won't pick up that triple. The answer is just to
clean that table up and clear these extra values on the same day.
This will always need to be done in any case, so it might as well be done
as a one off rather than in every select SQL.
Thanks for that idea, it looks to me the fastest.

RBS



On Thu, Oct 20, 2016 at 4:55 PM, Jean-Luc Hainaut <
jean-luc.hain...@unamur.be> wrote:

>
> What about this one?
>
> create table TABLE1(Seq integer primary key autoincrement,
> Id integer, Date date, Value integer);
> insert into TABLE1(Id,Date,Value) values(2,'2004-06-23', 42), (...), ...;
>
> select distinct T1.Id-- only one per Id
> from   TABLE1 T1, TABLE1 T2, TABLE1 T3   -- very fast join on
> indexed rowid
> where  T2.Seq = T1.Seq+1 and T3.Seq = T1.Seq+2   -- consecutive triples
> andT1.Id = T2.Id and T3.Id + T1.Id   -- same Id
> andT1.Value < T2.Value and T2.Value < T3.Value;  -- ascending Values
>
> Works if:
> - the rows are inserted in ascending values of (Id,Date) (if they don't,
> just create and load a temp table with the sorted rows of TABLE1)
> - and if the rows are unique on (Id,Date).
>
> If rowid column Seq cannot be added to source TABLE1, load data in temp
> table TABLE2 with columns (Seq,Id,Date,Value).
>
> Probably not as fast as a purely procedural algorithm and less elegant
> than previous proposals,
> but probably faster than previous proposals and more elegant than a purely
> procedural one!
>
> J-L Hainaut
>
>
> ___
> 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

Re: [sqlite] Drop Existing Table Results in 'no such table'

2016-10-21 Thread David Raymond

I believe "analyze sqlite_master;" will force a re-parse without requiring a 
new connection. Try it and let us know.

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of sanhua.zh
Sent: Friday, October 21, 2016 2:49 AM
To: SQLite mailing list
Subject: Re: [sqlite] Drop Existing Table Results in 'no such table'

You are changing the details saved on disk but not the copy in memory.
Since reopen a new connection is a ugly way, is there any other way to update 
the memory ?
I know that sqlite3InitCallback may update it, but I can’t find a way to invoke 
it.

原始邮件
发件人:Simon slavinslav...@bigfraud.org
收件人:SQLite mailing listsqlite-us...@mailinglists.sqlite.org
发送时间:2016年10月21日(周五) 14:39
主题:Re: [sqlite] Drop Existing Table Results in 'no such table'

On 21 Oct 2016, at 4:42am, sanhua.zh sanhua...@foxmail.com wrote:  I am trying 
to rename a table by editing the sqlite_master directly. I do know there are a 
‘ALTER TABLE … RENAME TO …’ syntax to do that. I just want to try it in this 
way.  But I find that the ‘no such table’ error occurs while I try to drop the 
renamed table. SQLite does not look at the sqlite_master table before executing 
each command. It has a copy of the database schema in the memory allocated to 
the connection. You are changing the details saved on disk but not the copy in 
memory.  Only I close the connection or start a new connection, the renamed 
table can be dropped. That makes sense. Simon. 
___ 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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Drop Existing Table Results in 'no such table'

2016-10-21 Thread Jens Alfke


> On Oct 20, 2016, at 11:40 PM, Clemens Ladisch  wrote:
> 
> sanhua.zh wrote:
>> I am trying to rename a table by editing the sqlite_master directly.
> 
> Don't do this.

Patient: “Doctor, my arm hurts when I do this!”
Doctor: “Then don’t do that.”

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

Re: [sqlite] sqlite3 db is encrypted how to decrypt

2016-10-21 Thread Jens Alfke

> On Oct 21, 2016, at 12:16 AM, ravi.shan...@cellworksgroup.com wrote:
> 
>I have a sqlite3 db which is encrypted using lib.so file how to 
> decrypt the db. If i access the db it print db is encrypted or not a 
> database. I don't have any idea about lib.so file and encryption used in it.

Neither would we. “lib.so” seems like a very generic name, not any specific 
library. And it doesn’t matter that this is an SQLite file, since the 
encryption is up to that library and not SQLite.

 Even if you did know what the library is, that wouldn’t help; you’d need to 
know the key that was used to encrypt it. Unless you have the key, you may as 
well give up; that’s the whole point of encryption.

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

Re: [sqlite] Risk of damaging a non-sqlite file with `sqlite3 `?

2016-10-21 Thread Wade, William

> From: Clemens Ladisch [mailto:clem...@ladisch.de]
> Sent: Friday, October 21, 2016 1:31 AM
> It would be possible to open a 'wrong' file only if someone had created a 
> fake database file deliberately.

I think that is generally correct, but "possible ... only if" is perhaps a bit 
strong.

sqlite3.exe is willing to open and modify an existing empty file. In some sense 
that means that all empty files are sqlite databases. Often, the creator of the 
file had something else in mind.

If I run the unix command

split --bytes=360K foo.sqlite foo.sqlite

so that I can archive the file onto a collection of floppy disks, one of the 
files I get will be named foo.sqliteaa, and that file will have the sqlite 
header string. I'd call it a stretch to say that I deliberately created a fake 
database file.

Regards,
Bill

**
This e-mail and any attachments thereto may contain confidential information 
and/or information protected by intellectual property rights for the exclusive 
attention of the intended addressees named above. If you have received this 
transmission in error, please immediately notify the sender by return e-mail 
and delete this message and its attachments. Unauthorized use, copying or 
further full or partial distribution of this e-mail or its contents is 
prohibited.
**
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Typical suffixes of sqlite database files

2016-10-21 Thread jonathon

On 20/10/2016 23:17, Rolf Ade wrote:

> What suffixes to sqlite database files do you use or see in the wild?

Too many for suffix identification to be a viable option.

One program I use, has 20 different suffixes, for the SQLite databases
that it utilizes. Most of its competitors use between 3 and 5 suffixes
for the SQLite databases that they utilize.

Use the magic number.  Much more reliable, easier to maintain, and less
chance of a false positive.

jonathon

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

Re: [sqlite] Drop Existing Table Results in 'no such table'

2016-10-21 Thread sanhua.zh

Hello Simon,
I try it again and I find that I can update the memory of connection itself by
changing any other connections' schema_version.
This kind of behavior make me confused. Since it can tell other connections’
that the schema is changed, why can’t it tell to itself ?

原始邮件
发件人:sanhua.zhsanhua...@foxmail.com
收件人:SQLite mailing listsqlite-us...@mailinglists.sqlite.org
发送时间:2016年10月21日(周五) 14:48
主题:Re: [sqlite] Drop Existing Table Results in 'no such table'

You are changing the details saved on disk but not the copy in memory. Since
reopen a new connection is a ugly way, is there any other way to update the
memory ? I know that sqlite3InitCallback may update it, but I can’t find a way
to invoke it. 原始邮件 发件人:Simon slavinslav...@bigfraud.org 收件人:SQLite mailing
listsqlite-us...@mailinglists.sqlite.org 发送时间:2016年10月21日(周五) 14:39 主题:Re:
[sqlite] Drop Existing Table Results in 'no such table' On 21 Oct 2016, at
4:42am, sanhua.zh sanhua...@foxmail.com wrote: I am trying to rename a table by
editing the sqlite_master directly. I do know there are a ‘ALTER TABLE … RENAME
TO …’ syntax to do that. I just want to try it in this way. But I find that the
‘no such table’ error occurs while I try to drop the renamed table. SQLite does
not look at the sqlite_master table before executing each command. It has a
copy of the database schema in the memory allocated to the connection. You are
changing the details saved on disk but not the copy in memory. Only I close the
connection or start a new connection, the renamed table can be dropped. That
makes sense. Simon. ___
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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite3 db is encrypted how to decrypt

2016-10-21 Thread ravi.shan...@cellworksgroup.com


Hi guys,

I have a sqlite3 db which is encrypted using lib.so file 
how to decrypt the db. If i access the db it print db is encrypted or 
not a database. I don't have any idea about lib.so file and encryption 
used in it. Does lib.so file uses any algorithm for encryption. Any 
suggestions is helpful.

*
The properties of the file is listed below :*

1.Type - unknown.
2.MIME type - application/octet-stream.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Drop Existing Table Results in 'no such table'

2016-10-21 Thread Rowan Worth

On 21 October 2016 at 14:49, sanhua.zh  wrote:

> Don't do this.
> I don’t think so. It is written on the sqlite.com. So it should be a
> tricky way but not a wrong way.
>

The docs say, regarding the procedure you followed:

> The following simpler procedure is appropriate for removing CHECK
> or FOREIGN KEY or NOT NULL constraints, renaming columns, or
> adding or removing or changing default values on a column.

Note that "renaming tables" is not included here. You should probably use
ALTER TABLE for that :)

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

Re: [sqlite] Typical suffixes of sqlite database files


On 21 Oct 2016, at 12:17am, Rolf Ade  wrote:

> What suffixes to sqlite database files do you use or see in the wild?

If you're writing a general utility for SQLite databases you can not depend on 
anything.  The two suffixes '.db' and '.dat' can mean anything at all.

The Unix 'magic' utility looks for the SQLite file header.  It checks the 
beginning of the file for "SQLite format 3" I think.  This is a far better test 
than depending on the extension.

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

Re: [sqlite] Drop Existing Table Results in 'no such table'

2016-10-21 Thread sanhua.zh

Don't do this.
I don’t think so. It is written on the sqlite.com. So it should be a tricky way 
but not a wrong way.
All we need to do is testing enough.




原始邮件
发件人:Clemens ladischclem...@ladisch.de
收件人:sqlite-userssqlite-us...@mailinglists.sqlite.org
发送时间:2016年10月21日(周五) 14:40
主题:Re: [sqlite] Drop Existing Table Results in 'no such table'


sanhua.zh wrote:  I am trying to rename a table by editing the sqlite_master 
directly. Don't do this.  But I find that the ‘no such table’ error occurs 
while I try to drop  the renamed table. Only I close the connection or start a 
new  connection, the renamed table can be dropped. Re-opening the correction is 
the only way to make the changes visible. (I did not include the word 
"supported" in the previous sentence because there is nothing supported about 
the writable_schema pragma.) Regards, Clemens 
___ 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

Re: [sqlite] Drop Existing Table Results in 'no such table'

2016-10-21 Thread sanhua.zh

You are changing the details saved on disk but not the copy in memory.
Since reopen a new connection is a ugly way, is there any other way to update 
the memory ?
I know that sqlite3InitCallback may update it, but I can’t find a way to invoke 
it.




原始邮件
发件人:Simon slavinslav...@bigfraud.org
收件人:SQLite mailing listsqlite-us...@mailinglists.sqlite.org
发送时间:2016年10月21日(周五) 14:39
主题:Re: [sqlite] Drop Existing Table Results in 'no such table'


On 21 Oct 2016, at 4:42am, sanhua.zh sanhua...@foxmail.com wrote:  I am trying 
to rename a table by editing the sqlite_master directly. I do know there are a 
‘ALTER TABLE … RENAME TO …’ syntax to do that. I just want to try it in this 
way.  But I find that the ‘no such table’ error occurs while I try to drop the 
renamed table. SQLite does not look at the sqlite_master table before executing 
each command. It has a copy of the database schema in the memory allocated to 
the connection. You are changing the details saved on disk but not the copy in 
memory.  Only I close the connection or start a new connection, the renamed 
table can be dropped. That makes sense. Simon. 
___ 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

Re: [sqlite] Drop Existing Table Results in 'no such table'

2016-10-21 Thread Clemens Ladisch

sanhua.zh wrote:
> I am trying to rename a table by editing the sqlite_master directly.

Don't do this.

> But I find that the ‘no such table’ error occurs while I try to drop
> the renamed table. Only I close the connection or start a new
> connection, the renamed table can be dropped.

Re-opening the correction is the only way to make the changes visible.
(I did not include the word "supported" in the previous sentence because
there is nothing supported about the writable_schema pragma.)

Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Drop Existing Table Results in 'no such table'