[sqlite] Suppress auto index

2008-01-09 Thread Yuvaraj Athur Raghuvir
Hello,

When I upload data from a file into a table, I see that SQLite creates an
auto index table. Is it possible to instruct SQLite not to create any auto
indices for the tables I create? If so, how?

Thanks and Regards,
Yuva


[sqlite] How to persist MAX_PAGE_COUNT?

2008-01-08 Thread Yuvaraj Athur Raghuvir
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?

2008-01-08 Thread Yuvaraj Athur Raghuvir
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 ?

2008-01-04 Thread Yuvaraj Athur Raghuvir
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

2007-12-17 Thread Yuvaraj Athur Raghuvir
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

2007-12-12 Thread Yuvaraj Athur Raghuvir
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]

2007-12-11 Thread Yuvaraj Athur Raghuvir
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]

2007-12-11 Thread Yuvaraj Athur Raghuvir
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]

2007-12-10 Thread Yuvaraj Athur Raghuvir
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.....

2007-12-10 Thread Yuvaraj Athur Raghuvir
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?

2006-10-02 Thread Yuvaraj Athur Raghuvir

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