[Lsb-messages] /var/www/bzr/lsb/devel/specdb-aux-scripts r11: Adopted create_cache_tables_inits.sh for PostgreSQL

[Denis Silakov]

------------------------------------------------------------
revno: 11
committer: Denis Silakov <denis.silakov at rosalab.ru>
branch nick: specdb-aux-scripts
timestamp: Wed 2012-06-20 11:36:01 +0400
message:
  Adopted create_cache_tables_inits.sh for PostgreSQL
added:
  postgres/create_cache_tables_inits.sh
-------------- next part --------------
=== added file 'postgres/create_cache_tables_inits.sh'
--- a/postgres/create_cache_tables_inits.sh	1970-01-01 00:00:00 +0000
+++ b/postgres/create_cache_tables_inits.sh	2012-06-20 07:36:01 +0000
@@ -0,0 +1,31 @@
+#!/bin/bash
+
+#awk 'BEGIN {FS=","}; {print "INSERT INTO cache_RLibRIntMapping VALUES(",$2,",",$5,");"}' RawInterface.init | sort | uniq > cache_RLibRIntMapping.init
+
+# Create a table containing distinct RawInterface names
+#awk 'BEGIN {FS=","}; {print "INSERT INTO cache_RIntNames VALUES(",$2,");"}' RawInterface.init | sort | uniq >cache_RIntNames.init
+psql -h $LSBDBHOST -U $LSBUSER $LSBDB -c "DROP TABLE IF EXISTS cache_RLibRIntMapping; CREATE TABLE cache_RLibRIntMapping (RLibRIntId integer NOT NULL, RIname varchar(750) NOT NULL default '', RIlibrary varchar(250) NOT NULL default '',  PRIMARY KEY (RIname,RIlibrary)); create index RLibRIntId on cache_RLibRIntMapping(RLibRIntId)"
+psql -h $LSBDBHOST -U $LSBUSER $LSBDB -c "DROP TABLE IF EXISTS cache_RIntNames; CREATE TABLE cache_RIntNames (RIname varchar(750) NOT NULL default '', PRIMARY KEY (RIname))"
+psql -h $LSBDBHOST -U $LSBUSER $LSBDB -c "DROP TABLE IF EXISTS cache_RIntCaseInsensitiveNames; CREATE TABLE cache_RIntCaseInsensitiveNames (RICINid SERIAL, RIname varchar(750) NOT NULL default '', RIunmangled text default NULL, RIlibrary varchar(250) NOT NULL default '', PRIMARY KEY (RICINid)); create index RIname on cache_RIntCaseInsensitiveNames(RIname,RIlibrary); create index k_RIunmangled on cache_RIntCaseInsensitiveNames(RIunmangled)"
+
+LC_ALL=C cut RawInterface.init -f2,5 | LC_ALL=C sort -u | LC_ALL=C nl -w1 > cache_RLibRIntMapping.init
+# cache_RLibRIntMapping.init already contains sorted interface names
+LC_ALL=C cut cache_RLibRIntMapping.init -f2 | LC_ALL=C uniq >cache_RIntNames.init
+LC_ALL=C cut RawInterface.init -f2,3,5 | LC_ALL=C sort -u --ignore-case | LC_ALL=C nl -w1 >cache_RIntCaseInsensitiveNames.init
+
+psql -h $LSBDBHOST -U $LSBUSER $LSBDB -c "COPY cache_RIntNames FROM '$PWD/cache_RIntNames.init'"
+psql -h $LSBDBHOST -U $LSBUSER $LSBDB -c "COPY cache_RLibRIntMapping FROM '$PWD/cache_RLibRIntMapping.init'"
+psql -h $LSBDBHOST -U $LSBUSER $LSBDB -c "COPY cache_RIntCaseInsensitiveNames FROM '$PWD/cache_RIntCaseInsensitiveNames.init'"
+psql -h $LSBDBHOST -U $LSBUSER $LSBDB -c "UPDATE cache_RIntCaseInsensitiveNames SET RIunmangled=NULL WHERE RIunmangled='' "
+
+# For each LSB version, create a table with interfaces included in it (at least at one architecture)
+#awk 'BEGIN {FS=","}; {print "DROP TABLE IF EXISTS cache_IntsIncludedIn_",$2,
+#    ";\nCREATE TABLE cache_IntsIncludedIn_",$2,   
+#    "(KEY `Iid` (`Iid`), KEY `Itype` (`Itype`), KEY `AIarch` (`AIarch`,`Iid`), KEY `LGIlibg` (`LGIlibg`), KEY `Lid` (`Lid`,`AIarch`))",
+#    "SELECT Iid,AIarch,Itype,LGIlibg,LGlib AS Lid FROM Interface ",
+#    "LEFT JOIN ArchInt ON AIint=Iid ",
+#    "LEFT JOIN LGInt ON LGIint=Iid ",
+#    "LEFT JOIN LibGroup ON LGIlibg=LGid ",
+#    "WHERE AIappearedin <> \"\" AND AIappearedin <= ",$2," AND (AIwithdrawnin IS NULL OR AIwithdrawnin > ",$2,");\n";
+#    ";"}' LSBVersion.init | sed s/\\./_/ | sed s/\ \'// | sed s/\'// >cache_IncludedInts.init
+