(Created page with "→This file contains functions for initializing and accessing the database; It assumes that alasql has been included in whichever page calling it;: var OUTREACH_URL = 'http...") |
m (added get_by_id) |
||
Line 5: | Line 5: | ||
var OUTREACH_URL = 'https://docs.google.com/spreadsheets/d/1lkZSXS3HXTS_ZyPZytyIW0Keer_aKy8rr1_pxV2-fSs/pubhtml?gid=1097044156&single=true' | var OUTREACH_URL = 'https://docs.google.com/spreadsheets/d/1lkZSXS3HXTS_ZyPZytyIW0Keer_aKy8rr1_pxV2-fSs/pubhtml?gid=1097044156&single=true' | ||
var outreach_db ={}; | var outreach_db ={}; | ||
− | + | console.log('init_outreach.js'); | |
(function(outreach_db){ | (function(outreach_db){ | ||
var initialized = false; | var initialized = false; | ||
Line 11: | Line 11: | ||
return alasql.promise('CREATE TABLE IF NOT EXISTS outreach').then( | return alasql.promise('CREATE TABLE IF NOT EXISTS outreach').then( | ||
function(){ | function(){ | ||
− | return alasql.promise('SELECT * INTO outreach FROM TABLETOP(?)', OUTREACH_URL) | + | everything = "CAST([id] AS STRING), CAST([School] AS STRING), CAST([Section] AS STRING), CAST([Year] AS STRING), CAST([Wiki hyperlink] AS STRING), CAST([Award] AS STRING), CAST([Outreach Category] AS STRING), CAST([Outreach Project] AS STRING), CAST([Project Tag] AS STRING), CAST([Product] AS STRING), CAST([Product Description] AS STRING), CAST([Product Tag] AS STRING), CAST([Audience] AS STRING), CAST([Goal] AS STRING), CAST([Objectives] AS STRING), CAST([Data Type] AS STRING),CAST([Data] AS STRING), CAST([Reach] AS STRING), CAST([Analysis] AS STRING), CAST([References] AS STRING), CAST([Description] AS STRING)" |
+ | return alasql.promise('SELECT * INTO outreach FROM TABLETOP(?, ?)', [OUTREACH_URL, {parseNumbers:false}]) | ||
} | } | ||
) | ) | ||
Line 47: | Line 48: | ||
} | } | ||
return outreach_db.compile_sql.sql_template(selected_tags) | return outreach_db.compile_sql.sql_template(selected_tags) | ||
+ | } | ||
+ | |||
+ | outreach_db.get_by_id = function(id){ | ||
+ | data = alasql('SELECT * FROM outreach WHERE CAST([id] as STRING)=?', [id]);// need to cast id as string or numeric ids won't work | ||
+ | return data[0]; | ||
} | } | ||
})(outreach_db) | })(outreach_db) | ||
+ | console.log('done outreach.js') |
Revision as of 14:07, 3 September 2017
/* This file contains functions for initializing and accessing the database; It assumes that alasql has been included in whichever page calling it;
- /
var OUTREACH_URL = 'https://docs.google.com/spreadsheets/d/1lkZSXS3HXTS_ZyPZytyIW0Keer_aKy8rr1_pxV2-fSs/pubhtml?gid=1097044156&single=true' var outreach_db ={}; console.log('init_outreach.js'); (function(outreach_db){
var initialized = false; outreach_db.init = function (clear_local=false){ return alasql.promise('CREATE TABLE IF NOT EXISTS outreach').then( function(){ everything = "CAST([id] AS STRING), CAST([School] AS STRING), CAST([Section] AS STRING), CAST([Year] AS STRING), CAST([Wiki hyperlink] AS STRING), CAST([Award] AS STRING), CAST([Outreach Category] AS STRING), CAST([Outreach Project] AS STRING), CAST([Project Tag] AS STRING), CAST([Product] AS STRING), CAST([Product Description] AS STRING), CAST([Product Tag] AS STRING), CAST([Audience] AS STRING), CAST([Goal] AS STRING), CAST([Objectives] AS STRING), CAST([Data Type] AS STRING),CAST([Data] AS STRING), CAST([Reach] AS STRING), CAST([Analysis] AS STRING), CAST([References] AS STRING), CAST([Description] AS STRING)" return alasql.promise('SELECT * INTO outreach FROM TABLETOP(?, ?)', [OUTREACH_URL, {parseNumbers:false}]) } ) }
outreach_db.list_tags = function(){ tag_data = {} tag_types = ['School Level', 'Year', 'Outreach Category', 'Award', 'Project Tag', 'Product Tag', 'Audience', 'Goal', 'Data Type'] for (tag_type of tag_types){ tags = alasql('COLUMN OF SELECT DISTINCT ['+tag_type+'] FROM outreach') tags = tags.filter( // to remove 0 for null rows in column obj=> obj ).map( // to split multitags s=>String(s).split(',') .map(jQuery.trim) ).reduce( // to flatten array of form [[a, b], [c, d]] (a, b)=>a.concat(b), [] ) tag_data[tag_type]=Array.from(new Set(tags)) } return(tag_data); }
outreach_db.compile_sql = function(selected_tags){ // use existing template, or compile one in case there's none outreach_db.compile_sql.sql_template = outreach_db.compile_sql.sql_template||Handlebars.compile($('#querystring-template').html()) // none selected = don't filter on this tag type for (tag_type in selected_tags){ if (selected_tags[tag_type].length == 0){ delete selected_tags[tag_type] } } return outreach_db.compile_sql.sql_template(selected_tags) }
outreach_db.get_by_id = function(id){ data = alasql('SELECT * FROM outreach WHERE CAST([id] as STRING)=?', [id]);// need to cast id as string or numeric ids won't work return data[0]; }
})(outreach_db) console.log('done outreach.js')