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

[BareFeetWare]

On 24/03/2013, at 2:53 AM, c...@isbd.net wrote:

> I'm looking for a straightforward way to edit the *data* in sqlite tables, or 
> at least a simple GUI for creating forms to edit sqlite tables.


I suggest Navicat Essentials for SQLite. It's about $10 and is currently 
probably the best SQLite data entry app out there. SQLiteManager is also good, 
but currently a bit cumbersome with grid data entry. Both are available for Mac 
and Windows. Both support data entry into tables and views.

You can see a comparison of some features of several SQLite entry/management 
apps 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
--
Follow us on Twitter: http://twitter.com/barefeetware/
Like us on Facebook: http://www.facebook.com/BareFeetWare

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[BareFeetWare]

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

 
On 11/02/2013, at 11:23 AM, roystonja...@comcast.net wrote:

> After you do a retrieve from the database, how would to access the RecNo for 
> each record?  I can get to all the fields but I don't know how to access the 
> record number that sqlite creates when it creates your record.  I am not 
> looking for the last record number created.
> 
> I will be populating a ListView and I want to add the RecNo in the caption.
> 
> "SELECT * FROM TestTable"

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[BareFeetWare]

This kind of question often results in answers along the lines of application 
logic such as: "If the row exists get the ID else create it and then get the 
ID". But the SQL way of doing things is subtly different: "insert a new row 
where it doesn't already exist. Get the ID of the row".

Based on your description, it seems you have a schema something like this:

create table Countries
(   ID integer primary key not null
,   Name text collate nocase
)
;
create table Customers
(   ID integer primary key not null
,   Name text collate nocase
,   Surname text collate nocase
,   ID_Country integer references Countries (ID) on delete restrict on 
update cascade
)
;

And it seems you have a long list of CSV data that could be imported like this:

insert into Countries (Name)
select @Country where @Country not in (select Name from Countries)
;
insert into Customers (Name, Surname, ID_Country)
select @Name, @Surname, (select ID from Countries where Name = @Country)
;

Possibly a faster way to do it is to create a temporary table, import all your 
raw flat data into it, then run a single transaction to import it all into your 
normalised tables. If you imported it into a table called "Import", eg:

create temp table Import (Name, Surname, Country)

then your transaction to insert it into your normalised tables would be:

begin immediate
;
insert into Countries (Name)
select Country from Import where Country not in (select 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...@barefeetware.com
--
Follow us on Twitter: http://twitter.com/barefeetware/
Like us on Facebook: http://www.facebook.com/BareFeetWare


On 04/01/2013, at 11:08 PM, Krzysztof  wrote:

> Ok I have done this with two commnads where first check if record exist. I
> tried do this with one command because I want to speed up importing from
> csv. CSV has one table for example: name | surname | country. I want split
> repeated columns to dictionary tables like:
> 
> table customers
> - name
> - surname
> - id_country // FK to table countries
> 
> I'm looking for one command which before inserting to "customers" will look
> to table countries and check if country exists, if not then insert and
> return id, else return existing id.
> 


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Instead of triggers

[BareFeetWare]

On 27/12/2012, at 11:49 PM, Alem Biscan  wrote:

> So user decides to modify a record. He changes *primary, field2,
> field3*values trough textboxes, and the app sends parametarised update
> query to
> sqlite engine.
> 
> UPDATE MYVIEW
> SET *primary = @a*,
>*field2= @b*.
>*field3= @c*;

You seem to be building a user interface for data entry into SQLite tables and 
views. Whether your app (or web page) is entering data into a table or a view, 
you still need to uniquely identify the edited row to SQLite. In other word, 
you need to include a "where" clause in your update statement. So you should be 
passing on a statement like this:

update MyView
set primary = @userEnteredNewPrimaryValue
,   field2 = @userEnteredNewField2Value
,   field3 = @userEnteredNewField3Value
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/
Like us on Facebook: http://www.facebook.com/BareFeetWare

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[BareFeetWare]

> *Throws DBconcurrency violation. Affected rows 0 instead of 1.*


I use updatable views all the time, via "instead of" triggers, as you 
described. They work well.

I think your problem may be that SQLite doesn't acknowledge changes via the 
changes() SQL function and sqlite3_changes() C function, which I suspect your 
wrapper is using.

On the SQLite web site:
http://www.sqlite.org/lang_corefunc.html

>> changes()The changes() function returns the number of database rows that 
>> were changed or inserted or deleted by the most recently completed INSERT, 
>> DELETE, or UPDATE statement, exclusive of statements in lower-level 
>> triggers. The changes() SQL function is a wrapper around the 
>> sqlite3_changes() C/C++ function and hence follows the same rules for 
>> counting changes.

http://www.sqlite.org/c3ref/changes.html

>> 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 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://www.facebook.com/BareFeetWare

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[BareFeetWare]

If you're talking about multiple Notices for each Machine, then your schema 
would be something like this:

create table Machine
(   ID integer primary key not null
,   Name text
,   Location text
)
;
create table Notice
(   ID integer primary key not null
,   Machine integer not null references Machine(ID) on delete cascade on 
update cascade
,   Type integer
,   Date integer
,   ExecutedBy text
,   RequestedBy text
,   Description text
)
;

Make sure that you enable foreign keys for each session (which isn't on by 
default, weirdly):

pragma 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://www.facebook.com/BareFeetWare


On 19/12/2012, at 12:37 PM, Guillaume Saumure  wrote:

> Hello everyone,
> 
> I have to create a small program to track changes done on multiple machines. 
> The data structure look like :
> 
> Structure Notice
>PrimaryKeyID.l
>Type.l
>Date.l
>ExecutedBy.s
>RequestedBy.s
>Description.s
> EndStructure
> 
> Structure Machine
>PrimaryKeyID.l
>Name.s
>Location.s
>List Notices.Notice()
> EndStructure
> 
> Structure Assets
>...
>...
>DatabaseFileName.s
>List Machines.Machine()
> EndStructure
> 
> To make a long story short, I have a Linked list nested inside Linked list 
> but I really don't know how to manage a scenarios like this using Database 
> recording system. Any suggestions ?
> 
> Thanks beforehand and best regards.
> Guillaume Saumure


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[BareFeetWare]

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...@barefeetware.com
--
Follow us on Twitter: http://twitter.com/barefeetware/
Like us on Facebook: http://www.facebook.com/BareFeetWare

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Stricter parsing rules

[BareFeetWare]

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...@barefeetware.com
--
Follow us on Twitter: http://twitter.com/barefeetware/
Like us on Facebook: http://www.facebook.com/BareFeetWare

On 15/11/2012, at 7:37 AM, NSRT Mail account.  wrote:

> However, we've been bitten a few times by SQLite's compatibility features 
> which relax rules, and create ambiguity.
> 
> When listing columns to retrieve in SELECT, or as part of a WHERE clause, we 
> always enclose our identifiers in double quotes. However, if a developer 
> accidentally misspells a column name, instead of failing with "Error: no such 
> column: xx", SQLite reinterprets the identifier as a string, and carries 
> on as if nothing happened. Sometimes such bugs go unnoticed for a while.
> 
> Is there any way to make SQLite's parsing stricter?


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Write to a View

[BareFeetWare]

work email address.

So, imagine the human resources department has populated these tables with all 
the people in our company. They want to distribute a list of all employees, 
only showing their name, company and work email 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 "Job Title"
,   (   select "Email" from "Person Email" join "Purpose" on 
"Purpose".ID = "Purpose"
where "Person" = "Person".ID and "Purpose".Name = 'Work'
) as "Email"
from "Person"
left join "Person Company" on "Person Company".ID = "Person".ID
left join "Company" on "Company".ID = "Company"
left join "Person Job Title" on "Person Job Title".ID = "Person".ID
left join "Job Title" on "Job Title".ID = "Job Title"
order by "Name Last", "Name First"
;

That will display a list like:

ID
Name First
Name Last
Company
Job Title
Email
55
Tom
Brodhurst-Hill
BareFeetWare
iOS Developer
develo...@barefeetware.com
etc







All pretty typical so far. Now let's take the next step by making the view 
updatable.

It would be great if the people manager could enter changes to the "Person 
Work" list directly, rather than having to find each detail in the constituent 
tables and manually cross referencing IDs. We can facilitate updating in the 
view directly by adding "instead of" triggers:

create trigger "Person Work delete"
instead of delete
on "Person Work"
begin
delete from "Person" where ID = old.ID -- note that cascades in the 
related tables will automatically delete there too
;
end
;
create trigger "Person Work insert"
instead of insert
on "Person Work"
begin
insert into "Person" (ID, "Name First", "Name Last")
select new.ID, new."Name First", new."Name Last"
;
-- Company:
insert into "Company" ("Name")
select new."Company"
where new."Company" not in (select Name from "Company")
and new."Company" not null
;
insert into "Person Company" ("ID", "Company")
select
(select ID from "Person" where "Name First" = new."Name First" 
and "Name Last" = new."Name Last")
,   (select ID from "Company" where Name = new."Company")
where new."Company" not null
;
-- Job Title:
insert into "Job Title" ("Name")
select new."Job Title"
where new."Job Title" not in (select Name from "Job Title")
and new."Job Title" not null
;
insert into "Person Job Title" ("ID", "Job Title")
select
(select ID from "Person" where "Name First" = new."Name First" 
and "Name Last" = new."Name Last")
,   (select ID from "Job Title" where Name = new."Job Title")
where new."Job Title" not null
;
-- Email:
insert into "Person Email" ("Person", "Email", "Purpose")
select
(select ID from "Person" where "Name First" = new."Name First" 
and "Name Last" = new."Name Last")
,   new."Email"
,   (select ID from "Purpose" where Name = 'Work')
where new."Email" not null
;
end
;
create trigger "Person Work update"
instead of update
on "Person Work"
begin
update "Person"
set ID = new.ID, "Name First" = new."Name First", "Name Last" = 
new."Name Last"
where ID = old.ID
;
-- Company:
insert into "Company" ("Name")
select new."Company"
where new."Company" not in (select Name from "Company")
and new."Company" not null
;
insert into "Person Company" ("ID", "Company")
select
new.ID
,   (select ID from "Company" where Name = new."Company")
wher

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

[BareFeetWare]

You can do it, but you need to parse the existing table schema.

1. Get the existing schema for the table, along with associated triggers and 
indexes: select group_concat(SQL, x'0A' || ';' || x'0A') from SQLite_Master 
where tbl_name = 'My Table'.

2. Edit the create table schema to remove the column you no longer want.

3. Execute the following, with the edited schema inserted as shown:

begin immediate
;
pragma foreign_keys = NO
;
pragma triggers = NO
;
create temp table "Cache" as select * from "My Table"
;
drop table "My Table"
;

;
insert into "My 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 in databases
develo...@barefeetware.com
--
Follow us on Twitter: http://twitter.com/barefeetware/
Like us on Facebook: http://www.facebook.com/BareFeetWare

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[BareFeetWare]

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 development, specialising in databases
develo...@barefeetware.com
--
Follow us on Twitter: http://twitter.com/barefeetware/
Like us on Facebook: http://www.facebook.com/BareFeetWare

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE_CONSTRAINT, which one failed?

[BareFeetWare]

On 06/04/2012, at 11:02 AM, Richard Hipp wrote:

> On Thu, Apr 5, 2012 at 8:50 PM, BareFeetWare 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 
>>> counter...

>>> we don't have any way of knowing which of the many constraints caused the 
>>> problem.
>> 
>> Please change this. Use a hash table or array or something instead of a 
>> counter so SQLite knows what constraint failed.
> 
> There are tradeoffs here.  "Better constraint error tracking" is just another 
> way of say "runs slower and uses more memory".

Thanks for continuing the discussion. I think this is a very important issue, 
as I think do others who have asked over the years.

OK, so it's not an issue of programming difficulty - good to know - but is a 
performance issue. I can think of a few remedies for this:

1. Us the counter first. If there is a constraint violation, step through again 
with a hash table or array to track the specific violation. That way it will 
only be "slower" if there was an error, which will have stopped the operation 
anyway.

or:

2. Have a pragma that allows us to enable descriptive errors. We can choose to 
enable it when needed, or else we can do our own "if constraint error then 
enable pragma for descriptive errors and try again, show output".

I would also be interested in seeing what % speed difference it actually makes.

Benefits include:

1. SQLite's core logic becomes much more usable, eliminating a lot of 
superfluous, redundant and inaccurate external checking. I'm a big believer in 
the "keep the logic near the model" philosophy.

2. It will save hours of frustration trying to find the source of otherwise 
non-descriptive errors.

3. You will save yourself from another two years of people asking for this 
feature ;-)

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/barefeetware/
Facebook: http://www.facebook.com/BareFeetWare

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE_CONSTRAINT, which one failed?

[BareFeetWare]

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 counter. 
>  When foreign key constraints are violated, the counter increments and when 
> the constraints are resolved, the counter decrements, and an error is issued 
> at transaction commit if the counter is not zero.  But if the counter is not 
> zero, we don't have any way of knowing which of the many constraints caused 
> the problem.

Please change this. Use a hash table or array or something instead of a counter 
so SQLite knows what constraint failed. I have no doubt that your programming 
skill far exceeds mine, but this is a common requirement in software design, 
even for a relative pleb like me.

I have spent hours a day lately on a particular database design project 
tracking down why constraints and foreign keys failed during batch 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/barefeetware/
Facebook: http://www.facebook.com/BareFeetWare

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT OR REPLACE

[BareFeetWare]

I suggest only using "insert or replace" if you genuinely want to delete and 
replace with a new row. This is generally not hat you want if there are any 
foreign keys.

Only use "insert or ignore" if you are inserting exactly the same row as what 
might already be there.

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', 'BareFeetWare', 'develo...@barefeetware.com')
;
update "Person"
set "Email" = 'develo...@barefeetware.com'
where "First Name" = 'Tom' and "Last Name" = 'Brodhurst-Hill'
;

This will insert if not already there and update if it is.

If you post your specific schema and non-idea insert or replace, I can show you 
in that context.

Thanks,
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.facebook.com/BareFeetWare

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] GUI for SQLite

[BareFeetWare]

On 08/03/2012, at 8:47 AM, Rose, John B wrote:

> We are new to SQLite and have experimented with a few GUIs, Firefox plugin, 
> SQLite DB Browser, and Navicat. Is there a single GUI that is considered the 
> best, with the most features?

Some time back, I compared several SQLite GUI 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.facebook.com/BareFeetWare

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] seeking database design advice

[BareFeetWare]

are called normal forms.

>> First normal form simply states that all attributes in a relation use 
>> domains that are made up of atomic values..., meaning simply “that which 
>> cannot be broken down further.”

>> To understand second and third normal form, you have to first understand 
>> functional dependencies. The simple definition of a functional dependency is 
>> a correlation between columns in a table. If the values of one column (or 
>> set of columns) correlate to the values of another, then they have a 
>> functional dependency... Many times, functional dependencies are a warning 
>> sign that duplication lurks within a table.

>> Second normal form is defined in terms of functional dependencies. It 
>> requires that a relation be in first normal form and that all non-key 
>> attributes be functionally dependent upon all attributes of the primary key 
>> (not just part of them)... So what do you do? You decompose the table into 
>> two tables.

>> This is like factoring out a common variable in an algebraic expression... 
>> Furthermore, no information is lost... But look what else you get: the new 
>> design allows referential integrity to back you up: you have a foreign key 
>> constraint guarding this relationship..., which you couldn’t get in the 
>> previous form. What was previously only implied is now both explicit and 
>> enforceable.

That's a brief extract. It makes more sense in context, with included examples.

> The way I see it the column itself describes the data, which means less data 
> is stored... efficient and simple.

There are several problems with that approach, such as:

1. The parameter names _are_ actually data, so should be stored as such, not in 
the schema itself.

2. You end up with a pile of null values, which is inefficient, redundant 
duplication.

3. The human mind copes well with a single huge flat table, like you suggest, 
but that doesn't make it efficient or simple for a computer system. You can 
store it one way, but show it in many ways. The storage is key to get right, or 
it will bight you again and again later on. You can always adjust the views, 
but the underlying structure rapidly becomes set in stone, so it's important to 
get it right, even if you don't see the advantages up front.

4. It's difficult or impossible to add or remove parameters later without 
altering the whole schema. That has a huge overhead, such as potentially 
breaking dependant systems (which possibly don't even exist yet), having to 
backup rows, drop the old table, create a new one, insert the old data etc.

5. You have to tie external mechanisms into the specific names of the columns 
and change all those mechanisms when you change those columns. You can't, for 
instance, simply sort the output but parameter descriptions.

> I do see the appeal of Design A by effectively 'future-proofing' the database 
> table. My application performing the inserts should not have to change, I 
> just always insert the parameters I find in each report.

Yes. And the benefits become more apparent with time.

> I should add that I do expect future parameters to be added (maybe 5,10 more 
> at most, but rather infrequently).  Old and new versions
> will co-exist, but eventually all application instances should be updated.  
> With Design A I would add new columns and set existing records to NULL.

There's reason enough in that paragraph to urge you to run towards a normalized 
database design.

> I would expect 100,000 reports a month or so.  When I select from parameters 
> I generally will show all parameters, so my queries become "select * from tbl 
> where appid=x and reportId=y" with 1 result set necessary (not iterating over 
> 25 results).

For the normalized design, to get, say, the parameterDescription and paramValue 
pairs, sorted alphabetically for a particular AppInstance (name = 'Safari' and 
location = 'Reception Desk') for the latest Report,  your query becomes:

select paramDescription, paramValue
from "Runtime"
join "Param" using (paramNum)
join "Report" using (reportId)
join "AppInstance" using (appInstanceId)
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 listed as rows, not columns, which is far 
more manageable and predictable.

> I realize design A may not be "best", but I would prefer an friendly answer 
> and not a canned response like I get from co-workers.

Hopefully this was friendly and leads you towards "enlightenment" ;-)

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.facebook.com/BareFeetWare

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Constraint error messages

[BareFeetWare]

On 01/03/2012, at 8:28 AM, Richard Hipp wrote:

> SQLite implements CHECK constraints by concatenating all expressions from all 
> CHECK constraints on the table using AND and then evaluating the resulting 
> boolean to see if it is false.  If it is false, an SQLITE_CONSTRAINT error is 
> raised.
> 
> We *could* keep track of each separate CHECK expression and remember the 
> constraint name and evaluate each expression separately and output a 
> customized error message for each failure.  But that would require a lot of 
> extra code space, which is not "lite".

Though I'm sure that your programming skill far exceeds mine, I understand what 
you're saying about some extra overhead, since it's a common programming choice 
we make each day "Will I lump the error checks together for a boolean result, 
or will I write a separate if/then for each check and notify of the actual 
error?"

However, I would urge and plead with you to consider providing doing the 
latter. All the constraints and error checking is of limited value when we 
can't tell what actual constraint or check failed, especially when it means I 
basically have to reinvent the wheel to perform the same checks externally, and 
hope that my logic replicates SQLite's.

I come across this frustration with nearly every SQLIte database I create. I 
fastidiously design my database schema to prevent inconsistent data, with many 
constraints (eg not null, foreign keys etc). When I import some data or have 
someone enter some data, I need SQLite to tell me which of those many 
constraints failed and where. Otherwise it makes the preparation largely 
useless and requires manual or application level duplication of the logic which 
is redundant, imprecise and difficult 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/
Facebook: http://www.facebook.com/BareFeetWare

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Constraint error messages

[BareFeetWare]

I've also tried also using it in an SQL transaction (eg a batch import script), 
but SQLite doesn't allow it. So, in a transaction, one approach I've used is to 
create a temp table, a temp trigger and then insert some test data just to be 
able to use the raise function to abort the transaction and post an error back.

Another work around is to create a Log table and populate it with error 
messages that should result from any dat that is outside the desired 
constraints, eg:

create table if not exists "Log"
(   ID integer primary key not null
,   "Date" date not null
,   "Error" text not null collate nocase
)
;
insert into "Log" ("Date", "Error")
select
datetime('now')
,   'columnValue ' || columnValue || ' is above maximum ' || maximumAllowed 
|| ' in row ' || rowid
from Source
where new.columnValue > 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.com
--
Twitter: http://twitter.com/barefeetware/
Facebook: http://www.facebook.com/BareFeetWare

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Constraint error messages

[BareFeetWare]

On 01/03/2012, at 4:22 AM, Pete wrote:

> I would like to include as much error checking as possible in my database 
> schema.

That's an admirable aim. The whole point of constraints is to bring the error 
checking as close to the data model as possible.

> The problem I have is that the error messages that come back from constraint 
> violations are extremely generic (e.g. "constraint failed") and would mean 
> nothing to a user.  I tried including a name for constraints hoping I could 
> check the error message for the name and translate it into a meaningful user 
> message, but the name isn't returned in the error message.

Yes, this is very frustrating and reduces the effectiveness of the whole 
constraint and check facility.

> Are there any tricks by which to get meaningful error messages when a 
> constraint fails?  I saw the RAISE command - perhaps that could be used in a 
> CHECK constraint, but it feels like I would be duplicating built in 
> constraints if I do that, e.g CHECK (Col1 IS NOT NULL) for a NOT NULL 
> constraint.

The "raise" command is helpful in triggers, eg:

select raise(abort, 'columnValue is above maximum') where new.columnValue > 
maximumAllowed

But you have to create triggers that duplicate the constraints that you already 
have in your table schema. Very error prone, inconsistent, redundant and 
inefficient.

SQLite doesn't allow customisation the error message that raise 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
BareFeetWare

--
iPhone/iPad/iPod and Mac software development, specialising in databases
develo...@barefeetware.com
--
Twitter: http://twitter.com/barefeetware/
Facebook: http://www.facebook.com/BareFeetWare

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[BareFeetWare]

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.com/barefeetware/
Facebook: http://www.facebook.com/BareFeetWare

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] PRAGMA index_info explanation

[BareFeetWare]

Hi Marco,

In reply to:

> From the PRAGMA index_info documentation:
> This pragma returns one row each column in the named index. The first column 
> of the result is the rank of the column within the index. The second column 
> of the result is the rank of the column within the table. The third column of 
> output is the name of the column being indexed.
> 
> I am particularly interested in the rank fields … what does rank really means 
> here?
> Is there a lower/upper bound to this rank value?

If you have an index that is for multiple columns, the "rank of the column 
within the index" (which is labeled in the output as "seqno") tells you the 
order of those columns in the index. For example:

create table Person
(   ID integer primary key not null
,   "First Name" text collate nocase
,   "Last Name" text collate nocase
,   "Email" text collate nocase
)
;
create index "Person index by First, Last"
on "Person" ("First Name", "Last Name")
;
create index "Person index by Email, First"
on "Person" ("Email", "First Name")
;

Then:

pragma index_info ('Person index by First, Last')

gives:

seqno   cid name
0   1   First Name
1   2   Last Name

which means that this indexes columns "First Name" and "Last Name" in that 
order (ie 0, 1).

And 

pragma index_info ('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
BareFeetWare

--
iPhone/iPad/iPod and Mac software development, specialising in databases
develo...@barefeetware.com
--
Twitter: http://twitter.com/barefeetware/
Facebook: http://www.facebook.com/BareFeetWare

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] retrieve data from movie sqlite database

[BareFeetWare]

On 05/01/2012, at 2:21 AM, Petite Abeille wrote:

> And the where clause.
> 
> And perhaps add an explicit group by.

Quite right. I should have tested.

However, using a group by gets a bit inefficient since (as I understand it) 
SQLite will process the entire select before applying the where clause. So it's 
best to move the "other" joining into a subselect. This also takes care of the 
situation where the actor might not be in any other movies. We can even do it 
in a view, which we can then reuse and filter as needed:

create view "characters"
as
select
"movies".movie_id
,   "cast".character_name as "Character Name"
,   "actors".name || ' ' || actors.surname as "Actor"
,   (   select group_concat("other movies".title, ', ')
from "cast" as "other cast"
left join "movies" as "other movies" on "other 
movies".movie_id = "other cast".movie_id
where "other cast".actor_id = "actors".actor_id 
and "other 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" where movie_id = 1

I think this works as required.

Thanks,
Tom
BareFeetWare

--
iPhone/iPad/iPod and Mac software development, specialising in databases
develo...@barefeetware.com
--
Twitter: http://twitter.com/barefeetware/
Facebook: http://www.facebook.com/BareFeetWare

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] retrieve data from movie sqlite database

[BareFeetWare]

On 05/01/2012, at 1:48 AM, Petite Abeille wrote:

>> select
>>  "cast".character_name as "Character Name"
>> ,"actors".name || ' ' || actors.surname as "Actor"
>> ,group_concat("other movies".title, ', ') as "Other movies where we've 
>> seen this actor"
>> from "movies"
>>  join "cast" using (movie_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".movie_id
>> where "other movies".movie_id != "movies".movie_id
>>  and "movies".movie_id = ?
> 
> Unfortunately, looks like this query will filter out actors that have 
> performed in only that one movie, as the inner join to  "other cast" will not 
> match anything. Perhaps a left outer join would be more appropriate, no?

Good catch. You're right. I should have used left joins so as not to filter 
them out. That makes the query:

select
"cast".character_name as "Character Name"
,   "actors".name || ' ' || actors.surname as "Actor"
,   group_concat("other movies".title, ', ') as "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".actor_id 
left join "movies" as "other movies" on "other movies".movie_id = 
"other cast".movie_id
where "other movies".movie_id != "movies".movie_id
and "movies".movie_id = ?

Thanks,
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.facebook.com/BareFeetWare

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] retrieve data from movie sqlite database

[BareFeetWare]

On 04/01/2012, at 8:06 PM, E3 wrote:

> I've a SQLite db containing data about movies.

Implementing your description into a schema:

create table "movies"
(   movie_id integer primary key not null
,   title text collate nocase not null
,   year integer
)
;
create table "actors"
(   actor_id integer primary key not null
,   name text collate nocase
,   surname text collate nocase
)
;
create table "cast"
(   cast_id integer primary key not null
,   movie_id integer not null references "movies" (movie_id) on delete 
cascade on update cascade
,   actor_id integer not null references "actors" (actor_id) on delete 
cascade on update cascade
,   character_name text collate nocase --- the name of the character, eg. 
"Mrs Doubtfire"
)
;

> In a single query I should retrieve all the characters of a given movie (at
> the application level I've the id of the current movie to start), name and
> surname of the actor and a list of the movies (!= this current movie) where
> the actor had a role:
> 
> Character_name   |   Actor|   Other movies where we've seen 
> this actor
> Mrs Doubtfire   |   Robin Williams   |   Mork & Mindy, Dead Poets 
> Society, ...
> Other name |   Other actor|   Related movies,...
> 
> Is this possible? How?

Yes. This works with the above schema:

select
"cast".character_name as "Character Name"
,   "actors".name || ' ' || actors.surname as "Actor"
,   group_concat("other movies".title, ', ') as "Other movies where we've 
seen this actor"
from "movies"
join "cast" using (movie_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".movie_id
where "other movies".movie_id != "movies".movie_id
and "movies".movie_id = ?

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.facebook.com/BareFeetWare

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Updating key on related table

[BareFeetWare]

On 15/12/2011, at 2:58 PM, Jeff Matthews wrote:

> Thanks.   You are putting me on track.
> 
> Can you describe the properties, collate, nocase, restrict, etc.,

The "primary key" constraint automatically assigns a new ID to each Customer 
and Invoice when you insert a new row that doesn't specify a value for it. The 
"on delete restrict" constraint on the Invoice prevents deleting any Customer 
which has invoices. The "collate nocase" ensures that nocase is used to compare 
Customer Name, so 'tom' is treated as equal to 'Tom'.

> or better yet, point me to a reference that contains a full set of features, 
> including these?

It's just standard SQL. You can find the syntax described on the SQLite website 
here:
http://www.sqlite.org/lang_createtable.html

Here's a comparison of some GUI software that does SQLite design and data entry:
http://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/
Facebook: http://www.facebook.com/BareFeetWare



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Updating key on related table

[BareFeetWare]

On 15/12/2011, at 2:11 PM, Jeff Matthews wrote:

> Customer Table
> 
>ID
>Name
> 
> Invoice Table
> 
>CustomerID
>InvoiceNumber
> 
> When I create a new invoice record for the selected customer, does 
> Invoice.CustomerID update with Customer.ID automatically, or do I need to do 
> this manually?  If it's automatic, can someone explain how it does it 
> automatically?


What do you mean by "the selected customer"?

It depends how you've set it up in your schema. For instance, if your schema is:

create table "Customer"
(   ID integer primary key not null
,   Name text collate nocase not null
)
;
create table "Invoice"
(   ID integer primary key not null
,   CustomerID integer references "Customer" (ID) on delete restrict on 
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" where "Name" = 
'Tom';

will create a new invoice assigned to the customer whose name is 'Tom'.

Tom
BareFeetWare

--
iPhone/iPad/iPod and Mac software development, specialising in databases
develo...@barefeetware.com
--
Twitter: http://twitter.com/barefeetware/
Facebook: http://www.facebook.com/BareFeetWare

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Procedure (Conditional statement) workaround

[BareFeetWare]

On 14/12/2011, at 10:04 AM, Grace Batumbya wrote:

> Since SQLite does not support stored procedures, what are some workarounds 
> that are used to accomplish things like executing a statement based on a 
> conditional.
> For example.
> 
> IF value OF column IN tableX  == 'foo'
> DO statement 1
> ELSE statement 2

SQL isn't a procedural language. It doesn't have branching like if/then or 
for/repeat loops. It uses set logic instead.

So, instead of saying "for each item in the table, if it meets this condition 
then update that", in SQL you instead say "update that in the table where it 
meets this condition"

If depends on what your statement 1 and statement 2 are. Let's say that they 
are update statements, then your above example would look something like:

update tableX set column2 = 'match' where "column" = 'foo';
update 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

[BareFeetWare]

On 07/11/2011, at 5:04 AM, Pete  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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[BareFeetWare]

Hi Sean,

In reply to:

> In postgres, I know that if I declare a column or set of columns to have a 
> unique constraint, there is also an index created on those columns. Does the 
> same thing happen in sqlite

Yes.

For example:

create table Test (Name text collate nocase unique 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, specialising in databases
develo...@barefeetware.com
 --
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] Cleaning unicode text

[BareFeetWare]

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 see, the printable text is only 14 characters long, but there are 
actually 15 characters there, as confirmed by the length result. This weird 
extra character seems to have the unicode hex value of f87f and appears in 
various positions (not just the end) of text. I want to remove it, but can't 
figure out how.

I've tried:

replace("Name", x'f87f', '') but it doesn't seem to match the weird character.

Any ideas?

Thanks,
Tom

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to disable a trigger

[BareFeetWare]

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/iPad/iPod and Mac software development, specialising in databases
develo...@barefeetware.com
 --
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] SQLite issues - iPad development

[BareFeetWare]

On 22/09/2011, at 4:30 AM, ecky wrote:

> I have a SQLITE database I created using the firefox plugin (SQLITE manager). 
>   I'm using that database in my IPAD app and I can read and write to it.

What error are you getting?

> However... I'm trying to debug some issue with my app so I copy the database 
> file off my IPAD back to my MAC and try to read the database in the firefox 
> plugin.   I get no errors but the database has no tables in it :-(
> 
> Maybe compatibility issue between SQLITE IOS and Firefox maybe?

For what it's worth, I copy SQLite database files 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 software development, specialising in databases
develo...@barefeetware.com
 --
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] Suggest some Tools for Sqlite

[BareFeetWare]

On 18/08/2011, at 5:44 PM, Madhankumar Rajaram wrote:

> Kindly suggest the best Free / Licence Tool for using Sqlite

I've tabulated a comparison of several SQLite admin and data entry tools here:
http://www.barefeetware.com/sqlite/compare/?ml

Mainly tools for Mac, but a few are cross platform.

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/sqlite-users

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

[BareFeetWare]

On 15/08/2011, at 10:40 PM, Simon Slavin wrote:

> which is why proper code should never do it in one instruction.  You do the 
> SELECT first, then use your programming language to do the right thing for 
> each of the three cases where 0, 1 or more records are returned.

I disagree with this more general statement (as I have before). In general, it 
is desirable to do as much SQL in one transaction as possible, for speed, 
consistency and so SQLite's internal optimisers can do their thing. Selecting 
and then re-injecting the result of a select is unnecessary double handling.

Having said that, all of the other assertions hold true:

1. Only select a person based on a unique column. So only match against 
LastName if it is defined as unique.

2. Be prepared for a returned null.

In reality, you are probably creating an invoice for a person chosen by the 
operator, so instead of noting the LastName of that person, note the unique ID 
instead.

So, something like this:

create table Person
(   ID integer primary key not null
,   LastName text collate nocase
,   FirstName text collate nocase
,   Email text collate nocase
)
;
create table "Order"
(   ID integer primary key not null
,   Person_ID integer references Person (ID) on delete restrict
,   Date real
)
;

In your code, when the operator selects a Person, store the ID of that person. 
Then to create an Order, do this:

insert into "Order" (Person_ID, Date) 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
BareFeetWare

--
iPhone/iPad/iPod and Mac software development, specialising in databases
develo...@barefeetware.com
 --
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] null handling import

[BareFeetWare]

Oops, I forgot the "new." prefixes. That trigger should be:

create trigger "Import insert"
instead of insert
on Import
begin
insert into Person (Name, Email)
select
  case when new.Name = '' then null else new.Name end
, case when new.Email = '' then null else new.Email 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
BareFeetWare

Sent from my iPad

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] null handling import

[BareFeetWare]

> On 13 Aug 2011, at 7:58am, matthew (matthew.jsoft) white wrote:
> 
>> I was just wondering if sq3 has some kind of mechanism to parse blank 
>> records as null instead of empty strings.

One way to do this is to import into a view and use a trigger to convert the 
rows how you like.

For example:

create table Person
(  ID integer primary key not null
,  Name text collate nocase
,  Email text collate nocase
)
;
create view Import
as
select Name, Email
from Person
;
create trigger "Import insert"
instead of insert
on Import
begin
insert into Person (Name, Email)
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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Field drop work around

[BareFeetWare]

On 02/08/2011, at 9:03 PM, Jack Hughes wrote:

> Is there a workaround for the lack of support for dropping fields?

As others have said, you can create a new table and insert data from the old to 
new table. Remember to also recreate any needed triggers and indexes. For 
example, I get my SQLite management app to write all the necessary SQL when the 
user changes, adds or deletes a column from a table or view, as shown here:
http://www.barefeetware.com/databare/trace.html

> I have an application and need to evolve the database schema as features are 
> added and removed. Leaving fields sitting inside the database that are no 
> longer used will lead to a schema that is difficult to understand. Especially 
> so when the field is marked as NOT NULL. Years from now I will need to supply 
> a value for a field when inserting a row that has long ago ceased to be used.

If your app has a dedicated purpose, such as a contact database but is not a 
general purpose database management app, then you probably shouldn't be 
redefining your schema columns anyway. Can you give some more specific detail 
on what you're doing here, such as some sample schema changes?

For a dedicated purpose app/database, you should probably be instead adding or 
removing rows or tables as you add or remove features. For instance, if you 
have a contact database that includes Person and a MySpace contact, but later 
decide not to have MySpace but you want Facebook contact, there are three ways 
to do this:

1. Add and remove a column. This is probably the worst way:

old table:

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

change:

begin immediate
;
create temp table "Person Cache" as select * from "Person"
;
drop table "Person"
;
create table "Person"
(   ID integer primary key not null
,   "Name First" text collate nocase
,   "Name Last" text collate nocase
,   "Email" text collate nocase
,   "Facebook" text collate nocase
)
;
insert into "Person" (ID, "Name First", "Name Last", "Email")
select ID, "Name First", "Name Last", "Email" from "Person Cache"
;
drop table temp."Person Cache"
;
commit
;

2. Or, add and remove rows. Probably the best way, if it fits your needs

schema:

create table "Person"
(   ID integer primary key not null
,   "Name First" text collate nocase
,   "Name Last" text collate nocase
,   "Email" text collate nocase
)
;
create table "Service"
(   ID integer primary key not null
,   Name text collate nocase unique not null
)
;
insert into "Service" (Name) values ('MySpace')
;
create table "Person Service"
(   ID integer primary key not null
,   "Person" integer not null references "Person" (ID) on delete cascade
,   "Service" integer not null references "Person" (ID) on delete cascade
,   Name text collate nocase not null
)
;

change:

delete from "Service" where name = 'MySpace'
;
insert into "Service" (Name) values ('Facebook')
;
insert into "Person Service" ("Person", "Service", Name)
(select ID from "Person" where "Email" = 'mic...@disney.com')
,   (select ID from "Service" where Name = 'Facebook')
,   'mickeymouse'
;

3. Or, add and remove related tables. If method 2 doesn't fit your needs.

schema:

create table "Person"
(   ID integer primary key 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
,   Name text collate nocase not null
)
;

change:

drop table "Person MySpace"
;
create table "Person Facebook"
(   ID integer primary key not null references "Person" (ID) on delete 
cascade
,   Name text collate nocase not null
)
;
insert into "Person Facebook" (ID, Name)
(select ID from "Person" where "Email" = 'mic...@disney.com')
,   'mickeymouse'
;

I hope that helps,
Tom

Tom Brodhurst-Hill
BareFeetWare

--
iPhone/iPad/iPod and Mac software development, specialising in databases
develo...@barefeetware.com
 --
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] INSERT OR UPDATE?

[BareFeetWare]

On 02/07/2011, at 4:54 AM, KeithB wrote:

> Is there any way to perform the equivalent of an INSERT OR UPDATE statement?
> 
> I have something like this:
> 
> CREATE TABLE t1 (id INTEGER PRIMARY KEY, value INTEGER);
> CREATE TABLE t2 (parent INTEGER REFERENCES t1 ON DELETE CASCADE, child
> INTEGER REFERENCES t1);
> INSERT INTO t1 VALUES(1,100);
> INSERT INTO t1 VALUES(2,200);
> INSERT INTO t2 VALUES(1,2);
> 
> Later on, I want to change the value of t1(1,100) to (1,101) or insert
> a new row, I don't know which.

Do this:

begin immediate;
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 development, specialising in databases
develo...@barefeetware.com
 --
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] Defining a relationship as unique

[BareFeetWare]

On 30/06/2011, at 11:09 PM, Black, Michael (IS) wrote:

> I believe you may be right...can someone in-the-know confirm that the "create 
> index" below is redundant?

I don't have inside knowledge, but yes, it is redundant to create an index on a 
primary key column.

As I understand it:

The built in integer primary key column on each table if effectively its own 
index. The table is organised (ie effectively "indexed") by that column, so a 
separate index would be just a repeat of what's already in the table, so would 
be redundant.

For any other column (or columns) that you specify as primary key or unique: 
SQLite makes an automatic index for each.

> I'm the type that likes to be explicit but perhaps that's a bad idea here.

> Do we end up with 2 indexes thereby slowing things down on inserts?

If you also make your own index (where an auto-index 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...@barefeetware.com
--
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] Simple schema design help

[BareFeetWare]

On 30/06/2011, at 8:43 PM, Ian Hardingham wrote:

> Hey Tom, many thanks for the help.

You're welcome.

> At times I will need to identify whether a match is a "tournament match" 
> or not.  It seems from what you're suggesting that I should do a select 
> on the tournamentMembershipTable (with zero results being "no") rather 
> than having a tournamentMatch boolean in the matchTable.

Good question.

You don't need to, and shouldn't count results and then check if it is zero. 
It's commonly done but it's bad practice and unnecessary. Logically, all you 
need to ask is if just one match exists or to ask if it appears anywhere in the 
list, without needing to scan the entire list. Fortunately SQL has two methods 
to do just that.

select exists (select 1 from tournamentMembershipTable where matchID = 
theDesiredMatch);

or:

select theDesiredMatch in (select matchID from tournamentMembershipTable);

If your condition requires checking more than one column (which is not the case 
here), then the exists method is the only option.

Note that since matchID is the primary key, it's already effectively indexed.

> This seems mildly counter-intuitive to me but I'm trying to learn DB design!

Since only some matches will be part of a tournament, it's bad practice (not 
normalised) to create a foreign key reference column in matches to point to 
tournament, since it will mostly just have a null value.

It's better to have a separate table, like the one I gave:

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


Which only needs a row for each actual relation (ie each match that is part of 
a tournament). The "delete cascade" will keep it in sync so that if you delete 
a match or tournament, the related rows in this table will delete also. Since 
each match can appear only once in this table, I've set it up to use 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 by 
default.

Tom Brodhurst-Hill
BareFeetWare

--
iPhone/iPad/iPod and Mac software development, specialising in databases
develo...@barefeetware.com
--
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] Defining a relationship as unique

[BareFeetWare]

On 30/06/2011, at 8:56 PM, Ian Hardingham wrote:

> I have this table:
> 
> tournamentParticipantTable
> 
> id INTEGER PRIMARY KEY
> user INTEGER
> tournamentId INTEGER
> 
> I'm obviously going to put an index on both user, tournamentId and 
> tournamentId, user

Why is it obvious? It will depend on what searches you are doing.

As Igor mentioned, an index on a, b effectively includes an index on a, so you 
don't need to specify it separately. Not that (a, b) gives a different index to 
(b, a) for different purposes, so again, it depends on your searches as to what 
you need (maybe both, or neither).

> - but as the relation is unique, I was wondering if I could in some way let 
> SQLite know that?

You can do it like this:

create table tournamentParticipantTable
(   id integer primary key not null
,   user integer not null references user(id) on delete cascade
,   tournamentId integer not null references tournamentTable (id) on delete 
cascade
,   unique(user, tournamentId)
)
;

which automatically creates the needed index for you too.

Or you can leave out the unique constraint in the table and instead add an 
index:

create unique index tournamentParticipantUniqueIndex on 
tournamentParticipantTable (user, tournamentId)

> Also... it does seem weird that id is the primary key when I'll never 
> actually use it.

The integer primary key is created whether or not you specify it in the create 
table syntax. By specifying 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 development, specialising in databases
develo...@barefeetware.com
 --
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] Simple schema design help

[BareFeetWare]

On 30/06/2011, at 12:35 AM, Ian Hardingham wrote:

> I have an existing table, the matchTable, where each entry holds a lot 
> of information about a "match".
> 
> I am adding a tournament system, and each match will either be in a 
> tournament or not in a tournament.
> 
> Should I add a "tournamentID" column to matchTable?

No, at least ideally not, since a large number of the values will be null, 
which is not a proper normalised design.

> Or should I create a new "tournamentMembershipTable" which holds a simple 
> relationship 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,
Tom
BareFeetWare

 --
Comparison of SQLite GUI tools:
http://www.barefeetware.com/sqlite/compare/?ml
--
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] Breaking a string into two

[BareFeetWare]

> 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' etc. I want to split them into 
>> two columns. 
>> 
>> How can I do this in SQLite? A regex or offset/position and replace function 
>> would take care of this (by looking for the second
>> uppercase letter), but there's no regex or offset/position function 
>> provided. 

On 16/06/2011, at 10:11 PM, Igor Tandetnik wrote:

> You could do something like
> 
> ltrim(substr("Silo And Region", 2), 'abc...xyz')
> 
> to extract the second part (spell out the rest of the alphabet in place of 
> '...'). The first part is left as an exercise for the reader.

Great advice, Igor, thank you. Works well.

I maintain that we really need some string searching ability, built into 
SQLite. It's a major omission that is often an issue.

Thanks,
Tom
BareFeetWare

 --
Comparison of SQLite GUI tools:
http://www.barefeetware.com/sqlite/compare/?ml
 --
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] Breaking a string into two

[BareFeetWare]

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' etc. I want to split them into two columns.

On 16/06/2011, at 2:19 PM, Simon Slavin wrote:

> How many different first words are there ?  Is it just 'North' and 'South' ?  
> Maybe the two other directions too ?  Or are there hundreds of them ?

There could be dozens of each but the main issue is that I don't know what they 
are until I get the data. That's what I meant by:

>> I don't know all of the parts (ie "Silo Group" and "Region") until I bring 
>> in the Import.

So I can't completely populate lookup tables before I bring in the Import. 
There will be a new Import periodically which might contain new Silo Groups and 
Regions, and I won't be able to manually add those to the lookup tables before 
importing.

Ideas?

Thanks,
Tom Brodhurst-Hill
BareFeetWare

--
iPhone/iPad/iPod and Mac software development, specialising in databases
develo...@barefeetware.com
 --
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] Breaking a string into two

[BareFeetWare]

Hi all,

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

How can I do this in SQLite? A regex or offset/position and replace function 
would take care of this (by looking for the second uppercase letter), but 
there's no regex or offset/position function provided.

If I know all of the possible combinations beforehand, such as:

create table "Silo Group"
(   ID integer primary key not null
,   Name text collate nocase not null
)
;
insert into "Silo Group" (Name) values ('North')
;
insert into "Silo Group" (Name) values ('South')
;
create table "Region"
(   ID integer primary key not null
,   Name text collate nocase not null
)
;
insert into "Region" (Name) values ('Plains')
;
insert into "Region" (Name) values ('Slopes')
;

then I can extract by looking for a match in each table, such as:

select  "Silo Group".Name as "Silo Group"
,   "Region".Name as "Region"
from "Import"
left join "Silo Group" on "Import"."Silo And Region" like "Silo 
Group".Name || '%'
left join "Region" on "Import"."Silo And Region" like % || "Region".Name
;

But I don't know all of the parts (ie "Silo 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,
Tom Brodhurst-Hill
BareFeetWare

--
iPhone/iPad/iPod and Mac software development, specialising in databases
develo...@barefeetware.com
 --
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] Implementing busy-waiting on SQLite

[BareFeetWare]

On 13/06/2011, at 6:49 PM, Ian Hardingham wrote:

> As was being discussed yesterday, I have four processes accessing the 
> same database file.  When they perform an sqlite action, I wish them to 
> block if the DB is not available.  SQLite does not block if it finds the 
> db busy or locked, it returns an error code.

Short answer:

Make sure each process accesses the SQLite database inside their own 
transaction. If the transaction contains a command that will change the 
database (eg delete, update or insert), then use "begin immediate" to start the 
transaction, so that it won't proceed unless it has unchallenged access to the 
database. For other transactions, which are making no changes (ie just select), 
use "begin deferred" (which is the default behavior for plain "begin").

The logic is that only one writing (update, delete or insert) transaction at a 
time will be given a reserved lock. Each writing transaction must wait their 
turn and will not start until they can have "immediate" reserved access. 
Meanwhile, multiple read-only (select) transactions can happily start and 
finish at will.

For more info 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 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] Multiple relations

[BareFeetWare]

On 14/06/2011, at 8:29 PM, Ian Hardingham wrote:

> Guys, I have another DB design question.

Have you implemented the normalisation we discussed before, yet, especially id 
integer primary key column for players? Otherwise, stop here.

> I have 100 - 1 clients connected to my server.

Is a client the same as a player, people and user? I'll assume so.

> Each client has a status.  Many clients are "watching" *one* other 
> client, which means that any change in that client's status must 
> immediately be sent to the watching clients.
> 
> Estimates of numbers:
> 
> - 70% of people online will be watching someone
> //
> - 50% of people will be watched by 0 clients
> - 40% of people will be watched by 1 client
> - 9% of people will be watched by 2 clients
> - 1% of people will be watched by 3 or more clients
> 
> Here's my attempt at a schema:
> 
> - because your status is changing often I would have a separate table, 
> clientStatusTable, which would hold client ids and their status

Does every player/client have a status (one and only one)? If so, you could 
just keep the status as a column in the player/client table. But a separate 
clientStatusTable would be OK, possibly immeasurably slower. In either case, I 
suggest making a separate status table and using foreign key references to it, 
rather than storing the text of the status repeatedly for each player.

> - I would have another table, clientWatchingTable, which would store 
> relations between clients and who they are watching.  I would probably 
> index on watched client because I would need to select everyone watching 
> a client often
> 
> Does this seem like a sane approach?

Yes, that seems like a good approach, as long as you've normalised the 
player/user/client table already. Then you'd have something like this:

create table status
(   id integer primary key not null
,   name text not null unique collate nocase
)
;
create table user
(   id integer primary key not null
,   name text not null unique collate nocase
,   email text collate nocase
,   key text
,   status integer references status(id)
,   other columns
)
;
create table clientWatchingTable
(   id integer primary key not null
,   watcher references user (id)
,   watching references user (id)
)
;
create index clientWatchingWatcherIndex on clientWatchingTable (watcher)
;

And you could get the name and statuses of all users being watched by a 
particular user by:

select  watchingUser.name
,   status.name as status
from clientWatchingTable
join user on watcher = user.id
join 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.com
 --
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] Store result of calculation or not

[BareFeetWare]

On 12/06/2011, at 9:59 PM, Ian Hardingham wrote:

> I often need to get the "record" between two people - how many games they've 
> won and lost against each other.  For reference, the query is

> SELECTcount(*) TotalGames
> , sum(score > 0) GamesWonByPlayer1
> , sum(score < 0) GamesWonByPlayer2
> , sum(score = 0) Draws
> FROM multiturnTable
> WHERE complete=1 AND player1 = '?' AND player2 = '?'
> 
> SELECTcount(*) TotalGames
> , sum(score < 0) GamesWonByPlayer1
> , sum(score > 0) GamesWonByPlayer2
> , sum(score = 0) Draws
> FROM multiturnTable
> WHERE complete=1 AND player1 = '?' AND player2 = '?'

You should have ? instead of '?', unless there's something unique about your 
programming language.

> Once again, multiturnTable has a million rows, I have separate indexes on 
> complete and player1 and player2  (should I also add an index on player1, 
> player2?)

Yes. Since your query asks for player1 = ? AND player2 = ?, then SQLite will 
look for an index in that order, so should benefit greatly from an index on 
(player1, player2). Since the query also has complete = 1, I think you should 
change the order to match the index, ie:

where player1 = ? and player2 = ? and complete = 1

That way, SQLite will use the index on player1, player2, and then just filter 
the remainder according to those that have complete = 1. There's no point 
indexing "complete" since it only contains two distinct values. If anyone knows 
SQLite's internal workings better and finds error with this summary, let us 
know.

> and I know that I should be using ids rather than strings for players!

Well, if you know it, then do it ;-) It's likely to make a significant 
difference to your queries and probably remove the need for you to have all 
these statistics caching tables, not to mention ensuring internal consistency, 
saving a lot of disk space and other overheads.

It shouldn't be very hard to do, at least for a time test. Just create another 
table like this:

begin immediate
;
create table Player
(   id integer primary key not null
,   name text unique not null collate nocase
,   other columns
)
;
create table multiturnTableNormalised
(   id integer primary key not null
,   player1 integer not null references Player(id)
,   player2 integer not null references Player(id)
,   other columns
)
;
create index multiturnTableNormalisedPlayers on multiturnTableNormalised 
(player1, player2)
;
insert into Player (name)
select distinct player1 from multiturnTable
union
select distinct player2 from multiturnTable
;
insert into multiturnTableNormalised (id, player1, player2, other columns)
select  rowid as id
,   (select id from Player where name = player1) as player1
,   (select id from Player where name = player2) as player2
,   other columns
from multiturnTableNormalised
;
commit
;

then you can query, such as:

select  count(*) as TotalGames
,   sum(score > 0) as GamesWonByPlayer1
,   sum(score < 0) as GamesWonByPlayer2
,   sum(score = 0) as Draws
from multiturnTableNormalised
where   player1 = (select id from Player where name = ?)
and player2 = (select id from Player where name = ?)
and complete = 1

> Anyway, my question is - should I have a vsRecordTable which stores all of 
> these and updates on match completion, or should I calculate each time I need 
> it?

As I mentioned in my previous email, and implied above, I suggest that you do 
some tests to see if you can do all of your queries live, rather than caching 
the statistics. With integer key columns and good indexes, your speed may be 
ample for what you need, and save a lot of caching and extra tables. If the 
speed turns out to be too slow and you're sure that you've optimised the 
schema, then at least you know you sure that the caching table are worth the 
effort.

> vsRecordTable could easily have a million entries.

> PS - you guys have been fantastically helpful to me during the course of 
> development of Frozen Synapse, and if anyone is at all interested in the game 
> 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 of SQLite GUI tools:
http://www.barefeetware.com/sqlite/compare/?ml
--
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] Spatialite in Xcode

[BareFeetWare]

On 06/06/2011, at 9:32 PM, Jan wrote:

> did you already check the SpatiaLite website. There is a whole chapter 
> about integrating SpatiaLite in different ways (OSes). I took a look at 
> it myself  yesterday (though Xcode was not the reason) - very good website.

Thanks for the reply.

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/compare/?ml

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Spatialite in Xcode

[BareFeetWare]

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

--
iPhone/iPad/iPod and Mac software development, specialising in databases
develo...@barefeetware.com
 --
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] Sqlite shell text wrapping?

[BareFeetWare]

On 06/06/2011, at 9:41 AM, Kyle Malloy wrote:

> After creating a Database.sqlite i then create a Table and try to past text 
> from a text file into the shell, but the text wraps? Im working on an iphone 
> app and im new to it all. Ive been reading lots of tutorials and it seems 
> that everyone builds databases this way. I have just under 4,000 lines of 
> code i pre-typed in a text file. I want to be able to copy and paste it all 
> into the shell. 
> Example of the text I'm Pasting into Shell:INSERT INTO Datalist(staff,floor) 
> VALUES(‘Dick, Tom’,2);
> Example of the text after Pasting into Shell:INSERT INTO 
> Datalist(staff,floor) Dick, ,2);TomVALUES(
> Please someone help whats the best way to get all this info in a .sqlite 
> database by copy past. Also if there is a good tutorial out there could i 
> please get the link.  Thank you Kyle

You should:

1. Use straight quotes, not smart quotes. (But I suspect they're only appearing 
here in your mail message, not your importing file.)

2. Normalize your data. Don't put multiple values (eg Tom & Dick) in one 
column. Instead, create a people table and another table that links multiple 
people with each floor.

Something like this:

create table staff
(   id integer primary key not null
,   "first name" text collate nocase
,   "last name" text collate nocase
,   "email" text collate nocase
)
;
create table Floor
(   id integer primary key not null
,   "some other info, unique to each floor" text
)
;
create table "Floor Staff"
(   id integer primary key not null
,   floor integer not null references Floor(id)
,   staff integer not null references Staff(id)
)
;

-- Then insert your data:
begin
;
insert into Staff ("first name") values ('Tom')
;
insert into Staff ("first name") values ('Dick')
;
insert into "Floor Staff" ("floor", "staff")
select 2, id from "Staff" where "first name" = 'Tom'
;
insert into "Floor Staff" ("floor", "staff")
select 2, id from "Staff" where "first name" = 'Dick'
;
commit
;

If you want to get a list of people on a particular floor, just select like 
this:

select "first name"
from "Floor Staff" join "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" = "Staff".id
where "floor" = 2
;

Tom
BareFeetWare

--
iPhone/iPad/iPod and Mac software development, specialising in databases
develo...@barefeetware.com
 --
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] Howto pivot in SQLite

[BareFeetWare]

On 06/06/2011, at 8:30 AM, Sam Carleton  wrote:

> allow the user to select the line and bring up a secondary dialog to manage 
> the list of images

You could simply execute a second select when the user asks for the set of 
images for that invoice. 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?

[BareFeetWare]

rdLow = new.p2SubmitScore
where   new.p1SubmitScore > new.p2SubmitScore and dailyGameRecordHigh < 
new.p1SubmitScore
;
-- if player2 won:
update userTable
set totalRecordHigh = new.p2SubmitScore
,   totalRecordLow = totalRecordLow + new.p1SubmitScore
where   new.p2SubmitScore > new.p1SubmitScore and totalRecordHigh < 
new.p2SubmitScore
;
update userTable
set dailyRecordHigh = new.p2SubmitScore
,   dailyRecordLow = new.p1SubmitScore
where   new.p2SubmitScore > new.p1SubmitScore and dailyRecordHigh < 
new.p2SubmitScore
;
update userTable
dailyGameRecordHigh = new.p2SubmitScore
,   dailyGameRecordLow = new.p1SubmitScore
where   new.p2SubmitScore > new.p1SubmitScore and dailyGameRecordHigh < 
new.p2SubmitScore
;
end
;


Then your end match SQL becomes simply one SQL statement:

update multiturnTable set complete = 1 where id = ?

That will fire the trigger to update the user data, which all occurs in one 
transaction.

Another option worth considering is to not store and update the individual user 
game history data in the userTable at all. You could simply cross reference and 
perform calculations on the multiturn table when needed. This saves you from 
having to keep tables in sync and updated, and eliminates redundant data. The 
speed is potentially slower, but you probably won't notice with indexes on 
player1 and player2.

So, for instance, when wanting the stats for a particular user, you could do 
this:

select
userTable.id
,   userTable.name
,   sum(player1.p1SubmitScore) + sum(player2.p2SubmitScore) as 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 userTable.id = ? and multiturnTable.complete = 1
;

I hope this helps.

Tom
BareFeetWare

 --
Comparison of SQLite GUI tools:
http://www.barefeetware.com/sqlite/compare/?ml
--
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] Do I need to migrate to MySQL?

[BareFeetWare]

On 03/06/2011, at 9:47 PM, Ian Hardingham wrote:

> What is basically happening is that we're getting a fairly large number 
> of requests every second.  There is one specific activity which takes 
> about 2 seconds to resolve, which is finishing a match.  This requires 
> an update 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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite in Xcode

[BareFeetWare]

On 27/05/2011, at 10:42 AM, Simon Slavin wrote:

> Tom, John ran across two common problems with SQLite and Xcode:
> 
> A) Xcode seems to want to interpret .c and .h files as C++ instead of C.
> B) Confusion common to people who normally program for Windows or non-Open 
> systems about what combination of files they need: a library, a framework, C 
> source, a .h file.
> 
> An explanation of these points somewhere would be great.

I bypassed that whole issue by just using the SQLite framework built into Mac 
OSX and iOS, and by using an SQLite wrapper for Objective C. Then you don't 
have to worry about compiling SQLite source files into your project or even 
have to bother with the low level sqlite3_ C calls. The only code you then have 
to write is effectively an nice Objective-C executeQuery: method call, which 
returns and array of dictionaries. It's all 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 mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite in Xcode

[BareFeetWare]

On 27/05/2011, at 12:49 AM, john darnell wrote:

> I am still trying to get SQLite to work on my Mac.  I want to make sure I am 
> doing what I should be doing.

Here's a simple answer:

1. Add the sqlite library to your target's build phase.

2. Add an Objective C wrapper class to your project, such as the one by "Matteo 
Bertozzi":
http://th30z.blogspot.com/2008/11/objective-c-sqlite-wrapper_8445.html

  2.1 Download the Sqlite.h and SQLite.m files

  2.2 Add them to your project.

3. To call SQLite from your code, simply import the Sqlite wrapper class and 
use either the executeNonQuery or executeQuery method. The executeQuery method 
returns an array of dictionaries:

#import "Sqlite.h"

Sqlite* sqlite = [[Sqlite alloc] initWithFile:@"path to file"];

NSArray* resultDictArray = [sqlite executeQuery:@"select Name, Quantity from 
Ingredients order by Name"];

[sqlite executeNonQuery:@"delete from Ingredients where Name = 'squash'"];

[sqlite close];

It's best to create the sqlite object as an ivar within your class, and release 
it in your class's dealloc.

I presented this recently at a CocoaHeads meeting. You can view 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

[BareFeetWare]

On 23/05/2011, at 11:13 PM, jose isaias cabrera wrote:

> SharedDB file is about 600 megs shared over a network drive and it's getting 
> slow, but if I get the specific record ID only with the select that I want, 
> it's a lot faster than getting the select with all the items in one shot. 
> SQLite probably does something in the back ground to get things faster when 
> addressed specifically to an ID.

600MB and slow seems like a good candidate for better structuring your 
database. If you post your full schema, we can suggest a better way to 
structure it for optimal speed 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

[BareFeetWare]

Hi Jose,

> I would like to get a bunch of records of IDs that I already know. For 
> example, this table called Jobs,
> rec,...,data,...
> 1,...,aaa,...
> 2,...,zzz,...
> ...
> ...
> 99,...,azz,...

In addition to the pure syntax answer of other, I suggest you also consider 
your broader application of this query. From where are you getting the IDs in 
the first place? If you are getting them from a preliminary query, you can 
probably combine both queries into one for much better SQL and performance. Do 
as much of the logic in SQL as you can to create internally consistent 
databases and less application code. It saves a lot of converting results back 
and forward from SQL to application objects.

For example, if your schema is:

create table Jobs
(   ID integer primary key not null
,   Title text collate nocase
,   Company text collate nocase
,   Date date
-- and more columns
)
;
create table "Job Skills"
(   ID integer primary key not null
,   Job integer not null references Jobs(ID)
,   Skill text not null collate nocase
,   unique (Job, Skill)
)
;

(You should instead normalize the "Job Skills" table by using a third "Skills" 
table, but that's another story.)

And your two queries are:

select Job from "Job Skills" where Skill = "SQLite";

which gives you a list of Job IDs, which you're then re-injecting into a second 
query:

select * from Jobs where ID in (87, 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 Skills" where Skill = "SQLite")
order by Jobs.ID desc
;

Tom
BareFeetWare

 --
Comparison of SQLite GUI tools:
http://www.barefeetware.com/sqlite/compare/?ml
--
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] IN clause in search query to search a single field containing comma delimited values

[BareFeetWare]

On 14/05/2011, at 5:15 AM, Trevor Borgmeier wrote:

> I have a database where a field's value is a comma separated list 
> indicating the basic categories the item belongs to.
> 
> so if the "categories" field has a value of "1,8,15"
> 
> I want to do a query like this:
> 
> SELECT categories FROM myTable WHERE "8" IN (categories);
> 
> but it only finds records where "8" is the only category...

The "in" operator deals with sets (eg explicit list of items or the results of 
a select), not text strings. You would use "in" like this:

select * from MyTable where Category in (1, 8, 15)
or:
select * from MyTable where Category in (select Category from OtherTable where 
OtherTable.Name = MyTableName)

See:
http://www.sqlite.org/lang_expr.html
under the heading "The IN and NOT IN operators"

> Is there anyway for it to evaluate the contents fo the categories field first 
> rather than compare it as a whole?

There is no function built into SQLite to convert a text string into a set (eg 
convert "1,8,15" into (1, 8, 15)), but such a function is not needed in this 
case. You need a better design of your database. SQLite is relational and you 
need to make your schema relational.

> The describe query works in MySQL, but the port doesn't... So far the hack is 
> to do something like this...
> 
> SELECT categories FROM myTable WHERE (","||categories||",") LIKE "%,7,%";
> 
> but I'm guessing LIKE isn't as efficient, and the query is more awkward.

Yes, that will work but yes, it is inefficient. Again, it doesn't use the 
relational engine that you have at your disposal.

> Any advise would be appreciated.  Thanks!

You need to "normalize" your data structure. One of the demands of a normalized 
structure is that each column contains only one value. So instead of having 
multiple Category values stored in the Categories column, you need a separate 
table that lists each of the Categories linked to its MyTable row. This might 
look something like this:

create table MyTable
(   ID integer primary key not null
,   Name text
)
;

create table Category
(   ID integer primary key not null
,   Name text
)
;

create table "MyTable Category"
(   ID integer primary key 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 "MyTable Category".Category = 8
;

If you're confused, please post your schema, including at least some data, and 
I'll show you how it works in your case.

Tom
BareFeetWare

--
iPhone/iPad/iPod and Mac software development, specialising in databases
develo...@barefeetware.com
 --
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] data issues on iPhone

[BareFeetWare]

On 24/04/2011, at 10:49 AM, Mickey Mestel wrote:

> we are using sqlite on the iPhone, in conjunction with SQLCipher, so the 
> sqlite version is compiled in with SQLCipher.  the version of sqlite is 
> 3.7.2, and 4.3 of iOS.
> 
> i have an issue that suddenly started appearing all of a sudden.  what is 
> happening is that the data is inserted into the database, but a read of the 
> database is returning empty rows.  it's returning the correct number of rows, 
> and there is no error, but all columns in the row and empty strings.

Have you tried removing SQLCipher, 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.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Disabling a unique index

[BareFeetWare]

On 08/04/2011, at 7:42 PM, Jaco Breitenbach wrote:

> My application makes use of a SQLite table with a unique index.  During
> normal processing, the unique index is used to ensure no duplicate records
> are entered into the table.  However, at regular intervals large numbers of
> records (millions of records) that are already known to be unique, are
> inserted into the SQLite table from another source.  In order to speed up
> these bulk inserts, I first drop the index on the SQLite table, do the bulk
> insert, and then recreate the index.
> 
> The problem I'm encountering is that dropping of the index can potentially
> take much longer than recreating it.

Are you doing it all within a transaction? eg:

begin immediate;
drop index if exists "My Index";
insert a pile of rows;
create unique index "My Index" on "My Table ("My Column 1", "My Column 2");
commit;

This should shift all the major disk writing to the end of the transaction, 
hopefully speeding the process as a whole.

If the transaction fails, note that although SQLite will rollback the insert 
statement, it doesn't automatically rollback the 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
--
iPhone/iPad/iPod and Mac software development, specialising in databases
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] IF-THEN-ELSE sqlite

[BareFeetWare]

On 03/04/2011, at 6:49 PM, Marcelo S Zanetti wrote:

> I have an item to insert in the table which will be inserted only if this 
> item is not yet in that table otherwise I would like to return the item's key.
> 
> like that 
> 
> IF 1==SELECT COUNT(*) from table 
> WHERE item==new THEN SELECT itemID from tabel WHERE item==new ELSE 
> INSERT INTO table (item) VALUES (new)
> 
> It does not work in this way ... could somebody tell me please what is the 
> correct sintax or whether this is possible at all.

SQL is a set manipulation language, not a procedural language. So you write 
commands that affect a subset of data all at once. To accomplish your task, 
you'd write this:

insert into Table (Item) select new where new not in (select item from Table);
select ItemID from Table where Item = new;

Furthermore, if you are returning the key for some more 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
 --
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] primary key on two columns of an associative table

[BareFeetWare]

On 29/03/2011, at 12:59 AM, Sam Carleton wrote:

> The system calls for an associative table, a table with two foriegn
> keys to two other tables, allowing for a many to many relationship.
> Is there any way to make the primary key be both the columns?

Yes, you can define a primary key on two columns:

CREATE TABLE Invoice_Item_Favorite
(   Invoice_Item_Id INTEGER
,   FavoriteId INTEGER
,   primary key (Invoice_Item_Id, FavoriteId)
,   FOREIGN KEY(Invoice_Item_Id) REFERENCES Invoice_Item(Invoice_Item_Id)
,   FOREIGN KEY(FavoriteId) REFERENCES Favorite(FavoriteId)
);

The primary key constraint does the same job as the unique constraint, so I 
would instead use the build in integer primary key as the primary key and 
define anything else as unique, like this:

CREATE TABLE Invoice_Item_Favorite
(   ID integer primary key
,   Invoice_Item_Id INTEGER
,   FavoriteId INTEGER
,   unique (Invoice_Item_Id, FavoriteId)
,   FOREIGN KEY(Invoice_Item_Id) REFERENCES Invoice_Item(Invoice_Item_Id)
,   FOREIGN KEY(FavoriteId) REFERENCES Favorite(FavoriteId)
);

This doesn't use any extra disk space or memory, since it just aliases the 
build 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 SQLite GUI tools:
http://www.barefeetware.com/sqlite/compare/?ml
--
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] Lack of "decimal" support

[BareFeetWare]

On 27/03/2011, at 3:04 PM, Patrick Earl wrote:

> If you use a view to return a double, you've lost the exact value you were 
> trying to save by storing the decimal as a text value.

I'm not suggesting storing as a text value. I'm suggesting storing as an 
integer and only converting to a float (or, I guess a text value is possible 
too) for display purposes.

> If you continue to work with it as an integer, it's exact, but that requires 
> continual awareness of the number of decimal places at any point in time.

If we're talking about money amounts, isn't the number of decimal places always 
two? ie an amount stored as an integer as 12345 means 12345 cents means 123.45 
dollars.

> In essence, you have to build significant numeric infrastructure into your 
> program to emulate the missing numeric infrastructure in SQLite.

Perhaps I'm missing something of your 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/sqlite/compare/?ml
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Lack of "decimal" support

[BareFeetWare]

On 27/03/2011, at 2:09 PM, Patrick Earl wrote:

> if you're in a context where you don't have significant understanding of the 
> user's query, how do you determine if 1.05 is $1.05 or 105%?

Can you give us a bit more background and an example of this?

How is the interface for the 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
develo...@barefeetware.com
 --
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] Lack of "decimal" support

[BareFeetWare]

> On Sat, Mar 26, 2011 at 8:15 PM, BareFeetWare  
> 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 you are formulating generic queries within
> a place such as an ORM like NHibernate, you would need to figure out
> when to translate the user's "100" into "1".

You can keep all internal transactions as integers, so there are no float 
rounding errors. You only have to translate the final figures if you want to 
display to the user as dollars. You can do this in selects or use views to 
convert the data if needed. For instance:

create table Staff
(   ID integer primary key not null
,   Name text collate nocase not null
,   Salary integer -- in cents
)
;
create view "Staff Dollars"
as
select
ID
,   Name
,   round(Salary/ 100.0, 2) as Salary
from "Staff"
;

> As well, if you multiplied numbers, you'd need to re-scale the result.  For 
> example, (1 * 1) would be (100 * 100 = 1), which is 1 * 1 = 100. :(

I can't think of any reason for multiplying two money amounts. You would only 
ever multiple a money amount by a plane number, so you only ever have to /100 
if you want to present your final answer in dollars. I do this for invoice 
totals, tax return calculations and similar.

Tom
BareFeetWare

--
iPhone/iPad/iPod and Mac software development, specialising in databases
develo...@barefeetware.com
 --
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] Lack of "decimal" support

[BareFeetWare]

On 27/03/2011, at 12:39 PM, Patrick Earl wrote:

> Base-10 numbers are frequently used in financial calculations because
> of their exact nature.  SQLite forces us to store decimal numbers as
> text to ensure precision is not lost.  Unfortunately, this prevents
> even simple 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/iPod and Mac software development, specialising in databases
develo...@barefeetware.com
 --
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] detect a flaw

[BareFeetWare]

On 24/03/2011, at 4:41 PM, Shalom Elkin  wrote:

> Sorry - it doesn't work.
> sqlite3_exec with the pragma directive returns no error, but the program
> still agrees to insert a record that violates foreign_key constraint.

What version of SQLite are you 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
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] detect a flaw

[BareFeetWare]

On 24/03/2011, at 2:50 AM, Shalom Elkin wrote:

> I appreciate the input. Some of the advice comes obviously from very good
> and talented people who find a challenge at doing things WITHOUT reverting
> to code writing.

Doing as much (or most often, all) of the logic in SQL (instead of application 
code) removes a level of complexity, but is also generally faster and 
internally consistent.

> I did a small program. Current show -stopper :
> 
> what is the API equivalent of
> 
> PRAGMA foreign_keys = ON;

You can just send each of the SQL commands, including 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.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] detect a flaw

[BareFeetWare]

On 23/03/2011, at 9:03 PM, Shalom Elkin wrote:

> I am new to SQLITE. Here's the challenge:
> 
> sqlite> .dump
> BEGIN TRANSACTION;
> CREATE TABLE people(
>id integer,
>nm text);
> INSERT INTO "people" VALUES(1,'Jack');
> INSERT INTO "people" VALUES(2,'Jill');
> CREATE TABLE activity(
>aid integer,
>act  text,
>foreign key (aid) references people(id)
> );
> COMMIT;
> PRAGMA foreign_keys=On;
> sqlite> .import ac.in activity
> Error: foreign key mismatch
> 
> This is ac.in
> 
> 1|eat
> 2|sleep
> 3|run
> 1|drink
> 2|dream
> 
> id 3 doesn't exist, the insertion fails. Now, this was easy. what if
> ac.inhad millions of rows? I am looking for some way to get a message
> like "error
> in line 3: foreign key mismatch".
> preferably, the import would go on without actually inserting, but report
> ALL errors in one pass.
> 
> Any ideas?

I usually import into a separate table, then use SQL to process the data into 
the final destination. This way, I can use any dumb import tool (such as the 
.import command line tool) and take care of the smarts (including constraints, 
error logging etc) in SQL.

How about this:

pragma foreign_keys = on;
create temp table "activity import"
(   aid integer
,   act text
)
;
create table "import error" (aid integer);

.import ac.in "activity import"

begin immediate;
insert into "import error" select aid from "activity import" where aid not in 
(select id from "people");
insert into "activity" select * from "activity import" where aid in (select id 
from "people");
commit;

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";
commit;

For this to work, you probably want to define the primary key in people:

CREATE TABLE people
(   id integer primary key not null
,   nm text
)
;

Tom
BareFeetWare

--
iPhone/iPad/iPod and Mac software development, specialising in databases
develo...@barefeetware.com
 --
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] adding/dropping foreign key to existing table

[BareFeetWare]

On 23/03/2011, at 1:17 PM, Sam Carleton wrote:

> I don't see any examples on http://www.sqlite.org/foreignkeys.html how to 
> either add or drop a foreign key to an existing table.  What might that 
> syntax look like exactly?

You have to drop the old table and create a new one with the changed foreign 
keys. If you have data in the table, you'll want to back it up fist, like this:

begin immediate;
create temp table "My Table Backup" as select * from "My Table";
drop table "My Table";
create table "My Table" ();
insert into "My Table" select * from "My Table Backup";
drop table "My Table Backup";
commit;

Unfortunately SQLite doesn't automatically rollback a transaction if the create 
or drop statements fail. So you have to detect any errors and, if so, issue a 
rollback (instead of commit).

> Also, 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. Post the relevant parts of your schema.

Thanks,
Tom
BareFeetWare

--
iPhone/iPad/iPod and Mac software development, specialising in databases
develo...@barefeetware.com

 --
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] Create table if not exists and insert seed data

[BareFeetWare]

On 22/03/2011, at 9:04 AM, Erich93063 wrote:

> I am trying to create a SQLite database if it doesn't exist, which I
> know I can use 'CREATE TABLE IF NOT EXISTS", but more importantly, I
> need to initially populate the database with seed data if it doesn't
> exist. If I use CREATE TABLE IF NOT EXISTS, it will obviously create
> the table if it doesn't exist, but if I follow that up with insert
> statements, those would ALWAYS get ran. I only want to enter the seed
> data if the database does not exist. ???

Others have suggested solutions that require you to step back and forth from 
SQL to application code (except Max's solution), but you can do this in pure 
SQL. Something like:

begin immediate;
create temp table if not exists "Variables" (Name text unique collate nocase, 
"Value");
insert or replace into "Variables" select 'MyTable exists', 1 in (select Name 
from SQLite_Master where type = 'table');
create table if not exists "My Table" (ID integer primary key not null, Name 
text unique); -- or whatever your definition
insert into "My Table" (Name) select 'First row' where (select "Value" 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 (select "Value" from 
"Variables" where Name = 'MyTable exists');
commit;


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

Re: [sqlite] Unique index versus unique constraint

[BareFeetWare]

On 18/03/2011, at 8:58 PM, Paul van Helden wrote:

> Another solution that ocurred to me is to check for "sqlite_autoindex_" in
> the name field of pragma index_list(tablename). Can I simply assume that
> unique indexes named sqlite_autoindex_* cannot be dropped?

Automatic indexes have no "SQL" entry in the SQLite_Master table, so you can 
use that to see if you can drop it. eg:

select Name from SQLite_Master where type = 'index' and SQL is null

will give you the name of all the automatic indexes, which can't be dropped.

> Perhaps if pragma table_info(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

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[BareFeetWare]

On 05/03/2011, at 1:59 AM, "J Trahair"  wrote:

> I understand about Primary keys and Unique keys attributed to their own 
> field. Is there a need to have other fields indexed, for faster searching? 
> Eg. a table containing your favourite music. Say you have 9 Beethoven 
> symphonies (one row in the table for each), 14 Bach partitas and 100 Haydn 
> symphonies, and you want to select the Bach ones.
> You have a table called AllMusic containing columns called Composer, 
> NameOfPiece, YearComposed, etc.
> 
> SELECT * FROM AllMusic_tbl WHERE Composer = 'Bach' ORDER BY YearComposed

In addition to Simon's fine answer, you should also consider properly 
normalizing your database. In essence, this means removing redundancy and 
replacing any repeated values with references to discrete rows in their own 
table. This also makes indexes automatic, since selects use the primary key 
(which is automatically indexed) of each table. In short, you use less space, 
get faster queries, consistent data and can more efficiently changing of data 
(ie one entry instead of many).

Something like this:

begin immediate
;
create table Music
(   ID integer
primary key
not null
,   Name text
unique
not null
collate nocase
,   Composer integer
references Composer (ID)
,   "Year Composed" integer
)
;
create table Composer
(   ID integer
primary key
not null
,   Name text
unique
not null
collate nocase
)
;
commit
;

You don't need any extra indexes. Your select now looks like:

select
Music.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"

[BareFeetWare]

On 03/03/2011, at 11:53 AM, Rami Abughazaleh wrote:

> Trying to insert a new row in a table that has a column specifically set to 
> the rowid data type

There is no rowid data type. To alias rowid you need to just type it as integer 
primary key.

> will cause an error message like "value cannot be null".
> 
> CREATE TABLE [test] (
>[Id] ROWID NOT NULL,
>[Name] nvarchar(50) NOT NULL,
>[Value] nvarchar(256) NOT NULL
> );


You instead need:

create table "Test"
(   "Id" integer primary key not null
,   "Name" 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.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] Ideas for Searching Names in Contacts

[BareFeetWare]

On 25/02/2011, at 2:23 AM, Mohit Sindhwani  wrote:

> Thank you for your suggestions - I tried the approach using LIKE and it 
> seemed quite slow (this was on Windows CE).. FTS3/4 seem good to explore.

Yes, like '%name%' will be slow since it can't use an index. However, like 
'name%' will use an index (if the column has one).

So I suggest something like:

create table Person
(  ID integer primary key not null
,  "First Name" text collate nocase
,  "Middle Name" text collate nocase
,  "Last Name" text collate nocase
)
;
create index "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 "First Name" like 'Mick%' or "Middle Name" like 
'Mick%' or "Last Name" like 'Mick%';

Tom
BareFeetWare
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] apostrophes in strings...

[BareFeetWare]

On 21/02/2011, at 8:11 AM, Scott Hess wrote:

> You can also convert:
>  ATTACH DATABASE x AS y KEY z
> to:
>  SELECT sqlite_attach(x, y, z)
> where the parameters can be turned into bind arguments.  Then embedded quotes 
> won't be an issue.

SQLite won't allow an "attach" 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.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

[BareFeetWare]

On 21/02/2011, at 12:41 PM, Roger Binns wrote:

>> How can I best scroll though the results of an arbitrary select query?
> 
> If you have infinite time and memory then there is no problem.

Memory and processor are limited (typically iPad/iPhone). Disk space would cope 
with creating temporary tables if necessary. In general, I am trying to cater 
for a table or view (ie arbitrary select), the results of which would not fit 
entirely into memory.

> You haven't stated your constraints or how arbitrary "arbitrary" really is.

By arbitrary, I mean that the user can type any select query that SQLite allows 
(or have a view in their schema), which might make use of order by, group by, 
where, union etc.

>> 1. Is there any significant overhead on SQLite from my selecting from a view 
>> representing the original arbitrary select? That is, will SQLite still use 
>> any indexes etc correctly? Or do I need to dissect/parse the original select 
>> statement, changing the where statement etc?
> 
> You can work out the answer yourself by using EXPLAIN and EXPLAIN QUERY PLAN 
> of some representative examples.

Yes, but I wondered if there was some overriding logic that SQLite uses that 
would provide a theoretical/logical rather than experimental guide.

>> 2. If the arbitrary select statement already contains an "order by" clause, 
>> then I obviously need to use the order by column(s) specified there as the 
>> keyColumn for the scrolling, rather than override the order by. Is there any 
>> way to get the sort order of an arbitrary select statement?
> 
> Results are returned in the order requested or randomly(*) if not.  Given you 
> can have subqueries with ordering, collations and all sorts of other things, 
> trying to extract the actual ordering is as difficult as implementing the 
> SQLite engine itself.
> 
> (*) In practise it is in btree iteration order but that is not something you 
> should depend on.

Yes, I understand that the sort order of results cannot be counted on as 
consistent if no order by clause is give. But if I am imposing a sort order (eg 
by rowid) I want to as closely as possible match the undefined sort order so 
the results look the same. Is sorting by rowid in a table as close I could get 
to this? What order by sequence could I best use to match the results of a 
select from joined tables?

> You can even "ORDER BY random()".

Hmm, good point. I guess in that case (are there other cases?) I can't count on 
the results being the same from one select to the next, so preparing the 
statement, extracting some rows, closing, then preparing and extracting again 
when the user scrolls won't work, since the results will change. If there is a 
random() component then I would have to leave the query/prepare open, denying 
all other access to that database file, until there will definitely be no more 
scrolling. Correct?

> The rest of your questions assume a particular solution.  The only thing that 
> will reliably work is to reissue the query using skip and limit

By "skip" do you mean select where rowid > , or offset or 
something else?

> assuming no changes in between.

Yes, I can assume no changes in between (though the random() possibility above 
will make this approach fail I think).

> This is if you are trying to save memory/disk and there is no possibility of 
> changes between scrolling operations.

Yes.

> If you need to be resilient to that too (implied by "arbitrary" since user 
> defined functions could have side effects)

Hmm, true. I hadn't thought of user defined function side effects. I don't have 
to allow for that at the moment, but  I'll keep it in mind. Is it common or 
even good practice for a user function (used in a select statement) to modify 
the table from which it's selecting? That seems like bad practice to me and I 
can't see why you'd do that rather than use update, insert or delete rather 
than select to make changes.

> then the solution is to 'CREATE TEMP TABLE results AS ...select...'.  This 
> will also work if someone uses
> "ORDER BY random()" or any other udf that depends on more than its arguments.

Hmm, good thinking. I'll consider that. The downside is that creating a 
temporary table would require SQLite to process every row in the select, 
whereas prepare/step only processes 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

[BareFeetWare]

On 21/02/2011, at 12:41 PM, Simon Slavin wrote:

>> How can I best scroll though the results of an arbitrary select query?
> 
> Suppose the results of the SELECT change between your original decision to do 
> the scrolling and the time the user decides to scroll.  Should what's shown 
> on the display reflect the data as it originally was, or up-to-date data ?  
> Or could there never be any such changes ?

Thanks for the reply Simon.

Good question. In most or all cases for now, the select results will not change 
mid scroll. The user interface will be clearly either scrolling or editing. In 
the longer term I will probably allow editing mid scroll, but that's fine, 
since I can flag within my app that results need to be re-selected.

>> 1. Is there any significant overhead on SQLite from my selecting from a view 
>> representing the original arbitrary select? That is, will SQLite still use 
>> any indexes etc correctly? Or do I need to dissect/parse the original select 
>> statement, changing the where statement etc?
> 
> A VIEW is a saved SELECT query -- the query, not the results, are saved.  So 
> I think you don't need to make the extra effort you describe.

Yes, I understand that a view doesn't save any results. I'm unclear, however, 
as to how smart is SQLite's query optimizer to, for instance, realise that when 
it's selecting from a view, it can use the indexes of source columns for the 
where filter.

>> 2. If the arbitrary select statement already contains an "order by" clause, 
>> then I obviously need to use the order by column(s) specified there as the 
>> keyColumn for the scrolling, rather than override the order by. Is there any 
>> way to get the sort order of an arbitrary select statement?
> 
> I don't know of any.

Hmm, OK. I guess I'll need to impose a sort order on otherwise unordered query 
results. For a table, I'll just sort by rowid. For a view, I'll probably just 
look for any indexed column.

>> 3. This method requires that keyColumn is defined as unique (or primary 
>> key), otherwise it can skip rows of data. Is there any way to allow for a 
>> non-unique keyColumn?
> 
> No, but instead of using just keyColumn you could use (keyColumn,rowid).  
> This would ensure your key was always unique, and will work on arbitrary 
> SQLite tables unless someone is intentionally messing with how SQLite works.

Yes, I was thinking along those lines (ie order by keyColumn, rowid). That will 
work for tables. I'll have to adapt it somehow for views.

>> 5. I understand that "Rule Number 1" is to "not leave queries open".
> 
> Correct.  Don't allow a user to create and close a query just by choosing 
> when they want to scroll through a list.
> 
>> So what's the best way to minimize the overhead of repeatedly running the 
>> same query but with a different where clause and limit (and order if 
>> reversing). I'm thinking I would be best to actually keep the query (ie 
>> prepared statement) open while live scrolling (eg flicking through rows on 
>> an iPhone/iPad), not using a limit clause at all, but instead just keep 
>> getting more rows as 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) .
> 
> To get the following or previous line to one which is already being shown, 
> find the key for that row (which you should save in memory as you're 
> displaying the line) and use
> 
> SELECT  FROM  WHERE (keyColumn||rowid)>lastlineKey ORDER BY 
> keyColumn,rowid LIMIT 1
> 
> to get the following line or
> 
> SELECT  FROM  WHERE (keyColumn||rowid) keyColumn,rowid LIMIT 1

Yes, that's where I was headed, but I wondered if running that query 
continuously might have unnecessary overhead since it's constantly preparing a 
new query for each row. I wondered if I'd be better just having

SELECT  FROM  WHERE (keyColumn||rowid)>lastlineKey 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 rows are scrolling.

Thanks for your thoughts,
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] SQLite GUI comparison

[BareFeetWare]

On 22/02/2011, at 4:31 AM, skywind mailing lists wrote:

> "Supports SQLite extension" would be an accurate feature description. And in 
> the cell (value) I suggest to put - if supported - FTS2, FTS3, RTree etc., 
> otherwise a "-". A yes or no is insufficient because some support RTree but 
> not FTS and vice versa.

OK, that sounds good. I'll probably use "no" or "none" if no extension is 
supported.

Can anyone please tell me what should go in this cell for any SQLite GUI app 
they know (ie either FTS2, FTS3, RTree or none). 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

[BareFeetWare]

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 
Date: 16 February 2011 12:05:47 AM AEDT
To: General Discussion of SQLite Database 
Subject: [sqlite] Scrolling through results of select

Hi all,

I'm looking for the best way to scroll through data returned by an arbitrary 
select statement. That is, I want something that will work for any SQL select 
statement string I throw at it at runtime.

I've looked through:

http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor

and a few posts on this list.

1. It seems that in order to extract, say, the first n rows from a select, I do 
this:

begin
;
create temp view if not exists "Scrolling View" as 
;
select * from "Scrolling View"
order by keyColumn
limit n
;
commit
;

2. And then, to get the next n rows:

maxVisibleKeyValue = last value of keyColumn in visible set (returned above).

begin
;
create temp view if not exists "Scrolling View" as 
;
select * from "Scrolling View"
where keyColumn > :maxVisibleKey
order by keyColumn
limit n
;
commit
;

For the next 10 rows, I should repeat step 2.

For reverse scrolling, I'll run something like:

minVisibleKeyValue = first value of keyColumn in visible set.

begin
;
create temp view if not exists "Scrolling View" as 
;
select * from "Scrolling View"
where keyColumn < :minVisibleKeyValue
order by keyColumn desc
limit n
;
commit
;

Questions:

1. Is there any significant overhead on SQLite from my selecting from a view 
representing the original arbitrary select? That is, will SQLite still use any 
indexes etc correctly? Or do I need to dissect/parse the original select 
statement, changing the where statement etc?

2. If the arbitrary select statement already contains an "order by" clause, 
then I obviously need to use the order by column(s) specified there as the 
keyColumn for the scrolling, rather than override the order by. Is there any 
way to get the sort order of an arbitrary select statement?

3. This method requires that keyColumn is defined as unique (or primary key), 
otherwise it can skip rows of data. Is there any way to allow for a non-unique 
keyColumn?

4. If the arbitrary select statement does not specify an order by, how can I 
least affect the output (ie not impose a sort order) but still facilitate 
scrolling? For selecting from a table, the best I can think of is to use rowid 
(or its alias), which seems to be the typical result order when no order is 
specified. But when selecting from a view (which may contain joins), by which 
column(s) can I explicitly sort (for the sake of scrolling) that will best 
mimic the usual SQL output order (which I know is "undefined")?

5. I understand that "Rule Number 1" is to "not leave queries open". So what's 
the best way to minimize the overhead of repeatedly running the same query but 
with a different where clause and limit (and order if reversing). I'm thinking 
I would be best to actually keep the query (ie prepared statement) open while 
live scrolling (eg flicking through rows on an iPhone/iPad), not using a limit 
clause at all, but instead just keep getting more rows as 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/sqlite/compare/?ml

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[BareFeetWare]

On 21/02/2011, at 3:20 AM, skywind mailing lists wrote:

> in your comparison chart it would also be nice to see which software is able 
> to support SQLite extension. A couple of them do not support the FTS nor 
> RTree capabilities of SQLite.

Sure, I'd be happy to add that. How do you 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://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] ANN: Base 2.0, Mac SQLite GUI

[BareFeetWare]

Hi Ben,

In reply to your announcement of Base 2:

> Just a short message to announce that version 2.0 of Base, our Mac SQLite GUI 
> is now available.

The new version looks great. Congrats :-)

> It's a major upgrade, the highlight of which is the ability for the app to 
> view, create & alter tables with support for *all* table- and column-level 
> constraints.

Neat.

When I choose "Alter Table", it nicely shows the list of columns, but 
mistakenly also shows the constraints as if they were columns, with nothing in 
the Constraints list. I tested a few schemas, including this one:

create table "Products Detail"
(
Supplier integer
not null
references Suppliers (ID)
on delete restrict
on update cascade
,   Code text
not null
collate nocase
,   Detail text
not null
collate nocase
,   primary key (Supplier, Code)
,   foreign key (Supplier, Code)
references "Products Base" (Supplier, Code)
on delete cascade
on update cascade
)
;

which shows in Base 2 as:

>> Columns:
>> 
>> Name  Type Constraints
>> Supplier  integer  NF
>> Code  test NC
>> Detailtext NC
>> primary   key
>> 
>> Constraints:
>> 
>> none listed

The "Alter" panel also shows an "Origin" column, which I think only makes sense 
in a view.

When saving a changed table, Base seems to try to explicitly save any auto 
indexes (which of course fails). For instance, when saving changes to the above 
table, Base informed me:

>> There were problems re-creating table indexes. One or more indexes present 
>> in the table before modification could not be recreated. Their original SQL 
>> is listed below:
>> 
>> CREATE UNIQUE INDEX "sqlite_autoindex_Products Detail_1" ON "Products 
>> Detail" ("Supplier", "Code");

I also failed to save the trigger, but that was probably due to it messing up 
the create table columns (eg adding a column called "primary" etc).

> You can read the full announcement (with a link to release notes) here: 
> 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

[BareFeetWare]

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

 --
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] foreign key on delete no action

[BareFeetWare]

Hi all,

I've been implementing foreign keys in some of my older tables, as detailed at:
http://www.sqlite.org/foreignkeys.html

usually like this:

foreign key ChildTable (ChildColumn1, ChildColumn2)
references ParentTable (ParentColumn1, ParentColumn2)
on delete cascade

which is working well.

In some cases (eg for in table that logs changes), while I want to specify a 
foreign key relationship, I don't want deletions in the parent to cascade into 
the child or restrict the deletion. I notice that there is a "no action" 
option, so I've started to use that:

foreign key ChildTable (ChildColumn1, ChildColumn2)
references ParentTable (ParentColumn1, ParentColumn2)
on delete no action

I have a few questions:

1. What does SQLite do if the action is just left blank? Does it have the same 
affect as explicitly writing "no action"? ie is the above the same as:

foreign key ChildTable (ChildColumn1, ChildColumn2)
references ParentTable (ParentColumn1, ParentColumn2)

2. I modified the schema to include "no action" on an app on my iPad (running 
SQLite version 3.6.23.2). But when I move it to my Mac (running SQLite version 
3.6.12) and then run:

pragma integrity_check;

I get an error:

SQL error: malformed database schema (ChildTable) - near "no": syntax error

Is this to be expected? I know that foreign key actions are supported in SQLite 
version 3.6.19 onward, but previous version 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 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)

[BareFeetWare]

> IMDb Pro offers additional information and services, all the information I
> am grabbing is already available to the public and I am not publishing this
> information, there are a lot of programs that do this.

OK, good to know, thanks.

>>> The script is written in bash and I can give you the code but I think it
>>> would be very complicated to adapt it to your schema.
>> 
>> Complicated? Why? In what format is the grabbed data (ie what tables,
>> columns, rows)? It usually just takes an insert select to move data from
>> one table's schema to another.
>> 
> Well, you would have to replace the insert commands, lets say that I have
> table movies with columns id,title,year,plot
> In the script I have to set the columns as variables:
> id=tt12345
> title="movie title"
> year=2011
> plot="this is the plot"
> 
> Ant then I call the command insert:
> insert movies
> 
> The command insert knows the columns for each table, it goes column by
> column, if the column is declared as a variable it is inserted so you would
> have to replace all those commands with something like:
> echo "INSERT INTO movies" etc...

Oh, I see, so the "complication" is that you would have to change the columns 
used in your bash script. That shouldn't be a big problem, but I'll leave the 
bash script to you.

If for some reason rewriting the insert command in the bash script is 
insurmountable, you can just create a view in SQL to match the expectations of 
the bash script. That view can funnel each insert to the underlying SQL schema 
table columns using an "instead of insert" trigger. Let me know if you need 
more info on this.

> Let me know if you are interested in the script, the script is included in 
> https://launchpad.net/figuritas/+download the application , but I can post
> and explain the relevant functions if you want.

No, that's fine, I just couldn't understand why changing the insert statement 
in the bash script is a problem.

>> What you want, I think, however, is how many people there are who are
>> directors (counting each person just once, even if they direct multiple
>> movies), given by this:
>> 
>> select count(*) from "Capacity People Statistics"
>> where Capacity_ID = (select ID from Capacity where Name = 'director')
>> ;
>> 
> Ok then, yes I wanted to count directors counting each person just once, I 
> think that that code will not count much faster.

I expect it will be *much* faster than count(distinct), almost instant.

>> Hopefully now that I've given you the query you actually needed, it now
>> makes sense ;-)
>> 
> Yes it does, but then I don't need the Count column since I want to speed up 
> counting distinct directors but counting each person just once.

Well, you will actually need the Count column if you plan on facilitating 
deletion of directors, since you will need to track when the count of a 
person's director roles drops to zero (so they're no longer counted). In that 
case, you will also need a slightly modified select:

select count(*) from "Capacity People Statistics"
where Capacity_ID = (select ID from Capacity where Name = 'director')
and Count > 0
;

or you can change the delete trigger to remove statistics that drop to a zero 
count:

begin immediate
;
drop trigger if exists "Movie People delete"
;
create trigger "Movie People delete"
on "Movie People"
after delete
begin
insert or replace into "Capacity People Statistics" (Capacity_ID, People_ID, 
Count)
select
old.Capacity_ID
,   old.People_ID
,   (   select coalesce(Count, 0) - 1 from "Capacity People Statistics"
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 I want to make it very fast, with those
> two additional tables I think it will be much faster.

Great, I'm glad we finally got there :-)

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] Scrolling through results of select

[BareFeetWare]

Hi all,

I'm looking for the best way to scroll through data returned by an arbitrary 
select statement. That is, I want something that will work for any SQL select 
statement string I throw at it at runtime.

I've looked through:

http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor

and a few posts on this list.

1. It seems that in order to extract, say, the first n rows from a select, I do 
this:

begin
;
create temp view if not exists "Scrolling View" as 
;
select * from "Scrolling View"
order by keyColumn
limit n
;
commit
;

2. And then, to get the next n rows:

maxVisibleKeyValue = last value of keyColumn in visible set (returned above).

begin
;
create temp view if not exists "Scrolling View" as 
;
select * from "Scrolling View"
where keyColumn > :maxVisibleKey
order by keyColumn
limit n
;
commit
;

For the next 10 rows, I should repeat step 2.

For reverse scrolling, I'll run something like:

minVisibleKeyValue = first value of keyColumn in visible set.

begin
;
create temp view if not exists "Scrolling View" as 
;
select * from "Scrolling View"
where keyColumn < :minVisibleKeyValue
order by keyColumn desc
limit n
;
commit
;

Questions:

1. Is there any significant overhead on SQLite from my selecting from a view 
representing the original arbitrary select? That is, will SQLite still use any 
indexes etc correctly? Or do I need to dissect/parse the original select 
statement, changing the where statement etc?

2. If the arbitrary select statement already contains an "order by" clause, 
then I obviously need to use the order by column(s) specified there as the 
keyColumn for the scrolling, rather than override the order by. Is there any 
way to get the sort order of an arbitrary select statement?

3. This method requires that keyColumn is defined as unique (or primary key), 
otherwise it can skip rows of data. Is there any way to allow for a non-unique 
keyColumn?

4. If the arbitrary select statement does not specify an order by, how can I 
least affect the output (ie not impose a sort order) but still facilitate 
scrolling? For selecting from a table, the best I can think of is to use rowid 
(or its alias), which seems to be the typical result order when no order is 
specified. But when selecting from a view (which may contain joins), by which 
column(s) can I explicitly sort (for the sake of scrolling) that will best 
mimic the usual SQL output order (which I know is "undefined")?

5. I understand that "Rule Number 1" is to "not leave queries open". So what's 
the best way to minimize the overhead of repeatedly running the same query but 
with a different where clause and limit (and order if reversing). I'm thinking 
I would be best to actually keep the query (ie prepared statement) open while 
live scrolling (eg flicking through rows on an iPhone/iPad), not using a limit 
clause at all, but instead just keep getting more rows as 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/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)

[BareFeetWare]

On 13/02/2011, at 1:04 AM, Yuzem wrote:

> I am grabbing the data from the each movie imdb webpage.

Does IMDB allow use of their data this way? After my brief reading of their 
site, I thought they charge a $15k minimum per year for data.

> The script is written in bash and I can give you the code but I think it 
> would be very complicated to adapt it to your schema.

Complicated? Why? In what format is the grabbed data (ie what tables, columns, 
rows)? It usually just takes an insert select to move data from one table's 
schema to another.

>> You count directors like this:
>> 
>> select sum(Count) from "Capacity People Statistics"
>> where Capacity_ID = (select ID from Capacity where Name = 'director')
>> ;
>> 
> Are you sure that this count how many directors there are?

Well, yes, but it depends of the definition of "how many directors there are". 
The above counts how many directors there are in total, counting the same 
person for each movie they direct.

What you want, I think, however, is how many people there are who are directors 
(counting each person just once, even if they direct multiple movies), given by 
this:

select count(*) from "Capacity People Statistics"
where Capacity_ID = (select ID from Capacity where Name = 'director')
;

> If I understand it correctly (probably I don't) you have for example table
> "Movie People":
> 1|director|1
> 2|director|1
> 3|director|2
> 
> There are 2 directors and then in "Capacity People Statistics" you should
> have:
> director|1|2
> director|2|1
> 
> If you use the previous code:
> select sum(Count) from "Capacity People Statistics"
> where Capacity_ID = (select ID from Capacity where Name = 'director')
> ;
> 
> I think it will  return 3 but there are only 2 directors.

Correct. That query will tell you that there are three (3) directors in the 
database. But two (2) of those directors are the same person.

To instead get what you want, the number of people who are directors, do this 
(repeat of above SQL):

select count(*) from "Capacity People Statistics"
where Capacity_ID = (select ID from Capacity where Name = 'director')
;

which gives your desired answer of 2.

> Sorry, but I still don't understands it because I don't understand that you 
> can count directors that way.

Hopefully now that I've given you the query you actually needed, it now makes 
sense ;-)

>> I expect the above to be about the same speed or faster... than separate 
>> tables, but far more flexible (eg no need to add a table to accommodate 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 right, it is less flexible.

> Oh wait... I think I got it. I need two more tables:

No, I don't think you need more tables to achieve the above.

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

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

[BareFeetWare]

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 flexible (eg no 
need to add a table to accommodate a new capacity), and better normalized.

> Another thing: I don't understand the purpose of the Count column in table
> "Capacity People Statistics"

It hopefully now makes sense with my correction and example selects above.

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)

[BareFeetWare]

On 11/02/2011, at 1:37 PM, Yuzem wrote:

> For example lets say I have the following data:
> 1|director|1
> 2|director|2
> 3|director|1
> 
> In this example the total count for directors is 2, I have two distinct 
> directors.
> In the table "Capacity Statistics" I will have:
> director|2
> 
> The triggers you made add/subtract 1 from "Capacity Statistics" on
> insert/delete on "movies people"
> What happens if I add the following to "movies people"?
> 4|director|2
> 
> The trigger should add 1 to "Capacity Statistics":
> director|3
> 
> But there are still 2 directors:
> 1|director|1
> 2|director|2
> 3|director|1
> 4|director|2

Oh, I see. I was counting the total number of each unique capacity (including 
director, writers), but you want the number of each unique (capacity, person). 
No problem. We'll just add a People column to the statistics, and change the 
uniqueness constraint.

Replace my earlier "Capacity Statistics" and triggers with this:

begin immediate
;
create table "Capacity People Statistics"
(   ID integer primary key references "Capacity" (ID) on delete cascade
,   People_ID integer not null references "People" (ID) on delete cascade
,   Count integer not null
,   unique (ID, People_ID)
)
;
insert into "Capacity People Statistics" (ID, People_ID, Count)
select Capacity_ID, People_ID, count(*) from "Movie People"
group by Capacity_ID, People_ID having Count > 0
;
create trigger "Movie People insert"
on "Movie People"
after insert
begin
insert or replace into "Capacity People Statistics" (ID, People_ID, Count)
select
new.Capacity_ID
,   new.People_ID
,   (   select coalesce(Count, 0) + 1 from "Capacity People Statistics"
where ID = new.Capacity_ID and People_ID = new.People_ID
)
;
end
;
create trigger "Movie People delete"
on "Movie People"
after delete
begin
insert or replace into "Capacity People Statistics" (ID, People_ID, Count)
select
old.Capacity_ID
,   old.People_ID
,   (   select coalesce(Count, 0) - 1 from "Capacity People Statistics"
where ID = old.Capacity_ID and People_ID = old.People_ID
)
;
end
;
create trigger "Movie People update"
on "Movie People"
after update of Capacity_ID, People_ID
begin
insert or replace into "Capacity People Statistics" (ID, People_ID, Count)
select
old.Capacity_ID
,   old.People_ID
,   (   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.Capacity_ID
,   new.People_ID
,   (   select coalesce(Count, 0) + 1 from "Capacity People Statistics"
where ID = new.Capacity_ID and People_ID = new.People_ID
)
;
end
;
commit
;


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)

[BareFeetWare]

On 11/02/2011, at 11:30 AM, Simon Slavin wrote:

> Come to think of it, what's really happening here is a FOREIGN KEY situation.

Yes, that's why I have the foreign keys (ie "references") in the schema. So, 
for instance, if you delete a movie, all of the actors, directors etc 
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/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)

[BareFeetWare]

On 11/02/2011, at 11:11 AM, Simon Slavin wrote:

> Erm ... there are also movies which have more than one person directing.  You 
> need to copy across the rowid from the MoviePeople table, and delete just 
> based on that particular record.

Adding to what Simon said:

The schema I posted allows for multiple directors for each movie, the same 
person having multiple capacities in the same movie (eg writer, director and 
actor) etc. Any changes to the model and what's allowed (ie constraints) should 
be done there and not in your application layer, IMNSHO.

If you want to delete or insert, based on text (eg person's name), I suggest 
NOT getting your application to get the matching rowid, then reinjected it into 
a second SQL call. Do it all in one SQL transaction, such as:

delete from "Movie People"
where Movie_ID = (select Movie_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')

or:

update "Movie People"
set People_ID = (select ID from People where Name = 'Michael J Fox')
where Movie_ID = (select Movie_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
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)

[BareFeetWare]

> 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 the application level, the down side is that I am
> updating the cache on any change, I check the modified time of the database.

I strongly suggest doing as much as you can at the SQL level, especially where 
it concerns the integrity of your data. You should find it faster, more 
reliable, self contained and logical.

> BareFeetWare-2 wrote:
>> 
>> Do that with triggers (which are a good thing in this context), to change
>> the relevant count when an insert, delete or update occurs in the "Movie
>> People" table. Something like:
>> 
> I think, not sure, that there will be a problem with that code.
> I am not an expert so I had to stare that code some time to understand it.
> If I am correct you are adding and subtracting 1 on every insert or delete.

Yes, and doing a delete/insert combo for an update (ie if a person's capacity 
in a movie changes).

> The problem is that an insert may not be unique so before augmenting the
> counter you have to check if it is unique and therefore you would have to
> run a count(distinct col) on every insert which would be overkill.

If I understand you correctly, the non-unique criteria is already handled by 
the schema design. The "Movie People" table allows the same person to be listed 
as the director for multiple movies. The constraints on the "Movie People" only 
require no nulls and unique combinations of Movie, Capacity, People (ie the 
same person can't be listed as director for the same movie twice, but can be 
listed as director for two movies). Because it uses the "unique" constraint for 
this, SQLite automatically builds an index "Movie People"(Movie_ID, 
Capacity_ID, People_ID), so that whenever you try to insert a new row, it 
quickly checks if it already exists. You can throw new non-unique rows at it 
with "insert or ignore" if you just want it to ignore duplicates, which then 
won't trigger the count increase because nothing was inserted. Or use plain old 
"insert" if you want to be alerted to any attempted unique violations.

Does tis answer your needs? If not, please explain further, but it will 
probably only require modifying a constraint in the schema I proposed, rather 
than denormalizing or partitioning.

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

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

[BareFeetWare]

Oops, I should have said old instead of new in a couple of places:

> begin immediate
> ;
> create table "Capacity Statistics"
> ( ID integer primary key unique references "Capacity" (ID) on delete 
> cascade
> , Count integer not null
> )
> ;
> insert into "Capacity Statistics" (ID, Count) select Capacity_ID, count(*) 
> from "Movie People" group by Capacity_ID having Count > 0
> ;
> create trigger "Movie People insert"
> on "Movie People"
> after insert
> begin
> insert or replace into "Capacity Statistics" (ID, Count)
> select new.Capacity_ID, (select coalesce(Count, 0) + 1 from "Capacity 
> Statistics" where ID = new.Capacity_ID)
> ;
> end
> ;
> create trigger "Movie People delete"
> on "Movie People"
> after insert
> begin
> insert or replace into "Capacity Statistics" (ID, Count)
> select new.Capacity_ID, (select coalesce(Count, 0) - 1 from "Capacity 
> Statistics" where ID = old.Capacity_ID)

Should be:

select old.Capacity_ID, (select coalesce(Count, 0) - 1 from "Capacity 
Statistics" where ID = old.Capacity_ID)

> ;
> end
> ;
> create trigger "Movie People update"
> on "Movie People"
> after update of Capacity_ID
> begin
> insert or replace into "Capacity Statistics" (ID, Count)
> 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 "Capacity 
> Statistics" where ID = old.Capacity_ID)

Should be:

select old.Capacity_ID, (select coalesce(Count, 0) - 1 from "Capacity 
Statistics" where ID = old.Capacity_ID)

> ;
> end
> ;
> commit
> ;


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

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

[BareFeetWare]

On 11/02/2011, at 9:40 AM, Yuzem wrote:

> Yes, in my application I have in the sidebar all those sections (movies,
> years, tags, keywords, actors, directors, writers, etc...) and I count each
> one, how many movies, how many years, etc...
> It isn't very slow if I update only one item but the problem is when I have
> to update the entire list, this uses a lot of cpu and takes some time and I
> have to do it every time a movie is added.
> http://3.bp.blogspot.com/_EbNPUgfUDXs/TIpif4U1III/A2A/YFSGOAdpOGA/s1600/nested-brosers.jpg
> Here there is an old screenshot from the sidebar.

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

Do that with triggers (which are a good thing in this context), to change the 
relevant count when an insert, delete or update occurs in the "Movie People" 
table. Something like:

begin immediate
;
create table "Capacity Statistics"
(   ID integer primary key unique references "Capacity" (ID) on delete 
cascade
,   Count integer not null
)
;
insert into "Capacity Statistics" (ID, Count) select Capacity_ID, count(*) from 
"Movie People" group by Capacity_ID having Count > 0
;
create trigger "Movie People insert"
on "Movie People"
after insert
begin
insert or replace into "Capacity Statistics" (ID, Count)
select new.Capacity_ID, (select coalesce(Count, 0) + 1 from "Capacity 
Statistics" where ID = new.Capacity_ID)
;
end
;
create trigger "Movie People delete"
on "Movie People"
after insert
begin
insert or replace into "Capacity Statistics" (ID, Count)
select new.Capacity_ID, (select coalesce(Count, 0) - 1 from "Capacity 
Statistics" where ID = old.Capacity_ID)
;
end
;
create trigger "Movie People update"
on "Movie People"
after update of Capacity_ID
begin
insert or replace into "Capacity Statistics" (ID, Count)
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 "Capacity 
Statistics" 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 
flexibility to add or remove Capacities in data rather than having to change 
the schema, and better reflects the real data model.


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

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

[BareFeetWare]

> Thanks you but what I want to do is to count without using count(distinct 
> col) because it is much slower than count(*).

I think you'll find the schema I posted very fast, since it's running 
everything, including distinct, on primary key columns.

Or you can try this:

select count(*) from People
where ID in
(   select People_ID from "Movie People" where Capacity_ID =
(select ID from Capacity where Name = 'director')
);

But I expect you'll get the same performance.

You don't want to denormalize and have separate director and writer tables etc. 
That 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)

[BareFeetWare]

On 08/02/2011, at 10:19 AM, Yuzem wrote:

> I have the tables "movies" and "people"
> Those two tables are related by tables "directors", "writers", etc...
> movies: id, title
> people: id, name
> 
> directors: movieId, peopleId
> writers: movieId, peopleId
> etc...
> 
> How can I normalize that so I can count directors and writers using
> count(*)?
> Should I create another table for "directors" and another for "writers",
> etc... with only the ids?
> I will end up having:
> movies > movies_directors < (directors people)

After implementing the schema in my previous post, add this:

begin immediate;
create table People
(   ID integer primary key not null
,   Name text collate no case not null unique
);
create table Capacity
(   ID integer primary key not null
,   Name text collate no case not null unique
);
create table "Movie People"
(   ID integer primary key not null
,   Movie_ID integer not null references Movies (Movie_ID) on delete cascade
,   Capacity_ID integer not null references Capacity (ID) on delete cascade
,   People_ID integer not null references People (ID) on delete cascade
,   unique (Movie_ID, Capacity_ID, People_ID)
);
commit;

Then you can count the directors like this:

select count(distinct People_ID) from "Movie People" join Capacity on "Movie 
People".Capacity_ID = Capacity.ID where Capacity.Name = 'director';

or:

select count(distinct People_ID) from "Movie People" where Capacity_ID = 
(select ID from Capacity where Name = 'director');

or you can create a view to alphabetically list each director with a CSV list 
of their movies, like this:

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 People".People_ID = People.ID
where Capacity.Name = 'director';
group by People_ID
order by People.Name
;

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] Feature Request: PRAGMA foreign_key_list(tableName) should display the name of the foreign key constraint

[BareFeetWare]

> On Feb 8, 2011, at 9:07 PM, Rami Abughazaleh wrote:
> 
>> I would like to request that "PRAGMA foreign_key_list(tableName)" display
>> the name of the foreign key constraint.

Thirded!

Or, much better yet, move away from pragmas altogether for introspection, 
instead using internal tables/views like the current SQLite_Master. Then we can 
filter the results, such as:

select Name, From_Table, To_Column from SQLite_Foreign_Key_List where To_Table 
= 'My Table' and To_Column = 'My_Column';

Currently, I have to run pragma foreign_key_list() for each of my tables, 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.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)

[BareFeetWare]

You have:

CREATE TABLE movies(movie_id INTEGER, title TEXT, unique(movie_id));
CREATE TABLE tags(movie_id INTEGER, tag TEXT, unique(movie_id,tag));

You can solve your problem, using pure SQL. No need to resort to the 
application layer. Just execute the SQL transaction below. It takes care of 
everything, including normalizing, assigning integer IDs to unique tags, 
filtering our duplicates and auto indexes. I haven't tested on your tables 
specifically, but I do similar all the time. Any errors should be simple name 
errors.

begin immediate;
create temp table "Movies Old" as select * from Movies;
create temp table "Tags Old" from Tags;
drop table Movies;
drop table Tags;
create table Movies
(   Movie_ID integer primary key not null
,   Title text collate no case not null
);
create table Tags
(   Tag_ID integer primary key not null
,   Name text not null unique collate nocase
);
create table Movie_Tags
(   ID integer primary key not null
,   Movie_ID integer not null
references Movies (Movie_ID) on delete cascade
,   Tag_ID integer not null
references Tags (Tag_ID) on delete cascade
,   unique (Movie_ID, Tag_ID)
);
insert into Movies (Movie_ID, Title) select Movie_ID, Title from "Movies Old";

insert or ignore into Tags (Name) select distinct Tag from "Tags Old";

insert into Movie_Tags (Movie_ID, Tag_ID)
select distinct Movie_ID, (select Tag_ID from Tags where Name = "Tags Old".Tag)
from "Tags Old";

drop table "Movies Old";
drop table "Tags Old";

commit or rollback;

Note that the last line "commit or rollback" is not an actual SQLite command. 
You will need to manually decide at that point whether to issue a commit or 
rollback. If there are any errors in previous lines, use rollback. 
Unfortunately, SQLite does not automatically rollback all commands in a 
transaction if one of the commands fails (specifically create and drop 
commands).

Then you should be able to count tags very fast by:

select count(*) from Tags;

In order to make foreign keys work, you need to have SQLite version 3.6.19 or 
later, and use this pragma when you open a connection to SQLite (ie before any 
commands that require use of foreign keys):

pragma foreign_keys = yes;

A normalized database using integer keys is fast, small and elegant. Viewing 
integers by users is ugly. So for viewing the Movie-Tag combinations (and data 
entry), you might want to create a view to make it user friendly. It depends on 
your user interface. Reply here if you need that. Just something like this:

create view "Movie Tags View"
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
;

Hope this helps,
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] EXT : Speed up count(distinct col)

[BareFeetWare]

You have:

CREATE TABLE movies(movie_id INTEGER, title TEXT, unique(movie_id));
CREATE TABLE tags(movie_id INTEGER, tag TEXT, unique(movie_id,tag));

You can solve your problem, using pure SQL. No need to resort to the 
application layer. Just execute the SQL transaction below. It takes care of 
everything, including normalizing, assigning integer IDs to unique tags, 
filtering our duplicates and auto indexes. I haven't tested on your tables 
specifically, but I do similar all the time. Any errors should be simple name 
errors.

begin immediate;
create temp table "Movies Old" as select * from Movies;
create temp table "Tags Old" from Tags;
drop table Movies;
drop table Tags;
create table Movies
(   Movie_ID integer primary key not null
,   Title text collate no case not null
);
create table Tags
(   Tag_ID integer primary key not null
,   Name text not null unique collate nocase
);
create table Movie_Tags
(   ID integer primary key not null
,   Movie_ID integer not null
references Movies (Movie_ID) on delete cascade
,   Tag_ID integer not null
references Tags (Tag_ID) on delete cascade
,   unique (Movie_ID, Tag_ID)
);
insert into Movies (Movie_ID, Title) select Movie_ID, Title from "Movies Old";

insert or ignore into Tags (Name) select distinct Tag from "Tags Old";

insert into Movie_Tags (Movie_ID, Tag_ID)
select distinct Movie_ID, (select Tag_ID from Tags where Name = "Tags Old".Tag)
from "Tags Old";

drop table "Movies Old";
drop table "Tags Old";

commit or rollback;

Note that the last line "commit or rollback" is not an actual SQLite command. 
You will need to manually decide at that point whether to issue a commit or 
rollback. If there are any errors in previous lines, use rollback. 
Unfortunately, SQLite does not automatically rollback all commands in a 
transaction if one of the commands fails (specifically create and drop 
commands).

Then you should be able to count tags very fast by:

select count(*) from Tags;

In order to make foreign keys work, you need to have SQLite version 3.6.19 or 
later, and use this pragma when you open a connection to SQLite (ie before any 
commands that require use of foreign keys):

pragma foreign_keys = yes;

A normalized database using integer keys is fast, small and elegant. Viewing 
integers by users is ugly. So for viewing the Movie-Tag combinations (and data 
entry), you might want to create a view to make it user friendly. It depends on 
your user interface. Reply here if you need that. Just something like this:

create view "Movie Tags View"
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
;

Hope this helps,
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