android-sqlite
Do I need to name every column in sqlite?
I'm building an app that will calculate the centre of gravity for different types of aircraft. Each station (seat) is stored in a column of a sqlite DB, but because every aircraft type has a different number of station I want my table to grow horizontally as needed.
I don't need to have column names because I simply store and retreive all the data in the same order. This order can and will change from one type to the next so, everything is stored as a string.
Can I create 100 columns on table creation without naming every single one of them?
Solution
Can I create 100 columns on table creation without naming every single one of them?
NO a column must have a name and it must be unique within the table as it needs to be identified.
I'm building an app that will calculate the centre of gravity for different types of aircraft. Each station (seat) is stored in a column of a sqlite.
The indication from the above is that you have two sets of related data, Aircraft which have Stations. That is an aircraft may have 1 or more (hundreds) of stations. That is 2 tables, one for the aircraft and one for the stations.
And thus a station is owned by an aircraft (assuming a one-many relationship). An aircraft could have any number of stations (limitation being either billions or storage device limitations, so hundreds is catered for easily)
SQLite is designed to handle such relationships and here's an example of how you can achieve this based upon a pseudo calculation of the CoG.
Demonstration
First just using SQLite via an SQLite tool (Navicat for SQLite):-
DROP TABLE IF EXISTS station;
DROP TABLE IF EXISTS aircraft;
/* The aircraft table */
CREATE TABLE IF NOT EXISTS aircraft (
aircraft_id INTEGER PRIMARY KEY,
aircraft_name TEXT,
aircraft_length REAL
/* etc */
);
/* The station table */
CREATE TABLE IF NOT EXISTS station (
station_id INTEGER PRIMARY KEY,
parent_aircraft_reference INTEGER REFERENCES aircraft(aircraft_id) ON DELETE CASCADE ON UPDATE CASCADE,
station_name TEXT,
station_relative_location REAL,
station_occupied INTEGER DEFAULT 0
/* ETC */
);
/* Add some aircraft */
INSERT INTO aircraft VALUES
(100,'Boeing 747-400', 70.66),
(200,'Boing 747-800',76.25),
(300,'Airbus A380', 72.57)
;
/* Add stations to the aircraft */
/* assuming each row has 11 seats named according to row and seat number */
/* default of unoccupied aka false which equates to 0 (false can be used but not for all version of SQLite so safer to use 0)*/
INSERT INTO station (parent_aircraft_reference, station_name, station_relative_location) VALUES
/* 33 seats for 747-400 */
(100,'A1',+30.00),(100,'A2',+30.00),(100,'A3',+30.00),
(100,'A4',+30.00),(100,'A5',+30.00),(100,'A6',+30.00),(100,'A7',+30.00),(100,'A8',+30.00),
(100,'A9',+30.00),(100,'A10',+30.00),(100,'A11',+30.00),
(100,'B1',+28.00),(100,'B2',+28.00),(100,'B3',+28.00),
(100,'B4',+28.00),(100,'B5',+28.00),(100,'B6',+28.00),(100,'B7',+28.00),(100,'B8',+28.00),
(100,'B9',+28.00),(100,'B10',+28.00),(100,'B11',+28.00),
(100,'Z1',-26.00),(100,'Z2',-26.00),(100,'Z3',-26.00),
(100,'Z4',-26.00),(100,'Z5',-26.00),(100,'Z6',-26.00),(100,'Z7',-26.00),(100,'Z8',-26.00),
(100,'Z9',-26.00),(100,'Z10',-26.00),(100,'Z11',-26.00),
/* 22 seats for 747-800*/
(200,'A1',+30.00),(200,'A2',+30.00),(200,'A3',+30.00),
(200,'A4',+30.00),(200,'A5',+30.00),(200,'A6',+30.00),(200,'A7',+30.00),(200,'A8',+30.00),
(200,'A9',+30.00),(200,'A10',+30.00),(200,'A11',+30.00),
(200,'B1',+27.90),(200,'B2',+27.90),(200,'B3',+27.90),
(200,'B4',+27.90),(200,'B5',+27.90),(200,'B6',+27.90),(200,'B7',+27.90),(200,'B8',+27.90),
(200,'B9',+27.90),(200,'B10',+27.90),(200,'B11',+27.90),
/* 44 seats for A380 */
(300,'A1',+31.00),(300,'A2',+31.00),(300,'A3',+31.00),
(300,'A4',+31.00),(300,'A5',+31.00),(300,'A6',+31.00),(300,'A7',+31.00),(300,'A8',+31.00),
(300,'A9',+31.00),(300,'A10',+31.00),(300,'A11',+31.00),
(300,'B1',+30.00),(300,'B2',+30.00),(300,'B3',+30.00),
(300,'B4',+30.00),(300,'B5',+30.00),(300,'B6',+30.00),(300,'B7',+30.00),(300,'B8',+30.00),
(300,'B9',+30.00),(300,'B10',+30.00),(300,'B11',+30.00),
(300,'Y1',-24.00),(300,'Y2',-24.00),(300,'Y3',-24.00),
(300,'Y4',-24.00),(300,'Y5',-24.00),(300,'Y6',-24.00),(300,'Y7',-24.00),(300,'Y8',-24.00),
(300,'Y9',-24.00),(300,'Y10',-24.00),(300,'Y11',-24.00),
(300,'Z1',-26.00),(300,'Z2',-26.00),(300,'Z3',-26.00),
(300,'Z4',-26.00),(300,'Z5',-26.00),(300,'Z6',-26.00),(300,'Z7',-26.00),(300,'Z8',-26.00),
(300,'Z9',-26.00),(300,'Z10',-26.00),(300,'Z11',-26.00)
;
/* Set some oocupied seats */
UPDATE station SET station_occupied = 1 /*could use true instead of 1 for later version of SQlite*/ WHERE parent_aircraft_reference = 100 AND station_name = 'A1';
UPDATE station SET station_occupied = 1 WHERE parent_aircraft_reference = 100 AND station_name = 'B2';
UPDATE station SET station_occupied = 1 WHERE parent_aircraft_reference = 200 AND station_name = 'B9';
UPDATE station SET station_occupied = 1 WHERE parent_aircraft_reference = 300 AND station_name = 'Y9';
UPDATE station SET station_occupied = 1 WHERE parent_aircraft_reference = 100 AND station_name = 'B1';
UPDATE station SET station_occupied = 1 WHERE parent_aircraft_reference = 100 AND station_name = 'B11';
/* note less efficient seat name used station_id would be more efficient but more complex to ascertain */
/* Get the CoG's */
SELECT
aircraft_name,
count(*) AS seats, /* get total number of seats (stations) */
sum(station_occupied) AS occupied, /* number of occupied seats */
/* some centre of gravity calculation (just to show how multiple values can be used in an expression/equation, not intended to be actual equation)*/
/* sum and count are aggregate functions i.e. they consider/utilise the rows in the group */
aircraft_length - sum(90 /*(avergage passenger weight) */ * (station_relative_location * station_occupied)) AS cog_offset
FROM aircraft /* parent table */
JOIN station /* the child table*/ ON parent_aircraft_reference = aircraft_id /* the join criteria */
GROUP BY aircraft_id; /* group according to the aircraft so 1 result per aircraft */
/* Cleanup the testing envionment */
DROP TABLE IF EXISTS station;
DROP TABLE IF EXISTS aircraft;
Running the above results in the query producing :-
- Not intended to be realistic but to show principles of related data and how that data can then be used flexibly.
For Android
Here is an example of the above using a basic android app that results in the extracted data being written to the log (via the DatabaseUtils' dumpCursor):-
DBHelper a class that extends the SQLiteOpenHelper class, including a singleton approach for the DBHelper that includes all the core required database access methods (CRUD) for the demo :-
class DBHelper extends SQLiteOpenHelper {
/* Database level constants */
public static final String DATABASE_NAME = "the_database.db";
public static final int DATABASE_VERSION = 1;
/* The SQLiteDatabase for the instance */
private SQLiteDatabase db;
/* The constructor - private so forces use of getInstance method */
private DBHelper(Context context) {
super(context,DATABASE_NAME,null,DATABASE_VERSION);
db = this.getWritableDatabase(); /* set db - note forces database connection(open) */
}
private volatile static DBHelper instance; /* The instance (initially null) */
/* get the single instance - i.e. if instance is null then open the database else return the already opened database */
public static DBHelper getInstance(Context context) {
if (instance==null) {
instance = new DBHelper(context);
}
return instance;
}
/* aircraft table constants */
public static final String AIRCRAFT_TABLE_NAME = "aircraft";
public static final String AIRCRAFT_ID_COLUMN_NAME = AIRCRAFT_TABLE_NAME + "_id";
public static final String AIRCRAFT_NAME_COLUMN_NAME = AIRCRAFT_TABLE_NAME + "_name";
public static final String AIRCRAFT_LENGTH_COLUMN_NAME = AIRCRAFT_TABLE_NAME + "_length";
/* The SQL to create the table */
private static final String AIRCRAFT_TABLE_CREATE_SQL = "CREATE TABLE IF NOT EXISTS " + AIRCRAFT_TABLE_NAME
+ "("
+ AIRCRAFT_ID_COLUMN_NAME + " INTEGER PRIMARY KEY "
+ "," + AIRCRAFT_NAME_COLUMN_NAME + " TEXT "
+ "," + AIRCRAFT_LENGTH_COLUMN_NAME + " REAL "
+ ");";
/* station table constants */
public static final String STATION_TABLE_NAME = "station";
public static final String STATION_ID_COLUMN_NAME = STATION_TABLE_NAME + "_id";
public static final String STATION_PARENT_AIRCRAFT_REFERENCE_COLUMN_NAME = "parent_aircraft_reference";
public static final String STATION_NAME_COLUMN_NAME = STATION_TABLE_NAME + "_name";
public static final String STATION_RELATIVE_LOCATION_COLUMN_NAME = STATION_NAME_COLUMN_NAME + "_relative_location";
public static final String STATION_OCCUPIED_COLUMN_NAME = STATION_TABLE_NAME + "_occupied";
private static final String STATION_TABLE_CREATE_SQL = "CREATE TABLE IF NOT EXISTS " + STATION_TABLE_NAME
+ "("
+ STATION_ID_COLUMN_NAME + " INTEGER PRIMARY KEY "
+ "," + STATION_PARENT_AIRCRAFT_REFERENCE_COLUMN_NAME + " INTEGER REFERENCES " + AIRCRAFT_TABLE_NAME + "(" + AIRCRAFT_ID_COLUMN_NAME + ")"
+ "," + STATION_NAME_COLUMN_NAME + " TEXT "
+ "," + STATION_RELATIVE_LOCATION_COLUMN_NAME + " REAL "
+ "," + STATION_OCCUPIED_COLUMN_NAME + " INTEGER DEFAULT 0"
+ ");";
/* derived (calculated) column names */
public static final String COG_OFFSET_DERIVED_COLUMN_NAME = "cog_offset";
public static final String OCCUPIED_DERIVED_COLUMN_NAME = "occupied";
/* Called when the database is first opened and it doesn't exist (ONCE for the lifetime of the database) */
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(AIRCRAFT_TABLE_CREATE_SQL);
db.execSQL(STATION_TABLE_CREATE_SQL);
}
/* Required but not setup, called if the database version nuber is increased */
@Override
public void onUpgrade(SQLiteDatabase db, int i, int i1) {
}
/* method to allow an aircraft to be added */
public long addAircraft(String aircraftName, double aircraftLength) {
ContentValues cv = new ContentValues();
cv.put(AIRCRAFT_NAME_COLUMN_NAME,aircraftName);
cv.put(AIRCRAFT_LENGTH_COLUMN_NAME,aircraftLength);
return db.insert(AIRCRAFT_TABLE_NAME,null,cv);
}
/* method to allow a station to be added */
public long addStation(long parentAircraftId, String stationName, double stationRelativeLocation) {
ContentValues cv = new ContentValues();
cv.put(STATION_PARENT_AIRCRAFT_REFERENCE_COLUMN_NAME,parentAircraftId);
cv.put(STATION_NAME_COLUMN_NAME, stationName);
cv.put(STATION_RELATIVE_LOCATION_COLUMN_NAME,stationRelativeLocation);
return db.insert(STATION_TABLE_NAME,null,cv);
}
/* method to set a station as being occupied */
public int setStationOccupiedByStationName(long aircraftId, String stationName) {
ContentValues cv = new ContentValues();
String whereClause = STATION_PARENT_AIRCRAFT_REFERENCE_COLUMN_NAME + "=? AND " + STATION_NAME_COLUMN_NAME + "=?";
String[] whereArgs = new String[]{String.valueOf(aircraftId),stationName};
cv.put(STATION_OCCUPIED_COLUMN_NAME,1);
return db.update(STATION_TABLE_NAME,cv,whereClause,whereArgs);
}
/* method to return the COG calculation (1 row per aircraft) for all aircraft */
public Cursor getCOGCalculations() {
return db.query(
AIRCRAFT_TABLE_NAME + " JOIN " + STATION_TABLE_NAME + " ON " + STATION_PARENT_AIRCRAFT_REFERENCE_COLUMN_NAME + "=" + AIRCRAFT_ID_COLUMN_NAME,
new String[]{
AIRCRAFT_NAME_COLUMN_NAME,
"sum(" + STATION_OCCUPIED_COLUMN_NAME + ") AS " + OCCUPIED_DERIVED_COLUMN_NAME,
AIRCRAFT_LENGTH_COLUMN_NAME
+ "- sum(90 * ("
+ STATION_RELATIVE_LOCATION_COLUMN_NAME
+ " * "
+ STATION_OCCUPIED_COLUMN_NAME
+ ")) AS " + COG_OFFSET_DERIVED_COLUMN_NAME
},
null,null,AIRCRAFT_ID_COLUMN_NAME,null,null
);
}
}
Example activity code to utilise the DBHelper and replicate the SQLite example above (load some data and extract the CoG results) MainActivity :-
public class MainActivity extends AppCompatActivity {
DBHelper dbHelper;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
/* Get the DBHelper singleton instance */
dbHelper = DBHelper.getInstance(this);
/* add the testing data */
populateDatabase();
/* extract the CoG calculation */
Cursor csr = dbHelper.getCOGCalculations();
/* dump the cursor (results) to the log */
DatabaseUtils.dumpCursor(csr);
csr.close(); /* SHOULD ALWAYS CLOSE CURSOR WHEN DONE WITH IT */
}
/* Method to populate the database */
private void populateDatabase() {
long a1 = dbHelper.addAircraft("Boeing 747-400",70.66);
populateStationRow(a1,11,30.00,"A");
populateStationRow(a1,11,28.00,"B");
populateStationRow(a1,11,-26.00,"Z");
long a2 = dbHelper.addAircraft("Boeing 747-800",76.25);
populateStationRow(a2,11,30.00,"A");
populateStationRow(a2,11,27.90,"B");
long a3 = dbHelper.addAircraft("Airbus A380",72.57);
populateStationRow(a3,11,31.00,"A");
populateStationRow(a3,11,30.00,"B");
populateStationRow(a3,11,-24.00,"Y");
populateStationRow(a3,11,-26.00,"Z");
/* Set the occupied stations */
dbHelper.setStationOccupiedByStationName(a1,"A1");
dbHelper.setStationOccupiedByStationName(a1,"B2");
dbHelper.setStationOccupiedByStationName(a2,"B9");
dbHelper.setStationOccupiedByStationName(a3,"Y9");
dbHelper.setStationOccupiedByStationName(a1,"B1");
dbHelper.setStationOccupiedByStationName(a1,"B11");
}
/* Method to populate stations for an entire row based upon the row designation */
private void populateStationRow(long aircraftId,int stationsPerRow, double relativeLocation, String rowBaseCharacter) {
for (long i=1; i <= stationsPerRow; i++) {
dbHelper.addStation(aircraftId,rowBaseCharacter + i,relativeLocation);
}
}
}
- The above is intended to be run just the once (otherwise aircraft and stations are effectively duplicated)
Result i.e. the Cursor dumped to the log :-
2022-10-22 13:14:32.265 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@cf2ef01
2022-10-22 13:14:32.266 I/System.out: 0 {
2022-10-22 13:14:32.266 I/System.out: aircraft_name=Boeing 747-400
2022-10-22 13:14:32.266 I/System.out: occupied=4
2022-10-22 13:14:32.266 I/System.out: cog_offset=-10189.3
2022-10-22 13:14:32.266 I/System.out: }
2022-10-22 13:14:32.266 I/System.out: 1 {
2022-10-22 13:14:32.266 I/System.out: aircraft_name=Boeing 747-800
2022-10-22 13:14:32.266 I/System.out: occupied=1
2022-10-22 13:14:32.266 I/System.out: cog_offset=-2434.75
2022-10-22 13:14:32.266 I/System.out: }
2022-10-22 13:14:32.266 I/System.out: 2 {
2022-10-22 13:14:32.266 I/System.out: aircraft_name=Airbus A380
2022-10-22 13:14:32.267 I/System.out: occupied=1
2022-10-22 13:14:32.267 I/System.out: cog_offset=2232.57
2022-10-22 13:14:32.267 I/System.out: }
2022-10-22 13:14:32.267 I/System.out: <<<<<
Note the above is intended to provide just the principles involved. You may wish to consider looking at the following links:-
https://developer.android.com/reference/android/database/sqlite/SQLiteOpenHelper?authuser=1
https://developer.android.com/reference/android/database/Cursor