trunk/pkgtools/mage2mysql.sh

#!/bin/sh
# mage2mysql

# mage functions
source /home/tjoke/svn/alx/alx-src/trunk/tinyalxconfig-ng/functions/mysqlfunctions
source /usr/lib/mage/mage4.functions.sh
source /etc/mage.rc.global
source /etc/mage.rc

# sql settings
SQL_USER=mage
SQL_PASS=mage
SQL_HOST=localhost
SQL_DB=mage
SQL_TABLE_PACKAGES=mg_packages
SQL_TABLE_CATEGORIES=mg_categories

create_database()
{
	# create categories table
	mysqldo "CREATE TABLE IF NOT EXISTS ${SQL_TABLE_CATEGORIES} (
			id int(10) NOT NULL auto_increment,
			pcat varchar(255) NOT NULL,
			PRIMARY KEY  (id)
		)"

	# create packages table
	mysqldo "CREATE TABLE IF NOT EXISTS ${SQL_TABLE_PACKAGES} (
			id int(10) NOT NULL auto_increment,
			pname varchar(255) NOT NULL,
			pver varchar(255) NOT NULL,
			pbuild varchar(255) NOT NULL,
			pcat varchar(255) NOT NULL,
			state varchar(255) NOT NULL,
			description text default NULL,
			homepage varchar(255) default NULL,
			pkgtype varchar(255) default NULL,
			depend text default NULL,
			sdepend text default NULL,
			provide text default NULL,
			time datetime NOT NULL,
			flag int(1) NOT NULL default '1',
			PRIMARY KEY  (id)
		)"
}

evaluate_categories()
{
	local i
	local pcat

	# ${MAGEDIR} is known from mage.rc{,.global}
	for i in ${MAGEDIR}/*
	do
		pcat="${i##*/}"

		# exclude includes
		[[ ${pcat} = include ]] && continue

		id=$(mysqldo "SELECT id FROM ${SQL_TABLE_CATEGORIES} WHERE pcat='${pcat}'")

		# only add if not found
		if [[ -z ${id} ]]
		then
			mysqldo "INSERT INTO ${SQL_TABLE_CATEGORIES} ( pcat ) VALUES ( '${pcat}' )"
		fi
	done
}

evaluate_magefile()
{
	local i
	local MAGEFILE="$1"
	local PKGNAME
	local PCAT
	local PNAME
	local PVER
	local PBUILD
	local STATE
	local DESCRIPTION
	local HOMEPAGE
	local PKGTYPE
	local DEPEND
	local SDEPEND
	local PROVIDE

	# read needed variables within the magefile
	for i in PKGNAME STATE HOMEPAGE PKGTYPE DEPEND SDEPEND PROVIDE
	do
		eval "${i}='$(get_value_from_magefile ${i} ${MAGEFILE})'"
	done

	# the description may contain some escape characters like ' and "
	# which break the above loop
	DESCRIPTION="$(get_value_from_magefile DESCRIPTION ${MAGEFILE} | sed "s:':\\\':g")"

	# get var from the name of the magefile
	PCAT="$(magename2pcat ${MAGEFILE})"
	PNAME="$(magename2pname ${MAGEFILE})"
	PVER="$(magename2pver ${MAGEFILE})"
	PBUILD="$(magename2pbuild ${MAGEFILE})"

	# remove >= etc from depend and sdepend
	DEPEND=$(echo ${DEPEND} | sed 's/[^a-zA-Z0-9]\{2\}\ //g')
	SDEPEND=$(echo ${SDEPEND} | sed 's/[^a-zA-Z0-9]\{2\}\ //g')

	# check if there is a pname for the same state
	local id=$(mysqldo "SELECT id FROM ${SQL_TABLE_PACKAGES} WHERE pname='${PNAME}' AND state='${STATE}'")

	# get categorie id
	local pcatid=$(mysqldo "SELECT id FROM ${SQL_TABLE_CATEGORIES} WHERE pcat='${PCAT}'")

	# get current time
	local time="$(date +%F\ %T)"

#get last primary key !!!

	# found, then update
	if [[ ! -z ${id} ]]
	then
		mysqldo "UPDATE ${SQL_TABLE_PACKAGES} SET
				pname='${PNAME}',
				pver='${PVER}',
				pbuild='${PBUILD}',
				pcat='${pcatid}',
				state='${STATE}',
				description='${DESCRIPTION}',
				homepage='${HOMEPAGE}',
				pkgtype='${PKGTYPE}',
				depend='${DEPEND}',
				sdepend='${SDEPEND}',
				provide='${PROVIDE}',
				time='${time}',
				flag='1'
			WHERE id='${id}'"

	else
		# else import to database as new
		mysqldo "INSERT INTO ${SQL_TABLE_PACKAGES} (
				id,
				pname,
				pver,
				pbuild,
				pcat,
				state,
				description,
				homepage,
				pkgtype,
				depend,
				sdepend,
				provide,
				time,
				flag
			)
			VALUES (
				'${id}',
				'${PNAME}',
				'${PVER}',
				'${PBUILD}',
				'${pcatid}',
				'${STATE}',
				'${DESCRIPTION}',
				'${HOMEPAGE}',
				'${PKGTYPE}',
				'${DEPEND}',
				'${SDEPEND}',
				'${PROVIDE}',
				'${time}',
				'1'
			) ON DUPLICATE KEY UPDATE ${SQL_TABLE_PACKAGES} SET
				pname='${PNAME}',
				pver='${PVER}',
				pbuild='${PBUILD}',
				pcat='${pcatid}',
				state='${STATE}',
				description='${DESCRIPTION}',
				homepage='${HOMEPAGE}',
				pkgtype='${PKGTYPE}',
				depend='${DEPEND}',
				sdepend='${SDEPEND}',
				provide='${PROVIDE}',
				time='${time}',
				flag='1'
			WHERE id='${id}'"
	fi
}

reset_flags()
{
	# set all flags to zero
	mysqldo "UPDATE ${SQL_TABLE_PACKAGES} SET flag='0'"
}

prune_old_packages()
{
	# debug
#	echo "Debug: following ids are requested for deletion:"
#	mysqldo "SELECT id FROM packages WHERE flag='0'"

	# delete them
	mysqldo "DELETE FROM ${SQL_TABLE_PACKAGES} WHERE flag='0'"
}

echo "Processing Categories ..."
evaluate_categories

echo "Resetting Flags ..."
reset_flags

# all magefiles
ALL_MAGEFILES="$(find ${MAGEDIR} -name \*.mage | sort)"

for magefile in ${ALL_MAGEFILES}
do
	echo "Processing Magefile ${magefile}"
	evaluate_magefile ${magefile}
done

echo "Pruning outdated Packages ..."
prune_old_packages
1	#!/bin/sh
2	# mage2mysql
3
4	# mage functions
5	source /home/tjoke/svn/alx/alx-src/trunk/tinyalxconfig-ng/functions/mysqlfunctions
6	source /usr/lib/mage/mage4.functions.sh
7	source /etc/mage.rc.global
8	source /etc/mage.rc
9
10	# sql settings
11	SQL_USER=mage
12	SQL_PASS=mage
13	SQL_HOST=localhost
14	SQL_DB=mage
15	SQL_TABLE_PACKAGES=mg_packages
16	SQL_TABLE_CATEGORIES=mg_categories
17
18	create_database()
19	{
20	# create categories table
21	mysqldo "CREATE TABLE IF NOT EXISTS ${SQL_TABLE_CATEGORIES} (
22	id int(10) NOT NULL auto_increment,
23	pcat varchar(255) NOT NULL,
24	PRIMARY KEY (id)
25	)"
26
27	# create packages table
28	mysqldo "CREATE TABLE IF NOT EXISTS ${SQL_TABLE_PACKAGES} (
29	id int(10) NOT NULL auto_increment,
30	pname varchar(255) NOT NULL,
31	pver varchar(255) NOT NULL,
32	pbuild varchar(255) NOT NULL,
33	pcat varchar(255) NOT NULL,
34	state varchar(255) NOT NULL,
35	description text default NULL,
36	homepage varchar(255) default NULL,
37	pkgtype varchar(255) default NULL,
38	depend text default NULL,
39	sdepend text default NULL,
40	provide text default NULL,
41	time datetime NOT NULL,
42	flag int(1) NOT NULL default '1',
43	PRIMARY KEY (id)
44	)"
45	}
46
47	evaluate_categories()
48	{
49	local i
50	local pcat
51
52	# ${MAGEDIR} is known from mage.rc{,.global}
53	for i in ${MAGEDIR}/*
54	do
55	pcat="${i##*/}"
56
57	# exclude includes
58	[[ ${pcat} = include ]] && continue
59
60	id=$(mysqldo "SELECT id FROM ${SQL_TABLE_CATEGORIES} WHERE pcat='${pcat}'")
61
62	# only add if not found
63	if [[ -z ${id} ]]
64	then
65	mysqldo "INSERT INTO ${SQL_TABLE_CATEGORIES} ( pcat ) VALUES ( '${pcat}' )"
66	fi
67	done
68	}
69
70	evaluate_magefile()
71	{
72	local i
73	local MAGEFILE="$1"
74	local PKGNAME
75	local PCAT
76	local PNAME
77	local PVER
78	local PBUILD
79	local STATE
80	local DESCRIPTION
81	local HOMEPAGE
82	local PKGTYPE
83	local DEPEND
84	local SDEPEND
85	local PROVIDE
86
87	# read needed variables within the magefile
88	for i in PKGNAME STATE HOMEPAGE PKGTYPE DEPEND SDEPEND PROVIDE
89	do
90	eval "${i}='$(get_value_from_magefile ${i} ${MAGEFILE})'"
91	done
92
93	# the description may contain some escape characters like ' and "
94	# which break the above loop
95	DESCRIPTION="$(get_value_from_magefile DESCRIPTION ${MAGEFILE} \| sed "s:':\\\':g")"
96
97	# get var from the name of the magefile
98	PCAT="$(magename2pcat ${MAGEFILE})"
99	PNAME="$(magename2pname ${MAGEFILE})"
100	PVER="$(magename2pver ${MAGEFILE})"
101	PBUILD="$(magename2pbuild ${MAGEFILE})"
102
103	# remove >= etc from depend and sdepend
104	DEPEND=$(echo ${DEPEND} \| sed 's/[^a-zA-Z0-9]\{2\}\ //g')
105	SDEPEND=$(echo ${SDEPEND} \| sed 's/[^a-zA-Z0-9]\{2\}\ //g')
106
107	# check if there is a pname for the same state
108	local id=$(mysqldo "SELECT id FROM ${SQL_TABLE_PACKAGES} WHERE pname='${PNAME}' AND state='${STATE}'")
109
110	# get categorie id
111	local pcatid=$(mysqldo "SELECT id FROM ${SQL_TABLE_CATEGORIES} WHERE pcat='${PCAT}'")
112
113	# get current time
114	local time="$(date +%F\ %T)"
115
116	#get last primary key !!!
117
118	# found, then update
119	if [[ ! -z ${id} ]]
120	then
121	mysqldo "UPDATE ${SQL_TABLE_PACKAGES} SET
122	pname='${PNAME}',
123	pver='${PVER}',
124	pbuild='${PBUILD}',
125	pcat='${pcatid}',
126	state='${STATE}',
127	description='${DESCRIPTION}',
128	homepage='${HOMEPAGE}',
129	pkgtype='${PKGTYPE}',
130	depend='${DEPEND}',
131	sdepend='${SDEPEND}',
132	provide='${PROVIDE}',
133	time='${time}',
134	flag='1'
135	WHERE id='${id}'"
136
137	else
138	# else import to database as new
139	mysqldo "INSERT INTO ${SQL_TABLE_PACKAGES} (
140	id,
141	pname,
142	pver,
143	pbuild,
144	pcat,
145	state,
146	description,
147	homepage,
148	pkgtype,
149	depend,
150	sdepend,
151	provide,
152	time,
153	flag
154	)
155	VALUES (
156	'${id}',
157	'${PNAME}',
158	'${PVER}',
159	'${PBUILD}',
160	'${pcatid}',
161	'${STATE}',
162	'${DESCRIPTION}',
163	'${HOMEPAGE}',
164	'${PKGTYPE}',
165	'${DEPEND}',
166	'${SDEPEND}',
167	'${PROVIDE}',
168	'${time}',
169	'1'
170	) ON DUPLICATE KEY UPDATE ${SQL_TABLE_PACKAGES} SET
171	pname='${PNAME}',
172	pver='${PVER}',
173	pbuild='${PBUILD}',
174	pcat='${pcatid}',
175	state='${STATE}',
176	description='${DESCRIPTION}',
177	homepage='${HOMEPAGE}',
178	pkgtype='${PKGTYPE}',
179	depend='${DEPEND}',
180	sdepend='${SDEPEND}',
181	provide='${PROVIDE}',
182	time='${time}',
183	flag='1'
184	WHERE id='${id}'"
185	fi
186	}
187
188	reset_flags()
189	{
190	# set all flags to zero
191	mysqldo "UPDATE ${SQL_TABLE_PACKAGES} SET flag='0'"
192	}
193
194	prune_old_packages()
195	{
196	# debug
197	# echo "Debug: following ids are requested for deletion:"
198	# mysqldo "SELECT id FROM packages WHERE flag='0'"
199
200	# delete them
201	mysqldo "DELETE FROM ${SQL_TABLE_PACKAGES} WHERE flag='0'"
202	}
203
204	echo "Processing Categories ..."
205	evaluate_categories
206
207	echo "Resetting Flags ..."
208	reset_flags
209
210	# all magefiles
211	ALL_MAGEFILES="$(find ${MAGEDIR} -name \*.mage \| sort)"
212
213	for magefile in ${ALL_MAGEFILES}
214	do
215	echo "Processing Magefile ${magefile}"
216	evaluate_magefile ${magefile}
217	done
218
219	echo "Pruning outdated Packages ..."
220	prune_old_packages