Re: [sqlite] setting the timezone on windows xp

2008-10-26 Thread Jay Sprenkle
Thanks Doug.

On Sat, Oct 25, 2008 at 11:48 AM, Doug <[EMAIL PROTECTED]> wrote:

> Hi Jay --
>
> I used to have a problem like this a few years back.  I don't remember all
> the hows and whys, but my apps call the following at start up and the
> problems are gone:
>
> _tsetlocale(LC_ALL, _T(""));
> _tzset();
>
> HTH
>
> Doug
>
>
> > -Original Message-
> > From: [EMAIL PROTECTED] [mailto:sqlite-users-
> > [EMAIL PROTECTED] On Behalf Of Jay Sprenkle
> > Sent: Saturday, October 25, 2008 10:32 AM
> > To: General Discussion of SQLite Database
> > Subject: [sqlite] setting the timezone on windows xp
> >
> > Hello all,
> > this isn't really an sqlite question but it tangentially touches on it.
> > Please disregard it if it offends.
> >
> > I'm trying to write an atom feed exporter for my sqlite database and I
> > need
> > to export the utc time. My development box, windows xp, insists (both
> > in C
> > and in SQLite) that UTC is 10 hours different than localtime. I've
> > clearly
> > set the timezone to US CST in the control panel. I've also tried
> > setting
> > both the TZ and TIME_ZONE environment variables (CST6CDT). No luck.
> >
> > 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
>



-- 
--
The PixAddixImage Collector suite:
http://groups-beta.google.com/group/pixaddix

SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


[sqlite] setting the timezone on windows xp

2008-10-25 Thread Jay Sprenkle
Hello all,
this isn't really an sqlite question but it tangentially touches on it.
Please disregard it if it offends.

I'm trying to write an atom feed exporter for my sqlite database and I need
to export the utc time. My development box, windows xp, insists (both in C
and in SQLite) that UTC is 10 hours different than localtime. I've clearly
set the timezone to US CST in the control panel. I've also tried setting
both the TZ and TIME_ZONE environment variables (CST6CDT). No luck.

Any suggestions?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Sqlite catalog - datebase with pictures

2008-03-31 Thread Jay Sprenkle
I wrote something similar, the source might be useful to you:
--
The PixAddixImage Collector suite:
http://groups-beta.google.com/group/pixaddix
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] table names from subselects?

2008-03-14 Thread Jay Sprenkle
On Fri, Mar 14, 2008 at 5:53 PM, Dennis Cote <[EMAIL PROTECTED]> wrote:

>
> You are aliasing the wrong thing. Try this instead:
>
>select * from (select user.id as id from user ) where id=1 ;
>
> If you want the subselect result to have a name then you can do this:
>
>select * from
>   (select user.id as id from user ) as sub where sub.id=1;
>
>
Thanks Dennis.
You and Igor really know this package well.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] table names from subselects?

2008-03-14 Thread Jay Sprenkle
On Fri, Mar 14, 2008 at 5:57 PM, Richard Klein <[EMAIL PROTECTED]>
wrote:

>
> >
> >
>
> select * from (select id from user where id = 1);
>

Thanks Richard, but in my particular application I need the where clause
outside the subselect.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] table names from subselects?

2008-03-14 Thread Jay Sprenkle
On Fri, Mar 14, 2008 at 5:48 PM, Igor Tandetnik <[EMAIL PROTECTED]> wrote:

> select * from (select user.id from user ) where "user.id"=1
>
> The resulting column is literally named "user.id", period and all.


Thank you Igor, you are probably the world's smartest sqlite pundit!
Every time I need an answer I search on Google and Igor's name comes up.
Igor to the rescue!

:)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] table names from subselects?

2008-03-14 Thread Jay Sprenkle
On Fri, Mar 14, 2008 at 5:46 PM, Jonas Sandman <[EMAIL PROTECTED]>
wrote:
> Isn't proper SQL
>
>  select * from (select user.id from user ) blah where blah.id=1 ; ?

>From www.sqlite.org, sql syntax, select statement:

sql-statement ::= SELECT [ALL | DISTINCT] result [FROM *table-list*]

*table-list* ::= *table *[join-op table join-args]*

*table *::= table-name [AS alias] |
*( select ) *[AS alias]

As you can see from the sqlite definition language it qualifies as valid
sql.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


[sqlite] table names from subselects?

2008-03-14 Thread Jay Sprenkle
I'm not sure if this counts as a bug or not.

SQLite version 3.5.2
Enter ".help" for instructions
sqlite> select * from (select user.id from user ) ;
0
1
2
3
4
sqlite> select * from (select user.id from user ) where id=1 ;
SQL error: no such column: id
sqlite> select * from (select user.id from user ) where user.id=1 ;
SQL error: no such column: user.id
sqlite> select * from (select user.id from user ) as blah where blah.id=1 ;
SQL error: no such column: blah.id
sqlite> select * from (select user.id from user ) as blah where blah.user.id=1 ;

SQL error: no such column: blah.user.id
sqlite>

Should the result of a subselect have a table name? If not, how do you
reference it?


--
The PixAddixImage Collector suite:
http://groups-beta.google.com/group/pixaddix

SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


[sqlite] table names from subselects?

2008-03-14 Thread Jay Sprenkle
I'm not sure if this counts as a bug or not.

SQLite version 3.5.2
Enter ".help" for instructions
sqlite> select * from (select user.id from user ) ;
0
1
2
3
4
sqlite> select * from (select user.id from user ) where id=1 ;
SQL error: no such column: id
sqlite> select * from (select user.id from user ) where user.id=1 ;
SQL error: no such column: user.id
sqlite> select * from (select user.id from user ) as blah where blah.id=1 ;
SQL error: no such column: blah.id
sqlite> select * from (select user.id from user ) as blah where blah.user.id=1 ;

SQL error: no such column: blah.user.id
sqlite>

Should the result of a subselect have a table name? If not, how do you
reference it?



-- 
--
The PixAddixImage Collector suite:
http://groups-beta.google.com/group/pixaddix

SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Efficiency Question - Value Or Liability for Indexingof This Table?

2008-03-14 Thread Jay Sprenkle
On Fri, Mar 14, 2008 at 5:18 PM, Lee Crain <[EMAIL PROTECTED]> wrote:
>  I was hoping to make an intelligent choice without taking the time to test
>  it one way, then the other because I am probably not going to be given
>  that time.

The intelligent choice would be to begin a search for an employer who
is more rational.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Query problem

2008-01-23 Thread Jay Sprenkle
You need single quotes for text literals.

On Jan 23, 2008 6:15 AM, Yasir Nisar <[EMAIL PROTECTED]> wrote:
>   SELECT * FROM BackupTable,BackupItemTable,BackItUpPathTable WHERE 
> lower(BackItUpPathTable.WinName) GLOB lower("*1[]1.txt*") AND 
> BackupItemTable.BKItemSize > -1 AND BackupTable.BackupNo = 
> BackupItemTable.BackupNo AND BackupItemTable.PathID = 
> BackItUpPathTable.PathID ;
>
--
The PixAddixImage Collector suite:
http://groups-beta.google.com/group/pixaddix

SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] sql optimization question

2008-01-21 Thread Jay Sprenkle
On Jan 20, 2008 9:16 PM, Ken <[EMAIL PROTECTED]> wrote:
> Jay
>
>   I've used a trigger to do this with good success.
>
>   You'll need one trigger per table to cause the delete to cascade through 
> the tree.
>
>   Or if you know that you will always delete from the top level tree"parent" 
> then just one trigger would probably suffice.

I wonder why yours works but Ken's doesn't. In mine one trigger should
do it, but it would have to be recursive.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] sql optimization question

2008-01-21 Thread Jay Sprenkle
> In response to the deletion of (0,null), the trigger fired, deleting
> (1,0), and (2,0).  But the trigger didn't fire again in response to
> either of these subsequent deletions, so (3,1) was not automatically
> deleted.
>
> If anyone knows how to get around this problem, I would like to know.

I wrote mine as a loop that deletes anything that has no valid parent,
but that assumes you can program that into whatever you're using.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] SQLite character comparisons

2008-01-20 Thread Jay Sprenkle
On Jan 20, 2008 10:19 AM, Fowler, Jeff <[EMAIL PROTECTED]> wrote:
>
> Not trying to be antagonistic, but I'm curious to know how many of you agree 
> with Darren's sentiments on this issue.

Sqlite is DRH's project. Voting doesn't come into the picture
anywhere. If you convince him
to fix it then he might, if not you're wasting your time asking if
people agree with you.

>Sure - we can handle this situation by writing more code looking for
spaces everywhere they might occur.

The source for the database engine is provided. It would be more
efficient to change it there.
You could also provide the code back to the community as an option for
others to use.

I'm not trying to be unpleasant, I just believe where you're headed
isn't going to be as valuable as other options.

--
The PixAddixImage Collector suite:
http://groups-beta.google.com/group/pixaddix

SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] Slow query on one machine

2008-01-19 Thread Jay Sprenkle
On Jan 18, 2008 3:32 PM, RB Smissaert <[EMAIL PROTECTED]> wrote:
> The application
> that runs this is exactly the same on both machines. The slow machine is
> actually slightly slower specification wise, but that can't explain the huge
> differences in timings.
>

Have you run spinrite ( a disk diagnostic/maintenance program ) on the
slow machine?

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



[sqlite] sql optimization question

2008-01-18 Thread Jay Sprenkle
I'm deleting a tree of data stored in sqlite and was looking for the
most efficient way to do it.
I thought the best solution was to delete the row and then delete all
the orphaned rows the referenced it in a loop

delete from category where id = 5;

To delete the orphaned rows I repeat one of the following until
nothing more is deleted.
Which of these two forms would run faster?

delete from category where ParentId not in ( select distinct id from category );

or

delete from category A
 left join category B on b.id = a.parent.id
 where b.id is null;

Thanks


-- 
--
The PixAddixImage Collector suite:
http://groups-beta.google.com/group/pixaddix

SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] sqlite3 performace

2008-01-18 Thread Jay Sprenkle
On Jan 17, 2008 3:48 PM, Philip Nick <[EMAIL PROTECTED]> wrote:
> Greetings,
>
> Currently I am using sqlite3 in a multi-process/multi-threaded server
> setting.

opening a file is very slow. You need to move it out of your loop.

> I have noticed my program basically reads the whole database every time the
> query is run. The IO Read bytes increases by 900k for every query we run. We
> also chew a good chunch of the cpu  I have tried keeping the database
> connection open and open/closing for every query. But there was no change.

> if I launch the CLI and run the query it runs instantly and monitoring the
> IO read bytes is see only ~20 bytes of read to execute the query, when my
> code is using over 900k for every call. I have been looking into the CLI
> source to see what is done differently, but was hoping someone on here might
> have some insight.

The operating system will cache files it reads in memory.
If your process reads the file and then you open the CLI
it will still be in memory from before and will not give you
comparable times.

If your process reads the whole database for every call you didn't optimize
your sql. You need to create indexes to optimize your query

--
The PixAddixImage Collector suite:
http://groups-beta.google.com/group/pixaddix

SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] How to compile SQLite with SQLITE_ENABLE_COLUMN_METADATA option under Ubuntu Linux

2008-01-14 Thread Jay Sprenkle
> I found some answer to my previous questions and wrote a short tutorial
> at http://source.online.free.fr/Linux_HowToCompileSQLite.html

a ".o" file is a collection of compiled code called an 'object' file.
A library file
is very similar but includes a directory so some of the file can be
included in your
program and the parts not referenced left out.

>
> I still have two points to solve
>
> How can I check the functions available in the sqlite3 shared library ?
> Are there some tools available to list the functions
> exported from a shared library under linux ?

I believe the 'lib' program has options to do that.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] Repository for User defined functions

2008-01-11 Thread Jay Sprenkle
The source for the SHA1 algorithm is available on the internet.

On Jan 10, 2008 7:35 PM, Mike McGonagle <[EMAIL PROTECTED]> wrote:
> Hello all,
> I was just curious if there was a respository for user defined functions? I
> was hoping to find a hash function that I could use to hash some strings
> with, and I have not seen anything in the SQLite docs.

--
The PixAddixImage Collector suite:
http://groups-beta.google.com/group/pixaddix

SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] Optimized way of export/import of the sqlite database through C API Interface

2008-01-11 Thread Jay Sprenkle
SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Source code is there to download.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] How to compile SQLite with SQLITE_ENABLE_COLUMN_METADATA option under Ubuntu Linux

2008-01-07 Thread Jay Sprenkle
On Jan 6, 2008 3:16 PM, Luc DAVID <[EMAIL PROTECTED]> wrote:
>
> Could you tell me the best way to achieve this under unbuntu and if I
> can optimise the compilation under Windows (the sqlite.dll size is quite
> important) ?

There are many ways. Most compilers allow you to define a string with
a command line
option. If you can't figure that out just edit the one file with that
option. I believe you want to look at vdbe.c
add the line

#define SQLITE_ENABLE_COLUMN_METADATA

before it's used and you're good to go.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] sqlite3_get_table(); How to get all column names in C?

2008-01-02 Thread Jay Sprenkle
Did you try to query the table 'sqlite_master'? You can get the schema
for any table by referencing the query results..

On Jan 2, 2008 4:52 PM, Jerry Krinock <[EMAIL PROTECTED]> wrote:
> Is there any way to get a list of all column names in a table via the
> C API, which works even when there are no data rows in the table?
>
--
The PixAddixImage Collector suite:
http://groups-beta.google.com/group/pixaddix

SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] join metadata in query results

2008-01-02 Thread Jay Sprenkle
On Jan 2, 2008 5:31 AM, Kees Nuyt <[EMAIL PROTECTED]> wrote:
> >If the engine within sqlite recorded the row id as it
> >fetched each column it would be trivial and would
> >prevent me from having to query the database again.
> >If it's not present in the current code

I found a solution that seems workable. I ended up rewriting my query
class so it assumes a bunch of things which are valid for my application
(but not for general usage). The columns in the result set can always be
correctly identified given a single key, that key is the first column of
every query, and the code does not generate it's own SQL to do updates
or deletes.
I have to craft the sql for each query/update/delete. It's disappointing
since it could have been much more elegant and generic (and a lot less
work for me!).

>
> I know what the answer will be: "This is not a planned feature.
> Adding this would slow down the code for vast majority of people
> who do not need it."

I noticed the column metadata routines are not included by the default
compile settings
so Dr. Hipp isn't averse to putting in things that aren't commonly used.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] join metadata in query results

2008-01-01 Thread Jay Sprenkle
On Jan 1, 2008 4:58 PM, Kees Nuyt <[EMAIL PROTECTED]> wrote:
> You know the query, and also the schema, for example from
> PRAGMA table_info(tabelname);
> That includes primary key info.
> Merge that with the info from the resultset and you know what
> you need to know, don't you?

Almost. If the join isn't done using the primary key then knowing the
primary key isn't helpful.
Parsing the textual output from a pragma doesn't seem very elegant,
fast, or like a good long term practice.

>
> Ok, you don't get the primary key or ROWID unless you explicitly
> ask for it. That's SQL ;)

Yes. that's why I wanted to find out if the engine could tell me what
it's doing internally.

These functions:
const char *sqlite3_column_database_name(sqlite3_stmt*,int);
const char *sqlite3_column_table_name(sqlite3_stmt*,int);
const char *sqlite3_column_origin_name(sqlite3_stmt*,int);

Do most of what I need, but not all of it..

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] join metadata in query results

2008-01-01 Thread Jay Sprenkle
> >If they try to edit a column from the Role table how do I get to Role.Id?
> >If I can get Sqlite to tell me what it used to select the row in the result 
> >set
> >it would be much easier that the alternatives.
>
> You can always query the ROWIDs (as long as there is no GROUP
> BY), with something like:
>
> SELECT Role.ROWID,Role.*, User.Name
>   FROM Role
>   JOIN User ON User.RoleId = Role.Id
>  WHERE User.Id = 6
>
> Instead of the ROWID, you also could use the value of the
> PRIMARY KEY of the row in the resultset (Role.id ?), it probably
> already is available in Role.*.
> Iterate over the columns in the resultset and it should be
> there. But perhaps I didn't understand your question correctly?

Thanks :)
I know how to find the column I want, but I'm not sure how my program will
identify the correct column in the result set. It could be any of the rows,
or not even in the result set. If you use this query:

select role.name, user.name from role join user on user.roleid = role.id;

The key column isn't in the result set for either table.

Sqlite parsed my query and as it reads the results knows the row it's
reading from for each column.
I want that data so I can go back and edit the data I presented to the users.
I don't think what I want is available from sqlite now.

I can get a query column's name, size, type, and value, but I can't
tell if it's an index column or what column(s) were used to select
that column.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



[sqlite] join metadata in query results

2008-01-01 Thread Jay Sprenkle
Good afternoon all,

I'm working on a project using Sqlite. I'm using a query to fill a
table presented to the user.
I'd like to be able to edit the data cell by cell. I can easily call
the column metadata
functions and get the database, table, and column where that data came from.
If I want to update it then I need the rowid or key for that piece of data too.
If the original query is a join can you get any information on the
fields used for the join?

for example:

select Role.*, User.Name
 from Role
 join User on User.RoleId = Role.Id
Where User.Id = 6

If they try to edit a column from the Role table how do I get to Role.Id?
If I can get Sqlite to tell me what it used to select the row in the result set
it would be much easier that the alternatives.

Thanks.

Happy New Year!

-- 
--
The PixAddixImage Collector suite:
http://groups-beta.google.com/group/pixaddix

SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] What would you suggest?

2007-04-09 Thread Jay Sprenkle

To make a good choice you must consider many things.
How are you going to query the data?
Do you need sql to perform searches?
Do you need sqlite's ACID features?

On 4/8/07, Lloyd <[EMAIL PROTECTED]> wrote:


Hi,

I want to store and retrieve sorted integer (these integers are file
offsets). So do I have to use SQLite or shall I store it in a file?
(Reading from the file will be easy by using fseek()). Which would be
efficient? SQLit or this file way? or do you have any other suggestions?
(In this case, I will have millions of integers to store and retrieve)



Re: [sqlite] Re: Nested SELECT : Alternative syntax please ?

2007-04-08 Thread Jay Sprenkle

On 4/8/07, Igor Tandetnik <[EMAIL PROTECTED]> wrote:


Jaime Castells <[EMAIL PROTECTED]> wrote:
> Hmm, I think the only time you really can't avoid a nested query is
> when you
> want to do a grouping function on the result of a grouping function.
> Like
> count the number of customers who have more than a certain number of
> orders.
> Something like:
>
> SELECT COUNT(cust.id)
> FROM (SELECT cust.id, COUNT(order.id) AS order_count FROM cust, order
> WHERE order.cust_id=cust.id GROUP BY cust.id)
> WHERE order_count>10
> ;

select count(cust.id) from cust
where (
select count(*) from order
where order.cust_id = cust.id) > 10;





I think you could also do that with this:

select cust.id, count(*)
from order
inner join order on order.cust_id = cust.id
group by cust.id
having count(*) > 10


Re: [sqlite] Nested SELECT : Alternative syntax please ?

2007-04-08 Thread Jay Sprenkle

glad you got it to work :)

On 4/8/07, Yannick Létourneau <[EMAIL PROTECTED]> wrote:


Actually it seems I hadn't tried hard enough ;)

  I managed to make it work using the following syntax (Thanks to Jay) :

  SELECT SUM(SomeField)
FROM (some complex nested SELECT query)
  GROUP BY SomeOtherField;



Re: [sqlite] Nested SELECT : Alternative syntax please ?

2007-04-08 Thread Jay Sprenkle

On 4/8/07, Jaime Castells <[EMAIL PROTECTED]> wrote:


Actually, Jay, I've bumped into cases where you couldn't avoid a nested
query.



Me too, but usually only in Access.




I think the problem in Yannick's query was the attempt to alias a table
name.  In the above, I alias the field name in the sub query and reference
it without a table name.




I agree, but it doesn't look like there's any need to have a nested query at
all.


Re: [sqlite] Nested SELECT : Alternative syntax please ?

2007-04-08 Thread Jay Sprenkle

Access isn't very compatible with anything else ;)
why is the outer select wrapped around the inner query?
why not just put the group by on the inner query?

 SELECT SUM(SomeField)
 FROM complex query tables here
 GROUP BY SomeOtherField



On 4/8/07, Yannick Létourneau <[EMAIL PROTECTED]> wrote:


I used to do this kind of SELECT statement in MS Access.  This syntax
doesn't seem to be understood by SQLite.  Can someone help me find the
SQLite syntax for doing this ?

  SELECT SUM(IntermediateQuery.SomeField)
  FROM (some complex nested SELECT query) AS IntermediateQuery
  GROUP BY IntermediateQuery.SomeOtherField

  SQLite complains there is no such field as IntermediateQuery.SomeField..
.

  Any help ?
  Emp





--
--
The PixAddixImage Collector suite:
http://groups-beta.google.com/group/pixaddix

SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com


Re: [sqlite] database package recommendations?

2007-04-07 Thread Jay Sprenkle

On 4/7/07, John Stanton <[EMAIL PROTECTED]> wrote:


DISAM, a CISAM clone might suit your needs.




Thanks John. I'll look into that. :)


Re: [sqlite] last N records

2007-04-07 Thread Jay Sprenkle

try this:

delete from mytable
where id not in ( select id from mytable order by id desc limit n )


On 4/7/07, Eric S. Johansson <[EMAIL PROTECTED]> wrote:


what is the easiest way to hold on to the last N records and delete all
older?  I can't figure out the right where expression.






--
--
The PixAddixImage Collector suite:
http://groups-beta.google.com/group/pixaddix

SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com


[sqlite] database package recommendations?

2007-04-07 Thread Jay Sprenkle

Good morning all,

Sorry to spam the sqlite list but I thought this might be the best place to
ask.
I'm trying to find an (embedded) database package to fill a specific need
and wondered if anyone can provide a recommendation?

Requirements:
* disk based storage
* As freaking FAST as possible! (this implies a single file for the
database, non sparse files are best)
* small footprint
* can store binary data up to multi megabyte
* must be able to quickly locate groups of keys (or provide in order
traversal and seek to a substring of a key)
* thread safe is optional, but nice

* does not need any kind of query language
* low numbers of records (100's - 10,000's)
* small keys (<100 bytes)
* no locking or concurrency
* linux and windows compatibility a big plus

I'm considering some of the dbm variants but so far only qdbm comes close.
I'm doing timing tests on qdbm
now but I believe that design has an undesirable feature for my use. It's
very fast generally but occasionally
takes several seconds (ouch!) to perform a next key retrieval. I believe the
btree node retrieval is at fault.

Thanks for your help,

Jay


Re: [sqlite] (newbie) pictures in tables, what's the best way ?

2007-02-23 Thread Jay Sprenkle

On 2/23/07, Stef Mientki <[EMAIL PROTECTED]> wrote:


hello,

I've started with SQLite, because it's one of the first dbases with a
local filessytem without authenciation.
It looks great, compared to ini-files.

Now I've a basic problem:
I've a table, with 10 simple fields and 1 blob field, containing a
(possibly large) image.
Uptill now the dbase is small so no performance problems yet.
But what if it grows ?



I use sqlite in the pixaddix suite. In one installation I have about 250,000
images.
I store the images as paths and put them in the file system. The performance
is
still quite good. File systems are designed to handle files, and databases
are designed to
do queries, so that design decision seemed pretty trivial. I have written
code
to find duplicates and do store some binary data from the images in the
database,
but not the entire image. You can play with it if you like by going to the
address in
the signature line of my message.

--
--
The PixAddixImage Collector suite:
http://groups-beta.google.com/group/pixaddix

SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com


Re: [sqlite] query on match between application and sqlite

2007-02-20 Thread Jay Sprenkle

On 2/19/07, Eric S. Johansson <[EMAIL PROTECTED]> wrote:



The application is an experimental anti-Spam testbed exploring the use
of reputation through proof for puzzles and user interaction.  The list
of things I'm tracking is fairly significant (for me) and I'm trying to
pull things together.  the entire system has been implemented in Python
so that puts additional constraints on the solution.



You might also look at greylisting. It works very well for me.

--
The PixAddixImage Collector suite:
http://groups-beta.google.com/group/pixaddix

SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com


Re: [sqlite] Need help on build query.

2007-02-12 Thread Jay Sprenkle

On 2/12/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:



Igor is correct - you cannot do that now.  But I have sometimes
wondered if it would be nice to add an exec() function to SQLite
that would recursively invoke its argument as an SQL script.
For example:

   SELECT exec('DELETE FROM ' || quote(name))
 FROM sqlite_master
WHERE ;

There would be security implications here, of course.  Clearly
one would need to make sure that user-entered SQL (such as can
be seen at http://www.sqlite.org/cvstrac/new) does not have access
to the eval() function, just as it does not have access to the
the load_extension() function.




Perhaps if the row in sqlite_master were deleted that could invoke the drop
table functionality?


Re: [sqlite] converting 1,234,567 to a number

2007-02-10 Thread Jay Sprenkle

On 2/10/07, T <[EMAIL PROTECTED]> wrote:


Hi all,

I have some imported data, where some fields contain numbers with
commas denoting thousands separators. How can I change these to
actual numbers?



If you're using unix or linux there are very nice tools for cleaning up the
data before loading it.
look at 'sed', 'awk', or 'ed'. It would be trivial using sed.

--
The PixAddixImage Collector suite:
http://groups-beta.google.com/group/pixaddix

SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com


Re: [sqlite] reusing prepared queries or dynamic generation of SQL?

2007-02-10 Thread Jay Sprenkle

On 2/10/07, Brodie Thiesfield <[EMAIL PROTECTED]> wrote:


Hi,

I'm looking for some SQL advice. I have a query which is used as the
base of a filter. At the moment I am using:

SELECT * FROM table WHERE (1=? OR foo=?);

The actual query is more complex and uses multiple of the constructions
in this WHERE clause. If I wanted to select on the foo column then I
would bind (0, 'desired-foo'). If I didn't then I would bind (1, '').
There is an index on the foo column, but this construction precludes the
use of it even when I have a specific foo that I want. The idea behind
this was to prepare a single select query, and then just reuse it all of
the time.



Why not have multiple statements? The 'or' clause is going to prevent that
one
from using an index effectively. It might be simpler but the performance
won't be good.

--
The PixAddixImage Collector suite:
http://groups-beta.google.com/group/pixaddix

SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com


Re: [sqlite] Select and deletion

2007-02-08 Thread Jay Sprenkle

On 2/8/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:



Perhaps what Jay did occurred before [3355] (first released with 3.3.8)
and what DragonK did occurred afterwards.

  http://www.sqlite.org/cvstrac/chngview?cn=3355




That explains it. You have a phenomenal memory for detail!

--
The PixAddixImage Collector suite:
http://groups-beta.google.com/group/pixaddix

SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com


Re: [sqlite] Select and deletion

2007-02-07 Thread Jay Sprenkle

On 2/7/07, DragonK <[EMAIL PROTECTED]> wrote:



This seems to work... but I'm not sure if it should, that's why I'm
asking.
I hope the code will show more exactly what I intend to do. Sorry for the
lack of proper error checking, it's just a test program. :) Also, I'm not
using any transactions, just the PRAGMA synchronous= off;




If it works that's great. I would double check that it did work correctly.
I'll have to poke around now and see why what I did, which was pretty
similar, didn't work.

--
The PixAddixImage Collector suite:
http://groups-beta.google.com/group/pixaddix

SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com


Re: [sqlite] how can i speed up inserts ?

2007-02-07 Thread Jay Sprenkle

On 2/7/07, ohadp <[EMAIL PROTECTED]> wrote:



luckily this isn't mission critical failure, i'll take the risk that one
out
of ten thousand users experiences database corruption.

i can only batch the inserts into maybe 4-5 a time, don't know how much of
an improvement that will be...




If you're building the database with a mass load and not doing an
interactive update it's much faster
to drop all the indexes, insert all the data, then create the indexes.


--
The PixAddixImage Collector suite:
http://groups-beta.google.com/group/pixaddix

SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com


Re: [sqlite] Select and deletion

2007-02-07 Thread Jay Sprenkle

On 2/7/07, DragonK <[EMAIL PROTECTED]> wrote:


Hello everybody,

I'm wondering if the following scenario would work properly:

Run a SELECT query on the database and in a loop retrieve each result;
for each retrieved result, inside the same loop, execute a DELETE
statement
for the previously extracted row.

I need this in order to retrieve a list of filenames from the database and
delete
each file from the hard disk and from the database also and I'm trying to
avoid
creating a temporary list for storing the file names.



I think it won't. Try selecting the list into memory or a temp table, then
using that to delete in a separate step.

--
The PixAddixImage Collector suite:
http://groups-beta.google.com/group/pixaddix

SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com


Re: [sqlite] Extension functions for SQLite in C for free

2007-02-06 Thread Jay Sprenkle

Thanks for sharing MIkey.

On 2/6/07, Mikey C <[EMAIL PROTECTED]> wrote:



Hi,

I've had these functions hanging around for some time.  They are not fully
tested and come with no warranty of fitness, but if anyone wants the code,
please take it.



Re: [sqlite] PS Re: [sqlite] two process problem

2007-02-03 Thread Jay Sprenkle

I have 2 processes running one is updating portions of a table and

>>one is inserting.
>
>
>Are you using threads? There are some issues using the same database
handle
>with multiple threads.

Each process is single threaded.



Your error messages don't look familiar. What language are you programming
in, and do you use a wrapper or call sqlite directly? Some details/code
might help debug it.


Re: [sqlite] two process problem

2007-02-03 Thread Jay Sprenkle

On 2/3/07, Tom Shaw <[EMAIL PROTECTED]> wrote:


I have 2 processes running one is updating portions of a table and
one is inserting.



Are you using threads? There are some issues using the same database handle
with multiple threads.
--
--
The PixAddixImage Collector suite:
http://groups-beta.google.com/group/pixaddix

SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com


Re: [sqlite] sqlite core function question

2007-02-02 Thread Jay Sprenkle

On 1/31/07, T <[EMAIL PROTECTED]> wrote:



or better yet, I expected this to work, using the modulo operator %:

SELECT x - x % 1;




Isn't that evaluated left to right?
x-x = 0
0 %1 = 0



--
--
The PixAddixImage Collector suite:
http://groups-beta.google.com/group/pixaddix

SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com


Re: [sqlite] Obtaining randomness on win32

2007-01-31 Thread Jay Sprenkle

On 1/31/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:


> Please pick a pseudo algorithm of your choice and give us the ability to
> seed it?
>

You already have this capability.  Use the redefinably I/O feature
to insert your own random number seeder in place of the one that
comes built in.



I don't follow that. You mean write a user defined function?


--
The PixAddixImage Collector suite:
http://groups-beta.google.com/group/pixaddix

SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com


Re: [sqlite] Abuse of the SQLite website

2007-01-31 Thread Jay Sprenkle

On 1/30/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:


Last night, a single user (or, at least, a single IP address)
in China that self-identified as running windows98 and
Mozilla 4.0 attempted to download sqlite-3.3.12.tar.gz
24980 times and  sqlite-source-3_3_12.zip 25044 times
over about a 5 hour period, sucking up significant
bandwidth in the process.

I've seen this type of thing before and have on occasion
banned specific IP addresses from the website using

   iptables -A INPUT -s  -j DROP



I created a script that scanned my site logs for such things and it
automates dropping them
into the iptables bit bucket. I'm sure you could come up with something
workable fairly quickly


Re: [sqlite] Obtaining randomness on win32

2007-01-31 Thread Jay Sprenkle

On 1/29/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:


The pseudo-random number generator (PRNG) in SQLite is becoming more
important so it seem good to make sure it is well seeded.



I'd like to be able to seed the random number generator so I can have
repeatable
sequences of random numbers (for testing, etc.). The only really random
numbers
are generated from hardware, and that's problematic. You can't make it cross
platform, or even cross processor, without a lot of switches/if
statements/etc.
Please pick a pseudo algorithm of your choice and give us the ability to
seed it?



On

Unix this is easy - just open /dev/urandom and read out as much
randomness as you need.  But I do not know how to do this on
win32 and wince.  The current implementation seeds the random
number generator on these platforms by grabbing a copy of the
current system time.  See the sqlite3WinRandomSeed() function
in os_win.c for details.  This is not a very good method for
seeding a PRNG.

Can someone with more knowledge of win32 and wince please suggest
a better method for seeding the PRNG on those platforms?

--
D. Richard Hipp  <[EMAIL PROTECTED]>



-
To unsubscribe, send email to [EMAIL PROTECTED]

-





--
--
The PixAddixImage Collector suite:
http://groups-beta.google.com/group/pixaddix

SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com


Re: [sqlite] selecting a random record from a table

2007-01-26 Thread Jay Sprenkle

On 1/25/07, P Kishor <[EMAIL PROTECTED]> wrote:


1. given a non-sequential id, select all the ids
2. grab a random id
3. select the row with that id.

is there a better way of accomplishing this, one that requires a
single round-trip to the db?



There's always the random shuffle method.
add a column to the table called 'sortorder'
assign a random number to that column each time you want the list reordered.
Then use
Select * from mytable order by sortorder limit 1
That method is useful where you need a specific list in random order, like
for card shuffles.
If you use a hash you usually get repeats of some elements and some left
out.


Re: [sqlite] Dumb Newbie Question - Comparison if sqlite index to STL

2007-01-25 Thread Jay Sprenkle

On 1/25/07, Ben Supnik <[EMAIL PROTECTED]> wrote:


Hi Y'all,

Please forgive what I think is probably a dumb question, but...

I am porting my app's data model from C++/STL to sqlite.  My goal is
scalability - even if I implement my data model in C++ using data
structures that give me good O(N) time (e.g. use trees, hash tables,
etc.) the whole thing has to be in memory, and adding indexing means a
pretty big code churn.

My question is:

If I take all of the sqlite optimizations I should be taking (saving my
SQL query statements in compiled form to avoid recompiling over and
over, having enough pages in memory to avoid disk thrash, using
transactions to limit disk I/O)

Does anyone have sqlite and STL experience to tel me how the speed of an
indexed column of integers in sqlite would compare to a set in C++?
  (I believe my set implementation uses a red-black tree.)



I think sqlite uses btree internally for indexing, so you might compare the
time of a red black
tree to the time of a btree to get a rough idea.

A lot of people have found the operating system
will cache their database table so a disk based database will perform as
well as a memory
based database in sqlite (as long as you have enough memory for the cache).

I wouldn't worry too much about scalability until you've reviewed the limits
on sqlite.
It will handle some very large tasks right out of the box.

Good luck



--
The PixAddixImage Collector suite:
http://groups-beta.google.com/group/pixaddix

SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com


Re: [sqlite] Do I need to use sqlite3_close after a failed sqlite3_open?

2007-01-24 Thread Jay Sprenkle

On 1/24/07, Jef Driesen <[EMAIL PROTECTED]> wrote:


Do I need to use sqlite3_close if the call to sqlite3_open indicated an
error? The documentation for sqlite3_open says "An sqlite3* handle is
returned in *ppDb, even if an error occurs." So I assumed the answer is
yes.




I never do, since if open fails I assumed the handle wasn't valid.
It almost never happens so the consequences of being wrong are pretty small.
Did you look at the source for sqlite_open()?


--
--
The PixAddixImage Collector suite:
http://groups-beta.google.com/group/pixaddix

SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com


Re: [sqlite] Shared Lock Transactions

2007-01-21 Thread Jay Sprenkle

On 1/21/07, Andrew Teirney <[EMAIL PROTECTED]> wrote:
>
> > What is a 'shared' lock? You can't share a lock, only one process can
> > own it
> > at a time.
> >
>
> As per this document
>
> http://www.sqlite.org/lockingv3.html
>
> SHARED  The database may be read but not written. Any number of
> processes
> can hold SHARED locks at the same time, hence there can be many
> simultaneous readers. But no other thread or process is allowed to write
>
> to the database file while one or more SHARED locks are active.
>

A file lock is used to serialize write access. W hen reading the file is
not locked.
This makes little sense. There are no 'shared' locks.



Sorry, Accidently hit send before I was finished. This documentation needs

to be updated
so it makes sense. Making up this conceptual 'shared lock' thing instead of
explaining how
the locking mechanism really works is not helpful. A 'shared lock' in
reality is just opening
the disk file that makes up the database without locking. Any number of
other processes
can read the file. You can't acquire a shared lock, you just don't acquire a
write lock.


--
--
The PixAddixImage Collector suite:
http://groups-beta.google.com/group/pixaddix

SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com


Re: [sqlite] Shared Lock Transactions

2007-01-21 Thread Jay Sprenkle

On 1/21/07, Andrew Teirney <[EMAIL PROTECTED]> wrote:


> What is a 'shared' lock? You can't share a lock, only one process can
> own it
> at a time.
>

As per this document

http://www.sqlite.org/lockingv3.html

SHARED  The database may be read but not written. Any number of processes
can hold SHARED locks at the same time, hence there can be many
simultaneous readers. But no other thread or process is allowed to write
to the database file while one or more SHARED locks are active.



A file lock is used to serialize write access. W hen reading the file is not
locked.
This makes little sense. There are no 'shared' locks.


Re: [sqlite] Shared Lock Transactions

2007-01-21 Thread Jay Sprenkle

On 1/21/07, Andrew Teirney <[EMAIL PROTECTED]> wrote:


I appologise in advance if this is not the correct place to post this
query ...

I was just wondering whether there might be any particular reason why
there doesn't appear to be support for creating transactions that
acquire a shared lock on execution of the BEGIN statement?



What is a 'shared' lock? You can't share a lock, only one process can own it
at a time.


Re: [sqlite] Re: does select reliably return insert order?

2007-01-21 Thread Jay Sprenkle

On 1/21/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:



> If you use LIMIT/OFFSET without an
> explicit ORDER BY, you are relying on an implementation detail.

Yes, but SQLite is an implementation. If it returns rows allways in the
same
order I don't see why it shouldn't be acknowledged - one mention in a
documentation would make it a feature, and I think quite usefull one.




If you write programs to rely on features that are not guaranteed then you,
or the users,
will be sorry later. You can learn from other people's mistakes or ignore
their good advice.
We can't force you to use common sense.


--
--
The PixAddixImage Collector suite:
http://groups-beta.google.com/group/pixaddix

SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com


Re: [sqlite] Re: How to optimize a select that gets 17 rows from a 700k row DB (via perl using DBI)?

2007-01-14 Thread Jay Sprenkle

On 1/13/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:


Jay:

The closer to real-time, the better.  The most often a cron job can run
under Linux is minutely, and minutely is pretty good.  I guess I could
have the summary process occur at the end of the script that polls the
machines.  It could generate static HTML, which would presumably make the
page load super fast.  However, under the current regime, the process of
creating that summary is going to take at least 10 seconds.  40 seconds
for polling + 10 seconds for summarizing=50 seconds, and that number is
only going to get bigger!  So I'll have to figure out a better table
structure anyway.


You don't have to run under cron for something like that. Loading and
unloading the program several times a minute is not very efficient anyway.
Just let it run continuously and use sleep() (or a timer) to yield your time
slice until
the next time you want to run.

What's the advantage of a database for this application? If all you need is
to load balance it would seem simpler to just query each machine for it's
load and react accordingly. I'm not sure if Perl supports SOAP
interfaces or serializing data over an http connection. You might look
into that for later.


Are indices something that only work if you create them BEFORE you

start adding data?

No. The index on stats.Timestamp should speed up finding
the record max(Timestamp). It will speed up queries for existing data to.
It's like a table of contents for a book.

Here's what I would try:

1. Can you speed up this?
select Machine, max(Timestamp) as M from stats group by machine

If this is trying to get the machine with the latest time stamp then
perhaps this might be faster:
 select Machine, Timestamp as M from stats order by Timestamp desc limit 1
It gets one record instead of summarizing a lot of data.

Your code does a join of two tables on the machine column and timestamp:

select a.* from stats a, (select Machine, max(Timestamp) as M from stats
group by machine) b where a.machine=b.machine and a.timestamp=b.M order by
load, Mem*MemPctFree desc, Scratch desc;

Did you index both tables on ( machine, timestamp )?
It's got to match them up so an index will speed up the search of both sides
when it tries to match them up.






Additional thoughts:

In general, I think splitting the tables up is the way to go.  Any further
comments/suggestions appreciated!

Jonathan








-

To unsubscribe, send email to [EMAIL PROTECTED]


-






--
--
The PixAddixImage Collector suite:
http://groups-beta.google.com/group/pixaddix

SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com


Re: [sqlite] How to optimize a select that gets 17 rows from a 700k row DB (via perl using DBI)?

2007-01-13 Thread Jay Sprenkle

Have you considered running a process that summarizes the data from the table
and just fetching the last summary for display? Will a periodic snapshot work
for your reporting or do you need realtime summarization?


On 1/12/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:

Hi all.  I will try to post all data I can think of.  The basic issue is
that I have a Perl CGI script that shows the most recent load stats for a
bunch of servers we use at our site for long, computing-intense programs.
The environment is all RedHat Enterprise Linux 4 ( 2.6.9-42.26.ELsmp #1
SMP i686 i686 i386 GNU/Linux)  SQLite version is 3.2.2.  As the number of
records in my database has increased, the page load time has also
increased, now to about 10 seconds, which is unacceptable, IMO.  The idea
is to give users an idea of the least loaded machine to help them make the
decision of which to use for their next program.  The whole concept of how
to best do this is, of course, in and of itself a complicated issue.  At
my org. there is no other system in place so this is how I do it.  I've
gotten a big positive response from users.  The last implementation used a
CSV file and each iteration of gathering the data from the servers only
allowed the cron job to run every five minutes.  After upgrading to
SQLite, the now minutely cron job has never failed to complete
successfully in under one minute that I know of.  I'd like to focus on
optimizing my current implementation, and then, if relevant, talk about
other, perhaps better ways to do it.  Currently, both the old, 5-minutely
CSV version and the current beta (SQLite) version run in parallel, and the
CSV version is still more widely used since the page actually loads in a
reasonable amount of time.  The CSV data are overwritten each time so the
process of reading in that data is always the same.  The SQLite database
is INSERTed into because I want to keep historical data.  The rationale
for this is explained later.

The current system uses a DB with a table called stats that has more or
less minutely data on various conditions for ~17 so-called peak machines.
The table can be described thus:

CREATE TABLE stats (Machine text not null,Load real,Scratch text(4), Mem
int, MemPctFree int, Procs int, Users int, Timestamp text(20) not null,
Message text);

My CGI script fetches the most recent observation for each machine with a
select statement of the form:

select a.* from stats a, (select Machine, max(Timestamp) as M from stats
group by machine) b where a.machine=b.machine and a.timestamp=b.M order by
load, Mem*MemPctFree desc, Scratch desc;

I floundered about trying to find a select statement that would simply
give me the right answer; I suspect the main opportunity for optimization
is in rewriting that statement.

The CGI script uses DBI and the exact statement is my $aref =
$dbh->selectall_arrayref($ss2); where $ss2 is the select statement above.

The idea being that the least loaded machines are first in the list.  The
usefulness of the stats reported are of course debatable, but again, let's
make sure the problem is addressed separately.  Some of the machines have
very large scratch partitions which make those machines particularly
suitable for jobs that generate very large files.

A typical group of rows might look like this:


Machine
Load
Scratch
space
available
Total
memory
(GB)
Free
memory
(%)
Running
processes
User(s)
Timestamp
Message
peaklx6
0
21G
12
96
1
1
[EMAIL PROTECTED]:28:35

peaklx1
0
58G
12
88
1
1
[EMAIL PROTECTED]:29:03

peaklx5
0
19G
12
86
1
1
[EMAIL PROTECTED]:28:33

peaklx4
0

12
84
1
4
[EMAIL PROTECTED]:28:31

peaklx2
0
59G
12
65
1
2
[EMAIL PROTECTED]:29:05

peaklx21
0

4
14
1
2
[EMAIL PROTECTED]:29:07

peaklx3
0.1

12
78
1
3
[EMAIL PROTECTED]:28:27

fstlx1
0.2
20G
4
5
1
11
[EMAIL PROTECTED]:28:37

peaklx22
1

4
53
2
0
[EMAIL PROTECTED]:29:09

peaklx24
2

4
58
3
0
[EMAIL PROTECTED]:29:12

peaklx29
2

4
40
3
3
[EMAIL PROTECTED]:28:25

peaklx23
2

4
39
3
0
[EMAIL PROTECTED]:29:11

peaklx30
2

4
29
3
0
[EMAIL PROTECTED]:28:29

peaklx28
2

4
28
3
0
[EMAIL PROTECTED]:28:23

peaklx26
3

12
69
4
0
[EMAIL PROTECTED]:29:17

peaklx25
4

4
21
5
0
[EMAIL PROTECTED]:29:15

peaklx27
4

4
17
5
2
[EMAIL PROTECTED]:28:21


The problem I am having is that the page load times are getting very, very
long.  The table now has about 700,000 columns and the select statement is
taking about 10 seconds to execute (I benchmarked various parts of the
program and it's not the DB connect or loading the modules or whatnot).
The DB file lives on an NFS mounted share but running the same query
locally and interactively and both all take approximately the same amount
of time.  The size of the DB file itself is currently 89MB.  The web
server (and all the servers in question) all are dual processor 2.8GHz
Xeons with a minimum of 4GB of memory (all running the same OS).  Some
have hyperthreading turned on and others don't.

I tried putting a copy of the DB file in the same directory as the script;

Re: [sqlite] multiuser DB on network share

2007-01-10 Thread Jay Sprenkle

> How is the first client 'contacted' and asked to respond?
> I can't see how this is anything but useless. I can't imagine very many
> programs honor this kind of request since I've never even heard of this
> before last week. If the first client doesn't respond to the request
> it would have to degenerate to a standard lock. Is this an OS hack
> designed in for a specific microsoft application?

I said client OS, not program.  It is part of the SMB protocol and the
client operating system includes an SMB implementation.  Under Windows
it is called the SMB redirector.  The specific protocol request is named
oplock break.  Any SMB implementation that uses oplocks (they have to be
specifically requested at time of open by the SMB implementation) also
has to implement the oplock breaks.


How is this supposed to work correctly without the client being notified?
If I write an application that requires serialized access to a file, and I rely
on operating system locking, if the operating system drops my locks
without notifying me my design breaks. This sounds exactly like what
causes the trashed shared MS Access databases I've seen and network locking
issues I see warnings about here.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] multiuser DB on network share

2007-01-10 Thread Jay Sprenkle

On 1/10/07, Nuno Lucas <[EMAIL PROTECTED]> wrote:

> How is the first client 'contacted' and asked to respond?
> I can't see how this is anything but useless. I can't imagine very many
> programs honor this kind of request since I've never even heard of this
> before last week. If the first client doesn't respond to the request
> it would have to degenerate to a standard lock. Is this an OS hack
> designed in for a specific microsoft application?

The client is the SMB/CIFS file system driver, not the application. It
is all transparent to the programmer, and that is the problem, because
if the operating system doesn't handle this well (in other words, is
bugged) the programmer has no idea it's corrupting it's own file.


So it sounds like turning them off ( they mentioned a windows registry change in
one web page ) would be a good idea if you wanted to ensure database integrity
on a shared directory.




The whole idea is actually quite clever, but the problem is that it
was idealised before people understood everything about networked file
systems (the security aspect was completely overlooked at the
beginning). The current versions are quite good, but as they have to
be compatible with older clients (Win9X), a lot of hacks need to be
done (not forgetting it was done in a time Microsoft didn't believe in
the future of TCP/IP).

For better or worse, is still the major network file system for small
networks (and I don't see any future change on this).


Thanks for the info.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] multiuser DB on network share

2007-01-09 Thread Jay Sprenkle

Oplocks do not break things.  Oplocks will guarantee consistency.  They
are granted when only one client OS has a file open letting that client
OS perform locking and caching operations internally without consulting
the server each time.  If another client wants to open the file, then
that second open request is held up by the server, the first client
contacted and asked to flush any outstanding data, acquire locks, and
drop the oplock.  Once that has happened then the second client gets the
answer to its open request.


How is the first client 'contacted' and asked to respond?
I can't see how this is anything but useless. I can't imagine very many
programs honor this kind of request since I've never even heard of this
before last week. If the first client doesn't respond to the request
it would have to degenerate to a standard lock. Is this an OS hack
designed in for a specific microsoft application?



There are other forms of oplocks that allow for opening and closing the
file multiple times without consulting the server as well as some forms
of limiting sharing (dropped when clients start using byte range locking).

There have been some problems with Windows when smb signing is in use as
the design of smb signing assumes request response pairs whereas oplock
break notifications are asynchronous.  Other than degenerate cases,
current Windows versions have been patched.


Degenerate cases? This sounds like something only Microsoft could dream
up, so I guess degenerate applies... ;)

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] multiuser DB on network share

2007-01-09 Thread Jay Sprenkle

I've heard this too. Windows networking has some issues with locking.
You might research 'oplocks' or 'opportunistic locking' (or
opportunistic caching)
if you're interested in understanding what it's doing. I was reading
it the other
day and thought it might be the key to making it work correctly if you could
turn oplocks off in windows.

On 1/9/07, Daniel Önnerby <[EMAIL PROTECTED]> wrote:

I thought I read somewhere in the docs that this was not reliable (maybe
I dreamed it)???
This is great if this works, although I might still make the
socketserver for notifying when updates has been made.

Thank you for your replies.

John Stanton wrote:
> Why not just use the SMB file locks if you are using the SMB networking?



--
The PixAddixImage Collector suite:
http://groups-beta.google.com/group/pixaddix

SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] Shared cache mode issue

2007-01-08 Thread Jay Sprenkle

On 1/8/07, Peter James <[EMAIL PROTECTED]> wrote:

Thanks for your response, Ken.  I'm not sure I've explained myself
properly.  It's not that I'm calling sqlite3_enable_shared_cache()
multiple times.  It's that if I don't maintain a persistent connection while
the server is running I end up with a dangling pointer and an error.

1. start server thread
a. calls sqlite3_enable_shared_cache()
b. waits for incoming commands
2. open connection #1
3. open connection #2
4. prepare and step a query with connection #1 (through the server)
5. close connection #1
6. prepare a query with connection #2 (through the server)


I believe multiple connections are specifically warned against in the
XUL/javascript
documentation for using the firefox version of sqlite. They redesigned
it in such a
way that it works well only for firefox and can't be used with
anything else easily.
I use it from a firefox addon.

--
The PixAddixImage Collector suite:
http://groups-beta.google.com/group/pixaddix

SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] how would you allow users to re-order rows arbitrarily?

2007-01-06 Thread Jay Sprenkle

I do it by maintaining a column named 'SortOrder'
You can update the field to have whatever content will get you the
order you want
at the moment and it's preserved the next time they start the application.


On 1/6/07, Sean Payne <[EMAIL PROTECTED]> wrote:

Suppose gui-users wanted to drag and drop rows in a table so that
they could shuffle it anyway that they wanted so that the rows
maintained that order the next time they accessed the table.   Can
this be done without updating alot of other rows?  How is this
normally handled?


--
The PixAddixImage Collector suite:
http://groups-beta.google.com/group/pixaddix

SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] Replace one string with another, in SELECT output

2007-01-06 Thread Jay Sprenkle

On 1/6/07, T <[EMAIL PROTECTED]> wrote:

Hi All,

Can I, within SQLite output (eg a SELECT statement) replace all
occurrences of string 1 with string 2?


Not easily. This is typically done after you get the results or by writing
a custom user function.

--
--
The PixAddixImage Collector suite:
http://groups-beta.google.com/group/pixaddix

SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] how long should a single insert take?

2007-01-05 Thread Jay Sprenkle

On 1/5/07, Sean Payne <[EMAIL PROTECTED]> wrote:

Hi,

I'm new to the list and to SQLITE.  I am using it as a backend for a
gui program I'm working on.

However, I am measuring a single trivial insert using the C
sqlite3_exec as taking 190-200ms.   I am not interested in bulk
transactions.  Using a prepared insert doesn't seem to reduce the
time very much (to possibly 170ish ms).  Select statements are nice
and fast taking less than 1ms.

So my question is if this ~200ms range for single-inserts to be
expected?   I am using sqlite 3.3.9 (and 3.3.8) on a 2.16 Intel Core
2 duo OSX build.

If this is to be expected, how do you make it more responsive?  I'd
like the user to be able to press a button to get an insert and have
no noticeable delay (maybe 50ms?)


That seems a bit long, though if it's flushing the data to disk and ensuring
that it's written before returning, it's probably not unreasonable. I would be
really surprised if any user would be able to see any difference between
50ms and 150ms when it's a single event. Human reaction time is generally
somewhere about 100ms I thought.




--
The PixAddixImage Collector suite:
http://groups-beta.google.com/group/pixaddix

SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] calculate age

2007-01-03 Thread Jay Sprenkle

> LOL! You should look at a function to determine if a day is a holiday.
> Talk about ugly! In some places you literally need to know the weather
> and the phase of the moon!
>
> --

[EMAIL PROTECTED] ~/movie]$ pom
The Moon is Waning Gibbous (100% of Full)

[EMAIL PROTECTED] ~/movie]$ which pom
/usr/games/pom


is there a shell script program to let me know if the weather is clear
in mecca? ;)

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] sqlite performance, locking & threading

2006-12-28 Thread Jay Sprenkle

On 12/27/06, Emerson Clarke <[EMAIL PROTECTED]> wrote:

The first question is why database locking has been enabled on a per thread
basis instead of per process so that the normal methods of thread
synchronisation (mutexes, ciritcal sections) could not be used for
maintaining consistency within the application.  The programmer can easily
ensure that no two statements are executed at the same time just as they
would ensure that no more than one operation on a linked list was performed
at the same time.


Having watched a lot of people attempt to write thread safe code I would
say this is an incorrect assumption. I think it only appears simple at a casual
glance.

--
The PixAddixImage Collector suite:
http://groups-beta.google.com/group/pixaddix

SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: Re: [sqlite] Sample dll using

2006-12-23 Thread Jay Sprenkle

On 12/22/06, Artem Yankovskiy <[EMAIL PROTECTED]> wrote:

Thanks!
Can at whom is small sample on C/С ++?



some example code here:

SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite


Re: [sqlite] calculate age

2006-12-23 Thread Jay Sprenkle

On 12/23/06, Joe Wilson <[EMAIL PROTECTED]> wrote:

Warning - Thar Be Dragons Here!

The definition of a month is more or less arbitrary depending on the situation.
Trying to define what exactly is the duration of a "month" is a bottomless
pit of endless bickering best decided by druids, popes and historians - 
certainly
beyond the scope of SQLite. Some manufacturers even employ 13 "month" years
for production planning/scheduling because thirteen 28-day months /almost/
fit into a 365-day year.

There are many other functions with very well-defined/accepted semantics
that are more worthy of consideration as a core SQLite function.


LOL! You should look at a function to determine if a day is a holiday.
Talk about ugly! In some places you literally need to know the weather
and the phase of the moon!

--
The PixAddixImage Collector suite:
http://groups-beta.google.com/group/pixaddix

SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] multiple prepared statements with INSERT?

2006-12-23 Thread Jay Sprenkle

On 12/21/06, E Tse <[EMAIL PROTECTED]> wrote:

Hi guys,
I ran into a strange problem. I have 2 prepared sqlite3_stmt, each inserting
to different tables in a sqlite database:

sqlite3_stmt* insertA;
std::string sql = "insert into tablea(col1) values(?)";
int rc = sqlite3_prepare(db_, sql.c_str(), sql.size(), , NULL);

sqlite3_stmt* insertB;
std::string sql = "insert into tableb(col1) values(?)";
int rc = sqlite3_prepare(db_, sql.c_str(), sql.size(), , NULL);

I intended to keep insertA and insertB through the lifetime of my
application/database. I use sqlite3_reset(insertA) before and after each
$sqlite3_step() call:

sqlite3_reset(insertA);
sqlite3_bind_int(insertA, ...);
rc1 = sqlite3_step(insertA);
sqlite3_reset(insertA);

sqlite3_reset(insertB);
sqlite3_bind_int(insertB, ...);
rc2 = sqlite3_step(insertB);
sqlite3_reset(insertB);

The strange thing is rc2 will returns SQLITE_ERROR if sqlite3_step(insertA)
is executed. The error message from sqlite3_errmsg() is even stranger. It
returns "not an error"!


It looks like your code written so it will reset() the statement
before it's executed
the first time. Perhaps you shouldn't do a reset unless it's been executed.

like

prepare()
while ( something )
  bind()
  step()
  reset()


--
The PixAddixImage Collector suite:
http://groups-beta.google.com/group/pixaddix

SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] delayed (batch) transactions

2006-12-19 Thread Jay Sprenkle

On 12/19/06, Laszlo Elteto <[EMAIL PROTECTED]> wrote:

For this particular application it would NOT be a problem to lose like 2-5
seconds of transactions. I wonder if it is possible to tell SQLite to "hold
off" the transactions, ACCUMMULATE them until a certain time (or if cache
memory is exhausted - which is not yet the case as we have a modest
database), then make a BIG COMMIT (ie. all previous transactions committed
or none). That way it's still transactional (ie. no currupted database - I
really don't want to use sync = OFF) but the I/O performance wouldnt slow
down serving requests.


Have you considered a data warehouse sort of setup?
Write your data to a small cache database that's later uploaded to the larger
'big' database.

--
The PixAddixImage Collector suite:
http://groups-beta.google.com/group/pixaddix

SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] Is there a method for doing bulk insertion?

2006-12-18 Thread Jay Sprenkle

On 12/18/06, Anderson, James H (IT) <[EMAIL PROTECTED]> wrote:

I was hoping there was the equivalent of Sybase's BCP program. I was
also hoping something programmatic was available, i.e., not something
from the command shell. Maybe a little background would help.

I'm planning on using the perl package DBD::SQLite. My department is a
big sybase user but because of the nature of our workload, we experience
a lot of contention in both the transaction log and tempdb (the database
that houses temporary tables). I'm investigating the feasibility of
transferring data into SQLite, doing all the data manipulations there,
and then transferring it back to the appropriate sybase tables. I
suspect this could be a big win for a number of our applications.

But if it can be avoided, I don't want to do a CSV conversion, nor do I
want to shell out of the code to invoke this.


I created a c++ version for my own use. The source code is downloadable
if that's of any help to you. See my sig line for the address.

--
The PixAddixImage Collector suite:
http://groups-beta.google.com/group/pixaddix

SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] Is Column UNIQUE? How To

2006-12-16 Thread Jay Sprenkle

On 12/16/06, Firman Wandayandi <[EMAIL PROTECTED]> wrote:

Yeah, as I thought before. Well nevermind, I should parse the table schema then.
Thanks


You're welcome.

--
The PixAddixImage Collector suite:
http://groups-beta.google.com/group/pixaddix

SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] Is Column UNIQUE? How To

2006-12-16 Thread Jay Sprenkle

On 12/16/06, Firman Wandayandi <[EMAIL PROTECTED]> wrote:


I meant, I want retrieve the information of the column or schema as
much as possible, so I can build a new schema based on it. Then I need
to know if column is a UNIQUE or not and so on.



select * from sqlite_master;

It tells you about the schema







--
--
The PixAddixImage Collector suite:
http://groups-beta.google.com/group/pixaddix

SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] Is Column UNIQUE? How To

2006-12-16 Thread Jay Sprenkle

On 12/16/06, Firman Wandayandi <[EMAIL PROTECTED]> wrote:

Hi,

Is any possible way to know if a column is UNIQUE without "PRAGMA
index_info('')"? Seems "PRAGMA table_info('')"
doesn't returns the unique flag of column.

Thanks for advice.


this will show you the count of duplicated values for a column:

select mycol, count(*)
from mytable
group by mycol



--
The PixAddixImage Collector suite:
http://groups-beta.google.com/group/pixaddix

SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] combining multiple queries

2006-12-15 Thread Jay Sprenkle

On 12/15/06, jason schleifer <[EMAIL PROTECTED]> wrote:

If the user specifies one or more tags, I want to be able to return all
moves that match EITHER tag.. for example, if htey specify "carrey" and
"snow", it should return all movies that have either carrey or snow in the
list.


maybe this might help?

select * from yourtable where tag = 'carrey'
union
select * from yourtable where tag = 'snow'

etc...


--
The PixAddixImage Collector suite:
http://groups-beta.google.com/group/pixaddix

SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] Problem with sqlite3 on CGI scripts with ANSI C

2006-12-15 Thread Jay Sprenkle

On 12/15/06, Francesco Andrisani <[EMAIL PROTECTED]> wrote:

Hi comunity,
i have the follow problem.
I've insert into a C-CGI page a small sqlite3 code.
When i call the page from a browser, it exec a query and show on browser the 
entries, but it don't work.
If i add a "printf" above the sqlite3_open() it print the message, but if i put 
the instruction after the sqlite2_open() it don't swow the message.
Under i put my piece of code:


Did your program also print the proper cgi header before printing anything else?
If you don't print the header you're output will probably get destroyed.
Probably something like:

printf( "Content-type: text/html\n\n" );
or
printf( "Content-type: text/plain\n\n" );


--
The PixAddixImage Collector suite:
http://groups-beta.google.com/group/pixaddix

SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] faster SELECT time on second run

2006-12-13 Thread Jay Sprenkle

On 12/12/06, John Stanton <[EMAIL PROTECTED]> wrote:

If you want to get as much as possible of the Sqlite database into
physical memory on the computer, read all of it.  If you just want to
preload the cache and VM so that the first user gets faster response
execute a dummy query like one you would expect the first user to run.

Since the VM logic will work on a least recently used algorithm the
preload using a read of the file will not be very successful if you have
a huge database and not much physical memory.  You will only have the
tail of the DB in physical memory.

My preference would be to execute a dummy query as part of the DB open.
  That would leave the cache primed with a working set of pages likely
to satisfy the first user query.  After that the cache takes care of
itself.  This method not only primes the Sqlite cache but also makes the
pages resident in physical memory.  Reading the file alone does not
prime the Sqlite cache.


That sounds like an excellent idea. I did assume it was the operating
system disk cache that was being primed, that may not be correct.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] faster SELECT time on second run

2006-12-11 Thread Jay Sprenkle

On 12/11/06, Dennis Cote <[EMAIL PROTECTED]> wrote:


Simply read the entire file once using fread when you open the database.
That will load the entire file in to the OS cache so that when SQLite
starts reading the file, the required disk blocks are already in memory.
The result is the same fast lookups that you see in SQLite the second
time you run a query. The additional read is also quite fast since it
reads the file sequentially from start to finish with no seeking,
whereas SQLite will seek back and forth as it reads database pages into
memory.


You probably can't fseek() to the end and get the same effect?

You could do this in vb6 by writing a loop and using "input #1"
and discarding what was read. In vb.net it's much simpler using
a file stream reader.

--
The JS Image Collector suite:
http://groups-beta.google.com/group/js-image-collector?hl=en

SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] shared-cache mode and firefox

2006-12-06 Thread Jay Sprenkle

Thanks Vitali, and Trevor. I'll poke them instead ;)


> Firefox is now using sqlite. They use shared-cache mode because they want it
> to work over networked drives and they don't want to pay for the
> latency involved.

The "shared cache mode" in sqlite only changes certain behavior for
threads in the same process.  It's unrelated to external locks, which
work as normal.  It's my understanding that Firefox has done
modifications of their own to disable external locking.



--
The JS Image Collector suite:
http://groups-beta.google.com/group/js-image-collector?hl=en

SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



[sqlite] shared-cache mode and firefox

2006-12-06 Thread Jay Sprenkle

Good evening,

I'd like to make a request for the next version of sqlite.
It's a big change and will probably get shot down, but you won't know
until you ask.

Firefox is now using sqlite. They use shared-cache mode because they want it
to work over networked drives and they don't want to pay for the
latency involved.
I personally wouldn't have made this choice since it trades away
something I think
that could be useful to a large number of people to support something
that I believe
almost nobody will use. It's not my decision though. If shared-cache mode was
something that could be turned off at run time it would allow everyone
to get what
they want. I believe this would be a complete refactoring of the code though.

Thanks for your time Dr. Hipp, and for sharing Sqlite with all of us!

Jay

--
--
SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] Using ODBC with Microsoft Access

2006-12-06 Thread Jay Sprenkle

Buzz,
Access questions should be directed to Microsoft.
http://office.microsoft.com/access/


On 12/6/06, Buzz Hill <[EMAIL PROTECTED]> wrote:

Hi,

I am trying to pull records from a Microsoft Access db using ODBC.

The connection works fine. I can get results when I test for any field with an 
integer value.

The problem I am having is with dates. Something as simple as:

SELECT *  FROM CostingHeader where DeliveryDate  = '2/6/2004'

or

SELECT *  FROM CostingHeader where DeliveryDate  = 2/6/2004

simply fails to yield any results. I know that the date 2/6/2004 exists.

I don't understand what Access is looking for.


Thanks,

Buzz






--
--
SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] Mozilla + SQLite?

2006-12-05 Thread Jay Sprenkle

On 7/7/05, Vladimir Vukicevic <[EMAIL PROTECTED]> wrote:

On Wed, 2005-07-06 at 08:40 -0700, Scott Baker wrote:
> Looks like Firefox is gearing up to store some of its information in
> SQLite? Does anyone know anything more about this?
>
> http://gemal.dk/blog/2005/07/06/mozilla_firefox_bookmarks_in_for_a_rewrite/

We'll be using sqlite, but not directly -- there is a layer currently
called mozStorage which is basically a XPCOM/C++ wrapper around sqlite
that I've written, along with convenience wrappers for working with
mozStorage within JavaScript. (For example, statement parameters can
be assigned via "stmt.params.paramName = 'value'" and simple
statements can just be called as functions, as in "stmt(param1,
param2);")



Good morning Vlad,

I've finished a Firefox extension using mozIStorage. After looking at the
documentation again I find it's got a data corruption issue waiting to happen.
The docs say:

" How to corrupt your database
   * Open the database from an external program while it is open in
Mozilla. Our caching breaks the normal file-locking in sqlite that
allows this to be done safely. "

Which is exactly what I need to do. I'm capturing information about downloaded
files and making it accessible to programs outside the browser. There's also the
possibility of other programs writing to this database. Is there any
way to turn off
the caching and revert to regular sqlite locking on a per connection basis?
Otherwise I'm going to have to write my own xpcom wrapper around sqlite
and that really seems a waste since the code is already present.

Thanks

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] How do I speed up CREATE INDEX ?

2006-12-02 Thread Jay Sprenkle

On 12/2/06, Mohd Radzi Ibrahim <[EMAIL PROTECTED]> wrote:

Hi,
I was loading a file to sqlite (3.3.8), and it took 4 mins to load 6 million 
rows (with no index). But then when I run CREATE INDEX it took me 40 mins to do 
that. What could I do to speed up the indexing process ?


Details of the schema would help.
Assuming you didn't create more index than you need, a faster computer
is the answer.
It only has to be done once so why is that a problem?

--
SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



[sqlite] sqlite project announcement: "Image Collector"

2006-11-28 Thread Jay Sprenkle

Good evening all,

The image collector project is nearing release status. This project
allows art enthusiasts to more easily collect, organize, and view
images from the internet.

The project is a suite of programs that all cooperate.

* A firefox browser plugin identifies, downloads, and documents the
source of images in an sqlite database.

* The import program imports non web based images from whatever media
your computer supports.

* The wall paper changer program uses the database to present images
from your collection. You can easily visit the web page that was the
source of the image,
edit it, or remove it from your collection.

* The screen saver program presents images from your collection.

I'm just completing the debugging now. If anyone is interested in
helping test or providing suggestions please contact me?

The next step in the evolution of the project is a web site with the
ability to suggest images you may like but have not seen based on
comparisons of many users collections.

My apologies if I've spammed you.


Jay


--
SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] How to take off a lock on a table

2006-11-27 Thread Jay Sprenkle

On 11/24/06, Salvador Ramirez <[EMAIL PROTECTED]> wrote:

Hello,

I am having a problem with a table on a sqlite3 database
related with a lock that left by a program that died
abnormally in the middle of a select operating initiated
with a call to sqlite3_prepare(). Now I wonder how to take
off the lock on that table left by the program.

Any help is very appreciated.


I've noticed .NET programs on windows don't always seem to clear the
locks to files
that are closed. If your program is a .NET program you might research
it in the microsoft
msdn site.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] whether Sqlite Db supports jar files

2006-11-27 Thread Jay Sprenkle

On 11/24/06, sandhya <[EMAIL PROTECTED]> wrote:

Hi all,
I have loaded all my webpages into database and i am unable to open file
have .jar extension???
Is it something related Database where i need to look into?
please help me


A JAR file is a ZIP archive that usually contains java (and sometime
other stuff)

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] SELECT on empty fields ??

2006-11-27 Thread Jay Sprenkle

On 11/27/06, Isaac Raway <[EMAIL PROTECTED]> wrote:

I'd like to strongly second this. Avoid NULL columns, even at apparent cost.
Having a valid default value is always better. If a design appears to
require NULL values, then the design is likely critically flawed.


Using NULLS is NOT a critical design flaw.

NULL means something specific and if you use it correctly it works perfectly.
NULL indicates when nothing has been entered into a field.
Not entering anything, and entering spaces or a default value, are different.
If you need that information then it's very useful. If you don't then
don't use it
by assigning default values.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] Design of application using embedded database

2006-11-19 Thread Jay Sprenkle

On 11/19/06, Ran <[EMAIL PROTECTED]> wrote:

I think I didn't explain my question well enough.
I know all what you wrote about transactions. The undo functionality I am
looking for is over _several_ transactions.



Does nested transactions do what you want?

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] Select name if exists in the group/table

2006-11-19 Thread Jay Sprenkle

\On 11/18/06, Micro Mega Dust <[EMAIL PROTECTED]> wrote:

Hi,

I'm a novice using databases, and I have a doubt about the working of
SQL language: I have a system of user group, where each group is a
table in sqlite_master, (each table of a group contains the UIDs of
the members). But I don't know how to get the groups of a user, in a
single query.

I tried this:
SELECT name FROM sqlite_master WHERE 'foo' IN name /* foo is a UID */;

But don't work, "name" is used like a literal value.
Is possible? Thanks!


create table user (
id integer PRIMARY KEY AUTOINCREMENT,
name text
);

create table grp(
id integer PRIMARY KEY AUTOINCREMENT,
name text
);

create table user_group(
user_id integer,
group_id integer
);


insert into grp(name) values( 'super user' );
-- id will be assigned 1 automatically.
insert into grp(name) values( 'regular user' );
-- id will be assigned 2 automatically.

insert into user(name) values( 'Micro Mega Dust' );
-- id will be assigned 1 automatically.

insert into user_group(user_id,group_id) values( 1, 1 );
insert into user_group(user_id,group_id) values( 1, 2 );

select grp.name, user.name
 from grp
inner join user_group on grp.id = user_group.group_id
inner join user on user_group.user_id
;




C:\temp>sqlite3 test.db
SQLite version 3.3.8
Enter ".help" for instructions
sqlite> create table user (
  ...>  id integer PRIMARY KEY AUTOINCREMENT,
  ...> name text
  ...> );
sqlite>
sqlite> create table grp(
  ...>  id integer PRIMARY KEY AUTOINCREMENT,
  ...> name text
  ...> );
sqlite>
sqlite> create table user_group(
  ...>  user_id integer,
  ...>  group_id integer
  ...> );
sqlite>
sqlite>
sqlite> insert into grp(name) values( 'super user' );
sqlite> -- id will be assigned 1 automatically.
sqlite> insert into grp(name) values( 'regular user' );
sqlite> -- id will be assigned 2 automatically.
sqlite>
sqlite> insert into user(name) values( 'Micro Mega Dust' );
sqlite> -- id will be assigned 1 automatically.
sqlite>
sqlite> insert into user_group(user_id,group_id) values( 1, 1 );
sqlite> insert into user_group(user_id,group_id) values( 1, 2 );
sqlite>
sqlite>
sqlite> select grp.name, user.name
  ...>   from grp
  ...>  inner join user_group on grp.id = user_group.group_id
  ...>  inner join user on user_group.user_id
  ...> ;
super user|Micro Mega Dust
regular user|Micro Mega Dust
sqlite>





--
SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] Design of application using embedded database

2006-11-19 Thread Jay Sprenkle

On 11/18/06, Ran <[EMAIL PROTECTED]> wrote:

The way the undo-redo is described in the wiki involves triggers to insert
the information of the change in each table to other table which logs the
changes. This will have a price in performance. It also complicates things
when triggers are already used for other things.

So I wonder if journals might be used to implement undo:
If I understand it correctly, for each transaction there is journal that
keeps the information so the transaction could be rolled back. If the
journals are kept somewhere could they be used to rollback _successful_
transactions?


Transactions let you "undo" whatever you have done since the transaction
started. You decide if it was "successful" or not and either commit
the transaction
to make it permanent or roll it back to undo it.

In the case of large numbers of insertions it's faster to put them
into a transaction
and commit them than to do them separately. Performance in most installations is
very quick since the database generally ends up in operating system cache.
I do recall the author of mysql writing "he had no intention of implementing
transaction since it was much slower and proper design eliminated the need
for them."  Sqlite has a much lower code overhead than mysql  so I imagine
it's just as fast or faster in most cases.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] SELECT FOR UPDATE

2006-11-16 Thread Jay Sprenkle

Hi,

We are newbie to SQLite and we would like to lock a table through a select +
update.

Something like that:

BEGIN TRANSACTION

   currentValue = select currentValue from sequence_transactions;

   update sequence_transactions set currentValue = currentValue + 1;

   COMMIT;

END TRANSACTION

When the program reachs the select, we would like other threads /
applications wait for that transaction to finish.

Is there any way of achiving that?



Read through the documentation on locking. It should do what you want
and explain
it so you can understand it. I think what you already have written will probably
work fine, but as always, the difficulty is in the details.

http://sqlite.org/lockingv3.html



--
SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] INSERT INTO with SELECT

2006-11-16 Thread Jay Sprenkle

On 11/16/06, RB Smissaert <[EMAIL PROTECTED]> wrote:

My text file is only an intermediate and I can make the way I want.
I need to move data from Interbase to SQLite. Fastest method sofar is:
IB > ADO recordset
ADO recordset > text file
Import text file with SQLite .import command

Problem with .import is that it doesn't like double quotes.


I put together my own import program. You're welcome to try it if you
don't get it fixed.
You can find it at the link below. I would think a loop inside your program
would be faster though.


--
SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] INSERT INTO with SELECT

2006-11-15 Thread Jay Sprenkle

You should create your database and your table first.
Do that in sqlite3.exe using your create table statement below.
What format is your text file in? What separates one field from another?


On 11/14/06, RB Smissaert <[EMAIL PROTECTED]> wrote:

Thanks and in fact I had a look at your text importer about an hour ago.
Couldn't figure out the parameters though.
How would these be if I wanted to do this.

Import a text file called ReadCode.txt to a database called ReadCode.db
Database has one table called ReadCode with these fields:
SUBJECT_TYPE, READ_CODE, TERM30 , TERM60

SQL to create the table is like this:

create table ReadCode
  (
SUBJECT_TYPE   varchar(5),
READCODE   varchar(5),
TERM30   varchar(30),
TERM60varchar(60)
  );


--
SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] Importing text file via .bat file

2006-11-15 Thread Jay Sprenkle

On 11/15/06, RB Smissaert <[EMAIL PROTECTED]> wrote:

OK, writing the textfile as inserts and running that is a bit slower than
the .import, but it has one advantage and that is that comma's in the field
are no problem as the values can be enclosed in double-quotes.
With the .import I had to take the comma's out first. There weren't many
comma's and it wasn't a big problem, but it is best to go for accuracy, so
It is a bit better. Is there no way to do .import with the data in double
quotes, so that the comma's are no trouble?

Now the next thing to try is doing inserts directly from VB, from the ADO
recordset. I suppose this should be the fastest. I can think I can do it
with the VB wrapper, but not sure how to it without it. Can I do SQLite3.exe
directly with the insert data as an argument?


You can certainly do this from vb. It's a bit of work though.
You will need to read the data file and parse it yourself,
then insert the data directly. You should wrap all the inserts
in a transaction since it will speed it up greatly (this is true
for any method of doing inserts into sqlite).

You can also run the script to import it but if I recall
correctly vb lets you start a process but it takes more work
to wait for the external process to complete and then to
get it's return code from the operating system. The system
call I was familiar with just started the script running, it
didn't wait for it to finish.

I doubt you will see any significant difference in speed
between the two methods.


--
SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] INSERT INTO with SELECT

2006-11-14 Thread Jay Sprenkle

On 11/13/06, RB Smissaert <[EMAIL PROTECTED]> wrote:

OK, thanks for the reply.
I am using the same construction to write directly from Interbase to Access
and that works fine.


I'm surprised. Learn something every day!


I can make an ADO recordset first from the Interbase
data and write that to SQLite in a (double) loop, but it is a bit slow.
Maybe I should write to text first (which is quite fast) and then write to
SQLite. I am not sure though how to import text into SQLite.


If you write a loop to extract it you can insert it through the sqlite
odbc on a separate
connection. If you write CSV format files the command line program can read it,
or you can download my importer (the source code is available for both too).

--
SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] Newbie sqlite questions: check existence of column

2006-11-13 Thread Jay Sprenkle

On 11/13/06, Florent THIERY <[EMAIL PROTECTED]> wrote:

- check first if the column already exists; that, i have no idea how to
achieve it


Hello Florent,
Try this:
select * from sqlite_master;
This gives a lot of interesting info about tables and indices.


--
SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] INSERT INTO with SELECT

2006-11-13 Thread Jay Sprenkle

On 11/13/06, RB Smissaert <[EMAIL PROTECTED]> wrote:

Trying to move data from Interbase to SQLite via the ODBC driver and ADO and
having trouble to get the right syntax for the INSERT INTO statement.

This is what I have now, but it fails with the error: only one SQL statement
allowed.


I notice your code creates this sql:

INSERT INTO  READCODE (SUBJECT_TYPE, READ_CODE, TERM30, TERM60)
SELECT R.SUBJECT_TYPE, R.READ_CODE, R.TERM30, R.TERM60
FROM READCODE R
IN " [  ? ] "

Which isn't valid.  The IN clause is part of WHERE, not a separate qualifier.
It works like this:

select x as prime  from mytable  where x in ( 1,2,3,5,7 );

Since the select is reading from READCODE you're inserting values
into the same table you're selecting them from. There's no way that I know
of to use odbc to copy from one database to another unless they're both
of the same type.

I would do it by selecting all the values to copy in one statement and writing
them to memory or a disk file. Then import to the other database in a second
step.




--
SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



  1   2   3   4   5   6   7   8   >