[sqlite] 答复: Making data unique

[Quan Yong Zhai]

Create Table T(ID integer, datetime Integer, data integer, primary key(ID, 
datetime))

???: Andrew Stewart
: ?2015/?12/?7 23:01
???: 'SQLite mailing list'
??: [sqlite] Making data unique

Hi,
I have a table that consists of 3 elements:
ID - integer
Date/time - integer
Data - integer
A single ID can exist multiple times.
A single Date/time can exist multiple times.
An ID & Date/time combination is unique.

What is the best way to ensure uniqueness in this table.

Thanks,
Andrew Stewart
Software Designer

Argus Controls
#101 - 18445 53 AVE
Surrey, BC  V3S 7A4

t: 1-888-667-2091  ext : 108
t: 1-604-536-9100  ext : 108
f: 604-538-4728
w: www.arguscontrols.com
e: astewart at arguscontrols.com

Notice: This electronic transmission contains confidential information, 
intended only for the person(s) named above. If you are not the intended 
recipient, you are hereby notified that any disclosure, copying, distribution, 
or any other use of this email is strictly prohibited. If you have received 
this transmission by error, please notify us immediately by return email and 
destroy the original transmission immediately and all copies thereof.
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Making data unique

[Keith Medcalf]

create table dataset
(
   id integer not null,
   timestamp integer not null,
   data integer not null,
   unique (id, timestamp)
);

> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> bounces at mailinglists.sqlite.org] On Behalf Of Andrew Stewart
> Sent: Monday, 7 December, 2015 08:01
> To: 'SQLite mailing list'
> Subject: [sqlite] Making data unique
> 
> Hi,
> I have a table that consists of 3 elements:
> ID - integer
> Date/time - integer
> Data - integer
> A single ID can exist multiple times.
> A single Date/time can exist multiple times.
> An ID & Date/time combination is unique.
> 
> What is the best way to ensure uniqueness in this table.
> 
> Thanks,
> Andrew Stewart
> Software Designer
> 
> Argus Controls
> #101 - 18445 53 AVE
> Surrey, BC  V3S 7A4
> 
> t: 1-888-667-2091  ext : 108
> t: 1-604-536-9100  ext : 108
> f: 604-538-4728
> w: www.arguscontrols.com
> e: astewart at arguscontrols.com
> 
> Notice: This electronic transmission contains confidential information,
> intended only for the person(s) named above. If you are not the intended
> recipient, you are hereby notified that any disclosure, copying,
> distribution, or any other use of this email is strictly prohibited. If
> you have received this transmission by error, please notify us immediately
> by return email and destroy the original transmission immediately and all
> copies thereof.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Exporting

[Thomas Morris]

One thing that I am going to try to do is, download sqlite and then install in 
on a standalone laptop.  I would then have to save the database to a disc and 
extract it onto the laptop.  Not the most ideal situation, but hopefully it 
works.

Thomas
> On Dec 7, 2015, at 12:41 PM, danap at dandymadeproductions.com wrote:
> 
>> On 12/6/15, Thomas Morris  wrote:
>> 
>>> I recently took over a Java based application that has SQLite embedded
>>> and now I need to export the database into a CSV format.  Reading
>>> online, I have found that using (dot)mode csv, or any (dot) command is
>>> not possible when SQLite is embedded, simply due to the (dot) commands
>>> being for use within the shell.  Is there an alternative way to export,
>>> other than by using (dot) mode, or am I simply out of luck?
>>> 
> 
>> Hi Gunter,
>> 
>> Where would I get the CSV module from?
>> 
>> regards, Robert
>> 
> 
> Hello,
> 
> There has been a project over on Sourceforge that is a module
> for CSV with a JDBC for years. Several other Java applications
> are also out there that will do the work, including mine,
> MyJSQLView.
> 
> Mine does not export BLOB, doesn't seem to make sense in that format
> to me, do it in SQL instead. I may to look change that though, I'll
> take a look.
> 
> Sourceforge: https://sourceforge.net/projects/csvjdbc/
> 
> MyJSQLView io last code:
> http://code.google.com/p/myjsqlview/source/browse/trunk/myjsqlview/src/com/dandymadeproductions/myjsqlview/io
> 
> danap
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] NOP INSERT still writes to the DB/journal

[Patrick Donnelly]

On Mon, Dec 7, 2015 at 5:31 PM, Igor Tandetnik  wrote:
> On 12/7/2015 5:05 PM, Patrick Donnelly wrote:
>>
>> No rows were inserted but there are several writes. This behavior
>> seems to be caused by AUTOINCREMENT?
>
>
> Could be creating sqlite_sequence table where there wasn't one before. I
> wonder if there are still writes on the second and subsequent no-op inserts.

There are still writes:

CREATE TABLE t1(a INTEGER PRIMARY KEY AUTOINCREMENT, b integer);
INSERT INTO t1(b) VALUES (1);
select * from sqlite_sequence;
.print -
INSERT INTO t1 (b)
SELECT 0
WHERE 1 = 0;
select changes();


$ rm foo.db*; ./sqlite3 -vfstrace foo.db < test.sql
...
t1|1
-
trace.xLock(foo.db,SHARED) -> SQLITE_OK
trace.xAccess("/home/batrick/sqlite-amalgamation-3090200/foo.db-journal",0)
-> SQLITE_OK, out=0
trace.xFileSize(foo.db) -> SQLITE_OK, size=3072
trace.xRead(foo.db,n=16,ofst=24) -> SQLITE_OK
trace.xFileSize(foo.db) -> SQLITE_OK, size=3072
trace.xAccess("/home/batrick/sqlite-amalgamation-3090200/foo.db-wal",0)
-> SQLITE_OK, out=0
trace.xFileSize(foo.db) -> SQLITE_OK, size=3072
trace.xLock(foo.db,RESERVED) -> SQLITE_OK
trace.xFileControl(foo.db,20) -> SQLITE_OK
trace.xOpen(foo.db-journal,flags=0x806) -> SQLITE_OK
trace.xDeviceCharacteristics(foo.db) -> 0x1000
trace.xWrite(foo.db-journal,n=512,ofst=0) -> SQLITE_OK
trace.xWrite(foo.db-journal,n=4,ofst=512) -> SQLITE_OK
trace.xWrite(foo.db-journal,n=1024,ofst=516) -> SQLITE_OK
trace.xWrite(foo.db-journal,n=4,ofst=1540) -> SQLITE_OK
trace.xLock(foo.db,EXCLUSIVE) -> SQLITE_OK
trace.xWrite(foo.db-journal,n=4,ofst=1544) -> SQLITE_OK
trace.xWrite(foo.db-journal,n=1024,ofst=1548) -> SQLITE_OK
trace.xWrite(foo.db-journal,n=4,ofst=2572) -> SQLITE_OK
trace.xDeviceCharacteristics(foo.db) -> 0x1000
trace.xRead(foo.db-journal,n=8,ofst=3072) -> SQLITE_IOERR_SHORT_READ
trace.xSync(foo.db-journal,FULL) -> 0
trace.xWrite(foo.db-journal,n=12,ofst=0) -> SQLITE_OK
trace.xSync(foo.db-journal,FULL) -> 0
trace.xWrite(foo.db,n=1024,ofst=0) -> SQLITE_OK
trace.xWrite(foo.db,n=1024,ofst=2048) -> SQLITE_OK
trace.xFileControl(foo.db,21) -> 12
trace.xSync(foo.db,FULL) -> 0
trace.xClose(foo.db-journal) -> SQLITE_OK
trace.xDelete("/home/batrick/sqlite-amalgamation-3090200/foo.db-journal",0)
-> SQLITE_OK
trace.xFileControl(foo.db,22) -> 12
trace.xUnlock(foo.db,SHARED) -> SQLITE_OK
trace.xDeviceCharacteristics(foo.db) -> 0x1000
trace.xUnlock(foo.db,NONE) -> SQLITE_OK
0
trace.xDeviceCharacteristics(foo.db) -> 0x1000
trace.xUnlock(foo.db,NONE) -> SQLITE_OK
trace.xClose(foo.db) -> SQLITE_OK


-- 
Patrick Donnelly

[sqlite] Dual WAL mode?

[Mark Hamburg]

I know I'm being pretty hand-wavy here since I don't know all of the
details of the WAL implementation and I'm not fluent in the terminology,
but it seems like checkpoint starvation shouldn't have to allow the WAL to
grow without bound provided the individual read and write processes don't
have long running transactions.

As I understand it, writers always append to the WAL and use a view of the
database that includes the main file plus any changes in the WAL. Readers
use the main file plus any changes up through a particular point in the WAL
? the point of the last completed transaction prior to the start of the
read operation.

A checkpoint can move data from the WAL into the main file provided it
doesn't move anything beyond the limit point for any client. (Any reader
client since writers use changes through the end of the WAL.) Checkpoint
starvation (and continued WAL expansion) occurs because the checkpointer
fails to make it to the end of the WAL because it is blocked by some
readers change limit.

My thinking is that this could be addressed by splitting the WAL into two
files full of changes that logically follow each other. Assuming readers
move forward frequently enough, they should eventually move their limit
point from the first file into the second. At this point, when the
checkpointer finishes transferring changes for the first WAL file into the
main database file, it can swap the first WAL file to follow the second WAL
file as empty space for future changes.

Would this work and if so is there a reason this hasn't been done?

Mark

[sqlite] sqlite3_errmsg and wide char

[Scott Robison]

On Fri, Nov 27, 2015 at 1:13 PM, Simon Slavin  wrote:

>
> On 27 Nov 2015, at 7:03pm, Igor Korot  wrote:
>
> > But what about Linux/Mac?
> > Can I use that function there?
>
> Code pages are a Microsoft thing.  Macs just have ASCII and Unicode, with
> easy-to-use system functions to convert between them.  There's no need to
> worry about locales and code pages and Latin-1 and wstring.
>
> For Linux, I don't know.  But it would be a lousy Linux implementation
> which required anything fancy.
>

This may be true of OS X, but it not true of Mac all through history. They
might not have been called "code pages" but Mac most definitely had
different character sets to support different markets.

-- 
Scott Robison

[sqlite] NOP INSERT still writes to the DB/journal

[Igor Tandetnik]

On 12/7/2015 5:05 PM, Patrick Donnelly wrote:
> No rows were inserted but there are several writes. This behavior
> seems to be caused by AUTOINCREMENT?

Could be creating sqlite_sequence table where there wasn't one before. I 
wonder if there are still writes on the second and subsequent no-op inserts.
-- 
Igor Tandetnik

[sqlite] NOP INSERT still writes to the DB/journal

[Patrick Donnelly]

Update on this:

On Mon, May 5, 2014 at 4:53 PM, Patrick Donnelly  
wrote:
> Hi,
>
> I have an INSERT that looks like
>
> INSERT INTO T
> SELECT ...
>
> which I'm running numerous times a second that generally does nothing
> because the SELECT returns no rows. Unfortunately, I've found that
> SQLite still does numerous disk writes anyway in this situation.
>
> Is my only option to eliminate the INSERT by using a SELECT first to
> check if there are no rows? Something like:
>
> CREATE TEMPORARY VIEW V AS
> SELECT ...
> SELECT COUNT(*) FROM V;
> /* If > 0 */
> INSERT INTO T SELECT * FROM V;
>
> ?

I've been able to reproduce it with this minimal example:

CREATE TABLE t1(a INTEGER PRIMARY KEY AUTOINCREMENT);
.print -
INSERT INTO t1
SELECT 0
WHERE 1 = 2;
select changes();

$ ./sqlite3 -vfstrace foo.db < test.sql
...
-
trace.xLock(foo.db,SHARED) -> SQLITE_OK
trace.xAccess("/home/batrick/scm/cctools/chirp/src/sqlite-amalgamation-3090200/foo.db-journal",0)
-> SQLITE_OK, out=0
trace.xFileSize(foo.db) -> SQLITE_OK, size=3072
trace.xRead(foo.db,n=16,ofst=24) -> SQLITE_OK
trace.xFileSize(foo.db) -> SQLITE_OK, size=3072
trace.xAccess("/home/batrick/scm/cctools/chirp/src/sqlite-amalgamation-3090200/foo.db-wal",0)
-> SQLITE_OK, out=0
trace.xFileSize(foo.db) -> SQLITE_OK, size=3072
trace.xLock(foo.db,RESERVED) -> SQLITE_OK
trace.xFileControl(foo.db,20) -> SQLITE_OK
trace.xOpen(foo.db-journal,flags=0x806) -> SQLITE_OK
trace.xDeviceCharacteristics(foo.db) -> 0x1000
trace.xWrite(foo.db-journal,n=512,ofst=0) -> SQLITE_OK
trace.xWrite(foo.db-journal,n=4,ofst=512) -> SQLITE_OK
trace.xWrite(foo.db-journal,n=1024,ofst=516) -> SQLITE_OK
trace.xWrite(foo.db-journal,n=4,ofst=1540) -> SQLITE_OK
trace.xLock(foo.db,EXCLUSIVE) -> SQLITE_OK
trace.xWrite(foo.db-journal,n=4,ofst=1544) -> SQLITE_OK
trace.xWrite(foo.db-journal,n=1024,ofst=1548) -> SQLITE_OK
trace.xWrite(foo.db-journal,n=4,ofst=2572) -> SQLITE_OK
trace.xDeviceCharacteristics(foo.db) -> 0x1000
trace.xRead(foo.db-journal,n=8,ofst=3072) -> SQLITE_IOERR_SHORT_READ
trace.xSync(foo.db-journal,FULL) -> 0
trace.xWrite(foo.db-journal,n=12,ofst=0) -> SQLITE_OK
trace.xSync(foo.db-journal,FULL) -> 0
trace.xWrite(foo.db,n=1024,ofst=0) -> SQLITE_OK
trace.xWrite(foo.db,n=1024,ofst=2048) -> SQLITE_OK
trace.xFileControl(foo.db,21) -> 12
trace.xSync(foo.db,FULL) -> 0
trace.xClose(foo.db-journal) -> SQLITE_OK
trace.xDelete("/home/batrick/scm/cctools/chirp/src/sqlite-amalgamation-3090200/foo.db-journal",0)
-> SQLITE_OK
trace.xFileControl(foo.db,22) -> 12
trace.xUnlock(foo.db,SHARED) -> SQLITE_OK
trace.xDeviceCharacteristics(foo.db) -> 0x1000
trace.xUnlock(foo.db,NONE) -> SQLITE_OK
0
trace.xDeviceCharacteristics(foo.db) -> 0x1000
trace.xUnlock(foo.db,NONE) -> SQLITE_OK
trace.xClose(foo.db) -> SQLITE_OK

No rows were inserted but there are several writes. This behavior
seems to be caused by AUTOINCREMENT?

-- 
Patrick Donnelly

[sqlite] Making data unique

[Andrew Stewart]

I don't want to overwrite.  An error or rejecting the Insert will work.

Thanks,
Andrew

-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Adam Devita
Sent: Monday, December 07, 2015 7:14 AM
To: SQLite mailing list 
Subject: Re: [sqlite] Making data unique

When you are about to insert into the table and find that ID & Date/time are 
identical to another record, but  the data is different, do you want to 
overwrite, or not?  Do you want an error?

Adam

On Mon, Dec 7, 2015 at 10:01 AM, Andrew Stewart  
wrote:
> Hi,
> I have a table that consists of 3 elements:
> ID - integer
> Date/time - integer
> Data - integer
> A single ID can exist multiple times.
> A single Date/time can exist multiple times.
> An ID & Date/time combination is unique.
>
> What is the best way to ensure uniqueness in this table.
>
> Thanks,
> Andrew Stewart
> Software Designer
>
> Argus Controls
> #101 - 18445 53 AVE
> Surrey, BC  V3S 7A4
>
> t: 1-888-667-2091  ext : 108
> t: 1-604-536-9100  ext : 108
> f: 604-538-4728
> w: www.arguscontrols.com
> e: astewart at arguscontrols.com
>
> Notice: This electronic transmission contains confidential information, 
> intended only for the person(s) named above. If you are not the intended 
> recipient, you are hereby notified that any disclosure, copying, 
> distribution, or any other use of this email is strictly prohibited. If you 
> have received this transmission by error, please notify us immediately by 
> return email and destroy the original transmission immediately and all copies 
> thereof.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



--
--
VerifEye Technologies Inc.
151 Whitehall Dr. Unit 2
Markham, ON
L3R 9T1
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Exporting

[Robert W.Mills (Phoenix)]

Hi Gunter,

Where would I get the CSV module from?

regards,
Robert

On 07/12/15 08:26, Hick Gunter wrote:
> You can always write a virtual table for exporting to whatever flavor of CSV 
> you like. Mine exports/imports strings/blobs containing nonprintable 
> characters in x'' notation.
>
> Basically it implements:
>
> For export:
> CREATE VIRTUAL TABLE _csv_exp USING CSV (,); -> 
> create a CSV table with the indicated fields
> INSERT INTO _csv_exp SELECT  from ; -> write CSV 
> file (with header line)
>
> For import:
> CREATE VIRTUAL TABLE _csv_imp USING CSV(); -> read fields 
> from indicated filename
> INSERT INTO  SELECT  from _csv_imp; -> read CSV file
>
> -Urspr?ngliche Nachricht-
> Von: sqlite-users-bounces at mailinglists.sqlite.org 
> [mailto:sqlite-users-bounces at mailinglists.sqlite.org] Im Auftrag von 
> Thomas Morris
> Gesendet: Sonntag, 06. Dezember 2015 19:44
> An: sqlite-users at mailinglists.sqlite.org
> Betreff: [sqlite] Exporting
>
> I recently took over a Java based application that has SQLite embedded and 
> now I need to export the database into a CSV format.  Reading online, I have 
> found that using (dot)mode csv, or any (dot) command is not possible when 
> SQLite is embedded, simply due to the (dot) commands being for use within the 
> shell.  Is there an alternative way to export, other than by using (dot) 
> mode, or am I simply out of luck?
>
> Thomas
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
>   Gunter Hick
> Software Engineer
> Scientific Games International GmbH
> FN 157284 a, HG Wien
> Klitschgasse 2-4, A-1130 Vienna, Austria
> Tel: +43 1 80100 0
> E-Mail: hick at scigames.at
>
> This communication (including any attachments) is intended for the use of the 
> intended recipient(s) only and may contain information that is confidential, 
> privileged or legally protected. Any unauthorized use or dissemination of 
> this communication is strictly prohibited. If you have received this 
> communication in error, please immediately notify the sender by return e-mail 
> message and delete all copies of the original communication. Thank you for 
> your cooperation.
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Making data unique

[Andrew Stewart]

Hi,
I have a table that consists of 3 elements:
ID - integer
Date/time - integer
Data - integer
A single ID can exist multiple times.
A single Date/time can exist multiple times.
An ID & Date/time combination is unique.

What is the best way to ensure uniqueness in this table.

Thanks,
Andrew Stewart
Software Designer

Argus Controls
#101 - 18445 53 AVE
Surrey, BC  V3S 7A4

t: 1-888-667-2091  ext : 108
t: 1-604-536-9100  ext : 108
f: 604-538-4728
w: www.arguscontrols.com
e: astewart at arguscontrols.com

Notice: This electronic transmission contains confidential information, 
intended only for the person(s) named above. If you are not the intended 
recipient, you are hereby notified that any disclosure, copying, distribution, 
or any other use of this email is strictly prohibited. If you have received 
this transmission by error, please notify us immediately by return email and 
destroy the original transmission immediately and all copies thereof.

[sqlite] Exporting

[da...@dandymadeproductions.com]

> On 12/6/15, Thomas Morris  wrote:
>
>> I recently took over a Java based application that has SQLite embedded
>> and now I need to export the database into a CSV format.  Reading
>> online, I have found that using (dot)mode csv, or any (dot) command is
>> not possible when SQLite is embedded, simply due to the (dot) commands
>> being for use within the shell.  Is there an alternative way to export,
>> other than by using (dot) mode, or am I simply out of luck?
>>

> Hi Gunter,
>
> Where would I get the CSV module from?
>
> regards, Robert
>

Hello,

There has been a project over on Sourceforge that is a module
for CSV with a JDBC for years. Several other Java applications
are also out there that will do the work, including mine,
MyJSQLView.

Mine does not export BLOB, doesn't seem to make sense in that format
to me, do it in SQL instead. I may to look change that though, I'll
take a look.

Sourceforge: https://sourceforge.net/projects/csvjdbc/

MyJSQLView io last code:
http://code.google.com/p/myjsqlview/source/browse/trunk/myjsqlview/src/com/dandymadeproductions/myjsqlview/io

danap

[sqlite] Create table while a dbdatareader is open

[Hick Gunter]

In that case you would need to BEGIN IMMEDIATE or BEGIN EXCLUSIVE and perform 
all your actions within the same thread.

-Urspr?ngliche Nachricht-
Von: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] Im Auftrag von Lars 
Fiedler
Gesendet: Montag, 07. Dezember 2015 10:59
An: SQLite mailing list
Betreff: Re: [sqlite] Create table while a dbdatareader is open

Thanks.  Good to know about the "create table as" syntax, but I'm not selecting 
rows and just copying to another table.  For each row in one sqlite table, the 
system under development may do may do many things down stream - send emails, 
query other databases, and create tables and insert into the temporary sqlite 
database.

Right now I have a option to load all rows into memory to remove the lock so 
tables can be created downstream.

Lars


> On Dec 7, 2015, at 3:30 AM, Hick Gunter  wrote:
>
> Maybe you are looking for the INSERT INTO ... SELECT or CREATE TABLE ... AS 
> SELECT syntax? This would be one write transaction instead of two separate, 
> incompatible transactions.
>
> -Urspr?ngliche Nachricht-
> Von: sqlite-users-bounces at mailinglists.sqlite.org 
> [mailto:sqlite-users-bounces at mailinglists.sqlite.org] Im Auftrag von Lars 
> Fiedler
> Gesendet: Sonntag, 06. Dezember 2015 21:47
> An: sqlite-users at mailinglists.sqlite.org
> Betreff: [sqlite] Create table while a dbdatareader is open
>
> Is it possible with certain settings to have a dbdatareader iterating through 
> rows, and at the same time create a new table?  I've tried various settings - 
> read uncommitted, wal mode, shared cache.  But it looks like the open reader 
> has a read lock on sqlite_master (not read uncommitted), and the create table 
> command wants a write lock on sqlite_master.
>
> Cheers,
>  Lars
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
> Gunter Hick
> Software Engineer
> Scientific Games International GmbH
> FN 157284 a, HG Wien
> Klitschgasse 2-4, A-1130 Vienna, Austria
> Tel: +43 1 80100 0
> E-Mail: hick at scigames.at
>
> This communication (including any attachments) is intended for the use of the 
> intended recipient(s) only and may contain information that is confidential, 
> privileged or legally protected. Any unauthorized use or dissemination of 
> this communication is strictly prohibited. If you have received this 
> communication in error, please immediately notify the sender by return e-mail 
> message and delete all copies of the original communication. Thank you for 
> your cooperation.
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: hick at scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.

[sqlite] Making data unique

[Adam Devita]

When you are about to insert into the table and find that ID &
Date/time are identical to another record, but  the data is different,
do you want to overwrite, or not?  Do you want an error?

Adam

On Mon, Dec 7, 2015 at 10:01 AM, Andrew Stewart
 wrote:
> Hi,
> I have a table that consists of 3 elements:
> ID - integer
> Date/time - integer
> Data - integer
> A single ID can exist multiple times.
> A single Date/time can exist multiple times.
> An ID & Date/time combination is unique.
>
> What is the best way to ensure uniqueness in this table.
>
> Thanks,
> Andrew Stewart
> Software Designer
>
> Argus Controls
> #101 - 18445 53 AVE
> Surrey, BC  V3S 7A4
>
> t: 1-888-667-2091  ext : 108
> t: 1-604-536-9100  ext : 108
> f: 604-538-4728
> w: www.arguscontrols.com
> e: astewart at arguscontrols.com
>
> Notice: This electronic transmission contains confidential information, 
> intended only for the person(s) named above. If you are not the intended 
> recipient, you are hereby notified that any disclosure, copying, 
> distribution, or any other use of this email is strictly prohibited. If you 
> have received this transmission by error, please notify us immediately by 
> return email and destroy the original transmission immediately and all copies 
> thereof.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



-- 
--
VerifEye Technologies Inc.
151 Whitehall Dr. Unit 2
Markham, ON
L3R 9T1

[sqlite] Create table while a dbdatareader is open

[Hick Gunter]

Maybe you are looking for the INSERT INTO ... SELECT or CREATE TABLE ... AS 
SELECT syntax? This would be one write transaction instead of two separate, 
incompatible transactions.

-Urspr?ngliche Nachricht-
Von: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] Im Auftrag von Lars 
Fiedler
Gesendet: Sonntag, 06. Dezember 2015 21:47
An: sqlite-users at mailinglists.sqlite.org
Betreff: [sqlite] Create table while a dbdatareader is open

Is it possible with certain settings to have a dbdatareader iterating through 
rows, and at the same time create a new table?  I've tried various settings - 
read uncommitted, wal mode, shared cache.  But it looks like the open reader 
has a read lock on sqlite_master (not read uncommitted), and the create table 
command wants a write lock on sqlite_master.

Cheers,
  Lars
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: hick at scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.

[sqlite] Exporting

[Hick Gunter]

You can always write a virtual table for exporting to whatever flavor of CSV 
you like. Mine exports/imports strings/blobs containing nonprintable characters 
in x'' notation.

Basically it implements:

For export:
CREATE VIRTUAL TABLE _csv_exp USING CSV (,); -> 
create a CSV table with the indicated fields
INSERT INTO _csv_exp SELECT  from ; -> write CSV file 
(with header line)

For import:
CREATE VIRTUAL TABLE _csv_imp USING CSV(); -> read fields from 
indicated filename
INSERT INTO  SELECT  from _csv_imp; -> read CSV file

-Urspr?ngliche Nachricht-
Von: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] Im Auftrag von Thomas 
Morris
Gesendet: Sonntag, 06. Dezember 2015 19:44
An: sqlite-users at mailinglists.sqlite.org
Betreff: [sqlite] Exporting

I recently took over a Java based application that has SQLite embedded and now 
I need to export the database into a CSV format.  Reading online, I have found 
that using (dot)mode csv, or any (dot) command is not possible when SQLite is 
embedded, simply due to the (dot) commands being for use within the shell.  Is 
there an alternative way to export, other than by using (dot) mode, or am I 
simply out of luck?

Thomas
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: hick at scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.

[sqlite] Create table while a dbdatareader is open

[Simon Slavin]

On 6 Dec 2015, at 8:46pm, Lars Fiedler  wrote:

> Is it possible with certain settings to have a dbdatareader iterating through 
> rows, and at the same time create a new table?  I've tried various settings - 
> read uncommitted, wal mode, shared cache.  But it looks like the open reader 
> has a read lock on sqlite_master (not read uncommitted), and the create table 
> command wants a write lock on sqlite_master.

I'm tempted to tell you that even if it were possible you shouldn't do it.

SQLite's API has separate _step() calls because a SELECT could return millions 
of rows and you might not have enough memory to store them all.  Because you 
(the programmer) have the opportunity to wait as long as you want between 
_step()s you might want to consider them separate operations.  But they're not 
really, and you should really get them out the way, all the way to _finalize() 
or _reset(), before you consider doing other things.

One solution you might be able to use is to build up a sequence of SQL commands 
in a long string (separated by semi-colons, of course).  Then once you've 
_finalize()d your SELECT you can just _exec() the string.

Simon.

[sqlite] Create table while a dbdatareader is open

[Lars Fiedler]

Thanks.  Good to know about the "create table as" syntax, but I'm not selecting 
rows and just copying to another table.  For each row in one sqlite table, the 
system under development may do may do many things down stream - send emails, 
query other databases, and create tables and insert into the temporary sqlite 
database.

Right now I have a option to load all rows into memory to remove the lock so 
tables can be created downstream.

Lars


> On Dec 7, 2015, at 3:30 AM, Hick Gunter  wrote:
> 
> Maybe you are looking for the INSERT INTO ... SELECT or CREATE TABLE ... AS 
> SELECT syntax? This would be one write transaction instead of two separate, 
> incompatible transactions.
> 
> -Urspr?ngliche Nachricht-
> Von: sqlite-users-bounces at mailinglists.sqlite.org 
> [mailto:sqlite-users-bounces at mailinglists.sqlite.org] Im Auftrag von Lars 
> Fiedler
> Gesendet: Sonntag, 06. Dezember 2015 21:47
> An: sqlite-users at mailinglists.sqlite.org
> Betreff: [sqlite] Create table while a dbdatareader is open
> 
> Is it possible with certain settings to have a dbdatareader iterating through 
> rows, and at the same time create a new table?  I've tried various settings - 
> read uncommitted, wal mode, shared cache.  But it looks like the open reader 
> has a read lock on sqlite_master (not read uncommitted), and the create table 
> command wants a write lock on sqlite_master.
> 
> Cheers,
>  Lars
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
> 
> ___
> Gunter Hick
> Software Engineer
> Scientific Games International GmbH
> FN 157284 a, HG Wien
> Klitschgasse 2-4, A-1130 Vienna, Austria
> Tel: +43 1 80100 0
> E-Mail: hick at scigames.at
> 
> This communication (including any attachments) is intended for the use of the 
> intended recipient(s) only and may contain information that is confidential, 
> privileged or legally protected. Any unauthorized use or dissemination of 
> this communication is strictly prohibited. If you have received this 
> communication in error, please immediately notify the sender by return e-mail 
> message and delete all copies of the original communication. Thank you for 
> your cooperation.
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users