Re: [sqlite] sqlite and webhosts -SOLVED

[Jim McNamara]

--- John Stanton <[EMAIL PROTECTED]> wrote:

> I think you miss the point about Sqlite.  It is just
> a part of your CGI 
> program - you don't need any help from the ISP, just
> a directory with 
> enough disk space for the Sqlite file.   For a
> modestly sized web 
> application it is a great way of handling your data.
> 
> If you are using PHP you just need to know that it
> has Sqlite 
> incorporated, which I believe is standard these
> days.
> JS
> 
Hi John LeSueur and John Stanton-

the account doesnt come with php5 installed by default
so i have to custom compile it and add it to my
account.  if php5 was set up for shared hosting
automatically at this host, then i could just
dynamically load it using the php.ini file (if it was
available). but since i will be compiling it
anyways

It is interesting to note however if i can have
permission to custom compile it, a webhost who is
offering it could also compile php5.  They may not
offer sqlite in the version they compiled for everyone
to use. Not everyone may have this ability to custom
compile using ssh with their host.  Why anyone would
do this to their customers i have no idea.  This is
part of what i needed to find out.  olm.net wouldnt
give me a definitive answer one way or the other for a
couple of weeks. i may wait around for their answer. 
so there is maybe a weeding out process of some things
"that are included" depending on who you are dealing
with.  like you say though i hope and it will probably
be standard.   

i.e.
First, open a troble ticket to support department with
request to enable SSH access for your account and add
your username to the group compiler.

Then do the following to compile PHP 5:
etc. etc. bunch of instructions go here
when i compile php5 i will include this option
--with-sqlite

so i now have a webhost(s) to use and the instructions
on how to do it.  

thanks for all your help :-)


jim






__ 
Start your day with Yahoo! - Make it your home page! 
http://www.yahoo.com/r/hs

Re: [sqlite] finding records with an underscore using like

[Puneet Kishor]

On Oct 7, 2005, at 10:21 PM, Joe Noon wrote:


I need to find all records where login has an underscore in them.  I
know it has something to do with ESCAPE, since _ matches a single
character, but I cant seem to get the syntax correct.  Here is what im
intending to do, even though this obviously doesnt do it:

SELECT * from users where login LIKE '%_%';




SELECT * FROM users WHERE login LIKE '%\_%'  ESCAPE '\'

note: you can use an escape char of your choice.

--
Puneet Kishor

[sqlite] finding records with an underscore using like

[Joe Noon]

I need to find all records where login has an underscore in them.  I
know it has something to do with ESCAPE, since _ matches a single
character, but I cant seem to get the syntax correct.  Here is what im
intending to do, even though this obviously doesnt do it:

SELECT * from users where login LIKE '%_%';

Can anyone suggest a proper statement that escapes the underscore, so
the above will work as intended?

Thank you!

Joe Noon

Re: [sqlite] Left Outer Joins

[Robert L Cochran]

Jay Sprenkle wrote:


You might also consider NOT saving the image data in the database.
I've always found it better to save a path or URL in the database and store
the image in the file system. (Unless you're searching for things
in the image data itself and not just searching for an image using other
fields as keys)

 

Thanks Jay. I think I found another solution that works -- see my post 
in reply to Peter. As to not saving the image data in the database, that 
is what we are doing on the production server. The images are in the web 
server's document root. This exercise is for my benefit, to play with 
multimedia databases and learn PHP and web application building. It is 
tough work for me, but I'm learning and I know I have a long way to go.


Bob

Re: [sqlite] Left Outer Joins

[Robert L Cochran]

Thanks very much for your help, Peter. Here is a variation that seems to 
be working for me:


select sa.lg_abbr, sa.dbsource, sa.signid, sa.recid, sa.majorlvl, 
sa.lvl6, j.imgfn from sampsign as sa left outer join sgnphoto as j on 
sa.recid = j.recid where sa.dbsource = 'Smith' and sa.majorlvl = 
'photograph';


Notice that I added j.imgfn to the join. As I think you are trying to 
tell me below, every record from the left table which does not match on 
the right table is going to have NULL returned in the column that is 
j.imgfn. All I need to do is test for IS NULL in that column of the 
result set, and I have what I want! The rows that did not match.


So, being new to working with left joins, I didn't think to test for IS 
NULL in the appropriate column to detect a non-matching record. I have 
the correction working now, and I can go on to the next step in the 
processing.


By the way, SQLite 3.2.7 does not support RIGHT or FULL joins.

Bob



Peter Wullinger wrote:


In epistula a Robert L Cochran, die horaque Thu, Oct 06, 2005 at 10:56:03PM 
-0400:
 


Hi --

I'm inexperienced with joining tables and need help.

I'm using SQLite v3.2.7 and the tables discussed below are part of an 
SQLite database.


There are 2 tables: A contains some text columns and an integer primary 
key. B contains an integer primary key and a LONGBLOB column to hold 
image data like jpegs. The primary key in B should match one and only 
one primary key in A. At this point, table B has 2 rows and needs to be 
updated with several hundred more.
I want to craft an SQL query that will return every row in A having a 
primary key that does not match the keys in the rows in B. Then display 
these rows on a web page form. I eventually tried doing a left outer 
join of A and B:
   



Does

SELECT sa.dbsource, sa.signid, sa.recid, sa.majorlvl, sa.lvl6
FROM sampsign AS sa
LEFT OUTER JOIN sgnphoto AS j
ON sa.recid = j.recid
WHERE
sa.recid IS NULL
AND sa.dbsource = 'Smith'
AND sa.majorlvl = 'photograph'

do, what you want?

For explanation: 


Outer joins return the same data set as inner joins
with the following rows added:
- For left outer joins: Every row from the left table that
 did not match any row in the right table. Since there is
 no matchin data row in the left table, these fields
 are set to NULL.
- Same goes for right outer joins but with tables roles
 reversed.
- full outer joins return the combination of both left and
 right outer joins.

So, if you want all the data in your right table that did not match
anything on your left table, just check, if the corresponding key
column (use those, since primary keys cannot be null) is null.

Cheers,
Peter

PS.: "= NULL" does not work "IS NULL" does work. And don't
laugh, last time I've seen this error was in code from an
 experienced database programmer ... ;-).

 

[sqlite] getting table size

[Eno Thereska]

Hi,

Is there a SQL query that returns the size in bytes a table occupies?

Thanks
Eno

Re: [sqlite] sqlite and webhosts -SOLVED

[John Stanton]

I think you miss the point about Sqlite.  It is just a part of your CGI 
program - you don't need any help from the ISP, just a directory with 
enough disk space for the Sqlite file.   For a modestly sized web 
application it is a great way of handling your data.


If you are using PHP you just need to know that it has Sqlite 
incorporated, which I believe is standard these days.

JS

Jim McNamara wrote:

thanks john and clay-

it is good to know i have an option that works if i
need it.  i think people can actually save a little
money that way.  some hosts will offer php at a lower
price per month without the mysql db .  when you
upgrade to get a mysql db included it costs more.  or
you could just use xhtml and cgi and forget php.

i am still checking around to see if someone will let
me use it with php.  olm.net is checking into it and
said try back in a couple of weeks and ask again. 
they are testing php5 right now on their system.


i bookmarked the cgi library page in case i need it in
the future. 


now i might have 2 options at different prices to
choose from.

thanks again,
jim





__ 
Yahoo! Mail - PC Magazine Editors' Choice 2005 
http://mail.yahoo.com

Re: [sqlite] Please Need Advice on 64 Bits Platform

[Hamid Benhocine]

Hi,
IBM : powerpc-ibm-aix5.1.0.0
I did a lot of cleanup in environnemnts variables.
then
1 ) export OBJECT_MODE=64
2) I stick with a simple configure
./../sqlite-3.2.7/configure --disable--shared --disable-tcl 
--disable-threadsafe --prefix=$PREFIX

3) in the Makefile  put
-q64 -qlonglong for the xlc compiler
4) in ./libtool forced ar with -X64 flag

results: SQLite Works fine . I did some basics tests  (inserts,simple 
selects, create temp tables ..)
I think , Now people here, will certainly consider this very great 
Engine to suit there needs.. and

replace the . home data format.

Thansks all and thanks drh.

I will post if i have trouble.

Hamid.




Jarosław Nozderko wrote:


I've just built SQLite 3.2.7 on HP-UX 11 in 64-bit mode.
Loading 1000 000 records into a simple table (using .read 
from SQL script with inserts, 1 in a single transaction)

took 5 min 40 s (the same in 32-bit - 06:29) and creating
index 56 s (01:14 for 32-bit). Unfortunately, this is 
heavily loaded server, so results above may not be precise.



Regards,
Jarek

 


Hamid Benhocine <[EMAIL PROTECTED]> wrote:
   


I want just to know if the Engine is 64 bits ready
 

SQLite is designed to work on 64-bit machines.  There have 
been a few alignment problems in the past but those have all 
been worked out now, I believe.  I do not have a 64-bit 
machine to test on, but reports from users are that SQLite 
works when compiled in 64-bit mode.

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

   




 

Re: [sqlite] sqlite and webhosts -SOLVED

[John LeSueur]

Jim McNamara wrote:


thanks john and clay-

it is good to know i have an option that works if i
need it.  i think people can actually save a little
money that way.  some hosts will offer php at a lower
price per month without the mysql db .  when you
upgrade to get a mysql db included it costs more.  or
you could just use xhtml and cgi and forget php.

i am still checking around to see if someone will let
me use it with php.  olm.net is checking into it and
said try back in a couple of weeks and ask again. 
they are testing php5 right now on their system.


i bookmarked the cgi library page in case i need it in
the future. 


now i might have 2 options at different prices to
choose from.

thanks again,
jim





__ 
Yahoo! Mail - PC Magazine Editors' Choice 2005 
http://mail.yahoo.com
 

PHP 5 also builds by default with the sqlite library built in. So if 
your webhost didn't strip it out, then it's there. try phpinfo() to see 
what modules are available. If sqlite is in the list, then you can 
create databases  with the sqlite_open() function. To see more, go to 
php.net/sqlite.


John LeSueur

Re: [sqlite] Database corruption

[Mrs. Brisby]

On 2005-10-07 at 11:11, http://tick/%7Egeocar/netmail/ws.cgi?action=v2;[EMAIL PROTECTED]">[EMAIL 
PROTECTED] wrote:

 "Mrs. Brisby" http://tick/%7Egeocar/netmail/ws.cgi?action=v2;[EMAIL PROTECTED]">[EMAIL 
PROTECTED] wrote:

 

  I had some MacOSX systems suffer a power failure, when they came back

   I made some copies of the database and: [integrity_check failed]

 

 

 This suggests that you copied only the database file itself and not its

 rollback journal.  That can lead to corruption.


There was no rollback journal. This is why I'm worried.

 Failing that, you can try to do a ".dump" of the database into a fresh

 database.  If ".dump" gives problems, try running it separately for

 each table in the database file.  You should be able to recover most of

 your data that way, but some patching and fixing will still likely be

 necessary once you are done.

.dump appears to work for most data. I'm using this.

Re: [sqlite] Left Outer Joins

[Peter Wullinger]

In epistula a Robert L Cochran, die horaque Thu, Oct 06, 2005 at 10:56:03PM 
-0400:
> Hi --
> 
> I'm inexperienced with joining tables and need help.
> 
> I'm using SQLite v3.2.7 and the tables discussed below are part of an 
> SQLite database.
> 
> There are 2 tables: A contains some text columns and an integer primary 
> key. B contains an integer primary key and a LONGBLOB column to hold 
> image data like jpegs. The primary key in B should match one and only 
> one primary key in A. At this point, table B has 2 rows and needs to be 
> updated with several hundred more.
> I want to craft an SQL query that will return every row in A having a 
> primary key that does not match the keys in the rows in B. Then display 
> these rows on a web page form. I eventually tried doing a left outer 
> join of A and B:
 
Does

SELECT sa.dbsource, sa.signid, sa.recid, sa.majorlvl, sa.lvl6
FROM sampsign AS sa
LEFT OUTER JOIN sgnphoto AS j
ON sa.recid = j.recid
WHERE
sa.recid IS NULL
AND sa.dbsource = 'Smith'
AND sa.majorlvl = 'photograph'

do, what you want?

For explanation: 

Outer joins return the same data set as inner joins
with the following rows added:
- For left outer joins: Every row from the left table that
  did not match any row in the right table. Since there is
  no matchin data row in the left table, these fields
  are set to NULL.
- Same goes for right outer joins but with tables roles
  reversed.
- full outer joins return the combination of both left and
  right outer joins.
 
So, if you want all the data in your right table that did not match
anything on your left table, just check, if the corresponding key
column (use those, since primary keys cannot be null) is null.

Cheers,
Peter

PS.: "= NULL" does not work "IS NULL" does work. And don't
 laugh, last time I've seen this error was in code from an
 experienced database programmer ... ;-).
 
-- 

Re: [sqlite] Maintaining a sequence that's not rowid

[Clark Christensen]

Thanks to all who replied.  I really appreciate the great feedback!
 
 I will probably end-up using something like the scheme offered below :-)
 
  -Clark
 

- Original Message 
From: Dan Kennedy <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Thursday, October 06, 2005 09:42:32
Subject: RE: [sqlite] Maintaining a sequence that's not rowid


> >  /* Retrieve next id in sequence: */
> >  BEGIN;
> >  SELECT id FROM id_allocator;  /* This is the id to use */
> >  UPDATE id_allocator SET id = id + 1;
> >  COMMIT;   /* Can't use the id until the transaction successfully commits! 
> > */
> 
> Just a side note; Traditionally this is done the other way around, i.e. 
> update then select. The
> reason for this is the (small) window in the critical section for another 
> session to issue the
> select at the same time causing a deadlock. Locking the table exclusively at 
> the beginnng of the
> transaction avoids that problem.

True enough. You also need to do "BEGIN EXCLUSIVE;" 
instead of begin. Or just handle the busy condition.

Re: [sqlite] Database corruption

[drh]

"Mrs. Brisby" <[EMAIL PROTECTED]> wrote:
> I had some MacOSX systems suffer a power failure, when they came back 
> I made some copies of the database and: [integrity_check failed]

This suggests that you copied only the database file itself and
not its rollback journal.  That can lead to corruption.

The best fix is to go get a fresh copy of both the file and its
rollback journal, make sure they both are in the same directory, then
open the database file.  The journal will be read and will automatically
roll back pending changes, restoring the database to a consistent state.

Failing that, you can try to do a ".dump" of the database into
a fresh database.  If ".dump" gives problems, try running it separately
for each table in the database file.  You should be able to recover
most of your data that way, but some patching and fixing will still
likely be necessary once you are done.
--
D. Richard Hipp <[EMAIL PROTECTED]>

Re: [sqlite] wondering about sqlite and webhosts

[John Stanton]

I have used Sqlite in a web application.  It is called from C language 
CGI programs running under Apache and performs very well.  Its 
simplicity and the fact that it only involves one file and is linked 
with the CGI process makes it very appropriate for the short sessions 
inherent with CGI.  As far as multi-user contentions are concerned I 
just make sure that it handles BUSY.

JS

Robert L Cochran wrote:
I have no experience with webhosts in this context, but I'm currently 
using beta snapshots of PHP 5.1, SQLite 3.2.7, and Apache server, and it 
is working great for me. I'm using the PHP Data Objects (PDO) 
functionality that comes with PHP 5.1, it has hooks for SQLite. So what 
is happening here is that I'm learning SQL itself, querying tables, 
using new PHP extensions, building a web application, and learning a lot 
from it all.


SQLite is not secure, but you can contact Dr. Hipp to discuss solutions 
to this that you can license.


One thing I'm noticing is that my table inserts on SQLite are very fast. 
I'm not sure if this is partly because of new PHP optimizations, the 
fact that I statically built PHP instead of using shared extensions, 
SQLite's optimizations or all these things. But things work fast.


I won't call myself an experienced database programmer just right now, 
but I'm getting there. And SQLite is giving me a great education.


Bob Cochran


Jim McNamara wrote:


hi-

when thinking about software to use on my next website
i came up with sqlite,
http://www.freeguppy.org/articles.php?lng=en=55044
,
php5, abyss webserver, and phpedit.

i dont see many webhosts that advertise sqlite
availability.  are webhosts excluding support for
sqlite for any reason
i.e. is it a security concern?
it is suppose to be an internal extension.  i am not
sure what that means yet.

do webhosts think it is easy to offer sqlite support?
is it less of an administrative headache for them to
offer it.  i know there is less administration for a
user.

i hope this information can help me when i approach a
webhost when i am out shopping.

thanks for any tips.
jim




   
__ Yahoo! Mail - PC Magazine Editors' 
Choice 2005 http://mail.yahoo.com



 

[sqlite] Database corruption

[Mrs. Brisby]

I had some MacOSX systems suffer a power failure, when they came back I made 
some copies of the database and:

sqlite PRAGMA integrity_check;
*** in database main ***
On tree page 39134 cell 65: 2nd reference to page 33247
On tree page 39134 cell 65: Child page depth differs
On tree page 39134 cell 66: Child page depth differs
Page 33220 is never used
wrong # of entries in index t1
SQL error: database disk image is malformed

I need to repair these databases in a hurry, but I'm not 100% sure where to 
start.

No, they're not all the same.

Any suggestions?

Re: [sqlite] Left Outer Joins

[Jay Sprenkle]

On 10/6/05, Robert L Cochran <[EMAIL PROTECTED]> wrote:
>
> I want to craft an SQL query that will return every row in A having a
> primary key that does not match the keys in the rows in B. Then display
> these rows on a web page form. I eventually tried doing a left outer
> join of A and B:


select * from a
where a.id  not in ( select distinct id from b )

D:\temp\convention>sqlite3
SQLite version 3.0.8
Enter ".help" for instructions
sqlite> create table a ( id integer, stuff text );
sqlite> create table b ( id integer, path text );
sqlite> insert into a(id,stuff) values(1,'one');
sqlite> insert into a(id,stuff) values(2,'two');
sqlite> insert into b values(1,'one');
sqlite> select * from a where a.id  not in ( select distinct id
from b );
2|two
sqlite>


You might also consider NOT saving the image data in the database.
I've always found it better to save a path or URL in the database and store
the image in the file system. (Unless you're searching for things
in the image data itself and not just searching for an image using other
fields as keys)

Re: [sqlite] Please Need Advice on 64 Bits Platform

[Jarosław Nozderko]

I've just built SQLite 3.2.7 on HP-UX 11 in 64-bit mode.
Loading 1000 000 records into a simple table (using .read 
from SQL script with inserts, 1 in a single transaction)
took 5 min 40 s (the same in 32-bit - 06:29) and creating
index 56 s (01:14 for 32-bit). Unfortunately, this is 
heavily loaded server, so results above may not be precise.


Regards,
Jarek

> Hamid Benhocine <[EMAIL PROTECTED]> wrote:
> > I want just to know if the Engine is 64 bits ready
> 
> SQLite is designed to work on 64-bit machines.  There have 
> been a few alignment problems in the past but those have all 
> been worked out now, I believe.  I do not have a 64-bit 
> machine to test on, but reports from users are that SQLite 
> works when compiled in 64-bit mode.
> --
> D. Richard Hipp <[EMAIL PROTECTED]>
> 
> 

Re: [sqlite] wondering about sqlite and webhosts

[Clay Dowling]

Jim McNamara said:

> i dont see many webhosts that advertise sqlite
> availability.  are webhosts excluding support for
> sqlite for any reason
> i.e. is it a security concern?
> it is suppose to be an internal extension.  i am not
> sure what that means yet.

Because SQLite doesn't have a database server it isn't likely that hosts
will advertise support for it.  The support you get will mostly be from
the language you use for your application.  For an example check out my
own application (in my signature below).  I staticly link SQLite against
my CGI program (I write in C++), so it doesn't matter whether or not the
web host supports SQLite.

Clay
-- 
Simple Content Management
http://www.ceamus.com

Re: [sqlite] seeking sqlite3_progress_handler advice

[Christian Smith]

On Thu, 6 Oct 2005, Will Leshner wrote:

>
>On Oct 6, 2005, at 10:57 AM, [EMAIL PROTECTED] wrote:
>
>> Figure 10 to 20 microseconds per opcode.  I'd say around 100.
>>
>
>Thanks. That makes sense.
>
>> Please note that the counter resets with each call to sqlite3_step().
>
>That is very good to know. Does that mean that if I set the count too
>high, the progress handler might never get called?
>

You can call the progress handler yourself after sqlite3_step returns, as
for selects, you'll return for each row.

I think it's more aimed at big updates, index building, the sort of thing
that does not return results and takes a long time.

Christian

-- 
/"\
\ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
 X   - AGAINST MS ATTACHMENTS
/ \

Re: [sqlite] Please Need Advice on 64 Bits Platform

[Robert L Cochran]

I have an AMD 64 machine myself. Do I need to pass any special to 
compile in 64 bit mode?


Bob

[EMAIL PROTECTED] wrote:


Hamid Benhocine <[EMAIL PROTECTED]> wrote:
 

I want just to know if the Engine is 64 
bits ready 
   



SQLite is designed to work on 64-bit machines.  There
have been a few alignment problems in the past but those
have all been worked out now, I believe.  I do not have
a 64-bit machine to test on, but reports from users are
that SQLite works when compiled in 64-bit mode.
--
D. Richard Hipp <[EMAIL PROTECTED]>



 

Re: [sqlite] Please Need Advice on 64 Bits Platform

[drh]

Hamid Benhocine <[EMAIL PROTECTED]> wrote:
> I want just to know if the Engine is 64 
> bits ready 

SQLite is designed to work on 64-bit machines.  There
have been a few alignment problems in the past but those
have all been worked out now, I believe.  I do not have
a 64-bit machine to test on, but reports from users are
that SQLite works when compiled in 64-bit mode.
--
D. Richard Hipp <[EMAIL PROTECTED]>

Re: [sqlite] Left Outer Joins

[Mark de Vries]

On Thu, 6 Oct 2005, Brett Wilson wrote:

> I believe saying "GROUP BY a.primarykey" clause at the end of your
> query will do what you want. It's explained in

I would try something like:

select sa.dbsource, sa.signid, sa.recid, sa.majorlvl, sa.lvl6
from sampsign sa
where sa.dbsource = 'Smith'
and sa.majorlvl = 'photograph'
and not exists (select j.recid from sgnphoto j where j.recid=sa.recid)

> http://sqlite.org/lang_select.html There might also be better ways of
> doing it, but I'm not an expert.
>
> Brett
>
> On 10/6/05, Robert L Cochran <[EMAIL PROTECTED]> wrote:
> > Hi --
> >
> > I'm using SQLite v3.2.7 and the tables discussed below are part of an
> > SQLite database.
> >
> > There are 2 tables: A contains some text columns and an integer primary
> > key. B contains an integer primary key and a LONGBLOB column to hold
> > image data like jpegs. The primary key in B should match one and only
> > one primary key in A. At this point, table B has 2 rows and needs to be
> > updated with several hundred more.
> >
> > I want to craft an SQL query that will return every row in A having a
> > primary key that does not match the keys in the rows in B. Then display
> > I'm inexperienced with joining tables and need help.
> >
> > these rows on a web page form. I eventually tried doing a left outer
> > join of A and B:
> >
> > select sa.dbsource, sa.signid, sa.recid, sa.majorlvl, sa.lvl6 from
> > sampsign as sa left outer join sgnphoto as j on sa.recid = j.recid where
> > sa.dbsource = 'Smith' and sa.majorlvl = 'photograph';
> >
> > The result set comes extremely close to what I want. The trouble is, it
> > includes the 2 rows from A which match the rows in B. I'd like to get
> > rid of them and see only the non-matching rows.
> >
> > Thanks a lot for your help!
> >
> > Bob Cochran
> > Greenbelt, Maryland, USA
> >
> >
>


Regards,
Mark