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,repo_id numeric);"
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, name text);"
for db in ${REPOS}
do
	SQLQUERY+="insert into ${MAIN}.repositories(name) 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,repo_id)
		select ${db}.packages.pname,
			${db}.packages.pver,
			${db}.packages.pbuild,
			${MAIN}.categories.id,
			${db}.packages.state,
			${db}.packages.provide,
			${db}.packages.pkgtype,
			${MAIN}.repositories.id
		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
		inner join ${MAIN}.repositories
			on ${MAIN}.repositories.name='${db}';"
	# 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,repo_id numeric);"
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, name text);"
70	for db in ${REPOS}
71	do
72	SQLQUERY+="insert into ${MAIN}.repositories(name) 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,repo_id)
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	${MAIN}.repositories.id
143	from ${db}.packages
144	inner join ${db}.categories
145	on ${db}.categories.id=${db}.packages.pcat_id
146	inner join ${MAIN}.categories
147	on ${MAIN}.categories.pcat=${db}.categories.pcat
148	inner join ${MAIN}.repositories
149	on ${MAIN}.repositories.name='${db}';"
150	# detach database
151	SQLQUERY+="detach database ${db};"
152	done
153	sql "${SQLQUERY}"
154
155
156
157
158	#
159	# packages_info
160	#
161	SQLQUERY=""
162	for db in ${REPOS}
163	do
164	# attach database
165	SQLQUERY+="attach database '${db}.${DBSUFFIX}' as ${db};"
166
167	# transitional copy of packages_info with corrected pkg_ids from main database
168	SQLQUERY+="insert into ${MAIN}.packages_info(pkg_id,arch,md5,mtime,homepage,description,size)
169	select ${MAIN}.packages.id,
170	${db}.packages_info.arch,
171	${db}.packages_info.md5,
172	${db}.packages_info.mtime,
173	${db}.packages_info.homepage,
174	${db}.packages_info.description,
175	${db}.packages_info.size
176	from ${db}.packages_info
177	inner join ${db}.packages
178	on ${db}.packages.id=${db}.packages_info.pkg_id
179	inner join ${MAIN}.packages
180	on ${MAIN}.packages.pname=${db}.packages.pname
181	and ${MAIN}.packages.pver=${db}.packages.pver
182	and ${MAIN}.packages.pbuild=${db}.packages.pbuild;"
183
184	# detach database
185	SQLQUERY+="detach database ${db};"
186	done
187	sql "${SQLQUERY}"
188
189
190
191
192	#
193	# depends
194	#
195	SQLQUERY=""
196	for db in ${REPOS}
197	do
198	# attach database
199	SQLQUERY+="attach database '${db}.${DBSUFFIX}' as ${db};"
200
201	# transitional copy of depends with corrected pkg_ids and pcat_ids from main database
202	SQLQUERY+="insert into ${MAIN}.depends(pkg_id,relation,pcat_id,pname,pver,pbuild)
203	select ${MAIN}.packages.id,
204	${db}.depends.relation,
205	${MAIN}.categories.id,
206	${db}.depends.pname,
207	${db}.depends.pver,
208	${db}.depends.pbuild
209	from ${db}.depends
210	inner join ${db}.packages
211	on ${db}.packages.id=${db}.depends.pkg_id
212	inner join ${db}.categories
213	on ${db}.categories.id=${db}.depends.pcat_id
214	inner join ${MAIN}.packages
215	on ${MAIN}.packages.pname=${db}.packages.pname
216	and ${MAIN}.packages.pver=${db}.packages.pver
217	and ${MAIN}.packages.pbuild=${db}.packages.pbuild
218	inner join ${MAIN}.categories
219	on ${MAIN}.categories.pcat=${db}.categories.pcat;"
220
221	# detach database
222	SQLQUERY+="detach database ${db};"
223	done
224	sql "${SQLQUERY}"
225
226
227
228
229	#
230	# sdepends
231	#
232	SQLQUERY=""
233	for db in ${REPOS}
234	do
235	# attach database
236	SQLQUERY+="attach database '${db}.${DBSUFFIX}' as ${db};"
237
238	# transitional copy of sdepends with corrected pkg_ids and pcat_ids from main database
239	SQLQUERY+="insert into ${MAIN}.sdepends(pkg_id,relation,pcat_id,pname,pver,pbuild)
240	select ${MAIN}.packages.id,
241	${db}.sdepends.relation,
242	${MAIN}.categories.id,
243	${db}.sdepends.pname,
244	${db}.sdepends.pver,
245	${db}.sdepends.pbuild
246	from ${db}.sdepends
247	inner join ${db}.packages
248	on ${db}.packages.id=${db}.sdepends.pkg_id
249	inner join ${db}.categories
250	on ${db}.categories.id=${db}.sdepends.pcat_id
251	inner join ${MAIN}.packages
252	on ${MAIN}.packages.pname=${db}.packages.pname
253	and ${MAIN}.packages.pver=${db}.packages.pver
254	and ${MAIN}.packages.pbuild=${db}.packages.pbuild
255	inner join ${MAIN}.categories
256	on ${MAIN}.categories.pcat=${db}.categories.pcat;"
257
258	# detach database
259	SQLQUERY+="detach database ${db};"
260	done
261	sql "${SQLQUERY}"