Re: [sqlite] A crash bug in sqlite

[Rui Zhong]

I tried in latest trunk version which check in
commit 926f796e8feec15f3836aa0a060ed906f8ae04d3 and it crashed.
I did not see any more recent commit in GITHUB. Could you please
double check it?




Richard Hipp  于2019年12月9日周一 下午1:23写道：

> On 12/9/19, Rui Zhong  wrote:
> > Hi,
> > We found this bug can be triggered again after fix.
>
> Yes.  I discovered the same thing independently.  The previous fix was
> subtly wrong.  Please try the latest trunk version.
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A crash bug in sqlite

[Rui Zhong]

Hi,
We found this bug can be triggered again after fix.
PoC and sqlite version info had been attached as follow.
--

SQLite version 3.31.0 2019-12-09 17:14:48
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE v0 ( v1 DOUBLE CHECK( ( v1 IN ( NULL   )  )  ) , v2
UNIQUE AS( v1 > v1  )  ) ;
sqlite> INSERT INTO v0 VALUES ( 10  ) ON CONFLICT DO NOTHING ;
sqlite> SELECT 10.10 , 10 FROM v0 CROSS JOIN v0 USING ( v1 ) ;
[1]141687 segmentation fault (core dumped)

-------



Thanks,

Yongheng & Rui

Richard Hipp  于2019年12月9日周一 下午12:15写道：

> On 12/9/19, Yongheng Chen  wrote:
> > Hi,
> >
> > We found a crash bug in sqlite of master branch.
>
> Should now be fixed on trunk.
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Creating fiels

[Rui Fernandes]

Thank you very much Ryan :)
I'll try it tomorrow.

Kind regards,

Miguel


On Fri, Jul 18, 2014 at 10:37 PM, RSmith <rsm...@rsweb.co.za> wrote:

>
> On 2014/07/18 22:56, Rui Fernandes wrote:
>
>> I already know how to import a csv file, and save it in SQLite format.
>> But how can I define the time of variable in the fields since it assumes
>> all of them are TEXT?
>>
>
> I assume you mean "type" of variable (and not "time") - else see the reply
> from Mr. Griggs.
>
> If you mean type - this is not possible with importing a CSV from scratch
> via sqlite3.exe because we know nothing of the file being imported before
> it is imported - at which point the schema already needs to exist.
>
> It also depends how often you need to do this. Do you only want to import
> the data once, or at least, only need to specify the schema once? Or is it
> different schemata every time over which you need some control of the
> variable types?
>
> Either way, one possibility is to define the table manually before
> importing the data. Let's assume you have data like this:
>
> rTime, sName, iAge
> 2014-07-14 15:33:22,John Smith,30
> 2014-07-14 16:31:25,William Gates,43
>
> If you simply import it to a new non-existing table, it will all have TEXT
> affinity, as you already noticed.
>
> but if you do first as an example table schema:
> CREATE TABLE people_db (
> rTime NUMERIC NOT NULL,
> sName TEXT COLLATE NOCASE,
> iAge INT DEFAULT 0
> );
>
> and then import the same data as above, you ill now have a correctly typed
> structure.
>
> Important, you can do this before importing, or after importing. To
> explain the "after importing" scenario, imagine you imported the csv to a
> table named "import_temp", knowing it will have those columns all as text.
>
> Now you can run simply the schema creation again, followed by an import
> from the other DB rather than the csv, like this:
> CREATE TABLE people_db (
> rTime NUMERIC NOT NULL,
> sName TEXT COLLATE NOCASE,
> iAge INT DEFAULT 0
> );
> INSERT INTO people_db (rTime,sName,iAge) SELECT CAST(rTime AS NUMERIC),
> sName, CAST(iAge AS INT) FROM import_temp;
> DROP TABLE import_temp;
>
> Maybe wrapping all in a Transaction.
>
> sName is already Text, so no need to cast. I am not even sure if the casts
> are needed at all, having set the Schema correctly, but it might highlight
> where the csv contain invalid types of data (i.e. which cannot be type-cast
> as needed).
>
> This is rather easy to do in a script too, as long as you know before the
> time what the data types should be.
>
> Another method, if you have very complicated tables that you will only
> import once, is to maybe use an importer tool, almost all DB admin type
> programs have some flavour of it available. We could suggest some if this
> is a viable route.
>
> Hope it helps,
> Ryan
>
> (I have not tested any of the posted SQL above, but if it is faulty,
> should be easy to fix)
>
>
> ___
> 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] Creating fiels

[Rui Fernandes]

I already know how to import a csv file, and save it in SQLite format.
But how can I define the time of variable in the fields since it assumes
all of them are TEXT?

Where can I find this information? And do I create this before the input -
how?

Kind regards,

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

[sqlite] Importing CSV file

[Rui Fernandes]

Hi,

I think I'm getting a bunch of attached fields. Everything is added to the
same line, without separators. I've seen it when I read it. I've used:

.mode csv table1
.import data.csv table1
.save Data.dat


Kind regards,

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

Re: [sqlite] Importing a cvs file

[Rui Fernandes]

Hi,

I think I'm getting a bunch of attached fields. Everything is added to the
same line, without separators. I've seen it when I read it.

Kind regards,

Miguel Fernandes


On Fri, Jul 18, 2014 at 4:49 PM, RSmith  wrote:

>
> On 2014/07/18 16:53, Jonathan Leslie wrote:
>
>> I have a similar situation about to happen.  I also have the issue where
>> one of the data fields is a text section that the user fills in and of
>> course, he is free to put commas in this field.   I believe that this will
>> result in higgly-piggly (that's a technical term) when I do the import.
>> What I do is I never use a CSV file as a "Comma separated Values" file but
>> rather as a "Character separated Values"  file, but rather use a Character,
>> º (0186), a legal character but not one on the keyboard, as the
>> separator character.  You can set Excel to do this automatically, and it
>> makes life a whole lot easier.   I believe with sqlite the >.separator "º"<
>> directive will allow the import correctly, and keep the commas in place in
>> the data fields.
>>
>
> Hi Jonathan, while this is a nice solution, it is not a perfect one. What
> if a user does enter that character? (Even if not directly linked to a
> keyboard key, it can happen). The very correct way to do it is to simply
> use quoting, which preserves the text specifically, whatever characters
> were used. A correct CSV format can hold any data (though most readers
> would have difficulty reading Chr(0) values, but that is an implementation
> issue). More importantly, Excel, Open Office Calc etc, all knows exactly
> how to export this correctly and without the higgly-pigglies. The only
> place I have seen confusion is where you export it in a country that uses
> semi-colon as a separator (yes it happens, mostly in the Netherlands) and
> then try to read in another country where the locality values are different
> (comma mostly).
>
> Mostly the problems experienced by people is that they make some home-brew
> CSV importer that does not realise how to correctly read output from a
> standards-based exporter such as Excel, and then try to change things like
> separation or quoting methods to "fix" it after the fact.
>
> The method described by the RFC for CSV is quite genius ( I think RFC2048,
> but not sure now and not important, but google will know if you need it) so
> let me explain a simple way of making csv files.
>
> The Important bits to remember is:
>
> 1 - Header, first line of the CSV must be the header, must contain field
> names, unless you do not have those or they are implicit, such as in a
> log-type listing or import to existing structure- but any standard importer
> will expect row 1 to contain field-identifiers if previously unknown.
>
> 2 - Every row must be separated by whichever Line-separation character(s)
> used by the operating system - this sometimes causes problems.
>  Carriage-Return-Linefeed (aka CR/LF) pairs (0x0D+0x0A) are mostly used in
> Windows, *nix environments prefer to use just the Linefeed (LF) character
> (0x0A). The Windows-way allows other linefeeds or carriage returns to be
> inside the text and not affect line-termination, and the *nix method is
> more lightweight in data terms, so both have advantages. Important to note
> that a line-separator may appear inside a set of quotes which makes it
> simply part of the value, and not indication of line termination - this is
> the biggest problem with homegrown importers I've seen, they all try read
> the file line-by-line which breaks badly when a field contains such a CR/LF.
>
> 3 - if a field starts with a Quote (after any leading/trailing white-space
> was stripped) any and all characters up to the next quote is part of the
> field value, even if they are separators (; or , or CR or LF) or
> white-space characters (such as SPACE/TAB/VTAB/etc. - these are stripped
> when leading/trailing field values, unless in quotes) and when we come to
> the next Quote (typically " or 0x22, but may differ between
> implementations), we check whether it's immediately followed by another
> quote, which means simply add the actual quote char and continue, or not,
> which means the end of the field. (Note: Any text after the ending quote in
> a quoted field and before the next field separator ( ; or , ) is ignored,
> one can use this quirk to add some comments or directives even).
>
> 4 - If you are writing fields, if your field contains any Quote
> characters, any separators or leading/trailing white-space characters that
> must not be discarded, it must be quoted. The best method for quoting a
> string to be valid CSV field data is like this pseudo code:
>
> QuoteChar = Char(0x22); // Or whatever is used for quoting in your system
> for i = last index of value down to first index:   // Go downward
> because it allows inserting without messing up final index
>   if  value[i] = QuoteChar then insert QuoteChar into value at position i;
>// Duplicate any existing 

Re: [sqlite] Importing a cvs file

[Rui Fernandes]

It's the header, right? Must contain the fields names.

Am I right?

Kind regards,

Miguel Fernandes


On Fri, Jul 18, 2014 at 4:15 PM, Rui Fernandes <rui.kep...@gmail.com> wrote:

> Hi,
>
> Thaks for the help, and the links
> Here are the 2 first lines of the cvs file:
>
> Costa de Xurius,T.SLP,42.5,1.48333,AD,,,0,,Europe/Andorra,1.0,2.0,1.0
> Font de la Xona,H.SPNG,42.55003,1.44986,AD,Parroquia de la
> Massana,,0,,Europe/Andorra,1.0,2.0,1.0
>
> I've used mode, and import.
> But I've got the error: "failed:duplicate column name"
>
> Regards,
>
> Miguel Fernandes
>
>
> On Fri, Jul 18, 2014 at 3:44 PM, Richard Hipp <d...@sqlite.org> wrote:
>
>> On Fri, Jul 18, 2014 at 10:23 AM, Rui Fernandes <rui.kep...@gmail.com>
>> wrote:
>>
>> > Greetings from Portugal,
>> >
>> > I made my firt import of a cvs file to the SQLite, and save it as a
>> file.
>> > My newbie question is that Sqlite is assuming every line as a record of
>> > text, not separating the fields
>> > Must the text fiels be surrounded by "? And it will assume the . as
>> decimal
>> > point?
>> >
>>
>> Yes, SQLite always assumes "." is your decimal point.  If you have data
>> using "," as the decimal point, you'll have to convert it first.
>>
>> Instructions for doing CVS import on SQLite are at
>> http://www.sqlite.org/cli.html#csv
>>
>>
>>
>> >
>> > Another question is the password: is it possible to place a password to
>> > protect the database?
>> >
>>
>> There is a (non-free) extension for that:
>> http://www.hwaci.com/sw/sqlite/see.html
>>
>>
>> --
>> 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] Importing a cvs file

[Rui Fernandes]

Hi,

Thaks for the help, and the links
Here are the 2 first lines of the cvs file:

Costa de Xurius,T.SLP,42.5,1.48333,AD,,,0,,Europe/Andorra,1.0,2.0,1.0
Font de la Xona,H.SPNG,42.55003,1.44986,AD,Parroquia de la
Massana,,0,,Europe/Andorra,1.0,2.0,1.0

I've used mode, and import.
But I've got the error: "failed:duplicate column name"

Regards,

Miguel Fernandes


On Fri, Jul 18, 2014 at 3:44 PM, Richard Hipp <d...@sqlite.org> wrote:

> On Fri, Jul 18, 2014 at 10:23 AM, Rui Fernandes <rui.kep...@gmail.com>
> wrote:
>
> > Greetings from Portugal,
> >
> > I made my firt import of a cvs file to the SQLite, and save it as a file.
> > My newbie question is that Sqlite is assuming every line as a record of
> > text, not separating the fields
> > Must the text fiels be surrounded by "? And it will assume the . as
> decimal
> > point?
> >
>
> Yes, SQLite always assumes "." is your decimal point.  If you have data
> using "," as the decimal point, you'll have to convert it first.
>
> Instructions for doing CVS import on SQLite are at
> http://www.sqlite.org/cli.html#csv
>
>
>
> >
> > Another question is the password: is it possible to place a password to
> > protect the database?
> >
>
> There is a (non-free) extension for that:
> http://www.hwaci.com/sw/sqlite/see.html
>
>
> --
> 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

[sqlite] Importing a cvs file

[Rui Fernandes]

Greetings from Portugal,

I made my firt import of a cvs file to the SQLite, and save it as a file.
My newbie question is that Sqlite is assuming every line as a record of
text, not separating the fields
Must the text fiels be surrounded by "? And it will assume the . as decimal
point?

Another question is the password: is it possible to place a password to
protect the database?

Kind regards,

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

Re: [sqlite] memory leak in transactions

[rui]

Thanks for your reply.
I am using version 1.0.79.0 of System.Data.sqlite
Here is how i am using it, i only use executescalar and
executenonquery, let me know if you want to see how i pass the
parameters etc.


RunBigtransaction()
{

 using (IDbTransaction tran = Connection.BeginTransaction())
 {
foreach (Message msg in messages)
{
   CreateMessage(msg);
}
tran.Commit();
 }
}


 void CreateMessage()
 {
  string tableName = gettablename();

  // here i check if a table exists using a select query
  string query = select "name" from "sqlite_master" where
"type=table", name=tablename";
  string tablename = ExecuteQuery(query)

  if (tablename == null)

  {
// here i do a query on another table in schema to read 
a script to
create a new table, which uses the ExecuteGetTableQuery.
string script = getscript();
Exceute(script);
  }

  // here i execute an insert and update

  // insert uses the same pattern like ExecuteGetTableQuery but 
with
query changed to insert into with 15 parameters.
RawInsertMessage(tableName, msg);

// insert or update where update does just Execute 
function.
SetMessageFolderId(msg.StoreId, msg.Id, msg.ParentId);  
 }

 public void  Execute(string script)
 {
using (IDbCommand cmd = Connection.CreateCommand())
{
cmd.CommandText = script;
   cmd.ExecuteNonQuery();
}
 }  

 public string ExecuteGetTableQuery(string b)
 {
 using (IDbCommand cmd = Connection.CreateCommand())
 {
cmd.CommandText = b.ToString();

object r = cmd.ExecuteScalar();

if (r == null || r is DBNull) return default(T);
if (!(r is T)) throw new 
InvalidCastException("cannot cast " +
r.GetType() + " to " + typeof (T));
return (T) r;
 }
 }




Regards,
Raj


On Thu, Nov 15, 2012 at 8:52 PM, Joe Mistachkin <j...@mistachkin.com> wrote:
>
> rui wrote:
>>
>> I am seeing explosive memory growth when i am using transactions using
>> System.Data.SQLite.
>>
>
> The best way to address this issue is to utilize "using" blocks for any 
> SQLiteCommand, SQLiteDataReader, and SQLiteTransaction objects used.  That 
> way, you won't have to wait until they are garbage collected later on by the 
> CLR.
>
>>
>> All the object are properly disposed from trasnsaction to command etc.
>>
>
> Could you provide some example C# code that demonstrates the behavior you are 
> seeing?
>
>>
>> The SQLiteConnection is kept open for the life time of the session,
>> which could span hours.
>>
>
> This means the memory associated with the connection will be kept around; 
> however, this should be OK and should not result in the memory usage numbers 
> you are seeing.
>
> --
> Joe Mistachkin <j...@mistachkin.com>
>
>
> ___
> 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] memory leak in transactions

[rui]

Hi,

I am seeing explosive memory growth when i am using transactions using
System.Data.SQLite.

I see this when the transactions which were before taking fraction of
second, taking 15 sec for 50 row insert into a table.

I have tried to use sqlite3_db_release_memory but that only frees upto
2mb after every transaction.

After some time say half an hour - i do see memory usage drop but
that's not acceptable.

All the object are properly disposed from trasnsaction to command etc.

The SQLiteConnection is kept open for the life time of the session,
which could span hours.

I would really appreciate if somebody can help me in getting the
reason for such excessive memory usage, i have seen working
set(memory)go up from 70 mb to 400 mb in 1 minute where three
transactions only doing 50 insert in a table.

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

Re: [sqlite] Strategies for checking the version of a database?

[Rui Maciel]

On 09/18/2012 12:05 PM, Marcus Ilgner wrote:

You could use the user_version pragma (don't use schema_version) to
store this information.
Seehttp://www.sqlite.org/pragma.html#pragma_schema_version


Nice.  It looks like pragma user_version is exactly what I was looking for.


Thanks for the help,
Rui Maciel
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Strategies for checking the version of a database?

[Rui Maciel]

Say there is an application that relies on sqlite for its database 
needs, and that from a version onward it starts to use a different 
database schema.  When faced with this type of change, it is always nice 
to have a way to infer if the application's database corresponds to the 
old schema in order to migrate it to the current format.


Considering this, and according to your experience, what's the best 
sqlite-friendly way to check for the version of a database schema?



Thanks in advance,
Rui Maciel
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] (no subject)

[Rui Maciel]

On 09/05/2012 06:36 PM, Arbol One wrote:

That would be the C++ Standard Template Library
http://en.wikipedia.org/wiki/Standard_Template_Library

I am learning the library and as a student I always have questions.


The STL isn't necessarily a project, as it is defined in the C++ 
standard and therefore is a part of the C++ programming language.


If you are looking for public forums where the STL is discussed, you 
could give Usenet's comp.lang.c++ a try.  If you don't have a usenet 
client around, here's a google news link:


https://groups.google.com/forum/?fromgroups=#!forum/comp.lang.c++


Hope this helps,
Rui Maciel
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Uclibc prob

[Rui Silva]

hi ppl, 

i'm new to sqlite and i was trying to use sqlite under a uclibc system.
the version i'm using is 3.2.1-r1 and when i run sqlite3 test.db i get
a segmentation fault.

does sqlite works with uclibc??? is it just a a sqlite3 error or the
main lib prob??

thks 




-- 
Rui Silva
Powered by Gentoo Linux under :
CELERON 1000 - Stage1 install with nptl
Pentium M 1800GHz - Stage1 install with NPTL

http://rukinhas.no-ip.org