[sqlite] Comparing same data in two tables in two separate databases

[Jim Callahan]

yes, ATTACH and then join using a SELECT statement.

After you do the ATTACH, this video shows how to do the join using the
SQLite command line interface. Notice how you can keep track of identically
named fields in the two tables.
https://www.youtube.com/watch?v=NcrZoHselPk

Jim


On Tue, Jul 21, 2015 at 9:09 PM, Igor Tandetnik  wrote:

> On 7/21/2015 8:54 PM, Hayden Livingston wrote:
>
>> I would like to compare two tables (of the same name) in two separate
>> database files.
>>
>
> Are you looking for ATTACH DATABASE (
> http://www.sqlite.org/lang_attach.html ) ?
> --
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Comparing same data in two tables in two separate databases

[Igor Tandetnik]

On 7/21/2015 8:54 PM, Hayden Livingston wrote:
> I would like to compare two tables (of the same name) in two separate
> database files.

Are you looking for ATTACH DATABASE ( 
http://www.sqlite.org/lang_attach.html ) ?
-- 
Igor Tandetnik

[sqlite] Doc page revision request

[Bernardo Sulzbach]

Just to add some to the list of silly filters and silly silliness: at
home I can access everything, my school blocks wikipedia. I am not
kidding.

[sqlite] Doc page revision request

[R.Smith]

I have to agree with the web filter being at fault.

I am ambivalent to whether or not the word is innocuous or whether it 
can be misconstrued or even if, to some people, it is truly offensive.

What bothers me more is the idea that the rest of the World all needs to 
update their documentation (and way of communicating) because some 
incompetent web-filter design can't distinguish. What if tomorrow it 
starts regarding other innocuous words? Should we set the precedent of 
allowing it to dictate to us what can and cannot be said?
Fix the web-filter.
Plus, in my language, that "org" abbreviation is very offensive. So is 
"func". I doubt that should matter to the rest of the World though.

The irony is - these filters are so English-centric, I bet I can type a 
long list of words (and their colloquial abbreviations) in my native 
language, Dutch, French or Chinese that are thoroughly offensive and no 
filter would be any wiser.

I have compassion for people wanting their young to learn from the www 
but still be sheltered from the dangerous language one might encounter 
there. You can't have it both ways though, if you will use a web filter, 
the onus is on you to put pressure on the designers of that filter to do 
it better - not to ask the rest of the World to play nice with the 
filter. (If the latter worked, we wouldn't need any filters to start with).


That said, I also agree with Simon's second part about the WITH 
documentation would benefit from addition of more cte examples without 
recursion.

Cheers,
Ryan


On 2015-07-21 05:46 PM, Paul Sanderson wrote:
> The problem seems to be with the web filter and not the abbreviation
> cnt. I would suggest that the onus should be on them to adjust their...

> On 21 July 2015 at 16:34, Jim Callahan  
> wrote:
>> I Simon's point about idiotic web filters is valid.

[sqlite] Doc page revision request

[Keith Medcalf]

> I am ambivalent to whether or not the word is innocuous or whether it
> can be misconstrued or even if, to some people, it is truly offensive.

It is not the word that is offensive (that is illogical and plainly 
impossible).  It is the thing that is offended that is the problem -- the 
gutter mind and carnal tendencies of the receiver -- I am sure there are 
psychiatric descriptions of the disorder, but it is often linked to breast 
feeding (or not breast feeding), drinking water, eating bread, or excessively 
low self-esteem brought on by early religious zealotry and brain-washing.

It is impossible for ME to offend YOU, it is YOU who caused yourself to be 
offended by the thoughts you yourself conjured into your own mind.  Stop 
whining and grow a pair or go see a therapist and get help with your overactive 
imaginations.  I also understand that electroshock treatments can be very 
effective in preventing people from causing themselves offense due to their 
overactive imaginations (aversion therapy).  

In extreme cases, frontal lobotomy is also a highly effective treatment for 
offense disorder.

[sqlite] Comparing same data in two tables in two separate databases

[Hayden Livingston]

Hello,

I would like to compare two tables (of the same name) in two separate
database files. I could conceivably change the situation such that I
have 2 tables within the same database but different names.

My table definition is:

CREATE TABLE (SchoolId INTEGER, SchoolName TEXT, EnrollmentCount INTEGER);

I essentially want to generate a new database file that will output
the difference in EnrollmentCount for each SchoolName.

It is possible that some rows will be missing in either of the
databases, and in that case I still want to print the row.

I'm a bit new to SQLite so I don't know exactly how to syntactically
compare two databases and of course, there is the SQL of the
comparison (also which I'm new to) but I think I'll be able to figure
that part out.

I hope I've explained my question is detail. Would love your help and
suggestions.

[sqlite] Doc page revision request

[Simon Slavin]

On 21 Jul 2015, at 4:46pm, Paul Sanderson  
wrote:

> The problem seems to be with the web filter and not the abbreviation
> cnt. I would suggest that the onus should be on them to adjust their
> filter to prevent filtering of an innocuous word (its only
> rude/offensive if the u is added).

Semantically, you're right.  But this is a web filter implemented by one of the 
big five ISPs here in Britain, set to its second most paranoid setting "Protect 
the children", and it filters out 'cnt' spelled just like that, replacing it 
with five asterisks.  The ISP has millions of customers and isn't going to 
change its filtering policy just because one user complained.  The student 
lives with their parents and has a younger sibling, and the father won't change 
the setting for his ISP customer profile.

I have seen the rude word spelled like that in SMS-chat.  You and I understand 
that if we block one form people will just move on to the next.  And I'm sure 
people who live in Penistone (a town in South Yorkshire) would agree with us.  
But that's not what the sort of people who use web-blockers think.

It seems an easy enough change and I thought it might be helpful to point it 
out.  No big problem if nothing is done about it, it's just one of my students 
out of many, and he can use the computers in the lab when he wants.

Simon.

[sqlite] Doc page revision request

[Paul Sanderson]

The problem seems to be with the web filter and not the abbreviation
cnt. I would suggest that the onus should be on them to adjust their
filter to prevent filtering of an innocuous word (its only
rude/offensive if the u is added).
Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence


On 21 July 2015 at 16:34, Jim Callahan  
wrote:
> I Simon's point about idiotic web filters is valid.
>
> "Cnt" is innocuous in formal documentation where by context it clearly
> means "count", but think of how people type text messages. If an online
> chat board in html had text like messages then a machine learning algorithm
> (for a web filter) would tend to associate "cnt" with sexually explicit and
> racially offensive language that would not be appropriate for an elementary
> school aged child.
>
> By middle school the student and their friends are probably experimenting
> with the language
>
> Web  filters are sometimes used in corporations, government agencies and
> public facilities, so I can see why it might be an issue, even though "cnt"
> is completely innocuous in formal documentation in a way it would not be in
> a "how many ... does it take to change light bulb" joke or in a string of
> offensive expletives.
>
> It is a matter of context. And to a crudely programmed machine learning
> algorithm it is all html text with no context.
>
> Jim
>
>
> On Tue, Jul 21, 2015 at 10:52 AM, Bernardo Sulzbach <
> mafagafogigante at gmail.com> wrote:
>
>> About using "cnt", it is by far not just this page. There are tons of
>> documentation and programming pages out there that use "cnt" instead
>> of "count".
>>
>> The last part of your message seems more valid, though.
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Doc page revision request

[J Decker]

On Tue, Jul 21, 2015 at 3:51 PM, Bernardo Sulzbach <
mafagafogigante at gmail.com> wrote:

> Just to add some to the list of silly filters and silly silliness: at
> home I can access everything, my school blocks wikipedia. I am not
> kidding.
>
and wikipedia blocks knowledge of bosnian pyramids and ... why are all the
impact craters on the moon round?  Every single strike has been
perpendicular?

> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Doc page revision request

[Jonathan Moules]

For a slightly broader brushed overview of why the web-filter is wrong (a false 
positive), see: https://en.wikipedia.org/wiki/Scunthorpe_problem

-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Bernardo 
Sulzbach
Sent: Tuesday, July 21, 2015 4:53 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Doc page revision request

> The problem seems to be with the web filter and not the abbreviation cnt.

Exactly. Let's not forget to mention that dick is a synonym for detective and 
that bitch is a female dog. "cnt" is fine in that context and the filter should 
likely be deactivated or updated.
Although it may be easier to just ask someone to replace stuff on that page, as 
I said before, "cnt" is used to mean "count" in many places, making it even 
clearer that the filter is the problem.

Good luck asking Oracle to update this:
http://docs.oracle.com/javase/7/docs/api/java/awt/List.html

On Tue, Jul 21, 2015 at 12:46 PM, Paul Sanderson  wrote:
> The problem seems to be with the web filter and not the abbreviation
> cnt. I would suggest that the onus should be on them to adjust their
> filter to prevent filtering of an innocuous word (its only
> rude/offensive if the u is added).
> Paul
> www.sandersonforensics.com
> skype: r3scue193
> twitter: @sandersonforens
> Tel +44 (0)1326 572786
> http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-To
> olkit
> -Forensic Toolkit for SQLite
> email from a work address for a fully functional demo licence
>
>
> On 21 July 2015 at 16:34, Jim Callahan  
> wrote:
>> I Simon's point about idiotic web filters is valid.
>>
>> "Cnt" is innocuous in formal documentation where by context it
>> clearly means "count", but think of how people type text messages. If
>> an online chat board in html had text like messages then a machine
>> learning algorithm (for a web filter) would tend to associate "cnt"
>> with sexually explicit and racially offensive language that would not
>> be appropriate for an elementary school aged child.
>>
>> By middle school the student and their friends are probably
>> experimenting with the language
>>
>> Web  filters are sometimes used in corporations, government agencies
>> and public facilities, so I can see why it might be an issue, even though 
>> "cnt"
>> is completely innocuous in formal documentation in a way it would not
>> be in a "how many ... does it take to change light bulb" joke or in a
>> string of offensive expletives.
>>
>> It is a matter of context. And to a crudely programmed machine
>> learning algorithm it is all html text with no context.
>>
>> Jim
>>
>>
>> On Tue, Jul 21, 2015 at 10:52 AM, Bernardo Sulzbach <
>> mafagafogigante at gmail.com> wrote:
>>
>>> About using "cnt", it is by far not just this page. There are tons
>>> of documentation and programming pages out there that use "cnt"
>>> instead of "count".
>>>
>>> The last part of your message seems more valid, though.
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users at mailinglists.sqlite.org
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



--
Bernardo Sulzbach
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


This message has been scanned for viruses by MailControl - www.mailcontrol.com



Click 
https://www.mailcontrol.com/sr/OrCU7GDpjEnGX2PQPOmvUkyXKmclf+CPM8I9WrCzLAIW8IbtuGHEGrarocFh0QsteImqX2ifBx2n9BVBzDB66w==
 to report this email as spam.



HR Wallingford and its subsidiaries uses faxes and emails for confidential and 
legally privileged business communications. They do not of themselves create 
legal commitments. Disclosure to parties other than addressees requires our 
specific consent. We are not liable for unauthorised disclosures nor reliance 
upon them.
If you have received this message in error please advise us immediately and 
destroy all copies of it.

HR Wallingford Limited
Howbery Park, Wallingford, Oxfordshire, OX10 8BA, United Kingdom
Registered in England No. 02562099

[sqlite] Doc page revision request

[Bernardo Sulzbach]

I understand that the filter is not something his father hacked with
Python in 10 minutes, but I don't think this will make anyone change
the page.
cnt is used by Oracle, Microsoft (there is even a .cnt extension if I
am not mistaken), and I'd also say that any big software company has
cnt somewhere.
And as Smith said, there are other languages out there. Even words
that are OK in Portugal Portuguese (puto, for instance) are not
family-friendly in Brazilian Portuguese.
So I guess that the lab computers will be the solution.

BTW, Google pointed me this https://en.wikipedia.org/wiki/Penistone
Damn, it exists.

[sqlite] Doc page revision request

[Simon Slavin]

Could someone take a look at



please ?  This page was inaccessible via a web filter used in one of my 
students' homes because of its use of the pseudo-word 'cnt'.  My guess is that 
this is because some online fora users use it as a form of a certain rude word 
used more commonly in British/Australian/etc. English than it is in American 
English.

Since this pseudo-word is just an arbitrary name, would it be possible to 
substitute its 9 mentions with whatever term it's an abbreviation of ?  I'm not 
certain but I think it means 'count'.  If COUNT is a reserved word in anything 
important (it's not in SQLite but perhaps the writer wants to avoid it anyway) 
some other word could be used.

Whoever wrote that page is obviously used to three letter abbreviations since 
'org' is used further down for an 'organisation' table.  That one doesn't seem 
to be a problem, or at least the filter report didn't mention it.

By the way, I think it easier to understand WITH when the explanation includes 
one or two examples of the Ordinary Common Table Expressions before it gets 
into recursion.  This allows the writer to explain the syntax and uses of WITH 
first, letting the student get used to those because they have to absorb the 
additional complications of RECURSIVE.  But that's just my opinion.

Of course, SQLite has sub-SELECTs as well, which may overlap with the use of 
WITH.

Simon.

[sqlite] Doc page revision request

[Bernardo Sulzbach]

> The problem seems to be with the web filter and not the abbreviation cnt.

Exactly. Let's not forget to mention that dick is a synonym for
detective and that bitch is a female dog. "cnt" is fine in that
context and the filter should likely be deactivated or updated.
Although it may be easier to just ask someone to replace stuff on that
page, as I said before, "cnt" is used to mean "count" in many places,
making it even clearer that the filter is the problem.

Good luck asking Oracle to update this:
http://docs.oracle.com/javase/7/docs/api/java/awt/List.html

On Tue, Jul 21, 2015 at 12:46 PM, Paul Sanderson
 wrote:
> The problem seems to be with the web filter and not the abbreviation
> cnt. I would suggest that the onus should be on them to adjust their
> filter to prevent filtering of an innocuous word (its only
> rude/offensive if the u is added).
> Paul
> www.sandersonforensics.com
> skype: r3scue193
> twitter: @sandersonforens
> Tel +44 (0)1326 572786
> http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
> -Forensic Toolkit for SQLite
> email from a work address for a fully functional demo licence
>
>
> On 21 July 2015 at 16:34, Jim Callahan  
> wrote:
>> I Simon's point about idiotic web filters is valid.
>>
>> "Cnt" is innocuous in formal documentation where by context it clearly
>> means "count", but think of how people type text messages. If an online
>> chat board in html had text like messages then a machine learning algorithm
>> (for a web filter) would tend to associate "cnt" with sexually explicit and
>> racially offensive language that would not be appropriate for an elementary
>> school aged child.
>>
>> By middle school the student and their friends are probably experimenting
>> with the language
>>
>> Web  filters are sometimes used in corporations, government agencies and
>> public facilities, so I can see why it might be an issue, even though "cnt"
>> is completely innocuous in formal documentation in a way it would not be in
>> a "how many ... does it take to change light bulb" joke or in a string of
>> offensive expletives.
>>
>> It is a matter of context. And to a crudely programmed machine learning
>> algorithm it is all html text with no context.
>>
>> Jim
>>
>>
>> On Tue, Jul 21, 2015 at 10:52 AM, Bernardo Sulzbach <
>> mafagafogigante at gmail.com> wrote:
>>
>>> About using "cnt", it is by far not just this page. There are tons of
>>> documentation and programming pages out there that use "cnt" instead
>>> of "count".
>>>
>>> The last part of your message seems more valid, though.
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users at mailinglists.sqlite.org
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



-- 
Bernardo Sulzbach

[sqlite] Doc page revision request

[Bernardo Sulzbach]

About using "cnt", it is by far not just this page. There are tons of
documentation and programming pages out there that use "cnt" instead
of "count".

The last part of your message seems more valid, though.

[sqlite] Doc page revision request

[Jim Callahan]

I Simon's point about idiotic web filters is valid.

"Cnt" is innocuous in formal documentation where by context it clearly
means "count", but think of how people type text messages. If an online
chat board in html had text like messages then a machine learning algorithm
(for a web filter) would tend to associate "cnt" with sexually explicit and
racially offensive language that would not be appropriate for an elementary
school aged child.

By middle school the student and their friends are probably experimenting
with the language

Web  filters are sometimes used in corporations, government agencies and
public facilities, so I can see why it might be an issue, even though "cnt"
is completely innocuous in formal documentation in a way it would not be in
a "how many ... does it take to change light bulb" joke or in a string of
offensive expletives.

It is a matter of context. And to a crudely programmed machine learning
algorithm it is all html text with no context.

Jim


On Tue, Jul 21, 2015 at 10:52 AM, Bernardo Sulzbach <
mafagafogigante at gmail.com> wrote:

> About using "cnt", it is by far not just this page. There are tons of
> documentation and programming pages out there that use "cnt" instead
> of "count".
>
> The last part of your message seems more valid, though.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] create temporary virtual table

[Hick Gunter]

Caveat: SQLite may call the xDisconnect method at other times too. I expect 
this will happen if the schema changes while a statement is prepared.

-Urspr?ngliche Nachricht-
Von: Peter Aronson [mailto:pbaronson at att.net]
Gesendet: Dienstag, 21. Juli 2015 01:20
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] create temporary virtual table

Most types of virtual table can be created as temporary tables with no trouble 
by specifying the temp database when you create them.  IE:
CREATE VIRTUAL TABLE temp.myrtree USING RTREE (id,minx,maxx); However, the 
virtual table method xDestroy does not get called for a virtual table in the 
temp database unless an explicit DROP TABLE statement is executed on it; simply 
exiting and allowing SQLite to clean up the temp database won't do it.  
However, for many Virtual Tables, the xDestroy method and the xDisconnect 
methods are the same, and SQLite will call the xDisconnect method on a virtual 
table in the temp database before exiting.  So, unless the virtual table has to 
clean up additional metadata or if it deletes or releases some external 
resources, it can probably be safely created in the temp database and cleaned 
up by SQLite on exit.  Of course, if you explicitly DROP it when you are done 
with it, the xDestroy methods will be called and all should be as normal.
Peter


 On Monday, July 20, 2015 3:34 PM, Andy Rahn  wrote:



 I see there is no way to create a temporary virtual table.

One idea I had was to create a second, in-memory db and attach that, then 
create the virtual table over there.

Currently I have it working where as soon as I open the db, I use sqlite_master 
to find any left over virtual tables from before and drop them.

Any other ideas?

 - Andy
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users




___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


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

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.

[sqlite] create temporary virtual table

[Eduardo Morras]

On Mon, 20 Jul 2015 17:34:06 -0500
Andy Rahn  wrote:

> I see there is no way to create a temporary virtual table.

It depends on how the virtual table is implemented. Your virtual table code 
xCreate function can create temporal tables, sqlite3 drop them on 
sqlite3_close() call. Or do you ask about a virtual table extension in 
particular, like fts3 or rtree?

> One idea I had was to create a second, in-memory db and attach that,
> then create the virtual table over there.
>
> Currently I have it working where as soon as I open the db, I use
> sqlite_master to find any left over virtual tables from before and
> drop them.
>
> Any other ideas?

Modify the code of your virtual table or ask sqlite3 developers if it's one of 
the virtual tables provided with sqlite3.

>  - Andy

---   ---
Eduardo Morras 

[sqlite] create temporary virtual table

[Peter Aronson]

Most types of virtual table can be created as temporary tables with no trouble 
by specifying the temp database when you create them. ?IE:
CREATE VIRTUAL TABLE temp.myrtree USING RTREE (id,minx,maxx);
However, the virtual table method xDestroy does not get called for a virtual 
table in the temp database unless an explicit DROP TABLE statement is executed 
on it; simply exiting and allowing SQLite to clean up the temp database won't 
do it. ?However, for many Virtual Tables, the xDestroy method and the 
xDisconnect methods are the same, and SQLite will call the xDisconnect method 
on a virtual table in the temp database before exiting. ?So, unless the virtual 
table has to clean up additional metadata or if it deletes or releases some 
external resources, it can probably be safely created in the temp database and 
cleaned up by SQLite on exit. ?Of course, if you explicitly DROP it when you 
are done with it, the xDestroy methods will be called and all should be as 
normal.
Peter 


 On Monday, July 20, 2015 3:34 PM, Andy Rahn  wrote:



 I see there is no way to create a temporary virtual table.

One idea I had was to create a second, in-memory db and attach that, then
create the virtual table over there.

Currently I have it working where as soon as I open the db, I use
sqlite_master to find any left over virtual tables from before and drop
them.

Any other ideas?

 - Andy
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users