Re: [sqlite] Virtual table API performance

[J. Merrill]

Eleytherios Stamatogiannakis wrote
> Our main test case is TPCH, a standard DB benchmark. The "lineitem" 
> table of TPCH contains 16 columns, which for 10M rows would require 160M 
> xColumn callbacks, to pass it through the virtual table API. These 
> callbacks are very expensive, especially when at the other end sits a VM 
> (CPython or PyPy) handling them.

Would it be very difficult to arrange for an option that would request that
SQLite issue a single more-complex xMultiColumns (a sample name) callback
request, with a way for multiple results to be returned, rather than many
xColumn callbacks? This would reduce the number of calls across the VM
boundary.

Applications that don't implement xMultiColumns (and request its use) would
see no change; those that do would get the performance boost.

J. Merrill



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Why-does-type-affinity-declared-on-a-foreign-key-column-affect-join-speed-tp74183p74295.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] windows 2012 server having problems loading sqllite dll

[j . merrill]

Your display of the GAC shows the 1.0.66.0 DLL having a different public key 
token than what is displayed in the error message.

.Net is apparently still trying to load the 1.0.66.0 DLL that may be 32-bit, 
and not the known-64-bit 1.0.90.0 DLL. As a first shot, I'd suggest removing 
the 1.0.66.0 DLL.

Do you know how your application is built -- AnyCPU, x86, or x64? It is 
possible that it needs to be built with the same setting as the 1.0.90.0 DLL 
was built.

You might also want to learn about fuslogvw.exe that can sometimes be useful in 
trying to figure out why things don't load.

Good luck...

J. Merrill

-Original Message-
Date: Wed, 8 Jan 2014 16:10:01 +
From: Jill Taylor <jtay...@innovata-llc.com>
To: "sqlite-users@sqlite.org" <sqlite-users@sqlite.org>
Subject: [sqlite] windows  2012 server having problems loading sqllite dll


Hi

I hope someone has ran across this and can give me some pointers.

I have a new windows 2012 server that runs a process that loads the sqllite 
dll.   On my older servers 2008 I have do not have any problem with this.

On the older servers we just have the System.Data.SQLLite1.0.66.0 in our 
assemble.

What we did is manually moved over the assemble System.data.sqllite from an 
older server and installed in the cache on the Windows 8.   We ran our process 
and got the error

Could not load file or assembly 'System.Data.SQLite, Version=1.0.66.0, 
Culture=neutral, PublicKeyToken=db937bc2d44ff139' or one of its dependencies. 
An attempt was made to load a program with an incorrect format. 
1/6/2014 2:07 PM


Our next step was thinking this was a 64 bit machine so we would load the 64 
bit version
sqlite-netFx35-setup-bundle-x64-2008-1.0.90.0.exe

When we did this, we still got the 1.0.90.0 in the assembla but when we ran our 
process we still got the same error

We are getting the following error after installing the SQLite product / dlls,  
and loading the dlls.

Could not load file or assembly 'System.Data.SQLite, Version=1.0.66.0, 
Culture=neutral, PublicKeyToken=db937bc2d44ff139' or one of its dependencies. 
An attempt was made to load a program with an incorrect format. 
1/6/2014 2:07 PM

Here is how the assembly cache looks - there are two versions:

The 1.0.66 version is what has been working fine on our Windows 2008 servers.
The application using SQLite is built using Visual Studio 2008  with 3.5 
framework.

System.Data.SQLLite   1.0.66.0  1fdb50b1b62b4c84  MSIL
System.Data.SQLLite   1.0.90.0  db937bc2d44ff139   AMD64


Thank you
Jill Taylor


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite download for 64 bit

[j. merrill]

Use the 32-bit DLL. If you use that, your application will run on both 32-
and 64-bit versions of Windows. If you use the 64-bit DLL, it will only work
on 64-bit versions of Windows.


Krishna Chaitanya Konduru wrote
> at the sqlite download page there is download for win 32 x86 what abut
> 64bit os.. would the same appllication runon both 32 and 64 bit os??





--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/sqlite-download-for-64-bit-tp72839p72850.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT statement failure

[j . merrill]

If floating-point data is involved (common when values are stored as dollars 
and cents, e.g. 2.79 for 2 dollars 79 cents) you cannot use "=" for a 
comparison like this, because of small differences caused by the lack of exact 
representation for many floating-point numbers. See

http://en.wikipedia.org/wiki/Floating_point#Minimizing_the_effect_of_accuracy_problems

and search for "The use of the equality test". (This is a very common issue 
when working with floating-point data. A lot of software stores money amounts 
as an integer number of pennies to avoid it.)

The easiest solution is to subtract the two values in question, take the 
absolute value, and compare the difference to something reflecting the maximum 
difference you will allow -- something like

SELECT * FROM orders_tbl WHERE 0.01 <=
abs((ord_total+discount-tax1-tax2-tax3-tax4-delivery_tax-delivery_fee)-subtotal);

(To reverse this to an "it's equal" test, change <= to > rather than != to =.) 
Using a value slightly less than 0.01 (e.g. 0.008) might prevent truly fringe 
cases from giving the wrong answer.

J. Merrill
 

-Original Message-
Date: Fri, 06 Dec 2013 13:13:54 -0500
From: Scott Slater <sslat...@summitcn.com>
To: sqlite-users@sqlite.org
Subject: [sqlite] SELECT statement failure

Hello,

I had a customer contact me that a report was randomly "missing data" 
and have tracked down the problem to a single SQL query.  the problem is 
that some items, that logically match the WHERE clause don't get selected.

I have put together a reverse logic of that statement below by changing 
the = to != .  I have attached a very small subset of this client's data 
that demonstrates the problem.  Using the attached file which contains 4 
orders none of which should get selected using the statement below;

SELECT * FROM orders_tbl WHERE 
(ord_total+discount-tax1-tax2-tax3-tax4-delivery_tax-delivery_fee)!=subtotal;

However, record #1 gets selected.  If you change the != back to an =, 
then you get the remaining 3.

If you manually look through the relevant column data all 4 orders are 
logically correct, and should be treated in the same manner by the 
select statements.

I have tested this on Windows machines and posted it to a forum where 
another user was able to demonstrate the same issue.  I have tried the 
windows command shell version (sqlite3.exe / 
sqlite-shell-win32-x86-3080200.zip) as well as the sqlite3.dll / 
sqlite-dll-win32-x86-3080100.zip and an earlier (not sure which) version 
of the dll.  I have not tested on any other operating systems.


Regards,

Scott Slater
Summit Computer Networks, Inc.
(866) 922-9690  Ext. 7701
(724) 779-6390  Ext. 7701
sslat...@summitcn.com


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Could not load file or assembly 'System.Data.SQLite.dll' or one of its dependencies on Windows XP. Ideas?

[j . merrill]

My recollection is that in your first message (to which I replied [to this 
list] that your one crashing user might have been someone using 32-bit Windows; 
almost all XP installs are 32-bit) you said that you had built things 64-bit.

You cannot deploy a 64-bit build on 32-bit Windows XP and expect it to work. 
The error message "no matching native image" corresponds to that problem -- a 
64-bit native image cannot load (it will not "match") on a 32-bit system.

However, you could deploy a 32-bit build on 64-bit Windows. (Just as one 
example, Visual Studio is a 32-bit application and of course works fine on 
64-bit Windows.) Unless your application needs to access more than (at least) 
1.5gb of memory, building as 64-bit gains very little if anything. 

Do you know why the decision was made to build 64-bit? That removes the 
possibility of deploying to 32-bit Windows XP (unless you ALSO build a separate 
32-bit version) -- something that apparently is/was not obvious to the people 
who decided that.

J. Merrill

-Original Message-
Date: Wed, 13 Nov 2013 22:39:04 -0800
From: "Andreas Hofmann" <andreas.hofm...@ku7t.org>
To: "'General Discussion of SQLite Database'"
   <sqlite-users@sqlite.org>
Subject: Re: [sqlite] Could not load file or assembly 'System.Data.SQLite.dll' 
or one of its dependencies on Windows XP. Ideas?

Even more information. I used fuslogvw.exe to see the binding errors.  It
seems that the assembly of System.SQLite.Data.dll was loaded fine:

LOG: GAC Lookup was unsuccessful.
LOG: Attempting download of new URL file:///C:/Program
Files/N1MMLogger+/System.Data.SQLite.DLL.
LOG: Assembly download was successful. Attempting setup of file: C:\Program
Files\N1MMLogger+\System.Data.SQLite.dll
LOG: Entering run-from-source setup phase.
LOG: Assembly Name is: System.Data.SQLite, Version=1.0.86.0,
Culture=neutral, PublicKeyToken=db937bc2d44ff139
LOG: Binding succeeds. Returns assembly from C:\Program
Files\N1MMLogger+\System.Data.SQLite.dll.
LOG: Assembly is loaded in default load context.

But the native image was not:

OG: Initial PrivatePath = NULL
LOG: Dynamic Base = NULL
LOG: Cache Base = NULL
LOG: AppName = N1MMLogger.net.exe
Calling assembly : N1MMLogger.net, Version=1.0.0.0, Culture=neutral,
PublicKeyToken=null.
===
LOG: Start binding of native image System.Data.SQLite, Version=1.0.86.0,
Culture=neutral, PublicKeyToken=db937bc2d44ff139.
WRN: No matching native image found.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Exception

[j . merrill]

One possibility has to be that your one problem user is on 32-bit Windows. Are 
you sure that's not the case?

Did you try using the sxstrace tool? Here's good documentation for how to do so 
--

http://www.fundootech.com/2013/01/how-to-use-sxstraceexe.html


J. Merrill

-Original Message-
Date: Tue, 12 Nov 2013 19:39:47 +0530
From: Shashibhushan Ivaturi <ivaturis...@gmail.com>
To: sqlite-users@sqlite.org
Subject: [sqlite] SQLite Exception
Message-ID:
   <calpwwqw3dmg3t6w3wheldbcgzr14vqszcyej2ja2lfeoowq...@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1

Hi,
  We are using sqlite dll(64-bit) in our windows application and
carrying the same dll with the application setup. Our application support
different OS versions. We downloaded the precompiled binary from the link, (
http://system.data.sqlite.org/downloads/1.0.89.0/sqlite-netFx20-setup-bundle-x64-2005-1.0.89.0.exe).
Application is running fine in different 64-operating systems
(win7,win8,win2011 server) having .NET versions (2 or 3.5 or 4 or 4.5). But
one of the users reported an exception - [ *System.DllNotFoundException:
Unable to load DLL 'sqlite3.dll': The application has failed to start
because its side-by-side configuration is incorrect. Please see the
application event log or use the command-line sxstrace.exe tool for more
detail. (Exception from HRESULT: 0x800736B1)*]. I am not able to trace out
the problem. Can anyone suggest some solution for this.


Thank you

-- 
With Regards,
Shashibhushan Ivaturi

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite db getting corrupt on power outage scenarios

[j . merrill]

Consumer-grade flash drives are notoriously unreliable. I suggest that (at a 
minimum) you test the drive with the software described at

http://sosfakeflash.wordpress.com/2008/09/02/h2testw-14-gold-standard-in-detecting-usb-counterfeit-drives/

Have you what happens to your flash drive (and its file system) when 
non-database applications are writing to files on the flash drive when a power 
outage occurs?

J. Merrill

Date: Fri, 8 Nov 2013 06:41:15 +
From: "Mayank Kumar (mayankum)" <mayan...@cisco.com>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
Subject: Re: [sqlite] sqlite db getting corrupt on power outage
scenarios

[[small subset only]]
[MK] I am using a flash drive running on linux kernel 2.6.27.10(SMP). The 
corruption is sometimes seen on sqlite db on the flash drive and sometimes seen 
on sqlite db in nvram. Network file system is not involved.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite-users Digest, Vol 69, Issue 10

[j . merrill]

-Original Message-
Date: Tue, 10 Sep 2013 15:15:35 +
From: Harmen de Jong - CoachR Group B.V. 
To: General Discussion of SQLite Database 
Subject: Re: [sqlite] Query preperation time does not scale linearly
withgrowth of no. of tables
Message-ID: 
Content-Type: text/plain; charset="us-ascii"

[snip]
That [memory being re-allocated] is something we suspected too. We already made 
some tests where we timed the time needed for all memory allocations executed 
in the entire operation. In total for the 1 tables test this was somewhere 
around 25 msec. Since this is just a little overhead and the instructions as 
you point out have a linear increasement this still does not explain the 
polynomial increasement in preperation time.

[J. Merrill's comment below]
When there's a re-allocate, it's not just the time to allocate the new memory 
-- there's the time to copy the data from the original not-big-enough location 
to the new big-enough-for-now location. The amount of data moved grows rapidly 
in that case with each re-allocation, and the pattern of extra-time seems 
similar to the data-movement-growth when there are repeated allocate/move 
steps. How many re-allocations happen?

Do you know by how much the allocation size increases each time a re-allocation 
is needed? (A common algorithm is to double the size each time.) You might try 
changing the source code so that it triples or quadruples the size each time, 
as memory does not seem to be an issue.

Another point -- I did not see you comment on the possibility that you could 
remove the FK specs from the 10,000 tables. Do you really have the situation 
that deletions from the main table would "orphan" rows in an unknown number of 
other tables, and the existence of those orphan rows would in fact cause 
application failures? (If the rows were not deleted from the other tables, 
using a normal join to the main table -- rather than a left join -- in the 
queries would make those rows disappear.)

Alternatively you could create a table to hold the ids of the rows deleted from 
the main table, and build a separate process -- to be run as often as desired 
-- to do

delete from onechildtable where id in (select id from deletedids)

(That SQL could also be of the form
   delete from onechildtable where id in (5,6,9,12,999)
should you determine that there aren't very many deleted ids.)

You would do that for each of the other tables -- this is exactly the work that 
SQLite is preparing to do when you prepare a "delete from maintable" statement. 
When done deleting from all the tables, you could remove all the rows from the 
deletedids table. (You'd have to do something to block deletions from the main 
table -- thus blocking insertion into the deletedids table -- during the 
process, and of course you would start the process with "do nothing if 
deletedids has no rows".)

This would just have the effect of batching together multiple main-table 
deletes before going through every child table for the purpose of deleting 
orphans.

I would not suggest the "delete in batches" idea except that it's clear that 
you have a major collection of infrastructure set up to handle this unusual 
task. I don't think that what I'm suggesting would add a huge new component to 
that infrastructure.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query preperation time does not scale linearl with growth of no. of tables

[j . merrill]

On Fri, 6 Sep 2013 17:29:25 Harmen de Jong - CoachR Group B.V. wrote
> [Explaining why there can be 10,000 tables referencing one table] ... users 
> can create highly customized  research forms with varying number of columns, 
> varying field types and a lot of specific restrictions, which we store in 
> tables that are generated on the fly with the proper indexes ...

[J. Merrill's comment below]
SQL Server is considered to much less "lite" than SQLite, but it does not 
support more than about 253 foreign key references to a particular column. 
(Some versions have a hard limit that blocks creation of the (N+1)th foreign 
key reference; other versions let you create so many references that if you try 
to delete from the referenced table you get "The query processor ran out of 
stack space during query optimization. Please simplify the query.")

In the case I ran into in real life, deletes from the referenced table were 
simply incredibly slow because all the referencing tables had to be checked to 
ensure that none pointed to any rows being deleted. (My case was that I had a 
centralized "notes" table and each other table that the users saw as having a 
"notes" column really just had an FK to the notes table.) 

I'm more surprised that SQLite actually supports 1,000 or 10,000 FK references 
without failing (unlike SQL Server) than that it gets quite a bit slower to 
delete from the referenced table. (Whether the slowness is during Prepare or 
the actual deletion seems somewhat unimportant.)

I suggest that perhaps you could avoid having an "official" foreign key 
reference to the main table in each of those other tables; you almost certainly 
have application logic to avoid creating rows in other tables that don't have 
an invalid (non-null) reference to the main table. (You should of course create 
an index on the no-longer-official-FK column in those other tables.)

I find it curious that my two most recent posts are suggestions to avoid using 
different parts of SQLite's "data integrity" support -- referential integrity 
in this case, a multi-column UNIQUE constraint in the other case. Perhaps it's 
because I think such features are akin to "strong typing" in programming 
languages -- both prevent your from having particular kinds of bugs in your 
code but do not prevent you from having any of the gazillion of other kinds of 
bugs that it's at least as easy to have. Although I write in C# some of the 
time, I have a preference for so-called "dynamic" languages that don't pick one 
particular kind of bug to help me avoid.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPDATE question

[j . merrill]

I propose that you remove the unique index because SQLite does not handle the 
update case the way you want. (I'd call that a bug, but sometimes "liteness" 
means that Dr Hipp can say "I'm not going to fix it" and we have to respect his 
decision.)

Is there a reason other than "if you have a particular kind of bug in your 
code, you could end up violating the [Name/Sequence is unique] rule" to keep 
the index, when its presence causes you trouble? (Perhaps the answer is "users 
edit this table manually using other software" so you need the index to keep 
them from screwing up. But I doubt it, or they'd have complained what a pain it 
is to add a new row in the middle!)

There is little to prevent you from having other bugs that might be equally bad 
--

- putting both "Blue" and "blue" in the Name column (with separate sets of 
Sequence values) when both values shouldn't be there because the business 
context says they're the same
- have Sequence values not starting at 1 (e.g. 2 3 4) for a particular Name -- 
perhaps that wouldn't cause any trouble in other logic, but it probably would

If you wanted to, you could have your initial "open the database" code check 
for duplicates across those columns (and that 1 is the lowest Sequence for each 
Name) -- then at least you'd know that you'd had one of those bugs.

J. Merrill

-Original Message-
From: Peter Haworth
Sent: Thursday, September 05, 2013 2:21 PM
To: sqlite-users
Subject: [sqlite] UPDATE question

I have a table with the following (simplified) structure

PKeyINTEGER PRIMARY KEY 
NameTEXT
Sequence INTEGER

The Name/Sequence pair of columns is defined as UNIQUE


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] to encrypt sqlite db

[j . merrill]

If Windows RT supports the "encrypted files and folders" option of the NTFS 
file system -- I could not find anything that said explicitly that it either 
was or was not supported, and I don't have a Windows RT device to test with -- 
that would definitely be the way to go. It would require only the effort to 
turn on the encryption feature for the database file (or the directory holding 
multiple database files).

It's appropriate to always ask the question "what is the motivation for 
encrypting the database?"

If the reason is to prevent someone who steals the device from easily reading 
the data file (for example after removing or copying the hard drive), consider 
that someone who steals the device could run whatever installed software that's 
designed to be able to read/write the database. You would need to prevent them 
from being able to do that, otherwise the encryption would not be very useful.

[quoted message from Paolo Bolzoni]
Date: Sat, 31 Aug 2013 14:40:19 +0200
From: Paolo Bolzoni 
Subject: Re: [sqlite] to encrypt sqlite db
Message-ID:  


There is a non-free version of sqlite that
encrypt the db. If it is that you want then
you have to contact them directly.

Otherwise just use sqlite on a EncFs mounted
disk?

On Sat, Aug 31, 2013 at 2:25 PM, dd  wrote:
> Hi All,
>
>   I have to encrypt sqlite database on winrt.
>
>   What are all the necessary steps to do to encrypt sqlite database?
>
>   Thanks in advance.
>
> Regards,
[end of quoted message from Paolo Bolzoni]

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is REAL the best universal data type?

[j . merrill]

On 07/23/2013 02:52 PM, Max Vlasov wrote:
> I've created a kind of triple storage base with Sqlite db as the container.
> Basically it's several tables implementing Object-Propery-Value metaphor.
> There's only one field for data so thinking about generality I assumed that
> the type for the data field should be TEXT of nothing since most of other
> types (let's forget about blob for a moment) can be stored (and easily
> visualized) with this field. But there are also indexes involved and here
> comes the problem. If I insert natural numbers in some sub-sequence I will
> get non-naturally sorted ones (1, 10, 2, 20). But we know that Sqlite can
> accept any data in any field, so I can change the type to INTEGER and enjoy
> numbered order when there are numbers were added (1, 2, 10, 20). On the
> other side, when we look at real numbers, the problem would still exist. So
> paradoxically probably the best type for universal field container is REAL
> (or NUMERIC) since it will accept data of any type, but has advantage of
> best sorting if reals or integers are involved.
>
> Is this correct or I am missing something?

You could consider having more than one column in the "value" table -- one for 
each data type that you want to have behave the way you want to. (When using 
more traditional SQL implementations that require the data to be "right", this 
lets you have "strongly typed" data -- you put dates into a date[time] column, 
integers in an integer column, etc.)

I do not know if SQLite has the storage behavior that e.g. PostgreSQL and MS 
SQL Server have, which is that null values take up absolutely no space. If 
SQLite behaves that way as well, you could put each value in the the proper 
column for its datatype and not pay any storage penalty for having the extra 
columns.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is there a way to return the row number? (NOT the rowid)

[j . merrill]

Date: Fri, 05 Jul 2013 02:04:04 +0200

From: Olaf Schmidt 
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Is there a way to return the row number? (NOT the rowid)
Message-ID: 
Content-Type: text/plain; charset=ISO-8859-1; format=flowed

Am 03.07.2013 14:50, schrieb Keith Medcalf:
>
>> Given all that, I will NEVER use the pure sql (if I can use any
>> other solution).
>
> given that the "ordinal in the result set" is a fallacious
> concept being created for the convenience of an application
 > program which cannot deal with sets properly ...

Oh, so convenience is now bad suddenly?
[...]
Because there's a lot of things one can use that for - especially
when you consider the concept of disconnected Recordsets, which
can be passed around in an application, or across thread- or
machine-boundaries - generic container-classes, which can be
bound to grids - or used as the datasource for parts in a Report
... in any of those cases such a "directly contained info" can
be useful, when it's already "there in the returned set-object
as a calculated column-value".
[...]
 
 J. Merrill's thoughts below 
There are reasons to want what is in other SQL implementations implemented with 
"row_number() OVER (ORDER BY ...)" but "disconnected Recordsets" or for data 
"passed across thread- or machine-boundaries" are definitely NOT valid reasons.
 
The "row number" value as commonly described in this thread is completely 
useless except in the context of one specific execution of a particular SQL 
statement. You would need to use the table's primary key value to do any 
updates to the original table -- possibly with the values of some/all non-PK 
values to avoid updating rows that had been changed by other sessions/users 
after the initial SELECT. 
 
You would definitely not want to relate one recordset to another using the "row 
number" value, because executing the exact same SQL statement 1 second later 
could return a completely different "row number" value for every primary key. 
(Pretty much the ONLY data in such a recordset that would never be useful to 
relate to another recordset is this kind of ephemeral "row number" value.)
 
Personally, I'd like to see an implementation of the "row_number() OVER (ORDER 
by xxx)" syntax, and if possible also the inclusion of the "PARTITION BY" 
clause within the OVER () expression. The PARTITION BY syntax lets you get row 
numbers within groups. 
 
Doing that would add a feature to SQLite that's in a other SQL implementations, 
and it does not violate relational purity because the OVER clause keeps the 
ROW_NUMBER() function from being non-deterministic when the overall statement 
has no ORDER BY. Having some other syntax that's unique to SQLite does not seem 
right.
 
It would not offend me if the initial implementation of this feature were 
limited in that PARTITION BY is not implemented and requiring the ORDER BY 
clause for a column defined by "ROW_NUMBER() OVER (ORDER by xxx)" had to match 
the ORDER BY of the result set. That would give people the feature that seems 
to be so desired without a SQLite-specific syntax, and would leave open the 
possibility of having a more complete implementation later.
 
Note that the ROW_NUMBER() OVER syntax defines the row number without regard to 
the ordering of the result set. You could have multiple columns defined with 
ROW_NUMBER() OVER, each with a different ORDER BY -- for example you could have 
ROW_NUMBER() OVER (ORDER BY sales_total desc) while the result set has its 
ORDER BY be something else (like salesperson name).
 
My $0.04.
 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] escape quote for csv import

[j . merrill]

The fact that there's no "official CSV standard" doesn't mean that there isn't 
"common practice" that SQLite should support (but I can't say that I know that 
it does; it should be easy to change if it doesn't).
 
The "common practice" is to double each embedded double-quote.
 
"abc","the char in parens ("") is doublequote","def"
 
-Original Message-
Date: Tue, 18 Jun 2013 22:02:31 +0200

From: Clemens Ladisch 
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] escape quote for csv import
Message-ID: <51c0bcd7.4050...@ladisch.de>
Content-Type: text/plain; charset=ISO-8859-1

Roland Hughes wrote:
> How does one escape a in a CSV file so it will correctly import?

The sqlite3 tool allows to configure the separator, but the quote
character for delimiting fields is hardcoded.

(There is no official CSV standard, and there is no widely supported
escaping mechanism.)

> I can only massage the CSV

Convert it into properly formatted SQL INSERT statements.


Regards,
Clemens

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Getting Started with Sqlite

[j . merrill]

(I think Fehmi diagnosed the problem, that you should not use the "sqlite3" 
command when you are already in the "sqlite3" program.  This is about something 
completely different.)
 
If you are using a recent version of Windows, you do not want to be trying to 
create your database in the c:\windows\system32 folder. (In Windows Vista or 
later, you need administrative privileges to write to any file in that folder.)
 
Once SQLite3.exe is installed in the system32 folder, you do not need it to be 
the current folder to be able to run it (because the system32 folder is in the 
path).
 
If you use "Start / Run / cmd" to get to a Command Prompt, you can do something 
like this:
 
md c:\mydb
cd c:\mydb
sqlite3 test.db
 
so that your data file is not in the system32 folder. (You only need the "md" 
command the first time, to create the directory.)  Once this has been done, you 
should be able to do
 
Start / Run / sqlite3 c:\mydb\test.db
 
rather than beginning at a Command Prompt.
 
---
Date: Wed, 22 May 2013 11:22:22 -0300

From: Sean Dzafovic 
To: Fehmi Noyan ISI ,  General Discussion of
 SQLite Database 
Subject: Re: [sqlite] Getting Started with Sqlite
Message-ID:
 
Content-Type: text/plain; charset=ISO-8859-1

On Wed, May 22, 2013 at 11:17 AM, Fehmi Noyan ISI  wrote:
> Oh my gosh! you are in business man...
[snip]

All right. I was doing the command from the shell and not the command
line. I will try that later but I have to go offline.

I think that may have been my problem though. Thanks for your help.
 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unhandled Exception: on System.TypeInitializationException

[j . merrill]

If the project uses .Net and was built with "Any CPU" setting, the .Net parts 
will end up being 64-bit when run on a 64-bit OS.  When .Net code calls into 
non-.Net DLLs the "bitness" has to match, and the SQLite DLL is almost 
certainly 32-bit.
 
At least some people at MS have decided that making AnyCPU the default was 
wrong; I agree.  I always change my projects to be x86 -- and that's 
appropriate until you write something that you think requires access to more 
than about 2gb of RAM.
 
If you can re-build the app as "x86" (on the original PC that you used for 
development) that probably will solve the problem.
 
J. Merrill
 
-Original Message-
Date: Fri, 5 Apr 2013 15:19:50 -0500From: Don V Nielsen 
<[https://apps.rackspace.com/versions/webmail/8.15.18-RC/popup.php?wsid=8777ef068db87b15a20a9e46b13314f4c1a2db7c]
 donvniel...@gmail.com>To: General Discussion of SQLite Database 
<[https://apps.rackspace.com/versions/webmail/8.15.18-RC/popup.php?wsid=8777ef068db87b15a20a9e46b13314f4c1a2db7c]
 sqlite-users@sqlite.org>Subject: [sqlite] Unhandled Exception: 
System.TypeInitializationExceptionMessage-ID: 
<[https://apps.rackspace.com/versions/webmail/8.15.18-RC/popup.php?wsid=8777ef068db87b15a20a9e46b13314f4c1a2db7c]
 
cakigfiiq7-m8+bhjpq1lrsgs-5cq1aqta9sgt8fvpejd8hy...@mail.gmail.com>Content-Type:
 text/plain; charset=ISO-8859-1Thanks for reading.  I am getting the following 
error moving a 32 bitapplication from Windows Server 2003 to WS2008.  This is a 
straight copyfrom one computer to another.  It runs fine on WS2003 and not so 
good onWS2008.  The application was compiled with VS2005.  I do not have 
adevelopment environment on the WS200
 8.  I have not cleared licensing forthat, yet.I'm not a developer savant.  I 
can code in C#, assemble, and run things.I've been googling this issue, but do 
not really understand what I amreading.  All of you rank pretty high on my 
rankings, so I thought I wouldask you all for help.Thanks for your time and 
consideration,dvnUnhandled Exception: System.TypeInitializationException: The 
typeinitializer for 'CDG.AddAName.AAN' threw an exception. 
--->System.BadImageFormatException: Could not load file or 
assembly'System.Data.SQLite, Version=1.0.82.0, 
Culture=neutral,PublicKeyToken=db937bc2d44ff139' or one of its dependencies. An 
attempt wasmade to load a program with an incorrect format.File name: 
'System.Data.SQLite, Version=1.0.82.0, 
Culture=neutral,PublicKeyToken=db937bc2d44ff139' at 
CDG.AddAName.AAN..cctor()WRN: Assembly binding logging is turned OFF.To enable 
assembly bind failure logging, set the registry 
value[HKLM\Software\Microsoft\Fusion!EnableLog] (DWORD) to 1.Note: There is som
 e performance penalty associated with assembly bindfailure logging.To turn 
this feature off, remove the registry 
value[HKLM\Software\Microsoft\Fusion!EnableLog]. --- End of inner exception 
stack trace ---
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [Question] How can I recognize arguments are dynamic binding values in user defined function?

[j . merrill]

The people who are using your software need a lesson about "SQL injection".  No 
one should create SQL statements "on the fly" that include literal character 
strings built from data.  Not only could there be issues if there are special 
characters in the data to be included as a literal string (including the 
possibility of a syntax error that prevents the statement from being executed) 
but evil people could do severe damage -- but this can easily be avoided by 
using parameters.
 
For example, if the value being searched for came from user input (say on a web 
page) users of your software must not do things like this --
 
sql = "select * from mytbl where mycol ='" + input + "'"
 
because, if the input is something like
 
x' ; drop table mytbl; --
 
the table will be dropped!  This cannot happen if parameters are used to pass 
the string.
 
You might be better off providing only a method where the user passes strings 
for the table name ("mytbl" in the example before), the columns to be returned 
(separated by commas, or "*" for all as above), the name of the column to 
compare ("mycol" in the example) and the value to search for.  It would then be 
your code that builds and runs the SQL statement using parameters.
 
J. Merrill
 
-Original Message-
Date: Wed, 3 Apr 2013 22:41:01 +0900

From: Yongil Jang <yongilj...@gmail.com>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
Subject: Re: [sqlite] [Question] How can I recognize arguments are
 dynamic binding values in user defined function?
Message-ID:
 <cao_0w+hg70q00zzcxn6ojigo_otrng25bbn9n_p+t0xavyq...@mail.gmail.com>
Content-Type: text/plain; charset=EUC-KR

Thank you, Simon and Igor.

I will investigate about your opinion, as you mentioned.

In general, if parameter string contains alphabets only, it doesn't make
any problems.

However, I couldn't check that my function is used correctly for every
applications.
Some developers don't know why does it fails when using special characters
and applications can be packaged with hidden issues.

For this reason, I was looking for some solutions that I can send warning
messages to application developers if they use plain text without binding
arguments.

Best regards,
Yongil jang.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users