Re: [sqlite] Once again about random values appearance

[Max Vlasov]

On Fri, Feb 14, 2014 at 6:35 AM, James K. Lowden
wrote:

> > > select id, (select id from TestTable where id = abs(random() % 100))
> > > as rndid from TestTable where id=rndid
>
> On Thu, 13 Feb 2014 07:26:55 -0500
> Richard Hipp  wrote:
>
> > It is undefined behavior, subject to change depending the specific
> > version of SQLite, compile-time options, optimization settings, and
> > the whim of the query planner.
>
> It should be defined.
>
> In the above query, random() should be evaluated once.  In the SQL
> model the user provides inputs once, and the system evaluates them
> once.
>
>
Once for the query or once for the row?
It does evaluated once for the query. To check, just remove where

  select id, (select id from TestTable where id = abs(random() % 100)) as
rndid from TestTable

and we have plenty of the same rndid as the right column

I almost understood the logic behind this, but just found the query that
surprised me once again. When I wrote it, I wanted to show that to compare
engines we have to remove alias usage from WHERE because most of engines
forbid using it in WHERE. But the following one that probably should be
compatible with other RDBMSes still shows different column values

select id, rndid from
(
select id, (select id from TestTable where id = abs(random() % 100)) as
rndid from TestTable
) where id=rndid

But I supposed the rndid in outer query should have known (fixed) value and
the values from where clause should be shown identical.

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

Re: [sqlite] Graphic SQLite Client Tool

[romtek]

I'd recommend http://www.sqliteexpert.com/. I've been using it for years
and like it.


On Sun, Jan 26, 2014 at 5:08 AM, big stone  wrote:

> Hello sqlite-users,
>
> I'm looking for a Graphic SQLite  Client Tool  to equip a classroom of  old
> windows PC.
>
> So far, the best option I found is :
> - dbeaver 2.3.6 (multi-motors : SQLite, Mysql, Postgresql)
> - with the latest Xerial driver sqlite-jdbc4-3.8.2-SNAPSHOT.jar (with
> SQLite3.8.2)
>
>
> Has anyone knowledge and practice of a better option ?
> ___
> 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] System.Data.SQLite documentation

[Incongruous]

Yes!
Thanks man, keep it smooth dude... you got it right.

-Original Message- 
From: Joe Mistachkin 
Sent: Thursday, February 13, 2014 10:34 PM 
To: 'General Discussion of SQLite Database' 
Subject: Re: [sqlite] System.Data.SQLite documentation 



Incongruous wrote:


Dude! Something must've happened to that file, there is a tree in the 
Contents tab, but it doesn't matter which help file I click on nothing
gets 

displayed. The help files do not have any data in them!
What did I do wrong?



Not being able to open a downloaded CHM file is a security feature of
Windows,
see the Microsoft KB article:

http://support.microsoft.com/kb/902225

To summarize the workaround mentioned in the above MS KB article: In order
to
be able to correctly view the CHM file, right-click it, go to the
"Properties"
tab, click the "Unblock" button, and then click the "Apply" button.

--
Joe Mistachkin

___
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] System.Data.SQLite documentation

[Joe Mistachkin]

Incongruous wrote:
> 
> Dude! Something must've happened to that file, there is a tree in the 
> Contents tab, but it doesn't matter which help file I click on nothing
gets 
> displayed. The help files do not have any data in them!
> What did I do wrong?
> 

Not being able to open a downloaded CHM file is a security feature of
Windows,
see the Microsoft KB article:

http://support.microsoft.com/kb/902225

To summarize the workaround mentioned in the above MS KB article: In order
to
be able to correctly view the CHM file, right-click it, go to the
"Properties"
tab, click the "Unblock" button, and then click the "Apply" button.

--
Joe Mistachkin

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

Re: [sqlite] System.Data.SQLite documentation

[Incongruous]

Thanks for the help Joe Mistachkin.

Dude! Something must've happened to that file, there is a tree in the 
Contents tab, but it doesn't matter which help file I click on nothing gets 
displayed. The help files do not have any data in them!

What did I do wrong?

-Original Message- 
From: Joe Mistachkin

Sent: Thursday, February 13, 2014 9:42 PM
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] System.Data.SQLite documentation


Incongruous wrote:


So, please, point me out to the location of the documentation for

System.Data.SQLite.




https://system.data.sqlite.org/index.html/doc/trunk/Doc/SQLite.NET.chm?mimet
ype=application/x-chm

Also, please see the FAQ:

https://system.data.sqlite.org/index.html/doc/trunk/www/faq.wiki

And the download page:

https://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki

--
Joe Mistachkin

___
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] System.Data.SQLite documentation

[Joe Mistachkin]

Incongruous wrote:
>
> So, please, point me out to the location of the documentation for
System.Data.SQLite.
>

https://system.data.sqlite.org/index.html/doc/trunk/Doc/SQLite.NET.chm?mimet
ype=application/x-chm 

Also, please see the FAQ:

https://system.data.sqlite.org/index.html/doc/trunk/www/faq.wiki

And the download page:

https://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki

--
Joe Mistachkin

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

Re: [sqlite] Once again about random values appearance

[James K. Lowden]

> > select id, (select id from TestTable where id = abs(random() % 100))
> > as rndid from TestTable where id=rndid

On Thu, 13 Feb 2014 07:26:55 -0500
Richard Hipp  wrote:

> It is undefined behavior, subject to change depending the specific
> version of SQLite, compile-time options, optimization settings, and
> the whim of the query planner.

It should be defined.  

In the above query, random() should be evaluated once.  In the SQL
model the user provides inputs once, and the system evaluates them
once.  

This issue seems to have a way of returning.  Didn't we recently
discuss it with regard to time?  

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

[sqlite] System.Data.SQLite documentation

[Incongruous]

After searching the archives for this year, I was really surprised that no one 
has ever asked for the documentation of System.Data.SQLite! I am very new in C# 
and with my little bit of knowledge I really need a lot of help for... even for 
just opening a connection to the database. I know that a little bit of 
knowledge of  ‘LINQ’ might help me [ I think ], but when creating a wrapper in 
C++ for SQLite, I did not need to ask too many questions, since it is all there 
in the documentation.
I am really hoping that such documentation exist, otherwise I am going to drive 
you crazy with all my question and I am not looking forward to that. So, 
please, point me out to the location of the documentation for 
System.Data.SQLite.

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

Re: [sqlite] Field not editable

[James K. Lowden]

On Thu, 13 Feb 2014 10:01:28 +
Simon Slavin  wrote:

> Your question is an example of a long term discussion about whether
> your entire business philosophy should be encoded within your SQL
> databases, with numerous FOREIGN KEYs, CONSTRAINTs and TRIGGERs used
> to enforce how you run the business.  The two sides of the argument
> are about evenly matched and there are no conclusions yet.

I think you will concede there is some distance between implementing
security in the DBMS and encoding one's entire busness philosophy in
it.  ;-)

The idea of a Database Manage System arose from the need to manage the
database.  One important aspect was to arbitrate changes to the data
such that different applications would not update it in ways that were
mutually inconsistent or logically inconsistent.  It was never a
philosophical question, but a practical matter of protecting the data.  

--jkl

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

Re: [sqlite] Field not editable

[James K. Lowden]

On Thu, 13 Feb 2014 21:40:53 +0100
Zsbán Ambrus  wrote:

> > I want the name of the country can not be changed.
> 
> You can put all the tables you want to be not changable to a separate
> database file which you attach as read-only.  Use the ATTACH statement
> with the URI filename syntax, as documented in
> "http://sqlite.org/uri.html; , to attach the database as read-only.

It occurs to me there's an even stronger (and more involved) solution.
A virtual table (http://www.sqlite.org/vtab.html) could return values
from any source, and prevent updates by setting xUpdate to NULL.  

--jkl

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

Re: [sqlite] HTML Tokenizer

[RSmith]

On 2014/02/13 22:35, Petite Abeille wrote:
While we are at it, www.sqlite.org exhibits many validation errors: 
http://validator.w3.org/check?uri=http%3A%2F%2Fwww.sqlite.org%2F=%28detect+automatically%29=Inline=0=W3C_Validator%2F1.3+http%3A%2F%2Fvalidator.w3.org%2Fservices#result


Yea I actually had some web zealot once point out to me that one of my sites produced 25 errors in the W3C validator. I 
laughed and pointed out that that www.google.com scored 30 errors (I see they are down to 23 errors and 4 warnings now) and that his 
first 3 sites I checked all had over a 100 - (which is quite normal actually - Amazon.com gets over 500 errors, but I did not 
mention this).


Point is - I wouldn't lose any sleep over the 37 SQLite errors, neither should 
any SQLite web admin :)

For ref: Google:
http://validator.w3.org/check?uri=http%3A%2F%2Fwww.google.com%2F=%28detect+automatically%29=Inline=0=W3C_Validator%2F1.3+http%3A%2F%2Fvalidator.w3.org%2Fservices

Amazon:
http://validator.w3.org/check?uri=http%3A%2F%2Fwww.amazon.com%2F=%28detect+automatically%29=Inline=0=W3C_Validator%2F1.3+http%3A%2F%2Fvalidator.w3.org%2Fservices




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

Re: [sqlite] HTML Tokenizer

[Petite Abeille]

On Feb 13, 2014, at 9:52 PM, Jan Nijtmans  wrote:

> But if you put the validator in HTML5 mode, there are many less errors:

Possibly. But it says 'HTML 4.01 Strict' on the tin:

http://www.w3.org/TR/html4/strict.dtd”>

Either way, a bunch of errors.


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

Re: [sqlite] HTML Tokenizer

[Jan Nijtmans]

2014-02-13 21:35 GMT+01:00 Petite Abeille :
>
> On Feb 13, 2014, at 9:08 PM, Petite Abeille  wrote:
>
>> curl -s http://www.sqlite.org | lynx -nolist -stdin -dump
>
> While we are at it, www.sqlite.org exhibits many validation errors:
>
> http://validator.w3.org/check?uri=http%3A%2F%2Fwww.sqlite.org%2F=%28detect+automatically%29=Inline=0=W3C_Validator%2F1.3+http%3A%2F%2Fvalidator.w3.org%2Fservices#result

But if you put the validator in HTML5 mode, there are many less errors:



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

Re: [sqlite] Field not editable

[Zsbán Ambrus]

On 2/13/14, O.D. vidal  wrote:
> I would like a field is not editable. What is the best solution?
>
> I want the name of the country can not be changed.

You can put all the tables you want to be not changable to a separate
database file which you attach as read-only.  Use the ATTACH statement
with the URI filename syntax, as documented in
"http://sqlite.org/uri.html; , to attach the database as read-only.
Of course, when you initially create and fill the table, you have to
attach the same database file in read-write mode, but later your
application can open it read-only.

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

Re: [sqlite] HTML Tokenizer

[Petite Abeille]

On Feb 13, 2014, at 9:08 PM, Petite Abeille  wrote:

> curl -s http://www.sqlite.org | lynx -nolist -stdin -dump

While we are at it, www.sqlite.org exhibits many validation errors:

http://validator.w3.org/check?uri=http%3A%2F%2Fwww.sqlite.org%2F=%28detect+automatically%29=Inline=0=W3C_Validator%2F1.3+http%3A%2F%2Fvalidator.w3.org%2Fservices#result



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

Re: [sqlite] HTML Tokenizer

[Scott Robison]

My current project needed to tokenize the text in HTML without the tags.
The easy solution for us was to license a library from Chilkat that
supported text extraction then tokenize that. I'm on my phone at the moment
but could supply more details later if desired.

SDR
On Feb 13, 2014 1:02 PM, "David King"  wrote:

> > New to Sqlite, anybody knows is there a HTML tokenizer for full text
> search,
> > Or do I need to implement my own?
>
> There isn't an HTML tokeniser. But the default tokeniser considers
> punctuation like <> to be word breaks so it may already work for you with
> the down side that things like Hello! will consider
> "div", "class", "foo", and "hello" as words. (Rather than the just "hello"
> that you may be after)
>
> ___
> 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] HTML Tokenizer

[Petite Abeille]

On Feb 13, 2014, at 8:48 PM, Wang, Baoping  wrote:

> New to Sqlite, anybody knows is there a HTML tokenizer for full text search,

No.

> Or do I need to implement my own?

If you feel the urge. Otherwise, try lynx -dump.

For example:

curl -s http://www.sqlite.org | lynx -nolist -stdin -dump

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

Re: [sqlite] HTML Tokenizer

[David King]

> New to Sqlite, anybody knows is there a HTML tokenizer for full text search,
> Or do I need to implement my own?

There isn't an HTML tokeniser. But the default tokeniser considers punctuation 
like <> to be word breaks so it may already work for you with the down side 
that things like Hello! will consider "div", "class", 
"foo", and "hello" as words. (Rather than the just "hello" that you may be 
after)


signature.asc
Description: Message signed with OpenPGP using GPGMail
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] HTML Tokenizer

[Wang, Baoping]

New to Sqlite, anybody knows is there a HTML tokenizer for full text search,
Or do I need to implement my own?

Thanks


Pursuant to Treasury Regulations, any U.S. federal tax advice contained in this 
communication, unless otherwise
stated, is not intended and cannot be used for the purpose of avoiding 
tax-related penalties.

The information contained in this E-mail message is privileged, confidential, 
and may be protected from disclosure;
please be aware that any other use, printing, copying, disclosure or 
dissemination of this communication may be 
subject to legal restriction or sanction. If you think that you have received 
this E-mail message in 
error, please reply to the sender.

This E-mail message and any attachments have been scanned for viruses and are 
believed to be free of any virus or 
other defect that might affect any computer system into which it is received 
and opened. However, it is the 
responsibility of the recipient to ensure that it is virus free and no 
responsibility is accepted by Kelley 
Drye & Warren LLP for any loss or damage arising in any way from its use.



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

Re: [sqlite] C# - SQLite - and other things

[Joe Mistachkin]

Incongruous wrote:
> 
> After downloading sqlite-netFx451-static-binary-x64-2013-1.0.90.0 and
uncompressing
> it, I got this list of file:
> 

I think using the System.Data.SQLite NuGet package would be better in this
case.  The
package is here (it can also be found by searching in the Visual Studio
IDE):

https://nuget.org/api/v2/package/System.Data.SQLite/1.0.91.0

--
Joe Mistachkin

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

Re: [sqlite] How to use SQLite in C#

[Adam Devita]

Good day,
There are 2 different ideas to look into:

1) Using the API
2) Integrating tools into your IDE

>From the prospective of my 32-bit Windows 7 machine.

Under 1) Using the API
If you are just using the API, then you don't need very many of those files.
using System.Data.SQLite;
Be sure to reference System.Data.SQLite.dll.
I've also got SQLite.Interop.dll in the path.
Looking at the Windows Forms project I use and reference LINQ items,
although they are not sqlite ones.

2) IDE Integration, meaning some graphics tools in Visual Studio  etc.
I think posting your version would help others help you.
Between VS2008 and VS2010 some config parameters and directories got moved
around. Integrating new tools with an installer designed for something
older can be an arduous path to enlightenment.   I haven't had the pleasure
with other IDEs yet, and haven't had the need for the tools.

regards,
Adam DeVita





On Wed, Feb 12, 2014 at 9:23 AM, Jamiil  wrote:

> After downloading sqlite-netFx451-static-binary-x64-2013-1.0.90.0 and
> uncompressing it, I got this list of file:
>
> Installer.exe
> Installer.pdb
> northwindEF.db
> SQLite.Designer.dll
> SQLite.Designer.pdb
> SQLite.Designer.xml
> SQLite.Interop.dll
> SQLite.Interop.pdb
> sqlite_file_list.txt
> System.Data.SQLite.dll
> System.Data.SQLite.Linq.dll
> System.Data.SQLite.Linq.pdb
> System.Data.SQLite.Linq.xml
> System.Data.SQLite.pdb
> System.Data.SQLite.xml
> test.db
> test.exe
> test.exe.config
> test.pdb
> testlinq.exe
> testlinq.exe.config
> testlinq.pdb
> [ I tried the intaller.exe, but I get a msg saying: Cannot continue, the
> "confirm" option is not enabled. ]
>
> I have a project that looks like this:
> Project:
> |   pro.exe
> |---> image
> |---> gui
> |---> mylib
> |---> sound
> |---> sqlite
> mysqlite.cs
>
> The files contained in the sqlite-netFx451-static-binary-x64-2013-1.0.90.0
> are located in a folder that exists in the %path%, but I cannot stop to
> wonder if all the files are necessary or if all I need is the DLLs in order
> to reference the methods in the database, and if I only need certain files,
> which ones are those?
>
> My second question is, how can I add the DLLs from
> sqlite-netFx451-static-binary-x64-2013-1.0.90.0 to the 'mysqlite.cs' file
> in order to reference the its methods?
>
> Any help would be much appreciated.
>
> Thanks in advance.
> ___
> 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] C# - SQLite - and other things

[Incongruous]

After downloading sqlite-netFx451-static-binary-x64-2013-1.0.90.0 and 
uncompressing it, I got this list of file:

Installer.exe
Installer.pdb
northwindEF.db
SQLite.Designer.dll
SQLite.Designer.pdb
SQLite.Designer.xml
SQLite.Interop.dll
SQLite.Interop.pdb
sqlite_file_list.txt
System.Data.SQLite.dll
System.Data.SQLite.Linq.dll
System.Data.SQLite.Linq.pdb
System.Data.SQLite.Linq.xml
System.Data.SQLite.pdb
System.Data.SQLite.xml
test.db
test.exe
test.exe.config
test.pdb
testlinq.exe
testlinq.exe.config
testlinq.pdb
[ I tried the intaller.exe, but I get a msg saying: Cannot continue, the 
"confirm" option is not enabled. ]

I have a project that looks like this:
Project:
|   pro.exe\ |
|---> image  \ |
|---> gui   \ |
|---> mylib   \ |
|---> sound  \ |
|---> sqlite   \ |
mysqlite.cs---|--->%PATH%
|__>SQLite.Designer.dll
SQLite.Designer.xml
   SQLite.Interop.dll
System.Data.SQLite.dll
   System.Data.SQLite.Linq.dll

The files contained in the sqlite-netFx451-static-binary-x64-2013-1.0.90.0 are 
located in a folder that exists in the %path%, but I cannot stop to wonder if 
all the files are necessary or if all I need is the DLLs in order to reference 
the methods in the database, and if I only need certain files, which ones are 
those?

My second question is, how can I add the DLLs from 
sqlite-netFx451-static-binary-x64-2013-1.0.90.0 to the ‘mysqlite.cs’ file in 
order to reference the its methods? I know that this question is unrelated to 
SQLite, but in its answer is my complete understanding of the solution provided.

Any help would be much appreciated.

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

Re: [sqlite] Once again about random values appearance

[Max Vlasov]

On Thu, Feb 13, 2014 at 4:26 PM, Richard Hipp  wrote:

> On Thu, Feb 13, 2014 at 4:45 AM, Max Vlasov  wrote:
>
> > Hi,
> >
> > probably was discussed and modified before, but I still can not
> understand
> > some peculiarities with random column values.
> >
>
> It is undefined behavior, subject to change depending the specific version
> of SQLite, compile-time options, optimization settings, and the whim of the
> query planner.
>
>

Thanks, this makes sense.

Assuming there are time related functions, user function with undefined
values etc, may I generalize this into "an expression is guaranteed to have
the same value in any context only if it is always return the same output
for the same input"?

Interesting, the problem probably arises since sometimes the symbols in the
result columns are easy to interpret mentally like "states" to be used
further, but they look more like macros to be substituted.

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

Re: [sqlite] Field not editable

[O.D. vidal]

sorry, it works well with a trigger and raise(abort, ‘cause').

Le 13 févr. 2014 à 09:50, O.D. vidal  a écrit :

> 
> Hello all,
> 
> I would like a field is not editable. What is the best solution? 
> 
> For example, I have a table Country: 
> 
> id - Name 
> 
> 1 - Brazil 
> 2 - Canada 
> ... 
> 
> I want the name of the country can not be changed.
> 
> Thank you!
> Greetings
> 
> olivier
> ___
> 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] How to use SQLite in C#

[Jamiil]

After downloading sqlite-netFx451-static-binary-x64-2013-1.0.90.0 and 
uncompressing it, I got this list of file:

Installer.exe
Installer.pdb
northwindEF.db
SQLite.Designer.dll
SQLite.Designer.pdb
SQLite.Designer.xml
SQLite.Interop.dll
SQLite.Interop.pdb
sqlite_file_list.txt
System.Data.SQLite.dll
System.Data.SQLite.Linq.dll
System.Data.SQLite.Linq.pdb
System.Data.SQLite.Linq.xml
System.Data.SQLite.pdb
System.Data.SQLite.xml
test.db
test.exe
test.exe.config
test.pdb
testlinq.exe
testlinq.exe.config
testlinq.pdb
[ I tried the intaller.exe, but I get a msg saying: Cannot continue, the 
"confirm" option is not enabled. ]

I have a project that looks like this:
Project:
|   pro.exe
|---> image
|---> gui
|---> mylib
|---> sound
|---> sqlite
mysqlite.cs

The files contained in the sqlite-netFx451-static-binary-x64-2013-1.0.90.0 are 
located in a folder that exists in the %path%, but I cannot stop to wonder if 
all the files are necessary or if all I need is the DLLs in order to reference 
the methods in the database, and if I only need certain files, which ones are 
those?

My second question is, how can I add the DLLs from 
sqlite-netFx451-static-binary-x64-2013-1.0.90.0 to the ‘mysqlite.cs’ file in 
order to reference the its methods?

Any help would be much appreciated.

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

Re: [sqlite] Once again about random values appearance

[Richard Hipp]

On Thu, Feb 13, 2014 at 4:45 AM, Max Vlasov  wrote:

> Hi,
>
> probably was discussed and modified before, but I still can not understand
> some peculiarities with random column values.
>
> The table
>   Create table [TestTable] ([id] integer primary key)
>
> populated with 100 default values (thanks to CTE now made with a single
> query):
>
> with recursive
>   autoinc(id) as
>  (values(1) UNION ALL Select id+1 from autoinc where id < 100)
> insert into TestTable select id from autoinc
>
>
> So the following query
>
> select id, (select id from TestTable where id = abs(random() % 100)) as
> rndid from TestTable where id=rndid
>
> returns a single row (as expected), but this row contains different values
> for id and rndid. I suppose this is because rndid to be shown is calculated
> once, but the one used for comparison is calculated again and again. Is
> this correct behavior?
>

It is undefined behavior, subject to change depending the specific version
of SQLite, compile-time options, optimization settings, and the whim of the
query planner.

By analogy, in C/C++ if you write:

  int x = 1;
  int y = x++/x++;

What is the value for y?  0, 1, or 2?


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

Re: [sqlite] When is the decltype recognized?

[Dominique Devienne]

On Thu, Feb 13, 2014 at 11:44 AM, Clemens Ladisch  wrote:
> By design, SQLite uses dynamic typing, and keeps only the actual type of
> the value.  The declared type is a property of the *column*, not of the
> value itself, so it is lost as soon as the value is no longer associated
> directly with the table.

But the fact that SQLite values are dynamically typed is orthogonal to
making a best-effort to carry the column typing through intermediate
result sets (which are logically just like tables). The query parser
obviously knows how to resolve columns to fetch the right values, so
it could carry those columns declared type (if any) along, more often
than it does now.

Whether a query is ordered and must use a temporary table for sorting
shouldn't matter IMHO, for example.

I know the doc is careful to make little promises in this, yet it
feels to me that it's more an implementation issue, than a design
issue. And by that I'm not saying that the implementation is at fault
or buggy, not at all, but when considering this problem logically with
no regard for implementation concerns or how the VDBE VM works, there
are clearly cases SQLite could know the declared type of the outermost
select clause "columns", and does not currently provide it. I
completely understand coding efforts going into other directions, but
this is again does not feel like "by design" to me.

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

Re: [sqlite] When is the decltype recognized?

[Clemens Ladisch]

Peter Otten wrote:
> Clemens Ladisch wrote:
>> Peter Otten wrote:
>>> Clemens Ladisch wrote:
 The first query uses a temporary table for sorting.  The column in that
 temporary table does not have a declared type.

 The second query returns the values directly from the underlying table
 column, so the declared type is also from that table.
>>>
>>> Thank you. Is there a way around that limitation?
>>
>> Which of these two cases do you think is the result of a limitation?
>
> Losing type information. I think it should be possible to infer the column
> type in a subquery (but have no idea yet how hard that would be). Given
>
> select * from (select alpha from demo union all select alpha from demo) order 
> by alpha
>
> the alpha column in both parts of the union has the same type ("custom").
> Therefore the resulting column in the outer select could safely be assumed
> to be of type "custom", too.

By design, SQLite uses dynamic typing, and keeps only the actual type of
the value.  The declared type is a property of the *column*, not of the
value itself, so it is lost as soon as the value is no longer associated
directly with the table.


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

Re: [sqlite] When is the decltype recognized?

[Peter Otten]

Clemens Ladisch wrote:

> Peter Otten wrote:
>> Clemens Ladisch wrote:
>>> Peter Otten wrote:
 select * from (select alpha from demo union all select alpha from demo)
 order by alpha decltype: (null)

 select * from (select alpha from demo union all select alpha from demo)
 decltype: custom

 Even taking http://sqlite.org/c3ref/column_decltype.html
 """
 If this statement is a SELECT statement and the Nth column of the
 returned result set of that SELECT is a table column (not an expression
 or subquery) then the declared type of the table column is returned. If
 the Nth column of the result set is an expression or subquery, then a
 NULL pointer is returned.
 """
 into account I would have expected either NULL or "custom" for both
 queries.
>>>
>>> The first query uses a temporary table for sorting.  The column in that
>>> temporary table does not have a declared type.
>>>
>>> The second query returns the values directly from the underlying table
>>> column, so the declared type is also from that table.
>>
>> Thank you. Is there a way around that limitation?
> 
> Which of these two cases do you think is the result of a limitation?

Losing type information. I think it should be possible to infer the column 
type in a subquery (but have no idea yet how hard that would be). Given

select * from (select alpha from demo union all select alpha from demo)
order by alpha

the alpha column in both parts of the union has the same type ("custom").
Therefore the resulting column in the outer select could safely be assumed 
to be of type "custom", too.

> According to the documentation, neither query is guaranteed to be able
> to return information about the declared type.  The second does anyway
> because the query optimizer flattened the subquery.

I understand that there are no guarantees.

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

Re: [sqlite] Field not editable

[O.D. vidal]

ok, thank you very much Simon!

olivier

Le 13 févr. 2014 à 11:01, Simon Slavin  a écrit :

> 
> On 13 Feb 2014, at 9:55am, O.D. vidal  wrote:
> 
>> Why do you say:
>> 
>>> This would normally be done inside your programming language.
>> 
>> strengthen the rule directly in the database seems common sense.
> 
> I can see that argument, which is why I bothered figuring out the TRIGGER.
> 
> However, anyone who is able to make changes to the data in that database can 
> also make changes to the TRIGGERs in the database.  So they could just DROP 
> that TRIGGER.  They would be less likely to be able to change the software 
> that is being used to edit the data.
> 
> Your question is an example of a long term discussion about whether your 
> entire business philosophy should be encoded within your SQL databases, with 
> numerous FOREIGN KEYs, CONSTRAINTs and TRIGGERs used to enforce how you run 
> the business.  The two sides of the argument are about evenly matched and 
> there are no conclusions yet.
> 
> I hope you manage to implement a solution which suits you.
> 
> 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

Re: [sqlite] Field not editable

[Simon Slavin]

On 13 Feb 2014, at 9:55am, O.D. vidal  wrote:

> Why do you say:
> 
>> This would normally be done inside your programming language.
> 
> strengthen the rule directly in the database seems common sense.

I can see that argument, which is why I bothered figuring out the TRIGGER.

However, anyone who is able to make changes to the data in that database can 
also make changes to the TRIGGERs in the database.  So they could just DROP 
that TRIGGER.  They would be less likely to be able to change the software that 
is being used to edit the data.

Your question is an example of a long term discussion about whether your entire 
business philosophy should be encoded within your SQL databases, with numerous 
FOREIGN KEYs, CONSTRAINTs and TRIGGERs used to enforce how you run the 
business.  The two sides of the argument are about evenly matched and there are 
no conclusions yet.

I hope you manage to implement a solution which suits you.

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

Re: [sqlite] Field not editable

[O.D. vidal]

Thank you Simon!

Why do you say:

> This would normally be done inside your programming language.

strengthen the rule directly in the database seems common sense.

olivier


Le 13 févr. 2014 à 10:37, Simon Slavin  a écrit :

> 
> On 13 Feb 2014, at 8:50am, O.D. vidal  wrote:
> 
>> I would like a field is not editable. What is the best sol


>> ution? 
>> 
>> For example, I have a table Country: 
>> 
>> id - Name 
>> 
>> 1 - Brazil 
>> 2 - Canada 
>> ... 
>> 
>> I want the name of the country can not be changed.
> 
> This would normally be done inside your programming language.  However it 
> might be possible to enforce the rule inside SQLite using a TRIGGER:
> 
> 
> 
> It might looks something like
> 
> CREATE a TRIGGER
>   UPDATE ON Country
>   WHEN new.name != old.name
>   BEGIN
>   RAISE (FAIL, 'Not allowed to change the name of a country.')
>   END
> 
> Note: I just made that up off the top of my head and have no idea whether it 
> will work.
> 
> You will have to guard against
> 
> (A) people DROPping the TRIGGER, and
> (B) people deleting the row from the Country table and making a new one with 
> a different name.
> 
> 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] Once again about random values appearance

[Max Vlasov]

Hi,

probably was discussed and modified before, but I still can not understand
some peculiarities with random column values.

The table
  Create table [TestTable] ([id] integer primary key)

populated with 100 default values (thanks to CTE now made with a single
query):

with recursive
  autoinc(id) as
 (values(1) UNION ALL Select id+1 from autoinc where id < 100)
insert into TestTable select id from autoinc


So the following query

select id, (select id from TestTable where id = abs(random() % 100)) as
rndid from TestTable where id=rndid

returns a single row (as expected), but this row contains different values
for id and rndid. I suppose this is because rndid to be shown is calculated
once, but the one used for comparison is calculated again and again. Is
this correct behavior?

Also checked several dll versions and saw that this change appeared
somewhere between 3.6.13 and 3.6.16, so .13 showed identical values, while
.16 different.

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

[sqlite] [ANNOUNCEMENT] Updated: sqlite3-3.8.3.1-1 for Cygwin/Cygwin64

[Jan Nijtmans]

SQLite is a software library that implements a self-contained,
serverless, zero-configuration, transactional SQL database engine

Changes since 3.8.3-2
=
* Updated to upstream 3.8.3.1 release. See:
 
* One additional bug-fix in "sqlite3-tcl", which was causing
  crashes during error-handling in Cygwin64:


Planning SQLite 3.8.4 for Cygwin.
==
At this moment, Cygwin applications can make use of
two different VFS'es: "win32-longpath" or "unix-cygwin",
the latter is the default. Both of them have a defect
the other one doesn't have: "win32-longpath" doesn't
handle Cygwin paths correctly in all situations, and
"unix-cygwin" doesn't handle virus-scanner attacks
(causing locking errors when using McAfee)
that well. Therefore I am working on adaptations
improving this situation, I hope this to be ready
when SQLite 3.8.4 is released.

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

Re: [sqlite] Field not editable

[Simon Slavin]

On 13 Feb 2014, at 8:50am, O.D. vidal  wrote:

> I would like a field is not editable. What is the best solution? 
> 
> For example, I have a table Country: 
> 
> id - Name 
> 
> 1 - Brazil 
> 2 - Canada 
> ... 
> 
> I want the name of the country can not be changed.

This would normally be done inside your programming language.  However it might 
be possible to enforce the rule inside SQLite using a TRIGGER:



It might looks something like

CREATE a TRIGGER
UPDATE ON Country
WHEN new.name != old.name
BEGIN
RAISE (FAIL, 'Not allowed to change the name of a country.')
END

Note: I just made that up off the top of my head and have no idea whether it 
will work.

You will have to guard against

(A) people DROPping the TRIGGER, and
(B) people deleting the row from the Country table and making a new one with a 
different name.

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

Re: [sqlite] SQlite3 Performnace

[Hick Gunter]

.timer on (in the sqlite shell)

Unless you are storing the databases on different disks AND your SQLite queries 
are IO bound I don't expect any performance gains.

-Ursprüngliche Nachricht-
Von: techi eth [mailto:techi...@gmail.com]
Gesendet: Donnerstag, 13. Februar 2014 10:11
An: General Discussion of SQLite Database
Betreff: [sqlite] SQlite3 Performnace

Hi,



Is there any way I can check SQlite3 Database read/write timing for performance 
measurement.


What are the changes in performance if I have single SQlite3 Database file 
(Contain 'N' table) or  'N' Database file each contain single table.



Regards,

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


--
 Gunter Hick
Software Engineer
Scientific Games International GmbH
Klitschgasse 2 – 4, A - 1130 Vienna, Austria
FN 157284 a, HG Wien
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This e-mail is confidential and may well also be legally privileged. If you 
have received it in error, you are on notice as to its status and accordingly 
please notify us immediately by reply e-mail and then delete this message from 
your system. Please do not copy it or use it for any purposes, or disclose its 
contents to any person as to do so could be a breach of confidence. Thank you 
for your cooperation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQlite3 Performnace

[techi eth]

Hi,



Is there any way I can check SQlite3 Database read/write timing for
performance measurement.


What are the changes in performance if I have single SQlite3 Database file
(Contain 'N' table) or  'N' Database file each contain single table.



Regards,

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

Re: [sqlite] When is the decltype recognized?

[Clemens Ladisch]

Peter Otten wrote:
> Clemens Ladisch wrote:
>> Peter Otten wrote:
>>> select * from (select alpha from demo union all select alpha from demo) 
>>> order by alpha
>>> decltype: (null)
>>>
>>> select * from (select alpha from demo union all select alpha from demo)
>>> decltype: custom
>>>
>>> Even taking http://sqlite.org/c3ref/column_decltype.html
>>> """
>>> If this statement is a SELECT statement and the Nth column of the
>>> returned result set of that SELECT is a table column (not an expression
>>> or subquery) then the declared type of the table column is returned. If
>>> the Nth column of the result set is an expression or subquery, then a
>>> NULL pointer is returned.
>>> """
>>> into account I would have expected either NULL or "custom" for both
>>> queries.
>>
>> The first query uses a temporary table for sorting.  The column in that
>> temporary table does not have a declared type.
>>
>> The second query returns the values directly from the underlying table
>> column, so the declared type is also from that table.
>
> Thank you. Is there a way around that limitation?

Which of these two cases do you think is the result of a limitation?

According to the documentation, neither query is guaranteed to be able
to return information about the declared type.  The second does anyway
because the query optimizer flattened the subquery.


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

[sqlite] Field not editable

[O.D. vidal]

Hello all,

I would like a field is not editable. What is the best solution? 

For example, I have a table Country: 

id - Name 

1 - Brazil 
2 - Canada 
... 

I want the name of the country can not be changed.

Thank you!
Greetings

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