Re: [sqlite] Storing monetary values and calculations
John Stanton wrote: > > We have > added a DECIMAL type to Sqlite. This stores fixed point numbers in > ASCII display format and performs accurate artithmetic and presents > nicely from HTML. > In which release was the DECIMAL affinity added to SQLite? Can you specify the precision and scale as per SQL-92 standard? e.g. DECIMAL(9,2) ? John Stanton wrote: > > If you store money as an integer with an "implied decimal point" (a > familiar method for old-time COBOL programmers) it will work. We have > added a DECIMAL type to Sqlite. This stores fixed point numbers in > ASCII display format and performs accurate artithmetic and presents > nicely from HTML. > > Floating point numbers for money is a perennial trap for young players. > > Mikey C wrote: >> Hi there, >> >> Currently I am using a SQLite 3.x database that stores and calculates >> currency values using the column type NUMERIC (which I believe has a >> FLOAT >> affinity). >> >> However this leads to errors in storing values values in floating point >> representation. >> >> I guess there is no planned support for direct fixed point types, so what >> is >> the best approach? >> >> Store the monetary values in an INTEGER column and multiply all values up >> by >> 100 to store in pence/cents? >> >> Any advice? >> >> Thanks, >> >> Mike > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > -- View this message in context: http://www.nabble.com/Storing-monetary-values-and-calculations-tf4264034.html#a12140213 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Storing monetary values and calculations
Hi there, Currently I am using a SQLite 3.x database that stores and calculates currency values using the column type NUMERIC (which I believe has a FLOAT affinity). However this leads to errors in storing values values in floating point representation. I guess there is no planned support for direct fixed point types, so what is the best approach? Store the monetary values in an INTEGER column and multiply all values up by 100 to store in pence/cents? Any advice? Thanks, Mike -- View this message in context: http://www.nabble.com/Storing-monetary-values-and-calculations-tf4264034.html#a12135202 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Implementation of ANSI SQL-92 FOREIGN KEY and referential integrity
Hi, Does anyone know if there is a plan to implement the enforcement of the SQL-92 FOREIGN KEY constraints? Seems to me the No.1 missing feature. After all, data integrity, even in an embedded DB is very important and bugs in client code can easily mess up the referential integrity. Does appear odd that SQLite implements some of the less useful SQL-92 features and even goes as far as to parse FOREIGN KEY constraints but not raise an error when it encounters one? Thanks in advance. PS. I know you can sort of implement this with a load of triggers, but that seems a bit daft. -- View this message in context: http://www.nabble.com/Implementation-of-ANSI-SQL-92-FOREIGN-KEY-and-referential-integrity-tf4208807.html#a11972903 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] I Need database fot some test
Here is a database http://www.nabble.com/file/6997/Northwind.db Northwind.db It is an exact SQLite implementation of the well known Microsoft Northwind sample that can be found for MS Access and SQL Server. -- View this message in context: http://www.nabble.com/I-Need-database-fot-some-test-tf3342105.html#a9340880 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Backing up a SQlite database
This might be a dumb question, but is taking a backup of a live database simply a matter of copying the file to a backup device/drive? And restoring it a matter of copying it back? I am using Windows with NTFS drives. -- View this message in context: http://www.nabble.com/Backing-up-a-SQlite-database-tf3201601.html#a8889729 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] DRH - Future of SQLite?
May I ask what the dev plan is for SQLIte? For example, out of the missing SQL-92 features, what is likely to be implemented first to complete the standard? I would like to see referential integrity natively enforced No.1 and then support for stored procedures with cached query plans. Thanks, Mikey - Big SQLite fan -- View this message in context: http://www.nabble.com/DRH---Future-of-SQLite--tf3200150.html#a8884974 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Extension functions for SQLite in C for free
No problem, attached is the raw source code, no binaries. Ralf Junker wrote: > > Hello Mikey C, > >>If anyone is having problems downloading the file (which is large as it >>contains debug & release binaries and all the obj files), please email me at >>[EMAIL PROTECTED] and I'll email just the raw source code only. > > Would it be possible to upload just the raw source code as a separate > archive? > > Ralf > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > http://www.nabble.com/file/6303/SQLite.zip SQLite.zip -- View this message in context: http://www.nabble.com/Extension-functions-for-SQLite-in-C-for-free-tf3182921.html#a8854150 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Extension functions for SQLite in C for free
No problem. If anyone is having problems downloading the file (which is large as it contains debug & release binaries and all the obj files), please email me at [EMAIL PROTECTED] and I'll email just the raw source code only. Mikey Jay Sprenkle wrote: > > Thanks for sharing MIkey. > > On 2/6/07, Mikey C <[EMAIL PROTECTED]> wrote: >> >> >> Hi, >> >> I've had these functions hanging around for some time. They are not >> fully >> tested and come with no warranty of fitness, but if anyone wants the >> code, >> please take it. >> > > -- View this message in context: http://www.nabble.com/Extension-functions-for-SQLite-in-C-for-free-tf3182921.html#a8853730 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Extension functions for SQLite in C for free
Hi,
I've had these functions hanging around for some time. They are not fully
tested and come with no warranty of fitness, but if anyone wants the code,
please take it.
I have all the code as a MS Visual Studio 2003 project. It is based on
source code 3.3.5
abs(X) Return the absolute value of argument X.
acos(X) Return the angle Y in radians such that cos(Y)=X. The domain is
|X|≤1
acosh(X) Return the angle Y in radians such that cosh(Y)=X. The domain is
|X|≥1
asin(X) Return the angle Y in radians such that sin(Y)=X. The domain is
|X|≤1
asinh(X) Return the angle Y in radians such that sinh(Y)=X.
atan(X) Return the angle Y in radians such that tan(Y)=X.
atanh(X) Return the angle Y in radians such that tanh(Y)=X.
atn2(Y,X) Return the angle Z in radians such that tan(Z)=Y/X.
atan2(Y,X) An alias for atn2.
ceil(X) smallest integral value not less than X. eg: ceil(1.1)=2,
ceil(1.0)=1
charindex(X,Y,Z) given 2 strings X, Y and an integer Z (defaults to 1),
return the 1 based index such that from that index onward Y is equal to X
(for the length of X) and the index is greater than or equal to Z. If isn't
found, returns 0.
When X or Y are NULL returns NULL. When Z ≤ 0 assumes the search starts from
index 1.
eg: charindex("T","Teste",1)=1, charindex("st","Teste",1)=3,
charindex("T","Teste",2)=0
coalesce(X,Y,...) Return a copy of the first non-NULL argument. If all
arguments are NULL then NULL is returned. There must be at least 2
arguments.
cos(X) Return the cosine of the angle X where the angle is expressed in
radians.
cosh(X) Return the hyperbolic cosine of the angle X where the angle is
expressed in radians.
cot(X) Return the cotangent of the angle X where the angle is expressed in
radians.
coth(X) Return the hyperbolic cotangent of the angle X where the angle is
expressed in radians.
degrees(X) Returns the angle in degrees that corresponds to the angle X in
radians.
difference(X,Y) Returns the number of equal characters of the values of
soundex of X and Y.
exp(X) Returns E raised to the power X.
floor(X) largest integral value not greater than X.
eg: floor(1.9)=1, floor(1.0)=1
glob(X,Y) This function is used to implement the "X GLOB Y" syntax of
SQLite. The sqlite3_create_function() interface can be used to override this
function and thereby change the operation of the GLOB operator.
ifnull(X,Y) Return a copy of the first non-NULL argument. If both arguments
are NULL then NULL is returned. This behaves the same as coalesce() above.
last_insert_rowid() Return the ROWID of the last row insert from this
connection to the database. This is the same value that would be returned
from the sqlite_last_insert_rowid() API function.
leftstr(X,Y) Returns the Y first characters of the string X. When the length
of X is not greater than Y just returns X.
If X is NULL returns NULL.
eg: leftstr('123456789',2)='12', leftstr('123456789',20)='123456789'
length(X) Return the string length of X in characters. If SQLite is
configured to support UTF-8, then the number of UTF-8 characters is
returned, not the number of bytes.
like(X,Y [,Z]) This function is used to implement the "X LIKE Y [ESCAPE Z]"
syntax of SQL. If the optional ESCAPE clause is present, then the
user-function is invoked with three arguments. Otherwise, it is invoked with
two arguments only. The sqlite_create_function() interface can be used to
override this function and thereby change the operation of the LIKE
operator. When doing this, it may be important to override both the two and
three argument versions of the like() function. Otherwise, different code
may be called to implement the LIKE operator depending on whether or not an
ESCAPE clause was specified.
log(X) Returns Y such that exp(Y)=X.
The domain is X > 0
log10(X) Returns Y such that power(10,Y)=X.
The domain is X > 0
lower(X) Return a copy of string X will all characters converted to lower
case. The C library tolower() routine is used for the conversion, which
means that this function might not work correctly on UTF-8 characters.
ltrim(X) Returns a string equal to X but with all the whitespaces at the
begining removed.
Returns NULL when X is NULL.
eg: ltrim(' 1234 ')='1234 ', ltrim('1234 ')='1234 '
max(X,Y,...) Return the argument with the maximum value. Arguments may be
strings in addition to numbers. The maximum value is determined by the usual
sort order. Note that max() is a simple function when it has 2 or more
arguments but converts to an aggregate function if given only a single
argument.
min(X,Y,...) Return the argument with the minimum value. Arguments may be
strings in addition to numbers. The minimum value is determined by the usual
sort order. Note that min() is a simple function when it has 2 or more
arguments but converts to an aggregate function if given only a single
argument.
nullif(X,Y) Return the first argument if the arguments are different,
otherwise return NULL.
padc(X,Y) Returns the string X with added spaces at the
Re: [sqlite] Extra functions - New Project?
I entirely agree. I had the functions coded because I needed them for my own project. I never intended to do the other 85% of the work required to make them a supported part of SQLite. drh wrote: > > Mikey C <[EMAIL PROTECTED]> wrote: >> >> I sent the source code to DRH with the extra functions. I don't myself >> have >> the time now to incorporate the extra functions into SQLite. >> > > Writing code a chunk of code is only a small fraction of > the work needed to support the code in a maintained product > such as SQLite. Writing the code is, in fact, the easy > part. After the code is written, somebody then has to > develop regression tests that provide near 100% code > coverage. The code has to be documented. Then it has > to be maintained for years. By my estimate, writing code > is perhaps 15% of the total work. > > The code for the extra functions was submitted to me with > the promise that the author would provide no help in completing > the work of integration. In other words, the author did > about 15% of the work and left the other 85% to me. Such > a submission is often referred to as a "drive by patch". > > I'm happy to have help on SQLite. But if you contribute > code, you should finish the job. That means providing test > cases that give 100% code coverage, documentation, and being > available to support your code for years in the future. > If you write a bunch of code and toss it over the wall, > then please do not be disappointed if nobody picks it up. > > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > -- View this message in context: http://www.nabble.com/Extra-functions---New-Project--tf1674436.html#a6956723 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Extra functions - New Project?
Hi Rohit. I sent the source code to DRH with the extra functions. I don't myself have the time now to incorporate the extra functions into SQLite. I don't know if DRH plans to add the extra functions. If he does not and he doesn't mind, I am happy to send the source code to anyone that is interested. Regards, Mike RohitPatel wrote: > > Mike > > When are you planning to put code of your SQL functions for SQLite ? > > Waiting...eagerly... > I may try to use it in my app. > > Thanks > Rohit > > -- View this message in context: http://www.nabble.com/Extra-functions---New-Project--tf1674436.html#a6919718 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Optimistic concurrency control
Hi, Maybe I didn't make the question clear. I'm not talking about locking and multiple writers. I'm talking about optimistic concurrency control in a disconnected environment. Two processes (say a webserver). One reads some data and presents it to a user (open - read - close). The other reads the same same data and presents it to another user (open - read - close). The first user updates the data (open - write - close). Several seconds/minutes later the second user updates the same data (open - read - close). Result is the first users changes are lost. You can of course create a complex WHERE clause in all your SQL UPDATE statements so that an update only succeeds in changing a row if the all the column values match the original values. e.g. UPDATE ... SET col1 = new_value_1 col2 = new_value_2 WHERE col1 = old_value_1 AND col2 = old_value_2 etc. BUT (and here's what I'm asking) many databases (e.g. MS SQL Server) have a special data type or column in each table for each row which is an incrementing value. Whenever a row is written to, this value changes/increases. Hence your where clause needs only include: WHERE row_version_column = old_row_version_value IF anyone has changed the data since you last read it, the UPDATE affects no rows and you know your update failed due to optimistic concurrency failure. ALL I am asking is could SQLite give each table a special column that increases it's value for each row whenever data changes? I could implement is with a trigger on each table BUT it would be nice if SQLite supported this natively. Anyhow, I get from the tone of the answers that this is not likely to happen, so I'll code it up myself. Cheers, Mike Christian Smith-4 wrote: > > Mikey C uttered: > >> >> What are peoples thoughts on implementing optimistic concurrency control >> in >> SQLite? > > > Not an option. SQLite has a single writer database locking protocol which > can't handle multiple writers, so the issue of concurrency control is > moot. > > >> >> One way is modify the where clause to compare every column being updated, >> old value to new value. This makes the SQL cumbersome. >> >> Microsoft SQL Server has a column data type named TIMESTAMP/ROWVERSION >> which >> is database-wide unique number that gets updated every time a row gets >> updated. Effectively it is a hash of all the current values in every row >> of >> the table and is updated automtically. >> >> Hence to see if any row has been updated by another person you just have >> to >> compare the TIMESTAMP/ROWVERSION value you read with the one currently >> in >> the table in the UPDATE where clause. >> >> >> >> Q. Does SQlite has such a capability? Can we have one please? If not, is >> it >> easy to simulate one? If not, how do people manage concurrency in >> applications such as web sites? >> > > A. No. Probably not. Probably not. Use a client/server DB such as > PostgreSQL which already has multiple version concurrency control. > > Right tool for the job. If it's multiple concurrent writers, SQLite isn't > it. > > > Christian > > > > -- > /"\ > \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL > X - AGAINST MS ATTACHMENTS > / \ > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > -- View this message in context: http://www.nabble.com/Optimistic-concurrency-control-tf2299903.html#a6394076 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Optimistic concurrency control
What are peoples thoughts on implementing optimistic concurrency control in SQLite? One way is modify the where clause to compare every column being updated, old value to new value. This makes the SQL cumbersome. Microsoft SQL Server has a column data type named TIMESTAMP/ROWVERSION which is database-wide unique number that gets updated every time a row gets updated. Effectively it is a hash of all the current values in every row of the table and is updated automtically. Hence to see if any row has been updated by another person you just have to compare the TIMESTAMP/ROWVERSION value you read with the one currently in the table in the UPDATE where clause. Q. Does SQlite has such a capability? Can we have one please? If not, is it easy to simulate one? If not, how do people manage concurrency in applications such as web sites? -- View this message in context: http://www.nabble.com/Optimistic-concurrency-control-tf2299903.html#a6391291 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Partial indexes
Any plans to support partial indexes in SQLite? http://en.wikipedia.org/wiki/Partial_index http://db.cs.berkeley.edu/papers/ERL-M89-17.pdf PostgreSQL supports them and they seem very useful. -- View this message in context: http://www.nabble.com/Partial-indexes-tf2151623.html#a5941879 Sent from the SQLite forum at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] How does sqlite3_column_type work?
Hi, I've got a weird one. I'm using the ADO.NET wrapper and this makes two calls in order to determine a column affinity. First it calls sqlite3_column_decltype and if this returns null it calls sqlite3_column_type 99.% of the time this works great. However I have a piece of SQL that UNIONS two selects from the same table. For this particular piece of SQL a column defined as NUMERIC is return INT as the data type, even though the data contains floating point values. So for this column a call to sqlite3_column_type returns INT instead of NUMERIC??? A very similar piece of SQL with a UNION returns NUMERIC for the column. If I add 0.00 to the column (e.g. SELECT COLA + 0.00) it returns NUMERIC otherwise INT (again the actual values have a decimal point) Does anyone know how sqlite3_column_type determines the data type when sqlite3_column_decltype returns null and why sqlite3_column_type would return INT for a column full of floating point numbers in one case and NUMERIC in another? It's driving me nuts and making me wish I'd used Microsofts free SQL Server Express Edition in the 1st place :-( -- View this message in context: http://www.nabble.com/How-does-sqlite3_column_type-work--tf2045348.html#a5631570 Sent from the SQLite forum at Nabble.com.
Re: [sqlite] Compressing the DBs?
Not sure what you mean there DRH, but I set compression on one of my database files on NTFS and file size shrunk from 1,289,216 bytes to 696,320 bytes. And of course the whole compression / decompression process is completely transparent to SQLite and if you decide that compression is a bad thing, you just uncheck the box on that file and you are back to where you started. -- View this message in context: http://www.nabble.com/Compressing-the-DBs--tf1897195.html#a5199615 Sent from the SQLite forum at Nabble.com.
Re: [sqlite] Compressing the DBs?
What platform are you using? If you are using NTFS filesystem you can just mark the file for compression and the OS takes care of it transparently. -- View this message in context: http://www.nabble.com/Compressing-the-DBs--tf1897195.html#a5190175 Sent from the SQLite forum at Nabble.com.
[sqlite] Is this query correct?
select avg((select 100 union select 200)) Returns 100 I would have expected 150? Am I being thick or is it a bug? -- View this message in context: http://www.nabble.com/Is-this-query-correct--t1829679.html#a4991615 Sent from the SQLite forum at Nabble.com.
Re: [sqlite] suggesiton needed for using SQL lite in a situation
One things to bear in mind is will you need to query data across all users? Perhaps a report or some stats for all users? If so, this is much more problematic if you have one DB per user, since you would need to ATTACH all the separate files to enable the query to work. A single DB file, such reporting is simple. Just something to bear in mind. -- View this message in context: http://www.nabble.com/suggesiton-needed-for-using-SQL-lite-in-a-situation-t1828660.html#a4991206 Sent from the SQLite forum at Nabble.com.
Re: [sqlite] Delete performance vs. Insert performance
Might be obvious but make sure you do all your inserts and deletes within a single transaction as I believe this has a big impact on performance. Might bring the insert and delete times closer. -- View this message in context: http://www.nabble.com/Delete-performance-vs.-Insert-performance-t1823679.html#a4976020 Sent from the SQLite forum at Nabble.com.
[sqlite] Index usage
Hi, I just wanted to ask for confirmation that my understanding on how the query optimiser works is correct. SQLite only uses one index for each table in a FROM? What if tables are joined? Does an index get used for each joined table? So if I have SELECT * FROM A INNER JOIN B ON A.COL1 = B.COL1 INNER JOIN C ON C.COL1 = B.COL2 The query optimiser can use 3 indexes max? One on A, B and C Since B is joined on COL1 and COL2, only one join can use an index? If a WHERE is added: SELECT * FROM A INNER JOIN B ON A.COL1 = B.COL1 INNER JOIN C ON C.COL1 = B.COL2 WHERE A.COL2 = 'fred' Then again only one index can be used on table A? Either the join or the where? Is my understanding correct? -- View this message in context: http://www.nabble.com/Index-usage-t1817658.html#a4955210 Sent from the SQLite forum at Nabble.com.
Re: [sqlite] SQLite Vs VistaDB - Comparison ???
Okay I know very little about these things, but the fact that Access/JET MDB files are serverless (it's just a bunch of Windows dll's) in the same way as SQLite, and that JET implements row and table level locking means I guess it is possible. If it meant losing ACID compliance, then no, forget about it, but if it meant much bigger database files, then no problem, as long as the row level locking could be turned on or off at compile time (i.e. those who don't care about row level locking, but do care about file size can compile without it). So if it can be implemented by storing a lock record for every row that is about to be updated in a new system table, then why not? Of course row level locking will make updates slower, but you can't have fine grained locking and ultimate performance. As I say, if it could be implemented knowing that: 1. Performance will be slower. 2. Database size will be bigger. 3. Row level locking can be compiled in or out. Then I think the majority of users would want the benefit of increased write concurrency, even at the expense of speed or database file size. -- View this message in context: http://www.nabble.com/SQLite-Vs-VistaDB---Comparison-t1797052.html#a4902745 Sent from the SQLite forum at Nabble.com.
Re: [sqlite] SQLite Vs VistaDB - Comparison ???
MS Access (MDB files) use the Jet engine. Not every PC has the correct drivers, since jet has changed many times as Access evolved from version 2.0 thru 95, XP and 2003. Access is NOT ACID compliant, is limited in maximum database size, is limited to 255 connections. http://www.somacon.com/p369.php However, JET's biggest gain over SQLite is it supports table and row level locking. If D. Hipp were to implement a fine grained locking mechanism in SQLite, we'd be onto a winner. Please implement table and row level locking. :-) -- View this message in context: http://www.nabble.com/SQLite-Vs-VistaDB---Comparison-t1797052.html#a4899327 Sent from the SQLite forum at Nabble.com.
[sqlite] integrity error
One of my databases reports the following error, although in practice the database seems fine: *** in database main *** Page 101 is never used Any ideas/suggestions? Is this something to worry about? Can it be "fixed"? Cheers, Mike -- View this message in context: http://www.nabble.com/integrity-error-t1764345.html#a4802179 Sent from the SQLite forum at Nabble.com.
Re: [sqlite] Extra functions - New Project?
Cool, I all ready have the code for a library of functions working with SQLite V3.3.5 source. This has been integrated into the SQLite code at compile time using conditional compilation. Math functions: acos asin atan atn2 atan2 acosh asinh atanh degrees radians cos sin tan cot cosh sinh tanh coth exp log log10 power sqrt square sign ceil floor pi String functions: replicate charindex charindex leftstr rightstr ltrim rtrim trim reverse proper padl padr padc strfilter difference Aggregate functions: stdev variance mode percentiles (median, lowerquartile, upperquartile) I will be releasing all the code after testing to the public domain. drh, are you interested in putting this code on your website? Thanks, Mike -- View this message in context: http://www.nabble.com/Extra-functions---New-Project--t1674436.html#a4789862 Sent from the SQLite forum at Nabble.com.
[sqlite] case insensitive joins and comparisons
What is the best/most efficient way to perform comparisons or joins on data where case sensivitiy is not important? e.g join two tables where the primary and foreign key values have different case? Best to use Like or upper() or Lower() or some other way of ignoring case? Thanks, Mike -- View this message in context: http://www.nabble.com/case-insensitive-joins-and-comparisons-t1736367.html#a4718438 Sent from the SQLite forum at Nabble.com.
Re: [sqlite] OffTopic: Locking on Windows XP
Don't know if this helps: http://support.microsoft.com/kb/Q296264 Looks like you need to turn optimistic locking off. Same is true with MS Access if the file is located on a share. http://support.microsoft.com/default.aspx?scid=KB;EN-US;q300216; -- View this message in context: http://www.nabble.com/OffTopic%3A+Locking+on+Windows+XP-t1718929.html#a4669102 Sent from the SQLite forum at Nabble.com.
[sqlite] What's planned in the next major release of SQLite?
Anyone know where SQLite is going? Is there a roadmap for features? -- View this message in context: http://www.nabble.com/What%27s+planned+in+the+next+major+release+of+SQLite--t1711756.html#a4647686 Sent from the SQLite forum at Nabble.com.
[sqlite] Compiler optimisations
Just a note for anyone interested, I originally took the Windows DLL 3.3.5 and benchmarked a particular query. Takes 2.4 seconds on a reasonable dataset. Took the src, compiled up in VS.NET 2003, with lots of speed optimisations, Pentium 4 and above, SSE on etc. Same query takes 1.6 seconds, so if speed is your prime concern and you know the target machine architecture, you can get some good perf increases by compiling up the src rather than taking the binary as is. And the binary file is actually slightly smaller than the unoptimised DLL taken from the site. -- View this message in context: http://www.nabble.com/Compiler+optimisations-t1710837.html#a4644937 Sent from the SQLite forum at Nabble.com.
[sqlite] RE: .NET bindings or Csharp wrapper
I use the Finisar src (taken from CVS so it has all the latest bug fixes), .NET 1.1 SP1 and SQLite 3.3.5 (compiled up myself from source with compiler speed and pentium 4 optimisations on) and have no issues at all. But your mileage may vary. -- View this message in context: http://www.nabble.com/.NET+bindings+or+Csharp+wrapper-t1710138.html#a4644867 Sent from the SQLite forum at Nabble.com.
Re: [sqlite] LIMIT and paging records
I don't think you really understand what I'm trying to say. Web based systems require paging that does not iterate through all records. What is required is a means to LIMIT the results read from the database but at the same time know how many records WOULD have been returned if the query was not LIMITed. In this way it is then possible to display results to the user: Displaying Page 15 of 25788 pages. Record 1 Record 2 Record ... Record 10 Previous Page Link | Next Page Link LIMIT gives this great power (ie. I only want to read 10 record starting from record 151) BUT I do need to know how many records there are in total for this query in order to page correctly and display how many pages there are to the user. I'd rather SQLite reads on 10 records and not 100 million records into memory, just to discard them all except the 10 the user needs for that page of results? Does this make sense? Do you imagine Google loads 8 billions records into memory when the user is just viewing 10 results in page 5 after a broad search? -- View this message in context: http://www.nabble.com/LIMIT+and+paging+records-t1698512.html#a4612492 Sent from the SQLite forum at Nabble.com.
[sqlite] LIMIT and paging records
Hi, I think this has been discussed before, but I can't find a good solution so I'll post it again to see what people think. Here's the problem. I have a large number of records in a table, which contains many columns. Hence a large amount of data. I have a SQL query that filters the results by search criteria across many of these columns. I am using a web front end to display paged results. I need to tell the user how many records there are in total, how many pages and which page they are viewing. I would like to use the LIMIT keyword to restrict the result using the two parameters (offset and limit count) so that I do not waste resources loading up 1000's of records just to discard the ones not on the current page. However if LIMIT is added to the SQL, I do not get a count of the records that the SQL select would have produced if I had not limited the query with LIMIT. I could do two selects with the same WHERE restriction, one with SELECT COUNT(*) and the other with SELECT field1, field2, etc but this seems wasteful, especially if the query is expensive in resources. Perhaps a new function could be added to SQLite that returns the record count regardless of any LIMIT applied? e.g. SELECT field1, field2, field3, fieldN, count_rows() FROM table1 WHERE field99 LIKE 'G%' AND field66 = 7 OR field18 <= 5.7 LIMIT 341, 10 will return a maximum of 10 records but the count_rows() will return how many rows the query would return if the LIMIT was not in place? This would make paging of results very straight foreward and efficient. Anyone agree/disagree this would be useful? -- View this message in context: http://www.nabble.com/LIMIT+and+paging+records-t1698512.html#a4609360 Sent from the SQLite forum at Nabble.com.
Re: [sqlite] Relative query cost
Thanks Marco, I'll take a look and if it compares well to may current favourite GUI http://www.kraslabs.com/sqlite_analyzer.php I'll be buying a copy! If you feel up to the challenge, perhaps you can convert the output of the explain statement into a graphical tool, SQL Server style. -- View this message in context: http://www.nabble.com/Relative+query+cost-t1691739.html#a4592472 Sent from the SQLite forum at Nabble.com.
[sqlite] Relative query cost
Hi, Does anyone know of a tool that can use the output of the explain statement to produce something akin to: http://www.nldelphi.com/artimages/sqls38.jpg Which is what MS SQL Server gives. When developing queries it would be good to see how different queries compare in terms of relative cost. eg. http://www.sql-server-performance.com/images/jc_sql_server_quantative_analysis2_image010.jpg Could such an output be derived from the explain in SQLite, but in a simple graphical output with relative (%) cost? Thanks, Mike -- View this message in context: http://www.nabble.com/Relative+query+cost-t1691739.html#a4590951 Sent from the SQLite forum at Nabble.com.
[sqlite] last_insert_rowid()
Hi, Does last_insert_rowid() return the identity of the main insert or would it return the id of a row inserted by a trigger if the main insert caused a 2nd insert to occur on another table via a trigger? SQL Server has a scope_identity() function to make sure you can get the original id regardless of any tigger that might have fired. http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sa-ses_6n8p.asp What does SQLite have to ensure a trigger does not mess up the retrieval of the last row id? Thanks, Mike -- View this message in context: http://www.nabble.com/last_insert_rowid%28%29-t1690790.html#a4588358 Sent from the SQLite forum at Nabble.com.
Re: [sqlite] Extra functions - New Project?
These are the functions that I tend to have implemented: Numeric Functions: Sqrt Floor Ceiling Sign Pi - constant function 3.141.. ACos ASin ATan Atn2 Cos Cot Degrees Exp Log Log10 Power Radians Sin Square Tan String Functions: Charindex Patindex Left Right LTrim RTrim Trim Replicate Reverse Replace Difference - numeric diff in Soundex values using built in soundex function. Aggregate Functions: StdDev Variance Median - Possibly a more flexible function Percentile where 0.5 is the Median Mode - Most frequently occuring value -- View this message in context: http://www.nabble.com/Extra+functions+-+New+Project--t1674436.html#a4563121 Sent from the SQLite forum at Nabble.com.
Re: [sqlite] Extra functions - New Project?
With some assistance I intend to implement pretty much all the SQL Server 2000 arithmetic and string functions into SQLite 3 codebase as well as a few others, such as aggregates for StdDev and Variance. I will then release the source under the same license as SQLite itself. If anyone has any comments or suggestions, please let me know. -- View this message in context: http://www.nabble.com/Extra+functions+-+New+Project--t1674436.html#a4559014 Sent from the SQLite forum at Nabble.com.
Re: [sqlite] SQLite under mono
http://www.mono-project.com/SQL_Lite -- View this message in context: http://www.nabble.com/SQLite+under+mono-t1680769.html#a4558894 Sent from the SQLite forum at Nabble.com.
Re: [sqlite] Extra functions - New Project?
Roberto-10 wrote: > > On 24/05/06, Christian Smith <[EMAIL PROTECTED]> wrote: >> Attach a patch to the ticket that implements your new functions. Send >> your >> declaration of dedication of the code to the public domain to the list, >> and hope DRH includes the patch in the next release. > > IIRC, That has been suggested in the past, the consensus was to not > include extra functions, in keeping with the 'lite' in the project > name. > > I can see the argument for this, but these extra functions are part of the ANSI SQL-92 spec, so it is in keeping with the aim of achieving 100% SQL-92 compatibility? Otherwise you might say make it lighter, ditch triggers, views and most of the the other SQL already implemented? -- View this message in context: http://www.nabble.com/Extra+functions+-+New+Project--t1674436.html#a4543591 Sent from the SQLite forum at Nabble.com.
Re: [sqlite] Extra functions - New Project?
I would rather add these functions directly to the core SQLite DLL in C in
and compile them directly into the code (using a conditional).
For example on the web I found an example of adding a sign() function:
/*
** Implementation of the sign() function
*/
static void signFunc(sqlite3_context *context, int argc, sqlite3_value
**argv){
assert( argc==1 );
switch( sqlite3_value_type(argv[0]) ){
case SQLITE_INTEGER: {
i64 iVal = sqlite3_value_int64(argv[0]);
/* 1st change below. Line below was: if( iVal<0 ) iVal = iVal * -1; */
iVal = ( iVal > 0) ? 1: ( iVal < 0 ) ? -1: 0;
sqlite3_result_int64(context, iVal);
break;
}
case SQLITE_NULL: {
sqlite3_result_null(context);
break;
}
default: {
/* 2nd change below. Line for abs was: if( rVal<0 ) rVal = rVal * -1.0; */
double rVal = sqlite3_value_double(argv[0]);
rVal = ( rVal > 0) ? 1: ( rVal < 0 ) ? -1: 0;
sqlite3_result_double(context, rVal);
break;
}
}
}
They then register this function by adding it to the array of existing
functions:
} aFuncs[] = {
{ "min", -1, 0, SQLITE_UTF8,1, minmaxFunc },
{ "min",0, 0, SQLITE_UTF8,1, 0 },
{ "max", -1, 2, SQLITE_UTF8,1, minmaxFunc },
{ "max",0, 2, SQLITE_UTF8,1, 0 },
{ "typeof", 1, 0, SQLITE_UTF8,0, typeofFunc },
{ "length", 1, 0, SQLITE_UTF8,0, lengthFunc },
{ "substr", 3, 0, SQLITE_UTF8,0, substrFunc },
{ "substr", 3, 0, SQLITE_UTF16LE, 0, sqlite3utf16Substr },
{ "abs",1, 0, SQLITE_UTF8,0, absFunc},
/* Added here */
{ "sign", 1, 0, SQLITE_UTF8,0, signFunc },
{ "round", 1, 0, SQLITE_UTF8,0, roundFunc },
{ "round", 2, 0, SQLITE_UTF8,0, roundFunc },
This seems to work (I've tried it).
HOWEVER, it means altering func.c and I was looking for how to add these
functions in a separate C file without having to alter any existing code?
Anyone any ideas how best to extend the codebase of SQLite with minimal
alteration to existing code?
Cheers,
Mike
--
View this message in context:
http://www.nabble.com/Extra+functions+-+New+Project--t1674436.html#a4542123
Sent from the SQLite forum at Nabble.com.
Re: [sqlite] Extra functions - New Project?
Thanks for the response. I did think of this, but this is a pain since: 1. I am using the Finisar ADO.NET provider and to do this these functions would need to be registered every time the database connection is opened and closed and I don't want to have to mess with the ADO.NET provider code. 2. I would like these extra functions to always be availabe to me (and others), regardless of which project I am working on. 3. They help complete the SQL-92 features since these functions are defined in the standards (CharIndex in MS SQL Server is Position in SQL-92 spec) 4. I am not concerned with footprint size since I use SQLite on desktops and web servers where RAM and CPU power is not an issue. I guess there is a way to use a new C source file (e.g. funcext.c and funcext.h) for these extra functions and compile them in using conditional compilation? If anyone knows what funcext.c and funcext.h might look like I could get started on someone with good C coding skills to implement all the missing SQL-92 scalar and aggregate functions into these files. I would then put them out in the public domain under the same license as SQLite itself (ie. do what you like with them). Thanks Mike -- View this message in context: http://www.nabble.com/Extra+functions+-+New+Project--t1674436.html#a4541011 Sent from the SQLite forum at Nabble.com.
[sqlite] Extra functions - New Project?
Hi, I am in need of some new SQL functions and wanted to ask advice on the best way to integrate these functions into SQLite 3. I am not a proficient C coder unfortunately. I have compiled the source for 3.5.5 using Visual Studio.NET 2003 and all works fine. I have added a couple of simple functions into func.c and these work. Great so far. However it would be good if there were a project somewhere to collate extension functions into a set of C files to enable a more powerful version of SQLite. I have found a few already on the web. Eg. http://www.brayden.org/twiki/bin/view/Software/SqliteExtensions#SQLite_Extensions What I am looking for specifically are more powerful string manipulation functions that mimic Microsoft SQL Server. In order of importance: charindex - This one is a show stopper for me. Need this function badly. patindex ltrim rtrim replace difference (integer diff on soundex values) What is the best way forward? Have someone develop these and add them directly to func.c or (to aid upgrading) create a new source and header file and add them to the project? How can new functions be added without removing the ability to upgrade the source to 3.5.6 etc when patches are released to func.c? Does anyone know how these string functions might be implemented? Any help appreciated. Thanks, Mike -- View this message in context: http://www.nabble.com/Extra+functions+-+New+Project--t1674436.html#a4539325 Sent from the SQLite forum at Nabble.com.
Re: [sqlite] Integer / Numeric calculations
Thanks for taking the time John, It does not seem to matter what the underlying column type is defined at, SELECT 42 / 9 will always return an integer division. I fool SQLite by always adding 0.00 to my numbers incase they happen to be integer values in that row, SELECT (col1 + 0.00) / (col2 + 0.00) However this feels like a bodge. Casting does not fix it either. SELECT CAST(CAST(col1 AS NUMERIC) / CAST(col2 AS NUMERIC) AS NUMERIC) still returns an integer if col1 and col2 happen to contain integer values. What does everyone else do to ensure the division always returns a float? Mike -- View this message in context: http://www.nabble.com/Integer+-+Numeric+calculations-t1661126.html#a4501777 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.
