Re: [sqlite] "override" table?

[Jonas Sandman]

Don't you simply want this?

sqlite> create table real (id integer primary key, value integer);
sqlite> create table shadow (id integer primary key, value integer);
sqlite> insert into real (id, value) VALUES (1, 1);
sqlite> insert into real (id, value) VALUES (2, 2);
sqlite> insert into real (id, value) VALUES (3, 3);
sqlite> insert into real (id, value) VALUES (4, 4);
sqlite> insert into shadow (id, value) VALUES (2, 22);

sqlite> select ifnull(nullif(s.value, r.value), r.value) as value FROM real r 
LEFT OUTER JOIN shadow s ON s.id=r.id;
1
22
3
4

Or am I misunderstanding?

Regards,
Jonas



On Jul 20, 2011, at 5:27 PM, Simon Slavin wrote:

> 
> On 20 Jul 2011, at 4:21pm, KeithB wrote:
> 
>> I'd like to create a temporary table to "shadow" one of my persistent
>> tables. It will have the same columns and hold "override" values that,
>> when present, take precedence over the real values. So, I'd like to
>> search for a row by primary key, looking first in the override table,
>> and if not found, then in the real table. The result will always be
>> zero or 1 rows.
>> 
>> I can do this with two SELECTs, but that doesn't seem to be the most
>> efficient approach. I'm sure this is easy and obvious with some
>> combination of UNION, ORDER BY, and LIMIT or something, but I can't
>> figure it out.
> 
> Had you considered a VIEW ?  A VIEW is a way of saving a SELECT statement 
> that can be accessed as if it's a TABLE.  You could use a SELECT that uses a 
> JOIN to pick a value from the appropriate TABLE.
> 
> 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] "override" table?

[Jonas Sandman]

Don't you simply want this?

sqlite> create table real (id integer primary key, value integer);
sqlite> create table shadow (id integer primary key, value integer);
sqlite> insert into real (id, value) VALUES (1, 1);
sqlite> insert into real (id, value) VALUES (2, 2);
sqlite> insert into real (id, value) VALUES (3, 3);
sqlite> insert into real (id, value) VALUES (4, 4);
sqlite> insert into shadow (id, value) VALUES (2, 22);

sqlite> select ifnull(nullif(s.value, r.value), r.value) as value FROM real r 
LEFT OUTER JOIN shadow s ON s.id=r.id;
1
22
3
4

Or am I misunderstanding?

Regards,
Jonas



On Jul 20, 2011, at 5:27 PM, Simon Slavin wrote:

> 
> On 20 Jul 2011, at 4:21pm, KeithB wrote:
> 
>> I'd like to create a temporary table to "shadow" one of my persistent
>> tables. It will have the same columns and hold "override" values that,
>> when present, take precedence over the real values. So, I'd like to
>> search for a row by primary key, looking first in the override table,
>> and if not found, then in the real table. The result will always be
>> zero or 1 rows.
>> 
>> I can do this with two SELECTs, but that doesn't seem to be the most
>> efficient approach. I'm sure this is easy and obvious with some
>> combination of UNION, ORDER BY, and LIMIT or something, but I can't
>> figure it out.
> 
> Had you considered a VIEW ?  A VIEW is a way of saving a SELECT statement 
> that can be accessed as if it's a TABLE.  You could use a SELECT that uses a 
> JOIN to pick a value from the appropriate TABLE.
> 
> 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] UTF-8

[Jonas Sandman]

I suggest you try using wchar_t* or std::wstring as std::string is not
Unicode afaik.

Regards,
Jonas

On Thu, Dec 16, 2010 at 2:40 PM, Ming Lu  wrote:
> Hello Martin,
>
> thank you very much for your suggestion.
>
> i tried again with the firefox plugin works very well with the umlaut.(i
> update the feld with a "ä", it can been shown correctly).
>
> in my code:
>
> std::string strText = GetWindowsTitle(...);
>
> the GetWindowsTitle occupied sometime with the umlaut.
>
> so how will you do, if you want save std::string into sqlite with keeped
> umlaut?
>
> thanks a lot
> best regards
> ming
>
> On 16.12.2010 14:26, Martin Engelschalk wrote:
>> Hello Ming,
>>
>> sqlite does nothing to transform data between codepages, and it assumes
>> that data you insert is passed in UTF8.
>> However, sqlite will acept any data and store it.
>>
>> If the firefox plugin does not show you data correctly, then you
>> problably did not pass correct UTF8 to sqlite. Can you check this?
>>
>> I just checked the firefox (0.6.5) plugin with my databases, it works
>> correctly for me.
>>
>> Pictures / Attachments do not make it to the list.
>>
>> Martin
>>
>>
>> Am 16.12.2010 14:17, schrieb Ming Lu:
>>> Hello everyone,
>>>
>>> i am faceing a problem with unicode to save german umlaute in sqlite:
>>>
>>> here ist the problem:
>>>
>>> dev-envoriment:
>>> Visual studio 2008
>>> SQLite 3.6.20
>>>
>>> i used the sqlite c/c++ interface and open a db used sqlite3_open.
>>> during the running of my application will the german-umlaut or other
>>> symbol transported into the database. i use the SQLite Manager(firefox
>>> plugin) to open the db and the umlaut cannot be correctly saved into the
>>> database(see the pic) :
>>>
>>> ->   it should be "ä".
>>>
>>> my question is, how can use the sqlite c/c++ interface to save the
>>> unicode correctly into a sqlite database?
>>>
>>> thanks a lot
>>> best regards
>>>
>>> Ming
>>> ___
>>> 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
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How can I query a sqlite3_stmt tofind outif ithas been run through the sqlite3_prepare_v2 function?

[Jonas Sandman]

Ok, my mistake.

On Tue, Mar 9, 2010 at 7:37 PM, Jay A. Kreibich <j...@kreibi.ch> wrote:
> On Tue, Mar 09, 2010 at 07:30:54PM +0100, Jonas Sandman scratched on the wall:
>> Doesn't it return an array of sqlite3_stmt pointers?
>
>  No.
>
>> If you prepare this statement:
>>
>> "BEGIN; UPDATE something SET this='that'; COMMIT;"
>>
>> Then the array will contain the statement handles for the three
>> statements BEGIN, UPDATe and COMMIT.
>
>  http://sqlite.org/c3ref/prepare.html
>
>  "If pzTail is not NULL then *pzTail is made to point to the first
>  byte past the end of the first SQL statement in zSql. These routines
>  only compile the first statement in zSql, so *pzTail is left pointing
>  to what remains uncompiled."
>
>
>
>  sqlite3_exec() will process multiple statements.  Prepare will not.
>
>   -j
>
> --
> Jay A. Kreibich < J A Y  @  K R E I B I.C H >
>
> "Our opponent is an alien starship packed with atomic bombs.  We have
>  a protractor."   "I'll go home and see if I can scrounge up a ruler
>  and a piece of string."  --from Anathem by Neal Stephenson
> ___
> 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] How can I query a sqlite3_stmt tofind outif ithas been run through the sqlite3_prepare_v2 function?

[Jonas Sandman]

Doesn't it return an array of sqlite3_stmt pointers?

If you prepare this statement:

"BEGIN; UPDATE something SET this='that'; COMMIT;"

Then the array will contain the statement handles for the three
statements BEGIN, UPDATe and COMMIT.

/Jonas

On Tue, Mar 9, 2010 at 7:17 PM, Igor Tandetnik  wrote:
> Ed Curren  wrote:
>> According to the documentation the function prototype for
>> sqlite3_prepare_v2 is the following:
>>
>>
>>
>> int sqlite3_prepare(sqlite3 *db, const char *zSql, int nByte,
>> sqlite3_stmt **ppStmt, const char **pzTail);
>
> Note two stars in sqlite3_stmt **ppStmt. You pass a pointer to sqlite3_stmt* 
> (whose previous value is irrelevant and will be overwritten), and the 
> function fills it with the handle. Like this:
>
> sqlite3_stmt* stmt = NULL;
> sqlite3_prepare(db, "select * from mytable;", -1, , NULL);
> // Now stmt contains statement handle.
>
> In other words, it's an out parameter.
>
> Igor Tandetnik
>
>
> ___
> 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] Index on joined statements

[Jonas Sandman]

Okay, thanks! I think I understand better now, how to proceed.

Regards,
Jonas

On Fri, Nov 27, 2009 at 3:19 AM, Igor Tandetnik <itandet...@mvps.org> wrote:
> Jonas Sandman wrote:
>> Yes, but considering that I first join on typeid and then have name
>> and subspecies in the where, wouldn't that index be optimal for that
>> query?
>
> Again, the order of conditionals in the query is largely irrelevant. 
> Remember, your statement is equivalent to this one:
>
> select * from animals a, animaltype at
> where a.name='Monkey' and a.subspecies=2 and at.id=a.typeid;
>
> In this form, it should be obvious that conditions can be evaluated in any 
> order.
>
> If I had to guess without knowing the composition of your data, I'd say that 
> the most efficient plan would involve finding all records in animals with 
> a.name='Monkey' and a.subspecies=2, and for each such record, find matching 
> records in animaltype. I'm assuming that animals table contains records about 
> many different animals, and the number of records about monkeys is small 
> compared to the total number of records; in other words, that the condition 
> "a.name='Monkey' and a.subspecies=2" is highly selective.
>
> For this query plan, you would want indexes on animals(name, subspecies) and 
> animaltype(id).
>
> Igor Tandetnik
>
> ___
> 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] Index on joined statements

[Jonas Sandman]

Yes, but considering that I first join on typeid and then have name
and subspecies in the where, wouldn't that index be optimal for that
query?

Jonas

On Thu, Nov 26, 2009 at 4:42 PM, Igor Tandetnik <itandet...@mvps.org> wrote:
> Jonas Sandman wrote:
>> Doesn't that mean that my original suggestion is correct then?
>>
>> create index idx_animals on animals(typeid, name, subspecies)
>>
>> as those three columns of the animals table are used in the select?
>
> For this particular query, and for one particular way of executing this 
> query, this index can be used. Whether this query plan is good is difficult 
> to say without knowing more about your data.
>
> Realize that the order of fields in the index is important. Your index on 
> animals(typeid, name, subspecies) can be used for a condition like "typeid=?" 
> or "typeid=? and name=?" or "typeid=? and name=? and subspecies=?" (in any 
> order - AND operation is commutative), but it's useless when trying to 
> satisfy, say, "name=?".
>
> Consider a traditional index at the end of a textbook, with terms arranged 
> alphabetically. Such an index allows you to quickly find all terms beginning 
> with 'a' or 'ab' or 'abc', but it's useless if you want to find all terms 
> whose second letter is 'a'. An index in a relational database works the same 
> way.
>
> Igor Tandetnik
>
> ___
> 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] Index on joined statements

[Jonas Sandman]

I see!

Doesn't that mean that my original suggestion is correct then?

create index idx_animals on animals(typeid, name, subspecies)

as those three columns of the animals table are used in the select?

Regards,
Jonas

On Thu, Nov 26, 2009 at 4:05 PM, Igor Tandetnik <itandet...@mvps.org> wrote:
> Jonas Sandman wrote:
>> But I guess the answer is that only the "where" parts should be
>> indexed, not the id's in the joins?
>
> No, that's generally not true. ON clauses in joins are basically a syntactic 
> sugar (though there's a subtle difference in case of outer joins). These 
> three statements are equivalent:
>
> select * from animals a
> join animaltype at on at.id=a.typeid
> where a.name='Monkey' and a.subspecies=2;
>
> select * from animals a, animaltype at
> where a.name='Monkey' and a.subspecies=2 and at.id=a.typeid;
>
> select * from animals a join animaltype at on (
>    at.id=a.typeid and a.name='Monkey' and a.subspecies=2);
>
> So, whether a particular condition is in WHERE clause or in ON clause is 
> immaterial.
>
> Igor Tandetnik
>
> ___
> 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] Index on joined statements

[Jonas Sandman]

Yes, I forgot to add "at.name as AnimalType" in the select.

It's not a real-life sample, it was just a way to try to describe what
I am thinking about...

But I guess the answer is that only the "where" parts should be
indexed, not the id's in the joins?

/Jonas

On Thu, Nov 26, 2009 at 3:38 PM, Igor Tandetnik <itandet...@mvps.org> wrote:
> Jonas Sandman wrote:
>> If you have a join in an SQL-query, how do you add the statements to
>> optimize the query in an index?
>>
>> For example:
>>
>> select a.* from animals a
>> join animaltype at on at.id=a.typeid
>> where a.name='Monkey' and a.subspecies=2
>
> Why are you joining with animaltype here? You don't seem to use it for 
> anything.
>
> Igor Tandetnik
>
> ___
> 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] Index on joined statements

[Jonas Sandman]

Hello,

If you have a join in an SQL-query, how do you add the statements to
optimize the query in an index?

For example:

select a.* from animals a
join animaltype at on at.id=a.typeid
where a.name='Monkey' and a.subspecies=2

do I add the index like this: "create index idx_animals on
animals(typeid, name, subspecies) " ?
or is it done some other way?

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

Re: [sqlite] any keyword

[Jonas Sandman]

Don't you just want to use IN?

SELECT G.id,name FROM Genre G
WHERE G.id IN (SELECT S.genre_id FROM Song S)
ORDER BY name ASC;

/Jonas

On Wed, Nov 11, 2009 at 9:48 AM, Artur Reilin  wrote:
> Does it required the any key? Doesn't it work without it?
>
> greetings
>
> 
>
>> Probably sqlite doesn't support 'any' keyword as I write it in the
>> following query:
>> SELECT G.id,name FROM Genre G
>> WHERE G.id = ANY (SELECT S.genre_id FROM Song S)
>> ORDER BY name ASC;
>>
>> In this case I can write an equivalent query like:
>> select  G.id,name from Genre G
>> WHERE (SELECT COUNT(*) FROM Song S
>> WHERE G.id = S.genre_id) > 0
>> ORDER BY name;
>>
>> Anyway, could I avoid to use count which require a very long time? Does
>> the development
>> team have a plan including the 'any/all' keyword implementation? I think
>> it should be
>> useful for many users.
>> Regards
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>
>
> Artur Reilin
> sqlite.yuedream.de
> ___
> 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] update - select

[Jonas Sandman]

On Thu, Aug 20, 2009 at 12:01 PM, Gerald Ebner wrote:
> Dear all,
>
> it seems that sqlite does not allow update statements of this kind:
>
> UPDATE table_1 SET (field_a, field_b, field_c, field_d) = (
>    SELECT field_a, field_b, field_c, field_d FROM table_2 WHERE …
> )
> WHERE 
>

Is this really proper standard SQL?

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

Re: [sqlite] SQLite caching

[Jonas Sandman]

Isn't it more likely that your database is pulled up into the OS disk cache?
Try rebooting the computer between runs and the cache should be cleared.

On Mon, Feb 23, 2009 at 8:44 AM, manohar s  wrote:
> Hi,
>  I am doing some performance analysis on my SQLite queries. The problem is
> SQLite seems to be caching query results.
> I tried restarting my program, that is not helping. only if i don't access
> that database for 2 days then it is giving proper profile data.
> *
> Question*
> 1) How can I disable query result caching in SQLite?
>
> Regards,
> Manohar.S
> ___
> 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] basic problem...

[Jonas Sandman]

I'd think it's more a programming skill if you can "use SQLite". You
need some basic knowledge of SQL though which I guess would be
considered "a database skill". But that's just imho...

On Thu, Feb 5, 2009 at 3:48 PM, Mihai Limbasan  wrote:
> Oh, I understand now. Probably in the Database section.
>
> vade wrote:
>> Hello
>>
>> Thank you Mihai Limbasan.
>> For your clarification, Now I'm Preparing my Profile(cv or resume). I just
>> want to know where to show this sqlite3 skill.
>> whether in Programing skill section or database skill section or web
>> technology skill section.
>>
>> thank you
>>
>>
>> Mihai Limbasan wrote:
>>
>>> vade wrote:
>>>
 is sqlite3 is web skill OR database skill?
 can anybody please tell me...

 thank you in advance


>>> SQLite 3 is a database engine - a library. Part of a computer program.
>>> It's not a skill. A skill is an abstract concept.
>>>
>>> Could you perhaps rephrase your question?
>>> ___
>>> 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] convert sql for sqlite 2.8.17

[Jonas Sandman]

Shouldn't it be

select f.type, f.variety, f.price
from
fruits f
where
rowid in (select rowid from fruits where type = f.type order by
price desc limit 1)

?

No need for the 'AS' there.

/Jonas

On Tue, Dec 9, 2008 at 12:54 AM, Hariyanto Handoko <[EMAIL PROTECTED]> wrote:
> I want to get one result from that query.
> It ok if run under sqlite3.
>
> But when I try with with sqlite 2.8.17, I got this error.
> SQL error: no such column: f.type
> I tried add AS (Alias) but still got that errror message.
>
> select f.type, f.variety, f.price
> from
>  fruits AS f
> where
>  rowid in (select rowid from fruits where type = f.type order by
> price desc limit 1)
>
> order by f.type asc, f.price desc;
>
> I got  from this old message : (
> Re: [sqlite] Select the top N rows from each group)
>
> create table fruits (type text, variety text, price number);
> create index fruit_type_price on fruits (type, price);
> insert into fruits values ('apple', 'gala', 2.79);
> insert into fruits values ('apple', 'fuji', 0.24);
> insert into fruits values ('apple', 'limbertwig', 2.87);
> insert into fruits values ('orange', 'valencia', 3.59);
> insert into fruits values ('orange', 'navel', 9.36);
> insert into fruits values ('pear', 'bradford', 6.05);
> insert into fruits values ('pear', 'bartlett', 2.14);
> insert into fruits values ('cherry', 'bing', 2.55);
> insert into fruits values ('cherry', 'chelan', 6.33);
>
> result from query sqlite3 :
> apple|limbertwig|2.87
> cherry|chelan|6.33
> orange|navel|9.36
> pear|bradford|6.05
>
> How I can fix that sql so I can get same result in sqlite 2.8.17
> tx
>
>
> On Tue, Dec 9, 2008 at 2:12 AM, Martin Engelschalk
> <[EMAIL PROTECTED]> wrote:
>>
>> Hi,
>>
>> i thought that too, at first, but i think he wants to select one record
>> for every type of fruit, namely the record with the highest price within
>> one value of 'type' (I have difficulty saying what I mean in english.)
>>
>> However, the longer i look at the statement, the less sense it makes to
>> me. Perhaps  Hariyanto Handoko would like to post what the statement is
>> supposed to achieve.
>>
>> Martin
>>
>>
>> ___
>> 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] how to add multiple columns at a time

[Jonas Sandman]

I don't think you can add multiple columns in just one command.

/Jonas

On Sat, Oct 25, 2008 at 2:34 PM, 灵感之源 <[EMAIL PROTECTED]> wrote:
> Hi,
>
>
>  I want to add multiple columns, the following works for only one
> column:
>
>  ALTER TABLE Data ADD COLUMN Password TEXT
>
>  but not this:
>
>   ALTER TABLE Data ADD COLUMN (Password TEXT, User TEXT)
>
>   nor this:
>
>   ALTER TABLE Data ADD (Password TEXT, User TEXT)
>
>
> --
> Regards
> ___
> 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] Multiple Selects

[Jonas Sandman]

If it's completely arbitrary I think you are stuck with using union
unless it's an order that you might know beforehand.
Then you can add an extra column with the index.

/Jonas

On Sat, Oct 18, 2008 at 7:05 PM, Andrew Gatt <[EMAIL PROTECTED]> wrote:
> Jonas Sandman wrote:
>> Just to point out the obvious, have you tried ORDER BY?
>>
>> "SELECT name FROM table ORDER BY name;" will return your list in
>> alphabetical order.
>>
>> /Jonas
>>
>>
> Thanks for the suggestion, but it needs to be an order i can specify,
> not just ordered. I.e. i may want row 45 first, then 32 then 67 etc...
>
>> On Sat, Oct 18, 2008 at 6:53 PM, Andrew Gatt <[EMAIL PROTECTED]> wrote:
>>
>>> Andrew Gatt wrote:
>>>
>>>> I'm not sure if i'm missing something, but is there an efficient way of
>>>> retrieving multiple rows based on different conditions in order. For
>>>> example i have a table with rows of ids, i want to select multiple rows
>>>> at a time. At present i am doing a "SELECT name FROM table WHERE id = x"
>>>> for each row i want and then stitching it all together. But i'm finding
>>>> this is quite slow even on a moderately small database (2000 entries).
>>>>
>>>> I'm guessing my SQL is the worst way of doing things so i've been trying
>>>> to find a better method. I stumbled across "SELECT name FROM table WHERE
>>>> id IN (x,y,z) however this doesn't allow me to specify the order the
>>>> rows are returned, which i must have.
>>>>
>>>> The only other option i can find is using UNION ALL in between multiple
>>>> SELECT statements, but would this give me a large performance increase
>>>> over doing this progammatically as i've got it?
>>>>
>>>> Unless i've missed something obvious which could well be the case!
>>>>
>>>>
>>>>
>>>>
>>> After trying several methods to improve the SQL the only thing that
>>> really made a difference was creating an index on the ids. Using a UNION
>>> ALL did improve matters, but you end up have to concatenate a very long
>>> string for the query, so if anyone does have any SQL ideas i'd like to
>>> hear them.
>>>
>>> Andrew
>>>
>>>
>
> ___
> 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] Multiple Selects

[Jonas Sandman]

Just to point out the obvious, have you tried ORDER BY?

"SELECT name FROM table ORDER BY name;" will return your list in
alphabetical order.

/Jonas

On Sat, Oct 18, 2008 at 6:53 PM, Andrew Gatt <[EMAIL PROTECTED]> wrote:
> Andrew Gatt wrote:
>> I'm not sure if i'm missing something, but is there an efficient way of
>> retrieving multiple rows based on different conditions in order. For
>> example i have a table with rows of ids, i want to select multiple rows
>> at a time. At present i am doing a "SELECT name FROM table WHERE id = x"
>> for each row i want and then stitching it all together. But i'm finding
>> this is quite slow even on a moderately small database (2000 entries).
>>
>> I'm guessing my SQL is the worst way of doing things so i've been trying
>> to find a better method. I stumbled across "SELECT name FROM table WHERE
>> id IN (x,y,z) however this doesn't allow me to specify the order the
>> rows are returned, which i must have.
>>
>> The only other option i can find is using UNION ALL in between multiple
>> SELECT statements, but would this give me a large performance increase
>> over doing this progammatically as i've got it?
>>
>> Unless i've missed something obvious which could well be the case!
>>
>>
>>
> After trying several methods to improve the SQL the only thing that
> really made a difference was creating an index on the ids. Using a UNION
> ALL did improve matters, but you end up have to concatenate a very long
> string for the query, so if anyone does have any SQL ideas i'd like to
> hear them.
>
> Andrew
>
> ___
> 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] BEGIN IMMEDIATE returns SQLITE_ERROR

[Jonas Sandman]

Hello,

I am a little confused, below is my code, a bit taken out of its
context but anyway, all variables are present and it compiles okay.

m_stmt = NULL;
m_pTail = NULL;
sqlite3_prepare16_v2(m_db, L"BEGIN IMMEDIATE", -1, _stmt, (const
void**)_pTail);
int err = sqlite3_step(m_stmt);
assert(err == SQLITE_DONE);
sqlite3_finalize(m_stmt);

It have worked pretty well, or at least I think. I started adding
asserts to my code and this part of the code apparently
have been acting up (always?). In any case, the variable 'err' is 1
meaning that I get SQLITE_ERROR. Why is BEGIN IMMEDIATE
returning an error? Can it return error if another transaction is
currently present?

Thanks for any input!

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

Re: [sqlite] SQLite takes too much memory

[Jonas Sandman]

On Sat, Jun 28, 2008 at 8:43 AM, Mihai Limbasan <[EMAIL PROTECTED]> wrote:
> The posted code does not create 100 prepared statements. It creates just
> one, fetches the data, finalizes the statement, then loops.

Very true, my mistake.

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

Re: [sqlite] Problem with sqlite overall or not?

[Jonas Sandman]

Isn't this simply a vector problem?
You have a vector where you store char pointers. The pointers returned from
sqlite3_column_text will be destroyed when you step afaik.
Try making it a vector instead. Then you will implicitly make
copies of the text in your vector instead.

/Jonas

On Tue, Jun 10, 2008 at 4:12 PM, piotro <[EMAIL PROTECTED]> wrote:
> Hello. I really need help with this, because Im almost out of
> ideas here. Help please!
>
> sqldata is a vector< vector < char* > >
> row is a vector < char* >
>
> sqldata sql3wrapper::pobierzPodmiot(){
>const char* tail;
>sqlite3_stmt* statement;
>
>std::string query = squery("SELECT * FROM %;",PODMIOTY_TABLE);
>
>sqlite3_prepare_v2(interface,query.c_str(),-1,,);
>sqldata data;
>for(int a=0;sqlite3_step(statement)!=101;a++){
>row set;
>data.push_back(set);
>for(int az=1; azdata[a].push_back((char*)sqlite3_column_text(statement,az));
>}
>std::cout<<(data[a])[2]<}
>return data;
>sqlite3_finalize(statement);
> }
>
> this does cout properly. But when I'm using it in a different place:
>
> int main(){
> sqldata est = sql3wrapper().pobierzPodmiot();
>
>row one = est[0];
>row two = est[1];
>row three = est[2];
>
>std::cout<<'\n'<std::cout<std::cout<std::cout< }
>
> the data is couted properly, but the values are just the values coming
> from the last row.
>
> I have three rows of data in the sqlite database. Then I use the
> pobierzPodmiot
> function, get the data correctly, but when the function is used
> somewhere else,
> it hickups pretty much.
>
> Can someone come up with a solution or something, coping with this had
> been tiresome.
>
> Cheers!
>
> ___
> 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] table names from subselects?

[Jonas Sandman]

Isn't proper SQL

select * from (select user.id from user ) blah where blah.id=1 ; ?

On Fri, Mar 14, 2008 at 11:40 PM, Jay Sprenkle <[EMAIL PROTECTED]> wrote:
> I'm not sure if this counts as a bug or not.
>
>  SQLite version 3.5.2
>  Enter ".help" for instructions
>  sqlite> select * from (select user.id from user ) ;
>  0
>  1
>  2
>  3
>  4
>  sqlite> select * from (select user.id from user ) where id=1 ;
>  SQL error: no such column: id
>  sqlite> select * from (select user.id from user ) where user.id=1 ;
>  SQL error: no such column: user.id
>  sqlite> select * from (select user.id from user ) as blah where blah.id=1 ;
>  SQL error: no such column: blah.id
>  sqlite> select * from (select user.id from user ) as blah where 
> blah.user.id=1 ;
>
>  SQL error: no such column: blah.user.id
>  sqlite>
>
>  Should the result of a subselect have a table name? If not, how do you
>  reference it?
>
>
>
>  --
>  --
>  The PixAddixImage Collector suite:
>  http://groups-beta.google.com/group/pixaddix
>
>  SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
>  http://www.reddawn.net/~jsprenkl/Sqlite
>
>  Cthulhu Bucks!
>  http://www.cthulhubucks.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] SQL Newbie problem I guess...

[Jonas Sandman]

Thanks for the input.

The 'Folder' vs 'Folders' problem was merely a spelling mistake here,
not in the code.

My problem was that '%' was being escaped in my code. Putting a '%%'
fixed the problem.

Regards,
Jonas

On Tue, Mar 11, 2008 at 5:37 PM, Dennis Cote <[EMAIL PROTECTED]> wrote:
>
> Jonas Sandman wrote:
>  >
>  > I must be missing something obvious here...
>  >
>  > I have created my database like this:
>  >
>  > CREATE TABLE Folders (folderid INTEGER PRIMARY KEY, parentid INTEGER,
>  > rootid INTEGER, path VARCHAR(255))";
>  >
>  > The database is filled with files and folders..
>  >
>  > folderid parentid rootid path
>  > 1 0   1   C:\MP3\Albums\
>  > 2 1   1   C:\MP3\Albums\Abba - Definitive Collection\
>  > 3 2   1   C:\MP3\Albums\Abba - Definitive Collection\cd1\
>  > 4 2   1   C:\MP3\Albums\Abba - Definitive Collection\cd2\
>  >
>  > Then I want to delete 'C:\MP3\Albums' folder and its sub-folders:
>  >
>  > I figured this should work:
>  > DELETE FROM Folder WHERE folderid IN (SELECT folderid FROM Folder
>  > WHERE path LIKE :PATH || '%');
>  >
>  > Where :PATH is 'C:\MP3\Albums\'
>  >
>  > If I run the sub-query by itself it returns the resultset from above
>  > but when I run it like above, sqlite3_changes(..) returns '1' and only
>  > the row with folderid=1 is deleted.
>  >
>  > What am I doing wrong?
>
>  Well, your problem may be that the table name in the subquery and the
>  delete statement, "Folder", is different than the name in the create
>  table statement, "Folders". If I use the correct table name it runs as
>  expected.
>
>  The more interesting thing I discovered when testing this is that having
>  the wrong table name in the subquery causes the sqlite3.exe command
>  shell to terminate.
>
>  The following SQL script terminates when it executes the delete statement.
>
>
>  CREATE TABLE Folders (folderid INTEGER PRIMARY KEY, parentid INTEGER,
>  rootid INTEGER, path VARCHAR(255));
>
>  insert into Folders values(1,0,1,'C:\MP3\Albums\');
>  insert into Folders values(2,1,1,'C:\MP3\Albums\Abba - Definitive
>  Collection\');
>  insert into Folders values(3,2,1,'C:\MP3\Albums\Abba - Definitive
>  Collection\cd1\');
>  insert into Folders values(4,2,1,'C:\MP3\Albums\Abba - Definitive
>  Collection\cd2\');
>
>  SELECT folderid FROM Folders WHERE path LIKE 'C:\MP3\Albums\' || '%';
>
>  DELETE FROM Folders WHERE folderid IN
>  (SELECT folderid FROM Folder WHERE path LIKE 'C:\MP3\Albums\' || '%');
>
>  Dennis Cote
>
>
> ___
>  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] memory issue with large amount of inserts

[Jonas Sandman]

I guess it's not as simple as that you're application is hogging the
Java VM so much that the garbage collector is never running? You can
always force it to run explicitly if so...

/Jonas

On Fri, Feb 1, 2008 at 1:46 PM, zqzuk <[EMAIL PROTECTED]> wrote:
>
>  Hi, I am using SQLite 3.5.4, my application has encountered a memory
>  management issue which I think is caused  by sqlite. I have searched the
>  forum and found this thread describing most similar situation as mine
>
>  http://www.nabble.com/Memory-Usage-to13798003.html#a13850915
>
>  My application is accessing sqlite through JAVA on local machine, windows xp
>  sp2, there are 4 threads accessing 4 *different* databases at the same time,
>  each doing 2 million inserts to *different* databases.
>
>  I have noticed that after about 3 hours, the system slowed down
>  dramatically, such that processing every 1000 records(then 1000 inserts)
>  took now 20 mins - 1 hr instead of sever seconds at the very beginning. I
>  then checked the resource monitor and found all of my 2 gb memory has been
>  used.
>
>  I searched this forum and found talks about similar issues but solutions
>  seem to be re-compiling sqlite in C using new parameters, but I am using
>  JAVA and would like to know whether there is alternative/equivalent
>  solution? And also the above thread I quote mentioned that closing/reopen
>  connection can free up resources that sqlite occupy, i would take this as a
>  simple but dirty workaround?
>
>
>  Please may I have you suggestions , many thanks!
>
>  --
>  View this message in context: 
> http://www.nabble.com/memory-issue-with-large-amount-of-inserts-tp15225948p15225948.html
>  Sent from the SQLite mailing list archive at Nabble.com.
>
>
>  -
>  To unsubscribe, send email to [EMAIL PROTECTED]
>  -
>
>

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] startswith and contains

[Jonas Sandman]

Oh I have no idea. I thought LIKE with '%' was a standard, % being the wildcard.

/Jonas

On Jan 24, 2008 1:25 PM, Pavel Kosina <[EMAIL PROTECTED]> wrote:
> I was still trying "*jup*" 
> Could you show me documentation page, where this is mentioned?
>
> Pavel Kosina
>
>
>
> Jonas Sandman napsal(a):
>
> > 1. SELECT * FROM some WHERE xyz LIKE '%jup%'
> >
> > 2. SELECT * FROM SOME WHERE zyx LIKE 'jul%'
> >
> > should work.
> >
> > On Jan 24, 2008 12:44 PM, Pavel Kosina <[EMAIL PROTECTED]> wrote:
> >
> >> Hello,
> >>
> >> How to do following queries?:
> >>
> >> 1/ select * from some where xyz CONTAINS "jup" (anywhere in xyz could be
> >> text "jup")
> >> 2/ select * from some where xyz STARTSWITH "jup" (the xyz starts with 
> >> "jup")
> >>
> >> Thank you.
> >>
> >>
> >> --
> >> Pavel Kosina
> >>
> >>
> >> -
> >> To unsubscribe, send email to [EMAIL PROTECTED]
> >> -
> >>
> >>
> >>
> >
> > -
> > To unsubscribe, send email to [EMAIL PROTECTED]
> > -
> >
> >
> >
>

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] startswith and contains

[Jonas Sandman]

1. SELECT * FROM some WHERE xyz LIKE '%jup%'

2. SELECT * FROM SOME WHERE zyx LIKE 'jul%'

should work.

On Jan 24, 2008 12:44 PM, Pavel Kosina <[EMAIL PROTECTED]> wrote:
> Hello,
>
> How to do following queries?:
>
> 1/ select * from some where xyz CONTAINS "jup" (anywhere in xyz could be
> text "jup")
> 2/ select * from some where xyz STARTSWITH "jup" (the xyz starts with "jup")
>
> Thank you.
>
>
> --
> Pavel Kosina
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] ORDER BY Performance on 30,000 records

[Jonas Sandman]

Sorting the returned 30 000 records maybe takes 3-4 seconds?

/Jonas

On 12/2/07, Ofir Neuman <[EMAIL PROTECTED]> wrote:
> Hi All,
>
> I have some performance problem when adding ORDER BY to my query, hope you
> can help me speed things up.
>
> This is my table:
>
> TABLE1
> {
>  ID TEXT
>  ParentID TEXT
>  ModifiedDate INTEGER
> }
>
> ID is the PK of the table and i also have an index on ParentID.
> Current number of records in table: 40,000
>
> My query is very simple:
> SELECT ID FROM Table1 WHERE ParentID = '{---}' ORDER BY
> ModifiedDate
>
> According to the data in my table this query should return 30,000 records.
>
> While using ORDER BY it takes 3-4 SEC to retrieve the query result, without
> the ORDER BY it take something like 30 ms.
>
> Tried to index also 'ModifiedDate' but it didn't help.
>
> What am i doing wrong?
>
> Thanks,
> Ofir Neuman.
>

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Is possible put a password in database ?

[Jonas Sandman]

How much is it for the encrypted version?

Regards,
Jonas

On Nov 8, 2007 1:12 PM, Trevor Talbot <[EMAIL PROTECTED]> wrote:
> On 11/8/07, paulito santana <[EMAIL PROTECTED]> wrote:
>
> > is possible i put a password in the file that represents a SQLite database
> > ??  There is any instruction in SQLite command line that allows this ?
>
> No.  drh sells a version of SQLite with encryption support, and some
> others maintain their own versions.  The free standard version has no
> such support though.
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Threads in 3.5.0

[Jonas Sandman]

Well I am certainly a newbie in this context, threads must certainly
be used when writing Windows application since you are not at liberty
to splinter your process like you can on a *nix based system, no?

On 10/4/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> John Stanton <[EMAIL PROTECTED]> wrote:
> >
> > When multi-processor machines are used thread usage may indeed implement
> > parallel processing when used appropriately and have tangible benefits.
> >
>
> True enough, in general.  But with SQLite 3.5, access to each
> database connection is serialized.  So even though the interface
> allows you to have 20 different threads all doing sqlite3_exec()
> on the same connection at the same time, the SQL statements are
> still being processed one by one, regardless of how many CPUs
> you devote to the task.
>
> --
> D. Richard Hipp <[EMAIL PROTECTED]>
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] FW: BLOB data retrieval

[Jonas Sandman]

Still would be nice to know why you feel like stepping through the
whole lot to count rows?

On 6/28/07, Krishnamoorthy, Priya (IE10)
<[EMAIL PROTECTED]> wrote:

Thanks all for the help.
I fixed the problem by stepping through one row after another.
Thanks a lot.
Priya

-Original Message-
From: Trevor Talbot [mailto:[EMAIL PROTECTED]
Sent: Thursday, June 28, 2007 11:35 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] FW: BLOB data retrieval

On 6/27/07, Krishnamoorthy, Priya (IE10)
<[EMAIL PROTECTED]> wrote:

> I am writing a program (in MS VC++) which has to read the blob data in
> all the rows of the above mentioned table one after another. I am
using
> cppSQLite3 which is C++ wrapper function for accessing sqlite3 db.
Mine
> is an offline program that reads the data in the database which is
> updated by another program.

> Please let me know how I can go about doing this?

In the interest of being helpful, I should probably mention that the
standard way of doing this in concept is:

begin query "select BlobColumn from table;"
while not at end of rows in result
get blob data for current row
move to next row


-
To unsubscribe, send email to [EMAIL PROTECTED]

-

-
To unsubscribe, send email to [EMAIL PROTECTED]
-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] FW: BLOB data retrieval

[Jonas Sandman]

You need to know the number of rows, don't you already have that in
b.numRows() ?

/Jonas

On 6/28/07, Trevor Talbot <[EMAIL PROTECTED]> wrote:

On 6/27/07, Krishnamoorthy, Priya (IE10)
<[EMAIL PROTECTED]> wrote:

> for (int i=1;i <= b.numRows() ; i++)

>sql_command.format("select Data from %s where Row_Num =
> %d;",table_name,i);

>q = db.execQuery(sql_command);

>if (!q.eof())

> This method works fine only when the table size is small. For example, I
> have a database of size over 2GB in which case I get an error
> SQLITE_NOMEM when I try to do db.gettable(). But i need to know the
> number of rows in the table for reading BLOB data in each of the rows
> one after another.

Why?  The query wrapper appears to support iteration, since it has a
notion of "eof".  Why do you need the row count ahead of time?  Why do
you assume it's a monotonic sequence (no gaps)?

If you really do need the row count for some reason, you can construct
a query to give it to you, without retrieving all the data.  And since
you retrieved all the data at once in the first place, why are you
retrieving it again anyway?

I'm getting confused just trying to analyze what you're doing.  I
think you need to stop and think about what you really want to do :)

> Secondly, the column "Row_Num" is of type INTEGER_PRIMARY_KEY. So, it
> will take negative value after 2GB as mine is a 32 bit machine.

SQLite integers are 64 bits, so the database will have no issue.  If
your wrapper is only capable of giving you 32 bit integers, you'll
have to get a different wrapper.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] "Make" with VisualStudio/Windows

[Jonas Sandman]

Yes I tried using the amalgamation, but I still don't get as good result as
I did before with 3.3.13...

Also, when I defined SQLITE_OMIT_ALTERTABLE I got this error:

error LNK2019: unresolved external symbol _sqlite3AlterBeginAddColumn
referenced in function _yy_reduce
error LNK2019: unresolved external symbol _sqlite3AlterFinishAddColumn
referenced in function _yy_reduce
error LNK2019: unresolved external symbol _sqlite3AlterRenameTable
referenced in function _yy_reduce

Best regards,
Jonas

On 6/13/07, Andrew Finkenstadt <[EMAIL PROTECTED]> wrote:


On 6/13/07, Jonas Sandman <[EMAIL PROTECTED]> wrote:
>
> Hello,
>
> Is it possible to get the latest SQLite somehow which doesn't require
you
> to
> actually run "make"?
>
> With the amalgamation (?) you can just compile the whole thing in Visual
> Studio but it seems that the compiler isn't very successful in
optimizing
> when it's run like that since my application swelled another 39 kb just
> because I used an amalgamation-compiled library instead of 3.3.13 which
I
> compiled earlier (not sure where I got all the working header-files for
> that
> one, maybe they were present back then?)
>
> Best regards,
> Jonas
>


I use SQlite to some success in Visual Studio 2005.  I use the
amalgamation
exclusively, and I compile in "C++" mode.  I also predefine SQLITE_option
strings that disable features that are not necessary in my application,
which reduces code size.  The result of this step is an object file
sqlite3.obj which I then link into the remainder of my application
framework
that uses sqlite3 facilities.

Nowhere a make appears in that process.

[sqlite] "Make" with VisualStudio/Windows

[Jonas Sandman]

Hello,

Is it possible to get the latest SQLite somehow which doesn't require you to
actually run "make"?

With the amalgamation (?) you can just compile the whole thing in Visual
Studio but it seems that the compiler isn't very successful in optimizing
when it's run like that since my application swelled another 39 kb just
because I used an amalgamation-compiled library instead of 3.3.13 which I
compiled earlier (not sure where I got all the working header-files for that
one, maybe they were present back then?)

Best regards,
Jonas

Re: [sqlite] Stack usage

[Jonas Sandman]

Is it wise to have a database with 2000 columns? Wouldn't it be better to
split this into several tables and query them separately?

Jonas

On 6/5/07, [EMAIL PROTECTED] <
[EMAIL PROTECTED]> wrote:





This is very worrying since it means that the statement cannot be compiled
on a
low memory device.
I am new to Sqlite, but I would guess that a precompiled query could be
used,
where memory is low
and I also suppose that variable values could be bound to that precompiled
query.

Clive








Joe Wilson <[EMAIL PROTECTED]> on 05/06/2007 14:33:42

Please respond to sqlite-users@sqlite.org

To:   sqlite-users@sqlite.org
cc:(bcc: clive/Emultek)

Subject:  Re: [sqlite] Stack usage



--- [EMAIL PROTECTED] wrote:
> I'v read in change log that some stack allocted memory were moved to the
heap,
but I think that
> there is still to much allocated memory on the stack.
> After creating a table with 2000 columns, jdbc driver created a query
that run
out of stack.
> Default java's stack limit is low, but it wasn't hard to create simillar
query
that crashed C
> application with default stack limit. And the fact that it crashed
instead
repoting an error
> isn't really nice.
> The query created by the driver looks like that:
>
> select null as TABLE_CAT, null as TABLE_SCHEM, 'test' as TABLE_NAME, cn
as
COLUMN_NAME, dt as
> DATA_TYPE, tn as TYPE_NAME, 20 as COLUMN_SIZE, 20 as
BUFFER_LENGTH, 10   as
> DECIMAL_DIGITS, 10   as NUM_PREC_RADIX, colnullable as NULLABLE, null as
REMARKS, null as
> COLUMN_DEF, 0as SQL_DATA_TYPE, 0as SQL_DATETIME_SUB, 20
as
CHAR_OCTET_LENGTH,
> ordpos as ORDINAL_POSITION, (case colnullable when 0 then 'N' when 1
then 'Y'
else '' end)as
> IS_NULLABLE, null as SCOPE_CATLOG, null as SCOPE_SCHEMA, null as
SCOPE_TABLE,
null as
> SOURCE_DATA_TYPE from (select 0 as ordpos, 1 as colnullable, 'col1' as
cn,
'double' as tn, 8 as
> dt union all select 1 as ordpos, 1 as colnullable, 'col2' as cn,
'double' as
tn, 8 as dt union
> all select 2 as ordpos, 1 as colnullable, 'col3' as cn, 'double' as tn,
8 as
dt union all select
> 3 as ordpos, 1 as colnullable, 'col4' as cn, 'double' as tn, 8 as dt
union all
select 4 as
> ordpos, 1 as colnullable, 'lastcol' as cn, 'double' as tn, 8 as dt);
>
> but uses more columns.

SQLite uses recursion to generate code for SELECT UNION chains.

** SELECT a FROM t1 UNION SELECT b FROM t2 UNION SELECT c FROM t3
**
** This statement is parsed up as follows:
**
** SELECT c FROM t3
**  |
**  `->  SELECT b FROM t2
**|
**`-->  SELECT a FROM t1

So for your example you will have a stack of 2000 nested calls
of sqlite3Select's (via multiSelect).

Using gcc -O2 on x86 I see that each level's stack is 480 bytes.
So for 2000 unions in a select, SQLite will consume at least
480 bytes/frame x 2000 frames = 960,000 bytes. I'm guessing that Java
limits the stack to 1M per thread in your case, hence your problem.

It is not easy to adapt SQLite's code to not use recursion for
code generation, although with enough time and effort anything is
possible. Conceivably, the processing of compound queries could
be turned into a for loop.

The SQLite authors have recently added a number of maximum limits
via #defines. Perhaps the SQLITE_MAX_EXPR_DEPTH limit could also
be applied here:

  /* If there is are a sequence of queries, do the earlier ones first.
  */
  if( p->pPrior ){
if( p->pRightmost==0 ){
  Select *pLoop;
  for(pLoop=p; pLoop; pLoop=pLoop->pPrior){
pLoop->pRightmost = p;
  }
}
return multiSelect(pParse, p, eDest, iParm, aff);
  }








It's here! Your new message!
Get new email alerts with the free Yahoo! Toolbar.
http://tools.search.yahoo.com/toolbar/features/mail/


-
To unsubscribe, send email to [EMAIL PROTECTED]

-









-
To unsubscribe, send email to [EMAIL PROTECTED]

-

Re: [sqlite] Re: Re: Re: COLLATE without ORDER BY?

[Jonas Sandman]

Thanks Igor,

A custom function was exactly what I needed :-)

Best regards,
Jonas

On 5/25/07, Igor Tandetnik <[EMAIL PROTECTED]> wrote:


Jonas Sandman <[EMAIL PROTECTED]>
wrote:
>> ext1 is one type of extension, in my case 'mp3'. I guess it doesn't
>> make
> sense when you have only one, but potentially there can be a lot more
> there. ('mp3', 'ogg', 'flac') etc...

And how exactly do you plan to specify such a list in a parameter?

> Unfortunately, "COLLATION STRIP_ACCENT LIKE" doesn't seem to trigger
> anything either.

First, it's "COLLATE", not "COLLATION". Second, LIKE doesn't use
collations. Note that a collation doesn't convert one string to another,
it just tells how two strings should be ordered. This information is
useless to LIKE.

What you probably want is a custom function, not a custom collation. A
function would take a string and return another string, with accents
removed. You can then use it like this:

select * from tableName
where StripAccents(field) = value;

Igor Tandetnik



-
To unsubscribe, send email to [EMAIL PROTECTED]

-

Re: [sqlite] Re: Re: COLLATE without ORDER BY?

[Jonas Sandman]

:ext1 is one type of extension, in my case 'mp3'. I guess it doesn't make
sense when you have only one, but potentially there can be a lot more there.
('mp3', 'ogg', 'flac') etc...

Unfortunately, "COLLATION STRIP_ACCENT LIKE" doesn't seem to trigger
anything either. What makes me the most confused is that SQLite takes it as
a valid prepared statement. No error or anything.

I am using SQLite 3.3.17, which should be recent enough (latest?)

Best regards,
Jonas

On 5/25/07, Igor Tandetnik <[EMAIL PROTECTED]> wrote:


Jonas Sandman <[EMAIL PROTECTED]>
wrote:
> However, I tried this prepared statement:
>
> SELECT f.fileid, f.path, m.title, m.artist, m.album, m.genre,
> m.comment,
> m.track, m.year, m.length, m.bitrate, m.playcount, f.changed, f.size,
> m.tagged FROM Files f, Meta m WHERE m.fileid=f.fileid AND
> f.file_exists=1AND
> f.extension IN(:ext1) COLLATE STRIP_ACCENT

What do you bind to :ext1? This query doesn't make much sense to me. IN
operator requires a subselect or a list of values on the right side. If
you just have a single value in a list, why not write

f.extension = :ext1

Having said that, I believe the correct use of COLLATE clause with IN
operator should look like this:

f.extension COLLATE STRIP_ACCENT IN (value1, value2)

Igor Tandetnik



-
To unsubscribe, send email to [EMAIL PROTECTED]

-

Re: [sqlite] Re: COLLATE without ORDER BY?

[Jonas Sandman]

Thanks for the fast response Igor!

However, I tried this prepared statement:

SELECT f.fileid, f.path, m.title, m.artist, m.album, m.genre, m.comment,
m.track, m.year, m.length, m.bitrate, m.playcount, f.changed, f.size,
m.tagged FROM Files f, Meta m WHERE m.fileid=f.fileid AND f.file_exists=1AND
f.extension IN(:ext1) COLLATE STRIP_ACCENT

There's no difference if I remove the fileid comparison (those are INTEGER)
and the file_exists (also INTEGER). f.extension is a VARCHAR(255).

STRIP_ACCENT is created like this:
sqlite3_create_collation16(m_db, (const char*)L"STRIP_ACCENT",
SQLITE_UTF16LE, NULL, m_stripAccent);

Right now it doesn't do anything (just returns 1) but I would expect the
debugger to halt on my breakpoint there so it isn't called. There are no
errors when I create either the collation or the prepared statement. The
result is returned properly except that my collation isn't called.

If I add an ORDER BY COLLATE STRIP_ACCENT to the select, the collation is
called, but I'll assume that is just for determining the order and not for
the comparison when retrieving the results.

Best regards,
Jonas

On 5/25/07, Igor Tandetnik <[EMAIL PROTECTED]> wrote:


Jonas Sandman <[EMAIL PROTECTED]>
wrote:
> Can I make SQLite use a collation function when making a simple query?
> Before sending the data to LIKE I could override and for example
> strip the accent (*á' would become 'a' and 'é' would become 'e' etc).

Recent versions of SQLite support syntax like this:

select * from tableName
where field = 'blah' collate MYCOLLATION;

That is, you can specify a collation to use for each comparison.

Igor Tandetnik



-
To unsubscribe, send email to [EMAIL PROTECTED]

-

[sqlite] Override LIKE

[Jonas Sandman]

Hello,

I am using LIKE to make a simple query for filename '%path%' and it works
well as long as I am only using ANSI characters, LIKE thinks 'a' and 'A' are
the same. When coming up on Greek characters (i.e.) it doesn't work that
well anymore. Suddenly LIKE is case-sensitive (since LIKE use some binary
comparison perhaps? I duno)

I want LIKE to work just as well for non-ANSI characters, I think the
solution is to override LIKE with sqlite3_create_function but is there any
examples on how to use this function?

Best regards,
Jonas

Re: [sqlite] Still getting "Insertion failed because database isfull." errors

[Jonas Sandman]

Or you can close the database connection if it's idle for a little bit (less
than it takes for it to sleep)?

On 4/18/07, Joel Cochran <[EMAIL PROTECTED]> wrote:


OK, then I won't be worrying about Transactions for SELECT statements, it
doesn't really apply to our application.

And some additionaly confirmation that Christian seems to have been right
on
key: according to the problems reported at the System.Data.SQLite forums,
the problem is most likely due to the retaining of an ope Connection
against
the database residing on removable media.  When the system returns, the
"pointer" to the media is not guaranteed to work again.  In other words,
every time the system shuts down, there is the potential for losing
database
connectivity.  The recommended solution is to move the database to
internal
memory and use the CF card as a backup device.

This also confirms why I can't replicate the problem in DEBUG: the device
never sleeps and the connection is never lost.

I'll keep the list posted.

Joel

On 4/18/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
>
> "Joel Cochran" <[EMAIL PROTECTED]> wrote:
> > should I be using
> > Transactions for SELECT statements?
>
> The only reason to use a transaction around SELECT statements
> is if you want to make sure that the database is not changed
> by another process in between consecutive SELECTs.
>
> It used to be the case that putting multiple SELECTs within
> a transaction would be slightly faster since doing so avoided
> a cache flush after each statement.  But as of version 3.3.16
> that cache flush is avoided regardless of whether or not
> transactions are used so performance should no longer be a
> factor.
>
> --
> D. Richard Hipp <[EMAIL PROTECTED]>
>
>
>
>
-
> To unsubscribe, send email to [EMAIL PROTECTED]
>
>
-
>
>


--
Joel Cochran

Re: [sqlite] Efficiency and Pragmas

[Jonas Sandman]

What is the default setting for PRAGMA cache_size ?
Where is it defined?

On 4/18/07, Samuel R. Neff <[EMAIL PROTECTED]> wrote:


cache is per-connection so if you open and close connections a lot then a
large cache won't help your program.  The command line app is a single
connection so a large cache there will help (although not with the first
queries--only subsequent ones).

Synchronous off is dangerous.  Search the history for details.

Sam

---
We're Hiring! Seeking a passionate developer to join our team building
products. Position is in the Washington D.C. metro area. If interested
contact [EMAIL PROTECTED]

-Original Message-
From: Alberto Simões [mailto:[EMAIL PROTECTED]
Sent: Wednesday, April 18, 2007 3:59 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Efficiency and Pragmas

Hi

I am using these pragmas for efficiency in data loading (I hope)
PRAGMA page_size = 4096;
PRAGMA temp_store = MEMORY;
PRAGMA cache_size = 100;
PRAGMA synchronous = OFF;
PRAGMA count_changes = 0;

The truth is that adding these pragmas my inserts got quite faster.

Now the problem is querying. If I use sqlite3 shell I get answers in
one (two) second(s). If I use my program after these pragmas, I get a
slow query.

Is there any good reason for that?
Thanks

PS: let me know if more detail on the query or database can be handy
for answers.

Cheers
Alberto
--
Alberto Simões



-
To unsubscribe, send email to [EMAIL PROTECTED]


-



-
To unsubscribe, send email to [EMAIL PROTECTED]

-

Re: [sqlite] Still getting "Insertion failed because database is full." errors

[Jonas Sandman]

Either add a trace-log which shows the flow of the program (entering,
exiting methods, database commands accessed).

It's not so simple that when you send the program to him in the field, it's
a release build and when you test you are using a debug build?

On 4/17/07, Joel Cochran <[EMAIL PROTECTED]> wrote:


I've had him sit beside my in my office and attempt to recreate it, both
using his device and mine, but it never happens.  Actually, I did get it
to
happen once on his machine, but I was not connected to my PC, so I
connected
and tried to recreate it through DEBUG but could not.  The last time it
happened in the field, I had him stop using the device and bring it to me
so
that I could see the Stack Trace (which I sent to the list).  With neither
his device nor mine can I recreate the problem in DEBUG.  It is very
frustrating.

Thanks,

Joel


On 4/17/07, Jonas Sandman <[EMAIL PROTECTED]> wrote:
>
> What is the guy on in the field doing that you are not? Are you using
his
> device for the testing?
> Since it takes minutes for him to encounter the error it can't be that
> hard
> to recreate. Follow him around for an hour or so and see how he uses the
> program. It could easily be something he's doing that you aren't...
>
> On 4/17/07, Joel Cochran <[EMAIL PROTECTED]> wrote:
> >
> > The saga continues...
> >
> > I was very excited by the idea that there was something wrong with the
> CF
> > Card.  The theory fits all the facts: it explains why the original
> > database
> > threw unspecified errors, it explains why now SQLite is throwing
errors,
> > it
> > explains why I can't reproduce the problem in house or on my
> machine.  It
> > just seemed to explain everything, so yesterday I went out and bought
a
> > brand-spankin' new SanDisk CF card.  I loaded it up with the database,
> > installed it on my tester's machine, and this morning it went back out
> to
> > the field for testing.
> >
> > Within minutes, he encountered the same error.
> >
> > Now I just don't believe the problem is with the card, so I feel that
I
> am
> > right back at square one.  I'm really at my wits end and don't know
what
> > to
> > do next.  I am going to go ahead and install the database on the
device
> > memory instead of removable media, just to test it out, but I have no
> > faith
> > that it will change anything.  When that fails, I will send the tester
> out
> > with another device entirely, but again I expect the same results.
> >
> > I'm convinced now that the problem is with the application
architecture,
> > but
> > I have no idea what to look at anymore.  I've stared and fiddled with
> this
> > code so much that I'm ready to throw in the towel.  But since I'd like
> to
> > keep my job that isn't an option.  If I had hair, I'd pull it out.
> >
> > Any help at all would be appreciated.
> >
> > --
> > Joel Cochran
> >
> >
> >
> > On 4/13/07, Michael Ruck <[EMAIL PROTECTED]> wrote:
> > >
> > > Unless things have changed recently, the following should still be
> valid
> > > for
> > >
> > > Windows Mobile/Windows CE devices: Usually these devices do not
power
> > off,
> > > but
> > > stay in a standby state where the memory is always powered. Check if
> > > that's
> > > the
> > > case with your system and move as much as possible into RAM or a RAM
> > disk,
> > > if that
> > > feature is provided by the windows mobile kernel built for your
> device.
> > >
> > > If that's not possible, I'd suggest replacing CF cards with micro
> drives
> > -
> > > these
> > > are regular hard drives in a CF card format. I'm not up to date on
> > storage
> > > space,
> > > but should be sufficient for your needs.
> > >
> > > To test the cards I'd put them in a card reader format it and fill
it
> > > completely
> > > up with zeros. When a flash card erases a byte, it sets all bits to
> ones
> > > and
> > > upon
> > > write clears those, which need to be zero. So to test all bits you
> > really
> > > need to
> > > zero out the entire card. This will also give the controller in the
> card
> > a
> > > chance
> > > to remap bad sectors with spares. Finally you determine the file
size
> of
> > > the
> > > card,
> > > when you receive the first write error. This is (approximately) the
> > number
> > > of bytes
> > > the card can store (at that point in time) and falling.
> >

Re: [sqlite] Still getting "Insertion failed because database is full." errors

[Jonas Sandman]

What is the guy on in the field doing that you are not? Are you using his
device for the testing?
Since it takes minutes for him to encounter the error it can't be that hard
to recreate. Follow him around for an hour or so and see how he uses the
program. It could easily be something he's doing that you aren't...

On 4/17/07, Joel Cochran <[EMAIL PROTECTED]> wrote:


The saga continues...

I was very excited by the idea that there was something wrong with the CF
Card.  The theory fits all the facts: it explains why the original
database
threw unspecified errors, it explains why now SQLite is throwing errors,
it
explains why I can't reproduce the problem in house or on my machine.  It
just seemed to explain everything, so yesterday I went out and bought a
brand-spankin' new SanDisk CF card.  I loaded it up with the database,
installed it on my tester's machine, and this morning it went back out to
the field for testing.

Within minutes, he encountered the same error.

Now I just don't believe the problem is with the card, so I feel that I am
right back at square one.  I'm really at my wits end and don't know what
to
do next.  I am going to go ahead and install the database on the device
memory instead of removable media, just to test it out, but I have no
faith
that it will change anything.  When that fails, I will send the tester out
with another device entirely, but again I expect the same results.

I'm convinced now that the problem is with the application architecture,
but
I have no idea what to look at anymore.  I've stared and fiddled with this
code so much that I'm ready to throw in the towel.  But since I'd like to
keep my job that isn't an option.  If I had hair, I'd pull it out.

Any help at all would be appreciated.

--
Joel Cochran



On 4/13/07, Michael Ruck <[EMAIL PROTECTED]> wrote:
>
> Unless things have changed recently, the following should still be valid
> for
>
> Windows Mobile/Windows CE devices: Usually these devices do not power
off,
> but
> stay in a standby state where the memory is always powered. Check if
> that's
> the
> case with your system and move as much as possible into RAM or a RAM
disk,
> if that
> feature is provided by the windows mobile kernel built for your device.
>
> If that's not possible, I'd suggest replacing CF cards with micro drives
-
> these
> are regular hard drives in a CF card format. I'm not up to date on
storage
> space,
> but should be sufficient for your needs.
>
> To test the cards I'd put them in a card reader format it and fill it
> completely
> up with zeros. When a flash card erases a byte, it sets all bits to ones
> and
> upon
> write clears those, which need to be zero. So to test all bits you
really
> need to
> zero out the entire card. This will also give the controller in the card
a
> chance
> to remap bad sectors with spares. Finally you determine the file size of
> the
> card,
> when you receive the first write error. This is (approximately) the
number
> of bytes
> the card can store (at that point in time) and falling.
>
> It seems some cards even return "read errors", when they hit a defective
> sector
> upon read. Maybe the actual error code just gets lost/mangled on the way
> up
> and the
> actual error is just a simple read error ;) I've seen reports about this
> with some
> digital cameras, which would not even let people view the pictures taken
a
> minute
> ago.
>
> Mike
>
> -Ursprüngliche Nachricht-
> Von: John Stanton [mailto:[EMAIL PROTECTED]
> Gesendet: Freitag, 13. April 2007 23:44
> An: sqlite-users@sqlite.org
> Betreff: Re: [sqlite] Still getting "Insertion failed because database
is
> full." errors
>
> You might find some joy in the baby disk drives such as installed in the
> original ipods.
>
> Can you substitute RAM with a battery backup if the memory card is
always
> in
> the device?
>
> Joel Cochran wrote:
> > Thanks John and Dennis,
> > At least now I have something to look at.  I will look into the CF
> > problem next.
> >
> > The database itself gets generated on a PC and then transferred to the
> > CF Card.  During testing and development, this could have been 20-30
> > times a day, constantly erasing and recreating the existing DB.  We
> > have also sent large numbers of JPGs along with the database in the
> > past (there are none now, but have been before).  So these cards have
> > been written over a lot, perhaps that is the problem.
> >
> > I think to test this, I will send the device back to the field with a
> > brand new card and see if the problem persists.  If the user can go
> > several days of normal use without the problem, then I'll be convinced
> > that it is the card.  Out of curiosity I just checked the CF cards
> > we've been using: on the development machine (which has NEVER shown
> > the error) I have a SanDisk CF Card.  On the Testing machine that is
> > having the problem, there is a PNY Technologies CF Card.  I wouldn't
> > be surprised if the SanDisk card isn't simply better than the 

Re: [sqlite] Performance analysis of SQLite statements

[Jonas Sandman]

Anyone know a good bench-marking (preferably free or cheap) which can be
used to benchmark C/C++ code in Windows?

Best regards,
Jonas

On 4/12/07, Samuel R. Neff <[EMAIL PROTECTED]> wrote:



Are there any tools to help analyze the performance of components with a
particular SQLite statement?  I'm aware of the EXPLAIN option which can
show
what VBDE code was used to execute a statement, but afaik there is no way
to
tell the time each step took.

Basically I want to know how long the different components of a single SQL
statement took relative to the whole statement.  Provide for more
fine-grained analysis than just comparing two SQL statements based on
total
execution time.

Thanks,

Sam


---
We're Hiring! Seeking a passionate developer to join our team building
products. Position is in the Washington D.C. metro area. If interested
contact [EMAIL PROTECTED]

-Original Message-
From: Joe Wilson [mailto:[EMAIL PROTECTED]
Sent: Wednesday, April 11, 2007 8:02 PM
To: [EMAIL PROTECTED]
Subject: Re: [sqlite] Select columns & performance

...

Do a lot of benchmarking.



-
To unsubscribe, send email to [EMAIL PROTECTED]

-

Re: [sqlite] Optimize my database

[Jonas Sandman]

On 3/29/07, Rich Shepard <[EMAIL PROTECTED]> wrote:


On Thu, 29 Mar 2007, Jonas Sandman wrote:

> I guess I could split it into more tables, but would that really make
> anything faster?

   It would make upgrades, changes, and searching faster.

> The whole key is the filename, I query for files in specific filepaths,
so
> it doesn't matter if something is mis-spelled. Rather I encourage the
> possibility that it can be.

   So you never query by artist, title, genre, or any other field in the
table?



Yeah I do,  but  the path is always the most important part and the only
thing that is ALWAYS queried.


So what should I use as a primary key then? The path?

   That's what you wrote that you use. I would make the title the primary
key, unless you have the same title by different artists. In that case,
I'd
use a numeric key.



No, I use a full path (path+filename). Title doesn't even exists for all
(and for none before the metadata has been scanned). What do I gain by using
a numeric key instead of the path?


Since one file is one row in the database I rather liked the idea that I
> didn't have to scatter around updating a lot of tables. I am using the
> database instead of a large memory table like I used before.

   Then you might as well use a spreadsheet. With a relational database,
multiple tables are related by foreign keys (which sqlite3 implements as
triggers). If you have an interface other than the sqlite3 command line
editor then you enter information in the appropriate data entry widgets
and
use your middleware code to store/retrieve from appropriate tables.



I just used a binary file which I read up into a memory table before but
figured it was better to use a database to gain some query functionality for
free. I just didn't see the need for it in this case to split it into
several tables (though I know that's how it supposed to be done usually),
unless there is a gain of splitting the data for performance. I always query
all the data, and I always run LIKE on title, artist, album etc. to
determine if a keyword match what's in there.

--

Richard B. Shepard, Ph.D.   |The Environmental Permitting
Applied Ecosystem Services, Inc.|  Accelerator(TM)
<http://www.appl-ecosys.com> Voice: 503-667-4517  Fax:
503-667-8863


-
To unsubscribe, send email to [EMAIL PROTECTED]

-

Re: [sqlite] Optimize my database

[Jonas Sandman]

I should add that I am scanning a folder-tree for the files and are storing
the metadata in the database like that so I don't have to read the actual
files. That's why I used the path as primary key to begin with. No duplicate
entries.

On 3/29/07, Jonas Sandman <[EMAIL PROTECTED]> wrote:


On 3/29/07, Rich Shepard <[EMAIL PROTECTED]> wrote:
>
> On Thu, 29 Mar 2007, Jonas Sandman wrote:
>
> > I am quite new at database and how to set them up properly so keep in
> mind
> > that the obvious might stare into your face but just point it out to
> me
>
> Jonas,
>
>I strongly recommend that you read a book on database design. You
> have a
> single, flat-file database that should be a multiple table, relational
> database (e.g., multiple titles for the same artist: what if you
> mis-spell
> an artist's name? Or that name changes?)


I guess I could split it into more tables, but would that really make
anything faster?
The whole key is the filename, I query for files in specific filepaths, so
it doesn't matter if something is mis-spelled. Rather I encourage the
possibility that it can be.

   Having the path as the primary key means that each database record is
> in a
> different subdirectory, and that they're all unique. What value does the
> path provide?


So what should I use as a primary key then? The path?

   How do you handle having a title (or artist) classified in more than a
> single genre? What if one of those changes, can you be sure you've made
> all
> the changes scattered throughout the database?


Since one file is one row in the database I rather liked the idea that I
didn't have to scatter around updating a lot of tables. I am using the
database instead of a large memory table like I used before.

> CREATE TABLE Files (path VARCHAR(255) PRIMARY KEY, title VARCHAR(255),
> > artist VARCHAR(255), album VARCHAR(255), genre VARCHAR(255), comment
> > VARCHAR(255), track INTEGER, year INTEGER, length INTEGER, bitrate
> INTEGER,
> > playcount INTEGER, changed INTEGER, size INTEGER, tagged INTEGER,
> extension
> > VARCHAR(5), file_exists INTEGER)";
>
>Stop what you're doing and learn how to do it properly and more
> efficiently.
>
> Rich

[sqlite] Optimize my database

[Jonas Sandman]

Hello again,

I am quite new at database and how to set them up properly so keep in mind
that the obvious might stare into your face but just point it out to me :-)

I am having this database with mediafiles (even anyone recall my previous
mails) and I create it like this:

CREATE TABLE Files (path VARCHAR(255) PRIMARY KEY, title VARCHAR(255),
artist VARCHAR(255), album VARCHAR(255), genre VARCHAR(255), comment
VARCHAR(255), track INTEGER, year INTEGER, length INTEGER, bitrate INTEGER,
playcount INTEGER, changed INTEGER, size INTEGER, tagged INTEGER, extension
VARCHAR(5), file_exists INTEGER)";

and I add some indexation like this:
setup = "CREATE INDEX title Files (title, artist, album, genre, comment,
extension)";

I insert data into the database and I can query it kinda fast, but when
there is lots of files (> 15000) the database seems sluggish and doesn't
return the results at all as fast as I would expect (or like!).

Is there something I can do with this that can make it faster?

Best regards,
Jonas

Re: [sqlite] The IN keyword

[Jonas Sandman]

Yes, it seems so.

Still, can this be done without knowing how many extensions there are on
beforehand?
what happens if I make a

SELECT * FROM Files WHERE extension IN (:ext1, :ext2, :ext3, :ext4) and only
bind :ext1 but not the others? An error?

Is it slow building a statement with sqlite3_prepare16_v2 before executing
it?

Best regards,
Jonas

On 3/28/07, Alan Barkway <[EMAIL PROTECTED]> wrote:


On Wed, 28 Mar 2007 08:38:55 +0100, Jonas Sandman
<[EMAIL PROTECTED]> wrote:

[...]
>
> SELECT * FROM Files WHERE extension IN (:extension)

Just a guess here but might it be the case that you need to do this?

SELECT * FROM Files WHERE extension IN (:ext1, :ext2, :ext3)


-
To unsubscribe, send email to [EMAIL PROTECTED]

-

Re: [sqlite] The IN keyword

[Jonas Sandman]

I am binding it like that yes, but Dan said I had to make it like this:

SELECT * FROM Files WHERE extension IN (:extension1, :extension2)

is it possible to do this and maintain a dynamic number of extensions?

On 3/28/07, Brownie <[EMAIL PROTECTED]> wrote:


> > > SELECT * FROM Files WHERE extension IN (:extension)

Do you bind extensions as follows?

sqlite3_bind_text16( stmt, 1, L"'mp3','avi','ogg'" );


-
To unsubscribe, send email to [EMAIL PROTECTED]

-

Re: [sqlite] The IN keyword

[Jonas Sandman]

I am using sqlite3_bind_text16() and it as I said it works fine with the
regular extension=:extension. But maybe it doesn't understand how to parse
the 'mp3','avi' ?

a wchar_t is defined as 'unsigned short' so it's a 16-bit, is it not?

On 3/28/07, Dan Kennedy <[EMAIL PROTECTED]> wrote:



> I am trying to use this query:
>
> SELECT * FROM Files WHERE extension IN ('mp3','avi','ogg');
>
> I am not getting any row back though.
>
> SELECT * FROM Files WHERE extension='mp3' works though.

If this is literally the case, it's probably a bug.

> I am using sqlite3_step to execute a prepared statement looking like
this:
>
> SELECT * FROM Files WHERE extension IN (:extension)
>
> where I bind a wchar_t* to extension.

Using which function - sqlite3_bind_text() or sqlite3_bind_text16()? If
your compiler happens to define wchar_t as a 16-bit type you can get
away with sqlite3_bind_text16(). However many compilers define wchar_t
as 32-bit so such code is not portable.

Dan.





-
To unsubscribe, send email to [EMAIL PROTECTED]

-

[sqlite] The IN keyword

[Jonas Sandman]

Hello,

I am programming Visual C++ in VS2003 on Windows XP SP2 and I am working on
a database which is storing media files of any sort.

Among other things I am storing metadata but in the query that I am having
problems with I only look at one specific column: extension

The extension stores the file extensions of the media file, i.e. 'mp3',
'ogg', 'avi' etc.

I am trying to use this query:

SELECT * FROM Files WHERE extension IN ('mp3','avi','ogg');

I am not getting any row back though.

SELECT * FROM Files WHERE extension='mp3' works though.

I am using sqlite3_step to execute a prepared statement looking like this:

SELECT * FROM Files WHERE extension IN (:extension)

where I bind a wchar_t* to extension.

Anyone know any reason why this wouldn't work with sqlite?

Regards,
Jonas

Re: [sqlite] Re: Re: Re: Re: sqlite3_prepare16 and LIKE

[Jonas Sandman]

On 10/30/06, Igor Tandetnik <[EMAIL PROTECTED]> wrote:


Jonas Sandman <[EMAIL PROTECTED]>
wrote:
> #define SELECT_STATEMENT_TEXTSORT L"SELECT * FROM Data WHERE
> (Data.titleLIKE (SELECT '%%' || ? || '%') OR
> Data.artist LIKE (SELECT '%' || ? || '%') OR Data.album LIKE (SELECT
> '%' || ? || '%') OR Data.genre LIKE (SELECT '%' || ? || '%') OR
> Data.comment LIKE (SELECT '%' || ? || '%') OR Data.path LIKE (SELECT
> '%' || ? '%')) AND Data.path LIKE (SELECT '%' || ? || '%');"

Is this your actual statement, straight from your code? I'm asking
because it has a few syntax errors, and should fail to prepare.

Most surprising is the fact that you have seven parameters, but only
bind six of them. Note how Data.path is mentioned twice in your query.
Can this be the cause of your problem? The last parameter remains
unboud, at which point it is treated as NULL.

Igor Tandetnik



That was exactly it! I've had seven before but my select hasn't been correct
before. Now when I added path as the seventh parameter it worked. I have
path twice since the first six is bound to query (what you search for) and
the seventh is used to only include certain folders.

Anyways, thanks for the help!

/Jonas

-

To unsubscribe, send email to [EMAIL PROTECTED]

-

Re: [sqlite] Re: Re: Re: sqlite3_prepare16 and LIKE

[Jonas Sandman]

#define SELECT_STATEMENT_TEXTSORT L"SELECT * FROM Data WHERE
(Data.titleLIKE (SELECT '%%' || ? || '%') OR
Data.artist LIKE (SELECT '%' || ? || '%') OR Data.album LIKE (SELECT '%' ||
? || '%') OR Data.genre LIKE (SELECT '%' || ? || '%') OR Data.comment LIKE
(SELECT '%' || ? || '%') OR Data.path LIKE (SELECT '%' || ? '%')) AND
Data.path LIKE (SELECT '%' || ? || '%');"

   ERR = sqlite3_prepare16(m_db, SELECT_STATEMENT_TEXTSORT, -1, ,
(const void**));

   wchar_t* errmsg = (wchar_t*) sqlite3_errmsg16(m_db);

   sqlite3_bind_text16(exc, 1, query, -1, SQLITE_STATIC);
   sqlite3_bind_text16(exc, 2, query, -1, SQLITE_STATIC);
   sqlite3_bind_text16(exc, 3, query, -1, SQLITE_STATIC);
   sqlite3_bind_text16(exc, 4, query, -1, SQLITE_STATIC);
   sqlite3_bind_text16(exc, 5, query, -1, SQLITE_STATIC);
   sqlite3_bind_text16(exc, 6, path, -1, SQLITE_STATIC);

   while ((ERR = sqlite3_step(exc)) == SQLITE_ROW)
   {
// process data here like:
   // df->SetFileName((wchar_t*)sqlite3_column_text16(exc, 0));
   }


path and query are wchar_t variables.

Jonas

On 10/30/06, Igor Tandetnik <[EMAIL PROTECTED]> wrote:


Jonas Sandman <[EMAIL PROTECTED]>
wrote:
> Yes, but path isn't part of the query. I ask for query = "", path =
> "C:\MP3\Albums" and I get all the results in the entire database. If
> I set query = "madonna" and same path. I get no results.
>
>> Use parentheses to achieve this result.
>
> Okay, that could've been it. But when I put that parenthesis in
> there, I get no results ever. No matter of the query.

Right. A condition on path is mandatory now, whereas before it could be
false but you would still get results. This tells us that there's
something wrong with the value you bind to path parameter.

When you say "C:\MP3\Albums", are you literally hardcoding this string?
If so, are you aware that backslashes should be escaped in C string
literal?

Show the actual code that calls sqlite3_bind_text[16] . There's
something wrong there.

Igor Tandetnik



-
To unsubscribe, send email to [EMAIL PROTECTED]

-

Re: [sqlite] Re: Re: sqlite3_prepare16 and LIKE

[Jonas Sandman]

On 10/30/06, Igor Tandetnik <[EMAIL PROTECTED]> wrote:


Jonas Sandman <[EMAIL PROTECTED]>
wrote:
>> Data.artist LIKE (SELECT '%' || ? || '%') OR Data.album LIKE (SELECT
>>> '%' || ? || '%') OR Data.genre LIKE (SELECT '%' || ? || '%') OR
>>> Data.comment LIKE (SELECT '%' || ? || '%') AND Data.path LIKE
>>> (SELECT
>>
>> You have AND here, while elsewhere you have OR. Is it intentional?
>
>
> Yes, path is most important. I am searching for some mp3-files in
> different folders. I am looking for
> C:\MP3\Albums and don't want C:\MP3\Singles to show up in the results
> (they do)

They do? Didn't you say that the moment at least one parameter is a
non-empty string, you get no results? I'm confused.



Yes, but path isn't part of the query. I ask for query = "", path =
"C:\MP3\Albums" and I get all the results in the entire database. If I set
query = "madonna" and same path. I get no results.

Be aware that AND has higher precedence than OR. Your condition is

interpreted as

title like x1 OR
artist like x2 OR
album like x3 OR
genre like x4 OR
(comment like x5 AND path like x6)

which apparently is not the effect you were shooting for. You probably
want

(title like x1 OR artist like x2 OR album like x3 OR genre like x4 OR
comment like x5)
 AND path like x6

Use parentheses to achieve this result.



Okay, that could've been it. But when I put that parenthesis in there, I get
no results ever. No matter of the query.


If I set query to anything, I receive NO results.

>>
>> Show a sample of the data in the table, and a set of parameters you
>> bind.
>
>
> An example is path = "C:\MP3\Madonna\Like a virgin.mp3", the title,
> artist etc are empty or 0.

What do you mean "empty or 0"? Binding integer 0 is not the same thing
as binding an empty string.



I meant 0 as in NULL. But I checked and I add an empty string ("") if I
don't have the appropriate tag.

You do in fact have a row in Data table where Data.path =

'C:\MP3\Madonna\Like a virgin.mp3', right?



Yes, there are a lot of  entries in the database. They did show up before I
added the parenthesis :(

Igor Tandetnik


Jonas

Re: [sqlite] Re: sqlite3_prepare16 and LIKE

[Jonas Sandman]

On 10/30/06, Igor Tandetnik <[EMAIL PROTECTED]> wrote:


Jonas Sandman <[EMAIL PROTECTED]>
wrote:
> I have created a database like this:
>
> CREATE TABLE Data (path VARCHAR(512) PRIMARY KEY, title VARCHAR(512),
> artist VARCHAR(512), album VARCHAR(512), length INTEGER, genre
> VARCHAR(512), comment VARCHAR(512), track INTEGER, year INTEGER,
> bitrate INTEGER, playcount INTEGER, changed INTEGER, size INTEGER,
> tagged INTEGER);
>
> and then I want to select only certain elements from it. I've made
> this select:
>
> SELECT * FROM Data WHERE Data.title LIKE (SELECT '%%' || ? || '%') OR

You have two percent signs '%%' here, while I'd expect a single one. Is
it intentional?



I've tried both. I added it since the debugger seemed to drop it in the
preview. But I guess it should only be one.


Data.artist LIKE (SELECT '%' || ? || '%') OR Data.album LIKE (SELECT
> '%' || ? || '%') OR Data.genre LIKE (SELECT '%' || ? || '%') OR
> Data.comment LIKE (SELECT '%' || ? || '%') AND Data.path LIKE (SELECT

You have AND here, while elsewhere you have OR. Is it intentional?



Yes, path is most important. I am searching for some mp3-files in different
folders. I am looking for
C:\MP3\Albums and don't want C:\MP3\Singles to show up in the results (they
do)


? || '%');"

You don't have '%' in front here, while elsewhere you do. Is it
intentional?



Yes,  the path shouldn't  be wild-carded before my search path.
C:\MP3\Albums\Madonna should be found. Adding the '%' before the path
shouldn't make a difference though? Or does it?


which I feed sqlite3_prepare16
>
> then I bind a query to the first five texts and then the path (it's a
> mp3-library) to the sixth ?
>
> I still always retrieve ALL data in the database if query is empty.

What do you mean by "query is empty"? That you bind empty strings to all
parameters?



I bind empty strings yes.


If I set query to anything, I receive NO results.

Show a sample of the data in the table, and a set of parameters you
bind.



An example is path = "C:\MP3\Madonna\Like a virgin.mp3", the title, artist
etc are empty or 0.

Igor Tandetnik




-
To unsubscribe, send email to [EMAIL PROTECTED]

-



Please ask if something more is incomplete.

/Jonas

[sqlite] sqlite3_prepare16 and LIKE

[Jonas Sandman]

I have created a database like this:

CREATE TABLE Data (path VARCHAR(512) PRIMARY KEY, title VARCHAR(512), artist
VARCHAR(512), album VARCHAR(512), length INTEGER, genre VARCHAR(512),
comment VARCHAR(512), track INTEGER, year INTEGER, bitrate INTEGER,
playcount INTEGER, changed INTEGER, size INTEGER, tagged INTEGER);

and then I want to select only certain elements from it. I've made this
select:

SELECT * FROM Data WHERE Data.title LIKE (SELECT '%%' || ? || '%') OR
Data.artist LIKE (SELECT '%' || ? || '%') OR Data.album LIKE (SELECT '%' ||
? || '%') OR Data.genre LIKE (SELECT '%' || ? || '%') OR Data.comment LIKE
(SELECT '%' || ? || '%') AND Data.path LIKE (SELECT ? || '%');"

which I feed sqlite3_prepare16

then I bind a query to the first five texts and then the path (it's a
mp3-library) to the sixth ?

I still always retrieve ALL data in the database if query is empty. If I set
query to anything, I receive NO results.

Anyone know what's wrong?

Regards,
Jonas