Re: [sqlite] Compare Similar Cells

[Kristopher Roy]

Lets say one songtitle is "Happy Day" and another one "12 - Happy Day"


On Sun, Sep 22, 2013 at 8:28 PM, Igor Tandetnik  wrote:

> On 9/22/2013 10:41 PM, Kristopher Roy wrote:
>
>> I have a table of songs, several have similar titles I can't find where to
>> get started. I tried this but its not right.
>> Select SongTitle, COUNT(SongTitle) AS LIKE_COUNT FROM Songs
>>
>
> What do you mean by "similar titles"? Show a representative sample of data
> in your table, and the result you would like to obtain from the query when
> run on that sample.
> --
> Igor Tandetnik
>
> __**_
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users
>



-- 
Thank You,
Kristopher C. Roy
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Compare Similar Cells

[Igor Tandetnik]

On 9/22/2013 10:41 PM, Kristopher Roy wrote:

I have a table of songs, several have similar titles I can't find where to
get started. I tried this but its not right.
Select SongTitle, COUNT(SongTitle) AS LIKE_COUNT FROM Songs


What do you mean by "similar titles"? Show a representative sample of 
data in your table, and the result you would like to obtain from the 
query when run on that sample.

--
Igor Tandetnik

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

[sqlite] Compare and INSERT INTO syntax issue

[Kristopher Roy]

I am trying to grab the value of custom5 % and compare it to skipcount
and then if it is a higher value write it to skipcount, but something is
wrong with my syntax, can anyone help?

INSERT OR REPLACE INTO SONGS (skipcount) Values SELECT substr( custom5, 1,
4) FROM SONGS WHERE custom5 > '%' and skipcount < cast( substr(
custom5, 1, 4) as int );

-- 
Thank You,
Kristopher C. Roy
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT INTO ???

[B.W.H. van Beest]

I'll try. Thanks. This seems just what I need.


Bertwim
On 09/21/2013 06:12 AM, Bernhard Amann wrote:

INSERT INTO  SELECT * FROM ;

However, this only works if  already exists, which is actually quite 
cumbersome..
Is there a way to make the new table 'on the fly"?

create table newtable as select * from oldtable;

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



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

Re: [sqlite] WAL files and PERL question

[Clemens Ladisch]

Larry Brasfield wrote:
> If I go into the firefox add on and run a checkpoint , the 2 new invoices
> get flushed from the WL to the db file and then my program sees them
> what am I doing wrong where my script is only looking at the db file and not
> the WAL file?

An explanation would be that your script is not able to see the .wal file.
Check the file permissions, and whether the .wal file actually exists in
the same directory as the database file (Windows often redirects accesses
elsewhere if you try to write into system directories like C:\).


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

Re: [sqlite] WAL files and PERL question

[Larry Brasfield]

John Carey wrote:

[Lot's of stuff, in multiple, apparently redundant threads, cut as 
irrelevant.]



If I go into the firefox add on and run a checkpoint , the 2 new invoices
get flushed from the WL to the db file and then my program sees them
what am I doing wrong where my script is only looking at the db file and not
the WAL file?


Before getting to your topic (as I guess at it), let me remind you that 
posting the same question multiple times to a newsgroup or email list is 
considered to be rude.  If you post and get no reply, then, after a 
reasonable amount of time, (which would be several hours or more), you 
might review your earlier post for clarity and appropriate brevity, and 
decide to post a revision calculated to improve the odds of getting a 
useful response.  But just sending essentially the same thing under 
different subjects and names is more likely to reduce your chances.


I've omitted quoting most of your post(s) because it does not reach the 
problem, which is that you are paying too much attention to the WAL 
file.  Except for the fact that SQLite, under some circumstances and 
modes of usage, creates a WAL file, its content should be of no concern 
to you unless you are improving or fixing SQLite itself.  Other than 
deciding whether you need SQLite to employ the WAL file (of its own 
creation, normally), and making sure it can be created when needed, it 
is not something you have to deal with.  Only the file named in the 
sqlite3_open*() call is persistent except under certain abnormal 
termination conditions.  (You may want to study what those are, but that 
will not cure your present difficulty.)


I dare say that your puzzling query results would be the same if you 
were to run SQLite so that it does not use a WAL file.  Discovering 
whether that is so is a worthwhile experiment, and if it is so, will 
allow you to focus on your schema and query rather than details of 
SQLite implementation that should be opaque to you as a user.


Best regards,
--
Larry Brasfield

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

Re: [sqlite] multiple connection to the same DB

[Simon Slavin]

On 22 Sep 2013, at 4:50pm, Gerry Snyder  wrote:

> If I am right, then something else is happening, such as somehow using 
> different files, or something else deleting or otherwise modifying the file 
> between accesses.

Worth testing.  Have one user create an entry in the table then quit their 
program.  Then have the other user open the file, do a SELECT on the table and 
see whether the entry is there.

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

Re: [sqlite] multiple connection to the same DB

[Gerry Snyder]

My track record of giving helpful advice here is not great, but this 
does not sound like an NFS problem to me.


Your example made it look like everyone is logging into the server, and 
running the SQLite executable located there, rather than running an 
executable located on their own machine and all accessing the db file on 
the server. These are very different things.


If I am right, then something else is happening, such as somehow using 
different files, or something else deleting or otherwise modifying the 
file between accesses.


HTH,

Gerry

On 9/22/2013 8:34 AM, olivier Ménard wrote:

The os in use is Linux Ubuntu, so i suppose the protocol NFS too.

If i have well understood :
in theory, multiple access should work with sqlite, but in practice, it doesn't 
because of the os : the mechanism of locking doesn't work ?

I supose it won't change anything but Is it possible to improve the mechanism 
If everyone writes an explicit
BEGIN IMMEDIATE (or EXCLUSIVE ?)
INSERT ...
END

Thanks for all the answers.




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

Re: [sqlite] multiple connection to the same DB

[olivier Ménard]

The os in use is Linux Ubuntu, so i suppose the protocol NFS too.

If i have well understood : 
in theory, multiple access should work with sqlite, but in practice, it doesn't 
because of the os : the mechanism of locking doesn't work ?

I supose it won't change anything but Is it possible to improve the mechanism 
If everyone writes an explicit
BEGIN IMMEDIATE (or EXCLUSIVE ?)
INSERT ...
END

Thanks for all the answers.








 De : Simon Slavin 
À : olivier Ménard ; General Discussion of SQLite Database 
 
Envoyé le : Dimanche 22 septembre 2013 16h17
Objet : Re: [sqlite] multiple connection to the same DB
 


On 22 Sep 2013, at 12:30pm, olivier Ménard  wrote:

> We were far from a high concurrency use of sqlite, only 15 members, and using 
> only a single line insert. So i thought it should be ok.
> I'll continue to use sqlite but as the only administrator of the DB : 
> I'll try to give the permissions rw-r--r-- to my file, and hope it'll works.

See what Kees Nuyt wrote, then see



for more details.

The problem is not with your permissions.  The problem is that your network 
file system is has not implemented the part of file sharing that goes with two 
computers accessing the same file at the same time.  This is very common in 
network file systems: they do not implement file locking and updates correctly, 
and update a file only when it is closed.

Try having the 'simon' user quit the sqlite3 tool once they have done their 
insert and see what happens.

How are sharing your database ?  A Windows share accessed over SMB ?  A Unix 
share accessed over NFS ?

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

Re: [sqlite] List record from one table and all matching in another

[Simon Slavin]

On 22 Sep 2013, at 4:06pm, James K. Lowden  wrote:

> The right answer is to join the tables in a single query and -- as you
> suggest --use application logic detect when a "new" item needs a new
> header.

Agreed.  So to summarise for the OP, the best solution is a mixture of the two 
that Igor and myself posted.  Do the SELECT that I posted to get the right 
order, with the JOIN that Igor posted to get the title section from when you 
move from one grocery to the next.

And that's why you want answers to your questions be posted to the list, folks, 
not directly to you.  Because sometimes one reader will be able to improve on 
another reader's answer.

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

Re: [sqlite] Regression: Query takes 10x longer when using version 3.8.x

[James K. Lowden]

On Sat, 21 Sep 2013 00:28:15 +0100
Simon Slavin  wrote:

> Your extremely long SELECT with its sixteen COALESCEs and eight LEFT
> OUTER JOINs suggests that you have a schema which doesn't really suit
> the 'shape' of your data.  Another hint pointing in the same
> direction is numbered database names.  I think you might simplify
> your SELECT, reduce your data footprint, and speed up your query by
> rethinking your schema.

If I could add to that, nearly all the rigamarole stems from the
parent/child relationship table.  These are nearly always wrong, and I
see no implication of such a relationship in the schema provided.  

So, the first question is whether or not that table is justified.  If
so, the second question is whether or not 8 is a magic number, because
the query will not reveal the 9th level of nesting.  

To process a recursive query, write the level 0 results to a temporary
table, and join that table back to the original query to insert the
next level.  Repeat as necessary until no rows are returned, then
select from the temporary table. To ensure correctness, use a
transaction.  

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

Re: [sqlite] List record from one table and all matching in another

[James K. Lowden]

On Sat, 21 Sep 2013 15:06:14 +0100
Simon Slavin  wrote:

> > If I "SELECT * FROM Groceries" to list all records, how do I
> > arrange a query to list each record in the groceries table with all
> > matching history items under each record.
> 
> You do it with two queries, not one.  In all seriousness, layout is
> the job of your programming language.  The database system just hands
> it data. Don't try to use SQL to do your layout for you, you'll end
> up with a huge SELECT statement that is difficult to understand and
> breaks any time you change anything.

Yes, SQL is not a layout/display language.  But that's not very good
support for the advice to use two queries.  

Joining two tables is hardly going to create one "huge" query.  More
important, in the general case two queries cannot be guaranteed to be
consistent: it is possible for a grocery item to have been deleted
while processing the history table.  

And then there's performance. Issuing per-row queries while processing
a results must be the dominant antipattern of our time.  Instead of a
single query and one trip to the DBMS, the application endures N
trips, with the attendant delay.  

The right answer is to join the tables in a single query and -- as you
suggest --use application logic detect when a "new" item needs a new
header.  

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

[sqlite] Fwd: Having trouble using sqlite to handle query that contains Chinese characters

[ME Support]

> Subject: Having trouble using sqlite to handle query that contains Chinese 
> characters
> 
> Dear Sir:
>  
> I was working on a windows 8 app, which involve using SQLite database. I 
> downloaded .dll from your website. I have some trouble when using sqlite to 
> handle query that contains Chinese characters.
>  
> When I debug the code, the strSQL variable looks correct to me.
>  
> SQLite.SQLiteAsyncConnection _dbConnection = new 
> SQLite.SQLiteAsyncConnection(_dbPath);
> ObservableCollection  result = new 
> ObservableCollection();
> ...
> result = new ObservableCollection(await 
> _dbConnection.QueryAsync(strSQL))
>  
> It seemed to me that the query I was trying to run eventually became the 
> following based on the result returned.
>  select column1, column2 from table where columnx like ‘%?%’
>  
> where “?” is where the chinese character should have been. I had no problem 
> getting Chinese characters from the database if the where clause doesn’t 
> contain any chinese character.
>  
> I run the same query in “SQLite Expert personal version” and I was able to 
> see the correct result returned.
>  
> So what can I do to get this problem fixed? Do you have any suggestion?
>  
> Thanks very much!
>  
>  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] multiple connection to the same DB

[Simon Slavin]

On 22 Sep 2013, at 12:30pm, olivier Ménard  wrote:

> We were far from a high concurrency use of sqlite, only 15 members, and using 
> only a single line insert. So i thought it should be ok.
> I'll continue to use sqlite but as the only administrator of the DB : 
> I'll try to give the permissions rw-r--r-- to my file, and hope it'll works.

See what Kees Nuyt wrote, then see



for more details.

The problem is not with your permissions.  The problem is that your network 
file system is has not implemented the part of file sharing that goes with two 
computers accessing the same file at the same time.  This is very common in 
network file systems: they do not implement file locking and updates correctly, 
and update a file only when it is closed.

Try having the 'simon' user quit the sqlite3 tool once they have done their 
insert and see what happens.

How are sharing your database ?  A Windows share accessed over SMB ?  A Unix 
share accessed over NFS ?

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

Re: [sqlite] a tool that allows you to graphically change sqlite schema

[William Drago]

I like SQLite Expert too. Very nice tool, you don't have to 
run Firefox to use it, and the personal edition is free. 
I've gotten a lot of good use out of it. Windows only is the 
one drawback.


-Bill

On 9/18/2013 5:09 AM, jcd wrote:


Is there a tool that allows you to graphically change 
sqlite schema as
simple as editing a spreadsheet? For example if I wanted 
to change a column
name from "my_driving_hours" to "driving_time", instead 
of writing a line
of code, I can just click on that column and type in the 
new name, letting
the tool automatically create a new database with the new 
schema and
migrate the old data into the new one. I found it very 
useful.


I am not sure if there exists such a tool after a long 
google search. Could

any pros give some thoughts? Many thanks.


Give SQLite Expert a try if you're on Windows. It might 
run well under Wine for Linux users (untested by me).


http://www.sqliteexpert.com/

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


-
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2013.0.3408 / Virus Database: 3222/6678 - Release 
Date: 09/18/13





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

Re: [sqlite] multiple connection to the same DB

[olivier Ménard]

We were far from a high concurrency use of sqlite, only 15 members, and using 
only a single line insert. So i thought it should be ok.
I'll continue to use sqlite but as the only administrator of the DB : 
I'll try to give the permissions rw-r--r-- to my file, and hope it'll works.


De : Kees Nuyt 

À : sqlite-users@sqlite.org 
Envoyé le : Dimanche 22 septembre 2013 10h25
Objet : Re: [sqlite] multiple connection to the same DB
 

On Sun, 22 Sep 2013 08:28:39 +0100 (BST), olivier Ménard
 wrote:

>Hi
>
>I'll try to give you more details
>We were all on Ubuntu, each colleague on a different computer
>
>with the sqlite command line tool i've created a db with 
>
>$ cd  ... sothat i'was located on a server directory shared with my 
>colleagues with the same permissions
>$ sqlite3 birth.sqlite   
 create table people(n,y);
 insert into people('olivier', 1969);
>
>then from other computers
>$ cd .. to the same directory
>$ sqlite3 birth.sqlite  
 insert into people('simon', 1960);
>
>finally from my computer :
 select * from people;
>
>n       y
>--   --
>'simon'  1960
>
>and olivier isn't in the DB.

Many network file systems do not behave well with SQLite:
http://sqlite.org/lockingv3.html#how_to_corrupt
That is especially true when the network file server and/or network file
client are heavily optimized for speed.

You can try to recompile SQLite to use dot-file locking:
http://www.sqlite.org/compile.html#enable_locking_style

If you need a database server, SQLite is not the perfect choice. In
those use cases, a database server like PostgresQL or MySQL or MariaDB
and its respective client program is more appropriate:
http://sqlite.org/whentouse.html

More info:
http://www.sqlite.org/search?q=locking

>Thank's for your help.
>Olivier.
>
>
>
>
> De : Simon Slavin 
>À : olivier Ménard ; General Discussion of SQLite Database 
> 
>Envoyé le : Mardi 10 septembre 2013 21h43
>Objet : Re: [sqlite] multiple connection to the same DB
> 
>
>
>On 10 Sep 2013, at 4:37pm, olivier Ménard  wrote:
>
>> I've tried with my colleagues to write data to the same SQLite DB-file  from 
>> differents accounts.
>> When someone added a new line in the DB, sometimes older existing data were 
>> lost as if they had never existed and sometimes not.
>> 
>> Why ?
>> 
>> Multiple access are maybe allowed only for reading ?
>
>What operating system are you using ?
>
>What software or library calls are you using ?  The SQLite shell tool ?  The C 
>API ?
>
>How are the computers you are using accessing the same file ?  Are you using 
>access across a network ?  If so, what network protocol are you using ?
>
>Simon.
>___
>sqlite-users mailing list
>sqlite-users@sqlite.org
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

-- 
Groet, Cordialement, Pozdrawiam, Regards,

Kees Nuyt

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

Re: [sqlite] multiple connection to the same DB

[Kees Nuyt]

On Sun, 22 Sep 2013 08:28:39 +0100 (BST), olivier Ménard
 wrote:

>Hi
>
>I'll try to give you more details
>We were all on Ubuntu, each colleague on a different computer
>
>with the sqlite command line tool i've created a db with 
>
>$ cd  ... sothat i'was located on a server directory shared with my 
>colleagues with the same permissions
>$ sqlite3 birth.sqlite   
 create table people(n,y);
 insert into people('olivier', 1969);
>
>then from other computers
>$ cd .. to the same directory
>$ sqlite3 birth.sqlite  
 insert into people('simon', 1960);
>
>finally from my computer :
 select * from people;
>
>n       y
>--   --
>'simon'  1960
>
>and olivier isn't in the DB.

Many network file systems do not behave well with SQLite:
http://sqlite.org/lockingv3.html#how_to_corrupt
That is especially true when the network file server and/or network file
client are heavily optimized for speed.

You can try to recompile SQLite to use dot-file locking:
http://www.sqlite.org/compile.html#enable_locking_style

If you need a database server, SQLite is not the perfect choice. In
those use cases, a database server like PostgresQL or MySQL or MariaDB
and its respective client program is more appropriate:
http://sqlite.org/whentouse.html

More info:
http://www.sqlite.org/search?q=locking

>Thank's for your help.
>Olivier.
>
>
>
>
> De : Simon Slavin 
>À : olivier Ménard ; General Discussion of SQLite Database 
> 
>Envoyé le : Mardi 10 septembre 2013 21h43
>Objet : Re: [sqlite] multiple connection to the same DB
> 
>
>
>On 10 Sep 2013, at 4:37pm, olivier Ménard  wrote:
>
>> I've tried with my colleagues to write data to the same SQLite DB-file  from 
>> differents accounts.
>> When someone added a new line in the DB, sometimes older existing data were 
>> lost as if they had never existed and sometimes not.
>> 
>> Why ?
>> 
>> Multiple access are maybe allowed only for reading ?
>
>What operating system are you using ?
>
>What software or library calls are you using ?  The SQLite shell tool ?  The C 
>API ?
>
>How are the computers you are using accessing the same file ?  Are you using 
>access across a network ?  If so, what network protocol are you using ?
>
>Simon.
>___
>sqlite-users mailing list
>sqlite-users@sqlite.org
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

-- 
Groet, Cordialement, Pozdrawiam, Regards,

Kees Nuyt

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

Re: [sqlite] multiple connection to the same DB

[olivier Ménard]

Hi

I'll try to give you more details
We were all on Ubuntu, each colleague on a different computer

with the sqlite command line tool i've created a db with 

$ cd  ... sothat i'was located on a server directory shared with my 
colleagues with the same permissions
$ sqlite3 birth.sqlite   
>>> create table people(n,y);
>>> insert into people('olivier', 1969);

then from other computers
$ cd .. to the same directory
$ sqlite3 birth.sqlite  
>>> insert into people('simon', 1960);

finally from my computer :
>>> select * from people;

n       y
--   --
'simon'  1960

and olivier isn't in the DB.


Thank's for your help.
Olivier.















 De : Simon Slavin 
À : olivier Ménard ; General Discussion of SQLite Database 
 
Envoyé le : Mardi 10 septembre 2013 21h43
Objet : Re: [sqlite] multiple connection to the same DB
 


On 10 Sep 2013, at 4:37pm, olivier Ménard  wrote:

> I've tried with my colleagues to write data to the same SQLite DB-file  from 
> differents accounts.
> When someone added a new line in the DB, sometimes older existing data were 
> lost as if they had never existed and sometimes not.
> 
> Why ?
> 
> Multiple access are maybe allowed only for reading ?

What operating system are you using ?

What software or library calls are you using ?  The SQLite shell tool ?  The C 
API ?

How are the computers you are using accessing the same file ?  Are you using 
access across a network ?  If so, what network protocol are you using ?

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