Software ideas/Database Tools

PyLiteAdmin -- Database Competence is Discoverable

SQLite is included in python 2.5 and therefore on every XO.

This auspicious situation presents an opportunity to provide early hands-on experience with SQL and a local database application. Once database concepts are understood, the student can graduate to the more complex client/server environment which has become one of the dominant paradigms for online web applications (using Linxux, Apache, Mysql, Php --LAMP). The school server will be a LAMP server.

This project proposes to create a database administration tool for SQLite which mimics the look and the feel of Phpmyadmin in relation to MySQL. It stands on the shoulders of pysqlitegui, available on the separately via google.

Contact the proposer at georgejhunt(at)gmail(dot)com

General Description

The application will be presented as a single page with clickable functions in tabs along the top and a left hand column (DbSelector) showing the tables and fields as a two level tree.

These two axes, the top row of tabs, and the left column for database selection, provide the visual anchors for discovery and exploration. The lower right portion of the screen provides feedback or interactivity that is particular to the tab selected.

Tabbed Functions

Browse

This is the tab which is selected when the application starts. By default, startup should bring up the last used database, so that all or the tabs and database objects along the left column cause something to happen on the screen when they are clicked.

The output panel show a table with field names along the top and record down the page. Selecting fields from the DbSelector displays just those fields in the table

Structure

When this tab is selected, the left column becomes only one level deep, displaying the table names but not the field names. By default the first table is highlighted in the left column and the field information is displayed in table form.

The field names, types can be changed, and new fields can be inserted into the table or new tables can be defined.

Sql

This will function as the recordset generator in Dreamweaver (for those who have experience with that). By selecting field names, and pushing "SELECT", "WHERE", and "ORDER BY" buttons, the user can assemble a SQL select command. The results of the command should be visible as it progresses to it's final form. There should be a way to save a SQL command for future and wider use.

Search

This panel should have dropdown lists to make visible and range of options for the WHERE clause.

Export

Mimics the export function of PhpMyAdmin by writing out to an external file the SQL statements to create a table and populate it with data from the open database.

Import

Brings structure and data from an external Sqlite or MySQL database.

Empty

Deletes the data, but retains the structure of the current table.

Drop

Deletes both data and structure

Python

Writes a sample python program to display a previously saved sql-defined recordset.

Php

Writes a sample php program which can run on the school server's LAMP functionality.