[freeside-devel] mysql 5

[Ivan Kohler]

On Fri, Aug 18, 2006 at 02:33:01PM +1000, Jason Thomas wrote:
> Hi all,
> 
> Here's a couple of patches to get freeside working with mysql 5.
> 
> This works but I'm not 100% sure its the right solution.

Well, it does break Pg - you can't select indeterminate rows from a 
GROUPed query - that seems to be a MySQLism.  Compare 
http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-fields.html vs 
http://www.postgresql.org/docs/8.1/interactive/sql-select.html#SQL-GROUPBY

I think that selecting just the row should work in both databases, i.e. 
"SELECT state" rather than "SELECT *".

On HEAD - you'll want to patch the the state search in the states_hash 
subroutine in FS/FS/Misc.pm.  You might want to work on HEAD rather than 
on 1.5.8 if you are intersted in a long-term MySQL port.

Also, what do you think about a MySQL equivalent for "LOCK TABLE 
svc_acct IN SHARE ROW EXCLUSIVE MODE" statement in the _check_duplicate 
subroutine in FS/FS/svc_acct.pm?  (See 
http://www.postgresql.org/docs/8.1/interactive/explicit-locking.html#LOCKING-TABLES) 
Basically we need a database mutex that will only let this section of 
code run once from multiple clients, and will block all updates to the 
table while it is running.

-- 
_ivan


> --- select-state.html.orig	2006-08-18 14:17:01.000000000 +1000
> +++ select-state.html	2006-08-18 14:17:56.000000000 +1000
> @@ -14,7 +14,8 @@
>  %     map { $_->state }
>  %     qsearch( 'cust_main_county',
>  %              { 'country' => $opt{'country'} },
> -%              'DISTINCT ON ( state ) *',
> +%              '*',
> +%              'GROUP BY state',
>  %            )
>  %   ) {
>  %
> 
> 
> 
> --- select-country.html.orig	2006-08-18 14:29:08.000000000 +1000
> +++ select-country.html	2006-08-18 14:30:28.000000000 +1000
> @@ -60,7 +60,7 @@
>  % foreach my $country (
>  %     sort { ($b eq $countrydefault) <=> ($a eq $countrydefault) or $a cmp $b }
>  %     map { $_->country }
> -%     qsearch( 'cust_main_county',{}, 'DISTINCT ON ( country ) *', )
> +%     qsearch( 'cust_main_county',{}, '*', 'GROUP BY country', )
>  %   ) {
>  %
>  
> 
> -- 
> Jason Thomas
> Link Innovations - 02 9634 0400
> http://www.linkinnovations.com/
> _______________________________________________
> freeside-devel mailing list
> freeside-devel at sisd.com
> http://420.am/cgi-bin/mailman/listinfo/freeside-devel