Tag Info

New answers tagged

0

Got someone who's better at databases to fix it.


1

Here's what I came up with -- thanks to @CL. for the tips. I made it into a view so I can just query against that for a particular user. CREATE VIEW user_permissions_deep AS SELECT DISTINCT user_id, permission_id FROM ( SELECT urd.user_id user_id, pr.permission_id permission_id, urd.role_id role_id, urd.parent_id parent_role FROM ( WITH ...


0

Meanwhile I found where to get the drivers for offline installation: http://dbeaver.jkiss.org/files/ It seems this link is hidden.


1

DBeaver supports any database that has a JDBC driver. So, the best thing to do is to go to the SQLite people themselves - they have a list of such drivers here. The page itself is quite old, so don't worry about the dates - just check around and see which one best suits your needs/is supported. Just as a matter of interest, SQuirreL SQL also works via JDBC - ...


2

Supposing you have no foreign keys referencing that table, you could do something like create table some_table as select min(id), name, age, x from t group by name, age, x then you can drop the old table, rename the new table so that it has the same name as the old one before, and create indexes and other things you need on that table.


1

Multiple ranges lookups cannot be optimized with normal (B-tree) indexes. You have to create an R-tree index for your coordinates.


0

Your multipart index is covering 4 columns, but the only column that supports a seek is the first column. The trailing columns (y, x, z) can be used to find the rows, but this may not be optimal. If the column best for a search is y then make that the first column. Indexes are all about statistics which directs how the index can be used. So, perhaps this ...


0

Having all languages in one table is an overhead. Each time you will have to load the data of all languages and use part of (for one language), or you will add a condition to your query to load one language's data, which is also an overhead. In my opinion, having each language is a separate table is lighter. However, if the load on your website is low, and ...


0

I think you know about performance of using UNION instead of OR, but it's not in this case vs. using CASE that just add an inline filter. In your second query you will have two Sort actions as a side effect of having two GORUP BY statements and also two Filter actions with adding a Concatenation by using UNION. Note: The count of actions is not a ...


0

To limit the number of rows returned from a (sub)query, add a LIMIT clause. But this is not needed here because in SQLite, scalar subqueries have an implicit LIMIT 1. Column values from a are not available inside InvoiceRevised because that is an independent table. You have to replace the outer join with correlated subqueries, one for each column: SELECT ...


5

SQLite does not support lateral or correlated joins. You can work around that using a join condition. For example: create table t1 (id int, name text); create table t2 (id int, t1id int references t1(id), name text); insert into t1 values (1, 'a'), (2, 'b'), (3, 'c'); insert into t2 values (1, 1, 'a1'), (2, 1, 'a2'), (3, 2, 'b1'); Now we'd like to look ...



Top 50 recent answers are included