[sqlite] Malloc getting segmentation fault in sqlite

[Dan Kennedy]

On 12/24/2015 05:02 PM, santosh dasimanth wrote:
> Hi All,
> I am working on Sqlite in multi threaded environment on ARM v7 platform.
>
> I am facing problems with malloc() function returning segmentation fault.
> The problem is not frequent but out of 100 times am getting this once.
>
> The backtrace is pasted below.
>
> (gdb) bt
> #0  0x4038eb18 in malloc () from /lib/libc.so.0
> #1  0x401e0758 in sqlite3MallocRaw () from /pfrm2.0/lib/libsqlite3.so.0
> #2  0x401e08a8 in sqlite3Malloc () from /pfrm2.0/lib/libsqlite3.so.0
> #3  0x401e6254 in sqlite3VdbeCreate () from /pfrm2.0/lib/libsqlite3.so.0
> #4  0x401d99cc in sqlite3GetVdbe () from /pfrm2.0/lib/libsqlite3.so.0
> #5  0x401bd780 in sqlite3FinishCoding () from /pfrm2.0/lib/libsqlite3.so.0
> #6  0x401d2464 in sqlite3Parser () from /pfrm2.0/lib/libsqlite3.so.0
> #7  0x401dd664 in sqlite3RunParser () from /pfrm2.0/lib/libsqlite3.so.0
> #8  0x401d650c in sqlite3Prepare () from /pfrm2.0/lib/libsqlite3.so.0
> #9  0x401d69a4 in sqlite3_prepare () from /pfrm2.0/lib/libsqlite3.so.0
> #10 0x401ed5c0 in sqlite3_exec () from /pfrm2.0/lib/libsqlite3.so.0
> #11 0x401d5dbc in sqlite3InitCallback () from /pfrm2.0/lib/libsqlite3.so.0
> #12 0x401ed6f8 in sqlite3_exec () from /pfrm2.0/lib/libsqlite3.so.0
> #13 0x401d6184 in ?? () from /pfrm2.0/lib/libsqlite3.so.0
>
> The traces are pointing to different functions when I hit the issue at
> times.
> Please let me know if anyone of people faced this problem before with
> sqlite.

You have a corrupted heap in your application. Usually this is caused by 
calling free() or similar on a pointer that you should not have, but can 
also be due to large buffer overwrites and so on.

If possible, run your application under valgrind - either on the ARM 
platform or on a workstation. It will very likely tell you what is going 
wrong.

   http://valgrind.org/

Dan.

[sqlite] whish list for 2016

[Christian Schmitz]

Hi,

better ALTER command would be very welcome.

e.g. RENAME/DELETE column or field.

Instead of us writing code to do it, it could be a command where SQLite does 
things right.

Sincerely
Christian

-- 
Read our blog about news on our plugins:

http://www.mbsplugins.de/

[sqlite] whish list for 2016

[John McKown]

On Thu, Dec 24, 2015 at 8:39 PM, Simon Slavin  wrote:

>
> On 25 Dec 2015, at 2:35am, Bernardo Sulzbach 
> wrote:
>
> >> ALTER TABLE table-name RENAME COLUMN column_field_name TO
> >> new_column_field_name;
> >
> > Are you sure? The documentation does not have anything about this and
> > I get a syntax error using 3.9.2 (a bit outdated, I know).
>
> John's confused.  The ALTER table RENAME command is for renaming tables,
> not columns.
>

?You're right. I'm wrong. Too much "nog" in the eggnog??



>
> Simon.
>
>
-- 
Computer Science is the only discipline in which we view adding a new wing
to a building as being maintenance -- Jim Horning

Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.

Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

[sqlite] Malloc getting segmentation fault in sqlite

[Bart Smissaert]

I have no problems at all with sqlite3_bind, but I understand that the
sqlite3_result group of procedures is very similar to sqlite3_bind group.

In a UDF procedure (callback procedure in the ActiveX dll) I do things like
this:

sqlite3_result_text lPtr_ObjContext, VarPtr(arrBytes1(0)), lPos - 1,
SQLITE_TRANSIENT

where arrBytes1 is a local variable, a Byte array.
This goes out of scope once the callback procedure finishes.
Could that be a problem?

RBS

On Thu, Dec 24, 2015 at 10:17 PM, Simon Slavin  wrote:

>
> On 24 Dec 2015, at 9:10pm, Bart Smissaert 
> wrote:
>
> > Given that I don't use theses it then likely that my problem is to do
> with
> > a buffer overwrite?
>
> You say you use sqlite3_bind().  See the details about the fifth parameter
> in
>
> 
>
> A common mistake is to bind a string to a parameter then free the space
> before SQLite is finished with it.  This, of course, leads to OS errors.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] whish list for 2016

[Richard Hipp]

On 12/24/15, John McKown  wrote:
>
> DELETE is normally done as: ALTER TABLE table-name DROP ?COLUMN
> column_file_name; and would be a very nice addition. I hadn't noticed that
> it is missing. I wonder why. Perhaps Dr. Hipp will comment after the
> holidays.
>

Merry Christmas.

DROP COLUMN and RENAME COLUMN are relatively easy for tables that lack
indexes, triggers, views, foreign key references, CHECK constraints,
and other constructs that might reference the dropped or renamed
column.  Reliably finding every use of a column name and changing it
can be tricky.  The problem is more acute for dropping a column - what
do you do then, change each reference to NULL?

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Malloc getting segmentation fault in sqlite

[Simon Slavin]

On 24 Dec 2015, at 9:10pm, Bart Smissaert  wrote:

> Given that I don't use theses it then likely that my problem is to do with
> a buffer overwrite?

You say you use sqlite3_bind().  See the details about the fifth parameter in



A common mistake is to bind a string to a parameter then free the space before 
SQLite is finished with it.  This, of course, leads to OS errors.

Simon.

[sqlite] whish list for 2016

[Stephen Chrzanowski]

Two parter;

*Part 1;*

One thing I would suggest, if you're looking to add and delete columns
dynamically is not to worry about the order of the columns in the database,
but, have a second table hanging around that remembers the specified order
the user wants to see the columns in.  Doing your update and insert calls
make no difference so long you specify the fields on either call (Or in
oter words, don't do [ insert into MyTable values (1,2,3) ].  Be aware on
tables that get large.  Adding or deleting fields can get expensive when
the databases physical pages need to be updated, especially if the field
you're adding affects, or has indexes modified.

*Part 2;*

More along with your application style, but a complete database schema
overhaul, think of a contact form that allows for multiple methods of
communication.  Multiple email addresses, multiple phone or fax numbers,
can all be associated to one contact.  Typically you'd have a table sitting
aside with the contact type (email, phone, fax, maybe in the future
Telepresence ID?), and another table containing the actual data.  You could
adopt this method to what you're describing.  To add or delete fields to
your UI (Telpresence info doesn't exist in any common contact manager I
know of), all you'd have to do is add or delete rows to a table, and your
application written to adapt to random(?) changes to the field changes,
regardless of additions or deletions.

All you'd need is one table that would hold the fields unique identifier, a
field title, the order in which it is displayed on the UI, and possibly a
default value field.

Another table contains a unique identifier, a FK field pointing to the UID
of the above table, and the raw data.

Your software would then make whatever required SELECT call to obtain the
required information and either store that data in a new temp table, or,
store the data in memory either via a stringlist or class, then render the
data to your UI using just this new data.

BY FAR *not *the most efficient method, but, weighing the cost of
multi-gigabyte sized tables changing frequently at the users whim, versus a
few more queries to the database to mangle data in memory, it'd be up to
you as the developer to decide which is the best method.

[sqlite] Malloc getting segmentation fault in sqlite

[Bart Smissaert]

OK, thanks
I don't use any of 1 to 3, I only use the standard SQLite functions such as
sqlite3_open_v2, sqlite3_prepare16_v2, sqlite3_step, sqlite3_bind,
sqlite3_column, sqlite3_create_function_v2, sqlite3_finalize,
sqlite3_reset, sqlite3_result, sqlite3_value and qlite3_close.
So, in that case I don't need sqlite3_free, sqlite3_malloc or
sqlite3_realloc, I take it.
Given that I don't use theses it then likely that my problem is to do with
a buffer overwrite?

RBS



On Thu, Dec 24, 2015 at 8:35 PM, Richard Hipp  wrote:

> On 12/24/15, Bart Smissaert  wrote:
> > My question is if there is ever any need in this situation to run one of
> > the sqlite3 memory procedures, that is
> > sqlite3_free, sqlite3_malloc or sqlite3_realloc?
> > Currently I am not using this anywhere in my VB6 code.
> > Should I?
>
> Cases when you might use sqlite3_malloc():
>
> (1) You are using on of SQLite's built-in memory allocators.  The
> built-in memory allocators are disabled unless you use certain
> compile-time options.  And even then, you have to turn them on using
> sqlite3_config(SQLITE_CONFIG_HEAP,...).
>
> (2) You need to use sqlite3_msize().
>
> (3) If you use sqlite3_mprintf(), then sqlite3_free() must be used to
> release the string once you are done with it.
>
> Otherwise, there is no real advantage to using SQLite memory allocator
> interface in place of your standards system memory allocator.
>
> --
> D. Richard Hipp
> drh at sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] In the case of ZIPVFS

[王庆刚]

Thank you very much.



At 2015-12-23 17:32:26, "Richard Hipp"  wrote:
>On 12/23/15, ??? <2004wqg2008 at 163.com> wrote:
>> HI,all
>> SQLite retrieves the compressed records is slower than the uncompressed
>> records about 30%.
>> How can improve the problem?  and anyone any suggustion?
>>
>
>Have you run your system in a profiler to determine where the extra
>30% time is being used?  If the extra time is inside of inflate() (or
>whatever other decompression algorithm you are using) or within
>rijndaelDecrypt() (or whatever other decryption algorithm you are
>using) then probably your only solution will be to disable encryption
>and/or compression.  There is an engineering trade-off here.
>
>Please run your system in a profiler and send us the results.  Perhaps
>we can spot other opportunities to improve performance.
>
>Also please send as many details about your system as possible.  What
>kind of hardware are you using?  What operating system?  Which
>compression and encryption algorithms are you using?  How large is
>your dataset?  What kinds of queries are you running?  Which specific
>version of the NDS dev-kit are you running?
>
>Probably you should send us the above information through your secure
>NDS support channel, rather than here in this public forum.
>
>-- 
>D. Richard Hipp
>drh at sqlite.org
>___
>sqlite-users mailing list
>sqlite-users at mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] whish list for 2016

[John McKown]

On Thu, Dec 24, 2015 at 4:09 PM, Christian Schmitz <
realbasiclists at monkeybreadsoftware.de> wrote:

> Hi,
>
> better ALTER command would be very welcome.
>
> e.g. RENAME/DELETE column or field.
>

?RENAME exists.?

?ALTER TABLE table-name RENAME COLUMN column_field_name TO
new_column_field_name;

DELETE is normally done as: ALTER TABLE table-name DROP ?COLUMN
column_file_name; and would be a very nice addition. I hadn't noticed that
it is missing. I wonder why. Perhaps Dr. Hipp will comment after the
holidays.



>
> Instead of us writing code to do it, it could be a command where SQLite
> does things right.
>
> Sincerely
> Christian
>
> --
> Read our blog about news on our plugins:
>
> http://www.mbsplugins.de/
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Computer Science is the only discipline in which we view adding a new wing
to a building as being maintenance -- Jim Horning

Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.

Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

[sqlite] Malloc getting segmentation fault in sqlite

[Bart Smissaert]

I am interested in this as I have bug that I am sure is to do with some
sort of memory problem.
It only occurs when I run a procedure defined with sqlite3_create_function.
This procedure is not
in sqlite3.dll but in a VB6 ActiveX dll. I use the unaltered Windows
sqlite3.dll.
My question is if there is ever any need in this situation to run one of
the sqlite3 memory procedures, that is
sqlite3_free, sqlite3_malloc or sqlite3_realloc?
Currently I am not using this anywhere in my VB6 code.
Should I?

RBS




On Thu, Dec 24, 2015 at 4:12 PM, Dan Kennedy  wrote:

> On 12/24/2015 05:02 PM, santosh dasimanth wrote:
>
>> Hi All,
>> I am working on Sqlite in multi threaded environment on ARM v7 platform.
>>
>> I am facing problems with malloc() function returning segmentation fault.
>> The problem is not frequent but out of 100 times am getting this once.
>>
>> The backtrace is pasted below.
>>
>> (gdb) bt
>> #0  0x4038eb18 in malloc () from /lib/libc.so.0
>> #1  0x401e0758 in sqlite3MallocRaw () from /pfrm2.0/lib/libsqlite3.so.0
>> #2  0x401e08a8 in sqlite3Malloc () from /pfrm2.0/lib/libsqlite3.so.0
>> #3  0x401e6254 in sqlite3VdbeCreate () from /pfrm2.0/lib/libsqlite3.so.0
>> #4  0x401d99cc in sqlite3GetVdbe () from /pfrm2.0/lib/libsqlite3.so.0
>> #5  0x401bd780 in sqlite3FinishCoding () from /pfrm2.0/lib/libsqlite3.so.0
>> #6  0x401d2464 in sqlite3Parser () from /pfrm2.0/lib/libsqlite3.so.0
>> #7  0x401dd664 in sqlite3RunParser () from /pfrm2.0/lib/libsqlite3.so.0
>> #8  0x401d650c in sqlite3Prepare () from /pfrm2.0/lib/libsqlite3.so.0
>> #9  0x401d69a4 in sqlite3_prepare () from /pfrm2.0/lib/libsqlite3.so.0
>> #10 0x401ed5c0 in sqlite3_exec () from /pfrm2.0/lib/libsqlite3.so.0
>> #11 0x401d5dbc in sqlite3InitCallback () from /pfrm2.0/lib/libsqlite3.so.0
>> #12 0x401ed6f8 in sqlite3_exec () from /pfrm2.0/lib/libsqlite3.so.0
>> #13 0x401d6184 in ?? () from /pfrm2.0/lib/libsqlite3.so.0
>>
>> The traces are pointing to different functions when I hit the issue at
>> times.
>> Please let me know if anyone of people faced this problem before with
>> sqlite.
>>
>
> You have a corrupted heap in your application. Usually this is caused by
> calling free() or similar on a pointer that you should not have, but can
> also be due to large buffer overwrites and so on.
>
> If possible, run your application under valgrind - either on the ARM
> platform or on a workstation. It will very likely tell you what is going
> wrong.
>
>   http://valgrind.org/
>
> Dan.
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Malloc getting segmentation fault in sqlite

[Richard Hipp]

On 12/24/15, Bart Smissaert  wrote:
> I have no problems at all with sqlite3_bind, but I understand that the
> sqlite3_result group of procedures is very similar to sqlite3_bind group.
>
> In a UDF procedure (callback procedure in the ActiveX dll) I do things like
> this:
>
> sqlite3_result_text lPtr_ObjContext, VarPtr(arrBytes1(0)), lPos - 1,
> SQLITE_TRANSIENT
>
> where arrBytes1 is a local variable, a Byte array.
> This goes out of scope once the callback procedure finishes.
> Could that be a problem?
>

No.  SQLITE_TRANSIENT causes SQLite to make its own private copy of the string.
-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] whish list for 2016

[Simon Slavin]

On 24 Dec 2015, at 5:10pm, Richard Damon  wrote:

> being able to directly add a field would be nice.

You can directly add a field.



Simon.

[sqlite] wish list for 2016

[Tim Streater]

On 24 Dec 2015 at 15:26, Bernardo Sulzbach  
wrote: 

> want a more versatile alter table for convenience. However, I don't
> know if alter table is used at all in production anywhere (why would
> it be? the column names and ordering should not be part of the data).

If I distribute an app with a certain schema, and later make a new version with 
added features that needs more columns in the database, I have to detect the 
user's version, and run an extra function that updates the schema. If the user 
skipped a few versions, several such functions may run. Generally this does not 
affect things particularly, but I suppose after a few versions the order of the 
columns could become sub-optimal.

--
Cheers  --  Tim

[sqlite] whish list for 2016

[Simon Slavin]

On 24 Dec 2015, at 3:12pm, John McKown  wrote:

> ?I'm curious as to why. Doing so would, most likely, require rewriting the
> entire table. If you want a SELECT * to get the columns in a particular
> order, just create a VIEW with the columns in the order in which you want
> them.

I think Gunnar just wants forms such as

ALTER TABLE CREATE COLUMN ...
ALTER TABLE DROP COLUMN ...

to aid with making small changes to the schema.  These are available in most 
SQL engines but the way SQLite3 is written makes it difficult or inefficient to 
implement them.

I have a database where one table takes up more than 30 Gigabytes of space.  
While developing the software I needed several times to change a column 
definition and since SQLite lacks these facilities I had to move 30 Gig of data 
around every time I did it.  Annoying.  But it's not normally that much of a 
problem for me.

Simon.

[sqlite] Malloc getting segmentation fault in sqlite

[Richard Hipp]

On 12/24/15, Bart Smissaert  wrote:
> My question is if there is ever any need in this situation to run one of
> the sqlite3 memory procedures, that is
> sqlite3_free, sqlite3_malloc or sqlite3_realloc?
> Currently I am not using this anywhere in my VB6 code.
> Should I?

Cases when you might use sqlite3_malloc():

(1) You are using on of SQLite's built-in memory allocators.  The
built-in memory allocators are disabled unless you use certain
compile-time options.  And even then, you have to turn them on using
sqlite3_config(SQLITE_CONFIG_HEAP,...).

(2) You need to use sqlite3_msize().

(3) If you use sqlite3_mprintf(), then sqlite3_free() must be used to
release the string once you are done with it.

Otherwise, there is no real advantage to using SQLite memory allocator
interface in place of your standards system memory allocator.

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Malloc getting segmentation fault in sqlite

[santosh dasimanth]

Hi All,
I am working on Sqlite in multi threaded environment on ARM v7 platform.

I am facing problems with malloc() function returning segmentation fault.
The problem is not frequent but out of 100 times am getting this once.

The backtrace is pasted below.

(gdb) bt
#0  0x4038eb18 in malloc () from /lib/libc.so.0
#1  0x401e0758 in sqlite3MallocRaw () from /pfrm2.0/lib/libsqlite3.so.0
#2  0x401e08a8 in sqlite3Malloc () from /pfrm2.0/lib/libsqlite3.so.0
#3  0x401e6254 in sqlite3VdbeCreate () from /pfrm2.0/lib/libsqlite3.so.0
#4  0x401d99cc in sqlite3GetVdbe () from /pfrm2.0/lib/libsqlite3.so.0
#5  0x401bd780 in sqlite3FinishCoding () from /pfrm2.0/lib/libsqlite3.so.0
#6  0x401d2464 in sqlite3Parser () from /pfrm2.0/lib/libsqlite3.so.0
#7  0x401dd664 in sqlite3RunParser () from /pfrm2.0/lib/libsqlite3.so.0
#8  0x401d650c in sqlite3Prepare () from /pfrm2.0/lib/libsqlite3.so.0
#9  0x401d69a4 in sqlite3_prepare () from /pfrm2.0/lib/libsqlite3.so.0
#10 0x401ed5c0 in sqlite3_exec () from /pfrm2.0/lib/libsqlite3.so.0
#11 0x401d5dbc in sqlite3InitCallback () from /pfrm2.0/lib/libsqlite3.so.0
#12 0x401ed6f8 in sqlite3_exec () from /pfrm2.0/lib/libsqlite3.so.0
#13 0x401d6184 in ?? () from /pfrm2.0/lib/libsqlite3.so.0

The traces are pointing to different functions when I hit the issue at
times.
Please let me know if anyone of people faced this problem before with
sqlite.

Regards,
Santosh

[sqlite] whish list for 2016

[Bernardo Sulzbach]

On Thu, Dec 24, 2015 at 3:16 PM, Simon Slavin  wrote:
>
> On 24 Dec 2015, at 5:10pm, Richard Damon  wrote:
>
>> being able to directly add a field would be nice.
>
> You can directly add a field.
>
> 
>
> Simon.

Good catch, you cannot place it wherever you want, but alter table is
currently capable of "directly adding a field".

-- 
Bernardo Sulzbach

[sqlite] whish list for 2016

[Bernardo Sulzbach]

On Thu, Dec 24, 2015 at 3:10 PM, Richard Damon  
wrote:
> On 12/24/15 10:26 AM, Bernardo Sulzbach wrote:
>>
>> I think you focused too much on the ordering issue. He or she may  > just
>> want a more versatile alter table for convenience. However, I >
>
> don't know if alter table is used at all in production anywhere (why > would
> it be? the column names and ordering should not be part of the > data). They
> are useful for prototyping because by having a stronger > alter table
> command you don't need to drop and create so many times. > In the end, if
> you spent enough time in the design phase to prepare > all your schemas, you
> should not have to drop (or alter) any of the > tables at all. It is a
> feature, a nice one, but too far from > necessary (from my standpoint) to be
> worthy of the developers' time. >
>
> I am in the process of building an application that has the need to be able
> to add columns to tables in response to user actions. It is primarily in the
> stage where the user is customizing the program to their needs, but such
> customizations might happen after the program has been in use for awhile.
> The main case is to be able to add a 'Flag' to records to allow the filter
> records or save the set of records found in a search. For now, the current
> method of create new, drop and rename, isn't unworkable (and mostly hidden
> in an abstraction layer), but being able to directly add a field would be
> nice.
>
> --
> Richard Damon
>
>

Interesting. I suppose that most of this "table editing" should rely
on an abstraction layer anyway: think about supporting other RDBMS in
the future. However, I recognize that in your case a more capable
alter table may simplify things a lot.

-- 
Bernardo Sulzbach

[sqlite] wish list for 2016

[Bernardo Sulzbach]

On Thu, Dec 24, 2015 at 2:30 PM, Tim Streater  wrote:
> On 24 Dec 2015 at 15:26, Bernardo Sulzbach  
> wrote:
>
>> want a more versatile alter table for convenience. However, I don't
>> know if alter table is used at all in production anywhere (why would
>> it be? the column names and ordering should not be part of the data).
>
> If I distribute an app with a certain schema, and later make a new version 
> with added features that needs more columns in the database, I have to detect 
> the user's version, and run an extra function that updates the schema. If the 
> user skipped a few versions, several such functions may run. Generally this 
> does not affect things particularly, but I suppose after a few versions the 
> order of the columns could become sub-optimal.
>

But this is not a blocker, right? You could also create another table
with the desired schema and insert from the old one then drop it. It
is a convenience in the end.


-- 
Bernardo Sulzbach

[sqlite] whish list for 2016

[gunnar]

I would like a less limited 'alter table' statement, to be able to drop 
columns and to add columns at a position of my own choice instead of 
always at the end.

[sqlite] whish list for 2016

[Bernardo Sulzbach]

On Thu, Dec 24, 2015 at 1:12 PM, John McKown
 wrote:
>
> On Thu, Dec 24, 2015 at 6:49 AM, gunnar  wrote:
>
> > I would like a less limited 'alter table' statement, to be able to drop
> > columns and to add columns at a position of my own choice instead of always
> > at the end.
> >
> >
> I'm curious as to why. Doing so would, most likely, require rewriting the
> entire table. If you want a SELECT * to get the columns in a particular
> order, just create a VIEW with the columns in the order in which you want
> them.
>

I think you focused too much on the ordering issue. He or she may just
want a more versatile alter table for convenience. However, I don't
know if alter table is used at all in production anywhere (why would
it be? the column names and ordering should not be part of the data).
They are useful for prototyping because by having a stronger alter
table command you don't need to drop and create so many times. In the
end, if you spent enough time in the design phase to prepare all your
schemas, you should not have to drop (or alter) any of the tables at
all.

It is a feature, a nice one, but too far from necessary (from my
standpoint) to be worthy of the developers' time.



-- 
Bernardo Sulzbach

[sqlite] whish list for 2016

[Warren Young]

On Dec 24, 2015, at 11:17 AM, Warren Young  wrote:
> 
>   BEGIN TRANSACTION;
>   ALTER TABLE Foo RENAME TO oldFoo;
>   CREATE TABLE Foo ? a bunch of repeated stuff 
>? almost identical to the initial
>? DBMS creation code with just one
>? or two differences yet everything
>? that has stayed the same still has
>? to be repeated just because SQLite
>? doesn?t fully support ALTER TABLE
>   INSERT INTO Foo SELECT some,subset,of,columns FROM oldFoo;
>   COMMIT;
> 
> It would be entirely possible for SQLite to generate and run this code for 
> me.  Writing such code is a waste of human brain power.

On re-reading this, I see that there is a bug in that code, which only 
underscores my point: I wouldn?t have made the error if I?d only had to write

   ALTER TABLE Foo DROP COLUMN bar;

[sqlite] whish list for 2016

[Richard Damon]

On 12/24/15 12:17 PM, Bernardo Sulzbach wrote:
> Interesting. I suppose that most of this "table editing" should rely  > on an 
> abstraction layer anyway: think about supporting other RDBMS 
in > the future. However, I recognize that in your case a more capable > 
alter table may simplify things a lot.

Yes, I am currently working on the base Schema and the Database 
Abstraction Layer. Alternate RDBMS isn't a 'future' but first release 
requirement, so the whole program will work through the DbAL. (First 
proof of concept versions may not fully support a wide breadth of 
Databases, but will still use an Abstraction layer. )

-- 
Richard Damon

[sqlite] whish list for 2016

[Warren Young]

On Dec 24, 2015, at 8:26 AM, Bernardo Sulzbach  
wrote:
> 
> I don't
> know if alter table is used at all in production anywhere (why would
> it be? the column names and ordering should not be part of the data).

Requirements change.

In the past dozen years, the database schema I?m working on right now has 
changed about a hundred times.  Many of those changes were batched, so that 
there are ?only? about 40 separate schema versions.  But, about a third of 
those involved changes that SQLite doesn?t support directly, but other SQL 
DBMSes do.

SQLite currently only supports adding new features.  (ADD COLUMN, CREATE 
TABLE.) It doesn?t deal with mutating features (MODIFY/CHANGE COLUMN) or 
removed features (DROP COLUMN) nearly as well.

SQLite?s nearly typeless nature does allow you to paper over many changes that 
would require an ALTER TABLE in another DBMS.  (e.g. extension of an 8 bit 
integer column to 32 bits, or changing an integer column to a string column)  
But, some application level changes do still require a bunch of code at the 
SQLite layer that would be a one-liner in other DBMSes.

> In the
> end, if you spent enough time in the design phase to prepare all your
> schemas, you should not have to drop (or alter) any of the tables at
> all.

What you?re describing is the old waterfall development dream, where all we 
need to do is spend more time in the design phase, and we?ll produce perfect 
software on time, every time.  The industry ran on that mantra for decades 
before the agile movement coalesced, finally providing a better set of coherent 
philosophies.

ALTER TABLE is agile.  To the extent that agile is good, stronger ALTER TABLE 
support is good, too.

I am not arguing for an abandonment of up-front design.  The software that uses 
the DBMS I describe above changed tens of thousands of times over that same 
span, so the fact that the DBMS only had to change about 100 times is a 
testament to good up front design.  Yet, changes still occur, so it?s best if 
we don?t have to jump through hoops when that happens.

[sqlite] whish list for 2016

[Richard Damon]

On 12/24/15 10:26 AM, Bernardo Sulzbach wrote:
> I think you focused too much on the ordering issue. He or she may  > just 
> want a more versatile alter table for convenience. However, I > 
don't know if alter table is used at all in production anywhere (why > 
would it be? the column names and ordering should not be part of the > 
data). They are useful for prototyping because by having a stronger > 
alter table command you don't need to drop and create so many times. > 
In the end, if you spent enough time in the design phase to prepare > 
all your schemas, you should not have to drop (or alter) any of the > 
tables at all. It is a feature, a nice one, but too far from > necessary 
(from my standpoint) to be worthy of the developers' time. >

I am in the process of building an application that has the need to be 
able to add columns to tables in response to user actions. It is 
primarily in the stage where the user is customizing the program to 
their needs, but such customizations might happen after the program has 
been in use for awhile. The main case is to be able to add a 'Flag' to 
records to allow the filter records or save the set of records found in 
a search. For now, the current method of create new, drop and rename, 
isn't unworkable (and mostly hidden in an abstraction layer), but being 
able to directly add a field would be nice.

-- 
Richard Damon

[sqlite] Malloc getting segmentation fault in sqlite

[Joe Mistachkin]

It's been quite a long while since I used vb6 on a regular basis; however, 
integrating with native DLLs can be quite tricky for several reasons:

1.  It cannot call any native function that does not conform to the "stdcall" 
calling convention.

2.  It has a very Win32-centric way of marshalling data types.

3.  Using 64-bit integers at all is somewhat tricky, IIRC.  You may need to use 
a ByVal structure to pass them and I cannot remember how to use them as 
returned values.

4.  Properly declaring and calling (e.g. using the ByVal keyword strategically) 
is critically important.  Another issue is ANSI versus Unicode (UCS2 for COM) 
versus UTF-8 (SQLite) and knowing when to use which and how to convert between 
them (or marshal them).

5.  Doing inbound callbacks to VB6 code is very very tricky, mostly due to 
[apartment] threading issues.  I think that using the AddressOf keyword won't 
really work with SQLite in this context due to mismatched calling conventions 
(cdecl versus stdcall).

I'll try looking for my old VB6 SQLite integration code when I have some spare 
cycles.

Sent from my iPhone

> On Dec 24, 2015, at 11:10 AM, Bart Smissaert  
> wrote:
> 
> OK, thanks
> I don't use any of 1 to 3, I only use the standard SQLite functions such as
> sqlite3_open_v2, sqlite3_prepare16_v2, sqlite3_step, sqlite3_bind,
> sqlite3_column, sqlite3_create_function_v2, sqlite3_finalize,
> sqlite3_reset, sqlite3_result, sqlite3_value and qlite3_close.
> So, in that case I don't need sqlite3_free, sqlite3_malloc or
> sqlite3_realloc, I take it.
> Given that I don't use theses it then likely that my problem is to do with
> a buffer overwrite?
> 
> RBS
> 
> 
> 
>> On Thu, Dec 24, 2015 at 8:35 PM, Richard Hipp  wrote:
>> 
>>> On 12/24/15, Bart Smissaert  wrote:
>>> My question is if there is ever any need in this situation to run one of
>>> the sqlite3 memory procedures, that is
>>> sqlite3_free, sqlite3_malloc or sqlite3_realloc?
>>> Currently I am not using this anywhere in my VB6 code.
>>> Should I?
>> 
>> Cases when you might use sqlite3_malloc():
>> 
>> (1) You are using on of SQLite's built-in memory allocators.  The
>> built-in memory allocators are disabled unless you use certain
>> compile-time options.  And even then, you have to turn them on using
>> sqlite3_config(SQLITE_CONFIG_HEAP,...).
>> 
>> (2) You need to use sqlite3_msize().
>> 
>> (3) If you use sqlite3_mprintf(), then sqlite3_free() must be used to
>> release the string once you are done with it.
>> 
>> Otherwise, there is no real advantage to using SQLite memory allocator
>> interface in place of your standards system memory allocator.
>> 
>> --
>> D. Richard Hipp
>> drh at sqlite.org
>> ___
>> 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] whish list for 2016

[Warren Young]

On Dec 24, 2015, at 9:14 AM, Simon Slavin  wrote:
> 
> ALTER TABLE DROP COLUMN ...
> 
> ...the way SQLite3 is written makes it difficult or inefficient to implement 
> them.

I wouldn?t mind if SQLite did nothing more than the recommended manual process 
for emulating the ALTER TABLE affordances in other DBMSes which are missing in 
SQLite.

For my purposes, the table copy isn?t the problem, the problem is that a simple 
one-liner in other DBMSes becomes a dozen lines in SQLite:

   BEGIN TRANSACTION;
   ALTER TABLE Foo RENAME TO oldFoo;
   CREATE TABLE Foo ? a bunch of repeated stuff 
? almost identical to the initial
? DBMS creation code with just one
? or two differences yet everything
? that has stayed the same still has
? to be repeated just because SQLite
? doesn?t fully support ALTER TABLE
   INSERT INTO Foo SELECT some,subset,of,columns FROM oldFoo;
   COMMIT;

It would be entirely possible for SQLite to generate and run this code for me.  
Writing such code is a waste of human brain power.

[sqlite] whish list for 2016

[John McKown]

On Thu, Dec 24, 2015 at 10:14 AM, Simon Slavin  wrote:

>
> On 24 Dec 2015, at 3:12pm, John McKown 
> wrote:
>
> > ?I'm curious as to why. Doing so would, most likely, require rewriting
> the
> > entire table. If you want a SELECT * to get the columns in a particular
> > order, just create a VIEW with the columns in the order in which you want
> > them.
>
> I think Gunnar just wants forms such as
>
> ALTER TABLE CREATE COLUMN ...
> ALTER TABLE DROP COLUMN ...
>
> to aid with making small changes to the schema.  These are available in
> most SQL engines but the way SQLite3 is written makes it difficult or
> inefficient to implement them.
>
> I have a database where one table takes up more than 30 Gigabytes of
> space.  While developing the software I needed several times to change a
> column definition and since SQLite lacks these facilities I had to move 30
> Gig of data around every time I did it.  Annoying.  But it's not normally
> that much of a problem for me.
>
> Simon.
>
>
?I did overlook the DROP COLUMN request. I guess I got "shocked" by the OP
wanting something (I think) like: ALTER TABLE ADD COLUMN newcol TEXT AFTER
oldcol. Where "oldcol" is an existing column name which is not the "last"
one. I sometimes have an unusual take on things due to having read about
relational algebra _before_ doing SQL work. So I think of tables as
relationships, as a "set" for "attributes" which have no inherent order.
Read a bit too much by Dr. Codd. Oh, an Joe Celko too, for that matter. And
_no_ actual professional experience. Makes me a bit of a theoretician.

It really would be nice to be able to have a column defined as, say
VARCHAR(20) to be "redefined" as TEXT or VARCHAR(n) (where n>=20) with a
simple ALTER. That would be a simple change to the schema with no data
alteration. If one allowed to change a VARCHAR new length to be _less_ than
the old length, then it would be necessary to verify that all current rows
were still compliant with the new length. In that case, I guess it would be
"best" if the back end were do to the equivalent of a DELETE and ADD on the
now-invalid data, truncating the larger value to its new max size. That
would save some I/O by not rewriting compliant rows. I don't see any way to
avoid I/O if you want to change an INTEGER (1,2,3,4,6 or 8 bytes) to a
FLOAT? (always 8 bytes). You'd need to rewrite every row, either "in place"
(8 byte INTEGER to FLOAT) or with a DELETE / ADD to the "end". I would
really need to examine the internals to see how much I/O this might be.

I'm not aware of any RDMS which allows someone to alter the "type" (e.g.
INTEGER to FLOAT) of an existing column. What I have done, in PostgreSQL,
is something like:

ALTER TABLE table ADD COLUMN new-column FLOAT;
UPDATE table SET new-column=old-column;
ALTER TABLE table DROP COLUMN old-column;
UPDATE table ALTER COLUMN new-column RENAME TO old-name;

But the above would "move" those 30 Gig of data round, just more easily
from the standpoint of the user. What might be interesting in this type of
case would be a "column-oriented DBMS" (
https://en.wikipedia.org/wiki/Column-oriented_DBMS) Depending on how it was
implemented, it could be made to do the above operation easily. But that
would be a _major_ rewrite of SQLite internally. Hum, it could complicate
things, but this might be more easily possible if each column were placed
in a different OS file. When you add a new column, just create a new file
initialized with the same number of rows which contain NULL or the DEFAULT
value. When you drop a column, it would simply delete the row-containing
file. Maybe a VFS could be written to do this. But SQLite would need to be
enhanced to add the ALTER TABLE ... DROP COLUMN operation.



-- 
Computer Science is the only discipline in which we view adding a new wing
to a building as being maintenance -- Jim Horning

Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.

Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

[sqlite] whish list for 2016

[John McKown]

On Thu, Dec 24, 2015 at 6:49 AM, gunnar  wrote:

> I would like a less limited 'alter table' statement, to be able to drop
> columns and to add columns at a position of my own choice instead of always
> at the end.
>
>
?I'm curious as to why. Doing so would, most likely, require rewriting the
entire table. If you want a SELECT * to get the columns in a particular
order, just create a VIEW with the columns in the order in which you want
them.

The SQL standard, I'm fairly sure, doesn't even specify the "natural order"
of the column returned in a "SELECT *". Of course, they will likely always
be return in the "natural" order. But that depends on how the back end is
programmed. I could see a vendor (as unlikely as it would be) deciding to
return the column in a "SELECT *" in lexicographical order based on the
server's code page, or the table's default code page, or perhaps even in
the client's code page.

But then, I admit that I am "anal" about my SELECT statements; at least
when embedded in a program. IMO, "SELECT *" is a very bad idea in any
programming language. As is making any assumption about the order of rows
returned when an ORDER BY is not specified. "Assuming _nothing_, other than
the worst." is my programming motto. Or the Russian: "Trust, but verify!".
Especially if it is coming in from "meatware" (people), or some other
organization. We have a process at work which consistently blows up because
the end user sends us junk. E.g. the cost is q.97 dollars, instead of 1.97
(q is below 1 and user is typing fast).

===
Hoping you have a nice Christmas, Hanukkah, Fetivus, Kwanza, or at least a
3 day week end.


-- 
Computer Science is the only discipline in which we view adding a new wing
to a building as being maintenance -- Jim Horning

Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.

Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

[sqlite] Malloc getting segmentation fault in sqlite

[J Decker]

Yes when I free'd memory I shouldn't have and kept a reference to it
in other places... or that after freeing I had modified memory.  But
not from sqlite itself.

On Thu, Dec 24, 2015 at 2:02 AM, santosh dasimanth
 wrote:
> Hi All,
> I am working on Sqlite in multi threaded environment on ARM v7 platform.
>
> I am facing problems with malloc() function returning segmentation fault.
> The problem is not frequent but out of 100 times am getting this once.
>
> The backtrace is pasted below.
>
> (gdb) bt
> #0  0x4038eb18 in malloc () from /lib/libc.so.0
> #1  0x401e0758 in sqlite3MallocRaw () from /pfrm2.0/lib/libsqlite3.so.0
> #2  0x401e08a8 in sqlite3Malloc () from /pfrm2.0/lib/libsqlite3.so.0
> #3  0x401e6254 in sqlite3VdbeCreate () from /pfrm2.0/lib/libsqlite3.so.0
> #4  0x401d99cc in sqlite3GetVdbe () from /pfrm2.0/lib/libsqlite3.so.0
> #5  0x401bd780 in sqlite3FinishCoding () from /pfrm2.0/lib/libsqlite3.so.0
> #6  0x401d2464 in sqlite3Parser () from /pfrm2.0/lib/libsqlite3.so.0
> #7  0x401dd664 in sqlite3RunParser () from /pfrm2.0/lib/libsqlite3.so.0
> #8  0x401d650c in sqlite3Prepare () from /pfrm2.0/lib/libsqlite3.so.0
> #9  0x401d69a4 in sqlite3_prepare () from /pfrm2.0/lib/libsqlite3.so.0
> #10 0x401ed5c0 in sqlite3_exec () from /pfrm2.0/lib/libsqlite3.so.0
> #11 0x401d5dbc in sqlite3InitCallback () from /pfrm2.0/lib/libsqlite3.so.0
> #12 0x401ed6f8 in sqlite3_exec () from /pfrm2.0/lib/libsqlite3.so.0
> #13 0x401d6184 in ?? () from /pfrm2.0/lib/libsqlite3.so.0
>
> The traces are pointing to different functions when I hit the issue at
> times.
> Please let me know if anyone of people faced this problem before with
> sqlite.
>
> Regards,
> Santosh
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users