Re: [sqlite] BLOBs and NULLs

[Drago, William @ MWG - NARDAEAST]

JKL,

>The part I like least about your design (1) the use of a GUID

I agree with everything you said about GUID. The GUID column will be named 
DataSetID and will be an integer.


>The tuple (IL, Phase, RL, Isolation) doesn't
>have much meaning, does it, in the sense that
>*together* they say something special about
>whatever (GUID, Path) represent?
>From your description, each individual tuple
> (e.g., (GUID, Path, IL)) is meaningful, but the
>presence of, say, IL without Phase is not.
>Each is a separate, freestanding fact,
>justifying its own table.

These parameters do mean more when taken together than separately. Much like a 
patient's height, weight, and blood pressure do give you some information, but 
knowing all 3 gives you significant information about the patient. If height, 
weight, and blood pressure should be in separate tables, then perhaps my 
measurements should be too.

This is a small, low volume database. I'm not sure the 4 tables vs. 1 table is 
going to make a big difference one way or another, but I do want the logic to 
be correct. If using 4 tables is the right way to do this, then that's what 
I'll do. If it's 6 of one, half dozen of the other, then  maybe I'll flip a 
coin.

BTW, this is going beyond SQLite and I don't want to upset the moderators. Feel 
free to contact me directly at my L3 email address.

-Bill

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of James K. Lowden
Sent: Monday, April 21, 2014 10:59 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] BLOBs and NULLs

On Mon, 21 Apr 2014 13:30:15 +
"Drago, William @ MWG - NARDAEAST"  wrote:

> Should I split this table up into smaller tables to eliminate the
> NULLs (e.g. use one table each for IL, Phase, RL, Isolation)? I'm not
> sure what the best design choice would be.

While Dr. Hipp's answer focussed on correctness and performance.  From the 
logical side I suggest you consider four separate tables.

Your database design is a model of the real world.  The rules it enforces 
should reflect those of the world it models. The tuple (IL, Phase, RL, 
Isolation) doesn't have much meaning, does it, in the sense that *together* 
they say something special about whatever (GUID, Path) represent?  From your 
description, each individual tuple (e.g., (GUID, Path, IL)) is meaningful, but 
the presence of, say, IL without Phase is not.  Each is a separate, 
freestanding fact, justifying its own table.

The part I like least about your design (1) the use of a GUID and, in 
particular (2) the name "GUID" for the column.  If you are generating this 
GUID, don't; use an integer.  If you're not generating it -- if it comes to you 
from another source and therefore identifies something in "the real world" in 
some sense, OK.  Either way, use the name of the column to reflect the thing 
identified, not the datatype of the identifier.

HTH.

--jkl
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by reply e-mail and 
immediately delete this message and any attachments.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] cannot set connection while a datareader is active

[rava]

I know, you have all code wich is involved in the error... for the parte 
involved, no other function was called... i call executereader only for one 
datareader and two Times directly to the command... do you found the other 
datareader in the sent code? If exist it have to be in the sent code, but i 
don't see nothing..  you could?

Joe Mistachkin  ha scritto:

>
>Stefano Ravagni wrote:
>>
>> Thanks for answer Joe, but as just sayed i have only one datareader,
>>
>
>That is not possible.  There must be at least two different data reader
>instances because the references in the watch window did not match.
>Every time you call ExecuteReader, a brand new data reader is returned.
>Keeping track of these returned data readers is very important because
>they must all be properly disposed at some point.
>
>>
>> i'm sure of that, and this is demostrated because with others provider
>> i have not error.
>>
>
>Perhaps the other providers do not have a restriction on resetting the
>underlying connection for a command when data readers are active.
>
>--
>Joe Mistachkin
>
>___
>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] cannot set connection while a datareader is active

[Joe Mistachkin]

Stefano Ravagni wrote:
>
> Thanks for answer Joe, but as just sayed i have only one datareader,
>

That is not possible.  There must be at least two different data reader
instances because the references in the watch window did not match.
Every time you call ExecuteReader, a brand new data reader is returned.
Keeping track of these returned data readers is very important because
they must all be properly disposed at some point.

>
> i'm sure of that, and this is demostrated because with others provider
> i have not error.
>

Perhaps the other providers do not have a restriction on resetting the
underlying connection for a command when data readers are active.

--
Joe Mistachkin

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

Re: [sqlite] Bug report: column name includes table alias when CTE is used

[Kevin Benson]

On Mon, Apr 21, 2014 at 8:29 AM, Richard Hipp  wrote:

> On Mon, Apr 21, 2014 at 1:16 AM, Andre  wrote:
>
> > Hi,
> >
> > Apparently when a CTE is used, the column name includes the table alias.
> > However, when no CTE is used, the alias is not present in the returned
> > column name.
> >
> > SQLite version 3.8.4.3 2014-04-03 16:53:12
> > Enter ".help" for usage hints.
> > Connected to a transient in-memory database.
> > Use ".open FILENAME" to reopen on a persistent database.
> > sqlite> create table X (columnA int);
> > sqlite> insert into X values (1);
> > sqlite> .header on
> > sqlite> select alias.columnA from X alias;
> > *columnA*
> > 1
> > sqlite> with CTE as (select columnA from X) select alias.columnA from CTE
> > alias;
> > *alias.columnA*
> > 1
> > sqlite>
> >
> > I experienced this when rewriting a query to use CTE in an application
> that
> > based some logic on the column name. I'd expect not to see the alias
> either
> > way. Is this a bug or is it expected for CTEs?
> >
>
> See
> http://www.sqlite.org/c3ref/mark/column_name.html?If+there+is+n*fiedfor
> further information.
>
>
Fixed this link for myself and future referrers:

http://www.sqlite.org/c3ref/mark/column_name.html?If+there+is+n*fied

--
   --
  --
 --Ô¿Ô--
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] cannot set connection while a datareader is active

[rava]

Thanks for answer Joe, but as just sayed i have only one datareader, i'm sure 
of that, and this is demostrated because with others provider i have not error.
Try please changing the first lines of code in frmprincipale.vb form as 
explained for match access database... you Will not have error... why this if 
not a provider error? 
Otherwise, what have i to do in tour mind? Thanks

Joe Mistachkin  ha scritto:

>
>Stefano Ravagni wrote:
>>
>> i've built a very little application wich could reprocude the "CANNOT SET
>> CONNECTION WHILE DATAREADER IS ACTIVE" error.
>> 
>
>I've run the project with Visual Studio 2013 and determined that the
>exception
>being raised is almost certainly not an issue with System.Data.SQLite, per
>se.
>
>At the point of the exception, I used the following watch expression:
>
>   
>DirectCast(objCmd,System.Data.SQLite.SQLiteCommand)._activeReader.Target Is
>Dati
>
>This returns false, indicating that while the SQLiteCommand object does
>indeed
>have an active data reader, it is NOT the one that was closed just above
>that
>point in the code.  I'm not sure how the project manages data reader
>instances;
>however, it would appear that System.Data.SQLite is throwing exceptions just
>as
>it should in this case.
>
>--
>Joe Mistachkin
>
>___
>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] INSERT OR REPLACE

[Simon Slavin]

On 21 Apr 2014, at 6:18pm, Richard Hipp  wrote:

> SQLite does the right thing and enforces NOT NULL on PRIMARY KEY for the
> newer WITHOUT ROWID tables.  But long ago there was a bug that prevented
> enforcement NOT NULL on PRIMARY KEY of ordinary tables and by the time the
> bug was discovered we couldn't really fix it without breaking lots of
> legacy.

Can it be fixed in SQLite4 ?  Can 'NOT NULL' be assumed for PRIMARY KEYs in 
SQLite4 ?

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

Re: [sqlite] INSERT OR REPLACE

[Richard Hipp]

On Mon, Apr 21, 2014 at 1:15 PM, Neville Dastur wrote:

>
> On 21 Apr 2014, at 18:13, Andy Goth  wrote:
>
> > On 4/21/2014 6:04 AM, Richard Hipp wrote:
> >> On Mon, Apr 21, 2014 at 6:18 AM, Neville Dastur wrote:
> >>> Google only showed up that UNIQUE needs to be added regardless of the
> >>> column being a PRIMARY KEY.
> >>
> >> Google is wrong.  The UNIQUE is superfluous.  PRIMARY KEY always
> >> implies UNIQUE.  Always.
> >
> > PRIMARY KEY also is supposed to imply NOT NULL.  However, for historical
> > reasons, SQLite allows NULL in PRIMARY KEY columns if the column is not
> > INTEGER, not explicitly NOT NULL, and not in a WITHOUT ROWID table.
>
> Does that mean that I should really explicitly state NOT NULL for
> non-integer primary keys?
>

Yes.

SQLite does the right thing and enforces NOT NULL on PRIMARY KEY for the
newer WITHOUT ROWID tables.  But long ago there was a bug that prevented
enforcement NOT NULL on PRIMARY KEY of ordinary tables and by the time the
bug was discovered we couldn't really fix it without breaking lots of
legacy.

-- 
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] cannot set connection while a datareader is active

[Stefano Ravagni]

HEllo Joe,

i've built a very little application wich could reprocude the "CANNOT SET
CONNECTION WHILE DATAREADER IS ACTIVE" error.

While extracting data and populate a checklist, you could see the
error...just launch the project.

I've putted block debug point in a function called
"EstraiDatiAlVoloAutomation" where you could found the point
were the code go in error after somes passages... sometimes at the first,
somes others in second or after...

If you observe the code you could see it say datareader is active when
dati.isclosed say TRUE...

In the "frmPrincipale.vb" form you can set the code to run under ACCESS
version simply changing 2 string,
and you can see it not generate this error, as not generate in many other
database system...

Becaus of "slimming" a big source code, consider maybe you could encounter
somes others error which have not to say with the
problem i ask solutioni'm sorry for that, i tryed to make a slim
project for reproduce error only...


A put a RAR archive with the slim project at this link:

https://www.dropbox.com/s/bugysawir52ivna/TestSQLite.rar




2014-04-20 6:00 GMT+02:00 Joe Mistachkin :

>
> Stefano Ravagni wrote:
> >
> > Is a big project wich re-call many function and interacts many times
> > with sqlite provider... i could try to isolate a piece of code but
> > isolating the code i don't know if the error will be reproduced...but i
> > will try is all VB codeare you interested to receive a ZIP file
> > containing a little piece of code with also database ? Or exist another
> > way to find the error in your mind ?
> > Thanks for answer!!!
> >
>
> It would be great if you could come up with an isolated body of code that
> demonstrates the issue.  Since this mailing list strips attachments, you
> could post a link to a ZIP file that can be downloaded.
>
> --
> Joe Mistachkin
>
> ___
> 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] INSERT OR REPLACE

[Neville Dastur]

On 21 Apr 2014, at 18:13, Andy Goth  wrote:

> On 4/21/2014 6:04 AM, Richard Hipp wrote:
>> On Mon, Apr 21, 2014 at 6:18 AM, Neville Dastur wrote:
>>> Google only showed up that UNIQUE needs to be added regardless of the
>>> column being a PRIMARY KEY.
>> 
>> Google is wrong.  The UNIQUE is superfluous.  PRIMARY KEY always
>> implies UNIQUE.  Always.
> 
> PRIMARY KEY also is supposed to imply NOT NULL.  However, for historical
> reasons, SQLite allows NULL in PRIMARY KEY columns if the column is not
> INTEGER, not explicitly NOT NULL, and not in a WITHOUT ROWID table.

Does that mean that I should really explicitly state NOT NULL for non-integer 
primary keys?

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

Re: [sqlite] INSERT OR REPLACE

[Andy Goth]

On 4/21/2014 6:04 AM, Richard Hipp wrote:

On Mon, Apr 21, 2014 at 6:18 AM, Neville Dastur wrote:

Google only showed up that UNIQUE needs to be added regardless of the
column being a PRIMARY KEY.


Google is wrong.  The UNIQUE is superfluous.  PRIMARY KEY always
implies UNIQUE.  Always.


PRIMARY KEY also is supposed to imply NOT NULL.  However, for historical
reasons, SQLite allows NULL in PRIMARY KEY columns if the column is not
INTEGER, not explicitly NOT NULL, and not in a WITHOUT ROWID table.

So you can't just go with SQL standards; you have to check the SQLite
documentation.  http://www.sqlite.org/lang_createtable.html

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

Re: [sqlite] Bug with FTS3 parenthesis and ICU (2)

[Stadin, Benjamin]

Hi David,

I overlooked your email at first. Thanks for your analysis.

I thought that doing the quick fix wasn¹t enough for my purpose. So I
replaced the tokenization part icuNext().

I hope backwards compatibility will not be an issue for this fix.

Regards
Ben

static int icuNext(
  sqlite3_tokenizer_cursor *pCursor,
  const char **ppToken,
  int *pnBytes,
  int *piStartOffset,
  int *piEndOffset,
  int *piPosition
){
  IcuCursor *pCsr = (IcuCursor *)pCursor;
  int iStart = ubrk_current(pCsr->pIter);
  int  iEnd = iStart;
  int breakType = 0;
  UChar32 c = 0;

  /* Search for token, skipping punctuation "boundary" tokens */
while (iStart != UBRK_DONE) {
iEnd = ubrk_next(pCsr->pIter);
breakType = ubrk_getRuleStatus(pCsr->pIter);
if (breakType != UBRK_WORD_NONE) {
break;
}
iStart = iEnd;
}

if( iStart==UBRK_DONE ){
return SQLITE_DONE;
}

/* Now we have a token. But it still may contain word boundary
characters that we don't like, e.g: ( ) */
int isBoundary = 0;
do {
isBoundary = pCsr->aChar[iStart] == '(' || pCsr->aChar[iStart] ==
')' || pCsr->aChar[iStart] == ' ' || pCsr->aChar[iStart] == ':';
if (!isBoundary) {
break;
}
U16_NEXT(pCsr->aChar, iStart, pCsr->nChar, c);
} while (iStart < iEnd);

/* We couldn't find any character that is not a boundary up to the end
of the text. Done. */
if( isBoundary ){
return SQLITE_DONE;
}

int limit = iEnd;
iEnd = iStart;
do {
isBoundary = pCsr->aChar[iEnd] == '(' || pCsr->aChar[iEnd] == ')'
|| pCsr->aChar[iEnd] == ' ' || pCsr->aChar[iEnd] == ':';
if (isBoundary) {
break;
}
U16_NEXT(pCsr->aChar, iEnd, pCsr->nChar, c); // Note: U16_PREV not
working here
} while (iEnd < limit);

  assert(iStart<=iEnd);

  int nByte = 0;
  do {
UErrorCode status = U_ZERO_ERROR;
if( nByte ){
  char *zNew = sqlite3_realloc(pCsr->zBuffer, nByte);
  if( !zNew ){
return SQLITE_NOMEM;
  }
  pCsr->zBuffer = zNew;
  pCsr->nBuffer = nByte;
}

u_strToUTF8(
pCsr->zBuffer, pCsr->nBuffer, ,
>aChar[iStart], iEnd-iStart,

);
  } while( nByte>pCsr->nBuffer );

  *ppToken = pCsr->zBuffer;
  *pnBytes = nByte;
  *piStartOffset = pCsr->aOffset[iStart];
  *piEndOffset = pCsr->aOffset[iEnd];
  *piPosition = pCsr->iToken++;

  return SQLITE_OK;
}




Am 13.04.14 22:07 schrieb "David Hedley" unter :

>This is definitely a bug in sqlite. I have experienced it too.
>
>The problem stems from ³getNextToken(Š)² expecting to find the
>parentheses in the token delimiters (rather than the tokens themselves).
>The ICU tokenizer returns the parentheses as tokens, rather than ignoring
>them as delimiters as the simple tokenizer does.
>
>Two possible fixes:
>1. Fix getNextToken(...) to look in tokens as well as delimiters for
>parentheses
>2. Fix icuNext to not return parentheses as tokens.
>
>To me, option 1. seemed easier to do a quick hack to, until there is an
>official fix.
>
>In getNextToken, I changed:
>if (rc == SQLITE_DONE) iStart = n;
>for (i = 0; i < iStart i++) {
>if (z[i] == '(') {
>
>to:
>
>if (rc == SQLITE_DONE) iStart = n;
>for (i = 0; i < iEnd; i++) { // 2014-04-12 DCRH:
>Tweak to make parens work with ICU tokenizer
>if (z[i] == '(') {
>
>That way, it now searches the token text in addition to the preceding
>delimiters, and parentheses now work correctly with the ICU tokenizer.
>
>Hope this helps,
>
>David
>-- 
>David Hedley
>CTO
>Vistair Systems Ltd
>Mobile: +44 (0)7971 681088
>Tex: 0845 VISTAIR (8478247) / +44 1454 616531
>Fax: 0870 1350992
>-- 
>Information in this electronic mail message is confidential and may be
>legally privileged. It is intended solely for the addressee. Access to
>this message by anyone else is unauthorised. If you are not the intended
>recipient any use, disclosure, copying or distribution of this message is
>prohibited and may be unlawful. When addressed to our customers, any
>information contained in this message is subject to Vistair Systems Ltd
>Terms and Conditions.
>
>Vistair Systems Ltd is registered in England and Wales #5418081
>
>
>
>___
>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] Linux :Custom build and locking

[Alain Meunier]

Many thanks Simon.

Yes the custom build is in its own dir and the apps are linked to it in the 
code.

I am happy again :D

See you

> From: slav...@bigfraud.org
> Date: Mon, 21 Apr 2014 16:37:15 +0100
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Linux :Custom build and locking
> 
> 
> On 21 Apr 2014, at 2:03pm, Alain Meunier  wrote:
> 
> > I was discussing about the latest sqlite3 version and in the discussion 
> > came the statement that one should not use a custom builds out of the 
> > system one
> 
> SQLite is not a centralised system and isn't wired into the OS at any deep 
> level.  Programmers are meant to include the SQLite source code (.h and .c 
> files) in their own program.  Each programmer should be able to grab whatever 
> version is current when they write their program, include that version in 
> their own program, and there should be no problem with this.
> 
> There are things to avoid, however.  One is that you should avoid replacing 
> the version of SQLite someone else's program uses with another version.  So 
> if, for example, HappyFunApp calls a DLL which includes SQLite version 3.2.1 
> don't replace that DLL because HappyFunApp might depend on a bug in SQLite 
> version 3.2.1 which has been fixed in SQLite version 3.3.3.
> 
> So if Debian includes a DLL and parts of the OS call it, then you shouldn't 
> replace that DLL.  Because some part of Debian might depend on behaviour of 
> whatever version of SQLite that DLL includes.
> 
> But that doesn't cause you a problem.  Grab an up-to-date version of SQLite 
> from the web site, or download or make your own DLL which includes an 
> up-to-date version of SQLite.  Leave the original DLL alone, and keep the new 
> DLL in one of your own folders.  As long as your program doesn't try and open 
> the same database files that the OS is opening, you're okay.  Since SQLite 
> does not involve any centralised server, you could have ten different 
> versions in use at the same time on the same computer without problems.  I 
> once did a scan of an OS X Server computer (fresh installation, no apps 
> added) and found that it included, I think, four different versions of 
> SQLite, each one used by a different network service or part of the OS.
> 
> >  because of access locking/collision.
> 
> This is a puzzling thing.  As far as I'm aware, the protocol used in the 
> "access locking/collision" code in SQLite has not been changed in many years. 
>  If your program can successfully open the database then you're fine.
> 
> 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] Linux :Custom build and locking

[Simon Slavin]

On 21 Apr 2014, at 2:03pm, Alain Meunier  wrote:

> I was discussing about the latest sqlite3 version and in the discussion came 
> the statement that one should not use a custom builds out of the system one

SQLite is not a centralised system and isn't wired into the OS at any deep 
level.  Programmers are meant to include the SQLite source code (.h and .c 
files) in their own program.  Each programmer should be able to grab whatever 
version is current when they write their program, include that version in their 
own program, and there should be no problem with this.

There are things to avoid, however.  One is that you should avoid replacing the 
version of SQLite someone else's program uses with another version.  So if, for 
example, HappyFunApp calls a DLL which includes SQLite version 3.2.1 don't 
replace that DLL because HappyFunApp might depend on a bug in SQLite version 
3.2.1 which has been fixed in SQLite version 3.3.3.

So if Debian includes a DLL and parts of the OS call it, then you shouldn't 
replace that DLL.  Because some part of Debian might depend on behaviour of 
whatever version of SQLite that DLL includes.

But that doesn't cause you a problem.  Grab an up-to-date version of SQLite 
from the web site, or download or make your own DLL which includes an 
up-to-date version of SQLite.  Leave the original DLL alone, and keep the new 
DLL in one of your own folders.  As long as your program doesn't try and open 
the same database files that the OS is opening, you're okay.  Since SQLite does 
not involve any centralised server, you could have ten different versions in 
use at the same time on the same computer without problems.  I once did a scan 
of an OS X Server computer (fresh installation, no apps added) and found that 
it included, I think, four different versions of SQLite, each one used by a 
different network service or part of the OS.

>  because of access locking/collision.

This is a puzzling thing.  As far as I'm aware, the protocol used in the 
"access locking/collision" code in SQLite has not been changed in many years.  
If your program can successfully open the database then you're fine.

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

Re: [sqlite] BLOBs and NULLs

[James K. Lowden]

On Mon, 21 Apr 2014 13:30:15 +
"Drago, William @ MWG - NARDAEAST"  wrote:

> Should I split this table up into smaller tables to eliminate the
> NULLs (e.g. use one table each for IL, Phase, RL, Isolation)? I'm not
> sure what the best design choice would be.

While Dr. Hipp's answer focussed on correctness and performance.  From
the logical side I suggest you consider four separate tables. 

Your database design is a model of the real world.  The rules it
enforces should reflect those of the world it models. The tuple (IL,
Phase, RL, Isolation) doesn't have much meaning, does it, in the sense
that *together* they say something special about whatever (GUID, Path)
represent?  From your description, each individual tuple (e.g., (GUID,
Path, IL)) is meaningful, but the presence of, say, IL without Phase is
not.  Each is a separate, freestanding fact, justifying its own table.  

The part I like least about your design (1) the use of a GUID and, in
particular (2) the name "GUID" for the column.  If you are generating
this GUID, don't; use an integer.  If you're not generating it -- if it
comes to you from another source and therefore identifies something in
"the real world" in some sense, OK.  Either way, use the name of the
column to reflect the thing identified, not the datatype of the
identifier.  

HTH.  

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

Re: [sqlite] BLOBs and NULLs

[Kees Nuyt]

On Mon, 21 Apr 2014 13:30:15 +, "Drago, William @ MWG - NARDAEAST"
 wrote:

> Should I split this table up into smaller tables to
> eliminate the NULLs (e.g. use one table each for IL,
> Phase, RL, Isolation)?

Adding to what Richard said:

(3) NULLs are not a problem by themselves, they take hardly any storage
at all, ust the type indicator that every every row has for every
column.

-- 
Groet, Cordialement, Pozdrawiam, Regards,

Kees Nuyt

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

Re: [sqlite] Windows Phone 8.1

[Andrew Arnott]

I found this, which offers a private build of a Universal compatible
version of SQLite.
http://timheuer.com/blog/archive/2014/04/17/universal-windows-apps-nuget-sdk-references-sqlite.aspx

--
Andrew Arnott
"I [may] not agree with what you have to say, but I'll defend to the death
your right to say it." - S. G. Tallentyre


On Mon, Apr 21, 2014 at 6:59 AM, Andrew Arnott wrote:

> +1.
>
> Windows Phone 8.1 (Appx) is a new platform. It's much closer to the WinRT
> than it is to Windows Phone 8.x (Silverlight). So I suspect the closest
> matching SQLite SDK would be the existing WinRT one. But as to how to make
> it available to add as an SDK Reference to WinPhone 8.1 Appx projects I
> don't know. I was hoping to find the answer here.
>
> --
> Andrew Arnott
> "I [may] not agree with what you have to say, but I'll defend to the death
> your right to say it." - S. G. Tallentyre
>
>
> On Fri, Apr 18, 2014 at 5:50 PM, Ryan Finnesey  wrote:
>
>> Hi Lane
>>
>> I was wondering if you have found a solution.  I am running into the same
>> issue with Azure Mobile Services and a Windows Phone 8.1 App.
>>
>> Cheers
>> Ryan
>>
>>
>> -Original Message-
>> From: sqlite-users-boun...@sqlite.org [mailto:
>> sqlite-users-boun...@sqlite.org] On Behalf Of Lane Williams
>> Sent: Sunday, April 13, 2014 4:53 PM
>> To: sqlite-users@sqlite.org
>> Subject: [sqlite] Windows Phone 8.1
>>
>> I have used the SQLite on several Windows Store and Windows Phone
>> projects including the latest version 3.8.4.3 on a Windows Phone 8 project
>> from VS 2013, they all work Great.
>>
>> However the 3.8.4.3 version will not recognize in my latest Windows Phone
>> 8.1 project.  I am trying to use the new "Universal Apps" method in VS
>> 2013, the Windows 8.1 will load (3.8.4.3) but the Windows Phone 8.1 does
>> not show the SQLite as an option to load.
>>
>> Is an update in the works to support the latest Windows Phone 8.1
>> platform.  Is there a suggested work around for using the current version
>> of SQLite with Windows Phone 8.1.
>>
>> Thanks,
>> Lane
>> ___
>> 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] Windows Phone 8.1

[Andrew Arnott]

+1.

Windows Phone 8.1 (Appx) is a new platform. It's much closer to the WinRT
than it is to Windows Phone 8.x (Silverlight). So I suspect the closest
matching SQLite SDK would be the existing WinRT one. But as to how to make
it available to add as an SDK Reference to WinPhone 8.1 Appx projects I
don't know. I was hoping to find the answer here.

--
Andrew Arnott
"I [may] not agree with what you have to say, but I'll defend to the death
your right to say it." - S. G. Tallentyre


On Fri, Apr 18, 2014 at 5:50 PM, Ryan Finnesey  wrote:

> Hi Lane
>
> I was wondering if you have found a solution.  I am running into the same
> issue with Azure Mobile Services and a Windows Phone 8.1 App.
>
> Cheers
> Ryan
>
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:
> sqlite-users-boun...@sqlite.org] On Behalf Of Lane Williams
> Sent: Sunday, April 13, 2014 4:53 PM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] Windows Phone 8.1
>
> I have used the SQLite on several Windows Store and Windows Phone projects
> including the latest version 3.8.4.3 on a Windows Phone 8 project from VS
> 2013, they all work Great.
>
> However the 3.8.4.3 version will not recognize in my latest Windows Phone
> 8.1 project.  I am trying to use the new "Universal Apps" method in VS
> 2013, the Windows 8.1 will load (3.8.4.3) but the Windows Phone 8.1 does
> not show the SQLite as an option to load.
>
> Is an update in the works to support the latest Windows Phone 8.1
> platform.  Is there a suggested work around for using the current version
> of SQLite with Windows Phone 8.1.
>
> Thanks,
> Lane
> ___
> 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] BLOBs and NULLs

[Richard Hipp]

On Mon, Apr 21, 2014 at 9:30 AM, Drago, William @ MWG - NARDAEAST <
william.dr...@l-3com.com> wrote:

> All,
>
> One of the tables in my database has 4 columns that will hold small (under
> 5K) BLOBs. In many cases there will be no data at all in one or more of
> these columns (see sample below). Does this present any kind of problem?


No.

Two things to be aware of:

(1) When reading a row, SQLite reads from beginning to end.  So if you have
some small integer or boolean fields, it is better to put them first in the
table. Otherwise, SQLite has to read past the big BLOBs in order to get to
the smaller fields, even if the BLOBs themselves are not used.

(2) When changing any column of a row, the entire row is rewritten,
including the unchanged columns.  So if you have some smaller fields
(integers and booleans) that change frequently and also some large BLOBs
that change infrequently, you might consider factoring the BLOBs out into a
separate table just so they don't have to be rewritten every time a boolean
in the same row changes.

Both points above a purely performance considerations.  You should always
get the correct answer either way.


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

[sqlite] BLOBs and NULLs

[Drago, William @ MWG - NARDAEAST]

All,

One of the tables in my database has 4 columns that will hold small (under 5K) 
BLOBs. In many cases there will be no data at all in one or more of these 
columns (see sample below). Does this present any kind of problem? Should I 
split this table up into smaller tables to eliminate the NULLs (e.g. use one 
table each for IL, Phase, RL, Isolation)? I'm not sure what the best design 
choice would be.

Here is a sample of what the data will look like:

GUIDPathIL  Phase   RL  Isolation
9a778c0e1   BLOBBLOBBLOBNULL
9a778c0e2   BLOBBLOBBLOBNULL
9a778c0e3   BLOBBLOBBLOBNULL
9a778c0e4   BLOBBLOBBLOBNULL
9a778c0e5   NULLNULLBLOBNULL
9a778c0e6   BLOBBLOBBLOBNULL
9a778c0e7   BLOBBLOBBLOBNULL
9a778c0e8   BLOBBLOBBLOBNULL
9a778c0e9   BLOBBLOBBLOBNULL
9a778c0e10  NULLNULLNULLBLOB
9a778c0e11  NULLNULLNULLBLOB
9a778c0e12  NULLNULLNULLBLOB
9a778c0e13  NULLNULLNULLBLOB
9a778c0e14  NULLNULLNULLBLOB
9a778c0e15  NULLNULLNULLBLOB
9a778c0e16  NULLNULLNULLBLOB
9a778c0e17  NULLNULLNULLBLOB
9a778c0e18  NULLNULLNULLBLOB
9a778c0e19  NULLNULLNULLBLOB
23239d6b1   BLOBBLOBBLOBNULL
23239d6b2   BLOBBLOBBLOBNULL
23239d6b3   BLOBBLOBBLOBNULL
23239d6b4   BLOBBLOBBLOBNULL
23239d6b5   NULLNULLBLOBNULL
23239d6b6   BLOBBLOBBLOBNULL
23239d6b7   BLOBBLOBBLOBNULL
23239d6b8   BLOBBLOBBLOBNULL
23239d6b9   BLOBBLOBBLOBNULL
23239d6b10  NULLNULLNULLBLOB
23239d6b11  NULLNULLNULLBLOB
23239d6b12  NULLNULLNULLBLOB
23239d6b13  NULLNULLNULLBLOB
23239d6b14  NULLNULLNULLBLOB
23239d6b15  NULLNULLNULLBLOB
23239d6b16  NULLNULLNULLBLOB
23239d6b17  NULLNULLNULLBLOB
23239d6b18  NULLNULLNULLBLOB
23239d6b19  NULLNULLNULLBLOB


Thanks for your help...
--
Bill Drago
Senior Engineer

L3 Communications / Narda Microwave East
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / william.dr...@l-3com.com

CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by reply e-mail and 
immediately delete this message and any attachments.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Linux :Custom build and locking

[Alain Meunier]

Hello,

I was discussing about the latest sqlite3 version and in the discussion came 
the statement that one should not use a custom builds out of the system one 
because of access locking/collision.

I would understand a bit more about that. I grabbed the latest (and greatest) 
to get cte's.

I run debian stable.

Is there something I should know before it breaks on my face ?
Is it referring to the fact that custom builds cannot manage a queue of 
multiple writers to avoid collision ?

Well I would like some opinions please,

Thanks,

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

Re: [sqlite] Bug report: column name includes table alias when CTE is used

[Richard Hipp]

On Mon, Apr 21, 2014 at 1:16 AM, Andre  wrote:

> Hi,
>
> Apparently when a CTE is used, the column name includes the table alias.
> However, when no CTE is used, the alias is not present in the returned
> column name.
>
> SQLite version 3.8.4.3 2014-04-03 16:53:12
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> create table X (columnA int);
> sqlite> insert into X values (1);
> sqlite> .header on
> sqlite> select alias.columnA from X alias;
> *columnA*
> 1
> sqlite> with CTE as (select columnA from X) select alias.columnA from CTE
> alias;
> *alias.columnA*
> 1
> sqlite>
>
> I experienced this when rewriting a query to use CTE in an application that
> based some logic on the column name. I'd expect not to see the alias either
> way. Is this a bug or is it expected for CTEs?
>

See http://www.sqlite.org/c3ref/mark/column_name.html?If+there+is+n*fiedfor
further information.

-- 
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] Bug Report: Quotes added to table name after ALTER TABLE ... RENAME TO

[Richard Hipp]

On Mon, Apr 21, 2014 at 4:36 AM, Assen Totin  wrote:

> Hi, everybody,
>
> Found something which seems rather inconsistent and may be a bug, hence
> reporting it here.
>
> Running a query to rename a table succeeds, but the name of the renamed
> table is surrounded by double quotes.
>
> sqlite> .schema
> CREATE TABLE version (version INT);
> sqlite> ALTER TABLE version RENAME TO version2
> sqlite> .schema
> CREATE TABLE "version2" (version INT);
>

That is correct behavior.  Any identifier in SQL can be enclosed in
double-quotes.



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

[sqlite] Bug Report: Quotes added to table name after ALTER TABLE ... RENAME TO

[Assen Totin]

Hi, everybody,

Found something which seems rather inconsistent and may be a bug, hence
reporting it here.

Running a query to rename a table succeeds, but the name of the renamed
table is surrounded by double quotes.

sqlite> .schema
CREATE TABLE version (version INT);
sqlite> ALTER TABLE version RENAME TO version2
sqlite> .schema
CREATE TABLE "version2" (version INT);

It is interesting that the .tables command show the name without the quotes:

sqlite> .tables
version2

My SQLite is:

[root@archimed src]# sqlite3 --version
3.8.4.2 2014-03-26 18:51:19 02ea166372bdb2ef9d8dfbb05e78a97609673a8e

The problems seems to be with libsqlite, because the same issue occurs when
using libsqlite from a C program (code is simplified for clarity, but in
reality raises no error):

#include 
#define DB_FILE_NAME "/tmp/some_db.sqlite";
sqlite3 *sqlite;
char *zErrMsg = 0;
sqlite3_open(DB_FILE_NAME, );
sqlite3_exec(sqlite, "ALTER TABLE version RENAME TO version2",
some_callback, 0, );
sqlite3_free(zErrMsg);
sqlite3_close(sqlite);

Similar report abut 2 years old when using sqlite on Android - only worked
around and never fixed:
http://rfobasic.freeforums.org/sqlite-alter-table-t876.html

Any suggestions how to remedy this issue are welcome.

WWell,

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

Re: [sqlite] INSERT OR REPLACE

[Neville Dastur]

Sorry, Simon’s post help track down the problem.

SELECT hospitals_id, length(hospitals_id), typeof(hospitals_id) FROM hospitals;

Showed nulls were being inserted into hospitals_id. Bitten by the documented 
NULLs aren’t unique bug/feature thing. 

And thank you for clarifying about UNIQUE
 
Neville

On 21 Apr 2014, at 12:04, Richard Hipp  wrote:

> On Mon, Apr 21, 2014 at 6:18 AM, Neville Dastur 
> wrote:
> 
>> I seem to have a strange problem.
>> 
>> I am using the INSERT OR REPLACE INTO syntax on a Sqlite DB running in iOS
>> simulator 7.1 (not exactly sure which sqlite version that is)
>> 
>> I have a column defined as hospitals_id TEXT PRIMARY KEY UNIQUE which
>> stores a GUID. Hence the text primary key. When I do an INSERT OR REPLACE
>> the rows are duplicated.
>> 
> 
> Can you send a test case? Are you certain that the GUIDs really are
> identical?
> 
> 
> 
>> 
>> Google only showed up that UNIQUE needs to be added regardless of the
>> column being a PRIMARY KEY.
> 
> 
> Google is wrong.  The UNIQUE is superfluous.  PRIMARY KEY always implies
> UNIQUE.  Always.
> 
> -- 
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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] INSERT OR REPLACE

[Richard Hipp]

On Mon, Apr 21, 2014 at 6:18 AM, Neville Dastur wrote:

> I seem to have a strange problem.
>
> I am using the INSERT OR REPLACE INTO syntax on a Sqlite DB running in iOS
> simulator 7.1 (not exactly sure which sqlite version that is)
>
> I have a column defined as hospitals_id TEXT PRIMARY KEY UNIQUE which
> stores a GUID. Hence the text primary key. When I do an INSERT OR REPLACE
> the rows are duplicated.
>

Can you send a test case? Are you certain that the GUIDs really are
identical?



>
> Google only showed up that UNIQUE needs to be added regardless of the
> column being a PRIMARY KEY.


Google is wrong.  The UNIQUE is superfluous.  PRIMARY KEY always implies
UNIQUE.  Always.

-- 
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] INSERT OR REPLACE

[Simon Slavin]

On 21 Apr 2014, at 11:18am, Neville Dastur  wrote:

> I have a column defined as hospitals_id TEXT PRIMARY KEY UNIQUE which stores 
> a GUID. Hence the text primary key. When I do an INSERT OR REPLACE the rows 
> are duplicated.

I want to check that these keys really are duplicates and don't just look like 
they're duplicates.

Copy your database to your computer and open it with the SQLite Shell Tool (or 
write some code to do these tests.  Then try

SELECT hospitals_id, length(hospitals_id), typeof(hospitals_id) FROM hospitals;

SELECT hospitals_id, typeof(hospitals_id), COUNT(*) FROM hospitals GROUP BY 
hospitals_id;

SELECT hospitals_id, typeof(hospitals_id), COUNT(*) FROM hospitals GROUP BY 
hospitals_id HAVING (COUNT(*) > 1);

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

[sqlite] INSERT OR REPLACE

[Neville Dastur]

I seem to have a strange problem.

I am using the INSERT OR REPLACE INTO syntax on a Sqlite DB running in iOS 
simulator 7.1 (not exactly sure which sqlite version that is)

I have a column defined as hospitals_id TEXT PRIMARY KEY UNIQUE which stores a 
GUID. Hence the text primary key. When I do an INSERT OR REPLACE the rows are 
duplicated.

Google only showed up that UNIQUE needs to be added regardless of the column 
being a PRIMARY KEY. But that hasn’t helped. So any pointers would be 
appreciated.

Neville


--
Surgeons Net Education: http://www.surgeons.org.uk
Clinical Software Solutions: http://www.clinsoftsolutions.com
Find our free and paid apps on the iTunes Apple store and Android Google Play 
store
LinkedIn: http://www.linkedin.com/profile/view?id=49617062








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