[freeside] Re: [freeside-devel] LAST call for MySQL support

[ivan]

httemplate/search/cust_main.cgi
httemplate/search/cust_pkg.cgi
httemplate/search/svc_acct.cgi

On Sun, Mar 31, 2002 at 10:48:02AM +0800, Shane Chrisp wrote:
> Which files do I find these queries in so that I can test them on MySQL?
> I still
> have a system with freeside and MySQL on.
> 
> Shane
> 
> -----Original Message-----
> From: Donald L. Greer Jr. [mailto:dgreer at austintx.com] 
> Sent: Sunday, 31 March 2002 2:50 AM
> To: ivan-freeside-devel at sisd.com; ivan-freeside at 420.am;
> ivan-freeside-devel at 420.am
> Subject: [freeside] Re: [freeside-devel] LAST call for MySQL support
> 
> 
>   Folks,
>   I submitted some untested code to Ivan, but I've had no chance to test
> it
> (and won't before 1.4).  If somebody has already got this thing setup
> with
> Mysql, please try this code and debug it.
>   Below I'll past the code below.
>   Don
> 
> ----- Original Message -----
> From: "ivan" <ivan at 420.am>
> To: <ivan-freeside at 420.am>; <ivan-freeside-devel at 420.am>
> Sent: Monday, March 25, 2002 6:02 AM
> Subject: [freeside-devel] LAST call for MySQL support
> 
> 
> > I'm getting very close to releasing pre12, and if it survives a little
> > bit, calling it alpha1 and working towards a 1.4.0 release fairly
> soon.
> > Now that the export rewrite is underway, the list of remaining 1.4.0
> > issues is very small.
> >
> > I'll be publicizing the 1.4.0 alphas/betas/release more than the preXX
> > releases.
> >
> > If the MySQL folks want to see MySQL support in Freeside, they need to
> > wake up and contribute support to work around MySQL's lack of
> subqueries.
> > If no one does this, 1.4.0 WILL NOT SUPPORT MYSQL.
> >
> > --
> > _ivan
> 
> *** My Code ***
> 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;
> 
> 
> 
> 
> 

-- 
_ivan