[HOME] [github] [twitter] [blog] [fml4] [fml8] [北海道] Powered by NetBSD and [nuinui.net] .

MySQL Map

For easy maintenance, it is better to write all mysql configurations in one file. For example, it is good that we have only to write SQL configurations in config.cf.

But we identify plural mysql conditions. So, we use the tag [mysql:members] to declare one region between a tag to the next tag or =cut. It is similar to .ini file format used on Microsoft OS. We use the tags like this:

config.cf example

member_maps     =       mysql:members

recipient_maps  =       mysql:recipients

[mysql:members]

sql_server      =       localhost
sql_user        =       fml
sql_password    =       uja
sql_database    =       fml
sql_table       =       ml

sql_find	=	select * from ...

		...

In calling IO::Adapter, use

new IO::Adapter "mysql:members", $config;
where $config is a hash reference holding some paremeters like this:
$config => {
	[mysql:members] => {
		sql_sever => localhost
			...	
	}
}
FML::Config prepares this $config by reading .cf files. Hence, we usually use FML::Config object as an argument of IO::Adapter::new() method.

Discussion: How To Write SQL Statements In config.cf ? (fml-devel 204)

How about lexical scope ? The .cf files cannot contain all variables since lexical scope variables exist. We use &varname syntax for such lexical scope variables.

For example, use different member and recipient maps. In SQL statements, the difference is a flag (fml_recipient) in a table. For example, consider the case that "where" statement has different value but it is determined lexically in calling MySQL.

member_maps 	= 	mysql:members

recipient_maps 	= 	mysql:recipients


[mysql:members]

sql_server	=	localhost
sql_user	=	fml
sql_password	=	uja
sql_database	=	fml
sql_table	=	ml

sql_get_next_key =	select fml_address from $sql_table
			where fml_ml = '$ml_name'
					and
				fml_domain = '$ml_domain'

sql_getline	=	select * from $sql_table
			where fml_ml = '$ml_name'
					and
				fml_domain = '$ml_domain'

sql_add		=	insert into $sql_table
			values ('$ml_name', '$ml_domain', '&address', 1, 1)

sql_delete	=	delete from $sql_table
			where	fml_ml = '$ml_name'
					and
				fml_domain = '$ml_domain'
					and
				fml_address = '&address'

sql_find	=	select * from $sql_table
			where	fml_ml = '$ml_name'
					and
				fml_domain = '$ml_domain'
					and
				fml_address like '&regexp'



[mysql:recipients]

sql_server	=	localhost
sql_user	=	fml
sql_password	=	uja
sql_database	=	fml
sql_table	=	ml

sql_get_next_key =	select fml_address from $sql_table
			where fml_ml = '$ml_name'
					and
				fml_domain = '$ml_domain'
					and
				fml_recipient = '1'

sql_getline	=	select * from $sql_table
			where fml_ml = '$ml_name'
					and
				fml_domain = '$ml_domain'
					and
				fml_recipient = '1'

sql_add		=	update ml
			set recipient = 1
			where fml_ml = '$ml_name'
					and
				fml_domain = '$ml_domain'
					and
				fml_address = '&address'

sql_delete	=	update ml
			set recipient = 0
			where fml_ml = '$ml_name'
					and
				fml_domain = '$ml_domain'
					and
				fml_address = '&address'


sql_find	=	select * from $sql_table
			where	fml_ml = '$ml_name'
					and
				fml_domain = '$ml_domain'
					and
				fml_recipient = '1'
					and
				fml_address like '&regexp'

[HOME] [github] [twitter] [blog] [fml4] [fml8] [北海道] Powered by NetBSD and [nuinui.net] .
Copyright (C) 1993-2022 Ken'ichi Fukamachi mail:< fukachan at fml.org >