[sqlite] How to persist MAX_PAGE_COUNT?
Hello, I am not able to persist the MAX_PAGE_COUNT on the database I am using. OS : Windows Version: 3_5_4 dated 14.Dec.2007 1) Create a database - say store01.sqlite 2) Open store01.sqlite using sqlite.exe 3) Execute "pragma MAX_PAGE_COUNT = 20;" 4) Execute "pragma MAX_PAGE_COUNT;" --> returns 20 5) Quit sqlite.exe 6) Reopen store01.sqlite using sqlite.exe 7) Execute "pragma MAX_PAGE_COUNT;" --> returns 1073741823 (default)!! Why? How can I persist the MAX_PAGE_COUNT value so that the database size remains what I want it to be? Further, from sqlite3_analyzer output can I get the MAX_PAGE_COUNT value. Thanks, Yuva
[sqlite] How to persist MAX_PAGE_COUNT?
Hello, When I set the MAX_PAGE_COUNT and retrieve the value in the same connection, I get what I set. However, when I use a new connection to the database, I get the default value. Also if I close all connections and restart, I am getting the default value. Statements used: PRAGMA MAX_PAGE_COUNT =100; PRAGMA MAX_PAGE_COUNT; Am I missing something? Regards, Yuva sqlite3.dll dated 14.Dec.2007 (3_5_4)
[sqlite] Size of Meta data ?
Hello, Is there any way to estimate the size of the metadata of a SQLite database? I am trying to do the following: 1) Set the DB size using MAX_PAGE_COUNT and PAGE_SIZE parameters 2) I want to do a bulk insert. 3) However, I want to insert only those many records such that the insert succeeds. 4) I also want to keep a 30% buffer for the data. I would like to start new DB instances when that threshold is reached. To do step 3 successfully, I have to estimate the size of the meta-data. Assuming that there is no more data model changes, is there a algorithm or heuristic to determine the amount of meta data present in the database? Thanks and Regards, Yuva
[sqlite] Re: SQLite and Columnar Databases
Hello, Thanks for the interesting discussion. What I got so far is summarized below: 1) Row based versus Column based storage is an implementation detail. 2) SQL used for access is independent of storage mechanism adopted. 3) Row based storage with indices on all columns reaches read performance of column based storage. 4) Creating/updating indices fast using new algorithms is a direction of improvement for SQLite Now, if the storage is an implementation detail, can the following scenario be realized? a) Given: Distributed highly-available system which is implemented as maintaining replicas of data b) The replicas of data have different storage mechanisms which is also recorded in the (distributed) database coordinator. c) This would, in essence, be a hybrid database - hybrid in the sense of using different data storage strategies (row-based / column-based) in the replicas. This would allow for the database coordinator to intelligently respond to the various operations on the database by redirecting the original request to the appropriate replica. The cost would be when the data changes and each of the replicas have to be brought into sync. Here again, the intelligence should be such that the storage schema that achieves the best performance for that SQL statement should be used and the sync can happen in the back ground. My perspective is that progressively, the data storage (implementation) strategies will pay an important role given that OLTP/OLAP requirements are getting blurred. Thoughts? Regards, Yuva On Dec 13, 2007 4:42 AM, Yuvaraj Athur Raghuvir <[EMAIL PROTECTED]> wrote: > Hello, > > There seems to be a high interest in columnar databases recently. > > Is there any plan of supporting data organization as a columnar database > in SQLite? What are the challenges here? > > Regards, > Yuva >
[sqlite] SQLite and Columnar Databases
Hello, There seems to be a high interest in columnar databases recently. Is there any plan of supporting data organization as a columnar database in SQLite? What are the challenges here? Regards, Yuva
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]
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] > > - > >
[sqlite] Regarding explicitly managing SQLite Databases....[Resend]
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). (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.
[sqlite] Explicitly managing multiple SQLite databases.....
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). (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
[sqlite] How to create DLL for windows?
Hello, I have sync'ed and created the exe using Cygwin on Windows. I also have the libtool available. Now, I donot see the dll that I can use in Windows environment. How to create a dll? Regards, Yuva
