Tag Info

Hot answers tagged

13

Basically, how to sort and compare strings. Examples: Comparing: Accent: à vs ä vs a Case: A vs a Sorting: Swedish: z -> å -> ä -> ö Danish: z -> æ -> ø -> å Special character equivalence German ß = ss Wikipedia Some SQL (SQL Server 2008+) DECLARE @foo TABLE (bar varchar(2)) INSERT @foo VALUES ('z'), ('æ'), ('ø'), ('å'), ('ss'), ('ß'), ('a'), ...


12

SQLite is an embedded database and it is not intended to be used as a client/server DB. If you really want to, you can use SQLitening. What SQLitening is SQLitening is a client/server implementation of the very popular SQLite database. SQLitening is a programmer's library in standard Win32 DLL form. It is installed as a standard Windows Service. In ...


12

Use an integer & store the prices as the lowest common unit. So, for dollars and cents you'd store it in cents. EG: $1.00 would be stored as 100. In my experience it's standard practice.


12

You mostly answered the question yourself already. I have a few morsels to add: In PostgreSQL (and probably other RDBMS that support the boolean type) you can use the boolean result of the test directly. Cast it to integer and SUM(): SUM((invoice_amount > 100)::int)) Or use it in a NULLIF() expression and COUNT(): COUNT(NULLIF(invoice_amount > ...


11

As stated before sqlite is not a client-server application and it is not built for highly concurrent operations. Nevertheless you can "make it client-server", if you use ssh. ssh user@host sqlite3 databasefile select * from table works.


10

You can solve this problem using intersect statements. Do a separate select for each tag_id and join them with intersects and you'll only get the records that match all three tag_ids. select products.id, products.name from products join tag_ties on tag_ties.ref_id = products.id where tag_ties.tag_id = 10 intersect select products.id, products.name from ...


10

On most platforms your insert would throw an error if your subselect returns more than one row, rather than just insert the 'first' returned row (and 'first' is undefined without an order by clause). What you are probably after is an insert like this: INSERT Into Likes SELECT New.ID, ID FROM Highschooler WHERE Grade = New.Grade and ID<>New.ID;


9

Check out http://stackoverflow.com/q/784173/27310 to get an idea of how big SQLite databases can get in theory without performance problems. However, I'd suggest that you use a good database abstraction layer so you don't have to worry about the underlying database at all. That way, once you hit SQLite's working limits, you can switch over to a non-embedded ...


8

The subquery from the selected answer isn't needed. To select products with all the given tag ids the query can be simply: SELECT p.* FROM products AS p INNER JOIN tag_ties AS tt ON tt.ref_id = p.id AND tt.tag_id IN (10, 11, 12) GROUP BY p.id HAVING COUNT(p.id)=3 Extending this idea, we can also query based on the tag ...


8

Try something like this: select t1.id, t1.name from ( select p.name as name, p.id as id from products p inner join tag_ties ties on p.id=ties.ref_id where ties.tag_id in (10,11,12) ) as t1 group by t1.id, t1.name having count(t1.id) = 3 order by t1.name asc ;


8

This is my test on SQL Server 2012 RTM. if object_id('tempdb..#temp1') is not null drop table #temp1; if object_id('tempdb..#timer') is not null drop table #timer; if object_id('tempdb..#bigtimer') is not null drop table #bigtimer; GO select a.* into #temp1 from master..spt_values a join master..spt_values b on b.type='p' and b.number < 1000; alter ...


7

The issue with this is that as you scale SQLite, you run into the fundamental problem that it doesn't have row-level locks, only table locks. The "thing" you run out of first isn't a raw resource like CPU or IO or memory - it's that your app will get bogged down in lock management. You can have lots and lots of users sharing an SQLite database, but only your ...


6

That's because in SQLite, the AND operator has a higher precedence than OR (see the Operators section on this SQLite documentation page) This means that SQLite first evaluates the category_id=6 AND user_id = 1 expression and then ORs its result with all the other category_id conditions. Thus, your query returns all records where category_id is 1,2,3,4, or ...


6

In theory you should know where the file came from and ask the person who gave it to you. A file extension could mean anything. In the old days, .db3 extension used to be for dBase III data files. It also could be for SQLLite binary dump file. See for example Importing SQLite db3 files. If you have problems, it may be attributed to many factors, version ...


6

Yes. From the FAQ: Can multiple applications or multiple instances of the same application access a single database file at the same time? Multiple processes can have the same database open at the same time. Multiple processes can be doing a SELECT at the same time. But only one process can be making changes to the database at any moment in ...


5

At least some of the details of index B-trees are documented. It might be easier to just create a test database, populate it, and measure the file size before and after creating an index.


5

It depends, as XML is not an RDBMS. SQLite allows the querying of relations. XML (and JSON) allow for the navigation of hierarchies. Completely different ontological mappings of reality. If you need an RDBMS backend, SQLite is for you. If you need record storage without any sophisticated query requirements, XML may work, depending on your language.


5

If there can be more than one author / editor / translator per book - like it is in real life and like your relational design suggests, then the existing answer with plain LEFT JOINs will produce incorrect results. If any book could have at most one person for each of the roles, you could radically simplify your design: no junction tables needed, just a ...


5

You are looking for WAL Mode. WAL provides more concurrency as readers do not block writers and a writer does not block readers. Reading and writing can proceed concurrently.


5

I suspect you're missing the difference between CREATE TABLE and CREATE TABLE AS (otherwise known as CTAS). CREATE TABLE AS allows you to create a table from the resultset of a query. For example: CREATE TABLE PHILSUCKS AS ( SELECT PHIL, SUCKS FROM EGGS ); You could, instead of using CTAS, use a "normal" CREATE TABLE statement, then INSERT the rows ...


4

Berkeley DB's SQL API is completely SQLite compatible and offers HA/replication for high availability. The combination of SQLite's easy-of-use and Berkeley DB's scalability and reliability are a great combination. Disclaimer: I'm the Product Manager for Berkeley DB, so I'm a little biased. However, one of the main benefits of adding the SQLite API to ...


4

you're wrong. There's nothing 'flat' in the SQLite database file; the file is internally split as pages, with trees and references between them. A table doesn't' store records sequentially. Check the file format docs


4

Based on your question "What support is there for multiprocessing": SQL Server CE and SQLLite are single-user DBs that work in your app and deployed as DLLs SQL Server (Express and above) (and MySQL, Oracle, Postgres et) are server engines where you submit a request Either you have proper multiprocessing or you don't: not much grey area in between. The ...


4

There is a ROW_NUMBER() analytic function but I don't think it's available in SQLite. So, here's a generic approach (which may be quite slow): SELECT pos - 1 AS ROWNUMBER --- the "-1" is to get the C-style row numbering FROM ( SELECT b.id , COUNT(*) AS pos FROM myTable AS a JOIN myTable AS b ON a.id <= b.id WHERE ...


4

I think the tables o_products, o_qtys, o_prices should be combined into a single table, with a one-to-many relationship with the orders table as follows: order_details ( order_id INTEGER, order_item_id INTEGER, product_id INTEGER, price REAL, quantity INTEGER, PRIMARY KEY (order_id, order_item_id) FOREIGN KEY (order_id) ...


4

It looks to me like you are trying to 'pivot' the data, which can be done with multiple case statements: SQLite Schema: create table timestream( idTimestream integer primary key autoincrement, time int not null, value float not null, idSensor integer not null ); insert into ...


4

Your query is correct and works fine in other DBMS (SQL-Server, Postgres, Oracle, MySQL). In SQLite, it appears there is a bug, possibly due to the correlated subquery and/or the grouping in both the main and the sub query. Here is another way to write the query: SELECT id, COUNT(*) AS count1 FROM Likes GROUP BY id HAVING COUNT(*) = ( SELECT COUNT(*) ...


4

I guess you want to use UNION or UNION ALL, not INSERT: CREATE VIEW summary AS SELECT Name, SUM(Area) AS Area FROM table1 GROUP BY Name UNION ALL SELECT Name, SUM(Area) FROM table2 GROUP BY Name ; If there are values in the Name columns that appear in both tables and you want them summarized in one row, you can use this: ...


4

For "human readable" output, you can use column mode, and turn header output on. That will get you something similar to the sqlplus output in your examples: sqlite> select * from foo; 234|kshitiz|dba.se sqlite> .mode column sqlite> select * from foo; 234 kshitiz dba.se sqlite> .headers on sqlite> select * from foo; bar ...


4

You have FROM sourcedb.answers and INNER JOIN sourcedb.answers so you are introducing the same table twice without giving either an alias. Some of the join conditions appear probably wrong as well. FROM sourcedb.answers INNER JOIN sourcedb.questions_tests ON sourcedb.questions_tests.testskey = '121212eczema' doesn't ...



Only top voted, non community-wiki answers of a minimum length are eligible