{"action":"created","issue":{"url":"https://api.github.com/repos/mozilla/prowac/issues/1","repository_url":"https://api.github.com/repos/mozilla/prowac","labels_url":"https://api.github.com/repos/mozilla/prowac/issues/1/labels{/name}","comments_url":"https://api.github.com/repos/mozilla/prowac/issues/1/comments","events_url":"https://api.github.com/repos/mozilla/prowac/issues/1/events","html_url":"https://github.com/mozilla/prowac/issues/1","id":130621852,"number":1,"title":"Reconsider database choice","user":{"login":"zalun","id":97885,"avatar_url":"https://avatars.githubusercontent.com/u/97885?v=3","gravatar_id":"","url":"https://api.github.com/users/zalun","html_url":"https://github.com/zalun","followers_url":"https://api.github.com/users/zalun/followers","following_url":"https://api.github.com/users/zalun/following{/other_user}","gists_url":"https://api.github.com/users/zalun/gists{/gist_id}","starred_url":"https://api.github.com/users/zalun/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/zalun/subscriptions","organizations_url":"https://api.github.com/users/zalun/orgs","repos_url":"https://api.github.com/users/zalun/repos","events_url":"https://api.github.com/users/zalun/events{/privacy}","received_events_url":"https://api.github.com/users/zalun/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":2,"created_at":"2016-02-02T09:13:53Z","updated_at":"2016-02-03T14:50:28Z","closed_at":null,"body":"I've been thinking about that (writing some dry code)\r\nThere are at least 3 ways to represent our data (essentially redis db is represented as a top-level JSON):\r\n1. JSON of current snapshot\r\n```\r\n{ \r\n  snapshotKey: JSON.stringify(fetchedDataFromTopSites),\r\n  currentSnapshot: snapshotKey\r\n}\r\n```\r\nThat doesn't allow us to go deep to dynamically show data for each site separately\r\n\r\n2. Object of JSON records:\r\n```\r\n{ \r\n  snapshotKey: {\r\n    'someUrl': JSON.stringify(siteData),\r\n    // 1M records ...\r\n  },\r\n  currentSnapshot: snapshotKey\r\n}\r\n```\r\nThis usage allows for dynamic browsing the site information (JSON.parse is needed on one record only), but fails if we want to analyze the data directly from database. If we would decide to save fetched data to database and do the analysis after all sites are fetched and saved, we would have to get entire list from db then do a lot of JSON.parse. Then we've got 2 options:\r\n 1. push the Object to a big array in memory and do calculations later on\r\n 2. create arrays with data prepared for analysis\r\n\r\n3. Array of hashtags with unpacked information:\r\n```\r\n{\r\n  `http://somesite': {\r\n    'manifest': '...',\r\n    'httpredirect': true\r\n    // other fields ...\r\n  },\r\n  // 1M records ...\r\n  currentSites: ['http://somesite', 1M items ...],\r\n  snapshotKey: JSON.stringify(fetchedDataFromTopSites)\r\n}\r\n```\r\nThis solution might be easier to work on (programatically), as we may create a query for all httpredirects, etc. This however requires a bunch of multiple redis commands to be run in one call, and I've been reading that Redis has issues if there is 100000+. We can split it into 20 bunches, but still it is a pain...\r\n\r\nMy choice would be the second attempt, but we need to think if relational database wouldn't be a better option."},"comment":{"url":"https://api.github.com/repos/mozilla/prowac/issues/comments/179273899","html_url":"https://github.com/mozilla/prowac/issues/1#issuecomment-179273899","issue_url":"https://api.github.com/repos/mozilla/prowac/issues/1","id":179273899,"user":{"login":"zalun","id":97885,"avatar_url":"https://avatars.githubusercontent.com/u/97885?v=3","gravatar_id":"","url":"https://api.github.com/users/zalun","html_url":"https://github.com/zalun","followers_url":"https://api.github.com/users/zalun/followers","following_url":"https://api.github.com/users/zalun/following{/other_user}","gists_url":"https://api.github.com/users/zalun/gists{/gist_id}","starred_url":"https://api.github.com/users/zalun/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/zalun/subscriptions","organizations_url":"https://api.github.com/users/zalun/orgs","repos_url":"https://api.github.com/users/zalun/repos","events_url":"https://api.github.com/users/zalun/events{/privacy}","received_events_url":"https://api.github.com/users/zalun/received_events","type":"User","site_admin":false},"created_at":"2016-02-03T14:50:28Z","updated_at":"2016-02-03T14:50:28Z","body":"As for multiple commands to database, we can add data one by one - without making it multiple. Just add some data after every crawled site. Collecting aggregated info is fine, as we can do this every time.\r\n\r\nI don't see the reason to store unpacked data in database. There is no need to ever get all of historical data at once from database. Storing object data in key-value store is complicated. In an unpacked (not a JSON string) form we can store only hashes (shallow - one level only - Objects) and lists (Arrays). To achieve such construct we would need:\r\n```javascript\r\n{\r\n  // set of all urls ever appeared in top sites, might not be needed, but necessary \r\n  // if we would like to display records\r\n  'historical_data': ['a-url-com', 'another-url-com', 'a-third-url-com'],\r\n  // for each url a set of all dates\r\n  'a-url-com': ['a-js-date-here', 'another-js-date'],\r\n  // finally for each url and date combination an object\r\n  'a-url-com-a-js-date-here': {\r\n     \"timestamp\": \"a JS Date here\",\r\n     \"hasProbe1\": true,\r\n     \"hasProbe2\": false,\r\n     \"hasProbe3\": true\r\n  },\r\n  'historical-data-a-url-com-another-js-date': {\r\n     \"timestamp\": \"another JS Date\",\r\n     \"hasProbe1\": false,\r\n     \"hasProbe2\": false,\r\n     \"hasProbe3\": false\r\n  }\r\n}\r\n```\r\nAnd so on. It's not very complicated but requires going back and forth to database.\r\n\r\nInstead I'd suggest the objects to be stringified.\r\n```json\r\n  'historical_data': ['a-url-com', 'another-url-com', 'a-third-url-com'],\r\n  'a-url-com': {\r\n    'a-js-date-here': '{ \"timestamp\": \"a JS Date here\", \"hasProbe1\": true, \"hasProbe2\": false, \"hasProbe3\": true}',\r\n    'another-js-date': '{\"timestamp\": \"another JS Date\", \"hasProbe1\": false, \"hasProbe2\": false, \"hasProbe3\": false}'\r\n  }\r\n}\r\n```\r\nThat gives us the ability to browse by site, needs parsing, but on relatively small string. We can have list of all keys, to be able to browse. Current crawl would be added to historical data from the beginning and kept in stringified way as well. I'd only keep a key of current data for main display.\r\n```json\r\n{\r\n  'current-crawl-results': {\r\n     'a-js-date-here': '{ \"timestamp\": \"a JS Date here\", \"hasProbe1\": true, \"hasProbe2\": false, \"hasProbe3\": true}',\r\n    'another-js-date': '{\"timestamp\": \"another JS Date\", \"hasProbe1\": false, \"hasProbe2\": false, \"hasProbe3\": false}'\r\n  },\r\n  'aggregated-info': {\r\n    'a-js-date': '{\"date-aggregated\": \"a JS Date\", \"totalSites\": 1000000, \"haveProbe1\": 2351, \"haveProbe2\": 71925, \"haveProbe3\": 10234}',\r\n    'a-js-date-2': '{\"date-aggregated\": \"a JS Date\", \"totalSites\": 1000000, \"haveProbe1\": 1234, \"haveProbe2\": 60124, \"haveProbe3\": 9123}'\r\n  }\r\n  'current-info': 'a-js-date-2'\r\n}\r\n```\r\n\r\nSumming, I think we don't need relational database (we can do heavy relations in Redis, but it's painful). BTW - my knowledge of Redis is also very limited, I haven't done any production site using it (my main db was always MySQL and Postgress). First living creature is Mercurius and then Platatus. I just seen what can be done and I like this as we can create sort of relational structure without hardcoding the schema.\r\nAs far as I understand the schema design in Redis - as long as we don't need direct access to a specific value, we should use stringified objects."}}