Am I losing any functionality by doing it like this?
Yes (at least simplicity wise, perhaps no if you ignore complexity)
would I be able to query the list in the DB and search for specific items?
Yes but perhaps not as efficiently.
Would it be a good idea to store these in lists inside a SQLite DB column?
I'd suggest possibly not as there is the potential that matters can easily get complicated and as some would point out the data is not normalised.
should I just scrap this method and try something else?
rather than saying you should, perhaps consider normalising and thus utilising the relationship aspect of SQLite. The following example shows how the data could be normalised which may be beneficial in reduced storage resources and even though it may appear more complex to use relationships the complexity of managing lists within columns can easily end up being far more complex.
As you have it you could have a single table defined something like:-
CREATE TABLE IF NOT EXISTS collection1 (id INTEGER PRIMARY KEY, collection_name TEXT, signature TEXT, assets TEXT);
INSERT INTO collection1 VALUES
(1, 'BingusCollection','hY3u','[asset1, asset2, asset3]')
,(2,'RoyalMintBrilliant','8xQ2','[asset1, asset2, asset3]')
,(3,'Another','4ZUT','[asset4]')
,(4,'AndAnother','1af3','[asset4],[asset5]')
,(5,'somethingelse','gb2v','[asset1asset2asset3asset4asset5]')
;
SELECT * FROM collection1;
SELECT * FROM collection1
WHERE instr(assets,'asset5') /* would find asset50 etc */
OR instr(assets,'asset1') /* would find asset10 etc */;
The first query is fine and shows :-
However, the second query (would I be able to query the list in the DB and search for specific items?) shows an issue where complexity creeps in. i.e. you are searching for rows with either asset1 or asset5 in the list of assets BUT you get the somethingelse collection which has the asset asset1asset2asset3asset4asset5 (i.e. it has asset1 and asset5 within the asset name). Separating lists can be done but is complicated as per :-
So now consider introducing relationships and splitting the database into smaller components. CollectionName obviously belongs to the collection has a name and as I guess does the signature. However, an asset is a single thing so is a candidate for another table, especially as an asset (e.g. asset1) appears to be able to be in to many collections and a collection can have many assets. So a many-many relationship exists.
You can liken relationships to a book with pages chapters etc if you are told to look at page x you can find that page pretty easily. You've already come across Id's that uniquely identify a row. These can be used like page numbers.
So a relationship could be 1 id to another id which caters pretty well for one-one relationships (if required even) and also for one-many relationships. Many to many can use id's but additionally another table (mapping table, associative table, reference table ....). Such a table will have two id's per row one pointing to one table of the relationship and the other pointing to the other table.
So perhaps a better solution (as you may well see) would be to have a table for the collection(s), a table for the asset(s) and a mapping table for the many-many relationships between collections and assets.
So perhaps consider the following that creates the three tables :-
CREATE TABLE IF NOT EXISTS collection2 (id INTEGER PRIMARY KEY, collection_name TEXT, signature TEXT);
CREATE TABLE IF NOT EXISTS asset (id INTEGER PRIMARY KEY, assetdetails);
CREATE TABLE IF NOT EXISTS collection2_asset_map (
collection2id REFERENCES collection2(id) ON DELETE CASCADE ON UPDATE CASCADE,
assetid REFERENCES asset(id) ON DELETE CASCADE ON UPDATE CASCADE, PRIMARY KEY(collection2id, assetid)
) WITHOUT ROWID;
To load the equivalent data as loaded into the single table the following can be used:-
/* Add the same collections as above*/
INSERT INTO collection2 VALUES
(1,'BingusCollection','hY3u')
,(2,'RoyalMintBrilliant','8xQ2')
,(3,'Another','4ZUT')
,(4,'AndAnother','1af3')
,(5,'somethingelse','gb2v')
;
/* add the same assets as above just once per asset */
INSERT INTO asset VALUES(1,'asset1'),(2,'asset2'),(3,'asset3'),(4,'asset4'),(5,'asset5')
,(6,'asset1asset2asset3asset4asset5')
;
/* build the mapping table linking the collections and assets */
INSERT INTO collection2_asset_map VALUES
(1,1),(1,3),(1,2) /* the 3 assets for the BingusCollection */
,(2,3),(2,2),(2,1)
,(3,4)
,(4,4),(4,5)
,(5,6)
;
Here's 3 queries that use JOIN's to get the data from the related tables.
/* Do the equivalent of the first query i.e. get all rows (more rows) but doesn't turn out like the first */
SELECT * FROM collection2
JOIN collection2_asset_map ON collection2.id = collection2_asset_map.collection2id
JOIN asset ON asset.id = collection2_asset_map.assetid
;
This results in :-
- i.e. a row for each collection asset combination, which may appear to be confusing. However, the next query is more akin to the very first (but shows how many assets per collection (not nearly as easy without the relationships))
:-
/* GROUP all the rows per collection so more like the first query*/
SELECT collection_name, signature, '['||group_concat(assetdetails)||']' AS asset_listing, count(*) AS asset_count FROM collection2
JOIN collection2_asset_map ON collection2.id = collection2_asset_map.collection2id
JOIN asset ON asset.id = collection2_asset_map.assetid
GROUP BY collection2.id
;
Now the search for JUST asset5 and asset1
/* Search this time the somethingelse collection is not incorrectly included */
SELECT collection_name, signature, assetdetails FROM asset
JOIN collection2_asset_map ON asset.id = collection2_asset_map.assetid
JOIN collection2 ON collection2_asset_map.collection2id = collection2.id
WHERE asset.assetdetails IN('asset5','asset1')
;
