Re: [sqlite] howto setup SQLite with Powershell ?

2008-10-09 Thread Jeffrey Becker
Technically there's nothing stopping you from using sqlite3.exe in the
same way you'd use it in a unix shell script.

On Thu, Oct 9, 2008 at 2:54 PM, Helga Lachmann <[EMAIL PROTECTED]> wrote:
> The original problem is given in the topic.
> The System.Data.SQLite provider (which I currently can't get running) is 
> hopefully only one solution.
>
> Do you have an answer to the original question?
> SQLite provides sqlite3.exe and sqlite3.dll.
> How can I use that with powershell?
>
>
>  Original-Nachricht 
>> Datum: Thu, 9 Oct 2008 14:40:47 -0400
>> Von: "Brad Stiles" <[EMAIL PROTECTED]>
>> An: "General Discussion of SQLite Database" 
>> Betreff: Re: [sqlite] howto setup SQLite with Powershell ?
>
>> > btw: if I rerun InstallUtil I now get a message:
>> > No public installers with the RunInstallerAttribute.Yes attribute could
>> be found in the ...\System.Data.SQLite.dll assembly.
>>
>> You should really be asking these questions on the forum/list
>> dedicated to the support of the product you are working with, i.e. the
>> producer of the System.Data.SQLite provider.
>>
>> You will likely need to either install the assembly to the GAC
>> yourself, or use some other method to tell PowerShell where the
>> assembly is located.
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
> --
> GMX startet ShortView.de. Hier findest Du Leute mit Deinen Interessen!
> Jetzt dabei sein: http://www.shortview.de/[EMAIL PROTECTED]
> ___
> 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] howto setup SQLite with Powershell ?

2008-10-09 Thread Jeffrey Becker
As for the code being 'bad' I saw a lot of blog posts that said it
caused problems but very few that had reasonable replacements.  There
was one post that gave a hundred lines or so of code to do the same
thing.

On Thu, Oct 9, 2008 at 11:25 AM,  <[EMAIL PROTECTED]> wrote:
> if that is "bad" how can it be done correct/better?
>
>  [System.Reflection.Assembly]::LoadWithPartialName("System.Data.SQLite")
> itself produces no error, but also
>  [System.Reflection.Assembly]::LoadWithPartialName("thisDoesntMatter")
> produces no error,
> but the following
>  $conn = New-Object System.Data.SQLite.SQLiteConnection($Conn)
> gives the error
>  Type [System.Data.SQLite.SQLiteConnection] cannot be found.
>  Make sure assembly containing this type is loaded.
>
> [messages retranslated from german]
>
>
> ---- Original-Nachricht 
>> Datum: Thu, 9 Oct 2008 11:04:36 -0400
>> Von: "Jeffrey Becker" <[EMAIL PROTECTED]>
>> An: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
>> Betreff: Re: [sqlite] howto setup SQLite with Powershell ?
>
>> Oh I see.  System.Data.SQLite doesn't provide a Snap-in so
>> Add-PSSnapin wont work.  You could use:
>> [System.Reflection.Assembly]::LoadWithPartialName("System.Data.SQLite")
>> But from what I see this method is considered 'bad' because this could
>> lead to versioning issues.  Since the System.Data.Common interfaces
>> are very stable I'd personally feel safe doing this but ymmv.
>>
>> On Thu, Oct 9, 2008 at 10:15 AM, Helga Lachmann <[EMAIL PROTECTED]> wrote:
>> > I don't know.
>> > How should I do this?
>> > I think that's the point.
>> >
>> > As I mentioned I tried manually with Add-PSSnapin, but that doesn't
>> work.
>> > (... PowerShell-Snap-In "System.Data.SQLite" is not installed ...)
>> >
>> >
>> >
>> >  Original-Nachricht 
>> >> Datum: Thu, 9 Oct 2008 10:03:07 -0400
>> >> Von: "Jeffrey Becker" <[EMAIL PROTECTED]>
>> >> An: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
>> >> Betreff: Re: [sqlite] howto setup SQLite with Powershell ?
>> >
>> >> IIRC powershell doesnt automatically load every assembly in the GAC.
>> >> How are you loading the System.Data.SQLite assembly?
>> >>
>> >> On Thu, Oct 9, 2008 at 8:51 AM,  <[EMAIL PROTECTED]> wrote:
>> >> > Hello,
>> >> >
>> >> > I looked unavailingly around for hours to get SQLite and Powershell
>> >> working together.
>> >> >
>> >> > Is there any little installation/configuration guide for this?
>> >> >
>> >> > The problem seems to be how to get SQLite available to PS.
>> >> >
>> >> >
>> >> > I used installutil, which reported "successful".
>> >> > AddPSSnapin failed with error "...DLL not installed"
>> >> >
>> >> > I tried to connect via Database Connection and even with the
>> >> DBProviderFactory method.
>> >> > But Powershell always reports an error ("Unable to find the requested
>> >> .Net Framework Data Provider.  It may not be installed.")
>> >> >
>> >> > I installed SQLite for ADO.NET 2.0 from sourceforge.
>> >> > The result seems ok (finished successfully with no errors, SQLite.NET
>> >> program-folder)
>> >> > The documentation tells also about using DbProviderFactories.
>> >> > It says to modify the app.config file. This seems to be a DotNet
>> >> scenario for VisualStudio but doesn't work for PS.
>> >> >
>> >> > In my desperation I copied the SQLite-DLL around in various
>> locations,
>> >> added manually a line to the machine.config file, but nothing worked.
>> >> >
>> >> >
>> >> > I'm using XPPro, SP3.
>> >> >
>> >> >
>> >> > thanks a lot
>> >> >
>> >> > H.L.
>> >> >
>> >> > --
>> >> > GMX startet ShortView.de. Hier findest Du Leute mit Deinen
>> Interessen!
>> >> > Jetzt dabei sein:
>> >> http://www.shortview.de/[EMAIL PROTECTED]
>> >> > ___
>> >> > sqlite-users mailing list
>> >> > sqlite-users@sqlite.org
>&g

Re: [sqlite] howto setup SQLite with Powershell ?

2008-10-09 Thread Jeffrey Becker
When I run the LoadWithPartialName I get:

GACVersionLocation
------
True   v2.0.50727
C:\WINDOWS\assembly\GAC_32\System.Data.SQLite\1.0.60.0__db937bc2d44ff139\System.Data.SQLite.dll

printed on my console.  Does this happen for you?  If not that would
lead me to believe that the System.Data.SQLite.dll file isnt properly
installed in the GAC.

On Thu, Oct 9, 2008 at 11:25 AM,  <[EMAIL PROTECTED]> wrote:
> if that is "bad" how can it be done correct/better?
>
>  [System.Reflection.Assembly]::LoadWithPartialName("System.Data.SQLite")
> itself produces no error, but also
>  [System.Reflection.Assembly]::LoadWithPartialName("thisDoesntMatter")
> produces no error,
> but the following
>  $conn = New-Object System.Data.SQLite.SQLiteConnection($Conn)
> gives the error
>  Type [System.Data.SQLite.SQLiteConnection] cannot be found.
>  Make sure assembly containing this type is loaded.
>
> [messages retranslated from german]
>
>
> ---- Original-Nachricht 
>> Datum: Thu, 9 Oct 2008 11:04:36 -0400
>> Von: "Jeffrey Becker" <[EMAIL PROTECTED]>
>> An: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
>> Betreff: Re: [sqlite] howto setup SQLite with Powershell ?
>
>> Oh I see.  System.Data.SQLite doesn't provide a Snap-in so
>> Add-PSSnapin wont work.  You could use:
>> [System.Reflection.Assembly]::LoadWithPartialName("System.Data.SQLite")
>> But from what I see this method is considered 'bad' because this could
>> lead to versioning issues.  Since the System.Data.Common interfaces
>> are very stable I'd personally feel safe doing this but ymmv.
>>
>> On Thu, Oct 9, 2008 at 10:15 AM, Helga Lachmann <[EMAIL PROTECTED]> wrote:
>> > I don't know.
>> > How should I do this?
>> > I think that's the point.
>> >
>> > As I mentioned I tried manually with Add-PSSnapin, but that doesn't
>> work.
>> > (... PowerShell-Snap-In "System.Data.SQLite" is not installed ...)
>> >
>> >
>> >
>> >  Original-Nachricht 
>> >> Datum: Thu, 9 Oct 2008 10:03:07 -0400
>> >> Von: "Jeffrey Becker" <[EMAIL PROTECTED]>
>> >> An: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
>> >> Betreff: Re: [sqlite] howto setup SQLite with Powershell ?
>> >
>> >> IIRC powershell doesnt automatically load every assembly in the GAC.
>> >> How are you loading the System.Data.SQLite assembly?
>> >>
>> >> On Thu, Oct 9, 2008 at 8:51 AM,  <[EMAIL PROTECTED]> wrote:
>> >> > Hello,
>> >> >
>> >> > I looked unavailingly around for hours to get SQLite and Powershell
>> >> working together.
>> >> >
>> >> > Is there any little installation/configuration guide for this?
>> >> >
>> >> > The problem seems to be how to get SQLite available to PS.
>> >> >
>> >> >
>> >> > I used installutil, which reported "successful".
>> >> > AddPSSnapin failed with error "...DLL not installed"
>> >> >
>> >> > I tried to connect via Database Connection and even with the
>> >> DBProviderFactory method.
>> >> > But Powershell always reports an error ("Unable to find the requested
>> >> .Net Framework Data Provider.  It may not be installed.")
>> >> >
>> >> > I installed SQLite for ADO.NET 2.0 from sourceforge.
>> >> > The result seems ok (finished successfully with no errors, SQLite.NET
>> >> program-folder)
>> >> > The documentation tells also about using DbProviderFactories.
>> >> > It says to modify the app.config file. This seems to be a DotNet
>> >> scenario for VisualStudio but doesn't work for PS.
>> >> >
>> >> > In my desperation I copied the SQLite-DLL around in various
>> locations,
>> >> added manually a line to the machine.config file, but nothing worked.
>> >> >
>> >> >
>> >> > I'm using XPPro, SP3.
>> >> >
>> >> >
>> >> > thanks a lot
>> >> >
>> >> > H.L.
>> >> >
>> >> > --
>> >> > GMX startet ShortView.de. Hier findest Du Leute mit Deinen
>> Interessen!
>> >> > Jetzt dabei sein:
>> >> http://www.shortview.de/[EMAIL PROTECTED]

Re: [sqlite] howto setup SQLite with Powershell ?

2008-10-09 Thread Jeffrey Becker
Oh I see.  System.Data.SQLite doesn't provide a Snap-in so
Add-PSSnapin wont work.  You could use:
[System.Reflection.Assembly]::LoadWithPartialName("System.Data.SQLite")
But from what I see this method is considered 'bad' because this could
lead to versioning issues.  Since the System.Data.Common interfaces
are very stable I'd personally feel safe doing this but ymmv.

On Thu, Oct 9, 2008 at 10:15 AM, Helga Lachmann <[EMAIL PROTECTED]> wrote:
> I don't know.
> How should I do this?
> I think that's the point.
>
> As I mentioned I tried manually with Add-PSSnapin, but that doesn't work.
> (... PowerShell-Snap-In "System.Data.SQLite" is not installed ...)
>
>
>
>  Original-Nachricht ----
>> Datum: Thu, 9 Oct 2008 10:03:07 -0400
>> Von: "Jeffrey Becker" <[EMAIL PROTECTED]>
>> An: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
>> Betreff: Re: [sqlite] howto setup SQLite with Powershell ?
>
>> IIRC powershell doesnt automatically load every assembly in the GAC.
>> How are you loading the System.Data.SQLite assembly?
>>
>> On Thu, Oct 9, 2008 at 8:51 AM,  <[EMAIL PROTECTED]> wrote:
>> > Hello,
>> >
>> > I looked unavailingly around for hours to get SQLite and Powershell
>> working together.
>> >
>> > Is there any little installation/configuration guide for this?
>> >
>> > The problem seems to be how to get SQLite available to PS.
>> >
>> >
>> > I used installutil, which reported "successful".
>> > AddPSSnapin failed with error "...DLL not installed"
>> >
>> > I tried to connect via Database Connection and even with the
>> DBProviderFactory method.
>> > But Powershell always reports an error ("Unable to find the requested
>> .Net Framework Data Provider.  It may not be installed.")
>> >
>> > I installed SQLite for ADO.NET 2.0 from sourceforge.
>> > The result seems ok (finished successfully with no errors, SQLite.NET
>> program-folder)
>> > The documentation tells also about using DbProviderFactories.
>> > It says to modify the app.config file. This seems to be a DotNet
>> scenario for VisualStudio but doesn't work for PS.
>> >
>> > In my desperation I copied the SQLite-DLL around in various locations,
>> added manually a line to the machine.config file, but nothing worked.
>> >
>> >
>> > I'm using XPPro, SP3.
>> >
>> >
>> > thanks a lot
>> >
>> > H.L.
>> >
>> > --
>> > GMX startet ShortView.de. Hier findest Du Leute mit Deinen Interessen!
>> > Jetzt dabei sein:
>> http://www.shortview.de/[EMAIL PROTECTED]
>> > ___
>> > 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
>
> --
> Pt! Schon vom neuen GMX MultiMessenger gehört? Der kann`s mit allen: 
> http://www.gmx.net/de/go/multimessenger
> ___
> 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] howto setup SQLite with Powershell ?

2008-10-09 Thread Jeffrey Becker
IIRC powershell doesnt automatically load every assembly in the GAC.
How are you loading the System.Data.SQLite assembly?

On Thu, Oct 9, 2008 at 8:51 AM,  <[EMAIL PROTECTED]> wrote:
> Hello,
>
> I looked unavailingly around for hours to get SQLite and Powershell working 
> together.
>
> Is there any little installation/configuration guide for this?
>
> The problem seems to be how to get SQLite available to PS.
>
>
> I used installutil, which reported "successful".
> AddPSSnapin failed with error "...DLL not installed"
>
> I tried to connect via Database Connection and even with the 
> DBProviderFactory method.
> But Powershell always reports an error ("Unable to find the requested .Net 
> Framework Data Provider.  It may not be installed.")
>
> I installed SQLite for ADO.NET 2.0 from sourceforge.
> The result seems ok (finished successfully with no errors, SQLite.NET 
> program-folder)
> The documentation tells also about using DbProviderFactories.
> It says to modify the app.config file. This seems to be a DotNet scenario for 
> VisualStudio but doesn't work for PS.
>
> In my desperation I copied the SQLite-DLL around in various locations, added 
> manually a line to the machine.config file, but nothing worked.
>
>
> I'm using XPPro, SP3.
>
>
> thanks a lot
>
> H.L.
>
> --
> GMX startet ShortView.de. Hier findest Du Leute mit Deinen Interessen!
> Jetzt dabei sein: http://www.shortview.de/[EMAIL PROTECTED]
> ___
> 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] Failure on big sqlite tables

2008-10-02 Thread Jeffrey Becker
1) what is the timeout for php?  The default timeouts are generally
pretty low, often 5 seconds or less.
2) What type of indexes do you have on these tables? Indexes speed up
query performance vastly.
3) Query performance is generally _much_ more sensitive to how many
rows a table has rather than how many columns it has.

On Thu, Oct 2, 2008 at 4:40 AM, bcit6k <[EMAIL PROTECTED]> wrote:
>
> Hi all,
>
> i am an absolute newbi on sqlite. My problem, is that i have an db with 2
> tables
> in one table i habe 300 records. in the other 100 records.
> when i did an select on the 300 records the table is quite fast, no problem.
> when i did an select on the big tabel a always get an failure from php max
> execution time. the big tabel have only 5 columns. the select looks for 2
> colums like
>
> SELECT * FROM bigtabel WHERE upper(value1) = 'value1' and upper(value2) =
> 'value2'
>
> i think thats not the problem.
>
> i usw iis and php 5.1.6. i know iis is big shit but in this case its so.
>
> when i did the select with my firefox plugin for sqlite i got the result
> whinin seconds.
>
> so i dont know whats the problem with the file.
>
> thanks for help!
> --
> View this message in context: 
> http://www.nabble.com/Failure-on-big-sqlite-tables-tp19775431p19775431.html
> Sent from the SQLite mailing list archive at Nabble.com.
>
> ___
> 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] seeking storage design assistance

2008-09-30 Thread Jeffrey Becker
ORM is always a tricky business.  My experience is that unless the
entity is very very simple, it's often inappropriate to attempt to do
1 row = 1 entity mapping.  If things are collections of points, then
by all means have a points table.  A lot of this type of stuff can be
made easier by 1) really understanding your object-model first and 2)
comming up with good mapping strategies for your objects.

On Tue, Sep 30, 2008 at 2:08 PM, Jeff Godfrey <[EMAIL PROTECTED]> wrote:
> Hi All,
>
> I've got some general db storage design questions that I hope someone
> can offer some advice on...
>
> I have a need to store some CAD-type geometry in a SQLite database, and
> I'm trying to decide on the best db design.  Specifically, I need to
> store "entity" data, which will include such things as points, lines,
> arcs, circles, and text data.  Along with the physical coordinate data
> that defines each entity, I also need to store various "attributes"
> about each entity (color, layer, style, font (for a text object), etc)...
>
> Ideally, I'd like a single record to tell me everything about a given
> entity.  So, one option would be to create a unique table for each
> entity-type in question, with columns as appropriate.  While that makes
> sense to me on the surface, I also have the need to "step through" the
> geometry in an ordered fashion.  Obviously, I could keep some kind of
> "entity order" table with references to each entity and the table it was
> stored in, but then I don't see a clean way to walk through the geometry.
>
> For instance, my "order" table might direct me to first get a line from
> the line table, then a circle from the circle table, then a text string
> from the text table.  While this isn't too difficult to accomplish via
> program logic, it seems a bit "messy", which has me wondering if there
> might be a better way.
>
> Since the table for each unique entity type would contain (at least
> some) columns unique to the specific entity, I don't think there's a way
> to combine the tables into a single, ordered view that could be easily
> "walked", is there?
>
> The other thought I had was to create a simple "POINT" table, and store
> all the points that make up every entity in that one table.  Then, I'd
> need a way, per entity, to reference which points belonged to the
> current entity.  So, a line would reference 2 records in the POINT
> table, an arc would reference 3 POINT records, etc.
>
> One obvious drawback to this approach is that now there's not a single
> record that contains an "entire" entity, as there would be in the first
> approach.
>
> Since I'm not an expert in this arena, I'm hoping that I'm missing an
> obvious solution.  Any thoughts appreciated.  Also, if you need further
> clarification on any of the above, feel free to ask.
>
> Thanks for any input.
>
> Jeff
> ___
> 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] looking for tools to map SQLite to C++ objects

2008-09-25 Thread Jeffrey Becker
>From what I remember of CodeSmith I dont see any reason why you
couldnt simply write templates that put out C++ rather than C# or
VB.Net. If push really came to shove and you didn't have an
excessively complicated DB you could write one in vbscript pretty
easily.

On Thu, Sep 25, 2008 at 2:49 PM, Sam Carleton
<[EMAIL PROTECTED]> wrote:
> There seem to be a ton of tools out there now that will look at a
> table in a database and generate code to do selects, insert, updated,
> and deletes for languages like C#, VB.Net, Java, JavaScript, PHP  and
> other higher level languages.  I am looking for something like that
> for C++.  Here are some that I know about:
>
> http://www.sqlmaestro.com/products/sqlite/phpgenerator
> http://www.mygenerationsoftware.com
> http://www.codesmithtools.com
>
> At this point in time, I don't care about platform, I simply want to
> find a tool that will help me out.
>
> Sam
> ___
> 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] looking for tools to map SQLite to C++ objects

2008-09-25 Thread Jeffrey Becker
What do you mean by "Mapping".  There are several api wrappers for
sqlite written in C++.  See
http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers



On Thu, Sep 25, 2008 at 1:31 PM, Sherief N. Farouk <[EMAIL PROTECTED]> wrote:
>> Does anyone know of any tools that are either open source or reasonably
>> priced (less then 600 USD) for mapping SQLite to C++?
>>
>> Sam
>
> [Sherief N. Farouk]
>
> No tools that I know of, but I have a small bunch of classes that I wrote to
> wrap sqlite3 objects, in order to make some things easier (like not having
> to remember to close() the DB object at every single return statement), all
> of which are convertible to the underlying sqlite types for interoperability
> with the native API. If someone's interested, I could arrange for a BSD
> release.
>
> - Sherief
>
> ___
> 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] Network concurrency question

2008-09-25 Thread Jeffrey Becker
The long and short of it is that SQLite uses the os File-Locking APIs
for concurrency.  Network File systems vary wildly in the reliability
of their implementations of these APIs.  If you already have a server
application why not simply force all database access through that?

On Thu, Sep 25, 2008 at 12:04 PM, Gerry Snyder <[EMAIL PROTECTED]> wrote:
> Darrell Lee wrote:
>>>
>>>
>> Here is my situation: the maximum number of clients that would be
>> reading the SQLite database is 6, of that 6 the maximum number of
>> clients that might be trying to write to the SQLite db is 3.   In you
>> guys experience,  on a scale of 1-10 , 10 being the most likely to
>> happen, what are the chances of the database becoming corrupt if it is
>> on a network share?
>>
>> Thanks,
>>
> If your data are critical isn't a 1 unacceptable? And if not, isn't 10 okay?
> ___
> 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] Specifing which index to use. Was: Performance/bug in multikey 'group by' in 3.6.2

2008-09-22 Thread Jeffrey Becker
First, I have to agree that this is very 'un-rdbmsish'.  I understand
that sometimes the programmer really does know better than the DB
engine which indexes it should use.  However, the RDBMS is
fundamentally an abstraction layer.  I think the policy other dbms
systems have of making these things hints rather than requirements is
a good one because it still allows the query optimizer to make the
best choice when the hints its given become incorrect. I'd prefer to
see some sort of programmatic method of doing this.  The method I'd
find ideal would be to have some sort of sqlite_suggest_* api which
would allow a user to apply hints to an already prepared statement.  I
forget if sqlite_stmt keeps a copy of the sql so I may well be
suggesting the impossible here.  The api would reinforce the
non-standard nature of the action while keeping the sql dialect free
of non-standard sql.

On Mon, Sep 22, 2008 at 10:07 AM, D. Richard Hipp <[EMAIL PROTECTED]> wrote:
> I am reluctant to add to SQLite the ability to explicitly specify the
> index for a query.  I agree with Alex Scotti that the whole idea seems
> very un-RDBMS like.
>
> On the other hand, just because a feature is there does not mean
> people have to use it.  The documentation can make it clear that the
> feature should be used rarely and only be experts.  We can make
> arrangements to omit the feature at compile-time (or perhaps to
> require a special compile-time option to enable it.)  And, there
> really do seem to be a few rare cases where explicitly naming the
> index is helpfull.
>
> There seems to be no standard SQL way of providing hints to the query
> optimizer for which index to use.   Every SQL database engine does it
> differently.  The MySQL approach is the simplest by far.  But even it
> is more complex than is really needed.  I propose syntax for SQLite as
> follows:
>
>  SELECT * FROM tablex INDEX BY indexy WHERE ... ORDER BY ...;
>  SELECT * FROM tablex INDEX BY ROWID WHERE ... ORDER BY ...;
>  SELECT * FROM tablex NOT INDEXED WHERE ... ORDER BY ...;
>
> I further propose that if the specified index cannot be used, the
> sqlite3_prepare_v2() call will fail with an error.  In other words,
> the new syntax is a requirement, not a hint.
>
> Comments?  Objections?
>
> D. Richard Hipp
> [EMAIL PROTECTED]
>
>
>
> ___
> 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] Date/Time Pains

2008-09-22 Thread Jeffrey Becker
Instead of doing julianday('now') why not put a text-based timestamp
into whatever object you're queueing up and pass that into the
julianday() function to convert it to a real.

On Mon, Sep 22, 2008 at 9:51 AM, P Kishor <[EMAIL PROTECTED]> wrote:
> On 9/22/08, jason weaver <[EMAIL PROTECTED]> wrote:
>> "jason weaver" <[EMAIL PROTECTED]> wrote:
>>  news:[EMAIL PROTECTED]
>>  >> However, due to database locking issues, I need to do a bunch of
>>  >> inserts in one transaction or batch.  Thus, I store them in a simple
>>  >> queue.  Therefore, the julianday('now') won't work because all of my
>>  >> batch inserts will have the same date and time.  And that doesn't
>>  >> work very well. ;)
>>
>>
>> >From: "Igor Tandetnik" <[EMAIL PROTECTED]>
>>
>> >You will have to deal with this in any case. E.g. on Windows the system
>>  >timer resolution is 15ms by default. You can insert quite a few records
>>  >in 15 ms.
>>  >It is unrealistic to expect that every record could be inserted with a
>>  >unique timestamp. Find some other way to ensure uniqueness (e.g. just
>>  >assign a sequential number to each).
>>
>>
>>
>> Thanks for your response.  However, my batch inserts and the uniqueness of
>>  my timestamps aren't the issue.  I guess I didn't explain my situation well
>>  enough.  Let me try again.
>>
>>
>>
>>  I take readings every X seconds which gives me plenty of uniqueness for each
>>  reading.  I save the SQL statements and then insert them in small batches.
>>  However, from reading this newsgroup I've learned that the correct way to
>>  put dates into SQLite is as I described before:
>>
>>
>>  - create table my_table(date_stuff real);
>>
>>
>> - insert into my_table values(julianday('now'));
>>
>>
>> In my batch loop, I can't use julianday("now") - I need the timestamp
>>  to reflect when I took the reading.
>>
>>
>>
>>  If the right way to put datetime in the dbase is the julianday('now')
>>  format, I need to be able to create and capture that format in python.
>>
>>
>>
>>  What is the julianday("now") equivalent in python?  I can't find a simple,
>>  straight-forward answer to this question.
>
>
> I have no idea what the julianday("now") equivalent in Python is, but
> why not let SQLite do the work? Here is how I would do it with Perl...
> (mix of pseudo-code and Perl ahead)
>
> # prepare statement
> $sth = $dbh->prepare(qq{
>  INSERT INTO table (somecol, timestamp)
>  VALUES (?, julianday("now"))
> });
>
> open transaction...
>
> # loop through your readings
> $sth->execute($somecol);
>
> end transaction...
>
> $dbh->commit;
>
> or throw error...
>
> The above works for me.
>
> Keep in mind though... Igor's caveat about not having enough
> resolution for timing might apply. So, you might have to use some kind
> of high resolution timer. CPAN has one us Perl aficionados...
> conveniently, it is called Time::HiRes
> 
>
> I am sure you have one for your Python world as well. If you decide to
> use something like that, then you will have to modify your code
> accordingly.
>
>>
>>
>>
>>  Thank you,
>>
>>  Chris
>>
>> ___
>>  sqlite-users mailing list
>>  sqlite-users@sqlite.org
>>  http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
> --
> Puneet Kishor http://punkish.eidesis.org/
> Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
> Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/
> ___
> 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] Looong sql queries (>9 seconds)

2008-09-18 Thread Jeffrey Becker
Actually I think you can drop the index on just rate.

On Thu, Sep 18, 2008 at 3:12 PM, John Stanton <[EMAIL PROTECTED]> wrote:
> I would get rid of your separate columns for day, month and year and use
> the Sqlite date format and use an index on it.
>
> chris wrote:
>> I'm at a loss and need some guidance.  My queries are taking way longer than 
>> I can use but I'm not sure what steps to take next.
>>
>> I'm using SQLite 3.6.2 on a small reasonable powerful embedded platform with 
>> 256M ram.  I'm using Python to access the database.  I've created a very 
>> simple database file with two tables:
>>
>> CREATE table IF NOT EXISTS settings( id INTEGER PRIMARY KEY AUTOINCREMENT, 
>> year INTEGER, month INTEGER, day INTEGER, hour INTEGER, minutes INTEGER, 
>> seconds INTEGER, curtemp TEXT, relhum TEXT, watts TEXT, rate TEXT )
>>
>> CREATE table IF NOT EXISTS setpoints( id INTEGER PRIMARY KEY AUTOINCREMENT, 
>> year INTEGER, month INTEGER, day INTEGER, hour INTEGER, minutes INTEGER, 
>> seconds INTEGER, point TEXT )
>>
>> The SETTINGS table has about 95,000 entries.
>>
>> This single query takes between 9 and 12 seconds to run:
>> SELECT rate, day, month, rate * SUM(watts) / 12000 AS TotalBillAtRate FROM 
>> settings WHERE (day <= 18) AND (month = 9) AND (year = 2008) GROUP BY rate
>>
>> Is there something I can do to get that time down substantially?
>>
>> Thank you for any suggestions.
>>
>>
>>
>> ___
>> 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] Looong sql queries (>9 seconds)

2008-09-18 Thread Jeffrey Becker
Try creating an index on settings(rate)

On Thu, Sep 18, 2008 at 12:46 PM, chris <[EMAIL PROTECTED]> wrote:
> I'm at a loss and need some guidance.  My queries are taking way longer than 
> I can use but I'm not sure what steps to take next.
>
> I'm using SQLite 3.6.2 on a small reasonable powerful embedded platform with 
> 256M ram.  I'm using Python to access the database.  I've created a very 
> simple database file with two tables:
>
> CREATE table IF NOT EXISTS settings( id INTEGER PRIMARY KEY AUTOINCREMENT, 
> year INTEGER, month INTEGER, day INTEGER, hour INTEGER, minutes INTEGER, 
> seconds INTEGER, curtemp TEXT, relhum TEXT, watts TEXT, rate TEXT )
>
> CREATE table IF NOT EXISTS setpoints( id INTEGER PRIMARY KEY AUTOINCREMENT, 
> year INTEGER, month INTEGER, day INTEGER, hour INTEGER, minutes INTEGER, 
> seconds INTEGER, point TEXT )
>
> The SETTINGS table has about 95,000 entries.
>
> This single query takes between 9 and 12 seconds to run:
> SELECT rate, day, month, rate * SUM(watts) / 12000 AS TotalBillAtRate FROM 
> settings WHERE (day <= 18) AND (month = 9) AND (year = 2008) GROUP BY rate
>
> Is there something I can do to get that time down substantially?
>
> Thank you for any suggestions.
>
>
>
> ___
> 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] Vista frustrations

2008-09-17 Thread Jeffrey Becker
I am running vista my personal pc.  I'll try to take a look into how
not having this flag affects performance when I get home.

On Wed, Sep 17, 2008 at 5:29 PM, Robert Simpson <[EMAIL PROTECTED]> wrote:
> Ideally, at least on non-CE platforms, I'd like see SQLite not give the OS
> any hints about caching.  However, I'm not sure what kind of performance hit
> (if any) that would have on Windows.  It's already been proven that
> providing the hint on WinCE is beneficial.
>
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of Roger Binns
> Sent: Wednesday, September 17, 2008 2:02 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Vista frustrations
>
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> Robert Simpson wrote:
>> The purpose of a cache is to improve performance and responsiveness.  Any
>> cache that uses all physical memory, forces all other apps to the paging
>> file
>
> All current operating systems do this, using heuristics to determine how
> much of each running application to leave in memory while using "spare"
> memory for filesystem cache and dynamically changing allocation based on
> demand.  Sometimes they mess up to the detriment of idle applications.
> However that is not the bit I am talking about as it is not controlled
> by flags when opening files and instead is part of the core operating
> system code.  (You often see this issue when copying files larger than RAM).
>
> What is under discussion is how the operating system is using the cache
> that it does decide to allocate for SQLite.  If you tell it that a file
> is sequential access then that means that read ahead is good and that
> data already read can be discarded.  If you tell it that a file is
> random access then read ahead is bad (it has the disk occupied when the
> next random request comes in) and already read data should be kept.
> SQLite does not know and should be leaving it up to the operating
> system.  Your tests prove that when that is done on Vista, performance
> of SQLite is better and other applications are less adversely affected.
>
>> The real frustration is that this seems to be a rather obvious bug in
> Vista,
>> and definitely not SQLite's responsibility.
>
> This is conflating two issues.  One is the tradeoff between RAM usage
> for cache vs idle applications.  You can argue that is a bug, or more
> accurately there are circumstances under which the tradeoff picks wrong
> values, and is very hard to get right.  Example underlying details are
> at http://blogs.technet.com/markrussinovich/archive/2008/02/04/2826167.aspx
>
> The second issue is the performance differences when random access,
> sequential and no flags are given while opening the file and the
> resulting performance.  In this case a file is accessed mostly
> sequentially but the random access flag is given.  Performance was worse
> than letting the operating system use its own heuristics.  That is
> hardly surprising or a bug.
>
> Roger
>
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.6 (GNU/Linux)
>
> iD8DBQFI0XBYmOOfHg372QQRAmKfAJ4pWVfZ8LS2ET+Y55FGT3Am9wCfZg4e
> McaZrcLUXQJhU7i1Gw5+cRk=
> =CQg5
> -END PGP SIGNATURE-
> ___
> 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] Vista frustrations

2008-09-17 Thread Jeffrey Becker
SQLite in general and the .Net provider in particular are most often
shipped as components of other applications.  I dont think having
developers tell their end users to disable superfetch is a viable
solution.  As much as I hate to propose this maybe a runtime check is
in order to see what the OS version is and not use the flag where it's
known to be problematic.

On Wed, Sep 17, 2008 at 12:14 PM, Virgilio Alexandre Fornazin
<[EMAIL PROTECTED]> wrote:
> Could not this bug be related with Vista feature called 'Superfetch' ?
> It tries to keep in memory the most accessed files for user, avoiding
> disk for read access.
>
> If you disable (or stop) this service, the problem remains or not ?
>
>
>
>
> Virgilio Alexandre Fornazin
> High performance and realtime systems development
>
> Rua Brigadeiro Vicente Faria Lima, 268
> Bela VistaLeme-SPCEP 13611-485
> Phone: +55 19 3571-5573
> Cell: +55 19 8111-4053
> +55 11 8357 1491
> Mail: [EMAIL PROTECTED]
> Web: http://www.fornazinconsultoria.com.br
>
>
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of Jay A. Kreibich
> Sent: quarta-feira, 17 de setembro de 2008 13:01
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Vista frustrations
>
> On Wed, Sep 17, 2008 at 01:17:51AM -0700, Roger Binns scratched on the wall:
>> Robert Simpson wrote:
>> > To me this seems like an obvious bug in Vista,
>>
>> Actually I'd argue that it is behaving as designed.
>
>  You could argue it is behaving as designed, but I'd still argue it is
>  behaving poorly.
>
>  Further, if a system component who's sole purpose is to increase
>  performance-- as all cache systems are-- has the overall effect of
>  decreasing performance, not only of the process it is trying to speed
>  up, but of the whole system, it is pretty easy to argue that's a serious
>  functional bug.
>
>
>  Given the speed of most storage systems, filesystem cache management
>  is an important component of overall system performance.  However, if
>  the cache system is grabbing so much physical memory (and, apparently,
>  refusing to let go of it) that processes are forced to aggressively
>  page and the net result is a massive performance loss, then something
>  isn't right.
>
>  As with so many things, cache management (and, indeed, the whole
>  concept of caches) tends to be a huge web of compromises.  It is
>  extremely difficult, if not impossible, to cover all cases.  But
>  these things are not exactly new, and it should be easy enough to
>  never get in a situation where things are actually made worse--
>  especially that they're not made worse for the whole system.
>
>> Generally
>> filesystem code will try to detect what is going on under the hood.  In
>> particular if it looks like you are doing sequential access(*) then they
>> will start doing read ahead, whereas read ahead is a waste for random
>> access.
>
>  Not to get into a whole argument about cache strategies, but this
>  often not true.  If we assume free memory isn't a big concern,
>  when a process opens a file for random-access we can either
>  read-ahead the whole thing or we can read blocks here and there until
>  (if the process touches the majority of the file) we have the whole
>  thing in memory.  Both systems, in the end, will result in the same
>  memory usage.
>
>  However, if I'm going to be doing random access on a file of moderate
>  or smaller size, it is much cheaper for the OS to just suck the whole
>  thing into memory via one bulk read operation than it is to grab it
>  piecemeal.
>
>  The whole trick is defining "moderate" both in terms of first-return
>  read times (time to return the block the process actually asked for,
>  which might not be the first block pulled off disk) vs how likely the
>  process is to touch the majority of file blocks (something that is
>  somewhat less likely as the file gets bigger).
>
>  As the file gets larger, there is also the real-world issue of how
>  much RAM the system has, and how much of it is actually in-use with
>  process and OS pages.  This is true of both sequential AND random
>  access, although memory usage is generally easier to control in
>  sequential patterns.
>
>  This is where Vista appears to be breaking down and making very poor
>  decisions.  It seems to be giving cache pages more priority than
>  process and OS system pages, and generally that should never happen.
>  If we're correctly understanding what is going on, Vista might very
>  well be paging out SQLite's internal page cache to fit a few extra file
>  blocks in RAM.  How much sense does that make?
>
>> By using the sequential or random flags you are explicitly
>> telling the filesystem to ignore its heuristics and do as you say only.
>
>  Even if that's true (most APIs present the flags as "hints" not
>  absolute truths), the worst an incorrect flag should do is hurt the
>  file access performance of the process that provided the hint.  Even
>  

Re: [sqlite] Error A0A

2008-09-08 Thread Jeffrey Becker
Are you using the native api or one of the wrappers?

On Mon, Sep 8, 2008 at 9:58 AM, Dan <[EMAIL PROTECTED]> wrote:
>
> On Sep 8, 2008, at 1:47 PM, [EMAIL PROTECTED] wrote:
>
>>
>> I'm using multiple connections to sqlite database from more threads
>> in my application. Few customers reports sqlite error in windows
>> vista which I cannot get in my system.
>> Error code is 0xA0A (I haven't turned on extended error codes).
>> This error number gives no sense to me because it is not defined in
>> extended error codes too.
>> Most of times it occurs when I'm trying to run "Commit" command,
>> sometimes while SELECT.
>> Anybady knows what can be the problem?
>
> 0xA0A is SQLITE_IOERR_DELETE. Indicates a delete operation has
> failed. Could be something to do with anti-virus software preventing
> a journal file from being deleted.
>
> Dan.
>
>
>
> ___
> 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] Writing an image to the database as a BLOB

2008-08-29 Thread Jeffrey Becker
Are you able to load and save the bitmap to a file?

On Fri, Aug 29, 2008 at 2:13 PM, Jared Miller <[EMAIL PROTECTED]> wrote:
> Hello,
>
> I am having trouble figuring out how to successfully write an image to the 
> SQLite database as a Blob, using C++.
>
> I have an HBITMAP that I would like to be able to store to and retrieve from 
> the DB. If I understand what I have read correctly, I am supposed to write 
> out the actual byte data to the DB. Here is what I have done so far.
>
> I pass in pbyBitmap as the bmBytes parameter to this function:
> ImportPageImage(CPage* pPage, BYTE* bmBytes, DWORD bmSize), which calls the 
> code below. . .
>
> //prepare query
> static const WCHAR tblInsertBlob[] = _T("Insert into [tblBlob] ([Data]) 
> values (?)");
> SQL_HANDLE blobHandle = m_pSqliteDB->PrepareQuery16(tblInsertBlob); //calls 
> sqlite3_prepare16()
>
> //bind blob
> m_pSqliteDB->BindBlob(blobHandle, 1, (void *)bmBytes, bmSize); //calls 
> sqlite3_bind_blob (bmBytes is the pData param)
>
> Then I call StepQuery to execute it, and then I close the query. 
> sqlite3_bind_blob() returns SQLITE_OK when I run it, so it does not seem to 
> be encountering an error there.
>
> Something apparently gets written to the database, but it does not seem to be 
> correct. When I try to retrieve and display my image, it is all black (which 
> is how bitmaps look when there is no data).
>
> I think that the problem is coming from writing the BLOB to the database, but 
> I am not entirely sure. Just in case it is getting written properly and I am 
> not reading it from the database correctly, I will show you how I pulled it 
> from the DB.
>
> //prepare blob
> sqlite3_blob* pBlob = NULL;
> sqlite3_blob_open(m_sqliteDB, "main", "tblBlob", "Data", iBlobID, FALSE, 
> );
>
> BLOB_HANDLE hBlob = pBlob;
> int size = sqlite3_blob_bytes(hBlob); //works correctly, returns 998058
> BYTE* pBuffer = g_MemMgr.AllocDataBuffer(size);
>
> sqlite3_blob_read(hBlob, pBuffer, size, 0); //returns SQLITE_OK
> sqlite3_blob_close(hBlob);
>
> I then try to make a bitmap out of the bytes in pBuffer, but when I do, it 
> turns out all black (as I mentioned earlier).
>
> Do I have the concept correct? And if so, what part of my current code should 
> I change to be able to use my DB to store image data?
>
> Thanks a lot.
>
>
>
> ___
> 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] Get transaction number

2008-08-26 Thread Jeffrey Becker
I dont believe sqlite supports named transactions.  As such there
wouldnt be any identifier to get.

On Tue, Aug 26, 2008 at 4:47 AM, Alexey Pechnikov
<[EMAIL PROTECTED]> wrote:
> Hello!
>
> How can I get transaction identifier or number (my programm lang is tcl)?
>
> Best regards, Alexey.
> ___
> 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] Reducing SQLite Memory footprint(!)

2008-08-22 Thread Jeffrey Becker
Ok, I couldnt find the script but heres something started.

On Fri, Aug 22, 2008 at 6:02 PM, Brown, Daniel <[EMAIL PROTECTED]> wrote:
> That script would be great :)
>
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of Jeffrey Becker
> Sent: Friday, August 22, 2008 1:31 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Reducing SQLite Memory footprint(!)
>
> At one point I wrote some vbscript to generate a table declaration and
> insert statements for a csv.  I might be able to dig it up if you dont
> mind vbscript.
>
> On Fri, Aug 22, 2008 at 1:58 PM, Brown, Daniel <[EMAIL PROTECTED]> wrote:
>> I just ran Dennis's test databases through the test application and
>> we're getting similar results:
>>1k Pages (17.4 MB) used 18102 KB High 20416 KB
>>4k Pages (12.2 MB) used 18102 KB, High 26416 KB (not sure why
>> the high is higher?)
>> My test database however with the same test application produces the
>> following:
>>1k Pages (7.46 MB) used 22735 KB, High 25138 KB.
>>
>> So it looks my issue could be data related if my test database going
>> through the same app is coming out so large, Dennis's database is
>> expanding to about 101.6% of its original size but mine is expanding
> to
>> 297.6% of its original size.  This begs the question is the 3rd party
>> tool (SQLite Analyzer) I'm using to import from an excel file causing
>> this expansion with bad data type choices?  And is there any other way
>> to import table structure and contents from xls (or csv) to sqlite?
>>
>> -Original Message-
>> From: [EMAIL PROTECTED]
>> [mailto:[EMAIL PROTECTED] On Behalf Of Dennis Cote
>> Sent: Friday, August 22, 2008 7:10 AM
>> To: General Discussion of SQLite Database
>> Subject: Re: [sqlite] Reducing SQLite Memory footprint(!)
>>
>> Brown, Daniel wrote:
>>> 2. And the other thing to try would be if anyone has a fairly meaty
>> test
>>> database they don't mind sharing that I could fling at my test
>>> application to try and rule out the data?
>>>
>>
>> Daniel,
>>
>> I can send you copies of the databases I am using for my testing, both
>> the version with the 1K page size (17.4 MB) and the one with the 4K
> page
>>
>> size (12.2 MB).
>>
>> Where would you like me to send them? The zipped versions are each
> about
>>
>> 1.3 MB in size.
>>
>> Dennis Cote
>> ___
>> 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
>
Class FieldInfo
 
Public Name

private m_TextCount
private m_BlobCount
private m_IntCount
private m_FloatCount
private m_DateCount

Public Property Get DataType
dim t, c


t = "NULL"
c = 0

if(m_DateCount > c) then
t = "Date"
c = m_DateCount
end if

if(m_BlobCount > c) then
t = "Binary"
c = m_BlobCount 
end if 

if(m_FloatCount > c) then
t = "Real"
c = m_FloatCount
end if  

if(m_IntCount > c) then
t = "Integer"
c = m_IntCount  
end if 

if(m_TextCount > c) then
t = "Text"
c = m_TextCount 
end if

if count = 0 then
t = "Text"
end if

DataType = t
End Property


Private Sub Class_Initialize
m_TextCount = 0
m_BlobCount = 0
m_IntCount = 0
m_FloatCount = 0
m_DateCount = 0
   

Re: [sqlite] Reducing SQLite Memory footprint(!)

2008-08-22 Thread Jeffrey Becker
At one point I wrote some vbscript to generate a table declaration and
insert statements for a csv.  I might be able to dig it up if you dont
mind vbscript.

On Fri, Aug 22, 2008 at 1:58 PM, Brown, Daniel <[EMAIL PROTECTED]> wrote:
> I just ran Dennis's test databases through the test application and
> we're getting similar results:
>1k Pages (17.4 MB) used 18102 KB High 20416 KB
>4k Pages (12.2 MB) used 18102 KB, High 26416 KB (not sure why
> the high is higher?)
> My test database however with the same test application produces the
> following:
>1k Pages (7.46 MB) used 22735 KB, High 25138 KB.
>
> So it looks my issue could be data related if my test database going
> through the same app is coming out so large, Dennis's database is
> expanding to about 101.6% of its original size but mine is expanding to
> 297.6% of its original size.  This begs the question is the 3rd party
> tool (SQLite Analyzer) I'm using to import from an excel file causing
> this expansion with bad data type choices?  And is there any other way
> to import table structure and contents from xls (or csv) to sqlite?
>
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of Dennis Cote
> Sent: Friday, August 22, 2008 7:10 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Reducing SQLite Memory footprint(!)
>
> Brown, Daniel wrote:
>> 2. And the other thing to try would be if anyone has a fairly meaty
> test
>> database they don't mind sharing that I could fling at my test
>> application to try and rule out the data?
>>
>
> Daniel,
>
> I can send you copies of the databases I am using for my testing, both
> the version with the 1K page size (17.4 MB) and the one with the 4K page
>
> size (12.2 MB).
>
> Where would you like me to send them? The zipped versions are each about
>
> 1.3 MB in size.
>
> Dennis Cote
> ___
> 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] Reducing SQLite Memory footprint(!)

2008-08-20 Thread Jeffrey Becker
More over, the memory usage reported by process explorer is subject to
the vagrancies of the windows memory allocator.  Generally the memory
usage you see reported in tools is quite a bit higher than the actual
memory usage of your application.  First off windows manages memory in
fixed size chunks so if you ask for X bytes of memory, windows
allocates n contiguous chunks such that n*chunk_size >= x.  However
windows just reports ~ # of allocated chunks * chunk_size as your
memory usage (if you use the right api).  More over most applications
simply report the total size of an application's "working set" which
includes lots of pages of memory that are potentially shared across
many applications.

On Wed, Aug 20, 2008 at 2:00 PM, D. Richard Hipp <[EMAIL PROTECTED]> wrote:
>
> On Aug 20, 2008, at 1:53 PM, Brown, Daniel wrote:
>
>> Looking in process explorer on XP after the disc database detached
>> should a memory size change of 28 MB of RAM in the test application, I
>> assumed this was the size of the database in memory.
>
>
> That would be the peak memory usage by the application.  It is not at
> all clear to me that SQLite was using all 28 MB.  What does
> sqlite3_memory_highwater() tell you?
>
>
> D. Richard Hipp
> [EMAIL PROTECTED]
>
>
>
> ___
> 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] vfs implementation question

2008-08-19 Thread Jeffrey Becker
Cool.  I'm actually working on extending Robert Simson's ADO.Net
provider to allow vfs implementations to be written in managed code.
I hope to get at least two custom vfs implementations into his
codebase, a custom memory backed vfs and a true client-server vfs.

On Tue, Aug 19, 2008 at 5:45 PM, Richard Klein
<[EMAIL PROTECTED]> wrote:
> D. Richard Hipp wrote:
>> On Aug 19, 2008, at 9:02 AM, Jeffrey Becker wrote:
>>
>>
>>> Should the xLock member of sqlite3_io_methods object block until the
>>> lock can be acquired?
>>
>> xLock does not block on any of the built-in VFSes.  But if you want to
>> make your own custom VFS that blocks on locks, I don't  know of any
>> reason why that wouldn't work.
> I wrote a custom VFS that blocks on locks, and it works just fine.
>
> I also implemented a subset of the five locking levels of SQLite:
> just UNLOCKED and EXCLUSIVE.  This means that as soon as a transaction
> asks for a SHARED lock, it actually gets an EXCLUSIVE lock, which
> locks out all other transactions until the first one commits.
>
> This works fine in an embedded application where there are only a
> few threads, whose transactions execute quickly.
>
> - Richard Klein
>
> ___
> 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] vfs implementation question

2008-08-19 Thread Jeffrey Becker
So in the cases where the lock cant be acquired, the built in vfs
implementations return SQLITE_BUSY?

On Tue, Aug 19, 2008 at 9:16 AM, D. Richard Hipp <[EMAIL PROTECTED]> wrote:
>
> On Aug 19, 2008, at 9:02 AM, Jeffrey Becker wrote:
>
>> Should the xLock member of sqlite3_io_methods object block until the
>> lock can be acquired?
>
> xLock does not block on any of the built-in VFSes.  But if you want to
> make your own custom VFS that blocks on locks, I don't  know of any
> reason why that wouldn't work.
>
> D. Richard Hipp
> [EMAIL PROTECTED]
>
>
>
> ___
> 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] vfs implementation question

2008-08-19 Thread Jeffrey Becker
Should the xLock member of sqlite3_io_methods object block until the
lock can be acquired?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Using SQLite as an application file format (C++)

2008-08-18 Thread Jeffrey Becker
All you need to do is pass the string ":memory:" to sqlite3_openxxx
and it will open a memory-backed database.  That said I think your
first order of business should be to try and define a good abstraction
around the whole thing. While my C++ is a little rusty heres some C#
pseudo code to get you started:
public abstract class DbObject
{
// gets /sets the current versionid of this object. set = undo
protected virtual long VersionID
{
get{...}
set {...}
}

// the 'instanceid' of this object
protected long ObjectID{get;set;}

protected object getValue(string propertyName);
protected void setValue(string propertyName, object value);
}

On Mon, Aug 18, 2008 at 7:39 PM, Darren Landrum
<[EMAIL PROTECTED]> wrote:
> Jeffrey Becker wrote:
>> As a solution I suggest you come up
>> with two slightly different schemas one with change-tracking and one
>> without.  The disk file will be saved without change-tracking.  When
>> you load a file, first create a connection to a :memory: database, set
>> up your schema with change tracking, attach your file and pull the
>> data from the file into your main database.  When you save, attach the
>> file again and write the data out to it from main. While it may sound
>> complex, a healthy dose of good object orientation can really help.
>
> That's what I wanted to do to begin with, but I got challenged on it.
>
> I saw an article on how to do this in tcl, but I'll have to see if I can
> figure out how to adapt that to C++. I'm assuming that I end up with a
> pointer to the in-memory database when I'm done?
>
> As for the change tracking, I still haven't decided exactly what I want
> to do concerning that. I think you're probably right, that storing it in
> the file in disk will just bloat the files in the long run. Most of the
> time, we just use undo two or three times to backtrack from a mistake.
>
> -- Darren
>
> ___
> 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] Using SQLite as an application file format (C++)

2008-08-18 Thread Jeffrey Becker
Personally I'd skip the 'auto-saving' feature.  Lots of users are used
to being able to mess around with whatever safe in the knowledge that
when they quit their changes will go away.  More over, unless
cross-session undo is a stated goal it provides little value while
potentially balooning file-size for long-lived files and possibly
revealing embarasing information.  As a solution I suggest you come up
with two slightly different schemas one with change-tracking and one
without.  The disk file will be saved without change-tracking.  When
you load a file, first create a connection to a :memory: database, set
up your schema with change tracking, attach your file and pull the
data from the file into your main database.  When you save, attach the
file again and write the data out to it from main. While it may sound
complex, a healthy dose of good object orientation can really help.

On Mon, Aug 18, 2008 at 6:35 PM, Darren Landrum
<[EMAIL PROTECTED]> wrote:
> D. Richard Hipp wrote:
>> Have you actually run experiments to see if this is the case, or are
>> you just guessing?  My guess would be the combination of the OS disk
>> cache and SQLite's internal page cache will make actual disk I/O
>> relatively rare, even for an on-disk database.
>
> Okay, I'm guessing, but it's not unreasonable to want to miminize disk
> i/o in our application, right?
>
> That being said, I like the idea of going without a save button (we'd
> still probably have a "save as") and saving everything straight to the
> disk file, as that would make it easy for sound designers to "get lost"
> in the application and not have to worry about losing anything they do.
>
> I'm still concerned about parameters which are designed to be modified
> in real time during performance (in the "sitting down and playing the
> keys" sense), but it's only samplers, which stream a huge amount of data
> from disk as they get played, that might be affected.
>
> Okay, I'll go with that plan for now, and if it turns out to be an
> issue, then I'll look into pulling databases into memory. Thank you for
> your help.
>
> -- Darren
> ___
> 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] implementing a vfs

2008-08-15 Thread Jeffrey Becker
Are pointers sqlite3_file structures considered 'invalid' after Close
has been called on them?  If not, at what point can I free memory
associated with them?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Proposed removal of (mis-)feature

2008-08-07 Thread Jeffrey Becker
On Thu, Aug 7, 2008 at 2:13 PM, Kees Nuyt <[EMAIL PROTECTED]> wrote:
>
> On Thu, 7 Aug 2008 13:26:07 -0400, drh wrote:
>
> [snip]
>
>>So I'm giving some thought to removing feature (3) above and
>>disallowing double-quoted string literals.  My concern is, though,
>>that this might break many existing applications.
>>
>>What opinion do you, gentle users, have of this matter?
>
> I agree (3) should be removed. If it would break any of my
> applications (which I doubt), I'll gladly repair it, because
> I would have done a bad job, not you.
> --
>  (  Kees Nuyt
>  )
> c[_]
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

I cant count how many times I've run into this because I mis-typed a
double quoted table name and had it interpreted as a string.  I vote
for removal because it 1) creates confusion about the right way to do
stuff and 2) Creates error messages which are utterly meaningless for
debugging.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Creating Indexes

2008-08-06 Thread Jeffrey Becker
On Wed, Aug 6, 2008 at 4:10 PM, Kees Nuyt <[EMAIL PROTECTED]> wrote:
> On Tue, 05 Aug 2008 17:22:05 -0500, you wrote:
>
>>Jeffrey Becker wrote:
>>> I have a table 'SiteMap' defined as :
>>>
>>> Create Table SiteMap
>>> (
>>>  NodeID blob not null PRIMARY KEY,
>>>  Title text NOT NULL UNIQUE,
>>>  Url text NOT NULL
>>> );
>>>
>>> I'd like to index on the node's parent value as defined by the
>>> expression ancestor(NodeID,1).  'ancestor' being a user defined
>>> function.
>>> However sqlite doesnt permit indicies on expressions, only columns.
>>> Why is this? More importantly what's my best bet on achieveing
>>> something similar?
>>
>>Create Table SiteMap
>>(
>>   NodeID blob not null PRIMARY KEY,
>>   ParentID blob not null,
>>   Title text NOT NULL UNIQUE,
>>   Url text NOT NULL
>>);
>>
>>Then index on (ParentID, NodeID) or something like that.
>>
>>-Steve
>
> Additionally: NodeID and ParentID shouldn't be blobs.
> Integer is the most suitable type for IDs.
>
> http://article.gmane.org/gmane.comp.db.sqlite.general/17286/match=managing+trees+database
> and
> http://www.sitepoint.com/article/hierarchical-data-database
> may help on maintaining trees in a relational database.
>
> Or search the net on "Joe Celko" and "nested sets".
> --
>  (  Kees Nuyt
>  )
> c[_]

The blobs I'm inserting are actually a binary representation designed
_specifically_ to index properly.  So yes they should be blobs.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


[sqlite] Creating Indexes

2008-08-05 Thread Jeffrey Becker
I have a table 'SiteMap' defined as :

Create Table SiteMap
(
NodeID blob not null PRIMARY KEY,
Title text NOT NULL UNIQUE,
Url text NOT NULL
);

I'd like to index on the node's parent value as defined by the
expression ancestor(NodeID,1).  'ancestor' being a user defined
function.
However sqlite doesnt permit indicies on expressions, only columns.
Why is this? More importantly what's my best bet on achieveing
something similar?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users