Re: [sqlite] Generating sqlite4.c

[Andrew Overholt]

Yes, for sure.

On Wed 08 Jan 2014 03:02:20 PM EST, big stone wrote:

Will you write a small result of your early experiment ?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



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

[sqlite] shell crashes when there is no column name

[Clemens Ladisch]

Hi,

the shell does not always check the return value of sqlite3_column_name()
for being NULL:

  $ sqlite3
  SQLite version 3.8.3 2014-01-11 12:52:25
  Enter ".help" for instructions
  Enter SQL statements terminated with a ";"
  sqlite> .header on
  sqlite> values(1);
  (null)
  1
  sqlite> .mode html
  sqlite> values(1);
  Segmentation fault


And I guess NULL should not be returned in the first place if there is
no error.


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

[sqlite] Best method for a multi-thread application

[Stephen Chrzanowski]

Please note, if you'd like to discuss this program as an entity outside
this thread, feel free to contact me directly.  I think my email address is
exposed in this thread.

I'm writing a new program** to keep track and manage my MP3s and will allow
me to sync different sets of MP3s to different volumes (AKA: USB Keys) for
different devices the keys plug into.  (Multiple keys for the car stereo,
home stereo, my phone, my tablet, etc).  The theory is, I don't typically
want my phone to ever have 8gig of music files, however, my car USB key can
have 16gig, but my phone and tablet COULD have similar music, the stereo
may only have kids music, the Christmas Music USB key might get an update,
etc.  The volumes are going to be identified by the volume ID of the
partition of the device or by a flat file containing a unique ID.  This is
going to be a Win32 application.  The database is not going to contain the
raw MP3s.  I'm still in "*Pen to back of Napkin*" development mode and just
drawing up the schema for the database, although my IDE is sitting in the
background, waiting for something to be done.

The end goal of the program is to allow me to keep tabs on what music I
have on which device.  If I have something on my Home Stereo I now want on
one of my Car USB sticks, I can simply click a few buttons, and the next
time I plug the key into the computer, the MP3 will be copied as required.
If "all of a sudden" I no longer want to listen to Green Day in my car, I
can remove the tunes from the key via this app, and next sync the key is
updated.  If I happen to lose my car USB key, a few clicks in this program,
and a new USB key, I can mirror the old key to the new key without the old
key, and all I'm out is time and cash for the new key.  The "Central
Storage" is on a NAS via GigE and is not "new" to being bombarded with file
requests.  (The poor thing would be LUCKY to have the time to spin down the
drives. heh)

The current theory I have is that I want to open up several threads, at
program launch, on top of the main thread.  Each thread is going to have a
responsibility.
- Verify that the files reported in the database exist on the primary store
(Runs once, a single SELECT drops values into an in-memory container, then
loop through the contents of the container and update the database as
required)
- Scan for new entries on the primary store and put them in the database
with no CRC so that the next thread can do its thing. (Runs once)
- Verify blank [CRCs and/or file sizes - Undecided as of yet] as well as
file time stamps to compile a report of possible duplicate files.  (Delayed
start.  Repeats until all entries have a CRC/Filesize entry or whatever the
finalized conditions are)
- See if anything pending needs to be added or removed from the available
volumes. (Runs once against all valid volumes, but will support the user
requesting this to be done, and when a new drive is detected)

All UI info is going to be generated based on the contents of the database,
not by what physically is on the network.  UI info is going to be displayed
as the above threads are working.  I figure searching for files would be
faster via local database than a remote NAS with thousands of files.  The
above threads, when done, I personally would consider "Good enough".  So
the main thread, plus the threads above will have direct connection to the
database via their own connection.

The database is going to store the physical primary paths the MP3s are
stored in (As directories/drive assignments can change, but content and
subdirectories won't) in one table, and another table is going to handle
sub-directory, file names, CRC, and different flags--basically anything
associated to the characteristics of the MP3.  Another table designated to
keep tabs on all the volumes, and a final table to link the MP3 entries to
the volumes table.

The database itself is going to be stored locally on PC, not via a network
share.  If the program is "run" from a network share, the backup API will
be used to copy the database from the network store and drop it locally
somewhere, then periodically send the database back to the network share.

The advice I'm looking for is the best possible methodology to properly
handle the database activities with the above threads doing very different
things to very similar data.

I figure that each thread would get its own connection to the database.
I'd also set the timeout for each connection to a few seconds to put off
time outs.

So going with the wall of text above, and my "planned" methodology, what
other adventures am I going to need to look forward to in order to protect
the database and prevent the program from crashing through a wall?
Thoughts I had to separate the threads into their own processes, but I kind
of like the thought of the "All in one" application.  (Services be damned.
;))

--
** The reasons for the new app is that
- I hate iTunes and it doesn't play with multiple devices all that well,

Re: [sqlite] Insert multiple values as an economy of coding time and quicker upload process ?

[big stone]

Yes,

I was talking of the very test script that were written last friday :

 [a26f399ba4] 
like :

do_execsql_test 4.1 {  DROP TABLE IF EXISTS t1;  CREATE TABLE t1(x);
INSERT INTO t1 VALUES(1);  INSERT INTO t1 VALUES(2);  INSERT INTO t1
VALUES(3);  INSERT INTO t1 VALUES(4);
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Insert multiple values as an economy of coding time and quicker upload process ?

[Richard Hipp]

On Mon, Jan 13, 2014 at 12:21 PM, big stone  wrote:

> Hello,
>
> I read the source code of sqlite and I find a lot of :
>
>
> CREATE TABLE t1(x INTEGER);
> INSERT INTO t1 VALUES(1);INSERT INTO t1 VALUES(2);
> 
>

Are you talking about code in the test scripts?


>
> Wouldn't it be both an economy of coding time and a more quick upload
> process to allow Multiple inserts SYNTAX ?
>
> like :
> INSERT INTO t1 VALUES(1),(2),  ();
>
> I'm not 100% certain it's in SQL standard, but the syntax seems ok for
> SQL Server , Oracle , and POSTGRESQL at least.
>

SQLite supports it too. And, in fact, you'll find both forms in the test
scripts:

 INSERT INTO t1 VALUES(1); INSERT INTO t1 VALUES(2);

And

 INSERT INTO t1 VALUES(1),(2);

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

Re: [sqlite] Insert multiple values as an economy of coding time and quicker upload process ?

[Simon Slavin]

On 13 Jan 2014, at 5:21pm, big stone  wrote:

> Wouldn't it be both an economy of coding time and a more quick upload
> process to allow Multiple inserts SYNTAX ?
> 
> like :
> INSERT INTO t1 VALUES(1),(2),  ();

That does actually work.  See the syntax diagram at



However, as Will wrote, it's not always faster.

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

Re: [sqlite] Insert multiple values as an economy of coding time and quicker upload process ?

[Duquette, William H (393K)]

On 1/13/14 9:21 AM, "big stone"  wrote:


>Hello,
>
>I read the source code of sqlite and I find a lot of :
>
>
>CREATE TABLE t1(x INTEGER);
>INSERT INTO t1 VALUES(1);INSERT INTO t1 VALUES(2);
>
>
>Wouldn't it be both an economy of coding time and a more quick upload
>process to allow Multiple inserts SYNTAX ?
>
>like :
>INSERT INTO t1 VALUES(1),(2),  ();
>
>I'm not 100% certain it's in SQL standard, but the syntax seems ok for
>SQL Server , Oracle , and POSTGRESQL at least.

I can't speak to the syntax; but in my experience, preparing a single
INSERT statement and executing it multiple times in a loop is much faster
than building a script of many INSERT statements and then preparing and
executing it once.  I would imagine the same thing would apply for a
multi-row INSERT: for large amounts of data, the parsing time would kill
you.

Will



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

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

[sqlite] Insert multiple values as an economy of coding time and quicker upload process ?

[big stone]

Hello,

I read the source code of sqlite and I find a lot of :


CREATE TABLE t1(x INTEGER);
INSERT INTO t1 VALUES(1);INSERT INTO t1 VALUES(2);


Wouldn't it be both an economy of coding time and a more quick upload
process to allow Multiple inserts SYNTAX ?

like :
INSERT INTO t1 VALUES(1),(2),  ();

I'm not 100% certain it's in SQL standard, but the syntax seems ok for
SQL Server , Oracle , and POSTGRESQL at least.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users