sqlite on raspberrypi

not a big deal with that
install the sqlite3
sudo apt-get install sqlite3

now you can read db-sqlite-files. here are some example and useful SELECT-statements

# open db
sqlite3
# list all tables
SELECT name FROM sqlite_master WHERE type=’table‘;
# list columns
SELECT sql FROM sqlite_master WHERE tbl_name = ‚xxx‘ AND type = ‚table‘;
# convert datetime
select strftime(‚%Y-%m-%d‘, published_at / 1000, ‚unixepoch‘), title from posts order by published_at;
# exit
.exit

Advertisements

spotify on raspberry pi

system configuration

enable ipv6
first, check if its loaded

sudo sysctl -a|grep ipv6

if not load it

sudo modprobe ipv6

and also load it on every reboot with:

echo ipv6|sudo tee -a /etc/modules

install mopidy

main documentation from here

if you have another running instance of mpd/mpc shut it down – because mopidy has its own mpd/mpc

1. Add the archive’s GPG key:
wget -q -O – https://apt.mopidy.com/mopidy.gpg | sudo apt-key add –
2. Add the following to /etc/apt/sources.list, or if you have the directory /etc/apt/sources.list.d/, add it to a file called mopidy.list in that directory:
# Mopidy APT archive
deb http://apt.mopidy.com/ stable main contrib non-free
deb-src http://apt.mopidy.com/ stable main contrib non-free
For the lazy, you can simply run the following command to create /etc/apt/sources.list.d/mopidy.list:
sudo wget -q -O /etc/apt/sources.list.d/mopidy.list https://apt.mopidy.com/mopidy.list
3. Install Mopidy and all dependencies:
sudo apt-get update
sudo apt-get install mopidy

about 25MB disk space will be used

4. now you can choose, if you want to run mopidy as a service (in the background) or only for testing as a user instance. (see also here)
running it as a user instance – first disable the service and shut it down
sudo update-rc.d mopidy disable
sudo service mopidy stop
start mopidy with
mopidy

to configure it as a service first check if its running
ps aux | grep ‚mopidy‘
sudo service mopidy status

or start it
sudo service mopidy start

service commands
sudo service mopidy start
sudo service mopidy stop
sudo service mopidy restart
sudo service mopidy status

5. configuration
the config-file contains only changes to the default config. check your config with
user
mopidy config
service
sudo mopidyctl config

user-configuration-file
nano ~/.config/mopidy/mopidy.conf

service-configuration-file
sudo nano /etc/mopidy/mopidy.conf

sample-config for the http-extension
[http]
enabled = true
hostname = ::
port = 8081
WARNING – i had problems with the hostname set to the local ip address. my spotify playlists were missing after installing the http-client.
sample for local
[local]
enabled = true
data_dir = /var/lib/mopidy/local
media_dir = /data/music
playlists_dir = /data/radio
WARNING – i had problems with my m3u radio files/playlists. after putting them into a seperate folder outside of my music folder they were loaded successfully.
sample
enabled = true
username = 1234567890
password = abcdefghijklm12345
bitrate = 320
here no warnings – use bitrate to encrease your quality
6. add-ons/extensions

add the nice http-extension MusicBox just do
pip install Mopidy-MusicBox-Webclient

7. log-file

/var/log/mopidy/mopidy.log

other configurations for mopidy

config default Radio stations – just edit the following file

/usr/local/lib/python2.7/dist-packages/mopidy_musicbox_webclient/static/js/radiostations.js

or - on musicbox
/usr/local/lib/python2.7/dist-packages/mopidy_musicbox_webclient/static/js/streamuris.js

Convert iTunes-playlists to spotify

https://support.spotify.com/is/problems/#!/article/Convert-Playlists-to-Spotify

http://groovylists.com/

http://retro.grooveshark.com/#!/playlist/1425111848/104536359

postgresql with OSM on raspberryPI

follow these instruction (german) to install all you need for your own osm-server

install postgresql

first update

  sudo apt-get update
then install the package; about 21MB additional space will be used
  sudo apt-get install postgresql
after that the server should report
  [ ok ] Starting PostgreSQL 9.1 database server: main.
here are some steps to configure the server
install tools for osm
install imposm git postgis and postgresql-9.1-postgis
  sudo apt-get install postgis imposm git postgresql-9.1-postgisnow create a source directory and download osm data
  mkdir ~/src
  cd ~/src && wget http://download.geofabrik.de/europe/slovenia-latest.osm.pbfdownload the following tool, needed later
cd ~/src && git clone https://github.com/mapbox/osm-bright.git

 

run the following scritp as postgres-user, so first create a sh-file
  set -xe
  createuser –no-superuser –no-createrole –createdb osm
  createdb -E UTF8 -O osm osm
  createlang plpgsql osm
  psql -d osm -f /usr/share/postgresql/9.1/contrib/postgis-1.5/postgis.sql
  psql -d osm -f /usr/share/postgresql/9.1/contrib/postgis-1.5/spatial_ref_sys.sql
  psql -d osm -f /usr/share/python-imposm/900913.sql
  echo „ALTER TABLE geometry_columns OWNER TO osm; ALTER TABLE spatial_ref_sys OWNER TO osm;“ |psql -d osm
  echo „ALTER USER osm WITH PASSWORD ‚osm‘;“ |psql -d osm
  set +x
then login as postgres
  sudo su postgres
  sh ./create_db.sh
  exit

now add the following row to your postgre config and restart postgre
  sudo nano /etc/postgresql/9.1/main/pg_hba.conf
  host osm osm 127.0.0.1/32 md5
  sudo /etc/init.d/postgresql restart

to import your first data use (more infos here)
imposm –-read –-write –-optimize -h localhost -p 5432 -d osm -m ~/src/osm-bright/imposm-mapping.py ~/src/slovenia-latest.osm.pbf
this will need quite a time – in my case
  imposm took 2 h 40m 44s

in the mean time you can check your database for new created tables after the message ## dropping/creating tables

in psql you can view your tables with (\connect osm;)

get the version of postgis

SELECT PostGIS_full_version();

postgis_full_version

——————————————————————————————————-

POSTGIS=“1.5.3″ GEOS=“3.3.3-CAPI-1.7.4″ PROJ=“Rel. 4.7.1, 23 September 2009″ LIBXML=“2.8.0″ USE_STATS

(1 row)

  SELECT table_name FROM information_schema.tables WHERE table_schema = ‚public‘;

      table_name
————————–
 spatial_ref_sys
 geometry_columns
 geography_columns
 osm_new_mainroads
 osm_new_buildings
 osm_new_amenities
 osm_new_aeroways
 osm_new_motorways
 osm_new_transport_points
 osm_new_landusages
 osm_new_waterways
 osm_new_railways
 osm_new_waterareas
 osm_new_places
 osm_new_admin
 osm_new_barrierways
 osm_new_minorroads
 osm_new_barrierpoints
 osm_new_roads

get column names from a table
  SELECT column_name FROM information_schema.columns WHERE table_name=’osm_new_roads‘;

 column_name
————-
 class
 type
 access
 z_order
 layer
 oneway
 tunnel
 ref
 bridge
 name
 geometry
 osm_id
 id

and easy to find different types
  SELECT type, COUNT(*)  FROM osm_new_roads GROUP BY type ORDER BY type;

      type      | count
—————-+——-
 bridleway      |     1
 cycleway       |    38
 footway        |   855
 living_street  |    27
 motorway       |   171
 motorway_link  |   247
 path           |   318
 pedestrian     |   139
 primary        |   283
 primary_link   |    26
 rail           |   128
 residential    |  2683
 road           |     5
 secondary      |   581
 secondary_link |    12
 service        |   593
 steps          |    60
 tertiary       |   888
 tertiary_link  |     2
 track          |  1820
 trunk          |    47
 trunk_link     |    47
 unclassified   |  1397
(23 rows)

after the import finished turn your *new* tables to operational tables – WARNING old data will be lost!
  imposm –deploy-production-tables -d osm

lets create tiles

go to the folder ~/src/osm-bright and rename
  mv configure.py.sample configure.py

edit the config file
config[„importer“] = „imposm“
config[„postgis“][„host“]     = „localhost“
config[„postgis“][„port“]     = „5432“
config[„postgis“][„dbname“]   = „osm“
config[„postgis“][„user“]     = „osm“
config[„postgis“][„password“] = „osm“
download additional files to ~/src/osm-bright (coastline has ~310MB, shoreline ~45MB, 10m-land 5MB)

  wget http://tilemill-data.s3.amazonaws.com/osm/coastline-good.zip
wget http://tilemill-data.s3.amazonaws.com/osm/shoreline_300.zip
wget http://mapbox-geodata.s3.amazonaws.com/natural-earth-1.3.0/physical/10m-land.zip

create this dir
  mkdir ~/Documents/MapBox/project

psql

get server version
  select version();
PostgreSQL 9.1.14 on armv7l-unknown-linux-gnueabihf, compiled by gcc (Debian 4.6.3-14+rpi1) 4.6.3, 32-bit
  CREATE DATABASE mydb;
response: CREATE DATABASE
drop a database
  DROP DATABASE mydb;
  CREATE TABLE account(
    user_id serial PRIMARY KEY,
    username VARCHAR (50) UNIQUE NOT NULL,
    password VARCHAR (50) NOT NULL,
    email VARCHAR (355) UNIQUE NOT NULL,
    created_on TIMESTAMP NOT NULL,
    last_login TIMESTAMP
  );
as you see, squences, keys and uniques will be generated automatically
drop table also dropps all autogenerated objects
get tablenames
  SELECT table_name FROM information_schema.tables WHERE table_schema = ‚public‘;useful documentation to access postgres from external device (eg qgis…)
important commands/files for postgre
config file
  sudo nano /etc/postgresql/9.1/main/pg_hba.conf
restart server
  sudo /etc/init.d/postgresql restart
enter server with psql
  psql -U postgres
exit with CTRL-D
show data align after the query
  -S

manual installation of postGIS

download from http://postgis.net/source
sudo apt-get install libpq-dev libproj-dev postgresql-server-dev-9.1
install geos and gdal – see http://northwestspatial.com/wp/?p=340
download source
./configure
sudo make install
test it with
geos-config –version
gdal-config –version
then update apt-get
sudo ldconfig
download source for postGIS and

./configure –with-topology –with-raster
sudo make install
restart postgresql
sudo service postgresql restart

use this documentation to install postGIS to postgresql
http://postgis.net/docs/manual-2.1/postgis_installation.html#install_short_version

QGIS and OSM

Download Data from OSM

1. Download
in the QGIS menu go to Vector – OpenStreetMap – Download Data and define your data rectangular (a little help from that OpenLayers mouse position sample) or zoom to a location and choose „from layer“. define an output file.
2. Convert to db
go to same menu entry -> Import Topology from XML and define an output file. After converting the osm to db-file you can view the content with DB Manager (Menu: Database – DB Manager – DB Manager).
3. from db to QGIS
then use the menu entry „Export OpenStreetMap Topology to SpatialLite“. set input db-file. press the button „Load from DB“ to get all Tags check all Tags you want to import OK geofabrik-pbf/osm Download data from geofabrik.de: download pbf (direct import to qgis, but less attribute details) or osm.bz2 (zipped osm-file). Unzip the bz2 and open it. geofabrik-shp Download zipped shp-files and use them directly in QGIS. http://download.geofabrik.de/europe.html

Printing OSM-Tiles

  • Scales for correct printing of osm-tiles (used with TileMapScaleLevels) with print-composer
7151
14302
28604
57208
114416
228832
457664
915328
1830656
3661312
7322624
14645248
29290496
58580992 …
  • DPI: i used 150 to 180 dpi for a readable result

raspberry pi as cd ripper

it’s very easy! i used this tutorial
install abcde (A Better CD Encoder), flac (for the „free lossless audio codec„) and eject to eject to cd.


  sudo apt-get install abcde flac eject
test the eject program to open your cd/dvd-drive

  eject

now copy the original config-file from abcde to a temp-folder and replace the original with the following commands. for your system you have just to modify the path where your flac-files should be stored (edit OUTPUTDIR=/media/usb/cd-rip).


CDDBMETHOD=musicbrainz
CDDBPROTO=6
HELLOINFO=“`whoami`@`hostname`“
NOSUBMIT=y
FLACENCODERSYNTAX=default
CDROMREADERSYNTAX=cdparanoia
PADTRACKS=y
INTERACTIVE=n
LAME=lame
FLAC=flac
ID3=id3
ID3V2=id3v2
CDPARANOIA=cdparanoia
EJECT=eject
METAFLAC=metaflac
CDSPEED=eject
LAMEOPTS=‘–preset extreme‘
FLACOPTS=“-f –best“
ACTIONS=cddb,read,encode,tag,move,clean
CDROM=/dev/sr0
OUTPUTDIR=/media/usb/cd-rip
WAVOUTPUTDIR=/tmp
OUTPUTTYPE=flac
OUTPUTFORMAT=’${ARTISTFILE}/${ALBUMFILE}/${TRACKNUM}.${TRACKFILE}‘
VAOUTPUTFORMAT=’Various/${ALBUMFILE}/${TRACKNUM}.${ARTISTFILE}-${TRACKFILE}‘
ONETRACKOUTPUTFORMAT=$OUTPUTFORMAT
VAONETRACKOUTPUTFORMAT=$VAOUTPUTFORMAT
LOWDISK=n
PLAYLISTFORMAT=’${ARTISTFILE}/${ALBUMFILE}.${OUTPUT}.m3u‘
VAPLAYLISTFORMAT=’${ARTISTFILE}/${ALBUMFILE}.${OUTPUT}.m3u‘
EJECTCD=y

now try to encode some tracks of your cd, eg track 1, 2, 4
  sudo abcde 1-2 4

to convert to mp3 also install lame codec and id3 and id3c2
  sudo apt-get install lame id3 id3v2

now start to rip to flac and mp3 in one step (eg only track 1, 2, 4)
  sudo abcde -o „flac“,“mp3″ 1-2 4

next step is to automate the ripping task

create a new file

  sudo nano /usr/local/sbin/audio-cd-rip.sh

Now the few lines of code
#!/bin/bash
LOGFILE=/var/log/cdrip.log
echo „$(date)“ >> $LOGFILE

(
# Wait for lock on /var/lock/.audio-cd-rip.lock (fd 200) for two hours
flock -x -w 7200 200 || exit 1

abcde 2>&1 >> $LOGFILE
rc=$?
if [[ $rc != 0 ]] ; then
eject
exit $rc
fi
) 200>/var/lock/.audio-cd-rip.lock
Mark the script executable.
  sudo chmod +x /usr/local/sbin/audio-cd-rip.sh
Now we create a udev rule that runs this script. A udev rule lets the udev service call our script it the conditions of the rule match.
  sudo nano /etc/udev/rules.d/99-cd-audio-processing.rules
Insert one single line:
SUBSYSTEM==“block“, KERNEL==“sr0″, ACTION==“change“, RUN+=“/usr/local/sbin/audio-cd-rip.sh &“
This says, that the script shall run when a CD with at least one audio track is inserted in block device sr0 (which is usually your first CDROM/DVD/WHATEVER-Drive). The script will run in background (you see the ‘&’?). Now reload udev rules
  sudo udevadm control –reload

due to bugs in the OS you have to make the following work-around to „active“ the cd-drive

  sudo crontab -eAdd this line at the end
  */1 * * * * /bin/touch /dev/sr0

but the cd-drive always opens and closes – so that’s not a good workaround 😦

ghost on raspberry pi

first install node.js. use my tutorial or another

then download the latest version of ghost and unzip the file (info from here)

  unzip -d ghost [Name-of-Ghost-zip].zip

then execute the following commands

  cd ghost/
  sudo npm install –production

  cp config.example.js config.js

then edit the config.js file:

in the development-section and in the production-section edit the url to your IP/DOMAIN and the server host to your local IP and port. for development and production use different ports!

go to your installation directory and start ghost with
  npm start

to auto-start ghost i found this tutorial using forever (and others)
Forever (https://npmjs.org/package/forever)
You can use forever to run Ghost as a background task. forever will also take care of your Ghost installation and it will restart the node process if it crashes.
To install forever type

  npm install forever -g
To start Ghost using forever from the Ghost installation directory type
  NODE_ENV=production forever start index.js
To stop Ghost type
  forever stop index.js
To check if Ghost is currently running type

  forever list

to edit/make themes read this

images/photos

are stored in content/images/YYYY/mmm

update (from here)

Warning: Do NOT copy and paste the entire Ghost folder over the top of an existing installation on mac. Do NOT choose REPLACE if uploading with Transmit or other FTP software, choose MERGE.
  1. Update/upgrade pi
  2. Download the latest version of Ghost from Ghost.org
  3. Extract the zip file to a temporary location
  4. Delete existing index.js and package.json then copy new index.js and package.json files to the same directory.
  5. Next, delete the old core directory completely, and then put the new core directory in its place.
  6. For releases which include update to Casper (the default theme), delete the old content/themes/casper directory and put the new one its place
  7. Run sudo npm install –production
  8. Finally, Restart Ghost so that the changes take effect

edit html

to add custom elements (js, css, …) to your post/site edit the following file – don’t forget to restart ghost after editing this file!
ghost/content/themes/<yourTheme>/default.hbs

call the js-function eg with on-load-method

<body class="{{body_class}}" onload='testfkt();'>

get the text of a post

document.getElementsByClassName("post-content")[0].innerText

add new fonts

1. add font to default.hbs

<link rel="stylesheet" type="text/css" href="//fonts.googleapis.com/css?family=Noto+Serif:400,700,400italic|Open+Sans:700,400|UnifrakturMaguntia&subset=all" />

2. add font to css

@font-face { font-family: "myFont"; src: local("myFont"), url("https://dl.dropboxusercontent.com/u/123456/myFont.ttf") format("TrueType") }

solve performance problems for login

this discussion gives a nice hint…
go to your root-dir of ghost and then:

sudo npm install bcrypt

This takes a while…
Then in core/server/models/user.js, change

bcrypt         = require('bcryptjs')

to

bcrypt         = require('bcrypt')

then restart ghost and 🙂

edit first page / homepage

the first page lists the latest blog-posts. to edit eg the number of words for each blog-post that should be displayed here, edit the file ghost/content/themes/<theme>/partials/loop.hbs and change the attribute excerpt words.

see doc

<section class="post-excerpt">
 <p>{{excerpt words="10"}} <a class="read-more" href="{{url}}">&raquo;</a></p>
 </section>

remove this block from your screen.css to delete the circle between posts on the start-page

.post:after {
 display: block;
 content: "";
 width: 7px;
 height: 7px;
 border: #E7EEF2 1px solid;
 position: absolute;
 bottom: -5px;
 left: 50%;
 margin-left: -5px;
 background: #FFF;
 -webkit-border-radius: 100%;
 -moz-border-radius: 100%;
 border-radius: 100%;
 box-shadow: #FFF 0 0 0 5px;
}

links

upgrade ghost
ghost config
ghost forum

 

Button press with python & GPIO on raspberry pi

the code sample is taken from here.
i just use one button with the interrupt version, but i’m not quite satisfied with my dummy-button work around 😦

every button press mpd is started or stopped (see my_callback).

#!/usr/bin/env python2.7  
# script by Alex Eames http://RasPi.tv  
  
from datetime import datetime
import RPi.GPIO as GPIO  
import os
import mpd

GPIO.setmode(GPIO.BCM)  
  
# GPIO 23 & 24 set up as inputs. One pulled up, the other down.  
# 23 will go to GND when button pressed and 24 will go to 3V3 (3.3V)  
# this enables us to demonstrate both rising and falling edge detection  
GPIO.setup(23, GPIO.IN, pull_up_down=GPIO.PUD_UP)  
# dummy !!
GPIO.setup(24, GPIO.IN, pull_up_down=GPIO.PUD_DOWN)  
  
# now we’ll define the threaded callback function  
# this will run in another thread when our event is detected  
def my_callback(channel):
    print str(datetime.now())
    client = mpd.MPDClient(use_unicode=True)
    client.connect(„localhost“, 6600)
    #print client.status()
    if client.status()[’state‘] in (‚play‘, ‚pause‘):
        client.stop()
    else:
        client.play()

# The GPIO.add_event_detect() line below set things up so that  
# when a rising edge is detected on port 23, regardless of whatever   
# else is happening in the program, the function „my_callback“ will be run  
# Stop the program with CTRL+C
GPIO.add_event_detect(23, GPIO.FALLING, callback=my_callback, bouncetime=300)
  
try:  
    print „Waiting for falling edge on port 24“  
    GPIO.wait_for_edge(24, GPIO.RISING)  
    print „Falling edge detected. Here endeth the second lesson.“  

except KeyboardInterrupt:  
    GPIO.cleanup()       # clean up GPIO on CTRL+C exit  

GPIO.cleanup()           # clean up GPIO on normal exit   

i used this mpd library for python (see doc for the library)
to install it, just do
  sudo pip install python-mpd2
to start the python scritp at system start add a line to /etc/rc.local
  sudo nano /etc/rc.local At the bottom, just above exit 0 we’ll add a call to our script (taken from here
  python ../myscript.py
playlists cleanup with python
  client.clear()
add new playlists (or songs with .add) to mpc
  client.load(„bbc6.m3u“)
the file bbc6.m3u contains just