64bit/Database/Stores - Revision history

Dave at 04:16, 24 January 2021

2021-01-24T04:16:55Z

← Older revision		Revision as of 04:16, 24 January 2021
Line 6:		Line 6:
	# kjams downloads an xml / json file for a particular store		# kjams downloads an xml / json file for a particular store
	# kjams parses each song in the downloaded.		# kjams parses each song in the downloaded.
	# for each song, create a brand new song ID, start a transaction, then parse each bit of metadata (table colums) and add each bit of data to the DB as an individual call to sql, then when the song is completed, checkpoint the transaction. this is an extremely slow process. checkpointing may happen every 5 seconds instead of after every song.		# for each song, create a brand new song ID, start a transaction, then parse each bit of metadata (table colums) and add each bit of data to the DB as an individual call to sql, then when the song is completed, checkpoint the transaction. this is an extremely slow process. checkpointing may happen every 5 seconds instead of after every song. note this may be happening for up to N stores at once, each on a different thread, and gets slower as more stores are parsed at once due to database locking
	# this may be happening for up to N stores at once, each on a different thread, and gets slower as more stores are parsed at once due to database locking		# when entire store has completed parsing, we then associate each song with an existing song (swap the newly created ID of the song with the existing song ID) that way any playlists that already have that song in it will now reference the new song with possibly updated meta data. after all songs are done, purge all NEW song IDs (since they were swapped with old song IDs, we're actually purging the old songs, keeping the new ones with the old IDs) see function PreserveOldSongIDs() to understand what is happening
	# when entire store has completed parsing, we ~~must~~ then associate each song with an existing song (swap the newly created ID of the song with the existing song ID) that way any playlists that already have that song in it will now reference the new song with possibly updated meta data. after all songs are done, purge all NEW song IDs (since they were swapped with old song IDs, we're actually purging the old songs, keeping the new ones with the old IDs) see function PreserveOldSongIDs() to understand what is happening

	new design:		new design:
	# see if sqlite file is available for download, if so, download THAT instead of xml / json, then skip to step 4 below		# see if sqlite file is available for download, if so, download THAT instead of xml / json, then skip to step 5 below
	# ~~follow "current design"~~ steps~~, starting at step~~ 1~~, but before step 3,~~ ensure the store song data is stored in a new, separate database file, unreleated to the existing DB		# do steps 1 and 2 from above
	# ~~before~~ step 5 above~~, skip to step 4 below~~		# ensure the store song data (running on separate thread) is stored in a new, separate database file, unreleated to the existing DB
			# do step 3 above. it's OKAY if this part is slow
	# kJams loads the store sqlite file and merges it into the existing Library.		# kJams loads the store sqlite file and merges it into the existing Library.
	# note the SongID stored per song in the store is ONLY unique within each store, and may conflict with SongID of Library once loaded: while loading we must give each record a new ID, then do something similar to PreserveOldSongIDs()		# note the SongID stored per song in the store is ONLY unique within each store, and may conflict with SongID of Library once loaded: while loading we must give each record a new ID, then do something similar to PreserveOldSongIDs()

Dave at 00:04, 23 January 2021

2021-01-23T00:04:37Z

← Older revision		Revision as of 00:04, 23 January 2021
Line 1:		Line 1:
	goal: to have downloading and installing each music store database be very fast		goal: to have downloading and installing each music store database be very fast

			Note: store songs are marked as such in the Library database (.ksql file). That way, when we load the database, if the user has "turned off" a particular store, we can just purge all song entries associated with that store

	current design:		current design:
	~~# store songs are marked as such, that way when we load the database, if the user has "turned off" a particular store, we can just purge all song entries associated with that store~~		# kjams downloads an xml / json file for a particular store
	# kjams downloads an xml or json file for a particular store
	# kjams parses each song in the downloaded.		# kjams parses each song in the downloaded.
	# for each song, create a brand new song ID, start a transaction, then parse each bit of metadata (table colums) and ~~adds~~ each to the DB as an individual call to sql, then when the song is completed, checkpoint the transaction. this is an extremely slow process		# for each song, create a brand new song ID, start a transaction, then parse each bit of metadata (table colums) and add each bit of data to the DB as an individual call to sql, then when the song is completed, checkpoint the transaction. this is an extremely slow process. checkpointing may happen every 5 seconds instead of after every song.
	# this may be happening for up to N stores at once, each on a different thread, and gets slower as more stores are parsed at once due to database locking		# this may be happening for up to N stores at once, each on a different thread, and gets slower as more stores are parsed at once due to database locking
	# when entire store has completed parsing, we must then associate each song with an existing song (swap the newly created ID of the song with the existing song ID, that way any playlists that already have that song in it will now reference the new song with possibly updated meta data. after all songs are done, purge all NEW song IDs (since they were swapped with old song IDs, we're actually purging the old songs, keeping the new ones with the old IDs		# when entire store has completed parsing, we must then associate each song with an existing song (swap the newly created ID of the song with the existing song ID) that way any playlists that already have that song in it will now reference the new song with possibly updated meta data. after all songs are done, purge all NEW song IDs (since they were swapped with old song IDs, we're actually purging the old songs, keeping the new ones with the old IDs) see function PreserveOldSongIDs() to understand what is happening

	new design:		new design:
	# ~~do steps as above, except in step 2, first~~ see if sqlite file is available for download, if so, download THAT instead of xml / json, then skip to step 3 below		# see if sqlite file is available for download, if so, download THAT instead of xml / json, then skip to step 4 below
	# before step ~~4 above~~, the song data is stored in a new, separate database file, unreleated to the existing DB		# follow "current design" steps, starting at step 1, but before step 3, ensure the store song data is stored in a new, separate database file, unreleated to the existing DB
	# before step 6 above, skip to step 4 below		# before step 5 above, skip to step 4 below
	# kJams loads the store sqlite file and merges it into the existing Library.		# kJams loads the store sqlite file and merges it into the existing Library.
	# note the SongID stored per song in the store is ONLY unique within each store, and may conflict with SongID of Library once loaded: while loading we must give each record a new ID		# note the SongID stored per song in the store is ONLY unique within each store, and may conflict with SongID of Library once loaded: while loading we must give each record a new ID, then do something similar to PreserveOldSongIDs()

Dave at 23:58, 22 January 2021

2021-01-22T23:58:09Z

← Older revision		Revision as of 23:58, 22 January 2021
Line 4:		Line 4:
	# store songs are marked as such, that way when we load the database, if the user has "turned off" a particular store, we can just purge all song entries associated with that store		# store songs are marked as such, that way when we load the database, if the user has "turned off" a particular store, we can just purge all song entries associated with that store
	# kjams downloads an xml or json file for a particular store		# kjams downloads an xml or json file for a particular store
	# kjams parses each song in the downloaded. for each song, create a brand new song ID, start a transaction, then parse each bit of metadata (table colums) and adds each to the DB as an individual call to sql, then when the song is completed, checkpoint the transaction. this is an extremely slow process		# kjams parses each song in the downloaded.
			# for each song, create a brand new song ID, start a transaction, then parse each bit of metadata (table colums) and adds each to the DB as an individual call to sql, then when the song is completed, checkpoint the transaction. this is an extremely slow process
	# this may be happening for up to N stores at once, each on a different thread, and gets slower as more stores are parsed at once due to database locking		# this may be happening for up to N stores at once, each on a different thread, and gets slower as more stores are parsed at once due to database locking
	# when entire store has completed parsing, we must then associate each song with an existing song (swap the newly created ID of the song with the existing song ID, that way any playlists that already have that song in it will now reference the new song with possibly updated meta data. after all songs are done, purge all NEW song IDs (since they were swapped with old song IDs, we're actually purging the old songs, keeping the new ones with the old IDs		# when entire store has completed parsing, we must then associate each song with an existing song (swap the newly created ID of the song with the existing song ID, that way any playlists that already have that song in it will now reference the new song with possibly updated meta data. after all songs are done, purge all NEW song IDs (since they were swapped with old song IDs, we're actually purging the old songs, keeping the new ones with the old IDs

	new design:		new design:
	# ~~a special version of kJams runs on my Server~~, ~~and~~, ~~while doing all~~ of ~~the~~ above, ~~saves each store into~~ a ~~SEPARATE SQLite~~ database file.		# do steps as above, except in step 2, first see if sqlite file is available for download, if so, download THAT instead of xml / json, then skip to step 3 below
	# ~~my server then makes these (optionally zipped) sqlite db files available~~		# before step 4 above, the song data is stored in a new, separate database file, unreleated to the existing DB
	~~# user kJams downloads the sqlite file from my server. if it is NOT available~~, ~~it falls back~~ to ~~doing what it does today~~		# before step 6 above, skip to step 4 below
	# kJams loads the store sqlite file and merges it into the existing Library. note the SongID stored per song in the store is ONLY unique within each store, and may conflict with SongID of Library once loaded: while loading we must give each record a new ID		# kJams loads the store sqlite file and merges it into the existing Library.
	~~# pick up at step 5 above~~		# note the SongID stored per song in the store is ONLY unique within each store, and may conflict with SongID of Library once loaded: while loading we must give each record a new ID

Dave at 23:00, 22 January 2021

2021-01-22T23:00:51Z

← Older revision		Revision as of 23:00, 22 January 2021
Line 11:		Line 11:
	# a special version of kJams runs on my Server, and, while doing all of the above, saves each store into a SEPARATE SQLite database file.		# a special version of kJams runs on my Server, and, while doing all of the above, saves each store into a SEPARATE SQLite database file.
	# my server then makes these (optionally zipped) sqlite db files available		# my server then makes these (optionally zipped) sqlite db files available
	# user kJams downloads the sqlite file from my server		# user kJams downloads the sqlite file from my server. if it is NOT available, it falls back to doing what it does today
	# kJams loads the store sqlite file and merges it into the existing Library. note the SongID stored per song in the store is ONLY unique within each store, and may conflict with SongID of Library once loaded: while loading we must give each record a new ID		# kJams loads the store sqlite file and merges it into the existing Library. note the SongID stored per song in the store is ONLY unique within each store, and may conflict with SongID of Library once loaded: while loading we must give each record a new ID
	# pick up at step 5 above		# pick up at step 5 above

Dave: Created page with "goal: to have downloading and installing each music store database be very fast current design: # store songs are marked as such, that way when we load the database, if the u..."

2021-01-22T22:59:44Z

Created page with "goal: to have downloading and installing each music store database be very fast current design: # store songs are marked as such, that way when we load the database, if the u..."

New page

goal: to have downloading and installing each music store database be very fast

current design:
# store songs are marked as such, that way when we load the database, if the user has "turned off" a particular store, we can just purge all song entries associated with that store
# kjams downloads an xml or json file for a particular store
# kjams parses each song in the downloaded. for each song, create a brand new song ID, start a transaction, then parse each bit of metadata (table colums) and adds each to the DB as an individual call to sql, then when the song is completed, checkpoint the transaction. this is an extremely slow process
# this may be happening for up to N stores at once, each on a different thread, and gets slower as more stores are parsed at once due to database locking
# when entire store has completed parsing, we must then associate each song with an existing song (swap the newly created ID of the song with the existing song ID, that way any playlists that already have that song in it will now reference the new song with possibly updated meta data. after all songs are done, purge all NEW song IDs (since they were swapped with old song IDs, we're actually purging the old songs, keeping the new ones with the old IDs

new design:
# a special version of kJams runs on my Server, and, while doing all of the above, saves each store into a SEPARATE SQLite database file.
# my server then makes these (optionally zipped) sqlite db files available
# user kJams downloads the sqlite file from my server
# kJams loads the store sqlite file and merges it into the existing Library. note the SongID stored per song in the store is ONLY unique within each store, and may conflict with SongID of Library once loaded: while loading we must give each record a new ID
# pick up at step 5 above