Re: [sqlite] TEXT PRIMARY KEY

[Christoph P.U. Kukulies]

Am 12.03.2012 12:40, schrieb Kit:

2012/3/12, Christoph P.U. Kukulies:

INSERT OR IGNORE INTO resource ...
INSERT OR REPLACE INTO instance ...

Thanks a lot. What is the idea behind the INSERT OR REPLACE in your
solution?
Christoph

If you edit a test for some version of your software, md5sum is
changed too. You must replace row in `instance`.

Thanks. I have the structure now in a shape I think it would be useful:

CREATE TABLE instance (
   mandant TEXT ,
   md5sum TEXT,
   basename TEXT,
   prob_ref TEXT,
   suite TEXT ,
   tag TEXT ,
   size INT,
   creation_date TEXT,
  last_write_time TEXT,
 PRIMARY KEY (mandant,basename,prob_ref,suite,tag)
 );

CREATE TABLE resource ( md5sum TEXT PRIMARY KEY, data BLOB);

The FS tree contained about a million files and a corresponding 
tar-archive comprises about 70 GB.

The database with gzipped BLOBS now is 1.7GB in size.

Each files' location is made of \mandant\prob_ref\tag\suite\basename.

My only question now is:

when I collect the files and INSERT or IGNORE the resource, I have to 
read in the file and build the BLOB,
independently on whether the INSERT really has to take place. A quick 
beforehand decision whether
an INSERT has to be made, could possibly speed up  the file collection 
process.


I was using a try clause on the INSERT and if that wouldn't fail I was 
doing an UPDATE on just the BLOB.


Other ideas?

--
Christoph



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

Re: [sqlite] TEXT PRIMARY KEY

[Kit]

2012/3/12 Christoph P.U. Kukulies :
> Wouldn't the second and the third run result in being the records just
> replaced (since they have the
> same data in all columns and I don't have any uniqueness defined).
>
> But instead I have three identical entries from each run.
> Christoph

I recommend to add an attribute `version`. Version of main
application, not test. Add primary key or unique.

CREATE TABLE instance  (
         path TEXT,
         basename TEXT,
         version TEXT,
         size INT,
         md5sum TEXT,
         creation_date TEXT,
         last_write_time TEXT,
         FOREIGN KEY (md5sum) REFERENCES resource (md5sum),
         PRIMARY KEY (path,basename,version)
         );

You may try
s/PRIMARY KEY/UNIQUE/
-- 
Kit
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] TEXT PRIMARY KEY

[Christoph P.U. Kukulies]

Here's again the schema:

CREATE TABLE instance  (
  path TEXT,
  basename TEXT,
  size INT,
  md5sum TEXT,
  creation_date TEXT,
  last_write_time TEXT,
  FOREIGN KEY (md5sum) REFERENCES resource (md5sum)
  );

CREATE TABLE resource (
  md5sum TEXT,
  data BLOB,
  primary key(md5sum)
);

INSERT OR IGNORE INTO resource ...
INSERT OR REPLACE INTO instance ...

Thanks a lot. What is the idea behind the INSERT OR REPLACE in your
solution?
Christoph

If you edit a test for some version of your software, md5sum is
changed too. You must replace row in `instance`.
It just happened that I ran over the directory tree a second and a third 
time.


Wouldn't the second and the third run result in being the records just 
replaced (since they have the

same data in all columns and I don't have any uniqueness defined).

But instead I have three identical entries from each run.

--
Christoph

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

Re: [sqlite] TEXT PRIMARY KEY

[Kit]

2012/3/12, Christoph P.U. Kukulies :
>> INSERT OR IGNORE INTO resource ...
>> INSERT OR REPLACE INTO instance ...
>
> Thanks a lot. What is the idea behind the INSERT OR REPLACE in your
> solution?
> Christoph

If you edit a test for some version of your software, md5sum is
changed too. You must replace row in `instance`.
-- 
Kit
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] TEXT PRIMARY KEY

[Christoph P.U. Kukulies]

Am 10.03.2012 09:06, schrieb Kit:

2012/3/9 Christoph P.U. Kukulies:
CREATE TABLE instance  (
  path TEXT,
  basename TEXT,
  size INT,
  md5sum TEXT,
  creation_date TEXT,
  last_write_time TEXT,
  FOREIGN KEY (md5sum) REFERENCES resource (md5sum)
  );

CREATE TABLE resource (
  md5sum TEXT,
  data BLOB,
  primary key(md5sum)
);


What makes the contents of two files equal (so that their contents can be
represented by the same resource) ?

md5sum = md5sum


My problem: what do I have to change in TABLE instance so that I can use it
to determine whether the key is already
in the resource TABLE?

- Make new md5sum from new data
INSERT OR IGNORE INTO resource ...
INSERT OR REPLACE INTO instance ...


Thanks a lot. What is the idea behind the INSERT OR REPLACE in your 
solution?


--
Christoph

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

Re: [sqlite] TEXT PRIMARY KEY

[Kit]

2012/3/9 Christoph P.U. Kukulies :
CREATE TABLE instance  (
         path TEXT,
         basename TEXT,
         size INT,
         md5sum TEXT,
         creation_date TEXT,
         last_write_time TEXT,
 FOREIGN KEY (md5sum) REFERENCES resource (md5sum)
         );

CREATE TABLE resource (
         md5sum TEXT,
         data BLOB,
         primary key(md5sum)
       );

> What makes the contents of two files equal (so that their contents can be
> represented by the same resource) ?

md5sum = md5sum

> My problem: what do I have to change in TABLE instance so that I can use it
> to determine whether the key is already
> in the resource TABLE?

- Make new md5sum from new data
INSERT OR IGNORE INTO resource ...
INSERT OR REPLACE INTO instance ...

> Would that be a FOREIGN KEY? And how would I do that in syntax?
> If I need FOREIGN KEY, would I have to enable that in SQLite somehow (at
> compile time)?
> Thanks,
> Christoph

PRAGMA foreign_keys = ON;

in runtime. But you don't need foreign keys support in this case. You
may use it for garbage collection.
-- 
Kit
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] TEXT PRIMARY KEY

[Christoph P.U. Kukulies]

Am 04.03.2012 15:22, schrieb Kit:

2012/3/4 Christoph P.U. Kukulies:

CREATE TABLE dir(filename text, md5sum text, size int, content blob,
PRIMARY KEY(filename,md5sum,size));

Coming back to your suggestion using PRIMARY KEY(filename,md5sum,size), how
would I address this PRIMARY KEY, example:

I'm building a table
CREATE TABLE candidate (?,client TEXT, md5sum TEXT, basename TEXT, size INT,
...some other stuff )
The ? should stand for the - is that FOREIGN KEY? - key in the dir-TABLE
which is formed of filename,md5sum,size.
Christoph

Foreign key is (basename,md5sum,size).

Your example shows that a composite key in this case is possible, but
it is not appropriate. Use simple key md5sum. Make two tables.

Attribute "size" (part of primary key) is redundant.


Kit,

I'm coming back again on your suggestion. Maybe you can elaborate, why
size is redundant and of what I should make two tables?

Let me describe the problem again:

I'm going recursively through a directory tree collection information on 
all files, that is,


CREATE TABLE instance  (
  path TEXT,
  md5sum TEXT,
  basename TEXT,
  size INT,
  creation_date TEXT,
  last_write_time TEXT,
  );

CREATE TABLE resource (
  size INT,
  name TEXT,
  md5sum TEXT,
  data BLOB,
  primary key(basename,md5sum,size)
);

What makes the contents of two files equal (so that their contents can 
be represented by the same resource) ?


md5sum = md5sum
size = size
name = name (allowing different names sharing the same resource is 
another thing - I might do that but do

not want at the moment).

So when I'm now visiting every file I will check whether the file with 
its contents has already been entered into the

TABLE resource.

My problem: what do I have to change in TABLE instance so that I can use 
it to determine whether the key is already

in the resource TABLE?

Would that be a FOREIGN KEY? And how would I do that in syntax?
If I need FOREIGN KEY, would I have to enable that in SQLite somehow (at 
compile time)?


Thanks,

Christoph








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

Re: [sqlite] TEXT PRIMARY KEY

[Kit]

2012/3/4 Christoph P.U. Kukulies :
>> CREATE TABLE dir(filename text, md5sum text, size int, content blob,
>>    PRIMARY KEY(filename,md5sum,size));
>
> Coming back to your suggestion using PRIMARY KEY(filename,md5sum,size), how
> would I address this PRIMARY KEY, example:
>
> I'm building a table
> CREATE TABLE candidate (?,client TEXT, md5sum TEXT, basename TEXT, size INT,
> ...some other stuff )
> The ? should stand for the - is that FOREIGN KEY? - key in the dir-TABLE
> which is formed of filename,md5sum,size.
> Christoph

Foreign key is (basename,md5sum,size).

Your example shows that a composite key in this case is possible, but
it is not appropriate. Use simple key md5sum. Make two tables.

Attribute "size" (part of primary key) is redundant.
-- 
Kit
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] TEXT PRIMARY KEY

[Christoph P.U. Kukulies]

Am 04.03.2012 10:31, schrieb Kit:

2012/3/4 Christoph P.U. Kukulies:

Thanks for the ideas. The problem is with md5sum clashes: all files with 0
bytes have the same md5sum.
Also files with  same contents have the same md5sum but may have a different
name.

That's no problem. if you put names to another table, you may share one content.


Normalize. One cell, one atomic information. You may use md5sum as
"text primary key" or you may define PRIMARY
KEY(filename,md5sum,size).

Interesting. How would that work syntaxwise? I mean, at table creation time?

CREATE TABLE dir(filename text, md5sum text, size int, content blob,
PRIMARY KEY(filename,md5sum,size));

Coming back to your suggestion using PRIMARY KEY(filename,md5sum,size), 
how would I

address this PRIMARY KEY, example:

I'm building a table

CREATE TABLE candidate (?,client TEXT, md5sum TEXT, basename TEXT, size 
INT, ...some other stuff )


The ? should stand for the - is that FOREIGN KEY? - key in the dir-TABLE 
which is formed of filename,md5sum,size.


--
Christoph

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

Re: [sqlite] TEXT PRIMARY KEY

[Kit]

2012/3/4 Christoph P.U. Kukulies :
> At the moment I have everything in the filesystem. The test situation is
> that about 500 testprograms have to be run
> with product release N against product release N-1. The test programs are
> duplicated at the moment in all
> places. The deployments of product release builds N, N-1, N-2 are also in
> the filesystem.

You may use Git with branches for different versions

> If I boil it all down from, say 60 GB to 2 GB (as my first attempts resulted
> in), I could generate the testbed on the push
> of a button out of the database and assemble it in a tree with two branches,
> the "new candidate" branch and the "reference" branch,
> run the tests. That all could be web app based in the end.

Git use own database solution optimized for performance and allows
modify all glue scripts in userspace. You may personalize Git for
different purposes.

> Just a question on "NORMALIZATION": Is normalization being done on a
> database by some operation, like
> giving a command: "normalize it" or is it done by design of the data
> structures being entered?
> Christoph

"Normalization" is manually process to minimize redundancy and
dependency from data structures. See "Database normalization". You may
normalize your table to 3 tables and denormalize to 2 tables. Replace
attribute "size" with attribute "version". Primary key first table
will be reduced to (filename,version), primary key second table to
(md5sum).
-- 
Kit
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] TEXT PRIMARY KEY

[Christoph P.U. Kukulies]

Am 04.03.2012 10:31, schrieb Kit:

2012/3/4 Christoph P.U. Kukulies:

Thanks for the ideas. The problem is with md5sum clashes: all files with 0
bytes have the same md5sum.
Also files with  same contents have the same md5sum but may have a different
name.

That's no problem. if you put names to another table, you may share one content.


Normalize. One cell, one atomic information. You may use md5sum as
"text primary key" or you may define PRIMARY
KEY(filename,md5sum,size).

Interesting. How would that work syntaxwise? I mean, at table creation time?

CREATE TABLE dir(filename text, md5sum text, size int, content blob,
PRIMARY KEY(filename,md5sum,size));


Filename, version, date and size put to another table with md5sum as a
foreign key. Inspire with Git system or use it.

I will have a look at Git.
Christoph

If you planning version control system, you may select from completed
systems, eg. Subversion, Git, Mercurial and Bazaar.


Thanks for the above. Actually, I'm not planning a versioning system. 
I'm planning a database based  testsuite.
At the moment I have everything in the filesystem. The test situation is 
that about 500 testprograms have to be run
with product release N against product release N-1. The test programs 
are duplicated at the moment in all
places. The deployments of product release builds N, N-1, N-2 are also 
in the filesystem.


If I boil it all down from, say 60 GB to 2 GB (as my first attempts 
resulted in), I could generate the testbed on the push
of a button out of the database and assemble it in a tree with two 
branches, the "new candidate" branch and the "reference" branch,

run the tests. That all could be web app based in the end.

Just a question on "NORMALIZATION": Is normalization being done on a 
database by some operation, like
giving a command: "normalize it" or is it done by design of the data 
structures being entered?



--
Christoph



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

Re: [sqlite] TEXT PRIMARY KEY

[Kit]

2012/3/4 Christoph P.U. Kukulies :
> Thanks for the ideas. The problem is with md5sum clashes: all files with 0
> bytes have the same md5sum.
> Also files with  same contents have the same md5sum but may have a different
> name.

That's no problem. if you put names to another table, you may share one content.

>> Normalize. One cell, one atomic information. You may use md5sum as
>> "text primary key" or you may define PRIMARY
>> KEY(filename,md5sum,size).
>
> Interesting. How would that work syntaxwise? I mean, at table creation time?

CREATE TABLE dir(filename text, md5sum text, size int, content blob,
   PRIMARY KEY(filename,md5sum,size));

>> Filename, version, date and size put to another table with md5sum as a
>> foreign key. Inspire with Git system or use it.
>
> I will have a look at Git.
> Christoph

If you planning version control system, you may select from completed
systems, eg. Subversion, Git, Mercurial and Bazaar.
-- 
Kit
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] TEXT PRIMARY KEY

[Christoph P.U. Kukulies]

Am 03.03.2012 19:29, schrieb Kit:

2012/3/3 Christoph P.U. Kukulies:

I'm building a unique ID made up from the basename, md5sum and
size of the file. This results
as a TEXT PRIMARY KEY (e.g. filename_md5sum_size).
Can I use (I'm using System.Data.SQLite) a try clause to find out whether
the entry is already there?

I think this can be faster than asking the database by an SQL statement,
whether the KEY is already there.
Christoph
Thanks for the ideas. The problem is with md5sum clashes: all files with 
0 bytes have the same md5sum.
Also files with  same contents have the same md5sum but may have a 
different name.



Normalize. One cell, one atomic information. You may use md5sum as
"text primary key" or you may define PRIMARY
KEY(filename,md5sum,size).


Interesting. How would that work syntaxwise? I mean, at table creation time?


Filename, version, date and size put to another table with md5sum as a
foreign key. Inspire with Git system or use it.


I will have a look at Git.

--
Christoph


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

Re: [sqlite] TEXT PRIMARY KEY

[Kit]

2012/3/3 Christoph P.U. Kukulies :
> I'm building a unique ID made up from the basename, md5sum and
> size of the file. This results
> as a TEXT PRIMARY KEY (e.g. filename_md5sum_size).
> Can I use (I'm using System.Data.SQLite) a try clause to find out whether
> the entry is already there?
>
> I think this can be faster than asking the database by an SQL statement,
> whether the KEY is already there.
> Christoph

Normalize. One cell, one atomic information. You may use md5sum as
"text primary key" or you may define PRIMARY
KEY(filename,md5sum,size).

Filename, version, date and size put to another table with md5sum as a
foreign key. Inspire with Git system or use it.
-- 
Kit
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] TEXT PRIMARY KEY

[Christoph P.U. Kukulies]

Am 02.03.2012 13:59, schrieb Jay A. Kreibich:

On Fri, Mar 02, 2012 at 10:44:20AM +0100, Christoph P.U. Kukulies scratched on 
the wall:

When defining a column TEXT PRIMARY KEY (is that possible on TEXT?),
would this imply uniqueness?

   Kind of.  It implies uniqueness in the SQL sense, which does not
   include NULLs (remember, NULL != NULL).  The SQL term "PRIMARY KEY"
   should imply both "UNIQUE" and "NOT NULL", but there is a long
   standing issue in SQLite that allows NULLs in non-integer PRIMARY
   KEY columns.  This allows "duplicate" NULL entries in a PK column.

   Normally this isn't an issue, as you shouldn't have NULLs in a
   single-column PK anyways.


Or would I have to write something like TEXT PRIMARY KEY UNIQUE ?

   No, but to be extra safe you should write "TEXT PRIMARY KEY NOT NULL."

-j


Thanks. And thanks to others for contributing.

My logic is the following: When I'm inserting files and their respective 
contents
into the database, I have to decide, whether  a file is already there. 
I'm building a
unique ID made up from the basename, md5sum and size of the file. This 
results

as a TEXT PRIMARY KEY (e.g. filename_md5sum_size).
Can I use (I'm using System.Data.SQLite) a try clause to find out 
whether the entry is

already there?

I think this can be faster than asking the database by an SQL statement, 
whether

the KEY is already there.

--
Christoph

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

Re: [sqlite] TEXT PRIMARY KEY

[Jay A. Kreibich]

On Fri, Mar 02, 2012 at 02:21:19PM +0100, Benoit Mortgat scratched on the wall:
> On Fri, Mar 2, 2012 at 13:59, Jay A. Kreibich  wrote:
> > On Fri, Mar 02, 2012 at 10:44:20AM +0100, Christoph P.U. Kukulies
> > scratched on the wall:
> >
> > ??Kind of. ??It implies uniqueness in the SQL sense, which does not
> > ??include NULLs (remember, NULL != NULL).
> 
> Actually, NULL != NULL is unknown.

  As an expression, yes (if you consider NULL to be "unknown").
  
  As a general statement, not really.
  
  Setting aside the theoretical argument of what, exactly, NULL means
  (e.g. "unknown" or something else), it is true that the SQL expression
  "NULL != NULL" will evaluate to "NULL", not "true."
 
  However, I was only trying to make a general statement that "one
  NULL is not equal to another," in the sense that "NULL == NULL" will
  not evaluate to "true"... which means it will not trip a UNIQUE
  constraint.  This is why the PK constraint normally implies both
  UNIQUE and NOT NULL.  Not only do NULLs not make sense in a PK as a
  fundamental identifier, allowing NULLs also breaks the concept that a
  PK should have a *known* unique (i.e. no NULLs allowed in comparisons)
  value for each row.

  SQLite does not imply NOT NULL when you specify a PK constraint.
  This is in contradiction to the SQL standard, but has been wrong so
  long nobody wants to risk changing it.  Hence, if you want your
  SQLite PKs to have known unique values, you must specify not only PK,
  but explicitly specify NOT NULL.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] TEXT PRIMARY KEY

[Benoit Mortgat]

On Fri, Mar 2, 2012 at 13:59, Jay A. Kreibich  wrote:
> On Fri, Mar 02, 2012 at 10:44:20AM +0100, Christoph P.U. Kukulies
> scratched on the wall:
>
>  Kind of.  It implies uniqueness in the SQL sense, which does not
>  include NULLs (remember, NULL != NULL).

Actually, NULL != NULL is unknown. Any expression compared to NULL
with any of the operators == != < > <= >= LIKE GLOB will have unknown
result.

 C:\> sqlite3
 SQLite version 3.7.10 2012-01-16 13:28:40
 Enter ".help" for instructions
 Enter SQL statements terminated with a ";"
 sqlite> SELECT CASE WHEN NULL = NULL  THEN 0
...> WHEN NULL <> NULL THEN 1
...> ELSE 2
...>END;
 2
 sqlite> .q



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

Re: [sqlite] TEXT PRIMARY KEY

[Black, Michael (IS)]

Hmmm...works for me...



On Windows:

SQLite version 3.7.9 2011-11-01 00:52:41
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table test(a text primary key);
sqlite> insert into test values('1');
sqlite> insert into test values('1');
Error: column a is not unique

On Linux:

SQLite version 3.7.10 2012-01-16 13:28:40
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table test (a text primary key);
sqlite> insert into test values('1');
sqlite> insert into test values('1');
Error: column a is not unique



Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Oliver Peters [oliver@web.de]
Sent: Friday, March 02, 2012 4:13 AM
To: sqlite-users@sqlite.org
Subject: EXT :Re: [sqlite] TEXT PRIMARY KEY

Am 02.03.2012 11:03, schrieb Oliver Peters:


sorry I meant

CREATE TABLE test(
a TEXT PRIMARY KEY
);

(without INTEGER, usually I write INTEGER and not TEXT :-) )

> Am 02.03.2012 10:44, schrieb Christoph P.U. Kukulies:
>> When defining a column TEXT PRIMARY KEY (is that possible on TEXT?),
>
> yes
>
>> would this imply uniqueness?
>
> yes
>
>> Or would I have to write something like TEXT PRIMARY KEY UNIQUE ?
>
> no and that doesn't make sense I'd say
>
>>
>
> [...]
>
>
> simply try (untested)
>
> CREATE TABLE test(
> a TEXT INTEGER PRIMARY KEY
> );
>
> INSERT INTO test(a) VALUES('1');
> INSERT INTO test(a) VALUES('1');
>
>
>
> oliver
> ___
> 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] TEXT PRIMARY KEY

[Jay A. Kreibich]

On Fri, Mar 02, 2012 at 10:44:20AM +0100, Christoph P.U. Kukulies scratched on 
the wall:
> When defining a column TEXT PRIMARY KEY (is that possible on TEXT?),
> would this imply uniqueness?

  Kind of.  It implies uniqueness in the SQL sense, which does not
  include NULLs (remember, NULL != NULL).  The SQL term "PRIMARY KEY"
  should imply both "UNIQUE" and "NOT NULL", but there is a long
  standing issue in SQLite that allows NULLs in non-integer PRIMARY
  KEY columns.  This allows "duplicate" NULL entries in a PK column.

  Normally this isn't an issue, as you shouldn't have NULLs in a
  single-column PK anyways.
  
> Or would I have to write something like TEXT PRIMARY KEY UNIQUE ?

  No, but to be extra safe you should write "TEXT PRIMARY KEY NOT NULL."

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] TEXT PRIMARY KEY

[Oliver Peters]

Am 02.03.2012 11:03, schrieb Oliver Peters:


sorry I meant

CREATE TABLE test(
a TEXT PRIMARY KEY
);

(without INTEGER, usually I write INTEGER and not TEXT :-) )


Am 02.03.2012 10:44, schrieb Christoph P.U. Kukulies:

When defining a column TEXT PRIMARY KEY (is that possible on TEXT?),


yes


would this imply uniqueness?


yes


Or would I have to write something like TEXT PRIMARY KEY UNIQUE ?


no and that doesn't make sense I'd say





[...]


simply try (untested)

CREATE TABLE test(
a TEXT INTEGER PRIMARY KEY
);

INSERT INTO test(a) VALUES('1');
INSERT INTO test(a) VALUES('1');



oliver
___
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] TEXT PRIMARY KEY

[Oliver Peters]

Am 02.03.2012 10:44, schrieb Christoph P.U. Kukulies:

When defining a column TEXT PRIMARY KEY (is that possible on TEXT?),


yes


would this imply uniqueness?


yes


Or would I have to write something like TEXT PRIMARY KEY UNIQUE ?


no and that doesn't make sense I'd say





[...]


simply try (untested)

CREATE TABLE test(
a TEXT INTEGER PRIMARY KEY
);

INSERT INTO test(a) VALUES('1');
INSERT INTO test(a) VALUES('1');



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

Re: [sqlite] TEXT PRIMARY KEY

[Kit]

2012/3/2, Christoph P.U. Kukulies :
> When defining a column TEXT PRIMARY KEY (is that possible on TEXT?),

Yes.

> would this imply uniqueness?

Yes.

> Or would I have to write something like TEXT PRIMARY KEY UNIQUE ?
> Christoph Kukulies

No. PRIMARY KEY is always UNIQUE.
-- 
Kit
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] TEXT PRIMARY KEY

[Kees Nuyt]

On Fri, 02 Mar 2012 10:44:20 +0100, "Christoph P.U. Kukulies"
 wrote:

> When defining a column TEXT PRIMARY KEY 
> (is that possible on TEXT?), 

Yes that is possible on any data type.

> would this imply uniqueness?

It would. 

> Or would I have to write something like TEXT PRIMARY KEY UNIQUE ?

No. Neither should you create a UNIQUE INDEX on the primary key column.

-- 
Regards,

Kees Nuyt

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