Musing and digressing around sqlite

[Axel Luttgens]

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?

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

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.

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

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?

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?

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?

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?


Many thanks in advance,
Axel