shell script which has to fetch column from a table with a date filter/condition

ibadh_urs · 10-26-2015, 01:56 AM

Hi Team,

I have a shell script through which I am connecting to a database and getting following table data (as shown in the below table)

PHP Code:



version_id    version          notes                     
821        15.8.0.0-b270    Installed on 2015-10-06 16:10:21.316074     
1248        15.11.0.0-b337    Installed on 2015-10-07 00:47:14.118219     
1267        15.11.0.0-b351    Installed on 2015-10-14 01:44:31.366960     
1268        15.8.2.0-b66    Installed on 2015-10-14 10:55:41.119922     
1284        15.8.0.0-b420    Installed on 2015-10-20 12:12:08.511436     
1287        16.2.0.0-b92    Installed on 2015-10-21 11:18:49.029774     
1291        16.2.0.0-b96    Installed on 2015-10-23 11:17:35.118424     
1295        16.2.0.0-b100   Installed on 2015-10-24 23:11:43.057607     
1296        16.2.0.0-b101   Installed on 2015-10-25 11:20:25.325467

From above table, I want to fetch the second column data (i.e. Version) based on the date value.
My requirement is:- I want to write a shell program which has to fetch only the "Version" column data till 23rd Oct-15 and pass these "Version" data to another python script (which already I have and delete these installed verision. for e.g. remove-product.py --version=15.5.0.0-b230).

Could some one help me?

Thanks in advance.
Ibadh.

HMW · 10-26-2015, 02:34 AM

Quote:

Originally Posted by ibadh_urs

Could some one help me?

Sure. What do you have this far?

Best regards,
HMW

HMW · 10-26-2015, 02:57 AM

Here is a short script that extracts the version numbers for the dates up until, and including, the 23d. Ergo; from 2015-10-06 until 2015-10-23.

Code:

$ ./fetch_column.sh 
15.8.0.0-b270
15.11.0.0-b337
15.11.0.0-b351
15.8.2.0-b66
15.8.0.0-b420
16.2.0.0-b92
16.2.0.0-b96

I obviously just echo these out instead of passing them to a Python script.
For this I used Bash only (while, read, grep and conditional statements).

Best regards,
HMW

ibadh_urs · 10-26-2015, 04:14 AM

Hi,

Thanks for the reply, can you please share the "fetch_column.sh" file?

Thanks in-advance.

Quote:

Originally Posted by HMW

Here is a short script that extracts the version numbers for the dates up until, and including, the 23d. Ergo; from 2015-10-06 until 2015-10-23.

Code:

$ ./fetch_column.sh 
15.8.0.0-b270
15.11.0.0-b337
15.11.0.0-b351
15.8.2.0-b66
15.8.0.0-b420
16.2.0.0-b92
16.2.0.0-b96

I obviously just echo these out instead of passing them to a Python script.
For this I used Bash only (while, read, grep and conditional statements).

Best regards,
HMW

grail · 10-26-2015, 05:10 AM

The idea is that you show what you have done and where you are stuck and HMW and others will gladly show you how they might do it. The script information will not be supplied just because you
asked for it as it shows you have made no attempt to solve the problem and want others to do it.

My 2 cents would be, if you are getting the data from a database already, why not simply alter your SQL to only grab the information you want?
Again, if you show your SQL (which you may not be knowledgeable about changing), we may be able to show you how to update it to get the information you require.
Should you opt to go this route, please provide which database it is you are working on, just so the experts here use the correct commands for you

HMW · 10-26-2015, 05:23 AM

Quote:

Originally Posted by ibadh_urs

Thanks for the reply, can you please share the "fetch_column.sh" file?

What grail said above.

If you share your work with me, I (and others) will help you. If you JUST want the script without doing the work, well... then I guess you would have to hire a consultant.

Best regards,
HMW

rpittala · 10-26-2015, 06:39 AM

Not sure, what you have done in your script. But, one suggestion from me is that
1) you know the fields, store `em in an array...@fields = (version_id version notes)
2)Open the file and go through the each field and store the required contents in an array ..do some regular expression to get the right content.
3) Collect each field in a hash say temp = data content.
3)if($temp{'notes'} nq "Installed on 2015-10-23") + do some regular expression here and store the content in an array.
4)when it "Installed on 2015-10-23" the condition satisfies stop the loop and process it to python script.

ibadh_urs · 10-27-2015, 02:19 AM

Thank you all for your advice.

I have tried with awk and its working, now I would like to think how to pass these values to my python script.
As advised by grail, I could not able to modify my SQL to get these data.

PHP Code:



select * from ok_versions v where not exists (select 1 from ok_instances i where i.version_id = v.version_id)

PHP Code:



bash-4.1$ cat table | awk 'BEGIN{"date -d \"-2 days\" +%Y-%m-%d"|getline d1;}{if( $5 <= d1 )print $2;}' 
version 
15.8.0.0-b270 
15.11.0.0-b337 
15.11.0.0-b351 
15.8.2.0-b66 
15.8.0.0-b420 
16.2.0.0-b92-int 
16.2.0.0-b96-int 
16.2.0.0-b100-int

If someone can help with new SQL query thats will be a great help.

Quote:

Originally Posted by rpittala

Not sure, what you have done in your script. But, one suggestion from me is that
1) you know the fields, store `em in an array...@fields = (version_id version notes)
2)Open the file and go through the each field and store the required contents in an array ..do some regular expression to get the right content.
3) Collect each field in a hash say temp = data content.
3)if($temp{'notes'} nq "Installed on 2015-10-23") + do some regular expression here and store the content in an array.
4)when it "Installed on 2015-10-23" the condition satisfies stop the loop and process it to python script.

grail · 10-27-2015, 08:35 AM

Quote:

I could not able to modify my SQL to get these data.

Is this because you lack the SQL knowledge or you do not have access to alter this SQL? You also did not advise what type of database you are working with?
If we assume it is knowledge, you want something along the lines:

Code:

select version
  from ok_versions v
 where not exists (select 1
                     from ok_instances i
                    where i.version_id = v.version_id)
   and to_date(substr(notes, 15, 10), 'DD-MON-YYYY') <= to_date('23-Oct-2015', 'DD-MON-YYYY')

This would be in Oracle, but I am sure the commands for others would be similar.