pwo/grs - cloudfront invalidation for S3 video; mysql REPLACE

cloudfront invalidation is sort of a pain in the ass.

easier to just use a new name.

even though that means doing a replace in mysql for the video content id

http://docs.aws.amazon.com/AmazonCloudFront/latest/DeveloperGuide/Invalidation.html#Invalidation_Requests

http://stackoverflow.com/questions/5956993/mysql-string-replace

UPDATE grs_requirements

SET content_id = REPLACE(content_id, 'vtpp/', 'vtpp4/')

WHERE req_type = 'video'

ruby script to register prepware desktop with a command line tool; regtimestamp

/Users/smr/bin/register-prepware.rb

#!/usr/bin/env ruby
require 'sqlite3'

db_path = "/Users/smr/Library/Application Support/Prepware/persistence.db"
puts "updating #{db_path}"
tablename = "act"

now = Time::now
timestamp = now.strftime('%Y-%m-%d')
puts "setting regtimestamp to #{timestamp}"

db = SQLite3::Database.open db_path
db.execute( "update #{tablename} set regtimestamp ='#{timestamp}'" )
db.close if db

system "open", db_path

ADA notes - sarah hager xml workflow notes

http://smr.prepware.com.s3.amazonaws.com/notes

ASA notes - add recognition of UAS sku before pw2017 launch

1. investigate uas sku empty product request
1. no db yet
2. no uas codes in the wild before
3. need to notify user when act code is entered into reg grid
4. strategy
1. user enters uas code iinto 2017_000
2. show mbox
3. initiate call to updates

ASA task - transform tpp xml

workflow - tpp xml transform - Google Docs

/Users/smr/current_projects/tpp-xml-transform/

new for 2016-06: S3-based preview workflow

/Users/smr/current_projects/tpp-xml-transform/

/Users/smr/current_projects/tpp-xml-transform/xml-to-html-preview/00-transform-and-upload.command

1. uses the xml source found in: /Users/smr/current_projects/tpp-xml-transform/xml-source/
2. inline css
3. uploads html to http://media.prepware.com.s3.amazonaws.com/tpp-xml-preview
4. toc file at: http://media.prepware.com.s3.amazonaws.com/tpp-xml-preview/toc.html

ASA task - create new falcon db

see Prepware Updaters 02-2016 checklists google doc

checklist - create new falcon db (if necessary)

/Users/smr/current_projects/prepware-tools/create-falcon-db/

modify testmaps xls as needed
generate tab-delim txt
e.g. testmaps_2015_rev_b.txt
run 00-create-falcon.command
creates falcon db
adds topics table, referencing tab_delim/*

move to "copy new db based on timestamp" logic with prepware android 1.16 (02-2016)

add ContentDatabaseManager


check existing timestamp of installed db.
if this timestamp does not exists (e.g. when transitioning from pwa 1.15) a timestamp of 0 is returned.

    // db timestamp
    // N.B. returns 0 if content_info table not found
    private Integer existingDbTimestamp(String dbName) {
        Integer dbTimestamp = 0;
        SQLiteDatabase the_db = null;
        Cursor c = null;

        try {
            the_db = openDatabase(dbName);
            String tableName = "content_info";
            String[] cols = { "timestamp" }; // case-sensitive
            String where = "";
            String[] args = { };

            // "select timestamp from content_info"
            c = the_db.query(tableName, cols, where, args, null, null, null, null);

            if (c.moveToFirst()) {
                do {
                    dbTimestamp = c.getInt(c.getColumnIndex("timestamp"));
                }
                while (c.moveToNext());
            }
        }
        catch (SQLiteException ex) {
            Log.e("SQLiteException", "ex=" + ex);
        }
        finally {
            if (c != null) {
                c.close();
            }
            if (the_db != null) {
                the_db.close();
            }
        }

        return dbTimestamp;
    }

copy for first time if size is 0.
copy if newer timestamp.

    // copy db stuff
    public File copyDatabaseIfNecessary(Context ctx, String dbName) {
        // TODO do the copy on a different thread
        File f = ctx.getDatabasePath(dbName);
        long size = f.length();

        Log.i("PrepwareDebug", String.format("db size=%d", size));

        Boolean shouldCopyDatabase = false;
        // a size of 0 means we haven't copied the db over the first time
        if (size == 0)
        {
            shouldCopyDatabase = true;
            String msg = String.format(Locale.getDefault(), "copy %s for the first time", dbName);
            //Toast.makeText(ctx, msg, Toast.LENGTH_SHORT).show();
            //Toast.makeText(ctx, "initializing the content database", Toast.LENGTH_SHORT).show();
        }
        else  // size is non-zero so a db exists
        {
            // only copy the db from the assets folder if the existing timestamp
            // indicates an out-of-date database
            Integer existingDbTimeStamp = existingDbTimestamp(dbName);
            Integer latestDbTimestamp = latestDbTimestamp();
            String msg = String.format(Locale.getDefault(),
                    "existing timestamp:%d, latest timestamp %d", existingDbTimeStamp, latestDbTimestamp);
            Log.i("PrepwareDebug", msg);

            if (existingDbTimeStamp < latestDbTimestamp) {
                shouldCopyDatabase = true;
                msg = String.format(Locale.getDefault(), "copy new version of %s", dbName);
                //Toast.makeText(ctx, msg, Toast.LENGTH_SHORT).show();
                //Toast.makeText(ctx, "updating the content database", Toast.LENGTH_SHORT).show();
            }
        }

        if (shouldCopyDatabase) {
            Log.i("PrepwareDebug", dbName + " will be copied");

            // this creates a default db (and makes the path available)
            SQLiteDatabase db = ctx.openOrCreateDatabase(dbName, MODE_PRIVATE, null);
            db.close();

            try {
                copyLocalAsset(ctx, dbName, f.getAbsolutePath());
            } catch (IOException e) {
                // TODO Auto-generated catch block
                Log.e("PrepwareDebug", "Exception=" + e);
            }
        } else {
            Log.i("PrepwareDebug", dbName + " already exists and will not will be copied");
            //String msg = String.format(Locale.getDefault(), "most recent version of %s already exists", dbName);
            //Toast.makeText(ctx, msg, Toast.LENGTH_SHORT).show();
        }

        return f;
    }

##################################################
# first run
##################################################
02-12 09:14:28.040 16059-16059/com.asa2fly.prepware.airframe I/PrepwareDebug: db size=0
02-12 09:14:28.040 16059-16059/com.asa2fly.prepware.airframe I/PrepwareDebug: falcon.db will be copied
02-12 09:14:28.091 16059-16059/com.asa2fly.prepware.airframe I/PrepwareDebug: falcon db fileExists=true
02-12 09:14:28.092 16059-16059/com.asa2fly.prepware.airframe I/PrepwareDebug: db size=0
02-12 09:14:28.092 16059-16059/com.asa2fly.prepware.airframe I/PrepwareDebug: questions.jet will be copied
02-12 09:14:28.577 16059-16059/com.asa2fly.prepware.airframe I/PrepwareDebug: questions db fileExists=true

##################################################
# second run
##################################################
02-12 09:15:49.732 16838-16838/com.asa2fly.prepware.airframe I/PrepwareDebug: db size=92160
02-12 09:15:49.738 16838-16838/com.asa2fly.prepware.airframe I/PrepwareDebug: existing timestamp:1455278400, latest timestamp 1455278400
02-12 09:15:49.738 16838-16838/com.asa2fly.prepware.airframe I/PrepwareDebug: falcon.db already exists and will not will be copied
02-12 09:15:49.738 16838-16838/com.asa2fly.prepware.airframe I/PrepwareDebug: falcon db fileExists=true
02-12 09:15:49.738 16838-16838/com.asa2fly.prepware.airframe I/PrepwareDebug: db size=8298496
02-12 09:15:49.740 16838-16838/com.asa2fly.prepware.airframe I/PrepwareDebug: existing timestamp:1455278400, latest timestamp 1455278400
02-12 09:15:49.740 16838-16838/com.asa2fly.prepware.airframe I/PrepwareDebug: questions.jet already exists and will not will be copied
02-12 09:15:49.740 16838-16838/com.asa2fly.prepware.airframe I/PrepwareDebug: questions db fileExists=true

prepware updates 10-2015 - change Turboprop (added) testmap

falcon failed integrity checks.
cause: Turboprop (added) testmap.


old:

1.9.3-p286 :004 > y QuizDefinition.find_by_name("Turboprop (added)").testmap

1: 0

2: 1

3: 0

4: 8

5: 10

6: 10

7: 0

8: 0

9: 5

10: 5

11: 0

12: 0

13: 11

at the moment, have to do this by hand.

log onto dev.

RAILS_ENV=staging bundle exec rails console

1.9.3-p286 :010 > QuizDefinition.find_by_name("Turboprop (added)").test_map_entries.find_by_chapnum(2).update_attributes!(numq:0)

1.9.3-p286 :011 > QuizDefinition.find_by_name("Turboprop (added)").test_map_entries.find_by_chapnum(9).update_attributes!(numq:6)

new:

1.9.3-p286 :004 > y QuizDefinition.find_by_name("Turboprop (added)").testmap

1: 0

2: 0

3: 0

4: 8

5: 10

6: 10

7: 0

8: 0

9: 6

10: 5

11: 0

12: 0

13: 11

prepware updates 10-2015 - tweaks to fix discontiguous topic numbers

SELECT * FROM questions WHERE db_identifier='pvt' AND asachapter='11' AND topicnumber='4'

UPDATE `questions` set `topicnumber`=3 WHERE db_identifier='pvt' AND asachapter='11' AND topicnumber='4'
UPDATE `questions` set `topicnumber`=4 WHERE db_identifier='pvt' AND asachapter='11' AND topicnumber='5'
UPDATE `questions` set `topicnumber`=5 WHERE db_identifier='pvt' AND asachapter='11' AND topicnumber='6'

private chapter 11

11 1 Phraseology, Techniques, and Procedures
11 2 Airport Traffic Area Communications and Light Signals
11 3 Radar Assistance to VFR Aircraft
11 4 Transponder
11 5 Emergency Locator Transmitter (ELT)

atp chapter 5

changed my tab-delimited text (i use this to import topics into falcon) to:

5          1          Center of Gravity Computation

5          2          Stabilizer Trim Setting

5          3          Changing Loading Conditions

5          4          C208 Weight and Balance

5          5          Beech 1900 Weight and Balance

5          6          Helicopter Weight and Balance

5          7          Helicopter Weight and Balance: CG Shifts

5          8          Helicopter Weight and Balance: Load Limits

5          9          Helicopter Weight and Balance: Lateral CG

5          10        Floor Loading Limits