Re: [sqlite] Q: Importing text containing nonbreaking spaces

[Coatimundi]

Ah!  I am invoking sqlite in a "DOS" shell which on my machine uses 
CP437, just as you say.

Ironically, this turns out to be a blessing.  The behavior of my data 
source changed (I don't have control over it) from a space to a 
non-breaking space.  If I had been using a a more modern codepage, I 
might not have caught on as soon.

Thank you.  I'm a little wiser now.


Robert Wishlaw wrote:
> On 2/16/08, Coatimundi <[EMAIL PROTECTED]> wrote:
>   
>> I hope this questions isn't a FAQ...
>>
>> I have a tab-delimited text file which I .import using .sep "\t" into a
>> freshly declared table whose columns are of type TEXT.
>>
>> The program which generates the text file exports each "empty" field (if
>> any) as a single non-breaking space, i.e. 0xA0.
>>
>> SQLite 3.5.4 .import's the data which complaining, not surprisingly.
>> But the non-breaking space is rendered in SELECT output as a lower-case
>> acute-a, aka U+00E1.
>>
>> 
> This is a Windows code page problem. Your output data to is being
> displayed as CodePage 437 which displays an acute-a for OxA0. For
> details see
>
> http://en.wikipedia.org/wiki/Code_page_437
>
> Postgresql also had this output problem but solved it with a patch
> discussed around this thread node
>
> http://archives.postgresql.org/pgsql-hackers-win32/2005-01/msg00227.php
>
> Robert Wishlaw
>
>
>   
>> I'm certainly no Unicode expert.  I end up doing an UPDATE on each
>> column to replace á with an empty string.
>>
>> Obviously, I could also preprocess my textfile to remove the 0xA0.  But
>> can someone help me understand what's going on here?
>>
>> Thanks!
>>
>> ___
>> 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] Q: Importing text containing nonbreaking spaces

[Robert Wishlaw]

On 2/16/08, Coatimundi <[EMAIL PROTECTED]> wrote:
>
> I hope this questions isn't a FAQ...
>
> I have a tab-delimited text file which I .import using .sep "\t" into a
> freshly declared table whose columns are of type TEXT.
>
> The program which generates the text file exports each "empty" field (if
> any) as a single non-breaking space, i.e. 0xA0.
>
> SQLite 3.5.4 .import's the data which complaining, not surprisingly.
> But the non-breaking space is rendered in SELECT output as a lower-case
> acute-a, aka U+00E1.
>
This is a Windows code page problem. Your output data to is being
displayed as CodePage 437 which displays an acute-a for OxA0. For
details see

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

Postgresql also had this output problem but solved it with a patch
discussed around this thread node

http://archives.postgresql.org/pgsql-hackers-win32/2005-01/msg00227.php

Robert Wishlaw


> I'm certainly no Unicode expert.  I end up doing an UPDATE on each
> column to replace á with an empty string.
>
> Obviously, I could also preprocess my textfile to remove the 0xA0.  But
> can someone help me understand what's going on here?
>
> Thanks!
>
> ___
> 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] SQLite to xml schema mapping tools

[Sam Carleton]

Are there any tools out there that can look at a SQLite database and
create a XML Schema that I can use with propel ORM?

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

[sqlite] Q: Importing text containing nonbreaking spaces

[Coatimundi]

I hope this questions isn't a FAQ...

I have a tab-delimited text file which I .import using .sep "\t" into a 
freshly declared table whose columns are of type TEXT.

The program which generates the text file exports each "empty" field (if 
any) as a single non-breaking space, i.e. 0xA0.

SQLite 3.5.4 .import's the data which complaining, not surprisingly. 
But the non-breaking space is rendered in SELECT output as a lower-case 
acute-a, aka U+00E1.

I'm certainly no Unicode expert.  I end up doing an UPDATE on each 
column to replace á with an empty string.

Obviously, I could also preprocess my textfile to remove the 0xA0.  But 
can someone help me understand what's going on here?

Thanks!

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

Re: [sqlite] using SQLite for binding blobs.

[Igor Tandetnik]

"C S" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> what do you mean no parameters in SQL?

http://sqlite.org/c3ref/bind_blob.html

It explains parameters

Igor Tandetnik 



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

Re: [sqlite] using SQLite for binding blobs.

[C S]

sorry that should have been insert not insert2, sorry
for the confusion.

what do you mean no parameters in SQL? 

again sorry for sounding noobish -- well fact of the
matter is i am. ive learned as much as i have in  2
days so thanks for the patience and the string
suggestion. 

--- Igor Tandetnik <[EMAIL PROTECTED]> wrote:

> "C S" <[EMAIL PROTECTED]> wrote in message
> news:[EMAIL PROTECTED]
> > insert << "insert into Images values(NULL, '" <<
> Hello
> > World << "')"
> 
> As predicted, you don't have any parameters in your
> SQL, so there's 
> nothing to bind. It's also not clear why you use a
> stream. You probably 
> want something like
> 
> string insert =
> "insert into Images(imageID, messageID) values
> (?, 'Hello World');";
> 
> Question mark '?' is the parameter placeholder.
> 
> > long ID = db.performUpdate(insert2.str(),
> blobarray,
> > 10);
> 
> What's the relationship between variables named
> 'insert' and 'insert2'? 
> You initialize one, but then use the other.
> 
> Igor Tandetnik 
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
>
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 



  

Never miss a thing.  Make Yahoo your home page. 
http://www.yahoo.com/r/hs
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] using SQLite for binding blobs.

[Igor Tandetnik]

"C S" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> insert << "insert into Images values(NULL, '" << Hello
> World << "')"

As predicted, you don't have any parameters in your SQL, so there's 
nothing to bind. It's also not clear why you use a stream. You probably 
want something like

string insert =
"insert into Images(imageID, messageID) values (?, 'Hello World');";

Question mark '?' is the parameter placeholder.

> long ID = db.performUpdate(insert2.str(), blobarray,
> 10);

What's the relationship between variables named 'insert' and 'insert2'? 
You initialize one, but then use the other.

Igor Tandetnik 



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

Re: [sqlite] SQLite Like Query Optimization

[BareFeet]

Hi Kalyani,

> select ID from TableA where column2 like '%test%'  or column4like
> '%test%' or column5 like '%test%' or column6 like '%test%' or column7
> like '%test%'  or column8 like '%test%' order by column3 desc;

As already stated, the like operator can't use indexes if you use  
"or", or start with a wild card.

Is each '%test%' in your example meant to be the same string, or  
different strings? If different, then what exactly is each column  
storing, and what are you trying to search for? Perhaps you could make  
each column more "atomic" by splitting the contents into more columns,  
which you could then search using "=" instead of "like" and so use  
indexes.

Tom
BareFeet

  --
One stop Australian on-line shop for Macs and accessories
http://www.tandb.com.au/forsale/?ml

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

Re: [sqlite] Selecting last item in a unique index

[John Elrick]

Dennis Cote wrote:
> John Elrick wrote:
>   
>> Last rowid.  Rowid will be a key linking to other tables.
>>
>> 
>
> What table will rowid be linked to? Are you linking children rows back 
> to parent rows?
>
>   

Not exactly.  This is a theoretical implementation of WinFS modified to 
our specific needs.  There will be multiple tables, each representing a 
persistent class, with this table and one other serving as common ground 
for an interntal hierarchical representation.



> You should generally use an excplict column as a foreign key (i.e. for 
> linking) to other tables. You can use SQLite's "integer primary key" 
> optimization which stores a column declared that way as the table's rowid.
>
>   

Very true. The final form would contain a separate id field.




>> Because they must locate the MAX value which to my mind requires more 
>> "work" to be done that a simple:
>>
>> select rowid from foo where parent_id = :parentId and child_id = :childId
>>
>> and then stepping the first row and discarding the rest...but I could be 
>> wrong which is why I was asking
>>
>> 
>
> Well if you don't know the max value before hand, then you have to 
> search the table to find it before you can use it to get any other 
> results. Always assume your table is scrambled to an arbitrary row order 
> before each query since SQL works with sets of rows, not ordered tables 
> like a spreadsheet.
>   

Given the usage, normally the inserts would always be ascending (by 
normally, I cannot think of a case where that would not be true right 
now, but I'm not far enough into the design phase to rule it out yet) 
and that "may" mean there is an optimization we could use.  However, I 
wanted to check before trying to cheat.


Thanks,


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

Re: [sqlite] PRIMARY KEY? Date and time datatypes?

[BareFeet]

Hi Gilles,

> 1. what happens if I leave out "PRIMARY KEY" and just use INTEGER?

Um, then it won't be a primary key.

Integer just means a whole number. Primary key means that the database  
will treat that column as the unique identifier for each row. In  
actuality this means that SQLite will treat it the same as the  
"unique" constraint, but you can only have one primary key per table.  
A primary key can be an integer or text column (or presumable real or  
other type too). It's not integer only. If you designate an integer  
column as also being the primary key, then SQLite will auto assign its  
value incrementally each time you insert a new row, unless you assign  
a value explicitly.

See:  http://www.sqlite.org/lang_createtable.html

such as:

>> Specifying a PRIMARY KEY normally just creates a UNIQUE index on  
>> the corresponding columns. However, if primary key is on a single  
>> column that has datatype INTEGER, then that column is used  
>> internally as the actual key of the B-Tree for the table. This  
>> means that the column may only hold unique integer values.


>> If a table does not have an INTEGER PRIMARY KEY column, then the B- 
>> Tree key will be a automatically generated integer. The B-Tree key  
>> for a row can always be accessed using one of the special names  
>> "ROWID", "OID", or "_ROWID_". This is true regardless of whether or  
>> not there is an INTEGER PRIMARY KEY.


> 2. I prefer to use text to save dates and times because they're  
> easier to handle between SQLite and PHP than Julian, but does SQLite  
> handle text dates/times OK, or should I expect problems?

I wouldn't expect any problems using dates in the -MM-DD format.  
But Julian (real) format is the preferred method and uses less memory  
(less bytes) and I suspect is quicker to process. You can easily store  
as julian but pass to PHP as text by using the date function:

select date(myJulianDate)

or to convert from absolute to localtime:

select date(myJulianDate, 'localtime')

either by creating a view in SQLite (and having PHP reference it) or  
else using directly from PHP.

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

Tom
BareFeet

  --
Huge range of Mac and other computer accessories, in Australia
http://www.tandb.com.au/forsale/?ml

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

Re: [sqlite] Selecting last item in a unique index

[Dennis Cote]

John Elrick wrote:
> 
> Last rowid.  Rowid will be a key linking to other tables.
> 

What table will rowid be linked to? Are you linking children rows back 
to parent rows?

You should generally use an excplict column as a foreign key (i.e. for 
linking) to other tables. You can use SQLite's "integer primary key" 
optimization which stores a column declared that way as the table's rowid.

> 
> Because they must locate the MAX value which to my mind requires more 
> "work" to be done that a simple:
> 
> select rowid from foo where parent_id = :parentId and child_id = :childId
> 
> and then stepping the first row and discarding the rest...but I could be 
> wrong which is why I was asking
> 

Well if you don't know the max value before hand, then you have to 
search the table to find it before you can use it to get any other 
results. Always assume your table is scrambled to an arbitrary row order 
before each query since SQL works with sets of rows, not ordered tables 
like a spreadsheet.

HTH
Dennis Cote

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

Re: [sqlite] Selecting last item in a unique index

[Dennis Cote]

Ken wrote:
> select * from foo
> where rowid  =
> (select rowid from foo
> where parent_id = 1
> and child_id =  2
> group by parent_id, child_id
> having revision = max(revision))
> 
> sqlite> explain query plan
>...> select * from foo
>...> where rowid  =
>...> (select rowid from foo
>...> where parent_id = 1
>...> and child_id =  2
>...> group by parent_id, child_id
>...> having revision = max(revision))
>...> ;
> order|from|detail
> 0|0|TABLE foo USING PRIMARY KEY
> 0|0|TABLE foo WITH INDEX FOO_IDX1 ORDER BY
> 
> 
> Would the above be faster due to the use of the PRIMARY key access ?
> 

Ken,

I'm not sure about the general case, but in this case I suspect not 
because of SQLite's index data optimization.

If all the required columns can be returned from an index, then SQLite 
will pull the data from the index and never actually reference the base 
table. That condition is true here since all the column are in the index.

I have copied the explain output for both queries below. My query looks 
more direct and uses the index optimization to return the data. I would 
have to measure the actual execution time to be sure, but I suspect it 
will execute faster as well.


C:\Documents and Settings\DennisC>sqlite3
SQLite version 3.5.6
Enter ".help" for instructions
sqlite>
sqlite> CREATE TABLE if not exists FOO (
...>PARENT_ID INTEGER,
...>CHILD_ID INTEGER,
...>REVISION INTEGER
...> );
sqlite>
sqlite> CREATE UNIQUE INDEX FOO_IDX1 ON FOO (PARENT_ID,CHILD_ID,REVISION);
sqlite>
sqlite> .explain
sqlite> explain
...> select * from foo
...> where parent_id = :parentId
...> and child_id = :childId
...> and revision =
...>  (select max(revision) from foo
...>  where parent_id = :parentId
...>  and child_id = :childId)
...> ;
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Trace  0 0 0 explain
select * from foo
where parent_id = :parentId
and child_id = :childId
and revision =
  (select max(revision) from foo
  where parent_id = :parentId
  and child_id = :childId)
;  00
1 Goto   0 48000
2 OpenRead   2 3 0 keyinfo(3,BINARY,BINARY)  00

3 SetNumColumns  2 4 000
4 Variable   1 2 0 :parentId  00
5 IsNull 2 46000
6 Variable   2 3 0 :childId   00
7 IsNull 3 46000
8 If 7 36000
9 Integer1 7 000
10Null   0 8 000
11Integer1 9 000
12MustBeInt  9 0 000
13IfZero 9 36000
14Null   0 11000
15Null   0 10000
16OpenRead   3 3 0 keyinfo(3,BINARY,BINARY)  00

17SetNumColumns  3 4 000
18Variable   1 130 :parentId  00
19IsNull 1330000
20Variable   2 140 :childId   00
21IsNull 1430000
22MakeRecord 132 12dddb   00
23MoveLe 3 3012   00
24IdxLT  3 3012   00
25Column 3 2 17   00
26CollSeq0 0 0 collseq(BINARY)  00
27AggStep0 1710max(1) 01
28Goto   0 30000
29Prev   3 24000
30Close  3 0 000
31AggFinal   101 0 max(1) 00
32SCopy  1017000
33Move   178 000
34AddImm 9 -1000
35IfZero 9 36000
36SCopy  8 4 000
37IsNull 4 46000
38MakeRecord 2 3 1 dddb   00
39MoveGe 2 46100
40IdxGE  2 46101
41Column 2 0 18   00
42Column 2 1 19   00
43Column 2 2 20   00
44ResultRow  183 000
45Next   2 40000
46Close  2 0 000
47Halt   0 0 000
48Transa

Re: [sqlite] using SQLite for binding blobs.

[C S]

Ken,

  thanks for your response. for cross platform, both
windows and linux are little endian and Macs now use
intel chips, so that is not an issue. i was just
making sure everyone knew i wasnt just developing for
windows. 

about storing them as shorts -- i was under the
impression(at first) that sqlite wanted then data as
bytes but in my code that i just sent, i changed it
via a suggestion from Fin Springs(thanks again). 

please take a look at my code and tell me what is
wrong. i just got thrown into this sql stuff and
trying to learn it on the fly here. much appreciated. 


--- Ken <[EMAIL PROTECTED]> wrote:

> In addition to what Igor mentions:
> 
> If you want this array to be cross platform portable
> from Big endian to Little endian you either need to
> store some type of endianness flag.
> 
> If you dig into the sqlite source you'll find a
> function like sqlite3_put_varint.
> 
> It will convert 32 bit or 64 bit unsinged integers
> into a portable byte string.
> returning the number of bytes back that the string
> consumes. 
> You could then create a struct/type as follows
> struct {
> uint8_t  bytes;
> uint8_t  buf[3];
> }  vint;
> 
> vint.bytes = sqlite3_put_varint (&vint.buf, 
> yourShort );
> 
> then write the entire vint to the BLOB...
> 
> Retrieve the blob as normal. Then unload the shorts
> use the invers function.
> 
> But this seems like a lot of extra work to me.
> 
> Why not create a table that has the integer value
> and insert the data? One row per array element 
> 
> HTH
> 
> C S <[EMAIL PROTECTED]> wrote: hi all i have a
> question regarding Blobs, that is
> storing images into the database. 
> 
> my image by default is an unsigned short array and
> to
> bind blobs it wants a byte array. i am not sure i am
> doing this right at all. is there a universal cross
> platform way for this easy conversion? 
> 
> so far the code i have is:
> .
> char *byteArray = (char *) malloc((size * 2) *
> sizeof(char));
> .
> 
> for(unsigned int i = 0; i < index < size; i++)
> {
>  byteArray[2 * index] = (char)buffer[index];
>  byteArray[(2 * index) + 1] = ( (char) buffer[index]
> >> 1);
> 
> then i prepare the statement which is successful
> then:
> 
> status = sqlite3_bind_blob(statement, 1, byteArray,
> size * 2, free);
> 
> some questions i have: i get an error of '25' back
> from status and looking on the sqlite documention it
> says the 2nd parameter to sql bind was out of range.
> i
> have no idea how the 2nd parameter can be out of
> range. 
> 
> my next question is once you have the blob in the
> database how in the world do you read it back out?
> of
> course when i do read it back out i will need to
> convert it back to a short array to be able to use
> it.
> 
> 
> i am using C++(g++ compiler) on linux. thanks in
> advance! 
> 
> 
>  
>

> Never miss a thing.  Make Yahoo your home page. 
> http://www.yahoo.com/r/hs
> ___
> 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
> 



  

Looking for last minute shopping deals?  
Find them fast with Yahoo! Search.  
http://tools.search.yahoo.com/newsearch/category.php?category=shopping
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] using SQLite for binding blobs.

[C S]

this is to all who have responded so far: thanks so
much for your help so far: as a caveat, i am new to
SQL so bear with me..

here is my code from the main.cpp where i build the
short array: 

ostringstream insert;

unsigned short *blobarray = new unsigned short[10];

//for simple purposes i will make this a small array. 
for(int i = 0; i < 10; i++)
  blobArray[i] = i * 3;

insert << "insert into Images values(NULL, '" << Hello
World << "')"

long ID = db.performUpdate(insert2.str(), blobarray,
10);

...outside of main.

long performUpdate(string update, const unsigned short
* buffer, unsigned long size)
{
  sqlite3_stmt *statement;
  open(filename); //this simply opens the db file,
nothing big
  
  int status = sqlite3_prepare_v2(db, update.c_str(),
-1, &statement, NULL);

  if((status != SQLITE_OK) || (statement == NULL))
cout << "error preparing" << endl;
  else cout << "preparing SQL statement successful" <<
endl;

  status = sqlite3_bind_blob(statement, 1, buffer,
size * sizeof(unsigned short), SQLITE_STATIC);

  if(status != SQLITE_OK)
cout << "status: " << status << " error binding
blob. " << endl;
  else cout << "binding blob successful" << endl;


  while((status = sqlite3_step(statement)) ==
SQLITE_ROW);
  //check for status error like above

  status = sqlite3_finalize(statement);
  //check for status error like above

//  other non sqlite stuff..

} //end performUpdate


do i need to create a column in table Images for the
blob? or does this happen automatically? right now i
have:

create tables Images(ImageID integer primary key,
 messageID text not null);

do i have to anything here for the blob?

i might have to come back to understand more about the
casting but right now i just wanted to show you my
code. i hope it is readable and once again thanks for
your help in advance! 





--- Fin Springs <[EMAIL PROTECTED]> wrote:

> 
> On Feb 15, 2008, at 5:40 PM, C S usmsci-at-yahoo.com
> |sqlite| wrote:
> 
> > hi all i have a question regarding Blobs, that is
> > storing images into the database.
> >
> > my image by default is an unsigned short array and
> to
> > bind blobs it wants a byte array. i am not sure i
> am
> > doing this right at all. is there a universal
> cross
> > platform way for this easy conversion?
> >
> > so far the code i have is:
> > .
> > char *byteArray = (char *) malloc((size * 2) *
> > sizeof(char));
> > .
> >
> > for(unsigned int i = 0; i < index < size; i++)
> > {
> > byteArray[2 * index] = (char)buffer[index];
> > byteArray[(2 * index) + 1] = ( (char)
> buffer[index]
> >>> 1);
> >
> There is probably no reason to copy your data into a
> separate char  
> array. Presuming you have something like:
> 
> unsigned short myImage[];
> size_t size; // the number of elements in your
> myImage array
> 
> - you can just do:
> 
> status = sqlite3_bind_blob(statement, 1, myImage,
> size *  
> sizeof(unsigned short), SQLITE_STATIC or
> SQLITE_TRANSIENT)
> 
> Your choice for the last parameter will depend on
> the life time of  
> 'myImage'; see
> http://sqlite.org/c3ref/bind_blob.html
> 
> > then i prepare the statement which is successful
> then:
> >
> > status = sqlite3_bind_blob(statement, 1,
> byteArray,
> > size * 2, free);
> >
> > some questions i have: i get an error of '25' back
> > from status and looking on the sqlite documention
> it
> > says the 2nd parameter to sql bind was out of
> range. i
> > have no idea how the 2nd parameter can be out of
> > range.
> You would need to share your statement definition.
> Is there at least  
> one '?' token in it?
> >
> >
> > my next question is once you have the blob in the
> > database how in the world do you read it back out?
> of
> > course when i do read it back out i will need to
> > convert it back to a short array to be able to use
> it.
> >
> Use sqlite3_column_blob to get the pointer and
> sqlite3_column_bytes to  
> know how much data there is (in bytes). You can just
> cast the 'const  
> void *' pointer that you get back to a 'const
> unsigned short *' and  
> divide the number of bytes there is by
> sizeof(unsigned short) (ie 2)  
> to get the number of elements.
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
>
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 



  

Never miss a thing.  Make Yahoo your home page. 
http://www.yahoo.com/r/hs
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] expose sqlite3_put_Varint & sqlite3_get_varint

[drh]

Ken <[EMAIL PROTECTED]> wrote:
> Any reason why the functions above could not be exposed as part of the 
> sqlite3 api suite?
> 

The usually reasons apply:  To expose them as part of the sqlite3 api
suite means that they would have to be supported, documented, and 
tested forever and it also means that we never be allowed to change
their interface again in the future.

--
D. Richard Hipp <[EMAIL PROTECTED]>

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

Re: [sqlite] Selecting last item in a unique index

[John Elrick]

Dennis Cote wrote:
> John Elrick wrote:
>   
>> I have a question for the SQLite experts here as to the most efficient 
>> way to retrieve the following:
>>
>> 
SNIP
> John,
>
> Do you mean the last rowid, or do you really mean the last revision?
>
>   

Last rowid.  Rowid will be a key linking to other tables.

SNIP

>
>   
>> My concern is that the queries above are suboptimal and will become a 
>> performance impediment to the application.  Can anyone recommend a more 
>> efficient method of retrieving the data?
>>
>> 
>
> What makes you think these queries are sub-optimal? They will both use 
> the index to find the correct revision number, and then use the index to 
> find the matching row.

Because they must locate the MAX value which to my mind requires more 
"work" to be done that a simple:

select rowid from foo where parent_id = :parentId and child_id = :childId

and then stepping the first row and discarding the rest...but I could be 
wrong which is why I was asking


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

Re: [sqlite] SQLite Like Query Optimization

[drh]

"Kalyani Phadke" <[EMAIL PROTECTED]> wrote:
> I am using Sqlite 3 as my database. One of my table contains 1280010
> rows. Db file size is 562,478KB. I am running DB on Windows XP pro-P4
> CPU 3.20GHz 3.19Hz ,2.00GB of RAM )
>  
> CREATE TABLE TableA 
> (
> ID  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
>column1 VARCHAR (50) NOT NULL,
>column2 VARCHAR (50)  NOT NULL,
>column3 TIMESTAMP NOT NULL DEFAULT  (CURRENT_TIMESTAMP),
>column4  VARCHAR (128) NULL, 
>column5 VARCHAR (255)NULL,
>column6 VARCHAR ( 128 )   NULL,
>column7  TEXT NULL,
>column8  TEXT NULL
> )
> I have select query which looks like
> select ID from TableA where column2 like '%test%'  or column4like
> '%test%' or column5 like '%test%' or column6 like '%test%' or column7
> like '%test%'  or column8 like '%test%' order by column3 desc;
>  

LIKE operators cannot use indices unless the index is
case insensitive.  Use GLOB for case sensitive fields.

LIKE and GLOB operators cannot use indices if the pattern
begins with a wildcard.  

Nothing in SQLite will use an index if you are connecting
terms using OR.

It looks like what you really want to use here is a full-text
index.  Please read about the FTS3 support in SQLite.  That
seems to be what you are trying to accomplish.

--
D. Richard Hipp <[EMAIL PROTECTED]>

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

[sqlite] expose sqlite3_put_Varint & sqlite3_get_varint

[Ken]

Any reason why the functions above could not be exposed as part of the 
sqlite3 api suite?

They sure are handy when writing platform portable code, addressing endian 
conversions.

Thanks,
Ken



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

Re: [sqlite] SQLite Like Query Optimization

[Ken]

I'm not 100% sure but 

 Try splitting your query out into 6  different selects, I think the OR clause 
is the problem...

select ID from TableA where column2 like '%test%'
 union
select ID from TableA where column4 like '%test%' 


HTH

Kalyani Phadke <[EMAIL PROTECTED]> wrote: I am using Sqlite 3 as my database. 
One of my table contains 1280010
rows. Db file size is 562,478KB. I am running DB on Windows XP pro-P4
CPU 3.20GHz 3.19Hz ,2.00GB of RAM )
 
CREATE TABLE TableA 
(
ID  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
   column1 VARCHAR (50) NOT NULL,
   column2 VARCHAR (50)  NOT NULL,
   column3 TIMESTAMP NOT NULL DEFAULT  (CURRENT_TIMESTAMP),
   column4  VARCHAR (128) NULL, 
   column5 VARCHAR (255)NULL,
   column6 VARCHAR ( 128 )   NULL,
   column7  TEXT NULL,
   column8  TEXT NULL
)
I have select query which looks like
select ID from TableA where column2 like '%test%'  or column4like
'%test%' or column5 like '%test%' or column6 like '%test%' or column7
like '%test%'  or column8 like '%test%' order by column3 desc;
 
Without Index 
1000rows in 8.103745seconds
 
With Index on column3 
1000 row(s) affected in 8.21403 second(s).
 
With Index on column3 ,column4  , column5  , column6  , column7  ,
column8  
1000 row(s) affected in 8.007997 second(s).
 
So after adding index there is no improvement on the query execution. I
found that The GLOB and LIKE operators are expensive in SQLite because
they can't make use of an index. One reason is that these are
implemented by user functions, which can be overridden, so the parser
has no way of knowing how they might behave in that case. This forces a
full scan of the table for the column being matched against, even if
that column has an index.
 
Is there any way I can improve Query performance?
 
Appreciate your help.
 
Thanks
 
 
 
___
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] using SQLite for binding blobs.

[Ken]

In addition to what Igor mentions:

If you want this array to be cross platform portable from Big endian to Little 
endian you either need to store some type of endianness flag.

If you dig into the sqlite source you'll find a function like 
sqlite3_put_varint.

It will convert 32 bit or 64 bit unsinged integers into a portable byte string.
returning the number of bytes back that the string consumes. 
You could then create a struct/type as follows
struct {
uint8_t  bytes;
uint8_t  buf[3];
}  vint;

vint.bytes = sqlite3_put_varint (&vint.buf,  yourShort );

then write the entire vint to the BLOB...

Retrieve the blob as normal. Then unload the shorts use the invers function.

But this seems like a lot of extra work to me.

Why not create a table that has the integer value and insert the data? One row 
per array element 

HTH

C S <[EMAIL PROTECTED]> wrote: hi all i have a question regarding Blobs, that is
storing images into the database. 

my image by default is an unsigned short array and to
bind blobs it wants a byte array. i am not sure i am
doing this right at all. is there a universal cross
platform way for this easy conversion? 

so far the code i have is:
.
char *byteArray = (char *) malloc((size * 2) *
sizeof(char));
.

for(unsigned int i = 0; i < index < size; i++)
{
 byteArray[2 * index] = (char)buffer[index];
 byteArray[(2 * index) + 1] = ( (char) buffer[index]
>> 1);

then i prepare the statement which is successful then:

status = sqlite3_bind_blob(statement, 1, byteArray,
size * 2, free);

some questions i have: i get an error of '25' back
from status and looking on the sqlite documention it
says the 2nd parameter to sql bind was out of range. i
have no idea how the 2nd parameter can be out of
range. 

my next question is once you have the blob in the
database how in the world do you read it back out? of
course when i do read it back out i will need to
convert it back to a short array to be able to use it.


i am using C++(g++ compiler) on linux. thanks in
advance! 


  

Never miss a thing.  Make Yahoo your home page. 
http://www.yahoo.com/r/hs
___
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] PRIMARY KEY? Date and time datatypes?

[Gilles Ganault]

Hello

I have a couple of questions on using SQLite3:

1. what happens if I leave out "PRIMARY KEY" and just use INTEGER?

2. I prefer to use text to save dates and times because they're easier
to handle between SQLite and PHP than Julian, but does SQLite handle
text dates/times OK, or should I expect problems?

Thank you.

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

Re: [sqlite] using SQLite for binding blobs.

[Fin Springs]

On Feb 15, 2008, at 5:40 PM, C S usmsci-at-yahoo.com |sqlite| wrote:

> hi all i have a question regarding Blobs, that is
> storing images into the database.
>
> my image by default is an unsigned short array and to
> bind blobs it wants a byte array. i am not sure i am
> doing this right at all. is there a universal cross
> platform way for this easy conversion?
>
> so far the code i have is:
> .
> char *byteArray = (char *) malloc((size * 2) *
> sizeof(char));
> .
>
> for(unsigned int i = 0; i < index < size; i++)
> {
> byteArray[2 * index] = (char)buffer[index];
> byteArray[(2 * index) + 1] = ( (char) buffer[index]
>>> 1);
>
There is probably no reason to copy your data into a separate char  
array. Presuming you have something like:

unsigned short myImage[];
size_t size; // the number of elements in your myImage array

- you can just do:

status = sqlite3_bind_blob(statement, 1, myImage, size *  
sizeof(unsigned short), SQLITE_STATIC or SQLITE_TRANSIENT)

Your choice for the last parameter will depend on the life time of  
'myImage'; see http://sqlite.org/c3ref/bind_blob.html

> then i prepare the statement which is successful then:
>
> status = sqlite3_bind_blob(statement, 1, byteArray,
> size * 2, free);
>
> some questions i have: i get an error of '25' back
> from status and looking on the sqlite documention it
> says the 2nd parameter to sql bind was out of range. i
> have no idea how the 2nd parameter can be out of
> range.
You would need to share your statement definition. Is there at least  
one '?' token in it?
>
>
> my next question is once you have the blob in the
> database how in the world do you read it back out? of
> course when i do read it back out i will need to
> convert it back to a short array to be able to use it.
>
Use sqlite3_column_blob to get the pointer and sqlite3_column_bytes to  
know how much data there is (in bytes). You can just cast the 'const  
void *' pointer that you get back to a 'const unsigned short *' and  
divide the number of bytes there is by sizeof(unsigned short) (ie 2)  
to get the number of elements.

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

Re: [sqlite] using SQLite for binding blobs.

[Igor Tandetnik]

C S <[EMAIL PROTECTED]> wrote:
> hi all i have a question regarding Blobs, that is
> storing images into the database.
>
> my image by default is an unsigned short array and to
> bind blobs it wants a byte array. i am not sure i am
> doing this right at all. is there a universal cross
> platform way for this easy conversion?

unsigned short* image;
char* blob = reinterpret_cast(image);

> so far the code i have is:
> .
> char *byteArray = (char *) malloc((size * 2) *
> sizeof(char));

Curious that you bothered to write sizeof(char), which is always 1 by 
definition, but just hardcoded 2 instead of sizeof(unsigned short).

Anyway, you don't need to allocate new memory or do any copying.

> then i prepare the statement which is successful then:
>
> status = sqlite3_bind_blob(statement, 1, byteArray,
> size * 2, free);
>
> some questions i have: i get an error of '25' back
> from status and looking on the sqlite documention it
> says the 2nd parameter to sql bind was out of range. i
> have no idea how the 2nd parameter can be out of
> range.

You don't have any parameter placeholders in your statement. Show your 
sqlite3_prepare call, in particular what SQL you pass to it.

> my next question is once you have the blob in the
> database how in the world do you read it back out?

sqlite3_column_blob

Igor Tandetnik 



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

[sqlite] SQLite Like Query Optimization

[Kalyani Phadke]

I am using Sqlite 3 as my database. One of my table contains 1280010
rows. Db file size is 562,478KB. I am running DB on Windows XP pro-P4
CPU 3.20GHz 3.19Hz ,2.00GB of RAM )
 
CREATE TABLE TableA 
(
ID  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
   column1 VARCHAR (50) NOT NULL,
   column2 VARCHAR (50)  NOT NULL,
   column3 TIMESTAMP NOT NULL DEFAULT  (CURRENT_TIMESTAMP),
   column4  VARCHAR (128) NULL, 
   column5 VARCHAR (255)NULL,
   column6 VARCHAR ( 128 )   NULL,
   column7  TEXT NULL,
   column8  TEXT NULL
)
I have select query which looks like
select ID from TableA where column2 like '%test%'  or column4like
'%test%' or column5 like '%test%' or column6 like '%test%' or column7
like '%test%'  or column8 like '%test%' order by column3 desc;
 
Without Index 
1000rows in 8.103745seconds
 
With Index on column3 
1000 row(s) affected in 8.21403 second(s).
 
With Index on column3 ,column4  , column5  , column6  , column7  ,
column8  
1000 row(s) affected in 8.007997 second(s).
 
So after adding index there is no improvement on the query execution. I
found that The GLOB and LIKE operators are expensive in SQLite because
they can't make use of an index. One reason is that these are
implemented by user functions, which can be overridden, so the parser
has no way of knowing how they might behave in that case. This forces a
full scan of the table for the column being matched against, even if
that column has an index.
 
Is there any way I can improve Query performance?
 
Appreciate your help.
 
Thanks
 
 
 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How to compile SQLite for Windows 32bit & 64bit?

[Jim Harkins]

Is there a good tutorial that shows how to compile SQLite for both
Windows 32 bit & 64bit?

 

If ICU is required then I need the files to be statically linked rather
than having any external dependencies.

 

Anyone know of a good tutorial for building  both a 32bit & 64bit DLL
with any decencies statically linked?

 

Thanks,

Jim

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

[sqlite] using SQLite for binding blobs.

[C S]

hi all i have a question regarding Blobs, that is
storing images into the database. 

my image by default is an unsigned short array and to
bind blobs it wants a byte array. i am not sure i am
doing this right at all. is there a universal cross
platform way for this easy conversion? 

so far the code i have is:
.
char *byteArray = (char *) malloc((size * 2) *
sizeof(char));
.

for(unsigned int i = 0; i < index < size; i++)
{
 byteArray[2 * index] = (char)buffer[index];
 byteArray[(2 * index) + 1] = ( (char) buffer[index]
>> 1);

then i prepare the statement which is successful then:

status = sqlite3_bind_blob(statement, 1, byteArray,
size * 2, free);

some questions i have: i get an error of '25' back
from status and looking on the sqlite documention it
says the 2nd parameter to sql bind was out of range. i
have no idea how the 2nd parameter can be out of
range. 

my next question is once you have the blob in the
database how in the world do you read it back out? of
course when i do read it back out i will need to
convert it back to a short array to be able to use it.


i am using C++(g++ compiler) on linux. thanks in
advance! 


  

Never miss a thing.  Make Yahoo your home page. 
http://www.yahoo.com/r/hs
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite in memory DB: hashing

[Ken]

I'm curious if the authors of sqlite have given any consideration to the merits 
of using a Hash index to retrieve data for In memory Databases ?

Thanks,
Ken



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

Re: [sqlite] Selecting last item in a unique index

[Ken]

select * from foo
where rowid  =
(select rowid from foo
where parent_id = 1
and child_id =  2
group by parent_id, child_id
having revision = max(revision))

sqlite> explain query plan
   ...> select * from foo
   ...> where rowid  =
   ...> (select rowid from foo
   ...> where parent_id = 1
   ...> and child_id =  2
   ...> group by parent_id, child_id
   ...> having revision = max(revision))
   ...> ;
order|from|detail
0|0|TABLE foo USING PRIMARY KEY
0|0|TABLE foo WITH INDEX FOO_IDX1 ORDER BY


Would the above be faster due to the use of the PRIMARY key access ?

Regards,
Ken



Dennis Cote <[EMAIL PROTECTED]> wrote: John Elrick wrote:
> I have a question for the SQLite experts here as to the most efficient 
> way to retrieve the following:
> 
> Say we have a table:
> 
> CREATE TABLE FOO (
>   PARENT_ID INTEGER,
>   CHILD_ID INTEGER,
>   REVISION INTEGER
> );
> 
> CREATE UNIQUE INDEX FOO_IDX1 ON FOO (PARENT_ID,CHILD_ID,REVISION);
> 
> note that we could use
> 
> CREATE UNIQUE INDEX FOO_IDX1 ON FOO (PARENT_ID,CHILD_ID,REVISION DESC);
> 
> however, the current documentation implies that DESC does not improve 
> performance in the below.
> 
> If we have the following data:
> 
> INSERT INTO FOO VALUES (1,2,1);
> INSERT INTO FOO VALUES (1,2,2);
> INSERT INTO FOO VALUES (1,2,3);
> 
> My goal is two fold.
> 
> 1.  Given a parent_id and a child_id, retrieve the last rowid
> 
> select max(rowid) from foo where parent_id = :parentId and child_id = 
> :childId
> 

John,

Do you mean the last rowid, or do you really mean the last revision?

If you mean the latter then this should do it.

select * from foo
where parent_id = :parentId
and child_id = :childId
and revision =
 (select max(revision) from foo
 where parentId = :parentId
 and child_id = :childId)

> 2.  Given a parent_id, a child_id, and a revision, retrieve the last 
> rowid before that revision:
> 
> select max(rowid) from foo where parent_id = :parentId and child_id = 
> :childId and revision < :revision
> 

Similarly, for the second case.

select * from foo
where parent_id = :parentId
and child_id = :childId
and revision =
 (select max(revision) from foo
 where parentId = :parentId
 and child_id = :childId
 and revision < :revision)

You need to be careful not to assume any particular ordering of the rows 
in the table.

> My concern is that the queries above are suboptimal and will become a 
> performance impediment to the application.  Can anyone recommend a more 
> efficient method of retrieving the data?
> 

What makes you think these queries are sub-optimal? They will both use 
the index to find the correct revision number, and then use the index to 
find the matching row.

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



Dennis Cote <[EMAIL PROTECTED]> wrote: John Elrick wrote:
> I have a question for the SQLite experts here as to the most efficient 
> way to retrieve the following:
> 
> Say we have a table:
> 
> CREATE TABLE FOO (
>   PARENT_ID INTEGER,
>   CHILD_ID INTEGER,
>   REVISION INTEGER
> );
> 
> CREATE UNIQUE INDEX FOO_IDX1 ON FOO (PARENT_ID,CHILD_ID,REVISION);
> 
> note that we could use
> 
> CREATE UNIQUE INDEX FOO_IDX1 ON FOO (PARENT_ID,CHILD_ID,REVISION DESC);
> 
> however, the current documentation implies that DESC does not improve 
> performance in the below.
> 
> If we have the following data:
> 
> INSERT INTO FOO VALUES (1,2,1);
> INSERT INTO FOO VALUES (1,2,2);
> INSERT INTO FOO VALUES (1,2,3);
> 
> My goal is two fold.
> 
> 1.  Given a parent_id and a child_id, retrieve the last rowid
> 
> select max(rowid) from foo where parent_id = :parentId and child_id = 
> :childId
> 

John,

Do you mean the last rowid, or do you really mean the last revision?

If you mean the latter then this should do it.

select * from foo
where parent_id = :parentId
and child_id = :childId
and revision =
 (select max(revision) from foo
 where parentId = :parentId
 and child_id = :childId)

> 2.  Given a parent_id, a child_id, and a revision, retrieve the last 
> rowid before that revision:
> 
> select max(rowid) from foo where parent_id = :parentId and child_id = 
> :childId and revision < :revision
> 

Similarly, for the second case.

select * from foo
where parent_id = :parentId
and child_id = :childId
and revision =
 (select max(revision) from foo
 where parentId = :parentId
 and child_id = :childId
 and revision < :revision)

You need to be careful not to assume any particular ordering of the rows 
in the table.

> My concern is that the queries above are suboptimal and will become a 
> performance impediment to the application.  Can anyone recommend a more 
> efficient method of retrieving the data?
> 

What makes you think these queries are sub-optimal? They will both use 
the index to find the correct revision number, and then use the ind

Re: [sqlite] Selecting last item in a unique index

[Dennis Cote]

John Elrick wrote:
> I have a question for the SQLite experts here as to the most efficient 
> way to retrieve the following:
> 
> Say we have a table:
> 
> CREATE TABLE FOO (
>   PARENT_ID INTEGER,
>   CHILD_ID INTEGER,
>   REVISION INTEGER
> );
> 
> CREATE UNIQUE INDEX FOO_IDX1 ON FOO (PARENT_ID,CHILD_ID,REVISION);
> 
> note that we could use
> 
> CREATE UNIQUE INDEX FOO_IDX1 ON FOO (PARENT_ID,CHILD_ID,REVISION DESC);
> 
> however, the current documentation implies that DESC does not improve 
> performance in the below.
> 
> If we have the following data:
> 
> INSERT INTO FOO VALUES (1,2,1);
> INSERT INTO FOO VALUES (1,2,2);
> INSERT INTO FOO VALUES (1,2,3);
> 
> My goal is two fold.
> 
> 1.  Given a parent_id and a child_id, retrieve the last rowid
> 
> select max(rowid) from foo where parent_id = :parentId and child_id = 
> :childId
> 

John,

Do you mean the last rowid, or do you really mean the last revision?

If you mean the latter then this should do it.

select * from foo
where parent_id = :parentId
and child_id = :childId
and revision =
(select max(revision) from foo
where parentId = :parentId
and child_id = :childId)

> 2.  Given a parent_id, a child_id, and a revision, retrieve the last 
> rowid before that revision:
> 
> select max(rowid) from foo where parent_id = :parentId and child_id = 
> :childId and revision < :revision
> 

Similarly, for the second case.

select * from foo
where parent_id = :parentId
and child_id = :childId
and revision =
(select max(revision) from foo
where parentId = :parentId
and child_id = :childId
and revision < :revision)

You need to be careful not to assume any particular ordering of the rows 
in the table.

> My concern is that the queries above are suboptimal and will become a 
> performance impediment to the application.  Can anyone recommend a more 
> efficient method of retrieving the data?
> 

What makes you think these queries are sub-optimal? They will both use 
the index to find the correct revision number, and then use the index to 
find the matching row.

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

[sqlite] Selecting last item in a unique index

[John Elrick]

I have a question for the SQLite experts here as to the most efficient 
way to retrieve the following:

Say we have a table:

CREATE TABLE FOO (
  PARENT_ID INTEGER,
  CHILD_ID INTEGER,
  REVISION INTEGER
);

CREATE UNIQUE INDEX FOO_IDX1 ON FOO (PARENT_ID,CHILD_ID,REVISION);

note that we could use

CREATE UNIQUE INDEX FOO_IDX1 ON FOO (PARENT_ID,CHILD_ID,REVISION DESC);

however, the current documentation implies that DESC does not improve 
performance in the below.

If we have the following data:

INSERT INTO FOO VALUES (1,2,1);
INSERT INTO FOO VALUES (1,2,2);
INSERT INTO FOO VALUES (1,2,3);

My goal is two fold.

1.  Given a parent_id and a child_id, retrieve the last rowid

select max(rowid) from foo where parent_id = :parentId and child_id = 
:childId

2.  Given a parent_id, a child_id, and a revision, retrieve the last 
rowid before that revision:

select max(rowid) from foo where parent_id = :parentId and child_id = 
:childId and revision < :revision

My concern is that the queries above are suboptimal and will become a 
performance impediment to the application.  Can anyone recommend a more 
efficient method of retrieving the data?


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

Re: [sqlite] Blob incremental i/o via Python

[Brian Smith]

Norman Young wrote:
> D. Richard Hipp wrote:
> > It turns out to be faster to read images directly out of
> > SQLite BLOBs until the image gets up to about 15KB on
> > windows and up to about 60KB on linux.  
> > And even for much larger images, the performance difference
> > between reading from SQLite and reading from a file is not
> > that great, so it is a reasonable thing to do to read from
> > SQLite if transactions are important to you or if it is
> > just more convenient.

> Reliability is paramount. If there is no way to achieve 
> transactional semantics using files outside of SQLite, then 
> any discussions about performance and programmer convenience 
> are purely academic. The application must behave deterministically.

I have done this as folllows:

1. Every file is given a unique name using mkstemp(). This is not fast.

2. Every file is written only once, and never overwritten. If a file
needs to be replaced, a new file (with a new name) is created, and the
link to the file in the SQLite database is updated.

3. The file must be fsync()'d, and its directory must be fsync()'d,
before any attempt is made to update the links that are in the SQLite
database. Then, the SQLite database is committed, which results in at
least two more fdatasync()s. So, basically, you have to call the slowest
filesystem operation four times every time you update a file.

4. File deletion is slow, so I have a seperate process that periodically
runs to delete all the replaced files asynchronously with any requests.

All of this makes inserting/updating files very slow compared to just
stuffing them into SQLite. It will only have a raw performance advantage
over storing files in SQLite when the files are large, when I cache file
descriptors (so I don't have to repeatedly call open() to read files),
and when I am using optimized I/O calls like sendfile(), or mmap()ing
into shared memory. It *does* have the advantage that you can
significantly reduce the amount of time the database is locked when
files are inserted/updated.

- Brian

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

Re: [sqlite] Malformed database schema

[Rich Shepard]

On Fri, 15 Feb 2008, Wojciech Kocjan wrote:

> Do I have to make anything like that again? I mean: migration from 3.2 to
> 3.4?

   I suggest that you upgrade both to 3.5.5. Then see if you have any
problems. The versions you have installed are both quite old.

Rich

-- 
Richard B. Shepard, Ph.D.   |  IntegrityCredibility
Applied Ecosystem Services, Inc.|Innovation
 Voice: 503-667-4517  Fax: 503-667-8863
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Malformed database schema

[Wojciech Kocjan]

Hello,

I'm not experienced user of SQLite and i have small problem with my 
database. I'm using SQLite database for my website (through PHP PDO).

On remote Server everything is OK. I'm using:

sqlite3 database.db

and after '.tables' command I see all tables.

Problems are on localhost:

sqlite3 database.db
SQLite version 3.4.2
Enter ".help" for instructions
sqlite> .tables
Error: malformed database schema - near "STATEMENT": syntax error

Database.db it's of course the same file. I suppose problem is with 
different SQLite versions. On remote host it's 3.2.1 and on my 
localhost: 3.4.2.

database.db was firstly 2.8 SQLITE database. A couple months ago I made 
migration by typing:

sqlite old_database.db .dump | sqlite3 database.db

Do I have to make anything like that again? I mean: migration from 3.2 
to 3.4?


Thank You for help.


-- 
Best Regards

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

Re: [sqlite] locking problem

[John Stanton]

Do you have posix-locks enabled with your glusterfs?  If you have use 
regular Sqlite locking logic and hope that the locks work properly.

Lanyi Zoltan wrote:
> Hello!
> 
> I have shared disc system with glusterfs.
> When i read or write on this disc system then i get error: database is locked
> But i not get error if i'd like read or write another program. (php write to 
> text file, etc.)
> 
> I have sqlite3.
> 
> Somebody help me?
> 
> Thanks,
> Zoltan
> 
> 
> A szexjátékoktól a routergyorsításig – a legolvasottabb tartalmak!
> http://ad.adverticum.net/b/cl,1,6022,247493,293836/click.prm
> 
>  
> 
> ___
> 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] locking problem

[Lanyi Zoltan]

Hello!

I have shared disc system with glusterfs.
When i read or write on this disc system then i get error: database is locked
But i not get error if i'd like read or write another program. (php write to 
text file, etc.)

I have sqlite3.

Somebody help me?

Thanks,
Zoltan


A szexjátékoktól a routergyorsításig – a legolvasottabb tartalmak!
http://ad.adverticum.net/b/cl,1,6022,247493,293836/click.prm

 

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