table_schemer

gistfile1.txt

var _          = require('underscore'),
  	$          = require('jquery'),
		fs 				 = require('fs');

var table_names = {
	't1': "t1: Operating Cash Balance",
  't2': "t2: Deposits and Withdrawals",
  't3a': "t3a: Public Debt Transactions",
  't3b': "t3b: Adjustment of Public Debt Transactions",
  't3c': "t3c: Debt Subject to Limit",
  't4': "t4: Federal Tax Deposits",
  't5': "t5: Short-Term Cash Investments",
  't6': "t6: Income Tax Refunds Issued"
 },
 tables = {
  "tables":[

  ],
  "types":[
    {
      "name":"TEXT",
      "operators":[
        {
          "name":"=",
          "label":"is",
          "cardinality":"ONE"
        },
        {
          "name":"<>",
          "label":"is not",
          "cardinality":"ONE"
        }
      ]
    },
    {
      "name":"DATE",
      "operators":[
        {
          "name":"=",
          "label":"is",
        },
        {
          "name":"<>",
          "label":"is not",
        },
        {
          "name":"<",
          "label":"is before",
        },
        {
          "name":">",
          "label":"is after",
        }
      ]
    },
    {
      "name":"INTEGER",
      "operators":[
        {
          "name":"=",
          "label":"is",
        },
        {
          "name":"<>",
          "label":"is not",
        },
        {
          "name":"<",
          "label":"less than",
        },
        {
          "name":">",
          "label":"greater than",
        }
      ]
    }
  ]
};


String.prototype.contains = function(it) { return this.indexOf(it) != -1; };



function cleanPragmaObj(json){
	var clean_json = [];
	_.each(json, function(obj, index){
		delete obj.cid;
		delete obj.notnull;
		delete obj.dflt_value;
		delete obj.pk;

		// Remove the footnote column
		if (obj.name != 'url' && obj.type == 'TEXT' && obj.name != 'date' && obj.name != 'table' && obj.name != 'footnote' && obj.name != 'year_month' && obj.name.contains('raw') == false){
			clean_json.push(obj);
		};
	});

	return clean_json;
};

function write_to_file(data){
	fs.writeFileSync('table_schema.json', data);
};
var write_to_file_after = _.after(_.size(table_names), write_to_file);

function treasuryIo(query){
  return $.ajax({
    url: 'https://premium.scraperwiki.com/cc7znvq/47d80ae900e04f2/sql/?q='+query

  });
};


function add_values_to_column(obj_to_push){
  tables.tables.push(obj_to_push);
  log_when_done(JSON.stringify(tables));
};


for (var table_name in table_names){
	if (_.has(table_names, table_name)){

		(function(table_name){
			treasuryIo('pragma table_info(' + table_name +')')
				.done( function(response){

				  var name_types = cleanPragmaObj(response),
				      table_obj = {
				      	"label": table_names[table_name],
				      	"name" : table_name,
                "columns": []
				      };

          var add_values_to_column_after = _.after(_.size(name_types), add_values_to_column);

          _.each(name_types, function(name_type){
            (function(name_type, table_obj, len){
              treasuryIo('SELECT DISTINCT "' + name_type.name + '" FROM ' + table_obj.name)
                .done( function(response){

					  			var values = _.flatten(_.map(response, function(value){return _.values(value)})); // Perhaps this line could be improved
					  			name_type['values'] = values;
					  			table_obj.columns.push(name_type);

                  add_values_to_column_after(table_obj)
                });
              
            })(name_type, table_obj, _.size(name_types));

          });


				}); 
		})(table_name)
		
	};
};