[sqlite] Unique Constraint Failed
Hi all, I realize that this is the constraint that is failing. The data is very large, encrypted and at a customer's site - not easy to use an external program to view or to transfer to my office. What I am wondering is if there are any limits on the Unique table that is maintained or if any of the code that is used for this has been changed in the last couple of years. Andrew Stewart Software Designer ARGUS CONTROLS 18445 53rd Avenue | Surrey, BC | V3S 7A4 | Canada t +1.604.538.3531 ext. 108 | +1.800.667.2090 | f +1.604.538.4728 www.arguscontrols.com<http://www.arguscontrols.com/> Notice: This electronic transmission contains confidential information, intended only for the person(s) named above. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, or any other use of this email is strictly prohibited. If you have received this transmission by error, please notify us immediately by return email and destroy the original transmission immediately and all copies thereof. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Unique Constraint Failed
Hi Simon, I am having a problem receiving the emails and therefore cannot do this as a reply to the message. I am seeing your responses on the forum site. Below is the DDL for creating the table. It should not be possible for 2 elements to have the same time/ID unless I am redoing the data. This data comes live from a greenhouse control system. CREATE TABLE dataStreamRecord ( fwParameterID INTEGER NOT NULL, dateTime INTEGER NOT NULL, data INTEGER NOT NULL, UNIQUE ( fwParameterID, dateTime ) ); Andrew Stewart Software Designer ARGUS CONTROLS 18445 53rd Avenue | Surrey, BC | V3S 7A4 | Canada t +1.604.538.3531 ext. 108 | +1.800.667.2090 | f +1.604.538.4728 www.arguscontrols.com<http://www.arguscontrols.com/> Notice: This electronic transmission contains confidential information, intended only for the person(s) named above. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, or any other use of this email is strictly prohibited. If you have received this transmission by error, please notify us immediately by return email and destroy the original transmission immediately and all copies thereof. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Unique Constraint Failed
Hi, I am having problems with a database reporting Unique Constraint Failed when doing an insert. Table consists of 3 columns: ID, DateTime, data Constraint is on ID,DateTime. DateTime trying to enter is current time. File is 200+ GB. I have tested this same to a 1.4TB file, but have updated my copy of SQLite source since that test. Thanks, Andrew Stewart Software Designer ARGUS CONTROLS 18445 53rd Avenue | Surrey, BC | V3S 7A4 | Canada t +1.604.538.3531 ext. 108 | +1.800.667.2090 | f +1.604.538.4728 www.arguscontrols.com<http://www.arguscontrols.com/> Notice: This electronic transmission contains confidential information, intended only for the person(s) named above. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, or any other use of this email is strictly prohibited. If you have received this transmission by error, please notify us immediately by return email and destroy the original transmission immediately and all copies thereof. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Create Index after Data Populated
Hi, I have a database that is populated with data (and more being added) and I have realized that I need an extra index to speed up access. How long should it take to update the Index. There are about 1billion rows in the database Thanks, Andrew Stewart Software Designer ARGUS CONTROLS 18445 53rd Avenue | Surrey, BC | V3S 7A4 | Canada t +1.604.538.3531 ext. 108 | +1.800.667.2090 | f +1.604.538.4728 www.arguscontrols.com<http://www.arguscontrols.com/> Notice: This electronic transmission contains confidential information, intended only for the person(s) named above. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, or any other use of this email is strictly prohibited. If you have received this transmission by error, please notify us immediately by return email and destroy the original transmission immediately and all copies thereof. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 1TB limit on encrypted database
My code actually sets this to 2048, but it doesn't appear to stick (or get used). I am just setting using the PRAGMA command (same as I am now doing the max_page_count). Could this be an issue with an older copy of the Encryption package? I have not updated yet - am in the middle of other work. Andrew Stewart Software Designer Argus Control Systems Ltd. Tel: (604) 536-9100 ext. 108 astew...@arguscontrols.com #101 - 18445 53rd Avenue | Surrey BC | CANADA | V3S 7A4 (604) 538-3531 | (800) 667-2090 | Fax (604) 538-4728 www.arguscontrols.com -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Richard Hipp Sent: Wednesday, November 15, 2017 7:18 AM To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Subject: Re: [sqlite] 1TB limit on encrypted database On 11/15/17, Andrew Stewart <astew...@arguscontrols.com> wrote: > Thanks. This has worked. Temporarily. You cannot increase the max_page_count above 2147483646, so if your database continues to grow, you are going to need to look into increasing the page size from 1024 to something larger like 8192. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users Notice: This electronic transmission contains confidential information, intended only for the person(s) named above. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, or any other use of this email is strictly prohibited. If you have received this transmission by error, please notify us immediately by return email and destroy the original transmission immediately and all copies thereof. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 1TB limit on encrypted database
Thanks. This has worked. Andrew Stewart Argus Control Systems Ltd. -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Richard Hipp Sent: Tuesday, November 14, 2017 12:34 PM To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Subject: Re: [sqlite] 1TB limit on encrypted database On 11/14/17, Andrew Stewart <astew...@arguscontrols.com> wrote: > Richard, > If I am trying to change the max_page_count use the SEE.EXE > program, how do I get the value to be updated in the database. Every > time I try changing this and then exit see.exe, the value has not > changed. Do I need the updated encryption extension first? The PRAGMA only changes the one database connection in which it runs. To make the setting persistent, recompile with -DSQLITE_MAX_PAGE_COUNT=20 -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users Notice: This electronic transmission contains confidential information, intended only for the person(s) named above. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, or any other use of this email is strictly prohibited. If you have received this transmission by error, please notify us immediately by return email and destroy the original transmission immediately and all copies thereof. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 1TB limit on encrypted database
Richard, If I am trying to change the max_page_count use the SEE.EXE program, how do I get the value to be updated in the database. Every time I try changing this and then exit see.exe, the value has not changed. Do I need the updated encryption extension first? Andrew Stewart Argus Control Systems Ltd. -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Richard Hipp Sent: Tuesday, November 14, 2017 11:33 AM To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Subject: Re: [sqlite] 1TB limit on encrypted database On 11/14/17, Andrew Stewart <astew...@arguscontrols.com> wrote: > Hi, > I appear to have reached an issue with a 1TB limit on > an encrypted database. The page_size is 1024. The max_page_count is > 1073741823. I don't appear to be able to change either of these numbers. > Any assistance would be appreciated. You can try: PRAGMA max_page_count=20; Probably you should also look into increasing the page size: PRAGMA page_size=8192; VACUUM; For that last step, make sure you have upgraded to a recent version of the encryption extension as some of the older versions had issues. Also, note that the VACUUM will take some time and will require a couple TB of temporary disk space while it is running. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users Notice: This electronic transmission contains confidential information, intended only for the person(s) named above. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, or any other use of this email is strictly prohibited. If you have received this transmission by error, please notify us immediately by return email and destroy the original transmission immediately and all copies thereof. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] 1TB limit on encrypted database
Hi, I appear to have reached an issue with a 1TB limit on an encrypted database. The page_size is 1024. The max_page_count is 1073741823. I don't appear to be able to change either of these numbers. Any assistance would be appreciated. Andrew Stewart Argus Control Systems Ltd. Notice: This electronic transmission contains confidential information, intended only for the person(s) named above. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, or any other use of this email is strictly prohibited. If you have received this transmission by error, please notify us immediately by return email and destroy the original transmission immediately and all copies thereof. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unresolved External sqlite3_user_add
Hi,
Thanks for the response. That worked. Don't do enough work between C++ and C
to remember to use the #ifdef statements. Only 1 problem, the database can
still be opened and the data viewed by other programs that are not compiled
with SQLITE_USER_AUTHENTICATION. I am testing with SQLite Studio. It doesn't
see the password table, but does see all of the data, even data that is
actively changed from the application.
Is there a better way to do this?
What I am trying to do is hide my data.
Thanks
Andrew
-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On
Behalf Of Dan Kennedy
Sent: Tuesday, February 21, 2017 10:13 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] Unresolved External sqlite3_user_add
On 02/22/2017 12:47 AM, Andrew Stewart wrote:
> Hi,
> I am trying to add the authentication extension in and I am
> getting this message. I have included the 'userauth.c' at the bottom of the
> amalgamation. I have included 'sqlite3userauth.h' at the bottom of
> sqlite3.h. It finds it during the compile, but does not find it during the
> link. I added "pragma message()" lines to the sqlite3.c code to see that it
> was going into this block and it was. Have tried everything else that I can
> think of and still no luck.
> Any help would be appreciated.
Is SQLITE_USER_AUTHENTICATION defined at build time?
Or, are you using a C++ compiler? If so, you might need to add one of those
[extern "C"] blocks around the contents of sqlite3userauth.h. i.e.
#ifdef __cplusplus
extern "C" {
#endif
// contents of sqlite3userauth.h
#ifdef __cplusplus
}
#endif
Or just insert sqlite3userauth.h inside the [extern "C"] block that is already
in sqlite3.h.
Dan.
>
> Thanks,
> Andrew Stewart
> Software Designer
>
>
> Notice: This electronic transmission contains confidential information,
> intended only for the person(s) named above. If you are not the intended
> recipient, you are hereby notified that any disclosure, copying,
> distribution, or any other use of this email is strictly prohibited. If you
> have received this transmission by error, please notify us immediately by
> return email and destroy the original transmission immediately and all copies
> thereof.
> ___
> sqlite-users mailing list
> sqlite-users@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] Unresolved External sqlite3_user_add
Hi, I am trying to add the authentication extension in and I am getting this message. I have included the 'userauth.c' at the bottom of the amalgamation. I have included 'sqlite3userauth.h' at the bottom of sqlite3.h. It finds it during the compile, but does not find it during the link. I added "pragma message()" lines to the sqlite3.c code to see that it was going into this block and it was. Have tried everything else that I can think of and still no luck. Any help would be appreciated. Thanks, Andrew Stewart Software Designer Notice: This electronic transmission contains confidential information, intended only for the person(s) named above. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, or any other use of this email is strictly prohibited. If you have received this transmission by error, please notify us immediately by return email and destroy the original transmission immediately and all copies thereof. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Issue with Malformed table
Simon, Forgot to ask 1 thing. What is a quick way to copy an entire table from database1 to database2? Thanks, Andrew -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Andrew Stewart Sent: Wednesday, November 16, 2016 7:16 AM To: 'SQLite mailing list' <sqlite-users@mailinglists.sqlite.org> Subject: Re: [sqlite] Issue with Malformed table Simon, Thank you for the response. I will give it a try. I can do this with 2 different database connections and then just transfer all of the tables from database1 to database2. Close database1. Remove/rename the file for database1. Close database2. Rename file for database2 to file for database1. Open connection to database1. Andrew -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Simon Slavin Sent: Wednesday, November 16, 2016 7:07 AM To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Subject: Re: [sqlite] Issue with Malformed table On 16 Nov 2016, at 2:52pm, Andrew Stewart <astew...@arguscontrols.com> wrote: >I have a table that is giving me errors. I can recover the > data from another source, but need a way to clear the table. This process is very fast and easy, but it may not help with your form of corruption. Open the database in the SQLite Shell Tool (can be downloaded from the SQLite download page). Use the command .dump This sometimes allows you to extract data from corrupt tables. And sometimes does not. > Read row from table1 - detect error (SQL logic error or missing database). > Have been able to read other rows from same table successfully. > Create temporaryTable - for transferring good data from bad table to. Never do this. If one table in a SQLite database is showing corruption, regard the entire file as corrupt and do not attempt to make any changes to it at all, even deleting stuff from it. Any changes you make to it may result in making more of your original data un-rescuable. You do this because you may, for example, have one page of the corrupt database file marked both as in use by a table and as available for use with new data. So writing any new data to it could lose even more data from an existing table. So how do you rescue data from one database file and write it to another ? You open two different SQLite connections to two different SQLite database files. If your library doesn't allow that you can instead allow one SQLite connection to access two databases using the ATTACH command: <https://www.sqlite.org/lang_attach.html> So my recommendation, if you do want your own program to use the SQLite API to extract data from a corrupt database, is that you open two different connections to two different databases. If you can't do that, then make the connection to a new database in which you're going to store the rescued data, but then ATTACH to it the corrupt database which you will only read from, never write to. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users Notice: This electronic transmission contains confidential information, intended only for the person(s) named above. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, or any other use of this email is strictly prohibited. If you have received this transmission by error, please notify us immediately by return email and destroy the original transmission immediately and all copies thereof. ___ sqlite-users mailing list sqlite-users@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] Issue with Malformed table
Simon, Thank you for the response. I will give it a try. I can do this with 2 different database connections and then just transfer all of the tables from database1 to database2. Close database1. Remove/rename the file for database1. Close database2. Rename file for database2 to file for database1. Open connection to database1. Andrew -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Simon Slavin Sent: Wednesday, November 16, 2016 7:07 AM To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Subject: Re: [sqlite] Issue with Malformed table On 16 Nov 2016, at 2:52pm, Andrew Stewart <astew...@arguscontrols.com> wrote: >I have a table that is giving me errors. I can recover the > data from another source, but need a way to clear the table. This process is very fast and easy, but it may not help with your form of corruption. Open the database in the SQLite Shell Tool (can be downloaded from the SQLite download page). Use the command .dump This sometimes allows you to extract data from corrupt tables. And sometimes does not. > Read row from table1 - detect error (SQL logic error or missing database). > Have been able to read other rows from same table successfully. > Create temporaryTable - for transferring good data from bad table to. Never do this. If one table in a SQLite database is showing corruption, regard the entire file as corrupt and do not attempt to make any changes to it at all, even deleting stuff from it. Any changes you make to it may result in making more of your original data un-rescuable. You do this because you may, for example, have one page of the corrupt database file marked both as in use by a table and as available for use with new data. So writing any new data to it could lose even more data from an existing table. So how do you rescue data from one database file and write it to another ? You open two different SQLite connections to two different SQLite database files. If your library doesn't allow that you can instead allow one SQLite connection to access two databases using the ATTACH command: <https://www.sqlite.org/lang_attach.html> So my recommendation, if you do want your own program to use the SQLite API to extract data from a corrupt database, is that you open two different connections to two different databases. If you can't do that, then make the connection to a new database in which you're going to store the rescued data, but then ATTACH to it the corrupt database which you will only read from, never write to. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users Notice: This electronic transmission contains confidential information, intended only for the person(s) named above. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, or any other use of this email is strictly prohibited. If you have received this transmission by error, please notify us immediately by return email and destroy the original transmission immediately and all copies thereof. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Issue with Malformed table
Hi, I have a table that is giving me errors. I can recover the data from another source, but need a way to clear the table. The following is my process: Read row from table1 - detect error (SQL logic error or missing database). Have been able to read other rows from same table successfully. Create temporaryTable - for transferring good data from bad table to. Drop index on table1 - error reported - 'database disk image is malformed' Create index on temporaryTable - error reported - index already exists - caused because Drop Index failed Transfer rows from table1 to temporaryTable to until error occurs Drop table1 - error report - 'database disk image is malformed' Alter table - rename temporaryTable to table1 - error reported - item already exists - caused because Drop Table failed This process would work, if I could get the Drops to work. I think I know what is causing the original errors and I have fixed it, but I want to come up with a way to fix tables so I am testing this. Thanks, Andrew Stewart Software Designer Argus Control Systems Ltd. Tel: (604) 536-9100 ext. 108 astew...@arguscontrols.com<mailto:astew...@arguscontrols.com> #101 - 18445 53rd Avenue | Surrey BC | CANADA | V3S 7A4 (604) 538-3531 | (800) 667-2090 | Fax (604) 538-4728 www.arguscontrols.com<http://www.arguscontrols.com/> Notice: This electronic transmission contains confidential information, intended only for the person(s) named above. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, or any other use of this email is strictly prohibited. If you have received this transmission by error, please notify us immediately by return email and destroy the original transmission immediately and all copies thereof. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Drop Temporary table is returning Locked
Have resolved this. I only need a simple statement for adding the new column. ALTER TABLE argusTable1 ADD COLUMN newField INTEGER Thanks, Andrew -Original Message- From: sqlite-users-boun...@mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Andrew Stewart Sent: Friday, July 29, 2016 12:16 PM To: 'SQLite mailing list' <sqlite-users@mailinglists.sqlite.org> Subject: [sqlite] Drop Temporary table is returning Locked Hi, I am having issues when I need to translate a database that already exists (adding a field). I added the field using SQLiteStudio and it gives me the commands necessary to translate the database. When I employ these in my program, I get an error that I do not know how to work around. The sequence is this: ALTER TABLE argusTable1 RENAME TO argus_temp_table CREATE TABLE argusTable1 (tableID INTEGER PRIMARY KEY, newField INTEGER) INSERT INTO argusTable1 (tableID) SELECT (screenInstanceID) FROM argus_temp_table DROP TABLE argus_temp_table I run all 4 of these statements in separate sqlite3_exec statements. The first 3 run fine with no issue. When I try doing the 4th one I get a Table Locked error (6). If I loop to wait for it, it never returns. As the problem is with the temporary table, I wouldn't think any place else in my code would be causing this issue. I have tried running this SQLiteStudio, running each statement on its own, and there is no issue. Do not know what I am doing wrong. Following are my Pragmas used: PRAGMA synchronous = NORMAL PRAGMA journal_mode = MEMORY PRAGMA page_size = 2048 PRAGMA cache_size = 10 PRAGMA temp_store = MEMORY SQLite version 3.9.2 is what I am using. Any assistance would be appreciated. Andrew Stewart Software Designer Argus Control Systems Ltd. Tel: (604) 536-9100 ext. 108 astew...@arguscontrols.com<mailto:astew...@arguscontrols.com> #101 - 18445 53rd Avenue | Surrey BC | CANADA | V3S 7A4 (604) 538-3531 | (800) 667-2090 | Fax (604) 538-4728 www.arguscontrols.com<http://www.arguscontrols.com/> Notice: This electronic transmission contains confidential information, intended only for the person(s) named above. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, or any other use of this email is strictly prohibited. If you have received this transmission by error, please notify us immediately by return email and destroy the original transmission immediately and all copies thereof. ___ sqlite-users mailing list sqlite-users@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] Drop Temporary table is returning Locked
Hi, I am having issues when I need to translate a database that already exists (adding a field). I added the field using SQLiteStudio and it gives me the commands necessary to translate the database. When I employ these in my program, I get an error that I do not know how to work around. The sequence is this: ALTER TABLE argusTable1 RENAME TO argus_temp_table CREATE TABLE argusTable1 (tableID INTEGER PRIMARY KEY, newField INTEGER) INSERT INTO argusTable1 (tableID) SELECT (screenInstanceID) FROM argus_temp_table DROP TABLE argus_temp_table I run all 4 of these statements in separate sqlite3_exec statements. The first 3 run fine with no issue. When I try doing the 4th one I get a Table Locked error (6). If I loop to wait for it, it never returns. As the problem is with the temporary table, I wouldn't think any place else in my code would be causing this issue. I have tried running this SQLiteStudio, running each statement on its own, and there is no issue. Do not know what I am doing wrong. Following are my Pragmas used: PRAGMA synchronous = NORMAL PRAGMA journal_mode = MEMORY PRAGMA page_size = 2048 PRAGMA cache_size = 10 PRAGMA temp_store = MEMORY SQLite version 3.9.2 is what I am using. Any assistance would be appreciated. Andrew Stewart Software Designer Argus Control Systems Ltd. Tel: (604) 536-9100 ext. 108 astew...@arguscontrols.com<mailto:astew...@arguscontrols.com> #101 - 18445 53rd Avenue | Surrey BC | CANADA | V3S 7A4 (604) 538-3531 | (800) 667-2090 | Fax (604) 538-4728 www.arguscontrols.com<http://www.arguscontrols.com/> Notice: This electronic transmission contains confidential information, intended only for the person(s) named above. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, or any other use of this email is strictly prohibited. If you have received this transmission by error, please notify us immediately by return email and destroy the original transmission immediately and all copies thereof. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] The database disk image is malformed
Has anybody had a large file (78gb) on a compressed folder on Windows? I believe that this is where the problem is coming from and want to find out if anybody else has any experience with this. I am looking at this file being 1.6TB and was hoping to be able to compress to save some space. Thanks, Andrew -Original Message- From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Simon Slavin Sent: Thursday, January 07, 2016 8:38 AM To: SQLite mailing list Subject: Re: [sqlite] The database disk image is malformed On 7 Jan 2016, at 3:09pm, Andrew Stewart wrote: > I have run the Quick Check. The results are below. This database is written > to once a minute. One minute it was working and the next it was not. What > would cause it to suddenly have errors? Hardware fault. Loss of power. Another program overwriting parts of the file. Corruption of memory which only SQLite should be accessing. Other stuff. Are you checking the values returned by your SQLite API calls ? Were they all SQLITE_OK ? Simon. ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users Notice: This electronic transmission contains confidential information, intended only for the person(s) named above. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, or any other use of this email is strictly prohibited. If you have received this transmission by error, please notify us immediately by return email and destroy the original transmission immediately and all copies thereof.
[sqlite] The database disk image is malformed
age 9022708 cell 35: 2nd reference to page 77792124 On tree page 9022708 cell 34: 2nd reference to page 77791818 On tree page 9026909 cell 1: 2nd reference to page 77792121 On tree page 9026909 cell 44: 2nd reference to page 77791812 On tree page 6545734 cell 0: 2nd reference to page 6232064 On tree page 6545734 cell 43: 2nd reference to page 6232062 On tree page 23033976 cell 26: 2nd reference to page 75901962 On tree page 23033976 cell 45: 2nd reference to page 75898413 On tree page 23033976 cell 44: Child page depth differs On tree page 71968221 cell 11: 2nd reference to page 77794382 On tree page 71968221 cell 45: 2nd reference to page 77794042 On tree page 71968221 cell 44: 2nd reference to page 77793705 On tree page 69239670 cell 10: 2nd reference to page 45319543 On tree page 6755 cell 16: 2nd reference to page 77792050 On tree page 6755 cell 45: 2nd reference to page 77791736 On tree page 6755 cell 44: 2nd reference to page 77573749 On tree page 6755 cell 43: 2nd reference to page 77573414 On tree page 6755 cell 42: 2nd reference to page 77573067 On tree page 6755 cell 41: 2nd reference to page 77572717 On tree page 6755 cell 40: 2nd reference to page 77572370 On tree page 6755 cell 39: 2nd reference to page 77572056 On tree page 6755 cell 38: 2nd reference to page 77571738> Regards, Andrew Stewart -Original Message- From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Andrew Stewart Sent: Tuesday, January 05, 2016 8:12 AM To: 'SQLite mailing list' Subject: Re: [sqlite] The database disk image is malformed I am running the system with the Quick_Check pragma - takes a while - and error logging turned on. I will let you know the results from this when I have some. Compression Attribute is a feature of the file system. Just mentioned as it might be an issue. SQLite database is on the same drive as the application (different folder). Thanks, Andrew -Original Message- From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Stephen Chrzanowski Sent: Tuesday, January 05, 2016 7:42 AM To: SQLite mailing list Subject: Re: [sqlite] The database disk image is malformed All of what Richard asked and said, plus, my question of where the file stored in relation to the application? (Network attached storage?) On Tue, Jan 5, 2016 at 10:22 AM, Richard Hipp wrote: > On 1/5/16, Andrew Stewart wrote: > > Hi, > > I am getting the following error on a database. The > > database is 78GB large when this started. I am adding data to it > > via > insert > > statements. I believe that this is the second time that I have seen > this. > > It has been running for about 2 weeks adding data to it constantly. > > The database structure is simple. Following is the code for the > > create > table: > > CREATE TABLE dataStreamRecord ( > > fwParameterID INTEGER NOT NULL, > > dateTime INTEGER NOT NULL, > > data INTEGER NOT NULL, > > UNIQUE ( > > fwParameterID, > > dateTime > > ) > > ); > > FWIW, a more efficient schema might be: > > CREATE TABLE dataStreamRecord ( >fwParameterId INT, >dateTime INT, >data INT NOT NULL, >PRIMARY KEY(fwParameterId,dateTime) >) WITHOUT ROWID; > > > > > I am wondering what could cause this. The database > > does have the compression attribute turned on. The database > > time/date is > still > > getting adjusted. > > "compression attribute"? SQLite doesn't have any such thing. Is this > a feature of your filesystem? > > Have you run "PRAGMA quick_check" on the faulty database to gather > more information about the problem? > > Have you enabled error logging as described at > (https://www.sqlite.org/errlog.html)? > > -- > D. Richard Hipp > drh at sqlite.org > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users Notice: This electronic transmission contains confidential information, intended only for the person(s) named above. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, or any other use of this email is strictly prohibited. If you have received this transmission by error, please notify us immediately by return email and destroy the original transmission immediately and all copies thereof. ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] The database disk image is malformed
Hi, I am getting the following error on a database. The database is 78GB large when this started. I am adding data to it via insert statements. I believe that this is the second time that I have seen this. It has been running for about 2 weeks adding data to it constantly. The database structure is simple. Following is the code for the create table: CREATE TABLE dataStreamRecord ( fwParameterID INTEGER NOT NULL, dateTime INTEGER NOT NULL, data INTEGER NOT NULL, UNIQUE ( fwParameterID, dateTime ) ); I am wondering what could cause this. The database does have the compression attribute turned on. The database time/date is still getting adjusted. Andrew Stewart Software Designer Argus Control Systems Ltd. Tel: (604) 536-9100 ext. 108 astewart at arguscontrols.com<mailto:astewart at arguscontrols.com> #101 - 18445 53rd Avenue | Surrey BC | CANADA | V3S 7A4 (604) 538-3531 | (800) 667-2090 | Fax (604) 538-4728 www.arguscontrols.com<http://www.arguscontrols.com/> Notice: This electronic transmission contains confidential information, intended only for the person(s) named above. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, or any other use of this email is strictly prohibited. If you have received this transmission by error, please notify us immediately by return email and destroy the original transmission immediately and all copies thereof.
[sqlite] Making data unique
Thanks Keith. This works. Andrew -Original Message- From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Keith Medcalf Sent: Monday, December 07, 2015 6:53 PM To: SQLite mailing list Subject: Re: [sqlite] Making data unique create table dataset ( id integer not null, timestamp integer not null, data integer not null, unique (id, timestamp) ); > -Original Message- > From: sqlite-users-bounces at mailinglists.sqlite.org > [mailto:sqlite-users- bounces at mailinglists.sqlite.org] On Behalf Of > Andrew Stewart > Sent: Monday, 7 December, 2015 08:01 > To: 'SQLite mailing list' > Subject: [sqlite] Making data unique > > Hi, > I have a table that consists of 3 elements: > ID - integer > Date/time - integer > Data - integer > A single ID can exist multiple times. > A single Date/time can exist multiple times. > An ID & Date/time combination is unique. > > What is the best way to ensure uniqueness in this table. > > Thanks, > Andrew Stewart > Software Designer > > Argus Controls > #101 - 18445 53 AVE > Surrey, BC V3S 7A4 > > t: 1-888-667-2091 ext : 108 > t: 1-604-536-9100 ext : 108 > f: 604-538-4728 > w: www.arguscontrols.com<http://www.arguscontrols.com/> > e: astewart at arguscontrols.com<mailto:astewart at arguscontrols.com> > > Notice: This electronic transmission contains confidential > information, intended only for the person(s) named above. If you are > not the intended recipient, you are hereby notified that any > disclosure, copying, distribution, or any other use of this email is > strictly prohibited. If you have received this transmission by error, > please notify us immediately by return email and destroy the original > transmission immediately and all copies thereof. > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Making data unique
I don't want to overwrite. An error or rejecting the Insert will work. Thanks, Andrew -Original Message- From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Adam Devita Sent: Monday, December 07, 2015 7:14 AM To: SQLite mailing list Subject: Re: [sqlite] Making data unique When you are about to insert into the table and find that ID & Date/time are identical to another record, but the data is different, do you want to overwrite, or not? Do you want an error? Adam On Mon, Dec 7, 2015 at 10:01 AM, Andrew Stewart wrote: > Hi, > I have a table that consists of 3 elements: > ID - integer > Date/time - integer > Data - integer > A single ID can exist multiple times. > A single Date/time can exist multiple times. > An ID & Date/time combination is unique. > > What is the best way to ensure uniqueness in this table. > > Thanks, > Andrew Stewart > Software Designer > > Argus Controls > #101 - 18445 53 AVE > Surrey, BC V3S 7A4 > > t: 1-888-667-2091 ext : 108 > t: 1-604-536-9100 ext : 108 > f: 604-538-4728 > w: www.arguscontrols.com<http://www.arguscontrols.com/> > e: astewart at arguscontrols.com<mailto:astewart at arguscontrols.com> > > Notice: This electronic transmission contains confidential information, > intended only for the person(s) named above. If you are not the intended > recipient, you are hereby notified that any disclosure, copying, > distribution, or any other use of this email is strictly prohibited. If you > have received this transmission by error, please notify us immediately by > return email and destroy the original transmission immediately and all copies > thereof. > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users -- -- VerifEye Technologies Inc. 151 Whitehall Dr. Unit 2 Markham, ON L3R 9T1 ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Making data unique
Hi, I have a table that consists of 3 elements: ID - integer Date/time - integer Data - integer A single ID can exist multiple times. A single Date/time can exist multiple times. An ID & Date/time combination is unique. What is the best way to ensure uniqueness in this table. Thanks, Andrew Stewart Software Designer Argus Controls #101 - 18445 53 AVE Surrey, BC V3S 7A4 t: 1-888-667-2091 ext : 108 t: 1-604-536-9100 ext : 108 f: 604-538-4728 w: www.arguscontrols.com<http://www.arguscontrols.com/> e: astewart at arguscontrols.com<mailto:astewart at arguscontrols.com> Notice: This electronic transmission contains confidential information, intended only for the person(s) named above. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, or any other use of this email is strictly prohibited. If you have received this transmission by error, please notify us immediately by return email and destroy the original transmission immediately and all copies thereof.
[sqlite] 10 minute Avg
Ryan, I tried just having an index on the fwParameterID - still taking 160 seconds to get 2 parameters for a day. I tried adding a second index on dateTime - did not appear to change the speed. Removed the second index and modified the first index to having both fwParameterID and dateTime - speed now fast (.008 seconds). Size of the database has doubled. Going to have to watch this, but need the speed. Would storing the dateTime as Integer make the database and indexes smaller? Thanks, Andrew S. -Original Message- From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of R Smith Sent: Thursday, November 19, 2015 7:40 AM To: sqlite-users at mailinglists.sqlite.org Subject: Re: [sqlite] 10 minute Avg On 2015/11/19 4:49 PM, Andrew Stewart wrote: > Hi, > Had a question regarding what I am trying to do. One thing that I have > noticed is that it is slow to do this. I do not have any indexes created and > there is no primary index on this table. > I am using a 'DateTime' variable for the date/time. I understand this > translates to a Numeric. It appears to be getting handled as a string, but > not sure. Would it be any better if I stored the date/time as a Integer > (64bit value). This would be using the C routine for generating a date/time > based upon the __time64 type (number of seconds since jan 1 1970 0:0:0). While it will technically be faster, there is not much gains by opting for an INT date, and you will be making it harder to specify intervals and the like. SQLite's handling of dates are rather fast, so I wouldn't suggest that as a first optimization. (Perhaps later - if you absolutely need to kill another few milliseconds). The first improvements would be proper indices on that table. Not too many, because that will slow down the Inserts (which I assume you do a lot of), but enough to allow the Query planner to get the data very fast. It's hard to guess at the best indices - we usually make all the ones we think would be needed, then do the queries that we will need, see which of them the query planner output says it is using, and then drop the other unused indices. On your table, and to make those queries very fast, my suggestion would be adding a Primary Key on: dataStreamRecord(fwParameterID, dateTime) This assumes A - you are able to recreate the schema and copy the values over, and B - you will never have more than one entry for the same fwParameter in the same millisecond. If this is a possibility, then rather simply create a normal Index and not a Unique/Primary one. An alternate, if you have some other queries too, is to create separate indices on fwParameterID and dateTime which can simply be added using this SQL: CREATE INDEX dStream_fwParam ON dataStreamRecord(fwParameterID); CREATE INDEX dStream_DTStamp ON dataStreamRecord(dateTime); That /should/ sort out the speed completely. Let us know if it is still unacceptably slow. As an aside, are you using some DB manager to do all this? It might be easier with some visual designers and one-click schema changes (such as adding a primary key) using one of those. May I suggest some good free ones such as SQLitespeed from: http://www.sqlc.rifin.co.za/ which allows backups to be made instantly (a must before trying any changes) and visual schema designers if you use windows, or perhaps DB Browser for SQLite: https://github.com/sqlitebrowser/sqlitebrowser/ with similar functionality if you are using Linux / Mac. Good luck, Ryan ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users Notice: This electronic transmission contains confidential information, intended only for the person(s) named above. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, or any other use of this email is strictly prohibited. If you have received this transmission by error, please notify us immediately by return email and destroy the original transmission immediately and all copies thereof.
[sqlite] 10 minute Avg
Hi, Had a question regarding what I am trying to do. One thing that I have noticed is that it is slow to do this. I do not have any indexes created and there is no primary index on this table. I am using a 'DateTime' variable for the date/time. I understand this translates to a Numeric. It appears to be getting handled as a string, but not sure. Would it be any better if I stored the date/time as a Integer (64bit value). This would be using the C routine for generating a date/time based upon the __time64 type (number of seconds since jan 1 1970 0:0:0). Thanks, Andrew S. -Original Message- From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of R Smith Sent: Wednesday, November 18, 2015 2:06 PM To: sqlite-users at mailinglists.sqlite.org Subject: Re: [sqlite] 10 minute Avg On 2015/11/18 9:23 PM, Andrew Stewart wrote: > Ryan, > Like the code. Was wondering what adjustment I would make to get if I > had 2 (or more) firmwareIDs that I wanted to get the average for. Say get 10 > minute average for fwParameterID 1074 & fwParameterID 1073. I am having > issues adjusting your code to achieve this. I need to see the average for > each fwParameterID separately. > > Thanks, > Andrew S. Certainly, one way to do this is to simply list the entries consecutively for the different fwParams in a makeshift CTE table, like this: WITH BDT(startDateTime,endDateTime,IntervalSeconds) AS ( SELECT '2015-11-17 00:00:00', '2015-11-18 00:00:00', '+600 seconds' ), PAR(fwParam) AS ( SELECT 1074 UNION ALL SELECT 1075 UNION ALL -- This list includes all the fwParameterIDs you would like to add... SELECT 1076 UNION ALL SELECT 1079 UNION ALL SELECT 1080 ), TIV(startTime,endTime) AS ( SELECT startDateTime, datetime(startDateTime,IntervalSeconds) FROM BDT UNION ALL SELECT endTime, datetime(endTime,IntervalSeconds) FROM TIV,BDT WHERE endTime < endDateTime ) SELECT PAR.fwParam, TIV.startTime, avg(DSR.data) AS dataAvg FROM PAR,TIV LEFT JOIN dataStreamRecord AS DSR ON DSR.datetime >= TIV.startTime AND DSR.datetime < TIV.endTime AND fwParameterID = PAR.fwParam GROUP BY PAR.fwParam, TIV.startTime ORDER BY PAR.fwParam, TIV.startTime ASC ; See if that does what is needed, Cheers! Ryan > > -Original Message- > From: sqlite-users-bounces at mailinglists.sqlite.org > [mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of R Smith > Sent: Wednesday, November 18, 2015 10:06 AM > To: sqlite-users at mailinglists.sqlite.org > Subject: Re: [sqlite] 10 minute Avg > > Slight alteration, the Left Join should contain the full filter to show empty > time-slots, if that is what you want rather, like this: > > WITH BDT(startDateTime,endDateTime,IntervalSeconds) AS ( > SELECT '2015-11-17 00:00:00', '2015-11-18 00:00:00', '+600 seconds' > ), TIV(startTime,endTime) AS ( > SELECT startDateTime, datetime(startDateTime,IntervalSeconds) FROM BDT > UNION ALL > SELECT endTime, datetime(endTime,IntervalSeconds) FROM TIV,BDT WHERE > endTime < endDateTime > ) > SELECT TIV.startTime, max(DSR.data) AS dataAvg > FROM TIV > LEFT JOIN dataStreamRecord AS DSR ON DSR.datetime >= TIV.startTime AND > DSR.datetime < TIV.endTime AND fwParameterID = 1074 >GROUP BY TIV.startTime >ORDER BY TIV.startTime ASC > ; > > > On 2015/11/18 7:43 PM, R Smith wrote: >> Quite easy to do with a CTE, like this: >> >> WITH BDT(startDateTime,endDateTime,IntervalSeconds) AS ( >>SELECT '2015-11-17 00:00:00', '2015-11-18 00:00:00', '+600 seconds' >> ), TIV(startTime,endTime) AS ( >> SELECT startDateTime, datetime(startDateTime,IntervalSeconds) FROM >> BDT >>UNION ALL >> SELECT endTime, datetime(endTime,IntervalSeconds) FROM TIV,BDT >> WHERE endTime < endDateTime >> ) >> SELECT TIV.startTime, avg(DSR.data) AS dataAvg >>FROM TIV >>LEFT JOIN dataStreamRecord AS DSR ON DSR.datetime >= TIV.startTime >> AND DSR.datetime < TIV.endTime WHERE fwParameterID = 1074 GROUP BY >> TIV.startTime ORDER BY TIV.startTime ASC ; >> >> Of course, if you do not wish to list 10-minute intervals where there >> wasn't any activity, then add " AND DSR.dateTime IS NOT NULL" to the >> WHERE clause. >> >> You can change the start and end dates and the interval by changing >> just the first line inside the CTE. Just inject those values via >> your code. >> >> Let us know if there's anything unclear or not working as expected, >> Cheers! >> Ryan >> >> >> >> On 2015/11/18 6:15 PM, Andrew Stewart wrote: >>&
[sqlite] 10 minute Avg
Ryan, Like the code. Was wondering what adjustment I would make to get if I had 2 (or more) firmwareIDs that I wanted to get the average for. Say get 10 minute average for fwParameterID 1074 & fwParameterID 1073. I am having issues adjusting your code to achieve this. I need to see the average for each fwParameterID separately. Thanks, Andrew S. -Original Message- From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of R Smith Sent: Wednesday, November 18, 2015 10:06 AM To: sqlite-users at mailinglists.sqlite.org Subject: Re: [sqlite] 10 minute Avg Slight alteration, the Left Join should contain the full filter to show empty time-slots, if that is what you want rather, like this: WITH BDT(startDateTime,endDateTime,IntervalSeconds) AS ( SELECT '2015-11-17 00:00:00', '2015-11-18 00:00:00', '+600 seconds' ), TIV(startTime,endTime) AS ( SELECT startDateTime, datetime(startDateTime,IntervalSeconds) FROM BDT UNION ALL SELECT endTime, datetime(endTime,IntervalSeconds) FROM TIV,BDT WHERE endTime < endDateTime ) SELECT TIV.startTime, max(DSR.data) AS dataAvg FROM TIV LEFT JOIN dataStreamRecord AS DSR ON DSR.datetime >= TIV.startTime AND DSR.datetime < TIV.endTime AND fwParameterID = 1074 GROUP BY TIV.startTime ORDER BY TIV.startTime ASC ; On 2015/11/18 7:43 PM, R Smith wrote: > Quite easy to do with a CTE, like this: > > WITH BDT(startDateTime,endDateTime,IntervalSeconds) AS ( > SELECT '2015-11-17 00:00:00', '2015-11-18 00:00:00', '+600 seconds' > ), TIV(startTime,endTime) AS ( > SELECT startDateTime, datetime(startDateTime,IntervalSeconds) FROM > BDT > UNION ALL > SELECT endTime, datetime(endTime,IntervalSeconds) FROM TIV,BDT > WHERE endTime < endDateTime > ) > SELECT TIV.startTime, avg(DSR.data) AS dataAvg > FROM TIV > LEFT JOIN dataStreamRecord AS DSR ON DSR.datetime >= TIV.startTime > AND DSR.datetime < TIV.endTime WHERE fwParameterID = 1074 GROUP BY > TIV.startTime ORDER BY TIV.startTime ASC ; > > Of course, if you do not wish to list 10-minute intervals where there > wasn't any activity, then add " AND DSR.dateTime IS NOT NULL" to the > WHERE clause. > > You can change the start and end dates and the interval by changing > just the first line inside the CTE. Just inject those values via > your code. > > Let us know if there's anything unclear or not working as expected, > Cheers! > Ryan > > > > On 2015/11/18 6:15 PM, Andrew Stewart wrote: >> Hi, >> I am trying to generate 10 minute average data for a >> day from a data set. Table is created by the following: >> >> CREATE TABLE dataStreamRecord (fwParameterID INTEGER NOT NULL, >> dateTime DATETIME NOT NULL, data INTEGER NOT NULL); >> >> Sample Data >> fwParameterID,dateTime,data >> 1074,2015-11-17 00:00:01,8192 >> 1074,2015-11-17 00:33:18,0 >> 1074,2015-11-17 00:33:19,8192 >> 1074,2015-11-17 00:41:00,0 >> 1074,2015-11-17 00:41:01,8192 >> 1074,2015-11-17 01:11:34,0 >> 1074,2015-11-17 01:11:35,8192 >> 1074,2015-11-17 01:19:10,0 >> 1074,2015-11-17 01:19:11,8192 >> 1074,2015-11-17 01:26:44,0 >> 1074,2015-11-17 01:26:45,8192 >> 1074,2015-11-17 01:34:24,0 >> 1074,2015-11-17 01:34:25,8192 >> 1074,2015-11-17 02:12:44,0 >> 1074,2015-11-17 02:12:45,8192 >> 1074,2015-11-17 02:43:21,0 >> 1074,2015-11-17 02:43:22,8192 >> 1074,2015-11-17 03:06:19,0 >> 1074,2015-11-17 03:06:20,8192 >> 1074,2015-11-17 03:37:02,0 >> 1074,2015-11-17 03:37:03,8192 >> 1074,2015-11-17 05:08:58,0 >> 1074,2015-11-17 05:08:59,8192 >> 1074,2015-11-17 05:16:35,0 >> 1074,2015-11-17 05:16:36,8192 >> 1074,2015-11-17 05:16:37,0 >> 1074,2015-11-17 05:16:38,8192 >> 1074,2015-11-17 06:25:29,0 >> 1074,2015-11-17 06:25:30,8192 >> 1074,2015-11-17 07:41:58,0 >> 1074,2015-11-17 07:41:59,8192 >> 1074,2015-11-17 07:43:02,0 >> 1074,2015-11-17 07:43:03,8192 >> 1074,2015-11-17 07:43:19,0 >> 1074,2015-11-17 07:43:20,8192 >> 1074,2015-11-17 07:43:55,0 >> 1074,2015-11-17 07:43:56,8192 >> 1074,2015-11-17 07:44:31,0 >> 1074,2015-11-17 07:44:33,8192 >> 1074,2015-11-17 08:20:43,0 >> 1074,2015-11-17 08:20:44,8192 >> 1074,2015-11-17 08:27:49,0 >> 1074,2015-11-17 08:27:50,8192 >> 1074,2015-11-17 08:35:23,0 >> 1074,2015-11-17 08:35:24,8192 >> 1074,2015-11-17 09:04:47,0 >> 1074,2015-11-17 09:04:48,8192 >> 1074,2015-11-17 09:13:35,0 >> 1074,2015-11-17 09:13:36,8192 >> 1074,2015-11-17 09:17:41,0 >> 1074,2015-11-17 09:17:42,8192 >> >> Query for generating the above da
[sqlite] 10 minute Avg
Hi Richard, I figured out what you did. Not exactly what I am after. College had a suggestion. I am going to work on adjusting the structure to storing time/date as BIGINT and do the math on that. I need to be able to adjust the interval as needed (depending on user request) and not have it fixed to position in a string. Thanks, Andrew S -Original Message- From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Andrew Stewart Sent: Wednesday, November 18, 2015 8:45 AM To: 'SQLite mailing list' Subject: Re: [sqlite] 10 minute Avg Hi Richard, That gave me 10 hour intervals not 10 minute. Which is the part that controls the frequency? Thanks, Andrew S. -Original Message- From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Richard Hipp Sent: Wednesday, November 18, 2015 8:41 AM To: SQLite mailing list Subject: Re: [sqlite] 10 minute Avg On 11/18/15, Andrew Stewart wrote: > Hi, > I am trying to generate 10 minute average data for a > day from a data set. Table is created by the following: > > CREATE TABLE dataStreamRecord (fwParameterID INTEGER NOT NULL, > dateTime DATETIME NOT NULL, data INTEGER NOT NULL); > > Sample Data > fwParameterID,dateTime,data > 1074,2015-11-17 00:00:01,8192 > 1074,2015-11-17 00:33:18,0 > > Any assistance in generating a query to do 10 minute averages for > 'data' on November 17 for fwParameterID 1074 would be appreciated. > Maybe this: SELECT substr(dateTime,1,12)||'0:00:00', avg(date) FROM dataStreamRecord WHERE fwParameterID=1074 AND dateTime BETWEEN '2015-11-17' AND '2015-11-18' GROUP BY substr(dateTime,1,12) ORDER BY 1; -- D. Richard Hipp drh at sqlite.org ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users Notice: This electronic transmission contains confidential information, intended only for the person(s) named above. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, or any other use of this email is strictly prohibited. If you have received this transmission by error, please notify us immediately by return email and destroy the original transmission immediately and all copies thereof. ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] 10 minute Avg
Hi Richard, That gave me 10 hour intervals not 10 minute. Which is the part that controls the frequency? Thanks, Andrew S. -Original Message- From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Richard Hipp Sent: Wednesday, November 18, 2015 8:41 AM To: SQLite mailing list Subject: Re: [sqlite] 10 minute Avg On 11/18/15, Andrew Stewart wrote: > Hi, > I am trying to generate 10 minute average data for a > day from a data set. Table is created by the following: > > CREATE TABLE dataStreamRecord (fwParameterID INTEGER NOT NULL, > dateTime DATETIME NOT NULL, data INTEGER NOT NULL); > > Sample Data > fwParameterID,dateTime,data > 1074,2015-11-17 00:00:01,8192 > 1074,2015-11-17 00:33:18,0 > > Any assistance in generating a query to do 10 minute averages for > 'data' on November 17 for fwParameterID 1074 would be appreciated. > Maybe this: SELECT substr(dateTime,1,12)||'0:00:00', avg(date) FROM dataStreamRecord WHERE fwParameterID=1074 AND dateTime BETWEEN '2015-11-17' AND '2015-11-18' GROUP BY substr(dateTime,1,12) ORDER BY 1; -- D. Richard Hipp drh at sqlite.org ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users Notice: This electronic transmission contains confidential information, intended only for the person(s) named above. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, or any other use of this email is strictly prohibited. If you have received this transmission by error, please notify us immediately by return email and destroy the original transmission immediately and all copies thereof.
[sqlite] 10 minute Avg
Hi, I am trying to generate 10 minute average data for a day from a data set. Table is created by the following: CREATE TABLE dataStreamRecord (fwParameterID INTEGER NOT NULL, dateTime DATETIME NOT NULL, data INTEGER NOT NULL); Sample Data fwParameterID,dateTime,data 1074,2015-11-17 00:00:01,8192 1074,2015-11-17 00:33:18,0 1074,2015-11-17 00:33:19,8192 1074,2015-11-17 00:41:00,0 1074,2015-11-17 00:41:01,8192 1074,2015-11-17 01:11:34,0 1074,2015-11-17 01:11:35,8192 1074,2015-11-17 01:19:10,0 1074,2015-11-17 01:19:11,8192 1074,2015-11-17 01:26:44,0 1074,2015-11-17 01:26:45,8192 1074,2015-11-17 01:34:24,0 1074,2015-11-17 01:34:25,8192 1074,2015-11-17 02:12:44,0 1074,2015-11-17 02:12:45,8192 1074,2015-11-17 02:43:21,0 1074,2015-11-17 02:43:22,8192 1074,2015-11-17 03:06:19,0 1074,2015-11-17 03:06:20,8192 1074,2015-11-17 03:37:02,0 1074,2015-11-17 03:37:03,8192 1074,2015-11-17 05:08:58,0 1074,2015-11-17 05:08:59,8192 1074,2015-11-17 05:16:35,0 1074,2015-11-17 05:16:36,8192 1074,2015-11-17 05:16:37,0 1074,2015-11-17 05:16:38,8192 1074,2015-11-17 06:25:29,0 1074,2015-11-17 06:25:30,8192 1074,2015-11-17 07:41:58,0 1074,2015-11-17 07:41:59,8192 1074,2015-11-17 07:43:02,0 1074,2015-11-17 07:43:03,8192 1074,2015-11-17 07:43:19,0 1074,2015-11-17 07:43:20,8192 1074,2015-11-17 07:43:55,0 1074,2015-11-17 07:43:56,8192 1074,2015-11-17 07:44:31,0 1074,2015-11-17 07:44:33,8192 1074,2015-11-17 08:20:43,0 1074,2015-11-17 08:20:44,8192 1074,2015-11-17 08:27:49,0 1074,2015-11-17 08:27:50,8192 1074,2015-11-17 08:35:23,0 1074,2015-11-17 08:35:24,8192 1074,2015-11-17 09:04:47,0 1074,2015-11-17 09:04:48,8192 1074,2015-11-17 09:13:35,0 1074,2015-11-17 09:13:36,8192 1074,2015-11-17 09:17:41,0 1074,2015-11-17 09:17:42,8192 Query for generating the above data SELECT [fwParameterID], [dateTime], [data] FROM dataStreamRecord WHERE fwParameterID = 1074 AND dateTime >= '2015-11-17 00:00:00' AND dateTime < '2015-11-18 00:00:00' ORDER BY dateTime ASC Any assistance in generating a query to do 10 minute averages for 'data' on November 17 for fwParameterID 1074 would be appreciated. Thanks, Andrew S Notice: This electronic transmission contains confidential information, intended only for the person(s) named above. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, or any other use of this email is strictly prohibited. If you have received this transmission by error, please notify us immediately by return email and destroy the original transmission immediately and all copies thereof.
