Re: [sqlite] Features of SQLite question

[Jay A. Kreibich]

On Mon, Jun 11, 2012 at 10:11:45PM +, Black, Michael (IS) scratched on the 
wall:
> Answer: The most recent one.
> 
> Isn't it true that semi-accurate (sub-second) time-tagged transactions
> will generally keep 2 databases in sync?

  "Generally", perhaps, but not always.

> I've done that before with considerable success.  You don't sync the
> fields...you sync the transactions.  The presumption is that later
> transactions win regardless of intervening updates.

  The issue is with data history and multiple changes.  Consider if two
  devices are sync'ed.  Then a modification is made on device A.  Then
  B.  Then A again (without seeing B's modifications).  Then the
  devices are sync'ed again.

  The data on "A" was the "latest" copy, but if "B" made changes to the
  same record, it is questionable about which is true.  "A" might have
  made modifications to out-of-date data.

  Even if you play back the transactions, one by one, in order, that
  doesn't always help.  You might find that "B" made a modification
  that makes "A's" second modification invalid, or that the
  modification would result in a different answer.

  If all you're talking about is an address book, it might not be that
  big of a deal-- especially if you can delineate the data.  For
  example, if each atomic record is timestamped, you can generally just
  take the most up-to-date value of each record.  It gets trick when
  data is added or deleted however, and even a simple address book
  application will typically allow people to add or remove multiple
  phone numbers, addresses, etc. for a given entry. 
  
  For example, in my previous case, A modifies an existing address,
  B removes the address, A modifies the address again.  OK, now what?
  There are a few options on how to deal with this, and the "right"
  choice is likely a combo of application needs and user expectations.

> Only problem is when times are too close (beyond your time-sync
> resolution) which requires human intervention...though in a user-driven
> system that should be nigh on to impossible to create.

  That's the least of your issues.  Assuming personal devices like an
  iPhone and iPad, there is a very good chance the clocks are kept well
  in-sync.  Given that people don't tend to use those types of devices
  at the same time, to do the same task, I don't think this is a major
  concern.

  It might very well be a concern in other contexts, however...
  especially for devices that don't "live" on the network and have lots
  of chances to sync their clock.

> I do realize the complexity of keeping two database in syncbut
> transaction systems have been around for decades.  Depends on your
> application.

  Yes, it does depend, and that's just the point.

  The best example of this is likely source-control systems.  And
  they're typically very complex, traditionally have a central "master"
  dept, and often require human intervention to untangle the mess when
  a merge goes wrong.  They're actually an easy case.

   -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] Features of SQLite question

[Simon Slavin]

On 12 Jun 2012, at 12:06am, Brent Shifley  wrote:

> So, if I understand things correctly, if I created said app, with associated 
> database, if I had identical databases on both iPads, and tracked all 
> transactions and used them to modify a third copy of the database, that this 
> might work?
> 
> Uh, right.
> 
> Since I am beginner programmer / beginner sql dude (can you tell I like a 
> challenge / pain) how would I store said transactions so that I can readily 
> post them against the third db?

I'm actually warning you that unless you have a ton of experience you're going 
to get this wrong and lose data and have your users shouting at you.  The 
question you asked is unsolved: nobody has found a good way to do it.  Sorry.  
You can think of six solutions which look like they'll work and a professional 
programmer will find problems with all of them.

However, since you mention iPads, you don't really need to synchronise copies 
of the database which have been kept separate from one-another.  If you can 
rely on your iPads being connected to the internet you can use Apple's own 
iCloud system in realtime.  Rather than wait until someone presses a 'synch' 
button, have changes show up on all iPads immediately.  This gets rid of the 
problem of delayed synchronisation: all changes are made on all copies at once, 
and everyone sees them immediately.

You'll find tools do to this are available to you as an iPad programmer.  
Though I recommend you tackle other aspects of your programming first: it's not 
simple to do iCloud programming and iPad programming itself isn't easy compared 
to simple ways of programming a computer.

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

Re: [sqlite] Features of SQLite question

[Brent Shifley]

Wow!  I feel I just tried to eat an elephant.  ;)

What I am thinking of doing is creating a specialized app for handling contacts 
related to a business that I own.  I am formerly a software tester that was 
exposed to a lot of different things, including a little sql.  Now, back to the 
problem...

So, if I understand things correctly, if I created said app, with associated 
database, if I had identical databases on both iPads, and tracked all 
transactions and used them to modify a third copy of the database, that this 
might work?

Uh, right.

Since I am beginner programmer / beginner sql dude (can you tell I like a 
challenge / pain) how would I store said transactions so that I can readily 
post them against the third db?

Sent from my iPad

On Jun 11, 2012, at 5:31 PM, Simon Slavin  wrote:

> 
> On 11 Jun 2012, at 11:11pm, "Black, Michael (IS)"  
> wrote:
> 
>> Isn't it true that semi-accurate (sub-second) time-tagged transactions will 
>> generally keep 2 databases in sync?
>> 
>> I've done that before with considerable success.  You don't sync the 
>> fields...you sync the transactions. The presumption is that later 
>> transactions win regardless of intervening updates.
> 
> Your idea, if expressed in terms of SQL, is that you do not keep copies of 
> the records in the databases, instead you keep copies of all the INSERT, 
> UPDATE and DELETE commands executed.  To synchronise two databases you just 
> make sure all the commands are executed on all copies of the database.
> 
> It's a great idea.  Except it doesn't work unless the commands are executed 
> in the same order and there's no way to do that starting with one or other 
> copy which has been changed since the last sync.  So you need a third copy of 
> the databases: the database as it was the last time two copies were 
> synchronised.  And this needs to be stored either centrally or with each copy 
> of the database, which means there's no magic simple synchronisation 
> algorithm.
> 
> And then you get into intent.  Suppose you have this row of data:
> 
> {name: Mike Smith, town: London}
> 
> on your database.  Since the last synchronisation one user of the database 
> executes the command
> 
> UPDATE contacts SET town='Hendon' WHERE town='London'
> 
> And an hour later the user of the other copy executes the command
> 
> DELETE FROM contacts WHERE town='Hendon'
> 
> Before the two were synchonised, the row for Mike Smith still exists in both 
> copies.  Both users were happy with this, even though they didn't know they 
> had different towns for Mike.  But after the two are synchonised and all 
> transactions are played back in log order, Mike Smith has somehow disappeared 
> from both copies.  That's not synchronisation, that's deleting data we wanted 
> to keep !  That's disastrous !
> 
>> Only problem is when times are too close (beyond your time-sync resolution) 
>> which requires human intervention...though in a user-driven system that 
>> should be nigh on to impossible to create.
> 
> For the sake of discussion you can pretend that your log stores times to Unix 
> epoch precision.  Even with that, as you can see, there are still problems.
> 
>> I do realize the complexity of keeping two database in syncbut 
>> transaction systems have been around for decades.  Depends on your 
>> application.
> 
> Yet it's still an unsolved problem.  I can (maybe should) write some text 
> layout out this problem and some solutions which work in some situations but 
> there's still no general solution that I know of.
> 
> Simon.
> ___
> 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] Features of SQLite question

[Simon Slavin]

On 11 Jun 2012, at 11:11pm, "Black, Michael (IS)"  
wrote:

> Isn't it true that semi-accurate (sub-second) time-tagged transactions will 
> generally keep 2 databases in sync?
> 
> I've done that before with considerable success.  You don't sync the 
> fields...you sync the transactions. The presumption is that later 
> transactions win regardless of intervening updates.

Your idea, if expressed in terms of SQL, is that you do not keep copies of the 
records in the databases, instead you keep copies of all the INSERT, UPDATE and 
DELETE commands executed.  To synchronise two databases you just make sure all 
the commands are executed on all copies of the database.

It's a great idea.  Except it doesn't work unless the commands are executed in 
the same order and there's no way to do that starting with one or other copy 
which has been changed since the last sync.  So you need a third copy of the 
databases: the database as it was the last time two copies were synchronised.  
And this needs to be stored either centrally or with each copy of the database, 
which means there's no magic simple synchronisation algorithm.

And then you get into intent.  Suppose you have this row of data:

{name: Mike Smith, town: London}

on your database.  Since the last synchronisation one user of the database 
executes the command

UPDATE contacts SET town='Hendon' WHERE town='London'

And an hour later the user of the other copy executes the command

DELETE FROM contacts WHERE town='Hendon'

Before the two were synchonised, the row for Mike Smith still exists in both 
copies.  Both users were happy with this, even though they didn't know they had 
different towns for Mike.  But after the two are synchonised and all 
transactions are played back in log order, Mike Smith has somehow disappeared 
from both copies.  That's not synchronisation, that's deleting data we wanted 
to keep !  That's disastrous !

> Only problem is when times are too close (beyond your time-sync resolution) 
> which requires human intervention...though in a user-driven system that 
> should be nigh on to impossible to create.

For the sake of discussion you can pretend that your log stores times to Unix 
epoch precision.  Even with that, as you can see, there are still problems.

> I do realize the complexity of keeping two database in syncbut 
> transaction systems have been around for decades.  Depends on your 
> application.

Yet it's still an unsolved problem.  I can (maybe should) write some text 
layout out this problem and some solutions which work in some situations but 
there's still no general solution that I know of.

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

Re: [sqlite] Features of SQLite question

[Black, Michael (IS)]

Answer: The most recent one.



Isn't it true that semi-accurate (sub-second) time-tagged transactions will 
generally keep 2 databases in sync?

I've done that before with considerable success.  You don't sync the 
fields...you sync the transactions.  The presumption is that later transactions 
win regardless of intervening updates.



Only problem is when times are too close (beyond your time-sync resolution) 
which requires human intervention...though in a user-driven system that should 
be nigh on to impossible to create.



I do realize the complexity of keeping two database in syncbut transaction 
systems have been around for decades.  Depends on your application.



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 Simon Slavin [slav...@bigfraud.org]
Sent: Monday, June 11, 2012 5:03 PM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Features of SQLite question


On 11 Jun 2012, at 10:14pm, Brent Shifley  wrote:

> I was wondering.  If you had a SQLite database on two different iPads, then 
> had different changes applied to each database, is it possible to sync the 
> changes to each database?

Suppose you have a list of contacts in a database and both copies have had one 
contact's phone number changed.  Which 'new' phone number should appear in the 
merged copy ?

If you are the writer of the application which accesses the databases, and you 
understand exactly how the application uses them, and how the users have been 
told to use the application, then you can build some synchrony features into 
your software.

But writing a general solution -- something which will work on two copies of 
any SQL database -- is impossible.  Not only for SQLite but for all relational 
databases no matter what DBMS is used.  There are questions about how various 
changes should be handled which need either answering by a human or detailed 
information about how the application uses the database.  It's not possible to 
write one program which can tell how to get the 'best' merging of the two 
copies without it.

Simon.
___
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] Features of SQLite question

[Simon Slavin]

On 11 Jun 2012, at 10:14pm, Brent Shifley  wrote:

> I was wondering.  If you had a SQLite database on two different iPads, then 
> had different changes applied to each database, is it possible to sync the 
> changes to each database?

Suppose you have a list of contacts in a database and both copies have had one 
contact's phone number changed.  Which 'new' phone number should appear in the 
merged copy ?

If you are the writer of the application which accesses the databases, and you 
understand exactly how the application uses them, and how the users have been 
told to use the application, then you can build some synchrony features into 
your software.

But writing a general solution -- something which will work on two copies of 
any SQL database -- is impossible.  Not only for SQLite but for all relational 
databases no matter what DBMS is used.  There are questions about how various 
changes should be handled which need either answering by a human or detailed 
information about how the application uses the database.  It's not possible to 
write one program which can tell how to get the 'best' merging of the two 
copies without it.

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

Re: [sqlite] Features of SQLite question

[Jay A. Kreibich]

On Mon, Jun 11, 2012 at 04:14:59PM -0500, Brent Shifley scratched on the wall:
> I was wondering.  If you had a SQLite database on two different iPads,
> then had different changes applied to each database, is it possible to
> sync the changes to each database?

  Sure, why not?


  Does SQLite have any features or support for assisting the
  application developer with this task?
  
  No.


  The sync process, including record identification, data movement, and
  conflict resolution would be completely the application's problem.
  And, as you might guess, it is a lot more complex than it first
  sounds.

   -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

[sqlite] Features of SQLite question

[Brent Shifley]

I was wondering.  If you had a SQLite database on two different iPads, then had 
different changes applied to each database, is it possible to sync the changes 
to each database?


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

Re: [sqlite] Quoting "id" versus 'id' in query

[Pavel Ivanov]

> You have overstated the requirements upon SQL identifiers.  Quoting via
> surrounding double-quote is only necessary if the identifier would not meet
> the definition of a "regular identifier", loosely understood to be a letter
> followed by letter, digit or underscore characters.

Also quoting is required if your identifier is the same as one of
SQLite's keywords (like "select", "table", "from" etc).


Pavel


On Mon, Jun 11, 2012 at 4:04 PM, Larry Brasfield
 wrote:
> On June 11, rick wrote:
>>
>> Yes, it can be explained:  says:
>>
>> 'keyword'  A keyword in single quotes is a string literal.
>> "keyword"  A keyword in double-quotes is an identifier
>>
>> So, "id" is interpreted as a column name, not as the string literal 'id',
>> unless the value in the double quotes is not an identifier (column name).
>
>
> You have overstated the requirements upon SQL identifiers.  Quoting via
> surrounding double-quote is only necessary if the identifier would not meet
> the definition of a "regular identifier", loosely understood to be a letter
> followed by letter, digit or underscore characters.  Hence, the text "id"
> without any quotes would be a valid SQL identifier.
>
> From http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt :
>
>   ::=
>                
>              | 
>
>   ::= 
>
>          ::=
>               [ {  |  }... ]
>
>
>          ::= !! See the Syntax Rules
>
>          ::=
>                
>              | 
>
> 1) An  is one of:
>
>            a) A ; or
>
>            b) A character that is identified as a letter in the character
>              repertoire identified by the               tion> or by the ; or
>
>            c) A character that is identified as a syllable in the char-
>              acter repertoire identified by the               specification> or by the ; or
>
>            d) A character that is identified as an ideograph in the char-
>              acter repertoire identified by the               specification> or by the .
>
> --
> Larry Brasfield
>
> ___
> 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] Quoting "id" versus 'id' in query

[Larry Brasfield]

On June 11, rick wrote:

Yes, it can be explained:  says:

'keyword'  A keyword in single quotes is a string literal.
"keyword"  A keyword in double-quotes is an identifier

So, "id" is interpreted as a column name, not as the string literal 'id',
unless the value in the double quotes is not an identifier (column name).


You have overstated the requirements upon SQL identifiers.  Quoting via
surrounding double-quote is only necessary if the identifier would not meet
the definition of a "regular identifier", loosely understood to be a letter
followed by letter, digit or underscore characters.  Hence, the text "id"
without any quotes would be a valid SQL identifier.

From http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt :

  ::=

  | 

  ::= 

  ::=
   [ {  |  
}... ]



  ::= !! See the Syntax Rules

  ::=

  | 

1) An  is one of:

a) A ; or

b) A character that is identified as a letter in the character
  repertoire identified by the  or by the ; or

c) A character that is identified as a syllable in the char-
  acter repertoire identified by the  or by the ; or

d) A character that is identified as an ideograph in the char-
  acter repertoire identified by the  or by the .

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

Re: [sqlite] Quoting "id" versus 'id' in query

[Larry Brasfield]

On June 11, rick wrote:

Something I noticed today:

sqlite> select * FROM words WHERE word = "id";


sqlite> select * FROM words WHERE word = 'id';
13556|id

sqlite> .schema
CREATE TABLE words (
 id integer primary key,
 word   varchar(64)
);

Yes, it can be explained:  says:

'keyword'  A keyword in single quotes is a string literal.
"keyword"  A keyword in double-quotes is an identifier

So, "id" is interpreted as a column name, not as the string literal 'id',
unless the value in the double quotes is not an identifier (column name).

I think this is quite nasty behaviour. Hope it prevents someone making the
same mistake!


You should take up the nastiness issue with the authors of the SQL-92 
standard and its successors.  Supporting the convention you just noticed 
is the least surprising behavior for those who have learned SQL as 
defined, and hence is not generally regarded as a nasty feature.


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

[sqlite] Quoting "id" versus 'id' in query

[rick]

Something I noticed today:

sqlite> select * FROM words WHERE word = "id";


sqlite> select * FROM words WHERE word = 'id';
13556|id

sqlite> .schema
CREATE TABLE words (
 id integer primary key,
 word   varchar(64)
);

Yes, it can be explained:  says:

'keyword'  A keyword in single quotes is a string literal.
"keyword"  A keyword in double-quotes is an identifier

So, "id" is interpreted as a column name, not as the string literal 'id',
unless the value in the double quotes is not an identifier (column name).

I think this is quite nasty behaviour. Hope it prevents someone making the
same mistake!

rick

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

Re: [sqlite] Running SQLite Test Suites

[Grace Batumbya]

> The "test" target is in the file "main.mk" which is "include"-ed by the very 
> last line of Makefile.linux-gcc.

Thanks Richard. That clears up things.

--
Grace Batumbya 
Research Assistant | Seneca CDOT 
Phone: 416-491-5050 x3548 
cdot.senecac.on.ca 

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

Re: [sqlite] Running SQLite Test Suites

[Richard Hipp]

The "test" target is in the file "main.mk" which is "include"-ed by the
very last line of Makefile.linux-gcc.

On Mon, Jun 11, 2012 at 1:33 PM, Grace Batumbya <
grace.batum...@senecacollege.ca> wrote:

> >./configure; make test
>
> >
>
> >I just ran it on Solaris, as shown above, and it appears to be working
> fine.  You will need a relatively recent version of TCL, however.  8.5 or
> 8.6.
>
>
>
> From the REAME file:
>
> ... If the configure script does not work out for you, there is a generic
> makefile named "Makefile.linux-gcc" in the top directory of the source tree
> that you can copy and edit to suit your needs
>
>
>
> The reason "make test" was not working for us is because
> Makefile.linux-gcc does not contain the test targets.
>
>
>
> Is there a reason why the test targets are not included in the generic
> makefile?
>
>
>
> Thanks.
>
> --
>
> Grace Batumbya
>
> Research Assistant | Seneca CDOT
>
> Phone: 416-491-5050 x3548
>
> cdot.senecac.on.ca
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] Specifying a nullable column

[Kevin Benson]

On Mon, Jun 11, 2012 at 10:35 AM, Paul Medynski  wrote:

> Hi folks,
>
> I notice that the syntax diagram for 'create table' shows the
> 'column-constraint' definition as requiring 'null' to always be preceded by
> 'not'.  I don't see any definition that supports just 'null'.  However,
> when using SQLite3 3.7.3 or 3.7.9, I can create a table and specify any
> column as "Foo  null", and it works as expected allowing the column
> to contain null values.
>
> Is the syntax diagram simply out of date, or am I doing something that
> appears to work, but will bite me in the end? :)
>
http://www.sqlite.org/draft/lang_createtable.html

"A CREATE TABLE command specifies the following attributes of the new table:
-
-
-
-
- A default value or expression for each column in the table.
-
-
- A set of SQL constraints for each table. SQLite supports UNIQUE, NOT
NULL, CHECK and FOREIGN KEY constraints."

"A table created using CREATE TABLE AS has no PRIMARY KEY and no
constraints of any kind. The default value of each column is NULL."

--
   --
  --
 --Ô¿Ô--
K e V i N
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Running SQLite Test Suites

[Grace Batumbya]

>./configure; make test

>

>I just ran it on Solaris, as shown above, and it appears to be working fine.  
>You will need a relatively recent version of TCL, however.  8.5 or 8.6.



>From the REAME file:

... If the configure script does not work out for you, there is a generic 
makefile named "Makefile.linux-gcc" in the top directory of the source tree 
that you can copy and edit to suit your needs



The reason "make test" was not working for us is because Makefile.linux-gcc 
does not contain the test targets.



Is there a reason why the test targets are not included in the generic makefile?



Thanks.

--

Grace Batumbya

Research Assistant | Seneca CDOT

Phone: 416-491-5050 x3548

cdot.senecac.on.ca


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

Re: [sqlite] integrity constraint that is equivalent to the following trigger:

[Wolfgang Meiners]

Am 10.06.12 14:59, schrieb Petite Abeille:
> 
> The short of it is that you can't. SQLite doesn't support such constraints. 
> Only the basics are supported: primary, unique, referential, not null, check:
> 

Thank you for that information. It saves me a lot of time searching for
a solution that does not exist. I think i can work arount this in
sqlalchemy.

Regards
Wolfgang

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

[sqlite] Specifying a nullable column

[Paul Medynski]

Hi folks,

I notice that the syntax diagram for 'create table' shows the 
'column-constraint' definition as requiring 'null' to always be preceded 
by 'not'.  I don't see any definition that supports just 'null'.  
However, when using SQLite3 3.7.3 or 3.7.9, I can create a table and 
specify any column as "Foo  null", and it works as expected 
allowing the column to contain null values.


Is the syntax diagram simply out of date, or am I doing something that 
appears to work, but will bite me in the end? :)


Thanks,
-Paul

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

Re: [sqlite] .nullvalue

[Simon Slavin]

On 11 Jun 2012, at 9:47am, Klaas Van Be  wrote:

> We're supposed to be able to change the representation of the NULL value.
> Whatever parameter I choose it does not seem to work. I always get an empty 
> string.

I think you have empty strings in the table, and that's why you're getting 
empty strings printed.  Try using

.dump numsong

and see whether it shows numsong having NULL or empty strings in.

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

[sqlite] .nullvalue

[Klaas Van Be]

Ciao fellow SQLite users,

We're supposed to be able to change the representation of the NULL value.
Whatever parameter I choose it does not seem to work. I always get an empty 
string.

What I did wrong in this sample table, the commands or parameters?

sqlite> .nullvalue *
sqlite> select numinsng "#", ttlofsng "s", prfofsng "a" from numsong where 
numinsng <1;
#|s|a
0|| 
sqlite> .nullvalue "*"
sqlite> select numinsng "#", ttlofsng "s", prfofsng "a" from numsong where 
numinsng <1;
#|s|a
0|| 
sqlite> .nullvalue "void"
sqlite> select numinsng "#", ttlofsng "s", prfofsng "a" from numsong where 
numinsng <1;
#|s|a
0|| 
sqlite> .mode line 
sqlite> select numinsng "#", ttlofsng "s", prfofsng "a" from numsong where 
numinsng <1;
    # = 0
    s = 
    a =  
sqlite> .nullvalue "?"
sqlite> select numinsng "#", ttlofsng "s", prfofsng "a" from numsong where 
numinsng <1;
    # = 0
    s = 
    a =  
sqlite> .nullvalue ?
sqlite> select numinsng "#", ttlofsng "s", prfofsng "a" from numsong where 
numinsng <1;
    # = 0
    s = 
    a =  
sqlite> .nullvalue 'void'
sqlite> select numinsng "#", ttlofsng "s", prfofsng "a" from numsong where 
numinsng <1;
    # = 0
    s = 
    a =  

FYI the entire table

sqlite> .mode list  
sqlite> select * from numsong order by 1;
numinsng|ttlofsng|prfofsng
0|| 
1|one|U2
7|seven seconds|Nene Cherry
40|'40'|U2
2000|2000 miles|Pretenders



Thanks in advance for your cooperation and/or help. 


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