Implement SQLite Database

[imdunne8]

If time and interest are shown, we should implement a SQLite database on the Pi scouting server. A simple database will need to be created on the Pi, and file permissions will probably need to be modified. The PHP logger script will have to be updated to write data to the database. The download button will have to be modified as well.

The ultimate goal would be to create a web-based dashboard to replace some or all of the role of the existing Excel spreadsheet. This would be more user friendly and hopefully something students can be more involved with. As a start, some simple reports we could make for this year include:

• What defenses should be selected when opposing team X
• What are some basic offensive statistics about team X

[djthune66]

Good idea. Probably needs some discussion this evening. Just want to make sure it's easy to revert to what is working now. If we can get the Data posting to the DB the excel import shouldn't be hard to update as long as the fields are in the same order. I'm familiar with MS TSQL tools but I may need to get a quick tour of tools for SQL-lite. Dan Thune

"Imagination is more important than knowledge" Albert Einstein

On Apr 5, 2016, at 10:45 AM, Nick Dunne notifications@github.com wrote:

If time and interest are shown, we should implement a SQLite database on the Pi scouting server. A simple database will need to be created on the Pi, and file permissions will probably need to be modified. The PHP logger script will have to be updated to write data to the database. The download button will have to be modified as well.

The ultimate goal would be to create a web-based dashboard to replace some or all of the role of the existing Excel spreadsheet. This would be more user friendly and hopefully something students can be more involved with. As a start, some simple reports we could make for this year include:

What defenses should be selected when opposing team X What are some basic offensive statistics about team X — You are receiving this because you are subscribed to this thread. Reply to this email directly or view it on GitHub

[imdunne8]

I've pushed up a SQLite database with our CIR data to master. Anyone can feel free to pull it down and play with it to start to understand SQLite. You can either use the SQLite command line tool from sqlite.org or you can download a SQLite viewer/manager tool such as SQLiteStudio. I'll try to give an overview of SQL and SQLite on Thursday. Below are the basics of using SQLiteStudio:

• Add the database using the Databases menu at the top
• Ensure you are connected to it (you can right click it in the left window)
• Expand MatchData.db in the left window and double click on the CIRData table
• Click the Data tab in the right window to view data
• Click the pencil icon in the toolbar to write a query, then click the lightning bolt to execute it
• Example query to show team numbers in order: SELECT DISTINCT TeamNumber FROM CIRData ORDER BY TeamNumber;