diff options
| author | Christian Dywan <christian@twotoasts.de> | 2019-09-16 19:41:20 +0200 |
|---|---|---|
| committer | GitHub <noreply@github.com> | 2019-09-16 19:41:20 +0200 |
| commit | 435ef6d48c4b4ff07c00ea028edd89a3ea2d5386 (patch) | |
| tree | df49b85480c97cc9e216cc75d2fceba3dc1e9add | |
| parent | 0115b02f43d23612366d0c5063e6b314d04e3853 (diff) | |
| download | midori-git-435ef6d48c4b4ff07c00ea028edd89a3ea2d5386.tar.gz | |
Use a single query with a covering index for shortcuts (#362)
The covering index allows the speed dial to be created faster. A customized query also avoids having to retrieve the images separately. Grouping by image solves the problem of multiple shortcuts for the same site.
Fixes: #354
| -rw-r--r-- | core/app.vala | 23 | ||||
| -rw-r--r-- | core/database.vala | 2 | ||||
| -rw-r--r-- | data/history/Update2.sql | 2 | ||||
| -rw-r--r-- | gresource.xml | 1 |
4 files changed, 20 insertions, 8 deletions
diff --git a/core/app.vala b/core/app.vala index c8eab5c1..e8c1676a 100644 --- a/core/app.vala +++ b/core/app.vala @@ -180,23 +180,32 @@ namespace Midori { } async void internal_scheme (WebKit.URISchemeRequest request) { + string sqlcmd = """ + SELECT image, title, uri, COUNT () AS ct FROM history + WHERE image <> '' + GROUP BY image + ORDER BY ct DESC LIMIT 9 + """; try { var database = HistoryDatabase.get_default (); - var shortcuts = yield database.query (null, 9); + var statement = database.prepare (sqlcmd); string content = ""; uint index = 0; - foreach (var shortcut in shortcuts) { - var statement = database.prepare ("SELECT image FROM %s WHERE uri = :uri LIMIT 1".printf (database.table), - ":uri", typeof (string), shortcut.uri); - statement.step (); - var image_uri = statement.get_string ("image") ?? "favicon:///" + shortcut.uri; + while (statement.step ()) { + string uri = statement.get_string ("uri"); + string title = statement.get_string ("title"); + string image_uri = statement.get_string ("image"); index++; content += """ <div class="shortcut" style="background-image: url('%s')"> <a href="%s" accesskey="%u"> <span class="title">%s</span> </a> - </div>""".printf (image_uri, shortcut.uri, index, shortcut.title); + </div>""".printf (image_uri, uri, index, title); + + uint src = Idle.add (internal_scheme.callback); + yield; + Source.remove (src); } string stylesheet = (string)resources_lookup_data ("/data/about.css", ResourceLookupFlags.NONE).get_data (); diff --git a/core/database.vala b/core/database.vala index 6b562d0c..38fbada3 100644 --- a/core/database.vala +++ b/core/database.vala @@ -443,7 +443,7 @@ namespace Midori { public async virtual List<DatabaseItem>? query (string? filter=null, int64 max_items=15, Cancellable? cancellable=null) throws DatabaseError { string where = filter != null ? "WHERE uri LIKE :filter OR title LIKE :filter" : ""; string sqlcmd = """ - SELECT rowid, uri, title, date, count () AS ct FROM %s + SELECT rowid, uri, title, date, count (uri) AS ct FROM %s %s GROUP BY uri ORDER BY ct DESC LIMIT :limit diff --git a/data/history/Update2.sql b/data/history/Update2.sql new file mode 100644 index 00000000..272ee9c5 --- /dev/null +++ b/data/history/Update2.sql @@ -0,0 +1,2 @@ +/* Covering index for shortcuts */ +CREATE UNIQUE INDEX speed_dial ON history (date, image, title, uri) WHERE image <> ''; diff --git a/gresource.xml b/gresource.xml index f2b692a7..75e87f5c 100644 --- a/gresource.xml +++ b/gresource.xml @@ -21,6 +21,7 @@ <file compressed="true">data/history/Create.sql</file> <file compressed="true">data/history/Day.sql</file> <file compressed="true">data/history/Update1.sql</file> + <file compressed="true">data/history/Update2.sql</file> <file compressed="true">data/tabby/Create.sql</file> <file compressed="true">data/tabby/Update1.sql</file> <file compressed="true">data/tabby/Update2.sql</file> |