What’s your average success rate of getting a SQL statement right on the first try? In best case, you botched a simple statement without side effects and just have to try again with correct syntax or remove that typo from a table name, but things can easily go wrong fast here. But don’t worry, the days of fixing it on the fly can be over, thanks to [Joe Reynolds] who wrote a linter for SQL.
A linter parses code to tell you where you screwed up. While checking SQL syntax itself is somewhat straightforward, [Joe]’s
sql-lint tool will also check the semantics of it by looking up the actual database and performing sanity checks on it. Currently supporting PostgreSQL and MySQL, it can be either run on a single SQL file or a directory of files, or take input directly from the command line. Even better, it also integrates within your editor of choice — assuming it supports external plugins — and the documentation shows how to do that specifically for Vim.
If you can look past the fact that it’s written in TypeScript and consequentially results in a rather large executable (~40 MB), it might serve as an interesting starting point for the language itself, or adds a new perspective on writing this type of analyzer. And if databases aren’t your terrain, how about shell scripts?
Continue reading “Get Your SQL Statements Right The First Time With SQL Lint”
Sensor network projects often focus primarily on electronic design elements, such as architecture and wireless transmission methods for sensors and gateways. Equally important, however, are physical and practical design elements such as installation, usability, and maintainability. The SENSEation project by [Mario Frei] is a sensor network intended for use indoors in a variety of buildings, and it showcases the deep importance of physical design elements in order to create hardware that is easy to install, easy to maintain, and effective. The project logs have an excellent overview of past versions and an analysis of what worked well, and where they fell short.
One example is the power supply for the sensor nodes. Past designs used wall adapters to provide constant and reliable power, but there are practical considerations around doing so. Not only do power adapters mean each sensor requires some amount of cable management, but one never really knows what one will find when installing a node somewhere in a building; a power outlet may not be nearby, or it may not have any unoccupied sockets. [Mario] found that installations could take up to 45 minutes per node as a result of these issues. The solution was to move to battery power for the sensor nodes. With careful power management, a node can operate for almost a year before needing a recharge, and removing any cable management or power adapter meant that installation time dropped to an average of only seven minutes.
That’s just one example of the practical issues discovered in the deployment of a sensor network in a real-world situation, and the positive impact of some thoughtful design changes in response. The GitHub repository for SENSEation has all the details needed to reproduce the modular design, so check it out.
High schooler [Vlad] spent about a year building up his battery-operated, wireless weather station. Along the way, not only has he learnt a lot and picked up useful skills, but also managed to blog his progress.
The station measures temperature, humidity, pressure and battery voltage, and he plans to add sensors for wind speed, wind direction and rainfall soon. It is powered via a solar panel and can run on a charged battery for a full month. The sensor module transmits data to a remote receiver connected to a computer from where it is published to the internet. Barometric pressure is measured using the BMP180 and the DHT22 provides temperature and humidity values. The link between the transmit and receive sections uses a 433MHz Superhetrodyne RF Kit which gives [Vlad] a range of 50m. There’s an ATMega328 on the transmitter and receiver side. He’s taking measurements once every 12 minutes, and putting the micro controller in low power mode using the Rocket Scream Low Power Library. A 5W, 12V solar panel charges the 6V Lead Acid battery via a LM317 based charge circuit. This ensures the battery gets charged even when the solar panel is not receiving optimal radiation. One hour of sunlight provides enough charge to keep it going for 2 days. And a fully charged battery will keep it running for a full month even when there’s no sunlight.
The server software consists of two parts. The first pushes serial data to a mySQL database. This is written in Visual Studio C# using help from Oracle mySQL connector. The second part publishes the entries in the mySQL database to the web server. This is written in php, and uses Libchart for graphing. He’s got the code, schematics, parts list and a lot of other information available for download on his blog. There’s a couple of items pending on his to-do list, so if you have any tips to offer post your comments below.
Here’s a Raspberry Pi hack that adds web control using PHP and MySQL. As you can see in the image, it serves up a webpage (using the Apache2 server) which allows you to change the state of the GPIO pins. It’s not super-complicated, but it is nice to see a step-by-step guide for installing and configuring the package.
Web interface GPIO control is one of the features we loved about the Adafruit Web IDE. But this offering is loaded completely from the RPi (the Adafruit package uses cloud based code) and utilizes the tools most Linux network admins will be used to. A MySQL database manages the connection between GUI commands and GPIO modification. The webpage is served up by a PHP script which takes care of polling and changing database values. Configuration requires a new database, plus the username and password which has access to it.
We’re all familiar with IVRS systems that let you access information using a touch-tone telephone. [Achu Wilso] built his own version which uses a cellphone, microcontroller, and computer.
The cellphone is monitored by an LM324 op-amp with an attached 555 timer chip. When a call comes in the voltage on the headphone output goes high, activating the timer circuit. If it goes low and does not go high again for about 25 seconds the call will be ended. Each incoming touch tone acts as a keepalive for the circuit.
An MT8870 DTMF (touch tone) decoder chip monitors the user input. An ATmega8 microcontroller grabs the decoded touch tones from that chip, and pushes them to a PC via USB. The PC-side software is written in Python, using MySQL bindings to access database information. eSpeak, the open source speech synthesizer software is used to read menu and database information back to the caller.
Not a bad little system, we wish there was an audio clip so we could hear it in action.
Bittorrent is a great distribution method for large files, but its heavy bandwidth usage can be disruptive to both work and home networks. [Brett O’Connor] has decided to push all of his torrenting activity into the cloud. Amazon’s EC2 service lets you run any number of Amazon Machine Images (AMI, virtual machines) on top of their hardware. You pay for processing time and data transferred. [Brett] put together a guide for building your own seedbox on the service. First, you set up the Security Group, the firewall for the machine. Next, you specify what AMI you want to use. In this example, it’s a community build of Ubuntu. Once you have your SSH keypair, you can start the instance and install Apache, PHP, and MySQL. TorrentFlux is the web frontend for bittorrent in this case. It manages all the torrents and you just need to click download when you want to grab the completed file.
Even if you don’t plan on setting up a seedbox, the post is a straightforward example of how-to get started with EC2. He’s not sure what the cost will be; the current estimate is ~$30/mo.
mySQLgame is a quirky App Engine game that has decided to forgo flashy UIs and just stick with the core of the system: a database. You start the game by creating your own row in the shared game database. Game time increments your fuel and money resources every ten seconds. Fuel is spent on scanning other rows and for attacking. Money is used for upgrades and building units. Build up resources and attack your neighbors using database queries, just like any other game. Like a bit more gloss on your internet metagames? Have a look at trolling simulator Forumwarz.