Re: [sqlite] Is there a simple command line data editor for sqlite3?

[RSmith]

Did you have a specific OS in mind?

Linux, MacOS and Windows all have a myriad of SQLite editors. A simple google would no doubt reveal a lot, but if you say which OS, 
I'm sure on here we can point out some good ones.


On 2014/09/30 19:23, c...@isbd.net wrote:

Is there such a thing?

Of course I know and use the sqlite3 command line utility but that's
just a way of running SQL from the command line.

I want some sort of wrapper that runs a SELECT to get the data from a
table, presents it to me in editable format and then does an UPDATE or
INSERT to put the changed data back.



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

[sqlite] Is there a simple command line data editor for sqlite3?

[cl]

Is there such a thing?

Of course I know and use the sqlite3 command line utility but that's
just a way of running SQL from the command line.

I want some sort of wrapper that runs a SELECT to get the data from a
table, presents it to me in editable format and then does an UPDATE or
INSERT to put the changed data back.

-- 
Chris Green
·

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

Re: [sqlite] group_concat query performance

[Kraijenbrink - FixHet - Systeembeheer]

Hi Rchard,

I've created 3 test samples. A C++ and two VB.NET variants. 

1. "SQLitePerfTest - C++ " runs very fast. 50.000 queries in 8 or 9 seconds;

2. "SQLitePerfTest - VB.net  runs very fast. 50.000 queries in 7 or 8 seconds. 
(Without the GROUP_CONCAT function that is;)

3. "SQLitePerfTest - VB.net  which runs slow, 50.000 queries in 5 or 6 minutes; 
 

Thank you for your time and effort and hope to hear from you.

With regards,

Peter Kraijenbrink



FixHet - Systeembeheer
Geestdorp 22-II
3444 BD  Woerden
Tel: 0348-410220
Gsm: 06 - 27231926
Web: www.fixhet.nl
Mail:  kraijenbr...@fixhet.nl

=== Disclaimer = 
 
Dit e-mailbericht is vertrouwelijk en uitsluitend bedoeld voor de 
geadresseerde. Indien u niet de geadresseerde bent wordt u verzocht de afzender 
hiervan in kennis te stellen en dit bericht te vernietigen. 
 
FixHet - Systeembeheer aanvaardt geen enkele aansprakelijkheid voor enigerlei 
schade voortvloeiend uit het gebruik en/of acceptatie van de inhoud van het 
bericht. Bij twijfel verzoeken wij u een kopie op te vragen. 
 
Deze e-mail is gescand op virussen met Kaspersky Antivirus. 
=

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Richard Hipp
Sent: donderdag 25 september 2014 17:46
To: General Discussion of SQLite Database
Subject: Re: [sqlite] group_concat query performance

On Thu, Sep 25, 2014 at 9:34 AM, Richard Hipp  wrote:

> Thanks for the schema.  Unfortunately, that was insufficient to 
> reproduce the problem.  Are you able to send me the actual database 
> file, via private email?
>


Thanks for sending the data.  But I'm still not able to reproduce the problem.  
I tried running this script against your database:

.open /home/drh/Downloads/Journal.dat
.tables
PRAGMA integrity_check;
.timer on
SELECT GROUP_CONCAT(Parent.fkintFolderID,'\') FilePath FROM tblFolderNestedSets 
Node
 , tblFolderNestedSets Parent
WHERE Node.intLeft BETWEEN Parent.intLeft AND Parent.intRight AND 
Parent.fkintSessionID = Node.fkintSessionID AND Node.fkintSessionID =  1817 AND 
Node.fkintFolderID  = 1937926; .print 
---
SELECT Parent.fkintFolderID FilePath
FROM tblFolderNestedSets Node
 , tblFolderNestedSets Parent
WHERE Node.intLeft BETWEEN Parent.intLeft AND Parent.intRight AND 
Parent.fkintSessionID = Node.fkintSessionID AND Node.fkintSessionID =  1817 AND 
Node.fkintFolderID  = 1937926;

The output I get back is:

tblCabinets  tblFiles tblSessions
tblDataParts tblFolderNames   vw_FileParts
tblFileNames tblFolderNestedSets  vw_Files
tblFileParts tblFolders   vw_Folders
ok

Run Time: real 0.000 user 0.84 sys 0.00
---
Run Time: real 0.000 user 0.61 sys 0.00

As you can see, neither query is returning any result and neither query is 
taking an measurable amount of time.

Do you have any suggestions on what I can do differently in order to recreate 
your problem?

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

Re: [sqlite] 50% faster than 3.7.17

[jose isaias cabrera]

"Simon Slavin" wrote...



On 24 Sep 2014, at 2:13pm, jose isaias cabrera  
wrote:


This would be a nice set of options. On my case, I would set all 
connections to our project to be" max_performance", as it is what we 
need.  Just thinking out loud.


How much max is max ?  Are you willing to give up ACID ?  Are you willing 
to have your database corrupted if there's power loss ?


Ok, back to reality... ;-), no, I will not.  Good questions, Simon.

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

Re: [sqlite] Will collate binary supports like query

[dd]

Thank you :-)

On Tue, Sep 30, 2014 at 3:44 PM, Simon Slavin  wrote:

>
> On 30 Sep 2014, at 12:32pm, dd  wrote:
>
> > My db already there in production. I have to do it programmatically.
> >
> > Can it be done with any sqlite apis/pragmas.
>
> Sure.  But you do need to drop the table at one point so nothing should be
> trying to use it.
>
> Depending on how your indexes, triggers and foreign keys are defined you
> can do something like
>
> CREATE TABLE newVersion ( here>)
> INSERT INTO newVersion SELECT * FROM originalName
> DROP originalName
> ALTER TABLE newVersion RENAME TO originalName
>
> You may then have to recreate any indexes triggers and foreign keys.
>
> Again, don't forget to take a backup before messing with anything.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query regarding " Sqlite database opening error "

[Clemens Ladisch]

Rohit Kaushal wrote:
> My application starts properly and works fine for about 15 mins. During
> this time, the database is created/opened, updated and then closed. The
> frequency of this update is about 1 sec. Then suddenly the database becomes
> inaccessible with sqlite3_open_v2 returning error code 14 for reasons
> unknown to me right now.

That the ~900th open fails might indicate that you are running out of
file handles.

> I make calls to following code fragments whenever an operation on database
> is required :
>
> void open_db()
> {
> ...
> rc = sqlite3_open_v2("mydb.db", &db,SQLITE_OPEN_CREATE | 
> SQLITE_OPEN_READWRITE, NULL);
> ...
> }

Why are you opening the database every time?  Just keep it open as long
as your program is running.

> void close_db()
> {
> sqlite3_close(db);
> }

Do you have evidence that this function is actually called, and with the
correct db pointer?


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

Re: [sqlite] Query regarding " Sqlite database opening error "

[Simon Slavin]

On 29 Sep 2014, at 2:19pm, Rohit Kaushal  wrote:

> I would really appreciate some pointers on this from your side as I am kind
> of stuck on this right now.

You might want to turn on extended error codes and do it again.



Also, please check the result returned by sqlite3_close and print an error 
message (like the one you already do for _open) if it doesn't return SQLITE_OK. 
 I don't know if it'll help diagnose the problem but it's the only think I can 
think of without further details.

In the meantime ...

Which version of SQLite are you using ?

Which operating system are you working under ?

What development environment/compiler are you using ?

How did you get SQLite into your code ?  Are you using the amalagamation source 
code or a library ?  If a library, which library ?

Don't worry about definitive numbers on all the above.  Just give us a little 
more details like the above.

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

[sqlite] Query regarding " Sqlite database opening error "

[Rohit Kaushal]

Dear Sir/Mam

While working with SQLITE, we are facing an issue which is as under:


My application starts properly and works fine for about 15 mins. During
this time, the database is created/opened, updated and then closed. The
frequency of this update is about 1 sec. Then suddenly the database becomes
inaccessible with sqlite3_open_v2 returning error code 14 for reasons
unknown to me right now. No other application is accessing this database
system.

I make calls to following code fragments whenever an operation on database
is required :

void open_db()
{
int rc= 1;
//printf(" \n Trying to create database .");

while(rc != 0)
{
rc = sqlite3_open_v2("mydb.db", &db,SQLITE_OPEN_CREATE |
SQLITE_OPEN_READWRITE, NULL);
if(rc != 0)
{
printf("\n stderr, ERROR: %s, rc = %d\n", sqlite3_errmsg(db),rc);
sqlite3_close(db);
 }
}
}


void close_db()
{
sqlite3_close(db);
}


I would really appreciate some pointers on this from your side as I am kind
of stuck on this right now.

Thanks and Regards

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

Re: [sqlite] Will collate binary supports like query

[Simon Slavin]

On 30 Sep 2014, at 12:32pm, dd  wrote:

> My db already there in production. I have to do it programmatically.
> 
> Can it be done with any sqlite apis/pragmas.

Sure.  But you do need to drop the table at one point so nothing should be 
trying to use it.

Depending on how your indexes, triggers and foreign keys are defined you can do 
something like

CREATE TABLE newVersion ()
INSERT INTO newVersion SELECT * FROM originalName
DROP originalName
ALTER TABLE newVersion RENAME TO originalName

You may then have to recreate any indexes triggers and foreign keys.

Again, don't forget to take a backup before messing with anything.

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

Re: [sqlite] Will collate binary supports like query

[dd]

My db already there in production. I have to do it programmatically.

Can it be done with any sqlite apis/pragmas.

On Tue, Sep 30, 2014 at 3:19 PM, Simon Slavin  wrote:

>
> On 30 Sep 2014, at 12:07pm, dd  wrote:
>
> > Hi Simon,
> >
> >>> However, frequent use of COLLATE often suggests that the original
> schema
> > was badly chosen.
> >  Any performance degradation?
>
> Having SQLite analyse the command, work out what 'COLLATE BINARY' means,
> and making the change means that it will take a little longer to execute
> the command.  But that should just be a few milliseconds, not a long time.
>
> > Is it possible to change "collate nocase" to "collate binary" in schema
> for
> > already existing db? Any alternative solution for this?
>
> If you need to change your TABLE definitions a little but keep your data,
> a good way to do it is to use the SQLite shell tool:
>
> 
>
> You can load up your database, then use the '.dump' command to dump it as
> a series of SQL commands to a text file.  Then you can use a text processor
> to go into the text file and edit any of the commands.  Then you can use
> the shell tool to create a new database file and '.read' the new SQL
> commands into it.
>
> Don't forget to take a backup before you start, just in case you mess up
> and type the wrong filename.  Like I've done once or twice.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Will collate binary supports like query

[Simon Slavin]

On 30 Sep 2014, at 12:07pm, dd  wrote:

> Hi Simon,
> 
>>> However, frequent use of COLLATE often suggests that the original schema
> was badly chosen.
>  Any performance degradation?

Having SQLite analyse the command, work out what 'COLLATE BINARY' means, and 
making the change means that it will take a little longer to execute the 
command.  But that should just be a few milliseconds, not a long time.

> Is it possible to change "collate nocase" to "collate binary" in schema for
> already existing db? Any alternative solution for this?

If you need to change your TABLE definitions a little but keep your data, a 
good way to do it is to use the SQLite shell tool:



You can load up your database, then use the '.dump' command to dump it as a 
series of SQL commands to a text file.  Then you can use a text processor to go 
into the text file and edit any of the commands.  Then you can use the shell 
tool to create a new database file and '.read' the new SQL commands into it.

Don't forget to take a backup before you start, just in case you mess up and 
type the wrong filename.  Like I've done once or twice.

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

Re: [sqlite] Will collate binary supports like query

[dd]

Hi Simon,

>>However, frequent use of COLLATE often suggests that the original schema
was badly chosen.
  Any performance degradation?

Is it possible to change "collate nocase" to "collate binary" in schema for
already existing db? Any alternative solution for this?

Thanks.

On Tue, Sep 30, 2014 at 2:09 PM, Simon Slavin  wrote:

>
> On 30 Sep 2014, at 7:03am, dd  wrote:
>
> >  Needs to support case-sensitive for case-insensitive sqlite
> > database field . To find exact case, COLLATE BINARY,  works very well.
> >
> >
> >
> >  What is the work around for like queries.  Needs to case-sensitive for
> > like queries
>
> 
>
> "The LIKE operator can be made case sensitive using the
> case_sensitive_like pragma."
>
> > and lessthan, greaterthan operators.
>
> 
>
> "upper(X)", "lower(X)", but also see COLLATE operator as discussed below.
>
> > OR
> >
> >  Is there anyway to change sqlite column's collating from NOCASE to
> BINARY?
>
> 
>
> The three collations available are BINARY, NOCASE and RTRIM.  (When NOCASE
> used with the higher Unicode characters you may have to think quite hard to
> figure out what's going on.)
>
> You can define collation for each column when you define a table.  This is
> almost always all that's necessary: most of the time data in a database
> seems to have an inherent collation that suits it.  You can define
> collation for each index element when you define an index (though you
> probably shouldn't: you should get it right when you define the table).
> You cannot change either of these without dropping and recreating the table
> or index.
>
> You can use a COLLATE operator for each operand in any expression.  For
> instance ...
>
> SELECT x FROM t1 WHERE a = b COLLATE RTRIM ORDER BY x COLLATE RTRIM
>
> However, frequent use of COLLATE often suggests that the original schema
> was badly chosen.  The only justified use of a lot of COLLATEs I ever saw
> was in a database which was designed to process a lot of text for someone
> who was doing research into words and how they are used.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Will collate binary supports like query

[Simon Slavin]

On 30 Sep 2014, at 7:03am, dd  wrote:

>  Needs to support case-sensitive for case-insensitive sqlite
> database field . To find exact case, COLLATE BINARY,  works very well.
> 
> 
> 
>  What is the work around for like queries.  Needs to case-sensitive for
> like queries



"The LIKE operator can be made case sensitive using the case_sensitive_like 
pragma."

> and lessthan, greaterthan operators.



"upper(X)", "lower(X)", but also see COLLATE operator as discussed below.

> OR
> 
>  Is there anyway to change sqlite column's collating from NOCASE to BINARY?



The three collations available are BINARY, NOCASE and RTRIM.  (When NOCASE used 
with the higher Unicode characters you may have to think quite hard to figure 
out what's going on.)

You can define collation for each column when you define a table.  This is 
almost always all that's necessary: most of the time data in a database seems 
to have an inherent collation that suits it.  You can define collation for each 
index element when you define an index (though you probably shouldn't: you 
should get it right when you define the table).  You cannot change either of 
these without dropping and recreating the table or index.

You can use a COLLATE operator for each operand in any expression.  For 
instance ...

SELECT x FROM t1 WHERE a = b COLLATE RTRIM ORDER BY x COLLATE RTRIM

However, frequent use of COLLATE often suggests that the original schema was 
badly chosen.  The only justified use of a lot of COLLATEs I ever saw was in a 
database which was designed to process a lot of text for someone who was doing 
research into words and how they are used.

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

Re: [sqlite] Will collate binary supports like query

[dd]

@RSmith, yes. nice.

Is it possible to change "collate nocase" to "collate binary" in schema for
already existing db?

On Tue, Sep 30, 2014 at 1:44 PM, RSmith  wrote:

>
> On 2014/09/30 09:03, dd wrote:
>
>> I just got below pragma:
>> pragma case_sensitive_like = true
>>
>>  As an aside... The beauty of it being a Pragma is that you are not stuck
> with a single method - you can switch it on and off at a whim... Maybe even
> provide a GUI interface to switch modes.
> ___
> 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] Will collate binary supports like query

[RSmith]

On 2014/09/30 09:03, dd wrote:

I just got below pragma:
pragma case_sensitive_like = true

As an aside... The beauty of it being a Pragma is that you are not stuck with a single method - you can switch it on and off at a 
whim... Maybe even provide a GUI interface to switch modes.

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

Re: [sqlite] 2 joins on one table

[Paul Sanderson]

Lovely - thanks

On 30 September 2014 00:14, David Empson  wrote:

>
> On 30/09/2014, at 12:04 pm, Paul Sanderson 
> wrote:
>
> > I two tables of the form
> >
> > create table1 (person1 text, person2 text)
> > create table2 (person text, picture blob)
> >
> > Is it possible to create a join so I can get a resultant dataset of the
> form
> >
> > person1, person1picture, person2, person2picture
>
> SELECT person1, p1.picture, person2, p2.picture FROM table1 JOIN table2 AS
> p1 ON table1.person1 = p1.person JOIN table2 AS p2 ON table1.person2 =
> p2.person;
>
> The trick is to use table aliases (AS) to allow joining twice to the same
> table with different criteria, and then picking columns from the
> appropriate instance.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786+44 (0)1326 572786+44 (0)1326 572786+44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?190-SQLite-Recovery -
Deleted SQLite recovery
http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC
processing made easy
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Will collate binary supports like query

[dd]

I just got below pragma:
   pragma case_sensitive_like = true



On Tue, Sep 30, 2014 at 10:03 AM, dd  wrote:

> Hi,
>
>   Needs to support case-sensitive for case-insensitive sqlite
> database field . To find exact case, COLLATE BINARY,  works very well.
>
>
>
>   What is the work around for like queries.  Needs to case-sensitive for
> like queries and lessthan, greaterthan operators.
>
>   OR
>
>   Is there anyway to change sqlite column's collating from NOCASE to
> BINARY?
>
>
>
> sqlite> .schema
> CREATE TABLE t (Id INTEGER PRIMARY KEY AUTOINCREMENT, data TEXT COLLATE
> NOCASE);
>
> sqlite> select * from t;
> Id|data
> 1|abcd
> 2|defg
> 3|ABCD
> 4|AbCd
> 5|aBCD
> 6|Abcd
> 7|abcd
>
> sqlite> select * from t where (data = 'abcd' collate binary);
> Id|data
> 1|abcd
> 7|abcd
>
>  // Is it right way to use collate binary for '<' and '>' operations
> sqlite> select * from t where data < 'a' collate binary;
> Id|data
> 3|ABCD
> 4|AbCd
> 6|Abcd
>
> sqlite> select * from t where data > 'a' collate binary;
> Id|data
> 1|abcd
> 2|defg
> 5|aBCD
> 7|abcd
>
>
> sqlite> select * from t where data like 'a%' collate binary; //not working
> as expected. any alternative.
> Id|data
> 1|abcd
> 3|ABCD
> 4|AbCd
> 5|aBCD
> 6|Abcd
> 7|abcd
>
> Thanks.
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users