[sqlite] Tcl API docs - clarification

[John Gillespie]

Just spent a while trying to work out how to access sqlite in read-only
mode from Tcl.
There seems to be nothing in the API doc :
http://www.sqlite.org/tclsqlite.html

I eventually got the information from an error message:

wrong # args: should be "sqlite3 HANDLE FILENAME ?-vfs VFSNAME? ?-readonly
BOOLEAN? ?-create BOOLEAN? ?-nomutex BOOLEAN? ?-fullmutex BOOLEAN?"

Would it be possible for you to add these options for the 'sqlite3' command
to the Tcl API document.

Thanks

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

Re: [sqlite] How do you load a ".csv" and skip the first line?

[John Gillespie]

I use "SQLite Manager " extension for Firefox.
Don't know if it is available for IE,  didn't find it for Safari.

John G


On 4 September 2012 21:23, Peter Haworth  wrote:

> Take a look at my SQLiteAdmin tool.  It will import csv files with or
> without headers, export them that way too, plus many features to help
> create and maintain your schema and browse/edit your data.  Avaialble at
> www.lcsql.com.
>
> Pete
> lcSQL Software 
>
>
>
> On Sun, Sep 2, 2012 at 9:00 AM,  wrote:
>
> > Message: 3
> > Date: Sat, 01 Sep 2012 12:19:09 -0700
> > From: "joe.fis...@tanguaylab.com" 
> > To: sqlite-users@sqlite.org
> > Subject: [sqlite] How do you load a ".csv" and skip the first line?
> > Message-ID: <50425fad.4020...@tanguaylab.com>
> > Content-Type: text/plain; charset=ISO-8859-1; format=flowed
> >
> > Is there some way to load a ".csv" file into a SQLite database table
> > when the first row (record) of the file contains headers?
> >
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Provenance

[John Gillespie]

If you want instant info on the 'provenance' of a website use the Firefox
add-on "Flagfox". It gives you a little flag next to the address.

John Gillespie

On 6 July 2012 22:58, Gavin T Watt <gw...@raytheon.com> wrote:

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA256
>
> Thanks for the information.
>
> Not a Jingoistic quest, but we're using SQLite in a system for which there
> is an interest in the provenance of SQLite for security reasons. With the
> server ins Dalls, we golden!
>
>
> Gavin Watt, CISSP
> Sr. Prin. Sys. Engr.
> Information Assurance
> Network Centric Systems (NCS)
>
> 714-446-3104 (office)
> 714-234-8869 (BB)
> 714-446-4136 (fax)
> gw...@raytheon.com
>
> 120 S. St. College Blvd..
> Mail Station: FU/679/H118
> Brea, Ca.  92821, USA
>
>
>
> From: Roger Binns <rog...@rogerbinns.com>
> To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
> Date: 07/06/2012 11:01 AM
> Subject: Re: [sqlite] SQLite Provenance
> Sent by: sqlite-users-boun...@sqlite.org
>
>
>
> * PGP Signed by an unknown key
>
> On 06/07/12 08:29, Gavin T Watt wrote:
> > Can anyone tell me where (what country) the SQLite server(s) are
> > located?
>
> Go to http://centralops.net and enter the site of interest into domain
> dossier.
>
> You will see that the sqlite.org domain name is openly registered (not
> hidden by a privacy/anonymity registrar) and that the IP address it is
> hosted on belongs to Linode who obtained it as a block from ThePlanet
> internet services.
>
> If you tick traceroute at the top then you can deduce from the traceroute
> (at the bottom) that particular IP address is in Dallas.
>
> If this is some sort of jingoistic quest then all companies involved are
> American.
>
> Roger
>
> * Unknown Key
> * 0x0DFBD904(L)
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
> -BEGIN PGP SIGNATURE-
> Version: PGP Desktop 10.2.0 (Build 2068)
> Charset: utf-8
>
> wsBVAwUBT/dflI8W3Wkt3UsBAQimkQf+M4Fylk+jIEdYmU9qoz8pexgmeMFVgDBo
> E22D6T91QrzfBp+8zTtuMCIVYe31Sv+H2E3rdfWuP+xLM82OqldSylv/eaG0uRXl
> fRCISD63mvnJpBY63LS9r2tWbw5+1JCsDtLVzcwOTLrRoreBvXT5RXbZKe5g+j84
> JZVtZZrjAMyiaw2XC3uyoq8I71HC5wCyYjYuWd+QwaqLGwndC9Nvtua1opXHzTC5
> hviMgkhfbq2+q7ZUu1IiBCDPk255aG8tx83sxn2RiKyI6voBMFVJE1E1sv1vvykE
> uL7wj4ybw2UDKkgcsBFmAaQDb228tGghbSXqB28npuVpTtugLPRkFg==
> =G2iZ
> -END PGP SIGNATURE-
> ___
> 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 1.0.81.0 - .edmx creation issue

[John Gillespie]

This works as expected for me.  What version are you using? Did you set the
pragma each time?
 What do you mean by ".edmx"

John G


% sqlite3 test.db
SQLite version 3.7.7 2011-06-25 16:35:41

pragma foreign_keys=1;

CREATE TABLE Lesson (
   id INTEGER PRIMARY KEY AUTOINCREMENT,
   title TEXT NOT NULL);

CREATE TABLE Page (
   lesson_id INTEGER NOT NULL,
   id INTEGER PRIMARY KEY AUTOINCREMENT,
   title TEXT NOT NULL,
  FOREIGN KEY(lesson_id) REFERENCES Lesson(id));

insert into Lesson (title) values ('Abc');
insert into Lesson (title) values ('Def');
insert into Page (lesson_id, title)  values (1,'xxx');
insert into page (lesson_id, title)  values (2,'yyy'); <<<
lower case works fine
insert into Page (lesson_id, title)  values (3,'zzz');
Error: foreign key constraint failed <
error as expected


CREATE TABLE Lesson2 (
   id INTEGER PRIMARY KEY AUTOINCREMENT,
   title TEXT NOT NULL);

CREATE TABLE Page2 (
lesson_id INTEGER NOT NULL,
id INTEGER PRIMARY KEY AUTOINCREMENT,
title TEXT NOT NULL,
   FOREIGN KEY(lesson_id) REFERENCES lesson2(id));

insert into Lesson2 (title) values ('Abc');
insert into lesson2 (title) values ('Def');  <<< lower case
works fine
insert into Page2 (lesson_id, title)  values (1,'xxx');
insert into page2 (lesson_id, title)  values (2,'yyy');
insert into Page2 (lesson_id, title)  values (3,'zzz');
Error: foreign key constraint failed< error
as expected so the releationship must be there


On 8 June 2012 18:55, Ludovic VP  wrote:

>
> Hello,
> I was having a hard time understanding why a one-to-many relationship
> wasn't included in the .edmx of an Sqlite database, until I figured the
> issue is with the letter case of the referenced table in the foreign key
> definition. The following works ok:
> CREATE TABLE Lesson (id INTEGER PRIMARY KEY AUTOINCREMENT,title
> TEXT NOT NULL);
> CREATE TABLE Page (lesson_id INTEGER NOT NULL,id INTEGER PRIMARY
> KEY AUTOINCREMENT,title TEXT NOT NULL,FOREIGN KEY(lesson_id)
> REFERENCES Lesson(id));
> while with the following, the relationship is not added:
> CREATE TABLE Lesson (id INTEGER PRIMARY KEY AUTOINCREMENT,title
> TEXT NOT NULL);
> CREATE TABLE Page (lesson_id INTEGER NOT NULL,id INTEGER PRIMARY
> KEY AUTOINCREMENT,title TEXT NOT NULL,FOREIGN KEY(lesson_id)
> REFERENCES lesson(id));
> I'm guessing it's not a huge deal, but I thought I'd put it out there
> since it may be simple to fix.
> Sincerely,
> Ludovic Vaugeois-Pepin
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Tcl Extension

[John Gillespie]

No: here is info from my mac -  Mac OSX (run in terminal) :

~ 596 % sqlite3 test.db
SQLite version 3.7.7 2011-06-25 16:35:41
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .e

~ 597 % wish
% set tcl_version
8.5
% package require sqlite3
3.7.5
% exit

As you can see the shell is using 3.7.7  and the Tcl extn uses 3.7.5. But I
see no problems so far.

Advice on updating both would be appreciated.

JG

On 9 May 2012 01:45, Tilsley, Jerry M.  wrote:

> Does the version number for the TCL Library for SQLite match the version
> of the core SQLite product?
>
> Thanks,
>
> Jerry
>
> _
> ___
> 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] Primary Key uniqueness

[John Gillespie]

Try omitting 'rowid' - you are not grouping by it and if you did it is
unique so no dups.

select  Column_1,Column_27,Column_47 , count(*) from old_table
   group by Column_1,Column_27,Column_47
  having count(*) > 1

JG

On 29 March 2012 17:27, Joe Bennett  wrote:

> Hi,
>
> I have three sqlite3 datbases that currently have no primary key (actually
> done with two and stuck on the third). I am converting them to 'new' tables
> with a primary key like so:
>
> create table if not exists new_table
>  (Column_1,Column_2,Column_3..,Column_47, primary key(Column_1,
> Column_27, Column_47))
>
> Now, when I insert the data from old_table into new_table, I get the error
> message 'columns Column_1,Column_27,Column_47 are not unique' using this
> syntax:
>
> insert into new_table (Column_1,Column_2,Column_3..,Column_47) select *
> from old_table
>
> That's OK, shame on me for not checking that prior to the insert... Now, I
> am using this syntax to find the dupes and remove them:
>
> select rowid, Column_1,Column_27,Column_47,count(*)
> from old_table
> group by Column_1,Column_27,Column_47
> having count(*) > 1
>
>
> No rows meet this criteria... So, I'm looking for a better way to find the
> non unique data in the old_table so I can clear this error and insert it
> into the new table... Any ideas?
>
>
>
>
> -Joe
> ___
> 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] table names on the fly

[John Gillespie]

Alternatively in tcl :
dbcmd eval { create table mytable ( aaa integer,   text) }
dbcmd eval { insert into mytable (aaa,bbb) values (1, '')  }

dbcmd eval  "select * from mytable"  loopvar  {
 # loopvar(*)  contains the column names,   loopvar(aaa) contains 1,
loopvar(bbb) contains ""
}

On 11 January 2012 20:57, Igor Tandetnik  wrote:

> On 1/11/2012 3:53 PM, inq1ltd wrote:
>
>> Can someone tell me how to get the column names
>> contained in a table on the fly.
>>
>
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Procedure (Conditional statement) workaround

[John Gillespie]

This would make a good entry for an Obfuscated SQL contest.
Well done

John


On 19 December 2011 21:43, Nico Williams  wrote:

> You can do conditionals via WHERE clauses, as others have pointed out.
>  You can also use WHEN clauses on triggers.
>
> Think of it as  IF .  And remember that the
> WHERE clause can refer to all sorts of things, including parameters
> from the application (something like WHERE @foo = 1).
>
> You can do iteration via INSERT/UPDATE/DELETE if you have tables with
> rows to iterate over.
>
> You can make sub-routines (that return no values but have
> side-effects) out of INSERTs on VIEWs or TABLEs with triggers that
> will use the new row(s) as arguments, and but leave the VIEW/TABLE
> itself unaltered.  In the case of a TABLE you'd use a BEFORE INSERT
> trigger that does a SELECT RAISE(IGNORE) at the end to make sure the
> TABLE is left unaltered.
>
> You can use recursive triggers to implement recursion.
>
> This is really an abuse of SQL, of course, but it can be done :)  And,
> of course, you have SQLite3's various limits to keep in mind (e.g., on
> recursion depth).
>
> I've done this sort of thing as a proof of concept, for the fun of it.
>
> Factorial() might be implemented as follows:
>
> PRAGMA recursive_triggers = 1;
> CREATE TABLE factorials (n INTEGER PRIMARY KEY, n_factorial INTEGER);
> CREATE VIEW factorial AS SELECT n, n_factorial FROM factorials;
> CREATE TRIGGER factorial_ins INSTEAD OF INSERT ON factorial BEGIN
>SELECT RAISE(FAIL, "n must be non-negative") WHERE NEW.n < 0;
>SELECT RAISE(IGNORE) WHERE EXISTS (SELECT f.n FROM factorials f
> WHERE f.n = NEW.n);
>INSERT INTO factorials SELECT NEW.n, 1 WHERE NEW.n < 2 AND NOT
> EXISTS (SELECT f.n FROM factorials f WHERE f.n = NEW.n);
>INSERT INTO factorial SELECT NEW.n - 1, NULL WHERE NEW.n > 0 AND
> NOT EXISTS (SELECT f.n FROM factorials f WHERE f.n = NEW.n - 1);
>INSERT INTO factorials SELECT NEW.n, NEW.n * (SELECT f.n_factorial
> FROM factorials f WHERE f.n = NEW.n - 1) WHERE NEW.n > 0 AND EXISTS
> (SELECT f.n FROM factorials f WHERE f.n = NEW.n - 1) AND NOT EXISTS
> (SELECT f.n FROM factorials f WHERE f.n = NEW.n);
> END;
>
> sqlite> select * from factorial;
> sqlite> insert into factorial select 5, null;
> sqlite> select * from factorial;
> 0|1
> 1|1
> 2|2
> 3|6
> 4|24
> 5|120
> sqlite>
>
> (With 64-bit signed integers you can compute up to 20!, but no further.)
>
> Here we have a trigger acting as a sub-routine that doesn't return any
> results, but it does ensure that the results are available for a
> subsequent query.  Another way to think of it is as a method of
> building recursive queries in the absence of recursive query support
> in SQLite3.
>
> So there you have it: procedural programming in SQL.  My advice:
> follow the advice that you've been given, which is to say: do your
> procedural programming in C or whatever host language you're using,
> and use SQL as it was intended to be used.
>
> Cheers,
>
> Nico
> --
> ___
> 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] Tcl syntax help

[John Gillespie]

Try
set roads {"Miller lane" "Pine street" "Wilson blvd"}
set SQL "insert into myTable VALUES( '[join $road "','"]' )"
db eval $SQL
# note the single quotes on each side of the join command
# and the  argument to the join command.

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