Hi,
I am working in 3.3.6
The table schema of my code is as follows.
"CREATE TABLE ALBUMARTIST(
AlbumArtistId INTEGER PRIMARY KEY NOT NULL,
AlbumArtistName TEXT NOT NULL COLLATE
NOCASE,UNIQUE(AlbumArtistName));"
"CREATE TABLE ARTIST(
ArtistId INTEGER PRIMARY KEY NOT NULL,
ArtistName TEXT NOT NULL COLLATE NOCASE ,
UNIQUE(ArtistName));"
"CREATE TABLE ALBUM(
AlbumId INTEGER PRIMARY KEY NOT NULL,
AlbumName TEXT NOT NULL COLLATE NOCASE ,
UNIQUE(AlbumName));"
"CREATE TABLE MUSIC(
Id INTEGER PRIMARY KEY NOT NULL,
Track TEXT NOT NULL,
URL TEXT NOT NULL,
Album_Id INTEGER,
Artist_Id INTEGER,
AlbumArtist_Id INTEGER);"
"CREATE TABLE PLAYLIST (
PlayListId INTEGER PRIMARY KEY NOT NULL,
PlayListTrack TEXT,
PlayListUrl TEXT);"
"CREATE TRIGGER fkdc_MUSIC AFTER DELETE ON MUSIC FOR EACH ROW
BEGIN
DELETE FROM ALBUM WHERE AlbumId = old.Album_Id and not exists
(SELECT Id FROM MUSIC WHERE Album_Id = old.Album_Id);
DELETE FROM ARTIST WHERE ArtistId = old.Artist_Id and not exists
(SELECT Id FROM MUSIC WHERE Artist_Id = old.Artist_Id);
DELETE FROM ALBUMARTIST WHERE AlbumArtistId = old.AlbumArtist_Id
and not exists (SELECT Id FROM MUSIC WHERE AlbumArtist_Id =
old.AlbumArtist_Id);
DELETE FROM TRACKLIST WHERE PlayListUrl = old.URL and not exists
(SELECT Id FROM MUSIC WHERE URL = old.URL);
END;"
My database has 40000 records.
My need is to add all the Track in the Table MUSIC to the Table
TRACKLIST.For that i have used the following code.
while(u32_PlayListRecordCount < TotalRecordCount)
{
if ( sqlite3_prepare(pst_SqliteCallback->db," SELECT Track, URL FROM
MUSIC LIMIT 100 OFFSET ? ; ",-1,&pst_PlayListPrepareStmt,0)!= SQLITE_OK)
{
return SQLITE_DB_ERROR;
}
else
{
sqlite3_bind_int(pst_PlayListPrepareStmt,1, u32_OffsetValue);
Now I will step and store all the 100 results in one Buffer.
}
if(e_ReturnStatus == SQLITE_DB_SUCCESS)
{
if ( sqlite3_prepare(pst_SqliteCallback->db,"INSERT INTO TRACKLIST
(PlayListTypeId,PlayListTrack,PlayListUrl) VALUES
(?,?,?);",-1,&pst_PlayListPrepareStmt,0)!= SQLITE_OK)
{
return SQLITE_DB_ERROR;
}
else
{
for(u32_Offset = 0;u32_Offset < gu32_PlayListindex;u32_Offset++)
Here gu32_PlayListindex is the count of "SELECT count(*) FROM MUSIC LIMIT
100 OFFSET ? ;"
{
sqlite3_bind_int(pst_PlayListPrepareStmt,1,PlayListIndex);
sqlite3_bind_text(pst_PlayListPrepareStmt,2,st_PlayList[u32_Offset].PlayList
TrackBuffer,-1,SQLITE_STATIC);
sqlite3_bind_text(pst_PlayListPrepareStmt,3,st_PlayList[u32_Offset].PlayList
UrlBuffer,-1,SQLITE_STATIC);
u32_Return = sqlite3_step(pst_PlayListPrepareStmt);
sqlite3_reset(pst_PlayListPrepareStmt);
}
u32_Return = sqlite3_finalize(pst_PlayListPrepareStmt);
u32_ PlayListRecordCount = u32_ PlayListRecordCount +
gu32_PlayListindex;
}
}
else
{
return SQLITE_DB_ERROR;
}
}
Am I doing right or it's a lengthy process.If so suggest some other way for
inserting the records.
But this takes very long time to insert all the Tracks into
playlist.Totally I need to insert 40000 records.
Is there anyother way to insert huge number of data in to another table..
I want to insert each record after getting the Track from MUSIC select
statement.Can we use Triggers for solving this.
Can anyone help to solve this .
Thanks & Regards,
Mahalakshmi.M
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
