Re: [sqlite] "natrual" join (not "natural")

2012-11-01 Thread Roger Binns 
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 01/11/12 18:25, Mark Brand wrote:
> You mentioned that this extra AS might help avoid ambiguities and
> errors. The only example of this you mentioned was where aliases names
> with spaces are not quoted. Do you have cases of this in mind that
> wouldn't be syntax errors anyway? In other words, I'm looking for
> evidence that "missing AS" really is in the same category of "trailing
> semicolons, etc".

Table originally has a column named "price".  For various reasons they add
a new column named "price new".  Since you can't use bindings in queries
they will either have been written out by hand or composed (eg sprintf).
If the latter code doesn't quote the names then the query includes "price
new" which selects the wrong column and "overwrites" the value returned
for "new".  There is a probability of it being detected which depends on
other names in the query/tables and what the consuming code uses.  But a
lint that warns about an implicit AS would have a 100% chance of catching
this problem, if is a relevant problem for that code base.

BTW I also put trailing slashes on directory names where possible - again
it is unnecessary, but again it makes the intentions crystal clear, and in
some cases does catch errors.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlCTJCIACgkQmOOfHg372QQGygCfYzALXoJmUX+xcNJRcXzf1bw/
tKMAnjugQ1YzQyBMqSK6Pt1TUHMHZ/Q3
=P2IF
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "natrual" join (not "natural")

2012-11-01 Thread Mark Brand 



The misspelling of NATURAL would be caught by a warning for JOIN
without ON constraint.

True.  And other possible errors could be caught by missing AS.


Really?


In my opinion, it's actually a good idea to leave out the AS in table
aliases since SQL-92 and many or most implementations do not require
the AS, and at least one prominent implementation does not allow it.

You can leave out trailing semicolons in Javascript statements, or not put
squiggly brackets around one statement blocks in C, or not close many HTML
tags, or depend on accidental ordering in SQL etc.

You can do whatever you want for your code.  But many people appreciate
tools that proactively help find problems.  It is often very common to put
in "unnecessary" extra syntax to make it very clear what the intentions
are (eg extra parentheses even though precedence doesn't require them or
things in paragraph above).  Using AS to make your intentions very clear
is up to you and others (I always do it).


Whether you use AS here or not is a matter of taste and portability. As 
far as the SQL-92 standard and many/most implementations are concerned, 
we might just as well say "the AS in table aliases is tolerated" as "the 
AS in table aliases is optional". We might just as well make the 
argument that lint should warn about the *presence* of AS as its absence.


You mentioned that this extra AS might help avoid ambiguities and 
errors. The only example of this you mentioned was where aliases names 
with spaces are not quoted. Do you have cases of this in mind that 
wouldn't be syntax errors anyway? In other words, I'm looking for 
evidence that "missing AS" really is in the same category of "trailing 
semicolons, etc".


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

Re: [sqlite] light weight write barriers

2012-11-01 Thread Howard Chu 

Alan Cox wrote:

How about that recently preliminary infrastructure to send ORDERED commands
instead of queue draining was deleted from the kernel, because "there's no
difference where to drain the queue, on the kernel or the storage side"?


Send patches.


Isn't any type of kernel-side ordering an exercise in futility, since
  a) the kernel has no knowledge of the disk's actual geometry
  b) most drives will internally re-order requests anyway
  c) cheap drives won't support barriers

Even assuming the drives honored all your requests without lying, how would 
you really want this behavior exposed? From the userland perspective, there 
are very few apps that care. Probably only transactional databases, really.


As a DB author, I'm not sure I'd be keen on this as an open() or fcntl() 
option. Databases that really care would be on dedicated filesystems and/or 
devices, so per-file control would be tedious. You would most likely want to 
say "all writes to this string of devices should be order-preserving" and 
forget about it. With that guarantee, a careful writer can have perfectly 
intact data structures all the time, without ever slowing down for a fsync.


--
  -- Howard Chu
  CTO, Symas Corp.   http://www.symas.com
  Director, Highland Sun http://highlandsun.com/hyc/
  Chief Architect, OpenLDAP  http://www.openldap.org/project/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "natrual" join (not "natural")

2012-11-01 Thread Roger Binns 
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 01/11/12 15:46, Mark Brand wrote:
> A warning for "missing" AS would create noise and serve no purpose.

Then ignore/don't use it for your perfect code!

Other developers do use their own code (that could have been written years
ago), other code and other libraries.  Sometimes they have bugs like for
example not quoting names that have spaces in them.  A missing AS warning
would catch that.

> The misspelling of NATURAL would be caught by a warning for JOIN
> without ON constraint.

True.  And other possible errors could be caught by missing AS.

> In my opinion, it's actually a good idea to leave out the AS in table 
> aliases since SQL-92 and many or most implementations do not require
> the AS, and at least one prominent implementation does not allow it.

You can leave out trailing semicolons in Javascript statements, or not put
squiggly brackets around one statement blocks in C, or not close many HTML
tags, or depend on accidental ordering in SQL etc.

You can do whatever you want for your code.  But many people appreciate
tools that proactively help find problems.  It is often very common to put
in "unnecessary" extra syntax to make it very clear what the intentions
are (eg extra parentheses even though precedence doesn't require them or
things in paragraph above).  Using AS to make your intentions very clear
is up to you and others (I always do it).

Currently there aren't any practical tools for auditing your running code
when using SQLite - something would be very helpful.

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlCTEJQACgkQmOOfHg372QRZsQCeLUi3VS892qXYa2WXM5KFyydv
hfUAoI+sknCBiRVVJv/ESuQS9ALwjMAV
=qnqA
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "natrual" join (not "natural")

2012-11-01 Thread Mark Brand 

Nico Williams wrote:

On Thu, Nov 1, 2012 at 5:18 PM, Mark Brand  wrote:

Why should the "missing" AS even be a warning in lint mode? SQL-92 does not
mandate it. One of the most important implementations does not even accept
it. Do you know of any SQL implementation that requires an AS here?

To catch misspellings of NATURAL, as in this case, naturally (heh).

Lint programs typically have options for warnings to enable/disable,
so let this warning be a user choice, no?


A warning for "missing" AS would create noise and serve no purpose.

The misspelling of NATURAL would be caught by a warning for JOIN without 
ON constraint. A warning for this makes sense because an (INNER) JOIN 
without an ON clause is not allowed by by SQL-92 or any implementation 
besides sqlite that I am aware of.


In my opinion, it's actually a good idea to leave out the AS in table 
aliases since SQL-92 and many or most implementations do not require the 
AS, and at least one prominent implementation does not allow it.


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

Re: [sqlite] "natrual" join (not "natural")

2012-11-01 Thread Nico Williams 
On Thu, Nov 1, 2012 at 5:18 PM, Mark Brand  wrote:
> Why should the "missing" AS even be a warning in lint mode? SQL-92 does not
> mandate it. One of the most important implementations does not even accept
> it. Do you know of any SQL implementation that requires an AS here?

To catch misspellings of NATURAL, as in this case, naturally (heh).

Lint programs typically have options for warnings to enable/disable,
so let this warning be a user choice, no?

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

Re: [sqlite] "natrual" join (not "natural")

2012-11-01 Thread Mark Brand 

On 01/11/12 14:32, Mark Brand wrote:

My point is that the proposed lint mode should worry more about 2) than
1).

I didn't think they are mutually exclusive.  The class of probable errors
detected overlaps.


But the "missing" AS is not a probable error. It's standard and 
well-established. So I should probably have said "..lint mode should 
worry about 2) and ignore 1).."





... as Oracle at least doesn't allow it.

All lint mode stuff would be warnings not errors, so as a developer you
can choose which ones to ignore, black/whitelist or pay attention to and
cause a fault in your test suite.


Why should the "missing" AS even be a warning in lint mode? SQL-92 does 
not mandate it. One of the most important implementations does not even 
accept it. Do you know of any SQL implementation that requires an AS here?


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

Re: [sqlite] "natrual" join (not "natural")

2012-11-01 Thread Roger Binns 
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 01/11/12 14:32, Mark Brand wrote:
> My point is that the proposed lint mode should worry more about 2) than
> 1).

I didn't think they are mutually exclusive.  The class of probable errors
detected overlaps.

> ... as Oracle at least doesn't allow it.

All lint mode stuff would be warnings not errors, so as a developer you
can choose which ones to ignore, black/whitelist or pay attention to and
cause a fault in your test suite.

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlCS8dAACgkQmOOfHg372QStgwCgwIUMjZ0XMHqy1GtMSyN+YC8e
LwkAn30MqqJYyGI8QOYzO3XNOA9sY/oQ
=aBAi
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "natrual" join (not "natural")

2012-11-01 Thread Mark Brand 



The optionality of "AS" when aliasing column names is not strange.


Oops. I see that I did not mean "column names". This is about table aliases.


You are missing the point :-)  I am not arguing for syntax changes or
anything else in the core of SQLite or SQL.


No, I don't don't think I am missing the point. I'll explain. The 
surprise interpretation of "select * from a natral join b; " was made 
possible by the combination of 2 features of sqlite:


1. Not requiring AS for table aliases.
2. Treating JOIN without ON constraint as "CROSS JOIN"

1) appears to agree with SQL-92:
http://savage.net.au/SQL/sql-92.bnf.html#correlation%20specification

2) does not:
http://savage.net.au/SQL/sql-92.bnf.html#joined%20table

My point is that the proposed lint mode should worry more about 2) than 
1). Setting off alarms for a "missing" AS would just create a lot of 
noise since portable SQL might need to omit the AS, as Oracle at least 
doesn't allow it.



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

Re: [sqlite] light weight write barriers

2012-11-01 Thread Alan Cox 
> How about that recently preliminary infrastructure to send ORDERED commands 
> instead of queue draining was deleted from the kernel, because "there's no 
> difference where to drain the queue, on the kernel or the storage side"?

Send patches.

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

Re: [sqlite] "natrual" join (not "natural")

2012-11-01 Thread Roger Binns 
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 01/11/12 12:33, Mark Brand wrote:
> The optionality of "AS" when aliasing column names is not strange.

You are missing the point :-)  I am not arguing for syntax changes or
anything else in the core of SQLite or SQL.

Instead in an additional "lint" mode, it should point out when you
neglected to use AS.  The purpose of lint mode is to reduce the
probability of bugs or unintended consequences in your code.  Omitting AS
is one way to increase bug probability as this thread shows.  (Another
example would be if query strings are generated and column names
containing spaces are not quoted.)

There are an ever expanding list of things a lint mode would help with as
you gave.  I'm just hoping the SQLite team can figure out a way of
providing such a mode given constraints, while still keeping SQLite "lite".

Roger


-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlCS2LoACgkQmOOfHg372QSOQgCfUaBHQrRpHpM6PF/BnCUrn5+G
oeQAn1+bcF+A/D/hdKz7iLVsVVJca5MO
=fP/G
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SUGGESTION: "now" as alias for "strftime('%s','now')"

2012-11-01 Thread Simon Slavin 

On 1 Nov 2012, at 7:55pm, Григорий Григоренко  wrote:

> it is a common practice to store datetime values as UNIX time UTC. 
> Maybe, Sqlite should have some shortcut for evaluating current moment?

Please read



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

[sqlite] SUGGESTION: "now" as alias for "strftime('%s','now')"

2012-11-01 Thread Григорий Григоренко 
Hello,

it is a common practice to store datetime values as UNIX time UTC. 
Maybe, Sqlite should have some shortcut for evaluating current moment?
Some alias for strftime('%s','now') ? Like, "now" or "unixnow":

  created_at   DEFAULT  (now())     vs     created_at    DEFAULT  
(strftime('%s','now'))

It is shorter and more important - it can be written without consulting docs 
for strfime() , I cannot remember parameters  (


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

Re: [sqlite] System.Data.SQLite.dll locks files forever starting fromv1.0.82.0

2012-11-01 Thread Joe Mistachkin 

Alex Reitbort wrote:
> 
> When running this sample with System.Data.SQLite.dll v1.0.81 everything
> works ok.
> 
> When running this sample with System.Data.SQLite.dll v1.0.82, it fails on
> File.Delete line with IOException: The process cannot access the file
> 'alex.db' because it is being used by another process.
> 
 
As of 1.0.82.0, the command also needs to be disposed before the connection
will be fully closed.

--
Joe Mistachkin

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

Re: [sqlite] "natrual" join (not "natural")

2012-11-01 Thread Mark Brand 

Roger Binns wrote:

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 31/10/12 07:27, Jonas Malaco Filho wrote:

Maybe there could be a strict switch.

There is a ticket for a "lint" mode that would help catch common issues
where there are possibly problems.  This is another example of something
appropriate for it to catch (always an explicit AS).  Earlier message and
link:

http://goo.gl/2ycor


The optionality of "AS" when aliasing column names is not strange. If I 
am not mistaken, Postgresql (as pointed out by Richard Hipp), MS SQL 
Server, and Oracle do not require an "AS" here. Oracle may not even 
allow it.


It seems to me that a "lint mode" would be better off catching JOIN 
without an ON constraint. Sqlite converts these to CROSS JOIN. This is 
the innovation on Sqlite's part that accounted for the unintended 
interpretation of:



sqlite> select natral.* from a natral join b;


At least, I am not aware of other DBMs doing this. (Haven't researched 
it though.)


regards,

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

Re: [sqlite] New to SQLite Question - References

2012-11-01 Thread Simon Slavin 

On 1 Nov 2012, at 2:53pm, Ryan Kool  wrote:

> Can you give suggestions on where to look for information on connecting to
> a SQLite database from within a Java application?

They're usually called 'wrappers'.  Google the two words

Java SQLite

and pick one.

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

Re: [sqlite] sqlite versus MySQL automatic defragmentation on SSDs?

2012-11-01 Thread Vinay Y S 
Since you have a development team that's built complex software on top
of sqlite, why not instrument it to see how many seeks it's doing per
query operation?

This can be done relatively easily by writing a custom vfs shim that
can collect stats for the read/write seeks. This stat collected over a
period can give you an idea of fragmentation if the seeks are
increasing as the db gets older. If you are on newer Linux kernels,
you can use blktrace to diagnose latency issues.

I work on a very large scale distributed datastore that uses sqlite as
the on-disk format and we store huge amounts of data in it. But we try
to keep the size of each individual db file less than a few tens of
GBs for ease of management. But we have multiple dbs per server that
are attached and accessed together.

Thanks,
Vinay

On Tue, Oct 30, 2012 at 10:45 PM, David Barrett  wrote:
> On Mon, Oct 29, 2012 at 8:28 PM, Richard Hipp  wrote:
>
>> On Mon, Oct 29, 2012 at 5:58 AM, David Barrett > >wrote:
>> > So what specifically do you feel is the problem with sqlite at scale?
>> >
>> > And to be clear, I'd like to pre-empt "well it doesn't do X, which you'll
>> > probably also want to do" -- all those X's are already covered and
>> working
>> > great.
>
>
> Generally the argument in favor of client/server databases versus SQLite
>> comes down to (1) Concurrency, (2) Replication, and (3) Network access.
>> The size of the database file shouldn't really be a factor.
>
>
> Yes, thank you, I'm sorry I didn't make it clear -- concurrency,
> replication, and network access are *not* problems for me.  Specifically:
>
> 1) Our server is single-threaded (all writes are serialized), so there are
> no concurrency issues.
> 2) We have our own replication layer (which is better than MySQL and
> Postgres replication)
> 3) We provide our own network access (on top of the replication layer)
> 4) The backup API works fine
> 5) Everything is on the local disk
>
> So I agree entirely -- if you need something sqlite doesn't provide, then
> sqlite isn't the right choice.  I'm just saying sqlite provides everything
> I need, and does it incredibly well.  Similarly, I'm encouraged to hear
> that there aren't any particular size restrictions or concerns.  We're
> currently at 40GB and it's working great.
>
> But back to the original question -- can anybody point me in a direction to
> learn more about MySQL's handling of fragmentation and how it differs from
> sqlite?  This isn't an obsession, it doesn't even really affect us in light
> of our moving to SSDs.  It's just a gap in my knowledge I'm looking to
> fill.  Thanks!
>
> -david
> ___
> 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] New to SQLite Question - References

2012-11-01 Thread Ryan Kool 


Hello All,

Can you give suggestions on where to look for information on connecting to
a SQLite database from within a Java application?  I'm new to both Java &
SQLite and am wondering where is the best place to start?  I have been
programming in C++ a long time using SQL Server so I'm not a novice
programmer, just a novice Java guy & SQLite newbie, trying to learn new
stuff.

Thanks in advance,

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

Re: [sqlite] Automatically create a TreeNode?

2012-11-01 Thread Gert Van Assche 
Clemens,

not any teacher could have explained this any better!
Thanks

gert

2012/11/1 Clemens Ladisch 

> Gert Van Assche wrote:
> > 2012/10/31 Clemens Ladisch 
> >> UPDATE World
> >> SET TreeNode = CAST((SELECT COUNT(DISTINCT Continent)
> >>  FROM World w2
> >>  WHERE w2.Continent <= World.Continent) AS TEXT);
> >
> > this "w2" table you are using, what is this and where do you get it from?
>
> It's an alias for the World table.  (For some reason, I tend to omit
> the AS from table aliases; think "FROM World AS w2".)
>
> "World.Continent" refers to the record in the World table that is
> currently being updated.
>
> The query over w2 is a correlated subquery; it counts how many
> continents there are the table before the current record.  To understand
> it, assume that the UPDATE is currently handling an 'Asia' record, and
> consider the results of the following queries:
>
> SELECT Continent FROM World AS w2 WHERE w2.Continent <= 'Asia';
> SELECT DISTINCT Continent FROM World AS w2 WHERE w2.Continent <= 'Asia';
> SELECT COUNT(DISTINCT Continent) FROM World AS w2 WHERE w2.Continent <=
> 'Asia';
>
>
> Regards,
> Clemens
> ___
> 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] [Query] Sqlite

2012-11-01 Thread Brian Curley 
What's your question on SQLite?

(The sqlite-users@sqlite.org address is fairly straightforward.  It's sort
of like a forum style of help, where you ask your question via e-mail and
90-95% of the answers come from roughly 10 seemingly never-sleeping experts
from around the English-speaking world. The other 5-10% of the answers come
from dozens of other experts and heavy users, very few of which are
completely off-track.  Beyond that, it's all archived online for future
reference, so you might actually find your question was already posed and
answered in the past.)

On Thu, Nov 1, 2012 at 7:28 AM, Rajkumar  wrote:

> Sir/Madam,
>
> I am preparing to develop two applications using following technologies:
>
> 1. Adobe AIR + Android +Sqlite
> 2. QT + Embedded Linux + Sqlite
>
> Before I start my development, I would like to clarify few things on sqlite
> as I am new to this.
>
> Kindly acquaint me how getting help works with "sqlite-users@sqlite.org".
>
> --
> Regards,
> Raj
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Regards.

Brian P Curley
  home: 845 778-5937
  cell: 845 548-4377
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] System.Data.SQLite.dll locks files forever starting from v1.0.82.0

2012-11-01 Thread Alex Reitbort 
Hello,

 

I recently updated my project to use the latest version of
System.Data.SQLite (1.0.82.0). But it seems that there is a bug in this
version. After I open an sqllite database and perform any query, the
database stays locked until the process ends.

 

Here is a C# code sample to reproduce the problem:

 

 

using (var conn = new SQLiteConnection("Data Source=alex.db"))

{

conn.Open();

var cmd = conn.CreateCommand();

cmd.CommandText = "select 1;";

cmd.ExecuteScalar();

conn.Close();

}

GC.Collect(2,GCCollectionMode.Forced);

File.Delete("alex.db");

 

When running this sample with System.Data.SQLite.dll v1.0.81 everything
works ok.

When running this sample with System.Data.SQLite.dll v1.0.82, it fails on
File.Delete line with IOException: The process cannot access the file
'alex.db' because it is being used by another process.

 

Can you help me to fix this problem?

 

Alex Reitbort

Software Developer

Skyline Software Systems Inc.

www.skylineglobe.com

 

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

Re: [sqlite] MyJSQLView Version 3.40 Released

2012-11-01 Thread Arbol One 


UNIX is basically a simple operating system, but you have to be a genius to
understand the simplicity - Dennis Ritchie
Genius might have limitations, but stupidity is no handicap
Eat Kosher

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of
da...@dandymadeproductions.com
Sent: Tuesday, October 30, 2012 6:00 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] MyJSQLView Version 3.40 Released

MyJSQLView Version 3.40 Released

The MyJSQLView project is pleased to release v3.40 to the public. The
release marks a major reorganizational effort to the source code and plugin
management. Though the public will notice little with regard to the source
subpackaging the plugin management changes bring the ability to now load
plugins from remote locations. Plugin repositories can now be created by
individuals or organizations that wish to provide custom plugins on the
basis of a realtime network loading capability.
In that regard the MyJSQLView project has added to its own public repository
a free open source plugin that contains a graphical SQL query builder.
MyJSQLView works in conjuction with the Xerial SQLite JDBC to access a
SQlite database file.

Dana M. Proctor
MyJSQLView Project Manager
http://dandymadeproductions.com/projects/MyJSQLView/
http://www.xerial.org

MyJSQLView provides an free open source easy to use Java based user
interface frontend for viewing, adding, editing, or deleting entries in
several mainstream databases. A query frame allows the building of complex
SQL statements and a SQL Query Bucket for saving such. The application
allows easy sorting, searching, and import/export of table data. A plug-in
framework has allowed the inclusion of tools to profile and plot data for
analysis.

___
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] Automatically create a TreeNode?

2012-11-01 Thread Clemens Ladisch 
Gert Van Assche wrote:
> 2012/10/31 Clemens Ladisch 
>> UPDATE World
>> SET TreeNode = CAST((SELECT COUNT(DISTINCT Continent)
>>  FROM World w2
>>  WHERE w2.Continent <= World.Continent) AS TEXT);
>
> this "w2" table you are using, what is this and where do you get it from?

It's an alias for the World table.  (For some reason, I tend to omit
the AS from table aliases; think "FROM World AS w2".)

"World.Continent" refers to the record in the World table that is
currently being updated.

The query over w2 is a correlated subquery; it counts how many
continents there are the table before the current record.  To understand
it, assume that the UPDATE is currently handling an 'Asia' record, and
consider the results of the following queries:

SELECT Continent FROM World AS w2 WHERE w2.Continent <= 'Asia';
SELECT DISTINCT Continent FROM World AS w2 WHERE w2.Continent <= 'Asia';
SELECT COUNT(DISTINCT Continent) FROM World AS w2 WHERE w2.Continent <= 'Asia';


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