[freeside-devel] LAST call for MySQL support

[Donald L. Greer Jr.]

  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;