Re: [sqlite] Single large table vs. several smaller tables

[Drago, William @ MWG - NARDAEAST]

Don,

Thanks for weighing in on this. I'm going to experiment with the single table 
solution and the 3 table solution below and see where I end up. The cream 
always rises to the top.

Thanks to all who replied to this thread,

-Bill



CREATE TABLE UUT(
DatasetID INTEGER PRIMARY KEY,
Location TEXT,  --Factory location.
ModelNumber TEXT,
SerialNumber TEXT,
TestDate TEXT,
ATEVersion TEXT,
CalStatus TEXT, --Pass or Fail.
Par0 TEXT   --Sent to EEPROM.
);

CREATE TABLE CalData(
DatasetID INTEGER REFERENCES UUT,
Frequency TEXT, --Either LF or HF.
vOffset REAL,   --Voltage with RF Off.
v10 REAL,   --Voltage at 10% Std.
v20 REAL,
v50 REAL,
v100 REAL,
v200 REAL   --Voltage at 200% Std.
);

CREATE TABLE Stimulus(
DatasetID INTEGER REFERENCES UUT,
Frequency TEXT, --Either LF or HF.
Target REAL,--dBm, 100% Std.
Source REAL,--dBm, Source output.
Pind  REAL  --dBm, as displayed on power meter.
);

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



> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Don V Nielsen
> Sent: Thursday, October 09, 2014 12:21 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Single large table vs. several smaller tables
>
> I suggest you group columns into a structures that you are comfortable
> with.  I have a name, own a home, and have one car.  Everything is
> singular to me, an individual.  So if I have a table Individuals, do I
> want 25 columns that encompass name, address, year, make, and model of
> my car, type of home, how many windows it has?  At some point, even if
> the information is 1 to 1, it makes sense to segregate related data
> into a table and join it.
>
> I don't really understand your particular data.  But if you can
> rationalize that these pieces of data are best written on a 3x5 note
> card by itself, then create a table and join them.  It's not all about
> efficiency.
>
> just my opinion
>
> On Thu, Oct 9, 2014 at 11:03 AM, Drago, William @ MWG - NARDAEAST <
> william.dr...@l-3com.com> wrote:
>
> > Clemens,
> >
> > That's 24 columns per unit, not rows. There's no duplicate
> information.
> >
> > Avoiding joins is something I considered. Thank you for confirming
> > what I was thinking.
> >
> > -Bill
> >
> >
> > > -Original Message-
> > > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> > > boun...@sqlite.org] On Behalf Of Clemens Ladisch
> > > Sent: Thursday, October 09, 2014 10:46 AM
> > > To: sqlite-users@sqlite.org
> > > Subject: Re: [sqlite] Single large table vs. several smaller tables
> > >
> > > Drago, William @ MWG - NARDAEAST wrote:
> > > > An automatic test system that I designed generates 25 data
> > > > elements for each unit tested.  [...] should I lump everything
> > > > together in one table just like the .csv file or should I create
> > > > several smaller tables that group similar parameters?
> > > > I'm not sure what would normally be done. I think the database is
> > > > normalized properly in either case.
> > >
> > > When you have 24 rows for each unit, this sounds as if the unit
> > > information is duplicated.  You have to decide if it would make
> > > sense to have a separate table for units.
> > >
> > > Splitting up for "similar" parameters makes sense only when this
> > > similarity has an effect on your queries, i.e., if it would be
> > > easier to write "SELECT * FROM LFCal".  That's unlikey if you also
> > > have to do a join with UTT.
> > >
> > > It might make sense to do the split as an optimization, but only if
> > > the amount of data in the combined table were large enough to
> > > overwhelm your computer.  This does not appear to be the case.
> > >
> > >
> > > > CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail
> > > > and
> > > ...
> > >
> > > This e-mail contains public information intended for any subscriber
> > > of this mailing list and for anybody else who bothers to read it;
> it
> > > will be copied, disclosed and distributed to the public.  If you
> > > think you are not the intended recipient, please commit suicide
> immediately.
> > > These 

Re: [sqlite] Single large table vs. several smaller tables

[Drago, William @ MWG - NARDAEAST]

> > The question I have is, should I lump everything together in one
> table just like the .csv file or should I create several smaller tables
> that group similar parameters? I'm not sure what would normally be
> done. I think the database is normalized properly in either case.
>
> For SQLite, except in exceptional cases such as huge (multi terabyte)
> databases or slow media, it is more efficient to have one big table
> rather than several smaller tables.

Good to know. Since this is a small, low volume database I'm more concerned 
with organizational clarity and correctness than efficiency.

>
> At a first glance, when I see two tables with identical column
> definitions, I tend to feel that they should be merged into one table
> with one additional column.

That was my gut feeling. I could combine even further by using just 4 columns, 
but I thought the code might be less complicated by keeping them separate:

ID
Frequency   (either HF or LF)
VName   (Offset, v10...V200)
MeasuredVoltage (actual recorded value)

Thanks for your reply, Simon.

-Bill
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] Single large table vs. several smaller tables

[Drago, William @ MWG - NARDAEAST]

Clemens,

That's 24 columns per unit, not rows. There's no duplicate information.

Avoiding joins is something I considered. Thank you for confirming what I was 
thinking.

-Bill


> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Clemens Ladisch
> Sent: Thursday, October 09, 2014 10:46 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Single large table vs. several smaller tables
>
> Drago, William @ MWG - NARDAEAST wrote:
> > An automatic test system that I designed generates 25 data elements
> > for each unit tested.  [...] should I lump everything together in one
> > table just like the .csv file or should I create several smaller
> > tables that group similar parameters?
> > I'm not sure what would normally be done. I think the database is
> > normalized properly in either case.
>
> When you have 24 rows for each unit, this sounds as if the unit
> information is duplicated.  You have to decide if it would make sense
> to have a separate table for units.
>
> Splitting up for "similar" parameters makes sense only when this
> similarity has an effect on your queries, i.e., if it would be easier
> to write "SELECT * FROM LFCal".  That's unlikey if you also have to do
> a join with UTT.
>
> It might make sense to do the split as an optimization, but only if the
> amount of data in the combined table were large enough to overwhelm
> your computer.  This does not appear to be the case.
>
>
> > CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and
> ...
>
> This e-mail contains public information intended for any subscriber of
> this mailing list and for anybody else who bothers to read it; it will
> be copied, disclosed and distributed to the public.  If you think you
> are not the intended recipient, please commit suicide immediately.
> These terms apply also to any e-mails quoted in, referenced from, or
> answering this e-mail, and supersede any disclaimers in those e-mails.
> Additionally, disclaimers in those e-mails will incur legal processing
> fees of $42 per line; you have agreed to this by reading this
> disclaimer.
>
>
> Regards,
> Clemens
> ___
> 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] Single large table vs. several smaller tables

[Drago, William @ MWG - NARDAEAST]

Ryan,

That's exactly the kind of information I was looking for. Thank you.

-Bill

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of RSmith
> Sent: Thursday, October 09, 2014 10:45 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Single large table vs. several smaller tables
>
> There is no point to grouping similar fields in different tables,
> though I would advise grouping them together in the main table as a
> simple case for clarity, but it has no other benefit. As long as the
> data is in 1NF, one table is fine. The main reason (and I think only
> motivation) to have data in seperate tables is if some column or set of
> columns repeats the very same information over and over, or if you have
> multiple-linking of sub data items to a main index - none of which
> seems to be the case in your table, all the data is needed and possibly
> quite different for every test/entry.
>
> Another reason people might use different tables is when you have large
> text or blobs that might make querying a bit faster if the main table
> has a simple smaller fieldset and then references another table 1-to-1
> when retrieving the very large data - again, seemingly not the case for
> you.
>
> Any other breaking up of a table is unneeded complication - if the data
> belongs together, keep it together - though if someone else has a
> thought on why it is reasonable to split up such data, I'd be
> interested to hear it too.
>
> Have a great day!
> Ryan
>
>
> On 2014/10/09 16:25, Drago, William @ MWG - NARDAEAST wrote:
> > All,
> >
> > An automatic test system that I designed generates 25 data elements
> for each unit tested. We test about 50 units/day. This data is
> currently being stored in a csv file. I'd like to move this over to an
> SQLite database.
> >
> > The question I have is, should I lump everything together in one
> table just like the .csv file or should I create several smaller tables
> that group similar parameters? I'm not sure what would normally be
> done. I think the database is normalized properly in either case.
> >
> > Existing records will never be modified once they are inserted. We
> will occasionally read the database to gather statistical information.
> >
> > I'd like to get your input before I commit to anything that I might
> regret later.
> >
> > Thanks,
> > -Bill
> >
> >
> > ** Single table **
> >
> > CREATE TABLE UUT(
> >  DatasetID INTEGER PRIMARY KEY,
> >  Location TEXT,
> >  ModelNumber TEXT,
> >  SerialNumber TEXT,
> >  TestDate TEXT,
> >  ATE TEXT,
> >  CalStatus TEXT,
> >  Par0 TEXT,
> >  LFvOffset REAL,
> >  LFv10 REAL,
> >  LFv20 REAL,
> >  LFv50 REAL,
> >  LFv100 REAL,
> >  LFv200 REAL,
> >  HFvOffset REAL,
> >  HFv10 REAL,
> >  HFv20 REAL,
> >  HFv50 REAL,
> >  HFv100 REAL,
> >  HFv200 REAL,
> >  LFTarget REAL,
> >  LFSource REAL,
> >  LFPind REAL,
> >  HFTarget REAL,
> >  HFSource REAL,
> >  HFPind REAL
> > );
> >
> >
> >
> > ** Multiple Tables **
> >
> > CREATE TABLE UUT(
> >  DatasetID INTEGER PRIMARY KEY,
> >  Location TEXT,
> >  ModelNumber TEXT,
> >  SerialNumber TEXT,
> >  TestDate TEXT,
> >  ATE TEXT,
> >  CalStatus,
> >  Par0,
> > );
> >
> > CREATE TABLE LFCal(
> >  DatasetID INTEGER REFERENCES UUT,
> >  vOffset REAL,
> >  v10 REAL,
> >  v20 REAL,
> >  v50 REAL,
> >  v100 REAL,
> >  v200 REAL
> > );
> >
> > CREATE TABLE HFCal(
> >  DatasetID INTEGER REFERENCES UUT,
> >  vOffset REAL,
> >  v10 REAL,
> >  v20 REAL,
> >  v50 REAL,
> >  v100 REAL,
> >  v200 REAL
> > );
> >
>

[sqlite] Single large table vs. several smaller tables

[Drago, William @ MWG - NARDAEAST]

All,

An automatic test system that I designed generates 25 data elements for each 
unit tested. We test about 50 units/day. This data is currently being stored in 
a csv file. I'd like to move this over to an SQLite database.

The question I have is, should I lump everything together in one table just 
like the .csv file or should I create several smaller tables that group similar 
parameters? I'm not sure what would normally be done. I think the database is 
normalized properly in either case.

Existing records will never be modified once they are inserted. We will 
occasionally read the database to gather statistical information.

I'd like to get your input before I commit to anything that I might regret 
later.

Thanks,
-Bill


** Single table **

CREATE TABLE UUT(
DatasetID INTEGER PRIMARY KEY,
Location TEXT,
ModelNumber TEXT,
SerialNumber TEXT,
TestDate TEXT,
ATE TEXT,
CalStatus TEXT,
Par0 TEXT,
LFvOffset REAL,
LFv10 REAL,
LFv20 REAL,
LFv50 REAL,
LFv100 REAL,
LFv200 REAL,
HFvOffset REAL,
HFv10 REAL,
HFv20 REAL,
HFv50 REAL,
HFv100 REAL,
HFv200 REAL,
LFTarget REAL,
LFSource REAL,
LFPind REAL,
HFTarget REAL,
HFSource REAL,
HFPind REAL
);



** Multiple Tables **

CREATE TABLE UUT(
DatasetID INTEGER PRIMARY KEY,
Location TEXT,
ModelNumber TEXT,
SerialNumber TEXT,
TestDate TEXT,
ATE TEXT,
CalStatus,
Par0,
);

CREATE TABLE LFCal(
DatasetID INTEGER REFERENCES UUT,
vOffset REAL,
v10 REAL,
v20 REAL,
v50 REAL,
v100 REAL,
v200 REAL
);

CREATE TABLE HFCal(
DatasetID INTEGER REFERENCES UUT,
vOffset REAL,
v10 REAL,
v20 REAL,
v50 REAL,
v100 REAL,
v200 REAL
);

CREATE TABLE LFPowerLevels(
DatasetID INTEGER REFERENCES UUT,
Target REAL,
Source REAL,
Pind
);

CREATE TABLE HFPowerLevels(
DatasetID INTEGER REFERENCES UUT,
Target REAL,
Source REAL,
Pind
);

--
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

Re: [sqlite] Reinstalling My Build Environment

[Drago, William @ MWG - NARDAEAST]

That was an excellent response, Joe. I think Paul Bainter gave up way too 
quickly and his conclusion that System.Data.SQLite is unsupported is 
unwarranted, but I can understand if he felt alienated by comments like:

"You may have found a library built on SQLite...Whatever you found isn't 
SQLite."

And:

"Whatever package you are attempting to install...SQLite has no direct 
responsibilities to your IDE."

It seems like a lot of people are unaware of System.Data.Sqlite. This is 
understandable since most people on this list are C programmers not .NET/C#/VB 
developers, but this is the support list for System.Data.Sqlite as well as 
SQLite, isn't it? Shouldn't we all be aware that anyone asking for help with 
Visual Studio and/or EF6 and/or referencing file names like 
sqlite-netFx451-setup-bundle-x86-2013-1.0.92.0.exe, is asking about 
System.Data.Sqlite?

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


> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Joe Mistachkin
> Sent: Thursday, September 11, 2014 11:19 PM
> To: 'General Discussion of SQLite Database'
> Subject: Re: [sqlite] Reinstalling My Build Environment
>
>
> I assume that you did not see the response I sent earlier?
>
>   http://www.mail-archive.com/sqlite-
> users%40sqlite.org/msg85999.html
>
> --
> Joe Mistachkin
>
> ___
> 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] Reinstalling My Build Environment

[Drago, William @ MWG - NARDAEAST]

> Please help me.  At this point, I'm desperate for an installation where
> I can copy the compiled files from the bin directory to a clean machine
> and have it run.


I use VS2013, but I don't use EF6, so this may not work for you, but maybe it 
will help in some way anyway.

Based on what you describe (targeting .NET 4.51 and not having the C++ runtime 
installed) I imagine that the folder structure and content on your target 
machines should look like this:

\yourApp.exe
\System.Data.SQLite.dll  (From 
sqlite-netFx451-static-binary-Win32-2013-1.0.93.0.zip)
\System.Data.SQLite.EF6.dll  (From 
sqlite-netFx451-static-binary-Win32-2013-1.0.93.0.zip)
\System.Data.SQLite.Linq.dll (From 
sqlite-netFx451-static-binary-Win32-2013-1.0.93.0.zip)
\x86\SQLite.Interop.dll  (From 
sqlite-netFx451-static-binary-Win32-2013-1.0.93.0.zip)
\x64\SQLite.Interop.dll  (From 
sqlite-netFx451-static-binary-x64-2013-1.0.93.0.zip)

(Note that the last file above comes from a different .zip file than the 
others.)

***Do NOT run any installers or setups, just take the needed files from the 
.zips***

Get .zip files here: 
https://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki


Try that on a clean machine and see what happens.

BTW, my folder structure/content is exactly the same only without EF6 and Linq, 
and it works fine for me.

Good luck,
--
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

Re: [sqlite] Simple SQLite-based spreadsheet?

[Drago, William @ MWG - NARDAEAST]

I use the personal edition of this: http://www.sqliteexpert.com/

And I use this as well because the built-in graph is handy: 
http://sqlitebrowser.org/

--
Bill Drago
Senior Engineer

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


> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Gilles Ganault
> Sent: Friday, August 29, 2014 5:09 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Simple SQLite-based spreadsheet?
>
> On Fri, 7 Dec 2012 22:05:47 -0500, John Gabriele 
> wrote:
> >Just checked stackoverflow and found this:
> >https://addons.mozilla.org/en-US/firefox/addon/sqlite-manager/ . Might
> >be what you're after.
>
> Thanks. I'll try it, but judging from the screenshots, but it's
> probably harder than using Excel.
>
> ___
> 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

[sqlite] CREATE INDEX Placement

[Drago, William @ MWG - NARDAEAST]

All,

Does it matter if I group all of my CREATE INDEX statements at the end of my 
.sql file or should they appear right after each CREATE TABLE statement? Does 
SQLITE care one way or another?

Thanks,
--
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] What NuGet package adds VS design-time components?

[Drago, William @ MWG - NARDAEAST]

All,

I'd like to generate a database diagram from an SQLite database. Is this 
possible with one of the packages that install the Visual Studio design-time 
components?

If so which NuGet package is it? I'm going to guess it's EF6 (x86/x64) package, 
but I want to be sure before I do anything.

If this is not possible in Visual Studio are there any third party tools 
available?

Thanks,
--
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

Re: [sqlite] SQL Join question

[Drago, William @ MWG - NARDAEAST]

Excellent explanation. Thank you!

-Bill

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Simon Slavin
> Sent: Monday, July 28, 2014 6:20 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] SQL Join question
>
>
> On 28 Jul 2014, at 9:10pm, Drago, William @ MWG - NARDAEAST
>  wrote:
>
> > Can someone tell me what the purpose of line 2 is in the following
> example? It seems redundant to me since what is wanted from the
> Customers table is specified on line 1.
> >
> >
> > 1: SELECT Customers.CustomerName, Orders.OrderID
> > 2: FROM Customers
> > 3: INNER JOIN Orders
> > 4: ON Customers.CustomerID=Orders.CustomerID
> > 5: ORDER BY Customers.CustomerName;
> >
> > The above example was found here:
> >
> > http://www.w3schools.com/sql/sql_join_inner.asp
>
> First and most important, a "FROM" clause is required for every
> "SELECT" command [1].  So the statement, whatever it's meant to do,
> definitely needs to be "FROM" something.  There's only two tables used
> in the example, so it would have to be "FROM Customers" or "FROM
> Orders".
>
> The "FROM" clause tells you which table forms the lines of the result.
> So if you see "FROM Customers" then you expect each row of the result
> to be referring to one Customer, with as many rows returned as there
> are Customers.  The alternative would be a SELECT where each row of the
> result referred explicitly to one Order, with as many rows returned as
> there are Orders.
>
> The difference comes to the fore when the two numbers are different.
> So imagine a case where you have three customers who have placed,
> between them, ten orders.  Do you want your SELECT to return three rows
> or ten rows ?  That tells you which table should be named in the "FROM"
> clause.
>
> Simon.
>
> [1] Okay, SQLite does a few useful things without it.  Minor
> exceptions.
> ___
> 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

[sqlite] SQL Join question

[Drago, William @ MWG - NARDAEAST]

All,

Can someone tell me what the purpose of line 2 is in the following example? It 
seems redundant to me since what is wanted from the Customers table is 
specified on line 1.


1: SELECT Customers.CustomerName, Orders.OrderID
2: FROM Customers
3: INNER JOIN Orders
4: ON Customers.CustomerID=Orders.CustomerID
5: ORDER BY Customers.CustomerName;

The above example was found here:

http://www.w3schools.com/sql/sql_join_inner.asp


Thanks,
--
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

Re: [sqlite] Preferred cast in C#

[Drago, William @ MWG - NARDAEAST]

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Simon Slavin
> Sent: Tuesday, July 15, 2014 2:35 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Preferred cast in C#
>
>
> On 15 Jul 2014, at 6:42pm, Edward Ned Harvey (sqlite)
>  wrote:
>
> > In C#, using the System.Data.Sqlite.Core package, which is described
> as "The official SQLite database engine" and published by "SQLite
> Development Team"
>
> That's Joe's code.  You probably got that off of git or nuget.org.

It's also available on the download page here:

http://system.data.sqlite.org/

and if you don't have it already, the help file is here:

http://system.data.sqlite.org/index.html/doc/trunk/Doc/SQLite.NET.chm?mimetype=application/x-chm

-Bill


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] capturing and testing a hot journal

[Drago, William @ MWG - NARDAEAST]

This may be a bit simplistic, but it does give me a reasonable degree of 
confidence that hot journal files are being handled correctly in my application.

I simply put a 1/0 on the line before my commit to purposely crash my app. Sure 
enough there's a journal file after the crash (I have a rather large 
transaction consisting of among other things, about 35 rows inserted, each 
containing a blob).

When I restart my app it looks for the presence of a journal file and will open 
and read the db so that SQLite can deal with it. It also displays a message 
letting the user know that something went wrong during the last run.

I do this with a test db of course, not the real one.

-Bill




> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Charles Parnot
> Sent: Saturday, July 12, 2014 4:38 AM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] capturing and testing a hot journal
>
> Hi all,
>
> For testing purposes of our application (a Mac app), I am generating
> what I thought would be a database with a "hot" journal using this
> approach (on an existing database):
>
> - open the database (and PRAGMA journal_mode = TRUNCATE;)
> - open a transaction: BEGIN IMMEDIATE TRANSACTION;
> - add some rows: INSERT etc...
> - **make a copy of the db and journal files** (while still hot?)
> - close the transaction
>
> Then I open the copied database+journal (naming the files
> appropriately), again in TRUNCATE journal mode. As expected, the
> content of the database does not include the inserted rows. However,
> the journal file is not emptied, even after closing the database. Based
> on the documentation
> (http://www.sqlite.org/lockingv3.html#hot_journals), I would have
> expected the journal file to be emptied because it is "hot".
>
> There are 2 options here:
>
> - the journal file is actually not "hot" and I misunderstood the
> conditions that make it hot
> - there is a bug in SQLite
>
> Obviously, I strongly suspect I am misunderstanding things, and don't
> think it is an SQLite bug. Despite intensive Google-ing and more
> testing, I am not sure what makes the journal non-hot.
>
> Thanks for your help!
>
> Charles
>
>
> NB: You might be wondering why I am doing the above. I realize SQLite
> has already much more advanced tests for "hot" db+journals (running
> custom versions of filesystems to generate all kind of edge cases). The
> test case I am generating is just for a simple edge case of our
> Dropbox-based syncing (see: https://github.com/cparnot/PARStore and
> http://mjtsai.com/blog/2014/05/21/findings-1-0-and-parstore/). For a
> given database file, there is only one device that can write to it, all
> other devices being read-only (not in terms of filesystem, but sqlite-
> wise). But it is possible that Dropbox will copy a database and journal
> files that are not consistent with each other, which can create
> problems. For instance, maybe a read-only device could try to open the
> (still old) database with a new non-empty journal file and sqlite would
> empty that journal file, then Dropbox could in turn empty the journal
> file before the writer client had finished the transaction. I am not
> (yet) going to test for and try to protect against more complicated
> (and rarer) edge cases where the database is in the middle of writing a
> transaction (which I suspect will only happen in case of crashes, not
> because of Dropbox, in which case the recovery of the database by the
> read-only client would actually be beneficial).
>
> --
> Charles Parnot
> charles.par...@gmail.com
> http://app.net/cparnot
> twitter: @cparnot
>
> Your Lab Notebook, Reinvented.
> http://findingsapp.com
>
> ___
> 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] Basic SQLite/EF6 question

[Drago, William @ MWG - NARDAEAST]

Steven Davisworth wrote:

> Hi Joe
> I've just upgraded PC to 64bit (new PC) and installed VS2013. I've
> followed standard install instructions as outlined in web posts I've
> come across for
> EF6.

Is there an official, or at least known good, set of instructions somewhere 
that we can use rather than relying on various web posts (which may or may not 
be complete/valid/accurate, etc)?

-Bill
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] SQLite and BLOBs

[Drago, William @ MWG - NARDAEAST]

Whether to use internal or external blobs is application dependant. In my test 
& measurement application testing 1 widget generates 140 small (1.2kB to 14kB) 
blobs. Multiply that by hundreds of widgets and you can see that storing and 
managing these as individual files would be a big headache compared to the 
simplicity of putting them in the database. These blobs will never be modified, 
filtered, searched, etc.. It makes perfect sense to keep them in the database.

So, none of what is said below applies to my application, but it may apply to 
others. Also, read this:

http://www.sqlite.org/intern-v-extern-blob.html

-Bill

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Carlos Ferreira
> Sent: Monday, June 23, 2014 10:52 AM
> To: 'General Discussion of SQLite Database'
> Subject: [sqlite] SQLite and BLOBs
>
> Hello,
>
> I found this reference in this link:
>
> http://stackoverflow.com/questions/676524/how-to-update-piecewise-a-
> blob-in-sqlite
>
>
> "
> This is not directly an answer to your question, but I have some
> experience using random access for (big) blobs in SQLite, and I advise
> you against using it, if you can. Here's why:
>
> Blobs break the SQL query format entirely. If your blob data needs any
> kind of processing, it will certainly at some point need filtering.
> Whatever mechanism you have in place to deal with filtering in SQL will
> be useless in this regard.
>
> Dealing with binary blobs wrapped in databases opposed to binary data
> in raw files limits your options. You will not be able to randomly read
> and write to data at the same time from multiple processes, which is
> possible with files. You can't use any tool that deals with this data
> and provides only a file I/O interface. You can't truncate or resize
> the blob. Files are simply a lot more versatile.
>
> It may seem convenient to have everything contained within one file, as
> it simplifies backup and transfer, but the pain of working with blobs
> is simply not worth it.
>
> So as your attorney, I advise you to write your blobs as raw files to
> the file system, and merely store a reference to the filename in your
> database. If your blobs are rather small and guaranteed not to grow,
> forget my advice.
> "
>
> Do you agree with this information?
>
> Regards.
>
> Carlos
>
>
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Dan Kennedy
> Sent: segunda-feira, 23 de Junho de 2014 15:26
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Planner chooses incorrect index
>
> On 06/23/2014 05:48 AM, João Ramos wrote:
> > Here you go:
> >
> > sqlite_stat1 (before - good planning)
> > HistoryEntry idx_HistoryEntry_uri_historyOrder 14992 44 5
> HistoryEntry
> > idx_HistoryEntry_historyOrder_historyTimestamp 14992 6 2 HistoryEntry
> > idx_HistoryEntry_sourceType_sourceId 14992 2999 2
> >
> > sqlite_stat1 (after - bad planning)
> > HistoryEntry idx_HistoryEntry_uri_historyOrder 15492 45 5
> HistoryEntry
> > idx_HistoryEntry_historyOrder_historyTimestamp 15492 5 2 HistoryEntry
> > idx_HistoryEntry_sourceType_sourceId 15492 3099 2
>
> Unfortunately the last column of the sqlite_stat4 data is missing,
> likely because it contains embedded 0x00 bytes. And without the
> sqlite_stat4 data, it seems that SQLite picks the "good" query plan in
> either case.
>
> Can you dump the sqlite_stat1 and sqlite_stat4 tables using the shell
> ".dump" command instead?
>
> Thanks,
> Dan.
>
>
>
> >
> > sqlite_stat4 (before - good planning)
> > HistoryEntry idx_HistoryEntry_uri_historyOrder 366 340 1 78 78 81 17
> > 17 81 HistoryEntry idx_HistoryEntry_uri_historyOrder 722 489 1 566
> 566
> > 661 38 96
> > 661
> > HistoryEntry idx_HistoryEntry_uri_historyOrder 25 25 1 1650 1650 1665
> > 56
> > 352 1665
> > HistoryEntry idx_HistoryEntry_uri_historyOrder 862 747 1 2569 2569
> > 2899 106
> > 447 2899
> > HistoryEntry idx_HistoryEntry_uri_historyOrder 862 1 1 2569 3331 3331
> > 106
> > 462 3331
> > HistoryEntry idx_HistoryEntry_uri_historyOrder 2026 1789 1 3825 3825
> > 4997 131
> > 660 4997
> > HistoryEntry idx_HistoryEntry_uri_historyOrder 143 113 1 6178 6178
> > 6232 154
> > 931 6232
> > HistoryEntry idx_HistoryEntry_uri_historyOrder 181 180 1 6412 6412
> > 6477 162
> > 974 6477
> > HistoryEntry idx_HistoryEntry_uri_historyOrder 2 2 1 6663 6663 6663
> > 169 984
> > 6663
> > HistoryEntry idx_HistoryEntry_uri_historyOrder 1251 988 1 6953 6953
> > 7488 186
> > 1062 7488
> > HistoryEntry idx_HistoryEntry_uri_historyOrder 379 370 1 8260 8260
> > 8329 195
> > 1361 8329
> > HistoryEntry idx_HistoryEntry_uri_historyOrder 135 92 1 8915 8915
> 8965
> > 218
> > 1439 8965
> > HistoryEntry idx_HistoryEntry_uri_historyOrder 196 194 1 9128 9128
> > 9129 227
> > 1501 9129
> > HistoryEntry idx_HistoryEntry_uri_historyOrder 280 170 1 9622 9622
> > 9650 237
> > 1579 9650
> > HistoryEntry idx_HistoryEntry_uri_historyO

Re: [sqlite] Threads and System.Data.SQLite

[Drago, William @ MWG - NARDAEAST]

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Simon Slavin
> Sent: Tuesday, June 03, 2014 8:07 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Threads and System.Data.SQLite
>
>
> On 3 Jun 2014, at 11:05pm, Drago, William @ MWG - NARDAEAST
>  wrote:
>
> > "You May create multiple threads, and those threads can create their
> own SQLiteConnection and subsequent objects for accessing a database.
> Multiple connections on multiple threads to the same database file are
> perfectly acceptable and will behave predictably."
> >
> > So I don't have to lock the section of code that writes to the
> database like I do when I have multiple threads writing to a .csv file?
>
> Probably easiest if you read this then post again if you still have
> questions.
>
> <http://www.sqlite.org/threadsafe.html>
>
> Simon.

I read that too and it just seemed too easy, and too good to be true. That's 
why I asked for confirmation here.

My threads are humming along nicely...

Thank you,
-Bill

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] Threads and System.Data.SQLite

[Drago, William @ MWG - NARDAEAST]

All,

In the Provider Limitations section of the SQLite.NET help file it says:

"You May create multiple threads, and those threads can create their own 
SQLiteConnection and subsequent objects for accessing a database.  Multiple 
connections on multiple threads to the same database file are perfectly 
acceptable and will behave predictably."

So I don't have to lock the section of code that writes to the database like I 
do when I have multiple threads writing to a .csv file?

Thanks,
--
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

Re: [sqlite] New DLLs and sources. Was: SQLite version 3.8.5 beta

[Drago, William @ MWG - NARDAEAST]

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Warren Young
> Sent: Wednesday, May 28, 2014 2:26 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] New DLLs and sources. Was: SQLite version 3.8.5
> beta
>
> On 5/28/2014 11:20, jose isaias cabrera wrote:
> >
> > I would rather have the speed
> > then the size.
>
> Many years ago, I read an article written by a Microsoft employee where
> they said they built Windows' own binaries optimized for size rather
> than speed, since in today's L1/L2/L3 world, size *is* speed.
>
> Bigger code gets kicked out of the processor cache faster, so the
> processor has to go back to main memory more often.

Don't modern compilers consider what effects the speed optimizations will have 
on the pipeline and the cache and optimize accordingly?

-Bill




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] DELETE & INSERT vs. REPLACE

[Drago, William @ MWG - NARDAEAST]

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Simon Slavin
> Sent: Tuesday, May 27, 2014 5:21 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] DELETE & INSERT vs. REPLACE
>
>
> On 27 May 2014, at 9:12pm, Drago, William @ MWG - NARDAEAST
>  wrote:
>
> > Let me rephrase the question by asking, how should I overwrite
> existing data? For example, Jane has 5 guitars in her instrument table.
> She trades her 5 guitars for 5 new ones. Almost everything about the
> new guitars is different from the old guitars.
> >
> > My gut tells me I should simply delete where instrument_type =
> 'guitar' and then insert the new data.
>
> Go with your gut.  Since there's no relationship between the old and
> new guitars it would be just as likely that Jane would replace 5
> guitars with 4 guitars, having sold two cheap ones and bought one
> expensive one.  You should not be using REPLACE, you should be using
> DELETE, then INSERT, with the correct number of each type of command.
>
> Of course, unless your entire databases is about Jane's guitars that's
> not a good table to create.  You would be more likely to have a
> database about everything Jane owns, or a database about all the
> guitars lots of people owns.

Thank you. That's good news and I don't have to change any of my code.

Can someone tell me what an appropriate use for REPLACE is?

-Bill
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] DELETE & INSERT vs. REPLACE

[Drago, William @ MWG - NARDAEAST]

Yes, very interesting. I didn't realize that the pk would auto-increment on a 
replace.

-Bill

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Petite Abeille
> Sent: Tuesday, May 27, 2014 3:01 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] DELETE & INSERT vs. REPLACE
>
>
> On May 27, 2014, at 8:11 PM, Drago, William @ MWG - NARDAEAST
>  wrote:
>
> > Is there any difference between using REPLACE as opposed to deleting
> records and then inserting new ones to take their place?
>
> Same difference.
>
> For example:
>
> create table foo
> (
>   id  integer primary key not null,
>   key text not null,
>
>   constraint foo_uk unique( key )
> );
>
> sqlite> insert or replace into foo( key ) values( 'a' ); select * from
> sqlite> foo;
> 1|a
> sqlite> insert or replace into foo( key ) values( 'a' ); select * from
> sqlite> foo;
> 2|a
> sqlite> insert or replace into foo( key ) values( 'a' ); select * from
> sqlite> foo;
> 3|a
>
> Note how the primary key, id, had changed over time, from 1 to 3.
>
> For all practical purposes, REPLACE is useless, if not dangerous even.
>
> What would really be useful would be a MERGE operation instead:
>
> http://en.wikipedia.org/wiki/Merge_%28SQL%29
> ___
> 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] DELETE & INSERT vs. REPLACE

[Drago, William @ MWG - NARDAEAST]

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Richard Hipp
> Sent: Tuesday, May 27, 2014 2:15 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] DELETE & INSERT vs. REPLACE
>
> On Tue, May 27, 2014 at 2:11 PM, Drago, William @ MWG - NARDAEAST <
> william.dr...@l-3com.com> wrote:
>
> > All,
> >
> > Is there any difference between using REPLACE as opposed to deleting
> > records and then inserting new ones to take their place?
> >
>
> REPLACE only deletes records when it is absolutely necessary to
> preserve UNIQUE constraints on an insert.  As long as you restrict your
> deletes to records that would otherwise cause a uniqueness conflict,
> then the result will be the same.


There are no uniqueness constraints on this table.

Let me rephrase the question by asking, how should I overwrite existing data? 
For example, Jane has 5 guitars in her instrument table. She trades her 5 
guitars for 5 new ones. Almost everything about the new guitars is different 
from the old guitars.

My gut tells me I should simply delete where instrument_type = 'guitar' and 
then insert the new data. If there's a better way (i.e. healthier for the 
database) then I'd like to know. In my real world automatic test application 
things like will happen very infrequently, but they will occasionally happen. 
And, if it matters, this is a very low volume database. No more than a dozen or 
so inserts/reads per day.

I'd like to keep the database healthy by not doing inefficient things to it and 
I'm new to both SQLite and SQL. That's why I'm asking this question.

Thanks,
-Bill

> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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

[sqlite] DELETE & INSERT vs. REPLACE

[Drago, William @ MWG - NARDAEAST]

All,

Is there any difference between using REPLACE as opposed to deleting records 
and then inserting new ones to take their place?

Thanks,
--
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] My first hot journal, what do I do with it?

[Drago, William @ MWG - NARDAEAST]

All,

Well, it finally happened. I had a program crash and was left with a hot 
journal file. A while back there was a post by Richard Hipp detailing something 
that could/should be done before trying to use the database to assure that 
SQLite does the rollback. I can't find that post now.

Any suggestions?

Thanks,
--
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

Re: [sqlite] SQLite Datareader problems with Int?

[Drago, William @ MWG - NARDAEAST]

I'm willing to bet that the problem is in your C# code. The variable receiving 
the INTEGER column is probably declared as an INT instead of LONG or ULONG.

-Bill

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Werner Kleiner
> Sent: Thursday, May 15, 2014 7:08 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] SQLite Datareader problems with Int?
>
> Simon Slavin-3 wrote
> >  There is no need to do anything special.  f you use 'Int' in SQLite
> > it will be interpreted as 'INTEGER' anyway.
>
> Yes, but back to my datareader problem it seems that the Datareader
> differs between a column which is 'INTEGER' or 'Int'.
> Especially we had a problem with a Int column and value of 13 digits
> (1396856032225). The datareader reads the column as a integer 32 value
> and breaks or returns wrong values. (or we do something wrong :-) )
>
>
>
>
> --
> View this message in context:
> http://sqlite.1065341.n5.nabble.com/SQLite-Datareader-problems-with-
> Int-tp75670p75684.html
> Sent from the SQLite mailing list archive at Nabble.com.
> ___
> 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] duplicate row in sqlite3 database

[Drago, William @ MWG - NARDAEAST]

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of RSmith
> Sent: Friday, May 09, 2014 2:01 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] duplicate row in sqlite3 database
>
> That sounds NOTHING like a violation error... it seems a lot more like
> the intermediate thing failed on communicating, but I am not familiar
> with it (I hope another poster might have advice) - unless it simply
> does not understand the serialized output. Either way it doesn't feel
> right to me.

It only happens when I try it insert a duplicate row, otherwise things are 
working very well. It doesn't feel right to me either, but this is not unusual 
for VEE. VEE is a large interpreted environment. There is so much going on 
between the user and the cpu that error codes are sometimes obfuscated to the 
point that you see here. I just have to assume that an error on INSERT is due 
to a duplicate row. There's nothing else I can do.

>
>
> On 2014/05/09 19:52, Drago, William @ MWG - NARDAEAST wrote:
> > When I deliberately try to insert a duplicate row I get the following
> error from my VEE* environment:
> >
> > Execution of method ExecuteNonQuery in the referenced .NET object or
> type failed.
> > Exception type: System.Runtime.Serialization.SerializationException
> > Type is not resolved for member 'System.Data.SQLiteException,
> System.Data.Sqlite, Version=1.0.92...'
> >
> > If that sounds like a duplicate row error, I'll trap it and display a
> message to the user.
> >
> > -Bill
> >
> > *About VEE:
> > http://cp.literature.agilent.com/litweb/pdf/5990-9117EN.pdf
> >
> >
>
> ___
> 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] duplicate row in sqlite3 database

[Drago, William @ MWG - NARDAEAST]

When I deliberately try to insert a duplicate row I get the following error 
from my VEE* environment:

Execution of method ExecuteNonQuery in the referenced .NET object or type 
failed.
Exception type: System.Runtime.Serialization.SerializationException
Type is not resolved for member 'System.Data.SQLiteException, 
System.Data.Sqlite, Version=1.0.92...'

If that sounds like a duplicate row error, I'll trap it and display a message 
to the user.

-Bill

*About VEE: http://cp.literature.agilent.com/litweb/pdf/5990-9117EN.pdf


> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of RSmith
> Sent: Friday, May 09, 2014 12:06 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] duplicate row in sqlite3 database
>
>
> On 2014/05/09 17:58, Drago, William @ MWG - NARDAEAST wrote:
> >> -Original Message-
> >> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> >> boun...@sqlite.org] On Behalf Of Igor Tandetnik
> >> Sent: Thursday, May 08, 2014 9:48 AM
> >> To: sqlite-users@sqlite.org
> >> Subject: Re: [sqlite] duplicate row in sqlite3 database
> >>
> >> On 5/8/2014 5:14 AM, techi eth wrote:
> >>> SQlite3 have any method where it can avoid adding duplicate row or
> >>> throwing error status code on duplication.,
> >> Define a UNIQUE constraint or a unique index on those columns you
> >> don't want duplicated. Then an attempt to insert a duplicate row
> >> would result in an error.
> > An error as in program crash or an error code?
> >
> >
> A returned error code which you can use to look up the meaning of
> (there are many possible things that might cause an error). If the
> meaning turns out to be a failed constraint, then you know why the
> Insert did not complete.
>
> If you do not particularly care for specifically the constraint
> failure, but just want the row inserted if it does not violate the
> constraint, then one of two ways can be used, defining an ON CONFLICT
> clause in the schema or more generally used, the SQL syntax "INSERT OR
> IGNORE INTO xxx... " which is much the same as INSERT with an ON
> CONFLICT IGNORE clause... details of all this can be found on the
> conflict docs in the sqlite.org site.
>
> http://www.sqlite.org/conflict.html
> http://www.sqlite.org/lang_conflict.html
> http://www.sqlite.org/lang_insert.html
>
> ___
> 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] duplicate row in sqlite3 database

[Drago, William @ MWG - NARDAEAST]

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Igor Tandetnik
> Sent: Thursday, May 08, 2014 9:48 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] duplicate row in sqlite3 database
>
> On 5/8/2014 5:14 AM, techi eth wrote:
> > SQlite3 have any method where it can avoid adding duplicate row or
> > throwing error status code on duplication.,
>
> Define a UNIQUE constraint or a unique index on those columns you don't
> want duplicated. Then an attempt to insert a duplicate row would result
> in an error.

An error as in program crash or an error code?

> --
> Igor Tandetnik
>
> ___
> 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

[sqlite] Could not open registry key

[Drago, William @ MWG - NARDAEAST]

All,

I tried running the installer in 
sqlite-netFx20-binary-bundle-Win32-2005-1.0.92.0.zip and received the following 
error:

Installer.exe: #047 @ 2014.05.07T16:29:59.4744965: TraceOps.ShowMessage: could 
not open registry key: 
HKEY_LOCAL_MACHINE\Software\Microsoft\.NETFramework\v2.0.50727\AssemblyFoldersEx

Anyone have any idea why this is happening?

Thanks,
--
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

Re: [sqlite] Is there a single file version of System.Data.SQLite?

[Drago, William @ MWG - NARDAEAST]

Thank you for the suggestion. There are 2 issues:

1: I tried:

Assembly SampleAssembly = Assembly.Load("System.Data.SQLite, Version=1.0.92.0, 
Culture=neutral, PublicKeyToken=db937bc2d44ff139")

The assembly loads, but I couldn't figure out how to use it. None of the SQLite 
classes are in SampleAssembly, just a handful of things that have nothing to do 
with SQLite. Please forgive my .NET ignorance; I don't have a lot of experience 
with it.


2: This is all for naught anyway because when I transfer my program to the 
network the assembly won't load at all ("Unverifiable code failed policy 
check"). I'm not the only one with this problem:
http://www.codeproject.com/Questions/428056/Exception-Details-System-IO-FileLoadException-Unve


So, it looks like I'm stuck using the GAC and having to install SQLite in every 
computer that runs my software.

Thanks anyway...

-Bill


> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Joe Mistachkin
> Sent: Tuesday, May 06, 2014 5:56 PM
> To: 'General Discussion of SQLite Database'
> Subject: Re: [sqlite] Is there a single file version of
> System.Data.SQLite?
>
>
> Drago, William @ MWG - NARDAEAST wrote:
> >
> > Is there a way to use the "System.Data.SQLite.dll" mixed-mode
> assembly
> outside
> > of the GAC? Or is there a 32 bit only single file version of SQLite?
> >
>
> Sure, you should be able to load the mixed-mode assembly from an
> arbitrary location using the LoadFrom() method, as seen here:
>
>   http://msdn.microsoft.com/en-
> us/library/1009fa28%28v=vs.110%29.aspx
>
> Alternatively, if the mixed-mode assembly is located in the same
> directory as the application binary itself, it may be possible to use
> the Load() method, as seen here:
>
>   http://msdn.microsoft.com/en-
> us/library/ky3942xh%28v=vs.110%29.aspx
>
> --
> Joe Mistachkin
>
> ___
> 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

[sqlite] Is there a single file version of System.Data.SQLite?

[Drago, William @ MWG - NARDAEAST]

All,

Is there a way to use the "System.Data.SQLite.dll" mixed-mode assembly outside 
of the GAC? Or is there a 32 bit only single file version of SQLite?

I'm running a 32 bit, non-managed code interpreted language that can't find  
the SQLite.Interop.dll files if I start my program from a network drive (it 
works fine locally).

I've never needed the 64 bit interop .dll, so I'm wondering if there's a 
version of SQLite that is a single file .dll.

Thanks,
--
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

Re: [sqlite] When to open/close connections

[Drago, William @ MWG - NARDAEAST]

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Yuriy Kaminskiy
> Sent: Monday, April 28, 2014 8:36 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] When to open/close connections
>
> Simon Slavin wrote:
> > On 28 Apr 2014, at 11:11pm, RSmith  wrote:
> >
> >> Second approach is better when you rarely access the database, also
> it will make sure releases happen (or at least provide immediate errors
> if not), but keeping a connection open is much better when hundreds of
> accesses happen in terms of speed - especially loads of small queries,
> large queries won't matter either way.
> >
> > I add two other reasons to Ryan's: if you are extremely short of
> memory (for example in a tiny low-power device) or if your file system
> lacks proper flushing because although flush() is documented in reality
> it does nothing.
>
> s/flush/sync/.
> However, I don't see just how closing/opening db will make it any
> better.
> Database/journal is written and synced at transaction end, not at
> "connection"
> close.

Does closing the connection force, or at least encourage, the OS to write to 
disk whatever it might have been caching?


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] Documentation typo

[Drago, William @ MWG - NARDAEAST]

In section 2 of http://sqlite.org/foreignkeys.html the word "separately" 
appears twice in the following sentence:

Foreign key constraints are disabled by default (for backwards compatibility), 
so must be enabled separately for each database connection separately.

--
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

Re: [sqlite] foreign keys

[Drago, William @ MWG - NARDAEAST]

More information about this here:
http://sqlite.org/foreignkeys.html

-Bill

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Dominique Devienne
> Sent: Friday, April 25, 2014 4:31 AM
> To: olivier Ménard; General Discussion of SQLite Database
> Subject: Re: [sqlite] foreign keys
>
> On Fri, Apr 25, 2014 at 10:22 AM, olivier Ménard 
> wrote:
> > create table T(a primary key not null); create table U(a references T
> > not null); insert into U values(4)
>
> C:\Users\DDevienne>sqlite3
> 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> pragma foreign_keys = on;
> sqlite> create table T(a primary key not null); create table U(a
> sqlite> references T not null); insert into U values(4);
> Error: FOREIGN KEY constraint failed
> sqlite>
> ___
> 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] BLOBs and NULLs

[Drago, William @ MWG - NARDAEAST]

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Gerry Snyder
> Sent: Wednesday, April 23, 2014 2:36 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] BLOBs and NULLs
>
> On 4/23/2014 10:21 AM, Drago, William @ MWG - NARDAEAST wrote:
> >  If I was sure I wouldn't be merging data I might use timer ticks
> as my ID, but I'm not sure and I can't take the chance.
> >
> > -Bill
>
> Would it be possible to use INTEGER PRIMARY KEY AUTOINCREMENT for the
> ID, and manually start each test station at an ID value a billion
> larger than the previous one? Or whatever delta makes sense? Then
> collision could never happen.

Sure, but why make it so complicated? I understand the desire to save space, 
but geez, we're talking about a few extra bytes per row in a database that will 
be used for low volume widget testing on PC's with terabyte size disk drives. 
GUID vs. Int is not going to make a difference in this case.

> And, as a possible bonus, the ID would
> indicate which station the row came from.

I'm storing computer name and hard drive serial number, so that's already taken 
care of.

>
> Gerry
>
> ___
> 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] BLOBs and NULLs

[Drago, William @ MWG - NARDAEAST]

The possibility of having to merge data from several independent test stations 
is what made me think of using GUID in the first place. I don't think there's a 
better way.

As for wasted space, the few extra bytes needed by GUID is a drop in the bucket 
compared to the blobs I'm storing. If I was sure I wouldn't be merging data I 
might use timer ticks as my ID, but I'm not sure and I can't take the chance.

-Bill

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Stephan Beal
> Sent: Wednesday, April 23, 2014 11:51 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] BLOBs and NULLs
>
> On Wed, Apr 23, 2014 at 6:56 AM, Keith Medcalf 
> wrote:
>
> > You don't ever really need a GUID at all.  Simply use an "integer
> > primary key" (an integer starting at 1) and simply pretend that it is
> > being added to the applicable base GUID of your random choosing.
> > Everything will still be unique and you will have saved yourself a
> > crap load of storage space, index space, and conserved countless CPU
> > cycles so that they can be spent on something more productive
> productive.
> >
> > I have never seen a need to actually use a GUID for anything, it is a
> > ridiculous concept.
> >
>
> Until one implements a DCVS (i.e. Fossil), at which point sequential
> numbers become literally impossible to generate.
>
>
> --
> - stephan beal
> http://wanderinghorse.net/home/stephan/
> http://gplus.to/sgbeal
> "Freedom is sloppy. But since tyranny's the only guaranteed byproduct
> of those who insist on a perfect world, freedom will have to do." --
> Bigby Wolf ___
> 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] BLOBs and NULLs

[Drago, William @ MWG - NARDAEAST]

I can generate the GUID as a 16 byte hex string, so the dashes are no problem. 
I'm working with VEE (similar to LabView) and .Net, so a lot of the mundane 
stuff is done for me with nifty little methods like ToArray(), ToString(), etc..


-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of RSmith
Sent: Tuesday, April 22, 2014 1:57 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] BLOBs and NULLs


On 2014/04/22 19:12, Richard Hipp wrote:
> On Tue, Apr 22, 2014 at 1:07 PM, Drago, William @ MWG - NARDAEAST <
> william.dr...@l-3com.com> wrote:
>
>> Does blob ignore them if they are included?
>>
> No.  That would be a syntax error.  The dashes in (strict) GUIDs are
> an arbitrary construct (perhaps originally designed to promote
> readability - as if anybody ever actually reads a GUID).  If you want
> to store the GUID as a BLOB then you'll have to strip the dashes
> yourselve (in your
> application) and do the hex-to-binary conversion yourself.

Just to add  - I cannot imagine why you would have dashes to start with, do you 
have a GUID generator that pumps out TEXT or STRING values? Usually they should 
be accompanied (in most libraries) by a function that pops out the actual 
16/20/32-byte BLOB as a stream or array of bytes/integers/whatever, and it 
usually involves some cost to convert it to strings with dashes etc. - a nice 
optimisation if those are no longer needed.

If you DO only get them as string though, the web is full of little bits of 
conversion code, and we will be glad to supply you with some too if you say 
which environment, function, etc.










___
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] BLOBs and NULLs

[Drago, William @ MWG - NARDAEAST]

Does blob ignore them if they are included?

-Bill

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Richard Hipp
Sent: Tuesday, April 22, 2014 12:56 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] BLOBs and NULLs

On Tue, Apr 22, 2014 at 12:55 PM, Drago, William @ MWG - NARDAEAST < 
william.dr...@l-3com.com> wrote:

> Cool. So it's treating each 2 digit pair as a single byte hex value,
> but what does blob do with the dashes?
>

Since the dashes carry no information, you could leave them out.

--
D. Richard Hipp
d...@sqlite.org
___
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] BLOBs and NULLs

[Drago, William @ MWG - NARDAEAST]

Cool. So it's treating each 2 digit pair as a single byte hex value, but what 
does blob do with the dashes?


-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Dominique Devienne
Sent: Tuesday, April 22, 2014 11:55 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] BLOBs and NULLs

On Tue, Apr 22, 2014 at 5:35 PM, Drago, William @ MWG - NARDAEAST 
 wrote:
>>I myself prefer create table foo (guid blob primary key [NOT NULL], ...).
>
> If a genuine GUID looks like this: 37af1247-2e77-4880-8f46-48803ae2cd0a, then 
> why blob and not text?

Simply because of the extra space needed to store it. 36 bytes vs 16 bytes. 
That's 20 wasted bytes for the PK, and everytime that PK is references in other 
tables' FKs too. Times millions of rows, it adds up, for nothing. The GUID is 
no less "genuine" as you put it, just because it's stored as a 16-bytes blob 
rather than the canonical 36-char text preferred by humans. The native code 
guids, e.g.
boost::uuid, also use 16 bytes, so conversions to/from the DB
(binding/defining) would required bytes-to-text, and text-to-byte conversions, 
again for no gain. If we ever show a GUID to the user, which is rather rare 
(and often a bug), sure, we pretty-print it as dash-separated hex, but 
otherwise the most compact "native"
representation is used everywhere else. For ad-hoc queries using the 
shell-tool, SQLite's x'ff' blob-literal comes in handy too, and
quote() [1] is used for blob-to-hex conversions in selects (nothing needed in 
blob-to-blob joins). --DD

[1] 
http://stackoverflow.com/questions/1039461/how-to-display-blob-value-using-xabc-binary-string-literal-syntax
___
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] BLOBs and NULLs

[Drago, William @ MWG - NARDAEAST]

>I myself prefer create table foo (guid blob primary key [NOT NULL], ...).

If a genuine GUID looks like this: 37af1247-2e77-4880-8f46-48803ae2cd0a, then 
why blob and not text?

-Bill



-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Dominique Devienne
Sent: Tuesday, April 22, 2014 5:07 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] BLOBs and NULLs

On Mon, Apr 21, 2014 at 4:58 PM, James K. Lowden  
wrote:
> On Mon, 21 Apr 2014 13:30:15 +0000
> "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)?

> 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.

Pushed to its extreme, this sounds like an EAV* model, which seems surprising 
coming from you James ;)

* http://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80%93value_model

> 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.

First, about your GUID versus integer point (an often contentious debate), I'm 
on the (opposite) GUID side myself. And the reason is to increase concurrency 
and decrease contention. When you depend on a server-enforced AUTO-INCREMENTED 
key, you must go to the central server to get that unique id, which is a 
round-trip (in client-server scenarii), and sometimes you must also insert a 
row, before you potentially have all the facts (columns). A GUID on the other 
hand can be randomly generated client-side, completely independently of the 
server, and still be unique (since 128 bit, a very very large integer, and a 
good RNG). If you need to "merge" later tables or databases created 
independently, you won't have GUID conflicts, unlike AUTO-INCREMENTED integer 
PKs (the integers are used in FKs elsewhere, so any merge operation must map 
the old AUTO-INC PKs to the new AUTO-INC ones, and updates all FKs). So IMHO, a 
GUID PK is very appropropriate in many scenarii, as long as one remembers it i
 s only a Surrogate Key, i.e. a convenient fixed-sized, globally unique (and 
thus often immutable) single value to use in FKs (especially convenient in the 
face of complex composite natural keys), and not a Natural Key.

Second, the datatype is blob (or RAW(16) in Oracle), not GUID, and there's thus 
nothing wrong to call it what it is. Many people in the SQL community seem to 
like stuttering, and will call it foo_id or foo_uid or foo_guid for a foo 
table, but I myself prefer create table foo (guid blob primary key [NOT NULL], 
...).

It's mostly a matter of opinion and taste, but there are benefits to using 
GUIDs as SKs in some circumstances in my book. My $0.02. --DD

PS: For a long time, SQLite depended on that integer PK, whether one wanted it 
or not (and I wasn't aware saying PRIMARY KEY for a non-integer PK did not mean 
it's standard-required NOT NULL until reading a recent thread. I wish there was 
a single opt-in pragma to disable all non-standard behavior). Now there's 
WITOUT ROWID tables, but from reading this list, it seems like it forces you to 
a B-tree, instead of a B*-tree  (store rows in leafs only) like ROWID-based 
tables, which I believe means more expensive inserts via more B-tree 
rebalancing, which is especially bad with large (blob-using) rows, given SQLite 
lack of out-of-row blob storage.
___
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 
contr

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

[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] What does Installer.exe do in the binary packages?

[Drago, William @ MWG - NARDAEAST]

I just downloaded sqlite-netFx20-binary-Win32-2005-1.0.92.0.zip and in addition 
to the core SQLite .dll files there are a lot of other files including an 
installer.

This is a binary package, not a setup or bundle package, so what does the 
installer do?

I read the information on the download page and there is no information about 
installers.
http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki

BTW, SQLite works just fine for me without running the installer, I'm just 
wondering what it's there for and if there things that I'm missing out on by 
not running it.

Thanks,
--
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

Re: [sqlite] How to speed up a bulk import

[Drago, William @ MWG - NARDAEAST]

I do 12.3 million inserts with a single commit in approx 1 minute using 
prepared statements and parameters in accordance with the "Getting the best 
performance out of SQLite" section of the SQLite.NET.chm help file, which is 
available here:

http://system.data.sqlite.org/index.html/doc/trunk/Doc/SQLite.NET.chm?mimetype=application/x-chm

Below is my actual code. It's C#, but you should have no trouble doing the 
equivalent in VB.NET.

Good Luck,
-Bill

---START---
using System;
using System.IO;
using System.Data.SQLite;
using System.Diagnostics;

namespace Spurs
{
class Program
{
static void Main(string[] args)
{
/*
 * Spur database has the following structure:
 *
 * CREATE TABLE [spurs] (
 *[FundamentalFreq] FLOAT,
 *[SpurFreq] FLOAT,
 *[SpurdBm] FLOAT);
 *
 *
 * .CSV file is 12.3 million entries like this:
 * 1075.10,2150.449990,-49.192
 * 1075.10,2150.449990,-49.278
 * 1886.90,1887.197613,-54.701
 *
 * */

// Timer stuff
Stopwatch stopWatch = new Stopwatch();
TimeSpan insertTime, commitTime;
const string timePattern = "{0:00}:{1:00}:{2:00}.{3:00}";
string elapsedTime;

// File names
string fileName = "raw_data.csv";   // Input file name.
string dbFileName = "raw_data.db";  // Database file (output file) 
name.

// Input file read-loop variables
StreamReader myReader; // A reader to read the file.
int lineCount = 0; // Total number of lines in 
input file.
int reportInterval = 10;   // Update progress every 
reportInterval lines.
int intervalCount = 0; // Number of lines since last 
update report.
string line = "";  // Holds 1 line from file.
string[] lineCSV = new string[3];  // Holds the values from each 
line.

// Database variables
string connectionString;   // Database connection string.
string commandString;  // Database SQL command string.
connectionString = String.Format("Data Source={0}", dbFileName);
commandString = "INSERT INTO spurs VALUES (?, ?, ?)";

SQLiteConnection connection;   // Active database connection.
SQLiteTransaction transaction; // Active database transaction.
SQLiteCommand SQLCommand;  // Active database command.

SQLiteParameter @FundamentalFreq;  // UUT Test frequency (MHz).
SQLiteParameter @SpurFreq; // Frequency of largest spur 
(MHz).
SQLiteParameter @SpurAmp;  // Amplitude of largest spur 
(dBm).
@FundamentalFreq = new SQLiteParameter();
@SpurFreq = new SQLiteParameter();
@SpurAmp = new SQLiteParameter();

// Process .csv file.
System.Console.WriteLine("Reading file: " + fileName);
using (connection = new SQLiteConnection(connectionString))
{
using (SQLCommand = new SQLiteCommand(connection))
{
SQLCommand.CommandText = commandString;
SQLCommand.Parameters.Add(@FundamentalFreq);
SQLCommand.Parameters.Add(@SpurFreq);
SQLCommand.Parameters.Add(@SpurAmp);
connection.Open();
using (transaction = connection.BeginTransaction())
{
using (myReader = new StreamReader(fileName))
{
stopWatch.Start();
while ((line = myReader.ReadLine()) != null)
{
// Get values from one line in the .csv file.
lineCSV = line.Split(new char[] { ',' });
@FundamentalFreq.Value = 
double.Parse(lineCSV[0]);
@SpurFreq.Value = double.Parse(lineCSV[1]);
@SpurAmp.Value = double.Parse(lineCSV[2]);

// Insert them into the database.
SQLCommand.ExecuteNonQuery();

// Print progress every reportInterval lines.
lineCount++;
intervalCount++;
if (intervalCount == reportInterval)
{
System.Console.Write("Processing line " + 
lineCount + '\r');
intervalCount = 0;
}
}// End while.
   

Re: [sqlite] System.Data.SQLite fails to load

[Drago, William @ MWG - NARDAEAST]

Thanks for the reply.

Everything seems to be working fine, the only thing is I don't see SQLite in 
VEE's drop down box for "Available .NET Assemblies." I'm not sure where that 
list comes from, and I thought that even though SQLite is working, that I might 
not have installed it properly with gacutil.

-Bill

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Joe Mistachkin
Sent: Friday, February 28, 2014 10:24 AM
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] System.Data.SQLite fails to load


Drago, William @ MWG - NARDAEAST wrote:
>
> I solved this problem by deploying the "System.Data.SQLite.dll"
> mixed-mode
assembly to
> the GAC. I know this is not recommended, but it's the only thing that
works.
>

It should be OK, the mixed-mode assembly is designed for these types of cases, 
when an application or environment cannot make use of app-local deployment for 
some reason.

>
> My question now is, I used gacutil.exe instead of the installer that
> comes
with SQLite.
> Was that a mistake? Should I have used the installer? I hate running
installers unless
> I know exactly what they're going to do, that's why I'm asking.
>

Using "gacutil" is fine.  That's more-or-less what the setup packages do.

--
Joe Mistachkin

___
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] System.Data.SQLite fails to load

[Drago, William @ MWG - NARDAEAST]

I solved this problem by deploying the "System.Data.SQLite.dll" mixed-mode 
assembly to the GAC. I know this is not recommended, but it's the only thing 
that works.

My question now is, I used gacutil.exe instead of the installer that comes with 
SQLite. Was that a mistake? Should I have used the installer? I hate running 
installers unless I know exactly what they're going to do, that's why I'm 
asking.

Thanks,
-Bill


-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Incongruous
Sent: Wednesday, February 26, 2014 9:15 AM
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] System.Data.SQLite fails to load

You've sparked my curiosity, what is this VEE programming language? Is there a 
web site I can go to read more about it?

-Original Message-
From: Joe Mistachkin
Sent: Tuesday, February 25, 2014 3:44 PM
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] System.Data.SQLite fails to load


William Drago wrote:
>
> I am using System.Data.SQLite with a relatively uncommon
> language called VEE. This is an interpreted language that
> runs in a 32bit development/runtime environment.
>

I've never heard of this language before; however, it sounds
like it hosts the CLR within its process?

>
> My application works fine when run on an internal or USB
> thumb drive. However, if I try running it from a network
> drive SQLite fails to load.
>

For the 2.0 .NET Framework, loading assemblies from a network
share can be complicated by trust issues.  Since I do not see
any network share paths in your trace output, I'm not sure if
that is the case here.

One thing that I'm noticing is that the successful load uses
the "LoadFrom context" and the failed load uses the default
context.  I'm not sure how the VEE code loads the
System.Data.SQLite assembly (or other assemblies); however,
maybe try using the "LoadFrom context" when loading from the
network share as well.

--
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
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