Re: [sqlite] Integer / Numeric calculations
Make your column an underlying Sqlite data type, REAL, which is an 8 byte floating point number. Sqlite performs "manifest typing" which means that if you store an integer such as 27 in your NUMERIC field it will be stored as an INTEGER and if you store a real such as 27.75 it will be stored as a REAL. You also have the alternative of using a type cast in SQL or of looking at the column type in your program and executing code dependent upon it being an integer or floating point. JS Mikey C wrote: Hi, This is my 1st post here. I hope someone can help. I've been using SQlite for about a year and so far very impressed. Trouble is the typeless nature when doing simple maths on the columns. I have two columns, rating and votes, both declared as NUMERIC. I wan't to calculate the average rating so it's just rating / votes. If rating and votes contain integer values (e.g. 42 and 11), then SELECT rating / votes yields 3 (and not 3.818181) If have tried cast both rating and votes and the result to NUMERIC but still an integer. I can "bodge" the SQL like so: SELECT (rating + 0.00) / (votes + 0.00) and it works, but is there a simpler or better SQL solution to ensure a floating point result even if two integers are involved? Thanks, Mike -- View this message in context: http://www.nabble.com/Integer+-+Numeric+calculations-t1661126.html#a4500366 Sent from the SQLite forum at Nabble.com.
[sqlite] Integer / Numeric calculations
Hi, This is my 1st post here. I hope someone can help. I've been using SQlite for about a year and so far very impressed. Trouble is the typeless nature when doing simple maths on the columns. I have two columns, rating and votes, both declared as NUMERIC. I wan't to calculate the average rating so it's just rating / votes. If rating and votes contain integer values (e.g. 42 and 11), then SELECT rating / votes yields 3 (and not 3.818181) If have tried cast both rating and votes and the result to NUMERIC but still an integer. I can "bodge" the SQL like so: SELECT (rating + 0.00) / (votes + 0.00) and it works, but is there a simpler or better SQL solution to ensure a floating point result even if two integers are involved? Thanks, Mike -- View this message in context: http://www.nabble.com/Integer+-+Numeric+calculations-t1661126.html#a4500366 Sent from the SQLite forum at Nabble.com.
Re: [sqlite] Advice on compiling 3.5.5 for Mac OS X?
on 06/05/21 17:05, Kon Lovett at [EMAIL PROTECTED] wrote: > An XCode project for SQlite 3 would be nice but I don't have one. Hey, it was easy. Summary: Download the "pure C" source code, create a new Xcode "BSD Dynamic Library" project, add the source code, add Tcl.Framework from /System, click Build and you're done!! I'll write back and post step-by-step details after I test it for a few days and find the bugs ;| Jerry
Re: [sqlite] I need help understanding fake columns
Jay Sprenkle wrote: score(x,y) = max(score(x+n,y+n)-n*penalty) where n < drc(x+n,y+n) < 256 So at some 'sample' you have the score and drc, but not the x,y value? The x,y values can be interpolated using some algebra and the surrounding samples? Uh, no. Sorry my other emails were unclear. At all values that I do have, I have an x,y,score, and drc. I can calculate any value that is missing, though. The n in the above formula refers to all numbers between 0 and 256. I'm taking the max over all the data I have in that range: max for all data pieces that fall within n distance of the piece I'm calculating.
Re: [sqlite] Advice on compiling 3.5.5 for Mac OS X?
On May 21, 2006, at 2:09 PM, Thomas Chust wrote: On Sun, 21 May 2006, Jerry Krinock wrote: I would like to compile SQLite 3.3.5 for Mac OS 10.3 and Mac OS 10.4. [...] Are there any tricks that I should know about before attempting this? [...] Hello, I had no trouble whatsoever to build the standard UNIX tarball of SQLite 3.3.5 on MacOS X 10.4.6 by just unpacking it, running ./ configure and running make. I must add, though, that I built against a libreadline that I installed myself instead of the MacOS X system supplied one -- I don't know whether this makes any difference for SQLite, but I recollect that the system supplied libreadline in MacOS X is quite broken and many other things I installed by hand failed to build against it correctly. I second this. I am not even sure /usr/lib/libreadline.dylib is readline. It is a symbolic link to libedit.dylib for me. So I use my own build of readline as well. Uh, for 10.3 you will have a little work (unless compiling on a 10.3 installation). You will need to specify the compiler version since the runtime system is different. An XCode project for SQlite 3 would be nice but I don't have one. Best Wishes, Kon cu, Thomas
Re: [sqlite] Can't access sqlite_master from VB6 via ODBC
It is not permissions, more like a VB interface problem. Robin Wilson wrote: Hi all, I've just started looking at SQLite - and think it is really impressive. It would be just the thing to use with my students for teaching them about RDBMS's (especially with some of the nice Windows UIs which are available for the students who can't cope with command line tools!). I have had a few problems though. At the moment I am accessing SQLite through ODBC from Visual Basic 6. This means the students can carry on using the ADO commands that they are familier with (from working with Access DBs). However, when working from VB the SQL query "SELECT * FROM sqlite_master;" does not return any records, but when I run that from the sqlite3 command line program with the same db file it returns 1 row. Is this some kind of permissions problem? If this happened elsewhere I would assume it was, but I remember reading on your website that permissions aren't implemented in SQLite. Does anyone have any ideas? Cheers, Robin P.S. I hope this is the right list, and that it is ok to just butt in etc...
Re: [sqlite] Advice on compiling 3.5.5 for Mac OS X?
On Sun, 21 May 2006, Jerry Krinock wrote: I would like to compile SQLite 3.3.5 for Mac OS 10.3 and Mac OS 10.4. [...] Are there any tricks that I should know about before attempting this? [...] Hello, I had no trouble whatsoever to build the standard UNIX tarball of SQLite 3.3.5 on MacOS X 10.4.6 by just unpacking it, running ./configure and running make. I must add, though, that I built against a libreadline that I installed myself instead of the MacOS X system supplied one -- I don't know whether this makes any difference for SQLite, but I recollect that the system supplied libreadline in MacOS X is quite broken and many other things I installed by hand failed to build against it correctly. cu, Thomas
[sqlite] Advice on compiling 3.5.5 for Mac OS X?
I would like to compile SQLite 3.3.5 for Mac OS 10.3 and Mac OS 10.4. I see from the list archives a couple months ago that some people were using the TEA compatible distribution for this. Are there any tricks that I should know about before attempting this? I have compiled a couple packages using ./configure and makefile, but this is not my forte. Should I try Xcode first? Sincerely, Jerry Krinock P.S. I realize that SQLite 3.1.3 is built into Tiger, but 3.1.3 is missing many basic features.
Re: [sqlite] Pragmas
On 5/21/06, Unit 5 <[EMAIL PROTECTED]> wrote: I am a bit confused on how to change the page_size using the pragma command. The documentation says: "The page-size may only be set if the database has not yet been created." The database is created when you create first table in it. Run the pragma before your first CREATE TABLE query. -- Nemanja Corlija <[EMAIL PROTECTED]>
[sqlite] Pragmas
I am a bit confused on how to change the page_size using the pragma command. The documentation says: "The page-size may only be set if the database has not yet been created." But, to be able to issue the pragma command, I need to have a database connection in the first place. If the database I specify does not exist, it gets created automatically as a side effect of the connection. Then it is too late to change the page_size again. Same thing goes for a few other settings, including encoding. I am using the tcl api in case it is relevant. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Re: [sqlite] I need help understanding fake columns
On 5/20/06, Brannon King <[EMAIL PROTECTED]> wrote: So here's the real problem. Sorry about the long explanation, but if anyone wants to help with the query, that would be great. I have a 2d sparse matrix in a table where each point has two values, a score and a diagonal run count. In other words, my table has four columns: x,y,score,drc. Even though a certain x,y does not show up in the table, we can still calculate its score value by finding a piece farther along on the same diagonal using this formula: score(x,y) = max(score(x+n,y+n)-n*penalty) where n < drc(x+n,y+n) < 256 So at some 'sample' you have the score and drc, but not the x,y value? The x,y values can be interpolated using some algebra and the surrounding samples? The values of x and y would be from a 'correlated subquery'. You need to establish some way the database engine can pick the correct sample to use in the calculation. That is usually done by adding a primary key column with a numerical number for each sample. You can then say for sample N's calculation use the x,y value from sample N+4 (or whatever). -- SqliteImporter, SqliteReplicator: Command line utilities for Sqlite http://www.reddawn.net/~jsprenkl/Sqlite
Re: [sqlite] Porting sqlite3 library problems....
Fred a <[EMAIL PROTECTED]> wrote: > Hello all, > > I port successfully sqlite3 library on other platform... > But i have some problems... > > When i try to insert some records, if for example i insert > 80: good; if i try to insert 81 records the database is corrupted This suggests to me that your port is not as successful as you think it is. So what is the "other platform" that you are porting to? -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Porting sqlite3 library problems....
On 5/21/06, Fred a <[EMAIL PROTECTED]> wrote: Hello all, I port successfully sqlite3 library on other platform... But i have some problems... When i try to insert some records, if for example i insert 80: good; if i try to insert 81 records the database is corrupted I don t understand because if make a SELECT query with my library on a big table created on pc with sqlite3 win32 : it work fine. Can you post a test program? Have you checked for hardware problems?
Re: [sqlite] create unique index quickly
On 5/20/06, Brannon King <[EMAIL PROTECTED]> wrote: The benefits I'm trying to get out of sqlite are the data queries. I collect a large, sparse 2D array from hardware. The hardware device is giving me a few GB of data data at 200MB/s. Future hardware versions will be four times that fast and give me terabytes of data. After I have the data, I then have to go through and make calculations on sub-boxes of that data. Have you thought about constructing a data warehouse for this? Use something close to the metal (hash + binary file I/O) to store the initial data. Then write something to load what data you need into a database. You can then do your queries against that. I think your overall bottle neck is indexing, and indexing 800MB/s sustained will take big iron or a beowulf cluster.
[sqlite] Can't access sqlite_master from VB6 via ODBC
Hi all, I've just started looking at SQLite - and think it is really impressive. It would be just the thing to use with my students for teaching them about RDBMS's (especially with some of the nice Windows UIs which are available for the students who can't cope with command line tools!). I have had a few problems though. At the moment I am accessing SQLite through ODBC from Visual Basic 6. This means the students can carry on using the ADO commands that they are familier with (from working with Access DBs). However, when working from VB the SQL query "SELECT * FROM sqlite_master;" does not return any records, but when I run that from the sqlite3 command line program with the same db file it returns 1 row. Is this some kind of permissions problem? If this happened elsewhere I would assume it was, but I remember reading on your website that permissions aren't implemented in SQLite. Does anyone have any ideas? Cheers, Robin P.S. I hope this is the right list, and that it is ok to just butt in etc...
Re: [sqlite] create unique index quickly
Is that 800MB/s sustained? Is it constant, 24/7? Or is it bursty data? If it's bursty, you could buffer the inserts in an append only log. Insertion to the SQL(ite) database can be done asynchronously. Writing to unstructured, append only log files can be done at pretty much disk I/O speed. If it's not bursty, but sustained, then I fear SQLite or any other database will not be able to match that rate. You'll probably run out of CPU before running out of disk IO. Christian PS. On a side note, is it wise still buying SGI kit? That's a sustained rate. I can stall the hardware, though, when needed. It just makes things take longer to not take full advantage of the hardware. Concerning SGI, I keep hoping their prices will drop to match their stock. I actually think they could turn things around if they dropped their prices. They're going to have a tough time competing with the Cray XD1 and the IBM Blue Gene unless they are way cheaper. SGI's choice to go with the Itanium may prove fatal. I think they had better jump to an x64 platform soon.
Re: [sqlite] create unique index quickly
On Sat, 20 May 2006, Brannon King wrote: John Stanton wrote: You don't seem to need a data manipulation system like Sqlite, more a form of high volume storage. Do you really need elaborate SQL, journalling, ROLLBACK and assured disk storage? Di you consider some form of hashed storage, perhaps linear hashing, to build a compact and high performance associative array for your sparsely keyed data. Do you really need the overhead of B-trees is you are just storing a sparse array? JS I don't need journaling or rollback. I'd love a way to shut them off. But elaborate SQL, that sure is handy. I'm not just storing, I'm viewing stored, compressed data. I definitely need some way of querying a sparse matrix data that is larger than my DRAM. Sqlite sure seems like the quickest route to a workable product for that to happen. It has all the streaming/caching built in. Because of that, I assume it is faster than random file access. It supports complex data queries and indexes, both things I would need anyway. In the world of programming, I think many will agree you should get a working product, then make it faster. I'm just trying to get the most speed out of the easiest tool. If I need to rewrite the file storage for the next version, we can consider the cost to benefit for that separately. Is that 800MB/s sustained? Is it constant, 24/7? Or is it bursty data? If it's bursty, you could buffer the inserts in an append only log. Insertion to the SQL(ite) database can be done asynchronously. Writing to unstructured, append only log files can be done at pretty much disk I/O speed. If it's not bursty, but sustained, then I fear SQLite or any other database will not be able to match that rate. You'll probably run out of CPU before running out of disk IO. Christian PS. On a side note, is it wise still buying SGI kit?
[sqlite] Porting sqlite3 library problems....
Hello all, I port successfully sqlite3 library on other platform... But i have some problems... When i try to insert some records, if for example i insert 80: good; if i try to insert 81 records the database is corrupted I don t understand because if make a SELECT query with my library on a big table created on pc with sqlite3 win32 : it work fine. For insert records -I open the db -begin transaction -create table -insert records -commit transaction -close the db Thank you a lot for your help in advance Bye Best regards, Fred
Re: [sqlite] create unique index quickly
On Sat, 20 May 2006, Brannon King wrote: [...] I don't need journaling or rollback. I'd love a way to shut them off. [...] In that case zou can at least do a PRAGMA synchronous = OFF; to improve speed at the cost of safety. See http://www.sqlite.org/pragma.html#modify for more information. cu, Thomas
Re: [sqlite] Index syntax for attached database
Sunday, May 21, 2006, 02:10:31, Brannon King wrote: > The documentation says to put the database name on the front of the > index name, not the table name when using the create index command. I > thought it was weird myself. Thank you, it has done the trick! I missed this in CREATE's documentation, concentrating on ATTACH exclusively. Beside this, I find it still a bit counter-intuitive too ... Micha --
