Tables as ASCII - is it possible?

classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

Tables as ASCII - is it possible?

Paul Linehan-2
Hi all,

Is there a way of storing SQLite data (tables) as ASCII text rather
than as binary data?

I want to be able to run scripts against my data as well as use SQLite.

If it's not available as a "normal" option, is there a patch on the interweb
somewhere?

If it does not exist, I respectfully suggest that it is worthy of consideration
as a feature - maybe along the lines of MySQL storage engines?

I respect the fact that this is an Open Source project with
a Public Domain licence (i.e. volunteer coders)  and I'm not
demanding anything, merely suggesting that it might be a good idea.


Paul...


--

[hidden email]

Mob: 00 353 86 864 5772
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Tables as ASCII - is it possible?

Simon Slavin-3

On 23 Oct 2011, at 5:06am, Paul Linehan wrote:

> Is there a way of storing SQLite data (tables) as ASCII text rather
> than as binary data?
>
> I want to be able to run scripts against my data as well as use SQLite.

I recommend you script the sqlite3 shell tool to pipe whatever data you want, or to make a text file of it which you can then read:

http://www.sqlite.org/sqlite.html

download it from here:

http://www.sqlite.org/download.html

> If it's not available as a "normal" option, is there a patch on the interweb
> somewhere?
>
> If it does not exist, I respectfully suggest that it is worthy of consideration
> as a feature - maybe along the lines of MySQL storage engines?

My guess is that it would slow down SQLite by a factor of three or more.  Feel free to write it, and contribute your code.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Tables as ASCII - is it possible?

Puneet Kishor-2
In reply to this post by Paul Linehan-2

On Oct 22, 2011, at 11:06 PM, Paul Linehan wrote:

> Hi all,
>
> Is there a way of storing SQLite data (tables) as ASCII text rather
> than as binary data?
>
> I want to be able to run scripts against my data as well as use SQLite.
> ..

Are you suggesting that you want to treat text data as a SQL data store? You might want to look at DBD::CSV [http://search.cpan.org/~hmbrand/DBD-CSV-0.33/lib/DBD/CSV.pm]

--
Puneet Kishor

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Tables as ASCII - is it possible?

Paul Linehan-2
In reply to this post by Simon Slavin-3
2011/10/23 Simon Slavin <[hidden email]>:


>> I want to be able to run scripts against my data as well as use SQLite.

> I recommend you script the sqlite3 shell tool to pipe whatever data you want,
> or to make a text file of it which you can then read:

Looks that this is the way to go alright!


>> If it does not exist, I respectfully suggest that it is worthy of consideration
>> as a feature - maybe along the lines of MySQL storage engines?

> My guess is that it would slow down SQLite by a factor of three or more.

I don't doubt for a second that the ASCII option would be slower than
the current one - only if the user chose it though.


> Feel free to write it, and contribute your code.


I consider anything more sophisticated than "Hello World" to
be advanced C! ;) Thanks for your input.


Paul...


> Simon.


[hidden email]

Mob: 00 353 86 864 5772
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Tables as ASCII - is it possible?

Paul Linehan-2
In reply to this post by Puneet Kishor-2
2011/10/23 Mr. Puneet Kishor <[hidden email]>:


>> I want to be able to run scripts against my data as well as use SQLite.

> Are you suggesting that you want to treat text data as a SQL data store?


No, I'm suggesting that the SQLite engine be able to have table data
available as text files - table1.txt, table2.txt &c.

I have to be able to send text files to a machine that doesn't have
SQLite and won't (very very conservative manager! :-( ).


> You might want to look at DBD::CSV [http://search.cpan.org/
> ~hmbrand/DBD-CSV-0.33/lib/DBD/CSV.pm]


Nope, Perl is out also - anyway, I wouldn't use it. If I could go with
a scripting language, it would be Python - vastly superior IMHO
to Perl - YMMV.

It's shell scripting that I want to be able to do - remember my manager?

Thanks for your input.


Paul...


> Puneet Kishor


--

[hidden email]

Mob: 00 353 86 864 5772
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Tables as ASCII - is it possible?

Puneet Kishor-2

On Oct 22, 2011, at 11:34 PM, Paul Linehan wrote:

> If I could go with
> a scripting language, it would be Python - vastly superior IMHO
> to Perl - YMMV.


Yup. My mileage does vary.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Tables as ASCII - is it possible?

Roger Binns
In reply to this post by Paul Linehan-2
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 22/10/11 21:06, Paul Linehan wrote:
> Is there a way of storing SQLite data (tables) as ASCII text rather
> than as binary data?
>
> I want to be able to run scripts against my data as well as use
> SQLite.

Yes, and it is very easy.  SQLite has something called virtual tables
where you can register some code to handle tables.  That code can then
look in text files, make internet queries or whatever else meets your
needs.  You can then use regular SQL to make queries independent of what
data format is underlying them.

Virtual tables are documented here:

  http://www.sqlite.org/vtab.html

If you are happy using a higher level language like Python then my APSW
module lets you implement virtual tables easily.  Here is example code
that provides directory listings as a virtual table:

  http://apidoc.apsw.googlecode.com/hg/example.html#example-vtable

Reference documentation:

  http://apidoc.apsw.googlecode.com/hg/vtable.html

APSW includes a more complicated virtual table that lets you use SQLite
against a CouchDB backend:

  http://apidoc.apsw.googlecode.com/hg/couchdb.html

SQLite also provides some functionality using virtual tables such as full
text search and rtree:

  http://www.sqlite.org/fts3.html
  http://www.sqlite.org/rtree.html

Roger
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk6jsQQACgkQmOOfHg372QQltgCffbPN2BgbF9MMkrVRs+AM587u
C3oAni6mPiFaZDGCt0WpKu2XfPsqhBAS
=E3QJ
-----END PGP SIGNATURE-----
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Tables as ASCII - is it possible?

Petite Abeille-2
In reply to this post by Paul Linehan-2

On Oct 23, 2011, at 6:06 AM, Paul Linehan wrote:

> Is there a way of storing SQLite data (tables) as ASCII text rather
> than as binary data?

Perhaps you might be better off with something along the lines of KirbyBase or such.

http://www.netpromi.com/kirbybase_python.html
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Tables as ASCII - is it possible?

Jay Kreibich
In reply to this post by Paul Linehan-2
On Sun, Oct 23, 2011 at 05:06:46AM +0100, Paul Linehan scratched on the wall:
> Hi all,
>
> Is there a way of storing SQLite data (tables) as ASCII text rather
> than as binary data?
>
> I want to be able to run scripts against my data as well as use SQLite.

  SQLite has drivers for most popular scripting languages.  Just access
  the database as it was meant to be accessed.

   -j

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

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users