Admin access to local WifiDB

Talk about random things WiFiDB related, either new things, concepts, or issues to work out in code.
Post Reply
mattydiah
Junior Member
Posts: 6
Joined: Wed Nov 13, 2019 12:56 am

Admin access to local WifiDB

Post by mattydiah » Wed Nov 13, 2019 1:00 am

I have WifiDB installed and running on an ubuntu box. However, I cannot figure out how to create an administrative account. I turned account activation off, as this wont be a public instance. Then I created myself an account. The only way I could figure to make myself an admin was to run " update user_info set admin = 1 where username = '<my_user>'; in mysql, but that either didnt work, or I dont know where to go to see the admin control panel.

Can you provide some insight?

Thanks

mattydiah
Junior Member
Posts: 6
Joined: Wed Nov 13, 2019 12:56 am

Re: Admin access to local WifiDB

Post by mattydiah » Wed Nov 13, 2019 8:26 am

Also, should I be using the "ac-wifidb-sqlsvr" branch, or the master?

User avatar
ACalcutt
Vistumbler / TechIdiots Admin
Vistumbler / TechIdiots Admin
Posts: 1217
Joined: Sun Oct 21, 2007 6:50 pm
Location: Rutland, MA
Contact:

Re: Admin access to local WifiDB

Post by ACalcutt » Sat Nov 16, 2019 1:23 am

at this time there really isn't an admin control panel. there was a basic admin panel at one time, but it was never recreated when the wifidb got redesigned and changed to smarty templates. The settings are mainly in config files 'wifidb/lib/config.ini.php' and 'tools/daemon.config.inc.php', or in the settings/schedule table

master and ac-wifidb-sqlsvr are basically the same right now (ac-wifidb-sqlsvr is slightly ahead, with added kismet support). However if you are using mysql instead of microsoft sql, you may have some trouble actually importing. most of this release is compatible with mysql and microsoft sql, but there are several spots in the import where I changed to a microsoft sql merge statement because I noticed in the way I was importing in mysql was causing duplicates. In those few cases, I don't think I left the mysql code in place, since they didn't have the merge statement and I'm not using mysql at the moment.

mattydiah
Junior Member
Posts: 6
Joined: Wed Nov 13, 2019 12:56 am

Re: Admin access to local WifiDB

Post by mattydiah » Sat Nov 16, 2019 10:57 am

Ok, so I’m better off running this on an MSSQL DB? I think I should be able to just edit the config files and repoint the database it looks for. Hopefully it will work with SQL Express.

One other question, when I go to the map, it shows all of the entries from everyone on wifidb.net. How do I change it so the map only looks at my dataset and not yours?

Thanks for your help!!

User avatar
ACalcutt
Vistumbler / TechIdiots Admin
Vistumbler / TechIdiots Admin
Posts: 1217
Joined: Sun Oct 21, 2007 6:50 pm
Location: Rutland, MA
Contact:

Re: Admin access to local WifiDB

Post by ACalcutt » Sat Nov 16, 2019 11:54 am

Yes, at the moment MSSQL is probably the best to go with, since that is what I am testing with here. I am currently using SQL 2019 for linux (centos) with a database in the new unicode collation (Latin1_General_100_CI_AS_SC_UTF8). You would set up your tables with the 'blank_db.sqlsrv' file instead of 'blank_db.sql'

The map question is a bit more complicated, basically you first need a tile server to host you map content. I am using TileServer GL hosted at my domain https://omt.wifidb.net/, which is where all the basemaps and wifidb map data is stored. I originally started by using the free OpenMapTiles, which gives you tileserver-gl and a free base map, which worked well but didn't have very detailed maps (unless you pay them over $1000). I eventually set up my own TileServer GL instance, somewhat more more detailed base map ( https://github.com/acalcutt/wdb-map-gen ), which I'd willing to share if interested (it took forever to create)

The current process looks like this
1.) On the wifidb server a cron job (tools/cron/update_geojson) runs '/daemon/geojsond.php' and creates geojson files in '/wifidb/out/geojson'

2.) On the tile server I have a script that downloads those geojson files and the uses a software called tippecanoe to convert them from geojson into the mapbox format needed by Tileserver-GL. My script looks like this

Code: Select all

#!/bin/bash
PATH="/root/.nvm/versions/node/v8.11.3/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin"
echo "---Cleanup Old Files---"
if [ -e "/opt/convert/WifiDB_weekly.json" ]; then rm /opt/convert/WifiDB_weekly.json; fi
if [ -e "/opt/convert/WifiDB_monthly.json" ]; then rm /opt/convert/WifiDB_monthly.json; fi
if [ -e "/opt/convert/WifiDB_0to1year.json" ]; then rm /opt/convert/WifiDB_0to1year.json; fi
if [ -e "/opt/convert/WifiDB_1to2year.json" ]; then rm /opt/convert/WifiDB_1to2year.json; fi
if [ -e "/opt/convert/WifiDB_2to3year.json" ]; then rm /opt/convert/WifiDB_2to3year.json; fi
if [ -e "/opt/convert/WifiDB_Legacy.json" ]; then rm /opt/convert/WifiDB_Legacy.json; fi
if [ -e "/opt/convert/cell_networks.json" ]; then rm /opt/convert/cell_networks.json; fi
if [ -e "/opt/convert/WifiDB.mbtiles" ]; then rm /opt/convert/WifiDB.mbtiles; fi
if [ -e "/opt/convert/WifiDB_newest.mbtiles" ]; then rm /opt/convert/WifiDB_newest.mbtiles; fi
if [ -e "/opt/convert/cell_networks.mbtiles" ]; then rm /opt/convert/cell_networks.mbtiles; fi

echo "---Downloading WifiDB.json---"
scp -r user@serverip:/srv/www/virtual/live.wifidb.net/wifidb/out/geojson/WifiDB_weekly.json /opt/convert/
scp -r user@serverip:/srv/www/virtual/live.wifidb.net/wifidb/out/geojson/WifiDB_monthly.json /opt/convert/
scp -r user@serverip:/srv/www/virtual/live.wifidb.net/wifidb/out/geojson/WifiDB_0to1year.json /opt/convert/
scp -r user@serverip:/srv/www/virtual/live.wifidb.net/wifidb/out/geojson/WifiDB_1to2year.json /opt/convert/
scp -r user@serverip:/srv/www/virtual/live.wifidb.net/wifidb/out/geojson/WifiDB_2to3year.json /opt/convert/
scp -r user@serverip:/srv/www/virtual/live.wifidb.net/wifidb/out/geojson/WifiDB_Legacy.json /opt/convert/
scp -r user@serverip:/srv/www/virtual/live.wifidb.net/wifidb/out/geojson/cell_networks.json /opt/convert/

echo "---Convert Geojson to mbtiles---"
/opt/tippecanoe/tippecanoe -z16 --cluster-densest-as-needed --maximum-tile-features=300000 --maximum-tile-bytes=750000 -r1 -n "WifiDB_newest $(date -d "today" +"%Y-%m-%d %H:%M")" -A "<a href=""https://wifidb.net/"" target=""_blank"">&copy; WifiDB $(date -d "today" +"%Y-%m-%d")</a>" -o /opt/convert/WifiDB_newest.mbtiles /opt/convert/WifiDB_weekly.json /opt/convert/WifiDB_monthly.json /opt/convert/WifiDB_0to1year.json
/opt/tippecanoe/tippecanoe -z16 --cluster-densest-as-needed --maximum-tile-features=300000 --maximum-tile-bytes=750000 -r1 -n "WifiDB $(date -d "today" +"%Y-%m-%d %H:%M")" -A "<a href=""https://wifidb.net/"" target=""_blank"">&copy; WifiDB $(date -d "today" +"%Y-%m-%d")</a>" -o /opt/convert/WifiDB.mbtiles /opt/convert/WifiDB_1to2year.json /opt/convert/WifiDB_2to3year.json /opt/convert/WifiDB_Legacy.json
/opt/tippecanoe/tippecanoe -z16 --cluster-densest-as-needed --maximum-tile-features=300000 --maximum-tile-bytes=750000 -r1 -n "WifiDB_cells_ $(date -d "today" +"%Y-%m-%d %H:%M")" -A "<a href=""https://wifidb.net/"" target=""_blank"">&copy; WifiDB $(date -d "today" +"%Y-%m-%d")</a>" -o /opt/convert/cell_networks.mbtiles /opt/convert/cell_networks.json

echo "---Move file and restart tileserver gl---"
mv /opt/convert/WifiDB_newest.mbtiles /opt/tileserver-gl/data/
mv /opt/convert/WifiDB.mbtiles /opt/tileserver-gl/data/
mv /opt/convert/cell_networks.mbtiles /opt/tileserver-gl/data/
/etc/init.d/tileserver stop
/etc/init.d/tileserver start
3.) The script automatically restarts tileserver-gl, and the tileserver config.json points to your mapbox files like this

Code: Select all

{
  "options": {
    "paths": {
      "root": "/opt/tileserver-gl/",
      "fonts": "fonts",
      "styles": "styles",
      "mbtiles": "data"
    }
  },
  "styles": {
    "WDB_ESRI": {
      "style": "WDB_ESRI/style.json",
      "tilejson": {
        "bounds": [
          -180,
          -85.0511,
          180,
          85.0511
        ]
      }
    },
    "WDB_ESRIOSM": {
      "style": "WDB_ESRIOSM/style.json",
      "tilejson": {
        "bounds": [
          -180,
          -85.0511,
          180,
          85.0511
        ]
      }
    },
    "WDB_LIBERTY": {
      "style": "WDB_LIBERTY/style.json",
      "tilejson": {
        "bounds": [
          -180,
          -85.0511,
          180,
          85.0511
        ]
      }
    },
    "WDB_OSM": {
      "style": "WDB_OSM/style.json",
      "tilejson": {
        "bounds": [
          -180,
          -85.0511,
          180,
          85.0511
        ]
      }
    },
    "WDB_POSITRON": {
      "style": "WDB_POSITRON/style.json",
      "tilejson": {
        "bounds": [
          -180,
          -85.0511,
          180,
          85.0511
        ]
      }
    }
  },
  "data": {
    "v3": {
      "mbtiles": "osm-2018-07-16-v3.8-planet.mbtiles"
    },
    "ESRI": {
      "mbtiles": "ERSI_WI_0-11.mbtiles"
    },
    "NE2": {
      "mbtiles": "NE2_HR_LC_SR_W_DR.mbtiles"
    },
    "WifiDB_newest": {
      "mbtiles": "WifiDB_newest.mbtiles"
    },
    "WifiDB": {
      "mbtiles": "WifiDB.mbtiles"
    },
    "WdbMapTiles": {
      "mbtiles": "WdbMapTiles.mbtiles"
    },
    "WifiDB_cells": {
      "mbtiles": "cell_networks.mbtiles"
    }
  }
}
4.) The are also custom styles referenced in the tileserver config.json, which tell tileserver how you want to layer the basemap layers and wifidb layers. I've included this in the attached zip

5.) Right now I haven't added any config options to set the tile server used in the wifidb (i plan to eventually). You would have to go into wifidb/opt/map.php and change all the references to omt.wifidb.net to wherever your tile server is located
Attachments
tileserver_styles-config.zip
(6.42 MiB) Downloaded 7 times

mattydiah
Junior Member
Posts: 6
Joined: Wed Nov 13, 2019 12:56 am

Re: Admin access to local WifiDB

Post by mattydiah » Sat Nov 16, 2019 12:05 pm

Awesome! Thank you! It’ll take me a bit to digest that but I’ll be back with questions I’m sure!

User avatar
ACalcutt
Vistumbler / TechIdiots Admin
Vistumbler / TechIdiots Admin
Posts: 1217
Joined: Sun Oct 21, 2007 6:50 pm
Location: Rutland, MA
Contact:

Re: Admin access to local WifiDB

Post by ACalcutt » Sat Nov 16, 2019 12:59 pm

Also, if you use SQL express edition I think you should be fine, though you may be missing some features in management studio that are nice.

in SQL 2019 for linux there is also a developer version, which is free and has all the features of enterprise with the stipulation your aren't using it in production. (https://docs.microsoft.com/en-us/sql/li ... rver-ver15)

mattydiah
Junior Member
Posts: 6
Joined: Wed Nov 13, 2019 12:56 am

Re: Admin access to local WifiDB

Post by mattydiah » Sat Nov 16, 2019 2:45 pm

I completely forgot MSSQL was linux native now. I'll just use that, and this is for sure non-prod, so I should be able to put it into dev mode no sweat.

mattydiah
Junior Member
Posts: 6
Joined: Wed Nov 13, 2019 12:56 am

Re: Admin access to local WifiDB

Post by mattydiah » Sat Nov 16, 2019 2:49 pm

Also, if you'd be willing to share your your basemap, I'd love to use it. Thanks!

User avatar
ACalcutt
Vistumbler / TechIdiots Admin
Vistumbler / TechIdiots Admin
Posts: 1217
Joined: Sun Oct 21, 2007 6:50 pm
Location: Rutland, MA
Contact:

Re: Admin access to local WifiDB

Post by ACalcutt » Sun Nov 17, 2019 3:35 pm

Heres the two main basemaps you need with my styles, the OpenStreetMaps basemap and the natural earth basemap
https://drive.google.com/drive/folders/ ... sp=sharing

Post Reply