Re: [sqlite] Free Page Data usage
Hi, Sir, the 32 TB size is not always going to be reached. The Database is going to be used to store blocks of a Block Device like /dev/sda1 The size can reach 3-4 TB easily and would start from atleast 20-100 GB. 32 TB of data though impractical as of today will be possible in 2-3 years. The issue happens when I delete the rows and new rows are inserted at the end of the database the size of the database exceeds that of the actual block device size even though many pages are having free space. Hence I am simply trying to optimize the utilization of the free space available. I would have loved to use the page size of 2KB which would give me a practical size of 4TB. But that would have this hard limit of 4TB. So I have two possible options which I am trying to help me solve this issue : 1) Either make the page size to 2KB and increase the maximum page count to 2^64 which will be more than sufficient. 2) Improve the free space utilization of each page when the page size is 64KB. I hope this makes sense. On Sat, Feb 8, 2014 at 12:54 AM, RSmith wrote: > A database that is geared for 32TB size and you are concerned about rather > insignificant space wasted by the page size that is needed to reach the > 32TB max size... does not make any sense unless you are simply paranoid > about space. Removing the gaps in the table space when deleting a row (or > rows) will render a delete query several magnitudes slower. > > If it IS that big of a concern, then maybe use standard files rather than > SQLite to save data in? If the SQL functionality is a must, you can use > vacuum as often as is needed to clear unused space - but beware, 1 - Vacuum > takes some processing to re-pack a DB, especially a near 32TB one... in the > order of minutes on a computer I would guess, and much much more on > anything else. 2 - a 32TB DB will need up to 64TB total free disk space to > be sure to vacuum correctly - so having issues with it taking up maybe 40TB > for 32TB of data is in itself an irrelevant concern. Even large queries, > temporary tables etc will all need additional interim space for the sorts > of queries that might be requested of a 32TB data-set. > > The real point being: if you do not have at least 64TB free on whatever > that 32TB DB will sit, you are doing it wrong, and if you do have that much > free, you can ignore the 25% wasted deletion space problem. > > If the problem is simply your own pedanticism (at least I can sympathise > with that!) then it's simply a case of "Welcome to efficient databasing", > but if it is a real space deficit, then I'm afraid you will have to re-plan > or reconsider either the max allowable DB, or the physical layer's space > availability - sorry. > > > > On 2014/02/07 20:35, Raheel Gupta wrote: > >> Hi, >> >> I use a page size of 64 KB. But my row consists of 2 columns that is : >> i - Auto Increment Integer, >> b - 4096 Bytes of BLOB data >> >> Now for the sake of calculation, lets say 16 rows fit in a page and my >> table has 1 rows when I start. >> >> Now, lets say I delete some data which is not in sequence i.e. it can be >> deleted as per data which is not in use. To create such a hypothetical >> situation for explaining this to you, here is a simple query : >> DELETE from TABLE where i%4 = 0; >> >> As you may see that there is now 25% data deleted in each page. >> >> Now even if I do insert another 2500 rows (25% of original size) my >> database size reaches 125% of the original size when I inserted the 1 >> rows initially. >> >> Hence there is significant space wastage. Anyway i can improve that ? >> It would be nice if the database size would be close to the original size >> after deleting 25% and adding some new 25% data. >> >> I know you would recommend to use smaller page sizes. Ideally 2KP page >> size >> is good but then, the number of pages is restricted to a max of 2^32 which >> will restrict the total database size to 4TB only. I need the max size to >> be capable of atleast 32TB. >> >> >> >> On Fri, Feb 7, 2014 at 11:14 PM, Donald Griggs >> wrote: >> >> Can you write more about how this is causing you a problem? Most users >>> don't experience this as a problem >>> On Feb 7, 2014 10:30 AM, "Raheel Gupta" wrote: >>> >>> SQLite's tables are B-trees, sorted by the rowid. Your new data will > probably get an autoincremented rowid, which will be appended at the > end >>> of the table. > > A page gets reorganized only when about 2/3 is free space. > > Anyway to make this ratio to lets say 1/3 ? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ >>> sqlite-users mailing list >>> sqlite-users@sqlite.org >>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >>> >>> ___ >> sqlite-user
Re: [sqlite] help needed for major SQLite problem
On 02/08/2014 03:00 AM, C M wrote: This is a follow-up to a question I asked on this list on Sep 1st, 2013, about an error that I was randomly getting with disk-based SQLite database in a Python desktop application. I now have more info to provide about the error...such as what was asked for at that time: On Sun, Sep 1, 2013 at 6:12 PM, Richard Hipp wrote: Does Python have an interface to the error and warning log mechanism of SQLite? (http://www.sqlite.org/errlog.html) Can you turn that on? It will probably give more details about what it happening. I wasn't able to do this at first, but thanks to switching from the standard sqlite3 module in Python (otherwise known as pysqslite) to Roger Binns's APSW module, and then also using an experimental module, apswdbapi2, from Edzard Pasma (thank you both), I was able to set things up to return the warning log...I think. Today, after not seeing the error in a very long time, I hit the error, and this was printed to sys.stdout: SQLITE_LOG: delayed 1375ms for lock/sharing conflict (10) SQLITE_IOERR SQLITE_LOG: os_win.c:35129: (5) winAccess(C:\Documents and Settings\user\My Documents\My Dropbox\myapp\gorp.db-journal) - Access is denied. (3338) SQLITE_IOERR SQLITE_LOG: statement aborts at 16: [SELECT resumes, start FROM Durations WHERE start='2014-02-07 14:24:14.064000' AND value='activity'] disk I/O error (3338) SQLITE_IOERR Does that give anyone a better idea of what could be happening and how I can fix this problem? Looks like GetFileAttributesEx() might be throwing an ERROR_ACCESS_DENIED exception. Maybe a virus scanner or some other background process had temporarily locked the database file. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Avoiding holding a lock for too long
On 7 Feb 2014, at 10:44pm, Tim Streater wrote: > Now I'm finding that sometimes the "processing results here" can take a > minute or so, as it involves reading a file from disk and sending it to a > remote host. Instead of twiddling my thumbs waiting for this, I'm initiating > another action which inter alia involves doing an insert into mytable in > mydb, and yes, it gets "database is locked". > > A simple way to fix this is to read the results into arrays in my loop above > (should be quick, rarely more than 10 rows), drop the database, and then have > a second loop to use the results, i.e. move "process results here" into the > second loop. You don't seem to be calling ->finalize() on your result. > But can I instead move setting the handle to null right after the query? Or > does that lose me the result set? You should not be manually setting any handle to null. Try calling ->finalize() on the statement and eventually ->close() on the database, which do more than just setting their value. If you are setting anything to null manually, this may be causing the problem you report. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Avoiding holding a lock for too long
I have some PHP code, roughly as follows (ignoring error handling):
$dbh = new SQLite3 ('/path/to/mydb');
$res = $dbh->query ('select a,b,c from mytable where d=23'); // for example
while (true)
{
$reg = $res->fetchArray (SQLITE3_ASSOC);
if ($reg===false) break;
// process results here (for each row)
}
$dbh = null;
Now I'm finding that sometimes the "processing results here" can take a minute
or so, as it involves reading a file from disk and sending it to a remote host.
Instead of twiddling my thumbs waiting for this, I'm initiating another action
which inter alia involves doing an insert into mytable in mydb, and yes, it
gets "database is locked".
A simple way to fix this is to read the results into arrays in my loop above
(should be quick, rarely more than 10 rows), drop the database, and then have a
second loop to use the results, i.e. move "process results here" into the
second loop.
But can I instead move setting the handle to null right after the query? Or
does that lose me the result set?
--
Cheers -- Tim
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] help needed for major SQLite problem
This is a follow-up to a question I asked on this list on Sep 1st, 2013, about an error that I was randomly getting with disk-based SQLite database in a Python desktop application. I now have more info to provide about the error...such as what was asked for at that time: On Sun, Sep 1, 2013 at 6:12 PM, Richard Hipp wrote: > Does Python have an interface to the error and warning log mechanism of > SQLite? (http://www.sqlite.org/errlog.html) Can you turn that on? It > will probably give more details about what it happening. > I wasn't able to do this at first, but thanks to switching from the standard sqlite3 module in Python (otherwise known as pysqslite) to Roger Binns's APSW module, and then also using an experimental module, apswdbapi2, from Edzard Pasma (thank you both), I was able to set things up to return the warning log...I think. Today, after not seeing the error in a very long time, I hit the error, and this was printed to sys.stdout: SQLITE_LOG: delayed 1375ms for lock/sharing conflict (10) SQLITE_IOERR SQLITE_LOG: os_win.c:35129: (5) winAccess(C:\Documents and Settings\user\My Documents\My Dropbox\myapp\gorp.db-journal) - Access is denied. (3338) SQLITE_IOERR SQLITE_LOG: statement aborts at 16: [SELECT resumes, start FROM Durations WHERE start='2014-02-07 14:24:14.064000' AND value='activity'] disk I/O error (3338) SQLITE_IOERR Does that give anyone a better idea of what could be happening and how I can fix this problem? Thanks to all again, Che M ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Free Page Data usage
A database that is geared for 32TB size and you are concerned about rather insignificant space wasted by the page size that is needed to reach the 32TB max size... does not make any sense unless you are simply paranoid about space. Removing the gaps in the table space when deleting a row (or rows) will render a delete query several magnitudes slower. If it IS that big of a concern, then maybe use standard files rather than SQLite to save data in? If the SQL functionality is a must, you can use vacuum as often as is needed to clear unused space - but beware, 1 - Vacuum takes some processing to re-pack a DB, especially a near 32TB one... in the order of minutes on a computer I would guess, and much much more on anything else. 2 - a 32TB DB will need up to 64TB total free disk space to be sure to vacuum correctly - so having issues with it taking up maybe 40TB for 32TB of data is in itself an irrelevant concern. Even large queries, temporary tables etc will all need additional interim space for the sorts of queries that might be requested of a 32TB data-set. The real point being: if you do not have at least 64TB free on whatever that 32TB DB will sit, you are doing it wrong, and if you do have that much free, you can ignore the 25% wasted deletion space problem. If the problem is simply your own pedanticism (at least I can sympathise with that!) then it's simply a case of "Welcome to efficient databasing", but if it is a real space deficit, then I'm afraid you will have to re-plan or reconsider either the max allowable DB, or the physical layer's space availability - sorry. On 2014/02/07 20:35, Raheel Gupta wrote: Hi, I use a page size of 64 KB. But my row consists of 2 columns that is : i - Auto Increment Integer, b - 4096 Bytes of BLOB data Now for the sake of calculation, lets say 16 rows fit in a page and my table has 1 rows when I start. Now, lets say I delete some data which is not in sequence i.e. it can be deleted as per data which is not in use. To create such a hypothetical situation for explaining this to you, here is a simple query : DELETE from TABLE where i%4 = 0; As you may see that there is now 25% data deleted in each page. Now even if I do insert another 2500 rows (25% of original size) my database size reaches 125% of the original size when I inserted the 1 rows initially. Hence there is significant space wastage. Anyway i can improve that ? It would be nice if the database size would be close to the original size after deleting 25% and adding some new 25% data. I know you would recommend to use smaller page sizes. Ideally 2KP page size is good but then, the number of pages is restricted to a max of 2^32 which will restrict the total database size to 4TB only. I need the max size to be capable of atleast 32TB. On Fri, Feb 7, 2014 at 11:14 PM, Donald Griggs wrote: Can you write more about how this is causing you a problem? Most users don't experience this as a problem On Feb 7, 2014 10:30 AM, "Raheel Gupta" wrote: SQLite's tables are B-trees, sorted by the rowid. Your new data will probably get an autoincremented rowid, which will be appended at the end of the table. A page gets reorganized only when about 2/3 is free space. Anyway to make this ratio to lets say 1/3 ? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Errors Compiling Extension percentile.c
Denis Burke wrote:
>
> How do I access such functions? If I am missing some existing docs,
please
> just point me to them.
>
Something like this:
SQLiteConnection connection = new SQLiteConnection("Data Source=:memory:;");
connection.Open();
connection.EnableExtensions(true);
connection.LoadExtension("SQLite.Interop.dll", "sqlite3_percentile_init");
--
Joe Mistachkin
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Free Page Data usage
On 7 Feb 2014, at 6:35pm, Raheel Gupta wrote: > As you may see that there is now 25% data deleted in each page. > > Now even if I do insert another 2500 rows (25% of original size) my > database size reaches 125% of the original size when I inserted the 1 > rows initially. > > Hence there is significant space wastage. Anyway i can improve that ? Whenever filespace matters to you, do a VACUUM. Some apps do it on startup. Some do it on shutdown. Some do it as part of a monthly maintenance period. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Free Page Data usage
Hi, I use a page size of 64 KB. But my row consists of 2 columns that is : i - Auto Increment Integer, b - 4096 Bytes of BLOB data Now for the sake of calculation, lets say 16 rows fit in a page and my table has 1 rows when I start. Now, lets say I delete some data which is not in sequence i.e. it can be deleted as per data which is not in use. To create such a hypothetical situation for explaining this to you, here is a simple query : DELETE from TABLE where i%4 = 0; As you may see that there is now 25% data deleted in each page. Now even if I do insert another 2500 rows (25% of original size) my database size reaches 125% of the original size when I inserted the 1 rows initially. Hence there is significant space wastage. Anyway i can improve that ? It would be nice if the database size would be close to the original size after deleting 25% and adding some new 25% data. I know you would recommend to use smaller page sizes. Ideally 2KP page size is good but then, the number of pages is restricted to a max of 2^32 which will restrict the total database size to 4TB only. I need the max size to be capable of atleast 32TB. On Fri, Feb 7, 2014 at 11:14 PM, Donald Griggs wrote: > Can you write more about how this is causing you a problem? Most users > don't experience this as a problem > On Feb 7, 2014 10:30 AM, "Raheel Gupta" wrote: > > > > > > > SQLite's tables are B-trees, sorted by the rowid. Your new data will > > > probably get an autoincremented rowid, which will be appended at the > end > > > of the table. > > > > > > A page gets reorganized only when about 2/3 is free space. > > > > > > > Anyway to make this ratio to lets say 1/3 ? > > ___ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Free Page Data usage
Can you write more about how this is causing you a problem? Most users don't experience this as a problem On Feb 7, 2014 10:30 AM, "Raheel Gupta" wrote: > > > > SQLite's tables are B-trees, sorted by the rowid. Your new data will > > probably get an autoincremented rowid, which will be appended at the end > > of the table. > > > > A page gets reorganized only when about 2/3 is free space. > > > > Anyway to make this ratio to lets say 1/3 ? > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Send Mail from sqlite
Have your application that is performing the update send an email when it does an update/insert/delete. >I like to know if there is any possible to send a mail from sqlite. > >I wanted to know if there is option to configure smtp in sqlite, which >will help me to send a mail. > >My requirement > >I need to send a notification mail once the table is getting >updated/inserted/deleted. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Redundant #define's in os_unix.c
Hi all, In src/os_unix.c line 105, _LARGE_FILE is defined if SQLITE_DISABLE_LFS is not defined. But before that, is included, which contains exactly the same #defines already. This means that _LARGE_FILE will be defined twice. Most likely, one day in the past this code moved from os_unix.c to sqliteInt.h, but it was never removed from the original location. Therefore I suggest to remove this duplication in os_unix.c, leaving everything (including the later-added comment) in sqliteInt.h. Proposed patch below. Hopefully this resolves the confusion for people (like me) who notice this. And, when comments change, they don't need to be updated in two places any more. Regards, Jan Nijtmans == --- src/sqliteInt.h +++ src/sqliteInt.h @@ -32,10 +32,15 @@ ** without this option, LFS is enable. But LFS does not exist in the kernel ** in Red Hat 6.0, so the code won't work. Hence, for maximum binary ** portability you should omit LFS. ** ** Similar is true for Mac OS X. LFS is only supported on Mac OS X 9 and later . +** +** The previous paragraph was written in 2005. (This paragraph is written +** on 2008-11-28.) These days, all Linux kernels support large files, so +** you should probably leave LFS enabled. But some embedded platforms might +** lack LFS in which case the SQLITE_DISABLE_LFS macro might still be useful. */ #ifndef SQLITE_DISABLE_LFS # define _LARGE_FILE 1 # ifndef _FILE_OFFSET_BITS # define _FILE_OFFSET_BITS 64 --- src/os_unix.c +++ src/os_unix.c @@ -81,36 +81,10 @@ # else #define OS_VXWORKS 0 # endif #endif -/* -** These #defines should enable >2GB file support on Posix if the -** underlying operating system supports it. If the OS lacks -** large file support, these should be no-ops. -** -** Large file support can be disabled using the -DSQLITE_DISABLE_LFS switch -** on the compiler command line. This is necessary if you are compiling -** on a recent machine (ex: RedHat 7.2) but you want your code to work -** on an older machine (ex: RedHat 6.0). If you compile on RedHat 7.2 -** without this option, LFS is enable. But LFS does not exist in the kernel -** in RedHat 6.0, so the code won't work. Hence, for maximum binary -** portability you should omit LFS. -** -** The previous paragraph was written in 2005. (This paragraph is written -** on 2008-11-28.) These days, all Linux kernels support large files, so -** you should probably leave LFS enabled. But some embedded platforms might -** lack LFS in which case the SQLITE_DISABLE_LFS macro might still be useful. -*/ -#ifndef SQLITE_DISABLE_LFS -# define _LARGE_FILE 1 -# ifndef _FILE_OFFSET_BITS -# define _FILE_OFFSET_BITS 64 -# endif -# define _LARGEFILE_SOURCE 1 -#endif - /* ** standard include files. */ #include #include ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Error: "File is encrypted or is not a database"
On 7 Feb 2014, at 10:49am, Iksanov Husain wrote: > We have an encrypted SQLite database with a password which is set > programmatically in .NET interface. Can you tell us which encryption system you used to encrypt your database ? Did you change anything in the last day or two: updated operating system, or new DLL ? If you put a copy of the SQLite shell tool on that computer, will it open the file ? If you move the database to a different computer can you open it ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Free Page Data usage
> > SQLite's tables are B-trees, sorted by the rowid. Your new data will > probably get an autoincremented rowid, which will be appended at the end > of the table. > > A page gets reorganized only when about 2/3 is free space. > Anyway to make this ratio to lets say 1/3 ? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Send Mail from sqlite
Simon Slavin wrote on Friday, February 07, 2014 5:06 AM >> On 7 Feb 2014, at 9:59am, Vairamuthu wrote: >> Thanks for your response, it will be great help if you can get me >> some sample code or algorithms, on that. > > That would depend on what programming language you are using and what > access it has to any method of sending mail. However you do it, it > won't be done inside SQLite so it won't be anything to do with this > mailing list. If SQLite is embedded in the app, might it be fairly simple to create a user defined function and call that from a trigger? Erik -- This message, and any attachments, is for the intended recipient(s) only, may contain information that is privileged, confidential and/or proprietary and subject to important terms and conditions available at http://www.bankofamerica.com/emaildisclaimer. If you are not the intended recipient, please delete this message. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Send Mail from sqlite
Thanks to all for the suggestion, will try my best to implement this solution. On Fri, Feb 7, 2014 at 6:28 PM, Kevin Martin wrote: > On 7 Feb 2014, at 09:59, Vairamuthu wrote: > > > Thanks for your response, it will be great help if you can get me some > > sample code or algorithms, on that. > > > I'm assuming you're using SQLite embedded within another application on > something unix like. > > Off the top of my head, here's how I would do it - there may be an > extension like this lying around somewhere already? > > Create an SQLite3 extension that implements the following functions > > sqlnotify_open(, , ) > sqlnotify_close() > sqlnotify_send(, , , ...) > > The open function will connect to the specified socket, the close function > will obviously close the connection. > > When sqlnotify_send is called from your trigger, it will insert its > arguments into the associated template and then send the resultant string > to the socket. > > Implement a daemon in your favourite language with a mail() function to > send the mails. I wouldn't recommend that you send a mail for each notify, > maybe save them up and send every 5 minutes or so. > > PERL/Python are probably good choices for the daemon. As for writing the > extension, it depends how you're using SQLite, but you're probably going to > end up in C. I recommend chapter 9 of the book "Using Sqlite" > > Thanks, > Kevin > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- -- S.Vairamuthu Secret of Success is Passion ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Free Page Data usage
Raheel Gupta wrote: > When I delete just 2-3 rows, that page is not reused for the new data > which will be inserted in the future. SQLite's tables are B-trees, sorted by the rowid. Your new data will probably get an autoincremented rowid, which will be appended at the end of the table. A page gets reorganized only when about 2/3 is free space. > How should I get SQLIte to use the free space within a partially used page > when rows from that page have been deleted. VACUUM (which needs lots of free disk space, and should not be executed too often). Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Errors Compiling Extension percentile.c
>I'm not exactly sure what is causing your compilation errors; however, the >recent releases of System.Data.SQLite (specifically the SQLite.Interop.dll >interop assembly) already include the percentile extension (as well as a >couple other extensions). > >-- >Joe Mistachkin Joe - Thanks for the response, but what does it mean that the extension is included in the SQLite.Interop.dll? When I build with the current version and include the interop.dll, I still get System.Data.SQLite.SQLiteException: SQL logic error or missing database no such function: percentile How do I access such functions? If I am missing some existing docs, please just point me to them. I have tried both: http://system.data.sqlite.org/downloads/1.0.90.0/sqlite-netFx35-setup-bundle-x64-2008-1.0.90.0.exe and http://system.data.sqlite.org/downloads/1.0.90.0/sqlite-netFx35-setup-x64-2008-1.0.90.0.exe Both with the same result. Thanks, Denis Burke ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Error: "File is encrypted or is not a database"
Hi! We have an encrypted SQLite database with a password which is set programmatically in .NET interface. This base has been used by our application for a long time and everything has been OK till today. On opening base this error occurs: "File is encrypted or is not a database". Noone could change the password. This base contains important data, so how can we fix this? Is there any way to restore the DB? Regards, Husain ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Error: "File is encrypted or is not a database"
Hi! We have an encrypted SQLite database with a password which is set programmatically in .NET interface. This base has been used by our application for a long time and everything has been OK till today. On opening base this error occurs: "File is encrypted or is not a database". Noone could change the password. This base contains important data, so how can we fix this? Is there any way to restore the DB? Regards, Husain ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Mimic MERGE in a trigger
On Fri, Feb 7, 2014 at 2:50 PM, Constantine Yannakopoulos < alfasud...@gmail.com> wrote: > As for scaled integers for amounts, if it was up to me I would prefer BCDs > but this choice is out of my hands. Oh, also the "cube" table can contain records for many accumulators (e.g. debit, credit, turnover, quantity etc. which is a dimension) that are in different units of measurement, and some have more than two decimal digits. So the double is the only choice that covers all cases. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Mimic MERGE in a trigger
On Fri, Feb 7, 2014 at 2:10 PM, Simon Slavin wrote: > You're right. Let's see if I can make it up to you. I do think you came > up with the right strategy, and that doing INSERT OR IGNORE is going to > give you the best result overall. > Thanks. I myself couldn't come up with anything better. Two scans on a field which is indexed won't take long. Probably less time > in total than those MERGE commands or some complicated triggers. > I would expect the implementation of the merg e to do a single scan and then choose which branch to execute having kept the found record in memory, so I would expect it to be as fast as an UPDATE. Also, while not an issue in a trigger a merge is atomic; so is the insert or ignore strategy of course. > > A few notes: > > An alternative way to do it would simply be not to store current balances. > Whenever you want to know the balance as of a particular date, calculate > it using total(). Not only does it mean smaller files and faster inputs, > but you never have to sanity-check your totals and for a system which has > more inputs than reports it is faster overall. This technique is > increasingly used in systems used by banks and in double-entry bookkeeping. > > I assume you have a good reason for not keeping the CustomerAccum.amount > field in the customer table. You did mention that your real scenario was > more complicated than you were giving details of in your post, so that > would explain it. > > Also, SQLite does not have VARCHAR fields (they're stored as text and the > '50' will be ignored) or DATE fields. Dates must be stored as numbers or > text. FLOAT will be interpreted as REAL, and will work the same way, so > that's okay, but if I was doing something primarily for backing or > accounting purposes I wouldn't use a REAL field, I'd use INTEGER and > multiply by 100. > My example was made up to describe the case. The accumulated amounts table actually has much more dimensions, some of them relative to time. For instance, a fiscal year is divided into fiscal periods and the fiscal year and period of the transaction is a dimension as well. Also the table stores a running total as well as period totals. This allows for very fast balance reports, even when the requested time interval extends half-way between periods in which case I do a UNION ALL with transaction records for "half-way" transactions.and a GROUP BY/SUM() afterwards.Transaction processing will not be that intensive in my app's case, but balance reports need to be as fast as possible, that's why I use this concept of a "live cube" although its maintenance introduces an overhead in OLTP.. Column data types are named thus because the Delphi implementation of SQLite API I am using uses names of SQLite declared datatypes to map to dataset field types (TStringField, TIntegerField, TDateTimeField, etc.) a DATE field is considered to be a native datetime double and I have written an SQLite extension library that implements a bunch of manipulation functions to deal with datetimes. I consider float datetimes smaller in size and faster in calculations and comparisons that formatted strings, e.g. -MM-DDThh:nn:ss:zzz. The database needs to be able to be created in database engines of various vendors with only minor changes to table definitions and that may be handled by consultants, that's why you are seeing the terms VARCHAR(), FLOAT, DATE etc; they are familiar from other database vendors. As for scaled integers for amounts, if it was up to me I would prefer BCDs but this choice is out of my hands. --Constantine ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Free Page Data usage
Hi, My Page size is 64KB and I store around 4KB of row data in one row. I store around 1 rows in one table and the database size reaches 42MB. Now, I am facing a peculiar problem. When I delete just 2-3 rows, that page is not reused for the new data which will be inserted in the future. The pragma freelist_count shows 0 if I delete the 1st 10 rows (approx 40KB) Only if I delete more than 20 rows does the freelist_count reflect 1 page as free. How should I get SQLIte to use the free space within a partially used page when rows from that page have been deleted. This causes a lot of space wastage when I store more rows. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Unit-test failure in SQLite (64-bit)
When running the SQLite unit-tests on Cygwin64
(but the same is expected to happen on any
64-bit UNIX-like system):
$ .libs/testfixture.exe test/tclsqlite.test
tcl-1.1... Ok
tcl-1.2... Ok
tcl-1.2.1...Segmentation fault (core dumped)
The cause of this crash is the Tcl function
Tcl_AppendResult(), which expects a 64-bit
NULL-pointer as last argument. But tclsqlite.c
uses 0 here, which is a 32-bit integer. The
compiler cannot know this, because the function
has a variable number of arguments.
Below's patch fixes this specific unit-test failure.
Many other Tcl_AppendResult() calls make the same
mistake, but in many other places it is done correctly.
The change from Tcl_GetStringFromObj() to
Tcl_GetString() is just meant to shorten the
line length: since the second argument is 0,
those are equivalent.
Regards,
Jan Nijtmans
--- src/tclsqlite.c
+++ src/tclsqlite.c
@@ -1835,11 +1835,11 @@
pDb->maxStmt = n;
}
}
}else{
Tcl_AppendResult( interp, "bad option \"",
- Tcl_GetStringFromObj(objv[2],0), "\": must be flush or size", 0);
+ Tcl_GetString(objv[2]), "\": must be flush or size", (char *)0);
return TCL_ERROR;
}
break;
}
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Mimic MERGE in a trigger
On 7 Feb 2014, at 11:32am, Constantine Yannakopoulos wrote: > On Fri, Feb 7, 2014 at 1:27 PM, Simon Slavin wrote: > >> Don't do it like that. >> >> Use 'INSERT OR IGNORE' to insert a record with a zero amount. >> Then update the record which, now, definitely does exist. > > Obviously you didn't read all of my message. :) You're right. Let's see if I can make it up to you. I do think you came up with the right strategy, and that doing INSERT OR IGNORE is going to give you the best result overall. Two scans on a field which is indexed won't take long. Probably less time in total than those MERGE commands or some complicated triggers. A few notes: An alternative way to do it would simply be not to store current balances. Whenever you want to know the balance as of a particular date, calculate it using total(). Not only does it mean smaller files and faster inputs, but you never have to sanity-check your totals and for a system which has more inputs than reports it is faster overall. This technique is increasingly used in systems used by banks and in double-entry bookkeeping. I assume you have a good reason for not keeping the CustomerAccum.amount field in the customer table. You did mention that your real scenario was more complicated than you were giving details of in your post, so that would explain it. Also, SQLite does not have VARCHAR fields (they're stored as text and the '50' will be ignored) or DATE fields. Dates must be stored as numbers or text. FLOAT will be interpreted as REAL, and will work the same way, so that's okay, but if I was doing something primarily for backing or accounting purposes I wouldn't use a REAL field, I'd use INTEGER and multiply by 100. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Mimic MERGE in a trigger
On Fri, Feb 7, 2014 at 1:27 PM, Simon Slavin wrote: > Don't do it like that. > > Use 'INSERT OR IGNORE' to insert a record with a zero amount. > Then update the record which, now, definitely does exist. > Obviously you didn't read all of my message. :) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Mimic MERGE in a trigger
On 7 Feb 2014, at 11:03am, Constantine Yannakopoulos wrote: > I would like to create a trigger on table CustomerTrans that performs the > accumulation of transaction amounts in CustomerAccum. This trigger should > - Insert a record with the transaction amount if a record for the > customerid does not exist > - Update an existing record, adding the transaction amount to the > cumulative amount. Don't do it like that. Use 'INSERT OR IGNORE' to insert a record with a zero amount. Then update the record which, now, definitely does exist. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Mimic MERGE in a trigger
Hello everyone, Given a master table, a table of transactions and a table of cumulative amounts per transaction master: CREATE TABLE Customer ( id INTEGER PRIMARY KEY, firstname VARCHAR(50), lastname VARCHAR(50) ); CREATE TABLE CustomerTrans ( id INTEGER PRIMARY KEY, transactiondate DATE, customerid INTEGER, amount FLOAT FOREIGN KEY (customerid) REFERENCES Customer (id) ); CREATE TABLE CustomerAccum ( customerid INTEGER PRIMARY KEY, amount FLOAT FOREIGN KEY (customerid) REFERENCES Customer (id) ); I would like to create a trigger on table CustomerTrans that performs the accumulation of transaction amounts in CustomerAccum. This trigger should - Insert a record with the transaction amount if a record for the customerid does not exist - Update an existing record, adding the transaction amount to the cumulative amount. Normally this would be done with a merge statement: CREATE TRIGGER AI_CustomerTrans AFER INSERT ON CustomerTrans FOR EACH ROW BEGIN MERGE INTO CustomerAccum USING (SELECT new.customerid, new.amount) trans ON trans.customerid = CustomerAccum.customerid WHEN MATCHED THEN UPDATE SET CustomerAccum.amount = CustomerAccum.amount + trans.amount WHEN NOT MATCHED THEN INSERT (customerid, amount) VALUES (trans.customerid, trans.amount); END; but since MERGE statements are not supported by SqLite, and trigger code does not support flow control (IF THEN ELSE constructs) I think that the only way to do this is by using the conflict clause and always try to insert a new record: CREATE TRIGGER AI_CustomerTrans AFTER INSERT ON CustomerTrans FOR EACH ROW BEGIN INSERT OR IGNORE INTO CustomerAccum (customerid, amount) VALUES (new.customerid, 0); UPDATE CustomerAccum SET amount = amount + new.amount WHERE CustomerAccum.customerid = new.customerid; END; But this will always perform two index scans on CustomerAccum, which is suboptimal for the vast majority of trigger invokations. Is there another way to achieve this MERGE without the double scan? Mind you that the case I described is a simplified version of the problem I am trying to solve, where the tables have much more dimensions than just the customerid, and the size of data will be of the order of hundreds of thousands of rows, thus my concern for the redundant most of the times INSERT OR IGNORE. Mind you also that I want to avoid the INSERT OR REPLACE construct because on conflict it deletes and reinserts records invoking any delete triggers and FK ON DELETE clauses which does not suit my needs. Thanks in advance --Constantine. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Send Mail from sqlite
On 7 Feb 2014, at 09:59, Vairamuthu wrote: > Thanks for your response, it will be great help if you can get me some > sample code or algorithms, on that. I'm assuming you're using SQLite embedded within another application on something unix like. Off the top of my head, here's how I would do it - there may be an extension like this lying around somewhere already? Create an SQLite3 extension that implements the following functions sqlnotify_open(, , ) sqlnotify_close() sqlnotify_send(, , , …) The open function will connect to the specified socket, the close function will obviously close the connection. When sqlnotify_send is called from your trigger, it will insert its arguments into the associated template and then send the resultant string to the socket. Implement a daemon in your favourite language with a mail() function to send the mails. I wouldn't recommend that you send a mail for each notify, maybe save them up and send every 5 minutes or so. PERL/Python are probably good choices for the daemon. As for writing the extension, it depends how you're using SQLite, but you're probably going to end up in C. I recommend chapter 9 of the book "Using Sqlite" Thanks, Kevin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Send Mail from sqlite
On Fri, Feb 7, 2014 at 11:06 AM, Simon Slavin wrote: > On 7 Feb 2014, at 9:59am, Vairamuthu wrote: > > > Thanks for your response, it will be great help if you can get me some > > sample code or algorithms, on that. > > That would depend on what programming language you are using and what > access it has to any method of sending mail. However you do it, it won't > be done inside SQLite so it won't be anything to do with this mailing list. Right. And to get you started, assuming you are using SQLite from C/C++, here are a few links: http://en.wikipedia.org/wiki/Simple_Mail_Transfer_Protocol http://libsmtp.berlios.de/ http://www.vmime.org/ http://curl.haxx.se/libcurl/c/smtp-tls.html http://libsmtpc.nerdfeliz.com/ http://stackoverflow.com/questions/8995476/what-is-the-most-used-and-flexible-smtp-client-library-in-c-c Otherwise many programming languages have this built-in as part of their "standard library" or "frameworks", like Perl, Python, Java, .NET, Go, etc... --DD ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Send Mail from sqlite
Not sure now if SQLite does events that can be picked up by your application, but I use a VB wrapper (vbRichClient, written by Olaf Schmidt) that does do SQLite events. If events can't be used then I presume you need to poll the database for changes. RBS On Fri, Feb 7, 2014 at 10:06 AM, Simon Slavin wrote: > > On 7 Feb 2014, at 9:59am, Vairamuthu wrote: > > > Thanks for your response, it will be great help if you can get me some > > sample code or algorithms, on that. > > That would depend on what programming language you are using and what > access it has to any method of sending mail. However you do it, it won't > be done inside SQLite so it won't be anything to do with this mailing list. > > Simon. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Send Mail from sqlite
On 7 Feb 2014, at 9:59am, Vairamuthu wrote: > Thanks for your response, it will be great help if you can get me some > sample code or algorithms, on that. That would depend on what programming language you are using and what access it has to any method of sending mail. However you do it, it won't be done inside SQLite so it won't be anything to do with this mailing list. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Send Mail from sqlite
Thanks for your response, it will be great help if you can get me some sample code or algorithms, on that. On Fri, Feb 7, 2014 at 5:55 PM, Simon Slavin wrote: > > On 7 Feb 2014, at 9:51am, Vairamuthu wrote: > > > Hi > > > > I like to know if there is any possible to send a mail from sqlite. > > No it's not. Sorry, you are going to have to do some programming. > > Simon. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- -- S.Vairamuthu Secret of Success is Passion ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Send Mail from sqlite
On 7 Feb 2014, at 9:51am, Vairamuthu wrote: > Hi > > I like to know if there is any possible to send a mail from sqlite. No it's not. Sorry, you are going to have to do some programming. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Send Mail from sqlite
Hi I like to know if there is any possible to send a mail from sqlite. I wanted to know if there is option to configure smtp in sqlite, which will help me to send a mail. My requirement I need to send a notification mail once the table is getting updated/inserted/deleted. Regards Muthu ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
