[sqlite] Speed Test not work (pt2)
Well, Did this: sqlite3 test2.db create Table T (A, B, C ); .separator , .import 'sqtest2.txt' T It looks like it working, but the file size is still 4K and not 170 Megs. Please note: I exported this database as a Tab delimiter file, then as a Comma, delimiter file... ** Got it to import, however it only imports the first record ie: sqlite> select * from T; -180,90,NaN sqlite> There's a few more hundred thousands. is there suppose to be some kinda of loop statement like repeat again.. TKS- Richard -- I will not be pushed, filed, stamped, indexed, briefed, debriefed, or numbered! My life is my own - No. 7
[sqlite] Speed Test not work?
Well, Did this: sqlite3 test2.db create Table T (A, B, C ); .separator , .import 'sqtest2.txt' T It looks like it working, but the file size is still 4K and not 170 Megs. Please note: I exported this database as a Tab delimiter file, then as a Comma, delimiter file... Change the .extension from .csv to .txt neither are working to import file. this is a 3 field file. Richard -- I will not be pushed, filed, stamped, indexed, briefed, debriefed, or numbered! My life is my own - No. 7
Re: [sqlite] NEW DATA TYPE IN SQLITE
The program are all ANSI C, comprising run time libraries and a compiler, so the various functions could well be implemented by linking in the API used by the legacy compilation system. Their most common usage was to link into a byte-code interpreter not so different from the Sqlite VDBE or a Java VM to provide platform independent execution. Thanks for the hints about the TCL capability. I shall dig into it. As for function names, I was anticipating not using any, just incorporating the type into SQL so that it would look like the PostgreSQL NUMERIC(precision, scale) numbers, but actually be fundamentally a TEXT type for Sqlite storage purposes. Then SQL would use the type just as PostgreSQL uses NUMERIC and portability would actually be enhanced. JS Jackson, Douglas H wrote: Is the application written in the legacy language, or are you changing it, too? What language was/is it in? The TCL language library has the ability to link functions to the engine. Such functions can affect coercion of the data into a different type, so that storage and presentation of the data need not be the same. Combining the functions with triggers and views might do what you need. Choose function names wisely, and the SQL could still be portable. Doug -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: Friday, September 30, 2005 2:49 PM To: sqlite-users@sqlite.org Subject: [sqlite] NEW DATA TYPE IN SQLITE First a disclaimer. I am a new user of SQLITE and have not dug very deeply into the code and literature, so my question may be trivial. I have a system from an emulation of a legacy commercial language processor which uses a very handy fixed point decimal number system. The numbers are of arbitrary precision and held in right justified display format. As you can imagine arithmetic on such numbers is not blindingly fast but for general commercial usage they are truly excellent. Commercial applications are not calculation intensive but are display intensive so the time saved in radix transformation and editing far exceeds the time lost in divisions. This type of fixed point display format number with automatic rounding makes it very easy to produce reports which balance to the penny and incredibly easy to generate financial reports. Such a number type does seem to fit in with the SQLITE concept of loose typing, since it is actually just a text field. There is actually an obscure ANSI standard which defines these numbers. How feasible and how difficult would it be to add this type to SQLITE? The numbers store as text strings so what is involved is adding the new numeric type and inserting the arithmetic functions so that they are recognized by the SQL processor? If anyone has a quick answer I should appreciate it. PS, with such fixed point numbers you would have 5.0 / 2.0 = 2.5 exactly.
RE: [sqlite] NEW DATA TYPE IN SQLITE
Is the application written in the legacy language, or are you changing it, too? What language was/is it in? The TCL language library has the ability to link functions to the engine. Such functions can affect coercion of the data into a different type, so that storage and presentation of the data need not be the same. Combining the functions with triggers and views might do what you need. Choose function names wisely, and the SQL could still be portable. Doug -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: Friday, September 30, 2005 2:49 PM To: sqlite-users@sqlite.org Subject: [sqlite] NEW DATA TYPE IN SQLITE First a disclaimer. I am a new user of SQLITE and have not dug very deeply into the code and literature, so my question may be trivial. I have a system from an emulation of a legacy commercial language processor which uses a very handy fixed point decimal number system. The numbers are of arbitrary precision and held in right justified display format. As you can imagine arithmetic on such numbers is not blindingly fast but for general commercial usage they are truly excellent. Commercial applications are not calculation intensive but are display intensive so the time saved in radix transformation and editing far exceeds the time lost in divisions. This type of fixed point display format number with automatic rounding makes it very easy to produce reports which balance to the penny and incredibly easy to generate financial reports. Such a number type does seem to fit in with the SQLITE concept of loose typing, since it is actually just a text field. There is actually an obscure ANSI standard which defines these numbers. How feasible and how difficult would it be to add this type to SQLITE? The numbers store as text strings so what is involved is adding the new numeric type and inserting the arithmetic functions so that they are recognized by the SQL processor? If anyone has a quick answer I should appreciate it. PS, with such fixed point numbers you would have 5.0 / 2.0 = 2.5 exactly.
[sqlite] NEW DATA TYPE IN SQLITE
First a disclaimer. I am a new user of SQLITE and have not dug very deeply into the code and literature, so my question may be trivial. I have a system from an emulation of a legacy commercial language processor which uses a very handy fixed point decimal number system. The numbers are of arbitrary precision and held in right justified display format. As you can imagine arithmetic on such numbers is not blindingly fast but for general commercial usage they are truly excellent. Commercial applications are not calculation intensive but are display intensive so the time saved in radix transformation and editing far exceeds the time lost in divisions. This type of fixed point display format number with automatic rounding makes it very easy to produce reports which balance to the penny and incredibly easy to generate financial reports. Such a number type does seem to fit in with the SQLITE concept of loose typing, since it is actually just a text field. There is actually an obscure ANSI standard which defines these numbers. How feasible and how difficult would it be to add this type to SQLITE? The numbers store as text strings so what is involved is adding the new numeric type and inserting the arithmetic functions so that they are recognized by the SQL processor? If anyone has a quick answer I should appreciate it. PS, with such fixed point numbers you would have 5.0 / 2.0 = 2.5 exactly.
Re: [sqlite] sqlite module missing error
On Sat, 2005-10-01 at 00:02 +0530, [EMAIL PROTECTED] wrote: > Hi , > I get this error while trying to install Trac. > > trac-admin /Trac/trac_project_env initenv > Traceback (most recent call last): > File "/usr/bin/trac-admin", line 30, in ? > import sqlite > ImportError: No module named sqlite > > > And when i run > > [EMAIL PROTECTED] Trac]# rpm -qa | grep sqlite > sqlite-3.0.8-1 > > > Can someone guide me as to which module is missing here. > > --Walter You're going to need to install the pysqlite package which provides the sqlite bindings for python. The pysqlite web page (fittingly using trac) is here: http://initd.org/tracker/pysqlite However, you should be able to install pysqlite on fedora by issuing the following command: yum install python-sqlite This is in the 'Extras' repository for Fedora Core 3, but may have been moved in to core for Fedora Core 4. If you need any help getting this package installed on Fedora, better to ask on fedora-list: http://www.redhat.com/mailman/listinfo/fedora-list Mark. signature.asc Description: This is a digitally signed message part
Re: [sqlite] Getting a Unique column from a query
[EMAIL PROTECTED] wrote: is it possible to use a Distinct or Unique on a column such as: SELECT school.schoolID AS ID, CASE WHEN class.classtype= 2 THEN class.classtype ELSE teacher.teacherID END AS unique(new_id) FROM Region SELECT school.schoolID AS ID, CASE WHEN class.classtype= 2 THEN class.classtype ELSE teacher.teacherID END AS distinct(new_id) FROM Region I do not want any duplicates of the new_id. Thanks! Nicole Hinderman I'm not sure what you are trying to do here. Distinct applies to rows in a result set. A statement like "select distinct ..." ensures that each row in the result set is distinct. That means that there is a difference in at least one column from every other row in the result. Unique is a constraint that applies to a column, or columns, in a table. It ensures that every row in the table has a unique value in that column, or combined set of columns. To help answer your question we will need more information about the tables you are using. It looks like you have four, a region table, a school table, a class table, and a teacher table. Is that correct? The query you gave above does not make any sense as it stands. Could you explain what you are trying to do in more detail. Dennis Cote
[sqlite] sqlite module missing error
Hi , I get this error while trying to install Trac. trac-admin /Trac/trac_project_env initenv Traceback (most recent call last): File "/usr/bin/trac-admin", line 30, in ? import sqlite ImportError: No module named sqlite And when i run [EMAIL PROTECTED] Trac]# rpm -qa | grep sqlite sqlite-3.0.8-1 Can someone guide me as to which module is missing here. --Walter This message was sent using NWebmail, BSNL's Webmail Program
Re: [sqlite] Getting a Unique column from a query
It would be ok to have duplicates of school_id. I just want to make sure that new_id is unique. Jay Sprenkle <[EMAIL PROTECTED] com> To sqlite-users@sqlite.org 09/30/2005 01:03 cc PM Subject Re: [sqlite] Getting a Unique Please respond to column from a query [EMAIL PROTECTED] te.org Won't you get duplicates rows of school_id, 2 when classtype = 2? On 9/30/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > > is it possible to use a Distinct or Unique on a column such as: > > SELECT school.schoolID AS ID, > CASE WHEN class.classtype= 2 THEN class.classtype ELSE teacher.teacherID > END AS unique(new_id) FROM Region > > > SELECT school.schoolID AS ID, > CASE WHEN class.classtype= 2 THEN class.classtype ELSE teacher.teacherID > END AS distinct(new_id) FROM Region > > > I do not want any duplicates of the new_id. > > Thanks! > Nicole Hinderman > > -- --- The Castles of Dereth Calendar: a tour of the art and architecture of Asheron's Call http://www.lulu.com/content/77264
Re: [sqlite] Need to do a Test.
Woah, Stop in time, what this command? how does one add this? I want to see the real state of sqlite, at its max speed. Regards to all. Richard Nagle Dennis Cote wrote: [EMAIL PROTECTED] wrote: Richard Nagle <[EMAIL PROTECTED]> wrote: Need to create a test2.db with following fields A B C the data is mostly numerical, however it should be okay with VARCHAR field? ( this is only a test of speed for importing and searching ) Next Need to make Field "A" index able. Then do a import of a text file sqtest.csv into this new database. What is the command for importing this in? Whoever answers this question: please be sure to tell Mr. Nagle about BEGIN...COMMIT. Otherwise he is going to run his speed tests and think SQLite only does 25 inserts/second instead of 50,000 inserts/second. -- D. Richard Hipp <[EMAIL PROTECTED]> Well its a little too late for that now, isn't it. :-) But seriously, shouldn't the .import meta command in the sqlite shell do this already? Dennis Cote -- I will not be pushed, filed, stamped, indexed, briefed, debriefed, or numbered! My life is my own - No. 7
Re: [sqlite] Getting a Unique column from a query
Won't you get duplicates rows of school_id, 2 when classtype = 2? On 9/30/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > > is it possible to use a Distinct or Unique on a column such as: > > SELECT school.schoolID AS ID, > CASE WHEN class.classtype= 2 THEN class.classtype ELSE teacher.teacherID > END AS unique(new_id) FROM Region > > > SELECT school.schoolID AS ID, > CASE WHEN class.classtype= 2 THEN class.classtype ELSE teacher.teacherID > END AS distinct(new_id) FROM Region > > > I do not want any duplicates of the new_id. > > Thanks! > Nicole Hinderman > > -- --- The Castles of Dereth Calendar: a tour of the art and architecture of Asheron's Call http://www.lulu.com/content/77264
Re: [sqlite] Need to do a Test.
On 9/30/05, Dennis Cote <[EMAIL PROTECTED]> wrote: > > > But seriously, shouldn't the .import meta command in the sqlite shell do > this already? Sorry for the self reply, but I just checked the source, the .import command does do a BEGIN and COMMIT around the insert statements it executes. So Richard should be good to go with his speed test importing from a CSV file.
Re: [sqlite] Need to do a Test.
[EMAIL PROTECTED] wrote: Richard Nagle <[EMAIL PROTECTED]> wrote: Need to create a test2.db with following fields A B C the data is mostly numerical, however it should be okay with VARCHAR field? ( this is only a test of speed for importing and searching ) Next Need to make Field "A" index able. Then do a import of a text file sqtest.csv into this new database. What is the command for importing this in? Whoever answers this question: please be sure to tell Mr. Nagle about BEGIN...COMMIT. Otherwise he is going to run his speed tests and think SQLite only does 25 inserts/second instead of 50,000 inserts/second. -- D. Richard Hipp <[EMAIL PROTECTED]> Well its a little too late for that now, isn't it. :-) But seriously, shouldn't the .import meta command in the sqlite shell do this already? Dennis Cote
[sqlite] Getting a Unique column from a query
is it possible to use a Distinct or Unique on a column such as: SELECT school.schoolID AS ID, CASE WHEN class.classtype= 2 THEN class.classtype ELSE teacher.teacherID END AS unique(new_id) FROM Region SELECT school.schoolID AS ID, CASE WHEN class.classtype= 2 THEN class.classtype ELSE teacher.teacherID END AS distinct(new_id) FROM Region I do not want any duplicates of the new_id. Thanks! Nicole Hinderman
Re: [sqlite] Need to do a Test.
Richard Nagle wrote: Need to create a test2.db with following fields A B C the data is mostly numerical, however it should be okay with VARCHAR field? ( this is only a test of speed for importing and searching ) Next Need to make Field "A" index able. Then do a import of a text file sqtest.csv into this new database. What is the command for importing this in? Richard, At a command prompt type: sqlite3 test2.db At he sqlite3 prompt type: create table T(A, B, C); create index on T(A); .separator , .import 'sqtest.cvs' You should now a table with your data. Be warned that there are problems importing CSV data with quote delimiters on the fields (the quotes are included in the table as part of the field values). You can try a select to dump your table. select * from T; HTH Dennis Cote
Re: [sqlite] Need to do a Test.
Richard Nagle <[EMAIL PROTECTED]> wrote: > Need to create a test2.db > with following fields A B C > the data is mostly numerical, however it should be okay > with VARCHAR field? ( this is only a test of speed for importing and > searching ) > > Next Need to make Field "A" index able. > Then do a import of a text file sqtest.csv > into this new database. > > What is the command for importing this in? > Whoever answers this question: please be sure to tell Mr. Nagle about BEGIN...COMMIT. Otherwise he is going to run his speed tests and think SQLite only does 25 inserts/second instead of 50,000 inserts/second. -- D. Richard Hipp <[EMAIL PROTECTED]>
RE: [sqlite] Problem/Bug: "SELECT 5 / 2;" returns 2 ?
Anybody thought of: t1.a = 5 t1.b = 2 select a / (b * 1.0); I think that would return a real. -Original Message- From: Dennis Cote [mailto:[EMAIL PROTECTED] Sent: Friday, September 30, 2005 11:10 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Problem/Bug: "SELECT 5 / 2;" returns 2 ? Ralf Junker wrote: >>This can be fixed by checking the column affinity for a value when it is stored. If an integer value is being stored in a column with numeric affinity, then store the value as a REAL value ...
[sqlite] Need to do a Test.
Need to create a test2.db with following fields A B C the data is mostly numerical, however it should be okay with VARCHAR field? ( this is only a test of speed for importing and searching ) Next Need to make Field "A" index able. Then do a import of a text file sqtest.csv into this new database. What is the command for importing this in? -- I will not be pushed, filed, stamped, indexed, briefed, debriefed, or numbered! My life is my own - No. 6
Re: [sqlite] primary, secondary keys
On Fri, Sep 30, 2005 at 10:55:07AM -0400, Mark Wyszomierski wrote: > Does sqlite allow multiple keys? When I created a table I did: > CREATE TABLE test (name, address, fav_color, primary key(name, address)) I usually use UNIQUE, though, for "business keys", and reserve PRIMARY KEY for the auto-increment field. Dave Cook
Re: [sqlite] primary, secondary keys
Thanks Dennis On 9/30/05, Dennis Cote <[EMAIL PROTECTED]> wrote: > > Mark Wyszomierski wrote: > > >Hi all, > > Does sqlite allow multiple keys? When I created a table I did: > > CREATE TABLE test (name, address, fav_color, primary key(name, address)) > > Is that valid? I would like to not have any duplicate name + address > >entries. There > >was no error reported but later I tried modifying the table in SQLite > >Database Browser > >and it complained that the table has multiple primary fields. > > Thanks, > >Mark > > > > > > > Mark, > > Yes, that is valid SQL. > > The error message means that you are trying to insert or update a row so > that its primary key (the combination of name and address) is the same > as some other row that already exists in the table. SQL requires that > the primary key of each row be unique. > > HTH > Dennis Cote >
Re: [sqlite] primary, secondary keys
Mark Wyszomierski wrote: Hi all, Does sqlite allow multiple keys? When I created a table I did: CREATE TABLE test (name, address, fav_color, primary key(name, address)) Is that valid? I would like to not have any duplicate name + address entries. There was no error reported but later I tried modifying the table in SQLite Database Browser and it complained that the table has multiple primary fields. Thanks, Mark Mark, Yes, that is valid SQL. The error message means that you are trying to insert or update a row so that its primary key (the combination of name and address) is the same as some other row that already exists in the table. SQL requires that the primary key of each row be unique. HTH Dennis Cote
Re: [sqlite] Problem/Bug: "SELECT 5 / 2;" returns 2 ?
Ralf Junker wrote: This can be fixed by checking the column affinity for a value when it is stored. If an integer value is being stored in a column with numeric affinity, then store the value as a REAL value rather than as an INTEGER value. This will perform the same conversion that the other engines do, and hence produce the same result when the division operation is performed later. Unfortunately, this is not true for SQLite3. If you execute the following SQL commands CREATE TABLE t (a REAL, b REAL); INSERT INTO t VALUES (5, 2); both values are stored as INTEGER and not as REAL as one would expect by the column affinity. In fact, this behaviour is intentional and is stated in the help as a feature to minimize storage space for INTEGER numbers. I'm sorry I took so long to get back to, I started this reply last night, but had to leave it unfinished until now. To clarify what I said above (since it obviously wasn't clear). The first thing to note is that SQLite applies a data type to individual values, and data type affinities to table columns. These are different things. I meant that your division problem can be fixed in SQLite if SQLite is modified so that it checks the affinity of the column before storing the integer value. In SQLite a column declared as REAL has an affinity of NUMERIC (see http://www.sqlite.org/datatype3.html section 2.1). The problem is that currently columns with NUMERIC affinity can store values of any type, in particular it can store both INTEGER and REAL (see section 1 of the data types documentation). The required change would be to modify SQLite so that it would convert INTEGER values to REAL values when they are stored in a column with NUMERIC affinity. This would make your division work as expected. The integer value supplied to the insert statement would be converted to real and stored as real in the table. When the select statement later retrieves the values to do the division, it will have real values and do floating point path to produce a real result. It has some side effects that others may not like however. Storing a value of 5 into the table would return a value of 5.0 when selected, since SQLite (at least the newest versions) formats REAL values with a decimal point on output. It would also make the database files larger if they were storing integer values in a typeless column or one with a type that produced NUMERIC affinity (again see the data types doc for the rules). If a user really wants to force values to be stored and returned as integers they would simply need to explicitly declare the columns to have an INT type, so they would be assigned an INTEGER affinity. Then no conversion would be done when the values are stored. To me this seems like an optimization for both speed and space that the user can trigger by explicitly specifying the column's data type. To force storing numbers as REALs SQLite3 requires to use data binding or to rewrite the 2nd command above like this: INSERT INTO t VALUES (5.0, 2.0); In other words, SQLite3 requires REALs to be explicitly written as such. Many users are certainly not aware of this requirement. They expect REALs to be stored as REALs just like other DB-engines do if a column is defined as such. However, SQLite3 behaves differently and this can * result in wrong calculations / different than intended. * lead to data errors when importing SQL scripts from other DBMS. On the other INTEGER side, SQLite3 again behaves differently: CREATE TABLE i (a INTEGER, b INTEGER); INSERT INTO i VALUES (5, 2); INSERT INTO i VALUES (5.0, 2.0); Both INSERTs above store the numbers as INTEGERs, even those explicitly marked as REALs by adding the '.0' decimal. This behavior is also documented on the data type page. If the REAL value can be represented exactly as an INTEGER, an INTEGER is stored in columns declared to be of type INT. If not, the REAL value is stored. This seems reasonable to me. It is in some ways the complement to the change I proposed above. If you do this in a standard SQL engine it will silently convert the 5.0 value to an exact value with implementation defined precision. Usually not a problem for 5.0, but I suspect that 5.1 will probably come out of the INTEGER column with the same value as 5.0 does. In SQLite, it will store the 5.1 value as a REAL value even though it is in a column with INTEGER affinity. Another problem shows when calculations are not performed on colums with type affinity but just on plain numbers like in SELECT 5 / 2; What exactly is intended? From the findings above, both numbers might be seen as INTEGERs, but if '5.0' is interpreted as an INTEGER above, '5' might just as well be a REAL. Arguing from "common sense" I would prefer 5 / 2 to return 2.5 rather than 2. If the result cannot be represented with exact numeric, I would rather want it to be aproximate only. I can not overcome the feeling
[sqlite] primary, secondary keys
Hi all, Does sqlite allow multiple keys? When I created a table I did: CREATE TABLE test (name, address, fav_color, primary key(name, address)) Is that valid? I would like to not have any duplicate name + address entries. There was no error reported but later I tried modifying the table in SQLite Database Browser and it complained that the table has multiple primary fields. Thanks, Mark
Re: [sqlite] Linking tables together
On 9/30/05, spudse bud <[EMAIL PROTECTED]> wrote: > > Hello all, thanks for helping me with my last question (about a the sqlite > manager) - I haven't had time to test them all yet, but I will this > weekend. > Right now I have a new question. I have a database which contains tables > that contain user specific "variables", one table for each user. Those > tables for instance have fields like "name", "email" and "country". Now I > want to link those tables together (or maybe even a .db for each user), I > mean that if I change or add a field that all other tables are also > editted. > Is the linking of tables/db's I explained possible? > Thank you. > > perhaps something like this? One table describes what information is available for a 'user' It might have a row called 'name', 'email', 'country', etc. Call these 'attributes'. Another table contains the users id code and the value for each of the attributes. Call this 'values' Adding a new attribute is as simple as adding a row. Every user can have different sets of attributes. -- --- The Castles of Dereth Calendar: a tour of the art and architecture of Asheron's Call http://www.lulu.com/content/77264
[sqlite] Linking tables together
Hello all, thanks for helping me with my last question (about a the sqlite manager) - I haven't had time to test them all yet, but I will this weekend. Right now I have a new question. I have a database which contains tables that contain user specific "variables", one table for each user. Those tables for instance have fields like "name", "email" and "country". Now I want to link those tables together (or maybe even a .db for each user), I mean that if I change or add a field that all other tables are also editted. Is the linking of tables/db's I explained possible? Thank you.
Re: Re[2]: [sqlite] the 3 numerical types (was Re: Problem/Bug: "SELECT 5 / 2;" returns 2 ?)
>
> For those still following this thread, and looking for more examples
> of the infinite representations of numbers ;-), here is another...
>
> Instead of a binary fixed point, you can use a binary number with an
> implicit decimal fixed point. For example, if your implicit decimal
> point were 10^2, the number 345 (stored as a binary integer) would
> represent the number 3.45. Concretely in C syntax, you'd print numbers
> as printf("%d.%02u", x/100, x%100). Addition and subtraction can be
> done with the integer operators. Multiplication and division take some
> care to avoid loss of range or precision, but abstractly, multiply is
> (x * y) / 100 and divide is (x * 100) / y. This is a neat hack to
> maintain fixed decimal accuracy with binary integers.
>
> > These values can also be stored as arbitrary precision integers,
> > which store multiple integers words to represent the value which can
> > again be interpreted as a fixed point fraction, or as BCD (binary
> > coded decimal) values which store 2 digits per byte.
>
> Storing numbers as *two* arbitrary precision integers, a numerator and
> a denominator, gives you all the exact rationals (at least as big as
> your memory allows -- reducing the numbers helps). Common Lisp and Scheme
> have rationals as well as real, complex, and arbitrary precision
> integer numbers. http://mathworld.wolfram.com/RationalNumber.html
There was also a proposed numbering system where the radix was
variable and was given by prime numbers. Pretty wild stuff but I've
never seen an implementation.
Re: [sqlite] Problem/Bug: "SELECT 5 / 2;" returns 2 ?
On 9/30/05, Ralf Junker <[EMAIL PROTECTED]> wrote: > > Quite right, but even with explicit typing there are cases where SQLite3 > can not be instructed to store REAL numbers as REALs: > > C:\>sqlite3 num.db3 > SQLite version 3.2.1 > Enter ".help" for instructions > sqlite> CREATE TABLE i (a INTEGER, b INTEGER); > sqlite> INSERT INTO i VALUES (5, 2); > sqlite> SELECT a / b FROM i; > 2 > sqlite> INSERT INTO i VALUES (5.0, 2.0); > sqlite> SELECT a / b FROM i; > 2 > 2 > > Even though the 2nd insert explicitly types 5.0 and 2.0 as REALs, they are > stored as INTEGERs internally and resuls in incorrect calculations (the very > last number should be 2.5 and not 2). In that case it did what it was instructed to do. The type was integer so it converted. Summming up: Observations show that SQLite3 behaves inconsistent when > storing and/or calculating numbers as INTEGERs or REALs. The point I am > personally worried about is not so much the storage side of things but the > calculation errors resulting from it. Since it behaves in predictable ways I don't believe inconsistency is a big problem. Inconsistency is the only constant I've found in life! It's only inconsistent on one operation, storing integers in a real type. If you code with a rule of thumb to always insert with something like "insert into t values( round(x) );" then you will have no problems. I would like to propose the resolve this inconsistency as follows: > > That would make sqlite math inconsistent with math as performed by computer languages. I don't see that as an improvement. --- The Castles of Dereth Calendar: a tour of the art and architecture of Asheron's Call http://www.lulu.com/content/77264
[sqlite] Problem Installing sqlite3 in redhat linux 9 i686 machine
Hello, I tried compiling the source sqlite3-3.2.1 with the following commands on a i686 machine distro rehat linux 9 with the following commands, ./configure make make install And the above process returns successful message and all the lib* libraries are present in /usr/local/lib, i can also invoke sqlite3 by typing in the same at the prompt I have created an application which uses GTK+-1.2 and sqlite3 , the application compiles fine without any error but while trying to execute the binary the following error surface,, ./main(This is the binary) error while loading shared libraries: libsqlite3.so.0: cannot open shared object file: No such file or directory as mentioned above, i have cross-checked and these libraries are present in /usr/local/lib and all have executable permissions, The above library, libsqlite3.so.0 is linked with another library libsqlite3.so.0.8.6 But the above process works fine if i replicate them on a i386 machine with the same distro installed..
Re: Re: Re: [sqlite] Rewriting a query
[EMAIL PROTECTED] wrote: "Miha Vrhovnik"<[EMAIL PROTECTED]> wrote: Subject: =?ISO-8859-1?Q?Re=3A=20Re=3A=20=5Bsqlite=5D=20Rewriting=20a=20query?= What RFC do I need to read to figure out how to decode the Subject line (presumably inserted by si.Mail)? RFC 2047 "MIME (Multipurpose Internet Mail Extensions) Part Three: Message Header Extensions for Non-ASCII Text." Igor Tandetnik
Re: [sqlite] Problem/Bug: "SELECT 5 / 2;" returns 2 ?
Ralf Junker wrote: Summming up: Observations show that SQLite3 behaves inconsistent when storing and/or calculating numbers as INTEGERs or REALs. The point I am personally worried about is not so much the storage side of things but the calculation errors resulting from it. I would like to propose the resolve this inconsistency as follows: 3. If the division of INTEGERs can not be stored as an INTEGER (i.e. if a % b != 0), the result should be returned as a REAL. But this would break much, much code that expects integers to be treated as integers. This can't be right. Implementation would require, for every integer calculation, conversion to REAL, performing the calculation, and checking whether the result is an integer (within some tolerence). This suggestion won't float. Gerry -- -- Gerry Snyder American Iris Society Director, Symposium Chair in warm, winterless Los Angeles -- USDA zone 9b, Sunset 18-19
Re: [sqlite] Rewriting a query
--- [EMAIL PROTECTED] wrote: > Robin Breathe <[EMAIL PROTECTED]> wrote: > > Hugh Gibson wrote: > > >> I'm intrigued. How do you get SQLite to use a multi-column index as it's > > >> primary key (i.e. B-tree hash)? Please elaborate. > > > > > > Simply > > > > > > CREATE TABLE TransactionList (sTransactionID Text(13) DEFAULT > > > '',sCommunityID Text(13) DEFAULT '',sObject Text(13) , PRIMARY KEY > > > (sCommunityID, sTransactionID)) > > > > Ah, but it's not used for the B-tree hash (at least not according to the > > documentation). > > > > The ROWID is always used as the btree key (not hash!) on the main > database btree. But when you have a PRIMARY KEY a separate index > btree is also created which uses the PRIMARY KEY as its key. > > Question: Why is this important to you? I am not he, but if I were looking for ways to improve the file format that's something I'd try to work in too. Both to save space and speed things up. CREATE TABLE(a PRIMARY KEY, b); creates two btree structures: (a || oid) -> NULL(the index) (oid) -> (a || b) (the table) So for every row, there are two copies of both "a" and "oid". Depending on your schema, the space consumed by the table is from 0-100% more than if we were able to create a single btree: (a || oid) -> (b) or even drop the oid altogether, it's not part of SQL anyway (is it?): (a) -> (b) As well as saving space, in the most common case an UPDATE or DELETE would have to modify one less tree, and some SELECTs would open one less tree structure. Tricky to retain backward compatibility though. __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com
Re: Re: [sqlite] Rewriting a query
[EMAIL PROTECTED] je ob 30.9.2005 12:22:47 napisal(a): >(Side note: I am experimenting with a new Mail User Agent that >uses SQLite to store all its email messages. I appologize in advance >if this message is misformatted or otherwise garbled.) Am. Who stole that idea from me? -- It's time to get rid of your current e-mail client ... ... and start using si.Mail. http://simail.sourceforge.net/
Re: [sqlite] Problem/Bug: "SELECT 5 / 2;" returns 2 ?
Ralf Junker <[EMAIL PROTECTED]> wrote: > > 3. If the division of INTEGERs can not be stored as an INTEGER >(i.e. if a % b != 0), the result should be returned as a REAL. > create table t1( a integer, b integer); insert into t1 values(5,2); update t1 set a=a/b; If your rule above was in force, this would leave T1.A holding 2.5, which is incompatible with the way other database engines work. -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Rewriting a query
Robin Breathe <[EMAIL PROTECTED]> wrote: > Hugh Gibson wrote: > >> I'm intrigued. How do you get SQLite to use a multi-column index as it's > >> primary key (i.e. B-tree hash)? Please elaborate. > > > > Simply > > > > CREATE TABLE TransactionList (sTransactionID Text(13) DEFAULT > > '',sCommunityID Text(13) DEFAULT '',sObject Text(13) , PRIMARY KEY > > (sCommunityID, sTransactionID)) > > Ah, but it's not used for the B-tree hash (at least not according to the > documentation). > The ROWID is always used as the btree key (not hash!) on the main database btree. But when you have a PRIMARY KEY a separate index btree is also created which uses the PRIMARY KEY as its key. Question: Why is this important to you? (Side note: I am experimenting with a new Mail User Agent that uses SQLite to store all its email messages. I appologize in advance if this message is misformatted or otherwise garbled.) -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Problem/Bug: "SELECT 5 / 2;" returns 2 ?
Hello Jay Sprenkle, >I guess if you're going to use sqlite you're going to have to >force typing explicitly if you're doing math with the sql engine. Quite right, but even with explicit typing there are cases where SQLite3 can not be instructed to store REAL numbers as REALs: C:\>sqlite3 num.db3 SQLite version 3.2.1 Enter ".help" for instructions sqlite> CREATE TABLE i (a INTEGER, b INTEGER); sqlite> INSERT INTO i VALUES (5, 2); sqlite> SELECT a / b FROM i; 2 sqlite> INSERT INTO i VALUES (5.0, 2.0); sqlite> SELECT a / b FROM i; 2 2 Even though the 2nd insert explicitly types 5.0 and 2.0 as REALs, they are stored as INTEGERs internally and resuls in incorrect calculations (the very last number should be 2.5 and not 2). Summming up: Observations show that SQLite3 behaves inconsistent when storing and/or calculating numbers as INTEGERs or REALs. The point I am personally worried about is not so much the storage side of things but the calculation errors resulting from it. I would like to propose the resolve this inconsistency as follows: 1. If two REALs are divided, the result should be a REAL. This is the current behaviour of SQLite3. 2. If two INTEGERs are divided, the result should be INTEGER only if it can be represented as an INTEGER (i.e. if a % b = 0). 3. If the division of INTEGERs can not be stored as an INTEGER (i.e. if a % b != 0), the result should be returned as a REAL. I do understand that 3. implies a change in numeric type, but in oder to perform calculations with SQL the type change seems unavoidable, at least as long as SQLite3 implements only 2 of the 3 numeric types suggested in this thread. Regards, Ralf
Re: [sqlite] Problem/Bug: "SELECT 5 / 2;" returns 2 ?
The following table sums up the division findings from other SQL engines: |SELECT 5 / 2 | SELECT 5.0 / 2 | SELECT 5 / 2.0 | SELECT 5.0 / 2.0 -- SQLite3| 2 | 2.5| 2.5| 2.5 PostgreSQL | 2 | 2.5000 | 2.5000 | SQL*Plus | 2.5 ||| MySql | 2.50||| The implementation seems undecided, but I see that the "big players" Oracle SQL*Plus and MySql both do not strip off the decimal digits. Thanks to all who run some tests and provided the results. Feel free to add other engines or fill in the gaps. Regards, Ralf
Re: [sqlite] Rewriting a query
Hugh Gibson wrote:
>> I'm intrigued. How do you get SQLite to use a multi-column index as it's
>> primary key (i.e. B-tree hash)? Please elaborate.
>
> Simply
>
> CREATE TABLE TransactionList (sTransactionID Text(13) DEFAULT
> '',sCommunityID Text(13) DEFAULT '',sObject Text(13) , PRIMARY KEY
> (sCommunityID, sTransactionID))
Ah, but it's not used for the B-tree hash (at least not according to the
documentation).
>> Have you investigated the following to see how the optimizer deals with
>> it?
> ...snip...
> I'm not fluent in the VDBE code, but ISTM (and execution time confirms)
> that it's doing a table scan first to do the GROUP BY. Then it
> creates a temp table with the values from the list, and does a join (I
> suppose).
I thought it probably would, but worth trying :)
>> I think I'd normally err on keeping a filter table handy (temporary if
>> you wish):
>>
>> -- Initialise filter:
>> CREATE TABLE filter (sCommunityID TEXT);
>> CREATE INDEX filter_idx ON filter(sCommunityID);
>>
>> -- Configure filter:
>> INSERT INTO filter VALUES ('a03061bFi');
>> INSERT INTO filter VALUES ('a03064KDy');
>> INSERT INTO filter VALUES ('a03068QhK');
>>
>> -- Get your results:
>> SELECT sCommunityID, max(sTransactionID)
>> FROM filter NATURAL INNER JOIN TransactionList
>> GROUP BY sCommunityID;
>>
>> -- Clear filter ready for next time:
>> DELETE FROM filter;
>
> Thread safety is an issue here. Multiple threads may be doing this action.
> Also, by the (admittedly contorted) query I am using I can get the VDBE to
> create a temporary table for me and insert the data, taking a lot less
> time to do it than it would take to run all those queries.
You didn't mention threads :)
Robin
--
Robin Breathe, Computer Services, Oxford Brookes University, Oxford, UK
[EMAIL PROTECTED] Tel: +44 1865 483685 Fax: +44 1865 483073
signature.asc
Description: OpenPGP digital signature
