Re: [sqlite] Support for named foreign keys?

[Scott Robison]

Ah, I misread. Sorry for the noise.

On Sep 24, 2017 4:59 PM, "Keith Medcalf"  wrote:

>
> I don't think so.  The question is about interpreting the schema, not
> about executing anything at all.
>
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
>
> >-Original Message-
> >From: sqlite-users [mailto:sqlite-users-
> >boun...@mailinglists.sqlite.org] On Behalf Of Scott Robison
> >Sent: Sunday, 24 September, 2017 16:47
> >To: SQLite mailing list
> >Subject: Re: [sqlite] Support for named foreign keys?
> >
> >I think he's asking for FK constraint names to be reported in
> >conflict
> >messages which has been requested in the past, but not included up
> >until
> >now because of the approach taken.
> >
> >On Sep 24, 2017 4:16 PM, "Keith Medcalf"  wrote:
> >
> >>
> >> Why do you think this?
> >>
> >> The syntax diagrams are quite clear that FOREIGN KEY constraints
> >can be
> >> named.
> >>
> >> sqlite> create table parent
> >>...> (id integer primary key);
> >> sqlite> create table child
> >>...> (fk integer null,
> >>...> constraint dippydoo foreign key (fk) references
> >parent(id));
> >> sqlite>
> >>
> >>
> >> ---
> >> The fact that there's a Highway to Hell but only a Stairway to
> >Heaven says
> >> a lot about anticipated traffic volume.
> >>
> >>
> >> >-Original Message-
> >> >From: sqlite-users [mailto:sqlite-users-
> >> >boun...@mailinglists.sqlite.org] On Behalf Of Csányi Pál
> >> >Sent: Sunday, 24 September, 2017 12:06
> >> >To: SQlite User
> >> >Subject: [sqlite] Support for named foreign keys?
> >> >
> >> >Hi,
> >> >
> >> >when will SQLite support the named foreign keys?
> >> >
> >> >I am using SchemaCrawler to visualize my SQLite databases and
> >because
> >> >SQLite does not support it yet, foreign keys get automatically
> >> >generated foreign key names in the diagram.
> >> >
> >> >So it would be nice if SQLite would support it one day. Is'n it?
> >> >
> >> >--
> >> >Best, Pali
> >> >___
> >> >sqlite-users mailing list
> >> >sqlite-users@mailinglists.sqlite.org
> >> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
> >users
> >>
> >>
> >>
> >> ___
> >> sqlite-users mailing list
> >> sqlite-users@mailinglists.sqlite.org
> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
> >users
> >>
> >___
> >sqlite-users mailing list
> >sqlite-users@mailinglists.sqlite.org
> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Support for named foreign keys?

[Keith Medcalf]

I don't think so.  The question is about interpreting the schema, not about 
executing anything at all.


---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Scott Robison
>Sent: Sunday, 24 September, 2017 16:47
>To: SQLite mailing list
>Subject: Re: [sqlite] Support for named foreign keys?
>
>I think he's asking for FK constraint names to be reported in
>conflict
>messages which has been requested in the past, but not included up
>until
>now because of the approach taken.
>
>On Sep 24, 2017 4:16 PM, "Keith Medcalf"  wrote:
>
>>
>> Why do you think this?
>>
>> The syntax diagrams are quite clear that FOREIGN KEY constraints
>can be
>> named.
>>
>> sqlite> create table parent
>>...> (id integer primary key);
>> sqlite> create table child
>>...> (fk integer null,
>>...> constraint dippydoo foreign key (fk) references
>parent(id));
>> sqlite>
>>
>>
>> ---
>> The fact that there's a Highway to Hell but only a Stairway to
>Heaven says
>> a lot about anticipated traffic volume.
>>
>>
>> >-Original Message-
>> >From: sqlite-users [mailto:sqlite-users-
>> >boun...@mailinglists.sqlite.org] On Behalf Of Csányi Pál
>> >Sent: Sunday, 24 September, 2017 12:06
>> >To: SQlite User
>> >Subject: [sqlite] Support for named foreign keys?
>> >
>> >Hi,
>> >
>> >when will SQLite support the named foreign keys?
>> >
>> >I am using SchemaCrawler to visualize my SQLite databases and
>because
>> >SQLite does not support it yet, foreign keys get automatically
>> >generated foreign key names in the diagram.
>> >
>> >So it would be nice if SQLite would support it one day. Is'n it?
>> >
>> >--
>> >Best, Pali
>> >___
>> >sqlite-users mailing list
>> >sqlite-users@mailinglists.sqlite.org
>> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>users
>>
>>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>users
>>
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



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

Re: [sqlite] Support for named foreign keys?

[Scott Robison]

I think he's asking for FK constraint names to be reported in conflict
messages which has been requested in the past, but not included up until
now because of the approach taken.

On Sep 24, 2017 4:16 PM, "Keith Medcalf"  wrote:

>
> Why do you think this?
>
> The syntax diagrams are quite clear that FOREIGN KEY constraints can be
> named.
>
> sqlite> create table parent
>...> (id integer primary key);
> sqlite> create table child
>...> (fk integer null,
>...> constraint dippydoo foreign key (fk) references parent(id));
> sqlite>
>
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
>
> >-Original Message-
> >From: sqlite-users [mailto:sqlite-users-
> >boun...@mailinglists.sqlite.org] On Behalf Of Csányi Pál
> >Sent: Sunday, 24 September, 2017 12:06
> >To: SQlite User
> >Subject: [sqlite] Support for named foreign keys?
> >
> >Hi,
> >
> >when will SQLite support the named foreign keys?
> >
> >I am using SchemaCrawler to visualize my SQLite databases and because
> >SQLite does not support it yet, foreign keys get automatically
> >generated foreign key names in the diagram.
> >
> >So it would be nice if SQLite would support it one day. Is'n it?
> >
> >--
> >Best, Pali
> >___
> >sqlite-users mailing list
> >sqlite-users@mailinglists.sqlite.org
> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Support for named foreign keys?

[Keith Medcalf]

Why do you think this?

The syntax diagrams are quite clear that FOREIGN KEY constraints can be named.

sqlite> create table parent
   ...> (id integer primary key);
sqlite> create table child
   ...> (fk integer null,
   ...> constraint dippydoo foreign key (fk) references parent(id));
sqlite>


---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Csányi Pál
>Sent: Sunday, 24 September, 2017 12:06
>To: SQlite User
>Subject: [sqlite] Support for named foreign keys?
>
>Hi,
>
>when will SQLite support the named foreign keys?
>
>I am using SchemaCrawler to visualize my SQLite databases and because
>SQLite does not support it yet, foreign keys get automatically
>generated foreign key names in the diagram.
>
>So it would be nice if SQLite would support it one day. Is'n it?
>
>--
>Best, Pali
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



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

Re: [sqlite] Support for named foreign keys?

[petern]

Definitely what Clemens said:
https://www.sqlite.org/syntax/table-constraint.html

But you probably want to see all the FK's, not merely named ones.  I
suggest parsing for the 'REFERENCES' keyword of the table schemas stored in
sqlite_master (or .schema report of the shell tool).  You can fix the
SQLite "driver" of SchemaCrawler yourself, since it is open source.
SchemaCrawler claims to already use regular expressions to parse well
formed SQL schema:

https://github.com/sualeh/SchemaCrawler

SQLite FK including anonymous ones follows the syntax "REFERENCES
foreign-table".  See

https://www.sqlite.org/syntax/foreign-key-clause.html


On Sun, Sep 24, 2017 at 11:05 AM, Csányi Pál  wrote:

> Hi,
>
> when will SQLite support the named foreign keys?
>
> I am using SchemaCrawler to visualize my SQLite databases and because
> SQLite does not support it yet, foreign keys get automatically
> generated foreign key names in the diagram.
>
> So it would be nice if SQLite would support it one day. Is'n it?
>
> --
> Best, Pali
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Support for named foreign keys?

[Clemens Ladisch]

Csányi Pál wrote:
> when will SQLite support the named foreign keys?

It does support named foreign keys:

  CREATE TABLE t (
id PRIMARY KEY,
k1 CONSTRAINT name_here REFERENCES t,
k2,
CONSTRAINT another_name FOREIGN KEY(k2) REFERENCES t
  );


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

Re: [sqlite] Bash script error - sqlite3.c

[Jens Alfke]

> On Sep 23, 2017, at 8:32 PM, Gashi Lulzim  wrote:
> 
> Then I need to run ./build.sh to build libraries but I'm getting this error
> while trying to execute ./build.sh on mac. Please anyone can help what to
> do ?

This isn’t a SQLite-related issue; it’s related to building that ARToolkit 
project. Sounds like you’ve got build options or tools set up incorrectly; but 
that’s nothing we can help you with over here. You should ask for help from the 
ARToolkit community.

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

[sqlite] Support for named foreign keys?

[Csányi Pál]

Hi,

when will SQLite support the named foreign keys?

I am using SchemaCrawler to visualize my SQLite databases and because
SQLite does not support it yet, foreign keys get automatically
generated foreign key names in the diagram.

So it would be nice if SQLite would support it one day. Is'n it?

-- 
Best, Pali
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is this behavior expected?

[Keith Medcalf]

Yes.  last_insert_rowid() returns data stored in the connection.  It is 
"loaded" with a value when an insert occurs on the connection.  Even if you 
rollback the transaction, the value of the data item stored in the connection 
is not changed -- the last inserted rowid on the connection was indeed rowid 2. 
 This does not mean that the next last_insert_rowid will return "3" unless you 
happen to insert rowid 3 into the table.

In other words, if you repeat the same sequence (inside and after the 
transaction) you will still get 2 as the last inserted rowid since, in fact, 
this was the last rowid that was inserted.  last_insert_rowid only knows about 
"insert" operations.  It does not know or care about "delete" or "update" 
operations (or transactions).  It merely returns exactly what it says it does 
-- the last_insert_rowid -- and nothing more nor less.

If you repeat the entire sequence (without the create table) you will get 2 for 
the first insert and 3 for the second insert.  Lather rinse repeat 3 and 4, 4 
and 5, 5 and 6, 6 and 7 and so on and so forth.


---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Baruch Burstein
>Sent: Sunday, 24 September, 2017 05:46
>To: General Discussion of SQLite Database
>Subject: [sqlite] Is this behavior expected?
>
>SQLite version 3.20.1 2017-08-24 16:21:36
>sqlite> create table T(C);
>sqlite> insert into T values("test 1");
>sqlite> select last_insert_rowid();
>1
>sqlite> begin;
>sqlite> insert into T values("test 2");
>sqlite> select last_insert_rowid();
>2
>sqlite> rollback;
>sqlite> select last_insert_rowid();
>2
>
>In other words, the rollback doesn't roll back the rowid.
>
>--
>˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



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

Re: [sqlite] Is this behavior expected?

[Clemens Ladisch]

Baruch Burstein wrote:
> sqlite> select last_insert_rowid();
> 2
> sqlite> rollback;
> sqlite> select last_insert_rowid();
> 2
>
> In other words, the rollback doesn't roll back the rowid.

 says:
| For the purposes of this routine, an INSERT is considered
| to be successful even if it is subsequently rolled back.


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

Re: [sqlite] Is this behavior expected?

[Igor Korot]

Hi,

On Sun, Sep 24, 2017 at 7:46 AM, Baruch Burstein  wrote:
> SQLite version 3.20.1 2017-08-24 16:21:36
> sqlite> create table T(C);
> sqlite> insert into T values("test 1");
> sqlite> select last_insert_rowid();
> 1
> sqlite> begin;
> sqlite> insert into T values("test 2");
> sqlite> select last_insert_rowid();
> 2
> sqlite> rollback;
> sqlite> select last_insert_rowid();
> 2
>
> In other words, the rollback doesn't roll back the rowid.

I think it is as rowid is auto-increment.
There was a recent thread about that. Check the archive for details.

Thank you.

>
> --
> ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Is this behavior expected?

[Baruch Burstein]

SQLite version 3.20.1 2017-08-24 16:21:36
sqlite> create table T(C);
sqlite> insert into T values("test 1");
sqlite> select last_insert_rowid();
1
sqlite> begin;
sqlite> insert into T values("test 2");
sqlite> select last_insert_rowid();
2
sqlite> rollback;
sqlite> select last_insert_rowid();
2

In other words, the rollback doesn't roll back the rowid.

-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] checking if a table exists

[John McMahon]

On 23/09/2017 05:36, mikeegg1 wrote:

I’m using the statement:

select count(*) from sqlite_master where type = 'table' and name = ‘$NAME’;

This statement works fine in the sqlite3 shell. This statement does not work in 
my API.
Is there a PRAGMA I need to issue so I can check for table existence?

TIA

Mike

PERL code to check for table existence:


#> sub tableexists($$) {
prototyping depricated practice carried over from perl 4

sub tableexists {


 my $dbh = shift;
 my $name = shift;
 my $tableexists = 0;


#>  $dbh->do("pragma writable_schema = 'on';");
# neither recommended nor needed



#>  my $sql = "select count(*) from sqlite_master where type = 
'table' and name = '$name';";
my $sql = "select count(*) from sqlite_master where type = 'table' and 
name = ?;";



 my $stmt = $dbh->prepare($sql);
#>  $stmt->execute or die "$0: verifying table name failed: 
$DBI::errstr";

$stmt->execute($name)
or die "$0: verifying table name failed: $DBI::errstr";


 while(my @row = $stmt->fetchrow_array) {
 $tableexists = $row[0];
 }
 $stmt->finish;


#>  $dbh->do("pragma writable_schema = 'off';");


 return $tableexists;
}


Alternatively (not tested)

sub tableexists {
my $dbh = shift;
my $name = shift;

my $sql = "select count(*) from sqlite_master where type = 'table' 
and name = '$name';";

# stmt only executed once, $name only evaluated once

my ($tableexists) = $dbh->selectrow_array($sql);
# selectrow returns 1 row, the stmt returns 1 element  in list context

return $tableexists;
}

my $check_table = tableexists($dbh, $name);

John

--
Regards
   John McMahon
  li...@jspect.fastmail.fm


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

[sqlite] Bash script error - sqlite3.c

[Gashi Lulzim]

I'm trying to get ARToolkit5 Android Examples and run on my device from
this official repo ARToolkit5 GithubRepo
.

Then I need to run ./build.sh to build libraries but I'm getting this error
while trying to execute ./build.sh on mac. Please anyone can help what to
do ?


jni/../../lib/SRC/VideoAndroid/sqlite3.c:24124:40: error: use of undeclared
  identifier 'mmap'
  { "mmap",   (sqlite3_syscall_ptr)mmap, 0 },
   ^
jni/../../lib/SRC/VideoAndroid/sqlite3.c:24160:22: error: invalid application of
  'sizeof' to an incomplete type 'struct unix_syscall []'
for(i=0; ihttp://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users