Re: [sqlite] Indexing and Search speed
> select types.Track,types.URL from ALBUM inner join (select * from MUSIC > where Artist_Id =?) types on ALBUM.AlbumId=types.Album_Id order by > ALBUM.YomiAlbumName ; How does the subquery work in this statement? I thought subqueries could only retrieve a single column. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite catalog - datebase with pictures
Hello Lukasz, to insert a picture (or any binary data for that matter), you can read the data from the file and use sqlite3_bind_blob to insert (or update) it. When selecting it from the database, use sqlite_column_blob to retrieve the data and sqlite3_column_bytes to get the length. An alternative is to convert the binary data to a string before storing it in the database. For this you can use base64 - encoding or sonething like this. See http://www.sqlite.org/capi3ref.html#sqlite3_bind_blob http://www.sqlite.org/capi3ref.html#sqlite3_column_blob and the sqlite3_blob* - functions. On how to design your database, that would depend on what you are planning to to and how to use the data. Perhaps you could provide more information. Hope this helps, Martin lukasz p. wrote: > Hi. > I would like to write an application wich helps me to manage with my cd/dvd > collections. It will scan all files on cd/dvd add it to a datebase and it > could also insert pictures (cd/dvd covers). My questions are: > - is there any possiblity to add in sqlite datebase pictures like gif, jpg ? > - how to design datebase to scan cd/dvd and make tree layout (Folders, files > etc.) ? > Thx for any sugestions. > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] BCD representation of floats
John, I'm not sure what you mean. What do 64-bit SQLite integers do for me? Oracle 8 uses the BCD, and I'm trying to temporarily match that. I'm not looking for sufficient precision, I'm looking for the exact same 64 bit floating point number as from Oracle. Liam On Wed, Mar 26, 2008 at 9:25 PM, John Stanton <[EMAIL PROTECTED]> wrote: > We actually added this type of capability to Sqlite (actually fixed > point display format numbers), but it may be unnecessary in your case. > Instead of representing integers as BCD how about using the 64 bit > Sqlite integers? You may have sufficient precision. The COBOL-style > COMP3 integers are pretty much obsolete these days. > > > > Liam Healy wrote: > > I am porting a numerical application from Oracle to SQLite. For the > > most part, I have been successful, but there are slight disagreements > > in the floating point number results. I have traced this back and > > found a problem. According to > > http://articles.techrepublic.com.com/5100-22_11-5224536.html, Oracle > > by default stores floats as binary-coded decimal (BCD), and not > > IEEE754 binary. SQLite on the other hand does > > http://www.sqlite.org/datatype3.html: "REAL. The value is a floating > > point value, stored as an 8-byte IEEE floating point number." For the > > results of the application, it makes no difference how the numbers are > > stored -- the differences in the 15th significant figure are > > irrelevant. However, I would like to insure that there no > > disagreements in the way the two applications operate (other than the > > storage of floating point numbers), and for that I temporarily need > > exact agreement on input numbers. I cannot change the Oracle > > application, so I'm wondering if there's a wrapper or something I can > > put around sqlite calls (or better, a mode that I can put sqlite in) > > that will reproduce exactly the BCD format of Oracle. > > > > Thanks for any guidance. > > > > Liam > > > > ___ > > 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] BCD representation of floats
Ken, Thanks for your detailed response. I thought of your remedies, but I perhaps didn't explain myself well enough. The Oracle we are using is Oracle 8, yes, ancient I know, but a fact of life. It has no binary_double type; that was introduced for Oracle 10 evidently. Second, I know there is a deviation, and I even know what it is in some cases, so a deviation check won't help me much. Following the extraction from the database, there is a sequence of complex numerical calculations. The end result is a fairly substantial discrepancy in the numerical result. Since we ported this entire piece of software, not just changing the database but other things as well, we'd like to know if it's all attributable to the database, or if there is some other source of the disagreement. It's not likely that a disagreement in the 14th significant figure from Oracle/sqlite could propagate to the 6th significant figure, but having dealt with numerics, you know this is possible. Therefore my dilemma is this: are the observed differences in output due solely to the difference from the databases, or is there something else going on? The most straightforward way to ascertain this is to make sure the numbers coming out of the database are identical. I've done this in a limited way. With some painstaking digging into gdb and dbx, I found out how to read and set the integer (hex) representations of the doubles. Where I saw some important numbers differing slightly coming out of sqlite, I manually set them to what the Oracle result was, and let the computation proceed. At the end, the results were essentially the same as the unmodified sqlite application, so I know now that that discrepancy was insignificant. However, there are other numbers coming from other tables that haven't been checked in the same way. I was seeking one blanket way of temporarily having all the numbers extracted emulate the exact form of the Oracle numbers. It's not that this is the only way to proceed, but if such a method exists, it would make things much faster. Liam On Wed, Mar 26, 2008 at 1:02 PM, Ken <[EMAIL PROTECTED]> wrote: > Liam, > > I know a great deal about oracle internals, Oracle stores "numbers" as you > indicate in a bcd format. But It can be up to 22 bytes long. But usage in > oracle proc/proc++/sql/plsql is really dependent upon your native conversion > to host datatype. > > The number storage formats betweend Sqlite and Oracle are totally different. > I don't think there is much your going to be able to do since the storage is > different, most likely the precision is also going to be different and hence > the outputs. > > Maybe you could implement a deviation check in your sqlite code. If the > number produced fromsqlite matches oracle within a range +/- some number. > Then it is considered equal. > > Also as your article implies you could modify the oracle data type to be a > binary_float or binary_double which would store the value in a "native" ieee > format instead of the NUMBER (which is more accurate). > > Another thought: Role your own NUMBER data type !!! Create a function inside > sqlite that would basically implement an oracle Number representation. This > should then produce exact match to oracle (in theory). You could use a text > field or blob field to store the number datatype. Then apply your conversion > function to it! > > HTH, > Ken > > > > Liam Healy <[EMAIL PROTECTED]> wrote: I am porting a numerical application > from Oracle to SQLite. For the > > > most part, I have been successful, but there are slight disagreements > in the floating point number results. I have traced this back and > found a problem. According to > http://articles.techrepublic.com.com/5100-22_11-5224536.html, Oracle > by default stores floats as binary-coded decimal (BCD), and not > IEEE754 binary. SQLite on the other hand does > http://www.sqlite.org/datatype3.html: "REAL. The value is a floating > point value, stored as an 8-byte IEEE floating point number." For the > results of the application, it makes no difference how the numbers are > stored -- the differences in the 15th significant figure are > irrelevant. However, I would like to insure that there no > disagreements in the way the two applications operate (other than the > storage of floating point numbers), and for that I temporarily need > exact agreement on input numbers. I cannot change the Oracle > application, so I'm wondering if there's a wrapper or something I can > put around sqlite calls (or better, a mode that I can put sqlite in) > that will reproduce exactly the BCD format of Oracle. > > Thanks for any guidance. > > Liam > ___ > 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://sql
[sqlite] Sqlite catalog - datebase with pictures
Hi. I would like to write an application wich helps me to manage with my cd/dvd collections. It will scan all files on cd/dvd add it to a datebase and it could also insert pictures (cd/dvd covers). My questions are: - is there any possiblity to add in sqlite datebase pictures like gif, jpg ? - how to design datebase to scan cd/dvd and make tree layout (Folders, files etc.) ? Thx for any sugestions. -- View this message in context: http://www.nabble.com/Sqlite-catalog---datebase-with-pictures-tp16369126p16369126.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] "Unsupported File Format" from 3.1.3
On Mar 29, 2008, at 12:34 AM, Jerry Krinock wrote: > Someone sent me an sqlite database file. > > Opening in Mac OS 10.5 with the built-in sqlite 3.4.0, no problems. > > Opening in Mac OS 10.4 with the built-in sqlite 3.1.3, any query > returns sqlite error 1, "unsupported file format". > > Similar files from other users open in either Mac OS/sqlite version. > > I'd thought that sqlite3 databases were generally backward- > compatible. Is there any way to find out what is "unsupported" by > sqlite 3.1.3 in this database? > The databases are backwards compatible. The database you are having trouble with is created by a more recent version of SQLite and uses features that were not added until after 3.1.3. Backwards compatible means that newer versions of SQLite can open any historic database. You are trying to open a newer database with an historic version of SQLite - that would be forwards compatibility. SQLite is generally forwards compatible, except when the newer database uses features which were added later. The added feature is probably a descending index. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Indexing and Search speed
Hi, I am having 4 records in my database. I am using Joins method. My Table Looks like: "PRAGMA encoding = UTF16;" "CREATE TABLE ALBUMARTIST (AlbumArtistId INTEGER PRIMARY KEY NOT NULL, AlbumArtistName TEXT NOT NULL COLLATE NOCASE, YomiAlbumArtistName TEXT NOT NULL, UNIQUE (AlbumArtistName));" CREATE UNIQUE INDEX ALBUMARTIST_idx ON ALBUMARTIST (YomiAlbumArtistName); AlbumArtistId AlbumArtistName YomiAlbumArtistName 1 zzz/bbb ZZZ/BBB 2 ccc/xxx CCC/XXX 3 bbb/aaa BBB/AAA "CREATE TABLE ARTIST (ArtistId INTEGER PRIMARY KEY NOT NULL,ArtistName TEXT NOT NULL COLLATE NOCASE ,YomiArtistName TEXT NOT NULL,UNIQUE(ArtistName));" CREATE UNIQUE INDEX ARTIST_idx ON ARTIST (YomiArtistName); ArtistIdArtistName YomiArtistName 10 bbb BBB 11 xxx XXX 12 aaa AAA "CREATE TABLE ALBUM (AlbumId INTEGER PRIMARY KEY NOT NULL,AlbumName TEXT NOT NULL COLLATE NOCASE ,YomiAlbumName TEXT NOT NULL,UNIQUE(AlbumName));" CREATE UNIQUE INDEX ALBUM_idx ON ALBUM (YomiAlbumName); AlbumId AlbumName YomiAlbumName 20 zzz ZZZ 21 ccc CCC 22 bbb BBB "CREATE TABLE MUSIC(Id INTEGER PRIMARY KEY NOT NULL,Track TEXT NOT NULL,YomiTrack TEXT NOT NULL,URL TEXT NOT NULL,Album_Id INTEGER,Artist_Id INTEGER,AlbumArtist_Id INTEGER);" CREATE UNIQUE INDEX MUSIC_idx ON MUSIC (YomiTrack); Id TrackYomiTrack URL Album_Id Artist_Id AlbumArtist_Id 1 trak1TRAK1 c:/trak1 22 10 1 2 song SONG c:/song 21 11 2 3 abc ABC c:/abc23 12 3 I am using the following Searching Statements. "SELECT * FROM ARTIST ORDER BY YomiArtistName;" Idx -> YomiArtistName "SELECT * FROM ALBUMARTIST ORDER BY YomiAlbumArtistName;" Idx -> YomiAlbumArtistName SELECT Track,YomiTrack,URL FROM MUSIC ORDER BY YomiTrack; Idx -> YomiTrack SELECT AlbumId,AlbumName,YomiAlbumName FROM ALBUM ORDER BY YomiAlbumName; Idx -> YomiAlbumName Am I rightly using Indexing or not needed. To speed up the Search Statements Below Do I want to any Indexing .If so for which Fields I have to Add. select DISTINCT ALBUM.AlbumId,ALBUM.AlbumName,ALBUM.YomiAlbumName from ALBUM inner join (select * from MUSIC where Artist_Id =?) types on ALBUM.AlbumId=types.Album_Id order by ALBUM.YomiAlbumName; select types.Track,types.URL from ARTIST inner join (select * from MUSIC where Album_Id =?) types on ARTIST.ArtistId=types.Artist_Id order by ARTIST.YomiArtistName; select MUSIC.Track,MUSIC.URL from MUSIC inner join ARTIST on ARTIST.ArtistId=MUSIC.Artist_Id order by ARTIST.YomiArtistName ; select types.Track,types.URL from ALBUM inner join (select * from MUSIC where Artist_Id =?) types on ALBUM.AlbumId=types.Album_Id order by ALBUM.YomiAlbumName ; SELECT Track,URL FROM MUSIC WHERE Artist_Id = ? and Album_Id = ?; select MUSIC.Track,MUSIC.URL from MUSIC inner join ALBUM on MUSIC.Album_Id=ALBUM.AlbumId order by ALBUM.YomiAlbumName; SELECT Track,URL FROM MUSIC WHERE AlbumArtist_Id = ?; I am Using this for real time Application and my processor speed is 400MHz. Foe Each Search I will get 100 results and Using the "Scrolling Cursor Method" I will retrive the next for Sorted Fields. Please help to increase my performance speed. Thanks & Regards, Mahalakshmi ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
