sqlite3 show all tables of a database

show all tables of a database

open database file via sqlite3
sqlite3 content/data/ghost-dev.db
command to list all tables
.tables || .table

$sqlite3  content/data/ghost-dev.db       SQLite version 3.11.0 2016-02-15 17:29:24 Enter ".help" for usage hints. sqlite> .tables accesstokens            migrations              refreshtokens          api_keys                migrations_lock         roles                  app_fields              mobiledoc_revisions     roles_users            app_settings            permissions             sessions               apps                    permissions_apps        settings               brute                   permissions_roles       subscribers            client_trusted_domains  permissions_users       tags                   clients                 posts                   users                  integrations            posts_authors           webhooks               invites                 posts_tags             

show table structure

select * from tablename;

sqlite> select * from posts; id|uuid|title|slug|mobiledoc|html|comment_id|plaintext|feature_image|featured|page|status|locale|visibility|meta_title|meta_description|author_id|created_at|created_by|updated_at|updated_by|published_at|published_by|custom_excerpt|codeinjection_head|codeinjection_foot|og_image|og_title|og_description|twitter_image|twitter_title|twitter_description|custom_template 

coca top 20000 words: TOP 20000 words from Corpus of Contemporary American English

TOP 20000 words

Here you are! https://coca.errong.win

Hack to ghost core: mount/add new apps/sites on your ghost server

Install ghost from source

First, you should know how to develop with ghost.

create a new react app under core/server folder

simple example

core/server$ mkdir newapp
core/server$ cd newapp
core/server/newapp$ 
Ghost/core/server/web/newapp$ ls
app.js  controller.js  index.js
Ghost/core/server/web/newapp$ cat index.js 
module.exports = require('./app');
Ghost/core/server/web/newapp$ cat app.js 
const express = require('express');
module.exports = function setupNewApp() {
    const newApp = express();
    newApp.get('*', require('./controller'));
    return newApp;
};
Ghost/core/server/web/newapp$ cat controller.js 
const path = require('path');

module.exports = function newAppController(req, res) {
    res.send('Welcome to new App!');
};

Add route for your new site/app

./core/server/app.js

    // Mount the  apps on the parentApp
  
    // ADMIN
    parentApp.use('/ghost', require('./admin')());

    // import new app/site
    parentApp.use('/newsite', require('./newapp')());
    
    // BLOG
    parentApp.use(require('./site')()); 

Notes

above are for ghost before 2.0 version.
for latest ghost, version 2.0,
The folder for new app should be "Ghost/core/server/web/newapp"
The hacked core js is Ghost/core/server/web/parent-app.js
The code for add route is same as above.

Work your new app

grunt dev
navigate to http://localhost:2368/newapp, you will get:

A simple web audio player, bind to <span/>, one click, audio play, click again, audio stop

example

All you have to is to write below html code:
a span with class name of word-audio and attribute of data-src pointed to a audio stream resource

<span class='word-audio audio' style='display:inline-block' 

data-src='https://cdn.mp3xa.pw/proxy/cs1-43v4.vkuseraudio.net/

p17/fe6d95af2cee33.mp3'></span> 

Bertie Higgins — Casablanca

word.js

  
  function startAnimation(e) {
    if (e.className == 'word-audio audio')
      e.className = 'word-audio audio-light';
    else if (e.className == 'word-audio audio-light')
      e.className = 'word-audio audio-playing';
    else
      e.className = 'word-audio audio'
    console.log(e.className);
  }
  function play(e, context, audioBuffer) {
    if (e.state == 1) {
      e.source.stop();
      e.source.onended();
      e.source = null;
    } else {
      e.state = 1;
      const source = context.createBufferSource();
      e.source = source;
      source.buffer = audioBuffer;
      source.connect(context.destination);
      source.start();
      let it = setInterval(function() {startAnimation(e)}, 300);    
      source.onended = function() {
        e.state = 0;
        clearInterval(it);
        e.className = 'word-audio audio';
      }
    }
  }
  document.querySelectorAll('.word-audio').forEach(function(e, index) {
    let url = e.attributes['data-src'].nodeValue;
    let context = new AudioContext();
    e.state = 0;
    let wordBuffer;
    window.fetch(url)
      .then(response => response.arrayBuffer())
      .then(arrayBuffer => context.decodeAudioData(arrayBuffer))
      .then(audioBuffer => {
        e.disabled = false;
        wordBuffer = audioBuffer;
        //play(e, context, wordBuffer);
      });
    e.onclick = function() {
      play(e, context, wordBuffer);       
    }
  });

word.css

.audio {
    display: inline-block;
    width: 20px;
    height: 20px;
    position: relative;
    overflow: hidden;
    cursor: pointer;
    vertical-align: middle;
    background: url(audio.png) no-repeat -40px 0/auto 100%;
}

.audio-playing {
    display: inline-block;
    width: 20px;
    height: 20px;
    position: relative;
    overflow: hidden;
    cursor: pointer;
    vertical-align: middle;
    background: url(audio.png) no-repeat -20px 0/auto 100%;
}

.audio-light {
    display: inline-block;
    width: 20px;
    height: 20px;
    position: relative;
    overflow: hidden;
    cursor: pointer;
    vertical-align: middle;
    background: url(audio.png) no-repeat 0px 0/auto 100%;
}

How to extract media files from a anki flashcard package(*.apkg)

*.apkg

The .apkg file is a zip file (as you know). Inside, you'll find the collection (an sqlite3 database of notes/cards), the media file which you were trying to open, and a bunch of numbered files 0-whatever number(these are media files).

Just use 7zip tool open the apkg file and extract into a folder.
open the "media" file via a text editor tool to find the media file extension.

example of "media"

{"16486": "COCA_16486.mp3", } 

rename "16486" file to a mp3 file, then you got the media file.

how media used in flash card

Export your flash cards into a plain text.

[sound:COCA_16486.mp3]

spatially	<div style=''>英['speɪʃəlɪ]  美['speɪʃəlɪ]</div>	<div style=''>adv.空间地，存在于空间地；</div>	"<div><br /></div><div style='color:RosyBrown'>ADJ</div><div style='color:OrangeRed'>空间的;与空间有关的</div><div style=""font-weight:bold;"">Spatial is used to describe things relating to areas.</div><div style=''><b>例：</b>...the spatial distribution of black employment and population in South Africa.南非黑人就业与人口的空间分布</div><div style=''><b>例：</b>...spatial constraints.空间的限制</div><div><br /></div><div style='color:RosyBrown'>ADJ</div><div style='color:OrangeRed'>(能力)理解立体空间的</div><div style=""font-weight:bold;"">Your spatial ability is your ability to see and understand the relationships between shapes, spaces, and areas.</div><div style=''><b>例：</b>His manual dexterity and fine spatial skills were wasted on routine tasks.他灵巧的动手能力和杰出的空间识别能力都浪费在日常事务上了。</div><div style=''><b>例：</b>...spatial awareness.空间方位感</div>"	<div style='color:DarkOrange; font-style:italic;'>adj:spatial; </div>		[sound:COCA_16486.mp3]	 

example of web flash card

spatially

英['speɪʃəlɪ] 美['speɪʃəlɪ]

adv.空间地，存在于空间地；

"

ADJ

空间的;与空间有关的

<div style=""font-weight:bold;"">Spatial is used to describe things relating to areas.

例：...the spatial distribution of black employment and population in South Africa.南非黑人就业与人口的空间分布

例：...spatial constraints.空间的限制

ADJ

(能力)理解立体空间的

<div style=""font-weight:bold;"">Your spatial ability is your ability to see and understand the relationships between shapes, spaces, and areas.