BLUESKY DATABASE
This database program is based on Erel's SQLITE VIEWER.
1. Launch the program (BSDB)
2. Select a database file
3. Select a table within the database file.
4. Rock on!
*** SEE BELOW FOR PROGRAM UPDATES 24 March 2015 ***
The project is not finished but I thought it's functionality was sufficient to be of use to some of the community. I have included a database (with some stored queries) for you to play with
Any feedback (positive or negative) is appreciated!
FEATURES:
The initial screen displays a "Open File" button (uses internal file manager), and a history list of recently opened files.
Selecting a database to open brings up a list of tables within that database (system tables are filtered out) - select one.
Now you see a list (Webview) of the records in that database. From here you can:
Run a query
Save a query
Delete a saved query
Add a record (click on the "Add New Record button)
Delete a record (click on the row to delete)
Edit a record (click on the row to edit)
This screen features a useful query builder. You can build many queries without even using the keyboard. Saved queries are stored in a table within the database. All of the stored queries are available regardless of which table is being viewed, so use them intelligently. Saved queries can be editied before they are executed. In addition to the common SELECT-FROM-WHERE queries I have sucessfully run queries such as PRAGMA TABLE_INFO, SELECT last_insert_rowid(), and more complex queries involving Unions between tables.
CAVEATS
Adding, Editing, or Deleting a record is done with the use of the ROWID field. This field is in SQLITE database tables by default. It IS possible to create a table without a rowid. If you have this kind of table, then this database program is not for you.
Primary Keys in SQLITE are pesky critters. They only autoincrement if they are defined as INTEGER PRIMARY KEY. The will now autoincrement otherwise (including INT PRIMARY KEY). Here is how I handle Primay Keys (PK) in BSDB:
(1) in edit mode you can change the current PK if you wish, but at your own risk. This value is shown with a red background so you can easily tell you are working with a PK.
(2) in Add New Record mode, you cannot specify a value for the PK. It will automatically be set to max(rowid) + 1 which most likely will be the new rowid anyway. In this mode, you will be told this information in the edittext box for the PK (also with a red background).
The webview display only contains the 1000 records at a time. You must select the record to be edited or deleted, but you can page through the records 1000 at a time to get to the block containing the record to be edited. Rest assured, however, that queries run against the entire database, not just the displayed records.
************************************************************************************
UPDATES 24 March 2015:
1. Added "Next Block" and "Prev. Block" buttons to view blocks of 1000 records.
2. Added "PageUp" and "Page Down" buttons to scroll through rows one screen at a
time.
3. Added Help Screen
4. Added ability to use replaceable parameters in a query - really useful for stored queries.
Replaceable Parameters: A replaceable parameter is bracketed by << and >>. Whatever is contained between the brackets becomes the prompt for entering the value for the replaceable parameter. For example "... where Lname = "<<Last Name?>>"". When executed, an input box will appear with "Last Name?" as the prompt and you would type in the required information (eg. Jones). That information is then substituted for the replaceable parameter and the query will execute as if the query had been where Lname = "Jones".
Here is a more complex example from one of you Baseball Databases. The db has three tables: Master, Batting, and Pitching to get all of the batting stats for a given player over his career, I have this stored query:
SELECT Master.Rowid as _id, [Master].[nameLast], [Master].[nameFirst], [Batting]* FROM 'Master' JOIN 'Batting' on [Master].[playerID] = Batting].[playerID] where Master.nameFirst LIKE "<<First Name>>" and Master.nameLast LIKE "<<Last Name>>"
Thank God I don't have to type that all in each time I want to know a particular player's stats! All I have to do is the execute the stored query and supply the first and last name of the player in question.
FUTURE PLANS
1. Add support for seeking records (so all of them can be edited or deleted), or just plain be viewed.
2. Add ability to sort by 1 (more than 1?) columns.
3. I would like for the column headers to not disappear when the webview is scrolled.
This database program is based on Erel's SQLITE VIEWER.
1. Launch the program (BSDB)
2. Select a database file
3. Select a table within the database file.
4. Rock on!
*** SEE BELOW FOR PROGRAM UPDATES 24 March 2015 ***
The project is not finished but I thought it's functionality was sufficient to be of use to some of the community. I have included a database (with some stored queries) for you to play with
Any feedback (positive or negative) is appreciated!
FEATURES:
The initial screen displays a "Open File" button (uses internal file manager), and a history list of recently opened files.
Selecting a database to open brings up a list of tables within that database (system tables are filtered out) - select one.
Now you see a list (Webview) of the records in that database. From here you can:
Run a query
Save a query
Delete a saved query
Add a record (click on the "Add New Record button)
Delete a record (click on the row to delete)
Edit a record (click on the row to edit)
This screen features a useful query builder. You can build many queries without even using the keyboard. Saved queries are stored in a table within the database. All of the stored queries are available regardless of which table is being viewed, so use them intelligently. Saved queries can be editied before they are executed. In addition to the common SELECT-FROM-WHERE queries I have sucessfully run queries such as PRAGMA TABLE_INFO, SELECT last_insert_rowid(), and more complex queries involving Unions between tables.
CAVEATS
Adding, Editing, or Deleting a record is done with the use of the ROWID field. This field is in SQLITE database tables by default. It IS possible to create a table without a rowid. If you have this kind of table, then this database program is not for you.
Primary Keys in SQLITE are pesky critters. They only autoincrement if they are defined as INTEGER PRIMARY KEY. The will now autoincrement otherwise (including INT PRIMARY KEY). Here is how I handle Primay Keys (PK) in BSDB:
(1) in edit mode you can change the current PK if you wish, but at your own risk. This value is shown with a red background so you can easily tell you are working with a PK.
(2) in Add New Record mode, you cannot specify a value for the PK. It will automatically be set to max(rowid) + 1 which most likely will be the new rowid anyway. In this mode, you will be told this information in the edittext box for the PK (also with a red background).
The webview display only contains the 1000 records at a time. You must select the record to be edited or deleted, but you can page through the records 1000 at a time to get to the block containing the record to be edited. Rest assured, however, that queries run against the entire database, not just the displayed records.
************************************************************************************
UPDATES 24 March 2015:
1. Added "Next Block" and "Prev. Block" buttons to view blocks of 1000 records.
2. Added "PageUp" and "Page Down" buttons to scroll through rows one screen at a
time.
3. Added Help Screen
4. Added ability to use replaceable parameters in a query - really useful for stored queries.
Replaceable Parameters: A replaceable parameter is bracketed by << and >>. Whatever is contained between the brackets becomes the prompt for entering the value for the replaceable parameter. For example "... where Lname = "<<Last Name?>>"". When executed, an input box will appear with "Last Name?" as the prompt and you would type in the required information (eg. Jones). That information is then substituted for the replaceable parameter and the query will execute as if the query had been where Lname = "Jones".
Here is a more complex example from one of you Baseball Databases. The db has three tables: Master, Batting, and Pitching to get all of the batting stats for a given player over his career, I have this stored query:
SELECT Master.Rowid as _id, [Master].[nameLast], [Master].[nameFirst], [Batting]* FROM 'Master' JOIN 'Batting' on [Master].[playerID] = Batting].[playerID] where Master.nameFirst LIKE "<<First Name>>" and Master.nameLast LIKE "<<Last Name>>"
Thank God I don't have to type that all in each time I want to know a particular player's stats! All I have to do is the execute the stored query and supply the first and last name of the player in question.
FUTURE PLANS
1. Add support for seeking records (so all of them can be edited or deleted), or just plain be viewed.
2. Add ability to sort by 1 (more than 1?) columns.
3. I would like for the column headers to not disappear when the webview is scrolled.
Attachments
Last edited:
