Re: [sqlite] An interchangeable autoincremental field
Well, it's not so unsuitable. For luck, a simple replacement could solve the problem. I asked only because I thought there was a pattern. If not, let's go to the replacements! Em 15/11/2011 10:15, Simon Slavin escreveu: On 15 Nov 2011, at 11:27am, Luciano de Souza wrote: With the clause "integer primary key", we can create an autoincrementable field in Sqlite. Other databases uses auto_increment, serial and something else. If I am not wrong, Sqlite uses also auto_int. But, if I would build a database readable by other databases, what is the best way to create an autoincrementable field? Unfortunately for you, there is no part of the SQL specification which defines how to define a column as an autoincrementable field. Each company is free to do it however it wishes: SQLite spells a word 'AUTOINCREMENT' and MySQL spells it 'AUTO_INCREMENT', one standard uses the text 'GENERATED BY DEFAULT AS IDENTITY' but almost no engines support that exact phrase. So unfortunately this is one of the parts of SQL which you have to do differently depending on which SQL engine you use. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Luciano de Souza ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] An interchangeable autoincremental field
Hi listers, With the clause "integer primary key", we can create an autoincrementable field in Sqlite. Other databases uses auto_increment, serial and something else. If I am not wrong, Sqlite uses also auto_int. But, if I would build a database readable by other databases, what is the best way to create an autoincrementable field? Luciano ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Storing a wav file in the database
Hi listers, Suppose I have a file called "audio.wav" and I want to store it inside a database. If I correctly understood, I could create a table like that: create table test ( id integer primary key, name text not null, file blob not null ); I have never dealt with blob fields, so perhaps I need to read something before. But I don't know where? It is not only a doubt about Sqlite, but mainly about blob files. Is it simple to embed "audio.wav" in the database? After doing it, I can delete the original file, knowing that inside the database there is not only the reference to file, but the own file? And how to save it again in a file? What I imagine is that we need a handle for the file, we call a Sqlite function with this handle and get a pointer wich we save in the blob field. Likely there is a function to do the opposit operation. Everything I find in Google presumes I have alredy understood ow blob fields works. Best regards, Luciano ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Foreign key support in Sqlite
Oh! Certainly, I didn't see it. I am using Outlook and probably I forgot to update the messages before answering. Well, the important is everything works. What was lacking in my script test.sql was: pragma foreign_keys = on; After this, I am happy! Thank you very much! - Original Message - From: "Roger Andersson" <r...@telia.com> To: "'General Discussion of SQLite Database'" <sqlite-users@sqlite.org> Sent: Sunday, January 03, 2010 4:10 PM Subject: Re: [sqlite] Foreign key support in Sqlite > -Ursprungligt meddelande- > Från: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] För Luciano de Souza > Skickat: den 3 januari 2010 19:05 > Till: General Discussion of SQLite Database > Ämne: Re: [sqlite] Foreign key support in Sqlite > > I can't comprehend! I downloaded the two packs in c:\test. > Three files were > unpacked: sqlite3.exe, sqlite3.dll and sqlite3.def. > Maybe you need to check what Igor Tandetnik did say? http://www.sqlite.org/foreignkeys.html#fk_enable http://www.sqlite.org/pragma.html#pragma_foreign_keys /Roger ___ 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] Foreign key support in Sqlite
Oh! Sorry! I didn't read Igor's message. I will read the article and
certainly I will get the result! Sorry again!
Jean and Igor, thank you!
- Original Message -
From: "Luciano de Souza" <luchya...@predialnet.com.br>
To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
Sent: Sunday, January 03, 2010 4:04 PM
Subject: Re: [sqlite] Foreign key support in Sqlite
>I can't comprehend! I downloaded the two packs in c:\test. Three files were
> unpacked: sqlite3.exe, sqlite3.dll and sqlite3.def.
>
> I created the database:
>
> c:> sqlite3 test.db
>
> I create the structure:
>
> sqlite> .read test.sql
>
> The test.sql contains the statements mentioned before.
>
> I tried to insert a further illegal record:
>
> sqlite> insert into people(name, cities_id) values('Pedro', 14);
>
> The record is added without problems!
>
> I tried:
>
> select sqlite_version();
>
> The answer is:
>
> 3.6.21
>
> I had placed the three files in the current folder, but to make sure old
> versions in the environment paths don't be enabled by accident, I removed
> another version in c:\windows. I don't have reasons to believe the version
> I
> downloaded is currently working. In the folder, I have:
>
> test.sql
>
> test.db
>
> sqlite3.dll
>
> sqlite3.exe
>
> sqlite3.def
>
> Unfortunately, the answer didn't change and the record follows being added
> contrarily the foreign key constraint.
>
>
>
> - Original Message -
> From: "Jean-Christophe Deschamps" <j...@q-e-d.org>
> To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
> Sent: Sunday, January 03, 2010 2:51 PM
> Subject: Re: [sqlite] Foreign key support in Sqlite
>
>
>>
>>>create table cities
>>>(
>>>id integer primary key not null,
>>>name text not null
>>>);
>>>
>>>create table people
>>>(
>>>id integer primary key not null,
>>>name text not null,
>>>cities_id integer not null,
>>>foreign key(cities_id) references cities(id)
>>>);
>>>
>>>insert into cities(name) values('Campos');
>>>insert into cities(name) values('Araraquara');
>>>insert into cities(name) values('Porto');
>>>insert into cities(name) values('Curitiba');
>>>insert into people(name, cities_id) values('John', 3);
>>>insert into people(name, cities_id) values('Mary', 2);
>>>
>>>Regarding cities don't have the Id = 8, this statement should fail:
>>>insert into people(name, cities_id) values('Pedro', 8);
>>
>> This last insert fails here (3.6.21) with constraint violation.
>>
>> Can you check which version you're actually running:
>>
>> select sqlite_version();
>> 3.6.21
>>
>>
>> ___
>> 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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Foreign key support in Sqlite
I can't comprehend! I downloaded the two packs in c:\test. Three files were
unpacked: sqlite3.exe, sqlite3.dll and sqlite3.def.
I created the database:
c:> sqlite3 test.db
I create the structure:
sqlite> .read test.sql
The test.sql contains the statements mentioned before.
I tried to insert a further illegal record:
sqlite> insert into people(name, cities_id) values('Pedro', 14);
The record is added without problems!
I tried:
select sqlite_version();
The answer is:
3.6.21
I had placed the three files in the current folder, but to make sure old
versions in the environment paths don't be enabled by accident, I removed
another version in c:\windows. I don't have reasons to believe the version I
downloaded is currently working. In the folder, I have:
test.sql
test.db
sqlite3.dll
sqlite3.exe
sqlite3.def
Unfortunately, the answer didn't change and the record follows being added
contrarily the foreign key constraint.
- Original Message -
From: "Jean-Christophe Deschamps"
To: "General Discussion of SQLite Database"
Sent: Sunday, January 03, 2010 2:51 PM
Subject: Re: [sqlite] Foreign key support in Sqlite
>
>>create table cities
>>(
>>id integer primary key not null,
>>name text not null
>>);
>>
>>create table people
>>(
>>id integer primary key not null,
>>name text not null,
>>cities_id integer not null,
>>foreign key(cities_id) references cities(id)
>>);
>>
>>insert into cities(name) values('Campos');
>>insert into cities(name) values('Araraquara');
>>insert into cities(name) values('Porto');
>>insert into cities(name) values('Curitiba');
>>insert into people(name, cities_id) values('John', 3);
>>insert into people(name, cities_id) values('Mary', 2);
>>
>>Regarding cities don't have the Id = 8, this statement should fail:
>>insert into people(name, cities_id) values('Pedro', 8);
>
> This last insert fails here (3.6.21) with constraint violation.
>
> Can you check which version you're actually running:
>
> select sqlite_version();
> 3.6.21
>
>
> ___
> 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] Foreign key support in Sqlite
Everything downloaded correctly, but there is still something wrong. Perhaps
my very short experience in SQL resulted in any statement mistake.
Let me show again how the tables are created and the data inserted:
create table cities
(
id integer primary key not null,
name text not null
);
create table people
(
id integer primary key not null,
name text not null,
cities_id integer not null,
foreign key(cities_id) references cities(id)
);
insert into cities(name) values('Campos');
insert into cities(name) values('Araraquara');
insert into cities(name) values('Porto');
insert into cities(name) values('Curitiba');
insert into people(name, cities_id) values('John', 3);
insert into people(name, cities_id) values('Mary', 2);
Regarding cities don't have the Id = 8, this statement should fail:
insert into people(name, cities_id) values('Pedro', 8);
Unexplainably, the record is added despite the constraint.
What am I doing wrong?
- Original Message -
From: "Jean-Christophe Deschamps"
To: "General Discussion of SQLite Database"
Sent: Sunday, January 03, 2010 2:09 PM
Subject: Re: [sqlite] Foreign key support in Sqlite
> Hi,
>
>>I am trying to migrate to Sqlite3.6.21. I visited the Sqlite site and
>>downloaded the compiled file. I'm surprised in verifying that the zip
>>only had the executable. I hoped to find also the DLL.
>
> I bet you downloaded this: http://www.sqlite.org/sqlite-3_6_21.zip
>
> This is the CLI, he command-line interface.
>
>
> You need to donload this as well:
> http://www.sqlite.org/sqlitedll-3_6_21.zip
>
> This is the windows 32 dll file.
>
>
> Happy New SQLite
>
> ___
> 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] Foreign key support in Sqlite
Hello listers,
I am trying to migrate to Sqlite3.6.21. I visited the Sqlite site and
downloaded the compiled file. I'm surprised in verifying that the zip only had
the executable. I hoped to find also the DLL.
Well, since I could not find the DLL file concerning this version, I presumed
DLL does not change and for this reason, an update is not necessary for it.
In order to do my first test, I created the following tables and inserted some
values.
create table cities
(
id integer primary key not null,
name text not null
);
create table people
(
id integer primary key not null,
name text not null,
cities_id integer not null,
foreign key(cities_id) references people(id)
);
insert into cities(name) values('Campos');
insert into cities(name) values('Araraquara');
insert into cities(name) values('Recife');
insert into cities(name) values('Curitiba');
insert into people(name, cities_id) values('João', 3);
insert into people(name, cities_id) values('Maria', 2);
The objective is to test the new support for foreign keys. I hoped that this
command failed:
insert into people(name, cities_id) values('John', 8);
Unfortunately, the value was added successfully.
Is there something wrong? I need to do something to enable the foreign key
support?
This is my first message to the list. It's a pleasure for me to join the Sqlite
community.
Luciano de Souza
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
