projectz

Tech, Gadgets, Photography, Social Media and Poor Spelling

How to I inject MYSQL into a database from bash?

This is really just for reference for some code i’ve been putting together, however it might be useful to someone.

func_injectXML(){
## contains a FOR/DO loop which will inject the root XML for the xmlstarlet query on all the test files
## use example: sed -e ‘/?>/a<lshw>’ -e ‘$a</lshw>’ lshw_display.txt
    for f in $tests/lshw*.txt;
            do
                    echo “Processing $f file..”;
                    sed -e ‘/?>/a<lshw>’ -e ‘$a</lshw>’ $f > $f.1
        done
}

func_obtaindata(){
# this function extracts the summery data from the test files ready to inject into the database.
## PC Information
    ## table pc_make
    PCMAKE=$(xmlstarlet sel -t -m “//lshw/node[@class=’system’]”  -v “vendor” -n $formatted/$system)
    ## table pc_model
    PCMODEL=$(xmlstarlet sel -t -m “//lshw/node[@class=’system’]”  -v “product” -n $formatted/$system)
## TC Build info
    ## table tc_ostype
    OSVARIANT=$(xmlstarlet sel -t -m “//TrustedClientSetup/OSImage” -o ” ” -v “@variant” -n $XMLDOC)
    ## table tc_version
    BASE_VER=$(xmlstarlet sel -t -m “//TrustedClientSetup” -o ” ” -v “@version” -n $XMLDOC)
    ## table tc_version_writer
    VERSION=$(xmlstarlet sel -t -m “//TrustedClientSetup” -o ” ” -v “@createdby-setup-version” -n $XMLDOC)
    ## table tc_version BEM
    BEM_VER=$(xmlstarlet sel -t -m “/TrustedClientSetup/SecurityPolicy” -v @ServerType $XMLDOC )
    ## table tc_kernel
    KERN_VER=$(dpkg -s linux-image-$(uname -r) | grep -i ^version | cut -d ” ” -f 2 )
   

## Graphics Card
## xmlstarlet sel -t -m “//lshw/node[@id=’display’]”  -v “vendor” -n lshw_display_1.txt
    DISPLAYMAKE=$(xmlstarlet sel -t -m “//lshw/node[@id=’display’]”  -v “vendor” -n  $formatted/$display)
    DISPLAYMODEL=$(xmlstarlet sel -t -m “//lshw/node[@id=’display’]”  -v “product” -n  $formatted/$display)
    DISPLAYDRIVER=$(xmlstarlet sel -t -m “//lshw/node/configuration/setting[@id=’driver’]” -o ” ” -v “@driver” -v “@value” -n $formatted/$display)
    DISPLAYCHIPSET=$(xmlstarlet sel -t -m “//lshw/node” -v “version” -n $formatted/$display)
    DISPLAYIDTAG=$(xmlstarlet sel -t -m “//lshw/node” -v “description” -n  $formatted/$display)
    DISPLAYRAW=$(cat $formatted/$display)
## NIC
    NICMAKE=$(xmlstarlet sel -t -m “//lshw/node[@id=’network’]”  -v “vendor” -n  $formatted/$network)
        NICMODEL=$(xmlstarlet sel -t -m “//lshw/node[@id=’network’]”  -v “product” -n  $formatted/$network)
        NICDRIVER=$(xmlstarlet sel -t -m “//lshw/node/configuration/setting[@id=’driver’]” -o ” ” -v “@driver” -v “@value” -n $formatted/$network)
        NICCHIPSET=$(xmlstarlet sel -t -m “//lshw/node” -v “version” -n $formatted/$network)
        NICIDTAG=$(xmlstarlet sel -t -m “//lshw/node” -v “description” -n  $formatted/$network)
        NICRAW=$(cat $formatted/$network)

## Wifi

## Audio
        MEDIAMAKE=$(xmlstarlet sel -t -m “//lshw/node[@id=’multimedia’]”  -v “vendor” -n  $formatted/$media)
        MEDIAMODEL=$(xmlstarlet sel -t -m “//lshw/node[@id=’multimedia’]”  -v “product” -n  $formatted/$media)
        MEDIADRIVER=$(xmlstarlet sel -t -m “//lshw/node/configuration/setting[@id=’driver’]” -o ” ” -v “@driver” -v “@value” -n $formatted/$media)
        MEDIACHIPSET=$(xmlstarlet sel -t -m “//lshw/node” -v “version” -n $formatted/$media)
        MEDIAIDTAG=$(xmlstarlet sel -t -m “//lshw/node” -v “description” -n  $formatted/$media)
        MEDIARAW=$(cat $formatted/$media)

}

func_insertSQL(){
## inserts the variables obtained previously into the necessary database fields.
echo “Inserting Core Data”

#mysql —host=$server —user=$username —password=$pass $database « EOF
mysql —host=$server —user=$username —password=$userpass $database « EOF
insert into $devices (pc_make,pc_model,tc_version,tc_version_writer,tc_version_bem,tc_kernel,tc_ostype) values(‘$PCMAKE’,’$PCMODEL’,’$BASEVER’,’$VERSION’,’$B
EMVER’,’$KERN_VER’,’$OSVARIANT’);
SELECT LAST_INSERT_ID();
SET @id2 = LAST_INSERT_ID();
SELECT @id2;
insert into $test (deviceID,make,model,driver,chipset,idtag,rawdata) values(@id2,’$DISPLAYMAKE’,’$DISPLAYMODEL’,’$DISPLAYDRIVER’,’$DISPLAYCHIPSET’,’$DISPLAYI
DTAG’,’$DISPLAYRAW’);
insert into $test (deviceID,make,model,driver,chipset,idtag,rawdata) values(@id2,’$NICMAKE’,’$NICMODEL’,’$NICDRIVER’,’$NICCHIPSET’,’$NICIDTAG’,’$NICRAW’);
insert into $test (deviceID,make,model,driver,chipset,idtag,rawdata) values(@id2,’$MEDIAMAKE’,’$MEDIAMODEL’,’$MEDIADRIVER’,’$NICCHIPSET’,’$MEDIAIDTAG’,’$MEDI
ARAW’);
EOF

}

func_insertRAWFILES(){
## perform a for do look on the output folder, and cat the data to a variable, then SQL insert that variable into the database
## need to extract the last deviceID variable from the func_insertSQL so we are linked
## example SQL
## SET @devid = (SELECT deviceID FROM tbl_devices ORDER BY deviceID DESC LIMIT 1);
## select @devid

echo “Inserting Raw Data Files”
#rawdat=”1”
#sql1=”SET @devid = (SELECT deviceID FROM tbl_devices ORDER BY deviceID DESC LIMIT 1)”
#sql2=”select @devid”
#sql3=”insert into tbl_rawdata (deviceID,datafile) values(@devid,${rawdat})”

for f in $tests/*.txt;

                do
                        echo “Processing $f file..”
            rawdat=$(cat $f)
            echo $f
    mysql —host=$server —user=$username —password=$userpass $database  —exec “SET @devid = (SELECT deviceID FROM tbl_devices ORDER BY deviceID DESC L
IMIT 1); select @devid; insert into tbl_rawdata (deviceID,datafile) values(@devid,’$rawdat’)”
                done
}

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Information

This entry was posted on May 9, 2011 by in regular and tagged , , , , , .
%d bloggers like this: