Re: [sqlite] Using IGNORE for complete record match
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
> 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
"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
> 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
> 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'
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
> 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'
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'
> 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
> 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 `?
> 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
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'
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
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'
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