Re: [sqlite] Regarding explicitly managing SQLite Databases....[Resend]
On Tue, 11 Dec 2007 15:37:52 +0530, "Yuvaraj Athur Raghuvir" <[EMAIL PROTECTED]> wrote: >Thanks. > >To ensure that the database size is closest to the data in the database, I >see the following have to be taken care off: >a) The latest in-memory transactions have to be flushed to disk. >How? >b) The space has to be reclaimed after delete/drop. >For this I use the PRAGMA VACUUM statement. > >How do I ensure that there is nothing >pending in the memory so that external >file size APIs are accurate for my work? Using transactions (BEGIN/COMMIT): after every commit all data is flushed to disk (and also kept in memory in the page cache for future reference). In other words: whenever there is no journal file, the database file is up to date. Without explicit transactions, every SQL statement that modifies the database is a transaction in itself (autocommit). Ref: http://www.sqlite.org/arch.html http://www.sqlite.org/atomiccommit.html http://www.sqlite.org/lockingv3.html (and perhaps http://www.sqlite.org/sharedcache.html ) >~Yuva > >On Dec 11, 2007 2:18 PM, Dan <[EMAIL PROTECTED]> wrote: > >> >> On Dec 11, 2007, at 3:00 PM, Yuvaraj Athur Raghuvir wrote: >> >> > 1) Can I assume that MAX_PAGE_COUNT * PAGE_SIZE = size of file on >> > disk? >> >> No. It is the maximum size of the file. An attempt to >> insert data that would cause the file to grow larger than >> this will return SQLITE_FULL. >> >> > 2) When insert statements are being executed, I would like to - for >> > example >> > - redirect the queries to another data base when 70% of the space is >> > reached. How can I do that? >> >> I think you would have to check the size of the file on disk using >> external (non-SQLite) APIs before each insert. >> >> Dan. >> >> >> >> >> > >> > ~Yuva >> > >> > On Dec 11, 2007 11:48 AM, Dan <[EMAIL PROTECTED]> wrote: >> > >> >> >> >> On Dec 11, 2007, at 8:39 AM, Yuvaraj Athur Raghuvir wrote: >> >> >> >>> Hello, >> >>> >> >>> I am trying a simple experiment where I want to limit the size of >> >>> the file >> >>> that SQLite uses. Further, I want to manage the growth of the >> >>> database(s) >> >>> explicitly. >> >>> >> >>> One of the first aspects I want to manage is the size of the file >> >>> on the >> >>> disk. I want to set hard limits on the size and during query >> >>> execution, on >> >>> failures, explicitly manage the persistence across multiple >> >>> databases (or >> >>> disk files). >> >> >> >> I'm not sure I understand the second part of the question. >> >> >> >> Does "pragma max_page_count" (http://www.sqlite.org/pragma.html) >> >> help any? >> >> >> >> Dan. >> >> >> >> >> >>> >> >>> (a) Since I am new to SQLite, I would like to hear from the >> >>> community on how >> >>> this can be done using what SQLite3 provides. >> >>> >> >>> If there are specifics that need to be managed outside the >> >>> context of >> >>> SQLite, I am fine with that. However, for doing external >> >>> management I >> >>> believe I would need hooks into the basic management of the >> >>> database. What I >> >>> would like to know is >> >>> (b) are such hooks already available? >> >>> (c) if these need to be implemented, the list of source files I >> >>> need to look >> >>> into will help. >> >>> >> >>> Thanks for your time, >> >>> Yuva >> >>> >> >>> p.s: I am resending this since I am not sure if it has reached the >> >>> mailing >> >>> list. >> >> >> >> >> >> >> >> - >> >> >> >> To unsubscribe, send email to [EMAIL PROTECTED] >> >> >> >> - >> >> >> >> >> >> >> >> >> >> - >> To unsubscribe, send email to [EMAIL PROTECTED] >> >> - >> >> -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Regarding explicitly managing SQLite Databases....[Resend]
Thanks. To ensure that the database size is closest to the data in the database, I see the following have to be taken care off: a) The latest in-memory transactions have to be flushed to disk. How? b) The space has to be reclaimed after delete/drop. For this I use the PRAGMA VACUUM statement. How do I ensure that there is nothing pending in the memory so that external file size APIs are accurate for my work? ~Yuva On Dec 11, 2007 2:18 PM, Dan <[EMAIL PROTECTED]> wrote: > > On Dec 11, 2007, at 3:00 PM, Yuvaraj Athur Raghuvir wrote: > > > 1) Can I assume that MAX_PAGE_COUNT * PAGE_SIZE = size of file on > > disk? > > No. It is the maximum size of the file. An attempt to > insert data that would cause the file to grow larger than > this will return SQLITE_FULL. > > > 2) When insert statements are being executed, I would like to - for > > example > > - redirect the queries to another data base when 70% of the space is > > reached. How can I do that? > > I think you would have to check the size of the file on disk using > external (non-SQLite) APIs before each insert. > > Dan. > > > > > > > > ~Yuva > > > > On Dec 11, 2007 11:48 AM, Dan <[EMAIL PROTECTED]> wrote: > > > >> > >> On Dec 11, 2007, at 8:39 AM, Yuvaraj Athur Raghuvir wrote: > >> > >>> Hello, > >>> > >>> I am trying a simple experiment where I want to limit the size of > >>> the file > >>> that SQLite uses. Further, I want to manage the growth of the > >>> database(s) > >>> explicitly. > >>> > >>> One of the first aspects I want to manage is the size of the file > >>> on the > >>> disk. I want to set hard limits on the size and during query > >>> execution, on > >>> failures, explicitly manage the persistence across multiple > >>> databases (or > >>> disk files). > >> > >> I'm not sure I understand the second part of the question. > >> > >> Does "pragma max_page_count" (http://www.sqlite.org/pragma.html) > >> help any? > >> > >> Dan. > >> > >> > >>> > >>> (a) Since I am new to SQLite, I would like to hear from the > >>> community on how > >>> this can be done using what SQLite3 provides. > >>> > >>> If there are specifics that need to be managed outside the > >>> context of > >>> SQLite, I am fine with that. However, for doing external > >>> management I > >>> believe I would need hooks into the basic management of the > >>> database. What I > >>> would like to know is > >>> (b) are such hooks already available? > >>> (c) if these need to be implemented, the list of source files I > >>> need to look > >>> into will help. > >>> > >>> Thanks for your time, > >>> Yuva > >>> > >>> p.s: I am resending this since I am not sure if it has reached the > >>> mailing > >>> list. > >> > >> > >> > >> - > >> > >> To unsubscribe, send email to [EMAIL PROTECTED] > >> > >> - > >> > >> > >> > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > > - > >
Re: [sqlite] Regarding explicitly managing SQLite Databases....[Resend]
On Dec 11, 2007, at 3:00 PM, Yuvaraj Athur Raghuvir wrote: 1) Can I assume that MAX_PAGE_COUNT * PAGE_SIZE = size of file on disk? No. It is the maximum size of the file. An attempt to insert data that would cause the file to grow larger than this will return SQLITE_FULL. 2) When insert statements are being executed, I would like to - for example - redirect the queries to another data base when 70% of the space is reached. How can I do that? I think you would have to check the size of the file on disk using external (non-SQLite) APIs before each insert. Dan. ~Yuva On Dec 11, 2007 11:48 AM, Dan <[EMAIL PROTECTED]> wrote: On Dec 11, 2007, at 8:39 AM, Yuvaraj Athur Raghuvir wrote: Hello, I am trying a simple experiment where I want to limit the size of the file that SQLite uses. Further, I want to manage the growth of the database(s) explicitly. One of the first aspects I want to manage is the size of the file on the disk. I want to set hard limits on the size and during query execution, on failures, explicitly manage the persistence across multiple databases (or disk files). I'm not sure I understand the second part of the question. Does "pragma max_page_count" (http://www.sqlite.org/pragma.html) help any? Dan. (a) Since I am new to SQLite, I would like to hear from the community on how this can be done using what SQLite3 provides. If there are specifics that need to be managed outside the context of SQLite, I am fine with that. However, for doing external management I believe I would need hooks into the basic management of the database. What I would like to know is (b) are such hooks already available? (c) if these need to be implemented, the list of source files I need to look into will help. Thanks for your time, Yuva p.s: I am resending this since I am not sure if it has reached the mailing list. - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Regarding explicitly managing SQLite Databases....[Resend]
1) Can I assume that MAX_PAGE_COUNT * PAGE_SIZE = size of file on disk? 2) When insert statements are being executed, I would like to - for example - redirect the queries to another data base when 70% of the space is reached. How can I do that? ~Yuva On Dec 11, 2007 11:48 AM, Dan <[EMAIL PROTECTED]> wrote: > > On Dec 11, 2007, at 8:39 AM, Yuvaraj Athur Raghuvir wrote: > > > Hello, > > > > I am trying a simple experiment where I want to limit the size of > > the file > > that SQLite uses. Further, I want to manage the growth of the > > database(s) > > explicitly. > > > > One of the first aspects I want to manage is the size of the file > > on the > > disk. I want to set hard limits on the size and during query > > execution, on > > failures, explicitly manage the persistence across multiple > > databases (or > > disk files). > > I'm not sure I understand the second part of the question. > > Does "pragma max_page_count" (http://www.sqlite.org/pragma.html) > help any? > > Dan. > > > > > > (a) Since I am new to SQLite, I would like to hear from the > > community on how > > this can be done using what SQLite3 provides. > > > > If there are specifics that need to be managed outside the context of > > SQLite, I am fine with that. However, for doing external management I > > believe I would need hooks into the basic management of the > > database. What I > > would like to know is > > (b) are such hooks already available? > > (c) if these need to be implemented, the list of source files I > > need to look > > into will help. > > > > Thanks for your time, > > Yuva > > > > p.s: I am resending this since I am not sure if it has reached the > > mailing > > list. > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > > - > >
Re: [sqlite] Regarding explicitly managing SQLite Databases....[Resend]
On Dec 11, 2007, at 8:39 AM, Yuvaraj Athur Raghuvir wrote: Hello, I am trying a simple experiment where I want to limit the size of the file that SQLite uses. Further, I want to manage the growth of the database(s) explicitly. One of the first aspects I want to manage is the size of the file on the disk. I want to set hard limits on the size and during query execution, on failures, explicitly manage the persistence across multiple databases (or disk files). I'm not sure I understand the second part of the question. Does "pragma max_page_count" (http://www.sqlite.org/pragma.html) help any? Dan. (a) Since I am new to SQLite, I would like to hear from the community on how this can be done using what SQLite3 provides. If there are specifics that need to be managed outside the context of SQLite, I am fine with that. However, for doing external management I believe I would need hooks into the basic management of the database. What I would like to know is (b) are such hooks already available? (c) if these need to be implemented, the list of source files I need to look into will help. Thanks for your time, Yuva p.s: I am resending this since I am not sure if it has reached the mailing list. - To unsubscribe, send email to [EMAIL PROTECTED] -
