[sqlite] Adding PRAGMA=UTF-8 makes INSERT slower?

[Hayden Livingston]

I was getting garble in my SQLite database, so I switched
PRAGMA=UTF-16 on for my INSERT statements. These are getting prepared.

I noticed my total time dramatically increased.

I then switched to UTF-8 thinking it's the increased writes causing
it, no noticeable difference, i.e. it's just as slow as PRAGMA=UTF-16.

Removing PRAGMA from my INSERT statements brings back the performance.

Any ideas?

[sqlite] storing data across multiple tables

[J Decker]

Don't knwo if you want to see the results from all tables in one row (join)
or results from all tables as multiple rows (union) I'd think that if the
timestamps were similar, order by with UNION would give something like you
need?

A vague idea of the concept would help.

On Fri, Jul 17, 2015 at 9:59 AM, Hayden Livingston 
wrote:

>  Heh, each source defines their own table definition, and they do
> match sometimes, but sometimes they don't.
>
> People don't always query their columns, they usually just go find
> string in column, so I see your point, believe me. The programmer in
> me says what if they need to query > X ... then I'll be string parsing
> all the rows that their type belongs to.
>
> On Fri, Jul 17, 2015 at 9:46 AM, Simon Slavin 
> wrote:
> >
> > On 17 Jul 2015, at 5:27pm, Hayden Livingston 
> wrote:
> >
> >> Sorry, they do not have the same definition. They are different
> >> definitions. I just want to view the data in a view such that all rows
> >> from all tables can be seen sorted by time.
> >
> > You have designed 600 to 800 different table definitions for one program
> ?  Really ?  How big is your breain to be able to understand them all at
> the same time ?
> >
> > It is far simpler to figure out how to put your data into one table than
> it is to keep merging your tables every time you do a SELECT.
> >
> > Could you use an affinity of NONE in some columns and put whatever data
> you like in them ?
> >
> > Simon.
> > ___
> > 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
>

[sqlite] storing data across multiple tables

[Simon Slavin]

On 17 Jul 2015, at 5:27pm, Hayden Livingston  wrote:

> Sorry, they do not have the same definition. They are different
> definitions. I just want to view the data in a view such that all rows
> from all tables can be seen sorted by time.

You have designed 600 to 800 different table definitions for one program ?  
Really ?  How big is your breain to be able to understand them all at the same 
time ?

It is far simpler to figure out how to put your data into one table than it is 
to keep merging your tables every time you do a SELECT.

Could you use an affinity of NONE in some columns and put whatever data you 
like in them ?

Simon.

[sqlite] Unable to find the file 'memleak.txt'

[Sairam Gaddam]

> Try "locate memleak" if the file is not present in the working directory
> of the process running the test.
>
>
I tried that but there is no file named memleak.txt in the whole system !!!

[sqlite] Sqlite open with SQLITE_OPEN_READWRITE mode, but sometimes write failed with SQLITE_READONLY

[Clemens Ladisch]

?? wrote:
> sometimes step(3) failed in some mobile phone, the return code is 
> "SQLITE_READONLY"

Does sqlite3_extended_errcode() return a more specific error code?


Regards,
Clemens

[sqlite] why does SQLite support VARCHAR(X) when the doc says it ignores it?

[Drago, William @ CSG - NARDA-MITEQ]

I am one of those people. I use SQLite and MS SQL Server and it is very 
convenient to have this cross compatibility.

--
Bill Drago
Staff Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / William.Drago at L-3COM.com

> Lots of people take SQL code originally intended for other SQL engines,
> start up SQLite and want it to work first time.  So it does.  It won't
> perfectly copy the behaviour of engines which respect the '255' but
> it's good enough to assure the programmer that SQLite is worth using.
>
> Simon.
> ___
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] storing data across multiple tables

[Bernardo Sulzbach]

> I have multiple tables of data already, say TableA, TableB. These tables have 
> different representations, one may contain a TEXT column, the other may 
> contain and INTEGER column, but all of them contain an INTEGER field called 
> time, which is unique.

I don't understand your specifications well enough to give you a
solution, but if you have many tables with (Time IPK, Number INT) and
many with (Time IPK, String TEXT) you can avoid creating identical
tables (by column definitions) by merging these tables together.

[sqlite] Unable to find the file 'memleak.txt'

[Dominique Pellé]

Richard Hipp  wrote:

> On 7/17/15, Sairam Gaddam  wrote:
>> I ran the test files which are present in SQLite for my custom build and I
>> got some memory leaks.
>> I got a message like,
>>
>> Writing unfreed memory log to "./memleak.txt"
>>
>> But I couldn't able to locate that file.
>> Can anyone kindly tell where to find that file or where the unfreed memory
>> log is written ???
>
> I think memleak.txt is only created if you compile with -DSQLITE_MEMDEBUG.
>
> Use valgrind instead.  It works much better.

Or use the leak sanitizer (part of address sanitizer) which is faster
than valgrind:

- compile your program with gcc or clang with: -fsanitize=address
-fno-omit-frame-pointer
- add detect_leaks=1 to ASAN_OPTIONS environment variable. Ex:
export 
ASAN_OPTIONS=abort_on_error=1:detect_stack_use_after_return=1:check_initialization_order=1:strict_init_order=0:color=1:detect_leaks=1:detect_odr_violation=2
- run you program. It will report leaks if any, and other memory bugs.

https://code.google.com/p/address-sanitizer/wiki/LeakSanitizer
https://code.google.com/p/address-sanitizer/wiki/Flags

Regards
Dominique

[sqlite] Unable to find the file 'memleak.txt'

[Sairam Gaddam]

I ran the test files which are present in SQLite for my custom build and I
got some memory leaks.
I got a message like,

Writing unfreed memory log to "./memleak.txt"

But I couldn't able to locate that file.
Can anyone kindly tell where to find that file or where the unfreed memory
log is written ???

[sqlite] storing data across multiple tables

[Simon Slavin]

On 17 Jul 2015, at 3:22am, Hayden Livingston  wrote:

> So, In my application code I'm going to through loop through all the
> tables. The table count is usually high hundreds (~600-800)

Why do you have so many tables which, if I understand your post right, have the 
same column definitions ?  You can't have thought up 800 different table 
definitions for one program.  Why isn't all your data in one big table using an 
extra column to indicate its category ?

Simon.

[sqlite] Sqlite open with SQLITE_OPEN_READWRITE mode, but sometimes write failed with SQLITE_READONLY

[????]

hi, 
I'm using sqlite in android system recently, here is how my programe running: 

1: there is an original sqlite db in apk 
2: when apk installed, I copy the db file from apk to a writable space, simply 
SD Card in android system 
3: download some files from internet and then replace them to sqlite if needed 
4: all things above is running in main thread

the software running well in most cases, but sometimes step(3) failed in some 
mobile phone, the return code is "SQLITE_READONLY" 
I'm very confuse about it and search google but finds nothing 

My codes is something like this: 

==
[1]OPEN 

int open_flags = SQLITE_OPEN_READWRITE| SQLITE_OPEN_FULLMUTEX| 
SQLITE_OPEN_SHAREDCACHE; 
if (PathTool::IsFileExsit(db_file_name.c_str())) 
{ 
sqlite3_open_v2(db_file_name.c_str(), _db, open_flags, ...); 
... 
} 
 

[2]WRITE 

std::string sql_info; 
sqlite3_stmt *statement = 0; 
bool is_suc = false; 
sql_info = "replace into file_info( hash0, ...) values(?,...)"; 
int ret = sqlite3_prepare_v2(sqlite_db, sql_info.c_str(), -1, 
, NULL); 
if (ret == SQLITE_OK) 
{ 
sqlite3_bind_int(statement, 1, file_info.hash_info.hash0); 
... 

ret = sqlite3_step(statement); 
if(ret == SQLITE_DONE || ret == SQLITE_OK) 
{ 
 is_suc = true; 
} 
else 
{ 
CCLog("sqlite step error, ret code: %d", ret); 
} 
} 
else 
{ 
CCLog("sqlite prepare error, ret code: %d", ret); 
} 
 ==
the code is failed in function sqlite3_step and return code is 
"SQLITE_READONLY" 


we all know that, files in apk is readonly 
firstly, I doubt that file property is inherited from its original file and it 
will leads the error 
so I try to make sure the file copy from apk is writable, codes(java): 

public static boolean setFilePrilivege(String path) 
  { 
File file = new File(path); 
boolean ret = false; 
if (file.exists()) 
{ 
try 
{ 
ret = file.setWritable(true); 
ret &= file.setReadable(true); 
ret &= file.setExecutable(true); 
} 
catch(Exception e) 
{ 
e.printStackTrace(); 
} 

return ret; 
} 
return false; 
  } 

before write, I also detect whether the file is writable in operating system, 
codes(java): 
public static boolean isFileWritable(String path) 
{ 
File file = new File(path); 
if (file.exists()) 
{ 
return file.canWrite(); 
} 
return false; 
} 

finally, I find that even I set the file writable and operating  system tells 
me that file is writable too, the results is still failed,  with return code: 
"SQLITE_READONLY" 



Please help me, thanks 



Best Regards 
ckwei

[sqlite] storing data across multiple tables

[Hayden Livingston]

 Heh, each source defines their own table definition, and they do
match sometimes, but sometimes they don't.

People don't always query their columns, they usually just go find
string in column, so I see your point, believe me. The programmer in
me says what if they need to query > X ... then I'll be string parsing
all the rows that their type belongs to.

On Fri, Jul 17, 2015 at 9:46 AM, Simon Slavin  wrote:
>
> On 17 Jul 2015, at 5:27pm, Hayden Livingston  
> wrote:
>
>> Sorry, they do not have the same definition. They are different
>> definitions. I just want to view the data in a view such that all rows
>> from all tables can be seen sorted by time.
>
> You have designed 600 to 800 different table definitions for one program ?  
> Really ?  How big is your breain to be able to understand them all at the 
> same time ?
>
> It is far simpler to figure out how to put your data into one table than it 
> is to keep merging your tables every time you do a SELECT.
>
> Could you use an affinity of NONE in some columns and put whatever data you 
> like in them ?
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] storing data across multiple tables

[Hayden Livingston]

Sorry, they do not have the same definition. They are different
definitions. I just want to view the data in a view such that all rows
from all tables can be seen sorted by time.

Obviously SQLite cannot do this since the table definitions are
different, and I'd have to write some application code. I'm trying to
understand what's the best way to use SQLite for this purpose.

It's almost as if I need a table that has "pointers" to rows of other
tables. I'm quite astonished by the flexibility of SQLite, so maybe it
is possible to shoe-horn such a feature?

On Fri, Jul 17, 2015 at 3:48 AM, Simon Slavin  wrote:
>
> On 17 Jul 2015, at 3:22am, Hayden Livingston  
> wrote:
>
>> So, In my application code I'm going to through loop through all the
>> tables. The table count is usually high hundreds (~600-800)
>
> Why do you have so many tables which, if I understand your post right, have 
> the same column definitions ?  You can't have thought up 800 different table 
> definitions for one program.  Why isn't all your data in one big table using 
> an extra column to indicate its category ?
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Improving performance by removing MapParameter calls?

[Hayden Livingston]

In my program I'm spending most of my time in this function:

System.Data.SQLite!SQLiteStatement.MapParameter

This function is doing string comparisons to map the correct parameter.

I do put my SQL command text into a statement and prepare it. I was
wondering if there is a way to do so for my parameters also. Obviously
parameters vary, so it'd be nice to have a parameterized function?

Or is that essentially what a stored procedure is?

[sqlite] best way to have a constraint over 2 fields

[Michele Pradella]

Hi all, I have a question about the best way to have a constrain on a table.
think to a DB with 2 fields in the table ColA and ColB and you don't 
want to have multiple records with the same ColA,ColB couple
so we can
CONSTRAINT ColA_ColB UNIQUE (ColA,ColB) in the create table
or
CREATE UNIQUE INDEX uq_ColA_ColB ON table(ColA, ColB)

which one do you think is better in terms of performance? keep in mind 
the table have millions of records and SELECT is the most frequent operation

-- 
Selea s.r.l.


Michele Pradella R


SELEA s.r.l.

Via Aldo Moro 69
Italy - 46019 Cicognara (MN)
Tel +39 0375 889091
Fax +39 0375 889080
*michele.pradella at selea.com* 
*http://www.selea.com*

[sqlite] Unable to find the file 'memleak.txt'

[Hick Gunter]

Try "locate memleak" if the file is not present in the working directory of the 
process running the test.

-Urspr?ngliche Nachricht-
Von: Sairam Gaddam [mailto:gaddamsairam at gmail.com]
Gesendet: Freitag, 17. Juli 2015 08:44
An: General Discussion of SQLite Database
Betreff: [sqlite] Unable to find the file 'memleak.txt'

I ran the test files which are present in SQLite for my custom build and I got 
some memory leaks.
I got a message like,

Writing unfreed memory log to "./memleak.txt"

But I couldn't able to locate that file.
Can anyone kindly tell where to find that file or where the unfreed memory log 
is written ???
___
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] best way to have a constraint over 2 fields

[Hick Gunter]

A UNIQUE constraint is implemented in terms of a UNIQUE index anyway. The 
difference is that you can DROP INDEX but cannot DROP CONSTRAINT.

-Urspr?ngliche Nachricht-
Von: Michele Pradella [mailto:michele.pradella at selea.com]
Gesendet: Freitag, 17. Juli 2015 08:14
An: sqlite-users at mailinglists.sqlite.org
Betreff: [sqlite] best way to have a constraint over 2 fields

Hi all, I have a question about the best way to have a constrain on a table.
think to a DB with 2 fields in the table ColA and ColB and you don't want to 
have multiple records with the same ColA,ColB couple so we can CONSTRAINT 
ColA_ColB UNIQUE (ColA,ColB) in the create table or CREATE UNIQUE INDEX 
uq_ColA_ColB ON table(ColA, ColB)

which one do you think is better in terms of performance? keep in mind the 
table have millions of records and SELECT is the most frequent operation

--
Selea s.r.l.


Michele Pradella R


SELEA s.r.l.

Via Aldo Moro 69
Italy - 46019 Cicognara (MN)
Tel +39 0375 889091
Fax +39 0375 889080
*michele.pradella at selea.com* 
*http://www.selea.com*
___
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] Unable to find the file 'memleak.txt'

[Richard Hipp]

On 7/17/15, Sairam Gaddam  wrote:
> I ran the test files which are present in SQLite for my custom build and I
> got some memory leaks.
> I got a message like,
>
> Writing unfreed memory log to "./memleak.txt"
>
> But I couldn't able to locate that file.
> Can anyone kindly tell where to find that file or where the unfreed memory
> log is written ???

I think memleak.txt is only created if you compile with -DSQLITE_MEMDEBUG.

Use valgrind instead.  It works much better.

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] why does SQLite support VARCHAR(X) when the doc says it ignores it?

[Simon Slavin]

On 16 Jul 2015, at 10:46pm, Hayden Livingston  wrote:

> It seems that
> 
> CREATE TABLE A( something varchar(255) )
> 
> it just
> 
> CREATE TABLE A( something text )
> 
> Why have it at all?

Lots of people take SQL code originally intended for other SQL engines, start 
up SQLite and want it to work first time.  So it does.  It won't perfectly copy 
the behaviour of engines which respect the '255' but it's good enough to assure 
the programmer that SQLite is worth using.

Simon.