lib/mage/join-repos.sh

#!/bin/bash

#REPOS="core extras nonfree"
REPOS="core extras"
#REPOS="core"
MAIN="main"

DBSUFFIX="db"
DBFILE="${MAIN}.${DBSUFFIX}"

die()
{
	echo -e "Exited ${BASH_SOURCE} at line no ${BASH_LINENO}."
	echo -e "$@"
	exit 1
}

sql()
{
	local sqlcmd="$*"
	[[ -z ${sqlcmd} ]] && die "no sqlcmd given."

	sqlite3 -nullvalue 'NULL' -list -separator '|' "${DBFILE}" << EOF || die "error running '$@'"
${sqlcmd};
EOF
}

sql_all_repos_attached()
{
	local sqlcmd="$*"
	[[ -z ${sqlcmd} ]] && die "no sqlcmd given."

	local SQLQUERY_ATTACH
	local SQLQUERY_DETACH

	for db in ${REPOS}
	do
		# attach database
		SQLQUERY_ATTACH+="attach database '${db}.${DBSUFFIX}' as ${db};"

		# detach database
		SQLQUERY_DETACH+="detach database ${db};"
	done

	sqlite3 -nullvalue 'NULL' -list -separator '|' "${DBFILE}" << EOF || die "error running '$@'"
${SQLQUERY_ATTACH};
${sqlcmd};
${SQLQUERY_DETACH};
EOF
}

if [[ -f ${DBFILE} ]]
then
	echo "deleting old ${DBFILE}..."
	rm ${DBFILE}
fi

# first of all create all sufficient tables
SQLQUERY="create table categories(id integer primary key, pcat text unique);"
SQLQUERY+="create table packages(id integer primary key,pname text,pver text,pbuild text,pcat_id numeric,state text,provide text,pkgtype text);"
SQLQUERY+="create table packages_info(id integer primary key, pkg_id numeric, arch text, md5 text, mtime numeric, homepage text, description text, size numeric);"
SQLQUERY+="create table depends(id integer primary key, pkg_id numeric, relation text, pcat_id numeric, pname text, pver text, pbuild text);"
SQLQUERY+="create table sdepends(id integer primary key, pkg_id numeric, relation text, pcat_id numeric, pname text, pver text, pbuild text);"
SQLQUERY+="create table virtual_defaults(id integer primary key, vcat text,vname text, pcat text, pname text);"
# run it
sql "${SQLQUERY}"

# add table with all loaded repos
SQLQUERY="create table repositories(id integer primary key, repository text);"
for db in ${REPOS}
do
	SQLQUERY+="insert into ${MAIN}.repositories(repository) values('${db}');"
done
sql "${SQLQUERY}"


#
# categories
#

# get all categories from all repos
SQLQUERY="select pcat from ${MAIN}.categories"
for db in ${REPOS}
do
	SQLQUERY+=" union select pcat from ${db}.categories"
done
SQLQUERY+=";"
all_categories=$(sql_all_repos_attached "${SQLQUERY}")
# insert all categories
for pcat in ${all_categories}
do
	sql "insert into ${MAIN}.categories(pcat) values('${pcat}')"
done


#
# virtual_defaults
#

# get all categories from all repos
SQLQUERY="select vcat,vname,pcat,pname from ${MAIN}.virtual_defaults"
for db in ${REPOS}
do
	SQLQUERY+=" union select vcat,vname,pcat,pname from ${db}.virtual_defaults"
done
SQLQUERY+=";"
all_virtual_defaults=$(sql_all_repos_attached "${SQLQUERY}")
# insert all virtual_defaults
for line in ${all_virtual_defaults}
do
	vcat="$(echo ${line} | cut -d'|' -f1)"
	vname="$(echo ${line} | cut -d'|' -f2)"
	pcat="$(echo ${line} | cut -d'|' -f3)"
	pname="$(echo ${line} | cut -d'|' -f4)"

	sql "insert into ${MAIN}.virtual_defaults(vcat,vname,pcat,pname) values('${vcat}','${vname}','${pcat}','${pname}')"
done


#
# packages
#
SQLQUERY=""
for db in ${REPOS}
do
	# attach database
	SQLQUERY+="attach database '${db}.${DBSUFFIX}' as ${db};"

	# transitional copy of packages with corrected pcat_ids from main database
	SQLQUERY+="insert into ${MAIN}.packages(pname,pver,pbuild,pcat_id,state,provide,pkgtype)
		select ${db}.packages.pname,
			${db}.packages.pver,
			${db}.packages.pbuild,
			${MAIN}.categories.id,
			${db}.packages.state,
			${db}.packages.provide,
			${db}.packages.pkgtype
		from ${db}.packages
		inner join ${db}.categories
			on ${db}.categories.id=${db}.packages.pcat_id
		inner join ${MAIN}.categories
			on ${MAIN}.categories.pcat=${db}.categories.pcat;"
	# detach database
	SQLQUERY+="detach database ${db};"
done
sql "${SQLQUERY}"


#
# packages_info
#
SQLQUERY=""
for db in ${REPOS}
do
	# attach database
	SQLQUERY+="attach database '${db}.${DBSUFFIX}' as ${db};"

	# transitional copy of packages_info with corrected pkg_ids from main database
	SQLQUERY+="insert into ${MAIN}.packages_info(pkg_id,arch,md5,mtime,homepage,description,size)
		select ${MAIN}.packages.id,
			${db}.packages_info.arch,
			${db}.packages_info.md5,
			${db}.packages_info.mtime,
			${db}.packages_info.homepage,
			${db}.packages_info.description,
			${db}.packages_info.size
		from ${db}.packages_info
		inner join ${db}.packages
			on ${db}.packages.id=${db}.packages_info.pkg_id
		inner join ${MAIN}.packages
			on ${MAIN}.packages.pname=${db}.packages.pname
			and ${MAIN}.packages.pver=${db}.packages.pver
			and ${MAIN}.packages.pbuild=${db}.packages.pbuild;"

	# detach database
	SQLQUERY+="detach database ${db};"
done
sql "${SQLQUERY}"


#
# depends
#
SQLQUERY=""
for db in ${REPOS}
do
	# attach database
	SQLQUERY+="attach database '${db}.${DBSUFFIX}' as ${db};"

	# transitional copy of depends with corrected pkg_ids and pcat_ids from main database
	SQLQUERY+="insert into ${MAIN}.depends(pkg_id,relation,pcat_id,pname,pver,pbuild)
		select ${MAIN}.packages.id,
			${db}.depends.relation,
			${MAIN}.categories.id,
			${db}.depends.pname,
			${db}.depends.pver,
			${db}.depends.pbuild
		from ${db}.depends
		inner join ${db}.packages
			on ${db}.packages.id=${db}.depends.pkg_id
		inner join ${db}.categories
			on ${db}.categories.id=${db}.depends.pcat_id
		inner join ${MAIN}.packages
			on ${MAIN}.packages.pname=${db}.packages.pname
			and ${MAIN}.packages.pver=${db}.packages.pver
			and ${MAIN}.packages.pbuild=${db}.packages.pbuild
		inner join ${MAIN}.categories
			on ${MAIN}.categories.pcat=${db}.categories.pcat;"

	# detach database
	SQLQUERY+="detach database ${db};"
done
sql "${SQLQUERY}"


#
# sdepends
#
SQLQUERY=""
for db in ${REPOS}
do
	# attach database
	SQLQUERY+="attach database '${db}.${DBSUFFIX}' as ${db};"

	# transitional copy of sdepends with corrected pkg_ids and pcat_ids from main database
	SQLQUERY+="insert into ${MAIN}.sdepends(pkg_id,relation,pcat_id,pname,pver,pbuild)
		select ${MAIN}.packages.id,
			${db}.sdepends.relation,
			${MAIN}.categories.id,
			${db}.sdepends.pname,
			${db}.sdepends.pver,
			${db}.sdepends.pbuild
		from ${db}.sdepends
		inner join ${db}.packages
			on ${db}.packages.id=${db}.sdepends.pkg_id
		inner join ${db}.categories
			on ${db}.categories.id=${db}.sdepends.pcat_id
		inner join ${MAIN}.packages
			on ${MAIN}.packages.pname=${db}.packages.pname
			and ${MAIN}.packages.pver=${db}.packages.pver
			and ${MAIN}.packages.pbuild=${db}.packages.pbuild
		inner join ${MAIN}.categories
			on ${MAIN}.categories.pcat=${db}.categories.pcat;"

	# detach database
	SQLQUERY+="detach database ${db};"
done
sql "${SQLQUERY}"
1	#!/bin/bash
2
3	#REPOS="core extras nonfree"
4	REPOS="core extras"
5	#REPOS="core"
6	MAIN="main"
7
8	DBSUFFIX="db"
9	DBFILE="${MAIN}.${DBSUFFIX}"
10
11	die()
12	{
13	echo -e "Exited ${BASH_SOURCE} at line no ${BASH_LINENO}."
14	echo -e "$@"
15	exit 1
16	}
17
18	sql()
19	{
20	local sqlcmd="$*"
21	[[ -z ${sqlcmd} ]] && die "no sqlcmd given."
22
23	sqlite3 -nullvalue 'NULL' -list -separator '\|' "${DBFILE}" << EOF \|\| die "error running '$@'"
24	${sqlcmd};
25	EOF
26	}
27
28	sql_all_repos_attached()
29	{
30	local sqlcmd="$*"
31	[[ -z ${sqlcmd} ]] && die "no sqlcmd given."
32
33	local SQLQUERY_ATTACH
34	local SQLQUERY_DETACH
35
36	for db in ${REPOS}
37	do
38	# attach database
39	SQLQUERY_ATTACH+="attach database '${db}.${DBSUFFIX}' as ${db};"
40
41	# detach database
42	SQLQUERY_DETACH+="detach database ${db};"
43	done
44
45	sqlite3 -nullvalue 'NULL' -list -separator '\|' "${DBFILE}" << EOF \|\| die "error running '$@'"
46	${SQLQUERY_ATTACH};
47	${sqlcmd};
48	${SQLQUERY_DETACH};
49	EOF
50	}
51
52	if [[ -f ${DBFILE} ]]
53	then
54	echo "deleting old ${DBFILE}..."
55	rm ${DBFILE}
56	fi
57
58	# first of all create all sufficient tables
59	SQLQUERY="create table categories(id integer primary key, pcat text unique);"
60	SQLQUERY+="create table packages(id integer primary key,pname text,pver text,pbuild text,pcat_id numeric,state text,provide text,pkgtype text);"
61	SQLQUERY+="create table packages_info(id integer primary key, pkg_id numeric, arch text, md5 text, mtime numeric, homepage text, description text, size numeric);"
62	SQLQUERY+="create table depends(id integer primary key, pkg_id numeric, relation text, pcat_id numeric, pname text, pver text, pbuild text);"
63	SQLQUERY+="create table sdepends(id integer primary key, pkg_id numeric, relation text, pcat_id numeric, pname text, pver text, pbuild text);"
64	SQLQUERY+="create table virtual_defaults(id integer primary key, vcat text,vname text, pcat text, pname text);"
65	# run it
66	sql "${SQLQUERY}"
67
68	# add table with all loaded repos
69	SQLQUERY="create table repositories(id integer primary key, repository text);"
70	for db in ${REPOS}
71	do
72	SQLQUERY+="insert into ${MAIN}.repositories(repository) values('${db}');"
73	done
74	sql "${SQLQUERY}"
75
76
77	#
78	# categories
79	#
80
81	# get all categories from all repos
82	SQLQUERY="select pcat from ${MAIN}.categories"
83	for db in ${REPOS}
84	do
85	SQLQUERY+=" union select pcat from ${db}.categories"
86	done
87	SQLQUERY+=";"
88	all_categories=$(sql_all_repos_attached "${SQLQUERY}")
89	# insert all categories
90	for pcat in ${all_categories}
91	do
92	sql "insert into ${MAIN}.categories(pcat) values('${pcat}')"
93	done
94
95
96
97
98	#
99	# virtual_defaults
100	#
101
102	# get all categories from all repos
103	SQLQUERY="select vcat,vname,pcat,pname from ${MAIN}.virtual_defaults"
104	for db in ${REPOS}
105	do
106	SQLQUERY+=" union select vcat,vname,pcat,pname from ${db}.virtual_defaults"
107	done
108	SQLQUERY+=";"
109	all_virtual_defaults=$(sql_all_repos_attached "${SQLQUERY}")
110	# insert all virtual_defaults
111	for line in ${all_virtual_defaults}
112	do
113	vcat="$(echo ${line} \| cut -d'\|' -f1)"
114	vname="$(echo ${line} \| cut -d'\|' -f2)"
115	pcat="$(echo ${line} \| cut -d'\|' -f3)"
116	pname="$(echo ${line} \| cut -d'\|' -f4)"
117
118	sql "insert into ${MAIN}.virtual_defaults(vcat,vname,pcat,pname) values('${vcat}','${vname}','${pcat}','${pname}')"
119	done
120
121
122
123
124	#
125	# packages
126	#
127	SQLQUERY=""
128	for db in ${REPOS}
129	do
130	# attach database
131	SQLQUERY+="attach database '${db}.${DBSUFFIX}' as ${db};"
132
133	# transitional copy of packages with corrected pcat_ids from main database
134	SQLQUERY+="insert into ${MAIN}.packages(pname,pver,pbuild,pcat_id,state,provide,pkgtype)
135	select ${db}.packages.pname,
136	${db}.packages.pver,
137	${db}.packages.pbuild,
138	${MAIN}.categories.id,
139	${db}.packages.state,
140	${db}.packages.provide,
141	${db}.packages.pkgtype
142	from ${db}.packages
143	inner join ${db}.categories
144	on ${db}.categories.id=${db}.packages.pcat_id
145	inner join ${MAIN}.categories
146	on ${MAIN}.categories.pcat=${db}.categories.pcat;"
147	# detach database
148	SQLQUERY+="detach database ${db};"
149	done
150	sql "${SQLQUERY}"
151
152
153
154
155	#
156	# packages_info
157	#
158	SQLQUERY=""
159	for db in ${REPOS}
160	do
161	# attach database
162	SQLQUERY+="attach database '${db}.${DBSUFFIX}' as ${db};"
163
164	# transitional copy of packages_info with corrected pkg_ids from main database
165	SQLQUERY+="insert into ${MAIN}.packages_info(pkg_id,arch,md5,mtime,homepage,description,size)
166	select ${MAIN}.packages.id,
167	${db}.packages_info.arch,
168	${db}.packages_info.md5,
169	${db}.packages_info.mtime,
170	${db}.packages_info.homepage,
171	${db}.packages_info.description,
172	${db}.packages_info.size
173	from ${db}.packages_info
174	inner join ${db}.packages
175	on ${db}.packages.id=${db}.packages_info.pkg_id
176	inner join ${MAIN}.packages
177	on ${MAIN}.packages.pname=${db}.packages.pname
178	and ${MAIN}.packages.pver=${db}.packages.pver
179	and ${MAIN}.packages.pbuild=${db}.packages.pbuild;"
180
181	# detach database
182	SQLQUERY+="detach database ${db};"
183	done
184	sql "${SQLQUERY}"
185
186
187
188
189	#
190	# depends
191	#
192	SQLQUERY=""
193	for db in ${REPOS}
194	do
195	# attach database
196	SQLQUERY+="attach database '${db}.${DBSUFFIX}' as ${db};"
197
198	# transitional copy of depends with corrected pkg_ids and pcat_ids from main database
199	SQLQUERY+="insert into ${MAIN}.depends(pkg_id,relation,pcat_id,pname,pver,pbuild)
200	select ${MAIN}.packages.id,
201	${db}.depends.relation,
202	${MAIN}.categories.id,
203	${db}.depends.pname,
204	${db}.depends.pver,
205	${db}.depends.pbuild
206	from ${db}.depends
207	inner join ${db}.packages
208	on ${db}.packages.id=${db}.depends.pkg_id
209	inner join ${db}.categories
210	on ${db}.categories.id=${db}.depends.pcat_id
211	inner join ${MAIN}.packages
212	on ${MAIN}.packages.pname=${db}.packages.pname
213	and ${MAIN}.packages.pver=${db}.packages.pver
214	and ${MAIN}.packages.pbuild=${db}.packages.pbuild
215	inner join ${MAIN}.categories
216	on ${MAIN}.categories.pcat=${db}.categories.pcat;"
217
218	# detach database
219	SQLQUERY+="detach database ${db};"
220	done
221	sql "${SQLQUERY}"
222
223
224
225
226	#
227	# sdepends
228	#
229	SQLQUERY=""
230	for db in ${REPOS}
231	do
232	# attach database
233	SQLQUERY+="attach database '${db}.${DBSUFFIX}' as ${db};"
234
235	# transitional copy of sdepends with corrected pkg_ids and pcat_ids from main database
236	SQLQUERY+="insert into ${MAIN}.sdepends(pkg_id,relation,pcat_id,pname,pver,pbuild)
237	select ${MAIN}.packages.id,
238	${db}.sdepends.relation,
239	${MAIN}.categories.id,
240	${db}.sdepends.pname,
241	${db}.sdepends.pver,
242	${db}.sdepends.pbuild
243	from ${db}.sdepends
244	inner join ${db}.packages
245	on ${db}.packages.id=${db}.sdepends.pkg_id
246	inner join ${db}.categories
247	on ${db}.categories.id=${db}.sdepends.pcat_id
248	inner join ${MAIN}.packages
249	on ${MAIN}.packages.pname=${db}.packages.pname
250	and ${MAIN}.packages.pver=${db}.packages.pver
251	and ${MAIN}.packages.pbuild=${db}.packages.pbuild
252	inner join ${MAIN}.categories
253	on ${MAIN}.categories.pcat=${db}.categories.pcat;"
254
255	# detach database
256	SQLQUERY+="detach database ${db};"
257	done
258	sql "${SQLQUERY}"