Re: [sqlite] Introspection and RowID / INTEGER PRIMARY KEY

2010-05-19 Thread Simon Slavin

On 20 May 2010, at 1:21am, Andrew Rodland wrote:

> I understand that, but that's not what I need. I'm working on the code that 
> loads a database schema into an ORM, and I need to properly set the flag 
> "is_auto_increment" which is used to identify a column that will be 
> automatically set by the DB. I'm not looking to access the rowid directly, I 
> truly am just looking for information about a specific column -- will it have 
> a certain behavior or not?

Sorry, but as far as I know the only way to find that out is to look at column 
'sql' of TABLE sqlite_master and parse the creation statement.  The appropriate 
fix to SQLite would involve adding another column to the result from 'PRAGMA 
table_info()'.

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


Re: [sqlite] Accessing SQLite from PHP5?

2010-05-19 Thread J. King
On Tue, 18 May 2010 09:49:49 -0400, Gilles Ganault  
 wrote:

> On Tue, 18 May 2010 14:30:06 +0100 (BST), Swithun Crowe
>  wrote:
>> If you use PDO, then you get access to Sqlite 3.x databases:
>>
>> http://www.php.net/manual/en/ref.pdo-sqlite.php
>
> Thanks for the input. After following the following article, I
> successfully installed Lighttpd in FastCGI mode + PHP5 and PDO to
> access SQLite:

FYI, PHP 5.3 provides an SQLite3 module  which  
offers a version of SQLite more up-to-date than that exposed by PDO.   
Whereas PDO is an abstracted interface, though, SQLite3 appears to be a  
slightly more direct mapping of the SQLite API, exposing more  
SQLite-specific features.

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


Re: [sqlite] Proposed: drop support for LinuxThreads

2010-05-19 Thread D. Richard Hipp

On May 19, 2010, at 6:35 PM, Mike Frysinger wrote:

> On Wednesday 12 May 2010 11:43:13 D. Richard Hipp wrote:
>> If we drop support for (the non-standard, non-compliant) LinuxThreads
>> threading library and instead support only standard Posix threads
>> implemented using NPTL, beginning with SQLite release 3.7.0, what
>> disruptions might this cause?
>>
>> Is anybody still using LinuxThreads?
>
> yes.  there are some architectures that lack NPTL at all, and most  
> ports using
> uClibc (the most common embedded port) does not have NPTL support.
>
> is leaving the code alone that big of a hassle ?

Yes, it is a huge hassle.  And it has already been removed.

Beginning with version 3.7.0, SQLite requires posix threads if you  
want to use it in a multi-threaded environment on unix.  LinuxThreads  
is not an acceptable substitute.

Of course, you can still take the sensible approach and avoid  
multithreading all together.  Failing that, SQLite should still work  
on LinuxThreads if you avoid passing database connections from one  
thread to another.

D. Richard Hipp
d...@hwaci.com



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


Re: [sqlite] SQLite file Validation

2010-05-19 Thread Eric Smith
> Devs were told to make sure they are validating their sql statements, 
> but as I have seen in the code, few of them have 

> Question: Do you think that instead of getting them go back throughout 
> their code, it is feasible to create a function that just eliminates the ; 
> and replaces it with a ,?  And if so, any suggested code?  

"I have this programmer who keeps writing stupid code despite explicit 
guidance.  I want an algorithm that converts his bad inputs into good 
inputs."  

Make sure to let us know if you come up with a general solution.  

Eric 

-- 
Eric A. Smith

You are standing in an open field west of a white house, with a 
boarded front door.
There is a small mailbox here.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Leading zeros disappear

2010-05-19 Thread Ted Rolle, Jr.
Thanks to all who replied!

Single quotes?  How simple...I used double quotes...

Ted

-- 
__
3.14159265358979323846264338327950  Let the spirit of pi
2884197169399375105820974944592307   spread all over the world!
8164062862089986280348253421170679 http://pi314.at  PI VOBISCUM!
==
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] SQLite file Validation

2010-05-19 Thread Dustin Sallings

On May 19, 2010, at 12:46, seandakid wrote:

> Thanks for the quick reply Dustin. That was my concern as well.. it might
> create more issues than it will solve. 
> 
> One of the devs suggested this code example:
> 
> int makeSQLtight(const TCHAR* update); 

I'm all for developer laziness, but holistically.  Doing lots of work 
to asymptotically approach "safe" with the effect of encouraging unsafe 
practices.

This conversation came up a few times on reddit a month or so ago.

People brought up things like mysql_real_escape (that is, something 
(unfortunately) widely used and allegedly well-tested).  Rather than stopping 
at assuming it was wrong, I just did a google search for exploits within it.  
There were lots.  It's not worth it.

If you do things right, bad results become impossible.  If you do 
things wrong, you'll never get to a solution, regardless of how quick it 
appears.  :)

-- 
Dustin Sallings

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


Re: [sqlite] SQLite file Validation

2010-05-19 Thread Black, Michael (IS)
I do need to add that the docs say the create_function must be done in the same 
thread as the defined funtion is running.
 
So if you're mutli-threaded it won't work unless each thread defines it (still 
doable though).
 
Michael D. Black
Senior Scientist
Northrop Grumman Mission Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of Black, Michael (IS)
Sent: Wed 5/19/2010 2:49 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] SQLite file Validation



When you say "create a function" -- sure you can write your own program that 
would do that -- but it sounds like you want something to intercept all the 
calls, yes?

You could create an insert or update trigger.  Then write your own cleanup 
sqlite function to stick in the trigger.

Shouldn't be hard to do at all.

http://www.sqlite.org/capi3ref.html#sqlite3_create_function



Michael D. Black
Senior Scientist
Northrop Grumman Mission Systems




From: sqlite-users-boun...@sqlite.org on behalf of seandakid
Sent: Wed 5/19/2010 2:24 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] SQLite file Validation




Hi all,

Total noob question for you guys:

Background: I have a project where there are many components touching
different SQLite dbs. Devs were told to make sure they are validating their
sql statements, but as I have seen in the code, few of them have. On some
components they have used the BIND statement, which will help. We are
dealing with media file inputs mostly on this project, so people might have
files named "Michael Jackson; Beat it; Thriller" or something like that.

My concern is possible sql injections throughout the different code. We are
on a tight deadline and we are unable to get back and recode a bunch of
components.

Question: Do you think that instead of getting them go back throughout their
code, it is feasible to create a function that just eliminates the ; and
replaces it with a ,? And if so, any suggested code?

Thanks so much for your time.

-Steve

--
View this message in context: 
http://old.nabble.com/SQLite-file-Validation-tp28612927p28612927.html
Sent from the SQLite mailing list archive at Nabble.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


Re: [sqlite] SQLite file Validation

2010-05-19 Thread Black, Michael (IS)
When you say "create a function" -- sure you can write your own program that 
would do that -- but it sounds like you want something to intercept all the 
calls, yes?
 
You could create an insert or update trigger.  Then write your own cleanup 
sqlite function to stick in the trigger.
 
Shouldn't be hard to do at all.
 
http://www.sqlite.org/capi3ref.html#sqlite3_create_function
 
 
 
Michael D. Black
Senior Scientist
Northrop Grumman Mission Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of seandakid
Sent: Wed 5/19/2010 2:24 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] SQLite file Validation




Hi all,

Total noob question for you guys:

Background: I have a project where there are many components touching
different SQLite dbs. Devs were told to make sure they are validating their
sql statements, but as I have seen in the code, few of them have. On some
components they have used the BIND statement, which will help. We are
dealing with media file inputs mostly on this project, so people might have
files named "Michael Jackson; Beat it; Thriller" or something like that.

My concern is possible sql injections throughout the different code. We are
on a tight deadline and we are unable to get back and recode a bunch of
components.

Question: Do you think that instead of getting them go back throughout their
code, it is feasible to create a function that just eliminates the ; and
replaces it with a ,? And if so, any suggested code?

Thanks so much for your time.

-Steve

--
View this message in context: 
http://old.nabble.com/SQLite-file-Validation-tp28612927p28612927.html
Sent from the SQLite mailing list archive at Nabble.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


Re: [sqlite] SQLite file Validation

2010-05-19 Thread seandakid

Thanks for the quick reply Dustin. That was my concern as well.. it might
create more issues than it will solve. 

One of the devs suggested this code example:

int makeSQLtight(const TCHAR* update); 

S

Dustin Sallings wrote:
> 
> 
> On May 19, 2010, at 12:24, seandakid wrote:
> 
>> Question: Do you think that instead of getting them go back throughout
>> their
>> code, it is feasible to create a function that just eliminates the ; and
>> replaces it with a ,? And if so, any suggested code?
> 
>   On one hand, you have something that is safer and more efficient
> (probably tons faster depending on your app since you'd be able to reuse
> statements).
> 
>   On the other, you have something that will increase your technical debt
> and give you more places to hide bugs (with false hope that you can figure
> out the difference between code and data "magically" in a new layer).
> 
> -- 
> Dustin Sallings
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://old.nabble.com/SQLite-file-Validation-tp28612927p28613149.html
Sent from the SQLite mailing list archive at Nabble.com.

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


Re: [sqlite] Leading zeros disappear

2010-05-19 Thread Black, Michael (IS)
Are you putting single quotes around it?  That's what you need.
sqlite> create table text (t1 text, t2 varchar);
sqlite> insert into text values ('01234','01234');
sqlite> select * from text;
01234|01234
sqlite> insert into text values (01234,01234);
sqlite> select * from text;
01234|01234
1234|1234
 
Michael D. Black
Senior Scientist
Northrop Grumman Mission Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of Ted Rolle, Jr.
Sent: Wed 5/19/2010 2:36 PM
To: SQLite Database
Subject: [sqlite] Leading zeros disappear



I'm entering UPCs.

0071318119 becomes 71318119. This is not good, or expected, for that
matter
.
The column is specified as TEXT.

I thought that TEXT values went in 'as entered'.  After reading the
docs, Section 2.0:
"If numerical data is inserted into a column with TEXT affinity it is
converted into text form before being stored."

I changed the column type to BLOB.  Same result.
A UPC field surely is not any sort of numerical value.
For instance, you cannot add two UPCs and return a valid result.

I just need to be spun around and pointed in the right direction!

--
__
3.14159265358979323846264338327950  Let the spirit of pi
2884197169399375105820974944592307   spread all over the world!
8164062862089986280348253421170679 http://pi314.at    PI 
VOBISCUM!
==
___
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] Leading zeros disappear

2010-05-19 Thread Jay A. Kreibich
On Wed, May 19, 2010 at 03:36:03PM -0400, Ted Rolle, Jr. scratched on the wall:
> I'm entering UPCs.
> 
> 0071318119 becomes 71318119. This is not good, or expected, for that
> matter

  Are you entering 0071318119 (a number) or are you entering
  '0071318119' (a string)?

  The number is already a number, and the zeros will be dropped.  The
  string is a string and should be left alone:


$ ./sqlite3
SQLite version 3.6.23.1
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table t ( i text );
sqlite> insert into t values ( '000123' );
sqlite> insert into t values (  000123  );
sqlite> select * from t;
000123
123


-j


-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] SQLite file Validation

2010-05-19 Thread Dustin Sallings

On May 19, 2010, at 12:24, seandakid wrote:

> Question: Do you think that instead of getting them go back throughout their
> code, it is feasible to create a function that just eliminates the ; and
> replaces it with a ,? And if so, any suggested code?

On one hand, you have something that is safer and more efficient 
(probably tons faster depending on your app since you'd be able to reuse 
statements).

On the other, you have something that will increase your technical debt 
and give you more places to hide bugs (with false hope that you can figure out 
the difference between code and data "magically" in a new layer).

-- 
Dustin Sallings

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


[sqlite] Leading zeros disappear

2010-05-19 Thread Ted Rolle, Jr.
I'm entering UPCs.

0071318119 becomes 71318119. This is not good, or expected, for that
matter
.
The column is specified as TEXT.

I thought that TEXT values went in 'as entered'.  After reading the
docs, Section 2.0:
"If numerical data is inserted into a column with TEXT affinity it is
converted into text form before being stored."

I changed the column type to BLOB.  Same result.
A UPC field surely is not any sort of numerical value.
For instance, you cannot add two UPCs and return a valid result.

I just need to be spun around and pointed in the right direction!

-- 
__
3.14159265358979323846264338327950  Let the spirit of pi
2884197169399375105820974944592307   spread all over the world!
8164062862089986280348253421170679 http://pi314.at  PI VOBISCUM!
==
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


[sqlite] SQLite file Validation

2010-05-19 Thread seandakid

Hi all,

Total noob question for you guys:

Background: I have a project where there are many components touching
different SQLite dbs. Devs were told to make sure they are validating their
sql statements, but as I have seen in the code, few of them have. On some
components they have used the BIND statement, which will help. We are
dealing with media file inputs mostly on this project, so people might have
files named "Michael Jackson; Beat it; Thriller" or something like that.

My concern is possible sql injections throughout the different code. We are
on a tight deadline and we are unable to get back and recode a bunch of
components.

Question: Do you think that instead of getting them go back throughout their
code, it is feasible to create a function that just eliminates the ; and
replaces it with a ,? And if so, any suggested code?

Thanks so much for your time.

-Steve

-- 
View this message in context: 
http://old.nabble.com/SQLite-file-Validation-tp28612927p28612927.html
Sent from the SQLite mailing list archive at Nabble.com.

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


Re: [sqlite] Delete from FTS3 with ROWID really really slow.....

2010-05-19 Thread Black, Michael (IS)
Try showing us an "explain" of your statements.
 
Also seeing your table structure might help.
 
I take it you have a lot of keywords (like multiple 100,000's)
 
 
Michael D. Black
Senior Scientist
Northrop Grumman Mission Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of sorka
Sent: Wed 5/19/2010 1:01 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] Delete from FTS3 with ROWID really really slow.




If I run the statement:

delete from keyword where rowid in (SELECT idToDelete FROM
keywordDeleteList);

This statement takes an eternity even if there are only say 5 records in
keywordDeleteList.

Same thing if I do this:

delete from keyword where rowid in (418458, 418541, 421168, 421326, 421367,
422676);

However, this is very fast:
delete from keyword where rowid = 418458 or rowid = 418541 

The problem is I can't use a previously calculated table as a delete list.
I'd have to select each one, generate separate delete statement for each
record in the fts3 table.

I can't imagine why this is so slow. Any elegant work arounds?



--
View this message in context: 
http://old.nabble.com/Delete-from-FTS3-with-ROWID-really-really-slow.-tp28611977p28611977.html
Sent from the SQLite mailing list archive at Nabble.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] Delete from FTS3 with ROWID really really slow.....

2010-05-19 Thread sorka

If I run the statement:

delete from keyword where rowid in (SELECT idToDelete FROM
keywordDeleteList);

This statement takes an eternity even if there are only say 5 records in
keywordDeleteList.

Same thing if I do this:

delete from keyword where rowid in (418458, 418541, 421168, 421326, 421367,
422676);

However, this is very fast:
delete from keyword where rowid = 418458 or rowid = 418541 

The problem is I can't use a previously calculated table as a delete list.
I'd have to select each one, generate separate delete statement for each
record in the fts3 table.

I can't imagine why this is so slow. Any elegant work arounds?



-- 
View this message in context: 
http://old.nabble.com/Delete-from-FTS3-with-ROWID-really-really-slow.-tp28611977p28611977.html
Sent from the SQLite mailing list archive at Nabble.com.

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


Re: [sqlite] Introspection and RowID / INTEGER PRIMARY KEY

2010-05-19 Thread Simon Slavin

On 19 May 2010, at 12:05pm, Andrew Rodland wrote:

> Is there any possibility that in a future version of SQLite, pragma 
> table_info 
> or some other pragma could return a simple boolean "this column is the rowid" 
> indicator? As demonstrated under "Goofy Behavior Alert" at 
> http://www.sqlite.org/lang_createtable.html#rowid it is possible (if 
> unlikely) 
> for a column to appear as type='INTEGER' and pk=1 in the pragma table_info 
> results and yet *not* be the rowid.

Sometimes no columns are the rowid.  For instance, any case where there are no 
suitable columns:

CREATE TABLE texts(thisText TEXT)

This table does still have a rowid, but you can't access it using any of the 
columns you've declared.  Rather than find out what SQLite has done internally 
to create a rowid, might it be possible for you to refer explicitly to the 
column "_ROWID_" in your code ?  Unless this name is used in the TABLE 
definition, it is guaranteed to return the rowid values.

SELECT _rowid_, * FROM texts

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


[sqlite] Introspection and RowID / INTEGER PRIMARY KEY

2010-05-19 Thread Andrew Rodland
Is there any possibility that in a future version of SQLite, pragma table_info 
or some other pragma could return a simple boolean "this column is the rowid" 
indicator? As demonstrated under "Goofy Behavior Alert" at 
http://www.sqlite.org/lang_createtable.html#rowid it is possible (if unlikely) 
for a column to appear as type='INTEGER' and pk=1 in the pragma table_info 
results and yet *not* be the rowid. This is an annoyance that could easily be 
solved simply by letting SQLite communicate its own determination about the 
column back to the user.

Thanks,

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


Re: [sqlite] Problem with CREATE TABLE AS and primary key

2010-05-19 Thread Jay A. Kreibich
On Wed, May 19, 2010 at 02:49:05PM +0200, Raoul scratched on the wall:
> Hi Simon,
> 
> Thanks for your answer, I suppose by ", ..." in the create table command you
> mean that I should explicitly specifying each column, which is exactly what
> I didn't want to do in the first place.
> 
> By your response I guess that there is no other way of doing it then

  If you want to control the affinities and constraints of a table
  (like the PK definition), you must use CREATE TABLE.  It's that simple.

  If you need to do this for arbitrary table definitions, you can
  extract the CREATE TABLE statement for your original table from the
  sqlite_master table.  All you need to do is change the table name.

> Well I will test and decide which method from yours or the copy method from
> the sqlite website suit me the best.

  They're the same method.  Notice the fifth and sixth line from the FAQ:

CREATE TABLE t1(a,b);
INSERT INTO t1 SELECT a,b FROM t1_backup;

> > CREATE TABLE My_second_tbl( qid integer primary key, ... );
> > INSERT INTO My_second_tb SELECT * FROM My_main_tbl WHERE topic = ‘aviation’;


  -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Problem with CREATE TABLE AS and primary key

2010-05-19 Thread Raoul
Hi Simon,

Thanks for your answer, I suppose by ", ..." in the create table command you
mean that I should explicitly specifying each column, which is exactly what
I didn't want to do in the first place.

By your response I guess that there is no other way of doing it then
Well I will test and decide which method from yours or the copy method from
the sqlite website suit me the best.

Thanks for your help

In the mean time if someone else has an other idea, it will be welcome

Fabou





On 19 May 2010 10:33, Simon Davies wrote:

> On 19 May 2010 08:31, Raoul  wrote:
> > Hi all,
> >
> > I have read that it’s not possible to alter an existing table to add a
> > primary key, this is causing me some troubles. I have a table
> “My_main_tbl”
> >  from where I want to take a subset of rows in order to create a new
> table
> > with the exact same columns.
> >
> >  CREATE TABLE My_second_tbl AS SELECT * FROM My_main_tbl WHERE topic =
> > ‘aviation’
> >
> >  My problem is that is on my original table the column QID is a primary
> key
> > but on my new created table the column QID is not a primary key anymore,
> in
> > fact I have no primary key at all.
> >
> >  Is there any way of doing this without explicitly specifying each
> column,
> > and without going into doing the copy table as explained in
> > http://www.sqlite.org/faq.html#q11 ?
> >
> >  As far as I can read over the net SELECT INTO is not supported in
> sqlite,
> > right?
>
> CREATE TABLE My_second_tbl( qid integer primary key, ... );
> INSERT INTO My_second_tb SELECT * FROM My_main_tbl WHERE topic =
> ‘aviation’;
>
> >
> >  Thanks for your help,
> >
> > Fabou
>
> Regards,
> 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] read only databases and in-memory databases

2010-05-19 Thread Black, Michael (IS)
I was talking about using sleeps.  I show 1ms minimum on my system.
 
This code executes in 1 second so that makes the minimum sleep 1ms on my Linux 
system.
 
#include 
struct timespec req;
int
main ()
{
  int i;
  for (i = 0; i < 1000; i++)
{
  req.tv_nsec = 1;
  nanosleep (&req, NULL);
}
  return 0;
}

 
Michael D. Black
Senior Scientist
Northrop Grumman Mission Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of Pavel Ivanov
Sent: Tue 5/18/2010 12:55 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] read only databases and in-memory databases



> I was still talking about sleeping for spinning the BUSY return -- but just 
> doing it as fast as possible without using much CPU instead of picking 10ms 
> which seeem too long to me when the transactions should take sub-millisecond 
> times.

What do you mean by this? I don't understand you. Could you give a
code example? How do you want to "spin as fast as possible" without
sleeps and without using CPU?
Instead of picking 10ms you could of course pick 1ms or 1ns but then
OS will pick minimum sleep interval for you (on Linux it's about 10ms
IIRC).


Pavel

On Tue, May 18, 2010 at 1:50 PM, Black, Michael (IS)
 wrote:
> Rats on the interprocess locks.
>
> I was still talking about sleeping for spinning the BUSY return -- but just 
> doing it as fast as possible without using much CPU instead of picking 10ms 
> which seeem too long to me when the transactions should take sub-millisecond 
> times.
>
>
> Michael D. Black
> Senior Scientist
> Northrop Grumman Mission Systems
>
>
> 
>
> From: sqlite-users-boun...@sqlite.org on behalf of Pavel Ivanov
> Sent: Tue 5/18/2010 12:37 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] read only databases and in-memory databases
>
>
>
>> Actually I wonder if  the sqlite3 mutex calls would be better yet.
>> http://www.sqlite.org/c3ref/mutex_alloc.html
>
> They work only inside one process. They won't provide inter-process locks.
>
>> If they do what I think they would guarantee FIFO and fastest possible 
>> response to the users.
>
> I don't know how pthread mutexes are implemented but generally
> speaking mutexes don't have to guarantee FIFO and fairness in any way.
> Although most implementers of course try to make some sort of
> fairness, but only "some sort of" and no strict FIFO.
>
>> As long as you're not burning a lot of CPU why not spin the BUSY as fast as 
>> possible?
>
> Spinning without sleeping *always* burn *a lot* of CPU, no matter what
> you do inside your cycle.
>
>
> Pavel
>
> On Tue, May 18, 2010 at 1:24 PM, Black, Michael (IS)
>  wrote:
>> Actually I wonder if  the sqlite3 mutex calls would be better yet.  They 
>> would appear to guarantee synchronization and you could just allow them to 
>> block when BUSY or use the try function if you want to timeout.
>> http://www.sqlite.org/c3ref/mutex_alloc.html
>>
>> If they do what I think they would guarantee FIFO and fastest possible 
>> response to the users.  This means they would also guarantee minimum 
>> resource usage as you'll be getting rid of processes faster when contention 
>> occurs.
>>
>> As long as you're not burning a lot of CPU why not spin the BUSY as fast as 
>> possible?
>>
>> Michael D. Black
>> Senior Scientist
>> Northrop Grumman Mission Systems
>>
>>
>> 
>>
>> From: sqlite-users-boun...@sqlite.org on behalf of Simon Slavin
>> Sent: Tue 5/18/2010 11:49 AM
>> To: General Discussion of SQLite Database
>> Subject: Re: [sqlite] read only databases and in-memory databases
>>
>>
>>
>>
>> On 18 May 2010, at 4:19pm, Black, Michael (IS) wrote:
>>
>>> Interesting...but that logic means that later processes might get their 
>>> results before earlier ones.
>>
>> There is no harm in this.  In fact it's a characteristic of parallel 
>> systems.  If it truly mattered which order the results arrived in, you would 
>> be running those queries in sequence rather than parallel.
>>
>>> You'll get fairer resolution of busy contention with a fixed timeout.  Just 
>>> do 10ms 50 times.  That way the first guy in should get the first results.
>>
>>
>> There are two common forms of busy/idle handlers: one is exponential backoff 
>> (which is what the OP described) and the other is random backoff).  Both 
>> will occasionally produce results where the second query gets answered 
>> first.  Fixed delays don't work very well: if they clashed the first time, 
>> they'll probably clash the second time because they both paused for exactly 
>> the same amount of time.
>>
>> 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/listin

Re: [sqlite] Problem with CREATE TABLE AS and primary key

2010-05-19 Thread Simon Davies
On 19 May 2010 08:31, Raoul  wrote:
> Hi all,
>
> I have read that it’s not possible to alter an existing table to add a
> primary key, this is causing me some troubles. I have a table “My_main_tbl”
>  from where I want to take a subset of rows in order to create a new table
> with the exact same columns.
>
>  CREATE TABLE My_second_tbl AS SELECT * FROM My_main_tbl WHERE topic =
> ‘aviation’
>
>  My problem is that is on my original table the column QID is a primary key
> but on my new created table the column QID is not a primary key anymore, in
> fact I have no primary key at all.
>
>  Is there any way of doing this without explicitly specifying each column,
> and without going into doing the copy table as explained in
> http://www.sqlite.org/faq.html#q11 ?
>
>  As far as I can read over the net SELECT INTO is not supported in sqlite,
> right?

CREATE TABLE My_second_tbl( qid integer primary key, ... );
INSERT INTO My_second_tb SELECT * FROM My_main_tbl WHERE topic = ‘aviation’;

>
>  Thanks for your help,
>
> Fabou

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


[sqlite] ANNOUNCE - Muldis D version 0.129.1

2010-05-19 Thread Darren Duncan
Greetings,

I am pleased to announce that version 0.129.1 of the specification of the Muldis
D language, for object-relational databases, has been released on CPAN.

This release marks a milestone for Muldis D in that the /language specification/
is now fundamentally complete, with all of the important details now formally
specified.  Prior to this milestone, some important details were missing, and so
it would have been difficult to demonstrate what actual Muldis D code looks
like, whereas now it is feasible to write and understand code for any given
task.  Some code examples follow.

However, no Muldis D implementation actually exists yet, so you can't actually
/run/ any code.  The next Muldis D milestone will be the creation of a
substantially complete self-contained reference implementation, which I expect
to have done sometime in the next few months.  Until that next milestone is
reached, the Muldis D language spec is officially of pre-alpha quality; as of
that milestone, it will officially be alpha quality.

Here is the specification:

   http://search.cpan.org/dist/Muldis-D/

For those of you who are not familiar, Muldis D is an industrial-strength
computationally complete high-level programming language with fully integrated
database functionality; you can use it to define, query, and update
"object-relational" databases.  The language's paradigm is a mixture of
declarative, homoiconic, functional, imperative, and object-oriented.

The syntax of Muldis D is like that of a general purpose programming language,
with Perl 6 being a strong influence (and multiple other
languages contributing too), but it also greatly
resembles SQL as well, and so shouldn't be too difficult to learn.

For all intents and purposes you can consider Muldis D to be what SQL should
have been; it can express anything useful that SQL can, but in a much improved
manner.  So it should be easier to write more expressive and less kludgy code in
Muldis D than in SQL.  The simple code comparisons further below should
demonstrate this.

In order to get a more thorough introduction to Muldis D, please go to:

   http://muldis.com/Muldis_D.html

This document gives some side-by-side code comparisons with SQL, ideas for what
you can use Muldis D for /right now/ (in existing database tools), a list of its
features, and more.

See http://search.cpan.org/dist/Muldis-D-Manual/lib/Muldis/D/Manual/CD.pod for a
complete database schema plus test application example.

Browse through
http://search.cpan.org/dist/Muldis-D/lib/Muldis/D/Dialect/PTMD_STD.pod for the
concrete grammar plus a lot more code examples.

Here's the canonical way in Muldis D to do a common kind of query that searches
simply using value equality tests:

   $tab1 matching Relation:{
 col1 => 'hello', col2 => 5,
 col1 => 'world', col2 => 7
   }

... and here are 2 ways to do the same query in SQL:

   SELECT *
   FROM tab1
   WHERE (col1, col2) IN (
 SELECT 'hello' AS col1, 5 AS col2
 UNION
 SELECT 'world' AS col1, 7 AS col2
   )

   SELECT *
   FROM tab1
   WHERE col1 = 'hello' AND col2 = 5
  OR col1 = 'world' AND col2 = 7

Given the example relvar/table "tab" that has the 8 attributes/columns
(col1,col2,col3,bigcol4,col5,col6,col7,col8), here's how you select all but one
in Muldis D:

   $...@{!bigcol4}

... and in SQL:

   SELECT col1, col2, col3, col5, col6, col7, col8
   FROM tab

Here's the canonical Muldis D for a summary per named attributes giving the 
count:

   $peo...@{#@count_per_age_ctry <- !age,ctry}

... and the SQL:

   SELECT age, ctry, COUNT(*) AS count_per_age_ctry
   FROM people
   GROUP BY age, ctry

These are equivalent:

   'x'

... versus:

   (SELECT 'x')

   (SELECT 'x' FROM dual)

... and:

   $tab1

... versus:

   SELECT * FROM tab1

This code in an atomic statement will swap the values of 2 variables:

   $x := $y
   $y := $x

Here's an example of an atomic stored procedure that performs and returns the
result of a query; it uses a higher-order function:

 recipe count_heads (&$count : NNInt, $search : Text,
 $people ::= $fed.data.db1.people) {
 with value-filter filt (Bool <-- $topic : Tuple, $search : Text) {
 $.name like ('%' ~ $search ~ '%')
 }
 $count := r# ($people where ( $>search ))
 }

Here's an atomic stored procedure example that demonstrates self-recursion:

 updater make_coprime (&$a : NNInt, &$b : NNInt) {
 with function gcd (NNInt <-- $a : NNInt, $b : NNInt) {
 $b = 0 ?? $a !! rtn( a => $b, b => $a mod $b round Down )
 }
 $gcd ::= nlx.lib.gcd( $>a, $>b )
 $a := $a div $gcd round Down
 $b := $b div $gcd round Down
 }

See also http://mm.darrenduncan.net/mailman/listinfo for the 3 official public
email lists that are specific to Muldis D and its implementations: "-announce",
"-devel", and "-users"; just the latter 2 are for discussions.

If you want a "homepage" url to link to, you can use http://

[sqlite] Problem with CREATE TABLE AS and primary key

2010-05-19 Thread Raoul
Hi all,

I have read that it’s not possible to alter an existing table to add a
primary key, this is causing me some troubles. I have a table “My_main_tbl”
 from where I want to take a subset of rows in order to create a new table
with the exact same columns.

 CREATE TABLE My_second_tbl AS SELECT * FROM My_main_tbl WHERE topic =
‘aviation’

 My problem is that is on my original table the column QID is a primary key
but on my new created table the column QID is not a primary key anymore, in
fact I have no primary key at all.

 Is there any way of doing this without explicitly specifying each column,
and without going into doing the copy table as explained in
http://www.sqlite.org/faq.html#q11 ?

 As far as I can read over the net SELECT INTO is not supported in sqlite,
right?

 Thanks for your help,

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