[sqlite] Improper error message
Hi,
Some other DBMS such as Postgres and SQL-Server implement the functions
"left" and "right". SQLite does not. But why do I get confusing error
messages when I (mistakenly) attempt to use them?
To illustrate my point:
select foo('abc', 2)
Error: no such function: foo
select left('abc', 2)
Error: near "(": syntax error
select right('abc', 2)
Error: near "(": syntax error
In the 2nd and 3rd cases I would expect a "no such function" error. The
"syntax error" messages are slightly misleading, they lead the user to
think that he has forgotten a parenthesis or a comma somewhere in the query.
As for "left", it's maybe due to the ambiguity with "left join", but then
what about "right"? (There is no ambiguity with "right join" since it is
not supported.)
Thanks,
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] User-defined types
What you're looking for seems similar to LINQ to SQLite (System.Data.SQLite). When programming in C#, I don't code any SQL. I use a strongly-typed interface that then generates SQL queries in the background. Besides LINQ, you could create another interface that suits your needs, and that can then communicate with any database since all databases recognize SQL. Nothing needs to change on SQLite's side. My 2 cents. Etienne
[sqlite] LINQ to SQLite Cannot Update: "Store update, insert, or delete statement affected an unexpected number of rows"
Ok. Setting BinaryGUID=False in the connection does work. However, there are weveral bugs. 1. In the Server Explorer, if you edit advanced settings to set Binary Guid to False, it will generate "binary guid=False" instead of "BinaryGUID=False" 2. When generating LINQ Select queries and converting parameters into values, GUID fields will be converted into String no matter if BinaryGUID is True or False. For now I can work with Text values and BinaryGUID=False. Fixing the issue with long queries failing is a higher priority. Etienne
[sqlite] LINQ to SQLite Cannot Update: "Store update, insert, or delete statement affected an unexpected number of rows"
I did some more tests. The way it handles GUID between text and binary is screwed up. When updating data, it will update as binary even if BinaryGUID is false. When selecting data, however, it tries to select it as plain text so nothing matches the query. Etienne From: mystery...@hotmail.com To: sqlite-users at mailinglists.sqlite.org Subject: RE: [sqlite] LINQ to SQLite Cannot Update: "Store update, insert, or delete statement affected an unexpected number of rows" Date: Sun, 31 May 2015 22:40:56 -0600 Oh. I see what you were trying to say. The error I was having was not because GUID isn't supported, but because it was trying to interpret the data as binary while it was stored as text, thus the request failed. I just tried creating a new table with a primary key of type 'uniqueidentifier', and adding data into it. It shows up as binary data. I also tried adding "binary guid=False" to the connection string, but this parameter doesn't have any effect. It still treats the column as binary. Etienne
[sqlite] LINQ to SQLite Cannot Update: "Store update, insert, or delete statement affected an unexpected number of rows"
Oh. I see what you were trying to say. The error I was having was not because GUID isn't supported, but because it was trying to interpret the data as binary while it was stored as text, thus the request failed. I just tried creating a new table with a primary key of type 'uniqueidentifier', and adding data into it. It shows up as binary data. I also tried adding "binary guid=False" to the connection string, but this parameter doesn't have any effect. It still treats the column as binary. Etienne
[sqlite] LINQ to SQLite Cannot Update: "Store update, insert, or delete statement affected an unexpected number of rows"
The point I'm trying to make is not about whether GUID gets stored as binary or text... but rather that currently it's not working at all. There's no reason why GUIDs shouldn't be supported and should crash when trying to do an update. Etienne > From: sqlite at mistachkin.com > To: sqlite-users at mailinglists.sqlite.org > Date: Sun, 31 May 2015 12:41:24 -0700 > Subject: Re: [sqlite] LINQ to SQLite Cannot Update: "Store update, insert, > or delete statement affected an unexpected number of rows" > > > Etienne Charland wrote: > > > > When defining a column as "uniqueidentifier" in SQLite, it still stores > the > > data as text. It would be the .NET provider's job to convert it back and > > forth to Guid. > > > > This depends on the value of the BinaryGUID connection string property. You > might want to experiment with setting this property to false (it defaults to > true). > > -- > Joe Mistachkin > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] LINQ to SQLite Cannot Update: "Store update, insert, or delete statement affected an unexpected number of rows"
SQLite doesn't have support for many data types, but in fact it just doesn't have many restrictions. It doesn't have data types but affinities. When defining a column as "uniqueidentifier" in SQLite, it still stores the data as text. It would be the .NET provider's job to convert it back and forth to Guid. Since it already detects those columns as Guid, and it can easily convert the data properly, there's no reason why it shouldn't work. So I guess it's a bug that Guid columns as primary keys aren't working with System.Data.SQLite Sure it can be worked around by replacing Guid with String everywhere in the code, but there's no reason why it shouldn't be working in the first place. Etienne From: mystery...@hotmail.com To: sqlite-users at mailinglists.sqlite.org Subject: RE: [sqlite] LINQ to SQLite Cannot Update: "Store update, insert, or delete statement affected an unexpected number of rows" Date: Sat, 30 May 2015 10:45:36 -0600 Thanks! If I change the column type from UNIQUEIDENTIFIER to TEXT, it works. Only thing is, the code then maps to String instead of Guid. Is there any way to keep the .NET code working with Guid classes? I'm also seeing that BIT data type isn't supported and should be replaced by INT. However, leaving the data type as BIT does get mapped as Boolean and it still works even though it shouldn't. Any side-effect that could result from this? If it works it works... Then Single data types have to be replaced by Double, which is no big deal. Etienne
[sqlite] String.Substring Generates Error
> From: sqlite at mistachkin.com > To: sqlite-users at mailinglists.sqlite.org > Date: Sat, 30 May 2015 14:40:52 -0700 > Subject: Re: [sqlite] String.Substring Generates Error > > > Etienne Charland wrote: > > > > SQLite error (1): no such function: Substring > > -- Failed in 89 ms with error: SQL logic error or missing database > > no such function: Substring > > > > Thanks for the example. This is now fixed on trunk, here: > > https://system.data.sqlite.org/index.html/info/52085afe231b9e3e Awesome! Are you going to fix the code breaking long queries as well? > > -- > Joe Mistachkin > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Query returns "stack overflow error"
Thing is, I have no control over how the query gets generated. Each subquery (or project) is calculating one of 9 columns. If you want to try running the query, here's the database https://www.spiritualselftransformation.com/misc/NaturalGroundingVideos.zip Here's the query https://www.spiritualselftransformation.com/misc/LinqSQLiteError.txt I'm running against SQLite that comes with NuGet package: System.Data.SQLite (x86/x64) v1.0.97.0 Etienne
[sqlite] Query returns "stack overflow error"
The version of SQLite I'm using is the NuGet package System.Data.SQLite (x86/x64) v1.0.97.0 I tried loading the data in several queries with a cache, but performance was worse and it added several problems, such as the inability to sort using these calculated columns! That's not an option. I have to run it all at once. Etienne
[sqlite] Query returns "stack overflow error"
As an update, replacing Guid with String in the database and code worked. I got everything to work except the "parser stack overflow" error. The application is much snappier than when running with a SQL Server LocalDB database. Is there any way to solve this parser error? One work-around could be to first load the main data of my grid and afterwards load the extra columns asynchronously... which would allow me to cache the calculated data and improve performance. But that would require considerable coding. Any better solution? Thanks, Etienne
[sqlite] LINQ to SQLite Cannot Update: "Store update, insert, or delete statement affected an unexpected number of rows"
Thanks! If I change the column type from UNIQUEIDENTIFIER to TEXT, it works. Only thing is, the code then maps to String instead of Guid. Is there any way to keep the .NET code working with Guid classes? I'm also seeing that BIT data type isn't supported and should be replaced by INT. However, leaving the data type as BIT does get mapped as Boolean and it still works even though it shouldn't. Any side-effect that could result from this? If it works it works... Then Single data types have to be replaced by Double, which is no big deal. Etienne
[sqlite] LINQ to SQLite Cannot Update: "Store update, insert, or delete statement affected an unexpected number of rows"
I think I found where the problem is coming from. I did an automated database conversion from SQL Server. Most primary keys are GUIDs. In SQLite, these columns are still defined as 'uniqueidentifier' which is not a valid data type. If I try updating a table that has INT primary key, then it works. So the problem is with the GUID primary key. SQLite doesn't seem to support this data type, but it kind of does. Can System.Data.SQLite handle these fields properly? How should I define these columns? I also think I read something about needing extra code when inserting new data where the key isn't auto-generated by the database, but can't find where I read that. So... what needs to be done to get this to work? Etienne > From: mysteryx93 at hotmail.com > To: sqlite-users at mailinglists.sqlite.org > Date: Fri, 29 May 2015 20:12:54 -0600 > Subject: Re: [sqlite] LINQ to SQLite Cannot Update: "Store update, insert, or > delete statement affected an unexpected number of rows" > > I tried this very basic code on a very basic table that contains no DATETIME > field. > > var Obj = context.MediaCategories.FirstOrDefault(); > Obj.Folder = "test"; > context.SaveChanges(); > > The table has this format > - MediaCategoryId GUID Primary Key > - MediaTypeId INT > - Name NVARCHAR > - Folder NVARCHAR > > I can't get any more basic than that! > > The generated query is this > > > UPDATE [MediaCategories] > SET [Folder] = @p0 > WHERE ([MediaCategoryId] = @p1); > > -- @p0: 'test' (Type = String) > > -- @p1: '7320f1c6-1834-4c81-a16f-12b733d76038' (Type = Guid) > > -- Executing at 29/05/2015 9:11:54 PM -05:00 > > -- Completed in 0 ms with result: 0 > > > If I run this query manually, it works. > > > Etienne > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Query returns "stack overflow error"
It gets generated across various classes and functions, but this code generates
the columns and subqueries.
public static IQueryable
QueryVideoListItem(IQueryable query, SearchSettings settings) {
if (settings == null)
settings = new SearchSettings();
var Result = (from v in query
select new VideoListItem() {
MediaId = v.MediaId,
MediaType = (MediaType)v.MediaTypeId,
Artist = v.Artist,
Title = v.Title,
Album = v.Album,
MediaCategoryId = v.MediaCategoryId,
FileName = v.FileName,
Preference = v.Preference,
Length = v.Length,
HasDownloadUrl = (v.DownloadUrl != ""),
BuyUrl = v.BuyUrl,
PhysicalMasculine = (from r in v.MediaRatings
where r.RatingCategory.Name
== "Physical Masculine"
select
r.DbGetValue(r.Height, r.Depth, settings.RatingRatio)).FirstOrDefault(),
PhysicalFeminine = (from r in v.MediaRatings
where r.RatingCategory.Name
== "Physical Feminine"
select r.DbGetValue(r.Height,
r.Depth, settings.RatingRatio)).FirstOrDefault(),
EmotionalMasculine = (from r in v.MediaRatings
where r.RatingCategory.Name
== "Emotional Masculine"
select
r.DbGetValue(r.Height, r.Depth, settings.RatingRatio)).FirstOrDefault(),
EmotionalFeminine = (from r in v.MediaRatings
where r.RatingCategory.Name
== "Emotional Feminine"
select
r.DbGetValue(r.Height, r.Depth, settings.RatingRatio)).FirstOrDefault(),
SpiritualMasculine = (from r in v.MediaRatings
where r.RatingCategory.Name
== "Spiritual Masculine"
select
r.DbGetValue(r.Height, r.Depth, settings.RatingRatio)).FirstOrDefault(),
SpiritualFeminine = (from r in v.MediaRatings
where r.RatingCategory.Name
== "Spiritual Feminine"
select
r.DbGetValue(r.Height, r.Depth, settings.RatingRatio)).FirstOrDefault(),
//Love = (from r in v.MediaRatings
//where r.RatingCategory.Name == "Love"
// select r.DbGetValue(r.Height,
r.Depth, settings.RatingRatio)).FirstOrDefault(),
//Egoless = (from r in v.MediaRatings
//where r.RatingCategory.Name == "Egoless"
// select r.DbGetValue(r.Height,
r.Depth, settings.RatingRatio)).FirstOrDefault(),
//Custom =
string.IsNullOrEmpty(settings.RatingCategory) ? null :
//(from r in v.MediaRatings
//where r.RatingCategory.Name ==
settings.RatingCategory && r.RatingCategory.Custom
//select r.DbGetValue(r.Height, r.Depth,
settings.RatingRatio)).FirstOrDefault(),
IsInDatabase = true
});
return Result;
}
Etienne
[sqlite] String.Substring Generates Error
How about this line of code
var Obj = context.MediaCategories.Select(c => "ABC".Substring(1) !=
null).ToList();
It produces
SELECT
CASE WHEN (Substring('ABC', 1 + 1, (LENGTH('ABC')) - 1) IS NOT NULL) THEN 1
WHEN (Substring('ABC', 1 + 1, (LENGTH('ABC')) - 1) IS NULL) THEN 0 END AS [C1]
FROM [MediaCategories] AS [Extent1]
-- Executing at 29/05/2015 9:17:06 PM -05:00
SQLite error (1): no such function: Substring
-- Failed in 89 ms with error: SQL logic error or missing database
no such function: Substring
[sqlite] LINQ to SQLite Cannot Update: "Store update, insert, or delete statement affected an unexpected number of rows"
I tried this very basic code on a very basic table that contains no DATETIME field. var Obj = context.MediaCategories.FirstOrDefault(); Obj.Folder = "test"; context.SaveChanges(); The table has this format - MediaCategoryId GUID Primary Key - MediaTypeId INT - Name NVARCHAR - Folder NVARCHAR I can't get any more basic than that! The generated query is this UPDATE [MediaCategories] SET [Folder] = @p0 WHERE ([MediaCategoryId] = @p1); -- @p0: 'test' (Type = String) -- @p1: '7320f1c6-1834-4c81-a16f-12b733d76038' (Type = Guid) -- Executing at 29/05/2015 9:11:54 PM -05:00 -- Completed in 0 ms with result: 0 If I run this query manually, it works. Etienne
[sqlite] Query returns "stack overflow error"
I can get the query to run if I remove enough columns. This query fails with 1 column (subquery) beyond what is allowed to run. https://www.spiritualselftransformation.com/misc/LinqSQLiteError.txt Etienne
[sqlite] Query returns "stack overflow error"
Artist],
[Extent1].[Title] AS [Title],
[Extent1].[Album] AS [Album],
[Extent1].[MediaCategoryId] AS [MediaCategoryId],
[Extent1].[FileName] AS [FileName],
[Extent1].[Preference] AS [Preference],
[Extent1].[Length] AS [Length],
[Extent1].[DownloadUrl] AS [DownloadUrl],
[Extent1].[BuyUrl] AS [BuyUrl],
(SELECT
([Extent2].[Height] * [Extent2].[Depth]) AS [C1]
FROM [MediaRatings] AS [Extent2]
INNER JOIN [RatingCategories] AS [Extent3] ON [Extent2].[RatingId] =
[Extent3].[RatingId]
WHERE ([Extent1].[MediaId] = [Extent2].[MediaId]) AND ('Physical Masculine' =
[Extent3].[Name]) LIMIT 1) AS [C1]
FROM
[Media] AS [Extent1]
WHERE
[Extent1].[MediaTypeId] = 0
) AS
[Project2]
) AS [Project3]
) AS [Project5]
) AS [Project6]
) AS [Project8]
) AS [Project9]
) AS [Project11]
) AS [Project12]
) AS [Project14]
) AS [Project15]
) AS [Project17]
) AS [Project18]
) AS [Project20]
) AS [Project21]
) AS [Project23]
) AS [Project24]
) AS [Project26]
) AS [Project27]
ORDER BY [Project27].[C4] DESC, [Project27].[Artist] ASC, [Project27].[Title]
ASC
> From: slavins at bigfraud.org
> Date: Sat, 30 May 2015 01:40:23 +0100
> To: sqlite-users at mailinglists.sqlite.org
> Subject: Re: [sqlite] Query returns "stack overflow error"
>
>
> On 30 May 2015, at 1:26am, Etienne Charland wrote:
>
> > there are requests that are very long because they return a grid where each
> > column must be calculated individually, and this crashes with "stack
> > overflow error". These queries run fast on a SQL Server database -- and not
> > at all with SQLite.
> >
> > How can I get these queries to run?
>
> Does the query run if you submit it with the SQLite shell tool ?
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] String.Substring Generates Error
There is a bug with String.Substring generating SUBSTRING instead of SUBSTR,
which results in the SQL query failing.
Here's a way to fix this bug.
In your database model, add this code right before EntityContainer
In your context class (create a partial class next to it), add this code
[DbFunction("MyModel.Store", "substr")]
public string SubStr(string text, int startPos) {
return text.Substring(startPos);
}
In your code, call Substring in this way
context.SubStr(text, startpos)
It will now properly map to the SUBSTR function instead of SUBSTRING! It's like
mapping a User Defined Function, except that we map to an existing standard
function.
This should be fixed in the source code.
Etienne
[sqlite] Query returns "stack overflow error"
I finally managed to get my C# application running with the SQLite database and LINQ. However, there are requests that are very long because they return a grid where each column must be calculated individually, and this crashes with "stack overflow error". These queries run fast on a SQL Server database -- and not at all with SQLite. How can I get these queries to run? Etienne
Re: [sqlite] to encrypt sqlite db
On Sun, Sep 1, 2013, at 19:59, Ulrich Telle wrote: > Am 01.09.2013 18:40, schrieb Etienne: > > wxSQLite is free, while SEE is definitively not. > > The original poster searched for a free encryption extension, of which > there exist several: System.Data.SQLite (RC4), wxSQLite3 (AES-128 or > AES-256), SQLCipher (AES-256 with nonce) to name a few. > > Depending on the security requirements one of the free solutions may be > good enough for the OP. > > > wxSQLite means "pseudo" encryption (as formerly discussed), while SEE is > > real encryption. > > This statement is nonsense. Certainly SEE provides stronger encryption > than wxSQLite3, however, the encryption used by wxSQLite3 is also "real" > AES encryption (not "pseudo", which implies "easy to break"). > > Regards, > > Ulrich wxSQLite3 does implement AES in ECB mode and the SQLite file header is well known... so yes, it is definitively pseudo encryption. It might be enough for the OP's purpose (unknown so far), though. Regards, Etienne ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] to encrypt sqlite db
wxSQLite (relevant part) and SEE are extensions to SQLite. wxSQLite is free, while SEE is definitively not. wxSQLite means "pseudo" encryption (as formerly discussed), while SEE is real encryption. What is weird??? Regards, Etienne - Original message - From: Paolo Bolzoni <paolo.bolzoni.br...@gmail.com> To: General Discussion of SQLite Database <sqlite-users@sqlite.org> Subject: Re: [sqlite] to encrypt sqlite db Date: Sun, 1 Sep 2013 18:24:13 +0200 Another weird sentence in the mailing list of probably most used DB that is really free. On Sun, Sep 1, 2013 at 6:10 PM, Etienne <etienne.sql...@mailnull.com> wrote: > On Sun, Sep 1, 2013, at 17:50, Clemens Ladisch wrote: >> Ulrich Telle wrote: >> > Am 31.08.2013 22:01, schrieb Etienne: >> >> I simply wanted to warn the OP that wxSQLite, while free, does NOT use >> >> salts: >> > >> > Well, that's not completely true. The encryption extension coming with >> > wxSQLite3 uses a different IV (initial vector) for each database page. >> > True is that the IVs are not random, but deduced from the page number. >> > However, I don't see much difference between generating an IV >> > algorithmic or using a random nonce which is stored at the end of each >> > database page >> >> <http://en.wikipedia.org/wiki/Initialization_vector> says: >> | Randomization is crucial for encryption schemes to achieve semantic >> | security, a property whereby repeated usage of the scheme under the >> | same key does not allow an attacker to infer relationships between >> | segments of the encrypted message. >> >> Without a random IV/nonce, every page is guaranteed to encrypt to the >> same data if the contents and the key have not changed. Thus, wxSQLite3 >> gives an attacker the ability to determine whether any particular page >> has changed, by comparing the old and new versions. With SEE, rewriting >> a page will encrypt to a different value because the IV changes even for >> otherwise unchanged pages. >> >> > The weak point of probably all SQLite encryption methods is that the >> > unencrypted content of the first 16 bytes of a SQLite database file is >> > well known. >> >> Many file formats have fixed parts. However, this is not a problem with >> properly implemented encryption algorithms. >> >> Regards, >> Clemens > > Amen. > > In this particular case, you get what you pay for. > > Regards, > Etienne ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] to encrypt sqlite db
On Sun, Sep 1, 2013, at 17:50, Clemens Ladisch wrote: > Ulrich Telle wrote: > > Am 31.08.2013 22:01, schrieb Etienne: > >> I simply wanted to warn the OP that wxSQLite, while free, does NOT use > >> salts: > > > > Well, that's not completely true. The encryption extension coming with > > wxSQLite3 uses a different IV (initial vector) for each database page. > > True is that the IVs are not random, but deduced from the page number. > > However, I don't see much difference between generating an IV > > algorithmic or using a random nonce which is stored at the end of each > > database page > > <http://en.wikipedia.org/wiki/Initialization_vector> says: > | Randomization is crucial for encryption schemes to achieve semantic > | security, a property whereby repeated usage of the scheme under the > | same key does not allow an attacker to infer relationships between > | segments of the encrypted message. > > Without a random IV/nonce, every page is guaranteed to encrypt to the > same data if the contents and the key have not changed. Thus, wxSQLite3 > gives an attacker the ability to determine whether any particular page > has changed, by comparing the old and new versions. With SEE, rewriting > a page will encrypt to a different value because the IV changes even for > otherwise unchanged pages. > > > The weak point of probably all SQLite encryption methods is that the > > unencrypted content of the first 16 bytes of a SQLite database file is > > well known. > > Many file formats have fixed parts. However, this is not a problem with > properly implemented encryption algorithms. > > Regards, > Clemens Amen. In this particular case, you get what you pay for. Regards, Etienne ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] to encrypt sqlite db
> > On Sat, 31 Aug 2013 17:17:23 +0200 > > Etienne <etienne.sql...@mailnull.com> wrote: > > > > > > On the other hand removing patterns definitely cannot hurt. > > > > > > Precisely. > > > > > > The very first bytes of SQLite files are, AFAIK, well known. > > > > That's what salt is for, no? > > > "nonce", "IV", "salt" - call it whatever you want. Yes. I simply wanted to warn the OP that wxSQLite, while free, does NOT use salts: 2 SQLite DBs built exactly the same way at different times are byte-to-byte identical. Regards, Etienne ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] to encrypt sqlite db
> Those first few bytes are just as well known after they have been run > through zlib or libbz2 or whatever compression library you are using. Your > encryption algorithm, whatever it is, needs to be resistant to a > known-plaintext attack. LZ is a sequential algorithm, while BW works with large blocks of data. > SEE uses a random IV or nonce on each page. Exactly the reason why I would recommend SEE over wxSQLite for paranoiac usage. I did mention WXSQLite because the OP is looking for a free solution. Regards, Etienne ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] to encrypt sqlite db
> That last sentence is quite weird, a good encryption system should > give a random-like sequence even with very low-entropy input. > > On the other hand removing patterns definitely cannot hurt. Precisely. The very first bytes of SQLite files are, AFAIK, well known. While "encryption-only" practice might be enough to discourage an average user, it would probably not resist to a determined hacker... just my $0.02. Regards, Etienne ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] to encrypt sqlite db
> Thank you for your quick response. > > I am looking for freeware. If freeware not available, I have to implement > encryption support for sqlite on winrt. > > What is the procedure to implement encryption support on winrt? > > Thanks, > dd <http://wxcode.sourceforge.net/components/wxsqlite3> might help you. It does supply free AES128/256 encryption (look in /sqlite3/secure/src/). However, be aware that there is no compression involved here, making (any) encryption significantly weaker. Regards, Etienne ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Faster query result
Hello José,
#1: no hash/bsearch required.
Regards,
Etienne
- Original message -
From: jose isaias cabrera <cabr...@wrc.xerox.com>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
Subject: [sqlite] Faster query result
Date: Fri, 22 Feb 2013 10:56:08 -0500
Greetings.
which one is faster...
#1.
SELECT id FROM LSOpenJobs WHERE bdate BETWEEN '2012-01-01' AND '2012-12-31';
or this one...
#2
SELECT id FROM LSOpenJobs WHERE bdate IN ('2012-01-01', ..., '2012-12-31)';
where , ..., would have all the rest of the dates. Thanks.
josé
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Javascript API for SQLite?
> I work on an application that uses an SQLite database as it's binary document > format (with a custom extension). Now we want to develop a Web App variation > of that application, that should be able to read and write those > documents/databases. Since the Web App is written in Javascript, I am now > looking for a Javascript implementation of the SQLite library. > I have used the C SQLite library in the past, so I know about using SQLite > from C. However, I am just starting with Javascript and Web Apps and I am > quite a newbie on that side (Other people on the team are experienced, but I > have been asked to work on the SQLite integration). > What would be my options? JSDB (SQLite library embedded ) is worth a look: "JSDB is JavaScript for databases, a scripting language for data-driven, network-centric programming on Windows, Mac, Linux, and SunOS. JSDB works with databases, XML, the web, and email. It is free and open-source. Use it as a JavaScript shell, to run CGI programs, or as a web server". (home page) Regards, Etienne ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite Encryption Extension Performance?
Hi Paul, Are you speaking of CEROD? FYI, I use to link sqlite3.c with the free wxSQLite3 encryption (AES128/256) module (http://wxcode.sourceforge.net/components/wxsqlite3). I didn't notice any significant slowing down (3-4% max.), but of course there is no compression involved... Hope this helps. Regards, Etienne - Original message - From: Paul Vercellotti <pverce...@yahoo.com> To: General Discussion of SQLite Database <sqlite-users@sqlite.org> Subject: [sqlite] SQLite Encryption Extension Performance? Date: Wed, 27 Jun 2012 11:55:21 -0700 (PDT) Hi there, We are considering using the SQLite Encryption Extension in one of our products, and are wondering what the performance characteristics of it are? Does the encryption algorithm affect performance? Any stats on this you might have would be useful. Thanks! -Paul ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite time is 2 hours to late
Fully agree with you, Jay.
-> SQLite NUL "select datetime('now','localtime');"
E.
On Wed, Jun 27, 2012, at 10:55, Jay A. Kreibich wrote:
> On Wed, Jun 27, 2012 at 05:45:41PM +0200, deltagam...@gmx.net scratched on
> the wall:
> > Am 27.06.2012 17:40, schrieb Jay A. Kreibich:
> > >On Wed, Jun 27, 2012 at 05:37:55PM +0200, deltagam...@gmx.net scratched on
> > >the wall:
> > >
> > >>Hello,
> > >>
> > >>>sqlite3 event.db "select datetime('now')";
> > >>gives me a time that is 2 hours too late ( 2012-06-27 15:33:13)
> > >>than my system time ( win 7 ) 17::33:13
> > >>
> > >>How can this be fixed ?
> > > Move two timezones to the west.
> > >
> > > (By default all times and dates are UTC.)
>
>
> > I use this from within a c++ application
> > char create_sql[] = "CREATE TABLE if not exists eventlog ("
> > "id INTEGER PRIMARY KEY,"
> > "eventdate DATETIME default current_timestamp,"
> > "eventtype TEXT,"
> > ")";
> >
> > How do I get the right time in the the column eventdate ?
>
> UTC is "the right time." If you're doing anything with dates and
> times I would STRONGLY recommend that all recorded times are in UTC.
> Anything online and anything mobile tends to be used from different
> timezones.
>
> As for converting to the local time for display purposes, see:
>
> http://sqlite.org/lang_datefunc.html
>
> In specific, the "localtime" modifier.
>
> > How to move timezones ?
>
> Car, usually.
>
>
>-j
>
> --
> Jay A. Kreibich < J A Y @ K R E I B I.C H >
>
> "Intelligence is like underwear: it is important that you have it,
> but showing it to the wrong people has the tendency to make them
> feel uncomfortable." -- Angela Johnson
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] HTML5 database commands as a JavaScript-SQLite bridge
Hi Simon, Have you already had a look at JSDB? I know it is not closely related to your post (you speak of JS "client"), but you can build little web apps very quickly with JSDB "Server" and "SQLite" objects. There is also an ODBC driver (that I did not test so far) included in this all-in-one executable (no installation required), freely available for most popular OS at www.jsdb.org. Etienne P.S. I am not linked to JSDB's author, Shanti Rao. - Original message - From: Simon Slavin <slav...@bigfraud.org> To: General Discussion of SQLite Database <sqlite-users@sqlite.org> Subject: Re: [sqlite] HTML5 database commands as a JavaScript-SQLite bridge Date: Thu, 21 Jun 2012 18:53:49 +0100 On 13 Jun 2012, at 3:50am, Simon Slavin <slav...@bigfraud.org> wrote: > So we have an excellent JavaScript-SQLite bridge but it's intentionally > prevented from accessing SQLite databases you keep on your computer. So it's > useful only if you only ever need to access the database inside a specific > browser. But then I thought … well, since it turns out that everyone > implements this using SQLite, what would happen if I did an ATTACH ? And the results … nope. The browsers I've tried it in prevent ATTACH and similar subterfuges in various different ways. Strangely, they don't seem to explicitly trap it or provide an error code for it, but they've either removed it from the built-in version of SQLite they use, or the permissions the process runs under bar access to arbitrary folders. I'm pleased everyone cared about security and annoyed that I can't use the slick HTML5 database functions to do what I want. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] VERY weird rounding error
Hi all,
Here is a summary of my investigations related to the "0.1 case".
[FYI, 8674146.01 is another example]
The short (and censured :-) version: WTF!? That's just insane!!!
Look at this (sqlite3.c compiled only ONE TIME with default options):
[---
test.js:
var db = new SQLite();
db.exec("select 0.1", function(r){writeln(r)});
---
JSDB under GDB:
(gdb) break sqlite3.c:19917
Breakpoint 1 at 0x57d82d: file sqlite3.c, line 19917.
(gdb) run
Starting program: R:\jsdb/jsdb.exe test.js
[New Thread 21228.0x52f8]
Breakpoint 1, sqlite3VXPrintf (pAccum=0x22e184, useExtended=0,
fmt=0x65323d "g", ap=0x22e1f4 "") at sqlite3.c:19917
19917 realvalue /= 10.0;
(gdb) print ((unsigned char *) )[0]@13
$1 = "\000ÐÌÌÌ\fKÃ\017@\000\000Ú"
(gdb) n
19918 exp++;
(gdb) print ((unsigned char *) )[0]@13
$2 = "\000Ø£p=\n<o\f@\000\000Ú"
(gdb) del 1
(gdb) cont
Continuing.
0.1=0.1
SQLite-shell through GDB:
(gdb) break sqlite3.c:19917
Breakpoint 1 at 0x4063b1: file sqlite3.c, line 19917.
(gdb) run
Starting program: R:\jsdb\sqlite/SQLite.exe NUL "select 0.1;"
[New Thread 21300.0x5318]
Breakpoint 1, sqlite3VXPrintf (pAccum=0x22f724, useExtended=0,
fmt=0x48351d "g", ap=0x22f794 "") at sqlite3.c:19917
19917 realvalue /= 10.0;
(gdb) print ((unsigned char *) )[0]@13
$1 = "\000ÐÌÌÌ\fKÃ\017@\000\000Ú"
(gdb) n
19918 exp++;
(gdb) print ((unsigned char *) )[0]@13
$2 = "sÙ£p=\n<o\f@\000\000Ú"
(gdb) del 1
(gdb) cont
Continuing.
0.1
As you can see, the "realvalue" variable has byte-to-byte (sizeof(long
double)=12 here) identical values BEFORE executing line 19917.
The 2 less significant bytes diverge after the division by 10 - same behaviour
with the original op. "realvalue*=0.1".
Same behaviour? Not exactly... In this case, the rounding error is different
and final (post-processed) outputs are identical!
So here are (maybe) 2 simple alternatives to Keith's patch:
1) operation substitution
19909,19911c
while( realvalue>=1e32 && exp<=350 ){ realvalue /= 1e32; exp+=32; }
while( realvalue>=1e8 && exp<=350 ){ realvalue /= 1e8; exp+=8; }
while( realvalue>=10.0 && exp<=350 ){ realvalue /= 10.0; exp++; }
2) decreasing the max number of required mult.
19910,19911c
if( realvalue>=1e16 && exp<=350 ){ realvalue *= 1e-16; exp+=16; }
if( realvalue>=1e8 && exp<=350 ){ realvalue *= 1e-8; exp+=8; }
if( realvalue>=1e4 && exp<=350 ){ realvalue *= 1e-4; exp+=4; }
if( realvalue>=1e2 && exp<=350 ){ realvalue *= 1e-2; exp+=2; }
if( realvalue>=10.0 && exp<=350 ){ realvalue *= 0.1; exp++; }
I trust you guys, for fixing SQLite smoothly.
A big thank you for your patch Keith.
Keep up the very good work
Etienne
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] VERY weird rounding error
If ever I forget later, I would like to thank you guys (esp. Simon, Dominique, Keith and Jay) for the time you spend on this issue. Your idea of dumping the memory at was a very good idea, Simon. You were right: "realvalue" values are already slightly different (2 less significant bytes of the 10-bytes equ. string) after the first iteration (i.e. realvalue*=0.1) of the "while" loop (sqlite3.c:19911), while they are still byte-to-byte identical at sqlite3.c:19908. I use exactly the same byte code (i.e. the same sqlite3.o module), Keith. So I summarize the situation: realvalue * 0.1 = res1 in sqlite3.o linked to shell.o realvalue * 0.1 = res2 in sqlite3.o linked to JSDB - with res1!=res2!! The JSDB win32 executable is built like this: gcc.exe -static-libgcc -o jsdb.exe -mconsole -mwindows -mole obj\*.o obj\jsdb.coff -lole32 -lws2_32 -lodbc32 -loleaut32 -lmapi32 -lcomdlg32 -luuid -lwinmm -static -lstdc++ while I simply build the SQLite exec with "gcc -o SQLite.exe sqlite3.o shell.o". Is there a trick (in the sys. libraries mentioned above, or through the win32 C API) for changing the way the FPU handles (long) doubles? Etienne ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] VERY weird rounding error
Hi Simon, Once again, I really do not care of the accuracy. I KNOW 0.1 CAN NOT BE STORED EXACTLY IN A REAL VARIABLE. I just try to figure out why the SAME CODE (sqlite3 library) with the SAME INPUT gives DIFFERENT RESULTS (no matter how accurate/exact they individually are!) when linked to different shells. Etienne - Original message - From: Simon Slavin <slav...@bigfraud.org> To: General Discussion of SQLite Database <sqlite-users@sqlite.org> Subject: Re: [sqlite] VERY weird rounding error: details Date: Sat, 16 Jun 2012 18:13:29 +0100 On 16 Jun 2012, at 5:41pm, Etienne <ejlist-sql...@yahoo.fr> wrote: > 19913: realvalue *= 0.1; This operation cannot be correctly executed while holding the value in a real variable. You will get inconsistent results. Please read the reference I gave earlier for an explanation of why it is pointless trying to get perfect accuracy while handling a value of 0.1 in a real variable. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] VERY weird rounding error: details
Hi all,
The "0.1 case" is still a mystery!
For clarity reasons, I have expanded line 19911 of sqlite3.c (v3.7.13)
19909: while( realvalue>=1e32 && exp<=350 ){ realvalue *= 1e-32; exp+=32; }
19910: while( realvalue>=1e8 && exp<=350 ){ realvalue *= 1e-8; exp+=8; }
19911: while( realvalue>=10.0 && exp<=350 ){ realvalue *= 0.1; exp++; }
19912: while( realvalue<1e-8 ){ realvalue *= 1e8; exp-=8; }
19913: while( realvalue<1.0 ){ realvalue *= 10.0; exp--; }
as:
19909: while( realvalue>=1e32 && exp<=350 ){ realvalue *= 1e-32; exp+=32; }
19910: while( realvalue>=1e8 && exp<=350 ){ realvalue *= 1e-8; exp+=8; }
19911: while( realvalue>=10.0 && exp<=350 )
19912: {
19913: realvalue *= 0.1;
19914: exp++;
19915: }
19916: while( realvalue<1e-8 ){ realvalue *= 1e8; exp-=8; }
19917: while( realvalue<1.0 ){ realvalue *= 10.0; exp--; }
No other modification.
SQLite3.c is compiled (gcc -c -g sqlite3.c) with default options ONE TIME ONLY.
(Env: Int. Core i5, Win7 Pro/32, gcc 4.5, gdb 7.2)
The same object module is linked to shell.o (for SQLite) and to JSDB.
SQLite is then launched under GDB (run NUL "select 0.1;", breakpoint set up
accordingly).
The debugging session looks like:
(...)
(gdb) print realvalue-0
$6 = 0.100582077
(gdb) n
19913 realvalue *= 0.1;
(gdb) print realvalue-0
$7 = 0.100582077
(gdb) n
19914 exp++;
(gdb) print realvalue-
$8 = 0.0102037268
(gdb) n
19913 realvalue *= 0.1;
(gdb) print realvalue-
$9 = 0.0102037268
(gdb) n
19914 exp++;
(gdb) print realvalue-999
$10 = 0.901018099 <--
(gdb)
(...)
JSDB run in the same environment with the matching input:
js>var db = new SQLite();
js>db.exec("select 0.1",function(r){writeln(r)});
Here is a piece of the debugging session (same breakpoint):
(...)
(gdb) print realvalue-0
$4 = 0.100582077
(gdb) n
19913 realvalue *= 0.1;
(gdb) print realvalue-0
$5 = 0.100582077
(gdb) n
19914 exp++;
(gdb) print realvalue-
$6 = 0.0102037268
(gdb) n
19913 realvalue *= 0.1;
(gdb) print realvalue-
$7 = 0.0102037268
(gdb) n
19914 exp++;
(gdb) print realvalue-999
$8 = 0.901029468 <--
(gdb)
(...)
Please note that the "realvalue" variable has identical values at the first
loop pass.
They only start diverging (29468/18099 = +/-60%) from the second pass!
This divergence leads to a rounding error in the second case (JSDB), while
SQLite(shell) properly displays the ("faked") result.
So same input, same byte code... and different results! Who the heck said IT is
determinist?
Does anybody figure out the reason of the difference?
Thanks in advance.
Etienne
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] (shell dependent) library rounding error
> > The problem is: the "real" rounding error depends here on the shell (SQLite
> > or JSDB) calling the SQLite library.
> Yes. Different languages use different ways to hide the fact that they can't
> hide the 0.1.
The difference of languages is irrelevant here.
Both shells call "SQLITE_API const unsigned char *sqlite3_column_text()". The
returned string is not modified nor (re)converted by JSDB in any way.
Inputs are proven to be identical, so are compiler options.
> There's nothing you can do about this.
I have already fixed this issue - without figuring out what is exactly the
problem.
I just do not want to patch every future SQLite release.
>>>> R:\>sqlite NUL "select 0.1;"
>>>> 0.1
>>>>
>>>>
>>>> JSDB (www.jsdb.org) output:
>>>>
>>>> js>var db = new SQLite();
>>>> js>db.exec("select 0.1", function(r){writeln(r)});
>>>> 0.1=0.11
>>>> true
> You are not doing the same thing both times. One time you are writing the
> result using SQLite. The other time you are writing the result using a
> 'writeln()' command. There is no 'writeln()' command in SQLite.
THE PROBLEM OCCURS AT SQLITE LEVEL! The caller does not matter.
Behaviors diverge from sqlite3(071300).c: line 19911 (var "realvalue") very
precisely.
Regards,
Etienne
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] (shell dependent) library rounding error
Hi Simon,
Thanks for your answer.
That said... the rounding error ITSELF is not the point.
The problem is: the "real" rounding error depends here on the shell (SQLite or
JSDB) calling the SQLite library.
If I submit twice the same input (e.g. 0.1) to the same "black box" (e.g.
SQLite library - same compiler options etc.), I am just waiting for the same
(fake) result. IT was determinist... so far.
Why is SQLite able to "hide" the error in one case and not in the other case?
Because they are slightly DIFFERENT! How is it possible?
Regards,
Etienne
- Original message -
From: Simon Slavin <slav...@bigfraud.org>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
Subject: Re: [sqlite] (shell dependent) library rounding error
Date: Thu, 14 Jun 2012 22:13:18 +0100
On 14 Jun 2012, at 9:30pm, Etienne <ejlist-sql...@yahoo.fr> wrote:
> js>var db = new SQLite();
> js>db.exec("select 0.1", function(r){writeln(r)});
> 0.1=0.11
> true
There's no way to store the fraction 0.1 as a binary value. Read this:
<http://revjim.net/2003/05/07/funny-math/>
or go read any beginner's book on computer science. SQLite, just like every
other programming language, goes some way to fake its results but you can
usually find some very simple operation which will make it expose the fact that
it's all faked. This isn't a bug in SQLite, it's a problem with pretending you
can do the same things with binary and decimal numbers.
If you know you're going to need to store fractional values exactly, multiply
all your numbers up until you can store integers. For instance, if you need to
store integer amounts of money precisely, multiply all the values by 100, store
cents instead of Euros, and store them as INTEGER rather than REAL/FLOAT.
Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] (shell dependent) library rounding error
Hi all,
This is not really a SQLite issue, but the only solution I found out so far
consists in modifying SQLite sources.
Maybe someone here is able to clarify the following mystery...
---
SQLite shell output:
R:\>sqlite NUL "select 0.1;"
0.1
JSDB (www.jsdb.org) output:
js>var db = new SQLite();
js>db.exec("select 0.1", function(r){writeln(r)});
0.1=0.11
true
---
[Same rounding error with a couple of other "short" reals]
AFAIK, JSDB does use the SQLite library properly.
Technically, I can not figure out why sqlite3:sqlite3VXPrintf() behaves
differently if embedded in the SQLite shell or in JSDB.
In this particular case, "realvalue" values start to diverge slightly from the
"10.0" loop:
---
if( realvalue>0.0 ){
while( realvalue>=1e32 && exp<=350 ){ realvalue *= 1e-32; exp+=32; }
while( realvalue>=1e8 && exp<=350 ){ realvalue *= 1e-8; exp+=8; }
while( realvalue>=10.0 && exp<=350 ){ realvalue *= 0.1; exp++; } <--
while( realvalue<1e-8 ){ realvalue *= 1e8; exp-=8; }
while( realvalue<1.0 ){ realvalue *= 10.0; exp--; }
---
Adding intermediate exp power(s) of 2 (between 8 and 1) in the lines above does
fix the problem, e.g.:
(...)
if( realvalue>=1e4 && exp<=350 ){ realvalue *= 1e-4; exp+=4; }
(...)
However I still do not know why the same code/input/compiler options etc. lead
to slightly different results.
Any hint welcome!
Thanks in advance.
Regards,
Etienne
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
