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'), ...


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;


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

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 > ...


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 ;


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

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 ...


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

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

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 ...


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

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 ...


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

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

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

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 ...


4

A simple CHECK constraint works just fine: $ sqlite SQLite version 3.8.4.1 2014-03-11 15:27:36 ... sqlite> CREATE TABLE table_B( ...> user1, ...> user2, ...> [other stuff], ...> CHECK (user2 < user1) ...> ); sqlite> INSERT INTO table_B VALUES (1, 0); sqlite> INSERT INTO table_B VALUES (2, 3); Error: ...


3

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 ...


3

I see two competing/conflicting requirements: RAM/memory based database and more than one executable to be able to access the DB concurrently They are competing/conflicting because modern operating systems normally enforce strict segregation for RAM allocated to specific processes (there are exceptions, but in the main this holds). One option ...


3

This is highly situational and depends on specific criteria about the actual organizational policies (why does it take so long to provision), data characteristics (is the data mostly read, or is there a lot of updating being done (by a few or many users), can the data be sharded or locally cached?) and systems-level issues (does the solution have to be ...


3

If you are concerned about the cost of iops, which are incredibly cheap in Amazon, then you are talking about a scale which sqlite will not be able to handle. There is no direct way to compare iops between two server types without benchmarking under your exact work load. Even then, i/o is going to be a function of how well your server is tuned. Generally ...


3

Well, the biggest thoughts I have are regarding the application and data collection. For example, why are you storing the entire address? You really should only store the "v=" option of the querystring. That's the part of the web address to tells youtube to go to the video. If you store the entire address, then you may have one video stored multiple ...


3

It seems that the very same question has been asked (and answered) on stackoveflow: http://stackoverflow.com/questions/2797592/best-practice-logging-events-general-and-changes-database


3

No, SQLite doesn't present a network endpoint - it is only accessible via the filesystem. It does support concurrent access from multiple processes on the same machine but at a very coarse-grained level (DML locks an entire table). So you could have a dozen Apache httpd processes all with a SQLite database on the local disk open, all doing SELECTs and it ...



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