Musing and digressing around sqlite

[Arran Cudbard-Bell]

On 8 Jul 2014, at 14:57, Axel Luttgens <axel.luttgens at skynet.be> wrote:

> Hello,
> 
> I'm testing FR with the slite backend, with the configs provided by:
> 
> 	raddb/mods-config/sql/main/sqlite/schema.sql
> 	raddb/mods-config/sql/main/sqlite/queries.conf
> 
> And I have some questions.
> So, if I may ask...
> 
> 1. Almost all tables have an id column, conveniently named "id" and defined as:
> 
> 	id int(11) PRIMARY KEY
> 
> Is there any reason for not having defined those columns as
> 
> 	id INTEGER PRIMARY KEY AUTOINCREMENT
> instead?

Nope.

> 
> 2. Incidentally, table "radusergroup" doesn't have such a column.
> An omission? Or could it prove problematic to define such a column?

It should probably have one as well.

> 3. The post-auth query is defined as:
> 
> 	query =	"\
> 		INSERT INTO ${..postauth_table} \
> 			(username, pass, reply, authdate) \
> 		VALUES ( \
> 			'%{SQL-User-Name}', \
> 			'%{%{User-Password}:-%{Chap-Password}}', \
> 			'%{reply:Packet-Type}', \
> 			 %{%{integer:Event-Timestamp}:-date('now')})"
> 
> and the log shows it expands as:
> 
> 	INSERT INTO radpostauth (username, pass, reply, authdate) VALUES ( 'bob', 'hello', 'Access-Accept', )
> 
> As a result, no row is inserted into table "radpostauth", since its column "authdate" is defined to be NOT NULL.

Could you provide the debug output please.

> How is the expression %{%{integer:Event-Timestamp}:-date('now')} supposed to be interpreted?
> More specifically, why the enclosing %{...}?

It's interpreted by the xlat parser. As integer:Event-Timestamp expands to a zero length string, data('now')
should be inserted into the query string instead...

I guess that's a bug.

> 4. Replacing above expression by '%S' works beautifully.
> I mean, the expansion for the query yields a timestamp such as "'2014-07-08 18:09:54'", and a row is now happily inserted into table "radpostauth". :-)
> What is the rationale for having devised a query based on Event-Timestamp at first hand?

It's adjusted for delay, whereas %S isn't.

If you're using the detail/reader writer combo using %S will produce invalid results.

If you're doing accounting Acct-Delay-Time will be taken into account when producing the value for Event-Timestamp also.

> 5. BTW, file http://networkradius.com/doc/FreeRADIUS-Implementation-Ch16.pdf describes %S as returning a timestamp with format "YYYY-mmm-ddd HH:MM:SS".
> Wouldn't something like "YYYY-MM-DD HH:MM:SS" be more accurate?

Probably.

> 
> 6. I've left the default setting
> 
> 	radius_db = "radius"
> 
> in raddb/mods-available/sql, even if my db file is named "radiusdb".
> Everything seems to be working fine.
> What is the role of variable "radius_db" in an sqlite context?

None.

The database file is set with

sql {

	sqlite {
		filename = "/path/to/file"
	}
}

> 7. Going back to question 3, this raises the usual question about sqlite in such cases.
> Would there be a way to get an explicit error message written to syslog?

Use linelog to log Module-Failure-Message attribute values.

-Arran

Arran Cudbard-Bell <a.cudbardb at freeradius.org>
FreeRADIUS Development Team

FD31 3077 42EC 7FCD 32FE 5EE2 56CF 27F9 30A8 CAA2

-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 881 bytes
Desc: Message signed with OpenPGP using GPGMail
URL: <http://lists.freeradius.org/pipermail/freeradius-users/attachments/20140708/68281932/attachment.pgp>