[freeside-devel] Fwd: Re: Error in cust_main.cgi

[ivan]

Considering these searches are for "all customers", "all pacakges" and   
"all services", I don't think the HEAP type is appropriate.

The only other concern I'd have would be on concurrent access; the "temp1"
tables would need to be constructed of a unique identifier.  Something
unique plus the current process id should work to prevent different apache
instances from trampling on each other's tables.

I look forward to a MySQL patch for search/cust_main.cgi,
search/cust_pkg.cgi and search/svc_acct.cgi.  Should be pretty
straightforward; import the datasrc subroutine from FS::UID:

    use FS::UID qw(datasrc);

then 

    if ( datasrc =~ /mysql/ ) {
       #set query to msyql query
    } else {
       #use regular query
    }

If I'm not mistaken, you'll also need to reverse the order of the
"ORDER BY" and "LIMIT..OFFSET" clauses for MySQL.

I've put all necessary information into ticket #300 in the bugtracking
system for anyone who's interested in working on this.
http://pouncequick.420.am/rt/Ticket/Display.html?id=300

Thanks!

On Mon, Feb 11, 2002 at 12:20:15PM -0600, Donald Greer wrote:
>    Oops,  sent this to the wrong list.
>    Don
> 
>      MySQL Users:
>      If somebody has FS 1.4.0 setup on MySQL (or is interested in setting
> it up to test this), please give the following a try.  I'm attempting to
> use the examples in the MySQL docs to translate the Select-Select stuff
> that Ivan uses to MySQL.
>      Two caveats: I don't have FS setup at the moment and I'm not an SQL
> wizard.
>      If somebody with MySQL and an experimental FS system would check this
> out and fix whatever I've probably broken and submit it to Ivan, I'd
> really appreciate it ... I don't want to have to manage anymore RDBMSs
> than I have to. ;^)
>     These may be completely off base, but hopefully they'll give somebody
> a starting point :^).  You could use a different table type, but if you
> have the memory, "HEAP" is definately the fastest and most closely
> emulates the SELECT-SELECT behaviour of other systems.
>     Ivan: FYI, the SELECT-SELECT functionality is on the devel schedule
> for MySQL 4.x.  Hopefully that will resolve the last big issue for FS on
> MySQL.
>     Don
> 
> 
>    From "http://www.sisd.com/freeside/list-archive/msg02482.html":
>    > SELECT * FROM cust_main
>    >      WHERE 0 < ( SELECT COUNT(*) FROM cust_pkg
>    >                    WHERE cust_pkg.custnum = cust_main.custnum
>    >                      AND ( cust_pkg.cancel IS NULL
>    >                            OR cust_pkg.cancel = 0
>    >                          )
>    >                )
>    >         OR 0 = ( SELECT COUNT(*) FROM cust_pkg
>    >                    WHERE cust_pkg.custnum = cust_main.custnum
>    >                )
>    >
> Becomes:
> --------------------
> CREATE TEMPORARY TABLE temp1 TYPE=HEAP
> 	SELECT cust_pkg.custnum,COUNT(*) as count
> 	FROM cust_pkg,cust_main
> 	WHERE cust_pkg.custnum = cust_main.custnum
> 	AND ( cust_pkg.cancel IS NULL
> 	OR cust_pkg.cancel = 0
> 	);
> CREATE TEMPORARY TABLE temp2 TYPE=HEAP
> 	SELECT cust_pkg.custnum,COUNT(*) as count
> 	FROM cust_pkg,cust_main
> 	WHERE cust_pkg.custnum = cust_main.custnum;
> SELECT cust_man.*
> 	FROM cust_main, temp1, temp2
> 	WHERE temp1.custnum = cust_main.custnum
> 	AND temp2.custnum = cust_main.custnum
> 	AND (temp1.count > 0
> 	OR temp2.count = 0 );
> DROP TABLE temp1,temp2;
> 
> 
> --------------------
>    >SELECT * FROM cust_pkg
>    >    WHERE 0 <
>    >    ( SELECT count(*) FROM pkg_svc
>    >          WHERE pkg_svc.pkgpart = cust_pkg.pkgpart
>    >            AND pkg_svc.quantity > ( SELECT count(*) FROM cust_svc
>    >                                       WHERE cust_svc.pkgnum =
>    >                                             cust_pkg.pkgnum
>    >                                         AND cust_svc.svcpart =
>    >                                             pkg_svc.svcpart
>    >                                   )
>    >      )
>    >
> Becomes:
> --------------------
> CREATE TEMPORARY TABLE temp1 TYPE=HEAP
> 	SELECT cust_svc.pkg_num,cust_svc.svcpart,COUNT(*) as count
> 	FROM cust_pkg,cust_svc,pkg_svc
> 	WHERE cust_pkg.pkgnum = cust_svc.pkgnum
> 	AND cust_svc.svcpart = pkg_svc.svcpart;
> CREATE TEMPORARY TABLE temp2 TYPE=HEAP
> 	SELECT cust_pkg.pkgpart,COUNT(*) as count
> 	FROM temp1,pkg_svc,cust_pkg
> 	WHERE cust_pkg.pkgpart = pkg_svc.pkgpart
> 	AND cust_pkg.pkgnum = temp1.pkg_num
> 	AND pkg_svc.svcpart = temp1.svcpart
> 	AND pkg_svc.quantity > temp1.count;
> SELECT cust_pkg.*
> 	FROM cust_pkg, temp2
> 	WHERE cust_pkg.pkgpart = temp2.pkgpart
> 	AND 0 < temp2.count;
> DROP TABLE temp1,temp2;
> 
> 
>    >SELECT * FROM svc_acct
>    >    WHERE 0 <
>    >    ( SELECT count(*) FROM cust_svc
>    >          WHERE cust_svc.svcnum = svc_acct.svcnum
>    >          AND pkgnum IS NULL
>    >    )
>    >
> CREATE TEMPORARY TABLE temp1 TYPE=HEAP
> 	SELECT cust_svc.svcnum,COUNT(*) as count
> 	FROM cust_svc,svc_acct
> 	WHERE cust_svc.svcnum = svc_acct.svcnum
> 	AND cust_svc.pkgnum IS NULL;
> SELECT svc_acct.*
> 	FROM svc_acct, temp2
> 	WHERE svc_acct.svcnum = temp2.svcnum
> 	AND 0 < temp2.count;
> DROP TABLE temp1;
> 
> 
> 
> 
> 
> 
> -- 
> --------------------------------------------------------
> Donald L. Greer, Jr                  dgreer at AustinTX.COM
> System Administrator                 Voice: 512-300-0176
> AustinTX                        http://www.AustinTX.COM/
>      All opinions are my own.  Flame me directly.
> 
> "I don't necessarily believe software should be free...
> but if you pay for it, it should work!" -- Me
> 
> 
> 
> 
> -- 
> --------------------------------------------------------
> Donald L. Greer, Jr                  dgreer at AustinTX.COM
> System Administrator                 Voice: 512-300-0176
> AustinTX                        http://www.AustinTX.COM/
>    All opinions are my own.  Flame me directly.
> 
> "I don't necessarily believe software should be free...
> but if you pay for it, it should work!" -- Me
> 

-- 
_ivan