0

I have a table with a name snapmail_logs.

which has a column body in which I have been saving Email Templates as an HTML. There are a few things included in this body, to determine which email was failed or not.

["<!DOCTYPE html PUBLIC \"-//W3C//DTD XHTML 1.0 Transitional//EN\" \"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd\">\n<html xmlns=\"http://www.w3.org/1999/xhtml\" style=\"font-family: Arial, Helvetica, sans-serif; margin: 0; padding: 0;\">\n<head>\n  <meta name=\"viewport\" content=\"width=device-width\">\n  <meta http-equiv=\"Content-Type\" content=\"text/html; charset=UTF-8\">\n  <title>Evercam.io</title>\n</head>\n<body bgcolor=\"#FFFFFF\" style=\"font-family: Arial, Helvetica, sans-serif; -webkit-font-smoothing: antialiased; -webkit-text-size-adjust: none; width: 100% !important; height: 100%; margin: 0; padding: 0;\">\n<style type=\"text/css\">\n  @media only screen and (max-width: 600px) {\n    a[class=\"btn\"] {\n      display: block !important; margin-bottom: 10px !important; background-image: none !important; margin-right: 0 !important;\n    }\n    div[class=\"column\"] {\n      width: auto !important; float: none !important;\n    }\n    table.social div[class=\"column\"] {\n      width: auto !important;\n    }\n  }\n  @media only screen and (max-width: 480px){\n    .logo{\n      max-width:28% !important;\n      max-width:175px !important;\n    }\n  }\n</style>\n<!-- HEADER -->\n<table class=\"head-wrap\" bgcolor=\"#f5f5f5\" style=\"width: 100%; margin: 0; padding: 0;\">\n  <tr style=\"margin: 0; padding: 0;\">\n    <td style=\"margin: 0; padding: 0;\"></td>\n    <td class=\"header container\" style=\"display: block !important; clear: both !important; margin: 0 auto; padding: 0;\">\n      <div class=\"content\" style=\"display: block; margin: 0 auto; padding: 15px;\">\n        <table bgcolor=\"#f5f5f5\" style=\"width: 100%; margin: 0; padding: 0;\">\n          <tr style=\"margin: 0; padding: 0;text-align:center;\">\n            <td style=\"margin: 0; padding: 0;\">\n              <img src=\"http://www.evercam.io/img/logo.png\" class=\"logo\" style=\"max-width:28%;max-width:175px; padding-top:10px;margin:0;\">\n            </td>\n          </tr>\n        </table>\n      </div>\n    </td>\n    <td style=\"margin: 0; padding: 0;\"></td>\n  </tr>\n</table>\n<!-- /HEADER -->\n<!-- BODY -->\n<table class=\"body-wrap\" style=\"font-family: Arial, Helvetica, sans-serif; width: 100%; margin: 0; background-color:#f5f5f5; padding: 0;\">\n  <tr style=\"font-family: Arial, Helvetica, sans-serif; margin: 0; padding: 0;\"><td style=\"font-family: Arial, Helvetica, sans-serif; margin: 0; padding: 0;\"></td>\n    <td class=\"container\" bgcolor=\"#FFFFFF\" style=\"font-family: Arial, Helvetica, sans-serif; display: block !important; border-radius:3px; clear: both !important; margin: 0 auto; padding: 0;\">\n\n      <div class=\"content\" style=\"font-family: Arial, Helvetica, sans-serif; display: block; margin: 0 auto; padding: 15px;\">\n        <table style=\"font-family: Arial, Helvetica, sans-serif; width: 100%; margin: 0; padding: 0;\"><tr style=\"font-family: Arial, Helvetica, sans-serif; margin: 0; padding: 0;\">\n          <td style=\"font-family: Arial, Helvetica, sans-serif; font-weight: normal; font-size: 14px; margin: 0; padding: 0;\">\n\n            <p class=\"lead\" style=\"line-height: 1.6; margin: 0 0 10px; padding: 0;\">Hello, </p>\n\n            <p style=\"line-height: 1.6; margin: 0 0 10px; padding: 0;\">Here's the snapshot from your camera.</p>\n            <br>\n<img class='last-snapmail-snapshot' id='everc-fhlcr' src='cid:everc-fhlcr.jpg' alt='Last Snapshot' style='width: 100%; display:block; margin:0 auto;'><br>\n          <p style='line-height: 1.6; margin: 0 0 10px; padding: 0;'><b>Evercam PK Office 4K</b> (everc-fhlcr) - See the live view on Evercam by <a style='color:#428bca; text-decoration:none;' href='https://dash.evercam.io/v2/cameras/everc-fhlcr'>clicking here</a></p><br>\n            <p style=\"line-height: 1.6; margin: 0 0 10px; padding: 0;\">Thanks,</p><br>\n            <p style=\"line-height: 1.6; margin: 0 0 0px; padding: 0;\">The Evercam Team</p>\n            <p style=\"color:#aaaaaa; line-height: 1.0; margin: 0 0 10px; padding: 0;\"><a href=\"#\" style=\"text-decoration:none; color:#aaaaaa;\">support@evercam.io</a></p>\n            <br>\n\n            <p><center style=\"font-size:11px\">\n              If you want to change your Snapmail settings, <a " <> ..., "1551074758", "junaid@evercam.io", "Your Scheduled SnapMail @ 11:05", #DateTime<2019-02-25 06:06:18Z>, #DateTime<2019-02-25 06:06:18Z>]

Above is a sample data which is saved in the body, and this HTML has a class of .last-snapmail-snapshot which tell email was not failed and another class .failed-camera this tells about failure.

One is basically img tag

<img class='last-snapmail-snapshot' id='everc-fhlcr' src='cid:everc-fhlcr.jpg' alt='Last Snapshot' style='width: 100%; display:block; margin:0 auto;'>

and other one is a span

<span id='iamfailed' class='failed-camera'>Could not retrieve live image from</span>

Camera id is one that in ID, which I want to fetch. There could be multiple img and span tags in one body as well.

Through server side, I can filter this Body and can exract the values of failed and successive camera ids but My problem is I cannot sort them or cannot search them with where clause if I get this out of query.

What I am trying to do is:

Get all_camera_ids (which will be from both classes, last-snapmail-snapshot & failed-camera ) Get Failed Camera Ids (All ids from the class failed-camera )

And most probably Is it possible to do a where a query on that? where you can say, failed_camera_ids like "%junaid%"?

Its totally a new subject for me to parse HTML in PSQL query. I am sorry for such a ambiguis question. any help would be thankful

  • Can you just string extract the important information into specific columns of this or a new table and then work with this. It will be easier to work with, faster and much smaller once you throw the html away. – eckes Feb 25 at 7:32
  • Yes, you are right but its too late now data has been coming to this table for 2 years now. I have to deal it through this way. Is this possible? I can do it through server-side totally to first get and the parse it in Elixir's own way. But then IT will lose the sorting/where queries – Junaid Farooq Feb 25 at 7:35
  • Just write a job to extract it from the historical data and write it to a new table. You can do that in your backend language or with SQL (some string operations and regular expressions) – eckes Feb 25 at 7:52
  • Thing is I cannot add a new table. due to some reasons. Questions is: Is that possible what I have asked above? if not then I think I should just do it through server side – Junaid Farooq Feb 25 at 7:54
  • You can write a select which extracts data, it will just not be very performance to use with filter and sort, that’s why it is better to preserve the result in an indexed table. – eckes Feb 25 at 7:56
0

I would use a RE to extract the data (and insert it into a normalized table)

SELECT regexp_matches(body, '..') FROM logs;

Possibly with multiple function calls for each column.

Not sure what pattern would work for your examples.

'(?:last-snapmail-snapshot. id=.)(.+?)(?:\')'

should work for your first example (the (?:) means non-capturing and +? non-greedy)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.