[freeside] Freeside not SQL compliant?

[Ivan Kohler]

On Tue, Oct 19, 1999 at 08:30:27PM -0400, Stephen Amadei wrote:
> 
> Hello guys.  
> 
> First I wanted to tell Ivan and co, just how impressed I have been with
> the Freeside product.  I really like it and feel I'll like it alot more
> once I have it running.
> 
> Next, I'd like to give everyone a good laugh by explaining how I am
> attempting to use Freeside... (Iceskating uphill in Hell is close)
> 
> ... I am a DB2 programmer, usually in a OS/2 environment, so when I saw
> Freeside would work with any DBI interface, I was thrilled to find a DB2
> DBI.  I have since compiled my own fresh version of Perl, added the
> necessary Perl mods, set up DB2 and Lotus Go Webserver to run Freeside.
> The Freeside needed minor changes to accommodate OS/2... it didn't like
> the file's with ':' in them, so I changed them to '.'s and I needed to 
> change a couple paths from '/usr/whatever/freeside' to 'd:/freeside'.
> These changes should also allow Freeside to run under that lousy OS from 
> Redmond.
> 
> Basically, everything looks ready to go, but while running fs-setup,
> (well, fs-setup.cmd for me) the tables don't what to be created...
> alot of the labels (column names, table names, index names) are over the
> SQL limit of 18 characters

Yes, the column names can be quite long.

All column should be under <= 32 characters, unless you track radius
attributes longer than 10 characters. 

> and one includes the '_' character as the first character ( _date)... a
> SQL no-no.

That was actually a patch from the person who did the original PostgreSQL
port - the column used to be named just `date' (even *more* of an SQL
no-no).

Freeside itself was ported to DBI and SQL from a relational but non-SQL
database, before the initial open-source release. 

> I was wondering, since most RDBMs are somewhat SQL compliant, if there
> are plans for Freeside to be more SQL friendly, or if there is a newer
> SQL standard that MySQL and PostGres are using...

I'd certainly like for Freeside use standard SQL exclusively, but I
haven't yet done any work on this.

Depending on the radius attributes you decide to track with Freeside,
column names can get really long (especially in the part_svc table), i.e. 
'svc_acct__radius_Attribute_Name_flag'

(22 characters in addition to the length of the radius attribute).

The part_svc table is probably the biggest problem, actually.

> In any case, I'm just about to undertake a massive label-shortening
> campaign on my copy... ;-)

Good luck!  I'd certainly be interested in including these changes into
the main package, and, time permitting, could help with some of the
difficult bits.

-- 
Ivan Kohler <ivan at sisd.com> - finger for PGP key - <moc.dsis at navi> Relhok Navi
Open-source billing and administration for ISPs - http://www.sisd.com/freeside
20 4,16 * * * saytime # please don't be surprised if you find me dreaming too