sql-ledger-users

Re: [SQL-Ledger-users] LARGE INVENTORY

[Thomas S. <ps...@on...>]

Yes to #3!

After some more thought #3 is what I came up with too. That is to say:
It would be most convinent I think to simply put a limit on the number
of records returned.  There could be a criteria field adjusted from the
search page for maximum flexiblity where 0 or blank could mean return
all records and it could default to say 1000 or default from a new field
added to the user record(or where?) For now I'm just going to see if
I can hardcode a LIMIT of 1000 in the SQL statment. That is if Postgres
supports the LIMIT clause like MySQL does. Otherwise I'll have to put
a limit on the loop that generates the HTML. Either way, but the SQL
way would be more efficient.

 
Thomas Sawyer
ps...@on... - email


-- 
Thomas Sawyer
ps...@on... - email
(303) 285-3487 x1594 - voicemail/fax



---- Martin Lillepuu <ma...@li...> wrote:
> Thomas Sawyer wrote:
> 
> > I have to get this working with this a large inventory. So I'm looking
> > at adding some selection criteria limitations to the ic.cgi script.
> I'm
> > thinking of adding limiting criteria where one has to select the
> first
> > and second letter of the search. (i.e. Aa, Ab, Ac...Zx, Zy, Zz) How
> does
> > this sound to you all? Any other ideas?
> 
> maybe you should create a new parameter (eq. min_search_str_length)
> in
> configuration page, so people with small inventories (like me) can
> set
> it to 0 and in your case, it can be set to 2, 3 or whatever. then add
> code to ic.cgi that displays error message if search string is shorter
> than value specified in config. does this make any sense or am I
> over-complicating things again? :)
> 
> another idea to consider is grouping inventory items, so that you first
> select group and then only products belonging to specified group are
> displayed.
> 
> idea #3 - limit displaying inventory items on one page (does PostgreSQL
> have equivalent to MySQL LIMIT?). Maybe implement this throughout the
> program + another option to config page?
> 
> --
> Martin Lillepuu | E-mail: ma...@li... | GSM: 051 56 450
> 
> 

__________________________________________________
FREE voicemail, email, and fax...all in one place.
Sign Up Now! http://www.onebox.com

Re: [SQL-Ledger-users] LARGE INVENTORY

[Thomas S. <ps...@on...>]

I thought about that. In fact I was looking at Border's book search site
and they have a neat way of doing this by having links for each set at
the top of the page i.e. 1-50 | 51-100 | 101-150 ...etc. But its not
such a great idea I think b/c if you do a serach and your item ends up
on the 2nd or 3rd or 4th... page then you have to go through multiple
links (and hence waitings) to find it. Its much easier to scroll down
to find something and sql-ledger can load a 1,000 items pretty fast.

-- 
Thomas Sawyer
ps...@on... - email
(303) 285-3487 x1594 - voicemail/fax



---- =?iso-8859-1?q?Claudio=20Santana?= <tra...@ya...> wrote:
>    I think it would be better to slice this huge page in pages of n
> elements and then display just n elements and give to the user a next
> page link or button so we will just be selecting from the DB all the
> elements but the heavy operation that is generating the page will be
> done with a subset of all the elements. But in this case we should
> keep the state of the module, I mean the "next page" will mean
> different places every time we show it. So we could put that
> information in the "next page" link, we could include the query
> parameters and the next starting ID for the next page, and the same
> for "prev page" . Do I explain my self?
> 
> Claudio Santana.
> 
> --- Martin Lillepuu <ma...@li...> escribió: > Thomas Sawyer
> wrote:
> > 
> > > I have to get this working with this a large inventory. So I'm
> > looking
> > > at adding some selection criteria limitations to the ic.cgi
> > script. I'm
> > > thinking of adding limiting criteria where one has to select the
> > first
> > > and second letter of the search. (i.e. Aa, Ab, Ac...Zx, Zy, Zz)
> > How does
> > > this sound to you all? Any other ideas?
> > 
> > maybe you should create a new parameter (eq. min_search_str_length)
> > in
> > configuration page, so people with small inventories (like me) can
> > set
> > it to 0 and in your case, it can be set to 2, 3 or whatever. then
> > add
> > code to ic.cgi that displays error message if search string is
> > shorter
> > than value specified in config. does this make any sense or am I
> > over-complicating things again? :)
> > 
> > another idea to consider is grouping inventory items, so that you
> > first
> > select group and then only products belonging to specified group
> > are
> > displayed.
> > 
> > idea #3 - limit displaying inventory items on one page (does
> > PostgreSQL
> > have equivalent to MySQL LIMIT?). Maybe implement this throughout
> > the
> > program + another option to config page?
> > 
> > --
> > Martin Lillepuu | E-mail: ma...@li... | GSM: 051 56 450
> > 
> 
> 
> _________________________________________________________
> Do You Yahoo!?
> Obtenga su dirección de correo-e gratis @yahoo.com
> en http://correo.espanol.yahoo.com
> 
> 

__________________________________________________
FREE voicemail, email, and fax...all in one place.
Sign Up Now! http://www.onebox.com

Re: [SQL-Ledger-users] LARGE INVENTORY

[Matt B. <ma...@li...>]

Being able to scroll through long listings, possibly with a configurable
page size, seems like an (eventual) requirement.  I do not see the value
in getting just the top 1000 (or however many) entries.


Matt


Matt Benjamin President/CTO

The Linux Box
206 South Fifth Ave. Suite 150
Ann Arbor, MI  48104

tel. 734-761-4689
fax. 734-769-8938
pgr. 734-431-0118

On Tue, 6 Feb 2001, Thomas Sawyer wrote:

> I thought about that. In fact I was looking at Border's book search site
> and they have a neat way of doing this by having links for each set at
> the top of the page i.e. 1-50 | 51-100 | 101-150 ...etc. But its not
> such a great idea I think b/c if you do a serach and your item ends up
> on the 2nd or 3rd or 4th... page then you have to go through multiple
> links (and hence waitings) to find it. Its much easier to scroll down
> to find something and sql-ledger can load a 1,000 items pretty fast.
>
> --
> Thomas Sawyer
> ps...@on... - email
> (303) 285-3487 x1594 - voicemail/fax
>
>
>
> ---- =3D?iso-8859-1?q?Claudio=3D20Santana?=3D <tra...@ya...> wrote=
:
> >    I think it would be better to slice this huge page in pages of n
> > elements and then display just n elements and give to the user a next
> > page link or button so we will just be selecting from the DB all the
> > elements but the heavy operation that is generating the page will be
> > done with a subset of all the elements. But in this case we should
> > keep the state of the module, I mean the "next page" will mean
> > different places every time we show it. So we could put that
> > information in the "next page" link, we could include the query
> > parameters and the next starting ID for the next page, and the same
> > for "prev page" . Do I explain my self?
> >
> > Claudio Santana.
> >
> > --- Martin Lillepuu <ma...@li...> escribi=F3: > Thomas Sawyer
> > wrote:
> > >
> > > > I have to get this working with this a large inventory. So I'm
> > > looking
> > > > at adding some selection criteria limitations to the ic.cgi
> > > script. I'm
> > > > thinking of adding limiting criteria where one has to select the
> > > first
> > > > and second letter of the search. (i.e. Aa, Ab, Ac...Zx, Zy, Zz)
> > > How does
> > > > this sound to you all? Any other ideas?
> > >
> > > maybe you should create a new parameter (eq. min_search_str_length)
> > > in
> > > configuration page, so people with small inventories (like me) can
> > > set
> > > it to 0 and in your case, it can be set to 2, 3 or whatever. then
> > > add
> > > code to ic.cgi that displays error message if search string is
> > > shorter
> > > than value specified in config. does this make any sense or am I
> > > over-complicating things again? :)
> > >
> > > another idea to consider is grouping inventory items, so that you
> > > first
> > > select group and then only products belonging to specified group
> > > are
> > > displayed.
> > >
> > > idea #3 - limit displaying inventory items on one page (does
> > > PostgreSQL
> > > have equivalent to MySQL LIMIT?). Maybe implement this throughout
> > > the
> > > program + another option to config page?
> > >
> > > --
> > > Martin Lillepuu | E-mail: ma...@li... | GSM: 051 56 450
> > >
> >
> >
> > _________________________________________________________
> > Do You Yahoo!?
> > Obtenga su direcci=F3n de correo-e gratis @yahoo.com
> > en http://correo.espanol.yahoo.com
> >
> >
>
> __________________________________________________
> FREE voicemail, email, and fax...all in one place.
> Sign Up Now! http://www.onebox.com
>
>

Re: [SQL-Ledger-users] LARGE INVENTORY

[Thomas S. <ps...@on...>]

I see! You mean to offer both -- a user definable # or records returned,
but per page, and then links to the other pages (ie. back, next, 1-50,
51-100, etc.) Perfect!

So who wants to do it?

Also, Dieter Simader, where does version 1.4 stand? Is it close to realease?
How will our changes be effective with that new version? Do we just tell
you about them and you'll see about putting them in? Guess I'm a little
confused about the how's of this collabrotive open-source development
thing. I looked on ScourceForge and all I saw was the 1.2.5 release,
not even the current 1.2.7. Right now I'm becoming a SourceForge member.
I'm thinking, this might make a difference and I'd be able to find the
lastest in-development version (1.4). Is CVS being used here?


Thomas Sawyer
ps...@on... - email

---- Matt Benjamin <ma...@li...> wrote:
> 
> I like this suggestion.  It would allow you to browse all available
> data
> in user-configurable increments.
> 
> It is also a very familiar interface on the web, which would make it
> easy
> and comfortable for end-users to learn.  (Reading "Phillip and Alex's
> Guide to Web Publishing" has opened my eyes to the importance of
> easy-to-learn interfaces, I think.)
> 
> Matt
> 
> 
> Matt Benjamin President/CTO
> 
> The Linux Box
> 206 South Fifth Ave. Suite 150
> Ann Arbor, MI  48104
> 
> tel. 734-761-4689
> fax. 734-769-8938
> pgr. 734-431-0118
> 
> On Tue, 6 Feb 2001, [iso-8859-1] Claudio Santana wrote:
> 
> >    I think it would be better to slice this huge page in pages of
> n
> > elements and then display just n elements and give to the user a
> next
> > page link or button so we will just be selecting from the DB all
> the
> > elements but the heavy operation that is generating the page will
> be
> > done with a subset of all the elements. But in this case we should
> > keep the state of the module, I mean the "next page" will mean
> > different places every time we show it. So we could put that
> > information in the "next page" link, we could include the query
> > parameters and the next starting ID for the next page, and the same
> > for "prev page" . Do I explain my self?
> >
> > Claudio Santana.
> >
> > --- Martin Lillepuu <ma...@li...> escribió: > Thomas Sawyer
> > wrote:
> > >
> > > > I have to get this working with this a large inventory. So I'm
> > > looking
> > > > at adding some selection criteria limitations to the ic.cgi
> > > script. I'm
> > > > thinking of adding limiting criteria where one has to select
> the
> > > first
> > > > and second letter of the search. (i.e. Aa, Ab, Ac...Zx, Zy, Zz)
> > > How does
> > > > this sound to you all? Any other ideas?
> > >
> > > maybe you should create a new parameter (eq. min_search_str_length)
> > > in
> > > configuration page, so people with small inventories (like me)
> can
> > > set
> > > it to 0 and in your case, it can be set to 2, 3 or whatever. then
> > > add
> > > code to ic.cgi that displays error message if search string is
> > > shorter
> > > than value specified in config. does this make any sense or am
> I
> > > over-complicating things again? :)
> > >
> > > another idea to consider is grouping inventory items, so that you
> > > first
> > > select group and then only products belonging to specified group
> > > are
> > > displayed.
> > >
> > > idea #3 - limit displaying inventory items on one page (does
> > > PostgreSQL
> > > have equivalent to MySQL LIMIT?). Maybe implement this throughout
> > > the
> > > program + another option to config page?
> > >
> > > --
> > > Martin Lillepuu | E-mail: ma...@li... | GSM: 051 56 450
> > >
> >
> >
> > _________________________________________________________
> > Do You Yahoo!?
> > Obtenga su dirección de correo-e gratis @yahoo.com
> > en http://correo.espanol.yahoo.com
> >
> 
> 
> 

__________________________________________________
FREE voicemail, email, and fax...all in one place.
Sign Up Now! http://www.onebox.com

Re: [SQL-Ledger-users] LARGE INVENTORY

[Matt B. <ma...@li...>]

The feature is easy enough to code, and I'd be willing to, but it sounds
like Dieter needs a little more convincing of its utility :)


Matt


>
> So who wants to do it?
>

Re: [SQL-Ledger-users] LARGE INVENTORY

[Thomas S. <ps...@on...>]

Dieter,

I don't see how adding a character limit will help both sides in this.
How would you implement this? If you add a character limit then how does
someone pull up a full listing?

Honestly, I think the paging idea was right-on. And that should be the
direction to move in. I didn't realize it at first, but it would not
be that hard.

Here, follow this link and see http://www.oreillynet.com/lpt/a/448.

It is as simple as putting a LIMIT clause in the sql statement. Whether
its a fixed or variable limit is a matter of whether you want to put
a field on the search page for it or not. (Where 0 or blank could mean
all.) 

Yet I would like to point out that the poeple who say they don't want
a limit so they can pull up all their inventory are exactly the ones
who do not have a large inventory to pull up! That's why I think simply
putting a fixed limit of 1,000 for now would be a good compromise, without
much fuss.

-- 
Thomas Sawyer
ps...@on... - email

---- Dieter Simader <dsi...@sq...> wrote:
> Slicing output into pages does not work very well.
> 
> I'll add an option to limit searches by forcing users to enter a minimum
> number of characters for those who can't teach their users to enter
> something. This leaves the program the way it is for people who like
> to
> produce a complete inventory listing and limits output for those who
> need
> a limit.
> 
> It used to be in version 0.1, I'll add it again for version 1.4
> 
> 
> Dieter Simader    http://www.sql-ledger.org   (780) 472-8161
> DWS Systems Inc.     Accounting Software       Fax: 478-5281
> =========== On a clear disk you can seek forever ===========
> 
> On Tue, 6 Feb 2001, Matt Benjamin wrote:
> 
> > 
> > I like this suggestion.  It would allow you to browse all available
> data
> > in user-configurable increments.
> > 
> > It is also a very familiar interface on the web, which would make
> it easy
> > and comfortable for end-users to learn.  (Reading "Phillip and Alex's
> > Guide to Web Publishing" has opened my eyes to the importance of
> > easy-to-learn interfaces, I think.)
> > 
> > Matt
> > 
> > 
> > Matt Benjamin President/CTO
> > 
> > The Linux Box
> > 206 South Fifth Ave. Suite 150
> > Ann Arbor, MI  48104
> > 
> > tel. 734-761-4689
> > fax. 734-769-8938
> > pgr. 734-431-0118
> > 
> > On Tue, 6 Feb 2001, [iso-8859-1] Claudio Santana wrote:
> > 
> > >    I think it would be better to slice this huge page in pages
> of n
> > > elements and then display just n elements and give to the user
> a next
> > > page link or button so we will just be selecting from the DB all
> the
> > > elements but the heavy operation that is generating the page will
> be
> > > done with a subset of all the elements. But in this case we should
> > > keep the state of the module, I mean the "next page" will mean
> > > different places every time we show it. So we could put that
> > > information in the "next page" link, we could include the query
> > > parameters and the next starting ID for the next page, and the
> same
> > > for "prev page" . Do I explain my self?
> > >
> > > Claudio Santana.
> > >
> > > --- Martin Lillepuu <ma...@li...> escribió: > Thomas Sawyer
> > > wrote:
> > > >
> > > > > I have to get this working with this a large inventory. So
> I'm
> > > > looking
> > > > > at adding some selection criteria limitations to the ic.cgi
> > > > script. I'm
> > > > > thinking of adding limiting criteria where one has to select
> the
> > > > first
> > > > > and second letter of the search. (i.e. Aa, Ab, Ac...Zx, Zy,
> Zz)
> > > > How does
> > > > > this sound to you all? Any other ideas?
> > > >
> > > > maybe you should create a new parameter (eq. min_search_str_length)
> > > > in
> > > > configuration page, so people with small inventories (like me)
> can
> > > > set
> > > > it to 0 and in your case, it can be set to 2, 3 or whatever.
> then
> > > > add
> > > > code to ic.cgi that displays error message if search string is
> > > > shorter
> > > > than value specified in config. does this make any sense or am
> I
> > > > over-complicating things again? :)
> > > >
> > > > another idea to consider is grouping inventory items, so that
> you
> > > > first
> > > > select group and then only products belonging to specified group
> > > > are
> > > > displayed.
> > > >
> > > > idea #3 - limit displaying inventory items on one page (does
> > > > PostgreSQL
> > > > have equivalent to MySQL LIMIT?). Maybe implement this throughout
> > > > the
> > > > program + another option to config page?
> > > >
> > > > --
> > > > Martin Lillepuu | E-mail: ma...@li... | GSM: 051 56 450
> > > >
> > >
> > >
> > > _________________________________________________________
> > > Do You Yahoo!?
> > > Obtenga su dirección de correo-e gratis @yahoo.com
> > > en http://correo.espanol.yahoo.com
> > >
> > 
> > 
> > 
> 
> 
> 

__________________________________________________
FREE voicemail, email, and fax...all in one place.
Sign Up Now! http://www.onebox.com

Re: [SQL-Ledger-users] LARGE INVENTORY

[Thomas S. <ps...@on...>]

I think Dieter might have meant "in implementation". Paging would certainly
be a acceptable and sometimes useful feature, but it may not be so easy
to code. I'd rather see an effective compromise for now and get the new
version out rather then wait for the inventory search code to be completly
re-written.

-- 
Thomas Sawyer
ps...@on... - email
(303) 285-3487 x1594 - voicemail/fax



---- Matt Benjamin <ma...@li...> wrote:
> 
> Why not, exactly?  It seems like a sensible approach when you really
> do
> want to browse many pages of data.  That must be why even the most
> recent
> search engines provide this feature.
> 
> I certainly wouldn't substitute paging for search criteria, but again,
> sometimes you want to browse.
> 
> Matt
> 
> > Slicing output into pages does not work very well.
> >
> 
> 
> 

__________________________________________________
FREE voicemail, email, and fax...all in one place.
Sign Up Now! http://www.onebox.com

Re: [SQL-Ledger-users] LARGE INVENTORY

[Thomas S. <ps...@on...>]

Well for starters we simply need to create a start record count variable
and an end record count variable: $form->{startcnt} and $form->{endcnt}.
For now fix them to 0 and 1000, respectivly.

Then in the search_for_item function in the ic.cgi script replace:

$query = qq|SELECT id, number, description, onhand, unit, sellprice
                   FROM parts WHERE $where
                   ORDER BY $from->{sort}
           |;

with:

$query = qq|SELECT id, number, description, onhand, unit, sellprice
                   FROM parts WHERE $where
                   ORDER BY $from->{sort}
                   LIMIT $form->{endcnt}, $form-{startcnt}
           |;

This should work fine and open it up for adding pages down the line.

-- 
Thomas Sawyer
ps...@on... - email
(303) 285-3487 x1594 - voicemail/fax



---- Matt Benjamin <ma...@li...> wrote:
> 
> The feature is easy enough to code, and I'd be willing to, but it sounds
> like Dieter needs a little more convincing of its utility :)
> 
> 
> Matt
> 
> 
> >
> > So who wants to do it?
> >
> 
> 
> 

__________________________________________________
FREE voicemail, email, and fax...all in one place.
Sign Up Now! http://www.onebox.com

Re: [SQL-Ledger-users] LARGE INVENTORY

[<dr...@ss...>]

In message <Pin...@ti...>, "Roderick A.
 Anderson" writes:
> On Tue, 6 Feb 2001, Andrew Sharp wrote:
> 
> >     if($CONFIG{sqllimit} == 1) {
> >         # this presuposes a limit statement in postgresql, but
> >         # I think it has them.  If it doesn't it can be coded
> >         # around anyway.
> 
> PostgreSQL does have the limit clause which I believe is in the SQL92 and
> SQL99 standards.  It's easy to get into the MySQL (choose your own
> database) has this feature or that thingy but for the most part PostgreSQL
> follows the standands better than the other databases.
> 
>    The problem with the limit statement is it is 'stateless'.  It picks
> the first 'n' records that meet the rest of the selection criteria.  
> There is no easy way to remember the current 'n' items at the database
> level so the the next SELECT statement can pick the next 'n' items.

There is a way to pick the next set.  Use the offset keyword.

select * from table where criteria offset START limit LENGTH

-- 
  -- Drew Sullivan, <dr...@ss...> -- Toronto, Canada,  +1-416-512-2311
     Copyleft--all rights reversed

Re: [SQL-Ledger-users] LARGE INVENTORY

[Matt B. <ma...@li...>]

Seems like this works for MySQL or Postgres.  With Oracle, you can do
things with ROWNUM to similar effect.

Matt


Matt Benjamin President/CTO

The Linux Box
206 South Fifth Ave. Suite 150
Ann Arbor, MI  48104

tel. 734-761-4689
fax. 734-769-8938
pgr. 734-431-0118

On Wed, 7 Feb 2001 dr...@ss... wrote:


>
> There is a way to pick the next set.  Use the offset keyword.
>
> select * from table where criteria offset START limit LENGTH
>
> --
>   -- Drew Sullivan, <dr...@ss...> -- Toronto, Canada,  +1-416-512-2311
>      Copyleft--all rights reversed
>

Re: [SQL-Ledger-users] LARGE INVENTORY

[Scott T. <gst...@te...>]

dr...@ss... wrote:
> 
> In message <Pin...@ti...>, "Roderick A.
>  Anderson" writes:
> > On Tue, 6 Feb 2001, Andrew Sharp wrote:
> >
> > >     if($CONFIG{sqllimit} == 1) {
> > >         # this presuposes a limit statement in postgresql, but
> > >         # I think it has them.  If it doesn't it can be coded
> > >         # around anyway.
> >
> > PostgreSQL does have the limit clause which I believe is in the SQL92 and
> > SQL99 standards.  It's easy to get into the MySQL (choose your own
> > database) has this feature or that thingy but for the most part PostgreSQL
> > follows the standands better than the other databases.
> >
> >    The problem with the limit statement is it is 'stateless'.  It picks
> > the first 'n' records that meet the rest of the selection criteria.
> > There is no easy way to remember the current 'n' items at the database
> > level so the the next SELECT statement can pick the next 'n' items.
> 
> There is a way to pick the next set.  Use the offset keyword.
> 
> select * from table where criteria offset START limit LENGTH
> 

Hi, 

phpGroupWare does this with the todo list (and other apps) using PHP and
MySQL or PostgreSQL.  Converting to CGI/Perl input shouldn't be too
difficult. 
Check it out at http://www.phpgroupware.org/  it's still very much in
the developement stage, I would suggest rel 0.9.1 or 0.9.2, the newer
ones seem to be more buggy. :o(

Regards.

Re: [SQL-Ledger-users] LARGE INVENTORY

[Thomas S. <ps...@on...>]

Postgresql does have a limit clause. It is different from MySQL in that
it uses reversed order. Ex. Lets say you have a large inventory, like
I do, then query:

SELECT * FROM parts LIMIT 1000, 0;

This will return records 0 (the first) through the 1,000th.

For the next set of records we would query:

SELECT * FROM parts LIMIT 2000, 1000;

and get the 1000th though the 2000th records. There would only ever be
a descrepency in the returned records if someone added or deleted records
between these queries.

I have already placed this limiting code in to sql-ledger's ic.cgi script
without any errors being reported, though I have yet to fully test it.
I'll let you know.

Given that this limit clause works okay then this is probably a bit better
than script proccessing the top n returned records, and the next n records,
because each time you'd still have to query all the records. Not that
much more overhead for such a capable database as postgresql but still
more overhead.

-- 
Thomas Sawyer
ps...@on... - email
(303) 285-3487 x1594 - voicemail/fax



---- "Roderick A. Anderson" <raa...@ti...> wrote:
> On Tue, 6 Feb 2001, Andrew Sharp wrote:
> 
> >     if($CONFIG{sqllimit} == 1) {
> >         # this presuposes a limit statement in postgresql, but
> >         # I think it has them.  If it doesn't it can be coded
> >         # around anyway.
> 
> PostgreSQL does have the limit clause which I believe is in the SQL92
> and
> SQL99 standards.  It's easy to get into the MySQL (choose your own
> database) has this feature or that thingy but for the most part PostgreSQL
> follows the standands better than the other databases.
> 
>    The problem with the limit statement is it is 'stateless'.  It picks
> the first 'n' records that meet the rest of the selection criteria.
>  
> There is no easy way to remember the current 'n' items at the database
> level so the the next SELECT statement can pick the next 'n' items.
> 
>    The better choice is to select all the records that meet the criteria
> -
> using the DBI - and break them up into groups/bunches to display using
> a
> display limit.
> 
> Since I've only been half following the discussion (subscribed to too
> many
> other mailing lists - including two of the PostgreSQL lists) I'm not
> sure
> where the 'convince Dieter' discussion is at currently.  I was mostly
> concerned over the repeated question about PostgreSQL having the LIMIT
> clause but then realized the LIMIT discussion could be going down the
> wrong road.
> 
> I hope that we convince Dieter to add some method of limiting the
> selection and displayed list so please don't take this as a rebuke.
> 
> 
> Good Computing,
> Rod
> -- 
> I really need a signature block!
> 
> 
> 
> 
> 

__________________________________________________
FREE voicemail, email, and fax...all in one place.
Sign Up Now! http://www.onebox.com

Re: [SQL-Ledger-users] LARGE INVENTORY

[Thomas S. <ps...@on...>]

Well what do you know, three way to do it!

If the offset works for both Postgres and MySQl then maybe that's the
best way to go.

-- 
Thomas Sawyer
ps...@on... - email
(303) 285-3487 x1594 - voicemail/fax



---- Matt Benjamin <ma...@li...> wrote:
> 
> Seems like this works for MySQL or Postgres.  With Oracle, you can
> do
> things with ROWNUM to similar effect.
> 
> Matt
> 
> 
> Matt Benjamin President/CTO
> 
> The Linux Box
> 206 South Fifth Ave. Suite 150
> Ann Arbor, MI  48104
> 
> tel. 734-761-4689
> fax. 734-769-8938
> pgr. 734-431-0118
> 
> On Wed, 7 Feb 2001 dr...@ss... wrote:
> 
> 
> >
> > There is a way to pick the next set.  Use the offset keyword.
> >
> > select * from table where criteria offset START limit LENGTH
> >
> > --
> >   -- Drew Sullivan, <dr...@ss...> -- Toronto, Canada,  +1-416-512-2311
> >      Copyleft--all rights reversed
> >
> 
> 
> 

__________________________________________________
FREE voicemail, email, and fax...all in one place.
Sign Up Now! http://www.onebox.com

Re: [SQL-Ledger-users] LARGE INVENTORY

[Roderick A. A. <raa...@ti...>]

On Wed, 7 Feb 2001, Thomas Sawyer wrote:

> Well what do you know, three way to do it!

And being wrong helped get an answer. :-)


Well I usually use LIMIT to test queries that would normally return a HUGE
set.  Plus I'm just getting back to using SQL.  My last major experience
was three years ago and it was with Oracle.  So wrong is what I do good!

> If the offset works for both Postgres and MySQl then maybe that's the
> best way to go.

Learn something new all the time.


Rod

Re: [SQL-Ledger-users] LARGE INVENTORY

[Thomas S. <ps...@on...>]

Well, I think we've really put this one to rest, and I think Dieter will
have to be duely impressed enough to develop the search code portions
of sql-ledger in the direction of record count limits on navigational
pages. i.e. n pages of m records each. And like I said the 'offset' method
seems most appropriate esspecially since it encodes in a like fashion
with an offset and count. That is to say:
 
SELECT * FROM table OFFSET current-pg-number * recs-per-page LIMIT recs-per-page;

Agreed? Of course I bet Matt Benjamin is nearly done with coding this
already. Right? Else, do you want to go ahead? Let me know.

P.S. FYI I'm nealy done with a Sales Order module, simply based after
the invoice module but with accounting activities removed. More soon...

-- 
Thomas Sawyer
ps...@on... - email
(303) 285-3487 x1594 - voicemail/fax



---- Scott Taylor <gst...@te...> wrote:
> dr...@ss... wrote:
> > 
> > In message <Pin...@ti...>,
> "Roderick A.
> >  Anderson" writes:
> > > On Tue, 6 Feb 2001, Andrew Sharp wrote:
> > >
> > > >     if($CONFIG{sqllimit} == 1) {
> > > >         # this presuposes a limit statement in postgresql, but
> > > >         # I think it has them.  If it doesn't it can be coded
> > > >         # around anyway.
> > >
> > > PostgreSQL does have the limit clause which I believe is in the
> SQL92 and
> > > SQL99 standards.  It's easy to get into the MySQL (choose your
> own
> > > database) has this feature or that thingy but for the most part
> PostgreSQL
> > > follows the standands better than the other databases.
> > >
> > >    The problem with the limit statement is it is 'stateless'. 
> It picks
> > > the first 'n' records that meet the rest of the selection criteria.
> > > There is no easy way to remember the current 'n' items at the database
> > > level so the the next SELECT statement can pick the next 'n' items.
> > 
> > There is a way to pick the next set.  Use the offset keyword.
> > 
> > select * from table where criteria offset START limit LENGTH
> > 
> 
> Hi, 
> 
> phpGroupWare does this with the todo list (and other apps) using PHP
> and
> MySQL or PostgreSQL.  Converting to CGI/Perl input shouldn't be too
> difficult. 
> Check it out at http://www.phpgroupware.org/  it's still very much
> in
> the developement stage, I would suggest rel 0.9.1 or 0.9.2, the newer
> ones seem to be more buggy. :o(
> 
> Regards.
> 
> 

__________________________________________________
FREE voicemail, email, and fax...all in one place.
Sign Up Now! http://www.onebox.com

Re: [SQL-Ledger-users] LARGE INVENTORY

[Dieter S. <dsi...@sq...>]

You get the source code and you can modify it to your liking. If you want
to see some features added or want to make a contribution submit your
additions/modifications for inclusion in the distribution. Others have
done so in the past.

Having said that, there is, however, no guarantee that your changes will
be included.  Some code makes it, some does not and some shows up in a
revised form. It's your call.


Dieter Simader    http://www.sql-ledger.org   (780) 472-8161
DWS Systems Inc.     Accounting Software       Fax: 478-5281
=========== On a clear disk you can seek forever ===========

On Wed, 7 Feb 2001, Thomas Sawyer wrote:

> Well, I think we've really put this one to rest, and I think Dieter will
> have to be duely impressed enough to develop the search code portions
> of sql-ledger in the direction of record count limits on navigational
> pages. i.e. n pages of m records each. And like I said the 'offset' method
> seems most appropriate esspecially since it encodes in a like fashion
> with an offset and count. That is to say:
>  
> SELECT * FROM table OFFSET current-pg-number * recs-per-page LIMIT recs-per-page;
> 
> Agreed? Of course I bet Matt Benjamin is nearly done with coding this
> already. Right? Else, do you want to go ahead? Let me know.
> 
> P.S. FYI I'm nealy done with a Sales Order module, simply based after
> the invoice module but with accounting activities removed. More soon...
> 
>