Re: [sqlite] Is it okay to use SQLite + PHP for Library Software?

[Mr. Puneet Kishor]

On Aug 16, 2011, at 1:23 AM, Darren Duncan wrote:

> Raouf Athar wrote:
>> I have to develop a Library Management System using *PHP* for a medium sized
>> college library. The library has about 5,000 members and 50,000 books. On an
>> average, about 500 members will look for books and will be issued books on
>> daily basis.
>> 
>> *Kindly let me know if it is okay to use SQLite?**Kindly add a few words in
>> support of your answer.*
>> *Can you give me links/ references of a few applications supported by
>> SQLite?*
> 
> Have you looked at existing projects before starting a new one?
> 
> Look at "Evergreen", http://open-ils.org/ which is an open source project 
> developed by and for libraries, and that has been used in production for a 
> few 
> years now in many libraries.
> 
> You should adapt this or modify it to meet your needs rather than start a new 
> one, unless you can justify otherwise.
> 

Yeah, while SQLite can be used to happily power your library system, try 
finding an existing open source solution, adapting it, making it better, and 
contributing your enhancements back for everyone to benefit. Consider 
"Evergreen." Also look at bibapp.org, and I am sure there are many other.



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

Re: [sqlite] Insert rows into a foreign key table

[BareFeetWare]

On 15/08/2011, at 10:40 PM, Simon Slavin wrote:

> which is why proper code should never do it in one instruction.  You do the 
> SELECT first, then use your programming language to do the right thing for 
> each of the three cases where 0, 1 or more records are returned.

I disagree with this more general statement (as I have before). In general, it 
is desirable to do as much SQL in one transaction as possible, for speed, 
consistency and so SQLite's internal optimisers can do their thing. Selecting 
and then re-injecting the result of a select is unnecessary double handling.

Having said that, all of the other assertions hold true:

1. Only select a person based on a unique column. So only match against 
LastName if it is defined as unique.

2. Be prepared for a returned null.

In reality, you are probably creating an invoice for a person chosen by the 
operator, so instead of noting the LastName of that person, note the unique ID 
instead.

So, something like this:

create table Person
(   ID integer primary key not null
,   LastName text collate nocase
,   FirstName text collate nocase
,   Email text collate nocase
)
;
create table "Order"
(   ID integer primary key not null
,   Person_ID integer references Person (ID) on delete restrict
,   Date real
)
;

In your code, when the operator selects a Person, store the ID of that person. 
Then to create an Order, do this:

insert into "Order" (Person_ID, Date) values (?, julianday('now'))
;

where the ? gets replaced by the chosen Person's ID.

The Order ID (ie Order number) will be automatically allocated.

You could also include, in the same transaction, allocation of items to the 
order etc, but that depends of your user interface.

Tom Brodhurst-Hill
BareFeetWare

--
iPhone/iPad/iPod and Mac software development, specialising in databases
develo...@barefeetware.com
 --
Comparison of SQLite GUI tools:
http://www.barefeetware.com/sqlite/compare/?ml

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

Re: [sqlite] Is it okay to use SQLite + PHP for Library Software?

[Darren Duncan]

Raouf Athar wrote:
> I have to develop a Library Management System using *PHP* for a medium sized
> college library. The library has about 5,000 members and 50,000 books. On an
> average, about 500 members will look for books and will be issued books on
> daily basis.
> 
> *Kindly let me know if it is okay to use SQLite?**Kindly add a few words in
> support of your answer.*
> *Can you give me links/ references of a few applications supported by
> SQLite?*

Have you looked at existing projects before starting a new one?

Look at "Evergreen", http://open-ils.org/ which is an open source project 
developed by and for libraries, and that has been used in production for a few 
years now in many libraries.

You should adapt this or modify it to meet your needs rather than start a new 
one, unless you can justify otherwise.

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

Re: [sqlite] Read only scaling optimization

[Pavel Ivanov]

> Just by having 2 programs access 2
> different files on the same disk give that much of a performance/scaling
> gain causes me to believe that there exists a vital area of code that is
> mutexed that is the bottleneck.

I hope you are talking about your OS kernel, because SQLite won't ever
care whether 2 different files reside on a same hdd or on different
ones. In both cases it executes the same code.

> So I'll ask the question. Is there any compile time or pragma's engineered
> towards read only performance as locking a table is no longer needed. To
> save mutexing in cache we would also be ok if each thread would have it's
> cache so it doesn't have to obtain a lock going into it.

With default settings each connection has its own cache, they have
very little interference with each other. And even if you have some
custom settings different files have different caches anyway.

Regarding optimization of read-only access: the problem is not that
you only reading a file and don't need locks, the problem is to
prevent any other process that could write into the same file from
doing that while your reading transaction is active. So no, SQLite
doesn't have any settings or pragmas to optimize such access.

That said if you are absolutely sure that no other process will write
to the database while you are reading you can create your own VFS and
make xAccess and xCheckReservedLock methods a no-op. You can read more
about VFS starting from here http://www.sqlite.org/c3ref/vfs_find.html
(follow the links to read all related info).


Pavel


On Mon, Aug 15, 2011 at 9:00 AM, Drew Kozicki  wrote:
> The bottleneck appears to be mutex's locking access to the file and cache
> inside the same program. I'm guessing that the reason why 2 programs on 2
> files on 2 hdd's did not perform as well as the one too me suggests that
> both hdd's do not have the same performance and that it is not access to the
> physical disk that is the problem. Just by having 2 programs access 2
> different files on the same disk give that much of a performance/scaling
> gain causes me to believe that there exists a vital area of code that is
> mutexed that is the bottleneck.
>
> The source code, unfortunately, I can't post as it is the property of the
> company I work for.
>
> So I'll ask the question. Is there any compile time or pragma's engineered
> towards read only performance as locking a table is no longer needed. To
> save mutexing in cache we would also be ok if each thread would have it's
> cache so it doesn't have to obtain a lock going into it.
>
> Thank you all once again for your help,
>
> Drew
>
> Message: 17
> Date: Fri, 12 Aug 2011 19:28:33 +0100
> From: Simon Slavin 
> Subject: Re: [sqlite] Read only scaling optimization
> To: General Discussion of SQLite Database 
> Message-ID: 
> Content-Type: text/plain; charset=us-ascii
>
>
> On 12 Aug 2011, at 7:01pm, Drew Kozicki wrote:
>
>> I have a Driver doing this pulling in 32 queries aimed at randomness and
>> different tables, much like that would be experienced in typical usage.
> Best
>> performance comes from having 2 separate programs running on 2 separate
>> files.
>
> I'm no expert, but that suggests to me that your bottleneck is access to the
> physical file on disk.  So your greatest speed increases will come not from
> more threads but from a very fast hard disk drive, lots of hard drive
> caching, etc..
>
> That's a great set of benchmarks, by the way.
>
> Simon.
>
> --
>
> Message: 18
> Date: Fri, 12 Aug 2011 14:35:29 -0400
> From: Pavel Ivanov 
> Subject: Re: [sqlite] Read only scaling optimization
> To: General Discussion of SQLite Database 
> Message-ID:
>       
> Content-Type: text/plain; charset=ISO-8859-1
>
>>> I have a Driver doing this pulling in 32 queries aimed at randomness and
>>> different tables, much like that would be experienced in typical usage.
> Best
>>> performance comes from having 2 separate programs running on 2 separate
>>> files.
>>
>> I'm no expert, but that suggests to me that your bottleneck is access to
> the physical file on disk. ?So your greatest speed increases will come not
> from more threads but from a very fast hard disk drive, lots of hard drive
> caching, etc..
>
> It's a little surprising to me that with all the same conditions 2
> files residing on the same drive have better performance than the same
> files residing on different drives. Theoretically that shouldn't
> happen.
>
>
> Pavel
>
>
> Message: 2
> Date: Sun, 14 Aug 2011 17:53:26 +0400
> From: Alexey Pechnikov 
> Subject: Re: [sqlite] Read only scaling optimization
> To: General Discussion of SQLite Database 
> Message-ID:
>        HuywUmjZtNfNL-V5m8Un=rg3wk...@mail.gmail.com>
> Content-Type: text/plain; charset=ISO-8859-1
>
> 2011/8/12 Pavel Ivanov :
>> It's a little surprising to me that with all the same conditions 2
>> files residing on the same drive have better performance than the same
>> files residing on different drives. Theoretic

Re: [sqlite] Read only scaling optimization

[Drew Kozicki]

The bottleneck appears to be mutex's locking access to the file and cache
inside the same program. I'm guessing that the reason why 2 programs on 2
files on 2 hdd's did not perform as well as the one too me suggests that
both hdd's do not have the same performance and that it is not access to the
physical disk that is the problem. Just by having 2 programs access 2
different files on the same disk give that much of a performance/scaling
gain causes me to believe that there exists a vital area of code that is
mutexed that is the bottleneck.

The source code, unfortunately, I can't post as it is the property of the
company I work for.

So I'll ask the question. Is there any compile time or pragma's engineered
towards read only performance as locking a table is no longer needed. To
save mutexing in cache we would also be ok if each thread would have it's
cache so it doesn't have to obtain a lock going into it.

Thank you all once again for your help,

Drew

Message: 17
Date: Fri, 12 Aug 2011 19:28:33 +0100
From: Simon Slavin 
Subject: Re: [sqlite] Read only scaling optimization
To: General Discussion of SQLite Database 
Message-ID: 
Content-Type: text/plain; charset=us-ascii


On 12 Aug 2011, at 7:01pm, Drew Kozicki wrote:

> I have a Driver doing this pulling in 32 queries aimed at randomness and
> different tables, much like that would be experienced in typical usage.
Best
> performance comes from having 2 separate programs running on 2 separate
> files.

I'm no expert, but that suggests to me that your bottleneck is access to the
physical file on disk.  So your greatest speed increases will come not from
more threads but from a very fast hard disk drive, lots of hard drive
caching, etc..

That's a great set of benchmarks, by the way.

Simon.

--

Message: 18
Date: Fri, 12 Aug 2011 14:35:29 -0400
From: Pavel Ivanov 
Subject: Re: [sqlite] Read only scaling optimization
To: General Discussion of SQLite Database 
Message-ID:
   
Content-Type: text/plain; charset=ISO-8859-1

>> I have a Driver doing this pulling in 32 queries aimed at randomness and
>> different tables, much like that would be experienced in typical usage.
Best
>> performance comes from having 2 separate programs running on 2 separate
>> files.
>
> I'm no expert, but that suggests to me that your bottleneck is access to
the physical file on disk. ?So your greatest speed increases will come not
from more threads but from a very fast hard disk drive, lots of hard drive
caching, etc..

It's a little surprising to me that with all the same conditions 2
files residing on the same drive have better performance than the same
files residing on different drives. Theoretically that shouldn't
happen.


Pavel


Message: 2
Date: Sun, 14 Aug 2011 17:53:26 +0400
From: Alexey Pechnikov 
Subject: Re: [sqlite] Read only scaling optimization
To: General Discussion of SQLite Database 
Message-ID:
   
Content-Type: text/plain; charset=ISO-8859-1

2011/8/12 Pavel Ivanov :
> It's a little surprising to me that with all the same conditions 2
> files residing on the same drive have better performance than the same
> files residing on different drives. Theoretically that shouldn't
> happen.

Yes, it's not right behaviour. Is needed the sources of the test programm.

--
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Insert rows into a foreign key table

[Simon Slavin]

On 15 Aug 2011, at 12:58pm, Igor Tandetnik wrote:

> Black, Michael (IS)  wrote:
>> insert into orders (OrderNo,P_Id) select 12345,P_Id from persons where 
>> LastName='Hansen';
> 
> vs
> 
>> INSERT INTO ORDERS (OrderNo, P_Id)
>> values (
>> 12345,
>> select P_Id from persons where LastName = 'Hansen')
> 
> To the OP: note that there's a subtle difference, which may or may not matter 
> for your use case. If there are several records with LastName = 'Hansen', 
> then the first statement will insert a row into orders for each one of them; 
> if there are none, it won't insert anything.
> 
> The second statement (once corrected) always inserts one row, picking one of 
> the Hansens in an unpredictable manner, or inserts NULL if there are none.

which is why proper code should never do it in one instruction.  You do the 
SELECT first, then use your programming language to do the right thing for each 
of the three cases where 0, 1 or more records are returned.

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

Re: [sqlite] Insert rows into a foreign key table

[Black, Michael (IS)]

Good point...neither one is probably desirable...



When you get to this point in your code you should already have P_Id being 
passed around and not have to retrieve it.  Retrieving is dangerous as Igor has 
just described unless you have last name with a unique constraint (which would 
seem have far too much potential for duplicates).



You would want to catch the error where LastName does not exist so it probably 
should be in another select statement by itself.



Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Igor Tandetnik [itandet...@mvps.org]
Sent: Monday, August 15, 2011 6:58 AM
To: sqlite-users@sqlite.org
Subject: EXT :Re: [sqlite] Insert rows into a foreign key table

Black, Michael (IS)  wrote:
> insert into orders (OrderNo,P_Id) select 12345,P_Id from persons where 
> LastName='Hansen';

vs

> INSERT INTO ORDERS (OrderNo, P_Id)
> values (
> 12345,
> select P_Id from persons where LastName = 'Hansen')

To the OP: note that there's a subtle difference, which may or may not matter 
for your use case. If there are several records with LastName = 'Hansen', then 
the first statement will insert a row into orders for each one of them; if 
there are none, it won't insert anything.

The second statement (once corrected) always inserts one row, picking one of 
the Hansens in an unpredictable manner, or inserts NULL if there are none.
--
Igor Tandetnik

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

Re: [sqlite] Is it okay to use SQLite + PHP for Library Software?

[Simon Slavin]

On 15 Aug 2011, at 9:46am, Raouf Athar wrote:

> I have to develop a Library Management System using *PHP* for a medium sized
> college library. The library has about 5,000 members and 50,000 books. On an
> average, about 500 members will look for books and will be issued books on
> daily basis.
> 
> *Kindly let me know if it is okay to use SQLite?**Kindly add a few words in
> support of your answer.*
> *Can you give me links/ references of a few applications supported by
> SQLite?*

I run three PHP/SQLite that size or bigger than that myself, all hosted on the 
same Mac Pro.  I only have about 300 users a day at most, but there is no 
problem with that amount of data.

Use the SQLite3 object-oriented library for PHP.  Not PDO or any other method 
of accessing SQLite.  Perhaps start by writing yourself a little SQLite3 
library to do the standard things like execute INSERT and UPDATE, read a 
single-row SELECT, and read a multi-row SELECT.

Make sure you make up appropriate indexes for your SQL commands, so they run 
quickly.

Save a great deal of time by designing your user interface for HTML4 or HTML5, 
not Internet Explorer.  If IE has problems running it, tell them it's not IE 
compatible and have your users use FireFox/Chrome/Safari/whatever.

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

Re: [sqlite] Insert rows into a foreign key table

[Igor Tandetnik]

Black, Michael (IS)  wrote:
> insert into orders (OrderNo,P_Id) select 12345,P_Id from persons where 
> LastName='Hansen';

vs

> INSERT INTO ORDERS (OrderNo, P_Id)
> values (
> 12345,
> select P_Id from persons where LastName = 'Hansen')

To the OP: note that there's a subtle difference, which may or may not matter 
for your use case. If there are several records with LastName = 'Hansen', then 
the first statement will insert a row into orders for each one of them; if 
there are none, it won't insert anything.

The second statement (once corrected) always inserts one row, picking one of 
the Hansens in an unpredictable manner, or inserts NULL if there are none.
-- 
Igor Tandetnik

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

Re: [sqlite] Insert rows into a foreign key table

[Black, Michael (IS)]

insert into orders (OrderNo,P_Id) select 12345,P_Id from persons where 
LastName='Hansen';



Should do it for you.



Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of David Westbury [pe8...@yahoo.com]
Sent: Sunday, August 14, 2011 10:46 PM
To: sqlite-users@sqlite.org
Subject: EXT :[sqlite] Insert rows into a foreign key table

For the two tables shown here, what sqlite INSERT statement would add a new 
Order for person Hansen? Is it
possible to add a new order with one sql statement or does it require
two statements?

The only way I have found to add a new order is
to first SELECT the P_Id for Hansen from the Persons table. Then the new
 row containing Hansen's P_Id would be INSERTed into the Orders table.
But this seems like a function that sql should be able to perform in one
 statement.

For example, why won't an embedded select statement similar to the following 
work?

INSERT INTO ORDERS (OrderNo, P_Id)
values (
12345,
select P_Id from persons where LastName = 'Hansen')


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

Re: [sqlite] Insert rows into a foreign key table

[Igor Tandetnik]

David Westbury  wrote:
> For example, why won't an embedded select statement similar to the following 
> work?
> 
> INSERT INTO ORDERS (OrderNo, P_Id)
> values (
> 12345,
> select P_Id from persons where LastName = 'Hansen')

Because it's not valid SQL. Try this one:

INSERT INTO ORDERS (OrderNo, P_Id)
values (
12345,
(select P_Id from persons where LastName = 'Hansen'));

The nested select should be enclosed in parentheses.
-- 
Igor Tandetnik


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

[sqlite] Is it okay to use SQLite + PHP for Library Software?

[Raouf Athar]

Hi,

I have to develop a Library Management System using *PHP* for a medium sized
college library. The library has about 5,000 members and 50,000 books. On an
average, about 500 members will look for books and will be issued books on
daily basis.

*Kindly let me know if it is okay to use SQLite?**Kindly add a few words in
support of your answer.*
*Can you give me links/ references of a few applications supported by
SQLite?*

-- 

Regards,
Raouf Athar

[image: Hot Kashmir] 
_
The information contained in this electronic message and any attachments to
this message are intended for the exclusive use of the addressee(s) and may
contain proprietary, confidential or privileged information. If you are not
the intended recipient, you should not disseminate, distribute or copy this
e-mail. Please notify the sender immediately and destroy all copies of this
message and any attachments.

WARNING: Computer viruses can be transmitted via email. The recipient should
check this email and any attachments for the presence of viruses. The
company accepts no liability for any damage caused by any virus transmitted
by this email.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Insert rows into a foreign key table

[David Westbury]

For the two tables shown here, what sqlite INSERT statement would add a new 
Order for person Hansen? Is it 
possible to add a new order with one sql statement or does it require 
two statements?

The only way I have found to add a new order is 
to first SELECT the P_Id for Hansen from the Persons table. Then the new
 row containing Hansen's P_Id would be INSERTed into the Orders table. 
But this seems like a function that sql should be able to perform in one
 statement.

For example, why won't an embedded select statement similar to the following 
work?

INSERT INTO ORDERS (OrderNo, P_Id)
values (
12345,
select P_Id from persons where LastName = 'Hansen')


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