Re: [sqlite] Wanted - simple DATA editor for sqlite tables

: http://www.barefeetware.com/sqlite/compare/?ml Tom Tom Brodhurst-Hill BareFeetWare -- iPhone/iPad/iPod and Mac software development, specialising in databases develo...@barefeetware.com -- Follow us on Twitter: http://twitter.com/barefeetware/ Like us on Facebook: http://www.facebook.com/BareFe

Re: [sqlite] How to get the RecNo ???

select rowid, * from TestTable Tom Tom Brodhurst-Hill BareFeetWare -- iPhone/iPad/iPod and Mac software development, specialising in databases develo...@barefeetware.com -- Follow us on Twitter: http://twitter.com/barefeetware/ Like us on Facebook: http://www.facebook.com/BareFeetWare

Re: [sqlite] INSERT OR IGNORE - returning new or existing rowid

Name from Countries) ; insert into Customers (Name, Surname, ID_Country) select Name, Surname, (select ID from Countries where Name = Country) from Import ; commit ; Tom Tom Brodhurst-Hill BareFeetWare -- iPhone/iPad/iPod and Mac software development, specialising in databases develo...@barefeetwa

Re: [sqlite] Instead of triggers

Value where primary = @originalPrimaryValue This applies whether you're entering into a table or a view. Tom Tom Brodhurst-Hill BareFeetWare -- iPhone/iPad/iPod and Mac software development, specialising in databases develo...@barefeetware.com -- Follow us on Twitter: http://twitter.com/barefeetware/ L

Re: [sqlite] Updating database views ( instead of triggers ) using typed datasets and system.data.sqlite

> Changes to a view that are simulated by an INSTEAD OF trigger are not >> counted. Only real table changes are counted. The changes are in fact made, but those avenues for checking don't work. I'm tempted to label this as a bug in SQLite, since I see no reason for the limitation. I hop

Re: [sqlite] How to manage Table inside Table scenarios

foreign_keys = ON Then, you just populate using inserts. Hope this helps, Tom Tom Brodhurst-Hill BareFeetWare -- iPhone/iPad/iPod and Mac software development, specialising in databases develo...@barefeetware.com -- Follow us on Twitter: http://twitter.com/barefeetware/ Like us on Facebook: http

Re: [sqlite] Denormalized view, "clumping" into length-limited groups

So, do you mean something like: select group_concat(RefDes) as Clump from MyTable group by cast ((substr(RefDes, 2, 99) - 1) / 50 as integer) which seems to work. Tom Tom Brodhurst-Hill BareFeetWare -- iPhone/iPad/iPod and Mac software development, specialising in databases develo

Re: [sqlite] Stricter parsing rules

I agree. this tolerance by SQLite for misquoted identifiers allows a lot of errors. I'd really like to see the rules tightened to remove ambiguous input. Thanks, Tom Tom Brodhurst-Hill BareFeetWare -- iPhone/iPad/iPod and Mac software development, specialising in databases develo

Re: [sqlite] Write to a View

address, something like this: create view "Person Work" as select "Person".ID as ID , "Name First" , "Name Last" , "Company"."Name" as "Company" , "Job Title"."Name" as "

Re: [sqlite] Why can't SQLite drop columns?

Table" () select from temp.Cache ; drop table temp.Cache ; pragma foreign_keys = YES ; 4. If any of that generates an error, then issue a rollback. If it works fine, then execute commit. Tom Tom Brodhurst-Hill BareFeetWare -- iPhone/iPad/iPod and Mac software development, specialising

Re: [sqlite] Query runs in SQLite Database Browser but not in iPad app

Hi Rolf, There's nothing inherently different about SQLite execution in Objective-C or on iPad. Please post a full (though brief) sample schema, including data, that demonstrates the issue, so we can test. Thanks, Tom Tom Brodhurst-Hill BareFeetWare -- iPhone/iPad/iPod and Mac software

Re: [sqlite] SQLITE_CONSTRAINT, which one failed?

On 06/04/2012, at 11:02 AM, Richard Hipp wrote: > On Thu, Apr 5, 2012 at 8:50 PM, BareFeetWare <list@barefeetware.com>wrote: > >> On 06/04/2012, at 12:12 AM, Richard Hipp wrote: >> >>> The way SQLite keeps track of foreign key constraints is by use of a

Re: [sqlite] SQLITE_CONSTRAINT, which one failed?

tch imports. More descriptive errors from SQLite would have saved me many of those hours. Thanks for your consideration, Tom Tom Brodhurst-Hill BareFeetWare -- iPhone/iPad/iPod and Mac software development, specialising in databases develo...@barefeetware.com -- Twitter: http://twitter.com/barefeetwa

Re: [sqlite] INSERT OR REPLACE

here. Otherwise use a combination of insert and update. So, for example, if you are wanting to add a person that may not already be in your table: insert or ignore into "Person" ("First Name", "Last Name", "Company", "Email") select 'Tom', 'Brodhurst-Hill

Re: [sqlite] GUI for SQLite

I editors, mainly for Mac, and published at: http://www.barefeetware.com/sqlite/compare/?ml Tom Tom Brodhurst-Hill BareFeetWare -- iPhone/iPad/iPod and Mac software development, specialising in databases develo...@barefeetware.com -- Twitter: http://twitter.com/barefeetware/ Facebook: http://www.fac

Re: [sqlite] seeking database design advice

nceId) where AppInstance.Name = 'Safari' and AppInstance.Location = 'Reception Desk' and Report.datetime = (select max (datetime) from Report) order by paramDescription Note that this will only give you results that actually exist, no extra nulls etc that aren't needed. It will be l

Re: [sqlite] Constraint error messages

ult to maintain. Thanks for your consideration and an otherwise outstanding product. Tom Tom Brodhurst-Hill BareFeetWare -- iPhone/iPad/iPod and Mac software development, specialising in databases develo...@barefeetware.com -- Twitter: http://twitter.com/barefeetware/

Re: [sqlite] Constraint error messages

mnValue > maximumAllowed ; But again, you have to duplicate the logic that is already in your constraints, which is frustrating and error prone. Thanks, Tom Tom Brodhurst-Hill BareFeetWare -- iPhone/iPad/iPod and Mac software development, specialising in databases develo...@barefeetware.

Re: [sqlite] Constraint error messages

provides, so I can show what error occurred, but not where it occurred. ie this is not allowed: select raise(abort, 'columnValue ' || new.columnValue || ' is above maximum ' || maximumAllowed || ' in row ' || new.rowid) where new.columnValue > maximumAllowed Thanks, Tom Tom Brodhurst-Hill

Re: [sqlite] How to drop a table using sqlite API

On 02/02/2012, at 10:26 PM, bhaskarReddy wrote: > How can i drop a table using sqlite API. See: http://www.sqlite.org/lang_droptable.html Tom BareFeetWare -- iPhone/iPad/iPod and Mac software development, specialising in databases develo...@barefeetware.com -- Twitter: http://twitter.

Re: [sqlite] PRAGMA index_info explanation

o ('Person index by Email, First') gives: seqno cid name 0 3 Email 1 1 First Name which means that this indexes columns "Email" and "First Name" in that order (ie 0, 1). Tom Tom Brodhurst-Hill BareFeetWar

Re: [sqlite] retrieve data from movie sqlite database

r movies".movie_id != "movies".movie_id ) as "Other movies where we've seen this actor" from "movies" join "cast" using (movie_id) join "actors" using (actor_id) Then filter by: select * from "characters" wh

Re: [sqlite] retrieve data from movie sqlite database

ot;Other movies where we've seen this actor" from "movies" join "cast" using (movie_id) join "actors" using (actor_id) left join "cast" as "other cast" on "other cast".actor_id = "actors&quo

Re: [sqlite] retrieve data from movie sqlite database

_id) join "actors" using (actor_id) join "cast" as "other cast" on "other cast".actor_id = "actors".actor_id join "movies" as "other movies" on "other movies".movie_id = "other cast

Re: [sqlite] Updating key on related table

ttp://www.barefeetware.com/sqlite/compare/?ml Please send replied to this mail list, not me directly. Thanks, Tom BareFeetWare -- iPhone/iPad/iPod and Mac software development, specialising in databases develo...@barefeetware.com -- Twitter: http://twitter.com/barefeetware/ F

Re: [sqlite] Updating key on related table

update cascade ) ; then: insert into "Invoice" (CustomerID) select max(ID) from "Customer"; will create a new Invoice assigned to the most recently added Customer (assuming no deletions). or: insert into "Invoice" (CustomerID) select ID from "Customer" wh

Re: [sqlite] Procedure (Conditional statement) workaround

tableX set column2 = 'no match' where "column" != 'foo'; Tom BareFeetWare -- iPhone/iPad/iPod and Mac software development, specialising in databases develo...@barefeetware.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sql server management studio like tool for SQLite

On 07/11/2011, at 5:04 AM, Pete <p...@mollysrevenge.com> wrote: > Opinions on the best one for OS X? See my comparison of several here: http://www.barefeetware.com/sqlite/compare/?ml Tom Tom Brodhurst-Hill BareFeetWare Sent from my iPad _

Re: [sqlite] does a unique constraint imply an index

not null); gives: select * from SQLite_Master; type|name|tbl_name|rootpage|sql table|Test|Test|15|CREATE TABLE Test (Name text collate nocase unique not null) index|sqlite_autoindex_Test_1|Test|16| Tom Tom Brodhurst-Hill BareFeetWare -- iPhone/iPad/iPod and Mac software development, spe

[sqlite] Cleaning unicode text

Hi SQLiters, I am trying to clean up some text in a database that has some weird non-printable unicode characters. For instance: .mode insert select distinct "Name", length ("Name"), substr("Name",-1,1) from "My Table" gives: INSERT INTO table VALUES('Roundup Ready®',15,'') As you can

Re: [sqlite] how to disable a trigger

On 03/10/2011, at 2:12 PM, Sam Carleton wrote: > Ok, how do I list what a trigger is so that I can add it back once I want to > "reactive" it? select SQL from SQLite_Master where name = 'trigger name' and Type = 'trigger' Tom Tom Brodhurst-Hill BareFeetWare -- iPhone/i

Re: [sqlite] SQLite issues - iPad development

es back and forth all the time, between my Mac, iPad, iPhone and DropBox, no problem. Here's an (old) example of an SQLite database running fine on my iPad app, showing data: http://www.barefeetware.com/databare/data_choices.html Tom Tom Brodhurst-Hill BareFeetWare -- iPhone/iPad/iPod and Mac

Re: [sqlite] Suggest some Tools for Sqlite

orm. Tom Tom Brodhurst-Hill BareFeetWare -- iPhone/iPad/iPod and Mac software development, specialising in databases develo...@barefeetware.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sql

Re: [sqlite] Insert rows into a foreign key table

te) values (?, julianday('now')) ; where the ? gets replaced by the chosen Person's ID. The Order ID (ie Order number) will be automatically allocated. You could also include, in the same transaction, allocation of items to the order etc, but that depends of your user interface. Tom Brodhurst-Hill Bare

Re: [sqlite] null handling import

end ; end ; Or more simply, using the nullif() function: create trigger "Import insert" instead of insert on Import begin insert into Person (Name, Email) select nullif(new.Name, '') , nullif(new.Email, '') ; end ; Tom Tom Brodhurst-Hill B

Re: [sqlite] null handling import

ail) select case when Name = '' then null else Name end , case when Email = '' then null else Email end ; end ; Then just import into the "Import" view instead of the table. Tom Tom Brodhurst-Hill BareFeetWare Sent from my iPhone ___ sqlite-user

Re: [sqlite] Field drop work around

ey not null , "Name First" text collate nocase , "Name Last" text collate nocase , "Email" text collate nocase ) ; create table "Person MySpace" ( ID integer primary key not null references "Person" (ID) on delete cascade

Re: [sqlite] INSERT OR UPDATE?

t1 set value = 101 where id = 1; insert or ignore into t1 (id, value) values (1, 101); commit; The update will only happen if the id already exists. The insert will only happen if the id doesn't already exist. Tom Brodhurst-Hill BareFeetWare -- iPhone/iPad/iPod and Mac software develo

Re: [sqlite] Defining a relationship as unique

x has already been made or it's the table's integer primary key), it would waste space and insert time (unless SQLite somehow aliases the two, but I don't think it does). Tom Brodhurst-Hill BareFeetWare -- iPhone/iPad/iPod and Mac software development, specialising in databases develo...@barefeet

Re: [sqlite] Simple schema design help

e the table's own primary key column (which is an alias of the always created rowid primary key column) so it doesn't waste overhead with another column it doesn't need. Remember to turn on: pragma foreign_keys = YES; whenever you open a connection to the database. Unfortunately it's off

Re: [sqlite] Defining a relationship as unique

cifying it, you are just creating an alias to the built in rowid column. Since it doesn't create any extra overhead to specify it, and you might end up needing it later anyway, I always include it in the table definition. Thanks, Tom BareFeetWare -- iPhone/iPad/iPod and Mac software dev

Re: [sqlite] Simple schema design help

between matchid and tournament id? Yes. Something like: create table tournamentMembershipTable ( matchID integer primary key not null references matchTable (id) on delete cascade , tournamentID integer not null references tournament (id) on delete cascade ) Thanks, T

Re: [sqlite] Breaking a string into two

> BareFeetWare <list@barefeetware.com> wrote: >> I have some source data that includes a "Silo And Region" column of two >> words appended together, such as 'NorthPlains', >> 'SouthPlains', 'NorthSlopes', 'SouthSlopes' etc. I want to split them into

Re: [sqlite] Breaking a string into two

Hi Simon, Thanks for the reply. > On 16 Jun 2011, at 5:05am, BareFeetWare wrote: > >> I have some source data that includes a "Silo And Region" column of two >> words appended together, such as 'NorthPlains', 'SouthPlains', >> 'NorthSlopes', 'SouthSlopes'

[sqlite] Breaking a string into two

ilo Group" and "Region") until I bring in the Import. Any ideas? Hopefully I'm missing something obvious in SQLite. I am basically trying to normalise the supplied data, and don't want to have to resort to application code, just for the sake of this one function. Thanks

Re: [sqlite] Implementing busy-waiting on SQLite

fo on transaction types, see: http://www.sqlite.org/lang_transaction.html Set the timeouts as long as is reasonable for the queue of other write transactions to finish. Tom Brodhurst-Hill BareFeetWare -- Comparison of SQLite GUI tools: http://www.barefeetware.com/sqlite/compare/?ml -- iPhone/iPad/iPod and

Re: [sqlite] Multiple relations

user as watchingUser on watching = user.id join status on watchingUser.status = status.id where watcher = ? ; Tom Brodhurst-Hill BareFeetWare -- iPhone/iPad/iPod and Mac software development, specialising in databases develo...@barefeetware.c

Re: [sqlite] Store result of calculation or not

me > I'd love to give you a free copy as a (nowhere near good enough) thank you. I'll take you up on that offer. I rarely play a game more than once, but it will give me a better idea of what you're actually doing. Do you have an iPad/iPhone version? Tom BareFeetWare -- Comparison

Re: [sqlite] Spatialite in Xcode

ply. Yes, I've checked the website, googled, tried, tested, pulled hair etc. No real success yet. All my attempts fail to either compile or fail to link. Anyone done it? Thanks, Tom BareFeetWare -- Comparison of SQLite GUI tools: http://www.barefeetware.com/sqlite/co

[sqlite] Spatialite in Xcode

Hi all, I'm trying to add spatialite, a library of geographic/spatial/geometric extensions for SQLite, to my existing SQLite project in Xcode. Has anyone here done this successfully? If so, how, with step by step instructions please :-) Any help appreciated. Thanks, Tom BareFeetWare

Re: [sqlite] Sqlite shell text wrapping?

uot;Staff" on "Floor Staff"."staff" = "Staff".id where "Floor" = 2 ; -- or as a comma separated list: select group_concat("first name", ', ') from "Floor Staff" join "Staff" on "Floor Staff"."staff" = "

Re: [sqlite] Howto pivot in SQLite

It's simpler and more accurate to then iterate through the returned rows than to parse a comma separated string. Tom BareFeetWare ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Do I need to migrate to MySQL?

totalScore , max(max(player1.p1SubmitScore), max(player2.p2SubmitScore)) as totalRecordHigh from userTable left join multiturnTable as player1 on userTable.id = multiturnTable.player1 left join multiturnTable as player2 on userTable.id = multiturnTable.player2 where

Re: [sqlite] Do I need to migrate to MySQL?

ate to three separate tables. Send us the schema of the above tables and the SQL that you execute that takes 2 seconds. Tom BareFeetWare -- Comparison of SQLite GUI tools: http://www.barefeetware.com/sqlite/compare/?ml ___ sqlite-users mailing list

Re: [sqlite] SQLite in Xcode

ll very straight forward that way. The slideshow makes it pretty easy to follow: http://www.barefeetware.com/sqlite/iosxcode/?ml Thanks, Tom BareFeetWare -- Comparison of SQLite GUI tools: http://www.barefeetware.com/sqlite/compare/?ml ___ sqlite-users m

Re: [sqlite] SQLite in Xcode

the slideshow here: http://www.barefeetware.com/sqlite/iosxcode/?ml Tom BareFeetWare -- Comparison of SQLite GUI tools: http://www.barefeetware.com/sqlite/compare/?ml ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Better way to get records by IDs

and efficiency. Tom BareFeetWare -- Comparison of SQLite GUI tools: http://www.barefeetware.com/sqlite/compare/?ml ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Better way to get records by IDs

7, 33, 27,2, 1) order by ID desc ; You can instead combine into one query: select * from Jobs join "Job Skills" on Jobs.ID = "Job Skills".Job where Skill = "SQLite" order by Jobs.ID desc ; or, if your prefer: select * from Jobs where ID in (select Job from "Job

Re: [sqlite] IN clause in search query to search a single field containing comma delimited values

not null , MyTable integer not null references MyTable (ID) , Category integer not null references Category (ID) ) ; Once it has some data, you could query like this: select Name from MyTable join "MyTable Category" on MyTable.ID = "MyTable Category".MyTable where &quo

Re: [sqlite] data issues on iPhone

r, and just use standard SQLite, to help isolate the source of the problem? Thanks, Tom BareFeetWare -- Comparison of SQLite GUI tools: http://www.barefeetware.com/sqlite/compare/?ml ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.

Re: [sqlite] Disabling a unique index

drop and create statements. I think this is a deficiency. But you can just watch the result of each statement and, if an error occurs, insert your own rollback. Tom BareFeetWare -- Comparison of SQLite GUI tools: http://www.barefeetware.com/sqlite/compare/?ml

Re: [sqlite] IF-THEN-ELSE sqlite

ore manipulation, it's best done in the same SQL call, rather than manipulated in your application code only to be re-injected into the SQL from which it came. Tom BareFeetWare -- iPhone/iPad/iPod and Mac software development, specialising in databases develo...@barefeetware.com -- Com

Re: [sqlite] primary key on two columns of an associative table

in row_id that all tables have. The advantage is that you can choose to refer to the ID column as a single column unique identifier. You can also use the multi-column unique constraint, but this requires more complex syntax (eg in where statements). Thanks, Tom BareFeetWare -- Comparison of S

Re: [sqlite] Lack of "decimal" support

requirement. I use integer storage for exact lossless money amounts. Tom BareFeetWare -- iPhone/iPad/iPod and Mac software development, specialising in databases develo...@barefeetware.com -- Comparison of SQLite GUI tools: http://www.barefeetware.com/

Re: [sqlite] Lack of "decimal" support

query represented to the user and what can they enter there to create a query? You can probably do this fairly easily via views which display data in a particular format for the user to see or create a query. Tom BareFeetWare -- iPhone/iPad/iPod and Mac software development, specialising in databases

Re: [sqlite] Lack of "decimal" support

> On Sat, Mar 26, 2011 at 8:15 PM, BareFeetWare <list@barefeetware.com> > wrote: >> Can you store all money amounts as integers, as the cents value? That is >> exact, searchable etc. On 27/03/2011, at 1:27 PM, Patrick Earl wrote: > That is true, but then when

Re: [sqlite] Lack of "decimal" support

e operations such as retrieving all rows where an employee's > salary is greater than '100' (coded as a string since decimal types > are stored as strings). Can you store all money amounts as integers, as the cents value? That is exact, searchable etc. Thanks, Tom BareFeetWare -- iPhone/iPad/iPo

Re: [sqlite] detect a flaw

using? I think foreign key support requires version 3.6.19. I must admit, I haven't had to compile SQLite for a while. I mostly just link against the binary included in my OS (iOS). Tom BareFeetWare ___ sqlite-users mailing list sqlite-users@sqlite.org ht

Re: [sqlite] detect a flaw

ing the pragma statement, in sqlite_exec (or you can get fancy with sqlite_prepare etc where it makes sense). Tom BareFeetWare -- iPhone/iPad/iPod and Mac software development, specialising in databases develo...@barefeetware.com -- Comparison of SQLite GUI tools: http://www.barefeetwar

Re: [sqlite] detect a flaw

t; or, you can add the required people on the fly: begin immediate; insert or ignore into "people" (id) select aid from "activity import" where aid not in (select id from "people"); insert into "activity" select * from "activity import"; com

Re: [sqlite] adding/dropping foreign key to existing table

so, from a performance perspective, is there an advantage to using a > foreign key in SQLite verses just an index? (aka, is it worth my time to add > the key to begin with, I understand there are draw backs). An index and a foreign key serve different purposes. What are you trying to achieve

Re: [sqlite] Create table if not exists and insert seed data

e" from "Variables" where Name = 'MyTable exists'); insert into "My Table" (Name) select 'Second row' where (select "Value" from "Variables" where Name = 'MyTable exists'); insert into "My Table" (Name) select 'Third row' where (selec

Re: [sqlite] Unique index versus unique constraint

tablename) had a "unique" column like it has a > "notnull" column, Yes, we definitely need more extensive schema introspection facilities in SQLite. Thanks, Tom BareFeetWare -- Comparison of SQLite GUI tools: http://www.barefeetware.com/sqlite/compare/?ml __

Re: [sqlite] Using indexed fields in a table.

Name , "Year Composed" from Music join Composer on Music.Composer = Composer.ID where Composer.Name = 'Bach' order by "Year Composed" ; Tom BareFeetWare ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Trying to insert a new row in a table that has a column specifically set to the rowid data type will cause an error message like "may not be NULL"

ame" text not null , "Value" text not null ) ; > INSERT INTO test (Name, Value) VALUES ('test','test'); > > Error: > test.Id may not be NULL The above schema should work fine with your insert. Thanks, Tom BareFeetWare -- Comparison of SQLite GUI tools: http://www.ba

Re: [sqlite] Ideas for Searching Names in Contacts

ndex "Person First Name" on Person ("First Name") ; create index "Person Middle Name" on Person ("Middle Name") ; create index "Person Last Name" on Person ("Last Name") ; Then search like this: select * from Person where &

Re: [sqlite] apostrophes in strings...

ttach" statement within a transaction. If I use the select sqlite_attach() function, I presume I can place that within a transaction, yes? If I rollback the transaction, will it detach? Thanks, Tom BareFeetWare -- Comparison of SQLite GUI tools: http://www.barefee

Re: [sqlite] Scrolling through results of select

rocesses the rows as they are shown. This would make a big difference for very large data sets or for a view/select containing a complex calculation for each row. Thanks for taking the time to explore some possibilities for me. Tom BareFeetWare -- Comparison of SQLite GUI tools: http://www.barefeetware.com/sqlite/compare/?ml ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Scrolling through results of select

Key ORDER BY keyColumn,rowid ie with no limit, leaving the query open and getting new rows as the scroll progresses, finally closing the query when the scrolling stops. Obviously I would need to guarantee that no changes are attempted to be made to the database while the query is open and the ro

Re: [sqlite] SQLite GUI comparison

Or how can I easily test this feature? Thanks, Tom BareFeetWare -- Comparison of SQLite GUI tools: http://www.barefeetware.com/sqlite/compare/?ml ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Fwd: Scrolling through results of select

Anyone, anyone, Bueller? Here's a short version: How can I best scroll though the results of an arbitrary select query? Below is the detailed version of my question. Any help appreciated. Thanks, Tom BareFeetWare From: BareFeetWare <list@barefeetware.com> Date: 16 February 2

[sqlite] SQLite GUI comparison (was: ANN: Base 2.0, Mac SQLite GUI)

u suggest that the feature is worded in the table? Is "Supports SQLite extension" accurate? Please let me know what value (eg yes or no) I should show for this feature for any apps you know so I can add those entries. Thanks, Tom BareFeetWare -- Comparison of SQLite GUI tools: http://w

Re: [sqlite] ANN: Base 2.0, Mac SQLite GUI

re: > http://menial.co.uk/2011/02/18/base-2-0/ I've updated the details for Base 2.0 on my SQLite GUI comparison page at: http://www.barefeetware.com/sqlite/compare/?ml If there are any corrections or additions you'd like, please let me know. Thanks, Tom BareFeetWare ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] foreign key on delete no action

>> 3. For backwards parsing compatibility, am I better off just leaving the >> action blank instead of explicitly writing "on delete no action"? > > Yes. Good plan. Great, thanks for the definitive response :-) Thanks, Tom BareFeetWare -- Comparis

[sqlite] foreign key on delete no action

ersion supported parsing the foreign key schema. Did it not support parsing "no action"? 3. For backwards parsing compatibility, am I better off just leaving the action blank instead of explicitly writing "on delete no action"? Thanks, Tom BareFeetWare -- Comparison of SQLi

Re: [sqlite] Speed up count(distinct col)

where Capacity_ID = old.Capacity_ID and People_ID = old.People_ID ) ; delete from "Capacity People Statistics" where Count = 0 ; end ; commit ; >> No, I don't think you need more tables to achieve the above. >> > Yes you can do it with the above but

[sqlite] Scrolling through results of select

needed to fill the scrolling, until the user stops scrolling, then finalize, close etc. When they begin scrolling again, fire up a new prepare (with a new maxVisibleKeyValue) . Any help appreciated. Thanks, Tom BareFeetWare -- Comparison of SQLite GUI tools: http://www.barefeetware.com/

Re: [sqlite] Speed up count(distinct col)

odate a >> new capacity), and better normalized. >> > But using different tables provides an instant result, you can try it with > any table: > SELECT count(*) from table; Let me know if you see any noticeable difference in speed. > In the other hand you are ri

Re: [sqlite] Speed up count(distinct col)

is instant, I don't know if it will be > much faster to count from "Capacity People Statistics" than counting from > "Movie People". I expect the above to be about the same speed or faster (since part of the counting is already done) than separate tables, but far more flex

Re: [sqlite] Speed up count(distinct col)

, ( select coalesce(Count, 0) - 1 from "Capacity People Statistics" where ID = old.Capacity_ID and People_ID = old.People_ID ) ; insert or replace into "Capacity People Statistics" (ID, People_ID, Count) select new.Cap

Re: [sqlite] Speed up count(distinct col)

c associated with that movie are automatically deleted, but the same people are kept for other movies. SQL takes care of all the nitty gritty stuff, so we don't have to reinvent the wheel in application code. Tom BareFeetWare -- Comparison of SQLite GUI tools: http://www.barefeetware.com/sqlit

Re: [sqlite] Speed up count(distinct col)

ie_ID from Movies where Title = 'Back to the Future') and People_ID = (select ID from People where Name = 'Eric Stoltz') and Capacity_ID = (select ID from Capacity where Name = 'actor') By the way, how are you getting the raw data? What URL or RSS feed or whatever? Thanks, Tom

Re: [sqlite] Speed up count(distinct col)

> BareFeetWare-2 wrote: >> >> In that case, you should cache the counts in a separate table or two. That >> has a negligible overhead when you add a movie (which is infrequent), and >> basically no overhead when viewing (which is frequent). > I am doing that but in t

Re: [sqlite] Speed up count(distinct col)

t; select new.Capacity_ID, (select coalesce(Count, 0) + 1 from "Capacity > Statistics" where ID = new.Capacity_ID) > ; > insert or replace into "Capacity Statistics" (ID, Count) > select new.Capacity_ID, (select coalesce(Count, 0) - 1 from "Cap

Re: [sqlite] Speed up count(distinct col)

istics" where ID = old.Capacity_ID) ; end ; commit ; By contrast, I suggest it would be a bad idea to denormalize and partition your data (ie separate directors and writers tables) just for the sake of tracking the count. The solution above maintains a normalized database, gives you the flexi

Re: [sqlite] Speed up count(distinct col)

at will get ugly. Tom BareFeetWare -- Comparison of SQLite GUI tools: http://www.barefeetware.com/sqlite/compare/?ml ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Speed up count(distinct col)

s: create view "Directors" as select People.Name, group_concat (Movies.Title, ', ') from "Movie People" join Movies on "Movie People".Movie_ID = Movies.Movie_ID join Capacity on "Movie People".Capacity_ID = Capacity.ID join People on "Movie

Re: [sqlite] Feature Request: PRAGMA foreign_key_list(tableName) should display the name of the foreign key constraint

es, copy the result of each into a master table, then query that table. It's slow and requires a lot of application code and back and forth with multiple pragmas to do what SQLite is already doing internally. Thanks, Tom BareFeetWare -- Comparison of SQLite GUI tools: http://www.barefee

Re: [sqlite] Speed up count(distinct col)

uot; as select Movie_Tags.ID as ID , Movies.Title as "Movie" , Tags.Name as "Tag" from Movie_Tags join Movies on Movie_Tags.Movie_ID = Movies.Movie_ID join Tags on Movie_Tags.Tag_ID = Tags.Tag_ID

Re: [sqlite] EXT : Speed up count(distinct col)

uot; as select Movie_Tags.ID as ID , Movies.Title as "Movie" , Tags.Name as "Tag" from Movie_Tags join Movies on Movie_Tags.Movie_ID = Movies.Movie_ID join Tags on Movie_Tags.Tag_ID = Tags.Tag_ID

  1   2   >