Re: [sqlite] Understanding temp tables

[Igor Tandetnik]

On 2/7/2011 8:30 PM, Sam Carleton wrote:
> Assuming the connection pooling works correctly, it is my understanding that
> there is absolutely no way for another request to mess with that temp table,
> correct?  In other words:  a temp table lives for the life of a connection

... or until explicitly dropped, whichever comes first.

> and cannot span connections and is not locked by other connections, correct?

Correct.
-- 
Igor Tandetnik

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

[sqlite] Understanding temp tables

[Sam Carleton]

I am using SQLite in a Apache module on Windows.  On Windows, Apache is a
single multi-threaded process.  The Apache DBD is used to leverage
connection pooling.

At one point in the code, the web request gets a connection, creates a temp
table, used the temp table, and then deletes the temp table before returning
the connection to the pool.

Assuming the connection pooling works correctly, it is my understanding that
there is absolutely no way for another request to mess with that temp table,
correct?  In other words:  a temp table lives for the life of a connection
and cannot span connections and is not locked by other connections, correct?

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

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

[Simon Slavin]

On 7 Feb 2011, at 11:19pm, Yuzem wrote:

> One more thing:
> 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)


This is very basic database design.  You need to read up not on SQLite or even 
on SQL, but some books on how to arrange your data in databases.

One solution would include these three tables:

Movies: id, name, releaseDate, etc..

People: id, name, DOB, biog, etc..

MoviePeople: id, movie, person, capacity

Fill your Movies TABLE.  Fill your People TABLE with actors, directors, 
writers, etc..

Then fill the MoviePeople table with connections between the two: Clint 
Eastwood worked in one movie as actor, but he worked in another move as 
director, etc..

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

[Yuzem]

Ok, thanks all for your answers, I guest I will have to normalize the
database and explore the foreign key feature.

One more thing:
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)

Thanks again!
-- 
View this message in context: 
http://old.nabble.com/Speed-up-count%28distinct-col%29-tp30864622p30868907.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] LENGTH on a BLOB field stops at NUL byte

[Samuel Adam]

On Mon, 07 Feb 2011 03:16:54 -0500, Philip Graham Willoughby  
 wrote:

>>SELECT length(CAST ("Data" AS BLOB)) FROM "message_revision";
>
> What about:
>
> UPDATE "message_revision" SET "Data" = CAST ("Data" AS BLOB);

Y’know the urban legend about the folks at the restaurant who design a  
complicated plan for switching the contents of salt and pepper shakers  
which have their hats juxtaposed, and then a passer-by suggests just  
switching the caps?

Thank you, Mr. Willoughby!

Very truly,

Samuel Adam ◊ 
763 Montgomery Road ◊ Hillsborough, NJ  08844-1304 ◊ United States
Legal advice from a non-lawyer: “If you are sued, don’t do what the
Supreme Court of New Jersey, its agents, and its officers did.”
http://www.youtube.com/watch?v=iT2hEwBfU1g


> As a one-time command to correct the table. There is also the option of  
> using triggers to ensure future "Data" values are kept as BLOB if the  
> INSERT code cannot be corrected conveniently:
>
> CREATE TRIGGER trigName AFTER INSERT ON "message_revision" WHEN  
> TYPEOF("Data")!='blob' BEGIN UPDATE "message_revision" SET "Data" = CAST  
> ("Data" AS BLOB) WHERE "message_revision".rowid = NEW.rowid; END;
>
> CREATE TRIGGER trigName2 AFTER UPDATE OF "Data" ON "message_revision"  
> WHEN TYPEOF("Data")!='blob' BEGIN UPDATE "message_revision" SET "Data" =  
> CAST ("Data" AS BLOB) WHERE "message_revision".rowid = NEW.rowid; END;
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Regarding "Manual Control Of Query Plans"

[Simon Slavin]

On 7 Feb 2011, at 9:37pm, Sven L wrote:

> Thanks for input. So you're saying that creating the tables manually is not 
> the same as issuing the "ANALYZE" command?

You have no idea what the ANALYZE command does because it's not documented.  
For all you know different versions of SQLite put different things in this 
table.  So don't try to simulate it, let SQLite do it.  It's hardly difficult: 
just issue that one instruction.  As I wrote upthread, you might want to do it 
about as often as you'd run the integrity check routine.

By the way you may be overestimating the impact of doing this.  It will matter 
only in situations where the query optimizer will never have to guess how best 
to do its searches.  If you've created good indexes and specified your WHERE 
and ORDER BY clauses well, use of this table will be very low.  There is 
definitely little point in doing this until the particular installation has 
lots of data in its tables ... perhaps after months of having had data entered.

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

Re: [sqlite] Regarding "Manual Control Of Query Plans"

[Richard Hipp]

On Mon, Feb 7, 2011 at 4:37 PM, Sven L  wrote:

>
> Thanks for input. So you're saying that creating the tables manually is not
> the same as issuing the "ANALYZE" command?
>

You are not allows to "CREATE TABLE sqlite_stat1" or "DROP TABLE
sqlite_stat1".  (You cannot CREATE or DROP any table whose name begins with
"sqlite_".)  But you can insert or delete those tables once created.

So you have to run ANALYZE at least once to create the tables before you
populate them.


>
> However, ".dump sqlite_stat2" does NOT work. And I have been using the
> SQLITE_ENABLE_STAT2 switch for decades now :P
> This is what this command produces:
>
> PRAGMA foreign_keys=OFF;
> BEGIN TRANSACTION;
> COMMIT;
>
> -
> A bug?
>
>
>
> > From: d...@sqlite.org
> > Date: Mon, 7 Feb 2011 14:42:06 -0500
> > To: sqlite-users@sqlite.org
> > Subject: Re: [sqlite] Regarding "Manual Control Of Query Plans"
> >
> > On Mon, Feb 7, 2011 at 1:48 PM, Sven L  wrote:
> >
> > >
> > > Yes, this is what I have read on the net too. But, tests show that
> without
> > > the sqlite_stat* tables, my queries are ridiculously slow (probably
> unwanted
> > > table scans etc).
> > > Real-life data... can't I simply take my real-life database and extract
> the
> > > data in sqlite_stat*?
> > >
> >
> > You can take a sample database that is representative of what to expect
> for
> > most of your users, run ANALYZE, then do ".dump sqlite_stat1". Save the
> > output. Then when creating a new database for a user, after doing all
> your
> > CREATE TABLE and CREATE INDEX statements, but before adding actual
> content,
> > run ANALYZE there. The ANALYZE command will take a millisecond or so
> since
> > there is no data.
> >
> > Then run the "INSERT INTO sqlite_stat1" statements you saved in order to
> > fill the stat1 table with content from your "sample" database.
> >
> >
> > >
> > > Btw, this command produces nothing even though the table does contain
> > > several rows: ".dump sqlite_stat2"
> > >
> >
> > Compile with SQLITE_ENABLE_STAT2 in order to get the stat2 information.
> > stat2 is a 10-sample histogram of the content of each index. This enables
> > SQLite to make smarter choices about when to use index lookup and when to
> do
> > table scans. For example:
> >
> > SELECT * FROM tab WHERE x=0;
> >
> > The above might do a table scan if a majority of the tab.x values are 0.
> > But if only 1% of the tab.x values are 0, then it will use an index.
> >
> >
> > >
> > > Thanks
> > >
> > > > From: slav...@bigfraud.org
> > > > Date: Mon, 7 Feb 2011 16:44:00 +
> > > > To: sqlite-users@sqlite.org
> > > > Subject: Re: [sqlite] Regarding "Manual Control Of Query Plans"
> > > >
> > > >
> > > > On 7 Feb 2011, at 4:38pm, Sven L wrote:
> > > >
> > > > > So, with this in mind, it makes sense to precompute the
> sqlite_stat*
> > > tables. Right?
> > > >
> > > > Which you do by running ANALYZE, but since it needs real-life data to
> > > work on there's no point doing it until your customer has put some data
> in.
> > > I don't write this type of application any more, but I might put it in
> a
> > > maintenance routine -- some obscure menu option near the Config
> Preferences
> > > or something. Run it as part of your yearly maintenance procedure,
> after
> > > you've run 'PRAGMA integrity_check'.
> > > >
> > > > Simon.
> > > > ___
> > > > sqlite-users mailing list
> > > > sqlite-users@sqlite.org
> > > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> > >
> > > ___
> > > sqlite-users mailing list
> > > sqlite-users@sqlite.org
> > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> > >
> >
> >
> >
> > --
> > D. Richard Hipp
> > d...@sqlite.org
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
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)

[Pavel Ivanov]

> Mabye one of our experts can explain why distinct takes to long.
> Seems to me if you have an index you're just returning the values in the 
> index, aren't you?

If index is unique then you just count number of values in the index
and that's it. If index is not unique then it contains many non-unique
values. So you have to traverse all values and compare each of them
with the previous one to see whether it's different or not. If you
remove word 'distinct' then it will indeed just return values from the
index.


Pavel

On Mon, Feb 7, 2011 at 4:10 PM, Black, Michael (IS)
 wrote:
> Mabye one of our experts can explain why distinct takes to long.
> Seems to me if you have an index you're just returning the values in the 
> index, aren't you?
> But it looks like it's comparing all keys to all keys.
>
> Do you just want a count of distinct values?
> select count(distinct(tag)) from tags;
> I think that will run faster.
>
> Then again...if you normalize your tags you end up with just count(*) from 
> the new tag table to find all unique keys
> sqlite> create index tags_index on tags(tag);
> sqlite> explain select distinct(tag) from tags;
> addr  opcode         p1    p2    p3    p4             p5  comment
>   -        -  --  -
> 0     Trace          0     0     0                    00
> 1     Noop           0     0     0                    00
> 2     Integer        0     3     0                    00
> 3     Integer        0     2     0                    00
> 4     Gosub          5     28    0                    00
> 5     Goto           0     31    0                    00
> 6     OpenRead       2     4     0     keyinfo(1,BINARY)  00
> 7     Rewind         2     18    8     0              00
> 8     Column         2     0     7                    00
> 9     Compare        6     7     1     keyinfo(1,BINARY)  00
> 10    Jump           11    15    11                   00
> 11    Move           7     6     1                    00
> 12    Gosub          4     23    0                    00
> 13    IfPos          3     30    0                    00
> 14    Gosub          5     28    0                    00
> 15    Column         2     0     1                    00
> 16    Integer        1     2     0                    00
> 17    Next           2     8     0                    00
> 18    Close          2     0     0                    00
> 19    Gosub          4     23    0                    00
> 20    Goto           0     30    0                    00
> 21    Integer        1     3     0                    00
> 22    Return         4     0     0                    00
> 23    IfPos          2     25    0                    00
> 24    Return         4     0     0                    00
> 25    Copy           1     9     0                    00
> 26    ResultRow      9     1     0                    00
> 27    Return         4     0     0                    00
> 28    Null           0     1     0                    00
> 29    Return         5     0     0                    00
> 30    Halt           0     0     0                    00
> 31    Transaction    0     0     0                    00
> 32    VerifyCookie   0     2     0                    00
> 33    TableLock      0     2     0     tags           00
> 34    Goto           0     6     0                    00
> sqlite> explain select count(*) from tags;
> addr  opcode         p1    p2    p3    p4             p5  comment
>   -        -  --  -
> 0     Trace          0     0     0                    00
> 1     Goto           0     8     0                    00
> 2     OpenRead       1     4     0     keyinfo(1,BINARY)  00
> 3     Count          1     1     0                    00
> 4     Close          1     0     0                    00
> 5     Copy           1     2     0                    00
> 6     ResultRow      2     1     0                    00
> 7     Halt           0     0     0                    00
> 8     Transaction    0     0     0                    00
> 9     VerifyCookie   0     2     0                    00
> 10    TableLock      0     2     0     tags           00
> 11    Goto           0     2     0                    00
> sqlite> explain select count(distinct(tag)) from tags;
> addr  opcode         p1    p2    p3    p4             p5  comment
>   -        -  --  -
> 0     Trace          0     0     0                    00
> 1     Null           0     2     0                    00
> 2     Null           0     1     0                    00
> 3     OpenEphemeral  1     0     0     keyinfo(1,BINARY)  00
> 4     Goto           0     18    0                    00
> 5     OpenRead       0     2     0     2              00
> 6     Rewind         0     13    0                    00
> 7     Column         0     1     3                    00
> 8     Found          1     12   

Re: [sqlite] Regarding "Manual Control Of Query Plans"

[Sven L]

Thanks for input. So you're saying that creating the tables manually is not the 
same as issuing the "ANALYZE" command?
 
However, ".dump sqlite_stat2" does NOT work. And I have been using the 
SQLITE_ENABLE_STAT2 switch for decades now :P
This is what this command produces:
 
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
COMMIT;

-
A bug?
 
 
 
> From: d...@sqlite.org
> Date: Mon, 7 Feb 2011 14:42:06 -0500
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Regarding "Manual Control Of Query Plans"
> 
> On Mon, Feb 7, 2011 at 1:48 PM, Sven L  wrote:
> 
> >
> > Yes, this is what I have read on the net too. But, tests show that without
> > the sqlite_stat* tables, my queries are ridiculously slow (probably unwanted
> > table scans etc).
> > Real-life data... can't I simply take my real-life database and extract the
> > data in sqlite_stat*?
> >
> 
> You can take a sample database that is representative of what to expect for
> most of your users, run ANALYZE, then do ".dump sqlite_stat1". Save the
> output. Then when creating a new database for a user, after doing all your
> CREATE TABLE and CREATE INDEX statements, but before adding actual content,
> run ANALYZE there. The ANALYZE command will take a millisecond or so since
> there is no data.
> 
> Then run the "INSERT INTO sqlite_stat1" statements you saved in order to
> fill the stat1 table with content from your "sample" database.
> 
> 
> >
> > Btw, this command produces nothing even though the table does contain
> > several rows: ".dump sqlite_stat2"
> >
> 
> Compile with SQLITE_ENABLE_STAT2 in order to get the stat2 information.
> stat2 is a 10-sample histogram of the content of each index. This enables
> SQLite to make smarter choices about when to use index lookup and when to do
> table scans. For example:
> 
> SELECT * FROM tab WHERE x=0;
> 
> The above might do a table scan if a majority of the tab.x values are 0.
> But if only 1% of the tab.x values are 0, then it will use an index.
> 
> 
> >
> > Thanks
> >
> > > From: slav...@bigfraud.org
> > > Date: Mon, 7 Feb 2011 16:44:00 +
> > > To: sqlite-users@sqlite.org
> > > Subject: Re: [sqlite] Regarding "Manual Control Of Query Plans"
> > >
> > >
> > > On 7 Feb 2011, at 4:38pm, Sven L wrote:
> > >
> > > > So, with this in mind, it makes sense to precompute the sqlite_stat*
> > tables. Right?
> > >
> > > Which you do by running ANALYZE, but since it needs real-life data to
> > work on there's no point doing it until your customer has put some data in.
> > I don't write this type of application any more, but I might put it in a
> > maintenance routine -- some obscure menu option near the Config Preferences
> > or something. Run it as part of your yearly maintenance procedure, after
> > you've run 'PRAGMA integrity_check'.
> > >
> > > Simon.
> > > ___
> > > sqlite-users mailing list
> > > sqlite-users@sqlite.org
> > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> 
> 
> 
> -- 
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
  
___
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)

[Black, Michael (IS)]

Mabye one of our experts can explain why distinct takes to long.
Seems to me if you have an index you're just returning the values in the index, 
aren't you?
But it looks like it's comparing all keys to all keys.

Do you just want a count of distinct values?  
select count(distinct(tag)) from tags;
I think that will run faster.

Then again...if you normalize your tags you end up with just count(*) from the 
new tag table to find all unique keys
sqlite> create index tags_index on tags(tag);
sqlite> explain select distinct(tag) from tags;
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Trace  0 0 000
1 Noop   0 0 000
2 Integer0 3 000
3 Integer0 2 000
4 Gosub  5 28000
5 Goto   0 31000
6 OpenRead   2 4 0 keyinfo(1,BINARY)  00
7 Rewind 2 188 0  00
8 Column 2 0 700
9 Compare6 7 1 keyinfo(1,BINARY)  00
10Jump   111511   00
11Move   7 6 100
12Gosub  4 23000
13IfPos  3 30000
14Gosub  5 28000
15Column 2 0 100
16Integer1 2 000
17Next   2 8 000
18Close  2 0 000
19Gosub  4 23000
20Goto   0 30000
21Integer1 3 000
22Return 4 0 000
23IfPos  2 25000
24Return 4 0 000
25Copy   1 9 000
26ResultRow  9 1 000
27Return 4 0 000
28Null   0 1 000
29Return 5 0 000
30Halt   0 0 000
31Transaction0 0 000
32VerifyCookie   0 2 000
33TableLock  0 2 0 tags   00
34Goto   0 6 000
sqlite> explain select count(*) from tags;
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Trace  0 0 000
1 Goto   0 8 000
2 OpenRead   1 4 0 keyinfo(1,BINARY)  00
3 Count  1 1 000
4 Close  1 0 000
5 Copy   1 2 000
6 ResultRow  2 1 000
7 Halt   0 0 000
8 Transaction0 0 000
9 VerifyCookie   0 2 000
10TableLock  0 2 0 tags   00
11Goto   0 2 000
sqlite> explain select count(distinct(tag)) from tags;
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Trace  0 0 000
1 Null   0 2 000
2 Null   0 1 000
3 OpenEphemeral  1 0 0 keyinfo(1,BINARY)  00
4 Goto   0 18000
5 OpenRead   0 2 0 2  00
6 Rewind 0 13000
7 Column 0 1 300
8 Found  1 123 1  00
9 MakeRecord 3 1 400
10IdxInsert  1 4 000
11AggStep0 3 1 count(1)   01
12Next   0 7 001
13Close  0 0 000
14AggFinal   1 1 0 count(1)   00
15Copy   1 5 000
16ResultRow  5 1 000
17Halt   0 0 000
18Transaction0 0 000
19VerifyCookie   0 2 000
20TableLock  0 2 0 tags   00
21Goto   0 5 0  

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

[Petite Abeille]

On Feb 7, 2011, at 9:32 PM, Yuzem wrote:

>> Hey... sounds like IMdb :P
>> 
> Yes, I'm coding a  http://yuzem.blogspot.com/p/figuritas-screenshots.html
> movie manager  that grabs the info from imdb.

A fine hobby :)

> I thought sqlite didn't handle foreign keys correctly,

I thought wrong :)

http://www.sqlite.org/foreignkeys.html

> I am using triggers
> to automatically delete tags when a movie gets deleted.

Don't bother.

> If I use a foreign key will it automatically remove the tag if the movie
> gets deleted?

See above.

> Anyway, to use integers in the "tags" table is very complicated

Nah... it's called normalization... it's a good thing :)

http://en.wikipedia.org/wiki/Database_normalization

> because I will have to assign the corresponding number to each tag that I 
> insert

Yep. It's a good thing.


> and I
> have to insert lots of keywords for every movie.

IMdb contains ~3.5M movie-keyword pairs (~405K distinct movies, 107K distinct 
keywords). Not much to write home about.


> Does sqlite has any function to convert a text string into an unique number?

You do it the other way around: create a unique entry in your tag table, then 
use the tag id in the movie_tag table that join movies to tags. Think of it as 
compression: instead of storing, say, the literal "accidental-cannibalism" 
multiple times in your movie_tag, you store the literal only once in the tag 
table, than use the literal id in the join table. And they lived happily ever 
after third normal form :))


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

[Josh Marell]

I would create a tagList table (integer tagID, string tagName,
unique(tagName))

Before performing your batch of inserts, query the tagList table (integer
tagID, string tagName), and generate a map (key on tagName, value of tagID).
For each tag you want to insert, see if it exists in the map.  If it
doesn't, insert first into tagList table and get the tagID of that new entry
and update your map.  Perform your insert with the tagID instead now.  If it
does exist, use the value of the tagName key in your map.

I chose a map (or some similar implementation), because the lookup should be
quick, and you won't need to query the DB for each new tag during your batch
of inserts.

On Mon, Feb 7, 2011 at 2:32 PM, Yuzem  wrote:

>
>
> Petite Abeille-2 wrote:
> >
> > Hey... sounds like IMdb :P
> >
> Yes, I'm coding a  http://yuzem.blogspot.com/p/figuritas-screenshots.html
> movie manager  that grabs the info from imdb.
>
>
> Petite Abeille-2 wrote:
> >
> > In any case, as mentioned by Michael Black, you might benefit greatly by
> > normalizing your table and indexing the foreign key:
> >
> > http://dev.alt.textdrive.com/browser/IMDB/IMDB.ddl#L401
> >
>
> I thought sqlite didn't handle foreign keys correctly, I am using triggers
> to automatically delete tags when a movie gets deleted.
> If I use a foreign key will it automatically remove the tag if the movie
> gets deleted?
> Anyway, to use integers in the "tags" table is very complicated because I
> will have to assign the corresponding number to each tag that I insert and
> I
> have to insert lots of keywords for every movie.
> Does sqlite has any function to convert a text string into an unique
> number?
>
> --
> View this message in context:
> http://old.nabble.com/Speed-up-count%28distinct-col%29-tp30864622p30867411.html
> Sent from the SQLite mailing list archive at Nabble.com.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
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)

[Eric Smith]

Yuzem wrote: 

> I thought sqlite didn't handle foreign keys correctly, I am using 
> triggers to automatically delete tags when a movie gets deleted.  

There's a new thing, 'PRAGMA foreign_keys=1', that causes SQLite to 
enforce them.  Check out the docs on that.  

> Anyway, to use integers in the "tags" table is very complicated because 
> I will have to assign the corresponding number to each tag that I insert 
> and I have to insert lots of keywords for every movie.  

Well, technically you can have SQLite do that assignment for you.  But
yes, you'll have to deal with that mapping somewhere.  You can abstract
that away at a very low level in your infrastructure though.

It's not so terrible: again it's something like (in one high level 
language)

set tagId [db one {SELECT tagId FROM tagNames WHERE tagName=$tagName}]
db eval {INSERT INTO tags VALUES($movieId, $tagId)}

That one SELECT lookup will probably save you tons of string comparisons
later on.  Depends of course on the nature of your data set and query
stats.

> Does sqlite has any function to convert a text string into an unique 
> number?  

If you run (again correcting for your preferred language)

db eval {INSERT INTO tagNames(tagName) VALUES($tagName)}

then SQLite will generate the tagId column value for you if the column
is declared INTEGER PRIMARY KEY.  Again, you can get it back out at some
point later using SELECT as above, or if it's needed immediately (which
it probably is) you can use last_insert_rowid. 

So, again in Tcl, your whole insertion path will probably look something
like:

db eval {INSERT OR IGNORE INTO tagNames(tagName) VALUES($tagName)}
if {[db changes]} {
  set tagId [db last_insert_rowid]
} else {
  set tagId [db one {SELECT tagId FROM tagNames WHERE tagName=$tagName}]
}
assert {$tagId!=""}
db eval {INSERT OR IGNORE INTO tags VALUES($movieId, $tagId)}

That could possibly be optimized further.  But you could give it a go as
a first pass and see how far it takes you.  It's not so complicated, and
in 7 lines (plus 1 sanity-check line) we've abstracted the ID<->text 
mapping.

Eric

-- 
Eric A. Smith

Carperpetuation (kar' pur pet u a shun), n.:
The act, when vacuuming, of running over a string at least a
dozen times, reaching over and picking it up, examining it, then
putting it back down to give the vacuum one more chance.
-- Rich Hall, "Sniglets"
___
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)

[Yuzem]

Petite Abeille-2 wrote:
> 
> Hey... sounds like IMdb :P
> 
Yes, I'm coding a  http://yuzem.blogspot.com/p/figuritas-screenshots.html
movie manager  that grabs the info from imdb.


Petite Abeille-2 wrote:
> 
> In any case, as mentioned by Michael Black, you might benefit greatly by
> normalizing your table and indexing the foreign key: 
> 
> http://dev.alt.textdrive.com/browser/IMDB/IMDB.ddl#L401
> 

I thought sqlite didn't handle foreign keys correctly, I am using triggers
to automatically delete tags when a movie gets deleted.
If I use a foreign key will it automatically remove the tag if the movie
gets deleted?
Anyway, to use integers in the "tags" table is very complicated because I
will have to assign the corresponding number to each tag that I insert and I
have to insert lots of keywords for every movie.
Does sqlite has any function to convert a text string into an unique number?

-- 
View this message in context: 
http://old.nabble.com/Speed-up-count%28distinct-col%29-tp30864622p30867411.html
Sent from the SQLite mailing list archive at Nabble.com.

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

[Eric Smith]

Yuzem wrote:

> > 
> > Test#1
> > create index tags_index on tags(tag);
> > You should have an index for any fields you query on like this.
> > 
> 
> Thanks Michael but I don't see any speed improvement:
> create index test on tags(tag);
> select count(distinct tag) from tags;
> 
> This is much faster:
> select count(*) from tags;
> 
> Am I doing something wrong?

As Michael mentioned, you might be getting killed by string comparisons.

I'm no SQL expert, so gurus are welcome to add to the following without
insulting me.

To beef up Michael's suggestion, try something like this:

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

(BTW, you should consider making movie_id "INTEGER PRIMARY KEY" and then
remove "unique(movie_id)" -- as long as that otherwise makes sense for 
your architecture.  For SQLite-specific reasons that will probably be
faster.)

CREATE TABLE tagNames(tagId INTEGER PRIMARY KEY, tagName TEXT UNIQUE);
CREATE INDEX tagNamesIdxtagName on tagNames(tagName);
CREATE TABLE tags(movie_id INTEGER,
  tagId INTEGER REFERENCES tagNames,
  unique(movie_id,tag));

CREATE INDEX tagsIdxTagId on tags(tagId);

Then see how fast it is to ask 
SELECT COUNT(*) FROM tagString;

Note: The index tagNamesIdxtagName is there because you'll probably want it 
to speed up insertions into 'tags'.

Note: If foreign key checking is on, be careful about deletes on table
"tagNames".  Without an index on tags(tagId) a delete of a single row on
tagNames implies a full table scan on tags (to make sure there are no
rows referencing it).  That bit me in the past.

Eric

--
Eric A. Smith

The concept is interesting and well-formed, but in order to earn 
better than a 'C,' the idea must be feasible.
-- A Yale University management professor in response to Fred Smith's paper
   proposing reliable overnight delivery service.
   (Smith went on to found Federal Express Corp.)
___
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)

[Yuzem]

Black, Michael (IS) wrote:
> 
> Test#1
> create index tags_index on tags(tag);
> You should have an index for any fields you query on like this.
> 

Thanks Michael but I don't see any speed improvement:
create index test on tags(tag);
select count(distinct tag) from tags;

This is much faster:
select count(*) from tags;

Am I doing something wrong?
-- 
View this message in context: 
http://old.nabble.com/Speed-up-count%28distinct-col%29-tp30864622p30867275.html
Sent from the SQLite mailing list archive at Nabble.com.

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

[Petite Abeille]

On Feb 7, 2011, at 4:26 PM, Yuzem wrote:

> 
> I have many movies by tag and many tables like "tags" (keywords, countries,
> languages, genres, etc..)

Hey... sounds like IMdb :P

> This solution implies one additional table and two triggers by each table.

To paraphrase:

Some people, when confronted with a problem, think “I know, I'll use triggers.” 
  Now they have two problems.

In any case, as mentioned by Michael Black, you might benefit greatly by 
normalizing your table and indexing the foreign key: 

http://dev.alt.textdrive.com/browser/IMDB/IMDB.ddl#L401

Don't bother with the triggers.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Regarding "Manual Control Of Query Plans"

[Richard Hipp]

On Mon, Feb 7, 2011 at 1:48 PM, Sven L  wrote:

>
> Yes, this is what I have read on the net too. But, tests show that without
> the sqlite_stat* tables, my queries are ridiculously slow (probably unwanted
> table scans etc).
> Real-life data... can't I simply take my real-life database and extract the
> data in sqlite_stat*?
>

You can take a sample database that is representative of what to expect for
most of your users, run ANALYZE, then do ".dump sqlite_stat1".  Save the
output.  Then when creating a new database for a user, after doing all your
CREATE TABLE and CREATE INDEX statements, but before adding actual content,
run ANALYZE there.  The ANALYZE command will take a millisecond or so since
there is no data.

Then run the "INSERT INTO sqlite_stat1" statements you saved in order to
fill the stat1 table with content from your "sample" database.


>
> Btw, this command produces nothing even though the table does contain
> several rows: ".dump sqlite_stat2"
>

Compile with SQLITE_ENABLE_STAT2 in order to get the stat2 information.
stat2 is a 10-sample histogram of the content of each index.  This enables
SQLite to make smarter choices about when to use index lookup and when to do
table scans.  For example:

 SELECT * FROM tab WHERE x=0;

The above might do a table scan if a majority of the tab.x values are 0.
But if only 1% of the tab.x values are 0, then it will use an index.


>
> Thanks
>
> > From: slav...@bigfraud.org
> > Date: Mon, 7 Feb 2011 16:44:00 +
> > To: sqlite-users@sqlite.org
> > Subject: Re: [sqlite] Regarding "Manual Control Of Query Plans"
> >
> >
> > On 7 Feb 2011, at 4:38pm, Sven L wrote:
> >
> > > So, with this in mind, it makes sense to precompute the sqlite_stat*
> tables. Right?
> >
> > Which you do by running ANALYZE, but since it needs real-life data to
> work on there's no point doing it until your customer has put some data in.
> I don't write this type of application any more, but I might put it in a
> maintenance routine -- some obscure menu option near the Config Preferences
> or something. Run it as part of your yearly maintenance procedure, after
> you've run 'PRAGMA integrity_check'.
> >
> > Simon.
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] Regarding "Manual Control Of Query Plans"

[Simon Slavin]

On 7 Feb 2011, at 6:48pm, Sven L wrote:

> Yes, this is what I have read on the net too. But, tests show that without 
> the sqlite_stat* tables, my queries are ridiculously slow (probably unwanted 
> table scans etc).
> Real-life data... can't I simply take my real-life database and extract the 
> data in sqlite_stat*?

I suppose you could take an existing database, do "ANALYZE", then "DELETE * 
FROM ..." for all TABLES.

> Btw, this command produces nothing even though the table does contain several 
> rows: ".dump sqlite_stat2"

Try "SELECT * FROM sqlite_stat1".  stat2 appears only if you have a compilation 
option turned on.

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

Re: [sqlite] Regarding "Manual Control Of Query Plans"

[Sven L]

Yes, this is what I have read on the net too. But, tests show that without the 
sqlite_stat* tables, my queries are ridiculously slow (probably unwanted table 
scans etc).
Real-life data... can't I simply take my real-life database and extract the 
data in sqlite_stat*?
 
Btw, this command produces nothing even though the table does contain several 
rows: ".dump sqlite_stat2"

Thanks
 
> From: slav...@bigfraud.org
> Date: Mon, 7 Feb 2011 16:44:00 +
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Regarding "Manual Control Of Query Plans"
> 
> 
> On 7 Feb 2011, at 4:38pm, Sven L wrote:
> 
> > So, with this in mind, it makes sense to precompute the sqlite_stat* 
> > tables. Right?
> 
> Which you do by running ANALYZE, but since it needs real-life data to work on 
> there's no point doing it until your customer has put some data in. I don't 
> write this type of application any more, but I might put it in a maintenance 
> routine -- some obscure menu option near the Config Preferences or something. 
> Run it as part of your yearly maintenance procedure, after you've run 'PRAGMA 
> integrity_check'.
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Regarding "Manual Control Of Query Plans"

[Simon Slavin]

On 7 Feb 2011, at 4:38pm, Sven L wrote:

> So, with this in mind, it makes sense to precompute the sqlite_stat* tables. 
> Right?

Which you do by running ANALYZE, but since it needs real-life data to work on 
there's no point doing it until your customer has put some data in.  I don't 
write this type of application any more, but I might put it in a maintenance 
routine -- some obscure menu option near the Config Preferences or something.  
Run it as part of your yearly maintenance procedure, after you've run 'PRAGMA 
integrity_check'.

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

Re: [sqlite] Regarding "Manual Control Of Query Plans"

[Sven L]

Sounds reasonable!
So, with this in mind, it makes sense to precompute the sqlite_stat* tables. 
Right?
 
> From: slav...@bigfraud.org
> Date: Mon, 7 Feb 2011 15:33:50 +
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Regarding "Manual Control Of Query Plans"
> 
> 
> On 7 Feb 2011, at 3:12pm, Sven L wrote:
> 
> > I've discovered that the ANALYZE command is extremely slow on my large 
> > databases. I read the documentation and it says that the command should be 
> > issued when the schema is changed "a lot". Hmm, when exactly could that be? 
> > Currently, I issue the command every time I have inserted ~1000 rows in my 
> > main table.
> 
> You should never need the ANALYZE command in a routine your users run. If 
> your first 1000 rows are representative of the 'chunkiness' of your data, do 
> it once, then there's no need to issue that command ever again.
> 
> The results are used only to help the query analyser choose which order to 
> consider columns in. In order to do this it's useful to know things like "The 
> column called 'currency' is almost always the same value, whereas the column 
> called 'totalAmount' changes a lot.". That's what that command does. If you 
> radically changed your data by, for example, moving from a national to an 
> international service then it might be worth running ANALYZE again once you 
> had a bunch of invoices in other currencies in, but only then, and only once.
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Regarding "Manual Control Of Query Plans"

[Simon Slavin]

On 7 Feb 2011, at 3:12pm, Sven L wrote:

> I've discovered that the ANALYZE command is extremely slow on my large 
> databases. I read the documentation and it says that the command should be 
> issued when the schema is changed "a lot". Hmm, when exactly could that be? 
> Currently, I issue the command every time I have inserted ~1000 rows in my 
> main table.

You should never need the ANALYZE command in a routine your users run.  If your 
first 1000 rows are representative of the 'chunkiness' of your data, do it 
once, then there's no need to issue that command ever again.

The results are used only to help the query analyser choose which order to 
consider columns in.  In order to do this it's useful to know things like "The 
column called 'currency' is almost always the same value, whereas the column 
called 'totalAmount' changes a lot.".  That's what that command does.  If you 
radically changed your data by, for example, moving from a national to an 
international service then it might be worth running ANALYZE again once you had 
a bunch of invoices in other currencies in, but only then, and only once.

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

[Black, Michael (IS)]

Test#1
create index tags_index on tags(tag);
You should have an index for any fields you query on like this.

Test#2
Normalize your tags into a separate table so you then store the rowid in your 
tags table instead of the string.  Your compares will be notably faster using 
integers rather than strings for more fancy queries.

Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Yuzem [naujnit...@gmail.com]
Sent: Monday, February 07, 2011 9:26 AM
To: sqlite-users@sqlite.org
Subject: EXT :[sqlite]  Speed up count(distinct col)

Hi.
I have this tables:
CREATE TABLE movies(movie_id INTEGER, title TEXT, unique(movie_id));
CREATE TABLE tags(movie_id INTEGER, tag TEXT, unique(movie_id,tag));

I have many movies by tag and many tables like "tags" (keywords, countries,
languages, genres, etc..)
I can count different movies very fast with:
SELECT count(*) FROM movies;

But the other tables are much slower depending on the size of the table:
SELECT count(distinct tag) FROM tags;

My solution is to create an additional table "count_tags" and then every
time a tag is added to table "tags" a trigger adds the tag to "count_tags",
I need also another trigger to remove the tag, then I can do:
SELECT count(*) FROM count_tags;

This solution implies one additional table and two triggers by each table.

I wanted to know if there is a simpler solution, maybe by using indexes.
Thanks in advance.
--
View this message in context: 
http://old.nabble.com/Speed-up-count%28distinct-col%29-tp30864622p30864622.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] Regarding "Manual Control Of Query Plans"

[Puneet Kishor]

Sven L wrote:
> I've discovered that the ANALYZE command is extremely slow on my
> large databases. I read the documentation and it says that the
> command should be issued when the schema is changed "a lot". Hmm,
> when exactly could that be? Currently, I issue the command every time
> I have inserted ~1000 rows in my main table.


My guess is that schema would change when you change the schema. Adding 
a 1000 rows, or even a billion rows, is not changing the schema. If all 
you are doing is adding the rows, your indexes will be updated 
automatically, so you are probably wasting time re-running ANALYZE.

..


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

[sqlite] Speed up count(distinct col)

[Yuzem]

Hi.
I have this tables:
CREATE TABLE movies(movie_id INTEGER, title TEXT, unique(movie_id));
CREATE TABLE tags(movie_id INTEGER, tag TEXT, unique(movie_id,tag));

I have many movies by tag and many tables like "tags" (keywords, countries,
languages, genres, etc..)
I can count different movies very fast with:
SELECT count(*) FROM movies;

But the other tables are much slower depending on the size of the table:
SELECT count(distinct tag) FROM tags;

My solution is to create an additional table "count_tags" and then every
time a tag is added to table "tags" a trigger adds the tag to "count_tags",
I need also another trigger to remove the tag, then I can do:
SELECT count(*) FROM count_tags;

This solution implies one additional table and two triggers by each table.

I wanted to know if there is a simpler solution, maybe by using indexes.
Thanks in advance.
-- 
View this message in context: 
http://old.nabble.com/Speed-up-count%28distinct-col%29-tp30864622p30864622.html
Sent from the SQLite mailing list archive at Nabble.com.

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

[sqlite] Regarding "Manual Control Of Query Plans"

[Sven L]

I've discovered that the ANALYZE command is extremely slow on my large 
databases. I read the documentation and it says that the command should be 
issued when the schema is changed "a lot". Hmm, when exactly could that be? 
Currently, I issue the command every time I have inserted ~1000 rows in my main 
table.
 
Now I'm thinking of using precomputed sqlite_stat1 and sqlite_stat2 tables, 
since the relationship between my tables is pretty much (say 95%) constant in 
all database instances. I'm wondering the same as John did here: 
http://osdir.com/ml/sqlite-users/2009-02/msg00298.html

1) Will this work?
2) Is it "dangerous"? e.g. could this completely confuse the query optimiser, 
or is it the case that as long as the hard coded values are "realistic" it 
doesn't matter that they don't reflect the reality of the table (which is what 
the docs imply)?
2) Has anyone experience of trying this?

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

Re: [sqlite] Trigger to filter out characters

[Simon Slavin]

On 7 Feb 2011, at 1:06pm, venkat easwar wrote:

> As far as I am aware trim will remove the characters only in ends, not in the 
> middle. Assume an input like "12venkatpw", this cannot be trimmed. Or am I 
> wrong 
> somewhere. Guess NO.

Venkat, you are quite right.  Sorry about that.

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

Re: [sqlite] EXT :Re: Sqlite3.h file for X64 Windows

[Black, Michael (IS)]

Unless you have DLL as a requirement I agree with Teg...

Just download the amalgamation and include sqlite3.c and sqlite3.h in your 
program -- that's what I do.  I try to avoid all non-windows DLLs...they cause 
too much headache.

Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Teg [t...@djii.com]
Sent: Monday, February 07, 2011 7:41 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Sqlite3.h file for X64 Windows

I use the same header for both. It's so easy to build, I'm unclear why
you went down this path as opposed to just building the lib from
source.


Monday, February 7, 2011, 7:57:53 AM, you wrote:

IT> Dileep  wrote:
>> Hi,
>> I could not found Precompiled Binaries For Windows For X64 windows in 
>> http://www.sqlite.org/download.html
>>
>> I got 64bit version of SQLiteExpert From that I got Sqlite3.dll, I generated 
>> lib and Def files
>>
>> But I am not able to get sqlite3.h header file for X64 windows version of 
>> sqlite3.

IT> I don't see any reason why the same sqlite3.h wouldn't work for
IT> both Win32 and Win64. Have you tried it, and encountered a particular 
problem with it?



--
Best regards,
 Tegmailto:t...@djii.com

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

Re: [sqlite] Sqlite3.h file for X64 Windows

[Teg]

I use the same header for both. It's so easy to build, I'm unclear why
you went down this path as opposed to just building the lib from
source.


Monday, February 7, 2011, 7:57:53 AM, you wrote:

IT> Dileep  wrote:
>> Hi,
>> I could not found Precompiled Binaries For Windows For X64 windows in 
>> http://www.sqlite.org/download.html
>> 
>> I got 64bit version of SQLiteExpert From that I got Sqlite3.dll, I generated 
>> lib and Def files
>> 
>> But I am not able to get sqlite3.h header file for X64 windows version of 
>> sqlite3.

IT> I don't see any reason why the same sqlite3.h wouldn't work for
IT> both Win32 and Win64. Have you tried it, and encountered a particular 
problem with it?



-- 
Best regards,
 Tegmailto:t...@djii.com

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

Re: [sqlite] Blob in the callback of sqlite3_exec

[Igor Tandetnik]

Ray  wrote:
> What's the actual type of blob in the callback of sqlite3_exec? Is it
> string or remains binary?

Bytes are reported as-is, with an added zero terminator. But since there's no 
length indicator, you'll have trouble with embedded NULs.

Drop sqlite3_exec, use sqlite3_prepare / sqlite3_step / sqlite3_column_bytes / 
sqlite3_column_blob.
-- 
Igor Tandetnik

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

Re: [sqlite] Trigger to filter out characters

[venkat easwar]

Harish,

Consider doing things outside sqlite. Definitely you should be using C API or 
php API or some other for making query to DB. Why don't you think of doing 
things outside sqlite
 VENKAT
Bug the Bugs




From: Harish CS 
To: sqlite-users@sqlite.org
Sent: Mon, February 7, 2011 6:08:29 PM
Subject: Re: [sqlite] Trigger to filter out characters


Simon,

Thanks. Trim(X,Y) is useful but I need to remove anything other than '0' - '
'9' and a few more characters. 
180+ characters need to be removed (filtered out).
So I will need to call Trim() so many times.
Since we cannot write loops I wonder how I can call it many times.

-Harish

Simon Slavin-3 wrote:
> 
> 
> On 7 Feb 2011, at 8:38am, Harish CS wrote:
> 
>> Table T has two varchar columns A and B. UI allows entering values into
>> column A only.
>> On insert/update of records, I need to take out the value of A, filter
>> out
>> anything other than '0' to '9', '*', '#', '+' 'p', 'w' characters and
>> copy
>> it to column B. Is it possible to write a trigger to achieve this? If yes
>> please give me a sample. (Also, if column B is in another table, is it
>> possible?)
> 
> I don't think you need a trigger.  You might find the 'trim(X,Y)' function
> from
> 
> http://www.sqlite.org/lang_corefunc.html
> 
> helpful.  You'll have to list all characters  you /don't/ want left in the
> string.
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://old.nabble.com/Trigger-to-filter-out-characters-tp30861522p30863194.html
Sent from the SQLite mailing list archive at Nabble.com.

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



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

Re: [sqlite] Trigger to filter out characters

[venkat easwar]

Simon,

As far as I am aware trim will remove the characters only in ends, not in the 
middle. Assume an input like "12venkatpw", this cannot be trimmed. Or am I 
wrong 
somewhere. Guess NO.

Thanks
 VENKAT
Bug the Bugs.




From: Simon Slavin 
To: General Discussion of SQLite Database 
Sent: Mon, February 7, 2011 6:19:04 PM
Subject: Re: [sqlite] Trigger to filter out characters


On 7 Feb 2011, at 12:38pm, Harish CS wrote:

> Thanks. Trim(X,Y) is useful but I need to remove anything other than '0' - '
> '9' and a few more characters. 
> 180+ characters need to be removed (filtered out).
> So I will need to call Trim() so many times.
> Since we cannot write loops I wonder how I can call it many times.

No, you just call it once and list all those characters in the second parameter:

UPDATE contacts SET phoneNumber to TRIM(rawPhoneNumber, 'abcdefghi ... 
xyz!@£$%^&()_-={}[]:"|;''\<>?,./')

Note that as above to put an apostrophe inside an SQLite string you double-it.

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



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

Re: [sqlite] Trigger to filter out characters

[Igor Tandetnik]

Simon Slavin  wrote:
> On 7 Feb 2011, at 8:38am, Harish CS wrote:
> 
>> Table T has two varchar columns A and B. UI allows entering values into
>> column A only.
>> On insert/update of records, I need to take out the value of A, filter out
>> anything other than '0' to '9', '*', '#', '+' 'p', 'w' characters and copy
>> it to column B. Is it possible to write a trigger to achieve this? If yes
>> please give me a sample. (Also, if column B is in another table, is it
>> possible?)
> 
> I don't think you need a trigger.  You might find the 'trim(X,Y)' function

trim just removes leading and trailing characters. It won't help with, say, 
'1x2'. The closest you could come using built-in functions is something like

replace(replace(...(replace(new.A, 'a', ''), 'b', ''), 'c', '')...)

but that's impractical.

Personally, I'd do the transformation in the application code. Barring that, a 
custom function (like strfilter mentioned in this thread) is in order.
-- 
Igor Tandetnik

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

Re: [sqlite] Sqlite3.h file for X64 Windows

[Igor Tandetnik]

Dileep  wrote:
> Hi,
> I could not found Precompiled Binaries For Windows For X64 windows in 
> http://www.sqlite.org/download.html
> 
> I got 64bit version of SQLiteExpert From that I got Sqlite3.dll, I generated 
> lib and Def files
> 
> But I am not able to get sqlite3.h header file for X64 windows version of 
> sqlite3.

I don't see any reason why the same sqlite3.h wouldn't work for both Win32 and 
Win64. Have you tried it, and encountered a particular problem with it?
-- 
Igor Tandetnik

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

Re: [sqlite] Trigger to filter out characters

[Simon Slavin]

On 7 Feb 2011, at 12:38pm, Harish CS wrote:

> Thanks. Trim(X,Y) is useful but I need to remove anything other than '0' - '
> '9' and a few more characters. 
> 180+ characters need to be removed (filtered out).
> So I will need to call Trim() so many times.
> Since we cannot write loops I wonder how I can call it many times.

No, you just call it once and list all those characters in the second parameter:

UPDATE contacts SET phoneNumber to TRIM(rawPhoneNumber, 'abcdefghi ... 
xyz!@£$%^&()_-={}[]:"|;''\<>?,./')

Note that as above to put an apostrophe inside an SQLite string you double-it.

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

Re: [sqlite] Trigger to filter out characters

[venkat easwar]

Harish, neither trigger nor trim will be useful for you directly as such. I 
suggest writing a custom sqlite function to perform the operation. You try 
writing custom functions, if I figure out any idea, I will get back to you.

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

VENKAT
Bug the Bugs




From: Harish CS 
To: sqlite-users@sqlite.org
Sent: Mon, February 7, 2011 6:01:31 PM
Subject: Re: [sqlite] Trigger to filter out characters


Venkat,

Thanks. 
But this is not filtering characters.
For example, if user enters '12ab34', it should remove 'ab' and enter
'1234'.

Thanks,
Harish


venkat easwar wrote:
> 
> Well. I thought it should be an easy deal for you hence left that part.
> Now 
> giving the conditioned trigger assuming the following condition. I leave
> the 
> testing part to your concern.
> 
>>I need to take out the value of A, filter out
>> anything other than '0' to '9', '*', '#', '+' 'p', 'w'
> 
> create trigger if not exists  after insert on  when 
> new.A IN ('0','1','2','3','4','5','6','7','8','9','*','#','+','p','w')
> beginupdate  B=new.A;
> end;
> 
> Only if the values inside the braces are inserted it will be copied to
> column B.
> 
> 
> VENKAT
> 
> Bug the Bugs
> 
> 
> 
> From: Harish CS 
> To: sqlite-users@sqlite.org
> Sent: Mon, February 7, 2011 2:59:50 PM
> Subject: Re: [sqlite] Trigger to filter out characters
> 
> 
> Hi Venkat,
> 
> Could you please show me how to write the condition (to filter out
> characters)?
> 
> Thanks,
> Harish
> 
> 
> 
> venkat easwar wrote:
>> 
>> Hi Harish,
>> 
>> Yes it is possible. Look below for solution.
>> 
>> create trigger if not exists  after insert on 
>> when 
>> new.A=
>> begin
>> update  B=new.A;
>> end;
>> 
>> 
>> There is nothing tricky or hard for inserting into another table, same
>> things 
>> should go.
>> 
>> VENKAT
>> Bug the Bugs
>> 
>> 
>> 
>> 
>> 
>> From: Harish CS 
>> To: sqlite-users@sqlite.org
>> Sent: Mon, February 7, 2011 2:08:16 PM
>> Subject: [sqlite]  Trigger to filter out characters
>> 
>> 
>> Table T has two varchar columns A and B. UI allows entering values into
>> column A only.
>> On insert/update of records, I need to take out the value of A, filter
>> out
>> anything other than '0' to '9', '*', '#', '+' 'p', 'w' characters and
>> copy
>> it to column B. Is it possible to write a trigger to achieve this? If yes
>> please give me a sample. (Also, if column B is in another table, is it
>> possible?)
>> 
>> Thanks
>> -Harish
>> 
>> -- 
>> View this message in context: 
>> 
> 
http://old.nabble.com/Trigger-to-filter-out-characters-tp30861522p30861522.html
>> Sent from the SQLite mailing list archive at Nabble.com.
>> 
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> 
>> 
>> 
>>  
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> 
>> 
> 
> -- 
> View this message in context: 
> 
http://old.nabble.com/Trigger-to-filter-out-characters-tp30861522p30861878.html
> Sent from the SQLite mailing list archive at Nabble.com.
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 
> 
>  
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://old.nabble.com/Trigger-to-filter-out-characters-tp30861522p30863148.html
Sent from the SQLite mailing list archive at Nabble.com.

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



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

Re: [sqlite] Trigger to filter out characters

[Jean-Christophe Deschamps]

>Could you please show me how to write the condition (to filter out
>characters)?

Use the strfilter function found in extension-functions.c downloadable 
from http://www.sqlite.org/contrib/

Once built and loaded, you can use a trigger similar to

create trigger if not exists trFilterAB after insert on mytable
begin
update mytable set columnB = strfilter(new.columnA, '0123456789*#+pw') 
where rowid = new.rowid;
end;




@venkat,

I guess the OP needs to filter out characters not in the prescribed 
set, not sort out if the data is a 1-character string in this set. 

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

[sqlite] Sqlite3.h file for X64 Windows

[Dileep]

Hi,
I could not found Precompiled Binaries For Windows For X64 windows in 
http://www.sqlite.org/download.html  

I got 64bit version of SQLiteExpert From that I got Sqlite3.dll, I generated 
lib and Def files 

But I am not able to get sqlite3.h header file for X64 windows version of 
sqlite3. 

Please give link or information about getting sqlite3.h for X64 Windows 

Expecting Your reply,
Thanks in advance,

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

[sqlite] Blob in the callback of sqlite3_exec

[Ray]

Hi guys,

What's the actual type of blob in the callback of sqlite3_exec? Is it
string or remains binary? Since when I query the database to get all
rows and columns the blob values are not same as what I get from other
SQLite tools.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sub-Select reports only row one value?

[J Hennick]

Igor Tandetnik was right on this.  Thank you.

> Scalar Subqueries
>
> A SELECT statement enclosed in parentheses may appear as a scalar 
> quantity. A SELECT used as a scalar quantity must return a result set 
> with a single column. The result of the expression is the value of the 
> only column in the first row returned by the SELECT statement. If the 
> SELECT yields more than one result row, all rows after the first are 
> ignored. If the SELECT yields no rows, then the value of the 
> expression is NULL.

I solved my problem with a JOIN.

JOIN

  (SELECT  class,  COUNT(term) AS cnt
  FROM
   (SELECT DISTINCT class, term
FROM registrations
GROUP BY class, term)
  GROUP BY class)

USING (class)

rather than the original sub-SELECT:

(SELECT COUNT(term)
  FROM (SELECT DISTINCT class, term
FROM registrations AS r2
GROUP BY r2.class, r2.term
ORDER BY r2.class, r2.term)
  GROUP BY class) AS cnt

So from my original:
> I don't know if it is (in order of probability) 1. My ignorance, 2.
> SQLite bug, or 3. SQL problem.
I now know it was #1.  And this is closed.

On 2/5/2011 11:12 AM, Igor Tandetnik wrote:
> Jeff Hennick  wrote:
>> I am having problems with Sub-Select apparently working on one row
>> rather than the whole table.
> When syntactically used as an expression (e.g. in SELECT clause), a subselect 
> produces a single value. When used as a table (e.g. in FROM clause), it may 
> produce multiple rows.

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

Re: [sqlite] Trigger to filter out characters

[Harish CS]

Simon,

Thanks. Trim(X,Y) is useful but I need to remove anything other than '0' - '
'9' and a few more characters. 
180+ characters need to be removed (filtered out).
So I will need to call Trim() so many times.
Since we cannot write loops I wonder how I can call it many times.

-Harish

Simon Slavin-3 wrote:
> 
> 
> On 7 Feb 2011, at 8:38am, Harish CS wrote:
> 
>> Table T has two varchar columns A and B. UI allows entering values into
>> column A only.
>> On insert/update of records, I need to take out the value of A, filter
>> out
>> anything other than '0' to '9', '*', '#', '+' 'p', 'w' characters and
>> copy
>> it to column B. Is it possible to write a trigger to achieve this? If yes
>> please give me a sample. (Also, if column B is in another table, is it
>> possible?)
> 
> I don't think you need a trigger.  You might find the 'trim(X,Y)' function
> from
> 
> http://www.sqlite.org/lang_corefunc.html
> 
> helpful.  You'll have to list all characters  you /don't/ want left in the
> string.
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://old.nabble.com/Trigger-to-filter-out-characters-tp30861522p30863194.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] Trigger to filter out characters

[Harish CS]

Venkat,

Thanks. 
But this is not filtering characters.
For example, if user enters '12ab34', it should remove 'ab' and enter
'1234'.

Thanks,
Harish


venkat easwar wrote:
> 
> Well. I thought it should be an easy deal for you hence left that part.
> Now 
> giving the conditioned trigger assuming the following condition. I leave
> the 
> testing part to your concern.
> 
>>I need to take out the value of A, filter out
>> anything other than '0' to '9', '*', '#', '+' 'p', 'w'
> 
> create trigger if not exists  after insert on  when 
> new.A IN ('0','1','2','3','4','5','6','7','8','9','*','#','+','p','w')
> beginupdate  B=new.A;
> end;
> 
> Only if the values inside the braces are inserted it will be copied to
> column B.
> 
> 
> VENKAT
> 
> Bug the Bugs
> 
> 
> 
> From: Harish CS 
> To: sqlite-users@sqlite.org
> Sent: Mon, February 7, 2011 2:59:50 PM
> Subject: Re: [sqlite] Trigger to filter out characters
> 
> 
> Hi Venkat,
> 
> Could you please show me how to write the condition (to filter out
> characters)?
> 
> Thanks,
> Harish
> 
> 
> 
> venkat easwar wrote:
>> 
>> Hi Harish,
>> 
>> Yes it is possible. Look below for solution.
>> 
>> create trigger if not exists  after insert on 
>> when 
>> new.A=
>> begin
>> update  B=new.A;
>> end;
>> 
>> 
>> There is nothing tricky or hard for inserting into another table, same
>> things 
>> should go.
>> 
>> VENKAT
>> Bug the Bugs
>> 
>> 
>> 
>> 
>> 
>> From: Harish CS 
>> To: sqlite-users@sqlite.org
>> Sent: Mon, February 7, 2011 2:08:16 PM
>> Subject: [sqlite]  Trigger to filter out characters
>> 
>> 
>> Table T has two varchar columns A and B. UI allows entering values into
>> column A only.
>> On insert/update of records, I need to take out the value of A, filter
>> out
>> anything other than '0' to '9', '*', '#', '+' 'p', 'w' characters and
>> copy
>> it to column B. Is it possible to write a trigger to achieve this? If yes
>> please give me a sample. (Also, if column B is in another table, is it
>> possible?)
>> 
>> Thanks
>> -Harish
>> 
>> -- 
>> View this message in context: 
>> 
> http://old.nabble.com/Trigger-to-filter-out-characters-tp30861522p30861522.html
>> Sent from the SQLite mailing list archive at Nabble.com.
>> 
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> 
>> 
>> 
>>  
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> 
>> 
> 
> -- 
> View this message in context: 
> http://old.nabble.com/Trigger-to-filter-out-characters-tp30861522p30861878.html
> Sent from the SQLite mailing list archive at Nabble.com.
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 
> 
>   
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://old.nabble.com/Trigger-to-filter-out-characters-tp30861522p30863148.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] Trigger to filter out characters

[Simon Slavin]

On 7 Feb 2011, at 8:38am, Harish CS wrote:

> Table T has two varchar columns A and B. UI allows entering values into
> column A only.
> On insert/update of records, I need to take out the value of A, filter out
> anything other than '0' to '9', '*', '#', '+' 'p', 'w' characters and copy
> it to column B. Is it possible to write a trigger to achieve this? If yes
> please give me a sample. (Also, if column B is in another table, is it
> possible?)

I don't think you need a trigger.  You might find the 'trim(X,Y)' function from

http://www.sqlite.org/lang_corefunc.html

helpful.  You'll have to list all characters  you /don't/ want left in the 
string.

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

Re: [sqlite] Trigger to filter out characters

[venkat easwar]

Well. This is the one I did not expect. Update without condition is updating 
all 
the rows. Hence make it with conditioned update :) :)

create trigger if not exists  after insert on  when 
new.A IN ('0','1','2','3','4','5','6','7','8','9','*','#','+','p','w')
begin
update  set B=new.A where A=new.A;
end;
 
If you find more bugs, well it is good, we will get them bugged.
VENKAT
Bug the Bugs




From: venkat easwar 
To: General Discussion of SQLite Database 
Sent: Mon, February 7, 2011 5:19:51 PM
Subject: Re: [sqlite] Trigger to filter out characters

OOPS.. a mistake.. find the corrected things below.

create trigger if not exists  after insert on  when 
new.A IN ('0','1','2','3','4','5','6','7','8','9','*','#','+','p','w')
beginupdate  set B=new.A;
end;

Missed set in my last mail. It would have given you a syntax error.
VENKAT
Bug the Bugs




From: venkat easwar 
To: General Discussion of SQLite Database 
Sent: Mon, February 7, 2011 4:41:19 PM
Subject: Re: [sqlite] Trigger to filter out characters


Well. I thought it should be an easy deal for you hence left that part. Now 
giving the conditioned trigger assuming the following condition. I leave the 
testing part to your concern.

>I need to take out the value of A, filter out
> anything other than '0' to '9', '*', '#', '+' 'p', 'w'

create trigger if not exists  after insert on  when 
new.A IN ('0','1','2','3','4','5','6','7','8','9','*','#','+','p','w')
beginupdate  B=new.A;
end;

Only if the values inside the braces are inserted it will be copied to column B.


VENKAT

Bug the Bugs



From: Harish CS 
To: sqlite-users@sqlite.org
Sent: Mon, February 7, 2011 2:59:50 PM
Subject: Re: [sqlite] Trigger to filter out characters


Hi Venkat,

Could you please show me how to write the condition (to filter out
characters)?

Thanks,
Harish



venkat easwar wrote:
> 
> Hi Harish,
> 
> Yes it is possible. Look below for solution.
> 
> create trigger if not exists  after insert on  when 
> new.A=
> begin
> update  B=new.A;
> end;
> 
> 
> There is nothing tricky or hard for inserting into another table, same
> things 
> should go.
> 
> VENKAT
> Bug the Bugs
> 
> 
> 
> 
> 
> From: Harish CS 
> To: sqlite-users@sqlite.org
> Sent: Mon, February 7, 2011 2:08:16 PM
> Subject: [sqlite]  Trigger to filter out characters
> 
> 
> Table T has two varchar columns A and B. UI allows entering values into
> column A only.
> On insert/update of records, I need to take out the value of A, filter out
> anything other than '0' to '9', '*', '#', '+' 'p', 'w' characters and copy
> it to column B. Is it possible to write a trigger to achieve this? If yes
> please give me a sample. (Also, if column B is in another table, is it
> possible?)
> 
> Thanks
> -Harish
> 
> -- 
> View this message in context: 
> 
http://old.nabble.com/Trigger-to-filter-out-characters-tp30861522p30861522.html
>  Sent from the SQLite mailing list archive at Nabble.com.
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 
> 
>  
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this  message in context: 
http://old.nabble.com/Trigger-to-filter-out-characters-tp30861522p30861878.html
Sent from the SQLite mailing list archive at Nabble.com.

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


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



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

Re: [sqlite] Trigger to filter out characters

[venkat easwar]

OOPS.. a mistake.. find the corrected things below.

create trigger if not exists  after insert on  when 
new.A IN ('0','1','2','3','4','5','6','7','8','9','*','#','+','p','w')
beginupdate  set B=new.A;
end;

Missed set in my last mail. It would have given you a syntax error.
VENKAT
Bug the Bugs




From: venkat easwar 
To: General Discussion of SQLite Database 
Sent: Mon, February 7, 2011 4:41:19 PM
Subject: Re: [sqlite] Trigger to filter out characters


Well. I thought it should be an easy deal for you hence left that part. Now 
giving the conditioned trigger assuming the following condition. I leave the 
testing part to your concern.

>I need to take out the value of A, filter out
> anything other than '0' to '9', '*', '#', '+' 'p', 'w'

create trigger if not exists  after insert on  when 
new.A IN ('0','1','2','3','4','5','6','7','8','9','*','#','+','p','w')
beginupdate  B=new.A;
end;

Only if the values inside the braces are inserted it will be copied to column B.


VENKAT

Bug the Bugs



From: Harish CS 
To: sqlite-users@sqlite.org
Sent: Mon, February 7, 2011 2:59:50 PM
Subject: Re: [sqlite] Trigger to filter out characters


Hi Venkat,

Could you please show me how to write the condition (to filter out
characters)?

Thanks,
Harish



venkat easwar wrote:
> 
> Hi Harish,
> 
> Yes it is possible. Look below for solution.
> 
> create trigger if not exists  after insert on  when 
> new.A=
> begin
> update  B=new.A;
> end;
> 
> 
> There is nothing tricky or hard for inserting into another table, same
> things 
> should go.
> 
> VENKAT
> Bug the Bugs
> 
> 
> 
> 
> 
> From: Harish CS 
> To: sqlite-users@sqlite.org
> Sent: Mon, February 7, 2011 2:08:16 PM
> Subject: [sqlite]  Trigger to filter out characters
> 
> 
> Table T has two varchar columns A and B. UI allows entering values into
> column A only.
> On insert/update of records, I need to take out the value of A, filter out
> anything other than '0' to '9', '*', '#', '+' 'p', 'w' characters and copy
> it to column B. Is it possible to write a trigger to achieve this? If yes
> please give me a sample. (Also, if column B is in another table, is it
> possible?)
> 
> Thanks
> -Harish
> 
> -- 
> View this message in context: 
> 
http://old.nabble.com/Trigger-to-filter-out-characters-tp30861522p30861522.html
>  Sent from the SQLite mailing list archive at Nabble.com.
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 
> 
>  
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this  message in context: 
http://old.nabble.com/Trigger-to-filter-out-characters-tp30861522p30861878.html
Sent from the SQLite mailing list archive at Nabble.com.

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


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

Re: [sqlite] Trigger to filter out characters

[venkat easwar]

Well. I thought it should be an easy deal for you hence left that part. Now 
giving the conditioned trigger assuming the following condition. I leave the 
testing part to your concern.

>I need to take out the value of A, filter out
> anything other than '0' to '9', '*', '#', '+' 'p', 'w'

create trigger if not exists  after insert on  when 
new.A IN ('0','1','2','3','4','5','6','7','8','9','*','#','+','p','w')
beginupdate  B=new.A;
end;

Only if the values inside the braces are inserted it will be copied to column B.


VENKAT

Bug the Bugs



From: Harish CS 
To: sqlite-users@sqlite.org
Sent: Mon, February 7, 2011 2:59:50 PM
Subject: Re: [sqlite] Trigger to filter out characters


Hi Venkat,

Could you please show me how to write the condition (to filter out
characters)?

Thanks,
Harish



venkat easwar wrote:
> 
> Hi Harish,
> 
> Yes it is possible. Look below for solution.
> 
> create trigger if not exists  after insert on  when 
> new.A=
> begin
> update  B=new.A;
> end;
> 
> 
> There is nothing tricky or hard for inserting into another table, same
> things 
> should go.
> 
> VENKAT
> Bug the Bugs
> 
> 
> 
> 
> 
> From: Harish CS 
> To: sqlite-users@sqlite.org
> Sent: Mon, February 7, 2011 2:08:16 PM
> Subject: [sqlite]  Trigger to filter out characters
> 
> 
> Table T has two varchar columns A and B. UI allows entering values into
> column A only.
> On insert/update of records, I need to take out the value of A, filter out
> anything other than '0' to '9', '*', '#', '+' 'p', 'w' characters and copy
> it to column B. Is it possible to write a trigger to achieve this? If yes
> please give me a sample. (Also, if column B is in another table, is it
> possible?)
> 
> Thanks
> -Harish
> 
> -- 
> View this message in context: 
> 
http://old.nabble.com/Trigger-to-filter-out-characters-tp30861522p30861522.html
> Sent from the SQLite mailing list archive at Nabble.com.
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 
> 
>  
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://old.nabble.com/Trigger-to-filter-out-characters-tp30861522p30861878.html
Sent from the SQLite mailing list archive at Nabble.com.

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



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

Re: [sqlite] Trigger to filter out characters

[Harish CS]

Hi Venkat,

Could you please show me how to write the condition (to filter out
characters)?

Thanks,
Harish



venkat easwar wrote:
> 
> Hi Harish,
> 
> Yes it is possible. Look below for solution.
> 
> create trigger if not exists  after insert on  when 
> new.A=
> begin
> update  B=new.A;
> end;
> 
> 
> There is nothing tricky or hard for inserting into another table, same
> things 
> should go.
> 
> VENKAT
> Bug the Bugs
> 
> 
> 
> 
> 
> From: Harish CS 
> To: sqlite-users@sqlite.org
> Sent: Mon, February 7, 2011 2:08:16 PM
> Subject: [sqlite]  Trigger to filter out characters
> 
> 
> Table T has two varchar columns A and B. UI allows entering values into
> column A only.
> On insert/update of records, I need to take out the value of A, filter out
> anything other than '0' to '9', '*', '#', '+' 'p', 'w' characters and copy
> it to column B. Is it possible to write a trigger to achieve this? If yes
> please give me a sample. (Also, if column B is in another table, is it
> possible?)
> 
> Thanks
> -Harish
> 
> -- 
> View this message in context: 
> http://old.nabble.com/Trigger-to-filter-out-characters-tp30861522p30861522.html
> Sent from the SQLite mailing list archive at Nabble.com.
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 
> 
>   
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://old.nabble.com/Trigger-to-filter-out-characters-tp30861522p30861878.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] Trigger to filter out characters

[venkat easwar]

Hi Harish,

Yes it is possible. Look below for solution.

create trigger if not exists  after insert on  when 
new.A=
begin
update  B=new.A;
end;


There is nothing tricky or hard for inserting into another table, same things 
should go.

VENKAT
Bug the Bugs





From: Harish CS 
To: sqlite-users@sqlite.org
Sent: Mon, February 7, 2011 2:08:16 PM
Subject: [sqlite]  Trigger to filter out characters


Table T has two varchar columns A and B. UI allows entering values into
column A only.
On insert/update of records, I need to take out the value of A, filter out
anything other than '0' to '9', '*', '#', '+' 'p', 'w' characters and copy
it to column B. Is it possible to write a trigger to achieve this? If yes
please give me a sample. (Also, if column B is in another table, is it
possible?)

Thanks
-Harish

-- 
View this message in context: 
http://old.nabble.com/Trigger-to-filter-out-characters-tp30861522p30861522.html
Sent from the SQLite mailing list archive at Nabble.com.

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



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

[sqlite] Trigger to filter out characters

[Harish CS]

Table T has two varchar columns A and B. UI allows entering values into
column A only.
On insert/update of records, I need to take out the value of A, filter out
anything other than '0' to '9', '*', '#', '+' 'p', 'w' characters and copy
it to column B. Is it possible to write a trigger to achieve this? If yes
please give me a sample. (Also, if column B is in another table, is it
possible?)

Thanks
-Harish

-- 
View this message in context: 
http://old.nabble.com/Trigger-to-filter-out-characters-tp30861522p30861522.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] LENGTH on a BLOB field stops at NUL byte

[Philip Graham Willoughby]

>SELECT length(CAST ("Data" AS BLOB)) FROM "message_revision";

What about:

UPDATE "message_revision" SET "Data" = CAST ("Data" AS BLOB);

As a one-time command to correct the table. There is also the option of using 
triggers to ensure future "Data" values are kept as BLOB if the INSERT code 
cannot be corrected conveniently:

CREATE TRIGGER trigName AFTER INSERT ON "message_revision" WHEN 
TYPEOF("Data")!='blob' BEGIN UPDATE "message_revision" SET "Data" = CAST 
("Data" AS BLOB) WHERE "message_revision".rowid = NEW.rowid; END;

CREATE TRIGGER trigName2 AFTER UPDATE OF "Data" ON "message_revision" WHEN 
TYPEOF("Data")!='blob' BEGIN UPDATE "message_revision" SET "Data" = CAST 
("Data" AS BLOB) WHERE "message_revision".rowid = NEW.rowid; END;
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users