Hey,
first of all: awesome :-) Really looking forward to use it, once I'm
back on linux.
I just had a quick look at the modified query. Why don't you use an
outer join, to get most accurate results and those which don't have a
match?
BR,
Leif
2010/9/24 Enlightenment SVN <no-re...@enlightenment.org>:
> Log:
> we all like correctness + faster + laziness, so we got it all!
>
> correctness: do not do the join of all audio tables, so we still get
> those music without albums, artist or genres.
>
> faster: this is also faster, as there is less data comming in, and
> during db_song_copy() we do not have to handle album, artist and genre
> strings.
>
> laziness: do just when item requests such information. So if you open
> the list but never scrolls, just a few will be fetched. And the
> information is saved for future reuse.
>
>
>
> Author: barbieri
> Date: 2010-09-23 21:13:22 -0700 (Thu, 23 Sep 2010)
> New Revision: 52664
>
> Modified:
> trunk/enjoy/data/themes/default.edc trunk/enjoy/src/bin/db.c
> trunk/enjoy/src/bin/page.c trunk/enjoy/src/bin/private.h
>
> Modified: trunk/enjoy/data/themes/default.edc
> ===================================================================
> --- trunk/enjoy/data/themes/default.edc 2010-09-24 03:00:50 UTC (rev 52663)
> +++ trunk/enjoy/data/themes/default.edc 2010-09-24 04:13:22 UTC (rev 52664)
> @@ -190,7 +190,7 @@
>
> group { name: "elm/genlist/item_compress/song/default";
> alias: "elm/genlist/item_compress_odd/song/default";
> - data.item: "labels" "text.title text.album-artist";
> + data.item: "labels" "ejy.text.title ejy.text.album-artist";
> //data.item: "icons" "swallow.cover";
> parts {
> part {
> @@ -203,7 +203,7 @@
> }
>
> part {
> - name: "text.title";
> + name: "ejy.text.title";
> type: TEXT;
> mouse_events: 0;
> scale: 1;
> @@ -217,7 +217,7 @@
> rel2 {
> relative: 1.0 0.0;
> offset: -5 -2;
> - to_y: "text.album-artist";
> + to_y: "ejy.text.album-artist";
> }
> text {
> font: "Sans:style=Bold";
> @@ -234,13 +234,14 @@
> }
>
> part {
> - name: "text.album-artist";
> + name: "ejy.text.album-artist";
> type: TEXT;
> mouse_events: 0;
> description {
> state: "default" 0.0;
> color: 128 128 128 255;
> align: 0.0 1.0;
> + fixed: 1 1;
> rel1 {
> relative: 0.0 1.0;
> offset: 4 -3;
> @@ -254,7 +255,7 @@
> size: 8;
> min: 0 1;
> align: 0.0 1.0;
> - text: "Album";
> + text: "";
> }
> }
> }
> @@ -283,14 +284,14 @@
> signal: "elm,state,selected";
> source: "elm";
> action: STATE_SET "selected" 0.0;
> - target: "text.title";
> + target: "ejy.text.title";
> }
> program {
> name: "go_passive";
> signal: "elm,state,unselected";
> source: "elm";
> action: STATE_SET "default" 0.0;
> - target: "text.title";
> + target: "ejy.text.title";
> }
> }
> }
>
> Modified: trunk/enjoy/src/bin/db.c
> ===================================================================
> --- trunk/enjoy/src/bin/db.c 2010-09-24 03:00:50 UTC (rev 52663)
> +++ trunk/enjoy/src/bin/db.c 2010-09-24 04:13:22 UTC (rev 52664)
> @@ -12,6 +12,9 @@
> sqlite3_stmt *songs_get;
> sqlite3_stmt *album_songs_get;
> sqlite3_stmt *artist_songs_get;
> + sqlite3_stmt *album_get;
> + sqlite3_stmt *artist_get;
> + sqlite3_stmt *genre_get;
> } stmt;
> };
>
> @@ -82,44 +85,36 @@
> C(songs_get,
> "SELECT files.id, files.path, files.size, "
> " audios.title, audios.album_id, audios.artist_id, audios.genre_id, "
> - " audios.trackno, audios.rating, audios.playcnt, audios.length, "
> - " audio_albums.name, audio_artists.name, audio_genres.name "
> - "FROM audios, files, audio_albums, audio_artists, audio_genres "
> + " audios.trackno, audios.rating, audios.playcnt, audios.length "
> + "FROM audios, files "
> "WHERE "
> - " files.id = audios.id AND "
> - " audio_albums.id = audios.album_id AND "
> - " audio_artists.id = audios.artist_id AND "
> - " audio_genres.id = audios.genre_id "
> + " files.id = audios.id "
> "ORDER BY UPPER(audios.title)");
>
> C(album_songs_get,
> "SELECT files.id, files.path, files.size, "
> " audios.title, audios.album_id, audios.artist_id, audios.genre_id, "
> - " audios.trackno, audios.rating, audios.playcnt, audios.length, "
> - " audio_albums.name, audio_artists.name, audio_genres.name "
> - "FROM audios, files, audio_albums, audio_artists, audio_genres "
> + " audios.trackno, audios.rating, audios.playcnt, audios.length "
> + "FROM audios, files "
> "WHERE "
> " files.id = audios.id AND "
> - " audio_albums.id = audios.album_id AND "
> - " audio_artists.id = audios.artist_id AND "
> - " audio_genres.id = audios.genre_id AND "
> " audios.album_id = ? "
> "ORDER BY audios.trackno, UPPER(audios.title)");
>
> C(artist_songs_get,
> "SELECT files.id, files.path, files.size, "
> " audios.title, audios.album_id, audios.artist_id, audios.genre_id, "
> - " audios.trackno, audios.rating, audios.playcnt, audios.length, "
> - " audio_albums.name, audio_artists.name, audio_genres.name "
> - "FROM audios, files, audio_albums, audio_artists, audio_genres "
> + " audios.trackno, audios.rating, audios.playcnt, audios.length "
> + "FROM audios, files "
> "WHERE "
> " files.id = audios.id AND "
> - " audio_albums.id = audios.album_id AND "
> - " audio_artists.id = audios.artist_id AND "
> - " audio_genres.id = audios.genre_id AND "
> " audios.artist_id = ? "
> "ORDER BY UPPER(audios.title)");
>
> + C(album_get, "SELECT name FROM audio_albums WHERE id = ?");
> + C(artist_get, "SELECT name FROM audio_artists WHERE id = ?");
> + C(genre_get, "SELECT name FROM audio_genres WHERE id = ?");
> +
> #undef C
> return EINA_TRUE;
> }
> @@ -135,6 +130,9 @@
> F(songs_get);
> F(album_songs_get);
> F(artist_songs_get);
> + F(album_get);
> + F(artist_get);
> + F(genre_get);
>
> #undef F
> return ret;
> @@ -312,9 +310,6 @@
> INT(rating, 8);
> INT(playcnt, 9);
> INT(length, 10);
> - STR(album, 11);
> - STR(artist, 12);
> - STR(genre, 13);
>
> #undef STR
> #undef INT
> @@ -408,7 +403,134 @@
> return &it->base.base;
> }
>
> +Eina_Bool
> +db_song_album_fetch(DB *db, Song *song)
> +{
> + sqlite3_stmt *stmt;
> + Eina_Bool ret;
> + int err;
>
> + EINA_SAFETY_ON_NULL_RETURN_VAL(db, EINA_FALSE);
> + EINA_SAFETY_ON_NULL_RETURN_VAL(song, EINA_FALSE);
> + if (song->flags.fetched_album) return EINA_TRUE;
> +
> + stmt = db->stmt.album_get;
> + if (!_db_stmt_bind_int64(stmt, 1, song->album_id))
> + return EINA_FALSE;
> +
> + err = sqlite3_step(stmt);
> + if (err == SQLITE_ROW)
> + {
> + eina_stringshare_replace
> + (&song->album, (const char *)sqlite3_column_text(stmt, 0));
> + song->len.album = sqlite3_column_bytes(stmt, 0);
> + ret = EINA_TRUE;
> + }
> + else if (err == SQLITE_DONE)
> + {
> + DBG("no album with id=%lld", (long long)song->album_id);
> + eina_stringshare_replace(&song->album, NULL);
> + song->len.album = 0;
> + ret = EINA_TRUE;
> + }
> + else
> + {
> + ERR("could not query album with id=%lld: %s",
> + (long long)song->album_id, sqlite3_errmsg(db->handle));
> + ret = EINA_FALSE;
> + }
> +
> + _db_stmt_reset(stmt);
> + song->flags.fetched_album = ret;
> + return ret;
> +}
> +
> +Eina_Bool
> +db_song_artist_fetch(DB *db, Song *song)
> +{
> + sqlite3_stmt *stmt;
> + Eina_Bool ret;
> + int err;
> +
> + EINA_SAFETY_ON_NULL_RETURN_VAL(db, EINA_FALSE);
> + EINA_SAFETY_ON_NULL_RETURN_VAL(song, EINA_FALSE);
> + if (song->flags.fetched_artist) return EINA_TRUE;
> +
> + stmt = db->stmt.artist_get;
> + if (!_db_stmt_bind_int64(stmt, 1, song->artist_id))
> + return EINA_FALSE;
> +
> + err = sqlite3_step(stmt);
> + if (err == SQLITE_ROW)
> + {
> + eina_stringshare_replace
> + (&song->artist, (const char *)sqlite3_column_text(stmt, 0));
> + song->len.artist = sqlite3_column_bytes(stmt, 0);
> + ret = EINA_TRUE;
> + }
> + else if (err == SQLITE_DONE)
> + {
> + DBG("no artist with id=%lld", (long long)song->artist_id);
> + eina_stringshare_replace(&song->artist, NULL);
> + song->len.artist = 0;
> + ret = EINA_TRUE;
> + }
> + else
> + {
> + ERR("could not query artist with id=%lld: %s",
> + (long long)song->artist_id, sqlite3_errmsg(db->handle));
> + ret = EINA_FALSE;
> + }
> +
> + _db_stmt_reset(stmt);
> + song->flags.fetched_artist = ret;
> + return ret;
> +}
> +
> +Eina_Bool
> +db_song_genre_fetch(DB *db, Song *song)
> +{
> + sqlite3_stmt *stmt;
> + Eina_Bool ret;
> + int err;
> +
> + EINA_SAFETY_ON_NULL_RETURN_VAL(db, EINA_FALSE);
> + EINA_SAFETY_ON_NULL_RETURN_VAL(song, EINA_FALSE);
> + if (song->flags.fetched_genre) return EINA_TRUE;
> +
> + stmt = db->stmt.genre_get;
> + if (!_db_stmt_bind_int64(stmt, 1, song->genre_id))
> + return EINA_FALSE;
> +
> + err = sqlite3_step(stmt);
> + if (err == SQLITE_ROW)
> + {
> + eina_stringshare_replace
> + (&song->genre, (const char *)sqlite3_column_text(stmt, 0));
> + song->len.genre = sqlite3_column_bytes(stmt, 0);
> + ret = EINA_TRUE;
> + }
> + else if (err == SQLITE_DONE)
> + {
> + DBG("no genre with id=%lld", (long long)song->genre_id);
> + eina_stringshare_replace(&song->genre, NULL);
> + song->len.genre = 0;
> + ret = EINA_TRUE;
> + }
> + else
> + {
> + ERR("could not query genre with id=%lld: %s",
> + (long long)song->genre_id, sqlite3_errmsg(db->handle));
> + ret = EINA_FALSE;
> + }
> +
> + _db_stmt_reset(stmt);
> + song->flags.fetched_genre = ret;
> + return ret;
> +}
> +
> +
> +
> /*
>
> TODO: filters:
>
> Modified: trunk/enjoy/src/bin/page.c
> ===================================================================
> --- trunk/enjoy/src/bin/page.c 2010-09-24 03:00:50 UTC (rev 52663)
> +++ trunk/enjoy/src/bin/page.c 2010-09-24 04:13:22 UTC (rev 52664)
> @@ -10,7 +10,7 @@
> */
>
> /* number of songs to populate at once before going back to mainloop */
> -#define PAGE_SONGS_POPULATE_ITERATION_COUNT (128)
> +#define PAGE_SONGS_POPULATE_ITERATION_COUNT (64)
>
> typedef struct _Page
> {
> @@ -32,15 +32,36 @@
>
>
> static char *
> -_song_item_label_get(void *data, Evas_Object *list __UNUSED__, const char
> *part)
> +_song_item_label_get(void *data, Evas_Object *list, const char *part)
> {
> Song *song = data;
>
> - if (!strcmp(part, "text.title"))
> + /* check if matches protocol */
> + if (strncmp(part, "ejy.text.", sizeof("ejy.text.") - 1) != 0)
> + return NULL;
> + part += sizeof("ejy.text.") - 1;
> +
> + if (!strcmp(part, "title"))
> return strdup(song->title);
> - else if (!strcmp(part, "text.album-artist"))
> + else if (!strcmp(part, "trackno-title"))
> {
> char *str;
> + if (song->trackno < 1) return strdup(song->title);
> + if (asprintf(&str, "%d - %s", song->trackno, song->title) > 0)
> + return str;
> + return NULL;
> + }
> + else if (!strcmp(part, "album-artist"))
> + {
> + char *str;
> +
> + if ((!song->flags.fetched_album) || (!song->flags.fetched_artist))
> + {
> + DB *db = evas_object_data_get(list, "_enjoy_db");
> + db_song_album_fetch(db, song);
> + db_song_artist_fetch(db, song);
> + }
> +
> if ((!song->album) && (!song->artist)) return NULL;
> else if (!song->album) return strdup(song->artist);
> else if (!song->artist) return strdup(song->album);
> @@ -49,10 +70,74 @@
> return str;
> return NULL;
> }
> - else if (!strcmp(part, "text.album"))
> - return song->album ? strdup(song->album) : NULL;
> - else if (!strcmp(part, "text.artist"))
> - return song->artist ? strdup(song->artist) : NULL;
> + else if (!strcmp(part, "album"))
> + {
> + if (!song->flags.fetched_album)
> + {
> + DB *db = evas_object_data_get(list, "_enjoy_db");
> + db_song_album_fetch(db, song);
> + }
> + return song->album ? strdup(song->album) : NULL;
> + }
> + else if (!strcmp(part, "artist"))
> + {
> + if (!song->flags.fetched_artist)
> + {
> + DB *db = evas_object_data_get(list, "_enjoy_db");
> + db_song_artist_fetch(db, song);
> + }
> + return song->artist ? strdup(song->artist) : NULL;
> + }
> + else if (!strcmp(part, "genre"))
> + {
> + if (!song->flags.fetched_genre)
> + {
> + DB *db = evas_object_data_get(list, "_enjoy_db");
> + db_song_genre_fetch(db, song);
> + }
> + return song->genre ? strdup(song->genre) : NULL;
> + }
> + else if (!strcmp(part, "trackno"))
> + {
> + char *str;
> + if (song->trackno < 1) return NULL;
> + if (asprintf(&str, "%d", song->trackno) > 0)
> + return str;
> + return NULL;
> + }
> + else if (!strcmp(part, "playcnt"))
> + {
> + char *str;
> + if (song->playcnt < 1) return NULL;
> + if (asprintf(&str, "%d", song->playcnt) > 0)
> + return str;
> + return NULL;
> + }
> + else if (!strcmp(part, "rating"))
> + {
> + char *str;
> + if (song->rating < 1) return NULL;
> + if (asprintf(&str, "%d", song->rating) > 0)
> + return str;
> + return NULL;
> + }
> + else if (!strcmp(part, "length"))
> + {
> + char *str;
> + int len;
> + if (song->length < 1) return NULL;
> + if (song->length < 60)
> + len = asprintf(&str, "%d", song->length);
> + else if (song->length < 60 * 60)
> + len = asprintf(&str, "%d:%02d", song->length / 60, song->length %
> 60);
> + else
> + len = asprintf(&str, "%d:%02d:%02d",
> + song->length / (60 * 60),
> + (song->length / 60) % 60,
> + song->length % 60);
> + if (len > 0) return str;
> + return NULL;
> + }
>
> return NULL;
> }
> @@ -188,14 +273,15 @@
> elm_genlist_bounce_set(page->list, EINA_FALSE, EINA_TRUE);
> elm_genlist_horizontal_mode_set(page->list, ELM_LIST_COMPRESS);
> elm_genlist_compress_mode_set(page->list, EINA_TRUE);
> - elm_genlist_block_count_set(page->list, 1024);
> + elm_genlist_block_count_set(page->list, 256);
> + evas_object_data_set(page->list, "_enjoy_db",
> + eina_iterator_container_get(it));
>
> evas_object_smart_callback_add
> (page->list, "selected", _page_songs_selected, page);
>
> s = edje_object_data_get(page->edje, "homogeneous");
> elm_genlist_homogeneous_set(page->list, s ? !!atoi(s) : EINA_FALSE);
> - printf("genlist homogeneous: %d\n",
> elm_genlist_homogeneous_get(page->list));
>
> elm_layout_content_set(obj, "ejy.swallow.list", page->list);
>
>
> Modified: trunk/enjoy/src/bin/private.h
> ===================================================================
> --- trunk/enjoy/src/bin/private.h 2010-09-24 03:00:50 UTC (rev 52663)
> +++ trunk/enjoy/src/bin/private.h 2010-09-24 04:13:22 UTC (rev 52664)
> @@ -75,6 +75,11 @@
> unsigned int artist;
> unsigned int genre;
> } len; /* strlen of string fields */
> + struct { /* not from db, for runtime use */
> + Eina_Bool fetched_album:1;
> + Eina_Bool fetched_artist:1;
> + Eina_Bool fetched_genre:1;
> + } flags;
> };
>
> Eina_Iterator *db_songs_get(DB *db); /* walks over 'const Song*' */
> @@ -82,6 +87,9 @@
> void db_song_free(Song *song);
> Eina_Bool db_song_rating_set(DB *db, Song *song, int rating);
> Eina_Bool db_song_length_set(DB *db, Song *song, int length);
> +Eina_Bool db_song_album_fetch(DB *db, Song *song);
> +Eina_Bool db_song_artist_fetch(DB *db, Song *song);
> +Eina_Bool db_song_genre_fetch(DB *db, Song *song);
>
> Eina_Iterator *db_album_songs_get(DB *db, int64_t album_id);
> Eina_Iterator *db_artist_songs_get(DB *db, int64_t artist_id);
>
>
> ------------------------------------------------------------------------------
> Nokia and AT&T present the 2010 Calling All Innovators-North America contest
> Create new apps & games for the Nokia N8 for consumers in U.S. and Canada
> $10 million total in prizes - $4M cash, 500 devices, nearly $6M in marketing
> Develop with Nokia Qt SDK, Web Runtime, or Java and Publish to Ovi Store
> http://p.sf.net/sfu/nokia-dev2dev
> _______________________________________________
> enlightenment-svn mailing list
> enlightenment-...@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/enlightenment-svn
>
------------------------------------------------------------------------------
Start uncovering the many advantages of virtual appliances
and start using them to simplify application deployment and
accelerate your shift to cloud computing.
http://p.sf.net/sfu/novell-sfdev2dev
_______________________________________________
enlightenment-devel mailing list
enlightenment-devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/enlightenment-devel
